def __init__(self, host, pguser, schema, additional_sql=None): self.schema = schema self.cur = None self.con = None self.versioning = None self.pg_conn_info = "dbname={} host={} user={}".format( dbname, host, pguser) test_data_dir = os.path.dirname(os.path.realpath(__file__)) sql_file = os.path.join(test_data_dir, "composite_primary_key_db.sql") # create the test database os.system(f"dropdb --if-exists -h {host} -U {pguser} {dbname}") os.system(f"createdb -h {host} -U {pguser} {dbname}") os.system(f"psql -h {host} -U {pguser} {dbname} -f {sql_file}") self.pcon = psycopg2.connect(self.pg_conn_info) self.pcur = self.pcon.cursor() if additional_sql: self.pcur.execute(additional_sql) self.pcon.commit() versioning.historize( "dbname={} host={} user={}".format(dbname, host, pguser), b_schema)
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") # try the update wc = tmp_dir+"/issue358_wc.sqlite" if os.path.isfile(wc): os.remove(wc) spversioning = versioning.spatialite(wc, pg_conn_info) spversioning.checkout(['epanet_trunk_rev_head.junctions', 'epanet_trunk_rev_head.pipes']) scur = versioning.Db( dbapi2.connect( wc ) ) scur.execute("SELECT * FROM pipes") assert( len(scur.fetchall()) == 1 ) scur.execute("UPDATE pipes_view SET length = 1 WHERE OGC_FID = 1") scur.execute("SELECT * FROM pipes") assert( len(scur.fetchall()) == 2 ) scur.execute("UPDATE pipes_view SET length = 2 WHERE OGC_FID = 2") scur.execute("SELECT * FROM pipes") assert( len(scur.fetchall()) == 2 )
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") # try the update wc = tmp_dir + "/issue358_wc.sqlite" if os.path.isfile(wc): os.remove(wc) spversioning = versioning.spatialite(wc, pg_conn_info) spversioning.checkout( ['epanet_trunk_rev_head.junctions', 'epanet_trunk_rev_head.pipes']) scur = versioning.Db(dbapi2.connect(wc)) scur.execute("SELECT * FROM pipes") assert (len(scur.fetchall()) == 1) scur.execute("UPDATE pipes_view SET length = 1 WHERE OGC_FID = 1") scur.execute("SELECT * FROM pipes") assert (len(scur.fetchall()) == 2) scur.execute("UPDATE pipes_view SET length = 2 WHERE OGC_FID = 2") scur.execute("SELECT * FROM pipes") assert (len(scur.fetchall()) == 2)
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") # checkout pgversioning = versioning.pgServer(pg_conn_info, 'epanet_working_copy') pgversioning.checkout( ['epanet_trunk_rev_head.junctions', 'epanet_trunk_rev_head.pipes']) pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcur.execute( "UPDATE epanet_working_copy.pipes_view SET length = 4 WHERE id = 1") prtTab(pcur, 'epanet_working_copy.pipes_diff') prtHid(pcur, 'epanet_working_copy.pipes_view') pcur.execute("SElECT COUNT(id) FROM epanet_working_copy.pipes_view") assert (1 == pcur.fetchone()[0])
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_unversioned.sql") versioning.historize(pg_conn_info, "epanet") # try the update wc = tmp_dir + "/bug_in_branch_after_commit_wc.sqlite" if os.path.isfile(wc): os.remove(wc) spversioning = versioning.spatialite(wc, pg_conn_info) spversioning.checkout( ['epanet_trunk_rev_head.junctions', 'epanet_trunk_rev_head.pipes']) scur = versioning.Db(dbapi2.connect(wc)) scur.execute("SELECT * FROM pipes") scur.execute("UPDATE pipes_view SET length = 1 WHERE OGC_FID = 1") scur.commit() spversioning.commit('test') versioning.add_branch(pg_conn_info, "epanet", "mybranch", "add 'branch")
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 -f "+test_data_dir+"/epanet_test_db.sql") versioning.historize(pg_conn_info,"epanet") # try the update wc = tmp_dir+"/bug_in_branch_after_commit_wc.sqlite" if os.path.isfile(wc): os.remove(wc) spversioning = versioning.spatialite(wc, pg_conn_info) spversioning.checkout(['epanet_trunk_rev_head.junctions', 'epanet_trunk_rev_head.pipes']) scur = versioning.Db( dbapi2.connect( wc ) ) scur.execute("SELECT * FROM pipes") scur.execute("UPDATE pipes_view SET length = 1 WHERE OGC_FID = 1") scur.commit() spversioning.commit('test') versioning.add_branch(pg_conn_info,"epanet","mybranch","add 'branch")
def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"partial_checkout_test.sqlite") if os.path.isfile(sqlite_test_filename): os.remove(sqlite_test_filename) spversioning = versioning.spatialite(sqlite_test_filename, pg_conn_info) # create the test database os.system("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_unversioned.sql") pcon = psycopg2.connect(pg_conn_info) pcur = pcon.cursor() for i in range(10): pcur.execute(""" INSERT INTO epanet.junctions (id, elevation, geom) VALUES ('{id}', {elev}, ST_GeometryFromText('POINT({x} {y})',2154)); """.format(id=i + 2, elev=float(i), x=float(i + 1), y=float(i + 1))) pcon.commit() pcon.close() versioning.historize(pg_conn_info, 'epanet') # spatialite working copy spversioning.checkout( ["epanet_trunk_rev_head.junctions", "epanet_trunk_rev_head.pipes"], [[1, 2, 3], []]) assert (os.path.isfile(sqlite_test_filename) and "sqlite file must exist at this point") scon = dbapi2.connect(sqlite_test_filename) scur = scon.cursor() scur.execute("SELECT * from junctions") assert len(scur.fetchall()) == 3 # postgres working copy pgversioning = versioning.pgServer(pg_conn_info, 'my_working_copy') pgversioning.checkout( ["epanet_trunk_rev_head.junctions", "epanet_trunk_rev_head.pipes"], [[1, 2, 3], []]) pcon = psycopg2.connect(pg_conn_info) pcur = pcon.cursor() pcur.execute("SELECT * from my_working_copy.junctions_view") assert len(pcur.fetchall()) == 3
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") # 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.pipes_view WHERE id=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 versioning_id, 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.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()
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") # 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.pipes_view WHERE id=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 versioning_id, 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.close()
def test(host, pguser): dbname = "epanet_test_db" test_data_dir = os.path.dirname(os.path.realpath(__file__)) sql_file = os.path.join(test_data_dir, "epanet_test_db.sql") tmp_dir = tempfile.gettempdir() # create the test database os.system(f"dropdb --if-exists -h {host} -U {pguser} {dbname}") os.system(f"createdb -h {host} -U {pguser} {dbname}") os.system(f"psql -h {host} -U {pguser} {dbname} -f {sql_file}") pg_conn_info = f"dbname={dbname} host={host} user={pguser}" pcon = psycopg2.connect(pg_conn_info) pcur = pcon.cursor() pcur.execute("CREATE TYPE type_example AS ENUM('TEST1', 'TEST2')") pcur.execute("ALTER TABLE epanet.junctions " "ADD COLUMN type_field type_example;") pcon.commit() versioning.historize(pg_conn_info, "epanet") # try the update wc = tmp_dir + "/issue_type.sqlite" if os.path.isfile(wc): os.remove(wc) spversioning = versioning.spatialite(wc, pg_conn_info) spversioning.checkout(['epanet_trunk_rev_head.junctions']) scur = versioning.Db(dbapi2.connect(wc)) # scur.execute("SELECT * FROM pipes") # assert( len(scur.fetchall()) == 1 ) scur.execute("UPDATE junctions_view " "SET type_field = 'TEST1' WHERE OGC_FID = 1") scur.commit() spversioning.commit("test type") pcon = psycopg2.connect(pg_conn_info) pcur = pcon.cursor() pcur.execute("SELECT type_field FROM epanet.junctions " "WHERE id = 1 AND trunk_rev_end IS NULL") res = pcur.fetchall() assert (len(res) == 1) assert (res[0][0] == "TEST1")
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") # checkout pgversioning = versioning.pgServer(pg_conn_info, 'epanet_working_copy') pgversioning.checkout(['epanet_trunk_rev_head.junctions','epanet_trunk_rev_head.pipes']) pcur = versioning.Db(psycopg2.connect(pg_conn_info)) pcur.execute("UPDATE epanet_working_copy.pipes_view SET length = 4 WHERE id = 1") prtTab(pcur, 'epanet_working_copy.pipes_diff') prtHid( pcur, 'epanet_working_copy.pipes_view') pcur.execute("SElECT COUNT(id) FROM epanet_working_copy.pipes_view") assert(1 == pcur.fetchone()[0])
def __init__(self, host, pguser, schema): self.schema = schema self.cur = None self.con = None self.versioning = None self.pg_conn_info = f"dbname=epanet_test_db host={host} user={pguser}" self.pg_conn_info_cpy = f"dbname=epanet_test_copy_db host={host} user={pguser}" test_data_dir = os.path.dirname(os.path.realpath(__file__)) # create the test database os.system(f"dropdb --if-exists -h {host} -U {pguser} epanet_test_db") os.system(f"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") self.pcon = psycopg2.connect(self.pg_conn_info) self.pcur = self.pcon.cursor() for i in range(10): self.pcur.execute(""" INSERT INTO epanet.junctions (id, elevation, geom) VALUES ('{id}', {elev}, ST_GeometryFromText('POINT({x} {y})',2154)); """.format( id=i+3, elev=float(i), x=float(i+1), y=float(i+1) )) self.pcon.commit() versioning.historize(self.pg_conn_info, 'epanet')
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="******"/abbreviation_test.sqlite" if os.path.isfile(sqlite_test_filename): os.remove(sqlite_test_filename) spversioning = versioning.spatialite(sqlite_test_filename, pg_conn_info) # create the test database os.system("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 -f " + test_data_dir + "/epanet_test_db.sql") # delete existing data pcon = psycopg2.connect(pg_conn_info) pcur = pcon.cursor() for i in range(10): pcur.execute(""" INSERT INTO epanet.junctions (demand_pattern_id, elevation, geom) VALUES ('{demand_pattern_id}', {elev}, ST_GeometryFromText('POINT({x} {y})',2154)); """.format(demand_pattern_id=str(i + 2) + longname, elev=float(i), x=float(i + 1), y=float(i + 1))) pcon.commit() versioning.historize(pg_conn_info, 'epanet') spversioning.checkout( ["epanet_trunk_rev_head.junctions", "epanet_trunk_rev_head.pipes"]) assert (os.path.isfile(sqlite_test_filename) and "sqlite file must exist at this point") scon = dbapi2.connect(sqlite_test_filename) scon.enable_load_extension(True) scon.execute("SELECT load_extension('mod_spatialite')") scur = scon.cursor() scur.execute("SELECT id, demand_pattern_id from junctions") for rec in scur: if rec[0] > 2: assert rec[1].find(longname) != -1 scur.execute(f""" update junctions_view set demand_pattern_id='{another_longname}' where ogc_fid > 8""") scur.execute(f""" insert into junctions_view(id, demand_pattern_id, elevation, geom) select 13, '{new_longname}', elevation, geom from junctions_view where ogc_fid=4""") scon.commit() spversioning.commit('a commit msg') pcur.execute("""select versioning_id, demand_pattern_id from epanet_trunk_rev_head.junctions""") for row in pcur: print(row) if row[0] > 8: assert row[1].find(another_longname) != -1\ or row[1].find(new_longname) != -1
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'") 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'] 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 versioning_id FROM epanet_working_copy.pipes_view") assert( len(pcur.fetchall()) == 3 ) pcur.execute("SELECT versioning_id FROM epanet_working_copy.pipes_diff") assert( len(pcur.fetchall()) == 2 ) pcur.execute("SELECT versioning_id 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 versioning_id = 1") prtTab(pcur, 'epanet_working_copy.pipes_diff') pcur.execute("UPDATE epanet_working_copy.pipes_view SET length = 5 WHERE versioning_id = 4") prtTab(pcur, 'epanet_working_copy.pipes_diff') pcur.execute("DELETE FROM epanet_working_copy.pipes_view WHERE versioning_id = 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 versioning_id = 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 versioning_id = 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 (4,'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="******"versioning_base_test1.sqlite") sqlite_test_filename2 = os.path.join(tmp_dir, "versioning_base_test2.sqlite") sqlite_test_filename3 = os.path.join(tmp_dir, "versioning_base_test3.sqlite") sqlite_test_filename4 = os.path.join(tmp_dir, "versioning_base_test4.sqlite") sqlite_test_filename5 = os.path.join(tmp_dir, "versioning_base_test5.sqlite") if os.path.isfile(sqlite_test_filename1): os.remove(sqlite_test_filename1) if os.path.isfile(sqlite_test_filename2): os.remove(sqlite_test_filename2) if os.path.isfile(sqlite_test_filename3): os.remove(sqlite_test_filename3) if os.path.isfile(sqlite_test_filename4): os.remove(sqlite_test_filename4) if os.path.isfile(sqlite_test_filename5): os.remove(sqlite_test_filename5) # create the test database os.system("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 -f "+test_data_dir+"/epanet_test_db.sql") versioning.historize("dbname=epanet_test_db host={} user={}".format(host,pguser), "epanet") spversioning1 = versioning.spatialite(sqlite_test_filename1, pg_conn_info) spversioning2 = versioning.spatialite(sqlite_test_filename2, pg_conn_info) spversioning3 = versioning.spatialite(sqlite_test_filename3, pg_conn_info) spversioning4 = versioning.spatialite(sqlite_test_filename4, pg_conn_info) spversioning5 = versioning.spatialite(sqlite_test_filename5, pg_conn_info) # chechout two tables try: spversioning1.checkout(["epanet_trunk_rev_head.junctions","epanet.pipes"]) assert(False and "checkout from schema withouti suffix _branch_rev_head should not be successfull") except RuntimeError: pass assert( not os.path.isfile(sqlite_test_filename1) and "sqlite file must not exist at this point" ) spversioning1.checkout(["epanet_trunk_rev_head.junctions","epanet_trunk_rev_head.pipes"]) assert( os.path.isfile(sqlite_test_filename1) and "sqlite file must exist at this point" ) try: spversioning1.checkout(["epanet_trunk_rev_head.junctions","epanet_trunk_rev_head.pipes"]) assert(False and "trying to checkout on an existing file must fail") except RuntimeError: pass # edit one table and commit changes; rev = 2 scon = dbapi2.connect(sqlite_test_filename1) scon.enable_load_extension(True) scon.execute("SELECT load_extension('mod_spatialite')") scur = scon.cursor() scur.execute("UPDATE junctions_view SET elevation = '8' WHERE id = '2'") scon.commit() scur.execute("SELECT COUNT(*) FROM junctions") assert( scur.fetchone()[0] == 3 ) scon.close() spversioning1.commit('first edit commit') pcon = psycopg2.connect(pg_conn_info) pcur = pcon.cursor() pcur.execute("SELECT COUNT(*) FROM epanet.junctions") assert( pcur.fetchone()[0] == 3 ) pcur.execute("SELECT COUNT(*) FROM epanet.revisions") assert( pcur.fetchone()[0] == 2 ) # add revision : edit one table and commit changes; rev = 3 spversioning2.checkout(["epanet_trunk_rev_head.junctions", "epanet_trunk_rev_head.pipes"]) scon = dbapi2.connect(sqlite_test_filename2) scon.enable_load_extension(True) scon.execute("SELECT load_extension('mod_spatialite')") scur = scon.cursor() scur.execute("UPDATE junctions_view SET elevation = '22' WHERE id = '2'") scon.commit() #scur.execute("SELECT COUNT(*) FROM junctions") #assert( scur.fetchone()[0] == 3 ) scon.close() spversioning2.commit('second edit commit') # add revision : insert one junction and commit changes; rev = 4 spversioning3.checkout(["epanet_trunk_rev_head.junctions"]) scon = dbapi2.connect(sqlite_test_filename3) scon.enable_load_extension(True) scon.execute("SELECT load_extension('mod_spatialite')") scur = scon.cursor() scur.execute("INSERT INTO junctions_view(id, elevation, geom) VALUES ('10','100',GeomFromText('POINT(2 0)',2154))") scon.commit() #scur.execute("SELECT COUNT(*) FROM junctions") #assert( scur.fetchone()[0] == 3 ) scon.close() spversioning3.commit('insert commit') # add revision : delete one junction and commit changes; rev = 5 spversioning4.checkout(["epanet_trunk_rev_head.junctions", "epanet_trunk_rev_head.pipes"]) scon = dbapi2.connect(sqlite_test_filename4) scur = scon.cursor() # remove pipes so wen can delete referenced junctions scur.execute("DELETE FROM pipes_view") scon.commit() scur.execute("SELECT COUNT(*) FROM pipes_view") assert(scur.fetchone()[0]==0) scur.execute("DELETE FROM junctions_view WHERE id = 1") scon.commit() #scur.execute("SELECT COUNT(*) FROM junctions") #assert( scur.fetchone()[0] == 3 ) scon.close() spversioning4.commit('delete id=1 commit') select_str = diff_rev_view_str(pg_conn_info, 'epanet', 'junctions','trunk', 1,2) pcur.execute(select_str) res = pcur.fetchall() assert(res[0][0] == 'u') #print("fetchall 1 vs 2 = " + str(res)) #fetchall 1 vs 2 = [ #('u', 3, '1', 8.0, None, None, '01010000206A0800000000000000000000000000000000F03F', 2, 2, 2, 4)] select_str = diff_rev_view_str(pg_conn_info, 'epanet', 'junctions','trunk', 1,3) pcur.execute(select_str) res = pcur.fetchall() assert(res[0][0] == 'i') assert(res[1][0] == 'u') #print("fetchall 1 vs 3 = " + str(res)) #fetchall 1 vs 3 = [ #('u', 4, '1', 22.0, None, None, '01010000206A0800000000000000000000000000000000F03F', 3, None, 3, None), #('i', 3, '1', 8.0, None, None, '01010000206A0800000000000000000000000000000000F03F', 2, 2, 2, 4)] select_str = diff_rev_view_str(pg_conn_info, 'epanet', 'junctions','trunk', 1,4) pcur.execute(select_str) res = pcur.fetchall() assert(res[0][0] == 'i') assert(res[1][0] == 'i') assert(res[2][0] == 'u') assert(res[3][0] == 'a') # object is in intermediate state; will be deleted in rev 5 #print("fetchall 1 vs 4 = " + str(res)) #fetchall 1 vs 4 = [ #('u', 4, '1', 22.0, None, None, '01010000206A0800000000000000000000000000000000F03F', 3, None, 3, None), #('i', 3, '1', 8.0, None, None, '01010000206A0800000000000000000000000000000000F03F', 2, 2, 2, 4), #('a', 5, '10', 100.0, None, None, '01010000206A08000000000000000000400000000000000000', 4, None, None, None), #('i', 1, '0', 0.0, None, None, '01010000206A080000000000000000F03F0000000000000000', 1, 4, None, None)] select_str = diff_rev_view_str(pg_conn_info, 'epanet', 'junctions','trunk', 1,5) pcur.execute(select_str) res = pcur.fetchall() assert(res[0][0] == 'd') assert(res[1][0] == 'i') assert(res[2][0] == 'u') assert(res[3][0] == 'a') #print("fetchall 1 vs 5 = " + str(res)) #fetchall 1 vs 5 = [ #('u', 4, '1', 22.0, None, None, '01010000206A0800000000000000000000000000000000F03F', 3, None, 3, None), #('i', 3, '1', 8.0, None, None, '01010000206A0800000000000000000000000000000000F03F', 2, 2, 2, 4), #('a', 5, '10', 100.0, None, None, '01010000206A08000000000000000000400000000000000000', 4, None, None, None), #('d', 1, '0', 0.0, None, None, '01010000206A080000000000000000F03F0000000000000000', 1, 4, None, None)] # add revision : edit one table then delete and commit changes; rev = 6 spversioning5.checkout(["epanet_trunk_rev_head.junctions", "epanet_trunk_rev_head.pipes"]) scon = dbapi2.connect(sqlite_test_filename5) scur = scon.cursor() scon.enable_load_extension(True) scon.execute("SELECT load_extension('mod_spatialite')") scur.execute("UPDATE junctions_view SET elevation = '22' WHERE id = '1'") scur.execute("DELETE FROM junctions_view WHERE id = '1'") scon.commit() #scur.execute("SELECT COUNT(*) FROM junctions") #assert( scur.fetchone()[0] == 3 ) scon.close() spversioning5.commit('update and delete commit')
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.sql") versioning.historize( "dbname=epanet_test_db host={} user={}".format(host, pguser), "epanet") # checkout 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.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'") 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") # try the update wc = [os.path.join(tmp_dir, "issue357_wc0.sqlite"), os.path.join(tmp_dir, "issue357_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="******"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'") 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") 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.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 versioning_id = 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 versioning_id = 3" ) pcur.commit() pgversioning.commit("rev 7") pcur.execute( "UPDATE epanet_working_copy.pipes_view SET length = 4 WHERE versioning_id = 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 versioning_id = 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 versioning_id") 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 versioning_id FROM epanet.pipes ORDER BY versioning_id") assert ([i[0] for i in pcur.fetchall()] == [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 versioning_id FROM epanet_archive.pipes ORDER BY versioning_id" ) assert ([i[0] for i in pcur.fetchall()] == [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 versioning_id FROM epanet.pipes ORDER BY versioning_id") assert ([i[0] for i in pcur.fetchall()] == [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 versioning_id FROM epanet_archive.pipes ORDER BY versioning_id" ) assert ([i[0] for i in pcur.fetchall()] == [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 versioning_id") endv = pcur.fetchall() assert (end == endv) pcur.close()
def test(host, pguser): pg_conn_info = "dbname=epanet_test_db host=" + host + " user="******"/abbreviation_test.sqlite" if os.path.isfile(sqlite_test_filename): os.remove(sqlite_test_filename) spversioning = versioning.spatialite(sqlite_test_filename, pg_conn_info) # create the test database os.system("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_unversioned.sql") pcon = psycopg2.connect(pg_conn_info) pcur = pcon.cursor() for i in range(10): pcur.execute(""" INSERT INTO epanet.junctions (id, elevation, geom) VALUES ('{id}', {elev}, ST_GeometryFromText('POINT({x} {y})',2154)); """.format( id=str(i + 2) + '_this_is_a_very_long_name_that should_be_trunctated_if_buggy', elev=float(i), x=float(i + 1), y=float(i + 1))) pcon.commit() versioning.historize(pg_conn_info, 'epanet') spversioning.checkout( ["epanet_trunk_rev_head.junctions", "epanet_trunk_rev_head.pipes"]) assert (os.path.isfile(sqlite_test_filename) and "sqlite file must exist at this point") scon = dbapi2.connect(sqlite_test_filename) scur = scon.cursor() scur.execute("SELECT * from junctions") for rec in scur: if rec[0] > 2: assert rec[1].find( '_this_is_a_very_long_name_that should_be_trunctated_if_buggy' ) != -1 scur.execute( "update junctions_view set id='this_is_another_edited_very_long_name_that should_be_trunctated_if_buggy' where ogc_fid > 8" ) scur.execute( "insert into junctions_view(id, elevation, geometry) select 'newly inserted with long name', elevation, geometry from junctions_view where ogc_fid=4" ) scon.commit() spversioning.commit('a commit msg') pcur.execute("select jid, id from epanet_trunk_rev_head.junctions") for row in pcur: print row if row[0] > 8: assert row[1].find('this_is_another_edited_very_long_name_that should_be_trunctated_if_buggy') != -1\ or row[1].find('newly inserted with long name') != -1
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") # 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") 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.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 versioning_id = 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 versioning_id = 3") pcur.commit() pgversioning.commit("rev 7") pcur.execute("UPDATE epanet_working_copy.pipes_view SET length = 4 WHERE versioning_id = 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 versioning_id = 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 versioning_id") 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 versioning_id FROM epanet.pipes ORDER BY versioning_id") assert([i[0] for i in pcur.fetchall()] == [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 versioning_id FROM epanet_archive.pipes ORDER BY versioning_id") assert([i[0] for i in pcur.fetchall()] == [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 versioning_id FROM epanet.pipes ORDER BY versioning_id") assert([i[0] for i in pcur.fetchall()] == [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 versioning_id FROM epanet_archive.pipes ORDER BY versioning_id") assert([i[0] for i in pcur.fetchall()] == [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 versioning_id") endv = pcur.fetchall() assert(end==endv) 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") 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()