Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jun 17th, 2012  |  syntax: None  |  size: 11.22 KB  |  hits: 19  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. ____________________________________
  2. db_schema.xml
  3.  
  4.   1 <?xml version="1.0"?>                                                                                                                                                                                        
  5.   2 <database>
  6.   3     <table name="bootstrap" comment="This table is only intended to ever have one record">
  7.   4         <field name="schemaVersion" type="REAL" />
  8.   5         <field name="dbSchema" type="VARCHAR(1048576)" comment="1 MB of text" />
  9.   6     </table>
  10.   7     <table name="Symbol">
  11.   8         <field name="Type"              type="SMALLINT" />
  12.   9         <field name="SymbolSpace"       type="INTEGER" />
  13.  10     </table>
  14.  11     <table name="PrimitiveSymbolToContent">
  15.  12         <field name="SymbolID"          type="INTEGER" ref_target="Symbol(SymbolID)" />
  16.  13         <field name="Content"           type="VARCHAR(1024)" />
  17.  14     </table>
  18.  15     <table name="SymbolComposition">
  19.  16         <field name="CompositeSymbolID" type="INTEGER" ref_target="Symbol(SymbolID)" />
  20.  17         <field name="ComponentID"       type="INTEGER" ref_target="Symbol(SymbolID)" />
  21.  18     </table>
  22.  19     <table name="Memory">
  23.  20         <field name="Starttime"         type="timestamp" />
  24.  21         <field name="Duration"          type="timestamp" />
  25.  22     </table>
  26.  23     <table name="MemoryFrame">
  27.  24         <field name="MemoryID"          type="INTEGER" ref_target="Memory(MemoryID)" />
  28.  25         <field name="SequenceIndex"     type="INTEGER" />
  29.  26         <field name="TimeOffset"        type="INTERVAL" />
  30.  27     </table>
  31.  28     <table name="MemorySymbol">
  32.  29         <field name="MemoryFrameID"     type="INTEGER" ref_target="MemoryFrame(MemoryFrameID)" />
  33.  30         <field name="SymbolID"          type="INTEGER" ref_target="Symbol(SymbolID)" />
  34.  31         <field name="Activation"        type="REAL" />
  35.  32     </table>
  36.  33     <table name="Recommendation">
  37.  34         <field name="RecommenderID"     type="INTEGER" ref_target="Symbol(SymbolID)" />
  38.  35         <field name="RecommendeeID"     type="INTEGER" ref_target="Symbol(SymbolID)" />
  39.  36         <field name="Probability"       type="REAL" />
  40.  37     </table>
  41.  38 </database>
  42.  
  43. ____________________________________
  44. DBSchema.py
  45.  
  46. #!/usr/bin/env python3.1
  47. # Created: 9:30 PM 2/1/11
  48. #
  49.  
  50. import xml.dom.minidom;
  51. import os
  52. import collections
  53. import sys
  54.  
  55. import postgresql
  56.  
  57. import utils
  58.  
  59. class DBSchema:
  60.     instance = None
  61.     @staticmethod
  62.     def getInstance():
  63.         if DBSchema.instance == None:
  64.             DBSchema.instance = DBSchema()
  65.         return DBSchema.instance
  66.     def __init__(self):
  67.         self.dom = None
  68.         self.tables = []
  69.         self.schema = ''
  70.         self.loadSchema()
  71.     def loadSchema(self):
  72.         f = open('db_schema.xml', 'r')
  73.         self.schema  = '\n'.join(f.readlines())
  74.         f.close()
  75.         self.parseSchema(self.schema )
  76.     def parseSchema(self, xmlStr):
  77.         ## Parse the xml and generate a dictionary describing the database
  78.         #print("schema '{0}'".format(xmlStr))
  79.         self.dom = xml.dom.minidom.parseString(xmlStr)
  80.         dbNode = self.dom.getElementsByTagName("database")[0]
  81.         self.tables = []
  82.         for tableNode in dbNode.getElementsByTagName("table"):
  83.             # For each table
  84.             tableAttr = tableNode.attributes
  85.  
  86.             fields = collections.OrderedDict()
  87.  
  88.             for field in tableNode.getElementsByTagName("field"):
  89.                 # For each column in this table
  90.                 fieldAttr = field.attributes
  91.                 fieldName = fieldAttr['name'].value
  92.  
  93.                 fields[fieldName] = {}
  94.                 fields[fieldName]['type'] = fieldAttr['type'].value
  95.  
  96.                 if "ref_target" in fieldAttr.keys():
  97.                     fields[fieldName]['ref_target'] = fieldAttr['ref_target'].value
  98.                 if "bootstrap" == tableAttr['name'].value:
  99.                     self.schemaVersion = float(5)
  100.  
  101.             self.tables += [{'table-name':tableAttr['name'].value, 'fields':fields}]
  102.     def dropCmd(self):
  103.         return "DROP TABLE " + ", ".join(map(lambda el: el['table-name'], self.tables))
  104.     def createCmd(self):
  105.         sql = "BEGIN;\n"
  106.  
  107.         for table in self.tables:
  108.             # For each table
  109.             tableName = table['table-name']
  110.             sql += "\nCREATE TABLE %s\n" % (tableName)
  111.             sql += "(\n"
  112.             sql += "\t{0}ID SERIAL ".format(tableName)
  113.             for fieldName in table['fields'].keys():
  114.                 # For each column
  115.                 field = table['fields'][fieldName]
  116.                 sql += ",\n\t%s %s" % (fieldName, field['type'])
  117. #                if 'ref_target' in field.keys():
  118. #                    sql += ' REFERENCES %s' % field['ref_target']
  119.             sql += "\n);"
  120.  
  121.         sql += "\nCOMMIT;"
  122.  
  123.         return sql
  124.     def getFieldNamesFromTable(self, tableName):
  125.         return [tableName + 'ID'] + utils.iterToList(utils.iterToList(filter(lambda table: table['table-name'] == tableName,
  126.                       self.tables))[0]['fields'].keys())
  127.     def fillBootstrapTable(self):
  128.         sql = "INSERT INTO bootstrap (schemaVersion, dbSchema) VALUES ({0}, {1});".format(self.schemaVersion, self.schema)
  129.  
  130. def getInstance():
  131.     return DBSchema.getInstance()
  132.  
  133. if __name__ == '__main__':
  134.     dbs = DBSchema()
  135.     dbs.loadSchema()
  136.     if '--create-cmd' in sys.argv:
  137.         print(dbs.createCmd())
  138.     elif '--drop-cmd' in sys.argv:
  139.         print(dbs.dropCmd())
  140.     else:
  141.         print("usage: {0} --create-cmd | --drop-cmd".format(sys.argv[0]))
  142.  
  143.  
  144. ____________________________________
  145. DBInterface.py
  146.  
  147.  
  148. #!/usr/bin/env python3.1
  149. #Created: 6:59 PM, 2/2/11
  150.  
  151. import postgresql
  152.  
  153. import DBSchema
  154. import utils
  155. import misc
  156.  
  157. class DBInterface:
  158.     def __init__(self):
  159.         self.db_connection = None
  160.         self.setParams('alpha', 'josh', 'secret', 'localhost', '5432')
  161.     def setParams(self, dbname, username, password, server, port):
  162.         self.dbname = dbname
  163.         self.username = username
  164.         self.password = password
  165.         self.server = server
  166.         self.port = port
  167.     def getFields(self):
  168.         self.connect()
  169.         bootstrapFields = ['bootstrapID','schemaVersion','dbSchema']
  170.         sql = "SELECT * FROM bootstrap;"
  171.         result = self.query(sql)
  172.         print("getting fields")
  173.         for row in result:
  174.             i = 0
  175.             for col in row:
  176.                 if bootstrapFields[i] == 'dbSchema':
  177.                     DBSchema.getInstance().parseSchema(str(col))
  178.                 i += 1
  179.     def connect(self):
  180.         if self.db_connection == None:
  181.             self.db_connection = postgresql.open('pq://{0}:{1}@{2}:{3}/{4}'.format(self.username, self.password, self.server, self.port, self.dbname))
  182.     def query(self, queryString):
  183.         if self.db_connection == None:
  184.             raise Exception("Cannot execute query; no database connection")
  185.         return self.db_connection.prepare(queryString)
  186.     def createAccessors(self):
  187.         for fieldName in self.fieldNames:
  188.             # For each field name
  189.             setattr(self, fieldName, None)
  190.  
  191. class DBEntryReader(DBInterface):
  192.     """Abstract base class for table entry readers; subclasses will"""
  193.     """be auto-generated from the database schema"""
  194.     def __init__(self, table):
  195.         DBInterface.__init__(self)
  196.         self.table = table
  197.         self.filterList = {}
  198.         self.fieldNames = []
  199.         self.results = None
  200.         self.fieldNames = DBSchema.getInstance().getFieldNamesFromTable(self.table)
  201.         self.createAccessors()
  202.         self.createFilterFunctions()
  203.     def createFilterFunctions(self):
  204.         for fieldName in self.fieldNames:
  205.             # For each field name
  206.             def makeF(fname):
  207.                 def f(val):
  208.                     if type(val).__name__ == 'list':
  209.                         self.filterList[fname] = val
  210.                     else:
  211.                         self.filterList[fname] = [val]
  212.                 return f
  213.             setattr(self, 'FilterBy' + fieldName, makeF(fieldName))
  214.     def Execute(self):
  215.         queryString = "SELECT * FROM %s" % self.table
  216.         filterCount = len(self.filterList)
  217.         if filterCount > 0:
  218.             queryString += " WHERE "
  219.             idx = 0
  220.             for col in self.filterList.keys():
  221.                 # For each set of filter criteria
  222.                 valList = self.filterList[col]
  223.                 queryString += col + ' in (' +\
  224.                     ", ".join(utils.iterToList(map(lambda val: str(val), valList)))\
  225.                     + ')'
  226.                 idx += 1
  227.                 if idx < filterCount:
  228.                     queryString += " AND "
  229.         queryString += ';'
  230.  
  231.         self.connect()
  232.         #for r in self.results:
  233.         #    i = 0
  234.         #    for c in r:
  235.         #        setattr(self, self.fieldNames[i], c)
  236.         #        i += 1
  237.         print("queryString", queryString)
  238.  
  239.         self.results = self.query(queryString)
  240.         self.recordGenerator = self.RecordGenerator()
  241.     def RecordGenerator(self):
  242.         try:
  243.             for r in self.results:
  244.                 i = 0
  245.                 for c in r:
  246.                     setattr(self, self.fieldNames[i], c)
  247.                     i += 1
  248.                 yield True
  249.             yield False
  250.         finally:
  251.             pass
  252.     def Read(self):
  253.         return next(self.recordGenerator)
  254.     def Close(self):
  255.         self.recordGenerator.close()
  256.         self.results.close()
  257.         self.db_connection.close()
  258.  
  259. class DBEntryCreator(DBInterface):
  260.     """Abstract base class for table entry creators; subclasses will"""
  261.     """be auto-generated from the database schema"""
  262.     def __init__(self, table):
  263.         DBInterface.__init__(self)
  264.         self.table = table
  265.         self.fieldNames = DBSchema.getInstance().getFieldNamesFromTable(self.table)
  266.         self.createAccessors()
  267.     def Create(self):
  268.         queryString = "INSERT INTO %s " % self.table
  269.         fields = []
  270.         vars = []
  271.         values = []
  272.         i = 1
  273.         for fieldName in self.fieldNames:
  274.             if getattr(self, fieldName) == None:
  275.                 print("continuing on field", fieldName)
  276.                 continue
  277.             print("fieldName {0} = {1}".format(fieldName, getattr(self, fieldName)))
  278.             fields += [fieldName]
  279.             vars += ['$' + str(i)]
  280.             values += [getattr(self, fieldName)]
  281.             i += 1
  282.         print("fields", fields)
  283.         print("vars", vars)
  284.         print("values", values)
  285.         queryString += "(" + ", ".join(fields) + ")"
  286.         queryString += "VALUES "
  287.         queryString += "(" + ", ".join(vars) + ")"
  288.         queryString += ";"
  289.  
  290.         self.connect()
  291.  
  292.         print("query", queryString)
  293.  
  294.         prep  = self.query(queryString)
  295.  
  296.         results = prep.load_rows([tuple(values)])
  297.  
  298.         print("Create result", results)
  299.  
  300. if __name__ == '__main__':
  301.     dbRdr = DBEntryReader('Symbol')
  302.     dbRdr.FilterByType([0,2])
  303.     dbRdr.FilterBySymbolID([1,2,3])
  304.     dbRdr.Execute()
  305.     while dbRdr.Read():
  306.         print('SymbolID', dbRdr.SymbolID)
  307.         print('Type', dbRdr.Type)
  308.         print('SymbolSpace', dbRdr.SymbolSpace)
  309.     dbRdr.Close()
create a new version of this paste RAW Paste Data