Saturday, December 9, 2017

Ordering Database Tables by Foreign Key Dependencies

Some database operations that affect multiple tables must touch the affected tables in an order corresponding to their dependencies. For example, data must be loaded into parent tables before data can be loaded into child (dependent) tables. Some deletion and update operations may also need to be carried out in dependency order. A list of all tables in dependency order can be useful to automate such operations. Several methods are shown here for generating a list of tables in dependency order; these methods are:
  • Python code
  • A CTE
  • An execsql script.

All of these methods take, as input, a table of dependencies in which each row contains a pair of table names corresponding to a parent:child relationship. The Python code produces a Python list containing tables listed in dependency order, and the other two methods produce a table consisting of one column containing table names and another column containing integers that specify the dependency order. The first items in these lists are the tables that have no parents, and the last items in these lists are the tables with the longest chain of dependencies.

Getting the Dependencies


When using a DBMS that supports INFORMATION_SCHEMA tables, the following SQL can be used to obtain a table containing all direct parent:child pairs

create table dependencies as
select 
        tc.table_name as child,
        tu.table_name as parent
from 
        information_schema.table_constraints as tc
        inner join information_schema.constraint_table_usage as tu
             on tu.constraint_name = tc.constraint_name
where 
        tc.constraint_type = 'FOREIGN KEY'
        and tc.table_name <> tu.table_name;

Additional constraints may be used, or needed, in the WHERE clause to limit the set of tables returned--for example, to eliminate system tables, or to select only the tables in a particular schema. If there are cyclic dependency relationships among tables, this SQL will not complete, and so in those cases at least one of the tables in the cycle of mutual dependencies should be omitted using an appropriate specification in the WHERE clause.

The table produced by this SQL will include only those tables that are part of some foreign key relationship. Standalone tables that are neither a parent nor a child will not be included. Standalone tables and tables that are omitted because of cyclic dependency relationships can be added to the output of the dependency-ordering step.

Ordering Tables by Dependency


Converting the set of parent:child dependencies into a list of tables in dependency order requires an iterative or recursive traversal of the tree of relationships that is rooted at the tables that have no parents. The table-ordering routines that follow use different approaches:
  • A loop over a list of unprocessed dependencies in Python, where that list is modified within the loop.
  • A recursive traversal of the tree using a CTE, terminating when the farthest leaves of the tree have been reached.
  • Looping using end recursion in the execsql script to traverse the tree in a manner similar to the CTE.

The algorithm used in the Python code is distinct, whereas the algorithms used with the recursive CTE and the execsql script are very similar. (The algorithm used by the Python code can also be implemented using an execsql script, but the code is considerably longer than the implementation shown below.)

Python


The input for the Python code to generate a dependency-ordered list of tables is a table of dependencies consisting of a list of two-element lists or tuples, each containing a child table name and a parent table name. This list might be generated by querying the database (e.g., using the SQL above) or from static configuration data. Given this table of dependencies, a Python list of all of the tables in dependency order can be generated with the following function

def dependency_order(dep_list):
    rem_tables = list(set([t[0] for t in dep_list] + [t[1] for t in dep_list]))
    rem_dep = copy.copy(dep_list)
    sortkey = 1
    ret_list = []
    while len(rem_dep) > 0:
        tbls = [tbl for tbl in rem_tables if tbl not in [dep[0] for dep in rem_dep] ]
        ret_list.extend([ (tb, sortkey) for tb in tbls ])
        rem_tables = [ tbl for tbl in rem_tables if tbl not in tbls ]
        rem_dep = [ dep for dep in rem_dep if dep[1] not in tbls ]
        sortkey += 1
    if len(rem_tables) > 0:
        ret_list.extend([(tb, sortkey) for tb in rem_tables])
    ret_list.sort(cmp=lambda x,y: cmp(x[1], y[1]))
    return [ item[0] for item in ret_list ]

SQL CTE


For DBMSs that support them, a recursive CTE can be used to convert a table of parent:child dependencies into a list of tables in dependency order. The input for the following code should be a table of such dependencies; that table should be named "dependencies". The output of the recursive CTE contains all parent tables, and the remaining tables (that are not parents to any other table) are added in the SELECT statement that uses the CTE.

with recursive dep_depth as (
 select
  dep.child,
  dep.parent,
  1 as lvl
 from
  dependencies as dep
 union all
 select
  dep.child,
  dep.parent,
  dd.lvl + 1 as lvl
 from
  dep_depth as dd
  inner join dependencies as dep on dep.parent = dd.child
 )
select
 table_name,
 table_order
from (
 select
  dd.parent as table_name,
  max(lvl) as table_order
 from
  dep_depth as dd
 group by
  table_name
 union
 select
  dd.child as table_name,
  max(lvl) + 1 as level
 from
  dep_depth as dd
  left join dependencies as dp on dp.parent = dd.child
 where
  dp.parent is null
 group by
  dd.child
 ) as all_levels;

Execsql Script


For DBMSs that don't support recursive CTEs, and when use of a client language like Python is not desired, the metacommands provided by execsql allow recursive traversal of the tree of dependencies, as shown in the following code. Explanations of the metacommands used in this code can be found in the on-line documentation.

As with the CTE implementation, the input for the following code should be a table of parent:child dependencies that is named "dependencies".

The following code increments a counter to track and assign the successive levels of recursion during traversal from the root to the leaves of the dependency tree. Because automatically-generated sequences and variables are DBMS-specific extensions to SQL, for the sake of generality, this implementation uses execsql counter variables and substitution variables. Thus, but for minor differences in SQL syntax, the following code should run in any DBMS.

-- ====================================================================
--  Initialize the tables used to summarize dependency order.
--  Table created:
--    dep_level: A copy of "dependencies" with an additional column
--               to store the level in the hierarchy.  The dependency
--               level is set by an execsql counter variable for
--               generality.
-- ====================================================================
-- !x! sub current_level !!$counter_530!!
select
    child,
    parent,
    !!current_level!! as lvl
into
    temporary table dep_level
from
    dependencies;


-- ====================================================================
--  Create a view to evaluate whether there are any remaining
--  dependencies to evaluate.
--  View created:
--    unprocessed: The number of parent tables whose children are
--                 not already listed as parents in the 'dep_level' table.
--  Tables used:
--    dep_level
--    dependencies
-- ====================================================================
create temporary view unprocessed as
select count(distinct dep.child) as unproc
from
    dep_level as dl
    inner join dependencies as dep on dl.child = dep.parent
where
    dl.lvl = (select max(lvl) from dep_level);


-- ====================================================================
--  Define an execsql sub-script to increment the dependency level.
-- ====================================================================
-- !x! begin script add_new_level
-- !x! sub last_level !!current_level!!
-- !x! sub current_level !!$counter_530!!
insert into dep_level
    (child, parent, lvl)
select distinct
    dep.child,
    dl.child as parent,
    !!current_level!!
from
    dep_level as dl
    inner join dependencies as dep on dl.child = dep.parent
where
    dl.lvl = !!last_level!!;
-- !x! end script


-- ====================================================================
--  Define and execute an execsql sub-script to increment the dependency
--  level as many times as necessary.
-- ====================================================================
-- !x! begin script add_levels
-- !x! subdata remaining unprocessed
-- !x! execute script add_new_level
-- !x! subdata remaining unprocessed
-- !x! if(is_gt(!!remaining!!, 0)) { execute script add_levels }
-- !x! end script

-- !x! execute script add_levels


-- ====================================================================
--  Convert the dependency levels into a table order.
-- ====================================================================
create temporary table dependency_order as
select
 table_name,
 table_order
from (
 select
  dd.parent as table_name,
  max(lvl) as table_order
 from
  dep_level as dd
 group by
  table_name
 union
 select
  dd.child as table_name,
  max(lvl) + 1 as level
 from
  dep_level as dd
  left join dependencies as dp on dp.parent = dd.child
 where
  dp.parent is null
 group by
  dd.child
 ) as all_levels;

Saturday, November 19, 2016

A Year and Quarter Data Type for PostgreSQL

For some applications, time periods are needed that represent quarters of the year.  Although quarters could be represented by using an exact date data type and constraining the month and day values (i.e., to the first day of each quarter) or by using date ranges in PostgreSQL, these representations do not allow easy computation of the difference between two dates (in quarters) or the addition of specific number of quarters to a date to produce a new date.

The code below shows the implementation of custom composite data type in Postgres that represents quarters of a year. This data type is named "quarter". The implementation supports addition of a number of quarters to a "quarter" date to produce a new "quarter" date, and supports calculation of the difference between two "quarter" dates. Comparison operators are also defined for use in expressions using "quarter" dates and to allow indexing of data tables by "quarter" values.

The Postgres code to define this data type, and arithmetic and comparison operators, is shown below. Details on the creation of a custom data type and operators can be found in the Postgres documentation.

The definition of the "quarter" data type is simply:

create type quarter as (
 year integer,
 quarter integer
 );

To support addition and subtraction of an integer number of quarters to (or from) a "quarter" data type, a function must be defined to carry out this calculation. The arguments to the function are a "quarter" data type and the number of quarters; the second argument may be either positive or negative.

create or replace function qtr_add(
 qtr quarter,
 addqtr integer)
 returns quarter as
$BODY$
DECLARE
 qtr2 quarter;
BEGIN
 -- Adjust the year
 if addqtr >= 0 then
  qtr2.year := qtr.year + div((qtr.quarter + addqtr-1), 4);
 else
  qtr2.year := qtr.year - div(((4 - qtr.quarter) - addqtr), 4);
 end if;
 -- Adjust the quarter
 qtr2.quarter := mod(qtr.quarter + addqtr - 1, 4) +1;
 if qtr2.quarter < 1 then 
  qtr2.quarter := qtr2.quarter + 4;
 end if;
 --
 return qtr2;
END;
$BODY$
 language plpgsql
 immutable leakproof strict;


Subtracting two "quarter" dates can be carried out in a simple expression, but a function is defined to carry out this calculation because it is used for both the mathematical and comparison operators.

create or replace function qtr_diff(
 qtr1 quarter,
 qtr2 quarter)
 returns integer as
$BODY$
BEGIN
 return 4 * (qtr1.year - qtr2.year) + (qtr1.quarter - qtr2.quarter);
END;
$BODY$
 language plpgsql
 immutable leakproof strict;

To support comparison operators (<, <=, =, <>, >=, and >), a function is defined to compare two "quarter" dates. This function returns -1, 0, or 1 depending on whether its first argument is less than, equal to, or greater than its second argument.

create or replace function quarter_comp(
 qtr1 quarter,
 qtr2 quarter)
 returns integer as
$BODY$
DECLARE
 diff integer;
BEGIN
 diff := qtr_diff(qtr1, qtr2);
 if diff = 0 then
  return 0;
 elsif diff < 1 then
  return -1;
 end if;
 return 1;
END;
$BODY$
 language plpgsql
 immutable leakproof strict;


Each comparison operation is carried out by an individual function that uses the 'quarter_comp()' function.

-- --------------------------------------------------------------------------
-- qtr_lt()
-- --------------------------------------------------------------------------
create or replace function qtr_lt(
 qtr1 quarter,
 qtr2 quarter)
 returns boolean as
$BODY$
BEGIN
 return quarter_comp(qtr1, qtr2) < 0;
END;
$BODY$
 language plpgsql
 immutable leakproof strict;


-- --------------------------------------------------------------------------
-- qtr_lte()
-- --------------------------------------------------------------------------
create or replace function qtr_lte(
 qtr1 quarter,
 qtr2 quarter)
 returns boolean as
$BODY$
BEGIN
 return quarter_comp(qtr1, qtr2) <= 0;
END;
$BODY$
 language plpgsql
 immutable leakproof strict;


-- --------------------------------------------------------------------------
-- qtr_eq()
-- --------------------------------------------------------------------------
create or replace function qtr_eq(
 qtr1 quarter,
 qtr2 quarter)
 returns boolean as
$BODY$
BEGIN
 return quarter_comp(qtr1, qtr2) = 0;
END;
$BODY$
 language plpgsql
 immutable leakproof strict;


-- --------------------------------------------------------------------------
-- qtr_ne()
-- --------------------------------------------------------------------------
create or replace function qtr_ne(
 qtr1 quarter,
 qtr2 quarter)
 returns boolean as
$BODY$
BEGIN
 return quarter_comp(qtr1, qtr2) <> 0;
END;
$BODY$
 language plpgsql
 immutable leakproof strict;


-- --------------------------------------------------------------------------
-- qtr_gte()
-- --------------------------------------------------------------------------
create or replace function qtr_gte(
 qtr1 quarter,
 qtr2 quarter)
 returns boolean as
$BODY$
BEGIN
 return quarter_comp(qtr1, qtr2) >= 0;
END;
$BODY$
 language plpgsql
 immutable leakproof strict;



-- --------------------------------------------------------------------------
-- qtr_gt()
-- --------------------------------------------------------------------------
create or replace function qtr_gt(
 qtr1 quarter,
 qtr2 quarter)
 returns boolean as
$BODY$
BEGIN
 return quarter_comp(qtr1, qtr2) > 0;
END;
$BODY$
 language plpgsql
 immutable leakproof strict;


After the functions to carry out the arithmetic and comparison operations have been defined, the operators themselves can be defined.

-- --------------------------------------------------------------------------
-- +
-- --------------------------------------------------------------------------
create operator +(
 procedure = qtr_add,
 leftarg = quarter,
 rightarg = integer,
 commutator = +);

-- --------------------------------------------------------------------------
-- -
-- --------------------------------------------------------------------------
create operator -(
 procedure = qtr_diff,
 leftarg = quarter,
 rightarg = quarter);


-- --------------------------------------------------------------------------
-- <
-- --------------------------------------------------------------------------
create operator <(
 procedure = qtr_lt,
 leftarg = quarter,
 rightarg = quarter,
 commutator = >,
 negator = >=);

-- --------------------------------------------------------------------------
-- <=
-- --------------------------------------------------------------------------
create operator <=(
 procedure = qtr_lte,
 leftarg = quarter,
 rightarg = quarter,
 commutator = >=,
 negator = >);

-- --------------------------------------------------------------------------
-- =
-- --------------------------------------------------------------------------
create operator =(
 procedure = qtr_eq,
 leftarg = quarter,
 rightarg = quarter,
 commutator = =,
 negator = <>);

-- --------------------------------------------------------------------------
-- <>
-- --------------------------------------------------------------------------
create operator <>(
 procedure = qtr_ne,
 leftarg = quarter,
 rightarg = quarter,
 commutator = <>,
 negator = =);

-- --------------------------------------------------------------------------
-- >
-- --------------------------------------------------------------------------
create operator >(
 procedure = qtr_gt,
 leftarg = quarter,
 rightarg = quarter,
 commutator = <,
 negator = <=);

-- --------------------------------------------------------------------------
-- >=
-- --------------------------------------------------------------------------
create operator >=(
 procedure = qtr_gte,
 leftarg = quarter,
 rightarg = quarter,
 commutator = >=,
 negator = <);


To allow "quarter" dates to be used in indexes, an operator class must be defined.

create operator class quarter_ops default
for type quarter using btree as
   operator 1  <,
   operator 2  <=,
   operator 3  =,
   operator 4  >=,
   operator 5  >,
   function 1  quarter_comp(quarter, quarter);

The same approach could be used to define a custom "month" data type.  The code for a "month" data type would be almost identical, with changes needed only to the data type name used and to the functions 'qtr_add()' and 'qtr_diff()'.

Sunday, May 22, 2016

Using a Makefile with a Database

A makefile is a convenient and efficient way to run a multi-step process that creates or modifies files.  However, when one step of that process is to extract data from a server-based database, make has no inherent ability to determine when the data in the database has last been changed, and therefore, whether the data extraction step needs to be re-run.

The following Python program (db_updated.py) addresses this problem. It updates the modification date of a specific flag file (named db_updated.flag by default), creating the file if it does not already exist. This code is written to be used with a PostgreSQL database, but can be easily modified for other databases.

The program requires that the database contain at least one table with a column that contains a revision date (and time), either for that table or for the database as a whole. Command-line options can be used to specify the table(s) to be checked, and the name of the column to be checked. If neither is specified, a default table name of "auditlog" is used, and a default column name of "rev_time" is used.

Command-line options and arguments are:
db_updated.py [-t table] [-c column] [-u user] server database

Multiple -t options may be specified; the latest revision date of any of the tables will be used to set the modification date of the db_updated.flag file. If a user name is specified, the program will prompt for a password.


db_updated.py:
import argparse
import psycopg2
import getpass
import os
import os.path
import time

FLAG_FILE = 'db_updated.flag'

def get_user(server_name, database_name):
 user_name = raw_input("User name for %s database %s: " % (server_name, database_name))
 return user_name

def get_password(server_name, database_name, user_name):
 passwd = getpass.getpass("Password for user %s on %s database %s: " % (user_name, server_name, database_name))
 return passwd

def clparser():
 desc_msg = "Create or update the  database flag file if the given database has been recently modified."
 parser = argparse.ArgumentParser(description=desc_msg)
 parser.add_argument('-u', '--db_user', dest='db_user', action='store')
 parser.add_argument('-t', '--table', dest='table', action='append')
 parser.add_argument('-c', '--column', dest='column', action='store')
 parser.add_argument('server', action='store')
 parser.add_argument('database', action='store')
 return parser

def main():
 parser = clparser()
 arg = parser.parse_args()
 if not os.path.exists(FLAG_FILE):
  open(FLAG_FILE, 'a').close()
 if arg.db_user:
  db_user = arg.db_user
  pw = get_password(arg.server, arg.database, db_user)
  conn = psycopg2.connect(host=arg.server, database=arg.database, port=5432, user=unicode(db_user), password=unicode(pw))
 else:
  conn = psycopg2.connect(host=arg.server, database=arg.database, port=5432)
 curs = conn.cursor()
 column = arg.column or "rev_time"
 if arg.table:
  tables = 0
  sql = ''
  for t in arg.table:
   if tables > 0:
    sql = sql + " union "
   tables += 1
   sql = sql + "select max(%s) as latest from %s" % (column, t)
  if tables > 1:
   sql = "select max(latest) from (%s) as all_t" % sql
  sql = sql + ";"
 else:
  sql = "select max(%s) from auditlog;" % column
 curs.execute(sql)
 revtime = time.mktime(curs.fetchone()[0].timetuple())
 conn.close()
 os.utime(FLAG_FILE, (revtime, revtime))

main()

This can be used in a makefile like the example below. This example extracts data from a database and then runs an R script to further summarize the data.  The input and output file names for the R script are shown on the command line here, though encoding them in the script itself may be more convenient in many cases than parsing the command line in R.

.PHONY: all

all: db_updated.flag data_summary.csv
 db_updated.py -u user_name server_name db_name

data_summary.csv: data.csv summary_stats.R
 Rscript --vanilla summary_stats.R data.csv data_summary.csv

data.csv: db_updated.flag get_data.sql
 execsql.py -tp -u user_name get_data.sql server_name db_name

db_updated.flag:
 db_updated.py -u user_name server_name db_name

In this example, neither a table name nor a column name are included as arguments to db_updated.py. These may be needed in some circumstances, or the program modified so that the defaults are appropriate for the circumstances in which it is used.

Saturday, April 23, 2016

A Threaded Tkinter Toplevel Console Window

The code below implements a GUI window for output display, such as might be used as a console to display status messages or other information from a running program. It is implemented using a threaded Tkinter Toplevel widget, and is designed to be used in a non-GUI command-line program. The display that it produces looks like this:


The Python code for this GUI console is:
class ConsoleUIError(Exception):
 def __init__(self, msg):
  self.value = msg
 def __repr__(self):
  return ("ConsoleUIError(%r)" % self.value)

class ConsoleUI(object):
 class TkUI(object):
  def __init__(self, kill_event, stop_update_event, msg_queue, status_queue, title=None):
   self.kill_event = kill_event
   self.stop_update_event = stop_update_event
   self.msg_queue = msg_queue
   self.status_queue = status_queue
   import Tkinter as tk1
   import ttk as ttk1
   self.win = tk1.Toplevel()
   self.status_msg = tk1.StringVar()
   self.status_msg.set('')
   self.win.title(title if title else "execsql console")
   console_frame = ttk1.Frame(master=self.win, padding="2 2 2 2")
   console_frame.grid(column=0, row=0, sticky=tk1.NSEW)
   self.textarea = tk1.Text(console_frame, width=100, height=25, wrap='none')
   # Status bar
   statusframe = ttk1.Frame(master=self.win)
   statusbar = ttk1.Label(statusframe, text='', textvariable=self.status_msg, 
    relief=tk1.RIDGE, anchor=tk1.W)
   statusbar.pack(side=tk1.BOTTOM, fill=tk1.X)
   statusframe.grid(column=0, row=1, sticky=tk1.EW)
   # Scrollbars
   vscroll = tk1.Scrollbar(console_frame, orient="vertical", command=self.textarea.yview)
   hscroll = tk1.Scrollbar(console_frame, orient="horizontal", command=self.textarea.xview)
   self.textarea.configure(yscrollcommand=vscroll.set)
   self.textarea.configure(xscrollcommand=hscroll.set)
   self.textarea.grid(column=0, row=0, sticky=tk1.NSEW)
   vscroll.grid(column=1, row=0, sticky=tk1.NS)
   hscroll.grid(column=0, row=2, sticky=tk1.EW)
   # Allow resizing
   self.win.columnconfigure(0, weight=1)
   self.win.rowconfigure(0, weight=1)
   console_frame.columnconfigure(0, weight=1)
   console_frame.rowconfigure(0, weight=1)
   # Kill on window close
   self.win.protocol("WM_DELETE_WINDOW", self.kill)
   # Display and center the window
   self.win.update_idletasks()
   m = re.match("(\d+)x(\d+)([-+]\d+)([-+]\d+)", self.win.geometry())
   wwd = int(m.group(1))
   wht = int(m.group(2))
   swd = self.win.winfo_screenwidth()
   sht = self.win.winfo_screenheight()
   xpos = (swd/2) - (wwd/2)
   ypos = (sht/2) - (wht/2)
   self.win.geometry("%dx%d+%d+%d" % (wwd, wht, xpos, ypos))
   self.win.grab_set()
   self.win._root().withdraw()
   self.update_id = self.win.after(200, self.update)
   self.win.wait_window(self.win)
  def kill(self):
   if self.update_id:
    self.win.after_cancel(self.update_id)
    self.update_id = None
   self.win.destroy()
   self.win.update_idletasks()
  def update(self):
   self.update_id = None
   while not self.msg_queue.empty():
    msg = self.msg_queue.get(False)
    self.textarea.insert('end', msg)
    self.textarea.see('end')
    self.msg_queue.task_done()
   while not self.status_queue.empty():
    msg = self.status_queue.get(False)
    self.status_msg.set(msg)
   if self.kill_event.is_set():
    self.kill()
   else:
    if not self.stop_update_event.is_set():
     self.update_id = self.win.after(200, self.update)
 def __init__(self, title=None):
  self.title = title
  self.msg_queue = Queue.Queue()
  self.status_queue = Queue.Queue()
  self.kill_event = threading.Event()
  self.stop_update_event = threading.Event()
  self.consolethread = None
  self.update_id = None
  # Start the local event loop in a thread.
  def openconsole():
   self.active = True
   self.ui = self.TkUI(self.kill_event, self.stop_update_event, self.msg_queue, 
    self.status_queue, self.title)
   # Deallocate the Tk object here to avoid the "main thread is not in main loop" error.
   self.ui = None
  self.consolethread = threading.Thread(target=openconsole)
  self.consolethread.start()
 def write(self, msg):
  self.active = self.consolethread and self.consolethread.is_alive()
  if not self.active:
   raise ConsoleUIError(msg)
  self.msg_queue.put(msg)
 def write_status(self, msg):
  self.active = self.consolethread and self.consolethread.is_alive()
  if not self.active:
   raise ConsoleUIError(msg)
  self.status_queue.put(msg)
 def deactivate(self):
  self.kill_event.set()
  if self.consolethread and self.consolethread.is_alive():
   self.consolethread.join()
  self.active = False
 def wait_for_user_quit(self):
  self.stop_update_event.set()
  if self.consolethread and self.consolethread.is_alive():
   self.consolethread.join()
  self.active = False


Because it runs in its own thread, this console can be used to display information produced by the main program or even several other separate processes.

The 'write()' method of the ConsoleUI object will write the given text at the end of the console display.  A status message can also be written, separately from the stream of text that is written in the main part of the window.  The console window can be closed directly from the program with the 'deactivate()' method, or the program can pause until the user closes the window by using the 'wait_for_user_quit()' method.

Other Tkinter widgets should not be activated in other threads while this GUI console window is open.

Tuesday, April 12, 2016

Calculating the Median in SQL Using Window Functions

Numerous approaches to calculating the median in SQL have been presented in books and online. Here's another one that is short and simple, and makes use of window functions. This example is specific to PostgreSQL, but could be adapted to any other DBMS that supports window functions. To illustrate this method, I'll use a set of test data in a table named median_test:

create table median_test (id text, value double precision);

insert into median_test 
    (id, value)
select
    id,
    value
from
    (select 
        generate_series(1,100) as num, 
        chr(cast(random()*10 as integer)+65) as id, 
        random() as value
    ) as dd;

To support the median calculation, two columns are added using window functions, one of which is the row number of the ordered values (but doubled), and the other of which is the total number of rows. The window functions allow these to be calculated for each data frame, which is determined here by values of the id column. In this example the additional columns are added in a temporary view, but this could instead be a subquery, a common table expression, or even an actual table.

create or replace temporary view d as
select
    id,
    value,
    row_number() over(partition by id order by value) * 2 as rownum2,
    count(*) over (partition by id) as rows
from 
    median_test;

The median calculation is then carried out by averaging either the two central values when there is an even number of values, or the one central value when there is an odd number of values.

select 
    id,
    avg(value) as median
from
    d
where 
    rownum2 in (rows, rows+1, rows+2)
group by
    id;

When there is an odd number of rows, the single median value will have a value of rownum2 equal to rows+1. When there is an even number of rows, the two central rows will have values of rownum2 equal to either rows or rows+2.

Friday, December 4, 2015

Monday, November 23, 2015

execsql

execsql is a Python application that reads a text file of SQL commands and executes them against a database. The supported databases are:
  • PostgreSQL
  • MS-Access
  • MS-SQL Server
  • SQLite
  • MySQL or MariaDB
  • Firebird
  • ODBC DSN connections.
In addition to executing SQL statements, execsql also implements a number of metacommands that allow:
  • Import of data from text files and OpenDocument spreadsheets
  • Export of data to delimited text, HTML, JSON, LaTeX tables, and OpenDocument spreadsheets
  • Copying of data between databases, even of different DBMS types
  • Conditional execution of SQL code and metacommands based on data values or user input.

execsql allows variables to be defined and used to dynamically modify SQL code and metacommands. An INCLUDE metacommand can be used to modularize SQL scripts and facilitate code re-use. Variables can be used to parameterize included scripts. Conditional inclusion of scripts can be used to implement loops. Automatically incrementing counter variables can be used to control loops or generate unique values to be used in input or output.

execsql is fundamentally a command-line application that can be used to automate database operations, either by itself or as part of a toolchain that may include other steps to either pre-process data before loading, or post-process or analyze data that are extracted from a database. However, execsql also has the ability to present data to the user in dialog boxes, request several types of input from the user either on the terminal or in GUI dialogs, and display messages to the user either on the terminal or in a GUI console. These features allow flexible interactive applications to be created.

A guiding principle in the development of execsql is documentation of all data operations. The use of SQL scripts, rather than ad-hoc interactive operations in a GUI, is key to meeting that goal. In addition, execsql logs all usage information, including databases used, script files executed (including script file version information), variable assignments, user input, and errors. Chain-of-custody procedures are used in some disciplines to ensure traceability from collection through (typically) laboratory analysis. However, no formal chain of custody procedures ordinarily are applied to data during or after entry into a database. SQL script files and execsql log files can be used to produce a complete record of data operations so that traceability of data can be extended into the database environment.

execsql and its documentation are available from the Python Package Index (PyPI). Note that the search functionality in PyPI and in pip search is broken, and will return links to obsolete versions of execsql that are no longer available. The direct link will take you to the latest available version.

Sunday, May 31, 2015

Calendar Figure for Zim Journal Page

The daily journal page in Zim is a useful place to record one's meeting schedule and task list during the morning planning session, and update these, plus other activities, during the day.  I use a custom template for the journal page that has first-level headings of "Calendar", "Tasks", and "Activities".  At work, where Outlook is used for calendaring, the "Calendar" section can be initialized by preparing to e-mail a copy of the daily calendar in Outlook, and then copying and pasting the table of schedule data from the e-mail to Zim.  However, a graphic representation of the daily calendar is more useful than the textual representation obtained in this way.

The following R script can be used to embed a graphic representation of a daily calendar into a Zim page.  The R script plugin must be enabled to use this script.

mtg.names <- c('Dummy meeting xyz', 'Dummy meeting abc', 'Dummy meeting pqr')
start.times <- c(8, 10, 13)
end.times <- c(8.5, 11, 14.5)
# HEIGHT = 100
# WIDTH = 640
mtg.len <- end.times - start.times
fill.times <- 17 - end.times
times <- matrix(c(rev(start.times), rev(mtg.len), rev(fill.times)), byrow=TRUE, ncol=length(start.times))
par(oma=c(0,0,0,0), mar=c(2,12,0,0))
barplot(height=times, space=0, horiz=TRUE, names.arg=rev(mtg.names), las=1, col=c("white", "skyblue", "white"), 
        xlim=c(8,18), xpd=FALSE, xaxp=c(8,17,9), xaxs="i", yaxs="i"
        )
abline(v=9:16)
abline(v=seq(8.5,16.5,1), lty=2, col="gray25")

The first four lines of this script, specifying the meeting names, start and end times, and the figure height should be edited as necessary.  If more space for meeting names is needed, the left margin specification of "12" on line 9 should be increased as needed.  Depending on your monitor resolution and how much screen space you devote to Zim, you may also want to adjust the "WIDTH" specification on line 5; the value is in pixels.

This script produces a figure like this:



Saturday, May 23, 2015

Changing Bullets to Checklist Items in Zim

If a checklist template is stored in Zim as a bulleted list, when the template is copied to a new page to create an actual checklist, the bulleted items need to be converted to checklist items.  The custom tool script below will automatically perform this conversion for all bullets on the page.

#!/usr/bin/python
# zimbulletchecklist.py
#
# PURPOSE
# Convert leading bullets to checklist items in a Zim page. 
#
# NOTES
# 1. The name of the temporary Zim page file must be passed
#  as the (only) command-line argument.
# 2. All bullets (asterisks) with only leading whitespace
#  and followed by at least one whitespace character
#  will be converted to checklists.
#
# AUTHOR
# Dreas Nielsen (RDN)
#
# COPYRIGHT AND LICENSE
# Copyright (c) 2015, Dreas Nielsen
# License: GPL3
#
# HISTORY
#  Date   Remarks
# ---------- -----------------------------------------------------
# 2015-05-23 Created.  RDN.
# ==================================================================

_vdate = "2015-05-23"
_version = "1.0"

import sys
import os
import fileinput
import re

if len(sys.argv) != 2:
 sys.stderr.write("You must provide the temporary Zim page file name as a command-line argument.")
 sys.exit(1)
zimpage = sys.argv[1]
if not os.path.exists(zimpage):
 sys.stderr.write("The file %s does not exist." % zimpage)
 sys.exit(1)

bullet_rx = re.compile(r'^(\s*)\*\s(.*)$')

for line in fileinput.input(inplace=1):
 m = bullet_rx.match(line) 
 if m:
  sys.stdout.write(m.group(1)+'[ ] '+m.group(2)+'\n')
 else:
  sys.stdout.write(line)

Friday, May 15, 2015

A Markdown Table Generator for Zim

Zim's interface for table creation requires tables to be built up row by row.  If you know how many rows you need in a table, Zim's 'Custom Tools' feature can be used to simplify the process of creating a table.  Following is a Python script that can be used to create an empty Markdown table, with the desired number of rows and columns, at the bottom of a Zim wiki page.  A custom tool should be created in Zim that calls this script with the "%f" parameter as the sole command-line argument.  After this script is run you will see the table in the page as Markdown text; type Ctrl-R to reload the page, and Zim will format it like a table created with Zim's own table-creation tool.

#!/usr/bin/python
# zimtable.py
#
# PURPOSE
# Add an empty Markdown-formatted table to the end of a Zim
# page.
#
# NOTES
# 1. The name of the temporary Zim page file must be passed
#  as the (only) command-line argument.
# 2. This program will prompt for the number of rows and columns,
#  and the column width in tabs, and insert a pipe table
#  with those dimensions, plus one row for headers.
# 3. The separator line between headers and table cells
#  assumes 6 characters per tab.
#
# AUTHOR
# Dreas Nielsen (RDN)
#
# COPYRIGHT and LICENSE
# Copyright (c) 2015, Dreas Nielsen
# License: GPL3
#
# HISTORY
#  Date   Remarks
# ---------- -----------------------------------------------------
# 2015-05-13 Created without GUI to prompt for table size. RDN.
# 2015-05-15 Added GUI.  RDN.
# =====================================================================

_vdate = "2013-05-15"
_version = "1.0"

import sys
import os
import Tkinter as tk
import ttk


if len(sys.argv) != 2:
 sys.stderr.write("You must provide the temporary Zim page file name as a command-line argument.")
 sys.exit(1)
zimpage = sys.argv[1]
if not os.path.exists(zimpage):
 sys.stderr.write("The file %s does not exist." % zimpage)
 sys.exit(1)

def add_pipe_table(fn, rows, cols, colwidth=3):
 f = open(fn, "a")
 tabs = '\t' * colwidth
 sep = '-' * (6 * colwidth - 1)
 datarow = "|" + (cols) * ("%s%s" % (tabs, "|")) + '\n'
 seprow = "|" + (cols) * ("%s%s" % (sep, "|")) + '\n'
 f.write('\n')
 f.write(datarow)
 f.write(seprow)
 for r in range(rows):
  f.write(datarow)
 f.close()

def cancel_table(*args):
 ui.destroy()

def make_table(*args):
 add_pipe_table(zimpage, int(row_val.get()), int(col_val.get()), int(width_val.get()))
 ui.destroy()


ui = tk.Tk()
ui.title("Create Markdown Table in Zim")
# Frames
optframe = ttk.Frame(master=ui, padding="4 4 4 4")
btnframe = ttk.Frame(master=ui, padding="4 4 4 4")
optframe.grid(column=0, row=1, sticky=tk.EW)
btnframe.grid(column=0, row=2, sticky=tk.EW)
# Input frame contents
row_label = ttk.Label(master=optframe, text="Rows:")
row_val = tk.StringVar(optframe, value=10)
row_inp = tk.Spinbox(optframe, from_=1, to=50, textvariable=row_val)
col_label = ttk.Label(master=optframe, text="Columns:")
col_val = tk.StringVar(optframe, value=4)
col_inp = tk.Spinbox(optframe, from_=1, to=20, textvariable=col_val)
width_label = ttk.Label(master=optframe, text="Column width in tabs:")
width_val = tk.StringVar(optframe, value=3)
width_inp = tk.Spinbox(optframe, from_=1, to=5, textvariable=width_val)
row_label.grid(column=0, row=0, sticky=tk.E)
row_inp.grid(column=1, row=0, sticky=tk.W)
col_label.grid(column=0, row=1, sticky=tk.E)
col_inp.grid(column=1, row=1, sticky=tk.W)
width_label.grid(column=0, row=2, sticky=tk.E)
width_inp.grid(column=1, row=2, sticky=tk.W)
# Button frame contents
cancel_button = ttk.Button(btnframe, text="Cancel", command=cancel_table)
cancel_button.grid(column=0, row=0, sticky=tk.E, padx=3)
maketable_button = ttk.Button(btnframe, text="Make Table", command=make_table)
maketable_button.grid(column=1, row=0, sticky=tk.E, padx=3)

ui.bind("", cancel_table)
ui.bind("", make_table)

ui.eval('tk::PlaceWindow %s center' % ui.winfo_pathname(ui.winfo_id()))

ui.mainloop()