def GetTables(host, database, user, password, schema='public'):
connection_string = PG_CONN_STR.format(hostName=host,
dbName=database,
userName=user,
password=password)
engine = create_engine(connection_string, client_encoding='utf8')
conn = engine.connect()
metadata = MetaData(bind=engine, schema=schema)
metadata.reflect(bind=engine)
tables = []
for table in metadata.sorted_tables:
# create table
sql = str(CreateTable(table, bind=engine)).strip() + ';\n'
# comment on table
comment = conn.execute(
text(SQL_TABLE_COMMENT.format(TableName=table.name))).fetchall()
sql += (SQL_COMMENT_ON_TABLE.format(
Schema=table.schema, TableName=table.name, Comment=comment[0][2]) +
';\n') if comment[0][2] else ''
# create indexes
sorted_indexes = sorted(table.indexes, key=lambda ind: ind.name)
for index in sorted_indexes:
sql += str(CreateIndex(index, bind=engine)) + ';\n'
# create triggers
triggers = conn.execute(
text(SQL_TABLE_TRIGGERS.format(TableName=table.name))).fetchall()
for trigger in triggers:
sql += trigger[2] + ';\n'
tables.append((table.schema, table.name, sql))
return tables
def GetTriggerFunctions(host, database, user, password):
try:
connection_string = PG_CONN_STR.format(hostName=host, dbName=database, userName=user, password=password )
conn = create_engine(connection_string, client_encoding='utf8').engine.connect()
functions = conn.execute(text(SQL_TRIGGER_FUNCTIONS)).fetchall()
return functions
except Exception:
raise
finally:
conn.close()
def GetViews(host, database, user, password):
try:
connection_string = PG_CONN_STR.format(hostName=host, dbName=database, userName=user, password=password )
conn = create_engine(connection_string, client_encoding='utf8').engine.connect()
views = conn.execute(text(SQL_VIEWS)).fetchall()
return views
except Exception:
raise
finally:
conn.close()
def GetTriggerFunctions(host, database, user, password):
try:
connection_string = PG_CONN_STR.format(hostName=host,
dbName=database,
userName=user,
password=password)
conn = create_engine(connection_string,
client_encoding='utf8').engine.connect()
functions = conn.execute(text(SQL_TRIGGER_FUNCTIONS)).fetchall()
return functions
except Exception:
raise
finally:
conn.close()
def GetTables(host, database, user, password, schema='public'):
connection_string = PG_CONN_STR.format(hostName=host, dbName=database, userName=user, password=password )
engine = create_engine(connection_string, client_encoding='utf8')
conn = engine.connect()
metadata = MetaData(bind=engine, schema=schema)
metadata.reflect(bind=engine)
tables = []
for table in metadata.sorted_tables:
# create table
sql = str(CreateTable(table, bind=engine)).strip() + ';\n'
# comment on table
comment = conn.execute(text(SQL_TABLE_COMMENT.format(TableName=table.name))).fetchall()
sql += (SQL_COMMENT_ON_TABLE.format(Schema=table.schema,TableName=table.name,Comment=comment[0][2]) + ';\n') if comment[0][2] else ''
# create indexes
sorted_indexes = sorted(table.indexes, key=lambda ind: ind.name)
for index in sorted_indexes:
sql += str(CreateIndex(index, bind=engine)) + ';\n'
# create triggers
triggers = conn.execute(text(SQL_TABLE_TRIGGERS.format(TableName=table.name))).fetchall()
for trigger in triggers:
sql += trigger[2] + ';\n'
tables.append((table.schema, table.name,sql))
return tables
def GetTypes(host, database, user, password):
def GetType(row):
TYPE_COMPOSITE = "c"
TYPE_ENUM = "e"
TYPE_RANGE = "r"
#TYPE_EXTERNAL=''
ALIGNS = {'c': 'char', 's': 'int2', 'i': 'int4', 'd': 'double'}
STORAGES = {
'p': 'PLAIN',
'e': 'EXTERNAL',
'm': 'MAIN',
'x': 'EXTENDED'
}
typtype = row['typtype']
typinput = row['typinput']
typoutput = row['typoutput']
typreceive = row['typreceive']
typsend = row['typsend']
typmodin = row['typmodin']
typmodout = row['typmodout']
typanalyze = row['typanalyze']
typispreferred = row['typispreferred']
typbyval = row['typbyval']
typdefault = row['typdefault']
element = row['element']
typdelim = row['typdelim']
typlen = row['typlen']
typcategory = row['typcategory']
typalign = ALIGNS[
row['typalign']] if row['typalign'] in ALIGNS else 'unknown'
typstorage = STORAGES[
row['typstorage']] if row['typstorage'] in ALIGNS else 'unknown'
schemaname = row['schemaname']
typeName = row['alias']
isCollatable = row['typcollation'] == 100
attrs = row['attrs']
sql = 'CREATE TYPE {Schema}."{TypeName}"'.format(Schema=schemaname,
TypeName=typeName)
if typtype == TYPE_COMPOSITE:
sql += " AS\n (" + attrs
elif typtype == TYPE_ENUM:
raise Exception('TYPE_ENUM not implemented')
# sql +=" AS ENUM\n ("
elif typtype == TYPE_RANGE:
raise Exception('TYPE_RANGE not implemented')
# sql += " AS RANGE\n (SUBTYPE=" + rngsubtypestr
else:
sql += "\n (INPUT=" + typinput + ",\n OUTPUT=" + typoutput
if typreceive:
sql += ",\n RECEIVE=" + typreceive
if typsend:
sql += ",\n SEND=" + typsend
if typmodin:
sql += ",\n TYPMOD_IN=" + typmodin
if typmodout:
sql += ",\n TYPMOD_OUT=" + typmodout
if typanalyze:
sql += ",\n ANALYZE=" + typanalyze
sql += ",\n CATEGORY=" + typcategory
if typispreferred:
sql += ",\n PREFERRED=true"
if typbyval:
sql += ",\n PASSEDBYVALUE"
if typdefault:
sql += ", DEFAULT=" + typdefault
if element:
sql += ",\n ELEMENT=" + element + ", DELIMITER='" + typdelim + "'"
sql += ",\n INTERNALLENGTH=" + str(
typlen) + ", ALIGNMENT=" + typalign + ", STORAGE=" + typstorage
if isCollatable:
sql += ",\n COLLATABLE=true"
sql += ");\n"
return (schemaname, typeName.replace('"', ''), sql)
try:
connection_string = PG_CONN_STR.format(hostName=host,
dbName=database,
userName=user,
password=password)
conn = create_engine(connection_string,
client_encoding='utf8').engine.connect()
rows = conn.execute(text(SQL_TYPES)).fetchall()
except Exception:
raise
finally:
conn.close()
types = []
for row in rows:
types.append(GetType(row))
return types
def GetTypes(host, database, user, password):
def GetType(row):
TYPE_COMPOSITE="c"
TYPE_ENUM="e"
TYPE_RANGE="r"
#TYPE_EXTERNAL=''
ALIGNS = {'c': 'char','s': 'int2','i': 'int4','d': 'double'}
STORAGES = {'p': 'PLAIN','e': 'EXTERNAL','m': 'MAIN','x': 'EXTENDED'}
typtype = row['typtype']
typinput = row['typinput']
typoutput = row['typoutput']
typreceive = row['typreceive']
typsend = row['typsend']
typmodin = row['typmodin']
typmodout = row['typmodout']
typanalyze = row['typanalyze']
typispreferred = row['typispreferred']
typbyval = row['typbyval']
typdefault = row['typdefault']
element = row['element']
typdelim = row['typdelim']
typlen = row['typlen']
typcategory = row['typcategory']
typalign = ALIGNS[row['typalign']] if row['typalign'] in ALIGNS else 'unknown'
typstorage = STORAGES[row['typstorage']] if row['typstorage'] in ALIGNS else 'unknown'
schemaname = row['schemaname']
typeName = row['alias']
isCollatable = row['typcollation'] == 100
attrs = row['attrs']
sql = 'CREATE TYPE {Schema}."{TypeName}"'.format(Schema=schemaname, TypeName=typeName)
if typtype == TYPE_COMPOSITE:
sql += " AS\n (" + attrs
elif typtype == TYPE_ENUM:
raise Exception('TYPE_ENUM not implemented')
# sql +=" AS ENUM\n ("
elif typtype == TYPE_RANGE:
raise Exception('TYPE_RANGE not implemented')
# sql += " AS RANGE\n (SUBTYPE=" + rngsubtypestr
else:
sql += "\n (INPUT=" + typinput + ",\n OUTPUT=" + typoutput
if typreceive:
sql += ",\n RECEIVE=" + typreceive
if typsend:
sql += ",\n SEND=" + typsend
if typmodin:
sql += ",\n TYPMOD_IN=" + typmodin
if typmodout:
sql += ",\n TYPMOD_OUT=" + typmodout
if typanalyze:
sql += ",\n ANALYZE=" + typanalyze
sql += ",\n CATEGORY=" + typcategory
if typispreferred:
sql += ",\n PREFERRED=true"
if typbyval:
sql += ",\n PASSEDBYVALUE"
if typdefault:
sql += ", DEFAULT=" + typdefault
if element:
sql += ",\n ELEMENT=" + element + ", DELIMITER='" + typdelim + "'"
sql += ",\n INTERNALLENGTH=" + str(typlen) + ", ALIGNMENT=" + typalign + ", STORAGE=" + typstorage
if isCollatable:
sql += ",\n COLLATABLE=true"
sql +=");\n"
return (schemaname, typeName.replace('"','') ,sql)
try:
connection_string = PG_CONN_STR.format(hostName=host, dbName=database, userName=user, password=password )
conn = create_engine(connection_string, client_encoding='utf8').engine.connect()
rows = conn.execute(text(SQL_TYPES)).fetchall()
except Exception:
raise
finally:
conn.close()
types = []
for row in rows:
types.append(GetType(row))
return types
