[Jprogramming] sqlite J/C# jfiles replacement experiments
bill lam
bbill.lam at gmail.com
Mon Nov 29 10:27:16 HKT 2010
AFAIK missing non-gui packages can be relocated in addons. It also depends
on beta testers' feedback to decide which of these packages worth the
effort.
Вск, 28 Ноя 2010, Henry Rich писал(а):
> What?? What good reason is there to retire something simple that works?
> I use jfiles all over.
>
> Is there a doc somewhere that discusses migration from J6 to J7? I rely
> on wd, forms, grid, jfiles, sockets, winAPI.
>
> Henry Rich
>
> On 11/28/2010 7:40 PM, John Baker wrote:
> > 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();
> > }
> >
> > }
> >
> > }
> >
> >
> ----------------------------------------------------------------------
> For information about J forums see http://www.jsoftware.com/forums.htm
--
regards,
====================================================
GPG key 1024D/4434BAB3 2008-08-24
gpg --keyserver subkeys.pgp.net --recv-keys 4434BAB3
More information about the Programming
mailing list