{"action":"created","comment":{"url":"https://api.github.com/repos/analyst-collective/models/pulls/comments/54806285","id":54806285,"diff_hunk":"@@ -0,0 +1,64 @@\n+\n+import sqlparse, psycopg2, sys, os\n+\n+class Runner(object):\n+    def __init__(self, config, creds, models_dir):\n+        self.config = config\n+        self.creds = creds\n+        self.models_dir = models_dir\n+\n+        self.connection = psycopg2.connect(creds.conn_string)\n+\n+    def models(self):\n+        return self.config['models']\n+\n+    def drop_schema(self):\n+        sql = self.interpolate(\"drop schema if exists {schema} cascade\")\n+        self.execute(sql)\n+\n+    def create_schema(self):\n+        sql = self.interpolate(\"create schema {schema}\")\n+        self.execute(sql)\n+\n+    def clean_schema(self):\n+        self.drop_schema()\n+        self.create_schema()\n+\n+    def execute(self, sql):\n+        debug = sql.replace(\"\\n\", \" \").strip()[0:200]\n+        print \"Running: {}\".format(debug)\n+        with self.connection as connection:\n+            with connection.cursor() as cursor:\n+                cursor.execute(sql)\n+                print \"  {}\".format(cursor.statusmessage)\n+\n+    def interpolate(self, sql, model_name=\"\"):\n+        try:\n+            return sql.format(model=model_name, **self.config)\n+        except KeyError as e:\n+            print \"Error interpolating key: {{{error_key}}} in model: {model}\".format(error_key=str(e).replace(\"'\", \"\"), model=model_name)\n+            return None\n+\n+    def add_prefix(self, uninterpolated_sql, model):","path":"scripts/analyst_collective/runner.py","position":42,"original_position":42,"commit_id":"f5b5be6cbf057b83b38199b85783194dc22beced","original_commit_id":"f5b5be6cbf057b83b38199b85783194dc22beced","user":{"login":"drewbanin","id":1541139,"avatar_url":"https://avatars.githubusercontent.com/u/1541139?v=3","gravatar_id":"","url":"https://api.github.com/users/drewbanin","html_url":"https://github.com/drewbanin","followers_url":"https://api.github.com/users/drewbanin/followers","following_url":"https://api.github.com/users/drewbanin/following{/other_user}","gists_url":"https://api.github.com/users/drewbanin/gists{/gist_id}","starred_url":"https://api.github.com/users/drewbanin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/drewbanin/subscriptions","organizations_url":"https://api.github.com/users/drewbanin/orgs","repos_url":"https://api.github.com/users/drewbanin/repos","events_url":"https://api.github.com/users/drewbanin/events{/privacy}","received_events_url":"https://api.github.com/users/drewbanin/received_events","type":"User","site_admin":false},"body":"i don't like how this works at all, but it's something that I think needs to happen. Just threw it in here now as a proof-on-concept","created_at":"2016-03-02T22:47:54Z","updated_at":"2016-03-02T22:47:54Z","html_url":"https://github.com/analyst-collective/models/pull/5#discussion_r54806285","pull_request_url":"https://api.github.com/repos/analyst-collective/models/pulls/5","_links":{"self":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/comments/54806285"},"html":{"href":"https://github.com/analyst-collective/models/pull/5#discussion_r54806285"},"pull_request":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/5"}}},"pull_request":{"url":"https://api.github.com/repos/analyst-collective/models/pulls/5","id":61208863,"html_url":"https://github.com/analyst-collective/models/pull/5","diff_url":"https://github.com/analyst-collective/models/pull/5.diff","patch_url":"https://github.com/analyst-collective/models/pull/5.patch","issue_url":"https://api.github.com/repos/analyst-collective/models/issues/5","number":5,"state":"open","locked":false,"title":"Interface investigation","user":{"login":"drewbanin","id":1541139,"avatar_url":"https://avatars.githubusercontent.com/u/1541139?v=3","gravatar_id":"","url":"https://api.github.com/users/drewbanin","html_url":"https://github.com/drewbanin","followers_url":"https://api.github.com/users/drewbanin/followers","following_url":"https://api.github.com/users/drewbanin/following{/other_user}","gists_url":"https://api.github.com/users/drewbanin/gists{/gist_id}","starred_url":"https://api.github.com/users/drewbanin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/drewbanin/subscriptions","organizations_url":"https://api.github.com/users/drewbanin/orgs","repos_url":"https://api.github.com/users/drewbanin/repos","events_url":"https://api.github.com/users/drewbanin/events{/privacy}","received_events_url":"https://api.github.com/users/drewbanin/received_events","type":"User","site_admin":false},"body":"### Investigating interfaces\r\n\r\nI wrote a hacky python script as a proof of concept. There is _much_ to expand upon and clean up, so try your best to ignore the code itself. Right now, the script doesn't actually create/drop schemas/views, but that's a one-line change.\r\n\r\nThe script sorts the queries by filename, so there's `1_base.sql`, `2_filter.sql`, etc to resolve dependencies. I think we should be able to actually generate and walk a DAG instead (as @cmerrick mentioned) when the time comes.\r\n\r\nInterface hints are given as view comments:\r\n```sql\r\nCOMMENT ON VIEW pardot_analysis.visitoractivity_transformed IS 'timeseries,funnel,cohort';\r\n```\r\n\r\nI think this needs some more thought. Namely, I suspect that `visitoractivity_transformed` should expose the interface `event_stream`, and `event_stream` should expose `timeseries,funnel,cohort`. But again, we might not be there yet...\r\n\r\nOutstanding questions on my end:\r\n - are we enforcing the base/filter/transform paradigm? Or dropping it? Or ambivalent towards it?\r\n - do we have plans to use interface hinting for anything besides eventual validation? I have some ideas but I'm keen to hear your (analyst) collective thoughts.\r\n\r\nRespond!\r\n","created_at":"2016-03-01T08:35:14Z","updated_at":"2016-03-02T22:47:55Z","closed_at":null,"merged_at":null,"merge_commit_sha":"a909a115aa86955aa4209e22863ec216064387d8","assignee":{"login":"jthandy","id":2341841,"avatar_url":"https://avatars.githubusercontent.com/u/2341841?v=3","gravatar_id":"","url":"https://api.github.com/users/jthandy","html_url":"https://github.com/jthandy","followers_url":"https://api.github.com/users/jthandy/followers","following_url":"https://api.github.com/users/jthandy/following{/other_user}","gists_url":"https://api.github.com/users/jthandy/gists{/gist_id}","starred_url":"https://api.github.com/users/jthandy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jthandy/subscriptions","organizations_url":"https://api.github.com/users/jthandy/orgs","repos_url":"https://api.github.com/users/jthandy/repos","events_url":"https://api.github.com/users/jthandy/events{/privacy}","received_events_url":"https://api.github.com/users/jthandy/received_events","type":"User","site_admin":false},"milestone":null,"commits_url":"https://api.github.com/repos/analyst-collective/models/pulls/5/commits","review_comments_url":"https://api.github.com/repos/analyst-collective/models/pulls/5/comments","review_comment_url":"https://api.github.com/repos/analyst-collective/models/pulls/comments{/number}","comments_url":"https://api.github.com/repos/analyst-collective/models/issues/5/comments","statuses_url":"https://api.github.com/repos/analyst-collective/models/statuses/f5b5be6cbf057b83b38199b85783194dc22beced","head":{"label":"analyst-collective:interface-investigation","ref":"interface-investigation","sha":"f5b5be6cbf057b83b38199b85783194dc22beced","user":{"login":"analyst-collective","id":17324116,"avatar_url":"https://avatars.githubusercontent.com/u/17324116?v=3","gravatar_id":"","url":"https://api.github.com/users/analyst-collective","html_url":"https://github.com/analyst-collective","followers_url":"https://api.github.com/users/analyst-collective/followers","following_url":"https://api.github.com/users/analyst-collective/following{/other_user}","gists_url":"https://api.github.com/users/analyst-collective/gists{/gist_id}","starred_url":"https://api.github.com/users/analyst-collective/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/analyst-collective/subscriptions","organizations_url":"https://api.github.com/users/analyst-collective/orgs","repos_url":"https://api.github.com/users/analyst-collective/repos","events_url":"https://api.github.com/users/analyst-collective/events{/privacy}","received_events_url":"https://api.github.com/users/analyst-collective/received_events","type":"Organization","site_admin":false},"repo":{"id":52040684,"name":"models","full_name":"analyst-collective/models","owner":{"login":"analyst-collective","id":17324116,"avatar_url":"https://avatars.githubusercontent.com/u/17324116?v=3","gravatar_id":"","url":"https://api.github.com/users/analyst-collective","html_url":"https://github.com/analyst-collective","followers_url":"https://api.github.com/users/analyst-collective/followers","following_url":"https://api.github.com/users/analyst-collective/following{/other_user}","gists_url":"https://api.github.com/users/analyst-collective/gists{/gist_id}","starred_url":"https://api.github.com/users/analyst-collective/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/analyst-collective/subscriptions","organizations_url":"https://api.github.com/users/analyst-collective/orgs","repos_url":"https://api.github.com/users/analyst-collective/repos","events_url":"https://api.github.com/users/analyst-collective/events{/privacy}","received_events_url":"https://api.github.com/users/analyst-collective/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/analyst-collective/models","description":"","fork":false,"url":"https://api.github.com/repos/analyst-collective/models","forks_url":"https://api.github.com/repos/analyst-collective/models/forks","keys_url":"https://api.github.com/repos/analyst-collective/models/keys{/key_id}","collaborators_url":"https://api.github.com/repos/analyst-collective/models/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/analyst-collective/models/teams","hooks_url":"https://api.github.com/repos/analyst-collective/models/hooks","issue_events_url":"https://api.github.com/repos/analyst-collective/models/issues/events{/number}","events_url":"https://api.github.com/repos/analyst-collective/models/events","assignees_url":"https://api.github.com/repos/analyst-collective/models/assignees{/user}","branches_url":"https://api.github.com/repos/analyst-collective/models/branches{/branch}","tags_url":"https://api.github.com/repos/analyst-collective/models/tags","blobs_url":"https://api.github.com/repos/analyst-collective/models/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/analyst-collective/models/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/analyst-collective/models/git/refs{/sha}","trees_url":"https://api.github.com/repos/analyst-collective/models/git/trees{/sha}","statuses_url":"https://api.github.com/repos/analyst-collective/models/statuses/{sha}","languages_url":"https://api.github.com/repos/analyst-collective/models/languages","stargazers_url":"https://api.github.com/repos/analyst-collective/models/stargazers","contributors_url":"https://api.github.com/repos/analyst-collective/models/contributors","subscribers_url":"https://api.github.com/repos/analyst-collective/models/subscribers","subscription_url":"https://api.github.com/repos/analyst-collective/models/subscription","commits_url":"https://api.github.com/repos/analyst-collective/models/commits{/sha}","git_commits_url":"https://api.github.com/repos/analyst-collective/models/git/commits{/sha}","comments_url":"https://api.github.com/repos/analyst-collective/models/comments{/number}","issue_comment_url":"https://api.github.com/repos/analyst-collective/models/issues/comments{/number}","contents_url":"https://api.github.com/repos/analyst-collective/models/contents/{+path}","compare_url":"https://api.github.com/repos/analyst-collective/models/compare/{base}...{head}","merges_url":"https://api.github.com/repos/analyst-collective/models/merges","archive_url":"https://api.github.com/repos/analyst-collective/models/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/analyst-collective/models/downloads","issues_url":"https://api.github.com/repos/analyst-collective/models/issues{/number}","pulls_url":"https://api.github.com/repos/analyst-collective/models/pulls{/number}","milestones_url":"https://api.github.com/repos/analyst-collective/models/milestones{/number}","notifications_url":"https://api.github.com/repos/analyst-collective/models/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/analyst-collective/models/labels{/name}","releases_url":"https://api.github.com/repos/analyst-collective/models/releases{/id}","deployments_url":"https://api.github.com/repos/analyst-collective/models/deployments","created_at":"2016-02-18T21:38:59Z","updated_at":"2016-02-25T23:53:49Z","pushed_at":"2016-03-02T22:45:18Z","git_url":"git://github.com/analyst-collective/models.git","ssh_url":"git@github.com:analyst-collective/models.git","clone_url":"https://github.com/analyst-collective/models.git","svn_url":"https://github.com/analyst-collective/models","homepage":null,"size":117,"stargazers_count":1,"watchers_count":1,"language":null,"has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":1,"mirror_url":null,"open_issues_count":2,"forks":1,"open_issues":2,"watchers":1,"default_branch":"master"}},"base":{"label":"analyst-collective:master","ref":"master","sha":"ebea61dadbbfd031ec34e39a0f260450b02ce78a","user":{"login":"analyst-collective","id":17324116,"avatar_url":"https://avatars.githubusercontent.com/u/17324116?v=3","gravatar_id":"","url":"https://api.github.com/users/analyst-collective","html_url":"https://github.com/analyst-collective","followers_url":"https://api.github.com/users/analyst-collective/followers","following_url":"https://api.github.com/users/analyst-collective/following{/other_user}","gists_url":"https://api.github.com/users/analyst-collective/gists{/gist_id}","starred_url":"https://api.github.com/users/analyst-collective/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/analyst-collective/subscriptions","organizations_url":"https://api.github.com/users/analyst-collective/orgs","repos_url":"https://api.github.com/users/analyst-collective/repos","events_url":"https://api.github.com/users/analyst-collective/events{/privacy}","received_events_url":"https://api.github.com/users/analyst-collective/received_events","type":"Organization","site_admin":false},"repo":{"id":52040684,"name":"models","full_name":"analyst-collective/models","owner":{"login":"analyst-collective","id":17324116,"avatar_url":"https://avatars.githubusercontent.com/u/17324116?v=3","gravatar_id":"","url":"https://api.github.com/users/analyst-collective","html_url":"https://github.com/analyst-collective","followers_url":"https://api.github.com/users/analyst-collective/followers","following_url":"https://api.github.com/users/analyst-collective/following{/other_user}","gists_url":"https://api.github.com/users/analyst-collective/gists{/gist_id}","starred_url":"https://api.github.com/users/analyst-collective/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/analyst-collective/subscriptions","organizations_url":"https://api.github.com/users/analyst-collective/orgs","repos_url":"https://api.github.com/users/analyst-collective/repos","events_url":"https://api.github.com/users/analyst-collective/events{/privacy}","received_events_url":"https://api.github.com/users/analyst-collective/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/analyst-collective/models","description":"","fork":false,"url":"https://api.github.com/repos/analyst-collective/models","forks_url":"https://api.github.com/repos/analyst-collective/models/forks","keys_url":"https://api.github.com/repos/analyst-collective/models/keys{/key_id}","collaborators_url":"https://api.github.com/repos/analyst-collective/models/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/analyst-collective/models/teams","hooks_url":"https://api.github.com/repos/analyst-collective/models/hooks","issue_events_url":"https://api.github.com/repos/analyst-collective/models/issues/events{/number}","events_url":"https://api.github.com/repos/analyst-collective/models/events","assignees_url":"https://api.github.com/repos/analyst-collective/models/assignees{/user}","branches_url":"https://api.github.com/repos/analyst-collective/models/branches{/branch}","tags_url":"https://api.github.com/repos/analyst-collective/models/tags","blobs_url":"https://api.github.com/repos/analyst-collective/models/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/analyst-collective/models/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/analyst-collective/models/git/refs{/sha}","trees_url":"https://api.github.com/repos/analyst-collective/models/git/trees{/sha}","statuses_url":"https://api.github.com/repos/analyst-collective/models/statuses/{sha}","languages_url":"https://api.github.com/repos/analyst-collective/models/languages","stargazers_url":"https://api.github.com/repos/analyst-collective/models/stargazers","contributors_url":"https://api.github.com/repos/analyst-collective/models/contributors","subscribers_url":"https://api.github.com/repos/analyst-collective/models/subscribers","subscription_url":"https://api.github.com/repos/analyst-collective/models/subscription","commits_url":"https://api.github.com/repos/analyst-collective/models/commits{/sha}","git_commits_url":"https://api.github.com/repos/analyst-collective/models/git/commits{/sha}","comments_url":"https://api.github.com/repos/analyst-collective/models/comments{/number}","issue_comment_url":"https://api.github.com/repos/analyst-collective/models/issues/comments{/number}","contents_url":"https://api.github.com/repos/analyst-collective/models/contents/{+path}","compare_url":"https://api.github.com/repos/analyst-collective/models/compare/{base}...{head}","merges_url":"https://api.github.com/repos/analyst-collective/models/merges","archive_url":"https://api.github.com/repos/analyst-collective/models/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/analyst-collective/models/downloads","issues_url":"https://api.github.com/repos/analyst-collective/models/issues{/number}","pulls_url":"https://api.github.com/repos/analyst-collective/models/pulls{/number}","milestones_url":"https://api.github.com/repos/analyst-collective/models/milestones{/number}","notifications_url":"https://api.github.com/repos/analyst-collective/models/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/analyst-collective/models/labels{/name}","releases_url":"https://api.github.com/repos/analyst-collective/models/releases{/id}","deployments_url":"https://api.github.com/repos/analyst-collective/models/deployments","created_at":"2016-02-18T21:38:59Z","updated_at":"2016-02-25T23:53:49Z","pushed_at":"2016-03-02T22:45:18Z","git_url":"git://github.com/analyst-collective/models.git","ssh_url":"git@github.com:analyst-collective/models.git","clone_url":"https://github.com/analyst-collective/models.git","svn_url":"https://github.com/analyst-collective/models","homepage":null,"size":117,"stargazers_count":1,"watchers_count":1,"language":null,"has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":1,"mirror_url":null,"open_issues_count":2,"forks":1,"open_issues":2,"watchers":1,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/5"},"html":{"href":"https://github.com/analyst-collective/models/pull/5"},"issue":{"href":"https://api.github.com/repos/analyst-collective/models/issues/5"},"comments":{"href":"https://api.github.com/repos/analyst-collective/models/issues/5/comments"},"review_comments":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/5/comments"},"review_comment":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/analyst-collective/models/pulls/5/commits"},"statuses":{"href":"https://api.github.com/repos/analyst-collective/models/statuses/f5b5be6cbf057b83b38199b85783194dc22beced"}}}}