def get_tables_in_db():
"""Returns a list of the names of the tables in this database.
NOTE: This function only works for the SQLite engine.
"""
db = connection.get_db()
tables = db.select('sqlite_master', what='name', where='type = "table"')
return list(tables)
def init_db():
db = connection.get_db()
if lookup.db_contains_production_data():
logging.warning('Cowardly refusing to whipe a production db.')
return
delete_all_tables()
create_tables()
fill_tables_with_testdata()
def load_tables_from_csv():
db = connection.get_db()
f = open(get_csv_filename_from_tablename('crawler_rules'), 'r')
csv_reader = csv.DictReader(f)
try:
for d in csv_reader:
insert_crawler_rule(models.CrawlerRules(d))
finally:
f.close()
def get_crawler_rule_by_name(rule_name):
db = connection.get_db()
rules_rs = db.where('crawler_rules', name=rule_name)
rules = list(rules_rs)
if not rules:
return None
# rule_name is a primary index, we should have no duplicates. So rules has
# a length of 0 or 1
assert(len(rules) == 1)
return models.CrawlerRules(**rules[0])
def create_tables():
db = connection.get_db()
db.query('CREATE TABLE "crawler_rules" ('
'"name" TEXT PRIMARY KEY, '
'"url_list_articles" BLOB, '
'"css_article" TEXT, '
'"css_common_properties" TEXT, '
'"qps" REAL)')
db.query('CREATE TABLE "configuration" ('
'"key" TEXT PRIMARY KEY, '
'"value" TEXT)')
def delete_all_tables():
"""Delete all the tables of the currently opened db.
TODO: This function only deletes permanent tables, it should also delete
temporary tables.
"""
db = connection.get_db()
tables = lookup.get_tables_in_db()
for table in tables:
# The commentted query gives the error:
# sqlite3.OperationalError: near "?": syntax error
# db.query('DROP TABLE $table_name', vars={'table_name': table.name})
# TODO: Find out what's wrong with the previous line
db.query('DROP TABLE %s' % table.name)
def db_contains_production_data():
"""Returns true if this database contains production data.
A database is said to contain production data if it contains the key
'production' in the table 'configuration'. Use to token to prevent
accidental deletion of this table in functions like update.init_test_data.
"""
db = connection.get_db()
table_rs = db.where('sqlite_master', type='table', name='configuration')
if list(table_rs):
production_rs = db.where('configuration', key='production')
for production_row in production_rs:
if production_row.value == '0':
return False
else:
return bool(production_row.value)
return False
def insert_crawler_rule(crawler_rule):
db = connection.get_db()
db.insert('crawler_rules', seqname=None, **crawler_rule)
def fill_tables_with_testdata():
db = connection.get_db()
db.insert('configuration', key='production', value=False)
load_tables_from_csv()
