World: r3wp

SQLite used to only have text values - numbers were translated at 
runtime. Perhaps the REBOL driver hasn't caught up.

I don't understand what is our /direct mode good for, it does things 
as {"1234"} .... without it, rebol stores "1234" .... now if you 
want use tools like sqliteadmin, you would be mad using /direct mode 
stored values ...

I simply want rebol string "1234" to be stored as '1234' and when 
returned from query to be it once again "1234" not integer if I use 
'1234' (when using compose and sending string query)

MySQL has a non-standard command for inserting if not there for update: 
REPLACE. SQLite has more detailed conflict resolution, but includes 
REPLACE as a MySQL-compatible shortcut for INSERT OR REPLACE (the 
OR REPLACE is itself a shortcut for ON CONFLICT REPLACE). I agree 
that UPDATE should have the option of acting like INSERT OR REPLACE.

As for the datatype mismatch, I would have to look at the code of 
sqlite.r to see if it is aware that SQLite data is no longer typeless.

Here is the SQLite docs about datatypes: http://www.sqlite.org/datatype3.html

It seems to me I will work with /direct refinement. That way REBOL 
string "aaaaa" is stored in sqlite as 'aaaa', so direct queries for 
'aaaa' in console work, and back-loading (select) to rebol returns 
it as a rebol string. Without direct, 'aaaaa' would be returned as 
word ...

sql ["select * from test where id = ?" 5]

The SQLite driver transforms all necessary " and ' stuff.

Never had a problem with this.

not without direct ....

guys, have you ever got to situation, that working with rebol itself 
was more productive than with SQL? I redone my version of inventory 
checking to SQL, and thas thing imports for 10x more time, and most 
of the checks I do on rebol side anyway. Kudoz to remove-each function 
too :-)

how to do effectively following using SQL?

our subsidiary company changed IDs of some products during the inventory 
in our system ;-) I need to create table called e.g. transform-id(oldid, 
newid) .... and now I would like to somehow do:


foreach row maintable [if found? in transform table maintable/id, 
then replace maintable/id with newid) :-)

I really sometimes think of simply submitting two queries into sql, 
having results in block, doing it in rebol level, and then to build 
tables from the scratch by inserting into new tables :-)

Yep. Rebol is superb for data manipulation

Perhaps the REBOL driver hasn't caught up.

 ... SQLite datatypes have not changed for a long time, the driver 
 supports them all in direct mode.

I don't understand what is our /direct mode good for
 ... http://www.dobeash.com/SQLite/user-guide.html#section-2.1.3

OK, I think that is what Petr concluded :)

even if column type is text
 ... see http://www.sqlite.org/datatype3.html#affinity

WTF! I got burried by very strange behavior, which I would like to 
know what happened. IMO it is not related to SQLite itself, but maybe 
it is a deeper REBOL bug? Simply put I have following statement:


sql "update or ignore produkty set kod = (select novy_kod from prevodnik_devitky 
where prevodnik_devitky.kod=produkty.kod) where exists (select kod 
from 
prevodnik_devitky where prevodnik_devitky.kod=produkty.kod)"


... and I was becoming crazy, that the update did not happen. No 
indexes used (well, I am starting practically with sql, so no need 
to mess things more :-), and when I put EXACTLY the same line into 
SQLiteAdmin tool, it was performed OK.


I was really becoming mad, because it seemed to randomly work, when 
I changed/simplified the expression. Then I remembered my 2 years 
old ODBC scripts, when we imported data into SAP, from Database Advantage 
Server. I remembered there was some problem with multiline statement 
unless I used trim/lines.


I thought to myself, well, it was ODBC driver related, but why not 
to try it? So I tried to reformat my query to:


sql trim/lines "update or ignore produkty set kod = (select novy_kod 
from prevodnik_devitky where prevodnik_devitky.kod=produkty.kod) 
where exists (select kod from 
prevodnik_devitky where prevodnik_devitky.kod=produkty.kod)"


... and it started to work from my script. And I ask once again - 
What is going on here? 2 hours lost, which drove me nearly insane 
:-) I use no special editor but Notepad. The statement returned no 
error, so I thought it got performed, just incorrectly. It all seems 
to be related to one aspect - line is too long, so it wraps in Notepad 
and unless I use trim/lines, it is not performed.


Any educated gues to what is happening here? It is not SQLite related 
imo, I just did not know where to put it, as general bugs group is 
not here ....

ah, today I got another data corruption with sqlite driver ....

uh, it is update or replace which messes my data with nonsense :-(


update or replace produkty set kod = (select novy_kod from prevodnik 
where prevodnik.kod=produkty.kod)  where exists (select kod from 
prevodnik where prevodnik.kod=produkty.kod)


It is supposed to simply to lookup the "prevodnik" (translator) table, 
if there is new code (novy_kod). both tables seem to be OK, but after 
that statement kod in produkty (products) gets messed up ....

produkty.kod is unique ... if I remove "or replace" part, data seem 
to be OK, but it just no more does what I need it to do ....

I found the culprit but I doubt it is enough to track possible bug. 
Issuing following statement will make subsequent update corrupting 
data:


sql trim/lines "insert into prevodnik select kod, nazev, puvodni_kod, 
puvodni_kod, typ from produkty where substr(kod,1,1)='9' and kod 
like '%BZ'"


update or replace produkty set kod = (select novy_kod from prevodnik 
where prevodnik.kod=produkty.kod) where exists (select kod from prevodnik 
where prevodnik.kod=produkty.kod)

Petr, the new SQLite 3.5.3 release states:


Fix a long-standing bug in INSERT INTO ... SELECT ... statements 
where the SELECT is compound.

Maybe that's the problem?

gee, what are they doing? 50KB more size of dll to fix few bugs? 
They should aredy fix their docs whee they claim that other engine 
libraries start at 450KB ... they are nearly there too ...

... and they definitely should completly remove their claim that 
1 file for db is an advantage. That is the most serious obstacle 
of sqlite ... simplicity comes via ability to easily backup ... one 
file per index, table ...

Robert - tried that. Sadly it does not fix the issue. I will try 
to get to their ml and post the finding. Maybe I am doing something 
incorrectly, otoh I think that corruption should never happen, even 
if query would be nonsense :-)

Well, simplicity lies in the eyes of the beholder ... just having 
to back up a single file seems pretty easy to me ...

me too

if that size is not necessarily 100MB and you don't need to back-up 
all the files all the time ....

..then why don't you just back up the tables that you ?

..want ????

you can, by creating some code to do so, not by simply looking into 
the directory and using default OS facilities.

well, SQLite is really cool, but simply noone can defend that one 
argument to me. I also communicated that "defficiency" on their ml, 
and simply put - it is the way it is. 'Attach function is just nasty 
workaround, nothing more. But - I talk about some 5% of feature, 
which would I found usefull, I will use sqlite anyway, as simply 
put there is no simple replacement to what it provides ...

I didn't think that you needed to write any code to backup individual 
tables in SQLite but just supply the table name as a parameter to 
the .dump command.

I believe you can do this from the command line with SQLite3.

It seems I found a bug :-) http://www.sqlite.org/cvstrac/tktview?tn=2832

I ask here too, I want to adopt a database system for my rebol projects: 
which are the advantages of SQLLite over RebDB ? Why should adopt 
the first or the latter ?

I ask here too
 No need to cross post. We can all see the messages.

SQLite has one main advantage - it supports SQL like syntax - you 
can join tables. I can't imagine living without those features anymore.

RebDB is also mostly - in memory only database. It does not live 
on hd. So - SQLite has one advantage here - it supports locking over 
file-shared SQLite database.

Sorry Henrik my intentions where not to SPAM, I have later found 
the SQL lite group than the RebDB. Promise I'll write only once starting 
from now. Don't be upset :-)

That is still not full-fledget SQL server - those provide you with 
network independent access ...

Pekr, joining tables and having the data on disk are already two 
good points for SQL Lite. Obviusly I need Rebol/Pro licence before 
using it.

not necessarily.

I ahve read in the doc that Rebol/Pro is necessary. Could you explain 
?

Well - there is btn-sqlite (better than nothing) driver on rebol.org 
It works, with one bad effect - black console window appearing during 
the shell call. Just recently I put it on high priority list for 
W.7.6 to be fixed - all is needed is to set one flag for shell call 
....

I hope that it gets fixed.

Thanks pekr, I'll take a look at it. Next year, when I'll have more 
knoledge in RebGUI and Rebol2 I need to start a project which needs 
a database but it will be a single user project so SQL lite.is good 
enough.