def add_relations(user_id, user_projects):
"""
"""
# Insert default relation between projet and user
for prj_id in user_projects:
# prepare the sql query
sql_query = "INSERT INTO `relations-projects` "
sql_query += "(`user_id`, `project_id`) "
sql_query += "VALUES (%s, %s);" % (user_id, prj_id)
# run the sql query
querySQL(query=sql_query)
def add_projects():
"""
"""
projects = read_json_file(filename="data_projects.json")
for prj in projects:
# prepare the sql query
sql_query = "INSERT INTO `projects` "
sql_query += "(`name`, `active`) "
sql_query += "VALUES ('%s', %s);" % (prj["name"], prj["id"])
# run the sql query
querySQL(query=sql_query)
def add_globals():
"""
"""
global_variables = read_json_file(filename="data_globals.json")
for v in global_variables:
# prepare the sql query
sql_query = "INSERT INTO `test-environment` "
sql_query += "(`name`, `value`, `project_id`) "
sql_query += "VALUES ('%s', '%s', %s);" % (v["name"],
json.dumps(v["value"]),
v["project-id"])
# run the sql query
querySQL(query=sql_query)
def add_users(auth_salt):
"""
"""
users = read_json_file(filename="data_users.json")
for usr in users:
# prepare hash for password
t1 = hashlib.sha1()
t1.update(usr["password"].encode("utf8"))
pwd_hash = t1.hexdigest()
# password with salt
pwd_salt = "%s%s" % (auth_salt, pwd_hash)
# final hash of the password
t2 = hashlib.sha1()
if sys.version_info < (3,):
t2.update(pwd_salt)
else:
t2.update(pwd_salt.encode('utf-8'))
pwd_sha = t2.hexdigest()
# prepare apikey secret
k1_secret = hexlify(os.urandom(20))
if sys.version_info > (3,):
k1_secret = k1_secret.decode("utf8")
# level of the user
level_admin = 0
level_monitor = 0
level_tester = 0
if usr["level"] == "administrator":
level_admin = 1
elif usr["level"] == "monitor":
level_monitor = 1
else:
level_tester = 1
# prepare the sql query
sql_query = "INSERT INTO `users` "
sql_query += "(`login`, `password`, `administrator`, `monitor`,"
sql_query += "`tester`, `email`, `lang`, `style`, `active`, "
sql_query += "`online`, `notifications`, `defaultproject`, "
sql_query += "`apikey_id`, `apikey_secret` ) "
sql_query += "VALUES ('%s', '%s', %s, " % (
usr["login"], pwd_sha, level_admin)
sql_query += "%s, %s, '%s', 'en'," % (
level_monitor, level_tester, usr["email"])
sql_query += "'default', 1, 0, 'false;false;false;false;false;false;false;'"
sql_query += ", %s, '%s', '%s');" % (
usr["default-project"], usr["login"], k1_secret)
# execute the query
user_id = querySQL(query=sql_query)
# finally add the relations between user and projects
add_relations(user_id=user_id, user_projects=usr["projects"])
def add_configs():
"""
"""
# generate a salt
auth_salt = hexlify(os.urandom(20))
if sys.version_info > (3,):
auth_salt = auth_salt.decode("utf8")
cfg = {"auth-salt": auth_salt}
# insert them in the config table
sql_query = "INSERT INTO `config` "
sql_query += "(`opt`, `value`) "
sql_query += "VALUES ('auth-salt', '%s');" % auth_salt
# run the sql query
querySQL(query=sql_query)
return cfg
def create_sqlite3_model():
print("Initializing new database...")
print()
querySQL(query="CREATE TABLE `users` (\
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
`login` varchar(50) NOT NULL, \
`password` varchar(200) NOT NULL, \
`administrator` INT, \
`monitor` INT, \
`tester` INT, \
`email` varchar(200) NOT NULL, \
`lang` varchar(50) NOT NULL, \
`style` varchar(50) NOT NULL, \
`active` INT, \
`online` INT, \
`notifications` varchar(200) NOT NULL, \
`defaultproject` INT, \
`apikey_id` varchar(200), \
`apikey_secret` varchar(200) \
);")
querySQL(query="CREATE TABLE `tasks-history` (\
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
`eventtype` INT not null, \
`eventargs` VARCHAR(50), \
`eventtime` VARCHAR(20), \
`eventname` VARCHAR(200), \
`eventauthor` VARCHAR(50), \
`realruntime` VARCHAR(20), \
`eventduration` VARCHAR(20), \
`eventresult` VARCHAR(20), \
`projectid` INT \
) ;")
querySQL(query="CREATE TABLE `config` (\
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
`opt` TEXT, \
`value` TEXT \
);")
querySQL(query="CREATE TABLE `projects` (\
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
`name` TEXT, \
`active` INT, \
`description` TEXT \
);")
querySQL(query="CREATE TABLE `relations-projects` (\
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
`user_id` INT, \
`project_id` INT \
);")
querySQL(query="CREATE TABLE `test-environment` (\
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
`name` TEXT, \
`value` TEXT, \
`project_id` INT \
);")
querySQL(query="CREATE TABLE `agents` (\
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, \
`token` INT, \
`name` varchar(50) NOT NULL, \
`project_id` INT \
);")
