def __init__(self, connection, table_prefix=None, **kwargs):
"""Initialise the tagging service.
:param connection: dictionary object containing the database
connection details
:param table_prefix: optional prefix to be used for all tag tables
:param kwargs: additional keyword arguments. (optional identity and
topic_replace_list used by parent classes)
"""
super(SQLiteTaggingService, self).__init__(**kwargs)
self.connection = connection
self.tags_table = "tags"
self.tag_refs_table = "tag_refs"
#self.units_table = "units" #in version 2
self.categories_table = "categories"
self.topic_tags_table = "topic_tags"
self.category_tags_table = "category_tags"
if table_prefix:
self.tags_table = table_prefix + "_" + self.tags_table
self.tag_refs_table = table_prefix + "_" + self.tag_refs_table
self.categories_table = table_prefix + "_" + self.categories_table
self.topic_tags_table = table_prefix + "_" + self.topic_tags_table
self.category_tags_table = table_prefix + "_" + \
self.category_tags_table
self.sqlite_utils = SqlLiteFuncts(self.connection['params'], None)
def __init__(self, connection, table_prefix=None, **kwargs):
"""Initialise the tagging service.
:param connection: dictionary object containing the database
connection details
:param table_prefix: optional prefix to be used for all tag tables
:param kwargs: additional keyword arguments. (optional identity and
topic_replace_list used by parent classes)
"""
super(SQLiteTaggingService, self).__init__(**kwargs)
self.connection = connection
self.tags_table = "tags"
self.tag_refs_table = "tag_refs"
#self.units_table = "units" #in version 2
self.categories_table = "categories"
self.topic_tags_table = "topic_tags"
self.category_tags_table = "category_tags"
if table_prefix:
self.tags_table = table_prefix + "_" + self.tags_table
self.tag_refs_table = table_prefix + "_" + self.tag_refs_table
self.categories_table = table_prefix + "_" + self.categories_table
self.topic_tags_table = table_prefix + "_" + self.topic_tags_table
self.category_tags_table = table_prefix + "_" + \
self.category_tags_table
self.sqlite_utils = SqlLiteFuncts(self.connection['params'], None)
def sqlitefuncts_db_not_initialized():
global CONNECT_PARAMS
table_names = {
"data_table": DATA_TABLE,
"topics_table": TOPICS_TABLE,
"meta_table": META_TABLE,
"agg_topics_table": AGG_TOPICS_TABLE,
"agg_meta_table": AGG_META_TABLE,
}
client = SqlLiteFuncts(CONNECT_PARAMS, table_names)
yield client
# Teardown
if os.path.isdir("./data"):
files = glob.glob("./data/*", recursive=True)
for f in files:
os.remove(f)
os.rmdir("./data/")
if __name__ == "__main__":
from volttron.platform.dbutils import mongoutils
from volttron.platform.dbutils.sqlitefuncts import SqlLiteFuncts
tags = {'tag1': 'str', 'tag2': 'str', 'tag3': 'str', 'campusRef': 'ref',
'campus':'str', 'siteRef':'ref', 'site':'str'}
tag_refs= {'siteRef': 'site', 'campusRef': 'campus'}
query = 'tag1 OR tag2 AND (tag3 OR tag4) OR tag2 LIKE "a.*"'
query = 'tag1 AND tag2 OR tag4'
query = 'tag1 AND NOT (tag3>1 AND tag2>2 OR tag4<2)'
query = 'tag1 AND NOT(tag3="value1" OR tag1>2 AND tag3 LIKE "a.*b")'
query = 'campusRef.tag1 = 2 OR siteRef.tag2=3 AND tag3'
query = 'NOT(campusRef.tag1 = 2 OR siteRef.tag2=3) AND tag3'
ast = parse_query(query, tags, tag_refs)
print("USER QUERY:\n{}".format(query))
print("pretty print:\n{}".format(pretty_print(ast)))
# print ("MONGO QUERY AND SUB QUERIES")
# sub = list()
# c = mongoutils.get_tagging_queries_from_ast(ast, tag_refs, sub)
# print("Main query: {}".format(c))
# print("Sub query:{}".format(sub))
print ("SQLITE QUERY:")
print(SqlLiteFuncts.get_tagging_query_from_ast("topic_tags", ast,
tag_refs))
class SQLiteTaggingService(BaseTaggingService):
"""This is a tagging service agent that writes data to a SQLite database.
"""
def __init__(self, connection, table_prefix=None, **kwargs):
"""Initialise the tagging service.
:param connection: dictionary object containing the database
connection details
:param table_prefix: optional prefix to be used for all tag tables
:param kwargs: additional keyword arguments. (optional identity and
topic_replace_list used by parent classes)
"""
super(SQLiteTaggingService, self).__init__(**kwargs)
self.connection = connection
self.tags_table = "tags"
self.tag_refs_table = "tag_refs"
#self.units_table = "units" #in version 2
self.categories_table = "categories"
self.topic_tags_table = "topic_tags"
self.category_tags_table = "category_tags"
if table_prefix:
self.tags_table = table_prefix + "_" + self.tags_table
self.tag_refs_table = table_prefix + "_" + self.tag_refs_table
self.categories_table = table_prefix + "_" + self.categories_table
self.topic_tags_table = table_prefix + "_" + self.topic_tags_table
self.category_tags_table = table_prefix + "_" + \
self.category_tags_table
self.sqlite_utils = SqlLiteFuncts(self.connection['params'], None)
@doc_inherit
def setup(self):
"""
Read resource files and load list of valid tags, categories,
tags grouped by categories, list of reference tags and its
parent.
:return:
"""
_log.debug("Setup of sqlite tagging agent")
err_message = ""
table_names = []
try:
stmt = "SELECT name FROM sqlite_master " \
"WHERE type='table';"
name_list = self.sqlite_utils.select(stmt, None, fetch_all=True)
table_names = [name[0] for name in name_list]
_log.debug(table_names)
except Exception as e:
err_message = "Unable to query list of existing tables from the " \
"database. Exception: {}. Stopping tagging " \
"service agent".format(e.args)
table_name = ""
try:
table_name = self.tags_table
if self.tags_table in table_names:
_log.info("{} table exists. Assuming initial values have been "
"loaded".format(table_name))
else:
self._init_tags()
table_name = self.tag_refs_table
if self.tag_refs_table in table_names:
_log.info("{} table exists. Assuming initial values have been "
"loaded".format(table_name))
else:
self._init_tag_refs()
table_name = self.topic_tags_table
if self.topic_tags_table in table_names:
_log.info("{} table exists. Assuming initial values "
"have been loaded".format(table_name))
else:
self._init_topic_tags()
table_name = self.categories_table
if self.categories_table in table_names:
_log.info("{} table exists. Assuming initial values "
"have been loaded".format(table_name))
else:
self._init_categories()
table_name = self.category_tags_table
if self.category_tags_table in table_names:
_log.info("{} table exists. Assuming initial values "
"have been loaded".format(table_name))
else:
self._init_category_tags()
except Exception as e:
err_message = "Initialization of " + table_name + \
" table failed with exception: {}" \
"Stopping tagging service agent. ".format(str(e))
if err_message:
_log.error(err_message)
self.vip.health.set_status(
STATUS_BAD, "Initialization of tagging service "
"failed")
status = Status.from_json(self.vip.health.get_status_json())
self.vip.health.send_alert(TAGGING_SERVICE_SETUP_FAILED, status)
self.core.stop()
@doc_inherit
def load_valid_tags(self):
# Now cache list of tags and kind/type for validation during insert
cursor = self.sqlite_utils.select("SELECT name, kind from " +
self.tags_table,
fetch_all=False)
for record in cursor:
self.valid_tags[record[0]] = record[1]
@doc_inherit
def load_tag_refs(self):
# Now cache ref tags and its parent
cursor = self.sqlite_utils.select("SELECT tag, parent from " +
self.tag_refs_table,
fetch_all=False)
for record in cursor:
self.tag_refs[record[0]] = record[1]
def _init_tags(self):
file_path = self.resource_sub_dir + '/tags.csv'
_log.debug("Loading file :" + file_path)
self.sqlite_utils.execute_stmt("CREATE TABLE {}"
"(name VARCHAR PRIMARY KEY, "
"kind VARCHAR NOT NULL, "
"description VARCHAR)".format(
self.tags_table))
to_db = []
with open(file_path, 'r', encoding='utf-8') as content_file:
dr = csv.DictReader(content_file)
for row in dr:
to_db.append((row['name'], row['kind'], row['description']))
self.sqlite_utils.execute_many(
"INSERT INTO {} (name, kind, description) "
"VALUES (?, ?, ?);".format(self.tags_table), to_db)
self.sqlite_utils.commit()
def _init_tag_refs(self):
file_path = self.resource_sub_dir + '/tag_refs.csv'
_log.debug("Loading file :" + file_path)
self.sqlite_utils.execute_stmt("CREATE TABLE {} "
"(tag VARCHAR NOT NULL, "
" parent VARCHAR NOT NULL,"
"PRIMARY KEY (tag, parent))".format(
self.tag_refs_table))
with open(file_path, 'r') as content_file:
csv_str = content_file.read()
# csv.DictReader uses first line in file for column headings
# by default
dr = csv.DictReader(csv_str.splitlines()) # comma is default delimiter
to_db = [(i['tag'], i['parent_tag']) for i in dr]
self.sqlite_utils.execute_many(
"INSERT INTO {} (tag, parent) "
"VALUES (?, ?);".format(self.tag_refs_table), to_db)
self.sqlite_utils.commit()
def _init_categories(self):
file_path = self.resource_sub_dir + '/categories.csv'
_log.debug("Loading file :" + file_path)
self.sqlite_utils.execute_stmt("CREATE TABLE {}"
"(name VARCHAR PRIMARY KEY NOT NULL,"
"description VARCHAR)".format(
self.categories_table))
_log.debug("created categories table")
with open(file_path, 'r') as content_file:
csv_str = content_file.read()
dr = csv.DictReader(csv_str.splitlines())
to_db = [(i['name'], i['description']) for i in dr]
_log.debug("Categories in: {}".format(to_db))
self.sqlite_utils.execute_many(
"INSERT INTO {} (name, description) "
"VALUES (?, ?);".format(self.categories_table), to_db)
self.sqlite_utils.commit()
def _init_category_tags(self):
file_path = self.resource_sub_dir + '/category_tags.txt'
_log.debug("Loading file :" + file_path)
self.sqlite_utils.execute_stmt("CREATE TABLE {} "
"(category VARCHAR NOT NULL,"
"tag VARCHAR NOT NULL,"
"PRIMARY KEY (category, tag))".format(
self.category_tags_table))
_log.debug("created {} table".format(self.category_tags_table))
# TODO look for decoding issues here
with open(file_path, 'r', encoding='utf-8') as content_file:
txt_str = content_file.read()
to_db = []
if txt_str:
current_category = ""
tags = set()
for line in txt_str.splitlines():
if not line or line.startswith("##"):
continue
if line.startswith("#") and line.endswith("#"):
new_category = line.strip()[1:-1]
if len(tags) > 0:
to_db.extend([(current_category, x) for x in tags])
current_category = new_category
tags = set()
else:
temp = line.split(":") #ignore description
tags.update(re.split(" +", temp[0]))
# insert last category after loop
if len(tags) > 0:
to_db.extend([(current_category, x) for x in tags])
self.sqlite_utils.execute_many(
"INSERT INTO {} (category, tag) "
"VALUES (?, ?);".format(self.category_tags_table), to_db)
self.sqlite_utils.commit()
else:
_log.warning(
"No category to tags mapping to initialize. No such file " +
file_path)
def _init_topic_tags(self):
self.sqlite_utils.execute_stmt(
"CREATE TABLE {} (topic_prefix TEXT NOT NULL, "
"tag STRING NOT NULL, "
"value STRING, "
"PRIMARY KEY (topic_prefix, tag))".format(self.topic_tags_table,
self.tags_table))
self.sqlite_utils.execute_stmt(
"CREATE INDEX IF NOT EXISTS idx_tag ON " + self.topic_tags_table +
"(tag ASC);")
self.sqlite_utils.commit()
@doc_inherit
def query_categories(self,
include_description=False,
skip=0,
count=None,
order="FIRST_TO_LAST"):
query = '''SELECT name, description FROM ''' \
+ self.categories_table + '''
{order_by}
{limit}
{offset}'''
order_by = ' ORDER BY name ASC'
if order == 'LAST_TO_FIRST':
order_by = ' ORDER BY name DESC'
args = []
# can't have an offset without a limit
# -1 = no limit and allows the user to
# provide just an offset
if count is None:
count = -1
limit_statement = 'LIMIT ?'
args.append(count)
offset_statement = ''
if skip > 0:
offset_statement = 'OFFSET ?'
args.append(skip)
real_query = query.format(limit=limit_statement,
offset=offset_statement,
order_by=order_by)
_log.debug("Real Query: " + real_query)
_log.debug(args)
cursor = self.sqlite_utils.select(real_query, args, fetch_all=False)
result = OrderedDict()
for row in cursor:
_log.debug(row[0])
result[row[0]] = row[1]
_log.debug(list(result.keys()))
_log.debug(list(result.values()))
cursor.close()
if include_description:
return list(result.items())
else:
return list(result.keys())
@doc_inherit
def query_tags_by_category(self,
category,
include_kind=False,
include_description=False,
skip=0,
count=None,
order="FIRST_TO_LAST"):
query = 'SELECT name, kind, description FROM {tag} as t, ' \
'{category_tag} as c ' \
'WHERE ' \
't.name = c.tag AND c.category = "{category}" ' \
'{order_by} ' \
'{limit} ' \
'{offset}'
order_by = 'ORDER BY name ASC'
if order == 'LAST_TO_FIRST':
order_by = ' ORDER BY name DESC'
args = []
_log.debug("After orderby. skip={}".format(skip))
# can't have an offset without a limit
# -1 = no limit and allows the user to
# provide just an offset
if count is None:
count = -1
limit_statement = 'LIMIT ?'
args.append(count)
offset_statement = ''
if skip > 0:
offset_statement = 'OFFSET ?'
args.append(skip)
_log.debug("before real querye")
real_query = query.format(tag=self.tags_table,
category_tag=self.category_tags_table,
category=category,
limit=limit_statement,
offset=offset_statement,
order_by=order_by)
_log.debug("Real Query: " + real_query)
_log.debug(args)
cursor = None
try:
cursor = self.sqlite_utils.select(real_query,
args,
fetch_all=False)
result = []
for row in cursor:
_log.debug(row[0])
record = [row[0]]
if include_kind:
record.append(row[1])
if include_description:
record.append(row[2])
if include_description or include_kind:
result.append(record)
else:
result.append(row[0])
return result
finally:
if cursor:
cursor.close()
@doc_inherit
def query_tags_by_topic(self,
topic_prefix,
include_kind=False,
include_description=False,
skip=0,
count=None,
order="FIRST_TO_LAST"):
query = 'SELECT name, value, kind, description FROM {tags} as t1, ' \
'{topic_tags} as t2 ' \
'WHERE ' \
't1.name = t2.tag ' \
'AND t2.topic_prefix = "{topic_prefix}" ' \
'{order_by} ' \
'{limit} ' \
'{offset}'
order_by = 'ORDER BY name ASC'
if order == 'LAST_TO_FIRST':
order_by = ' ORDER BY name DESC'
args = []
# can't have an offset without a limit
# -1 = no limit and allows the user to
# provide just an offset
if count is None:
count = -1
limit_statement = 'LIMIT ?'
args.append(count)
offset_statement = ''
if skip > 0:
offset_statement = 'OFFSET ?'
args.append(skip)
_log.debug("before real query")
real_query = query.format(tags=self.tags_table,
topic_tags=self.topic_tags_table,
topic_prefix=topic_prefix,
limit=limit_statement,
offset=offset_statement,
order_by=order_by)
_log.debug("Real Query: " + real_query)
_log.debug(args)
cursor = None
try:
cursor = self.sqlite_utils.select(real_query,
args,
fetch_all=False)
result = []
for row in cursor:
_log.debug(row[0])
record = [row[0], row[1]]
if include_kind:
record.append(row[2])
if include_description:
record.append(row[3])
result.append(record)
return result
finally:
if cursor:
cursor.close()
@doc_inherit
def insert_topic_tags(self, tags, update_version=False):
t = dict()
to_db = []
result = dict()
result['info'] = dict()
result['error'] = dict()
_log.debug("IN INSERT tags {}".format(tags))
for topic_pattern, topic_tags in list(tags.items()):
for tag_name, tag_value in list(topic_tags.items()):
if tag_name not in self.valid_tags:
raise ValueError("Invalid tag name:{}".format(tag_name))
if tag_name == 'id':
_log.warning(
"id tags are auto generated by the system "
"topic_prefix servers as unique identifier for"
"an entity. id value sent({}) will not be "
"stored".format(tag_value))
topic_tags.pop('id', None)
continue
# TODO: Validate and convert values based on tag kind/type
# for example, for Marker tags set value as true even if
# value passed is None.
#tag_value = get_tag_value(tag_value,
# self.valid_tags[tag_name])
_log.debug("topic pattern is {}".format(topic_pattern))
prefixes = self.get_matching_topic_prefixes(topic_pattern)
if not prefixes:
result['error'][topic_pattern] = "No matching topic found"
continue
result['info'][topic_pattern] = []
for prefix in prefixes:
to_db.extend((prefix, t, v) for t, v in topic_tags.items())
# Add id explicitly
to_db.append((prefix, 'id', prefix))
result['info'][topic_pattern].append(prefix)
if len(result['info'][topic_pattern]) == 1 and \
topic_pattern == result['info'][topic_pattern][0]:
# means value sent was actually some pattern so add
# info to tell user the list of topic prefix that matched
# the pattern sent
_log.debug(
"topic passed is exact name. Not pattern. "
"removing from result info: {}".format(topic_pattern))
result['info'].pop(topic_pattern)
if to_db:
self.sqlite_utils.execute_many(
"REPLACE INTO {} (topic_prefix, tag, value) "
"VALUES (?, ?, ?);".format(self.topic_tags_table), to_db)
self.sqlite_utils.commit()
return result
@doc_inherit
def query_topics_by_tags(self, ast, skip=0, count=None, order=None):
query = self.sqlite_utils.get_tagging_query_from_ast(
self.topic_tags_table, ast, self.tag_refs)
order_by = ' \nORDER BY topic_prefix ASC'
if order == 'LAST_TO_FIRST':
order_by = ' \nORDER BY topic_prefix DESC'
# can't have an offset without a limit
# -1 = no limit and allows the user to
# provide just an offset
if count is None:
count = -1
limit_statement = ' \nLIMIT ' + str(count)
offset_statement = ''
if skip > 0:
offset_statement = ' \nOFFSET ' + str(skip)
real_query = query + order_by + limit_statement + offset_statement
_log.error("#Real Query: \n" + real_query)
conn = None
cursor = None
if "REGEXP" in real_query:
cursor, conn = self.sqlite_utils.regex_select(real_query,
None,
fetch_all=False,
cache_size=-4000)
else:
self.sqlite_utils.set_cache(-4000)
cursor = self.sqlite_utils.select(real_query, fetch_all=False)
result = []
if cursor:
result = [r[0] for r in cursor]
cursor.close()
if conn:
conn.close()
_log.debug("#Query result: {}".format(result))
return result
from volttron.platform.dbutils.sqlitefuncts import SqlLiteFuncts
tags = {
'tag1': 'str',
'tag2': 'str',
'tag3': 'str',
'campusRef': 'ref',
'campus': 'str',
'siteRef': 'ref',
'site': 'str'
}
tag_refs = {'siteRef': 'site', 'campusRef': 'campus'}
query = 'tag1 OR tag2 AND (tag3 OR tag4) OR tag2 LIKE "a.*"'
query = 'tag1 AND tag2 OR tag4'
query = 'tag1 AND NOT (tag3>1 AND tag2>2 OR tag4<2)'
query = 'tag1 AND NOT(tag3="value1" OR tag1>2 AND tag3 LIKE "a.*b")'
query = 'campusRef.tag1 = 2 OR siteRef.tag2=3 AND tag3'
query = 'NOT(campusRef.tag1 = 2 OR siteRef.tag2=3) AND tag3'
ast = parse_query(query, tags, tag_refs)
print("USER QUERY:\n{}".format(query))
print("pretty print:\n{}".format(pretty_print(ast)))
# print ("MONGO QUERY AND SUB QUERIES")
# sub = list()
# c = mongoutils.get_tagging_queries_from_ast(ast, tag_refs, sub)
# print("Main query: {}".format(c))
# print("Sub query:{}".format(sub))
print("SQLITE QUERY:")
print(SqlLiteFuncts.get_tagging_query_from_ast("topic_tags", ast,
tag_refs))
class SQLiteTaggingService(BaseTaggingService):
"""This is a tagging service agent that writes data to a SQLite database.
"""
def __init__(self, connection, table_prefix=None, **kwargs):
"""Initialise the tagging service.
:param connection: dictionary object containing the database
connection details
:param table_prefix: optional prefix to be used for all tag tables
:param kwargs: additional keyword arguments. (optional identity and
topic_replace_list used by parent classes)
"""
super(SQLiteTaggingService, self).__init__(**kwargs)
self.connection = connection
self.tags_table = "tags"
self.tag_refs_table = "tag_refs"
#self.units_table = "units" #in version 2
self.categories_table = "categories"
self.topic_tags_table = "topic_tags"
self.category_tags_table = "category_tags"
if table_prefix:
self.tags_table = table_prefix + "_" + self.tags_table
self.tag_refs_table = table_prefix + "_" + self.tag_refs_table
self.categories_table = table_prefix + "_" + self.categories_table
self.topic_tags_table = table_prefix + "_" + self.topic_tags_table
self.category_tags_table = table_prefix + "_" + \
self.category_tags_table
self.sqlite_utils = SqlLiteFuncts(self.connection['params'], None)
@doc_inherit
def setup(self):
"""
Read resource files and load list of valid tags, categories,
tags grouped by categories, list of reference tags and its
parent.
:return:
"""
_log.debug("Setup of sqlite tagging agent")
err_message = ""
table_names = []
try:
stmt = "SELECT name FROM sqlite_master " \
"WHERE type='table';"
name_list = self.sqlite_utils.select(stmt, None, fetch_all=True)
table_names = [name[0] for name in name_list]
_log.debug(table_names)
except Exception as e:
err_message = "Unable to query list of existing tables from the " \
"database. Exception: {}. Stopping tagging " \
"service agent".format(e.args)
table_name = ""
try:
table_name = self.tags_table
if self.tags_table in table_names:
_log.info("{} table exists. Assuming initial values have been "
"loaded".format(table_name))
else:
self._init_tags()
table_name = self.tag_refs_table
if self.tag_refs_table in table_names:
_log.info("{} table exists. Assuming initial values have been "
"loaded".format(table_name))
else:
self._init_tag_refs()
table_name = self.topic_tags_table
if self.topic_tags_table in table_names:
_log.info("{} table exists. Assuming initial values "
"have been loaded".format(table_name))
else:
self._init_topic_tags()
table_name = self.categories_table
if self.categories_table in table_names:
_log.info("{} table exists. Assuming initial values "
"have been loaded".format(table_name))
else:
self._init_categories()
table_name = self.category_tags_table
if self.category_tags_table in table_names:
_log.info("{} table exists. Assuming initial values "
"have been loaded".format(table_name))
else:
self._init_category_tags()
except Exception as e:
err_message = "Initialization of " + table_name + \
" table failed with exception: {}" \
"Stopping tagging service agent. ".format(e.args)
if err_message:
_log.error(err_message)
self.vip.health.set_status(STATUS_BAD,
"Initialization of tagging service "
"failed")
status = Status.from_json(self.vip.health.get_status_json())
self.vip.health.send_alert(TAGGING_SERVICE_SETUP_FAILED, status)
self.core.stop()
@doc_inherit
def load_valid_tags(self):
# Now cache list of tags and kind/type for validation during insert
cursor = self.sqlite_utils.select(
"SELECT name, kind from " + self.tags_table, fetch_all=False)
for record in cursor:
self.valid_tags[record[0]] = record[1]
@doc_inherit
def load_tag_refs(self):
# Now cache ref tags and its parent
cursor = self.sqlite_utils.select(
"SELECT tag, parent from " + self.tag_refs_table, fetch_all=False)
for record in cursor:
self.tag_refs[record[0]] = record[1]
def _init_tags(self):
file_path = self.resource_sub_dir + '/tags.csv'
_log.debug("Loading file :" + file_path)
self.sqlite_utils.execute_stmt("CREATE TABLE {}"
"(name VARCHAR PRIMARY KEY, "
"kind VARCHAR NOT NULL, "
"description VARCHAR)".format(
self.tags_table))
with open(file_path, 'r') as content_file:
csv_str = content_file.read()
# csv.DictReader uses first line in file for column headings
# by default
dr = csv.DictReader(csv_str.splitlines()) # comma is default delimiter
to_db = [(i['name'], i['kind'], i['description'].decode('utf8')) for i in dr]
self.sqlite_utils.execute_many(
"INSERT INTO {} (name, kind, description) "
"VALUES (?, ?, ?);".format(self.tags_table),
to_db)
self.sqlite_utils.commit()
def _init_tag_refs(self):
file_path = self.resource_sub_dir + '/tag_refs.csv'
_log.debug("Loading file :" + file_path)
self.sqlite_utils.execute_stmt(
"CREATE TABLE {} "
"(tag VARCHAR NOT NULL, "
" parent VARCHAR NOT NULL,"
"PRIMARY KEY (tag, parent))".format(self.tag_refs_table))
with open(file_path, 'r') as content_file:
csv_str = content_file.read()
# csv.DictReader uses first line in file for column headings
# by default
dr = csv.DictReader(csv_str.splitlines()) # comma is default delimiter
to_db = [(i['tag'], i['parent_tag']) for i in dr]
self.sqlite_utils.execute_many(
"INSERT INTO {} (tag, parent) "
"VALUES (?, ?);".format(self.tag_refs_table),
to_db)
self.sqlite_utils.commit()
def _init_categories(self):
file_path = self.resource_sub_dir + '/categories.csv'
_log.debug("Loading file :" + file_path)
self.sqlite_utils.execute_stmt(
"CREATE TABLE {}"
"(name VARCHAR PRIMARY KEY NOT NULL,"
"description VARCHAR)".format(self.categories_table))
_log.debug("created categories table")
with open(file_path, 'r') as content_file:
csv_str = content_file.read()
dr = csv.DictReader(csv_str.splitlines())
to_db = [(i['name'], i['description'].decode('utf8')) for i in dr]
_log.debug("Categories in: {}".format(to_db))
self.sqlite_utils.execute_many(
"INSERT INTO {} (name, description) "
"VALUES (?, ?);".format(self.categories_table), to_db)
self.sqlite_utils.commit()
def _init_category_tags(self):
file_path = self.resource_sub_dir + '/category_tags.txt'
_log.debug("Loading file :" + file_path)
self.sqlite_utils.execute_stmt(
"CREATE TABLE {} "
"(category VARCHAR NOT NULL,"
"tag VARCHAR NOT NULL,"
"PRIMARY KEY (category, tag))".format(self.category_tags_table))
_log.debug("created {} table".format(self.category_tags_table))
with open(file_path, 'r') as content_file:
txt_str = content_file.read()
to_db = []
if txt_str:
current_category = ""
tags = set()
for line in txt_str.splitlines():
if not line or line.startswith("##"):
continue
if line.startswith("#") and line.endswith("#"):
new_category = line.strip()[1:-1]
if len(tags) > 0:
to_db.extend([(current_category,x) for x in tags])
current_category = new_category
tags = set()
else:
temp= line.split(":") #ignore description
tags.update(re.split(" +", temp[0]))
# insert last category after loop
if len(tags)>0:
to_db.extend([(current_category, x) for x in tags])
self.sqlite_utils.execute_many(
"INSERT INTO {} (category, tag) "
"VALUES (?, ?);".format(self.category_tags_table), to_db)
self.sqlite_utils.commit()
else:
_log.warn("No category to tags mapping to initialize. No such "
"file " + file_path)
def _init_topic_tags(self):
self.sqlite_utils.execute_stmt(
"CREATE TABLE {} (topic_prefix TEXT NOT NULL, "
"tag STRING NOT NULL, "
"value STRING, "
"PRIMARY KEY (topic_prefix, tag))".format(
self.topic_tags_table, self.tags_table))
self.sqlite_utils.execute_stmt(
"CREATE INDEX IF NOT EXISTS idx_tag ON " +
self.topic_tags_table + "(tag ASC);")
self.sqlite_utils.commit()
@doc_inherit
def query_categories(self, include_description=False, skip=0, count=None,
order="FIRST_TO_LAST"):
query = '''SELECT name, description FROM ''' \
+ self.categories_table + '''
{order_by}
{limit}
{offset}'''
order_by = ' ORDER BY name ASC'
if order == 'LAST_TO_FIRST':
order_by = ' ORDER BY name DESC'
args = []
# can't have an offset without a limit
# -1 = no limit and allows the user to
# provide just an offset
if count is None:
count = -1
limit_statement = 'LIMIT ?'
args.append(count)
offset_statement = ''
if skip > 0:
offset_statement = 'OFFSET ?'
args.append(skip)
real_query = query.format(limit=limit_statement,
offset=offset_statement,
order_by=order_by)
_log.debug("Real Query: " + real_query)
_log.debug(args)
cursor = self.sqlite_utils.select(real_query, args, fetch_all=False)
result = OrderedDict()
for row in cursor:
_log.debug(row[0])
result[row[0]] = row[1]
_log.debug(result.keys())
_log.debug(result.values())
cursor.close()
if include_description:
return result.items()
else:
return result.keys()
@doc_inherit
def query_tags_by_category(self, category, include_kind=False,
include_description=False, skip=0, count=None,
order="FIRST_TO_LAST"):
query = 'SELECT name, kind, description FROM {tag} as t, ' \
'{category_tag} as c ' \
'WHERE ' \
't.name = c.tag AND c.category = "{category}" ' \
'{order_by} ' \
'{limit} ' \
'{offset}'
order_by = 'ORDER BY name ASC'
if order == 'LAST_TO_FIRST':
order_by = ' ORDER BY name DESC'
args = []
_log.debug("After orderby. skip={}".format(skip))
# can't have an offset without a limit
# -1 = no limit and allows the user to
# provide just an offset
if count is None:
count = -1
limit_statement = 'LIMIT ?'
args.append(count)
offset_statement = ''
if skip > 0:
offset_statement = 'OFFSET ?'
args.append(skip)
_log.debug("before real querye")
real_query = query.format(
tag=self.tags_table,
category_tag=self.category_tags_table,
category=category,
limit=limit_statement,
offset=offset_statement,
order_by=order_by)
_log.debug("Real Query: " + real_query)
_log.debug(args)
cursor = None
try:
cursor = self.sqlite_utils.select(real_query, args,
fetch_all=False)
result = []
for row in cursor:
_log.debug(row[0])
record = [row[0]]
if include_kind:
record.append(row[1])
if include_description:
record.append(row[2])
if include_description or include_kind:
result.append(record)
else:
result.append(row[0])
return result
finally:
if cursor:
cursor.close()
@doc_inherit
def query_tags_by_topic(self, topic_prefix, include_kind=False,
include_description=False, skip=0, count=None,
order="FIRST_TO_LAST"):
query = 'SELECT name, value, kind, description FROM {tags} as t1, ' \
'{topic_tags} as t2 ' \
'WHERE ' \
't1.name = t2.tag ' \
'AND t2.topic_prefix = "{topic_prefix}" ' \
'{order_by} ' \
'{limit} ' \
'{offset}'
order_by = 'ORDER BY name ASC'
if order == 'LAST_TO_FIRST':
order_by = ' ORDER BY name DESC'
args = []
# can't have an offset without a limit
# -1 = no limit and allows the user to
# provide just an offset
if count is None:
count = -1
limit_statement = 'LIMIT ?'
args.append(count)
offset_statement = ''
if skip > 0:
offset_statement = 'OFFSET ?'
args.append(skip)
_log.debug("before real query")
real_query = query.format(
tags=self.tags_table, topic_tags=self.topic_tags_table,
topic_prefix=topic_prefix, limit=limit_statement,
offset=offset_statement, order_by=order_by)
_log.debug("Real Query: " + real_query)
_log.debug(args)
cursor = None
try:
cursor = self.sqlite_utils.select(real_query, args,
fetch_all=False)
result = []
for row in cursor:
_log.debug(row[0])
record = [row[0], row[1]]
if include_kind:
record.append(row[2])
if include_description:
record.append(row[3])
result.append(record)
return result
finally:
if cursor:
cursor.close()
@doc_inherit
def insert_topic_tags(self, tags, update_version=False):
t = dict()
to_db =[]
result = dict()
result['info'] = dict()
result['error'] = dict()
_log.debug("IN INSERT tags {}".format(tags))
for topic_pattern, topic_tags in tags.items():
for tag_name, tag_value in topic_tags.items():
if not self.valid_tags.has_key(tag_name):
raise ValueError("Invalid tag name:{}".format(tag_name))
if tag_name == 'id':
_log.warn("id tags are auto generated by the system "
"topic_prefix servers as unique identifier for"
"an entity. id value sent({}) will not be "
"stored".format(tag_value))
topic_tags.pop('id', None)
continue
# TODO: Validate and convert values based on tag kind/type
# for example, for Marker tags set value as true even if
# value passed is None.
#tag_value = get_tag_value(tag_value,
# self.valid_tags[tag_name])
_log.debug("topic pattern is {}".format(topic_pattern))
prefixes = self.get_matching_topic_prefixes(topic_pattern)
if not prefixes:
result['error'][topic_pattern] = "No matching topic found"
continue
result['info'][topic_pattern] = []
for prefix in prefixes:
to_db.extend([(prefix,t,v) for t,v in topic_tags.items()])
# Add id explicitly
to_db.append((prefix, 'id', prefix))
result['info'][topic_pattern].append(prefix)
if len(result['info'][topic_pattern]) == 1 and \
topic_pattern == result['info'][topic_pattern][0]:
# means value sent was actually some pattern so add
# info to tell user the list of topic prefix that matched
# the pattern sent
_log.debug(
"topic passed is exact name. Not pattern. "
"removing from result info: {}".format(topic_pattern))
result['info'].pop(topic_pattern)
if to_db:
self.sqlite_utils.execute_many(
"REPLACE INTO {} (topic_prefix, tag, value) "
"VALUES (?, ?, ?);".format(self.topic_tags_table),
to_db)
self.sqlite_utils.commit()
return result
@doc_inherit
def query_topics_by_tags(self, ast, skip=0, count=None, order=None):
query = self.sqlite_utils.get_tagging_query_from_ast(
self.topic_tags_table, ast, self.tag_refs)
order_by = ' \nORDER BY topic_prefix ASC'
if order == 'LAST_TO_FIRST':
order_by = ' \nORDER BY topic_prefix DESC'
# can't have an offset without a limit
# -1 = no limit and allows the user to
# provide just an offset
if count is None:
count = -1
limit_statement = ' \nLIMIT ' + str(count)
offset_statement = ''
if skip > 0:
offset_statement = ' \nOFFSET ' + str(skip)
real_query = query + order_by + limit_statement + offset_statement
_log.debug("#Real Query: \n" + real_query)
conn = None
cursor = None
if "REGEXP" in real_query:
cursor, conn = self.sqlite_utils.regex_select(real_query, None,
fetch_all=False,
cache_size=-4000)
else:
self.sqlite_utils.set_cache(-4000)
cursor = self.sqlite_utils.select(real_query, fetch_all=False)
result =[]
if cursor:
result = [r[0] for r in cursor]
cursor.close()
if conn:
conn.close()
_log.debug("#Query result: {}".format(result))
return result