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()
