World: r3wp

What are you talking about?

All updates work like that, in every database that I am familiar 
with. It's a transaction isolation thing.

I don't think it changes the row id though, and definitely doesn't 
change the primary key.

simply put, I don't want to issue a reqeust first, then decide if 
received recordset is zero lenght to insert, and if ther is some 
record, to update ....

so you trying to say, that update, if record does not exist, will 
add new one?

it does not sound appropriate, or why guys were asking mysql to add 
insert .... on duplicate key update facility, if update would be 
able to do it itself?

No, I'm saying that update deletes the original record and inserts 
a new one, on every relational database I know. No update-in-place.

It just doesn't check for cascading actions or trigger events until 
it is done.

imo our driver does not work correctly. I am totally confused by 
modes. Neither /direct nor normal mode does what I expect. I want 
rebol string to be stored as string in sqlite.

it might work with rebol, but when you go to consolo and issue select 
* from test where id='5', it is not found. Because rebol stores it 
as "5", so I have to issue '"5"'

hmm, but issuing single '5', it is returned to rebol as integer, 
even if column type is text, that sucks :-)

now I have to choose, if I work with rebol only, or console only.

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