Classes Handling SQL Databases and CSV Files

Had a recent epiphany following a lengthy period of making myself use Objects and Classes.

Was doing a little updating to a small application and found myself wanting to add some functionality dealing with SQL databases and CSV files.  Whereas a couple of months ago my instincts would have taken me straight into "traditional" code I found myself naturally thinking that both SQL databases and CSV files were "things" and that the natural way to handle them was via classes.

What's presented here is quite basic, but illustrates how straightforward it is to start merging these concepts into existing applications.  The code needs a little care and attention to make it properly robust and will probably have evolved since this writing.  Remember also that this code is part of a learning process - there may well be smarter (more idiomatic) ways to achieve the desired effects.

SQL Databases

Having used Dyalog's supplied SQAPL code for many years it is almost second nature, the class script assumes that it is within the active workspace in a namespace called SQAPL.

Things we can do with the class...

Open a database object

db←⎕NEW #.UTIL.SQLDatabase #.GLOBALS.(database ODBCPassword ODBCUser)

Find out what tables are there

 db.TableNames

Find out about the columns in a table

  db.ColumnInfo 'aplmona'

This is a simplified version of the SQATables result.

Retrieve information from a table

 db.GetValues 'this' '*' 'id>200'

Just simplifying and avoiding SQLs verbosity.

Create a new table

db.CreateTable 'aplmonb' 'token varchar,lfn varchar'

Add new rows

count←db.AddRows newtable body

This is rather tedious because some databases (like SQLLite) are reluctant to accept mutirow input - I really need to explore my options if and when bulk addition of rows becomes important to me.

      :Class SQLDatabase
⍝ SQL database (may supplant SQL namespace eventually)
⍝:Dogon Research
⍝:File sqldatabase
⍝:Namespace #.UTIL.SQLDatabase
⍝:Depend    

    ⎕io ⎕ml←0 3

    :Field connection   

    ∇ Open w;rc
      :Access Public
      :Implements Constructor
      rc←↑#.SQAPL.SQAInit''
      connection←'C1'
      rc←#.SQAPL.SQAConnect(⊂'C1'),#.GLOBALS.(database ODBCPassword ODBCUser)
    ∇

    ∇ Close
      :Implements Destructor
      rc←SQAClose connection
    ∇

    :Property TableNames
    :Access Public
        ∇ z←get
          z←1⊃SQATables ⎕THIS.connection
          z←1↓2⌷[1]z
        ∇
    :endProperty

    ∇ z←ColumnInfo w;s
      :Access Public
      z←1⊃SQAColumns connection w
      z←1↓[0](⊂3 5 7)⌷[1]z
    ∇

    ∇ z←CreateTable(name coldefs)
      :Access Public
      z←SQADo connection('Create table ',name,' (',coldefs,')')
      z←0=↑z
    ∇

    ∇ z←DropTable w
      :Access Public
      z←SQADo connection('Drop table ',w)
      z←0=↑z
    ∇

    ∇ z←GetValues(table columns select)
      :Access Public
      columns←1↓∊',',¨columns
      z←↑2⊃SQADo connection('Select ',columns,' from ',table,' where ',select)
    ∇
 
    ∇ z←AddRows(table rows);sql;cols;row;rc
      :Access Public
      cols←ColumnInfo table
      sql←'Insert into ',table,' values (',(¯1↓∊{':',¨(0⌷[1]⍵),¨(((1⌷[1]⍵)∊'bit' 'tinyint' 'smallint' 'integer' 'bigint')/¨⊂'<I'),¨⊂':,'}cols),')'
      rc←SQAPrepare(connection,'.s1')sql
      rows←⊂[1]{(¯2↑1 1,⍴⍵)⍴⍵}rows
      :For row :In rows
          rc←SQAExec(⊂connection,'.s1'),row
      :EndFor
      rc←SQAClose connection,'.s1'
      z←↑⍴rows
    ∇

⍝:Saved ⋄ 2008 12 15 15 22 24 968 ⋄ dick ⋄ .\dbviewer
:EndClass

CSV Files

Taking a simple definition of a CSV file...
Here's what the class lets us do...

Open a CSV File

csvfile←⎕NEW #.UTIL.CSVFile csvname

Find the column names

  csvfile.Columns

Format the body as a (nested) character matrix

 ⍴csvfile.Body

This is functionality which is a candidate for refinement, analysing the column content and converting to character/numeric/date as judged relevant (left as an exercise for the reader).

:Class CSVFile
⍝ CSV file (may eventually supersede the old CSV namespace)
⍝:Dogon Research
⍝:File file\csvfile
⍝:Namespace #.UTIL.CSVFile
⍝:Depend  

    ⎕io ⎕ml←0 3

    :Field csvtie
    ∇ Open w
      ⍝ Open file
      :Implements Constructor
      :Access Public Instance
      csvtie←w ⎕NTIE 0
      ⎕DF'Dogon Research CSVFile <',w,'>'
    ∇
    
    ∇ Close
      :Implements Destructor
      ⎕NUNTIE csvtie
    ∇

    :Property Contents
         ∇ z←get
          z←⎕NREAD csvtie 80(⎕NSIZE csvtie)0
          z←((~z∊⎕UCS 10 13)⊂z)
        ∇
    :EndProperty

    :Property Columns
    :Access Public
        ∇ z←get
          z←↑⎕THIS.Contents
          z←(~z∊',')⊂z
        ∇
    :endProperty
    
    :Property Body
    :Access Public
        ∇ z←get
          z←1↓⎕THIS.Contents
          z←⊃{(~⍵∊',')⊂⍵}¨z
        ∇
    :EndProperty  

⍝:Saved ⋄ 2008 12 12 15 33 20 31 ⋄ dick ⋄ .\dbviewer
:EndClass

And Finally...

What these examples should show...

Page created 16 December 2008; Copyright © Dogon Research 2008