Ever since my first KScope in 2012 I felt the need to grab the data in the list of KScope-sessions as published by ODTUG and do what I do best with data: Query it, in stead of scrolling through pages and pages of it.

If you feel that same need to easily find the KScope sessions that match your criteria by use of a SQL query, you landed on the right page.

Execute queries on the KScope site

Every year ODTUG makes a beautiful website with, among other things, a list of presentations and a summary of their contents.
And by the time KScope will start we usually get a new conference app.

But looking at such a webpage I can’t help but thinking: “The questions I have about this data would be answered so much easier if it was in a table in the database”.

So, In 2012 I build a simple package that reads the source of the presentations-page of the KScope website, extracts the relevant data en presents it as a pipelined table function.
In 2013 I rebuild it so it supported that years site, and the year after that again for KScope14.
In 2015 I changed the code to make it support every KScope of which the site is still live.

The code can be downloaded further below.

Disclaimer:
Remember,
I developed this because I wanted the data to be queryable, and I can imagine I’m not the only one. Plus, I simply had a lot of fun working on this.
I did not make this public to show off my brilliant code. It’s not. It’s quite simple, and because of it depending on the structure of the code of the webpage, rather unstable.
Changes to the page ODTUG might make, can render my code in it’s current state useless.
If that happens, I will try to repair it and publish a new version.
If you choose to use my code and one day you notice it stopped working, please let me know (I might not have noticed yet myself), so I can upload a new version that supports the new layout of the webpage.
I would never, ever build something like this for a production environment, nor do I want to encourage anyone to do so.

Examples:
Below examples are for data from KScope15
Supply the ‘number’ of the edition of KScope you’re interested in to the function en the sessions are returned.
For KScope15 that number obviously is 15.

kscope15_all_sessions

Now you can easily answer questions like:
– Which speakers can not seem to squeeze their presentation into a one hour session.

kscope15_multipart_sessions– Which speakers this year did not present at the previous couple of KScopes.

kscope15_new_presenters

Code

Zip file containing scripts with all create statements for the package, types and ACL needed to execute SQL queries on the kscope site.
Just unzip and run driving_script.sql 
The download also contains scripts for materialized views (one for each supported kscope).
The mviews are called MV_ERO_KSCOPE##_SESSIONS, where ## means the number of the kscope.
Execute procedure ero$kscope_sessions.mv_refresh without parameters to refresh all kscope mviews. Enter a parameter value (for example 15) to refresh only the mview for that specific kscope.
After a refresh you can query the data through the mview a lot faster than through the pipelined table function. Refresh again if (you suspect) the sessionlist has changed.

KScope Sessions Pipelined Table Function
VersionDateChanges
2017-03May 21, 2017New version. Changes:

  1. The KScope site now also displays the name of the room in which a session is done, so the table function now also returns this data.
  2. ODTUG has started redirecting the url’s of old KScope sites to the KScope17 site. Currently www.kscope15.com and www.kscope16.com will just get you to www.kscope17.com.
    This means that the sessions of old KScopes would not be accessable by the code anymore.
    So, as of now, for all KScopes except the one we’re heading towards, a version on the WayBackMachine is used.
    For every old KScope the code now looks at the first ‘capture’ on the WayBackMachine just after the end of that KScope.
  3. Because the old KScope sites no longer need to be accessed, they are no longer in the ACL.
    Only the current KScope site and the WayBackMachine site are now in the ACL.
  4. Since archived versions are now used for old KScopes, KScope12 is reintroduced in the function, after being removed some time ago due to the fact that its site no longer exists.
2017-02March 08, 2017New version, small change: html code for bullets is ow replaced by an asterisk (*).
The only things changed are the package body and (the version number in)  the specification.
2017-01December 28, 2016New version, supports KScope17 sessions
Site does not yet show date/time data,so obviously that is not returned by the code yet.
When the date/time data is published the code will, if needed, be changed to support it.
Plus: Added functionality to handle html ascii codes and some tags in session title and description.
2016-02June 05, 2016New version. An ’empty session’ appeared in the list of sessions on the site, which caused all starttime, endtime and session-id data to disappear from all sessions.
The package is altered, so now it handles this situation correctly.
2016-01February 01, 2016New version, supports KScope16 sessions
SIte does not yet show date/time data,so obviously that is not returned by the code yet.
KScope site now shows date/time data, code did not need to be adjusted so, still version 2016-1
2015.03May 21, 2015– New code to support new sourcecode of KScope15 webpage.
– Introduced materialized views + mv_refresh procedure. Fetch data once, query often.
2015.02April 23, 2015No code changes. But the $ sign is removed from the scriptnames (not the objectnames!) because some experienced problems with this on unix/linux.
2015.01January 25, 2015As Kim pointed out: the previous zip file was incomplete 🙁


Static table

I can imagine you would rather have a static table, or that you don’t have access to a schema with the privileges needed to create the acl.
That’s why I did a create table as select for you and supply it’s result here as both an Oracle table and an Excel file:
Obviously they are static. So come back for new versions if the sessionlist changes, or …. download the pipelined table function 🙂

These two downloads contain the KScope17 data that was valid on June 17, 2017

Note, as of may 21, 2017:

  • Both now contain a new column “Room”. This holds the name of the room in which the session is done.
  • In the Excel the “Start Time” and “End Time” columns have changed. Now they only hold the time component. The date of the session is moved to a new column “Day”. This makes searching for sessions on a given day easier.
Excel fileOracle table + insert statements
Excel
Table ERO_KSCOPE_SESSIONS

Comments

Query KScope Sessions — 6 Comments

    Leave a Reply

    Your email address will not be published. Required fields are marked *