pure newLISP database

[Lutz]

http://unbalanced-parentheses.nfshost.com/index.cgi

A great specification for a newLISP database API. It could be implemented transparently with several back-ends: a newLISP native, SQLite, MySQL etc. Code written to this API would be portable. Something like:

(nldb:back-end "native")
or:
(nldb:back-end "SQLite")

... etc., would be used to select the appropriate database implementation. Because your API is relational, it would be easy to translate it to SQL in the back-end module.

"native" would be the first implementation, as it exactly defines how the API should behave. It would be complete in-memory with some nldb:save and nldb:load command to store and restore it to and from disk. It would be fast even with ten's of thousands of records.

[cormullion]

Thanks! :)

You can find the code here:

http://unbalanced-parentheses.nfshost.c ... ds/nldb.nl

and there's the simple periodic table database here:

http://unbalanced-parentheses.nfshost.c ... ments.nldb

I haven't finished the newlispdoc yet... :)

As for speed - well, preliminary results show that it's not as quick as an sqlite version (which is compiled C, after all), but not too bad considering the inefficient coding... :)

[newdep]

Aaa nice one !

Regarding speed...it should be a fast as newlisp itself is, it nothing
more then a big list afterall ;-)

I remember seeing another db in this forum from last year..
cant find it but that was a nice one too...

[cormullion]

I remember seeing another db in this forum from last year..
cant find it but that was a nice one too...

Yes, it was my starting point http://www.alh.net/newlisp/phpbb/viewtopic.php?p=10216. I was hoping that he would develop it like he said he intended, but then he went off to Common Lisp-land or somewhere...

I ended up simplifying his model a bit, too. I like simplifying stuff... :)

[unixtechie]

1. This is an exciting topic. The fact is THERE IS A SIMPLE DB TECHNIQUE/architecture that is roughly an order of magnitude faster than an SQL database in its typical applications (e.g. as a backend to a web application).

We can do a different and efficient newlisp-native DB if we code operations on columns only, rather than on tables of rows of data as it is done in SQL.

In fact, there exists currently a very interesting research project, a smallish database (think of MySQL before it achieved its corporate status), which implements column-based operations internally, but presents the user with SQL as a query language.
This database is open-source and available for download. My test run of TPC-H confirmed that this DB is roughly 10 times faster than MySQL, and it also eats up less memory: one won't be able to run the 1GB-scaled version of the benchmark on a machine with 1 GB of memory for MySQL, but it would run easily for the column-oriented DB.

It's easy to understand and prototype column operations even with shell utilities (and it's possible to do them in NewLisp, of course).

But this is only a first part of the story


2. There exists a simpler, more logical language to describe operations on data than SQL. Advocated by a university professor from the Low Contries, this approach seems to be dying now, after the demise of its author.
The language today lives only in an open-source tool that allows one to create databases and operations on them in this simpler language - and then translates them into SQL (for any of a wide number of subformats, e.g. Postgress, MySQL, Oracle, and even slqite).

The fact is the database product that the author of this concept created was also column-oriented, and therefore this simpler language is perfectly matched for a column-oriented approach.


So, this is a very interesting task, and I'd do this native scripted DB for NewLisp as a number of column-oriented operations, which might be presented to a user either or both as raw column operations, or as a set of SQL statements.

[cormullion]

Interesting - but I'm not sure I know how to proceed down that path at the moment. More research required.

At the moment, unfortunately, performance is less than sqlite on the same dataset - although not too much less, considering... There are some less than ideal things going on behind the scenes..

Lutz - how are association lists stored in memory? For example, is storing a hundred of these:

Code: Select all

 ((No 1) 
  (AtomicWeight 1.0079) 
  (Name "Hydrogen") 
  (Symbol "H") 
  (MP -259) 
  (BP -253) 
  (Density 0.09) 
  (EarthCrust 0.14) 
  (DiscoveryYear 1776) 
  (Group 1) 
  (IonizationEnergy 13.5984))

a lot worse than storing a hundred of these:

Code: Select all

(1 1.0079 "Hydrogen" "H" -259 -253 0.09 0.14 1776 1 13.5984)

or is it not as inefficient as one would expect? It's all those repetitions of the keys that I suspect would be inefficient... With a few thousand rows, that's a lot of identical keys! The design at the moment assumes that this is something to be avoided...

[didi]

After trying different other ways i see that nldb is the best solution for my application - hope i can show it to you soon .

Problem :

Code: Select all

(define (save-db filename)
;; Save the database in the named file.
 (let ((save-list tables))
    (push 'tables save-list)
    (apply save (cons filename save-list)) 
    (println "saved the database in " filename)))

This seems not to work, there is always only an empty list saved :
(set 'nldb:tables '()) - even if i see the table with the (show) .
Don't think this is win-xp depending.

[cormullion]

Puzzling. It looks OK here. Try this, to see what's different on your system:

Code: Select all

newLISP v.10.0.0 on OSX IPv4 UTF-8, execute 'newlisp -h' for more info.

> (load "nldb.nl")
MAIN
> (context nldb)
nldb
nldb> (create-table 'test1 '(one two three))
((one two three))
nldb> (add-row 'test1 '("a" "b" "c"))
((one two three) ("a" "b" "c"))
nldb> (create-table 'test2 '(one two three))
((one two three))
nldb> (add-row 'test2 '("d" "e" "f"))
((one two three) ("d" "e" "f"))
nldb> tables
(test1 test2)
nldb> (save-db "test.nldb")
saved the database in test.nldb
"test.nldb"
nldb> (! "cat test.nldb")

(set 'nldb:tables '(nldb:test1 nldb:test2))

(set 'nldb:test1 '(
  (nldb:one nldb:two nldb:three) 
  ("a" "b" "c")))

(set 'nldb:test2 '(
  (nldb:one nldb:two nldb:three) 
  ("d" "e" "f")))

0
nldb> 

[didi]

Thanks cormullion - this seems to work :

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\ShortNotizer>newlisp
newLISP v.10.0.0 on Win32 IPv4, execute 'newlisp -h' for more info.

> ( load "nldb.lsp" )
(lambda () (println (dup "_" 60) "\n" "Contents of database " (context) "\n" " "

(length nldb:tables) " table"
(if (> (length nldb:tables) 1)
"s" "") ": " nldb:tables "\n")
(dolist (nldb:table nldb:tables)
(println " Table: " nldb:table)
(println " Columns " (first (eval nldb:table)))
(println " Rows: " (length (rest (eval nldb:table))))
(dolist (nldb:row (rest (eval nldb:table)))
(println nldb:row))
(println))
(println (dup "_" 60))
(sym (date)))
>
> ( context nldb )
nldb
nldb>
nldb> ( create-table 'test1 '( one two three ))
((one two three))
nldb> ( add-row 'test1 '("a" "b" "c" ))
((one two three) ("a" "b" "c"))
nldb> ( create-table 'test2 '( one two three ))
((one two three))
nldb> ( add-row 'test2 '("d" "e" "f" ))
((one two three) ("d" "e" "f"))
nldb> tables
(test1 test2)
nldb> ( save-db "test.nldb" )
saved the database in test.nldb
"test.nldb"
nldb> ( ! "type test.nldb" )
(set 'nldb:tables '(nldb:test1 nldb:test2))

(set 'nldb:test1 '(
(nldb:one nldb:two nldb:three)
("a" "b" "c")))

(set 'nldb:test2 '(
(nldb:one nldb:two nldb:three)
("d" "e" "f")))

0
nldb>

ok - so it should work , now i try this in my progam ..

[didi]

OK - it's clear , i mixed MAIN and nldb - context . Now It works !

[cormullion]

I do that too.... :) I might be doing things the wrong way, but I find I can either work in the context all the time and drop the prefixes or work in other contexts and use the prefixes everywhere.

I think I've added a (context MAIN) at the end since uploading it, which I think is 'the correct thing' to do.