World: r3wp

Thanks Pekr for the good advice.

thanks,  I have the mySQL driver working but I was just mewsing at 
not having to have a WAMP deployment.

Yes, far (outside of my ignorance above) it works quite well.

SteveT: read http://www.sqlite.org/lockingv3.htmlfor more information 
on locking, especially the section titled, "6.0 How To Corrupt Your 
Database Files".

Thanks Ashley

I made a few tests with sqlite and concurrent processes writing and 
manual reading.

I can not recommend it, if you cannot accept data loss under these 
circumstances. Sooner or later it will get  in an inconsistent state.

Thanks for the info

Were the problems you experienced REBOL or SQLite related? See http://www.rebol.net/cgi-bin/rambo.r?id=4063&

problem does not occur if a periodic recycle is performed

 should read "problem occurs less frequently if a periodic recycle 
 is performed"

I experienced the problems Rebol and Sqlite related.

Rebol crashed soon trying to read after recovering from a lock, as 
the written data were no more consistent.

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?