def create(self):
"""Return a SQL statement to CREATE the index
:return: SQL statements
"""
stmts = []
# indexes defined by constraints are not to be dealt with as indexes
if getattr(self, '_for_constraint', None):
return stmts
unq = hasattr(self, 'unique') and self.unique
acc = ''
if hasattr(self, 'access_method') and self.access_method != 'btree':
acc = 'USING %s ' % self.access_method
tblspc = ''
if hasattr(self, 'tablespace'):
tblspc = '\n TABLESPACE %s' % self.tablespace
pred = ''
if hasattr(self, 'predicate'):
pred = '\n WHERE %s' % self.predicate
stmts.append("CREATE %sINDEX %s ON %s %s(%s)%s%s" % (
'UNIQUE ' if unq else '', quote_id(self.name),
self.qualname(self.table), acc, self.key_expressions(), tblspc,
pred))
if hasattr(self, 'cluster') and self.cluster:
stmts.append("CLUSTER %s USING %s" % (
self.qualname(self.table), quote_id(self.name)))
return stmts
def identifier(self):
"""Return a full identifier for a user mapping object
:return: string
"""
return "FOR %s SERVER %s" % (self.name == 'PUBLIC' and 'PUBLIC'
or quote_id(self.name),
quote_id(self.server))
def identifier(self):
"""Return a full identifier for a user mapping object
:return: string
"""
return "FOR %s SERVER %s" % (
self.username == 'PUBLIC' and 'PUBLIC' or quote_id(self.username),
quote_id(self.server))
def get_attrs(self, dbconn):
"""Get the attributes for the sequence
:param dbconn: a DbConnection object
"""
data = dbconn.fetchone(
"""SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM %s.%s""" % (quote_id(self.schema), quote_id(self.name)))
for key, val in list(data.items()):
setattr(self, key, val)
def get_attrs(self, dbconn):
"""Get the attributes for the sequence
:param dbconn: a DbConnection object
"""
data = dbconn.fetchone(
"""SELECT start_value, increment_by, max_value, min_value,
cache_value
FROM %s.%s""" % (quote_id(self.schema), quote_id(self.name)))
for key, val in data.items():
setattr(self, key, val)
def set_sequence_default(self):
"""Return SQL statements to set a nextval() DEFAULT
:return: list of SQL statements
"""
stmts = []
pth = self.set_search_path()
if pth:
stmts.append(pth)
stmts.append("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s" % (
quote_id(self.table), quote_id(self.name), self.default))
return stmts
def create(self):
"""Return SQL statements to CREATE the user mapping
:return: SQL statements
"""
options = []
if hasattr(self, 'options'):
options.append(self.options_clause())
return ["CREATE USER MAPPING FOR %s\n SERVER %s%s" % (
self.name == 'PUBLIC' and 'PUBLIC' or
quote_id(self.name), quote_id(self.server),
options and '\n ' + ',\n '.join(options) or '')]
def create(self):
"""Return SQL statements to CREATE the user mapping
:return: SQL statements
"""
options = []
if hasattr(self, 'options'):
options.append(self.options_clause())
return ["CREATE USER MAPPING FOR %s\n SERVER %s%s" % (
self.username == 'PUBLIC' and 'PUBLIC' or
quote_id(self.username), quote_id(self.server),
options and '\n ' + ',\n '.join(options) or '')]
def create(self):
"""Return SQL statements to CREATE the extension
:return: SQL statements
"""
opt_clauses = []
if hasattr(self, 'schema') and self.schema != 'public':
opt_clauses.append("SCHEMA %s" % quote_id(self.schema))
if hasattr(self, 'version'):
opt_clauses.append("VERSION '%s'" % self.version)
return ["CREATE EXTENSION %s%s" % (
quote_id(self.name), ('\n ' + '\n '.join(opt_clauses))
if opt_clauses else '')]
def add_owner(self):
"""Return statement to ALTER the sequence to indicate its owner table
:return: SQL statement
"""
stmts = []
pth = self.set_search_path()
if pth:
stmts.append(pth)
stmts.append("ALTER SEQUENCE %s OWNED BY %s.%s" % (
quote_id(self.name), quote_id(self.owner_table),
quote_id(self.owner_column)))
return stmts
def create(self):
"""Return SQL statements to CREATE the extension
:return: SQL statements
"""
opt_clauses = []
if self.schema is not None and self.schema not in (
'pg_catalog', 'public'):
opt_clauses.append("SCHEMA %s" % quote_id(self.schema))
if self.version is not None:
opt_clauses.append("VERSION '%s'" % self.version)
return ["CREATE EXTENSION %s%s" % (
quote_id(self.name), ('\n ' + '\n '.join(opt_clauses))
if opt_clauses else '')]
def create(self):
"""Return SQL statements to CREATE the extension
:return: SQL statements
"""
opt_clauses = []
if self.schema is not None and self.schema not in (
'pg_catalog', 'public'):
opt_clauses.append("SCHEMA %s" % quote_id(self.schema))
if self.version is not None:
opt_clauses.append("VERSION '%s'" % self.version)
return ["CREATE EXTENSION %s%s" % (
quote_id(self.name), ('\n ' + '\n '.join(opt_clauses))
if opt_clauses else '')]
def diff_map(self, inpk):
"""Generate SQL to transform an existing primary key
:param inpk: a YAML map defining the new primary key
:return: list of SQL statements
Compares the primary key to an input primary key and generates
SQL statements to transform it into the one represented by the
input.
"""
stmts = []
if hasattr(inpk, 'keycols') and hasattr(self, 'keycols') \
and hasattr(self, '_table') and hasattr(self._table, 'columns') \
and hasattr(self._table,'primary_key') and \
hasattr(self._table.primary_key,'keycols'):
selfcols = {i.number: i.name for i in self._table.columns}
selfpk = [selfcols[i] for i in self._table.primary_key.keycols]
if inpk.keycols != selfpk:
stmts.append(
"ALTER TABLE {tname} DROP CONSTRAINT {pkname}".format(
tname=self._table.name, pkname=self.name))
stmts.append("ALTER TABLE {tname} ADD CONSTRAINT {pkname} "
"PRIMARY KEY ({cols})".format(
tname=inpk._table.name, pkname=inpk.name,
cols=', '.join(inpk.keycols)))
if hasattr(inpk, 'cluster'):
if not hasattr(self, 'cluster'):
stmts.append("CLUSTER %s USING %s" % (
self._table.qualname(), quote_id(self.name)))
elif hasattr(self, 'cluster'):
stmts.append("ALTER TABLE %s\n SET WITHOUT CLUSTER" %
self._table.qualname())
stmts.append(self.diff_description(inpk))
return stmts
def diff_map(self, inpk):
"""Generate SQL to transform an existing primary key
:param inpk: a YAML map defining the new primary key
:return: list of SQL statements
Compares the primary key to an input primary key and generates
SQL statements to transform it into the one represented by the
input.
"""
stmts = []
# TODO chompare column names
if self.col_idx != inpk.col_idx:
stmts.append(self.drop())
stmts.append(inpk.add())
elif hasattr(inpk, 'cluster'):
if not hasattr(self, 'cluster'):
stmts.append("CLUSTER %s USING %s" % (
self._table.qualname(), quote_id(self.name)))
elif hasattr(self, 'cluster'):
stmts.append("ALTER TABLE %s\n SET WITHOUT CLUSTER" %
self._table.qualname())
stmts.append(self.diff_description(inpk))
return stmts
def diff_map(self, inindex):
"""Generate SQL to transform an existing index
:param inindex: a YAML map defining the new index
:return: list of SQL statements
Compares the index to an input index and generates SQL
statements to transform it into the one represented by the
input.
"""
stmts = []
if not hasattr(self, 'unique'):
self.unique = False
if self.access_method != inindex.access_method \
or self.unique != inindex.unique:
stmts.append("DROP INDEX %s" % self.qualname())
self.access_method = inindex.access_method
self.unique = inindex.unique
stmts.append(self.create())
# TODO: need to deal with changes in keycols
base = "ALTER INDEX %s\n " % self.qualname()
if hasattr(inindex, 'tablespace'):
if not hasattr(self, 'tablespace') \
or self.tablespace != inindex.tablespace:
stmts.append(base + "SET TABLESPACE %s"
% quote_id(inindex.tablespace))
elif hasattr(self, 'tablespace'):
stmts.append(base + "SET TABLESPACE pg_default")
stmts.append(self.diff_description(inindex))
return stmts
def create(self):
"""Return SQL statements to CREATE the trigger
:return: SQL statements
"""
constr = defer = ''
if hasattr(self, 'constraint') and self.constraint:
constr = "CONSTRAINT "
if hasattr(self, 'deferrable') and self.deferrable:
defer = "DEFERRABLE "
if hasattr(self, 'initially_deferred') and self.initially_deferred:
defer += "INITIALLY DEFERRED"
if defer:
defer = '\n ' + defer
evts = " OR ".join(self.events).upper()
if hasattr(self, 'columns') and 'update' in self.events:
evts = evts.replace("UPDATE",
"UPDATE OF %s" % (", ".join(self.columns)))
cond = ''
if hasattr(self, 'condition'):
cond = "\n WHEN (%s)" % self.condition
return [
"CREATE %sTRIGGER %s\n %s %s ON %s%s\n FOR EACH %s"
"%s\n EXECUTE PROCEDURE %s" %
(constr, quote_id(
self.name), self.timing.upper(), evts, self._table.qualname(),
defer, self.level.upper(), cond, self.procedure)
]
def diff_map(self, inuc):
"""Generate SQL to transform an existing unique constraint
:param inuc: a YAML map defining the new unique constraint
:return: list of SQL statements
Compares the unique constraint to an input unique constraint
and generates SQL statements to transform it into the one
represented by the input.
"""
stmts = []
if hasattr(inuc, 'keycols') and hasattr(self, 'keycols') \
and hasattr(self, '_table') and hasattr(self._table, 'columns'):
selfcols = {i.number: i.name for i in self._table.columns}
selfunique = [selfcols[i] for i in self.keycols]
if inuc.keycols != selfunique:
stmts.append(
"ALTER TABLE {tname} DROP CONSTRAINT {conname}".format(
tname=self._table.name, conname=self.name))
stmts.append("ALTER TABLE {tname} ADD CONSTRAINT {conname} "
"UNIQUE ({cols})".format(
tname=inuc._table.name, conname=inuc.name,
cols=', '.join(inuc.keycols)))
if hasattr(inuc, 'cluster'):
if not hasattr(self, 'cluster'):
stmts.append("CLUSTER %s USING %s" % (
self._table.qualname(), quote_id(self.name)))
elif hasattr(self, 'cluster'):
stmts.append("ALTER TABLE %s\n SET WITHOUT CLUSTER" %
self._table.qualname())
stmts.append(self.diff_description(inuc))
return stmts
def comment(self):
"""Return SQL statement to create COMMENT on constraint
:return: SQL statement
"""
return "COMMENT ON CONSTRAINT %s ON %s IS %s" % (
quote_id(self.name), self._table.qualname(), self._comment_text())
def create(self):
"""Return SQL statements to CREATE the trigger
:return: SQL statements
"""
stmts = []
constr = defer = ''
if hasattr(self, 'constraint') and self.constraint:
constr = "CONSTRAINT "
if hasattr(self, 'deferrable') and self.deferrable:
defer = "DEFERRABLE "
if hasattr(self, 'initially_deferred') and self.initially_deferred:
defer += "INITIALLY DEFERRED"
if defer:
defer = '\n ' + defer
evts = " OR ".join(self.events).upper()
if hasattr(self, 'columns') and 'update' in self.events:
evts = evts.replace("UPDATE", "UPDATE OF %s" % (
", ".join(self.columns)))
cond = ''
if hasattr(self, 'condition'):
cond = "\n WHEN (%s)" % self.condition
stmts.append("CREATE %sTRIGGER %s\n %s %s ON %s%s\n FOR EACH %s"
"%s\n EXECUTE PROCEDURE %s" % (
constr, quote_id(self.name), self.timing.upper(), evts,
self._table.qualname(), defer,
self.level.upper(), cond, self.procedure))
if hasattr(self, 'description'):
stmts.append(self.comment())
return stmts
def create(self):
"""Return SQL statements to CREATE the server
:return: SQL statements
"""
clauses = []
options = []
for opt in ['type', 'version']:
if hasattr(self, opt):
clauses.append("%s '%s'" % (opt.upper(), getattr(self, opt)))
if hasattr(self, 'options'):
options.append(self.options_clause())
return ["CREATE SERVER %s%s\n FOREIGN DATA WRAPPER %s%s" % (
quote_id(self.name),
clauses and ' ' + ' '.join(clauses) or '',
quote_id(self.wrapper),
options and '\n ' + ',\n '.join(options) or '')]
def create(self):
"""Return SQL statements to CREATE the server
:return: SQL statements
"""
clauses = []
options = []
for opt in ['type', 'version']:
if hasattr(self, opt):
clauses.append("%s '%s'" % (opt.upper(), getattr(self, opt)))
if hasattr(self, 'options'):
options.append(self.options_clause())
return ["CREATE SERVER %s%s\n FOREIGN DATA WRAPPER %s%s" % (
quote_id(self.name),
clauses and ' ' + ' '.join(clauses) or '',
quote_id(self.wrapper),
options and '\n ' + ',\n '.join(options) or '')]
def create(self):
"""Return a SQL statement to CREATE the index
:return: SQL statements
"""
stmts = []
pth = self.set_search_path()
if pth:
stmts.append(pth)
unq = hasattr(self, 'unique') and self.unique
acc = hasattr(self, 'access_method') \
and 'USING %s ' % self.access_method or ''
stmts.append("CREATE %sINDEX %s ON %s %s(%s)" % (
unq and 'UNIQUE ' or '', quote_id(self.name), quote_id(self.table),
acc, hasattr(self, 'keycols') and self.key_columns() or
self.expression))
return stmts
def add(self):
"""Return string to add the CHECK constraint via ALTER TABLE
:return: SQL statement
"""
return ["ALTER TABLE %s ADD CONSTRAINT %s %s (%s)" % (
self._table.qualname(), quote_id(self.name), self.objtype,
self.expression)]
def create(self):
"""Return SQL statements to CREATE the user mapping
:return: SQL statements
"""
options = []
if hasattr(self, 'options'):
opts = []
for opt in self.options:
(nm, val) = opt.split('=')
opts.append("%s '%s'" % (nm, val))
options.append("OPTIONS (%s)" % ', '.join(opts))
stmts = ["CREATE USER MAPPING FOR %s\n SERVER %s%s" % (
self.username == 'PUBLIC' and 'PUBLIC' or
quote_id(self.username), quote_id(self.server),
options and '\n ' + ',\n '.join(options) or '')]
return stmts
def create(self):
"""Return SQL statements to CREATE the schema
:return: SQL statements
"""
# special case for --revert
if self.name == 'pg_catalog':
return []
return ["CREATE SCHEMA %s" % quote_id(self.name)]
def qualname(self):
"""Return the schema-qualified name of the operator
:return: string
No qualification is used if the schema is 'public'.
"""
return self.schema == 'public' and self.name \
or "%s.%s" % (quote_id(self.schema), self.name)
def set_sequence_default(self):
"""Return SQL statements to set a nextval() DEFAULT
:return: list of SQL statements
"""
stmts = []
stmts.append("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s" % (
self.qualname(self.table), quote_id(self.name), self.default))
return stmts
def set_sequence_default(self):
"""Return SQL statements to set a nextval() DEFAULT
:return: list of SQL statements
"""
stmts = []
stmts.append("ALTER TABLE %s ALTER COLUMN %s SET DEFAULT %s" % (
self.qualname(self.table), quote_id(self.name), self.default))
return stmts
def qualname(self):
"""Return the schema-qualified name of the operator
:return: string
No qualification is used if the schema is 'public'.
"""
return self.schema == 'public' and self.name \
or "%s.%s" % (quote_id(self.schema), self.name)
def drop(self):
"""Return string to drop the constraint via ALTER TABLE
:return: SQL statement
"""
return [
"ALTER %s %s DROP CONSTRAINT %s" %
(self._table.objtype, self._table.qualname(), quote_id(self.name))
]
def create(self):
"""Return SQL statements to CREATE the schema
:return: SQL statements
"""
stmts = ["CREATE SCHEMA %s" % quote_id(self.name)]
if hasattr(self, "description"):
stmts.append(self.comment())
return stmts
def create(self):
"""Return SQL statements to CREATE the language
:return: SQL statements
"""
stmts = ["CREATE LANGUAGE %s" % quote_id(self.name)]
if hasattr(self, 'description'):
stmts.append(self.comment())
return stmts
def alter(self, inindex):
"""Generate SQL to transform an existing index
:param inindex: a YAML map defining the new index
:return: list of SQL statements
Compares the index to an input index and generates SQL
statements to transform it into the one represented by the
input.
"""
stmts = []
# indexes defined by constraints are not to be dealt with as indexes
if getattr(self, '_for_constraint', None):
return stmts
if not hasattr(self, 'unique'):
self.unique = False
if self.access_method != inindex.access_method \
or self.unique != inindex.unique \
or self.keys != inindex.keys:
stmts.append("DROP INDEX %s" % self.qualname())
self.access_method = inindex.access_method
self.unique = inindex.unique
self.keys = inindex.keys
stmts.append(self.create())
base = "ALTER INDEX %s\n " % self.qualname()
if hasattr(inindex, 'tablespace'):
if not hasattr(self, 'tablespace') \
or self.tablespace != inindex.tablespace:
stmts.append(base + "SET TABLESPACE %s"
% quote_id(inindex.tablespace))
elif hasattr(self, 'tablespace'):
stmts.append(base + "SET TABLESPACE pg_default")
if hasattr(inindex, 'cluster'):
if not hasattr(self, 'cluster'):
stmts.append("CLUSTER %s USING %s" % (
self.qualname(self.table), quote_id(self.name)))
elif hasattr(self, 'cluster'):
stmts.append("ALTER TABLE %s\n SET WITHOUT CLUSTER" %
self.qualname(self.table))
stmts.append(super(Index, self).alter(inindex))
return stmts
def add_owner(self):
"""Return statement to ALTER the sequence to indicate its owner table
:return: SQL statement
"""
stmts = []
stmts.append("ALTER SEQUENCE %s OWNED BY %s.%s" % (
self.qualname(), self.qualname(self.owner_table),
quote_id(self.owner_column)))
return stmts
def add_owner(self):
"""Return statement to ALTER the sequence to indicate its owner table
:return: SQL statement
"""
stmts = []
stmts.append("ALTER SEQUENCE %s OWNED BY %s.%s" %
(self.qualname(), self.qualname(
self.owner_table), quote_id(self.owner_column)))
return stmts
def add(self):
"""Return string to add the constraint via ALTER TABLE
:return: SQL statement
Works as is for primary keys and unique constraints but has
to be overridden for check constraints and foreign keys.
"""
stmts = []
tblspc = ''
if hasattr(self, 'tablespace'):
tblspc = " USING INDEX TABLESPACE %s" % self.tablespace
stmts.append("ALTER TABLE %s ADD CONSTRAINT %s %s (%s)%s" % (
self._table.qualname(), quote_id(self.name),
self.objtype, self.key_columns(), tblspc))
if hasattr(self, 'cluster') and self.cluster:
stmts.append("CLUSTER %s USING %s" % (
self._table.qualname(), quote_id(self.name)))
return stmts
def drop(self):
"""Return string to drop the constraint via ALTER TABLE
:return: SQL statement
"""
if not hasattr(self, 'dropped') or not self.dropped:
self.dropped = True
return "ALTER TABLE %s DROP CONSTRAINT %s" % (
self._table.qualname(), quote_id(self.name))
return []
def diff_map(self, inuc):
"""Generate SQL to transform an existing unique constraint
:param inuc: a YAML map defining the new unique constraint
:return: list of SQL statements
Compares the unique constraint to an input unique constraint
and generates SQL statements to transform it into the one
represented by the input.
"""
stmts = []
# TODO: to be implemented (via ALTER DROP and ALTER ADD)
if hasattr(inuc, 'cluster'):
if not hasattr(self, 'cluster'):
stmts.append("CLUSTER %s USING %s" % (
quote_id(self.table), quote_id(self.name)))
elif hasattr(self, 'cluster'):
stmts.append("ALTER TABLE %s\n SET WITHOUT CLUSTER" %
quote_id(self.table))
stmts.append(self.diff_description(inuc))
return stmts
def add(self):
"""Return a string to specify the column in a CREATE or ALTER TABLE
:return: partial SQL statement
"""
stmt = "%s %s" % (quote_id(self.name), self.type)
if hasattr(self, 'not_null'):
stmt += ' NOT NULL'
if hasattr(self, 'default'):
if not self.default.startswith('nextval'):
stmt += ' DEFAULT ' + self.default
return stmt
def create(self):
"""Return a SQL statement to CREATE the index
:return: SQL statements
"""
stmts = []
pth = self.set_search_path()
if pth:
stmts.append(pth)
unq = hasattr(self, 'unique') and self.unique
acc = hasattr(self, 'access_method') \
and 'USING %s ' % self.access_method or ''
tblspc = ''
if hasattr(self, 'tablespace'):
tblspc = '\n TABLESPACE %s' % self.tablespace
stmts.append("CREATE %sINDEX %s ON %s %s(%s)%s" % (
'UNIQUE ' if unq else '', quote_id(self.name),
quote_id(self.table), acc, self.key_expressions(), tblspc))
if hasattr(self, 'description'):
stmts.append(self.comment())
return stmts
def create(self):
"""Return SQL statements to CREATE the event trigger
:return: SQL statements
"""
filter = ''
if hasattr(self, 'tags'):
filter = "\n WHEN tag IN (%s)" % ", ".join(
["'%s'" % tag for tag in self.tags])
return ["CREATE %s %s\n ON %s%s\n EXECUTE PROCEDURE %s" % (
self.objtype, quote_id(self.name), self.event, filter,
self.procedure)]
def add(self):
"""Return string to add the constraint via ALTER TABLE
:return: SQL statement
Works as is for primary keys and unique constraints but has
to be overridden for check constraints and foreign keys.
"""
return "ALTER TABLE %s ADD CONSTRAINT %s %s (%s)" % (
DbSchemaObject(schema=self.schema, name=self.table).qualname(),
quote_id(self.name),
self.objtype, self.key_columns())
def add(self):
"""Return a string to specify the column in a CREATE or ALTER TABLE
:return: partial SQL statement
"""
stmt = "%s %s" % (quote_id(self.name), self.type)
if hasattr(self, 'not_null'):
stmt += ' NOT NULL'
if hasattr(self, 'default'):
stmt += ' DEFAULT ' + self.default
if hasattr(self, 'collation') and self.collation != 'default':
stmt += ' COLLATE "%s"' % self.collation
return (stmt, '' if self.description is None else self.comment())
def diff_map(self, inindex):
"""Generate SQL to transform an existing index
:param inindex: a YAML map defining the new index
:return: list of SQL statements
Compares the index to an input index and generates SQL
statements to transform it into the one represented by the
input.
"""
stmts = []
if not hasattr(self, 'unique'):
self.unique = False
if self.access_method != inindex.access_method \
or self.unique != inindex.unique \
or self.keys != inindex.keys:
stmts.append("DROP INDEX %s" % self.qualname())
self.access_method = inindex.access_method
self.unique = inindex.unique
self.keys = inindex.keys
stmts.append(self.create())
base = "ALTER INDEX %s\n " % self.qualname()
if hasattr(inindex, 'tablespace'):
if not hasattr(self, 'tablespace') \
or self.tablespace != inindex.tablespace:
stmts.append(base + "SET TABLESPACE %s"
% quote_id(inindex.tablespace))
elif hasattr(self, 'tablespace'):
stmts.append(base + "SET TABLESPACE pg_default")
if hasattr(inindex, 'cluster'):
if not hasattr(self, 'cluster'):
stmts.append("CLUSTER %s USING %s" % (
self.qualname(self.table), quote_id(self.name)))
elif hasattr(self, 'cluster'):
stmts.append("ALTER TABLE %s\n SET WITHOUT CLUSTER" %
self.qualname(self.table))
stmts.append(self.diff_description(inindex))
return stmts
def create(self):
"""Return SQL statements to CREATE the language
:return: SQL statements
"""
stmts = []
if not hasattr(self, '_ext'):
stmts.append("CREATE LANGUAGE %s" % quote_id(self.name))
if hasattr(self, 'owner'):
stmts.append(self.alter_owner())
if self.description is not None:
stmts.append(self.comment())
return stmts
def add(self):
"""Return string to add the CHECK constraint via ALTER TABLE
:return: SQL statement
"""
# Don't generate inherited constraints
if getattr(self, 'inherited', None):
return []
return [
"ALTER %s %s ADD CONSTRAINT %s %s (%s)" %
(self._table.objtype, self._table.qualname(), quote_id(
self.name), self.objtype, self.expression)
]
def create(self):
"""Return SQL statements to CREATE the event trigger
:return: SQL statements
"""
filter = ''
if hasattr(self, 'tags'):
filter = "\n WHEN tag IN (%s)" % ", ".join(
["'%s'" % tag for tag in self.tags])
return [
"CREATE %s %s\n ON %s%s\n EXECUTE PROCEDURE %s" %
(self.objtype, quote_id(
self.name), self.event, filter, self.procedure)
]
def create(self):
"""Return SQL statements to CREATE the data wrapper
:return: SQL statements
"""
clauses = []
for fnc in ['validator', 'handler']:
if hasattr(self, fnc):
clauses.append("%s %s" % (fnc.upper(), getattr(self, fnc)))
if hasattr(self, 'options'):
clauses.append(self.options_clause())
return ["CREATE FOREIGN DATA WRAPPER %s%s" % (
quote_id(self.name),
clauses and '\n ' + ',\n '.join(clauses) or '')]
def create(self):
"""Return SQL statements to CREATE the rule
:return: SQL statements
"""
where = instead = ''
if hasattr(self, 'condition'):
where = ' WHERE %s' % self.condition
if hasattr(self, 'instead'):
instead = 'INSTEAD '
return [
"CREATE RULE %s AS ON %s\n TO %s%s\n DO %s%s" %
(quote_id(self.name), self.event.upper(), self._table.qualname(),
where, instead, self.actions)
]
def drop(self):
"""Return string to drop the column via ALTER TABLE
:return: SQL statement
"""
if hasattr(self, 'dropped'):
return []
if hasattr(self, '_table'):
(comptype, objtype) = (self._table.objtype, 'COLUMN')
compname = self._table.qualname()
elif hasattr(self, '_type'):
(comptype, objtype) = ('TYPE', 'ATTRIBUTE')
compname = self._type.qualname()
else:
raise TypeError("Cannot determine type of %s", self.name)
return "ALTER %s %s DROP %s %s" % (comptype, compname, objtype,
quote_id(self.name))
def alter(self, incol):
"""Generate SQL to transform an existing column
:param insequence: a YAML map defining the new column
:return: list of partial SQL statements
Compares the column to an input column and generates partial
SQL statements to transform it into the one represented by the
input.
"""
stmts = []
base = "ALTER COLUMN %s " % quote_id(self.name)
# check NOT NULL
if not hasattr(self, 'not_null') and hasattr(incol, 'not_null'):
stmts.append(base + "SET NOT NULL")
if hasattr(self, 'not_null') and not hasattr(incol, 'not_null'):
stmts.append(base + "DROP NOT NULL")
# check data types
if not hasattr(self, 'type'):
raise ValueError("Column '%s' missing datatype" % self.name)
if not hasattr(incol, 'type'):
raise ValueError("Input column '%s' missing datatype" % incol.name)
if self.type != incol.type:
# validate type conversion?
stmts.append(base + "TYPE %s" % incol.type)
# check DEFAULTs
if not hasattr(self, 'default') and hasattr(incol, 'default'):
stmts.append(base + "SET DEFAULT %s" % incol.default)
if hasattr(self, 'default'):
if not hasattr(incol, 'default'):
stmts.append(base + "DROP DEFAULT")
elif self.default != incol.default:
stmts.append(base + "SET DEFAULT %s" % incol.default)
# check STATISTICS
if hasattr(self, 'statistics'):
if self.statistics == -1 and (
hasattr(incol, 'statistics') and incol.statistics != -1):
stmts.append(base + "SET STATISTICS %d" % incol.statistics)
if self.statistics != -1 and (not hasattr(incol, 'statistics') or
incol.statistics == -1):
stmts.append(base + "SET STATISTICS -1")
return (", ".join(stmts), self.diff_description(incol))
def add(self):
"""Return string to add the foreign key via ALTER TABLE
:return: SQL statement
"""
match = ''
if hasattr(self, 'match'):
match = " MATCH %s" % self.match.upper()
actions = ''
if hasattr(self, 'on_update'):
actions = " ON UPDATE %s" % self.on_update.upper()
if hasattr(self, 'on_delete'):
actions += " ON DELETE %s" % self.on_delete.upper()
if getattr(self, 'deferrable', False):
actions += " DEFERRABLE"
if getattr(self, 'deferred', False):
actions += " INITIALLY DEFERRED"
return "ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) " \
"REFERENCES %s (%s)%s%s" % (
self._table.qualname(), quote_id(self.name),
self.key_columns(), self.references.qualname(),
self.ref_columns(), match, actions)
def key_columns(self):
"""Return comma-separated list of key column names
:return: string
"""
return ", ".join([quote_id(col) for col in self.keycols])
def identifier(self):
"""Return a full identifier for a rule object
:return: string
"""
return "%s ON %s" % (quote_id(self.name), self._table.qualname())
