def recycle_journeyids(conn,data): cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cur.execute(""" create temporary table NewJourney( id bigserial primary key NOT NULL, privatecode varchar(255) NOT NULL, operator_id varchar(255) NOT NULL, availabilityconditionRef integer NOT NULL, journeypatternref integer NOT NULL, timedemandgroupref integer NOT NULL, productCategoryRef integer, noticeassignmentRef integer, departuretime integer, blockref varchar(255), name varchar(255), lowfloor boolean, hasLiftOrRamp boolean, haswifi boolean, bicycleAllowed boolean, onDemand boolean, isvirtual boolean default(false) ); """) for key,journey in data['JOURNEY'].items(): journey = deepcopy(journey) setRefsDict(journey,data['AVAILABILITYCONDITION'],'availabilityconditionref') setRefsDict(journey,data['JOURNEYPATTERN'],'journeypatternref') setRefsDict(journey,data['TIMEDEMANDGROUP'],'timedemandgroupref') setRefsDict(journey,data['NOTICEASSIGNMENT'],'noticeassignmentref',ignore_null=True) setRefsDict(journey,data['PRODUCTCATEGORY'],'productcategoryref') exists,id = simple_dict_insert(conn,'NEWJOURNEY',journey,check_existing=False,return_id=True) cur.execute(""" SELECT jn.operator_id,jo.id,jn.id as tmp_id FROM journey as jo,newjourney as jn WHERE jo.departuretime = jn.departuretime AND jo.privatecode = jn.privatecode AND jo.availabilityconditionref NOT IN (SELECT DISTINCT availabilityconditionref FROM availabilityconditionday WHERE isavailable = true) """) for row in cur.fetchall(): data['JOURNEY'][row['operator_id']]['id'] = row['id'] cur.execute("delete from newjourney where id = %s",[row['tmp_id']]) cur.execute("delete from journeytransfers where journeyref = %s or onwardjourneyref = %s",[row['id']]*2)
def recycle_journeyids(conn, data): cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cur.execute(""" create temporary table NewJourney( id bigserial primary key NOT NULL, privatecode varchar(255) NOT NULL, operator_id varchar(255) NOT NULL, availabilityconditionRef integer, journeypatternref integer, timedemandgroupref integer, productCategoryRef integer, noticeassignmentRef integer, departuretime integer, blockref varchar(255), name varchar(255), lowfloor boolean, hasLiftOrRamp boolean, haswifi boolean, bicycleAllowed boolean, onDemand boolean, isvirtual boolean default(false) ); """) for key, journey in data['JOURNEY'].items(): journey = deepcopy(journey) setRefsDict(journey, data['AVAILABILITYCONDITION'], 'availabilityconditionref') setRefsDict(journey, data['JOURNEYPATTERN'], 'journeypatternref') setRefsDict(journey, data['TIMEDEMANDGROUP'], 'timedemandgroupref') setRefsDict(journey, data['NOTICEASSIGNMENT'], 'noticeassignmentref', ignore_null=True) setRefsDict(journey, data['PRODUCTCATEGORY'], 'productcategoryref') exists, id = simple_dict_insert(conn, 'NEWJOURNEY', journey, check_existing=False, return_id=True) cur.execute( """ SELECT array_agg(distinct newjourney.availabilityconditionref) as availabilityconditions FROM journey JOIN (SELECT availabilityconditionref,array_agg(validdate ORDER BY validdate) as days FROM availabilityconditionday GROUP BY availabilityconditionref) as jac USING (availabilityconditionref) JOIN timedemandgroup as oj ON (oj.id = journey.timedemandgroupref) JOIN journeypattern as ojp ON (ojp.id = journey.journeypatternref) JOIN route AS orr ON (orr.id = ojp.routeref) JOIN line AS ol ON (ol.id = orr.lineref) JOIN (SELECT journeypatternref,array_agg(pointref ORDER BY pointorder) as points FROM pointinjourneypattern GROUP BY journeypatternref) as jjp USING (journeypatternref) ,newjourney JOIN (SELECT availabilityconditionref,array_agg(validdate ORDER BY validdate) as days FROM availabilityconditionday WHERE isavailable = true GROUP BY availabilityconditionref) as nac USING (availabilityconditionref) JOIN timedemandgroup as nt ON (nt.id = newjourney.timedemandgroupref) JOIN journeypattern as njpp ON (njpp.id = newjourney.journeypatternref) JOIN route AS nr ON (nr.id = njpp.routeref) JOIN line AS nl ON (nl.id = nr.lineref) JOIN (SELECT journeypatternref,array_agg(pointref ORDER BY pointorder) as points FROM pointinjourneypattern GROUP BY journeypatternref) as njp USING (journeypatternref) WHERE journey.operator_id = newjourney.operator_id AND --nac.days = jac.days AND (jjp.points != njp.points OR oj.operator_id != nt.operator_id OR nl.operatorref != ol.operatorref); """, [data['_validfrom']]) availabilityConditionrefs = cur.fetchone()['availabilityconditions'] print availabilityConditionrefs if availabilityConditionrefs is None: availabilityConditionrefs = [] print str(len(availabilityConditionrefs)) + ' calendars dirty' cur.execute( """ UPDATE availabilityconditionday SET isavailable = false WHERE availabilityConditionref = any(%s) AND validdate < %s AND availabilityconditionref in (SELECT id FROM availabilitycondition WHERE versionref = %s) ; """, [availabilityConditionrefs, data['_validfrom'], data['VERSION']['1']]) cur.execute("CREATE INDEX ON newjourney(operator_id)") cur.execute( """ SELECT journey.operator_id,journey.id,newjourney.id as tmp_id FROM journey JOIN availabilitycondition as oac ON (oac.id = journey.availabilityconditionref) ,newjourney JOIN availabilitycondition as nac ON (nac.id = newjourney.availabilityconditionref) WHERE journey.operator_id = newjourney.operator_id AND (%s = 0 or newjourney.availabilityconditionref != any(%s)) """, [len(availabilityConditionrefs), availabilityConditionrefs]) count = 0 for row in cur.fetchall(): count += 1 data['JOURNEY'][row['operator_id']]['id'] = row['id'] cur.execute("delete from newjourney where id = %s", [row['tmp_id']]) print str(count) + ' journeys recycled'
def recycle_journeyids(conn,data): cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) cur.execute(""" create temporary table NewJourney( id bigserial primary key NOT NULL, privatecode varchar(255) NOT NULL, operator_id varchar(255) NOT NULL, availabilityconditionRef integer, journeypatternref integer, timedemandgroupref integer, productCategoryRef integer, noticeassignmentRef integer, departuretime integer, blockref varchar(255), name varchar(255), lowfloor boolean, hasLiftOrRamp boolean, haswifi boolean, bicycleAllowed boolean, onDemand boolean, isvirtual boolean default(false) ); """) for key,journey in data['JOURNEY'].items(): journey = deepcopy(journey) setRefsDict(journey,data['AVAILABILITYCONDITION'],'availabilityconditionref') setRefsDict(journey,data['JOURNEYPATTERN'],'journeypatternref') setRefsDict(journey,data['TIMEDEMANDGROUP'],'timedemandgroupref') setRefsDict(journey,data['NOTICEASSIGNMENT'],'noticeassignmentref',ignore_null=True) setRefsDict(journey,data['PRODUCTCATEGORY'],'productcategoryref') exists,id = simple_dict_insert(conn,'NEWJOURNEY',journey,check_existing=False,return_id=True) cur.execute(""" SELECT array_agg(distinct newjourney.availabilityconditionref) as availabilityconditions FROM journey JOIN (SELECT availabilityconditionref,array_agg(validdate ORDER BY validdate) as days FROM availabilityconditionday GROUP BY availabilityconditionref) as jac USING (availabilityconditionref) JOIN timedemandgroup as oj ON (oj.id = journey.timedemandgroupref) JOIN journeypattern as ojp ON (ojp.id = journey.journeypatternref) JOIN route AS orr ON (orr.id = ojp.routeref) JOIN line AS ol ON (ol.id = orr.lineref) JOIN (SELECT journeypatternref,array_agg(pointref ORDER BY pointorder) as points FROM pointinjourneypattern GROUP BY journeypatternref) as jjp USING (journeypatternref) ,newjourney JOIN (SELECT availabilityconditionref,array_agg(validdate ORDER BY validdate) as days FROM availabilityconditionday WHERE isavailable = true GROUP BY availabilityconditionref) as nac USING (availabilityconditionref) JOIN timedemandgroup as nt ON (nt.id = newjourney.timedemandgroupref) JOIN journeypattern as njpp ON (njpp.id = newjourney.journeypatternref) JOIN route AS nr ON (nr.id = njpp.routeref) JOIN line AS nl ON (nl.id = nr.lineref) JOIN (SELECT journeypatternref,array_agg(pointref ORDER BY pointorder) as points FROM pointinjourneypattern GROUP BY journeypatternref) as njp USING (journeypatternref) WHERE journey.operator_id = newjourney.operator_id AND --nac.days = jac.days AND (jjp.points != njp.points OR oj.operator_id != nt.operator_id OR nl.operatorref != ol.operatorref); """,[data['_validfrom']]) availabilityConditionrefs = cur.fetchone()['availabilityconditions'] print availabilityConditionrefs if availabilityConditionrefs is None: availabilityConditionrefs = [] print str(len(availabilityConditionrefs)) + ' calendars dirty' cur.execute(""" UPDATE availabilityconditionday SET isavailable = false WHERE availabilityConditionref = any(%s) AND validdate < %s AND availabilityconditionref in (SELECT id FROM availabilitycondition WHERE versionref = %s) ; """,[availabilityConditionrefs,data['_validfrom'],data['VERSION']['1']]) cur.execute("CREATE INDEX ON newjourney(operator_id)") cur.execute(""" SELECT journey.operator_id,journey.id,newjourney.id as tmp_id FROM journey JOIN availabilitycondition as oac ON (oac.id = journey.availabilityconditionref) ,newjourney JOIN availabilitycondition as nac ON (nac.id = newjourney.availabilityconditionref) WHERE journey.operator_id = newjourney.operator_id AND (%s = 0 or newjourney.availabilityconditionref != any(%s)) """,[len(availabilityConditionrefs),availabilityConditionrefs]) count = 0 for row in cur.fetchall(): count += 1 data['JOURNEY'][row['operator_id']]['id'] = row['id'] cur.execute("delete from newjourney where id = %s",[row['tmp_id']]) print str(count) + ' journeys recycled'