def loadSchema(self):
BaseDatabase.loadSchema(self)
c = self.cursor()
c.execute("select type, name, tbl_name from sqlite_master")
slist = c.fetchall()
if not len(slist):
return self.version
for (type, name, tbl_name) in slist:
if type == "table":
# skip internal sqlite tables
if name.startswith('sqlite_'):
continue
if name.endswith("_sequence"):
self.sequences.setdefault(name[:-len("_sequence")], None)
else:
self.tables.setdefault(name, [])
elif type == "view":
self.views.setdefault(name, None)
elif type == "index":
if name.startswith("sqlite_autoindex_"):
continue
self.tables.setdefault(tbl_name, []).append(name)
elif type == "trigger":
self.triggers.setdefault(name, tbl_name)
return self.getVersion()
def loadSchema(self):
BaseDatabase.loadSchema(self)
c = self.cursor()
c.execute("select type, name, tbl_name from sqlite_master")
slist = c.fetchall()
if not len(slist):
return self.version
for (type, name, tbl_name) in slist:
if type == "table":
# skip internal sqlite tables
if name.startswith('sqlite_'):
continue
if name.endswith("_sequence"):
self.sequences.setdefault(name[:-len("_sequence")], None)
else:
self.tables.setdefault(name, [])
elif type == "view":
self.views.setdefault(name, None)
elif type == "index":
if name.startswith("sqlite_autoindex_"):
continue
self.tables.setdefault(tbl_name, []).append(name)
elif type == "trigger":
self.triggers.setdefault(name, tbl_name)
return self.getVersion()
def getVersion(self):
try:
return BaseDatabase.getVersion(self, raiseOnError=True)
except sqlerrors.InvalidTable:
# Postgres is so nice that it ruined the current
# transaction because of the missing table, so roll back.
self.dbh.rollback()
self.version = sqllib.DBversion(0, 0)
return self.version
def createTrigger(self, table, column, onAction, pinned=None):
if pinned is not None:
import warnings
warnings.warn(
'The "pinned" kwparam to createTrigger is deprecated and '
'no longer has any affect on triggers', DeprecationWarning)
onAction = onAction.lower()
assert (onAction in ["insert", "update"])
# prepare the sql and the trigger name and pass it to the
# BaseTrigger for creation
when = "AFTER"
sql = """
UPDATE %s SET %s = unix_timestamp() WHERE _ROWID_ = NEW._ROWID_ ;
""" % (table, column)
return BaseDatabase.createTrigger(self, table, when, onAction, sql)
def createTrigger(self, table, column, onAction, pinned=None):
if pinned is not None:
import warnings
warnings.warn(
'The "pinned" kwparam to createTrigger is deprecated and '
'no longer has any affect on triggers',
DeprecationWarning)
onAction = onAction.lower()
assert(onAction in ["insert", "update"])
# prepare the sql and the trigger name and pass it to the
# BaseTrigger for creation
when = "AFTER"
sql = """
UPDATE %s SET %s = unix_timestamp() WHERE _ROWID_ = NEW._ROWID_ ;
""" % (table, column)
return BaseDatabase.createTrigger(self, table, when, onAction, sql)
def close(self):
self.inode = (None, None)
return BaseDatabase.close(self)
def close(self):
self.inode = (None, None)
return BaseDatabase.close(self)
def loadSchema(self):
BaseDatabase.loadSchema(self)
c = self.cursor()
# get tables
c.execute("""
select tablename as name, schemaname as schema
from pg_tables
where schemaname not in ('pg_catalog', 'pg_toast', 'information_schema')
and ( schemaname !~ '^pg_temp_' OR schemaname = (pg_catalog.current_schemas(true))[1])
""")
for table, schema in c.fetchall():
if schema.startswith("pg_temp"):
self.tempTables[table] = sqllib.Llist()
else:
self.tables[table] = sqllib.Llist()
if not len(self.tables):
return self.version
# views
c.execute("""
select viewname as name
from pg_views
where schemaname not in ('pg_catalog', 'pg_toast', 'information_schema')
""")
for name, in c.fetchall():
self.views[name] = True
# indexes
c.execute("""
select indexname as name, tablename as table, schemaname as schema
from pg_indexes
where schemaname not in ('pg_catalog', 'pg_toast', 'information_schema')
and ( schemaname !~ '^pg_temp_' OR schemaname = (pg_catalog.current_schemas(true))[1])
""")
for (name, table, schema) in c.fetchall():
if schema.startswith("pg_temp"):
self.tempTables.setdefault(table, sqllib.Llist()).append(name)
else:
self.tables.setdefault(table, sqllib.Llist()).append(name)
# sequences. I wish there was a better way...
c.execute("""
SELECT c.relname as name
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'S'
AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
AND pg_catalog.pg_table_is_visible(c.oid)
""")
for name, in c.fetchall():
self.sequences[name] = True
# triggers
# AWKWARD: postgres 9.0 changed tgisconstraint to tgisinternal, so we
# have to detect which it is to maintain compatibility :(
# -- gxti 2010-11-01
c.execute("""
SELECT a.attname
FROM pg_catalog.pg_attribute a
LEFT JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
LEFT JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'pg_catalog' AND c.relname = 'pg_trigger'
AND a.attname in ('tgisconstraint', 'tgisinternal')
""")
colname, = c.fetchone()
c.execute("""
SELECT t.tgname, c.relname
FROM pg_catalog.pg_trigger t, pg_class c, pg_namespace n
WHERE t.tgrelid = c.oid AND c.relnamespace = n.oid
AND NOT t.%(colname)s
AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
AND ( n.nspname !~ '^pg_temp_' OR n.nspname = (pg_catalog.current_schemas(true))[1])
""" % dict(colname=colname))
for (name, table) in c.fetchall():
self.triggers[name] = table
version = self.getVersion()
return version
