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 -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 -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="******"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 = 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="******"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 __init__(self, host, pguser):
super().__init__(host, pguser, "main")
if os.path.isfile(sqlite_test_filename):
os.remove(sqlite_test_filename)
self.versioning = versioning.spatialite(sqlite_test_filename,
self.pg_conn_info)
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 __init__(self, host, pguser, additional_sql=None):
super().__init__(host, pguser, "main", additional_sql)
if os.path.isfile(sqlite_test_filename):
os.remove(sqlite_test_filename)
self.versioning = versioning.spatialite(sqlite_test_filename,
self.pg_conn_info)
self.versioning.checkout(["myschema_trunk_rev_head.referencing",
"myschema_trunk_rev_head.referenced"])
self.con = dbapi2.connect(sqlite_test_filename)
self.con.enable_load_extension(True)
self.con.execute("SELECT load_extension('mod_spatialite')")
self.cur = self.con.cursor()
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+"/issue287_pg_dump.sql")
# try the update
sqlite_test_filename = os.path.join(tmp_dir, "issue287_wc.sqlite")
shutil.copyfile(os.path.join(test_data_dir, "issue287_wc.sqlite"), sqlite_test_filename)
spversioning = versioning.spatialite(sqlite_test_filename, pg_conn_info)
spversioning.update()
spversioning.commit("test message")
def __init__(self, host, pguser, additional_sql=None):
super().__init__(host, pguser, "main", additional_sql)
if os.path.isfile(sqlite_test_filename):
os.remove(sqlite_test_filename)
self.versioning = versioning.spatialite(sqlite_test_filename,
self.pg_conn_info)
self.versioning.checkout([
"myschema_trunk_rev_head.referencing",
"myschema_trunk_rev_head.referenced"
])
self.con = dbapi2.connect(sqlite_test_filename)
self.con.enable_load_extension(True)
self.con.execute("SELECT load_extension('mod_spatialite')")
self.cur = self.con.cursor()
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="******"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 -c 'CREATE EXTENSION postgis'")
os.system("psql -h " + host + " -U "+pguser+" epanet_test_db -f "+test_data_dir+"/epanet_test_db.sql")
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)
scur = scon.cursor()
scur.execute("UPDATE junctions_view SET elevation = '8' WHERE id = '1'")
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"])
scon = dbapi2.connect(sqlite_test_filename2)
scur = scon.cursor()
scur.execute("UPDATE junctions_view SET elevation = '22' WHERE id = '1'")
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)
scur = scon.cursor()
scur.execute("INSERT INTO junctions_view(id, elevation, GEOMETRY) 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"])
scon = dbapi2.connect(sqlite_test_filename4)
scur = scon.cursor()
scur.execute("DELETE FROM junctions_view WHERE id = 0")
scon.commit()
#scur.execute("SELECT COUNT(*) FROM junctions")
#assert( scur.fetchone()[0] == 3 )
scon.close()
spversioning4.commit('delete id=0 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] == 'u')
assert(res[1][0] == 'i')
#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] == 'u')
assert(res[1][0] == 'i')
assert(res[2][0] == 'a')
assert(res[3][0] == 'i') # 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] == 'u')
assert(res[1][0] == 'i')
assert(res[2][0] == 'a')
assert(res[3][0] == 'd')
#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"])
scon = dbapi2.connect(sqlite_test_filename5)
scur = scon.cursor()
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="******"/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="******"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")
# 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="******"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="******"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="******"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")
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="******"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="******"/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
