def migrate_down(db):
for f in glob.glob("schema/*_down.sql"):
res = psql(db, "SELECT 'ok' FROM schema WHERE version = '%s'" % f.replace('_down','_up'))
if res['returncode'] == 0 and res['stdout'].find('ok') > -1:
print '> migate down %s' % f
res = psql(db, slurp(f))
if res['returncode'] == 0:
print res['stdout']
psql(db, "DELETE FROM schema WHERE version = '%s'" % f.replace('_down', '_up'))
def load_to_pg(db, fl, content, force=False):
s = sha.new(content).hexdigest()
if force or should_reload(db, fl, s):
print "\t<- %s" % fl
sql = prepr.process(fl, content)
res = psql(db, sql)
if "VERBOSE" in os.environ:
print res["stdout"]
if res["returncode"] == 0:
psql(db, "DELETE FROM modules WHERE file='%s'" % fl)
psql(db, "INSERT INTO modules (file,digest) VALUES ('%s','%s')" % (fl, s))
if res["stderr"] and res["returncode"] != 0:
raise Exception(res["stderr"])
def migrate_down(db):
for f in glob.glob("schema/*_down.sql"):
res = psql(
db, "SELECT 'ok' FROM schema WHERE version = '%s'" %
f.replace('_down', '_up'))
if res['returncode'] == 0 and res['stdout'].find('ok') > -1:
print '> migate down %s' % f
res = psql(db, slurp(f))
if res['returncode'] == 0:
print res['stdout']
psql(
db, "DELETE FROM schema WHERE version = '%s'" %
f.replace('_down', '_up'))
def load_to_pg(db, fl, content, force=False):
s = sha.new(content).hexdigest()
if force or should_reload(db, fl, s):
print '\t<- %s' % fl
sql = prepr.process(fl, content)
res = psql(db, sql)
if 'VERBOSE' in os.environ:
print res['stdout']
if res['returncode'] == 0:
psql(db, 'DELETE FROM modules WHERE file=\'%s\'' % fl)
psql(
db,
'INSERT INTO modules (file,digest) VALUES (\'%s\',\'%s\')' %
(fl, s))
if res['stderr'] and res['returncode'] != 0:
raise Exception(res['stderr'])
def migrate(db):
psql(db, 'create table if not exists schema (version text primary key)')
for f in sorted(glob.glob("schema/*_up.sql")):
res = psql(db, "SELECT 'ok' FROM schema WHERE version = '%s'" % f)
if res['returncode'] == 0 and res['stdout'].find('ok') < 0:
print '> migate %s' % f
mod = os.path.basename(f)
sql = ql.prepr.process('fhirbase_migration_' + mod,slurp(f))
res = psql(db, sql)
if res['returncode'] == 0:
print res['stdout']
psql(db, "INSERT INTO schema (version) VALUES ('%s')" % f)
def migrate(db):
psql(db, 'create table if not exists schema (version text primary key)')
for f in sorted(glob.glob("schema/*_up.sql")):
res = psql(db, "SELECT 'ok' FROM schema WHERE version = '%s'" % f)
if res['returncode'] == 0 and res['stdout'].find('ok') < 0:
print '> migate %s' % f
mod = os.path.basename(f)
sql = ql.prepr.process('fhirbase_migration_' + mod, slurp(f))
res = psql(db, sql)
if res['returncode'] == 0:
print res['stdout']
psql(db, "INSERT INTO schema (version) VALUES ('%s')" % f)
def should_reload(db, fl, digest):
res = psql(db, "SELECT digest FROM modules WHERE file='%s'" % fl)
if is_test_file(fl):
return True
return not res["stdout"] or res["stdout"].find(digest) == -1
return True
def is_changed(fl, content):
print "Digest " + s.hexdigest()
psql("SELECT digest FROM modules WHERE file='%s'" % fl)
def should_reload(db, fl, digest):
res = psql(db, 'SELECT digest FROM modules WHERE file=\'%s\'' % fl)
if is_test_file(fl): return True
return not res['stdout'] or res['stdout'].find(digest) == -1
return True
def is_changed(fl, content):
print 'Digest ' + s.hexdigest()
psql('SELECT digest FROM modules WHERE file=\'%s\'' % fl)
def perf(db, args):
perf_files = glob.glob('./perf/*.sql')
ql.reload_files(db, perf_files)
benchmarks = [
["disk usage right after generation of seed data",
"SELECT admin.admin_disk_usage_top(10)"],
["fhir.create called just one time",
"SELECT performance.create_patients(1)"],
["fhir.create called 1000 times in batch",
"SELECT performance.create_patients(1000)"],
["fhir.read called just one time",
"SELECT performance.read_patients(1)"],
["fhir.read called 1000 times in batch",
"SELECT performance.read_patients(1000)"],
["Updating single patient with fhir.update()",
"SELECT performance.create_temporary_patients(1000);\nSELECT performance.update_patients(1)"],
["fhir.delete called one time",
"SELECT performance.delete_patients(1)"],
["fhir.delete called 1000 times in batch",
"SELECT performance.delete_patients(1000)"],
["searching for non-existent name without index",
"SELECT count(*) FROM fhir.search('Patient', 'name=nonexistentname')"],
["building Patient.name index",
"SELECT performance.index_search_param('Patient','name')"],
["building Patient.gender index",
"SELECT performance.index_search_param('Patient','gender')"],
["building Patient.address index",
"SELECT performance.index_search_param('Patient','address')"],
["building Patient.telecom index",
"SELECT performance.index_search_param('Patient','telecom')"],
["building Participant.name index",
"SELECT performance.index_search_param('Participant','name')"],
["building Organization.name index",
"SELECT performance.index_search_param('Organization','name')"],
["building Encounter.status index",
"SELECT performance.index_search_param('Encounter','status')"],
["building Encounter.patient index",
"SELECT performance.index_search_param('Encounter','patient')"],
["building Encounter.participant index",
"SELECT performance.index_search_param('Encounter','participant')"],
["building Encounter.practitioner index",
"SELECT performance.index_search_param('Encounter','practitioner')"],
["building Patient.organization index",
"SELECT performance.index_search_param('Patient','organization')"],
["running VACUUM ANALYZE on patient table",
"VACUUM ANALYZE patient"],
["running VACUUM ANALYZE on encounter table",
"VACUUM ANALYZE encounter"],
["running VACUUM ANALYZE on organization table",
"VACUUM ANALYZE organization"],
["running VACUUM ANALYZE on practitioner table",
"VACUUM ANALYZE practitioner"],
["searching for patient with unique name",
"SELECT performance.search_patient_with_only_one_search_candidate()"],
["searching for all Johns in database",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&_count=50000000')"],
["searching Patient with name=John&gender=female&_count=100 (should have no matches at all)",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&gender=female&_count=100')"],
["searching Patient with name=John&gender=male&_count=100",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&gender=male&_count=100')"],
["searching Patient with name=John&gender=male&active=true&address=YALUMBA&_count=100",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&gender=male&active=true&address=YALUMBA&_count=100')"],
["searching Patient with name=John&gender=male&_count=100&_sort=name",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&gender=male&_count=100&_sort=name')"],
["searching Patient with name=John&gender=male&_count=100&_sort=active",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&gender=male&_count=100&_sort=active')"],
["searching Encounter with patient:Patient.name=John&_count=100&status=finished&practitioner:Practitioner.name=Alex",
"SELECT count(*) FROM fhir.search('Encounter', 'patient:Patient.name=John&_count=100&status=finished&practitioner:Practitioner.name=Alex')"],
["searching Encounter with patient:Patient.name=John&_count=100&patient:Patient.organization:Organization.name=Mollis",
"SELECT count(*) FROM fhir.search('Encounter', 'patient:Patient.name=John&_count=100&patient:Patient.organization:Organization.name=Mollis')"]
]
results = []
for test in benchmarks:
desc = test[0]
sql = test[1]
print desc
r = psql(db, "\\timing\n%s;" % sql)
print r["stdout"]
m = re.search("Time: ([0-9.,]+) ms", r["stdout"])
time = m.groups()[0]
results.append([desc, time])
print "-" * 50
maxlength = max([len(x[0]) for x in results]) + 5
print "\nRESULTS:"
for r in results:
print "{0: <{maxlength}} {1: >10} ms".format(r[0], r[1], maxlength=maxlength)
def perf(db, args):
perf_files = glob.glob('./perf/*.sql')
ql.reload_files(db, perf_files)
benchmarks = [
[
"disk usage right after generation of seed data",
"SELECT admin.admin_disk_usage_top(10)"
],
[
"fhir.create called just one time",
"SELECT performance.create_patients(1)"
],
[
"fhir.create called 1000 times in batch",
"SELECT performance.create_patients(1000)"
],
[
"fhir.read called just one time",
"SELECT performance.read_patients(1)"
],
[
"fhir.read called 1000 times in batch",
"SELECT performance.read_patients(1000)"
],
[
"Updating single patient with fhir.update()",
"SELECT performance.create_temporary_patients(1000);\nSELECT performance.update_patients(1)"
],
[
"fhir.delete called one time",
"SELECT performance.delete_patients(1)"
],
[
"fhir.delete called 1000 times in batch",
"SELECT performance.delete_patients(1000)"
],
[
"searching for non-existent name without index",
"SELECT count(*) FROM fhir.search('Patient', 'name=nonexistentname')"
],
[
"building Patient.name index",
"SELECT performance.index_search_param('Patient','name')"
],
[
"building Patient.gender index",
"SELECT performance.index_search_param('Patient','gender')"
],
[
"building Patient.address index",
"SELECT performance.index_search_param('Patient','address')"
],
[
"building Patient.telecom index",
"SELECT performance.index_search_param('Patient','telecom')"
],
[
"building Participant.name index",
"SELECT performance.index_search_param('Participant','name')"
],
[
"building Organization.name index",
"SELECT performance.index_search_param('Organization','name')"
],
[
"building Encounter.status index",
"SELECT performance.index_search_param('Encounter','status')"
],
[
"building Encounter.patient index",
"SELECT performance.index_search_param('Encounter','patient')"
],
[
"building Encounter.participant index",
"SELECT performance.index_search_param('Encounter','participant')"
],
[
"building Encounter.practitioner index",
"SELECT performance.index_search_param('Encounter','practitioner')"
],
[
"building Patient.organization index",
"SELECT performance.index_search_param('Patient','organization')"
],
["running VACUUM ANALYZE on patient table", "VACUUM ANALYZE patient"],
[
"running VACUUM ANALYZE on encounter table",
"VACUUM ANALYZE encounter"
],
[
"running VACUUM ANALYZE on organization table",
"VACUUM ANALYZE organization"
],
[
"running VACUUM ANALYZE on practitioner table",
"VACUUM ANALYZE practitioner"
],
[
"searching for patient with unique name",
"SELECT performance.search_patient_with_only_one_search_candidate()"
],
[
"searching for all Johns in database",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&_count=50000000')"
],
[
"searching Patient with name=John&gender=female&_count=100 (should have no matches at all)",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&gender=female&_count=100')"
],
[
"searching Patient with name=John&gender=male&_count=100",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&gender=male&_count=100')"
],
[
"searching Patient with name=John&gender=male&active=true&address=YALUMBA&_count=100",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&gender=male&active=true&address=YALUMBA&_count=100')"
],
[
"searching Patient with name=John&gender=male&_count=100&_sort=name",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&gender=male&_count=100&_sort=name')"
],
[
"searching Patient with name=John&gender=male&_count=100&_sort=active",
"SELECT count(*) FROM fhir.search('Patient', 'name=John&gender=male&_count=100&_sort=active')"
],
[
"searching Encounter with patient:Patient.name=John&_count=100&status=finished&practitioner:Practitioner.name=Alex",
"SELECT count(*) FROM fhir.search('Encounter', 'patient:Patient.name=John&_count=100&status=finished&practitioner:Practitioner.name=Alex')"
],
[
"searching Encounter with patient:Patient.name=John&_count=100&patient:Patient.organization:Organization.name=Mollis",
"SELECT count(*) FROM fhir.search('Encounter', 'patient:Patient.name=John&_count=100&patient:Patient.organization:Organization.name=Mollis')"
]
]
results = []
for test in benchmarks:
desc = test[0]
sql = test[1]
print desc
r = psql(db, "\\timing\n%s;" % sql)
print r["stdout"]
m = re.search("Time: ([0-9.,]+) ms", r["stdout"])
time = m.groups()[0]
results.append([desc, time])
print "-" * 50
maxlength = max([len(x[0]) for x in results]) + 5
print "\nRESULTS:"
for r in results:
print "{0: <{maxlength}} {1: >10} ms".format(r[0],
r[1],
maxlength=maxlength)
