Using CLSQL in LispWorks 8 (x64)

A few modifications are needed to use CLSQL in LispWorks 8.0. The core changes needed are in ...\quicklisp\dists\quicklisp\software\clsql-20210228-git\uffi\clsql-uffi.lisp. I changed the returning clauses in each of the following (and the function names for #-windows conditions):

#-windows

(uffi:def-function ("_strtoui64" c-strtoull)

    ((str (* :unsigned-char))

     (endptr (* :unsigned-char))

     (radix :int))

  :returning :uint64)

#-windows

(uffi:def-function ("_strtoi64" c-strtoll)

    ((str (* :unsigned-char))

     (endptr (* :unsigned-char))

     (radix :int))

  :returning :int64)

#+windows

(uffi:def-function ("_strtoui64" c-strtoull)

    ((str (* :unsigned-char))

     (endptr (* :unsigned-char))

     (radix :int))

  :returning :uint64)

#+windows

(uffi:def-function ("_strtoi64" c-strtoll)

    ((str (* :unsigned-char))

     (endptr (* :unsigned-char))

     (radix :int))

  :returning :int64)

LispWorks didn't know what to do with the :unsigned-long-long.

Two other things to try to make sure you are ready to move forward with your databases. If you are using SQLite3 or MySQL, you will need to locate the required dlls (or download them). Use 

(push (pathname "C:/path/to/x64") 

      clsql::*FOREIGN-LIBRARY-SEARCH-PATHS*)

to allow finding the paths to the dlls. Do this for each of the required dlls.

Each type of database can be loaded according to Database Back-ends. For example, SQLite3:

    (asdf:operate 'asdf:load-op 'clsql-sqlite3)

For an asd file that needs SQLite3, this is what I'm currently using after a call to (ql:quickload :clsql) before defsystem:

(let ((sqlite-path (truename "~/common-lisp/sql/x64"))) (unless (member sqlite-path clsql::*FOREIGN-LIBRARY-SEARCH-PATHS*) (push sqlite-path clsql::*FOREIGN-LIBRARY-SEARCH-PATHS*))) (asdf:operate 'asdf:load-op 'clsql-sqlite3)

After attempting to use the create-view-from-class function and finding that it created tables without foreign keys, I decided to use a simple SQL create script. It appears that execute-command only executes the first SQL statement, so it is necessary to split on the semi-colon (;) and then run each statement in sequence. (I am skipping the last "statement" in my results because my script has some gobble-dee-gook at the end.)

(defun create-project-database (db) (let* ((create-script (slurp (truename "~/common-lisp/project/data/create script.sql"))) (scripts (mapcar #'(lambda (u) (concatenate 'string u ";")) (split-any create-script #\;)))) (setf scripts (subseq scripts 0 (1- (length scripts)))) (loop for s in scripts do (execute-command s :database db)) db))

SQLite Bulk Insert

I was trying to insert a lot of records into an SQLite database and it was taking a long time. Lots amounted to over 137000 in one table. It was taking hours to complete. I decided it must be possible to do the insertion faster with some dynamic SQL. The approach was simple and probably lends itself to some basic abstraction, but I didn't go that far. I could probably use reflection to remove some of the icky repetitive code in it, but this was ok for my immediate needs.

First up, a basic abstract class to inherit. I include some basic convenience functions in it although it might not be great style.

public abstract class bulksert { public abstract string ValueTuple(); public abstract string TablePrefix(); public static string QuotedString(string s) { return "'" + s.Replace("'", "''") + "'"; } public static string NullOrQuoted(string s) { if (s == null) return "null"; else return QuotedString(s); } public static string NullOrNumber(int? i) { if (i.HasValue) return i.Value.ToString(); else return "null"; } public static string NullOrDate(DateTime? dt) { if (dt.HasValue) return QuotedString(dt.Value.ToString("yyyy-mm-dd HH:MM")); else return "null"; } public static string NullOrBit(bool? bit) { if (bit.HasValue) { if (bit.Value) return "1"; else return "0"; } else return "null"; } }

Next up, inherit the class in the table definitions you will use.

public class Versification : bulksert { public Versification() { } public Versification(GreekMorphNTRow grow) { BookIndex = grow.Book; Chapter = grow.Chapter; Verse = grow.Verse; WordIndex = grow.WordIndex; Text = grow.Text; Word = grow.Word; NormalizedWord = grow.Lemma; if (grow.AnalyticId.HasValue) AnalyticId = grow.AnalyticId.Value; } [PrimaryKey, AutoIncrement] public int Id { get; set; } public int BookIndex { get; set; } public int Chapter { get; set; } public int Verse { get; set; } public int WordIndex { get; set; } [MaxLength(50)] public string Text { get; set; } [MaxLength(50)] public string Word { get; set; } [MaxLength(50)] public string NormalizedWord { get; set; } [ForeignKey(typeof(AnalyticLookup))] public int AnalyticId { get; set; } public override string TablePrefix() { return "insert into Versification (BookIndex, Chapter, Verse, WordIndex, Text, Word, NormalizedWord, AnalyticId) values "; } public override string ValueTuple() { StringBuilder sb = new StringBuilder(); sb.Append("("); sb.Append(NullOrNumber(BookIndex)); sb.Append(","); sb.Append(NullOrNumber(Chapter)); sb.Append(","); sb.Append(NullOrNumber(Verse)); sb.Append(","); sb.Append(NullOrNumber(WordIndex)); sb.Append(","); sb.Append(NullOrQuoted(Text)); sb.Append(","); sb.Append(NullOrQuoted(Word)); sb.Append(","); sb.Append(NullOrQuoted(NormalizedWord)); sb.Append(","); sb.Append(NullOrNumber(AnalyticId)); sb.Append(")"); return sb.ToString(); } }  

Here are the two main reuseable routines that save a lot of time doing the inserts. They assume all the records are really of the same type, which is the most common application for a bulk insert. I don't know of any reason to make the inserts accommodate multiple types, but it would be possible to apply a partition at the start if you have an application for that. 

  public Task<int> Bulksert(IEnumerable<bulksert> bulks) { StringBuilder sb = new StringBuilder(); string prefix = bulks.First().TablePrefix(); sb.AppendLine(prefix); sb.Append(bulks.First().ValueTuple()); foreach(var b in bulks.Skip(1)) { sb.AppendLine(","); sb.Append(b.ValueTuple()); } sb.AppendLine(";"); return database.ExecuteAsync(sb.ToString()); } public Task<int> Bulksert(IEnumerable<bulksert> bulks, int clusterSize) { var cluster = bulks.Take(clusterSize); var remaining = bulks.Skip(clusterSize); var InitialTask = Bulksert(cluster); if (remaining.FirstOrDefault() == null) return InitialTask; else return InitialTask.ContinueWith(ti => Bulksert(remaining, clusterSize)).Unwrap(); }

Note that the syntax for these inserts is very specific to SQLite.