World: r3wp

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 ...

even if some part of file gets corrupted, you might be able to fix 
it ... once binary file is corrupted, I bet sqlite.dll code contains 
some parser, which will simply fail :-)

but - that is the worst scenario case :-) I hope I am wrong, as sqlite 
is heavily used, so it hopefully does not happen ....

Alek - as for backups, one file per table is imo better, because 
imagine your all-tables-in-one-file containing blog, the db may easily 
exceed large size ....

I'm not arguing with you in this case, Pekr.

anyway - that's all I can do about it - to dislike it :-) I expect 
they went with one file because of locking issues in FS ....

As I mentioned near the beginning of this thread, SQLite supports 
multiple database files each containing one or more tables - in fact 
they go so far as recommending that you separate multiple high-access 
tables out into different databases for concurrency reasons. In this 
sense, SQLite "databases" act more like traditional "tablespaces". 
So, if we wanted we could write our REBOL front-end so that it created/accessed 
each table in a database of the same name thus ensuring a one-to-one 
mapping between table names and database names. The advantages of 
this approach are:

	backups (only those tables that change need be backed up)

 external table administration (you can drop a table by deleting its 
 database file)

 concurrency (you spread your file locking across a greater number 
 of physical files)

Disadvantages:


 Administering your database is more cumbersome (you can't use the 
 sqlite3 admin tool to administer all tables in one session)

 Value of sqlite_master is diminished (you can't "select * from sqlite_master" 
 to report on all your tables in one query)

 Query references need to add a database prefix when referring to 
 a table not in their own database

 Name conflicts (all tables in one file means multiple databases can 
 use the same table names - the solution with multiple files would 
 be to segregate at the directory level)

 Multiple database files means you need to zip them prior to some 
 operations such as email attachment, etc


On balance, I actually prefer the one file / one database approach.


Pekr's other comments in relation to schema implementation also have 
merit (I've agreed with Pekr twice today - a new record!); I see 
the value of an ftp schema, an http schema, etc; but what value in 
a sqlite schema? Given that the entire schema can be written in a 
much more concise fashion as an anonymous context that exports a 
couple of key access functions to the global context; I can't see 
what the functional differences between the two implementations would 
be?


So, bar any good reasons to the contrary, these are the features 
of the implementation I am currently working on (a rough design spec 
if you like):

	Implemented as an anonymous context

 "Database" is a directory (which is specified when a database is 
 opened with 'open-db)

 Each table resides in a "tablespace" (aka SQLite database file) of 
 the same name
	File is automatically opened on first reference

 The /blocked refinement of 'db-open specifies that rows will be returned 
 in their own block (default is a single block of values)

 Non-numeric values (which SQLite stores natively as INTEGER and REAL) 
 will be subject to 'mold/all on insert and 'load on retrieval

 The /native refinement of 'open-db will turn this behaviour off (see 
 comments below)

 SQLite binding will be supported allowing statements such as ["insert 
 into table values (?,?,?)" 1 [bob-:-mail-:-com] "Some text"] and ["select 
 * from table where email = ?" [bob-:-mail-:-com]]


Whether to store values (including string!) as molded values in SQLite 
is an interesting question; on the one hand it gives you transparent 
storage and access to REBOL values – but at the performance cost 
of having to mold and load every TEXT value returned; and the storage 
cost of the overhead of a molded representation. On the other hand, 
if I only want to store numbers and strings anyway then I don't want 
this overhead. I think the only practical solution is a /native type 
option as detailed above.

OK, need to leave to work, just a note - I think we can forget having 
one-table-per-file, as it is simply a hack with sqlite. Version prior 
to 3.0 even had problems with transactions in that regard and even 
3.0 has stupid compile limitation (like we have with callbacks) to 
something like 16 separate table-files, so ...

as for your new version - looking forward to it. Maybe we could vote 
a bit for what is default behavior and fo naming conventions .... 
when I saw /blocked, I first thought something about blocking copy, 
waiting behavior ....

e.g. Bobik prefers 'blocked mode as a default, as he can directly 
pass it to grid (Henrik and Cyphre's too IIRC)

for 'foreach loops, flat result is probably better. I also liked 
the ability of /names, which returned records as objects, so someone 
could do foreach rec results [print [rec/name rec/last-name rec/age]]

I would probably think of /as-blocks /flat, /as-objects, or /res-blocks, 
/res-objects, /res-flat, dunno ... open to discussion ...