codemonth.dk

One project every month - making stuff better ...

GITHUB_UTL Tutorial 10: Even more data from github in sqlplus!

More on selecting data directly from your github repository, this time. So one of the things I have done since my last post, was replacing and refactoring the code that fetches data from the github api. This change has made it even more easy to get information displayed directly in sqlplus. What this also means though, is that you have to re install the entire github schema. But I have created an easy single install script, so simply log on as sys and execute the install.sql from the latest release, and you should be up and running in no time. So this time I will show you some of the selects that you can run inside of oracle to query your github repository.


First we need to setup our session so let us call the github_oracle_session package to do so:

exec github_oracle_session.set_github('file:/home/oracle/wallet', 'WalletPasswd123', 'github-user', 'gitPass123', 'github_utl_test', 'github-user');


With our session setup I will simply show you some of the selects that can be done:

SQL> select branch_name, commit_sha from table(github_tables.repository_branches('github-user', 'oraclegit'));   

BRANCH_NAME COMMIT_SHA
-------------------- ---------------------------------------------
master ab51e2142c0c2f231e54b23b94a140b31945749e

SQL>

SQL> column title format a80
SQL> select issue_id, title from table(github_tables.repository_issues('github-user','oraclegit'));

ISSUE_ID TITLE
---------- --------------------------------------------------------------------------------
4 issues_labels.add_labels - request boy cannot be json_list
3 github_issues.create_issue and edit_issue - labels as a list
2 GitHub response parse fails if array of json objects

SQL>

SQL> select content_type, content_name, content_path from table(github_tables.repository_contents('github-user','oraclegit'));

CONTENT_TY CONTENT_NAME CONTENT_PATH
---------- ----------------------------------- -----------------------------------
file .gitignore .gitignore
file .qs_commands .qs_commands
file README.md README.md
file github.body.sql github.body.sql
file github.spec.sql github.spec.sql
file github_issues.body.sql github_issues.body.sql
file github_issues.spec.sql github_issues.spec.sql
file github_issues_assignees.body.sql github_issues_assignees.body.sql
file github_issues_assignees.spec.sql github_issues_assignees.spec.sql
file github_issues_comments.body.sql github_issues_comments.body.sql
file github_issues_comments.spec.sql github_issues_comments.spec.sql

CONTENT_TY CONTENT_NAME CONTENT_PATH
---------- ----------------------------------- -----------------------------------
file github_issues_events.body.sql github_issues_events.body.sql
file github_issues_events.spec.sql github_issues_events.spec.sql
file github_issues_labels.body.sql github_issues_labels.body.sql
file github_issues_labels.spec.sql github_issues_labels.spec.sql
file github_issues_milestones.body.sql github_issues_milestones.body.sql
file github_issues_milestones.spec.sql github_issues_milestones.spec.sql
file github_oracle_content.body.sql github_oracle_content.body.sql
file github_oracle_content.spec.sql github_oracle_content.spec.sql
file github_oracle_session.body.sql github_oracle_session.body.sql
file github_oracle_session.spec.sql github_oracle_session.spec.sql
file github_org.body.sql github_org.body.sql

CONTENT_TY CONTENT_NAME CONTENT_PATH
---------- ----------------------------------- -----------------------------------
file github_org.spec.sql github_org.spec.sql
file github_repos.body.sql github_repos.body.sql
file github_repos.spec.sql github_repos.spec.sql
file github_repos_collaborators.body.sql github_repos_collaborators.body.sql
file github_repos_collaborators.spec.sql github_repos_collaborators.spec.sql
file github_repos_commits.body.sql github_repos_commits.body.sql
file github_repos_commits.spec.sql github_repos_commits.spec.sql
file github_repos_content.body.sql github_repos_content.body.sql
file github_repos_content.spec.sql github_repos_content.spec.sql
file github_repos_forks.body.sql github_repos_forks.body.sql
file github_repos_forks.spec.sql github_repos_forks.spec.sql

CONTENT_TY CONTENT_NAME CONTENT_PATH
---------- ----------------------------------- -----------------------------------
file github_repos_merge.body.sql github_repos_merge.body.sql
file github_repos_merge.spec.sql github_repos_merge.spec.sql
file github_repos_releases.body.sql github_repos_releases.body.sql
file github_repos_releases.spec.sql github_repos_releases.spec.sql
file github_tables.body.sql github_tables.body.sql
file github_tables.spec.sql github_tables.spec.sql
file github_utl_load.sql github_utl_load.sql
file install.sql install.sql
file network_acl.sql network_acl.sql
file oraclegit.body.sql oraclegit.body.sql
file oraclegit.spec.sql oraclegit.spec.sql

CONTENT_TY CONTENT_NAME CONTENT_PATH
---------- ----------------------------------- -----------------------------------
file oraclegit_capture.sql oraclegit_capture.sql
file oraclegit_push_type.sql oraclegit_push_type.sql
dir pljson pljson
file tables.sql tables.sql
dir temp temp
file user.sql user.sql

50 rows selected.

SQL>

SQL> select content_type, content_name, content_path from table(github_tables.repository_contents('github-user','oraclegit', 'pljson/'));

CONTENT_TY CONTENT_NAME CONTENT_PATH
---------- ----------------------------------- -----------------------------------
dir addons pljson/addons
file grantsandsynonyms.sql pljson/grantsandsynonyms.sql
file install.sql pljson/install.sql
file json.typ pljson/json.typ
file json_ac.sql pljson/json_ac.sql
file json_body.typ pljson/json_body.typ
file json_ext.sql pljson/json_ext.sql
file json_list.typ pljson/json_list.typ
file json_list_body.typ pljson/json_list_body.typ
file json_parser.sql pljson/json_parser.sql
file json_printer.sql pljson/json_printer.sql

CONTENT_TY CONTENT_NAME CONTENT_PATH
---------- ----------------------------------- -----------------------------------
file json_value.typ pljson/json_value.typ
file json_value_body.typ pljson/json_value_body.typ
file readme.txt pljson/readme.txt
file uninstall.sql pljson/uninstall.sql

15 rows selected.

SQL>


So as you can see, there are a lot of possibilities to combine data from your github account with the data in your oracle database. Next month (tomorrow) I will start up a new project, but I will still write a couple of posts on this one, since there are a lot more that can be done with this package, and I want to show powerfull this package really is. Next project will be in the same category as this though, as I will create a package to integrate your oracle database with the Jira issue and bug tracking tool from Atlasssian.

Tagged in : Advanced Queuing, DBMS_NETWORK_ACL_ADMIN, DBMS_SCHEDULER, Database Triggers, UTL_ENCODE, UTL_HTTP, UTL_RAW, sys_context