def DataTableList(cls, db=_DEFAULT_DB): # Output: List of data tables dba = DBAccess(db) sql_str = "SELECT table_name FROM %s" % settings.UPLOAD_FILE_TABLE rows, _ = dba.ExecuteQuery(sql_str) table_list = [r[0] for r in rows] return table_list
def RemoveUploadedFile(cls, filename, db=_DEFAULT_DB):
# Removes an uploaded file from UPLOADED_FILE_TABLE
# Input: filename - name of file to remove
# db - database to use
dba = DBAccess(db_path=db)
sql_str = "DELETE FROM %s WHERE table_name='%s'" % (
settings.UPLOAD_FILE_TABLE, filename)
dba.ExecuteQuery(sql_str, commit=True)
def test_AddFileToTable(self):
return
ft = FileTable(th.FILE_PATH, db=th.TEST_DB_PATH)
ft._AddFileToTable()
dba = DBAccess(db_path=th.TEST_DB_PATH)
sql_str = "SELECT * FROM %s WHERE file_name='%s'" % (
settings.UPLOAD_FILE_TABLE, th.FILE_NAME)
rows, _ = dba.ExecuteQuery(sql_str)
file_name = rows[0][0]
self.assertEqual(file_name, th.FILE_NAME)
def _ConstructSQLTableCreate(self, values):
dba = DBAccess()
if dba.IsTablePresent(self._table_name):
return ''
sql_str = "CREATE TABLE %s (" % self._table_name
for n in range(len(self._colnames)):
sql_str += "%s %s" % (self._colnames[n], SQLType(values[n]))
if n+1 >= len(self._colnames):
sql_str += ")"
else:
sql_str += ","
return sql_str
def _AddFileToTable(self):
# Records the file in the Django UploadedFiles table
dba = DBAccess(db_path=self._db_path)
if not dba.IsTablePresent(settings.UPLOAD_FILE_TABLE):
raise DBError('%s does not exist' % settings.UPLOAD_FILE_TABLE)
sql_str = "SELECT * from %s where table_name='%s'" % (
settings.UPLOAD_FILE_TABLE, self._filename)
rows, _ = dba.ExecuteQuery(sql_str)
if len(rows) == 0:
sql_str = '''
INSERT into %s ('file_name', 'file_path', 'table_name')
VALUES ('%s', '%s', '%s')
''' % (settings.UPLOAD_FILE_TABLE,
self._filename,
self._file_path,
self._table_name)
dba.ExecuteQuery(sql_str, commit=True)
def testDataTableList(self):
return
dba = DBAccess(db_path=th.TEST_DB_PATH)
ft = FileTable(th.FILE_PATH, db=th.TEST_DB_PATH)
sql_str = "DELETE FROM %s WHERE table_name='%s'" % (
settings.UPLOAD_FILE_TABLE, ft._table_name)
try:
dba.ExecuteQuery(sql_str, commit=True)
except:
pass
old_table_set = set(FileTable.DataTableList(db=th.TEST_DB_PATH))
ft.CreateAndPopulateTable()
new_table_set = set(FileTable.DataTableList(db=th.TEST_DB_PATH))
new_row_set = set([ft._table_name])
self.assertTrue(new_table_set.issuperset(new_row_set))
self.assertFalse(old_table_set.issuperset(new_row_set))
ft._ExecuteSQL('', CloseDB=True)
def maketable(request):
table_list = FileTable.DataTableList(CUR_DB)
if request.method == 'POST':
tablename = request.POST['tablename']
table_list.remove(tablename)
table_list.insert(0, tablename) # Make sure it's the default
# Create a form instance and populate it with data from the request:
display_form = TableForm(request.POST)
# check whether it's valid:
if display_form.is_valid():
numrows = GetFieldFromForm(display_form, 'numrows')
lastrow = GetFieldFromForm(display_form, 'lastrow')
dba = DBAccess()
if not dba.IsTablePresent(tablename):
raise TableviewUserError("Cannot find table %s" % tablename)
headers = dba.GetSchema(tablename)
sql_str = 'SELECT * FROM %s' % tablename
all_rows, _ = dba.ExecuteQuery(sql_str)
if 'Next' in request.POST:
firstrow = lastrow + 1
else:
firstrow = lastrow - 2*numrows
lastrow = firstrow + numrows - 1
rows = all_rows[firstrow-1:lastrow-1]
new_display_form = TableForm(initial={
'numrows': numrows, 'lastrow': lastrow})
ctx_dict = {'rows':rows,
'display_form': new_display_form,
'tablename': tablename,
'headers': headers,
'table_list': table_list,
}
return render_to_response('table_view.html', ctx_dict,
context_instance=RequestContext(request))
else:
pass
# if a GET (or any other method) we'll create a blank form
else:
display_form = TableForm()
return render(request, 'table_request.html',
{'display_form': display_form,
'table_list': table_list})
def testRemoveUploadedFile(self):
return
dba = DBAccess(db_path=th.TEST_DB_PATH)
ft = FileTable(th.FILE_PATH, db=th.TEST_DB_PATH)
ft.CreateAndPopulateTable()
table_set = set(FileTable.DataTableList(db=th.TEST_DB_PATH))
self.assertTrue(table_set.issuperset(set([ft._table_name])))
FileTable.RemoveUploadedFile(ft._filename, db=th.TEST_DB_PATH)
remove_table_set = set(FileTable.DataTableList(db=th.TEST_DB_PATH))
self.assertFalse(remove_table_set.issuperset(set([ft._table_name])))
def query(request):
dba = DBAccess()
if request.method == 'POST':
# Create a form instance and populate it with data from the request:
query_form = QueryForm(request.POST)
# check whether it's valid:
if query_form.is_valid():
query_string = GetFieldFromForm(query_form, 'query_string')
if len(query_string) > 0:
rows,_ = dba.ExecuteQuery(query_string)
headers = dba.GetSchemaFromSelect(query_string)
if len(rows[0]) != len(headers):
raise TableviewInternalError("header length doesn't match row length")
else: # Used in test
rows = []
headers = []
ctx_dict = {'rows':rows,
'query_string': query_string,
'headers': headers,
}
return render_to_response('query_result.html', ctx_dict,
context_instance=RequestContext(request))
else:
pass
# if a GET (or any other method) we'll create a blank form
# Extend by including the schemas with types
else:
query_form = QueryForm()
table_list = FileTable.DataTableList(CUR_DB)
rows = []
dba = DBAccess()
for table in table_list:
row = [table, ", ".join(dba.GetSchema(table))]
rows.append(row)
return render(request, 'query_request.html',
{'rows': rows,
'query_form': query_form,
})