steampipe external contributors

steampipe-external-contributors.py

import json, psycopg2, psycopg2.extras, os, re
conn_str = f"host='localhost' dbname='steampipe' user='steampipe' port='9193' password='{os.getenv('STEAMPIPE_LOCAL_PASSWORD')}'"

def get_repos():
  sql = f"""
    select 
      full_name
    from
      github_my_repository
    where
      full_name = 'turbot/steampipe'
      or full_name ~ 'turbot/steampipe-(mod|plugin)'
    order by
      full_name
  """
  print(sql)
  r = sql_read(sql)
  return r

def sql_read(sql):
  conn = psycopg2.connect(conn_str)
  try:
    cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)    
    cur.execute(sql)
    r = cur.fetchall()
    cur.close()
  except(Exception, psycopg2.DatabaseError) as error:
    print(error)
  finally:
    conn.close()
  return r

def sql_write(sql):
  conn = psycopg2.connect(conn_str)
  try:
    cur = conn.cursor()
    cur.execute(sql)
    cur.close()
    conn.commit()
  except(Exception, psycopg2.DatabaseError) as error:
    print(error)
  finally:
    conn.close()

def create_org_members_table(org):
  sql = f"""
    create table {org}_members as 
      select
        jsonb_array_elements_text(g.member_logins) as member_login
      from
        github_organization g
      where
        login = '{org}'
  """
  print(sql)
  sql_write(sql)
  
def create_commits_table_for_repo(org, repo_name, table_name):
  sql = f"""
    create table {table_name}_commits as 
      select
        sha,
        html_url,
        repository_full_name,
        author_login,
        author_date,
        message,
        commit->'author'->>'email' as author_email,
        commit->'author'->>'name' as author_name,
        committer_login,
        commit->'committer'->'email'->>'email' as committer_email,
        commit->'committer'->'name'->>'name' as committer_name
      from
        github_commit
      where
        repository_full_name = '{org}/{repo_name}'
  """
  print(sql)
  sql_write(sql)

def create_commit_tables_for_all_repos():
  repos = get_repos()
  for repo in repos:
    full_name = repo['full_name']
    match = re.match('(.+)/(.+)', full_name).groups()
    org = match[0]
    repo_name = match[1]
    table_name = repo_name.replace('-', '_')
    create_commits_table_for_repo(org, repo_name, table_name)

def drop_commit_tables_for_all_repos():
  repos = get_repos()
  for repo in repos:
    full_name = repo['full_name']
    match = re.match('(.+)/(.+)', full_name).groups()
    org = match[0]
    repo_name = match[1].replace('-','_')
    sql = f"drop table {repo_name}_commits"
    print(sql)
    sql_write(sql)

def build_table_blocks_for_all_repos():
  table_blocks = []
  repos = get_repos()
  for repo in repos:
    full_name = repo['full_name']
    match = re.match('(.+)/(.+)', full_name).groups()
    org = match[0]
    repo_name = match[1].replace('-','_')
    table_block = table_template.replace('__ORG__', org)
    table_block = table_block.replace('__REPO_NAME__', repo_name)
    table_block = table_block.replace('__TABLE_NAME__', f'{repo_name}_commits')
    table_blocks.append(table_block)
  return table_blocks

dashboard_template = """
mod "GitHubContribs" {
  title = "GitHubContribs"
}
dashboard "GitHubContribs" {
  __TABLES__
}
"""

table_template = """
  table {
    title = "__ORG__/__REPO_NAME__ external commits"
      sql = <<EOQ
      select
        c.repository_full_name,
        c.author_login,
        to_char(c.author_date, 'YYYY-MM-DD') as author_date,
        c.html_url,
        c.message
      from
        __TABLE_NAME__ c
      left join
        __ORG___members m
      on 
        m.member_login = author_login
      where
        m.member_login is null
        and c.author_login !~* 'dependabot'
      order by
        author_date desc
    EOQ
    column "html_url" {
      wrap = "all"
    }
    column "message" {
      wrap = "all"
    }
  }
"""

drop_commit_tables_for_all_repos()

create_org_members_table('turbot')

create_commit_tables_for_all_repos()

all_table_blocks = '\n\n'.join(build_table_blocks_for_all_repos())

dashboard = dashboard_template.replace('__TABLES__', all_table_blocks)

with open('/home/jon/steampipe-samples/all/github-contribs/mod.sp', 'w') as f:
  f.write(dashboard)