def is_superuser(self):
"Is current user a superuser?"
curs = pgexecute(self.conn, "SELECT 1 FROM pg_roles WHERE rolsuper "
"AND rolname = CURRENT_USER ")
row = curs.fetchone()
curs.close()
return row and True
def is_plpgsql_installed(self):
"Is PL/pgSQL installed?"
curs = pgexecute(self.conn,
"SELECT 1 FROM pg_language WHERE lanname = 'plpgsql'")
row = curs.fetchone()
curs.close()
return row and True
def is_plpgsql_installed(self):
"Is PL/pgSQL installed?"
curs = pgexecute(
self.conn, "SELECT 1 FROM pg_language WHERE lanname = 'plpgsql'")
row = curs.fetchone()
curs.close()
return row and True
def is_superuser(self):
"Is current user a superuser?"
curs = pgexecute(self.conn, "SELECT 1 FROM pg_roles WHERE rolsuper "
"AND rolname = CURRENT_USER ")
row = curs.fetchone()
curs.close()
return row and True
def clear(self):
"Drop tables and other objects"
STD_DROP = 'DROP %s IF EXISTS "%s" CASCADE'
# Schemas other than 'public'
curs = pgexecute(
self.conn,
"""SELECT nspname FROM pg_namespace
WHERE nspname NOT IN ('public', 'information_schema')
AND substring(nspname for 3) != 'pg_'
ORDER BY nspname""")
objs = curs.fetchall()
curs.close()
self.conn.rollback()
for obj in objs:
self.execute(STD_DROP % ('SCHEMA', obj[0]))
self.conn.commit()
# Extensions
if self.version >= 90100:
curs = pgexecute(
self.conn,
"""SELECT extname FROM pg_extension
JOIN pg_namespace n ON (extnamespace = n.oid)
WHERE nspname NOT IN ('information_schema')
AND extname != 'plpgsql'""")
exts = curs.fetchall()
curs.close()
self.conn.rollback()
for ext in exts:
self.execute(STD_DROP % ('EXTENSION', ext[0]))
self.conn.commit()
# Tables, sequences and views
curs = pgexecute(
self.conn,
"""SELECT relname, relkind FROM pg_class
JOIN pg_namespace ON (relnamespace = pg_namespace.oid)
WHERE relkind in ('r', 'S', 'v', 'f', 'm')
AND nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY relkind DESC""")
objs = curs.fetchall()
curs.close()
self.conn.rollback()
for obj in objs:
if obj['relkind'] == 'r':
objtype = 'TABLE'
elif obj['relkind'] == 'S':
objtype = 'SEQUENCE'
elif obj['relkind'] == 'v':
objtype = 'VIEW'
elif obj['relkind'] == 'f':
objtype = 'FOREIGN TABLE'
elif obj['relkind'] == 'm':
objtype = 'MATERIALIZED VIEW'
self.execute(STD_DROP % (objtype, obj[0]))
self.conn.commit()
# Types (base, composite and enums) and domains
#
# TYPEs have to be done before FUNCTIONs because base types depend
# on functions, and we're using CASCADE. Also, exclude base array
# types because they depend on the scalar types.
curs = pgexecute(
self.conn,
"""SELECT typname, typtype FROM pg_type t
JOIN pg_namespace n ON (typnamespace = n.oid)
WHERE typtype IN ('b', 'c', 'd', 'e')
AND NOT (typtype = 'b' AND typarray = 0)
AND nspname NOT IN ('pg_catalog', 'pg_toast',
'information_schema')""")
types = curs.fetchall()
curs.close()
self.conn.rollback()
for typ in types:
self.execute(STD_DROP % ('DOMAIN' if typ['typtype'] == 'd'
else 'TYPE', typ[0]))
self.conn.commit()
# Functions
curs = pgexecute(
self.conn,
"""SELECT proisagg, p.oid::regprocedure AS proc
FROM pg_proc p JOIN pg_namespace n ON (pronamespace = n.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2""")
funcs = curs.fetchall()
curs.close()
self.conn.rollback()
for func in funcs:
self.execute('DROP %s IF EXISTS %s CASCADE' % (
'AGGREGATE' if func['proisagg'] else 'FUNCTION', func['proc']))
self.conn.commit()
# Languages
if self.version < 90000:
if self.is_plpgsql_installed():
self.execute_commit("DROP LANGUAGE plpgsql")
# Operators
curs = pgexecute(
self.conn,
"""SELECT o.oid::regoperator
FROM pg_operator o JOIN pg_namespace n ON (oprnamespace = n.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')""")
opers = curs.fetchall()
curs.close()
self.conn.rollback()
for oper in opers:
self.execute("DROP OPERATOR IF EXISTS %s CASCADE" % (oper[0]))
self.conn.commit()
# Operator families
curs = pgexecute(
self.conn,
"""SELECT opfname, amname
FROM pg_opfamily o JOIN pg_am a ON (opfmethod = a.oid)
JOIN pg_namespace n ON (opfnamespace = n.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')""")
opfams = curs.fetchall()
curs.close()
self.conn.rollback()
for opfam in opfams:
self.execute(
'DROP OPERATOR FAMILY IF EXISTS "%s" USING "%s" CASCADE' % (
opfam[0], opfam[1]))
self.conn.commit()
# Operator classes
curs = pgexecute(
self.conn,
"""SELECT opcname, amname
FROM pg_opclass o JOIN pg_am a ON (opcmethod = a.oid)
JOIN pg_namespace n ON (opcnamespace = n.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')""")
opcls = curs.fetchall()
curs.close()
self.conn.rollback()
for opcl in opcls:
self.execute(
'DROP OPERATOR CLASS IF EXISTS "%s" USING "%s" CASCADE' % (
opcl[0], opcl[1]))
self.conn.commit()
# Conversions
curs = pgexecute(
self.conn,
"""SELECT conname FROM pg_conversion c
JOIN pg_namespace n ON (connamespace = n.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')""")
convs = curs.fetchall()
curs.close()
self.conn.rollback()
for cnv in convs:
self.execute(STD_DROP % ('CONVERSION', cnv[0]))
self.conn.commit()
# Collations
if self.version >= 90100:
curs = pgexecute(
self.conn,
"""SELECT collname FROM pg_collation c
JOIN pg_namespace n ON (collnamespace = n.oid)
WHERE nspname NOT IN (
'pg_catalog', 'information_schema')""")
colls = curs.fetchall()
curs.close()
self.conn.rollback()
for coll in colls:
self.execute(STD_DROP % ('COLLATION', coll[0]))
self.conn.commit()
# User mappings
curs = pgexecute(
self.conn,
"""SELECT CASE umuser WHEN 0 THEN 'PUBLIC' ELSE
pg_get_userbyid(umuser) END AS username, s.srvname
FROM pg_user_mappings u
JOIN pg_foreign_server s ON (srvid = s.oid)""")
umaps = curs.fetchall()
curs.close()
self.conn.rollback()
for ump in umaps:
self.execute('DROP USER MAPPING IF EXISTS FOR "%s" SERVER "%s"' % (
ump[0], ump[1]))
self.conn.commit()
# Servers
curs = pgexecute(self.conn, "SELECT srvname FROM pg_foreign_server")
servs = curs.fetchall()
curs.close()
self.conn.rollback()
for srv in servs:
self.execute(STD_DROP % ('SERVER', srv[0]))
self.conn.commit()
# Foreign data wrappers
curs = pgexecute(self.conn,
"SELECT fdwname FROM pg_foreign_data_wrapper")
fdws = curs.fetchall()
curs.close()
self.conn.rollback()
for fdw in fdws:
self.execute(STD_DROP % ('FOREIGN DATA WRAPPER', fdw[0]))
self.conn.commit()
def clear(self):
"Drop tables and other objects"
STD_DROP = 'DROP %s IF EXISTS "%s" CASCADE'
# Schemas other than 'public'
curs = pgexecute(
self.conn, """SELECT nspname FROM pg_namespace
WHERE nspname NOT IN ('public', 'information_schema')
AND substring(nspname for 3) != 'pg_'
ORDER BY nspname""")
objs = curs.fetchall()
curs.close()
self.conn.rollback()
for obj in objs:
self.execute(STD_DROP % ('SCHEMA', obj[0]))
self.conn.commit()
# Extensions
if self.version >= 90100:
curs = pgexecute(
self.conn, """SELECT extname FROM pg_extension
JOIN pg_namespace n ON (extnamespace = n.oid)
WHERE nspname NOT IN ('information_schema')
AND extname != 'plpgsql'""")
exts = curs.fetchall()
curs.close()
self.conn.rollback()
for ext in exts:
self.execute(STD_DROP % ('EXTENSION', ext[0]))
self.conn.commit()
# Tables, sequences and views
curs = pgexecute(
self.conn, """SELECT relname, relkind FROM pg_class
JOIN pg_namespace ON (relnamespace = pg_namespace.oid)
WHERE relkind in ('r', 'p', 'S', 'v', 'f', 'm')
AND nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY relkind DESC""")
objs = curs.fetchall()
curs.close()
self.conn.rollback()
for obj in objs:
if obj['relkind'] == 'r':
objtype = 'TABLE'
elif obj['relkind'] == 'S':
objtype = 'SEQUENCE'
elif obj['relkind'] == 'v':
objtype = 'VIEW'
elif obj['relkind'] == 'f':
objtype = 'FOREIGN TABLE'
elif obj['relkind'] == 'm':
objtype = 'MATERIALIZED VIEW'
self.execute(STD_DROP % (objtype, obj[0]))
self.conn.commit()
# Types (base, composite and enums) and domains
#
# TYPEs have to be done before FUNCTIONs because base types depend
# on functions, and we're using CASCADE. Also, exclude base array
# types because they depend on the scalar types.
curs = pgexecute(
self.conn, """SELECT typname, typtype FROM pg_type t
JOIN pg_namespace n ON (typnamespace = n.oid)
WHERE typtype IN ('b', 'c', 'd', 'e', 'r')
AND NOT (typtype = 'b' AND typarray = 0)
AND nspname NOT IN ('pg_catalog', 'pg_toast',
'information_schema')""")
types = curs.fetchall()
curs.close()
self.conn.rollback()
for typ in types:
self.execute(
STD_DROP %
('DOMAIN' if typ['typtype'] == 'd' else 'TYPE', typ[0]))
self.conn.commit()
# Functions
curs = pgexecute(
self.conn, """SELECT proisagg, p.oid::regprocedure AS proc
FROM pg_proc p JOIN pg_namespace n ON (pronamespace = n.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 1, 2""")
funcs = curs.fetchall()
curs.close()
self.conn.rollback()
for func in funcs:
self.execute('DROP %s IF EXISTS %s CASCADE' %
('AGGREGATE' if func['proisagg'] else 'FUNCTION',
func['proc']))
self.conn.commit()
# Languages
if self.version < 90000:
if self.is_plpgsql_installed():
self.execute_commit("DROP LANGUAGE plpgsql")
# Operators
curs = pgexecute(
self.conn, """SELECT o.oid::regoperator
FROM pg_operator o JOIN pg_namespace n ON (oprnamespace = n.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')""")
opers = curs.fetchall()
curs.close()
self.conn.rollback()
for oper in opers:
self.execute("DROP OPERATOR IF EXISTS %s CASCADE" % (oper[0]))
self.conn.commit()
# Operator families
curs = pgexecute(
self.conn, """SELECT opfname, amname
FROM pg_opfamily o JOIN pg_am a ON (opfmethod = a.oid)
JOIN pg_namespace n ON (opfnamespace = n.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')""")
opfams = curs.fetchall()
curs.close()
self.conn.rollback()
for opfam in opfams:
self.execute(
'DROP OPERATOR FAMILY IF EXISTS "%s" USING "%s" CASCADE' %
(opfam[0], opfam[1]))
self.conn.commit()
# Operator classes
curs = pgexecute(
self.conn, """SELECT opcname, amname
FROM pg_opclass o JOIN pg_am a ON (opcmethod = a.oid)
JOIN pg_namespace n ON (opcnamespace = n.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')""")
opcls = curs.fetchall()
curs.close()
self.conn.rollback()
for opcl in opcls:
self.execute(
'DROP OPERATOR CLASS IF EXISTS "%s" USING "%s" CASCADE' %
(opcl[0], opcl[1]))
self.conn.commit()
# Conversions
curs = pgexecute(
self.conn, """SELECT conname FROM pg_conversion c
JOIN pg_namespace n ON (connamespace = n.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')""")
convs = curs.fetchall()
curs.close()
self.conn.rollback()
for cnv in convs:
self.execute(STD_DROP % ('CONVERSION', cnv[0]))
self.conn.commit()
# Collations
if self.version >= 90100:
curs = pgexecute(
self.conn, """SELECT collname FROM pg_collation c
JOIN pg_namespace n ON (collnamespace = n.oid)
WHERE nspname NOT IN (
'pg_catalog', 'information_schema')""")
colls = curs.fetchall()
curs.close()
self.conn.rollback()
for coll in colls:
self.execute(STD_DROP % ('COLLATION', coll[0]))
self.conn.commit()
# User mappings
curs = pgexecute(
self.conn, """SELECT CASE umuser WHEN 0 THEN 'PUBLIC' ELSE
pg_get_userbyid(umuser) END AS username, s.srvname
FROM pg_user_mappings u
JOIN pg_foreign_server s ON (srvid = s.oid)""")
umaps = curs.fetchall()
curs.close()
self.conn.rollback()
for ump in umaps:
self.execute('DROP USER MAPPING IF EXISTS FOR "%s" SERVER "%s"' %
(ump[0], ump[1]))
self.conn.commit()
# Servers
curs = pgexecute(self.conn, "SELECT srvname FROM pg_foreign_server")
servs = curs.fetchall()
curs.close()
self.conn.rollback()
for srv in servs:
self.execute(STD_DROP % ('SERVER', srv[0]))
self.conn.commit()
# Foreign data wrappers
curs = pgexecute(self.conn,
"SELECT fdwname FROM pg_foreign_data_wrapper")
fdws = curs.fetchall()
curs.close()
self.conn.rollback()
for fdw in fdws:
self.execute(STD_DROP % ('FOREIGN DATA WRAPPER', fdw[0]))
self.conn.commit()
def clear(self):
"Drop schemas and other objects"
STD_DROP = 'DROP %s IF EXISTS "%s" CASCADE'
# Schemas
curs = pgexecute(
self.conn,
"""SELECT nspname FROM pg_namespace
WHERE nspname != 'information_schema'
AND substring(nspname for 3) != 'pg_'
ORDER BY nspname""")
objs = curs.fetchall()
curs.close()
self.conn.rollback()
for obj in objs:
self.execute(STD_DROP % ('SCHEMA', obj[0]))
self.conn.commit()
# Extensions
curs = pgexecute(
self.conn,
"""SELECT extname FROM pg_extension
JOIN pg_namespace n ON (extnamespace = n.oid)
WHERE nspname != 'information_schema'
AND extname != 'plpgsql'""")
exts = curs.fetchall()
curs.close()
self.conn.rollback()
for ext in exts:
self.execute(STD_DROP % ('EXTENSION', ext[0]))
self.conn.commit()
# User mappings
curs = pgexecute(
self.conn,
"""SELECT CASE umuser WHEN 0 THEN 'PUBLIC' ELSE
pg_get_userbyid(umuser) END AS username, s.srvname
FROM pg_user_mappings u
JOIN pg_foreign_server s ON (srvid = s.oid)""")
umaps = curs.fetchall()
curs.close()
self.conn.rollback()
for ump in umaps:
self.execute('DROP USER MAPPING IF EXISTS FOR "%s" SERVER "%s"' % (
ump[0], ump[1]))
self.conn.commit()
# Servers
curs = pgexecute(self.conn, "SELECT srvname FROM pg_foreign_server")
servs = curs.fetchall()
curs.close()
self.conn.rollback()
for srv in servs:
self.execute(STD_DROP % ('SERVER', srv[0]))
self.conn.commit()
# Foreign data wrappers
curs = pgexecute(self.conn,
"SELECT fdwname FROM pg_foreign_data_wrapper")
fdws = curs.fetchall()
curs.close()
self.conn.rollback()
for fdw in fdws:
self.execute(STD_DROP % ('FOREIGN DATA WRAPPER', fdw[0]))
self.conn.commit()
# Create default schema
self.execute("CREATE SCHEMA sd")
self.execute("set search_path='sd', 'pg_catalog'")
self.conn.commit()
