AltME groups: search

Great !  but... you haven't told me what result contains. Is it a 
string ?

Remember that COPY actually calls the COPY function in the scheme 
handler. (probably, I haven't looked at MySQL scheme).
Therefore maybe what you want is actually:

	result: copy copy port


The theory being that the first COPY provides you with the result 
string from inside the scheme handler (but the scheme still hangs 
on to it), and the second COPY works as usual in rebol (actually 
makes a copy).

Couldn't reproduce the bug...When an empty set is returned, the driver 
returns an literal empty block! value. This shouldn't be an issue 
as long as you don't modify the returned value. Anyway to avoid such 
side effect, the driver now return a fresh new block!.

Ok, will try to reproduce that with a 5+ version

maybe it needs to get "dirty" with a few selects first... I don't 
know

With or without using a fresh block! value for empty set

Do you have a small example that doesn't work for you ?

sorry, I can't make a simple example right now. no time. :-(

Sorry, it's a password related problem that I can duplicate remotely. 
Further investigation needed.

I solved the problem by installing the 0.99 version of mysql-protocol.r. 
It was quicker than trying to get XAMPP to accept a MySQL revised 
configuration file.

What do you all use to check if a port is open? Instead of getting 
a port not open when trying to read the "inserted" SQL results. RIght 
now I'm using error? try [ a: copy db] where db is the object with 
the results. I don't know, it doesn't seem to work all that well.

Doc, will there be a new version of the mysql driver somewhere punlicly 
available? I get a can't connect on softinnov.org?

hope that helps a bit

dumb question.. is this a valid query?

select i1.id,i1.ip,i1.session from isa i1,isa i2 where i1.ip=i2.ip 
and i1.session<>i2.session order by i1.ip
about 70'000 records in isa
running this query seams to crash/block the mysql server..

not a well constructed query. unless my mental parser isn't working. 
You are asking for each and every row where it doesn't match the 
current row... now I don't think that is your true intent ...

Henrik: Can yo give me the mysqladmin command line you're using to 
create a new user ?

mysql-admin is a GUI client.

Tried creating a user using that tool under WinXP and OSX, then I 
tried connecting with mysql-protocol.r => no problem...

so it's simply a different encryption scheme for some reason

when I created it from the console, I got a properly encrypted password 
like yours

that'll take a short while, the PC that the mysql server is on has 
been taken down. :-)

another thing I experienced: The server was moved to a new location. 
For some reason, everything went FUBAR, because DNS had not been 
set up. This caused connecting to the server to take about 60 seconds 
to get through. When I looked in the list of connections, it just 
says "logging in" for that long time. I connect to the server with 
an IP address, not a host name, so what's going on?

The server may try a reverse dns request on your IP when you connect 
and ends with a timeout after 60secs.

Good news : I've finally reached a very stable version of the mysql 
driver with v5+ servers ! The stability garanted only if using a 
database created with a v5+ server, copying old 3.x files in v5 server 
works but results in a big mess and unstable behaviour. Exporting/Importing 
v3.x data in v5 server using SQL flat files works very well (except 
for user with passwords, better recreate them in v5 directly).

copying old 3.x files in v5 server works but results in a big mess 
and unstable behaviour
 <--- is that because of MySQL or mysql-protocol.r?

I mean running a v5 server with v3.x tables.

I've just run a big stress test with 40'000+ requests generating 
around 2Gb of traffic without any trouble ! No bad handshakes anymore 
if you're using v5+ tables and new passwords.

I'm testing it over an ADSL connection now against a V5 server with 
multiple users. no trouble at all.

I've just uploaded v1.06 which adds support for a few missing new 
datatypes.

BTW, an old question I continue to ask but how do you all handle 
checking whether a port is open or not?

there are times when the connection to the database won't wake up 
after hours of sleep and the application locks. this also happens 
with mysql-admin, but I wonder if it really is server or a client 
side problem.

My driver should reconnect automatically is the connection is closed 
by the server when you send a new request even after hours of sleeping. 
I need to test that with a v5 server to see if it behaves as expected.

Thank you. With the old protocol I would sometimes get  an error 
 (and exit from the program) when I inserted into a closed port. 
I will give these a shot.

Great Doc!
I didn't believe you'd ever call a version worthy of 1.0 ;-)

Postgresql: Don't know, I may work on it this summer for a customer, 
so I may release new versions. I didn't had much feedback on this 
product, maybe too few users in the REBOL community...?

we will have to wait for A3:( but I still use the old alfa version 
of Rebol/Core with this script: http://oldes.multimedia.cz/rss/projects/a-pgsql/latest/a-pgsql.r

<Jaime>
Pekr. Join is relative easy to implement.


Just do a search on each table, and then create a new block by using 
the key that joins them.

foreach key table1 [
	values-table1: select key table1
	values-table2: select key table2
	append join-table reduce [values-table1 values-table2]
]

That is more or less the pseudo algorithm
</Jaime>

Also note that many join operations can be rewritten as sub-selects, 
as in:

	sql compose/deep [
		select * from a where [
			col < (
				sql [select max [col] from b]
			)
		]
	]

or:

	sql compose/deep/only [
		select * from a where [
			find (sql [select [col] from b]) col
		]
	]

Ashley - subselect is not 'join, is it? In y our above second example 
for e.g., it will mean that for each-record in table 'a, there will 
be one subselect ... that is going to be slow, no?

JOIN differs from SUB-SELECT where you want to aggregate the columns 
of more than one table, so:

	select a.col, b.col from a, b


cannot be refactored as a sub-select. There are two reasons why I 
have not implemented JOINs in RebDB [yet]:

1) Dramatic increase in code complexity

2) You can almost always do it more efficiently in REBOL as you *know* 
the data structures and desired result set *in advance*.


About the only time this does not work well [in RebDB] is where you 
have to pull the contents of more than one table across a network 
to derive a small subset as the result set. So while this SQL would 
not suffer:

	select a.col, b.col from a, b

this might:

	select a.key, b.val from a, b where a.key = b.key

depending on the size of b.

So it looks like the problem is Pekr's expectation that the database 
have certain common functions, often implemented in other databases. 
Switching from one database to another is hard because they don't 
all support the same functions. I think RebDB, as a youthful, new, 
growing database, should not be expected to have everything yet.

It's called a "Pseudo-Relational Database" for a good reason. ;)


But in answer to Pekr's previous point; *you* can work out *how* 
to do that aggregation more efficiently than the limited AI of most 
RDBMS systems. I've worked at DB2 and Oracle shops where it was mandated 
that all JOINs be performed inline! SQL optimizers are not trivial 
to write, and the meta-data overhead (indexes, statistics, hints, 
etc) required to get them to make the "right" choices are often a 
false economy.

that is imo correct design to simply avoid data inconsistency. Imagine 
if you would put 'company-name into 'oders, and later on company 
changes it's name a bit - you simply want that info to have stored 
only once ...

ok, here's my rebol odbc code for ADS (Advantage Database Server):

insert db-port trim/lines {

   SELECT DISTINCT ko.kodfyzak, fk.nazev, fk.ulice, fk.mesto, fk.psc, 
   fk.kodzeme, zk.land1, upper(fk.kodmeny),
                   upper(e.ico),

                   ko.kodfypri1, fp.nazev, fp.ulice, fp.mesto, fp.psc, fp.kodzeme, zp.land1, 
                   upper(fp.kodmeny)
                      
   FROM zakp_exp e

   INNER JOIN Kontjkv5 k5 ON k5.zakazka = concat(e.miv, left(e.czak, 
   6)) 
   LEFT OUTER JOIN kontrakt ko ON ko.ciskontr = k5.ciskontr

   LEFT OUTER JOIN firma fk ON fk.kodfirmy = ko.kodfyzak  AND fk.aktualni 
   = 'A'

   LEFT OUTER JOIN firma fp ON fp.kodfirmy = ko.kodfypri1 AND fp.aktualni 
   = 'A'
   LEFT OUTER JOIN zem zk ON fk.kodzeme = zk.kodzeme
   LEFT OUTER JOIN zem zp ON fp.kodzeme = zp.kodzeme
   LEFT OUTER JOIN delka_psc psc ON zp.land1 = psc.land1
   ORDER BY fk.nazev, fp.nazev
}

Stick to basic JOIN syntax in the form:

	select b.name, b.address
	from a, b
	where a.id = b.id
	and ...


those INNER JOIN and LEFT OUTER JOIN statements are unfamiliar to 
me and I get a headache just looking at them. ;)

maybe it is a pity rebol can't do union and join on more per record 
base, but per item base ...

UNION cannot substitute for JOIN. UNION aggregates the rows returned 
by multiple statements while JOIN [can] aggregate the columns returned 
by referring to multiple tables. A subtle but important distinction. 
For example:

Table-A
	Col
	====
	1
	2

Table-B
	Col
	====
	A
	B

	select a.col, b.col from a, b

	1 A
	1 B
	2 A
	2 B

	select * from a union select * from b

	1
	2
	A
	B

do not understand the syntax of select from a,b ... actually - never 
understood it :-) our db allows select, the rest is crosslinked via 
joins :-)

Thanks, I'll take a look at it. (Marco's sql-protocol.r is also a 
good read).

dbms3.r isn't in the library, where can I grab a copy from?

rebol.it is a good bet.

oohh.. looks like a Cobalt Raq.

The final [untested] solution is about as efficient as you can get. 
Adding JOIN support to RebDB so it can break the query down into 
similar steps is not a simple task. In all but the most trivial of 
cases you'd be better off coding it yourself.


What might be a good idea is to add something that lets you more 
easily specify the most common JOIN operation - master/detail with 
optional LOV (List Of Values) lookup(s). Your query is a classic 
example of this construct and it accounts for a surprisingly large 
number of queries.


The function would accept two queries, a master query (the orders 
table in your case) and a details query (the order-items table) and 
an optional block of column/LOV-table pairs (that perform substitutions 
such as company-name). The skeleton would look like:


 sql-join [master-query [block!] detail-query [block!] /order /lov 
 [block!] ]  [
		buffer: copy []
		...
		buffer
	]

and would be used as such:

	sql-join/order/lov [

  select [company-id order-id date amount total] from orders where 
  [amount > 2000]
	] [
		select * from order-items where %ID%
	] [8 1 5] [company-id companies]

Would this make things a tad easier?

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)

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

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.

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.

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

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.

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?

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.

I have a merge function.   :-)

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?

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?

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.

Well.  Is there any way to obtain the rowid (physical row order number) 
of a selected entry, or deleted or updated entry?  I am trying to 
devise a way to navigate into the database, from the initial point 
of a query or of an update.  But with delete operations, the first 
column, id, is not allways in sequence.  >> db-select id bottinDB 
 == [1 2 3 4 7 8 9 10]  >> db-select rowid bottinDB == [1 2 3 4 5 
6 7 8].  And, as much as I can, I want to keep the historical order 
of entry, as it gives me information.  It may be usefull to know 
that I did read a book or meet a person before another. The rowid 
gives a hint to that.  All and all, parametrizing the update and 
obtaining the rowid are stoping me to complete my small everyday 
application.  By the way, I do not see any example of 'demo' applications 
with RebDB and RebGui.  A little help with mine could give birth 
to one.  I should say that, design wise, RebGui is a great decision, 
to favor simplicity.  You adress there some needs never covered by 
other systems: the myriads of simple applets everybody needs but 
were unable to ask.  After a couple of  weekends, I have got 400 
lines of codes and am almost done.  And I am not a programmer. So 
I figure out how efficient it is for you guys. Thanks.

If records aren't inserted often you can use a date/time for the 
field - otherwise use a number that you would increment. Keep in 
mind that if you do a lot of insertions and deletions, a number field 
could end up overflowing unless you occasionally compact the numbers.

A guess is that the path  %test/param.par  is not being resolved 
correctly when encapped. Try adding.
	probe what-dir
and see if the results are different encapped and normally.

Sounds like a missing /only refinement on an insert. I'll look at 
this later tonight ... odd how no-one else has hit this in over 2 
years ... maybe not many folks store blocks! ;)

Think I've nailed the 3 spots /only is required:

	http://www.dobeash.com/RebDB/RebDB-203.zip


Note that this pre-release includes a few other untested changes; 
including /joins and /replaces refinements to db-select (refer discussion 
from 12-Feb-2006 onwards for explanation).

Ashley;  Can we get rid of the %rebgui.log  and RebDB %.log database 
replay conflict?  I've taken to just deleting the %rebgui.log on 
app startup, which is probably not a good habit.  Preferred would 
be a rename of the database replay logs to something less 'conflicting', 
say .ldb or some such.  .LOG is too easy to trip over during app 
(especially widget) development.  But I'll accept a nice short No, 
too.  :)

Thank you sir.  That's a way round.  :)

Hello, I have a question: which are the advantages of SQLLite over 
RebDB ? I have to chose something to lear for my projects.

One or more of your values is a string. Check the contents of my-table.dat

I think it's a little inconvenient that I can't order by a column 
that I'm not selecting. For example "db-select/order [name] mydb 
lasttime" fails :( Is it possible to work around this, so I don't 
have to select both name and lasttime, because it clutters my code?

That one got me too.  You don't use /where if you are using a key 
value   db-update database field "Value" 3   will hit the first record 
with key 3.   "key "in this sense means first field in the order 
of the column names.  A block without /where would be implied [value-to-compare-to-first-field 
value-to-compare-to-second-field-value...]  It can be a little weird 
when there are duplicates and you don't use secondary keys to filter. 
There is more to it or course.  But I always use /where syntax.  
:)

The "logic" is that db-update (and db-delete) are logical companions 
to both db-lookup and db-select ... so they have to operate comfortably 
in either mode (key or predicate). It is quite possible to have a 
db that *only* operates in "key" mode and *never* uses predicates! 
;)

I've only used MySQL a little, and quite a while ago, but I _think_ 
update (without where) affected the entire table there. Could be 
wrong though :)


Anyhow, I really love RebDB (And RebGUI also). It's really easy to 
use, and my program would probably be alot messier if I wrote my 
own data management functions :P

You are correct, with a standard SQL DB a delete or update statement 
without a where clause will affect every row in the table. With RebDB 
you can achieve the same thing by providing a where clause as follows:

	where [rowid > 0]


and for delete operations don't overlook the truncate (or drop) functions.

Works great.. Thanks!


You know, a really cool feature for RebDB would be to use tables 
stored on an ftp instead of locally. I think it would be possible 
since it's RAM based and you control your commits, but I could be 
wrong :) Now I'll have to sync my database manually every time I 
change computer

RebDB does have a built in client / server mode if that will help. 
  db-client.r  can talk with SQL.r from just about anywhere.  Or 
try  set path  with an ftp url, it may just work, never tried but 
looking at the code and the use of base-dir it seems like a reasonable 
chance of success.

Is it possible to have some tables locally and some on the ftp? Maybe 
I can make a copy of "db" and use it in parallell?

Table location is a global setting, so no. You can run two copies 
of %db.r (with different base-dir settings), but they won't share 
the same memory space.

That will be good enough for me, since I just want to synchronize 
each row in a table every now and then.

This ftp based storage is kind of neat.  I have to update my users' 
tables, and need to provide a script that updates their firebird 
tables.  I can embed the data in the update script, but this sounds 
a much better idea.

Ashley are you planning to release 2.0.3 as a production version?

Sorry about the formatting..as you might guess I'm running AltME 
on a Mac.

Same here ... and yes that behaviour is intentional. 2.0.3 is stable, 
it just requires some doc updates. I'll see about releasing it in 
a day or two.