def tbl_col_update(database,tbl_name,old_col_name,new_col_name):
''' sqlite does not support change column name so have implemented a simple
version; does not handle complex db features which we will not be needing currently'''
col_defn = tbl_cols_get(database,tbl_name)
new_col_defn=[]
for colname,coltype in col_defn:
if colname == old_col_name:
new_col_defn.append((new_col_name,coltype))
else:
new_col_defn.append((colname,coltype))
# try to drop tmp table in case left behind from prev run
try:
tbl_remove(database,tbl_name+"_new")
except:
pass
tbl_create(database,tbl_name+"_new",new_col_defn)
colnames,rows,exec_str = tbl_rows_get(database,tbl_name)
colindex = colnames.index(old_col_name)
colnames.remove(old_col_name)
colnames.insert(colindex,new_col_name)
rows = _quotestrs(rows)
tbl_rows_insert(database,tbl_name+"_new",colnames,rows)
tbl_remove(database,tbl_name)
tbl_rename(database,tbl_name+"_new",tbl_name)
def persist_ts_values(self, filename, tstype, row):
tablename = self.sourcevaluecolname + "_values"
col_defn = [("filename", "string"), ("min", "integer"),
("max", "integer"), ("avg", "integer")]
col_names = ["filename", "min", "max", "avg"]
row.insert(0, filename)
row = _quotestrs([row])
with self.database:
if tbl_exists(self.database, tablename) == False:
logmsg = "creating [" + tablename + "] in db [" + self.databasename + "]"
try:
tbl_create(self.database, tablename, col_defn)
except Exception, e:
log.log(PRIORITY.FAILURE,
msg=logmsg + " [" + e.message + "]")
else:
log.log(PRIORITY.SUCCESS, msg=logmsg)
logmsg = "inserting row [" + str(
row) + "] into table [" + tablename + "]"
try:
tbl_rows_insert(self.database, tablename, col_names, row)
except Exception, e:
log.log(PRIORITY.FAILURE, msg=logmsg + " [" + e.message + "]")
def formats_db_load(dbname="test_ssloader"):
tblname = "formats"
hex2name = {}
for key,value in colors.attr_get_keyval(include_callable=False,include_baseattr=False):
hex2name[value] = key
formats_col_defn = [('name','text'),('fgcolor','text'), ('bgcolor','text'),
('__id','text'),('__timestamp','text')]
formats_col_names = [row[0] for row in formats_col_defn]
dbrows=[]
for name,bg in colorpalette.iteritems():
if fontpalette.has_key(name):
fg = fontpalette[name]
else:
fg = '#000000'
__id = IDGenerator().getid()
__timestamp = datetime.now().strftime("%H:%M:%S")
dbrows.append(["\""+name+"\"",
"\""+hex2name[fg]+"\"",
"\""+hex2name[bg]+"\"",
"\""+__id+"\"","\""+__timestamp+"\""])
database = Database(dbname)
with database:
if tbl_exists(database,tblname) == True:
tbl_remove(database,tblname)
tbl_create(database,tblname, formats_col_defn)
exec_str, result = tbl_rows_insert(database,tblname,formats_col_names,dbrows)
def color_db_load(dbname="test_ssloader"):
tblname = "colors"
colors_col_defn = [('name', 'text'), ('hex', 'text'), ('rgb', 'text'),
('__id', 'text'), ('__timestamp', 'text')]
colors_col_names = [row[0] for row in colors_col_defn]
dbrows = []
for key, value in colors.attr_get_keyval(include_callable=False,
include_baseattr=False):
rgb = hex2rgb(value)
rgbstr = ",".join(map(str, rgb))
__id = IDGenerator().getid()
__timestamp = datetime.now().strftime("%H:%M:%S")
dbrows.append([
"\"" + key + "\"", "\"" + value + "\"", "\"" + rgbstr + "\"",
"\"" + __id + "\"", "\"" + __timestamp + "\""
])
database = Database(dbname)
with database:
if tbl_exists(database, tblname) == True:
tbl_remove(database, tblname)
tbl_create(database, tblname, colors_col_defn)
exec_str, result = tbl_rows_insert(database, tblname, colors_col_names,
dbrows)
def color_db_load(dbname="test_ssloader"):
tblname = "colors"
colors_col_defn = [('name','text'),('hex','text'),('rgb','text'),('__id','text'),('__timestamp','text')]
colors_col_names = [row[0] for row in colors_col_defn]
dbrows=[]
for key,value in colors.attr_get_keyval(include_callable=False,include_baseattr=False):
rgb = hex2rgb(value)
rgbstr = ",".join(map(str,rgb))
__id = IDGenerator().getid()
__timestamp = datetime.now().strftime("%H:%M:%S")
dbrows.append(["\""+key+"\"","\""+value+"\"","\""+rgbstr+"\"",
"\""+__id+"\"","\""+__timestamp+"\""])
database = Database(dbname)
with database:
if tbl_exists(database,tblname) == True:
tbl_remove(database,tblname)
tbl_create(database,tblname, colors_col_defn)
exec_str, result = tbl_rows_insert(database,tblname,colors_col_names,dbrows)
def _write_to_db(self, row, dname, tname, cdefn, cnames):
database = Database(dname)
with database:
if tbl_exists(database, tname) == False:
tbl_create(database, tname, cdefn)
tbl_rows_insert(database, tname, cnames, row)
def test_db_create(self):
database = Database(test_db.name)
with database:
tbl_create(database,test_db.tbl_name,test_db.col_defn)
database = Database(test_db.name,True)
with database:
self.assertTrue(database.tbl_exists(test_db.tbl_name))
def test_tbl_rows_insert_dupe_key(self):
database = Database(test_db.name, True)
with database:
tbl_create(database,test_db.tbl_name,test_db.col_defn, test_db.tbl_pk_defn)
with self.assertRaises(S3IntegrityError):
tbl_rows_insert(database,test_db.tbl_name,
test_db.col_name,
test_db.tbl_rows_dupe_key)
def test_tbl_rows_insert_str(self):
database = Database(test_db_str.name)
with database:
tbl_create(database,test_db_str.tbl_name,test_db_str.col_defn)
tbl_rows_insert(database,test_db_str.tbl_name,test_db_str.col_name,
test_db_str.tbl_rows)
database = Database(test_db_str.name,True)
with database:
self.assertEquals('foobar',database.execute("select col_name1 from tbl_name_test",True))
def test_tbl_create_pk(self):
database = Database(test_db.name)
with database:
tbl_create(database,test_db.tbl_name,test_db.col_defn,test_db.tbl_pk_defn)
database = Database(test_db.name,True)
with database:
self.assertEquals(1,tbl_index_count(database, test_db.tbl_name))
self.assertListEqual(test_db.tbl_pk_defn,tbl_index_defn_get(database,test_db.tbl_name))
def persist(self, createtable=True):
self._metadata_set()
# switch adult name to teacher
try:
_idx = self.tbl_col_defn.index('adult')
self.tbl_col_defn.remove('adult')
self.tbl_col_defn.insert(0, 'teacher')
except:
pass
if not tbl_exists(self.database, self.tbl_name) == True:
tbl_create(self.database, self.tbl_name, self.tbl_col_defn)
try:
_idx = self.tbl_col_names.index('adult')
self.tbl_col_names.remove('adult')
self.tbl_col_names.insert(0, 'teacher')
except:
pass
# and also objtype is not persisted
try:
_idx = self.tbl_col_names.index('objtype')
self.tbl_col_names.pop(_idx)
self.tbl_row_values[0].pop(_idx)
except:
pass
# or userobjid
try:
_idx = self.tbl_col_names.index('userobjid')
self.tbl_col_names.pop(_idx)
self.tbl_row_values[0].pop(_idx)
except:
pass
result, exec_str = tbl_rows_insert(self.database, self.tbl_name,
self.tbl_col_names,
self.tbl_row_values)
if hasattr(self, "id") == False:
_idx = self.tbl_col_names.index('__id')
setattr(self, "id", self.tbl_row_values[_idx])
if self.dm.has_key('id') == False:
_idx = self.tbl_col_names.index('__id')
self.dm["id"] = self.tbl_row_values[0][_idx].replace('\"', '')
return (result, exec_str)
def test_tbl_rows_insert(self):
database = Database(test_db.name)
with database:
tbl_create(database,test_db.tbl_name,test_db.col_defn)
tbl_rows_insert(database,test_db.tbl_name,test_db.col_name,
test_db.tbl_rows)
database = Database(test_db.name,True)
with database:
col_name,tbl_rows = tbl_rows_get(database,test_db.tbl_name)
self.assertListEqual(col_name,test_db.col_name)
self.assertListEqual(tbl_rows,test_db.tbl_rows)
def persist(self,createtable=True): self._metadata_set() if not tbl_exists(self.database,self.tbl_name) ==True: tbl_create(self.database, self.tbl_name, self.tbl_col_defn) result,exec_str = tbl_rows_insert(self.database, self.tbl_name, self.tbl_col_names, self.tbl_row_values) return(result,exec_str)
def test_tbl_rows_get_spoecific_field(self):
database = Database(test_db.name)
with database:
tbl_create(database,test_db.tbl_name,test_db.col_defn)
tbl_rows_insert(database,test_db.tbl_name,test_db.col_name,
test_db.tbl_rows)
database = Database(test_db.name,True)
with database:
col_name,tbl_rows,_ = tbl_rows_get(database,test_db.tbl_name,
['col_name1','col_name2',
'col_name3','col_name4'])
self.assertListEqual(col_name,test_db.col_name)
self.assertListEqual(tbl_rows,test_db.tbl_rows)
def tbl_move(database,database_new,tbl_name,overwrite=False):
# get old table details
with database:
col_defn = tbl_cols_get(database,tbl_name)
colnames,rows,exec_str = tbl_rows_get(database,tbl_name)
with database_new:
# create new table details
if overwrite == True:
try:
tbl_remove(database_new,tbl_name)
except:
pass
tbl_create(database_new,tbl_name,col_defn)
# insert new data
rows = _quotestrs(rows)
tbl_rows_insert(database_new,tbl_name,colnames,rows)
def setUp(self):
self.databasename = 'foo'
self.database = Database(self.databasename)
self.rawdata = [[1, 240, 'fileA'], [2, 241, 'fileA'],
[3, 242, 'fileB'], [4, 240, 'fileB']]
self.files = ['fileA', 'fileB']
self.col_defn = [("id", "integer"), ("watts", "integer"),
("filename", "string")]
self.col_names = ["id", "watts", "filename"]
self.tablename = "bar"
_rows = _quotestrs(self.rawdata)
with self.database:
tbl_create(self.database, self.tablename, self.col_defn)
tbl_rows_insert(self.database, self.tablename, self.col_names,
_rows)
def persist(self,
f=None,
column_defn=[("id", "integer"), ("bucket", "integer"),
("watts", "integer"), ("hr", "integer"),
("filename", "text")],
column_names=["id", "bucket", "watts", "hr", "filename"]):
database = Database(self.databasename)
_rows = self.dump(f)
with database:
if tbl_exists(database, self.tablename) == False:
tbl_create(database, self.tablename, column_defn)
try:
tbl_rows_insert(database, self.tablename, column_names, _rows)
except Exception, e:
log.log(PRIORITY.FAILURE,
msg="timseries persistence failed in db [" +
database.name + "] [" + e.message + "]")
else:
def formats_db_load(dbname="test_ssloader"):
tblname = "formats"
hex2name = {}
for key, value in colors.attr_get_keyval(include_callable=False,
include_baseattr=False):
hex2name[value] = key
formats_col_defn = [('name', 'text'), ('fgcolor', 'text'),
('bgcolor', 'text'), ('__id', 'text'),
('__timestamp', 'text')]
formats_col_names = [row[0] for row in formats_col_defn]
dbrows = []
for name, bg in colorpalette.iteritems():
if fontpalette.has_key(name):
fg = fontpalette[name]
else:
fg = '#000000'
__id = IDGenerator().getid()
__timestamp = datetime.now().strftime("%H:%M:%S")
dbrows.append([
"\"" + name + "\"", "\"" + hex2name[fg] + "\"",
"\"" + hex2name[bg] + "\"", "\"" + __id + "\"",
"\"" + __timestamp + "\""
])
database = Database(dbname)
with database:
if tbl_exists(database, tblname) == True:
tbl_remove(database, tblname)
tbl_create(database, tblname, formats_col_defn)
exec_str, result = tbl_rows_insert(database, tblname,
formats_col_names, dbrows)
def setUp(self):
self.databasename = 'foo'
self.database = Database(self.databasename)
self.rawdata = [[1, 1, 130, 240, 'fileA'], [2, 2, 131, 241, 'fileA'],
[3, 3, 132, 242, 'fileA'], [4, 4, 133, 240, 'fileA'],
[5, 5, 132, 261, 'fileA'], [6, 6, 131, 262, 'fileA'],
[7, 1, 150, 310, 'fileB'], [8, 2, 152, 307, 'fileB'],
[9, 3, 154, 309, 'fileB'], [10, 4, 152, 306, 'fileB'],
[11, 5, 151, 310, 'fileB'], [12, 6, 157, 311, 'fileB']]
self.files = ['fileA', 'fileB']
self.col_defn = [("id", "integer"), ("bucket", "integer"),
("hr", "integer"), ("watts", "integer"),
("filename", "string")]
self.col_names = ["id", "bucket", "hr", "watts", "filename"]
self.tablename = "bar"
_rows = _quotestrs(self.rawdata)
with self.database:
tbl_create(self.database, self.tablename, self.col_defn)
tbl_rows_insert(self.database, self.tablename, self.col_names,
_rows)
def setUp(self):
database = Database(test_db.name)
with database:
tbl_create(database,test_db.tbl_name,test_db.col_defn,test_db.tbl_pk_defn)