def initialise(metadata):
#===============================================================================
from sqlalchemy import Table, Column, ForeignKey
from sqlalchemy.types import Integer, Float, Unicode, Date, Enum, BLOB
Table(
'Chapter',
metadata,
Column('oid', Integer(), primary_key=True),
Column('title', Unicode(), nullable=False),
)
Table(
'Question',
metadata,
Column('oid', Integer(), primary_key=True),
Column('content', BLOB(), nullable=False),
Column('format', Enum('PNG', 'TIFF', 'GIF', 'JPEG'), nullable=False),
Column('width', Integer(), nullable=False),
Column('height', Integer(), nullable=False),
Column('chapter', ForeignKey('Chapter.oid'), nullable=False),
Column('answers', Unicode(), nullable=False),
Column('correct', Unicode(1), nullable=False),
)
Table(
'Questionnaire',
metadata,
Column('oid', Integer(), primary_key=True),
Column('date', Date(), nullable=False),
Column('title', Unicode(), nullable=False),
)
Table(
'QuestionnaireDefn',
metadata,
Column('oid', Integer(), primary_key=True),
Column('questionnaire',
ForeignKey('Questionnaire.oid'),
nullable=False),
Column('question', ForeignKey('Question.oid'), nullable=False),
Column('rank', Integer(), nullable=False),
Column('page', Integer(), nullable=False),
Column('offset', Float(), nullable=False),
)
Table(
'Paper',
metadata,
Column('oid', Integer(), primary_key=True),
Column('student', Unicode(), nullable=False),
Column('questionnaire',
ForeignKey('Questionnaire.oid'),
nullable=False),
)
Table(
'PaperAnswer',
metadata,
Column('oid', Integer(), primary_key=True),
Column('paper', ForeignKey('Paper.oid'), nullable=False),
Column('answer', Unicode(1), nullable=False),
)
# Imports
from sqlalchemy.exc import OperationalError
from sqlalchemy.schema import (Column, ForeignKey, Index, MetaData, Table,
UniqueConstraint)
from sqlalchemy.types import BINARY, BLOB, INTEGER, JSON, TEXT, VARCHAR
from lib import logger, mysql_engine
# Constants
CHAR_MAX_LEN = 255
BINARY_MAX_LEN = 2**32 - 1
SHA1_LEN = 20
# Types
LONGBLOB = BLOB(length=BINARY_MAX_LEN)
LONGTEXT = TEXT(length=BINARY_MAX_LEN)
LONGVARCHAR = VARCHAR(length=CHAR_MAX_LEN)
SHA1 = BINARY(length=SHA1_LEN)
# Table metadata
metadata = MetaData(bind=mysql_engine)
# All known strace executables and arguments
executables = Table(
'executables',
metadata,
Column('id', INTEGER, primary_key=True),
Column('system', LONGVARCHAR, nullable=False),
Column('executable', LONGVARCHAR, nullable=False),
Column('arguments_hash', SHA1, nullable=False),
def get_columns(self, connection, table_name, schema=None, **kw):
# Query to extract the details of all the fields of the given table
tblqry = """
SELECT r.rdb$field_name AS fname,
r.rdb$null_flag AS null_flag,
t.rdb$type_name AS ftype,
f.rdb$field_sub_type AS stype,
f.rdb$field_length/
COALESCE(cs.rdb$bytes_per_character,1) AS flen,
f.rdb$field_precision AS fprec,
f.rdb$field_scale AS fscale,
COALESCE(r.rdb$default_source,
f.rdb$default_source) AS fdefault
FROM rdb$relation_fields r
JOIN rdb$fields f ON r.rdb$field_source=f.rdb$field_name
JOIN rdb$types t
ON t.rdb$type=f.rdb$field_type AND
t.rdb$field_name='RDB$FIELD_TYPE'
LEFT JOIN rdb$character_sets cs ON
f.rdb$character_set_id=cs.rdb$character_set_id
WHERE f.rdb$system_flag=0 AND r.rdb$relation_name=?
ORDER BY r.rdb$field_position
"""
# get the PK, used to determine the eventual associated sequence
pk_constraint = self.get_pk_constraint(connection, table_name)
pkey_cols = pk_constraint["constrained_columns"]
tablename = self.denormalize_name(table_name)
# get all of the fields for this table
c = connection.execute(tblqry, [tablename])
cols = []
while True:
row = c.fetchone()
if row is None:
break
name = self.normalize_name(row["fname"])
orig_colname = row["fname"]
# get the data type
colspec = row["ftype"].rstrip()
coltype = self.ischema_names.get(colspec)
if coltype is None:
util.warn("Did not recognize type '%s' of column '%s'" %
(colspec, name))
coltype = sqltypes.NULLTYPE
elif issubclass(coltype, Integer) and row["fprec"] != 0:
coltype = NUMERIC(precision=row["fprec"],
scale=row["fscale"] * -1)
elif colspec in ("VARYING", "CSTRING"):
coltype = coltype(row["flen"])
elif colspec == "TEXT":
coltype = TEXT(row["flen"])
elif colspec == "BLOB":
if row["stype"] == 1:
coltype = TEXT()
else:
coltype = BLOB()
else:
coltype = coltype()
# does it have a default value?
defvalue = None
if row["fdefault"] is not None:
# the value comes down as "DEFAULT 'value'": there may be
# more than one whitespace around the "DEFAULT" keyword
# and it may also be lower case
# (see also http://tracker.firebirdsql.org/browse/CORE-356)
defexpr = row["fdefault"].lstrip()
assert defexpr[:8].rstrip().upper() == "DEFAULT", (
"Unrecognized default value: %s" % defexpr)
defvalue = defexpr[8:].strip()
if defvalue == "NULL":
# Redundant
defvalue = None
col_d = {
"name": name,
"type": coltype,
"nullable": not bool(row["null_flag"]),
"default": defvalue,
"autoincrement": "auto",
}
if orig_colname.lower() == orig_colname:
col_d["quote"] = True
# if the PK is a single field, try to see if its linked to
# a sequence thru a trigger
if len(pkey_cols) == 1 and name == pkey_cols[0]:
seq_d = self.get_column_sequence(connection, tablename, name)
if seq_d is not None:
col_d["sequence"] = seq_d
cols.append(col_d)
return cols
def get_columns(self, connection, table_name, schema=None, **kw):
# Query to extract the details of all the fields of the given table
tblqry = """
SELECT DISTINCT r.rdb$field_name AS fname,
r.rdb$null_flag AS null_flag,
t.rdb$type_name AS ftype,
f.rdb$field_sub_type AS stype,
f.rdb$field_length/
COALESCE(cs.rdb$bytes_per_character,1) AS flen,
f.rdb$field_precision AS fprec,
f.rdb$field_scale AS fscale,
COALESCE(r.rdb$default_source,
f.rdb$default_source) AS fdefault
FROM rdb$relation_fields r
JOIN rdb$fields f ON r.rdb$field_source=f.rdb$field_name
JOIN rdb$types t
ON t.rdb$type=f.rdb$field_type AND
t.rdb$field_name='RDB$FIELD_TYPE'
LEFT JOIN rdb$character_sets cs ON
f.rdb$character_set_id=cs.rdb$character_set_id
WHERE f.rdb$system_flag=0 AND r.rdb$relation_name=?
ORDER BY r.rdb$field_position
"""
# get the PK, used to determine the eventual associated sequence
pkey_cols = self.get_primary_keys(connection, table_name)
tablename = self.denormalize_name(table_name)
# get all of the fields for this table
c = connection.execute(tblqry, [tablename])
cols = []
while True:
row = c.fetchone()
if row is None:
break
name = self.normalize_name(row['fname'])
orig_colname = row['fname']
# get the data type
colspec = row['ftype'].rstrip()
coltype = self.ischema_names.get(colspec)
if coltype is None:
util.warn("Did not recognize type '%s' of column '%s'" %
(colspec, name))
coltype = sqltypes.NULLTYPE
elif colspec == 'INT64':
coltype = coltype(precision=row['fprec'],
scale=row['fscale'] * -1)
elif colspec in ('VARYING', 'CSTRING'):
coltype = coltype(row['flen'])
elif colspec == 'TEXT':
coltype = TEXT(row['flen'])
elif colspec == 'BLOB':
if row['stype'] == 1:
coltype = TEXT()
else:
coltype = BLOB()
else:
coltype = coltype(row)
# does it have a default value?
defvalue = None
if row['fdefault'] is not None:
# the value comes down as "DEFAULT 'value'": there may be
# more than one whitespace around the "DEFAULT" keyword
# (see also http://tracker.firebirdsql.org/browse/CORE-356)
defexpr = row['fdefault'].lstrip()
assert defexpr[:8].rstrip() == \
'DEFAULT', "Unrecognized default value: %s" % \
defexpr
defvalue = defexpr[8:].strip()
if defvalue == 'NULL':
# Redundant
defvalue = None
col_d = {
'name': name,
'type': coltype,
'nullable': not bool(row['null_flag']),
'default': defvalue
}
if orig_colname.lower() == orig_colname:
col_d['quote'] = True
# if the PK is a single field, try to see if its linked to
# a sequence thru a trigger
if len(pkey_cols) == 1 and name == pkey_cols[0]:
seq_d = self.get_column_sequence(connection, tablename, name)
if seq_d is not None:
col_d['sequence'] = seq_d
cols.append(col_d)
return cols
class Image(Base):
__tablename__ = 'image'
id = Column(BINARY(16), primary_key=True)
entity_id = Column(ForeignKey('entity.id'))
blob = Column(BLOB(255))
