def create_table(self, data, table_name, error_if_exists = False, **kwargs):
'Create a table based on the data, but don\'t insert anything.'
converted_data = convert(data)
if len(converted_data) == 0 or converted_data[0] == []:
raise ValueError(u'You passed no sample values, or all the values you passed were null.')
else:
startdata = OrderedDict(converted_data[0])
# Select a non-null item
for k, v in startdata.items():
if v != None:
break
else:
v = None
if_not_exists = u'' if error_if_exists else u'IF NOT EXISTS'
# Do nothing if all items are null.
if v != None:
try:
# This is vulnerable to injection.
sql = u'''
CREATE TABLE %s %s (
%s %s
);''' % (if_not_exists, quote(table_name), quote(k), get_column_type(startdata[k]))
self.execute(sql, commit = False)
except:
raise
else:
self.commit()
for row in converted_data:
self.__check_and_add_columns(table_name, row)
def save_var(self, key, value, **kwargs):
'Save one variable to the database.'
# Check whether Highwall's variables table exists
self.__check_or_create_vars_table()
column_type = get_column_type(value)
tmp = quote(self.__vars_table_tmp)
self.execute(u'DROP TABLE IF EXISTS %s' % tmp, commit = False)
# This is vulnerable to injection
self.execute(u'CREATE TABLE %s (`value` %s)' % (tmp, column_type), commit = False)
# This is ugly
self.execute(u'INSERT INTO %s (`value`) VALUES (?)' % tmp, [value], commit = False)
table = (quote(self.__vars_table), tmp)
params = [key, column_type]
self.execute(u'''
INSERT OR REPLACE INTO %s (`key`, `type`, `value`)
SELECT
? AS key,
? AS type,
value
FROM %s
''' % table, params)
self.execute(u'DROP TABLE %s' % tmp, commit = False)
self.__commit_if_necessary(kwargs)
def __check_or_create_vars_table(self):
self.create_table(
{'key': '', 'type': ''},
quote(self.__vars_table),
commit = False
)
sql = u'ALTER TABLE %s ADD COLUMN value BLOB' % quote(self.__vars_table)
self.execute(sql, commit = False)
self.commit()
table_info = self.execute(u'PRAGMA table_info(%s)' % quote(self.__vars_table))
column_names_observed = set([column['name'] for column in table_info])
column_names_expected = {'key', 'type', 'value'}
assert column_names_observed == column_names_expected, table_info
def column_names(self, table):
"""An iterable of column names, for a particular table or
view."""
table_info = self.execute(
u'PRAGMA table_info(%s)' % quote(table))
return (column['name'] for column in table_info)
def insert(self, data, table_name = 'dumptruck', **kwargs):
try:
self.create_table(data, table_name)
except:
raise
# Turn it into a list of zips.
converted_data = convert(data)
for row in converted_data:
self.__check_and_add_columns(table_name, row)
# .keys() and .items() are in the same order
# http://www.python.org/dev/peps/pep-3106/
for row in converted_data:
keys = [pair[0] for pair in row]
values = [pair[1] for pair in row]
question_marks = ','.join('?'*len(keys))
# This is vulnerable to injection.
sql = u'INSERT OR REPLACE INTO %s (%s) VALUES (%s);' % (
quote(table_name), ','.join(keys), question_marks)
self.execute(sql, values, commit=False)
self.__commit_if_necessary(kwargs)
def __check_or_create_vars_table(self):
sql = u"CREATE TABLE IF NOT EXISTS %s (`key` text PRIMARY KEY, `value` blob, `type` text)" % quote(self.__vars_table)
self.execute(sql, commit = False)
self.commit()
table_info = self.execute(u'PRAGMA table_info(%s)' % quote(self.__vars_table))
column_names_observed = set([column['name'] for column in table_info])
column_names_expected = {'key', 'type', 'value'}
assert column_names_observed == column_names_expected, table_info
def create_table(self, data, table_name, error_if_exists = False, **kwargs):
'Create a table based on the data, but don\'t insert anything.'
converted_data = convert(data)
startdata = dict(converted_data[0])
# Select a non-null item
for k, v in startdata.items():
if v != None:
break
try:
# This is vulnerable to injection.
self.execute(u'''
CREATE TABLE %s (
%s %s
);''' % (quote(table_name), quote(k), get_column_type(startdata[k])), commit = False)
except sqlite3.OperationalError, msg:
if (not re.match(r'^table.+already exists$', str(msg))) or (error_if_exists == True):
raise
def get_var(self, key):
'Retrieve one saved variable from the database.'
vt = quote(self.__vars_table)
data = self.execute(u'SELECT * FROM %s WHERE `key` = ?' % vt, [key], commit = False)
if data == []:
raise NameError(u'The DumpTruck variables table doesn\'t have a value for %s.' % key)
else:
tmp = quote(self.__vars_table_tmp)
row = data[0]
self.execute(u'DROP TABLE IF EXISTS %s' % tmp, commit = False)
# This is vulnerable to injection
self.execute(u'CREATE TEMPORARY TABLE %s (`value` %s)' % (tmp, row['type']), commit = False)
# This is ugly
self.execute(u'INSERT INTO %s (`value`) VALUES (?)' % tmp, [row['value']], commit = False)
value = self.dump(tmp)[0]['value']
self.execute(u'DROP TABLE %s' % tmp, commit = False)
return value
def create_index(self, columns, table_name, if_not_exists = True, unique = False, **kwargs):
'Create a unique index on the column(s) passed.'
index_name = simplify(table_name) + u'_' + u'_'.join(map(simplify, columns))
if unique:
sql = u'CREATE UNIQUE INDEX %s ON %s (%s)'
else:
sql = u'CREATE INDEX %s ON %s (%s)'
first_param = u'IF NOT EXISTS ' + index_name if if_not_exists else index_name
params = (first_param, quote(table_name), ','.join(map(quote, columns)))
self.execute(sql % params, **kwargs)
def __check_and_add_columns(self, table_name, converted_data_row):
column_types = self.__column_types(table_name)
for key,value in converted_data_row:
try:
column_type = get_column_type(value)
params = (quote(table_name), key, column_type)
sql = u'ALTER TABLE %s ADD COLUMN %s %s ' % params
self.execute(sql, commit = True)
except self.sqlite3.OperationalError, msg:
if str(msg).split(':')[0] == u'duplicate column name':
# The column is already there.
pass
else:
raise
def dump(self, table_name = 'dumptruck'): 'Dump a table.' return self.execute(u'SELECT * FROM %s;' % quote(table_name))
def drop(self, table_name = 'dumptruck', if_exists = False, **kwargs):
'Drop a table.'
return self.execute(u'DROP TABLE %s %s;' % ('IF EXISTS' if if_exists else '', quote(table_name)), **kwargs)
def __column_types(self, table_name):
# This is vulnerable to injection.
self.cursor.execute(u'PRAGMA table_info(%s)' % quote(table_name))
return {column[1]:column[2] for column in self.cursor.fetchall()}
for row in converted_data:
self.__check_and_add_columns(table_name, row)
# .keys() and .items() are in the same order
# http://www.python.org/dev/peps/pep-3106/
# rowid of inserted rows
rowids = []
for row in converted_data:
keys = [pair[0] for pair in row]
values = [pair[1] for pair in row]
# This is vulnerable to injection.
if len(keys) > 0:
question_marks = ','.join('?'*len(keys))
sql = u'INSERT %s INTO %s (%s) VALUES (%s);' % (upserttext, quote(table_name), ','.join(keys), question_marks)
self.execute(sql, values, commit=False)
else:
sql = u'INSERT %s INTO %s DEFAULT VALUES;' % (upserttext, quote(table_name))
self.execute(sql, commit=False)
rowids.append(self.execute('SELECT last_insert_rowid()', commit=False)[0]['last_insert_rowid()'])
self.__commit_if_necessary(kwargs)
# Return rowids as a list?
if hasattr(data, 'keys'):
return rowids[0]
else:
return rowids
for row in converted_data:
self.__check_and_add_columns(table_name, row)
# .keys() and .items() are in the same order
# http://www.python.org/dev/peps/pep-3106/
# rowid of inserted rows
rowids = []
for row in converted_data:
keys = [pair[0] for pair in row]
values = [pair[1] for pair in row]
# This is vulnerable to injection.
if len(keys) > 0:
question_marks = ','.join('?'*len(keys))
sql = u'INSERT INTO %s (%s) VALUES (%s);' % (quote(table_name), ','.join(keys), question_marks)
self.execute(sql, values, commit=False)
else:
sql = u'INSERT INTO %s DEFAULT VALUES;' % quote(table_name)
self.execute(sql, commit=False)
rowids.append(self.execute('SELECT last_insert_rowid()')[0]['last_insert_rowid()'])
self.__commit_if_necessary(kwargs)
# Return rowids as a list?
if hasattr(data, 'keys'):
return rowids[0]
else:
return rowids