def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"dbname=epanet_test_copy_db host=" + host + " user="******"dropdb --if-exists -h " + host + " -U " + pguser + " epanet_test_db") os.system("dropdb --if-exists -h " + host + " -U " + pguser + " epanet_test_copy_db") os.system("createdb -h " + host + " -U " + pguser + " epanet_test_db") os.system("createdb -h " + host + " -U " + pguser + " epanet_test_copy_db") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -c 'CREATE EXTENSION postgis'") os.system("psql -h " + host + " -U " + pguser + " epanet_test_copy_db -c 'CREATE EXTENSION postgis'") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -f " + test_data_dir + "/epanet_test_db_uuid.sql") # chechout #tables = ['epanet_trunk_rev_head.junctions','epanet_trunk_rev_head.pipes'] tables = ['epanet_trunk_rev_head.junctions', 'epanet_trunk_rev_head.pipes'] pgversioning = versioning.pgLocal(pg_conn_info, 'epanet_trunk_rev_head', pg_conn_info_cpy) pgversioning.checkout(tables) pcurcpy = versioning.Db(psycopg2.connect(pg_conn_info_cpy)) pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcurcpy.execute( "INSERT INTO epanet_trunk_rev_head.pipes_view(id, start_node, end_node, geom) VALUES ('2','1','2',ST_GeometryFromText('LINESTRING(1 1,0 1)',2154))" ) pcurcpy.execute( "INSERT INTO epanet_trunk_rev_head.pipes_view(id, start_node, end_node, geom) VALUES ('3','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))" ) pcurcpy.commit() prtHid(pcurcpy, 'epanet_trunk_rev_head.pipes_view') pcurcpy.execute("SELECT * FROM epanet_trunk_rev_head.pipes_view") assert (len(pcurcpy.fetchall()) == 3) pcur.execute("SELECT * FROM epanet.pipes") assert (len(pcur.fetchall()) == 1) pgversioning.commit('INSERT') pcur.execute("SELECT * FROM epanet.pipes") assert (len(pcur.fetchall()) == 3) pcurcpy.execute( "UPDATE epanet_trunk_rev_head.pipes_view SET start_node = '2' WHERE id = '0'" ) pcurcpy.commit() pcurcpy.execute("SELECT * FROM epanet_trunk_rev_head.pipes_view") assert (len(pcurcpy.fetchall()) == 3) pcur.execute("SELECT * FROM epanet.pipes") assert (len(pcur.fetchall()) == 3) pgversioning.commit('UPDATE') pcur.execute("SELECT * FROM epanet.pipes") assert (len(pcur.fetchall()) == 4) pcurcpy.close() pcur.close()
def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"dropdb --if-exists -h " + host + " -U "+pguser+" epanet_test_db") os.system("createdb -h " + host + " -U "+pguser+" epanet_test_db") os.system("psql -h " + host + " -U "+pguser+" epanet_test_db -c 'CREATE EXTENSION postgis'") pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcur.execute("CREATE SCHEMA epanet") pcur.execute(""" CREATE TABLE epanet.junctions ( hid serial PRIMARY KEY, id varchar, elevation float, base_demand_flow float, demand_pattern_id varchar, printmap integer[], geometry geometry('POINT',2154), geometry_schematic geometry('POLYGON',2154) )""") pcur.execute(""" INSERT INTO epanet.junctions (id, elevation, printmap, geometry, geometry_schematic) VALUES ('0',0,'{1,2,3}',ST_GeometryFromText('POINT(0 0)',2154), ST_GeometryFromText('POLYGON((-1 -1,1 -1,1 1,-1 1,-1 -1))',2154))""") pcur.execute(""" INSERT INTO epanet.junctions (id, elevation, printmap, geometry, geometry_schematic) VALUES ('1',1,'{}',ST_GeometryFromText('POINT(0 1)',2154), ST_GeometryFromText('POLYGON((0 0,2 0,2 2,0 2,0 0))',2154))""") pcur.execute(""" CREATE TABLE epanet.pipes ( hid serial PRIMARY KEY, id varchar, start_node varchar, end_node varchar, length float, diameter float, roughness float, minor_loss_coefficient float, status varchar, geometry geometry('LINESTRING',2154) )""") pcur.execute(""" INSERT INTO epanet.pipes (id, start_node, end_node, length, diameter, geometry) VALUES ('0','0','1',1,2,ST_GeometryFromText('LINESTRING(1 0,0 1)',2154))""") pcur.commit() pcur.close() versioning.historize( pg_conn_info, 'epanet' ) pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcur.execute("SELECT ST_AsText(geometry), ST_AsText(geometry_schematic) FROM epanet_trunk_rev_head.junctions") res = pcur.fetchall() assert( res[0][0] == 'POINT(0 0)' ) assert( res[1][1] == 'POLYGON((0 0,2 0,2 2,0 2,0 0))' ) wc = tmp_dir+'/wc_multiple_geometry_test.sqlite' if os.path.isfile(wc): os.remove(wc) spversioning = versioning.spatialite(wc, pg_conn_info) spversioning.checkout( ['epanet_trunk_rev_head.pipes','epanet_trunk_rev_head.junctions'] ) scur = versioning.Db( dbapi2.connect(wc) ) scur.execute("UPDATE junctions_view SET GEOMETRY = GeometryFromText('POINT(3 3)',2154) WHERE OGC_FID = 1") scur.commit() scur.execute("SELECT * from junctions_view") print("--------------") for res in scur.fetchall(): print(res) scur.close() spversioning.commit( 'moved a junction' ) pcur.execute("SELECT ST_AsText(geometry), ST_AsText(geometry_schematic), printmap FROM epanet_trunk_rev_head.junctions ORDER BY versioning_id DESC") res = pcur.fetchall() for r in res: print(r) assert( res[0][0] == 'POINT(3 3)' ) assert( res[0][1] == 'POLYGON((-1 -1,1 -1,1 1,-1 1,-1 -1))' ) assert( res[0][2] == [1, 2, 3] ) pcur.close()
def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"dropdb --if-exists -h " + host + " -U " + pguser + " epanet_test_db") os.system("createdb -h " + host + " -U " + pguser + " epanet_test_db") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -c 'CREATE EXTENSION postgis'") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -f " + test_data_dir + "/epanet_test_db.sql") # try the update wc = [ os.path.join(tmp_dir, "issue437_wc0.sqlite"), os.path.join(tmp_dir, "issue437_wc1.sqlite") ] spversioning0 = versioning.spatialite(wc[0], pg_conn_info) spversioning1 = versioning.spatialite(wc[1], pg_conn_info) for i, f in enumerate(wc): if os.path.isfile(f): os.remove(f) sp = spversioning0 if i == 0 else spversioning1 sp.checkout( ['epanet_trunk_rev_head.junctions', 'epanet_trunk_rev_head.pipes']) scur = [] for f in wc: scur.append(versioning.Db(dbapi2.connect(f))) scur[0].execute( "INSERT INTO pipes_view(id, start_node, end_node, geom) VALUES ('2','1','2',GeomFromText('LINESTRING(1 1,0 1)',2154))" ) scur[0].execute( "INSERT INTO pipes_view(id, start_node, end_node, geom) VALUES ('3','1','2',GeomFromText('LINESTRING(1 -1,0 1)',2154))" ) scur[0].commit() spversioning0.commit('commit 1 wc0') spversioning1.update() scur[0].execute("UPDATE pipes_view SET length = 1") scur[0].commit() scur[1].execute("UPDATE pipes_view SET length = 2") scur[1].execute("UPDATE pipes_view SET length = 3") scur[1].commit() spversioning0.commit("commit 2 wc0") scur[0].execute( "SELECT OGC_FID,length,trunk_rev_begin,trunk_rev_end,trunk_parent,trunk_child FROM pipes" ) print('################') for r in scur[0].fetchall(): print(r) scur[0].execute("UPDATE pipes_view SET length = 2") scur[0].execute("DELETE FROM pipes_view WHERE OGC_FID = 6") scur[0].commit() spversioning0.commit("commit 3 wc0") scur[0].execute( "SELECT OGC_FID,length,trunk_rev_begin,trunk_rev_end,trunk_parent,trunk_child FROM pipes" ) print('################') for r in scur[0].fetchall(): print(r) spversioning1.update() scur[1].execute( "SELECT OGC_FID,length,trunk_rev_begin,trunk_rev_end,trunk_parent,trunk_child FROM pipes_diff" ) print('################ diff') for r in scur[1].fetchall(): print(r) scur[1].execute("SELECT conflict_id FROM pipes_conflicts") assert (len(scur[1].fetchall()) == 6) # there must be conflicts scur[1].execute( "SELECT conflict_id,origin,action,OGC_FID,trunk_parent,trunk_child FROM pipes_conflicts" ) print('################') for r in scur[1].fetchall(): print(r) scur[1].execute( "DELETE FROM pipes_conflicts WHERE origin='theirs' AND conflict_id=1") scur[1].commit() scur[1].execute("SELECT conflict_id FROM pipes_conflicts") assert (len(scur[1].fetchall()) == 4) # there must be two removed entries scur[1].execute( "SELECT conflict_id,origin,action,OGC_FID,trunk_parent,trunk_child FROM pipes_conflicts" ) print('################') for r in scur[1].fetchall(): print(r) scur[1].execute( "DELETE FROM pipes_conflicts WHERE origin='mine' AND OGC_FID = 11") scur[1].execute("DELETE FROM pipes_conflicts WHERE origin='theirs'") scur[1].commit() scur[1].execute("SELECT conflict_id FROM pipes_conflicts") assert (len(scur[1].fetchall()) == 0) # there must be no conflict scur[1].execute( "SELECT OGC_FID,length,trunk_rev_begin,trunk_rev_end,trunk_parent,trunk_child FROM pipes" ) print('################') for r in scur[1].fetchall(): print(r)
def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"dropdb --if-exists -h " + host + " -U "+pguser+" epanet_test_db") os.system("createdb -h " + host + " -U "+pguser+" epanet_test_db") os.system("psql -h " + host + " -U "+pguser+" epanet_test_db -c 'CREATE EXTENSION postgis'") os.system("psql -h " + host + " -U "+pguser+" epanet_test_db -f "+test_data_dir+"/epanet_test_db.sql") # chechout #tables = ['epanet_trunk_rev_head.junctions','epanet_trunk_rev_head.pipes'] tables = ['epanet_trunk_rev_head.junctions', 'epanet_trunk_rev_head.pipes'] pgversioning1 = versioning.pgServer(pg_conn_info, 'epanet_working_copy') pgversioning2 = versioning.pgServer(pg_conn_info, 'epanet_working_copy_cflt') pgversioning1.checkout(tables) pgversioning2.checkout(tables) pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcur.execute("INSERT INTO epanet_working_copy.pipes_view(id, start_node, end_node, geom) VALUES ('2','1','2',ST_GeometryFromText('LINESTRING(1 1,0 1)',2154))") pcur.execute("INSERT INTO epanet_working_copy.pipes_view(id, start_node, end_node, geom) VALUES ('3','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))") pcur.commit() prtHid(pcur, 'epanet_working_copy.pipes_view') pcur.execute("SELECT pid FROM epanet_working_copy.pipes_view") assert( len(pcur.fetchall()) == 3 ) pcur.execute("SELECT pid FROM epanet_working_copy.pipes_diff") assert( len(pcur.fetchall()) == 2 ) pcur.execute("SELECT pid FROM epanet.pipes") assert( len(pcur.fetchall()) == 1 ) prtTab(pcur, 'epanet.pipes') prtTab(pcur, 'epanet_working_copy.pipes_diff') pcur.execute("UPDATE epanet_working_copy.pipes_view SET length = 4 WHERE pid = 1") prtTab(pcur, 'epanet_working_copy.pipes_diff') pcur.execute("UPDATE epanet_working_copy.pipes_view SET length = 5 WHERE pid = 4") prtTab(pcur, 'epanet_working_copy.pipes_diff') pcur.execute("DELETE FROM epanet_working_copy.pipes_view WHERE pid = 4") prtTab(pcur, 'epanet_working_copy.pipes_diff') pcur.commit() pgversioning1.commit("test commit msg") prtTab(pcur, 'epanet.pipes') pcur.execute("SELECT trunk_rev_end FROM epanet.pipes WHERE pid = 1") assert( 1 == pcur.fetchone()[0] ) pcur.execute("SELECT COUNT(*) FROM epanet.pipes WHERE trunk_rev_begin = 2") assert( 2 == pcur.fetchone()[0] ) # modify the second working copy to create conflict prtTab(pcur, 'epanet.pipes') pcur.execute("SELECT * FROM epanet_working_copy_cflt.initial_revision") print('-- epanet_working_copy_cflt.initial_revision ---') for r in pcur.fetchall(): print(r) prtHid(pcur, 'epanet_working_copy_cflt.pipes_view') prtTab(pcur, 'epanet_working_copy_cflt.pipes_diff') pcur.execute("UPDATE epanet_working_copy_cflt.pipes_view SET length = 8 WHERE pid = 1") pcur.commit() prtTab(pcur, 'epanet.pipes') prtTab(pcur, 'epanet_working_copy_cflt.pipes_diff') pcur.execute("SELECT COUNT(*) FROM epanet_working_copy_cflt.pipes_diff") for l in pcur.con.notices: print(l) assert( 2 == pcur.fetchone()[0] ) pcur.execute("INSERT INTO epanet_working_copy_cflt.pipes_view(id, start_node, end_node, geom) VALUES ('3','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))") prtTab(pcur, 'epanet_working_copy_cflt.pipes_diff') pcur.commit() pgversioning2.update( ) prtTab(pcur, 'epanet_working_copy_cflt.pipes_diff') prtTab(pcur, 'epanet_working_copy_cflt.pipes_update_diff') pcur.execute("SELECT COUNT(*) FROM epanet_working_copy_cflt.pipes_conflicts") assert( 2 == pcur.fetchone()[0] ) pcur.execute("SELECT COUNT(*) FROM epanet_working_copy_cflt.pipes_conflicts WHERE origin = 'mine'") assert( 1 == pcur.fetchone()[0] ) pcur.execute("SELECT COUNT(*) FROM epanet_working_copy_cflt.pipes_conflicts WHERE origin = 'theirs'") assert( 1 == pcur.fetchone()[0] ) prtTab(pcur, 'epanet_working_copy_cflt.pipes_conflicts') pcur.execute("DELETE FROM epanet_working_copy_cflt.pipes_conflicts WHERE origin = 'theirs'") pcur.execute("SELECT COUNT(*) FROM epanet_working_copy_cflt.pipes_conflicts") assert( 0 == pcur.fetchone()[0] ) prtTab(pcur, 'epanet_working_copy_cflt.pipes_diff') prtTab(pcur, 'epanet_working_copy_cflt.pipes_conflicts') pcur.commit() pgversioning2.commit("second test commit msg") pcur.execute("SELECT * FROM epanet_working_copy_cflt.initial_revision") print('-- epanet_working_copy_cflt.initial_revision ---') for r in pcur.fetchall(): print(r) prtHid(pcur, 'epanet_working_copy_cflt.pipes_view') prtTab(pcur, 'epanet_working_copy_cflt.pipes_diff') pcur.execute("UPDATE epanet_working_copy_cflt.pipes_view SET length = 8") prtTab(pcur, 'epanet_working_copy_cflt.pipes_diff') pcur.commit() pgversioning2.commit("third test commit msg") prtTab(pcur, 'epanet_working_copy_cflt.pipes_diff') pcur.execute("UPDATE epanet_working_copy_cflt.pipes_view SET length = 12") pcur.commit()
def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"dropdb --if-exists -h " + host + " -U "+pguser+" epanet_test_db") os.system("createdb -h " + host + " -U "+pguser+" epanet_test_db") os.system("psql -h " + host + " -U "+pguser+" epanet_test_db -c 'CREATE EXTENSION postgis'") pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcur.execute("CREATE SCHEMA epanet") pcur.execute(""" CREATE TABLE epanet.junctions ( hid serial PRIMARY KEY, id varchar, elevation float, base_demand_flow float, demand_pattern_id varchar, geometry geometry('POINT',2154), geometry_schematic geometry('POLYGON',2154) )""") pcur.execute(""" INSERT INTO epanet.junctions (id, elevation, geometry, geometry_schematic) VALUES ('0',0,ST_GeometryFromText('POINT(0 0)',2154), ST_GeometryFromText('POLYGON((-1 -1,1 -1,1 1,-1 1,-1 -1))',2154))""") pcur.execute(""" INSERT INTO epanet.junctions (id, elevation, geometry, geometry_schematic) VALUES ('1',1,ST_GeometryFromText('POINT(0 1)',2154), ST_GeometryFromText('POLYGON((0 0,2 0,2 2,0 2,0 0))',2154))""") pcur.execute(""" CREATE TABLE epanet.pipes ( hid serial PRIMARY KEY, id varchar, start_node varchar, end_node varchar, length float, diameter float, roughness float, minor_loss_coefficient float, status varchar, geometry geometry('LINESTRING',2154) )""") pcur.execute(""" INSERT INTO epanet.pipes (id, start_node, end_node, length, diameter, geometry) VALUES ('0','0','1',1,2,ST_GeometryFromText('LINESTRING(1 0,0 1)',2154))""") pcur.commit() pcur.close() versioning.historize( pg_conn_info, 'epanet' ) failed = False try: versioning.add_branch( pg_conn_info, 'epanet', 'trunk' ) except: failed = True assert( failed ) failed = False try: versioning.add_branch( pg_conn_info, 'epanet', 'mybranch', 'message', 'toto' ) except: failed = True assert( failed ) versioning.add_branch( pg_conn_info, 'epanet', 'mybranch', 'test msg' ) pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcur.execute("SELECT * FROM epanet_mybranch_rev_head.junctions") assert( len(pcur.fetchall()) == 2 ) pcur.execute("SELECT * FROM epanet_mybranch_rev_head.pipes") assert( len(pcur.fetchall()) == 1 ) ##versioning.add_revision_view( pg_conn_info, 'epanet', 'mybranch', 2) ##pcur.execute("SELECT * FROM epanet_mybranch_rev_2.junctions") ##assert( len(pcur.fetchall()) == 2 ) ##pcur.execute("SELECT * FROM epanet_mybranch_rev_2.pipes") ##assert( len(pcur.fetchall()) == 1 ) select_and_where_str = versioning.rev_view_str( pg_conn_info, 'epanet', 'junctions','mybranch', 2) #print select_and_where_str[0] + " WHERE " + select_and_where_str[1] pcur.execute(select_and_where_str[0] + " WHERE " + select_and_where_str[1]) assert( len(pcur.fetchall()) == 2 ) select_and_where_str = versioning.rev_view_str( pg_conn_info, 'epanet', 'pipes','mybranch', 2) #print select_and_where_str[0] + " WHERE " + select_and_where_str[1] pcur.execute(select_and_where_str[0] + " WHERE " + select_and_where_str[1]) assert( len(pcur.fetchall()) == 1 ) ##pcur.execute("SELECT ST_AsText(geometry), ST_AsText(geometry_schematic) FROM epanet_mybranch_rev_2.junctions") pcur.execute("SELECT ST_AsText(geometry), ST_AsText(geometry_schematic) FROM epanet.junctions") res = pcur.fetchall() assert( res[0][0] == 'POINT(0 0)' ) assert( res[1][1] == 'POLYGON((0 0,2 0,2 2,0 2,0 0))' ) wc = os.path.join(tmp_dir, 'wc_multiple_geometry_test.sqlite') spversioning = versioning.spatialite(wc, pg_conn_info) if os.path.isfile(wc): os.remove(wc) spversioning.checkout( ['epanet_trunk_rev_head.pipes','epanet_trunk_rev_head.junctions'] ) scur = versioning.Db( dbapi2.connect(wc) ) scur.execute("UPDATE junctions_view SET GEOMETRY = GeometryFromText('POINT(3 3)',2154)") scur.commit() scur.close() spversioning.commit( 'a commit msg' ) pcur.execute("SELECT ST_AsText(geometry), ST_AsText(geometry_schematic) FROM epanet_trunk_rev_head.junctions") res = pcur.fetchall() for r in res: print r assert( res[0][0] == 'POINT(3 3)' ) assert( res[1][1] == 'POLYGON((0 0,2 0,2 2,0 2,0 0))' ) pcur.close()
def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"dropdb --if-exists -h " + host + " -U " + pguser + " epanet_test_db") os.system("createdb -h " + host + " -U " + pguser + " epanet_test_db") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -c 'CREATE EXTENSION postgis'") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -f " + test_data_dir + "/epanet_test_db.sql") # chechout #tables = ['epanet_trunk_rev_head.junctions','epanet_trunk_rev_head.pipes'] tables = ['epanet_trunk_rev_head.junctions', 'epanet_trunk_rev_head.pipes'] pgversioning = versioning.pgServer(pg_conn_info, 'epanet_working_copy') pgversioning.checkout(tables) pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcur.execute( "INSERT INTO epanet_working_copy.pipes_view(id, start_node, end_node, geom) VALUES ('2','1','2',ST_GeometryFromText('LINESTRING(1 1,0 1)',2154))" ) pcur.commit() pgversioning.commit("rev 1") pcur.execute( "INSERT INTO epanet_working_copy.pipes_view(id, start_node, end_node, geom) VALUES ('3','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))" ) pcur.commit() pgversioning.commit("rev 2") pcur.execute( "INSERT INTO epanet_working_copy.pipes_view(id, start_node, end_node, geom) VALUES ('4','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))" ) pcur.commit() pgversioning.commit("rev 3") pcur.execute( "INSERT INTO epanet_working_copy.pipes_view(id, start_node, end_node, geom) VALUES ('5','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))" ) pcur.commit() pgversioning.commit("rev 4") pcur.execute("DELETE FROM epanet_working_copy.pipes_view S WHERE pid = 5") pcur.commit() pgversioning.commit("rev 5") pcur.execute( "INSERT INTO epanet_working_copy.pipes_view(id, start_node, end_node, geom) VALUES ('6','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))" ) pcur.commit() pgversioning.commit("rev 6") pcur.execute( "UPDATE epanet_working_copy.pipes_view SET length = 4 WHERE pid = 3") pcur.commit() pgversioning.commit("rev 7") pcur.execute( "UPDATE epanet_working_copy.pipes_view SET length = 4 WHERE pid = 1") pcur.commit() pgversioning.commit("rev 8") pcur.execute( "INSERT INTO epanet_working_copy.pipes_view(id, start_node, end_node, geom) VALUES ('7','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))" ) pcur.commit() pgversioning.commit("rev 9") pcur.execute( "INSERT INTO epanet_working_copy.pipes_view(id, start_node, end_node, geom) VALUES ('8','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))" ) pcur.commit() pgversioning.commit("rev 10") pcur.execute("DELETE FROM epanet_working_copy.pipes_view S WHERE pid = 7") pcur.commit() pgversioning.commit("rev 11") pcur.execute( "INSERT INTO epanet_working_copy.pipes_view(id, start_node, end_node, geom) VALUES ('9','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))" ) pcur.commit() pgversioning.commit("rev 12") pcur.execute("SELECT * FROM epanet.pipes ORDER BY pid") end = pcur.fetchall() printTab(pcur, 'epanet', 'pipes') pcur.execute("SELECT count(*) FROM epanet.pipes") [ret] = pcur.fetchone() assert (ret == 11) versioning.archive(pg_conn_info, 'epanet', 7) printTab(pcur, 'epanet', 'pipes') pcur.execute("SELECT count(*) FROM epanet.pipes") [ret] = pcur.fetchone() assert (ret == 9) pcur.execute("SELECT pid FROM epanet.pipes ORDER BY pid") ret = pcur.fetchall() assert (list(zip(*ret)[0]) == [1, 2, 4, 6, 7, 8, 9, 10, 11]) printTab(pcur, 'epanet_archive', 'pipes') pcur.execute("SELECT count(*) FROM epanet_archive.pipes") [ret] = pcur.fetchone() assert (ret == 2) pcur.execute("SELECT pid FROM epanet_archive.pipes ORDER BY pid") ret = pcur.fetchall() assert (list(zip(*ret)[0]) == [3, 5]) versioning.archive(pg_conn_info, 'epanet', 11) printTab(pcur, 'epanet', 'pipes') pcur.execute("SELECT count(*) FROM epanet.pipes") [ret] = pcur.fetchone() assert (ret == 7) pcur.execute("SELECT pid FROM epanet.pipes ORDER BY pid") ret = pcur.fetchall() assert (list(zip(*ret)[0]) == [2, 4, 6, 8, 9, 10, 11]) printTab(pcur, 'epanet_archive', 'pipes') pcur.execute("SELECT count(*) FROM epanet_archive.pipes") [ret] = pcur.fetchone() assert (ret == 4) pcur.execute("SELECT pid FROM epanet_archive.pipes ORDER BY pid") ret = pcur.fetchall() assert (list(zip(*ret)[0]) == [1, 3, 5, 7]) # view printTab(pcur, 'epanet_archive', 'pipes_all') pcur.execute("SELECT count(*) FROM epanet_archive.pipes_all") [ret] = pcur.fetchone() assert (ret == 11) pcur.execute("SELECT * FROM epanet_archive.pipes_all ORDER BY pid") endv = pcur.fetchall() assert (end == endv) pcur.close()
def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"dbname=epanet_test_copy_db host=" + host + " user="******"dropdb --if-exists -h " + host + " -U " + pguser + " epanet_test_db") os.system("dropdb --if-exists -h " + host + " -U " + pguser + " epanet_test_copy_db") os.system("createdb -h " + host + " -U " + pguser + " epanet_test_db") os.system("createdb -h " + host + " -U " + pguser + " epanet_test_copy_db") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -f " + test_data_dir + "/epanet_test_db.sql") versioning.historize( "dbname=epanet_test_db host={} user={}".format(host, pguser), "epanet") # chechout #tables = ['epanet_trunk_rev_head.junctions','epanet_trunk_rev_head.pipes'] tables = ['epanet_trunk_rev_head.junctions', 'epanet_trunk_rev_head.pipes'] pgversioning = versioning.pgLocal(pg_conn_info, 'epanet_trunk_rev_head', pg_conn_info_cpy) pgversioning.checkout(tables) pcurcpy = versioning.Db(psycopg2.connect(pg_conn_info_cpy)) pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcurcpy.execute( "INSERT INTO epanet_trunk_rev_head.pipes_view(id, start_node, end_node, geom) VALUES ('2','1','2',ST_GeometryFromText('LINESTRING(1 1,0 1)',2154))" ) pcurcpy.execute( "INSERT INTO epanet_trunk_rev_head.pipes_view(id, start_node, end_node, geom) VALUES ('3','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))" ) pcurcpy.commit() prtHid(pcurcpy, 'epanet_trunk_rev_head.pipes_view') pcurcpy.execute("SELECT * FROM epanet_trunk_rev_head.pipes_view") assert (len(pcurcpy.fetchall()) == 3) pcur.execute("SELECT * FROM epanet.pipes") assert (len(pcur.fetchall()) == 1) pgversioning.commit('INSERT') pcur.execute("SELECT * FROM epanet.pipes") assert (len(pcur.fetchall()) == 3) pcurcpy.execute( "UPDATE epanet_trunk_rev_head.pipes_view SET start_node = '2' WHERE id = '1'" ) pcurcpy.commit() pcurcpy.execute("SELECT * FROM epanet_trunk_rev_head.pipes_view") assert (len(pcurcpy.fetchall()) == 3) pcur.execute("SELECT * FROM epanet.pipes") assert (len(pcur.fetchall()) == 3) pgversioning.commit('UPDATE') pcur.execute("SELECT * FROM epanet.pipes") assert (len(pcur.fetchall()) == 4) pcurcpy.execute( "DELETE FROM epanet_trunk_rev_head.pipes_view WHERE id = '2'") pcurcpy.commit() pcurcpy.execute("SELECT * FROM epanet_trunk_rev_head.pipes_view") assert (len(pcurcpy.fetchall()) == 2) pcur.execute("SELECT * FROM epanet.pipes") assert (len(pcur.fetchall()) == 4) pgversioning.commit('DELETE') pcur.execute("SELECT * FROM epanet.pipes") assert (len(pcur.fetchall()) == 4) sqlite_test_filename1 = os.path.join(tmp_dir, "versioning_base_test1.sqlite") if os.path.isfile(sqlite_test_filename1): os.remove(sqlite_test_filename1) spversioning1 = versioning.spatialite(sqlite_test_filename1, pg_conn_info) spversioning1.checkout( ['epanet_trunk_rev_head.pipes', 'epanet_trunk_rev_head.junctions']) scon = dbapi2.connect(sqlite_test_filename1) scon.enable_load_extension(True) scon.execute("SELECT load_extension('mod_spatialite')") scur = scon.cursor() scur.execute( "INSERT INTO pipes_view(id, start_node, end_node, geom) VALUES (4, 1, 2,ST_GeometryFromText('LINESTRING(2 0, 0 2)',2154))" ) scon.commit() spversioning1.commit("sp commit") pgversioning.update() pcur.execute("SELECT * FROM epanet.pipes") assert (len(pcur.fetchall()) == 5) pcurcpy.execute("SELECT * FROM epanet_trunk_rev_head.pipes") assert (len(pcurcpy.fetchall()) == 5) pcur.execute("SELECT * FROM epanet_trunk_rev_head.pipes") assert (len(pcur.fetchall()) == 3) pcurcpy.execute("SELECT * FROM epanet_trunk_rev_head.pipes_view") assert (len(pcurcpy.fetchall()) == 3) pcur.execute( "SELECT versioning_id FROM epanet_trunk_rev_head.pipes ORDER BY versioning_id" ) ret = pcur.fetchall() assert ([i[0] for i in ret] == [3, 4, 5]) pcurcpy.execute( "SELECT ogc_fid FROM epanet_trunk_rev_head.pipes_view ORDER BY ogc_fid" ) ret = pcurcpy.fetchall() assert ([i[0] for i in ret] == [3, 4, 5]) pcurcpy.execute( "INSERT INTO epanet_trunk_rev_head.pipes_view(id, start_node, end_node, geom) VALUES (5,'1','2',ST_GeometryFromText('LINESTRING(3 2,0 1)',2154))" ) pcurcpy.commit() pgversioning.commit('INSERT AFTER UPDATE') pcurcpy.close() pcur.close()
def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"dropdb --if-exists -h " + host + " -U " + pguser + " epanet_test_db") os.system("createdb -h " + host + " -U " + pguser + " epanet_test_db") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -c 'CREATE EXTENSION postgis'") pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcur.execute("CREATE SCHEMA epanet") pcur.execute(""" CREATE TABLE epanet.junctions ( hid serial PRIMARY KEY, id varchar, elevation float, base_demand_flow float, demand_pattern_id varchar, geom geometry('POINT',2154) )""") pcur.execute(""" INSERT INTO epanet.junctions (id, elevation, geom) VALUES ('0',0,ST_GeometryFromText('POINT(1 0)',2154))""") pcur.execute(""" INSERT INTO epanet.junctions (id, elevation, geom) VALUES ('1',1,ST_GeometryFromText('POINT(0 1)',2154))""") pcur.execute(""" CREATE TABLE epanet.pipes ( hid serial PRIMARY KEY, id varchar, start_node varchar, end_node varchar, length float, diameter float, roughness float, minor_loss_coefficient float, status varchar, geom geometry('LINESTRING',2154) )""") pcur.execute(""" INSERT INTO epanet.pipes (id, start_node, end_node, length, diameter, geom) VALUES ('0','0','1',1,2,ST_GeometryFromText('LINESTRING(1 0,0 1)',2154))""" ) pcur.commit() pcur.close() versioning.historize(pg_conn_info, 'epanet') failed = False try: versioning.add_branch(pg_conn_info, 'epanet', 'trunk') except: failed = True assert (failed) failed = False try: versioning.add_branch(pg_conn_info, 'epanet', 'mybranch', 'message', 'toto') except: failed = True assert (failed) versioning.add_branch(pg_conn_info, 'epanet', 'mybranch', 'test msg') pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcur.execute("SELECT * FROM epanet_mybranch_rev_head.junctions") assert (len(pcur.fetchall()) == 2) pcur.execute("SELECT * FROM epanet_mybranch_rev_head.pipes") assert (len(pcur.fetchall()) == 1) ##versioning.add_revision_view( pg_conn_info, 'epanet', 'mybranch', 2) ##pcur.execute("SELECT * FROM epanet_mybranch_rev_2.junctions") ##assert( len(pcur.fetchall()) == 2 ) ##pcur.execute("SELECT * FROM epanet_mybranch_rev_2.pipes") ##assert( len(pcur.fetchall()) == 1 ) select_str, where_str = versioning.rev_view_str(pg_conn_info, 'epanet', 'junctions', 'mybranch', 2) pcur.execute(select_str + " WHERE " + where_str) assert (len(pcur.fetchall()) == 2) select_str, where_str = versioning.rev_view_str(pg_conn_info, 'epanet', 'pipes', 'mybranch', 2) pcur.execute(select_str + " WHERE " + where_str) assert (len(pcur.fetchall()) == 1) pcur.close()
def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"dropdb --if-exists -h " + host + " -U " + pguser + " epanet_test_db") os.system("createdb -h " + host + " -U " + pguser + " epanet_test_db") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -c 'CREATE EXTENSION postgis'") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -f " + test_data_dir + "/epanet_test_db.sql") # branch versioning.add_branch(pg_conn_info, "epanet", "mybranch", "add 'branch") # chechout from branch : epanet_brwcs_rev_head #tables = ['epanet_trunk_rev_head.junctions','epanet_trunk_rev_head.pipes'] tables = [ 'epanet_mybranch_rev_head.junctions', 'epanet_mybranch_rev_head.pipes' ] pgversioning = versioning.pgServer(pg_conn_info, 'epanet_brwcs_rev_head') pgversioning.checkout(tables) pcur = versioning.Db(psycopg2.connect(pg_conn_info)) # insert into epanet_brwcs_rev_head pcur.execute( "INSERT INTO epanet_brwcs_rev_head.pipes_view(id, start_node, end_node, geom) VALUES ('2','1','2',ST_GeometryFromText('LINESTRING(1 1,0 1)',2154))" ) pcur.execute( "INSERT INTO epanet_brwcs_rev_head.pipes_view(id, start_node, end_node, geom) VALUES ('3','1','2',ST_GeometryFromText('LINESTRING(1 -1,0 1)',2154))" ) pcur.execute( "DELETE FROM epanet_brwcs_rev_head.junctions_view WHERE jid=2") pcur.execute("DELETE FROM epanet_brwcs_rev_head.pipes_view WHERE pid=3") pcur.commit() pgversioning.commit("commit", "postgres") versioning.merge(pg_conn_info, "epanet", "mybranch") pcur.execute("SELECT max(rev) FROM epanet.revisions") assert (pcur.fetchone()[0] == 4) pcur.execute( "SELECT rev, commit_msg, branch FROM epanet.revisions WHERE rev=4") assert (pcur.fetchall() == [(4, 'Merge branch mybranch into trunk', 'trunk')]) pcur.execute( "SELECT pid, trunk_rev_begin, trunk_rev_end, mybranch_rev_begin,mybranch_rev_end FROM epanet.pipes" ) assert (pcur.fetchall() == [(1, 1, None, 2, None), (2, 3, None, 3, None)]) pcur.execute( "SELECT jid, trunk_rev_begin, trunk_rev_end, mybranch_rev_begin,mybranch_rev_end FROM epanet.junctions" ) assert (pcur.fetchall() == [(1, 1, None, 2, None), (2, 1, 2, 2, 2)]) pcur.close()
def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"dropdb --if-exists -h " + host + " -U " + pguser + " epanet_test_db") os.system("createdb -h " + host + " -U " + pguser + " epanet_test_db") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -c 'CREATE EXTENSION postgis'") os.system("psql -h " + host + " -U " + pguser + " epanet_test_db -f " + test_data_dir + "/epanet_test_db.sql") versioning.historize( "dbname=epanet_test_db host={} user={}".format(host, pguser), "epanet") pcur = versioning.Db(psycopg2.connect(pg_conn_info)) tables = [ 'epanet_trunk_rev_head.junctions', 'epanet_trunk_rev_head.pipes' ] pgversioning1 = versioning.pgServer(pg_conn_info, 'wc1') pgversioning2 = versioning.pgServer(pg_conn_info, 'wc2') pgversioning1.checkout(tables) pgversioning2.checkout(tables) print("checkout done") pcur.execute( "UPDATE wc1.pipes_view SET length = 4 WHERE versioning_id = 1") prtTab(pcur, "wc1.pipes_diff") pcur.commit() #pcur.close() pgversioning1.commit("msg1") #pcur = versioning.Db(psycopg2.connect(pg_conn_info)) print("commited") pcur.execute( "UPDATE wc2.pipes_view SET length = 5 WHERE versioning_id = 1") prtTab(pcur, "wc2.pipes_diff") pcur.commit() pgversioning2.update() print("updated") prtTab(pcur, "wc2.pipes_diff") prtTab(pcur, "wc2.pipes_conflicts") pcur.execute( "SELECT COUNT(*) FROM wc2.pipes_conflicts WHERE origin = 'mine'") assert (1 == pcur.fetchone()[0]) pcur.execute( "SELECT COUNT(*) FROM wc2.pipes_conflicts WHERE origin = 'theirs'") assert (1 == pcur.fetchone()[0]) pcur.execute("DELETE FROM wc2.pipes_conflicts WHERE origin = '" + resolution + "'") prtTab(pcur, "wc2.pipes_conflicts") pcur.execute("SELECT COUNT(*) FROM wc2.pipes_conflicts") assert (0 == pcur.fetchone()[0]) pcur.close()