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))