def get_timestamp(timestamp_order):
ts = 0
db = SimpleMysql(**__DB_PARAMS__)
cursor = db.query("select created_ts from zipnish_spans "
"order by created_ts %s limit 1" % timestamp_order)
if cursor.rowcount:
ts = cursor.fetchone()[0]
else:
print "No timestamp found, default to 0."
cursor.close()
db.conn.close()
return ts
class LogDatabase:
def __init__(self, **keyVals):
# saving database parameters
self.dbParams = keyVals
# table information
self.tablePrefix = 'zipnish_'
self.tables = ['spans', 'annotations']
# connect to database
self.db = SimpleMysql(
host=keyVals['host'],
db=keyVals['db'],
user=keyVals['user'],
passwd=keyVals['passwd'],
keep_alive=keyVals['keep_alive']
)
self.__create_tables()
if 'truncate_tables' in keyVals and keyVals['truncate_tables'] == True:
self.truncateTables()
def __create_tables(self):
spans_table_query = "CREATE TABLE IF NOT EXISTS zipnish_spans " \
"(span_id BIGINT NOT NULL, " \
"parent_id BIGINT, " \
"trace_id BIGINT NOT NULL, " \
"span_name VARCHAR(255) NOT NULL, " \
"debug SMALLINT NOT NULL, " \
"duration BIGINT, " \
"created_ts BIGINT);"
span_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_id);"
trace_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(trace_id);"
span_name_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_name(64));"
created_ts_index = "ALTER TABLE zipnish_spans ADD INDEX(created_ts);"
annotations_table_query = "CREATE TABLE IF NOT EXISTS zipnish_annotations " \
"(span_id BIGINT NOT NULL, " \
"trace_id BIGINT NOT NULL, " \
"span_name VARCHAR(255) NOT NULL, " \
"service_name VARCHAR(255) NOT NULL, " \
"value TEXT, " \
"ipv4 INT, " \
"port INT, " \
"a_timestamp BIGINT NOT NULL, " \
"duration BIGINT);"
span_id_key = "ALTER TABLE zipnish_annotations ADD FOREIGN KEY(span_id) " \
"REFERENCES zipnish_spans(span_id) ON DELETE CASCADE;"
trace_id_indx = "ALTER TABLE zipnish_annotations ADD INDEX(trace_id);"
span_name_index = "ALTER TABLE zipnish_annotations ADD INDEX(span_name(64));"
value_index = "ALTER TABLE zipnish_annotations ADD INDEX(value(64));"
a_timestamp_index = "ALTER TABLE zipnish_annotations ADD INDEX(a_timestamp);"
queryes = [spans_table_query, span_id_index0, trace_id_index0,
span_name_index0, created_ts_index,
annotations_table_query, span_id_key, trace_id_indx,
span_name_index, value_index, a_timestamp_index]
stmt = "SHOW TABLES LIKE 'zipnish_%'"
cursor = self.db.query(stmt)
table_count = len(cursor.fetchall())
if table_count == 0:
for query in queryes:
self.db.query(query)
self.db.commit()
def getParams(self):
return self.dbParams
def getDB(self):
return self.conn
def insert(self, tableName, rows):
table = self.tablePrefix + tableName
if len(rows) > 0:
for row in rows:
self.db.insert(table, row)
self.db.commit()
# truncate data in tables related to our application
def truncateTables(self):
print 'Truncating Tables:'
if self.db is not None and self.db.is_open():
for tableName in self.tables:
# table prefix + table name
table = self.tablePrefix + tableName
print 'truncating table -> ' + table
# delete table, and commit changes to database
self.db.delete(table)
self.db.commit()
class Database:
"""Create database from script and handling data input"""
def __init__(self, db_host, db_user, db_passwd, db_name):
"""Initialize database"""
self.db = SimpleMysql(
host=db_host,
db=db_name,
user=db_user,
passwd=db_passwd,
keep_alive=True
)
self.db.query('SET NAMES utf8;')
self.db.query('SET CHARACTER SET utf8;')
self.db.query('SET character_set_connection=utf8;')
try:
self.db.getOne('user_table', ['username'])
print 'Database working'
except Exception:
print 'Create new database'
sql_file = open(
os.path.join(
os.path.dirname(
os.path.abspath(__file__)
),
'sqlscript.sql'
), 'r'
)
sql_query = ''
for line in sql_file:
if line == 'go\n':
self.db.query(sql_query)
sql_query = ''
else:
sql_query = sql_query + line
sql_file.close()
self.register_user('admin', 'admin', 'admin', 'secret')
self.commit()
def commit(self):
"""Commit"""
self.db.conn.commit()
def username_exist(self, username):
"""Validate username"""
return True if self.db.getOne(
'user_table',
['username'],
('username=%s', [username])
) else False
def register_user(self, first_name, last_name, username, raw_password):
"""Register player to the database with username
and encrypted password"""
if self.username_exist(username):
return False
algo = 'sha1'
salt = encryption.get_hexdigest(algo, str(random.random()),
str(random.random()))[:5]
hsh = encryption.get_hexdigest(algo, salt, raw_password)
password = '******'.format(algo, salt, hsh)
self.db.insert('user_table', {
'first_name': first_name,
'last_name': last_name,
'username': username,
'password': password,
'role': 'normal'
})
return True
def login(self, username, password):
"""Check user login data, return true if the
data is matched with database"""
user_password = self.db.getOne(
'user_table',
['password'],
('username=%s', [username])
)
if user_password:
algo, salt, hsh = user_password[0].split('$')
return True if hsh == encryption.get_hexdigest(algo,
salt, password) else False
else:
return False
def get_last_insert_id(self, table):
"""Return last insert id"""
return self.db.query(
'select max(id) from %s' % (table)
).fetchone()[0]
def get_no_of_elements(self, table):
"""Return the number of record in specific table"""
return self.db.query(
'select count(id) from %s' % (table)
).fetchone()[0]
def get_no_of_elements_filter(self, table, condition):
"""Return the number of record in specific table"""
return self.db.query(
'select count(id) from %s where %s' % (table, condition)
).fetchone()[0]
def get_database(self):
return self.db
def input_drug(self):
sql_file = open(
os.path.join(
os.path.dirname(
os.path.abspath(__file__)
),
'drug.txt'
), 'r'
)
for line in sql_file:
li = re.compile("--").split(line)
self.db.insert('drug_table',
{
'name': li[0],
'unit': li[1]
}
)
sql_file.close()
self.commit()
class LogDatabase:
def __init__(self, **keyVals):
# saving database parameters
self.dbParams = keyVals
# table information
self.tablePrefix = 'zipnish_'
self.tables = ['spans', 'annotations']
# connect to database
self.db = SimpleMysql(host=keyVals['host'],
db=keyVals['db'],
user=keyVals['user'],
passwd=keyVals['passwd'],
keep_alive=keyVals['keep_alive'])
self.__create_tables()
def __create_tables(self):
spans_table_query = "CREATE TABLE IF NOT EXISTS zipnish_spans " \
"(span_id BIGINT NOT NULL, " \
"parent_id BIGINT, " \
"trace_id BIGINT NOT NULL, " \
"span_name VARCHAR(255) NOT NULL, " \
"debug SMALLINT NOT NULL, " \
"duration BIGINT, " \
"created_ts BIGINT);"
span_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_id);"
trace_id_index0 = "ALTER TABLE zipnish_spans ADD INDEX(trace_id);"
span_name_index0 = "ALTER TABLE zipnish_spans ADD INDEX(span_name(64));"
created_ts_index = "ALTER TABLE zipnish_spans ADD INDEX(created_ts);"
annotations_table_query = "CREATE TABLE IF NOT EXISTS zipnish_annotations " \
"(span_id BIGINT NOT NULL, " \
"trace_id BIGINT NOT NULL, " \
"span_name VARCHAR(255) NOT NULL, " \
"service_name VARCHAR(255) NOT NULL, " \
"value TEXT, " \
"ipv4 INT, " \
"port INT, " \
"a_timestamp BIGINT NOT NULL, " \
"duration BIGINT);"
span_id_key = "ALTER TABLE zipnish_annotations ADD FOREIGN KEY(span_id) " \
"REFERENCES zipnish_spans(span_id) ON DELETE CASCADE;"
trace_id_index = "ALTER TABLE zipnish_annotations ADD INDEX(trace_id);"
span_name_index = "ALTER TABLE zipnish_annotations ADD INDEX(span_name(64));"
value_index = "ALTER TABLE zipnish_annotations ADD INDEX(value(64));"
a_timestamp_index = "ALTER TABLE zipnish_annotations ADD INDEX(a_timestamp);"
queryes = [
spans_table_query, span_id_index0, trace_id_index0,
span_name_index0, created_ts_index, annotations_table_query,
span_id_key, trace_id_index, span_name_index, value_index,
a_timestamp_index
]
stmt = "SHOW TABLES LIKE 'zipnish_%'"
cursor = self.db.query(stmt)
table_count = len(cursor.fetchall())
if table_count == 0:
for query in queryes:
self.db.query(query)
self.db.commit()
def get_params(self):
return self.dbParams
def insert(self, table_name, rows):
table = self.tablePrefix + table_name
for row in rows:
self.db.insert(table, row)
self.db.commit()
passwd="",
keep_alive=True # try and reconnect timedout mysql connections?
)
#
db.insert("person", {'name': 'zhouyu', 'age': randint(1, 100)})
# print db.lastId()
# # db.commit()
db.delete("person", ("age between %s and %s", [18, 19]))
print db.lastQuery()
rows = db.getAll("person", ["id", "name", "age"], ("name=%s and age>%s", ["zhouyu", 10]))
print rows
for row in rows:
print row.id, row.name, row.age
# person = db.getOne("person", ['id', 'name', 'age'])
person2 = db.getAll("person", ['id', 'name', 'age'], None, ["id", "asc"])
print person2
print len(person2)
db.query("delete from user where id=5;")
print db.lastQuery()
def run_sql(query, *args):
db = SimpleMysql(**__DB_PARAMS__)
db.query(query, args)
db.commit()
db.conn.close()
