World: r3wp

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

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?