With the recent J 7.01 betas there have been rumbles that jfiles will be retired in the not to distant future. There are many good reasons for this so I started exploring jfiles alternatives and I think that sqlite may be viable.
Here's the good news: 1) sqlite is very stable, fast and produces binary compatible databases across all the OSs jsoftware supports. You can copy a sqlite database from a mac to linux or windows machine and access it without a single tweak. sqlite is even available on android and the iphone and is embedded in major software products like Adobe Lightroom. 2) data stored in sqlite format can be easily manipulated outside of J. A huge plus for many. 3) The relaxed implied typing in sqlite, (it ignores limits on literals and binary types), is well suited to J. 4) It's small and meant to be embedded. With so much going for it I spent sometime this weekend hacking around in J/C# . The following C# test program uses the ADO.NET sqlite provider at: http://sqlite.phxsoftware.com/ Oleg's J sqlite addon: http://www.jsoftware.com/jwiki/Addons/data/sqlite and a C#/J com interface I put together see: http://bakerjd99.wordpress.com/2010/05/28/a-c-net-class-for-calling-j/ It looks like you can easily stuff and fetch J binary data from all these environments without a big fuss: using System; using System.Collections.Generic; using System.IO; using System.Text; using System.Data; using System.Data.Common; using JServerClass; using LescasseConsulting.AplServer; using System.Data.SQLite; namespace SqliteTest00 { class Program { // test sqlite database private const string testdb = @"c:\temp\test.sqlite"; // test apl workspace private const string apltestws = @"C:\pd\apl\aplwin\utf8"; private static JServer StartJServer(JServer.JScriptType st, string inputscript) { // start a new J server and load root script JServer js = new JServer(st, inputscript); js.jShowServer = true; return js; } private static AplServer StartAPLServer(string ws) { AplServer apl = new AplServer(ws); apl.Visible = 1; return apl; } static void Main(string[] args) { // clear any old database if (File.Exists(testdb)) File.Delete(testdb); // start J JServer js = StartJServer(JServer.JScriptType.OnlyProfile, ""); // load up JOD databases - fetch words js.jDo("require 'general/jod'"); js.jDo("od ;:'play utils'"); js.jDo("'rc wordtable'=: 0 10 get }.dnl''"); // split up wordtable for JServer casting interface js.jDo("jwords=: 0 {" + '"' + "1 wordtable"); js.jDo("jdefinitions=: 2 {" + '"' + "1 wordtable"); js.jDo("jclasses=: ; 1 {" + '"' + "1 wordtable"); // set JOD nouns (stored as binaries) as byte strings js.jDo("nmask=: 0 = jclasses"); js.jDo("(nmask # 0 {" + '"' + "1 wordtable) =: nmask # 2 {" + '"' + "1 wordtable"); // load J sqlite interface - uses different dll than // the one used by the C# library but so far I haven't // seen any data compatibility issues. js.jDo("load 'data/sqlite'"); // tell J where the test database is js.jSet("testdb", testdb); // create J sqlite object for test database js.jDo("]db=: testdb conew 'psqlite'"); //js.jDo("blobsize__db 100000"); //// start APL and load test workspace //AplServer apl = StartAPLServer(apltestws); // we use these three SQLite objects SQLiteConnection sqlite_conn; SQLiteCommand sqlite_cmd; SQLiteDataReader sqlite_datareader; // create new database connection sqlite_conn = new SQLiteConnection("Data Source=" + testdb + ";Version=3;New=True;Compress=True;"); // open connection sqlite_conn.Open(); // create a new SQL command sqlite_cmd = sqlite_conn.CreateCommand(); // create table sqlite_cmd.CommandText = "CREATE TABLE test (word text primary key, nameclass integer, definition text, nounvalue blob);"; sqlite_cmd.ExecuteNonQuery(); string[] jwords; js.jGet("jwords", out jwords); string[] jdefinitions; js.jGet("jdefinitions", out jdefinitions); int[] jnameclasses; js.jGet("jclasses", out jnameclasses); if ((jwords.Length != jdefinitions.Length) || (jwords.Length != jnameclasses.Length)) { // list lengths match coming out of JOD if they // do not here the interface has punked my data return; } using (SQLiteTransaction mytransaction = sqlite_conn.BeginTransaction()) { using (SQLiteCommand mycommand = new SQLiteCommand(sqlite_conn)) { SQLiteParameter myparam1 = new SQLiteParameter(); SQLiteParameter myparam2 = new SQLiteParameter(); SQLiteParameter myparam3 = new SQLiteParameter(); SQLiteParameter myparam4 = new SQLiteParameter(DbType.Binary); mycommand.CommandText = "INSERT INTO test (word, nameclass, definition, nounvalue) VALUES(?,?,?,?)"; mycommand.Parameters.Add(myparam1); mycommand.Parameters.Add(myparam2); mycommand.Parameters.Add(myparam3); mycommand.Parameters.Add(myparam4); for (int i = 0; i < jwords.Length; i++) { string wname = jwords[i]; int wclass = jnameclasses[i]; myparam1.Value = wname; myparam2.Value = wclass; // JOD stores nouns as binaries all other words are text if (0 < wclass) myparam3.Value = (string)jdefinitions[i]; else { // fetch JOD binary byte[] nounvalue; js.jGet(wname, out nounvalue); myparam4.Value = nounvalue; } mycommand.ExecuteNonQuery(); } } mytransaction.Commit(); } // close C# sqlite connection sqlite_conn.Close(); // fetch words name from sqlite table and compare with originals js.jDo("sqlwords=: strtbl__db 'select word from test'"); js.jDo("jwords -: , }. sqlwords"); // fetch all noun binaries and compare with orginals js.jDo("sbinaries=: }. strquery__db 'select word,nounvalue from test where nameclass=0'"); js.jDo("sbinaries -: (0 {" + '"' + "1 sbinaries) ,. " + '"' + ".&.> 0 {" + '"' + "1 sbinaries"); // fetch all literals and compare with orginals js.jDo("sliterals=: }. strquery__db 'select word,definition from test where nameclass>0'"); js.jDo("sliterals -: (-.nmask) # 0 2 {" + '"' +"1 wordtable"); //// apl does not automatically shutdown //apl.CloseAPL(); } } } -- John D. Baker [email protected] ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
