World: r3wp

IIRC MySQL docs join section describes very nicely the technique 
of optimisation. And after reading it some few monts ago I agree, 
that 'join is not trivial task at all, basically due to get it optimised. 
But you are right that most of the time those two mentioned cases 
are needed. The thing which complicates the optimisation part is 
- is your column a key? primary key? is it at least indexed? etc.

anyway .... maybe a good question for RT, when finally RIF arrives, 
because if I understand you correctly, till then, RebDB will be memory 
only database (although open/seek is working already)

Graham - I noticed you used Firebird for your medical system - is 
there kind of embedded version as with sqlite?

Ashley, sql-protocol generates the code used to extract and join 
the data is grouped in 2 functions :
- make-do-select
- make-do-loop

They receive the column list, the table list (a block of table/alias 
pair), the where code to apply and the database (the port).


If you provide these parameters and change the code to invoke RebDB 
fuction to get the data, you will have a basic join implementation 
for RebDB. You can use it and extend it for RebDB, if you want, I 
will be happy if you do so.

ashley: sent latest dbms3.r via email.

ashley: in your example you are doing a query for each record returned 
in the first query, depending on the overhead of doing a query this 
might be a lot slower than a support for ioins in the dbms itself, 
even without any optimization (my code does not do any optimization, 
the programmer has to do optimization manually)

Thanks guys, I've had a good look at both implementations and I've 
got ideas from both for a future full JOIN implementation; but at 
the moment my master/detail code has come along nicely. I've now 
enhanced the db-select function to accept statements in these additional 
forms:


 select * from master joins [select * from details where &id] on id

 select * from master joins [select * from details where [all [master-id 
 = &id master-date = &date]] on [id date]


which works exactly like a normal join with the following differences:

	a) It can only join one table to another

 b) Detail columns are always joined to the right of master columns

 c) Table.column prefixes are not supported so all columns in the 
 join must be uniquely named


Apart from that you get all the benefits of db-select (can replace 
* with specific column combinations, order and group by on the final 
result set, etc) *and* it's significantly faster than even the raw 
REBOL code example I gave before (as the SQL is parsed once within 
db-select and all loop sub-selects are done in-line).

I've also implemented “lookups” with the following form:

	select * from table replaces id with name
	select * from table replaces [id-1 id-2] with [table-1 table-2]


which performs a highly optimized db-lookup for each replaced value, 
but has the following restrictions:


 a) The lookup expects lookup tables in the form [id label other-column(s)]
	b) Only single-key lookups are supported
	c) A lookup that fails will replace the column value with none!


I'm now in the process of benchmarking these changes against sqlite 
to see where the bottlenecks (if any) are. Feedback on the design 
decisions is welcome.


While I was doing this, I was once again reminded how cumbersome 
it is to construct SQL statements (not just for RebDB, same goes 
for the other SQL protocols), as the heavy use of 'compose, 'rejoin, 
etc adds noise that reduces legibility. The design goal is to provide 
alternatives to:


 sql compose/deep [select * from table where [all [col1 = (val1) col2 
 = (val2)]]]


so for a start the 'sql function should probably accept a string, 
to allow:

	sql join “select * from “ table


type constructs; but this doesn't make the first example easier. 
So how about the 'sql function accept a block containing a string 
statement followed by a number of substitution variables, as in:


 sql reduce [“select * from table where [all [col1 = &1 col2 = &2]]” 
 val1 val2]


which makes things a bit more readable (and shortens the expression 
if longer word names are used multiple times). So the two questions 
here are:

	a) Is this a good idea?

 b) If so, what substitution character (& % $ @ other) will cause 
 the least conflict with REBOL and/or SQL?

Traditional with embedded SQL, the technique is to use "host variables" 
which start with a colon:

 sql reduce “select * from table where [all [col1 = :var1 col2 = :var2 
 ]]”
And you'd magically replace :var1 with the value of var1.


Which is almost exactly the behaviour you'd expect from :var1 in 
REBOL too.

If you insist that that host variables always have a space before 
and after, that makes the whole substitution process a fairly simple 
parse operation.

here's some interesting reading on mySQL optimisations - http://dev.mysql.com/doc/refman/5.0/en/optimization.html

Ashley - why 'replace? Do you want to really replace id with looked-up 
value? I can imagine having some usage for 'id, especially in the 
case where 'id is of some meaning. I know it should not be, but e.g. 
some ppl might use, for companies db, companie's registration number, 
which is not anonymous id, and could be further used in the resultset 
...

Perhaps another keyword, 'appends, that inserts the matching label 
after the column that would otherwise have been replaced. Or, allow 
column names to be specified multiple times in the select clause 
with replacements occurring from the tail, so:

	select [id id date] from orders replaces id with name

might return:

	1 "Bob" 3-Jan-2006
	2 "Fred" 4-Jan-2006
	etc

I vote for 'appends keyword ...

Ashley - is that a techical limitation of RebDB design, or more a 
parser problem, that aggregates (master-detail) are supported only 
to second level?

Quick hack to get this one common case working [well]. Proper solution 
(full JOIN support) is a major redesign.

ok, thanks ....

well, as RIF is not coming, we are still far away from non-memory 
-only version, right?

Not quite ... see SQLite group ;)

Ashley, I have the question from Bobik - he asks, if proposed changes 
will be implemented anytime soon?

sql-protocol don't need these heavy use of 'compose, 'join, 'reduce 
 if you use the dialect. Something like this work :
   var1: "Marco" 
   insert my-db [select * from my-table where name = var1]
   my-result: copy my-db

Another example to ilustrate this :

   insert my-db [select * from my-table where name like rejoin ["*" 
   var1 "*"]]


This is only if you use the dialect to query the database. If you 
use the standard SQL string, you need to compose the query.

ashley, I'm starting to use rebdb this week for a very cool segmented 
code versioning project.

wrt simplyfing the use of "noise"  ... why not just call compose/deep 
by default within the 'execute call of the client on ALL sql calls? 
 it could be a global option and IMHO the use of parens within the 
code is quite obvious and is used in many dialects. and its less 
cumbersome than to add the reduce word in your code, a string and 
then variables out of context of the sql statement.

See my post from 12-Feb. The answer to "noise" is substitution variables 
(as it's a form of expression more familiar to long time SQLers). 
Also, I've never liked the idea of performing an operation (be it 
'compose, 'reduce, 'copy, etc) that takes away that choice from the 
coder ... someone may use statements that *never* need to be composed 
and they shouldn't have to carry that cost just because some else 
needs it.

I understand... which is why I noted, it could be an option... cause 
although  substitution variables are closer to SQL,  expression embedding 
within parenthesis is quite familiar in rebol ... 


but in any case, We can all just wrap the rebdb calls within our 
own I guess ;-)

and btw... good job on rebdb... for what it does,  I like it a lot.

.

Is it possible to parametrize the rowid? If I read the error message, 
it seems not :

>> pickno: 7
== 7
>> db-select/where * prospectDB [rowid = :pickno]
** Script Error: Cannot use subtract on get-word! value
** Where: switch
** Near: insert tail buffer copy/part skip
>> :pickno
== 7
>> db-select/where * prospectDB [rowid = 7]
== [

    7 "7-02-2006" "Nom-7" "Prénom-7" "Québec" "Cie-7" "Référence: US" 
    "Conjoint-7" "Enfant-7" "Bur: 418-845-7" "Rés: 418-845-7...
>>

Also should I select a row before to delete it or update it?  Reading 
the Quick start guide, il may be implicit from the examples.

The Parenthesis does not work too:

>> db-select/where * prospectDB [rowid = (pickno)]
** Script Error: Cannot use subtract on paren! value

>> db-select/where * prospectDB compose [rowid = (pickno)]

Thanks that works.  Can someone provide the syntax in db.r of the 
following command in sql.r.  I am looking for the function to update 
all or some 20 fields, loading only db.r in my interface.  The manual 
is sparse, so I tried it in sql.r but it does not give the equivalent 
function.  Is there an echo to toggle to have such translation? It 
would be helpfull to later to discover db.r.  The function I tried 
is :

update tbtst set [id name surname] to [2 "Leclerc2" "Normand2"] where 
[rowid = 2]

help db-update is a good start. ;) For a full list of db functions 
just enter "help db-" at the console.

is anyone here interested in a contextualised version of rebdb ? 
 I mean one with everything hidden within an object, and most functions 
fixed to support it?

it allows us to use rebdb by supplying table names within values, 
instead of having to supply them directly within code.

that is not the problem of RebDB imo ... e.g. me and my friend stopped 
using it because of missing 'join and because Ashley stated, that 
instead of reinventing the wheel (RIF based on-disk version), he 
suggests SQLite ....

the only drawback is that sql use needs ticks for table names.   
acter using it for a while I actually like this better, cause it 
clearly identifies what is a reserved word and was is an argument, 
within the statements.

other than that - I like RebDB very much and I regard it being one 
of the best rebol tools so far ....

I have a merge function.   :-)

which can do some of the joining.

but you do need to do two queries beforehand... which is not exactly 
a join.. but anyhow you do end up with a block containing interleaved 
data from two tables ... and its pretty quick.

Another newbee one: db-select can retreive the rows on multiples 
values like the query  db-select/where * noms [any [nom = 'leclerc 
prenom = 'marion]] but can I query a column's values to find all 
rows where a column contains a substring, like the query db-select/where 
* noms [find ["lec"] nomprenom] ?  This last one is not the answer. 
 What would be the good query for such a purpose?

Almost there, needs to be in the form:

	db-select/where * my-table [find string-column "string"]

I am trying to update my RebDB database with a level of indirection,
as I am using a block of 39 colunms names and 
the corresponding 39 entry-fields from ReBGui.

Shortening those blocks for the example's sake, I do try to update 
the DB with the following command:

Let's say that exColumns: [name surname dob phone] and exValues: 
["Doe" "Jane" "1990-01-01" "418-840-4040"].

Then >> db-update/where exDB (exColumns) (exValues) compose ['rowid 
= (to-id)] do not work, nor variants I know.
Proof:
>> db-create exDB exColumns == true

>> db-insert exDB ["Foe" "Joe" "1991-01-01" "514-480-8080"] == true
>> db-commit exDB == true

Then what is a working update, as those do not work with indirection 
in column & values spec and rowid?
to-id: 1

Variant1 >> db-update/where exDB exColumns exValues compose [rowid 
= to-id]
== ** User Error: Invalid or duplicate column

Variant2 >> db-update/where exDB (exColumns) (exValues) compose [rowid 
= to-id]

== ** Script Error: db-update expected columns argument of type: 
word block

Variant3 >> do [ db-update/where exDB exColumns exValues compose 
[rowid = to-id] ]
== ** User Error: Invalid or duplicate column

Variant4 >> reduce [ db-update/where exDB exColumns exValues compose 
[rowid = to-id] ]
== ** User Error: Invalid or duplicate column

Variant5 >> reduce [ db-update/where exDB (exColumns) (exValues) 
compose [rowid = to-id] ]

== ** Script Error: db-update expected columns argument of type: 
word block

The more I go, the more I am frustrated into the word/value distinction.
What is the resiliently simple answer to my show stopper?

I also tried:

>> db-update/where bottinDB (get fieldsnames) (get fvalues) compose 
['rowid = (to-id)]

** Script Error: db-update expected columns argument of type: word 
block

>> do [ db-update/where bottinDB (get fieldsnames) (get fvalues) 
compose ['rowid = (to-id)] ]

** Script Error: db-update expected columns argument of type: word 
block

>> reduce [ db-update/where exDB ( get exColumns) (get exValues) 
compose [rowid = to-id] ]

** Script Error: db-update expected columns argument of type: word 
block
I am getting short of inspiration.

And about find:
db-select/where * exDB [find name "e"]
Expectedly, any letter of the string searched retrieves it:
>> db-select/where * exDB [find name "e"]
== ["Foe" "Joe" "1991-01-01" "514-480-8080"]
>> db-select/where * exDB [find name "oe"]
== ["Foe" "Joe" "1991-01-01" "514-480-8080"]
>> db-select/where * exDB [find name "Foe"]
== ["Foe" "Joe" "1991-01-01" "514-480-8080"]

But, reverting column and search-string, is this behaviour a specification 
of RebDB, or is it simply luck?
>> db-select/where * exDB [find "e" name]
== []
>> db-select/where * exDB [find "Foe" name]
== ["Foe" "Joe" "1991-01-01" "514-480-8080"]

Following works fine:

	db-update/where names name "Jane" [rowid = 1]

and reversing the find is saying:

	find "e" name -> where name appears in "e" ... so [] is corect

 find "Foe" name -> where name appears in "Foe" ... so ["Foe" ...] 
 is correct

is this behaviour a specification of RebDB

 ... this is how the REBOL find word works. Type "help find" at the 
 REBOL console for more information.

Thanks for the 'find' explanation.  As for the update, the formula 
'db-update/where names name "Jane" [rowid = 1]' is a rather simple 
case.  I am trying to update a 39 columds database splitted into 
3 tab-panels.  So I figure that there is a way to parametrize the 
update and to obtain back, in return, the rowid  where RebDB placed 
it, as the rowid is not allways the same as the saved id, as shown 
in >> db-select id bottinDB
== [1 2 3 4 7 8 9 10]
>> db-select rowid bottinDB
== [1 2 3 4 5 6 7 8].

db-update returns an integer! indicating how many rows where updated. 
Rowid is the physical row sequence computed dynamically at query 
execution.


You can update multiple columns at once by providing a block of column 
names followed by a block of values (but number of values must match 
number of columns). If you are updating a large number of column 
values it is probably more efficient (and easier to code) to delete 
then re-insert the row.