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)