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 \ );")