World: r3wp

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

and somehow - I liked the ability of RebDB to define dbs in a simple 
way - you just named columns and did not have to care about types 
even ....

I am also used to rebol blocks. Somehow using string mode for constructing 
sql query is one level off for me, but otoh maybe better, because 
with block mode you sometimes don't cover all syntax options or you 
simply has to learn how to use it, whereas with string you simply 
compose default sql query and you can cut and paste examples or queries 
from docs, visual tools, etc.

as for visual tools - just suggest me one. I run thru installation 
of most of them. I did not find ANYTHING in a quality of mySQL Admin 
and mySQL Query. Thwo of them I liked are commercial, so imo sqlite, 
as a database of the year, is really badly supported here ....

Now at work - one more note, Ashley - my typical app design is as 
follows - application directory containing exe or modules, .cfg files, 
\system subdirectory, which contains other subdirectories as \cyphre-styles, 
\rebgui, \rebdb, \sqlite, according to usage, and \data subdirectory 
... so I am not sure that by default the driver should create the 
directory, as in the contrary when I want simply to have my data 
in \data dir, I don't want the driver to automatically create \my-db-name 
subdir ....

what is more - subdire has some meaning, if sqlite would use one 
table per file design, not sure here, I am opened to thoughts ....

I've given up on the one table per database idea as the default attachment 
limit is 10 files. On the positive side, the ATTACH command seems 
to work properly under 3.0+ and table names unique to an attached 
database do not have to be prefixed in queries. ;) My 'connect function:

>> help connect
USAGE:
    CONNECT database /create /attach databases /blocked /direct

DESCRIPTION:
     Open a SQLite database.
     CONNECT is a function value.

ARGUMENTS:
     database -- (Type: file url)

REFINEMENTS:
     /create -- Create database if non-existent

     /attach -- Attach up to 10 databases (of type file!) to this connection
         databases -- Database files (Type: block)
     /blocked -- Return each row as a block
     /direct -- Do not mold/load REBOL values

lets you do this:


 connect/attach %/c/app/data/system.db [%/c/app/data/users.db %/c/app/data/reports.db]
	sql "select * from a-system-table"
	sql "select * from a-user-table"
	sql "select * from a-report-table"


which could be quite useful in many situations. The default, however, 
is now one database.


BTW, my rewrite (even after adding a lot more functionality) is about 
twice as fast as the original protocol version.

huh - when can we expect beta? I am first one who wants to test :-)

I have no opinion on molded values issue .... maybe we could have 
something like /custom, with a dialect, or not so complicated, just 
/molded refinement, where you define which columns to mold - if that 
makes sense and would actually speed anything up?

other thing is, if we should support /object as original scheme did? 
Even  with odbc, some time ago, I simply created map-record function, 
which mapped record to object, for easier access (block position 
independent) .... dunno if you find that possibility usefull though 
....

<<SQLite "databases" act more like traditional "tablespaces">>
That's a nice flexible approach.

It may add an apparent unnecessary level of complexity for small 
databases, but the tablespace approach is intended to scale almost 
unlimitedly.

Think to when REBOL has taken over the world, and we have tables 
that exceed a single disk drive. Tablespaces exist (in part) to handle 
that sort of issue.

what is tablespace?

It's a data space that contains tables.

A tablespace can be split across disk volumes (or servers or machines)
And a table is defined as residing in one or more tablespaces.


The unit of back up is a tablespace -- or a database (which consists 
of 1 or more tablespaces)

It adds a lot of flexibility for large systems. But can be overkill 
for smaller ones....Where you probably just have one tablespace that 
lives in one folder.

OK, initial beta release can be found at: http://www.dobeash.com/files/SQLite.r

ok, please inform us here, if you do some changes/fixes :-) Thanks 
a lot ...

Ashley - maybe I vote for functions having sql- or sqlite- prefix 
.... sql then could become sql-query .... because ... standalone 
"disconnect" in rebol script is a bit strangely looking .... in rebol 
you can disconnect from many things ... it does not look obvisous 
at first sight .... what do others think?

also open, close vs connect, disconnect ... evne rebdb has db-open 
.... looks good ... maybe db- would be nice, telling and short prefix 
...

hmm, now I found oconnect refinements uncomfort ....

I would prefer setting /blocked or not, /direct or not, as a result 
fo query, so maybe those refinements should be available with /sql 
function instead?

could we mark this group [web-public]? Would anyone be offended?

Done. I've also updated SQLite.r to correct its mold/all handling 
and changed the /blocked refinement to /flat (the driver now returns 
rows as blocks by default).

Bumped the version to 0.1.2 with three enhancements:

1) Automatic NULL -> none! conversion (and vice versa)

2) 'sql now handles a SQLITE_BUSY return code by retrying the failing 
sqlite3_step 5 times at one second intervals

3) About value (number of retries) can be set with a new connect/timeout 
refinement

About -> Above

Ashley - why did you name somehow not traditionally functions as 
connect and disconnect? To not clash with open and close natives? 
:-)

Ashley - why can't we support multiple name-spaces?

can we have opened only one db at a time? And if we want to open 
other, do we have to attach, no other chance?

Would it also be possible to extend e.g. 'headings to return non-string 
 values? (but I wonder if heading can contain space, if so, that 
is bad) .... because it would allow for following contstructs:

cols: headings
== [ID Name Address]

values: [1 "Petr" "Czech Republic"]

set cols values

I also don't agree with exposing functions as you did it, without 
prefix of sqlite or db .... that aproach is flat ... and unless it 
is not forbidden by sqlite itself, I don't see a reason to limit 
ourselves to have just one openened database .... I will shortly 
try it ...

I assigned whole anonymous context to sqlite: , to have access to 
sqlite/dbid, which can be submitted to other library wrapper functions 
to try some stuff. In current state, 'connect returns true or false, 
I want handler though ...

maybe it would be better to introduce one more function - if connect? 
db1: connect %my-db.db

It seems you want cursors.

Then you can separate preparing of an sql statement and fetching 
from the cursor.

This would allow to reset an already compiled statement and to redo 
 do it without a new compilation.
Or even to cache compiled statements as Caché does it.