YAML Database Schema Parser for SQLAlchemy 0

  1from sqlalchemy import MetaData, Table, Column, ForeignKey, Integer, types
  2import yaml
  3
  4class TableDefinition(yaml.YAMLObject):
  5    ''' 
  6    Used to distinguish nested tables from columns.  This class is
  7    instantiated by YAML when an object with the ``!Table`` tag is
  8    visited.
  9    '''
 10    yaml_tag = u'!Table'
 11
 12    def __init__(self, name, columns):
 13        self.name = name
 14        self.columns = columns
 15
 16    
 17def constructColumn(name, type):
 18    '''
 19    Given the name of the type, return an SQLAlchemy Column.
 20
 21    Todo
 22    ----
 23    Allow arguments to be passed to the type engine here.  The length
 24    of a String, for example.
 25    '''
 26    try:
 27        return Column(name, types.__dict__[type]())
 28    except KeyError:
 29        raise KeyError('Unknown type "%s" in column "%s"' % (type, name))
 30
 31
 32def constructForeignKey(oneTable, manyName, metadata):
 33    '''
 34    Create a foreign key column in a table with the name given by
 35    manName that references the first primary key of the table given
 36    by oneTable.  Called when contructing a 'nested' table.
 37
 38    The arguments 'oneTable' and 'manyName' refer to the one-to-many
 39    relationship implied by the added foreign key.
 40    '''
 41
 42    # Use the first primary key from the 'one' table.
 43    primaryKeyName = oneTable.primary_key.keys()[0]
 44
 45    # column name in the form of 'table_primaryId'
 46    columnName = '%s_%s' % (oneTable.name, primaryKeyName)
 47
 48    # find the existing table and insert the column
 49    manyTable = Table(manyName, metadata, autoload=True, mustexist=True)
 50    manyTable.append_column(
 51        Column(columnName, None, ForeignKey(oneTable.columns[primaryKeyName])))
 52
 53
 54def constructTable(tableDef, metadata):
 55    ''' 
 56    Adds a Table to the given MetaData, recursively adding any
 57    "subtables" it finds.
 58    '''
 59
 60    # Check if this table has already been constructed.
 61    # This can happen if the relation graph is cyclic (not a tree).
 62    if tableDef.name in (table.name for table in metadata.table_iterator()):
 63        return
 64
 65    # The primary key must be added before other columns in case this
 66    # table contains any cyclic references to itself.
 67    if hasattr(tableDef, 'primary_key'):
 68        primary_key = constructColumn(tableDef.primary_key,
 69                                      tableDef.columns[tableDef.primary_key])
 70        # don't add this column again
 71        del tableDef.columns[tableDef.primary_key]
 72    else: # create a primary key if none specified explicitly 
 73        primary_key = Column('id', Integer, primary_key=True)
 74
 75    # add a new table to the metadata
 76    # The 'meat' columns are added later.  This allows a recursive call to this
 77    # function to skip tables that have already been added here.
 78    table = Table(tableDef.name, metadata, primary_key)
 79
 80    # add the columns to the metadata
 81    for name,value in tableDef.columns.iteritems():
 82        if isinstance(value, TableDefinition):
 83            # recursively construct any "subtables"
 84            constructTable(value, metadata)
 85            constructForeignKey(table, value.name, metadata)
 86        else:
 87            table.append_column(constructColumn(name, value))
 88
 89
 90def generateMetadata(fileObject):
 91    ''' 
 92    Given a YAML file containing a list of table definitions, returns a
 93    SQLAlchemy MetaData object describing the database.
 94    '''
 95
 96    schema = yaml.load(fileObject)
 97    metadata = MetaData()
 98
 99    # assumes the root element is a list of table definitions
100    for tableDef in schema:
101        constructTable(tableDef, metadata)
102
103    return metadata