SQLite and ADO.Net

Herein the latest episode of my long-running use of using APL to access and update SQL databases, most recently documented when migrating from MySQL to SQLite.  To date, all of my interfacing has been via Dyalog's supplied SQAPL workspace, which has served well.  What follows all relates to my present working environment (64-bit Windows 7 and Dyalog APL/W-64 Version 12.1).

A question sometimes asked is "why are you still using SQAPL and its underlying ODBC mechanism, when there are newer things like ADO.NET?".

The short answers to this are

But the question niggled, and I was overcome by that well-known attitude of "I'm an APL programmer, so I can do anything".

What Do We Need?

As best as I can tell SQLite is not directly supported by Microsoft's .NET Framework (you can deduce the level of my ignorance, because I'm not quite sure what the relationship is between .NET and ADO.NET), a third-party product needs to be used.  A Web search turned up a number of likely candidates, up at the top of the list (and with a suitable price-tag) was System.Data.SQLite from PHX Software (their website doesn't say much about who they are, or what they do).  Maybe there are better, or worse, products out there - for now this is what I'm using.

Download size is modest, and installation straightforward.  For whatever reason, the 64-bit dll file gets put into a subfolder of Program Files (x86) by default - the default path is as shown in the script.

What Does the Documentation Say?

Might as well have been written in Venusian.  Seems to refer back to Microsoft's .Net documentation (my guess is that System.Data.SQLite is a subclass of something more generic there).  There's a few examples, the one I understood was to create an empty database, but the general assumption seems to be that the reader knows what the documentation is telling them.  The forum is extensive, but the general tone seems to be set from this first sentence under "how to".

[...] After some thought, I decided that rather than do some really rudimentary tutorial code that demonstrates the simplest of SQL, my time would be better spent hitting on some of the more advanced topics and features of SQLite. [...]

So, that's what I'm here for, doing the mundane stuff for the APLer.

What's The Approach?

Lots of design choices, I decided to use Dyalog's object-orientation and write a class script, so that application code could stay aloof from the "how to" details.  I also decided that, so far as possible, everything would be self-contained - meaning that all the class would "remember" was the name of the database, each task would open and close the database connections.  If I get embarrassed by the performance hits, and/or get ambitious I might change this, but - for now - that's what I'm doing.

The way I've got the code to where it is has been largely experimental - write some code, create some objects, look at PropList and MethodList, pick out likely candidates and see what turns up.  There may well be smarter solutions to the problems I set myself - for now I'm content if I can access recognisable data.  This gives us a very simple constructor.

My use of SQAPL over the years has been straightforward - use simple SELECT statements to extract data from one or more tables, use APL to combine/analyse the data into useful forms, use INSERT/DELETE/UPDATE to make whatever changes are required.  Nothing sophisticated, and database tables with row counts in the region of hundreds/thousands.

What Does SQLite Tell Us About Itself?

Version

We find out what version of SQLite we're using with the Version property:

      s←⎕new #.UTIL.SQLite TestDatabase   ⍝ Take this as read from now on...
      s.Version
3.6.16

What Does SQLite Tell Us About the Database?

Database Name

SQLite itself refers to just the database filename, so this is a bit redundant.  Nevertheless, for the sake of completeness..

      s.Database
d:\dick\temp\test.db

Tables

More importantly, we need to know what tables it contains (assuming a degree of amnesia here, but maybe we'll want to explore an unknown database one day).  The ListTables method reveals all (or at least a carefully-selected subset of what SQLite actually knows)...

      {(⍴⍵) (⍴¨⍵) ⍵} s.ListTables ⍬                     ⍝ Just spelling out the structure
 8   4  4  3  6  3  5  7  7    this  that  aaa  aplmon  bbb  aaaaa  aplmona  aplmonb 

What Does SQLite Tell Us About Individual Tables?

Staying in amnesiac mode, the next step to exploring our database is to find out about individual tables, using the DescribeTable method...

      s.DescribeTable 'this'
 id     smallint  System.Int16 
 owner  varchar   System.String

It's also useful to know how many rows there are, provided by RowCount...

    s.RowCount 'this'
38

The keen-eyed will see something strange as the result is set, what's going on?  Answer is that what ExecuteScalar has given us is something which looks like a number, but whose ⎕dr is 326, which my trusty Dyalog Language Reference tells me is a "32 bits pointer" - whatever that might be.  Now you and I might think that the result of counting the number of rows in an SQL table would be a number, but then we're not "computer scientists".  Yet another example of the obfuscation and senseless complexity that has been foisted upon us.  We shall (no doubt) return to this topic later.

So Let's Read a Table...

Simplest case of extracting something really useful from a database, ReadTable does the business...

   {(⍴⍵) (3 2↑⍵)}  s.ReadTable 'this'
 38 2   0  owner       
       20  inserted    
       30  another row 

Notice that the Read method seems to return the first result twice - I guess there's something going on here where (once again) I'm not smart enough to figure out exactly why it's done this way.

You'll no doubt be as relieved as I am to find out that while the result here is another "32 bits pointer" we can do our APL thing and find that the contents behave just like characters and numbers.

Selective Reading

As pig-headed as I might be, reading whole tables is pretty daft.  What we really want to do is to read some fields, depending on some sort of selection - which we can handle with ReadSelected...

   ]displayr s.ReadSelected 'aplmona' 'token=''plus''' ('token' 'lfn' 'rfn')
┌3────────────────────────┐
3 ┌4───┐ ┌4───┐ ┌7──────┐ │
│ │plus│ │int8│ │float64│ │
│ └────┘ └────┘ └───────┘ │
│ ┌4───┐ ┌4───┐ ┌5────┐   │
│ │plus│ │int8│ │int32│   │
│ └────┘ └────┘ └─────┘   │
│ ┌4───┐ ┌4───┐ ┌4───┐    │
│ │plus│ │int8│ │int8│    │
│ └────┘ └────┘ └────┘    │
└2────────────────────────┘

Changed the database table to one with slightly more interesting content (the data is from Dyalog's Version 12.1 monitoring tool).

So - if you're still with it so far - now we can read from SQLite databases in what seems to be a moderately satisfactory way.  

Deleting Rows

Making our first creative step a destructive one, let's delete some rows from a table using DeleteSelected...

  s.RowCount 'this'
38
  s.DeleteSelected 'this' 'id=100'
  s.RowCount 'this'
33

Bingo, five rows gone - keep this up and there might soon be nothing left.

Updating Field Values

Taking another careful step into the realm of changing database content, we can update the values in one or more rows with UpdateField...

      s.ReadSelected 'this' 'id=10' ('id' 'owner')
10  rhubarb
10  rhubarb
10  rhubarb
10  rhubarb
10  rhubarb
10  rhubarb
      s.UpdateField 'this' 'id=10' ('owner' 'gooseberry')
1
      s.ReadSelected 'this' 'id=10' ('id' 'owner')
10  gooseberry
10  gooseberry
10  gooseberry
10  gooseberry
10  gooseberry
10  gooseberry

Five rows satisfied the "where" condition (in addition to being banal, this is also not a very sensible database table).  A little jiggery-pokery to handle numeric or character fields (not got round to dates yet) and a spontaneous result working as a place-holder for future developments (not keen on shy results).

So Let's Add a Row

In a sense, the "big one" - if we can add one row we can add as many as we like, use InsertRow...

  s.RowCount 'this'
33
 s.InsertRow 'this' ('id' 'owner') (123 'new row')
1
  s.RowCount 'this'
34

To be sure this is unlikely to be the optimal performer when adding many rows, but we're establishing principles here and trying to learn something.  And, in the interests of simplicity I've avoided bind variables, because I always found them a bit of a bind.

Let's Look at Performance

Having established that we seem to be able to read and modify database tables, how does the performance compare to SQAPL?  Two rough-and-ready tests reading complete tables (one of 4000 rows and 7 columns, the other of 8 rows and 3 columns).

On the larger table, ReadTable from the SQLite script averaged around 1500 milliseconds, SQAPL around 100 milliseconds - so things don't look great.  For the smaller table timings for the two approaches were very similar (effectively zero).

Meaning that the culprit looks like the looping round the GetValue and Read methods.  Obviously some further investigation is in order - if we find that this performance hit is actually significant in real usage (if the application is mostly selecting and amending single rows it may not matter too much).

Dyalog has introduced some experimental performance enhancements for DataTable objects, but my current state of ignorance doesn't tell me whether these might be applicable in this context.

Can System.Data.SQLite Cohabit With SQAPL?

An obvious way to migrate to ADO.NET is to add the SQLite script to existing workspaces (OK, whatever you use to build the active workspace) and incrementally replace existing code that uses SQAPL.  Which led to some frustration when first tried with a long-established workspace, ReadTable stopping short with DOMAIN ERROR in the ExecuteReader line and an APL/W Status Window populated with this gibberish...

System.Data.SQLite.SQLiteDataReader ExecuteReader(System.Data.CommandBehavior)
System.Data.SQLite.SQLiteDataReader ExecuteReader()
System.Data.Common.DbDataReader ExecuteReader()
System.Data.Common.DbDataReader ExecuteReader(System.Data.CommandBehavior)

The database seemed to be fine, because it was accessible from a development workspace.  Didn't seem to be an issue related to SQAPL because I could add the SQAPL code to my development workspace and have everything work.  But add SQLite to the "working" environment and things were not good (even suppressing the latent expression to be sure that no code had run).

Building a new workspace by copying old code into a clear workspace, then adding SQLite seems to have solved the issue - looks like over the years some undesirableness had accumulated.

Worth making a meal of this, because the way problems were reported seemed oblique at best - also because I had a very unfortunate episode when trying to run before walking and found myself with a computer that wouldn't recognise its own keyboard (sounds bizarre I know).  So, if you want to migrate to ADO.Net incrementally, it might be worthwhile to recreate workspaces from scratch.

Fancy a Date?

A "convenience" of most database packages is that they offer dates as a special data type.  Some of my databases have their origins more than a decade ago and have accumulated "dirty" data - particularly "not known" dates (set to 0000-00-00 or \\N, for various reasons).  Attempts to read rows with these "bad" values have been handled tolerantly via SQAPL, but System.Data.SQLite is stricter - give it either of these bad values and something like this pops up in the APL/W Status Window..

System.FormatException: String was not recognized as a valid DateTime.
    (   at System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
   at System.DateTime.ParseExact(String s, String[] formats, IFormatProvider provider, DateTimeStyles style)
   at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText)
   at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
   at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, Int32 index, SQLiteType typ))

Which is at least less enigmatic than what we saw in the previous example (it also comes up in red rather than blue - I need to find out about the colouring rules of the Status Window).  Tweaking the table to eliminate invalid dates (using SQAPL) puts everything to rights - now it all seems readable.

But, "helpfulness" rears its head again, and what's returned by ReadTable contains .Net DateTime values in the dates columns.  Going to have to do something about this (maybe Dyalog's experimental I-beams should be generalised so that they can operate on arbitrary arrays, not just on DataTables?).  Either way, a trivial issue (should we hold dates as day numbers or as yyyymmdd representations) has been obfuscated by the wonderful world of computer "science".

Summing Up


The SQLite Script

:Class SQLite
⍝ Class to access SQLite databases through ADO.NET 
⍝ Using System.Data.SQLite from phxsoftware.com
⍝:Dogon Research
⍝:File SQLite
⍝:Namespace #.UTIL.SQLite
⍝:Depend FILE GUI    

:USING System.Data.SQLite,c:\program files (x86)\SQLite.NET\bin\x64\System.Data.SQLite.dll 
    ⎕io ⎕ml←0 3          

    :Field Private database     

    ∇ Create w
⍝ Constructor
      :Implements Constructor
      :Access Public
      :If #.UTIL.FILE.FileExists w
          database←w
      :Else
          #.UTIL.GUI.InfoBox #'Database does not exist'(w,' does not exist')
          database←''
      :EndIf
    ∇

    :Property Database
    ⍝ Database name (for SQLite this is a filename)
    :Access Public
        ∇ z←get
          z←database
        ∇
    :EndProperty

    :Property Version
    ⍝ SQLite version
    :Access Public
        ∇ z←get;s
          s←⎕NEW SQLiteConnection''
          z←s.SQLiteVersion
        ∇
    :EndProperty

    ∇ DeleteSelected(table where);s;scommand;sr;command
      :Access Public
   ⍝ Delete rows from a table where the rows meet some sort of selection criterion
      s←OpenDatabase database
      command←'Delete from ',table,' where ',where
      scommand←⎕NEW SQLiteCommand(command s)
      sr←scommand.ExecuteNonQuery''
      s.Close
    ∇

    
    ∇ z←DescribeTable w;s;scommand;sr;fc
      :Access Public
     ⍝ Describe table fields
      s←OpenDatabase database
      scommand←⎕NEW SQLiteCommand(('Select * from ',w)s)
      sr←scommand.ExecuteReader''
      fc←sr.FieldCount
      z←⍪sr.GetName¨⍳fc
      z,←sr.GetDataTypeName¨⍳fc
      z,←sr.GetFieldType¨⍳fc
      s.Close
    ∇
   
    ∇ z←InsertRow(table fields values);s;scommand;command;sr;vals;val
      :Access Public
   ⍝ Insert a new row
      s←OpenDatabase database
      vals←''
      :For val :In values
          :If numeric val
              vals,←⊂⍕val
          :Else
              vals,←⊂'''',val,''''
          :EndIf
      :EndFor
      vals←1↓∊',',¨vals
      command←'Insert into ',table,' (',(1↓∊',',¨fields),') values (',vals,')'
      scommand←⎕NEW SQLiteCommand(command s)
      sr←scommand.ExecuteNonQuery''
      z←1
      s.Close
    ∇

    ∇ z←ListTables w;s;scommand;sr;fc;rc
      :Access Public
     ⍝ List of tables in the database
      s←OpenDatabase database
      scommand←⎕NEW SQLiteCommand('Select * from SQLITE_MASTER Where type=''table'''s)
      sr←scommand.ExecuteReader''
      fc←sr.FieldCount
      rc←0
      z←⍬
      :While rc
          z,←⊂sr.GetValue 1
          rc←sr.Read
      :EndWhile
      s.Close
      z←1↓z               ⍝ First value seems to be repeated
    ∇

    ∇ z←ReadSelected(table where fields);s;scommand;sr;fc;rc;command
      :Access Public
   ⍝ Read fields from a table where the rows meet some sort of selection criterion
      s←OpenDatabase database
      command←'Select ',(1↓∊',',¨fields),' from ',table,' where ',where
      scommand←⎕NEW SQLiteCommand(command s)
      sr←scommand.ExecuteReader''
      fc←sr.FieldCount
      rc←sr.Read
      z←⍬
      :While rc
          z,←⊂sr.GetValue¨⍳fc
          rc←sr.Read
      :EndWhile
      s.Close
      z←⊃z
    ∇

    ∇ z←ReadTable w;s;scommand;sr;fc;rc
      :Access Public
   ⍝ Read the whole of a table
      s←OpenDatabase database
      scommand←⎕NEW SQLiteCommand(('Select * from ',w)s)
      sr←scommand.ExecuteReader''
      fc←sr.FieldCount
      rc←sr.Read
      z←⍬
      :While rc
          z,←⊂sr.GetValue¨⍳fc
          rc←sr.Read
      :EndWhile
      s.Close
      z←⊃z
    ∇
   
   ∇ z←RowCount w;s;scommand;sr
    ⍝ How many rows in a table?
      :Access Public
      s←OpenDatabase database
      scommand←⎕NEW SQLiteCommand(('Select count(*) from ',w)s)
      sr←scommand.ExecuteScalar''
      z←⍎⍕↑sr
      s.Close
    ∇


   ∇ z←UpdateField(table where value);s;scommand;command;sr
      :Access Public
   ⍝ Update a field (one or more rows)
      s←OpenDatabase database
      :If numeric1⊃value
          command←'Update ',table,' set ',(↑value),'=',(⍕1⊃value),,' where ',where
      :Else
          command←'Update ',table,' set ',(↑value),'=''',(1⊃value),,''' where ',where
      :EndIf
      scommand←⎕NEW SQLiteCommand(command s)
      sr←scommand.ExecuteNonQuery''
      z←1
      s.Close
    ∇

⍝ ---------------- Internal tools

    ∇ s←OpenDatabase w
    ⍝ Open the database connection
      s←⎕NEW SQLiteConnection''
      s.ConnectionString←⊂'Data Source=',database
      s.Open
    ∇

    numeric←{ 0=1↑0⍴⍵}

⍝:Saved ⋄ 2010 2 28 14 46 35 616 ⋄ Dick ⋄ .\dbviewer
:EndClass



Page updated 7 March 2010; Copyright © Dogon Research 2010