Elements: periodic_table sqlite example in User Guide

[jazper]

I have never been able to get this to work.  After tweaking the elements string to ensure there are 11 items in each line (I take out the ellipsis in the example, and shorten the list to 5 lines just for testing), it never has yet inserted the elements into sqlite3, yet it also does not report an error.



Today I removed the

Code Select Expand

(and after

Code Select Expand

(if not (find "t1" (sql3:tables))) to arrive at this:

Code Select Expand

(if (not (find "t1" (sql3:tables)))
   (create-table)
   (init-table)).

Now, for the first time ever after tens of attempts, it inserts the elements to sqlite3 just fine, but I get a repl error message:

Code Select Expand

inserted element 1 1.0079 Hydrogen H -259 -253 0.09 0.14 1776 1 13.5984
inserted element 2 4.0026 Helium He -272 -269 0 0 1895 18 24.5874
inserted element 3 6.941 Lithium Li 180 1347 0.53 0 1817 1 5.3917
inserted element 4 277 Hassium Hs 0 0 0 0 1984 8 0
inserted element 5 268 Meitnerium Mt 0 0 0 0 1982 9 0

ERR: list index out of bounds in function int
called from user defined function sql3:sql
called from user defined function init-table even though the insert went fine.



Testing the indices in the repl, they look fine to me.  Can anyone spot why I get this error message?  Here follows the entire program:

Code Select Expand

(set 'elements [text]1 1.0079 Hydrogen H -259 -253 0.09 0.14 1776 1 13.5984
2 4.0026 Helium He -272 -269 0 0 1895 18 24.5874
3 6.941 Lithium Li 180 1347 0.53 0 1817 1 5.3917
4 277 Hassium Hs 0 0 0 0 1984 8 0
5 268 Meitnerium Mt 0 0 0 0 1982 9 0
[/text])

(module "sqlite3.lsp")

(if (sql3:open "periodic_table")
   (println "database opened/created")
   (println "problem: " (sql3:error)))

(set 'column-def "number INTEGER, atomic_weight FLOAT, element TEXT, symbol TEXT, mp FLOAT, bp FLOAT, density FLOAT, earth_crust FLOAT, discovered INTEGER, egroup INTEGER, ionization FLOAT")

(define (create-table)
 (if (sql3:sql (string "create table t1 (" column-def ")"))
    (println "created table ... OK")
    (println "problem " (sql3:error))))

(define (init-table)
 (dolist (e (parse elements "n" 0))
 (set 'line (parse e))
 (if (sql3:sql
  (format "insert into t1 values(%d, %f, '%s', '%s', %f, %f, %f, %f, %d, %d, %f);"
    (int (line 0))
    (float (line 1))
    (line 2)
    (line 3)
    (float (line 4))
    (float (line 5))
    (float (line 6))
    (float (line 7))
    (int (line 8))
    (int (line 9))
    (float (line 10))))
  ; success
  (println "inserted element " e)
  ; failure
  (println (sql3:error) ":" "problem inserting " e))))

(if (not (find "t1" (sql3:tables)))
   (create-table)
   (init-table))

(define (query sql-text)
 (set 'sqlarray (sql3:sql sql-text))    ; results of query
 (if sqlarray
   (map println sqlarray)
   (println (sql3:error) " query problem ")))



[cormullion]

Coincidentally, I uploaded an old test example this morning, following the forum posts yesterday...



http://github.com/cormullion/newlisp-projects/blob/master/sqlite-periodic-table.lsp">//http://github.com/cormullion/newlisp-projects/blob/master/sqlite-periodic-table.lsp



and it works on my machine. Make sure the directory is writeable before you start - (change-directory "/tmp") for example...

[cormullion]

I think this is your problem:

Code Select Expand


5 268 Meitnerium Mt 0 0 0 0 1982 9 0
[/text])


Spot the empty line at the end? If you delete that, it works. Perhaps the parse routines need tightening up... ;)

[jazper]

Thanks, Cormullion.  Snap.  After closing down, I realised that empty line was probably it, but you got there before me!



I will try your fuller example.

[jazper]

I ran the fuller example, but it - first it reports this:

Code Select Expand

create table t1 (number INTEGER, atomic_weight FLOAT, element TEXT, symbol TEXT, mp FLOAT, bp FLOAT, density FLOAT, earth_crust FLOAT, discovered INTEGER, egroup INTEGER, ionization FLOAT);
created table  ... Ok then comes up with this:

Code Select Expand


ERR: data type and format don't match : (float (line 4))
called from user defined function sql3:sql
called from user defined function init-table
I will have to check the element rows to see if they all line up, or whatever.

[cormullion]

Since it works for me, there could be some platform-dependent issues. The init-table function was just a quick hack to fill a database. If it's causing you problems, you should debug the line and item parsing code until you find the error. Sorry that you're finding issues everywhere today.. :(

[jazper]

Well, thanks for your attentions, Cormullion - your fuller program works straight off on my Win 76 64 bit setup - I commented out the chdir to /tmp line (too impatient to go creating it), but that is the only change I made.  All element data was inserted, and all the queries worked.



The attempt made about an hour ago was on an Ubuntu (12.10) 64 bit setup.  I did not think to check permissions for /tmp, just that it is there, but I checked everything else out.  Perhaps it is, as you say, platform specific that the same code doesn't work from Win 7 to Linux.  I don't know:  I know the latter is more particular about file and folder permissions.  It occurs to me it could be line-end problems, but newLISP wouldn't install without libreadline5 (I think), and I thought that would have fixed that sort of problem.



Onward ...

[rickyboy]

I tried this on an Ubuntu 12.04 box and I got no errors.



I started with a fresh box, built newlisp 10.4.5. from sources, and cloned cormullion's repo to get the test driver.



Well, that's just one data point.  I'll get my grubby little hands on a 12.10 box in about 6 hours or so, and I'll let you know what I found with that.  At any rate, I'm still interested to hear what becomes of jazper's debugging.  Good luck, jazper!

[jazper]

I ran Cormullion's elements.lsp code under Win XP at work: it ran without a hitch.  That's Windows 2, Ubuntu 0.



Sigh.  I foresee some bloodshot eyes ahead, trying to figure out why it will not work under Ubuntu 12.10.  



I built newLISP from source for Ubuntu 12.04 64 bit around July 2012, after a prompt from Lutz on this forum - I recall having some difficulties, but eventually succeeded (I know I wrote about it somewhere here).  When I get home, I will check the newLISP version, because I have not updated to a newer version since 12.04 - what I have is still in place after upgrading Ubuntu 12.04 to 12.10 online.

[jazper]

I tested Cormullion's program on a distro named Peppermint Ice, running on an old Acer Aspire One Z150.  It ran without problems.  Peppermint Ice is an Ubuntu lightweight spin, so I guess we could change the score to Windows 2: Ubuntu 1.  



Without tracing and debugging, I am pretty sure this is a "64 bit" problem.  I can't find any bug reports on Ubuntu forums about it not working, so I guess I am going to have to finally get my head around tracing and debugging line by line.  Been shying away from this for a long time :(

[cormullion]

For all this investigative work, perhaps you'll be awarded the Sleuth of the Week Sherlock Holmes Matching Hat, Pipe, and Hypodermic Syringe kit... :)

[Lutz]

On Mac OSX this: https://raw.github.com/cormullion/newlisp-projects/master/sqlite-periodic-table.lsp">https://raw.github.com/cormullion/newli ... -table.lsp">https://raw.github.com/cormullion/newlisp-projects/master/sqlite-periodic-table.lsp runs fine on both and 32-bit and 64-bit. But there the same libsqlite3.0.dylib works for 32-bit and 64-bit. At least on OSX, I would get an error message when trying to import a library with the wrong bit'ness and I guess the same would be true on Linux. Many Linux distributions come with two sets of libraries for 32 and 64-bit in two different paths.



Also, I don't think the libreadline has anything to do with it. This library is exclusively used on the command-line (REPL) but not by newLISP's primitive read-line which uses a lower level I/O.

[jazper]

Lutz got there before me - I did suspect libreadline, so that is now ruled out, but the fact remains that I did build my Ubuntu 64 bit newLISP from source myself, and I did encounter problems along the way.  So, I thought, it's as likely as anything else that I made a botch job of it.



So, I just ran nearly all of the demos.  Ubuntu crashed on font-demo.lsp, but I couldn't get it to crash again, so I think newLISP was probably built alright:  it's almost certainly elements.lsp code that's tripping up.  Sigh.

[jazper]

Hey, Cormullion, you got a chuckle from me there.  But, if I earn any a nickname from this, it'll come from a different author, the one who wrote about Sheerluck Holmes.

[rickyboy]

OK, I tried cormullion's test on an Ubuntu 12.10 box.  Works just fine.  No errors.  Sorry, jazper, you'll have to debug.  There doesn't seem to be an intrinsic issue with the operating system/platform that is causing your issue.  So, score 2 more points for Ubuntu because I got 12.04 running yesterday too (you forgot that in your score).



Funnily, a find of the libraries on my 12.10 system is different than my 12.04.

Code Select Expand

rick@waylon:~$ find /usr/lib -name libsqlite*
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0
rick@waylon:~$ ls -l /usr/lib/x86_64-linux-gnu/libsqlite3.so.0*
lrwxrwxrwx 1 root root     19 Oct 31 23:25 /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 -> libsqlite3.so.0.8.6
-rw-r--r-- 1 root root 692984 Jun 14  2012 /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
rick@waylon:~$
(This time I had to set a symlink, a la bairui, for libsqlite3.so, since it did not exist on this box.  I chose to put it in /usr/local/lib.)  BTW, precisely speaking, my 12.10 system is a Lubuntu system.



Well, here's another data point.