World: r3wp

Re: RebDB. I'm coming to the conclusion that SQLite is a superset 
of all I wanted from RIF; and it's:

	1) Here today
	2)Lean & mean
	3)One small (256KB) drop in DLL
	4)Works well with REBOL
	5)Public Domain


And, like RebDB, it is pretty flexible about column values (it uses 
column affinity to suggest how values may be coerced – so an INTEGER 
column could contain [1 999 “three” “four”])  which is more REBOLish 
than most other RDBMS's; and all values (TEXT, INTEGER, REAL and 
BLOB) are stored as variable byte length.


My benchmarking of RebDB vs SQLite with three tables; Customers (100), 
Orders (10 per customer) and Items (10 per order) gave the following 
results (measured in Transactions Per Second, with the first for 
RebDB and the second for SQLite):

	select * from Customers 148		120
	select * from Orders	141		11
	select * from Items	 76		 .73
	selective join on Order-Items for one order	8.93	437.23
	join on all Order-Items			.08	4.93
	size of all dat file(s)			410KB		625KB


What you have to remember is that RebDB is 100% memory-based using 
tight loops and REBOL natives where possible, versus SQLite which 
is disk-based and being called via routines.

ok, thanks a lot, maybe repost it in rebdb group, please?

ok, as for your benchmark, also note, that inserts etc. are slow 
as hell. Because it reopens the file each time ... IIRC there is 
some trick as transaction? for that?

begin
insert ...
...
end

hmm, but scheme for sqlite is still kind of cumbersome - non ability 
to open at certain path, no error if you open non existant db - it 
mistakenly automatically creates one, then of course your query fails 
.......

but I will somehow sort it out ....

The "create if not exists" is a feature of sqlite not the protcol. 
Think command line:

	sqlite3 new.db

The pathing thing is just an RTFM issue.

I tried to briefly look into API and not found path anywere mentioned 
... I will try looking better ;-)

A short-term workaround is to do something like:

	old-dir: what-dir
	change-dir new-dir
	open-db %my-db.db
	change-dir old-dir

;)

:-))

or maybe it is better to use sqlite.exe - after all who needs scheme 
for file access here, right?

ok, finally for lunch .... :-)

I decided to revert back to sqlite3 protocol, not the one Cal Dixon 
turned into scheme. The scheme simply does not fit here ...

I would be glad, if we would work from original sqlite3.r version, 
Ashley .... it is simplified wrapper, not messing with scheme code 
... first we can make this one better, then properly wrap scheme, 
although using scheme for local files is not of much use anyway ...

oh, I found the solution for the path problem. As I expected, the 
library needs path in filesystem friendly way. Just add to-local-files 
into sqlite-open function, so it should read as: sqlite3/open to-string 
to-local-file name tmp: make struct!

but with scheme the design is broken anyway, as scheme does not allow 
some chars, which are legitimate with filesystem, as "!", so ....

I prefer Cal's version as it adds one word to the global context:

	set-tracing

as opposed to:

	sqlite-close
	sqlite-error
	sqlite-exec
	sqlite-open
	sqlite3
	SQLITE_BLOB
	SQLITE_DONE
	SQLITE_FLOAT
	SQLITE_INTEGER
	SQLITE_NULL
	SQLITE_OK
	SQLITE_ROW
	SQLITE_TEXT


and tightens up some of the code; so I'll use it as the base to optimize 
from. One of the things I want to add is automatic type conversion 
so you can store and retrieve REBOL values without having to worry 
about the fact that they may be stored as TEXT in SQLite.

re:solution for the path problem. Was that for Cal's version? Problem 
occurs earlier than that in the 'open function with this line:

	port/locals/dbid: sqlite-open to-file port/target


asport/target contains only the file name regardless of what path 
you specify!

probably needs   to-file join port/path port/target

Yep, wish I had read your message earler. ;)


The 'open func sets port/target to a string of the file name and 
port/path to a string of the path (empty if none). So you just need 
to replace the sqlite-open in the 'open func with:
 
	port/locals/dbid: sqlite-open to-file join port/path port/target

as Anton stated above.

Oops, that should be:


 port/locals/dbid: sqlite-open to-file either port/path [join port/path 
 port/target] [port/target]


as port/path contains none! when no path is provided. On that note, 
anyone know why port/path and port/target are set to string! not 
file! ?

I would add this too 

  if not integer? port/locals/dbid  ......     [make error! port/locals/dbid]
as you see the source of errors earlier.

Ashley, don't know why, but they are string! for http and ftp schemes, 
and file! for file and directory schemes.

Actually, I do know why - I just read it today. The reason is that 
url paths don't necessarily map directly to the filesystem.

They can, and often they do, but doesn't have to be.

Ashley - ok, that is your choice, but imo strange one ... what is 
the point of having scheme access to apparently file-based database?

show me, how I can point sqlite to %/C/!mp3/mp3-list.db

the thing is, rebol's url parser fails on ! char imo ....

The scheme probably does not have to use rebol's url parser.

and are you guys sure your join port/path will work? As in low-level 
you are calling a library, which will not understand rebol path anyway, 
unless converted using to-local-file ...

because - original aproach sounds much clearer - simply db: sqlite-open 
%/C/!mp3/my-mp3-list.db

instead of db: open sqlite://localhost//C/!mp3/my-mp3-list.db - looks 
terrible ...

and to have functions available globally, Ashley had to trick it 
using 'set anyway, so I actually wonder what is the advantage of 
using the scheme :-)

the only one good reason is to have unified aproach to all dbs .... 
that is a good reason, but it should not be limiting ...

I will wait for your version, Ashley ... currently there is way too 
much fixes floating here in the channel for me to not screw the whole 
thing up :-)

What I don'T like about sqlite is that 1) it uses binary storage 
and that 2) it uses all-in-one-file aproach. I know it is low level, 
but I prefer directory/one-file-per-db aproach of RebDB kind of cool 
... I can watch/backup small dbs on per file base, not on going into 
sql base, to find out what internally changed ...

but that is the detail I will have to live with probably .....

ah, now I know where my feelings for one-file-per-table, plain text, 
comes from - it is Netscape/Mozilla/Unix mail format. Each slot in 
your mail is one file, it is text ... in opposite to Outlook one 
binary file. If something screws up in your binary files (as we had 
some crashes of Outlook mailboxes), then you are ... well :-)

AFAIK SQLite is one-file-per-db - did I miss something?

I want one file per table!

ah :)

even mysql does so ...

it greatly simplifies working with db, simply by visual checking 
in filesystem, backup is easier, etc.

so, for me, sqlite is near ideal, I give it a big minus because of 
that, in my opinion, bad design decision .... well, although I can 
imagine that they need to control locking/transacitons on file level, 
so it is easier for them to work with one file only ....

One file IMO is appreciated also in web programming - especially 
with small databases instead of flat-file. But - as You wrote - can 
be problematic at some level.

(one file = easy to transfer, easy to backup, easy to update)

easy to fix.

(or easier...)

of course our povs may vary, I try to be open to other opinion, but 
my experience (of course based upon my usage patterns), varry ...

Antont - it can't be easier to fix, as it is a binary file .... I 
really like old unix mail format, which nowadays uses mozilla/netscape 
- plain text files - that is what I call easy to fix ...