class Creator(object): def __init__(self, db): self.db = db self.logger = Logger(__name__) def drop(self, entity, items): cur = self.db.getCursor() for item in items: self.logger.debug("Dropping {} '{}'.".format(entity.lower(), item)) try: cur.execute("DROP {} IF EXISTS {}".format(entity.upper(), item)) except sqlite3.DatabaseError as e: self.logger.error("Deletion of {} '{}' failed : '{}'".format(entity.lower(), item, str(e))) raise def dropTables(self): cur = self.db.getCursor() self.db.beginTransaction() self.drop("table", TABLES) self.drop("view", VIEWS) self.db.commitTransaction() def createSchema(self): cur = self.db.getCursor() self.executeItems(SCHEMA, "table") self.executeItems(TRIGGER, "trigger") self.insertDefaults(cur) def insertDefaults(self, cur): self.executeItems(DEFAULTS, "default") def createIndices(self): self.executeItems(INDICES, "index") def createMetaData(self): sql = "INSERT OR REPLACE INTO VndbMeta(RID, Schema_Version) VALUES(1, {})".format(VNDB_SCHEMA_VERSION) self.executeItem(sql, "meta-data", transactional = True) def executeItem(self, item, name, cur = None, transactional = False): if not cur: cur = self.db.getCursor() if transactional: self.db.beginTransaction() self.logger.debug("Creating {} '{}'.".format(name, item)) try: res = cur.execute(item) except sqlite3.DatabaseError as e: self.logger.error("Creation of {} '{}' failed : '{}'".format(name, item, str(e))) #raise if transactional: self.db.commitTransaction() def executeItems(self, items, name): cur = self.db.getCursor() self.db.beginTransaction() for item in items: self.executeItem(item, name, cur) self.db.commitTransaction()
class BaseListener(antlr4.ParseTreeListener): """ """ value = [] def __init__(self, database, logLevel='INFO'): self.db = database self.logger = Logger(__name__, level=logLevel) super(BaseListener, self).__init__() self.session = database.session self.bakery = baked.bakery() self.bake_common_queries() def bake_common_queries(self): self.ATTRIBUTE_DEFINITION_BY_NAME = self.bakery(lambda session: self.session.query(Attribute_Definition).\ filter(Attribute_Definition.name == bindparam('name'))) self.MESSAGE_BY_NAME = self.bakery(lambda session: session.query( Message).filter(Message.name == bindparam('name'))) self.NODE_BY_NAME = self.bakery(lambda session: session.query(Node). filter(Node.name == bindparam('name'))) self.NODE_BY_RID = self.bakery(lambda session: session.query(Node). filter(Node.rid == bindparam('rid'))) self.SIGNAL_BY_NAME = self.bakery(lambda session: session.query( Signal).filter(Signal.name == bindparam('name'))) self.SIGNAL_BY_RID = self.bakery(lambda session: session.query( Signal).filter(Signal.rid == bindparam('rid'))) def log_insertion(self, table_name): self.logger.debug("Inserting values for '{}'.".format(table_name)) def getList(self, attr): return [x for x in attr] if attr else [] def getTerminal(self, attr, default=None): return attr.text if attr else default def getValue(self, attr, default=None): return attr.value if attr else default def exitIntValue(self, ctx): if ctx.i: ctx.value = toInt(ctx.i.text, 10) elif ctx.h: ctx.value = toInt(ctx.h.text, 16) else: ctx.value = None def exitFloatValue(self, ctx): ctx.value = toFloat(ctx.f.text) if ctx.f else None def exitNumber(self, ctx): if ctx.i: value = ctx.i.value elif ctx.f: value = ctx.f.value else: value = None ctx.value = value def exitStringValue(self, ctx): value = ctx.s.text.strip('"') if ctx.s else None ctx.value = value def exitIdentifierValue(self, ctx): value = ctx.i.text if ctx.i else None value = None if value == "<missing C_IDENTIFIER>" else value ctx.value = value def _formatMessage(self, msg, location): return "[{0}:{1}] {2}".format(location.start.line, location.start.column + 1, msg) def _log(self, method, msg, location=None): if location: method(self._formatMessage(msg, location)) else: method(msg) def info(self, msg, location=None): self._log(self.info.warn, msg, location) def warn(self, msg, location=None): self._log(self.logger.warn, msg, location) def error(self, msg, location=None): self._log(self.logger.warn, msg, location) def debug(self, msg, location=None): self._log(self.logger.warn, msg, location)
class Database: """ """ DBC_TEMPLATE = pkgutil.get_data("pydbc", "cgen/templates/dbc.tmpl") def __init__(self, dbname, dbtype="CAN", template=None, inMemory=False, logLevel='INFO'): """ """ self.logger = Logger("api.database", level=logLevel) self.logger.debug("Initializing Sqlite3 database '{}'".format(dbname)) self.dbtype = dbtype # TODO: check. self.db = CanDatabase(dbname, logLevel=logLevel, inMemory=inMemory) creator = Creator(self.db) self.queries = Queries(self.db) creator.createSchema() creator.createIndices() self.db.beginTransaction() def __str__(self): return "{}(name: {})".format(self.__class__.__name__, self.dbname) def __del__(self): self.close() @property def dbname(self): return self.db.dbname def name(self): return self.db.name def close(self): self.db.close() def getCursor(self): return self.db.getCursor() def insertOrReplaceStatement(self, insert, cur, tname, columns, *values): """ """ verb = "INSERT" if insert else "REPLACE" try: placeholder = ','.join("?" * len(values)) stmt = "{} INTO {}({}) VALUES({})".format(verb, tname, columns, placeholder) #result = cur.execute(stmt, [*values]) result = cur.execute(stmt, flatten(*values)) return cur.lastrowid except sqlite3.DatabaseError as e: excText = str(e) msg = "{} - Table: '{}'; Data: {}".format(excText, tname, values) self.logger.debug(msg) if excText.startswith("UNIQUE constraint failed:"): ii = excText.find(":") raise DuplicateKeyError( "Table: '{}'; Key-Column: '{}'; Data: {}".format( tname, excText[ii + 2:], values)) from None else: raise def insertStatement(self, cur, tname, columns, *values): """ """ return self.insertOrReplaceStatement(True, cur, tname, columns, *values) def replaceStatement(self, cur, tname, columns, *values): """ """ return self.insertOrReplaceStatement(False, cur, tname, columns, *values) def addCANCluster(self, name, comment): """ """ def addLINCluster(self, name, comment): """ """ def writeDbcFile(self, network=None, filename=None): """ """ pass def outputDbc(self, network, filename=None): """ """ dbcData = self.renderDbc(network) if filename: with open(os.fsencode(filename), "w", encoding="latin-1", newline="\r\n") as outf: outf.write(dbcData) else: print(dbcData) def renderDbc(self, network): """ """ namespace = dict(db=self.queries) res = renderTemplateFromText(self.DBC_TEMPLATE, namespace, formatExceptions=True) return res ## ## Attribute stuff. ## def addAttributeDefinition(self, name, objectType, valueType, defaultValue, limits, enumValues=None, comment=None): cur = self.getCursor() minimum = limits.min maximum = limits.max if valueType in (ValueType.FLOAT, ValueType.INT, ValueType.FLOAT): # TODO: type-check! defaultNumber = defaultValue defaultString = None else: defaultString = defaultValue defaultNumber = None self.insertStatement( cur, "Attribute_Definition", """Name, Objecttype, Valuetype, Minimum, Maximum, Enumvalues, Default_Number, Default_String, Comment""", name, objectType, valueType, minimum, maximum, enumValues, defaultNumber, defaultString, comment) return self.attributeDefinition(name) def attributeDefinitions(self, glob=None, regex=None): """ """ for item in self._searchTableForName("Attribute_Definition", glob, regex): yield AttributeDefinition.create(self.db, item) def attributeDefinition(self, name): """ """ cur = self.db.getCursor() where = "Name = '{}'".format(name) item = self.db.fetchSingleRow(cur, tname="Attribute_Definition", column="*", where=where) if item: return AttributeDefinition.create(self.db, item) else: return None def applicableAttributes(self, objectType): """ """ if not objectType in AttributeType.__members__.values(): self.logger.error( "applicableAttributes(): invalid objecttype '{}'.".format( objectType)) return None cur = self.db.getCursor() yield from self.db.fetchFromTable( cur, "Attribute_Definition", where="objecttype = {}".format(objectType)) def singleAttribute(self, objectType, name): """ """ if not objectType in AttributeType.__members__.values(): self.logger.error( "applicableAttributes(): invalid objecttype '{}'.".format( objectType)) return None cur = self.db.getCursor() return self.db.fetchSingleRow( cur, "Attribute_Definition", column="*", where="objecttype = {} AND Name = '{}'".format(objectType, name)) def attributeValue(self, oid, attrDef): cur = self.db.getCursor() return self.db.fetchSingleRow( cur, "Attribute_Value", column="*", where="Object_ID = {} AND Attribute_Definition = {}".format( oid, attrDef)) ## ## EnvVar Stuff. ## def addEnvVar(self, name, vartype, valueRange, unit, initialValue=None, accessNodes=None): """ """ pass def envVar(self, name): """ """ cur = self.db.getCursor() where = "Name = '{}'".format(name) item = self.db.fetchSingleRow(cur, tname="EnvVar", column="*", where=where) if item: return EnvVar(self, item['RID'], item['Name'], EnvVarType(item['Type']), item['Unit'], EnvVarAccessType(item['Access'] & 0x0f), item['Size'], item['Startup_Value'], Limits(item['Minimum'], item['Maximum']), item['Comment']) else: return None def envVars(self, glob=None, regex=None): """ """ for item in self._searchTableForName("EnvVar", glob, regex): yield EnvVar(self, item['RID'], item['Name'], EnvVarType(item['Type']), item['Unit'], EnvVarAccessType(item['Access'] & 0x0f), item['Size'], item['Startup_Value'], Limits(item['Minimum'], item['Maximum']), item['Comment']) ## ## Message stuff. ## def addMessage(self, name, identifier, size, comment=None): """Add a new message to database. Parameters ---------- name : str Message name needs to unique within the database and a valid C identifier. identifier : int CAN identifier. size : int Size of message in bytes. comment : str Arbitrary description of the node. Returns ------- `pydbc.api.message.Message` newly created Message object on success else None. Raises ------ `pydbc.exceptions.DuplicateKeyError` If a message with the same name already exists. """ cur = self.getCursor() self.insertStatement(cur, "Message", "Name, Message_ID, DLC, Comment", name, identifier, size, comment) return self.message(name) def messages(self, glob=None, regex=None): """ """ for item in self._searchTableForName("Message", glob, regex): yield Message(self, item['RID'], item['Name'], CANAddress(item['Message_ID']), item['DLC'], item['Comment']) def message(self, name): """ """ cur = self.db.getCursor() where = "Name = '{}'".format(name) item = self.db.fetchSingleRow(cur, tname="Message", column="*", where=where) if item: return Message(self, item['RID'], item['Name'], item['Message_ID'], item['DLC'], item['Comment']) else: return None def messageSignal(self, messageId, signalId): cur = self.db.getCursor() where = "Message = {} AND Signal = {}".format(messageId, signalId) return self.db.fetchSingleRow(cur, tname="Message_Signal", column="*", where=where) ## ## Node stuff. ## def addNode(self, name, comment=None): """Add a Node to the database. Parameters ---------- name : str Node name needs to unique within the database and a valid C identifier. comment : str Arbitrary description of the node. Returns ------- `pydbc.api.node.Node` newly created Node object on success else None. """ cur = self.getCursor() self.insertStatement(cur, "Node", "Name, Comment", name, comment) return self.node(name) def nodes(self, glob=None, regex=None): """ """ for item in self._searchTableForName("Node", glob, regex): yield Node(self, item['RID'], item['Name'], item['Comment']) def node(self, name): """ """ cur = self.db.getCursor() where = "Name = '{}'".format(name) item = self.db.fetchSingleRow(cur, tname="Node", column="*", where=where) if item: return Node(self, item['RID'], item['Name'], item['Comment']) else: return None ### def addCategory(self): """ """ pass def addValuetable(self): """ """ pass ## ## Misc. stuff. ## def _searchTableForName(self, tableName, glob=None, regex=None, additionalWhereClause=None): """ """ if glob is None and regex is None: where = None elif glob is not None: where = "Name GLOB '{}'".format(glob) elif regex is not None: where = "Name REGEXP '{}'".format(regex) if where is not None and additionalWhereClause is not None: where = "{} AND {}".format(where, additionalWhereClause) cur = self.db.getCursor() return self.db.fetchFromTable(cur, tableName, where=where)
class CanDatabase(object): """ """ def __init__(self, filename = ":memory:", inMemory = False, cachesize = 4, logLevel = 'INFO'): self._name,_ = os.path.splitext(filename) if inMemory: self._dbname = ":memory:" else: if not filename.lower().endswith(DB_EXTENSION): self._dbname = "{}.{}".format(filename, DB_EXTENSION) else: self._dbname = filename self._filename = filename self.conn = sqlite3.connect(self.dbname, isolation_level = None) self.conn.create_function("REGEXP", 2, regexer) self.conn.isolation_level = None self.cachesize = cachesize self.setPragmas() self._filename = filename self.logger = Logger('db', level = logLevel) def __del__(self): self.conn.close() def close(self): self.conn.close() @property def name(self): return self._name @property def dbname(self): return self._dbname @property def filename(self): return self._filename def getCursor(self): return self.conn.cursor() def setPragma(self, cur, key, value): cur.execute("PRAGMA {} = {}".format(key, value)) def setPragmas(self): cur = self.getCursor() self.setPragma(cur, "FOREIGN_KEYS", "ON") self.setPragma(cur, "PAGE_SIZE", "{}".format(PAGE_SIZE)) self.setPragma(cur, "CACHE_SIZE", "{}".format(calculateCacheSize(self.cachesize * 1024 * 1024))) self.setPragma(cur, "SYNCHRONOUS", "OFF") # FULL self.setPragma(cur, "LOCKING_MODE", "EXCLUSIVE") # NORMAL self.setPragma(cur, "TEMP_STORE", "MEMORY") # FILE """ #self.cur.execute('PRAGMA journal_mode = MEMORY') # TRUNCATE #self.cur.execute('PRAGMA journal_mode = WAL') """ def beginTransaction(self): self.conn.execute("BEGIN TRANSACTION") def commitTransaction(self): self.conn.commit() def rollbackTransaction(self): self.conn.rollback() def createDictFromRow(self, row, description): names = [d[0] for d in description] di = dict(zip(names, row)) return di def fetchSingleRow(self, cur, tname, column, where): cur.execute("""SELECT {} FROM {} WHERE {}""".format(column, tname, where)) row = cur.fetchone() if row is None: return [] return self.createDictFromRow(row, cur.description) def fetchSingleValue(self, cur, tname, column, where): cur.execute("""SELECT {} FROM {} WHERE {}""".format(column, tname, where)) result = cur.fetchone() if result is None: return None return result[0] def updateStatement(self, cur, tname, columns, where, *values): columns = [c.strip() for c in columns.split(",")] colStmt = ', '.join(["{} = ?".format(c) for c in columns]) sql = "UPDATE OR FAIL {} SET {} WHERE {}".format(tname, colStmt, where) try: res = cur.execute(sql, *values) except sqlite3.DatabaseError as e: excText = str(e) msg = "{} - Table: '{}'; Data: {}".format(excText, tname, values) self.logger.debug(msg) if excText.startswith("UNIQUE constraint failed:"): ii = excText.find(":") raise DuplicateKeyError("Table: '{}'; Key-Column: '{}'; Data: {}".format(tname, excText[ii + 2 : ], values)) from None else: raise def insertOrReplaceStatement(self, insert, cur, tname, columns, *values): """ """ verb = "INSERT OR FAIL" if insert else "REPLACE" try: placeholder = ','.join("?" * len(values)) stmt = "{} INTO {}({}) VALUES({})".format(verb, tname, columns, placeholder) cur.execute(stmt, flatten(*values)) #cur.execute(stmt, *values) except sqlite3.DatabaseError as e: msg = "{} - Data: {}".format(str(e), values) self.logger.error(msg) return None else: return cur.lastrowid def insertStatement(self, cur, tname, columns, *values): return self.insertOrReplaceStatement(True, cur, tname, columns, *values) def replaceStatement(self, cur, tname, columns, *values): return self.insertOrReplaceStatement(False, cur, tname, columns, *values) def fetchFromTable(self, cur, tname, columns = None, where = None, orderBy = None): whereClause = "" if not where else "WHERE {}".format(where) orderByClause = "ORDER BY rowid" if not orderBy else "ORDER BY {}".format(orderBy) columnsClause = columns if columns else "*" result = cur.execute("""SELECT {} FROM {} {} {}""".format(columnsClause, tname, whereClause, orderByClause), []) while True: row = cur.fetchone() if row is None: return else: yield self.createDictFromRow(row, cur.description)