def main():
dbman.execute_sql_statement("USE " + dbman.DATABASE)
files = os.listdir(settings.DESTINATION_FOLDER)
for f in files:
if f.startswith(settings.CREATE_FILE):
extract_content(f)
dbman.close_connection()
def main():
dbman.execute_sql_statement("USE " + dbman.DATABASE)
if dbman.check_table_exists('projects'):
trigger_delete_insert_project = """DROP TRIGGER IF EXISTS insert_project;"""
trigger_insert_project = """
CREATE TRIGGER insert_project
BEFORE INSERT
ON projects FOR EACH ROW
BEGIN
IF NEW.deleted = 1 THEN
SET NEW.id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_project)
dbman.execute_sql_statement(trigger_insert_project)
if dbman.check_table_exists("issues"):
trigger_delete_insert_issue = """DROP TRIGGER IF EXISTS insert_issue;"""
trigger_insert_issue = """
CREATE TRIGGER insert_issue
BEFORE INSERT
ON issues FOR EACH ROW
BEGIN
DECLARE project_found INTEGER;
SELECT id INTO project_found
FROM projects
WHERE id = NEW.repo_id;
IF (project_found IS NULL OR project_found = -1) THEN
SET NEW.id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_issue)
dbman.execute_sql_statement(trigger_insert_issue)
if dbman.check_table_exists("commits"):
trigger_delete_insert_commit = """DROP TRIGGER IF EXISTS insert_commit;"""
trigger_insert_commit = """
CREATE TRIGGER insert_commit
BEFORE INSERT
ON commits FOR EACH ROW
BEGIN
DECLARE project_found INTEGER;
SELECT id INTO project_found
FROM projects
WHERE id = NEW.project_id;
IF (project_found IS NULL OR project_found = -1) THEN
SET NEW.id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_commit)
dbman.execute_sql_statement(trigger_insert_commit)
if dbman.check_table_exists("project_members"):
trigger_delete_insert_project_member = """DROP TRIGGER IF EXISTS insert_project_member;"""
trigger_insert_project_member = """
CREATE TRIGGER insert_project_member
BEFORE INSERT
ON project_members FOR EACH ROW
BEGIN
DECLARE project_found INTEGER;
SELECT id INTO project_found
FROM projects
WHERE id = NEW.repo_id;
IF (project_found IS NULL OR project_found = -1) THEN
SET NEW.repo_id = -1;
SET NEW.user_id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_project_member)
dbman.execute_sql_statement(trigger_insert_project_member)
if dbman.check_table_exists("watchers"):
trigger_delete_insert_watcher = """DROP TRIGGER IF EXISTS insert_watcher;"""
trigger_insert_watcher = """
CREATE TRIGGER insert_watcher
BEFORE INSERT
ON watchers FOR EACH ROW
BEGIN
DECLARE project_found INTEGER;
SELECT id INTO project_found
FROM projects
WHERE id = NEW.repo_id;
IF (project_found IS NULL OR project_found = -1) THEN
SET NEW.repo_id = -1;
SET NEW.user_id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_watcher)
dbman.execute_sql_statement(trigger_insert_watcher)
if dbman.check_table_exists("forks"):
trigger_delete_insert_fork = """DROP TRIGGER IF EXISTS insert_fork;"""
trigger_insert_fork = """
CREATE TRIGGER insert_fork
BEFORE INSERT
ON forks FOR EACH ROW
BEGIN
DECLARE project_found INTEGER;
SELECT id INTO project_found
FROM projects
WHERE id = NEW.forked_from_id;
IF (project_found IS NULL OR project_found = -1) THEN
SET NEW.fork_id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_fork)
dbman.execute_sql_statement(trigger_insert_fork)
if dbman.check_table_exists("repo_labels"):
trigger_delete_insert_repo_label = """DROP TRIGGER IF EXISTS insert_repo_label;"""
trigger_insert_repo_label = """
CREATE TRIGGER insert_repo_label
BEFORE INSERT
ON repo_labels FOR EACH ROW
BEGIN
DECLARE project_found INTEGER;
SELECT id INTO project_found
FROM projects
WHERE id = NEW.repo_id;
IF (project_found IS NULL OR project_found = -1) THEN
SET NEW.id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_repo_label)
dbman.execute_sql_statement(trigger_insert_repo_label)
if dbman.check_table_exists("repo_milestones"):
trigger_delete_insert_repo_milestone = """DROP TRIGGER IF EXISTS insert_repo_milestone;"""
trigger_insert_repo_milestone = """
CREATE TRIGGER insert_repo_milestone
BEFORE INSERT
ON repo_milestones FOR EACH ROW
BEGIN
DECLARE project_found INTEGER;
SELECT id INTO project_found
FROM projects
WHERE id = NEW.repo_id;
IF (project_found IS NULL OR project_found = -1) THEN
SET NEW.id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_repo_milestone)
dbman.execute_sql_statement(trigger_insert_repo_milestone)
if dbman.check_table_exists("pull_requests"):
trigger_delete_insert_pull_request = """DROP TRIGGER IF EXISTS insert_pull_request;"""
trigger_insert_pull_request = """
CREATE TRIGGER insert_pull_request
BEFORE INSERT
ON pull_requests FOR EACH ROW
BEGIN
DECLARE project_found INTEGER;
SELECT id INTO project_found
FROM projects
WHERE id = NEW.base_repo_id;
IF (project_found IS NULL OR project_found = -1) THEN
SET NEW.id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_pull_request)
dbman.execute_sql_statement(trigger_insert_pull_request)
if dbman.check_table_exists("project_commits"):
trigger_delete_insert_project_commit = """DROP TRIGGER IF EXISTS insert_project_commit;"""
trigger_insert_project_commit = """
CREATE TRIGGER insert_project_commit
BEFORE INSERT
ON project_commits FOR EACH ROW
BEGIN
DECLARE project_found INTEGER;
SELECT id INTO project_found
FROM projects
WHERE id = NEW.project_id;
IF (project_found IS NULL OR project_found = -1) THEN
SET NEW.project_id = -1;
SET NEW.commit_id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_project_commit)
dbman.execute_sql_statement(trigger_insert_project_commit)
if dbman.check_table_exists("commit_parents"):
trigger_delete_insert_commit_parents = """DROP TRIGGER IF EXISTS insert_commit_parents;"""
trigger_insert_commit_parents = """
CREATE TRIGGER insert_commit_parents
BEFORE INSERT
ON commit_parents FOR EACH ROW
BEGIN
DECLARE commit_found INTEGER;
DECLARE commit_parent_found INTEGER;
SELECT id INTO commit_found
FROM commits
WHERE id = NEW.commit_id;
SELECT id INTO commit_parent_found
FROM commits
WHERE id = NEW.parent_id;
IF (commit_found IS NULL OR commit_found = -1 OR commit_parent_found IS NULL OR commit_parent_found = -1) THEN
SET NEW.commit_id = -1;
SET NEW.parent_id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_commit_parents)
dbman.execute_sql_statement(trigger_insert_commit_parents)
if dbman.check_table_exists("commit_comments"):
trigger_delete_insert_commit_comments = """DROP TRIGGER IF EXISTS insert_commit_comments;"""
trigger_insert_commit_comments = """
CREATE TRIGGER insert_commit_comments
BEFORE INSERT
ON commit_comments FOR EACH ROW
BEGIN
DECLARE commit_found INTEGER;
SELECT id INTO commit_found
FROM commits
WHERE id = NEW.commit_id;
IF (commit_found IS NULL OR commit_found = -1) THEN
SET NEW.id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_commit_comments)
dbman.execute_sql_statement(trigger_insert_commit_comments)
if dbman.check_table_exists("pull_request_history"):
trigger_delete_insert_pull_request_history = """DROP TRIGGER IF EXISTS insert_pull_request_history;"""
trigger_insert_pull_request_history = """
CREATE TRIGGER insert_pull_request_history
BEFORE INSERT
ON pull_request_history FOR EACH ROW
BEGIN
DECLARE pull_request_found INTEGER;
SELECT id INTO pull_request_found
FROM pull_requests
WHERE id = NEW.pull_request_id;
IF (pull_request_found IS NULL OR pull_request_found = -1) THEN
SET NEW.id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_pull_request_history)
dbman.execute_sql_statement(trigger_insert_pull_request_history)
if dbman.check_table_exists("pull_request_comments"):
trigger_delete_insert_pull_request_comment = """DROP TRIGGER IF EXISTS insert_pull_request_comment;"""
trigger_insert_pull_request_comment = """
CREATE TRIGGER insert_pull_request_comment
BEFORE INSERT
ON pull_request_comments FOR EACH ROW
BEGIN
DECLARE pull_request_found INTEGER;
SELECT id INTO pull_request_found
FROM pull_requests
WHERE id = NEW.pull_request_id;
IF (pull_request_found IS NULL OR pull_request_found = -1) THEN
SET NEW.comment_id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_pull_request_comment)
dbman.execute_sql_statement(trigger_insert_pull_request_comment)
if dbman.check_table_exists("pull_request_commits"):
trigger_delete_insert_pull_request_commit = """DROP TRIGGER IF EXISTS insert_pull_request_commit;"""
trigger_insert_pull_request_commit = """
CREATE TRIGGER insert_pull_request_commit
BEFORE INSERT
ON pull_request_commits FOR EACH ROW
BEGIN
DECLARE pull_request_found INTEGER;
SELECT id INTO pull_request_found
FROM pull_requests
WHERE id = NEW.pull_request_id;
IF (pull_request_found IS NULL OR pull_request_found = -1) THEN
SET NEW.pull_request_id = -1;
SET NEW.commit_id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_pull_request_commit)
dbman.execute_sql_statement(trigger_insert_pull_request_commit)
if dbman.check_table_exists("issue_labels"):
trigger_delete_insert_issue_label = """DROP TRIGGER IF EXISTS insert_issue_label;"""
trigger_insert_issue_label = """
CREATE TRIGGER insert_issue_label
BEFORE INSERT
ON issue_labels FOR EACH ROW
BEGIN
DECLARE issue_found INTEGER;
SELECT id INTO issue_found
FROM issues
WHERE id = NEW.issue_id;
IF (issue_found IS NULL OR issue_found = -1) THEN
SET NEW.issue_id = -1;
SET NEW.label_id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_issue_label)
dbman.execute_sql_statement(trigger_insert_issue_label)
if dbman.check_table_exists("issue_events"):
trigger_delete_insert_issue_event = """DROP TRIGGER IF EXISTS insert_issue_event;"""
trigger_insert_issue_event = """
CREATE TRIGGER insert_issue_event
BEFORE INSERT
ON issue_events FOR EACH ROW
BEGIN
DECLARE issue_found INTEGER;
SELECT id INTO issue_found
FROM issues
WHERE id = NEW.issue_id;
IF (issue_found IS NULL OR issue_found = -1) THEN
SET NEW.event_id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_issue_event)
dbman.execute_sql_statement(trigger_insert_issue_event)
if dbman.check_table_exists("issue_comments"):
trigger_delete_insert_issue_comment = """DROP TRIGGER IF EXISTS insert_issue_comment;"""
trigger_insert_issue_comment = """
CREATE TRIGGER insert_issue_comment
BEFORE INSERT
ON issue_comments FOR EACH ROW
BEGIN
DECLARE issue_found INTEGER;
SELECT id INTO issue_found
FROM issues
WHERE id = NEW.issue_id;
IF (issue_found IS NULL OR issue_found = -1) THEN
SET NEW.comment_id = -1;
END IF;
END;"""
dbman.execute_sql_statement(trigger_delete_insert_issue_comment)
dbman.execute_sql_statement(trigger_insert_issue_comment)
dbman.close_connection()
def init_database():
dbman.execute_sql_statement("DROP DATABASE IF EXISTS " + dbman.DATABASE)
dbman.execute_sql_statement("CREATE DATABASE " + dbman.DATABASE)
dbman.close_connection()
def main():
dbman.execute_sql_statement("USE " + dbman.DATABASE)
if dbman.check_table_exists("projects"):
dbman.execute_sql_statement("ALTER TABLE projects ADD INDEX forked_from (forked_from)")
dbman.execute_sql_statement("ALTER TABLE projects ADD INDEX deleted (deleted)")
# execute_sql_statement("ALTER TABLE projects ADD UNIQUE INDEX name (name, owner_id)")
# execute_sql_statement("ALTER TABLE projects ADD INDEX owner_id (owner_id)")
if dbman.check_table_exists("issues"):
dbman.execute_sql_statement("ALTER TABLE issues ADD INDEX repo_id (repo_id)")
dbman.execute_sql_statement("ALTER TABLE issues ADD INDEX issue_id (issue_id)")
# execute_sql_statement("ALTER TABLE issues ADD INDEX reporter_id (reporter_id)")
# execute_sql_statement("ALTER TABLE issues ADD INDEX assignee_id (assignee_id)")
# execute_sql_statement("ALTER TABLE issues ADD INDEX pull_request_id (pull_request_id)")
if dbman.check_table_exists("issue_events"):
dbman.execute_sql_statement("ALTER TABLE issue_events ADD INDEX issue_id (issue_id)")
dbman.execute_sql_statement("ALTER TABLE issue_events ADD INDEX action (action)")
# execute_sql_statement("ALTER TABLE issue_events ADD INDEX actor_id (actor_id)")
if dbman.check_table_exists("issue_labels"):
dbman.execute_sql_statement("ALTER TABLE issue_labels ADD INDEX label_id (label_id)")
if dbman.check_table_exists("repo_labels"):
dbman.execute_sql_statement("ALTER TABLE repo_labels ADD KEY repo_id (repo_id)")
if dbman.check_table_exists("pull_requests"):
dbman.execute_sql_statement("ALTER TABLE pull_requests ADD INDEX head_repo_id (head_repo_id)")
dbman.execute_sql_statement("ALTER TABLE pull_requests ADD INDEX base_repo_id (base_repo_id)")
# execute_sql_statement("ALTER TABLE pull_requests ADD INDEX merged (merged)")
# execute_sql_statement("ALTER TABLE pull_requests ADD INDEX user_id (user_id)")
# execute_sql_statement("ALTER TABLE pull_requests ADD INDEX head_commit_id (head_commit_id)")
# execute_sql_statement("ALTER TABLE pull_requests ADD INDEX base_commit_id (base_commit_id)")
# execute_sql_statement("ALTER TABLE pull_requests ADD INDEX idx_pullreq_id (pullreq_id)")
# execute_sql_statement("ALTER TABLE pull_requests ADD UNIQUE INDEX pullreq_id (pullreq_id, base_repo_id)")
if dbman.check_table_exists("pull_request_history"):
dbman.execute_sql_statement("ALTER TABLE pull_request_history ADD INDEX pull_request_id (pull_request_id)")
dbman.execute_sql_statement("ALTER TABLE pull_request_history ADD INDEX action (action)")
dbman.execute_sql_statement("ALTER TABLE pull_request_history ADD INDEX actor_id (actor_id)")
# if check_table_exists("users"):
# execute_sql_statement("ALTER TABLE users ADD INDEX user_email (email)")
# execute_sql_statement("ALTER TABLE users ADD UNIQUE INDEX login (login)")
if dbman.check_table_exists("issue_comments"):
dbman.execute_sql_statement("ALTER TABLE issue_comments ADD INDEX issue_id (issue_id)")
# execute_sql_statement("ALTER TABLE issue_comments ADD INDEX user_id (user_id)")
# if check_table_exists("watchers"):
# execute_sql_statement("ALTER TABLE watchers ADD INDEX user_id (user_id)")
if dbman.check_table_exists("repo_milestones"):
dbman.execute_sql_statement("ALTER TABLE repo_milestones ADD KEY repo_id (repo_id)")
if dbman.check_table_exists("pull_request_commits"):
dbman.execute_sql_statement("ALTER TABLE pull_request_commits ADD INDEX commit_id (commit_id)")
if dbman.check_table_exists("pull_request_comments"):
dbman.execute_sql_statement("ALTER TABLE pull_request_comments ADD INDEX pull_request_id (pull_request_id)")
# execute_sql_statement("ALTER TABLE pull_request_comments ADD INDEX user_id (user_id)")
# execute_sql_statement("ALTER TABLE pull_request_comments ADD INDEX commit_id (commit_id)")
# if check_table_exists("project_members"):
# execute_sql_statement("ALTER TABLE project_members ADD INDEX user_id (user_id)")
# if check_table_exists("project_commits"):
# execute_sql_statement("ALTER TABLE project_commits ADD INDEX commit_id (commit_id)")
# if check_table_exists("organization_members"):
# execute_sql_statement("ALTER TABLE organization_members ADD INDEX user_id (user_id)")
# if check_table_exists("forks"):
# execute_sql_statement("ALTER TABLE forks ADD INDEX forked_from_id (forked_from_id)")
# execute_sql_statement("ALTER TABLE forks ADD INDEX forked_project_id (forked_project_id)")
# if check_table_exists("followers"):
# execute_sql_statement("ALTER TABLE followers ADD INDEX follower_id (follower_id)")
if dbman.check_table_exists("commits"):
dbman.execute_sql_statement("ALTER TABLE commits ADD INDEX project_id (project_id)")
# execute_sql_statement("ALTER TABLE commits ADD UNIQUE INDEX sha (sha)")
# execute_sql_statement("ALTER TABLE commits ADD INDEX author_id (author_id)")
# execute_sql_statement("ALTER TABLE commits ADD INDEX committer_id (committer_id)")
# if check_table_exists("commit_parents"):
# execute_sql_statement("ALTER TABLE commit_parents ADD INDEX parent_id (parent_id)")
if dbman.check_table_exists("commit_comments"):
dbman.execute_sql_statement("ALTER TABLE commit_comments ADD INDEX commit_id (commit_id)")
# execute_sql_statement("ALTER TABLE commit_comments ADD UNIQUE INDEX comment_id (comment_id)")
# execute_sql_statement("ALTER TABLE commit_comments ADD INDEX user_id (user_id)")
dbman.close_connection()