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.

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:

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)

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()

An alternate Python CSV sniffer

I recently came across a case where the format sniffer in Python's csv module was not satisfactory.  The problem is that the format sniffer always identifies a quote character, even when there is no quote character in the CSV file (e.g., in the case of a tab-delimited file).  By default the format sniffer reports that a double-quote character is used.  This is not a problem if you subsequently use the csv module itself to read the file.  However, I wanted to use the csv module only if the file contained quote characters, and use a different method (the copy_from method of the psycopg module) if the file contained no quote characters.  I could not make this distinction using the csv module's format sniffer, so I wrote the following one to use instead.

import re

class CsvDiagError(Exception):
 def __init__(self, msg):
  self.value = msg
 def __str__(self):
  return self.value

class CsvLine():
 escchar = u"\\"
 def __init__(self, line_text):
  self.text = line_text
  self.delim_counts = {}
  self.item_errors = []  # A list of error messages.
 def __str__(self):
  return u"; ".join([u"Text: <<%s>>" % self.text, \
   u"Delimiter counts: <<%s>>" % ", ".join([u"%s: %d" % (k, self.delim_counts[k]) for k in self.delim_counts.keys()]) ])
 def count_delim(self, delim):
  # If the delimiter is a space, consider multiple spaces to be equivalent
  # to a single delimiter, split on the space(s), and consider the delimiter
  # count to be one fewer than the items returned.
  if delim == u" ":
   self.delim_counts[delim] = max(0, len(re.split(r' +', self.text)) - 1)
  else:
   self.delim_counts[delim] = self.text.count(delim)
 def delim_count(self, delim):
  return self.delim_counts[delim]
 def _well_quoted(self, element, qchar):
  # A well-quoted element has either no quotes, a quote on each end and none
  # in the middle, or quotes on both ends and every internal quote is either
  # doubled or escaped.
  # Returns a tuple of three booleans; the first indicates whether the element is
  # well-quoted, the second indicates whether the quote character is used
  # at all, and the third indicates whether the escape character is used.
  if qchar not in element:
   return (True, False, False)
  if len(element) == 0:
   return (True, False, False)
  if element[0] == qchar and element[-1] == qchar and qchar not in element[1:-1]:
   return (True, True, False)
  # The element has quotes; if it doesn't have one on each end, it is not well-quoted.
  if not (element[0] == qchar and element[-1] == qchar):
   return (False, True, False)
  e = element[1:-1]
  # If there are no quotes left after removing doubled quotes, this is well-quoted.
  if qchar not in e.replace(qchar+qchar, u''):
   return (True, True, False)
  # if there are no quotes left after removing escaped quotes, this is well-quoted.
  if qchar not in e.replace(self.escchar+qchar, u''):
   return (True, True, True)
  return (False, True, False)
 def record_format_error(self, pos_no, errmsg):
  self.item_errors.append(u"%s in position %d." % (errmsg, pos_no))
 def items(self, delim, qchar):
  # Parses the line into a list of items, breaking it at delimiters that are not
  # within quoted stretches.  (This is a almost CSV parser, for valid delim and qchar,
  # except that it does not eliminate quote characters or reduce escaped quotes.)
  self.item_errors = []
  if qchar is None:
   if delim is None:
    return self.text
   else:
    if delim == u" ":
     return re.split(r' +', self.text)
    else:
     return self.text.split(delim)
  elements = []  # The list of items on the line that will be returned.
  eat_multiple_delims = delim == u" "
  # States of the FSM:
  # _IN_QUOTED: An opening quote has been seen, but no closing quote encountered.
  #  Actions / transition:
  #   quote: save char in escape buffer / _ESCAPED
  #   esc_char : save char in escape buffer / _ESCAPED
  #   delimiter: save char in element buffer / _IN_QUOTED
  #   other: save char in element buffer / _IN_QUOTED
  # _ESCAPED: An escape character has been seen while _IN_QUOTED (and is in the escape buffer).
  #  Actions / transitions
  #   quote: save escape buffer in element buffer, empty escape buffer, 
  #    save char in element buffer / _IN_QUOTED
  #   delimiter: save escape buffer in element buffer, empty escape buffer,
  #    save element buffer, empty element buffer / _BETWEEN
  #   other: save escape buffer in element buffer, empty escape buffer,
  #    save char in element buffer / _IN_QUOTED
  # _QUOTE_IN_QUOTED: A quote has been seen while _IN_QUOTED (and is in the escape buffer).
  #  Actions / transitions
  #   quote: save escape buffer in element buffer, empty escape buffer, 
  #    save char in element buffer / _IN_QUOTED
  #   delimiter: save escape buffer in element buffer, empty escape buffer,
  #    save element buffer, empty element buffer / _DELIMITED
  #   other: save escape buffer in element buffer, empty escape buffer,
  #    save char in element buffer / _IN_QUOTED
  #     (An 'other' character in this position represents a bad format:
  #     a quote not followed by another quote or a delimiter.)
  # _IN_UNQUOTED: A non-delimiter, non-quote has been seen.
  #  Actions / transitions
  #   quote: save char in element buffer / _IN_UNQUOTED
  #    (This represents a bad format.)
  #   delimiter: save element buffer, empty element buffer / _DELIMITED
  #   other: save char in element buffer / _IN_UNQUOTED
  # _BETWEEN: Not in an element, and a delimiter not seen.  This is the starting state,
  #   and the state following a closing quote but before a delimiter is seen.
  #  Actions / transition:
  #   quote: save char in element buffer / _IN_QUOTED
  #   delimiter: save element buffer, empty element buffer / _DELIMITED
  #    (The element buffer should be empty, representing a null data item.)
  #   other: save char in element buffer / _IN_UNQUOTED
  # _DELIMITED: A delimiter has been seen while not in a quoted item.
  #  Actions / transition:
  #   quote: save char in element buffer / _IN_QUOTED
  #   delimiter: if eat_multiple: no action / _DELIMITED
  #     if not eat_multiple: save element buffer, empty element buffer / _DELIMITED
  #   other: save char in element buffer / _IN_UNQUOTED
  # At end of line: save escape buffer in element buffer, save element buffer.  For a well-formed
  # line, these should be empty, but they may not be.
  #
  # Define the state constants, which will also be used as indexes into an execution vector.
  _IN_QUOTED, _ESCAPED, _QUOTE_IN_QUOTED, _IN_UNQUOTED, _BETWEEN, _DELIMITED = range(6)
  #
  # Because of Python 2.7's scoping rules:
  # * The escape buffer and current element are defined as mutable objects that will have their
  #  first elements modified, rather than as string variables.  (Python 2.x does not allow
  #  modification of a variable in an enclosing scope that is not the global scope, but
  #  mutable objects like lists can be altered.  Another approach would be to implement this
  #  as a class and use instance variables.)
  # * The action functions return the next state rather than assigning it directly to the 'state' variable.
  esc_buf = [u'']
  current_element = [u'']
  def in_quoted():
   if c == self.escchar:
    esc_buf[0] = c
    return _ESCAPED
   elif c == qchar:
    esc_buf[0] = c
    return _QUOTE_IN_QUOTED
   else:
    current_element[0] += c
    return _IN_QUOTED
  def escaped():
   if c == delim:
    current_element[0] += esc_buf[0]
    esc_buf[0] = u''
    elements.append(current_element[0])
    current_element[0] = u''
    return _BETWEEN
   else:
    current_element[0] += esc_buf[0]
    esc_buf[0] = u''
    current_element[0] += c
    return _IN_QUOTED
  def quote_in_quoted():
   if c == qchar:
    current_element[0] += esc_buf[0]
    esc_buf[0] = u''
    current_element[0] += c
    return _IN_QUOTED
   elif c == delim:
    current_element[0] += esc_buf[0]
    esc_buf[0] = u''
    elements.append(current_element[0])
    current_element[0] = u''
    return _DELIMITED
   else:
    current_element[0] += esc_buf[0]
    esc_buf[0] = u''
    current_element[0] += c
    self.record_format_error(i+1, "Unexpected character following a closing quote")
    return _IN_QUOTED
  def in_unquoted():
   if c == delim:
    elements.append(current_element[0])
    current_element[0] = u''
    return _DELIMITED
   else:
    current_element[0] += c
    return _IN_UNQUOTED
  def between():
   if c == qchar:
    current_element[0] += c
    return _IN_QUOTED
   elif c == delim:
    elements.append(current_element[0])
    current_element[0] = u''
    return _DELIMITED
   else:
    current_element[0] += c
    return _IN_UNQUOTED
  def delimited():
   if c == qchar:
    current_element[0] += c
    return _IN_QUOTED
   elif c == delim:
    if not eat_multiple_delims:
     elements.append(current_element[0])
     current_element[0] = u''
    return _DELIMITED
   else:
    current_element[0] += c
    return _IN_UNQUOTED
  # Functions in the execution vector must be ordered identically to the
  # indexes represented by the state constants.
  exec_vector = [ in_quoted, escaped, quote_in_quoted, in_unquoted, between, delimited ]
  # Set the starting state.
  state = _BETWEEN
  # Process the line of text.
  for i, c in enumerate(self.text):
   state = exec_vector[state]()
  # Process the end-of-line condition.
  if len(esc_buf[0]) > 0:
   current_element[0] += esc_buf[0]
  if len(current_element[0]) > 0:
   elements.append(current_element[0])
  return elements
 def well_quoted_line(self, delim, qchar):
  # Returns a tuple of boolean, int, and boolean, indicating: 1) whether the line is
  # well-quoted, 2) the number of elements for which the quote character is used,
  # and 3) whether the escape character is used.
  wq = [ self._well_quoted(el, qchar) for el in self.items(delim, qchar) ]
  return ( all([b[0] for b in wq]), sum([b[1] for b in wq]), any([b[2] for b in wq]) )


def diagnose_delim(linestream, possible_delimiters=None, possible_quotechars=None):
 # Returns a tuple consisting of the delimiter, quote character, and escape
 # character for quote characters within elements of a line.  All may be None.
 # If the escape character is not None, it will be u"\".
 # Arguments:
 # * linestream: An iterable file-like object with a 'next()' method that returns lines of text
 #  as bytes or unicode.
 # * possible_delimiters: A list of single characters that might be used to separate items on
 #  a line.  If not specified, the default consists of tab, comma, semicolon, and vertical rule.
 #  If a space character is included, multiple space characters will be treated as a single
 #  delimiter--so it's best if there are no missing values on space-delimited lines, though
 #  that is not necessarily a fatal flaw unless there is a very high fraction of missing values.
 # * possible_quotechars: A list of single characters that might be used to quote items on
 #  a line.  If not specified, the default consists of single and double quotes.
 if not possible_delimiters:
  possible_delimiters = [ u"\t", u",", u";", u"|"]
 if not possible_quotechars:
  possible_quotechars = [ u'"', u"'"]
 lines = []
 for i in range(100):
  try:
   ln = linestream.next()
  except StopIteration:
   break
  except:
   raise
  while len(ln) > 0 and ln[-1] in (u"\n", u"\r"):
   ln = ln[:-1]
  if len(ln) > 0:
   lines.append(CsvLine(ln))
 if len(lines) == 0:
  raise CsvDiagError(u"CSV diagnosis error: no lines read")
 for ln in lines:
  for d in possible_delimiters:
   ln.count_delim(d)
 # For each delimiter, find the minimum number of delimiters found on any line, and the number of lines 
 # with that minimum number
 delim_stats = {}
 for d in possible_delimiters:
  dcounts = [ ln.delim_count(d) for ln in lines ]
  min_count = min(dcounts)
  delim_stats[d] = (min_count, dcounts.count(min_count))
 # Remove delimiters that were never found.
 for k in delim_stats.keys():
  if delim_stats[k][0] == 0:
   del(delim_stats[k])
 def all_well_quoted(delim, qchar):
  # Returns a tuple of boolean, int, and boolean indicating: 1) whether the line is
  # well-quoted, 2) the total number of lines and elements for which the quote character
  # is used, and 3) the escape character used.
  wq = [ l.well_quoted_line(delim, qchar) for l in lines ]
  return ( all([b[0] for b in wq]), sum([b[1] for b in wq]), CsvLine.escchar if any([b[2] for b in wq]) else None )
 def eval_quotes(delim):
  # Returns a tuple of the form to be returned by 'diagnose_delim()'.
  ok_quotes = {}
  for q in possible_quotechars:
   allwq = all_well_quoted(delim, q)
   if allwq[0]:
    ok_quotes[q] = (allwq[1], allwq[2])
  if len(ok_quotes) == 0:
   return (delim, None, None) # No quotes, no escapechar
  else:
   max_use = max([ v[0] for v in ok_quotes.values() ])
   if max_use == 0:
    return (delim, None, None)
   # If multiple quote characters have the same usage, return (arbitrarily) the first one.
   for q in ok_quotes.keys():
    if ok_quotes[q][0] == max_use:
     return (delim, q, ok_quotes[q][1])
 if len(delim_stats) == 0:
  # None of the delimiters were found.  Some other delimiter may apply,
  # or the input may contain a single value on each line.
  # Identify possible quote characters.
  return eval_quotes(None)
 else:
  if len(delim_stats) > 1:
   # If one of them is a space, prefer the non-space
   if u" " in delim_stats.keys():
    del(delim_stats[u" "])
  if len(delim_stats) == 1:
   return eval_quotes(delim_stats.keys()[0])
  # Assign weights to the delimiters.  The weight is the square of the minimum number of delimiters
  # on a line times the number of lines with that delimiter.
  delim_wts = {}
  for d in delim_stats.keys():
   delim_wts[d] = delim_stats[d][0]**2 * delim_stats[d][1]
  # Evaluate quote usage for each delimiter, from most heavily weighted to least.
  # Return the first good pair where the quote character is used.
  delim_order = sorted(delim_wts, key=delim_wts.get, reverse=True)
  for d in delim_order:
   quote_check = eval_quotes(d)
   if quote_check[0] and quote_check[1]:
    return quote_check
  # There are no delimiters for which quotes are OK.
  return (delim_order[0], None, None)
 # Should never get here
 raise CsvDiagError(u"CSV diagnosis error: an untested set of conditions are present")

Converging and Cyclic Cascades with Firebird

With the impending implementation of Firebird as a backend for LibreOffice Base, to replace HSQLDB, I re-ran the tests of converging and cyclic cascades that were previously carried out for other databases.  These tests were carried out using Firebird 2.5 running on Ubuntu Server 12.04.2 (64 bit), and using FlameRobin 0.9.3.2220 Unicode running on Ubuntu 13.04 (64 bit).

The only revision to the previous code was the elimination of the "null" specification for the element column of the d_sample table.

All of the tests of converging and cyclic cascades were completed successfully with Firebird.  Firebird therefore joins PostgreSQL and SQLite among the DBMSs that are feasible options for implementing complex data structures.

Surrogate Keys and Data Integrity Errors

Discussions of the use of surrogate keys versus natural keys rarely address the full range of data integrity errors that can occur when surrogate keys are used. This post illustrates how surrogate keys can lead to loss of data integrity in a way that is apparently not widely recognized.

There is one well-known type of data integrity error that can occur with surrogate keys. That is, when a table with a surrogate key also has a column (or combination of columns) that make up a natural key, duplicate data can be entered--and entity integrity therefore violated. This potential problem can be easily avoided, however, by creating a unique index on the columns making up the natural key.

The other, and less widely recognized, problem can lead to loss of relational integrity between tables. This problem can be illustrated using the data structure shown in Figure 1. This data structure represents a series of measurements made by a single organization (laboratory) using multiple instruments, as might be done when testing materials or procedures under a variety of different circumstances. Figure 1 shows the model data structure, which is the most direct representation of real-world data and relationships, using natural keys as primary keys. Only the primary key columns of each table are shown in the figure; in practice these tables would have additional attribute columns.

The DDL that would create this data structure is shown in Listing 1. Figure 1 and Listing 1 are provided primarily for reference, and for contrast with the approach to implementing this data model using surrogate keys.

Figure 1. Model data structure

Listing 1.  DDL for model data structure

create table laboratory (
 lab character varying (10) not null primary key
 );

create table instrument (
 lab character varying (10) not null,
 instrument character varying (10) not null,
 constraint pk_instrument primary key (lab, instrument),
 constraint fk_instumentlab foreign key (lab)
  references laboratory (lab)
  on update cascade on delete cascade
 );

create table run (
 lab character varying (10) not null,
 run_no character varying (10) not null,
 constraint pk_run primary key (lab, run_no)
 constraint fk_run_lab foreign key (lab)
  references laboratory (lab)
  on update cascade on delete cascade
 );

create table labdata (
 lab character varying (10) not null,
 run_no character varying (10) not null,
 instrument character varying (10) not null,
 material character varying (10) not null,
 constraint pk_labdata primary key (lab, run_no, instrument, material),
 constraint fk_labdata_run foreign key (lab, run_no)
  references run (lab, run_no)
  on update cascade on delete cascade,
 constraint fk_labdata_instr foreign key (lab, instrument)
  references instrument (lab, instrument)
  on update cascade on delete cascade
 );

The implementation of this model using surrogate keys is shown in Figure 2. Columns making up both the primary (surrogate) key and the candidate (natural) key are shown. Attribute columns, again, are not shown. The calibration table is not shown in Figure 2, as it is not needed for this example.

Figure 2. Model data structure with surrogate keys

DDL to implement this model using surrogate keys is shown in Listing 2. Because surrogate keys are not part of the SQL standard, the implemetation of surrogate keys is DBMS-specific. In this case (Figure 1 and Listing 2), the implementation is specific to SQL Server. SQL Server is used for this example because it cannot cascade updates and deletions through multiple pathways, and surrogate keys can eliminate the need for cascading updates. Thus, a SQL Server implementation of the model data structure is particularly likely to make use of surrogate keys. Three notable features of this DDL are:

• Each table has a unique index on the natural key to prevent data integrity errors resulting from creation of duplicate rows with the same natural key.

• There is no specification that updates be cascaded, in the expectation that the use of surrogate keys makes this unnecessary.

• All deletions are cascaded using triggers instead of "on delete cascade" clauses. SQL Server cannot cascade deletions to the labdata table, so one at least of those cascading deletions has to be handled by a trigger. In that case, however, SQL Server also cannot automatically cascade deletions from the laboratory table to the child table because SQL Server does not support "before delete" triggers, and the "instead of delete" trigger that must be used prevents SQL Server from guaranteeing that the automatic cascading deletion from the laboratory table is completed successfully. Consequently, all deletions are cascaded using triggers.

Listing 2. DDL for model data structure with surrogate keys

create table laboratory (
 lab_id integer identity(1,1) not null primary key,
 lab character varying (10) not null
 );
create unique index ix_nk_lab on laboratory (lab);
GO

create table instrument (
 instrument_id integer identity(1,1) not null primary key,
 lab_id integer not null,
 instrument character varying (10) not null,
 constraint fk_instrlab foreign key (lab_id)
  references laboratory (lab_id)
 );
create unique index ix_nk_instrument on instrument (instrument);
GO

create table run (
 run_id integer identity(1,1) not null primary key,
 lab_id integer not null,
 run_no character varying (10) not null,
 constraint fk_runlab foreign key (lab_id)
  references laboratory (lab_id)
 );
create unique index ix_nk_run on run (lab_id, run_no);
GO

create trigger tr_lab_del on laboratory instead of delete as
 begin
 delete from instrument where lab_id in (select lab_id from Deleted);
 delete from run where lab_id in (select lab_id from Deleted);
 delete from laboratory where lab_id in (select lab_id from Deleted);
 end;
GO

create table labdata (
 labdata_id integer identity(1,1) not null primary key,
 run_id integer not null,
 instrument_id integer not null,
 material character varying (10) not null,
 constraint fk_labdatarun foreign key (run_id)
  references run (run_id) on delete cascade,
 constraint fk_labdatainst foreign key (instrument_id)
  references instrument (instrument_id)
 );
create unique index ix_nk_labdata on labdata (run_id, instrument_id, material);
GO

create trigger tr_instr_del on instrument instead of delete as
 begin
 delete from labdata where instrument_id in (select instrument_id from Deleted);
 delete from instrument where instrument_id in (select instrument_id from Deleted);
 end;

create trigger tr_run_del on run instead of delete as
 begin
 delete from labdata where run_id in (select run_id from Deleted);
 delete from run where run_id in (select run_id from Deleted);
 end;

Listing 3 contains DML to insert several rows into the laboratory, instrument, and run tables of the structure shown in Figure 2.

Listing 3. Load lab, instrument, and run data

insert into laboratory (lab) values ('LabA');
insert into laboratory (lab) values ('LabB');

insert into instrument (lab_id, instrument) values (
 (select lab_id from laboratory where lab='LabA'),
 'Instr1');

insert into run (lab_id, run_no) values (
 (select lab_id from laboratory where lab='LabB'),
 'Run001');

At this point the data tables have been created using surrogate keys, and valid data have been added to three of the tables. Data can now be added to the labdata table. Adding data to that table will illustrate how surrogate keys allow the introduction of data integrity errors.

Listing 4 shows the addition of a row to the labdata table. This insert statement should fail, because the row that is added references a run conducted by laboratory B, using an instrument at laboratory A. The insert statement succeeds, however, introducing invalid data into the labdata table. Such a statement would fail if natural keys were used for these tables. The statement succeeds, and introduces invalid data, because surrogate keys have been used.

Listing 4. Insert lab data

insert into labdata (run_id, instrument_id, material) values (
 (select run_id from run 
  where lab_id=(select lab_id from laboratory where lab='LabB') 
  and run_no='Run001'),
 (select instrument_id from instrument 
  where lab_id=(select lab_id from laboratory where lab='LabA')
  and instrument='Instr1'),
 'MaterialX'
 );

To alleviate this problem, a trigger needs to be created for the labdata table which, for insert or update, will compare the corresponding lab_id values in the instrument and run tables, and cancel the insert or update statement if the lab_id values do not match. In this case the trigger only needs to look back one level.  In other data models the trigger may need to look back through multiple levels of tables to check the data.

However, addition of such a trigger on the labdata table does not eliminate all the ways in which data integrity errors can be introduced. Data integrity errors can also be introduced via update statements to the instrument and run tables. Listing 5 shows an update to values in the run table that will introduce data integrity errors into the labdata table despite the new trigger on the labdata table. Updates to the instrument table can have the same effect.

Listing 5. Update data

insert into laboratory (lab) values ('LabC');
update run 
set lab_id=(select lab_id from laboratory where lab='LabC') 
where 
 lab_id=(select lab_id from laboratory where lab='LabB')
 and run_no='Run001';

The problem of update statements introducing data integrity errors can also be addressed by adding triggers. Conceptually, in this case a trigger should be defined for the run table (and also for the instrument table) that looks into the labresult table to check that conflicting data are not created. For this data model, assuming that there are not already invalid data in the labdata table, an update like that shown in Listing 5 would always create a conflict within the affected row of the labresult table, and therefore introduce invalid data. Therefore, as a practical matter, the trigger on the run table (and also on the instrument table) should simply prevent updates like that shown in Listing 5.

Updates like that shown in Listing 5 do not need to be prevented if natural keys are used. If natural keys are used, the update would fail if Lab C is not also referenced in the instrument table, but it would succeed if the instrument table contains compatible data.

This example illustrates that the use of surrogate keys with certain types of data models potentially allows the introduction of data integrity errors, and that the problem a) requires the creation of additional triggers for insert and update operations, and b) may prevent the execution of legitimate update operations.

Converging and Cyclic Cascades in Database Systems

Enforcement of referential integrity is an essential feature of a database management system that will be used for non-trivial applications. However, among DBMSs that implement some referential integrity features there are important differences in the DBMS' abilities to propagate cascading updates and deletes through multiple pathways. Some commonly used DBMSs provide only partial, or no, support for cascading updates and deletes through multiple pathways. The lack of support for cascading updates and deletes means that the database cannot be used to accurately model some types of real-world relationships, or that artificial data structures must be used to work around the limitation.

There are two different basic data structures in which updates and deletes may be cascaded through multiple pathways. These two situations need to be distinguished because some DBMSs will cascade updates in one situation but not in the other. The two data structures are:

• The primary key defined in one table (first level) is referenced in two child tables (second level), and both of the child tables serve as parents of a fourth table (third level) that has a foreign key into each of the third-level tables.
• The primary key defined in one table is referenced by two different columns of a child table.

Both of these situations are illustrated in the following figure.

This is a simplified model of environmental sampling information. Samples may be collected as part of several different studies. Any study may be subdivided into different elements (representing, for example, different phases, teams, locations, or sample types). Some studies do not have distinct elements, so the study element table will not be populated. There are therefore two pathways from the study table to the sample table: one direct pathway and one indirect pathway via the study element table, where the latter pathway may or may not be complete. An update or deletion of a study identifier in the study table needs to be propagated through both pathways. The relationship between the study table and the sample table illustrates the first of the two types of structures where updates and deletions must be cascaded through multiple pathways.

The relationship between the length unit lookup table and the sample table illustrates the second of the two types of structures. Here the sample table has two distinct foreign keys into the length unit lookup table, each making up a distinct pathway between the two tables.

Several common and widely used DBMSs vary in their abilities to handle these types of data structures. The differences in support for cascading updates and deletions in data structures containing these features, including testing code and test results, are presented below for:

• PostgreSQL 9.0.3,
• MySQL 5.1.49,
• SQL Server 2008 R2,
• SQLite 3.7.2,
• MS-Access 2007, and
• LibreOffice Base 3.3.2.

Initializing the Databases

The DDL used to create the tables used for this test is:

create table d_study (study character varying(10) not null primary key);


create table d_studyelement (study character varying (10) not null,
  element character varying(10) not null,
  constraint pk_element primary key (study, element),
  constraint fk_elementstudy foreign key (study) references d_study (study)
    on update cascade on delete cascade);


create table l_lunit (length_unit character varying(10) not null primary key);


create table d_sample (study character varying (10) not null,
  sample character varying (10) not null,
  element character varying (10) null,
  surface_elevation double precision,
  elevation_units character varying (10),
  sample_depth double precision,
  depth_units character varying (10),
  constraint pk_sample primary key (study, sample),
  constraint fk_samplestudy foreign key (study) references d_study (study)
    on update cascade on delete cascade,
  constraint fk_sampleelem foreign key (study, element) references d_studyelement
    (study, element) on update cascade on delete cascade,
  constraint fk_sampleelev foreign key (elevation_units) references l_lunit (length_unit)
    on update cascade on delete cascade,
  constraint fk_sampledepth foreign key (depth_units) references l_lunit (length_unit)
    on update cascade on delete cascade);

This DDL can be executed directly by PostgreSQL, MySQL, SQLite, LibreOffice Base, and SQL Server. For MS-Access, the foreign key constraint specifications must be removed, and created using MS-Access' GUI instead. Also, for MS-Access, the character varying type must be replaced with a text type.

SQLite supports foreign keys (and cascading updates and deletions) only if PRAGMA foreign_keys = ON is specified. MySQL supports foreign keys only when the InnoDb engine is used.

The DML used to initialize these tables with testing data is:

insert into d_study (study) values ('StudyA');
insert into d_study (study) values ('StudyB');


insert into d_studyelement (study, element) values ('StudyA', 'Elem1');
insert into d_studyelement (study, element) values ('StudyA', 'Elem2');


insert into l_lunit (length_unit) values ('meters');
insert into l_lunit (length_unit) values ('cm');
insert into l_lunit (length_unit) values ('ft');


insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyA', 'Sample1', 'Elem1', 1234, 'meters', 2, 'cm');
insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyA', 'Sample2', 'Elem1', 1234, 'meters', 50, 'cm'); 
insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyA', 'Sample4', 'Elem1', 1234, 'meters', 1.2, 'meters');
insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyA', 'Sample5', 'Elem2', 602, 'ft', 2, 'meters');
insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyA', 'Sample6', 'Elem2', 602, 'ft', 4, 'meters');
insert into d_sample (study, sample, element, surface_elevation,
  elevation_units, sample_depth, depth_units)
  values ('StudyB', 'Sample1', null, 900, 'meters', 2, 'cm');

The same DML can be used for all databases, except for those cases (described below) where DBMS limitations do not allow data to be inserted.

Tests

The ability of each DBMS to cascade updates and deletes through multiple pathways can be illustrated using the following three test statements applied to databases initialized as described above.

1. Cascade an update through multiple paths

Changing the name of a study in the study table should result in that change being cascaded to the sample table through two pathways, both directly and via the study element table.

update d_study set study='StudyZ' where study='StudyA';

2. Cascade an update of multiple columns

Changing the code used for a length unit in the length unit table should result in that change being cascaded to both of the length unit columns in the sample table.

update l_lunit set length_unit='m' where length_unit='meters';

3. Cascade a deletion through multiple paths

Deleting a study from the d_study table should result in that change being cascaded to the sample table.

delete from d_study where study='StudyZ';

The statement above is appropriate when the first test of cascading updates has been completed successfully. When the first test is not passed, then the where clause for this test should reference 'StudyA' instead of 'StudyZ'.

DBMS Support for Converging Cascading Updates and Deletions

The following table summarizes the capabilities of the different DBMSs to cascade updates and deletions through multiple paths.

DBMS	Create relationships	Add data	Cascade update though multiple paths	Cascade update of multiple columns	Cascade deletions
PostgreSQL	Yes	Yes	Yes	Yes	Yes
MySQL (InnoDb)	Yes	Yes	No	Yes	Yes
SQL Server	No	No	No	No	No
SQLite	Yes	Yes	Yes	Yes	Yes
MS-Access	Yes	Partial (see text)	Yes	Yes	Yes
LibreOffice Base	Yes	Yes	No	Yes	Yes

PostgreSQL and SQLite are the only DBMSs that correctly handle both cascading updates and deletes for both types of multiple pathways.

MySQL and LibreOffice Base both fail to cascade updates through multiple pathways, but can cascade updates of multiple columns, and can cascade deletions through multiple pathways.

Microsoft Access can't add all of the example data. The sample for StudyB with a null element can't be added because MS-Access does not allow null columns in a foreign key relationship. This limitation prohibits the use of data structures in which one table is linked to several different parent tables by different columns, and at least two of those links contain data derived from a common parent. To ensure that every row in the table is linked to only one of the parents, all but one of the foreign keys in a row must be null, and this condition will prohibit data addition in MS-Access. In the case where the data does not contain nulls (as for 'StudyA' and 'Elem1' in the example), MS-Access successfully propagates updates and deletes through both types of data structures.

SQL Server is notable among the DBMSs tested here because it does not allow creation of the data structure to test cascading updates and deletions. Executing the DDL to create the sample table causes SQL Server to issue the error message:

Introducing FOREIGN KEY constraint 'fk_sampleelem' on table 'd_sample' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

If the foreign key from the sample table to the study element table is not specified (removed from the DDL or moved to the end of the CREATE TABLE statement, then SQL Server issues the error message

Introducing FOREIGN KEY constraint 'fk_sampledepth' on table 'd_sample' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

SQL Server does not allow either type of relationship to be created with foreign keys to automatically maintain referential integrity. This behavior is by design. Commonly recommended workarounds are to use surrogate keys and disable cascading updates, or to create custom triggers to propagate updates and deletions. One rationale for this limitation of SQL Server is that it protects the user from cascading operations through data structures containing cyclic relationships; presumably this protection is needed because such cascading operations would fail or introduce data integrity errors.

Data Structures with Cyclic Relationships

Are limitations on cascading updates and deletions through multiple pathways necessary to protect against infinite loops or data integrity errors? Such effects should not happen in the previous examples because there are no cycles in those data structures. However, perhaps the DBMSs that allow cascading through multiple paths are vulnerable to problems when updates and deletions are cascaded through data structures that actually do contain cyles. Tests with cyclic data were performed for all the DBMSs that allow the specification of cascading updates and deletions for such structures.

The data structure used for these tests is a single self-referential table, illustrated below.

The column names used in this example are representative of a data structure used for tree-structured data, but such self-referential structures are appropriate for a variety of types of data.

With this simple structure, two different types of data cycles were tested:

1. A completely self-referential cycle, where a node is its own parent.
2. An intertwined cross-reference, where two nodes each reference the other as their parent.

Both of these types of cycles may in some circumstances represent a logical error in the data. However, for those DBMSs that allow a self-referential structure to be created with automatic cascading of updates and deletions, these situations should nevertheless be handled sensibly.

Cascading updates and deletions were tested for all DBMSs except for SQL Server, which will not create this structure. Two deletion options were tested: "on delete cascade" and "on delete set null". All the DBMSs tested support the "on delete set null" option except for MS-Access.

Code to create the table and carry out cycle test #1 is below:

create table d_node (
  node_name character varying(10) not null,
  parent_node character varying(10),
  constraint pk_node primary key (node_name),
  constraint fk_node foreign key (parent_node)
    references d_node (node_name)
    on update cascade on delete cascade
  );


-- **** Test update
insert into d_node (node_name, parent_node) values ('NodeA', 'NodeA');
insert into d_node (node_name, parent_node) values ('NodeB', 'NodeA');
update d_node set node_name='NodeZ' where node_name='NodeA';
-- Check result
select * from d_node;


-- **** Test deletion with cascading deletions on deletes
delete from d_node;
insert into d_node (node_name, parent_node) values ('NodeA', 'NodeA');
insert into d_node (node_name, parent_node) values ('NodeB', 'NodeA');
delete from d_node where node_name='NodeA';
-- Check result
select * from d_node;


-- **** Test deletion with set null on deletes
delete from d_node;
alter table d_node drop constraint fk_node;
alter table d_node
add constraint fk_node foreign key (parent_node)
    references d_node (node_name)
    on update cascade on delete set null;
insert into d_node (node_name, parent_node) values ('NodeA', 'NodeA');
insert into d_node (node_name, parent_node) values ('NodeB', 'NodeA');
delete from d_node where node_name='NodeA';
-- Check result
select * from d_node;

The code to carry out cycle test #2 is similar, except that the following insert statements are used:

insert into d_node (node_name, parent_node) values ('NodeA', null);
insert into d_node (node_name, parent_node) values ('NodeB', 'NodeA');
update d_node set parent_node='NodeB' where node_name='NodeA';

The results of tests on the two types of cyclic data relationships are shown in the following table. A notation of "Yes" indicates that cascading updates and deletions were completed without error. The two entries for the deletion tests represent the results for "on delete cascade" and "on delete set null" options, respectively.

DBMS	Cycle Test 1 update	Cycle Test 1 delete	Cycle Test 2 update	Cycle Test 2 delete
PostgeSQL	Yes	Yes/Yes	Yes	Yes/Yes
MySQL (InnoDb)	No	Yes/Yes	No	Yes/Yes
SQLite	Yes	Yes/Yes	Yes	Yes/Yes
MS-Access	Yes	Yes/No	Yes	Yes/No
LibreOffice Base	Yes	Yes/Yes	Yes	No/Yes

Both PostgreSQL and SQLite complete all cascading updates and deletions without error.

The update operation on both tests fails in MySQL with an error message indicating that a foreign key constraint fails. However, deletions are cascaded correctly for both deletion options.

MS-Access correctly executes some update and deletion cascades for both tests. However, MS-Access does not support, and therefore cannot successfully complete, cascading deletions with a "set null" option.

LibreOffice Base correctly executes the update and delete operations of test 1, and the update operation of test 2. LibreOffice Base fails to complete the cascading deletion in test 2 with a stack overflow error. However, if the deletion cascade option is changed to "set null", LibreOffice Base completes the deletion without error. The results for cascading updates are interesting because although LibreOffice Base fails to cascade updates through multiple pathways, it successfully cascades updates through cyclic data structures.

Although usability assessments are not the focus of these tests, LibreOffice's user interface is notably ill suited to handling the cyclic data structure used here: the UI does not allow interactive creation of the self-join, and when data are edited interactively, the GUI display is not immediately updated to show the cascaded updates, with the consequence that the update appears not to have been performed correctly. There is no option to refresh the display, so the table must be closed and re-opened to see the effect of interactive changes that are cascaded through a self-join.

Conclusion

Although cascading updates and deletions are supported by many DBMSs, there are important variations in the robustness with which these features are implemented, particularly when there are multiple cascade pathways and cyclic structures. The differing capabilities may be important when selecting a DBMS with which to implement a particular data model. Most of the commonly used DBMSs tested failed to support cascading updates and deletions in one or more ways. However, there is currently at least one desktop DBMS (SQLite) and one client-server DBMS (PostgreSQL) that will successfully propagate cascading updates and deletions through multiple pathways and through cyclic data structures.

Linux Outliners

I have used outline-based tools to record notes and reference information since KAMAS became available for CP/M. Currently there are a number of outliners (or similar wiki-based software) available for Linux. Here is a comparison of the features of several of them. The applications and the features considered here reflect my personal judgements regarding importance and usability.

The outliners, or similar applications, considered here are:
• NoteCase (http://notecase.sourceforge.net/).
• Zim (http://zim-wiki.org/).
• KeepNote (http://keepnote.org/keepnote/).
• CherryTree (http://www.giuspen.com/cherrytree/).
• NeverNote (http://nevernote.sourceforge.net/) -- Not really an outliner, but used for the same general purpose
• BasKet Note Pads (http://basket.kde.org/)
• Tomboy (http://projects.gnome.org/tomboy/) -- A wiki rather than an outliner an outliner.

Feature Descriptions

Features that are important to me are described individually below, followed by a matrix showing the features in each application. This list of features reflects only those that are either important or interesting to me, and is far from a complete list of all the features found in any of these applications.

Text formatting
Text formatting features include use of bold, italic, or monospace typefaces; different text sizes; foreground and background colors; bulleted lists and numbered lists; and alignment and indentation. All of these applications have some subset of these features, although not necessarily all of them. This comparison does not include a detailed accounting of the differences between applications. For my use, almost any basic set of text formatting features is adequate.

Tables
Much of the reference information that I want to have readily available is best presented in tabular format. The ability to insert a table in a note, or create an outline node that is entirely a table, is therefore very high on my list of important features. Sorting and other spreadsheet features are not necessary.

Embedded images
Although most of my reference information and ongoing notes are textual, diagrams and other illustrations are often very valuable accompaniments to the text. Quite a few of my notes include equations or mathematical/statistical expressions, and these are most clearly presented in the form of images, such as TeX output.

Tags
Although an outline is a useful paradigm for structuring a lot of information, it is not necessarily the only, or the best, way to represent the relationships between different chunks of information. The ability to assign tags opens up the possibility of identifying related notes in a way that cuts across the more rigid outline structure (or flat structure for non-outlining software). Searching, selecting, or highlighting features should therefore be available that operate upon the tags.

Password / encryption
I have several dozen records of user IDs and other types of login information that I prefer to keep stored in encrypted format. Although this is a relatively small amount of information, and a single encrypted text file could be used to store it, I would prefer to be able to manage this information using the same application that I use for all my other notes.

Windows version
Despite the fact that this comparison is specifically for Linux-based software, I also use Windows both at work and at home. Therefore, the ability to access and update my notes on Windows computers is important to me.

Cross-computer sync
The file(s) used by each of these applications to store notes can be synchronized across computers using DropBox or Ubuntu One (though I have not confirmed this with all the applications). However, an application-specific synchronization tool that is able to identify and help resolve possibly inconsistent updates is preferable.

Wiki links
The ability to directly link one note to another provides a valuable alternative to the outline for structuring information. (In the case of NeverNote and Tomboy, wiki links are actually the only way to structure the information.)

File links
Notes about ongoing projects frequently reference files or directories on disk, and the ability to embed links to those files within a note makes the note-taking software more useful as a place to centralize documentation, explanation, interpretation, and planning information.

URL links
Live links to URLs are equivalent to live links to files: an important feature that makes the note-taking software a useful place to centralize documentation.

Mailto links
Although not as important as file links and URL links, live links to e-mail addresses can also be very useful. Ideally (but not generally implemented) is to have e-mail addresses in text automatically recognized (e.g., when the text of an e-mail is dropped or pasted into a note).

Equations
Mathematical or statistical formulae are important for a small fraction of my notes. Plain text representations of equations are usually unclear. Automatic rendering of equations following entry of TeX or MathML is a very nice feature--very valuable on those occasions when formulae are called for.

Date integration
Several types of notes, particularly those for work projects, are fundamentally date-related. These include daily journal entries, meeting notes, records of deliverables, and important communications. The ability to assign a date as an attribute of a note, not just in the text of the note, would allow notes to be selected and sorted by date or date range.

Drop e-mail to create note
A lot of information that arrives by e-mail would be valuable to capture in project notes. Although the information can be copied and pasted from e-mail messages to notes, drag-and-drop capability would be a more efficient means of capturing the information--especially if combined with automatic creation of links for all e-mail addresses in the text of the dropped e-mail. My testing of e-mail dropping was done with Evolution.

List notes matching search
Whether searching by text, tags, or dates, often the search must be conducted across an entire branch or the entire outline. In such cases, the most useful result of the search is a display of all of the matching notes. From this display the user can then quickly determine which note or notes he or she is searching for. Although all applications provide some form of searching, few display a list of all of the notes that match the search criteria.

Saved searches
Some searches may be carried out repeatedly. An example would be a search to find all notes of a certain type that were created in the last week or in the last month, as may be used to help create a recurring project status report. Rather than re-entering the search criteria every time this search is to be run, executing a saved version of the search specifications would be quicker and less error-prone. (This feature is also of greatest use when combined with the ability to list all the notes that match the search criteria.)

Outline colors or icons
Notes, or outline nodes, may be of various different types. The ability to apply custom icons or colors to distinguish these types helps the user to rapidly find information in the outline.

To-do integration
A frequent use for a note-taking application in a work environment is to record meeting notes, and commonly this includes identification of actions that the note-taker is to complete. These actions, or tasks, will ordinarily be recorded in the text of the meeting notes. At this point:
• The tasks may be manually re-entered into a different to-do list application,
• The tasks may be automatically transferable to a to-do list application, or
• The note taking software itself may have adequate features to be used as a to-do list application.
Either of the latter two is preferable to the first.

Code formatting
Although a note-taking application is not an appropriate tool for managing source code, nevertheless it is sometimes valuable to include code snippets in a note. Features that facilitate the display of code in a note are the use of a monospaced font and syntax highlighting.

Line numbering
Line numbering is useful when the text of a note needs to reference other text, such as a quote or a code snippet.

Multi-user collaboration
Allowing multiple individuals to view, enter, and update notes facilitates collaborative project work. This reduces the effort and time to extract, summarize, and transmit notes between team members, and to integrate any updates and comments that they may have.

Feature Comparisons

Feature	NoteCase	Zim	KeepNote	CherryTree	NeverNote	BasKet	Tomboy
Version reviewed	1.9.8	0.47	0.6.7	0.16	0.92.1	1.0.cmake	1.4.0
Text formatting	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Tables				Yes	Yes (a)
Embedded images	Yes	Yes	Yes	Yes	Yes	Yes
Tags					Yes	Yes
Password / Encryption	Yes					Yes
Windows version	Yes	Yes	Yes	Yes	Yes (b)		Yes
Cross-computer sync					Yes		Yes
Wiki links	Yes	Yes	Yes	Yes			Yes
File links	Yes	Yes	Yes (c)	Yes		Yes
URL links	Yes	Yes (d)	Yes	Yes		Yes	Yes
Mailto links	Yes	Yes (d)	Yes			Yes	Yes
Equations		Yes					Yes
Date integration		Yes (e)	Yes (f)
Drop e-mail to create note	(g)	(h)	(h)		(h)	(n)	(o)
List notes matching search		Yes	Yes
Saved searches					Yes (i)
Outline colors or icons			Yes (j)			Yes (j)
To-do integration		Yes (k)				Yes (k)
Code formatting			Yes (l)	Yes (m)		Yes	Yes (l)
Line numbering	Yes			Yes
Multi-user collaboration					Yes

Notes

a) In Evernote, tables can't be modified after creation. Tables are not implemented in the Linux client (NeverNote), thus their availability and usability are very limited.
b) Evernote, not NeverNote.
c) As attachment to note rather than as a link in the note text.
d) Zim automatically recognizes and creates appropriate links for text beginning with "http:" or "mailto:". Other applications require extra steps to establish these types of links.
e) Notes can be created in the outline structure by date (via a plugin), but there is no other way to sort notes by date.
f) Creation and modification date are stored, and a view can show them sorted by date, but a note can't be assigned to a specific date.
g) The temporary cached file can be attached--but it is temporary.
h) A link to the temporary cached file is inserted--but the file is temporary.
i) Applies only to current note; does not find all notes with the search term.
j) A custom icon can be applied to each note.
k) Including hierarachical to-do items with completion rollup, but no date, alarms, or time tracking.
l) Allows switch to monospace font within a note, which can be used to set off code.
m) Allows language-specific syntax highlighting (and line numbering) of code within blocks inside a note or in separate notes.
n) The title of the e-mail is copied into the note, and the body is inserted as an attachment that, when clicked, is imported into Evolution again.
o) Dropping produces a link in the note, but clicking that link crashes Evolution.


Intangibles

KeepNote has the nicest interface. In addition to a pane showing the outline and a pane showing the current note, it has a third pane that shows children of the node selected in the outline (even if that node is collapsed in the outline) or all of the nodes that match a search criterion.

Nevernote (Evernote) and Tomboy are not really outliners--the notes are unstructured. NeverNote has tags to organize notes, and Tomboy has wiki links. Neither is as suitable for keeping a number of related notes together as are the outline-based applications.

BasKet Note Pads is very slow to start up. NeverNote is relatively slow also. BasKet has a relatively odd paradigm for notes, which I find distracting during use. BasKet (or a BasKet file) also can be damaged in ordinary use so that it causes BasKet to crash a couple of seconds after startup. (Inserting a text item after a link to an image file will do it.)

Summary

All of these applications have features that I like, but none clearly dominates the others. Putting together elements from my own list, above, of important or nice-to-have features, the ideal outliner would have a combination of KeepNote's three-pane interface, CherryTree's tables, Zim's automatic recognition of URL and mailto links, and NeverNote's tags. Add good to-do list integration, good calendar integration, and CherryTree's code formatting for a comprehensively powerful note-taking application.

(This post was composed in CherryTree, exported to HTML, and simply pasted here.)