World: r3wp

I didn't expect to win the fight, but I wasn't really expecting to 
get pinned on the first move.  :)  But I'll struggle a little more. 
 In the long run I think the simplicity of the pure REBOL solutions 
(and a little careful manual (or scripted) management) will have 
fewer headaches than a larger full scale  ACID database (again in 
the small dataset arena).


My opinion may vary over time and over projects...but not today. 
  And sorry about clogging the SQLite chat.  rebols could well build 
up a fair amount of expertise with this engine given that  load/library 
is now open to all.

Ok, and please don't get me wrong. SQLite is nice because it's small 
and simple to attach to Rebol, thanks to the work of Ashley. So even 
for 10K records you can use it because the costs are low.

But, it stays a SQL database not directly optimized for Rebol usage.

So, getting a database system that has much better / native Rebol 
support makes a lot of sense. Hopefully such a system will once be 
implemented on the C level for good performance etc.

Will, did you try modifying the following lines in the driver:

	*lib: load/library ... ; to call the correct version

 *prepare: make routine! ... ; to use "sqlite3_prepare_v2" instead 
 of "sqlite3_prepare"

Thank you Ashley, have changed to v2 but get:

connect/log/create d 
** User Error: SQLite out of memory
** Near: make error! reform ["SQLite" error]

Are you able to connect to an existing db file?

rebview cl-sqlite.r


Any idea why I'm getting this (using Ubuntu and Ashley's sqlite.r): 
Script Error: Library error: libsqlite3.so: cannot open shared object 
file: No such file or directory
** Near: *lib: load/library switch/default fourth system/version
>>

Do you perhaps need the sqlite library in your working folder?

This solved the problem: "sudo apt-get install libsqlite3-dev"

Chris, you are right. Thanks!

How can I do an "if not exists? on a sqlite table?

All the tables are stored in the 'sqlite_master' table (including 
the 'Create' command)

select name from sqlite_master where type = 'table' and name = ?

Thanks, Chris. I have this working now.

What is the recommendet way to deal with a lock and to get rid of 
dat-journal?

I do not like to stop all processes and to manually remove the file.
Also to increase the retries has its limitations.
So, how do you handle the problem?

Ok, got it.

I increased the retries and use a shorter wait with a random duration 
If there is still a lock,  I use an sqlite/*step sqlite/sid

I found something odd with an app I'm writing. If I insert or update 
a field (varchar) with a string that has any spaces in it. , it throws 
a rebol error if I try to select it. 

For example, I have a string "OK BOb" stored and when I sql {select 
* from vocab}
** Script Error: OK has no value
** Where: rejoin
** Near: OK BOb 
It doesn't matter what the first word is.

I can't believe I'm the only one so I must be doing something wrong.

how do you connect to DB? There are two modes - normal, and /direct 
....

try to open it with /direct refinement ...

I just use "connect  %dbname.db

OK, I'll try that.

How did you know?

Hi all, is the SQLite driver for REBOL different to the standard 
in that it has locking working? Does this mean it can be used for 
small multi-user apps or is it that people DO but SHOULDN'T like 
with Access?

Thanks

I see.../direct "Do not mold/load REBOL values"

Says: # Lock detection
The driver detects locks and initiates retries on your behalf.

@ http://www.dobeash.com/sqlite.html

Though I have not tried it in multi-user env.

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.