World: r3wp

Hello, I need to implement a database with over 500 rows and I think 
SQLLite is the riight solution. Speed is much important as I need 
to perform about 20 queries each second. Is it loaded in memory ? 
Is there a way to load the database in memory ?

(*over 500K rows)

What's the size of each row? Given the size/price of memory, REBOL 
may be fast enough by itself. If you use sort, parse and create a 
little list comprehension dialect...

500k rows, every 2 bytes per row makes your DB grow by 1 MB. Now 
if your average size is 2KB/row, you'll use 1Gb of memory (this REBOL 
independent). Doable

It should be 0,5 KB per row.

So the database will be around 250MB. Is SQL Lite disk based or could 
we instruct it to load the database in memory ?

IIRC it allows also in-memory tables, but not 100% sure ...

The trouble for REBOL starts when you want  to save the db to disk 
or load it into memory. You'll have to implement a clever algorithm 
to make it fast.

I think you should try parse on a large file with /seek, just to 
test. Or load it in memory upfront, so you hav the cost once.

I mean, 250Mb is not so much. Only 1000 times the rebol executable

RebDB is memory-based, or if your DB structure and access is simple 
enoiugh just use sorted blocks. You really only *need* a DB if you 
require a complex access API such as SQL.

SQLite can use in memory tables. If persistens is not an issue and 
you just need to query and not changes are necessary SQLite is a 
good catch. But if your queries are very simple lookup and don't 
change in structure load everything and write a simple accessor function.

How would I add the following routine to sqlite.r:

	SQLITE_EXTERN const char sqlite3_version[];

I've tried:


 *version: make routine! [return: [string!]] *lib "sqlite3_version"

but that doesn't seem to work.

Routines only work for functions, not constants or variables. Is 
there a function that returns the value of that constant?

const char *sqlite3_libversion(void);	sqlite3_libversion() function 
returns a pointer to the sqlite3_version string constant.

int sqlite3_libversion_number(void);	sqlite3_libversion_number() 
interface returns an integer equal to SQLITE_VERSION_NUMBER.

What do you get back? This should work.

Does the version number work?

call, I mean.

Got it, typo on my part.

1.0.5 available at: http://www.dobeash.com/download.html


Mac OS X now uses the v2 API and newer dylib path. SQLite/version 
now contains version number as a tuple!

What versions of SQLite does the new driver support?

Versions 3.3.9 (first released 4-Jan-2007) onwards. Mac OS X used 
to ship with a really old version (3.0.8) dating from late 2004.

I'm currently using 3.6.3 and will update to 3.6.4 the newest release.

Ashley, there is still a problem with Click/Button and over.
The colour is irreversibly changing to the default in your demo

sorry, wrong group.

1.0.6 available at: http://www.dobeash.com/download.html


Fixes finalize error as documented at: http://www.mtcnet.net/~henryvm/sqlite/

Ashley, I just remembered that you can't call CONNECT/CREATE several 
times in one application. It gives the error "Database already connected" 
even if you use different file names.


To open more than one database file you have to use the sql ATTACH 
command starting from the 2nd database file.

I think makeing CONNECT handling this case implicit would make a 
lot of sense to make it simpler for users. So the programmer know, 
it's possible to alway call CONNECT/CREATE. What do you think?

BTW: I have this problem on Linux at the moment. The code runs with 
several calls on Windows.

Forget the last one. Doesn't work.

There is already a handler for this case but only if all databases 
are given upfront in a block.

And those files exist.

I now just commented the line that checks if a database is already 
connected. At least it now works but I'm not sure if this has some 
undesireable side-effects.

Ok, some more findings. I think the best way is to make a copy of 
the SQLite object for each database file. Than things are independent. 
The only thing to solve is to find an elegant way to select which 
SQLite object/connection to use without having to pre-fix all calls.

Any ideas for this?

Maybe something like a current database.

I'll look into it ... I like the idea of making this implicit.

If I'm updating multiple fields is this the syntax?

SQL reduce ["update books SET bk=?, chap=?, section=?, up_date=? 
WHERE id=?" book chapter section update id]


I do not get an error but it does not seem to be updating all the 
fields.

Ashley, ok. Let's do it together, I can spend some time to discuss 
things and code some variants.

Gyus, how to encrypt data in SQLite? I can't do it at app level (field 
storage level), as then SELECT would not work. Is the only solution 
to buy some SQLite variant, which encrypts at low level?

Yes.

I did buy the extension. Works very well.

Any link?

Could you use (say) Truecrypt to host the data files on a encrypted 
partition?

well, but at some point, you open-up that partition in order to be 
able to access it. The security is not there anymore. What I would 
like to have is direct SQLite low-level encryption, so that file 
might be visible to FS, but still encrypted. And your app provides 
password or something like that ... IIRC BrianH is using some such 
solution, I just don't remember its name.

I am not hesitating to invest some money into it ...

Look on the SQLite homepage under professional support.

IIRC encryption costs $2000

uff, so much? I expected something sub 500 ;-)

Robert, I was thinking we can depreciate the /create refinement by 
making that implict as well ... and the change required to support 
additional CONNECTs after the first should be as simple as changing 
the line that reads:

	all [dbid sql-error "Already connected"]

to something like:

	if all [dbid file? database]  [
		unless find file %/ [insert file what-dir]
		sql rejoin ["attach '" ...
		return
	]


which then raises the interesting question as to whether we should 
force database to be file! (so you'd have to attach multiple databases 
by issuing multiple CONNECTs ... it would certainly simply the CONNECT 
logic! ;)