{"action":"created","comment":{"url":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/comments/91155872","pull_request_review_id":11656791,"id":91155872,"diff_hunk":"@@ -0,0 +1,188 @@\n+#!/usr/bin/python\n+# -*- coding: utf-8 -*-\n+\n+from __future__ import print_function\n+from paramiko import SSHClient, AutoAddPolicy, SSHException\n+from os.path import isfile, join\n+from colorama import Fore, deinit\n+import os, sys, getopt, hashlib, datetime, time\n+\n+DB_NAME = os.environ[\"DB_NAME\"]\n+DB_USER = os.environ[\"DB_USER\"]\n+DB_PASS = os.environ[\"DB_PASS\"]\n+\n+SQL_SCRIPTS_DIR = \"./SQL\"\n+\n+USERNAME = \"teamcity\"\n+\n+# SQL_SCRIPT_LOG_FILE = \"executed-mysql-files.log\"\n+SQL_SCRIPTS_DEPLOYMENT_LOG_FILE = \"mysql-scripts-deployment.log\"\n+\n+INSERT_CMD = \"INSERT INTO CR_Scripts (Name, MD5, executed) VALUES(\\\"%s\\\", \\\"%s\\\", %d) ON DUPLICATE KEY UPDATE MD5=\\\"%s\\\", executed=%d;\"\n+\n+def getCurrentTimeStr():\n+\treturn time.strftime(\"%c\")\n+\n+def log(fileObj, msg):\n+\tfileObj.write(msg + \"\\n\")\n+\n+def generateMD5(fileObj):\n+\tmd5 = hashlib.md5()\n+\n+\tfor chunk in iter(lambda: fileObj.read(4096), b''):\n+\t\tmd5.update(chunk)\n+\n+\treturn md5.hexdigest()\n+\n+def retrieveMySqlLogInfo(filename):\n+\texecutedSqlFiles = set()\n+\n+\twith open(filename, 'r') as f:\n+\t\tfor line in f:\n+\t\t\texecutedSqlFiles.add(line.strip('\\n'))\n+\n+\treturn executedSqlFiles\n+\n+def readSqlFiles(directory):\n+\tsqlFiles = [f for f in os.listdir(directory)]\n+\n+\treturn sqlFiles\n+\n+def extractSqlScriptContent(filename):\n+\twith open(filename, 'r') as sqlFile:\n+\t\tdata = sqlFile.read()\n+\n+\treturn data\n+\n+def processErrors(logFile, errors):\n+\tfor line in errors:\n+\t\tlog(logFile, \"[ERROR]: \" + line)\n+\t\tprint(Fore.RED + line, \"\\n\")\n+\n+\t# exit program\n+\tprint(Fore.GREEN + \"Exiting deployment ...\")\n+\tlog(logFile, '\\n')\n+\n+def executeRemoteDBCmd(sshClient, sqlCmd):\n+\tstdin, stdout, stderr = sshClient.exec_command(\"mysql -D % s -u %s -p%s\" % (DB_NAME, DB_USER, DB_PASS))\n+\n+\tstdin.write(sqlCmd)\n+\tstdin.flush()\n+\tstdin.channel.shutdown_write()\n+\n+\tresult = stdout.readlines()\n+\tstdout.channel.shutdown_read()\n+\n+\terrors = stderr.readlines()\n+\tstderr.channel.shutdown_read()\n+\n+\texitCode = 0\n+\n+\tif len(errors) != 0:\n+\t\texitCode = 1\n+\n+\t# return a tuple of exit code and errors\n+\treturn (exitCode, errors, result)\n+\n+def deploySqlScripts(sshClient, files):\n+\tstart = time.time()\n+\n+\tdeploymentLog = open(SQL_SCRIPTS_DEPLOYMENT_LOG_FILE, 'a')\n+\t# sqlScriptLog = open(SQL_SCRIPT_LOG_FILE, 'a')\n+\n+\t# deploy all the new sql files\n+\tfor script, scriptHash in files.items():\n+\t\tlog(deploymentLog, getCurrentTimeStr())\n+\t\tlog(deploymentLog, \"Executing sql file: %s\" % script)\n+\n+\t\tprint(Fore.YELLOW + \"Executing sql file: %s with hash value  %s\" % (script, scriptHash))\n+\n+\t\texitCode, errors, result = executeRemoteDBCmd(sshClient, extractSqlScriptContent(join(SQL_SCRIPTS_DIR, script)))\n+\n+\t\tif exitCode != 0:\n+\t\t\tprocessErrors(deploymentLog, errors)\n+\t\t\treturn exitCode","path":"CI/cms-sql-files-deploy.py","position":104,"original_position":104,"commit_id":"e2b100072fa7106e7fcc88cca03c3a55db4a005a","original_commit_id":"e2b100072fa7106e7fcc88cca03c3a55db4a005a","user":{"login":"charlie-retzler","id":3252288,"avatar_url":"https://avatars.githubusercontent.com/u/3252288?v=3","gravatar_id":"","url":"https://api.github.com/users/charlie-retzler","html_url":"https://github.com/charlie-retzler","followers_url":"https://api.github.com/users/charlie-retzler/followers","following_url":"https://api.github.com/users/charlie-retzler/following{/other_user}","gists_url":"https://api.github.com/users/charlie-retzler/gists{/gist_id}","starred_url":"https://api.github.com/users/charlie-retzler/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/charlie-retzler/subscriptions","organizations_url":"https://api.github.com/users/charlie-retzler/orgs","repos_url":"https://api.github.com/users/charlie-retzler/repos","events_url":"https://api.github.com/users/charlie-retzler/events{/privacy}","received_events_url":"https://api.github.com/users/charlie-retzler/received_events","type":"User","site_admin":false},"body":"Also in SQL Server version we continue execute other scripts that haven't been run. Not sure if we should do the same or not.","created_at":"2016-12-06T19:42:10Z","updated_at":"2016-12-06T19:42:10Z","html_url":"https://github.com/crdschurch/SS-CMS/pull/200#discussion_r91155872","pull_request_url":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/200","_links":{"self":{"href":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/comments/91155872"},"html":{"href":"https://github.com/crdschurch/SS-CMS/pull/200#discussion_r91155872"},"pull_request":{"href":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/200"}}},"pull_request":{"url":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/200","id":96607756,"html_url":"https://github.com/crdschurch/SS-CMS/pull/200","diff_url":"https://github.com/crdschurch/SS-CMS/pull/200.diff","patch_url":"https://github.com/crdschurch/SS-CMS/pull/200.patch","issue_url":"https://api.github.com/repos/crdschurch/SS-CMS/issues/200","number":200,"state":"open","locked":false,"title":"add script for auto deployment of cms sql scripts","user":{"login":"aiqingyuyan","id":8572946,"avatar_url":"https://avatars.githubusercontent.com/u/8572946?v=3","gravatar_id":"","url":"https://api.github.com/users/aiqingyuyan","html_url":"https://github.com/aiqingyuyan","followers_url":"https://api.github.com/users/aiqingyuyan/followers","following_url":"https://api.github.com/users/aiqingyuyan/following{/other_user}","gists_url":"https://api.github.com/users/aiqingyuyan/gists{/gist_id}","starred_url":"https://api.github.com/users/aiqingyuyan/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aiqingyuyan/subscriptions","organizations_url":"https://api.github.com/users/aiqingyuyan/orgs","repos_url":"https://api.github.com/users/aiqingyuyan/repos","events_url":"https://api.github.com/users/aiqingyuyan/events{/privacy}","received_events_url":"https://api.github.com/users/aiqingyuyan/received_events","type":"User","site_admin":false},"body":"","created_at":"2016-12-05T20:57:55Z","updated_at":"2016-12-06T19:55:02Z","closed_at":null,"merged_at":null,"merge_commit_sha":"04d7c14ed1e08b70f40ea1ccfa458e2e265bda1b","assignee":{"login":"charlie-retzler","id":3252288,"avatar_url":"https://avatars.githubusercontent.com/u/3252288?v=3","gravatar_id":"","url":"https://api.github.com/users/charlie-retzler","html_url":"https://github.com/charlie-retzler","followers_url":"https://api.github.com/users/charlie-retzler/followers","following_url":"https://api.github.com/users/charlie-retzler/following{/other_user}","gists_url":"https://api.github.com/users/charlie-retzler/gists{/gist_id}","starred_url":"https://api.github.com/users/charlie-retzler/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/charlie-retzler/subscriptions","organizations_url":"https://api.github.com/users/charlie-retzler/orgs","repos_url":"https://api.github.com/users/charlie-retzler/repos","events_url":"https://api.github.com/users/charlie-retzler/events{/privacy}","received_events_url":"https://api.github.com/users/charlie-retzler/received_events","type":"User","site_admin":false},"assignees":[{"login":"charlie-retzler","id":3252288,"avatar_url":"https://avatars.githubusercontent.com/u/3252288?v=3","gravatar_id":"","url":"https://api.github.com/users/charlie-retzler","html_url":"https://github.com/charlie-retzler","followers_url":"https://api.github.com/users/charlie-retzler/followers","following_url":"https://api.github.com/users/charlie-retzler/following{/other_user}","gists_url":"https://api.github.com/users/charlie-retzler/gists{/gist_id}","starred_url":"https://api.github.com/users/charlie-retzler/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/charlie-retzler/subscriptions","organizations_url":"https://api.github.com/users/charlie-retzler/orgs","repos_url":"https://api.github.com/users/charlie-retzler/repos","events_url":"https://api.github.com/users/charlie-retzler/events{/privacy}","received_events_url":"https://api.github.com/users/charlie-retzler/received_events","type":"User","site_admin":false}],"milestone":null,"commits_url":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/200/commits","review_comments_url":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/200/comments","review_comment_url":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/comments{/number}","comments_url":"https://api.github.com/repos/crdschurch/SS-CMS/issues/200/comments","statuses_url":"https://api.github.com/repos/crdschurch/SS-CMS/statuses/e2b100072fa7106e7fcc88cca03c3a55db4a005a","head":{"label":"crdschurch:feature/US4328-automate-deployment-cms-scripts","ref":"feature/US4328-automate-deployment-cms-scripts","sha":"e2b100072fa7106e7fcc88cca03c3a55db4a005a","user":{"login":"crdschurch","id":1843144,"avatar_url":"https://avatars.githubusercontent.com/u/1843144?v=3","gravatar_id":"","url":"https://api.github.com/users/crdschurch","html_url":"https://github.com/crdschurch","followers_url":"https://api.github.com/users/crdschurch/followers","following_url":"https://api.github.com/users/crdschurch/following{/other_user}","gists_url":"https://api.github.com/users/crdschurch/gists{/gist_id}","starred_url":"https://api.github.com/users/crdschurch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/crdschurch/subscriptions","organizations_url":"https://api.github.com/users/crdschurch/orgs","repos_url":"https://api.github.com/users/crdschurch/repos","events_url":"https://api.github.com/users/crdschurch/events{/privacy}","received_events_url":"https://api.github.com/users/crdschurch/received_events","type":"Organization","site_admin":false},"repo":{"id":32023532,"name":"SS-CMS","full_name":"crdschurch/SS-CMS","owner":{"login":"crdschurch","id":1843144,"avatar_url":"https://avatars.githubusercontent.com/u/1843144?v=3","gravatar_id":"","url":"https://api.github.com/users/crdschurch","html_url":"https://github.com/crdschurch","followers_url":"https://api.github.com/users/crdschurch/followers","following_url":"https://api.github.com/users/crdschurch/following{/other_user}","gists_url":"https://api.github.com/users/crdschurch/gists{/gist_id}","starred_url":"https://api.github.com/users/crdschurch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/crdschurch/subscriptions","organizations_url":"https://api.github.com/users/crdschurch/orgs","repos_url":"https://api.github.com/users/crdschurch/repos","events_url":"https://api.github.com/users/crdschurch/events{/privacy}","received_events_url":"https://api.github.com/users/crdschurch/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/crdschurch/SS-CMS","description":"Crossroads implementation of Silverstripe","fork":false,"url":"https://api.github.com/repos/crdschurch/SS-CMS","forks_url":"https://api.github.com/repos/crdschurch/SS-CMS/forks","keys_url":"https://api.github.com/repos/crdschurch/SS-CMS/keys{/key_id}","collaborators_url":"https://api.github.com/repos/crdschurch/SS-CMS/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/crdschurch/SS-CMS/teams","hooks_url":"https://api.github.com/repos/crdschurch/SS-CMS/hooks","issue_events_url":"https://api.github.com/repos/crdschurch/SS-CMS/issues/events{/number}","events_url":"https://api.github.com/repos/crdschurch/SS-CMS/events","assignees_url":"https://api.github.com/repos/crdschurch/SS-CMS/assignees{/user}","branches_url":"https://api.github.com/repos/crdschurch/SS-CMS/branches{/branch}","tags_url":"https://api.github.com/repos/crdschurch/SS-CMS/tags","blobs_url":"https://api.github.com/repos/crdschurch/SS-CMS/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/crdschurch/SS-CMS/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/crdschurch/SS-CMS/git/refs{/sha}","trees_url":"https://api.github.com/repos/crdschurch/SS-CMS/git/trees{/sha}","statuses_url":"https://api.github.com/repos/crdschurch/SS-CMS/statuses/{sha}","languages_url":"https://api.github.com/repos/crdschurch/SS-CMS/languages","stargazers_url":"https://api.github.com/repos/crdschurch/SS-CMS/stargazers","contributors_url":"https://api.github.com/repos/crdschurch/SS-CMS/contributors","subscribers_url":"https://api.github.com/repos/crdschurch/SS-CMS/subscribers","subscription_url":"https://api.github.com/repos/crdschurch/SS-CMS/subscription","commits_url":"https://api.github.com/repos/crdschurch/SS-CMS/commits{/sha}","git_commits_url":"https://api.github.com/repos/crdschurch/SS-CMS/git/commits{/sha}","comments_url":"https://api.github.com/repos/crdschurch/SS-CMS/comments{/number}","issue_comment_url":"https://api.github.com/repos/crdschurch/SS-CMS/issues/comments{/number}","contents_url":"https://api.github.com/repos/crdschurch/SS-CMS/contents/{+path}","compare_url":"https://api.github.com/repos/crdschurch/SS-CMS/compare/{base}...{head}","merges_url":"https://api.github.com/repos/crdschurch/SS-CMS/merges","archive_url":"https://api.github.com/repos/crdschurch/SS-CMS/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/crdschurch/SS-CMS/downloads","issues_url":"https://api.github.com/repos/crdschurch/SS-CMS/issues{/number}","pulls_url":"https://api.github.com/repos/crdschurch/SS-CMS/pulls{/number}","milestones_url":"https://api.github.com/repos/crdschurch/SS-CMS/milestones{/number}","notifications_url":"https://api.github.com/repos/crdschurch/SS-CMS/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/crdschurch/SS-CMS/labels{/name}","releases_url":"https://api.github.com/repos/crdschurch/SS-CMS/releases{/id}","deployments_url":"https://api.github.com/repos/crdschurch/SS-CMS/deployments","created_at":"2015-03-11T14:56:07Z","updated_at":"2016-10-27T13:22:05Z","pushed_at":"2016-12-06T16:22:59Z","git_url":"git://github.com/crdschurch/SS-CMS.git","ssh_url":"git@github.com:crdschurch/SS-CMS.git","clone_url":"https://github.com/crdschurch/SS-CMS.git","svn_url":"https://github.com/crdschurch/SS-CMS","homepage":null,"size":501,"stargazers_count":1,"watchers_count":1,"language":"PHP","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":1,"forks":0,"open_issues":1,"watchers":1,"default_branch":"development"}},"base":{"label":"crdschurch:development","ref":"development","sha":"e2d0b5b6341b351e062cc96e39c011322857d1cf","user":{"login":"crdschurch","id":1843144,"avatar_url":"https://avatars.githubusercontent.com/u/1843144?v=3","gravatar_id":"","url":"https://api.github.com/users/crdschurch","html_url":"https://github.com/crdschurch","followers_url":"https://api.github.com/users/crdschurch/followers","following_url":"https://api.github.com/users/crdschurch/following{/other_user}","gists_url":"https://api.github.com/users/crdschurch/gists{/gist_id}","starred_url":"https://api.github.com/users/crdschurch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/crdschurch/subscriptions","organizations_url":"https://api.github.com/users/crdschurch/orgs","repos_url":"https://api.github.com/users/crdschurch/repos","events_url":"https://api.github.com/users/crdschurch/events{/privacy}","received_events_url":"https://api.github.com/users/crdschurch/received_events","type":"Organization","site_admin":false},"repo":{"id":32023532,"name":"SS-CMS","full_name":"crdschurch/SS-CMS","owner":{"login":"crdschurch","id":1843144,"avatar_url":"https://avatars.githubusercontent.com/u/1843144?v=3","gravatar_id":"","url":"https://api.github.com/users/crdschurch","html_url":"https://github.com/crdschurch","followers_url":"https://api.github.com/users/crdschurch/followers","following_url":"https://api.github.com/users/crdschurch/following{/other_user}","gists_url":"https://api.github.com/users/crdschurch/gists{/gist_id}","starred_url":"https://api.github.com/users/crdschurch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/crdschurch/subscriptions","organizations_url":"https://api.github.com/users/crdschurch/orgs","repos_url":"https://api.github.com/users/crdschurch/repos","events_url":"https://api.github.com/users/crdschurch/events{/privacy}","received_events_url":"https://api.github.com/users/crdschurch/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/crdschurch/SS-CMS","description":"Crossroads implementation of Silverstripe","fork":false,"url":"https://api.github.com/repos/crdschurch/SS-CMS","forks_url":"https://api.github.com/repos/crdschurch/SS-CMS/forks","keys_url":"https://api.github.com/repos/crdschurch/SS-CMS/keys{/key_id}","collaborators_url":"https://api.github.com/repos/crdschurch/SS-CMS/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/crdschurch/SS-CMS/teams","hooks_url":"https://api.github.com/repos/crdschurch/SS-CMS/hooks","issue_events_url":"https://api.github.com/repos/crdschurch/SS-CMS/issues/events{/number}","events_url":"https://api.github.com/repos/crdschurch/SS-CMS/events","assignees_url":"https://api.github.com/repos/crdschurch/SS-CMS/assignees{/user}","branches_url":"https://api.github.com/repos/crdschurch/SS-CMS/branches{/branch}","tags_url":"https://api.github.com/repos/crdschurch/SS-CMS/tags","blobs_url":"https://api.github.com/repos/crdschurch/SS-CMS/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/crdschurch/SS-CMS/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/crdschurch/SS-CMS/git/refs{/sha}","trees_url":"https://api.github.com/repos/crdschurch/SS-CMS/git/trees{/sha}","statuses_url":"https://api.github.com/repos/crdschurch/SS-CMS/statuses/{sha}","languages_url":"https://api.github.com/repos/crdschurch/SS-CMS/languages","stargazers_url":"https://api.github.com/repos/crdschurch/SS-CMS/stargazers","contributors_url":"https://api.github.com/repos/crdschurch/SS-CMS/contributors","subscribers_url":"https://api.github.com/repos/crdschurch/SS-CMS/subscribers","subscription_url":"https://api.github.com/repos/crdschurch/SS-CMS/subscription","commits_url":"https://api.github.com/repos/crdschurch/SS-CMS/commits{/sha}","git_commits_url":"https://api.github.com/repos/crdschurch/SS-CMS/git/commits{/sha}","comments_url":"https://api.github.com/repos/crdschurch/SS-CMS/comments{/number}","issue_comment_url":"https://api.github.com/repos/crdschurch/SS-CMS/issues/comments{/number}","contents_url":"https://api.github.com/repos/crdschurch/SS-CMS/contents/{+path}","compare_url":"https://api.github.com/repos/crdschurch/SS-CMS/compare/{base}...{head}","merges_url":"https://api.github.com/repos/crdschurch/SS-CMS/merges","archive_url":"https://api.github.com/repos/crdschurch/SS-CMS/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/crdschurch/SS-CMS/downloads","issues_url":"https://api.github.com/repos/crdschurch/SS-CMS/issues{/number}","pulls_url":"https://api.github.com/repos/crdschurch/SS-CMS/pulls{/number}","milestones_url":"https://api.github.com/repos/crdschurch/SS-CMS/milestones{/number}","notifications_url":"https://api.github.com/repos/crdschurch/SS-CMS/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/crdschurch/SS-CMS/labels{/name}","releases_url":"https://api.github.com/repos/crdschurch/SS-CMS/releases{/id}","deployments_url":"https://api.github.com/repos/crdschurch/SS-CMS/deployments","created_at":"2015-03-11T14:56:07Z","updated_at":"2016-10-27T13:22:05Z","pushed_at":"2016-12-06T16:22:59Z","git_url":"git://github.com/crdschurch/SS-CMS.git","ssh_url":"git@github.com:crdschurch/SS-CMS.git","clone_url":"https://github.com/crdschurch/SS-CMS.git","svn_url":"https://github.com/crdschurch/SS-CMS","homepage":null,"size":501,"stargazers_count":1,"watchers_count":1,"language":"PHP","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":1,"forks":0,"open_issues":1,"watchers":1,"default_branch":"development"}},"_links":{"self":{"href":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/200"},"html":{"href":"https://github.com/crdschurch/SS-CMS/pull/200"},"issue":{"href":"https://api.github.com/repos/crdschurch/SS-CMS/issues/200"},"comments":{"href":"https://api.github.com/repos/crdschurch/SS-CMS/issues/200/comments"},"review_comments":{"href":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/200/comments"},"review_comment":{"href":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/crdschurch/SS-CMS/pulls/200/commits"},"statuses":{"href":"https://api.github.com/repos/crdschurch/SS-CMS/statuses/e2b100072fa7106e7fcc88cca03c3a55db4a005a"}}}}
{"actor":{"display_login":"charlie-retzler"}}