World: r3wp

Hi, has anyone even got to the problem, where you import data into 
database, and it is corrupted? (select fails)

I am trying to analyse few sendmail logs. Our admin sent me three 
files. The first one, has those small boxes instead of newlines, 
you know it, when you try to open linux file under windows

I read all thre using read/lines, choose info I want, append it to 
resulting block. Probing block shows no defects. I believe it is 
a REBOL low level bug with some hidden chars. It happened on me in 
the past already, in different situation ...

If I import one file at a time, clear the block, then data is OK 
in sqlite, but if I append first to one block, then insert into sql, 
data is corrupted on few random places ...

following works:

;--- import log files
import-logs: does [

   ;--- pairs of incident No and incident file [[1 filename][2 filename] 
   atd.] ...

   log-files: [[1  06-12-06-7_50-7_59][2  06-12-12-15_46-15_47][3 
    06-12-13-15_29-15_31]]
   foreach file log-files [
     log-info: copy []
     log-file: read/lines file/2  
     ;print length? log-file
     foreach string-line log-file [
       line: parse string-line " "
       if line/7 == "GET" [ 

         append log-info reduce [line/1 line/2 line/4 line/8 line/11 to-string 
         file/1]
       ] 
     ] 

    SQL "BEGIN"

    foreach [date time ip-address url content-type incident-id] log-info 
    [

       SQL reduce ["INSERT INTO logs VALUES (?, ?, ?, ?, ?, ?)" date time 
       ip-address url content-type incident-id]
    ]
    SQL "COMMIT"

   ]
]

If I put SQL section out of the foreach file log-files, simply appending 
all logs at once, data is corrupted ... it is reproducable ....

hmm, it fails too ...

>> do %gotcha!.r
Script: "Untitled" (none)
Script: "SQLite driver" (5-Nov-2006)
>> sql "select * from logs"
** Syntax Error: Invalid string -- "
** Near: (line 1) È&*
6/Dec/06"

here's small package - www.xidys.com/gotcha!.zip

converting the first file (reading and saving) did not help either 
... my suspicion is, there is some bug with driver ...

>> sql "select * from logs"
** Syntax Error: Invalid integer -- 0+*
** Near: (line 1) 0+*
6/Dec/06"

later in the night, or over the weekend I will try not to use block 
syntax, but rather compose query string. All values inserted are 
strings (I tried with native rebol datatypes too)

uf, following works. Maybe it has something with my nonunderstanding 
of differences between string/non string values and how to properly 
insert them ...


 SQL s: rejoin ["INSERT INTO logs VALUES ('" date "', '" time "', 
 '" ip-address "', '" url "', '" content-type "', '" incident-id "')"]

whereas this one does not:


SQL reduce ["INSERT INTO logs VALUES (?, ?, ?, ?, ?, ?)" date time 
ip-address url content-type incident-id]

I am confused about what driver is doing ... the difference of Direct 
mode. Hmm, maybe I do - there is a difference between the string 
mode, and block mode. With string mode, the driver does not touch 
the expression, so I am responsible for putting VALUES('06-Dec-2006') 
or VALUES('"06-Dec-2006"') - simply put - if I want in db to have 
my date value being represented as a REBOL string, I have to put 
it into parenthesis myself. So actually parenthesing it twice, as 
sqlite itself already uses '06-Dec-2006' and considers it being a 
string ...

Maybe there is a bug with block mode?

Does block mode do datatype conversions? Perhaps there is a type 
mismatch.

CONNECT %test.db

SQL "drop table t"
SQL "create table t (c)"

SQL "insert into t values ('Word')"
SQL {insert into t values ('"String"')}
SQL ["insert into t values (?)" 'Word]
SQL ["insert into t values (?)" "String"]

test1: SQL "select * from t"

DISCONNECT

CONNECT/direct %test.db

SQL "drop table t"
SQL "create table t (c)"

SQL "insert into t values ('String')"
SQL ["insert into t values (?)" "String"]

test2: SQL "select * from t"

DISCONNECT

ok, so how can I explain to  mysel data corruption? It is reproducable. 
should I set type of fields when creating tables? Or should I create 
tables in external tool?

Petr, I had the same problem. There is a RAMBO ticket and Ashley 
posted his findings about it.

I use the SQLite command line tool for batch importing.

Pekr, "... Ashley posted his findings about it ...", refer post of 
7th Nov in this group.


Note that it works fine if you use the direct refinement, but then 
you won't have access to the full range of REBOL data types. Also 
note that you can use IMPORT instead of a foreach loop, as in:

	IMPORT statement values

Success! ... of sorts. If you add a 'recycle as the first line of 
the 'sql func then all seems to work fine; but a lot slowwwwwwer 
(1 minute 48 as opposed to 1.5 seconds in Pekr's test case).


But, if you recycle every 100 statements it still works and only 
increases the runtime to 1.85 seconds. I'll do a few more tests before 
uploading a new version with this change.

1.0.2 available at: http://www.dobeash.com/download.html


Workaround to RAMBO#4063. Seems to work with Pekr's and my test cases 
after several thousand runs without error.

Pekr, the import statement for your script would look like:

	sqlite/import "insert into logs values (?,?,?,?,?,?)" log-info


and is about twice as fast as the foreach loop (and now works correctly 
under 1.0.2).

beer-client-loop: func [/local last-mem mem-jetzt ports-bak] [
    recycle/off 
    last-mem: stats 
    forever [
        if error? set/any 'error try [
            wait 0.1
        ] [
            write %autsch.txt mold disarm error 
            win-log 
            print "-----------------" 
            print disarm error
        ] 
        if 20 * 1000 * 1000 + last-mem < mem-jetzt: stats [
            recycle 
            last-mem: stats 

            /print [now/time mem-jetzt - last-mem mem-jetzt last-mem]
        ]
    ]
]

that saved my script, which uses beer and heavy blitting

maybe that methods helps in your case too.

There seems to be a bit messy situation in how integers are handled 
with SQLite, so beware. If you don't specify column types, as eg. 
in my following example:

create table logs (date, time, ipaddr, url, ctype, incident)

, then expect following situation:

1) sql "select incident from logs where incident = 4"   ; works

2) sql ["select incident from logs where incident = ?" 4]  ; works

3) sql "select incident from logs where incident = '4'"   ;  does 
not work


The strange thing is, that editing my db in SQLiteAdmin, it shows 
not column types (but imo it has to choose some "default" type internally). 
Changing according field type to Integer type, makes above case number 
3) to work too ...


So maybe it is always better to not be lazy and specify precisely 
column types? But in fact, when I specified column type as Integer, 
I did NOT expect case 3 to work ... I am going to do more tests myself 
to save myself from later headaches during specifying more complicated 
queries :-)

Be sure to read "Manifest typing" at http://www.sqlite.org/different.html

Petr, yes I have seen some effects of these as well. But didn't tracked 
them further down yet.

I read "manifest typing" and it is strange - even if you set your 
column as an integer, it allows you to enter non-integer data ...

That's cool!! One of SQLite big advantages. Why should I be forced 
to tell the type upfront?

There is just no need for this. The DB should store each cell most 
efficient.

what do you use for typical rebol strings, or even dates? text? varchar?

I never specify anything. Only for INTEGER

that has a bit of a bad effect for sqladmin grid ... if you don't 
specify field type or length, it lists one collumn wide thru all 
the screen ...

Question: I have something like a bill-of-material. And I would like 
to get such a structure back as graph. I'm just thinking of this 
isn't a generic function suitable to be coupled with a database. 
What do you think?

Robert - what do you mean as a graph? A hieararchical structure?

I have good link, although it relates to mySQL - it shows how to 
query hierarchical data - http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

yes, most likely but with the possibility to have cycles etc.

Thx for the link. As always, good references Petr. You should make 
your money with answers.google.com

and of course having more than one parent.

heh, never heard of answers.google.com ... well, maybe that is why 
I would like to work as a consultant, hopefully with IBM ... the 
bad thing is, that I might miss programming a bit :-)

as for real graphs with relational model storage - that is beyond 
my understanding .... hopefull article might give you some pointers 
...

It's worth tracking down some of Joe Celko's articles on the issue 
too.
http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

I'm thinking about updating to the newest SQLite version. And I just 
read there is a new API which should be used.


The sqlite3_prepare_v2() and sqlite3_prepare16_v2() interfaces are 
recommended for all new programs. The two older interfaces are retained 
for backwards compatibility, but their use is discouraged. In the 
"v2" interfaces, the prepared statement that is returned (the sqlite3_stmt 
object) contains a copy of the original SQL. This causes the sqlite3_step() 
interface to behave a differently in two ways:


If the database schema changes, instead of returning SQLITE_SCHEMA 
as it always used to do, sqlite3_step() will automatically recompile 
the SQL statement and try to run it again. If the schema has changed 
in a way that makes the statement no longer valid, sqlite3_step() 
will still return SQLITE_SCHEMA. But unlike the legacy behavior, 
SQLITE_SCHEMA is now a fatal error. Calling sqlite3_prepare_v2() 
again will not make the error go away. Note: use sqlite3_errmsg() 
to find the text of the parsing error that results in an SQLITE_SCHEMA 
return. 


When an error occurs, sqlite3_step() will return one of the detailed 
result-codes like SQLITE_IOERR or SQLITE_FULL or SQLITE_SCHEMA directly. 
The legacy behavior was that sqlite3_step() would only return a generic 
SQLITE_ERROR code and you would have to make a second call to sqlite3_reset() 
in order to find the underlying cause of the problem. With the "v2" 
prepare interfaces, the underlying reason for the error is returned 
directly.

Ashley, are you working on an update to SQLite.r at the moment?

How long will the old API exist?

I don't know.