AltME groups: search

But i am curious about passing the true-flag back. IS there a reason 
not to do it, or just not addressed now?

Yes, /local will stay, as it is just another refinement and the local 
variables are just other parameters that aren't used. Since the parameter 
passing to a rebcode function is performed by the outer interpreter, 
getting rid of local would mean getting rid of all refinements to 
all functions. BTW, the treatment of /local as a special situation 
is just an artifact of the help function.

Volker, or a better (than the last one) assembler style:
rc-and: rebcode [fi f2 /local r l1] [
    sett f1
    braf l1
    sett f2
    label l1
    gett r
    return r
]

Pekr, it would depend on the instruction. Like in assembly code on 
real machines, some operations are faster than others. An assign 
or an add would be fast, a mul slower, and a div slower yet.

Adding an instruction in a loop will multiply the slowdown by the 
number of times the loop runs.

A nice thing to try would be forall in rebcode. We have apply now.

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.

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

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?

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

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, it looks mostly compatible to me, just the returned errors 
are a bit different.

Great! I'll give it a try with a new DLL.

Our Sqlite docs say: "2.3.3 Fkeys - This refinement returns information 
about the foreign keys (if any) that reference a table.", but sqlite 
does not support foreign-keys, only via triggers, no?

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

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

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.

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.

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

Never had a problem with this.

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

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

Petr, the new SQLite 3.5.3 release states:


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

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

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 ?

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.

NO, I want to use R2. I will be using R3 only for small projects 
and to help the comminity into debugging ! Have you already read 
what I think about people complaining R3. Your is a provocation !!! 
:-)))))

To GiuseppeC there is a simple server based on SQLite look at SQLIte 
Wiki/SQLiteNetworks/uSQLiteServer a protocol to this is also in rebol.org.


Anyway I can tell you a secret not to tell anybody: try to use rebface.exe 
from public available REBOL/SDK 2.7.5 BETA . It seems the /Pro restriction 
is not applied there for some reason and SQLite protocol works sweet 
for me there.

You can got  an idea run "local database backend" in one rebface 
process and application in another on rebol version of your choice. 

THANKS ASHLEY FOR A GOOD JOB.

Now I expect a totally free Rebol/Command :-)

(Hoping in a free upgrade to R3)

Petr, congrats to really find a bug. I think I have been hit by this 
one too.

I will make an update of the SQLite engine. I'm still using a rather 
old one.

Why not? It's ACID compliant and SQLite on a server where all file 
ops are local to the DB process seems OK to me.

From "Suggested Uses For SQLite" ( http://www.sqlite.org/features.html
)

Website Database

 Because it requires no configuration and stores information in order 
 disk files, SQLite is a popular choice as the database to back small 
 to medium-sized websites

Stand-in For An Enterprise RDBMS

 SQLite is often used as a surrogate for an enterprise RDBMS for demonstration 
 purposes or for testing. SQLite is fast and requires no setup, which 
 takes a lot of the hassle out of testing and which makes demos perky 
 and easy to launch.

Graham, that features page also says how to use SQLite to manage 
the files that store your application data. It's a cool hack.

I'm storing user configuration data on the server (firebird) in a 
text blob field ... which is one serialized rebol object.  Each time 
I change a configuation parameter, the whole object is saved back 
to the database.

I have found an interesting PHP project I would like to see ported 
on Rebol: http://adodb.sourceforge.net/. It is a layer to access 
nearly all database available in this world with plugings.

What I need is connect to a database , than in a second time, connect 
to another database, multiple connections open, no sqlite attach, 
possible?

I expect that is due to changes in SQLite - it has changed the API 
a bit between 3.4.0 and 3.5.x

Tretbase? I think it's pur Rebol based, right? No I won't. I don't 
think Rebol is the right tool for a database engine.

Robert;  I'll fight you on that.  :)  Depends on the scale.  RebDB 
is beautiful for dbs under the 10K ish limit;  TRETBASE is destined 
to be a very handy REBOL scripting database engine, and Paul has 
been hammering on some larger datasets, (not something I'd plan on, 
but it's being developed that way).  So unless you are working in 
the 100K+ record arena, or need multiple user concurrency, I do think 
REBOL is the right tool for a database engine.

I didn't expect to win the fight, but I wasn't really expecting to 
get pinned on the first move.  :)  But I'll struggle a little more. 
 In the long run I think the simplicity of the pure REBOL solutions 
(and a little careful manual (or scripted) management) will have 
fewer headaches than a larger full scale  ACID database (again in 
the small dataset arena).


My opinion may vary over time and over projects...but not today. 
  And sorry about clogging the SQLite chat.  rebols could well build 
up a fair amount of expertise with this engine given that  load/library 
is now open to all.

But, it stays a SQL database not directly optimized for Rebol usage.

So, getting a database system that has much better / native Rebol 
support makes a lot of sense. Hopefully such a system will once be 
implemented on the C level for good performance etc.

How can I do an "if not exists? on a sqlite table?

What is the recommendet way to deal with a lock and to get rid of 
dat-journal?

I do not like to stop all processes and to manually remove the file.
Also to increase the retries has its limitations.
So, how do you handle the problem?

Ok, got it.

I increased the retries and use a shorter wait with a random duration 
If there is still a lock,  I use an sqlite/*step sqlite/sid

I found something odd with an app I'm writing. If I insert or update 
a field (varchar) with a string that has any spaces in it. , it throws 
a rebol error if I try to select it. 

For example, I have a string "OK BOb" stored and when I sql {select 
* from vocab}
** Script Error: OK has no value
** Where: rejoin
** Near: OK BOb 
It doesn't matter what the first word is.

I can't believe I'm the only one so I must be doing something wrong.

thanks,  I have the mySQL driver working but I was just mewsing at 
not having to have a WAMP deployment.

I made a few tests with sqlite and concurrent processes writing and 
manual reading.

I can not recommend it, if you cannot accept data loss under these 
circumstances. Sooner or later it will get  in an inconsistent state.

problem does not occur if a periodic recycle is performed

 should read "problem occurs less frequently if a periodic recycle 
 is performed"

I experienced the problems Rebol and Sqlite related.

Rebol crashed soon trying to read after recovering from a lock, as 
the written data were no more consistent.

Hello, I need to implement a database with over 500 rows and I think 
SQLLite is the riight solution. Speed is much important as I need 
to perform about 20 queries each second. Is it loaded in memory ? 
Is there a way to load the database in memory ?

What's the size of each row? Given the size/price of memory, REBOL 
may be fast enough by itself. If you use sort, parse and create a 
little list comprehension dialect...

The trouble for REBOL starts when you want  to save the db to disk 
or load it into memory. You'll have to implement a clever algorithm 
to make it fast.

I think you should try parse on a large file with /seek, just to 
test. Or load it in memory upfront, so you hav the cost once.

RebDB is memory-based, or if your DB structure and access is simple 
enoiugh just use sorted blocks. You really only *need* a DB if you 
require a complex access API such as SQL.

SQLite can use in memory tables. If persistens is not an issue and 
you just need to query and not changes are necessary SQLite is a 
good catch. But if your queries are very simple lookup and don't 
change in structure load everything and write a simple accessor function.

Routines only work for functions, not constants or variables. Is 
there a function that returns the value of that constant?

const char *sqlite3_libversion(void);	sqlite3_libversion() function 
returns a pointer to the sqlite3_version string constant.

int sqlite3_libversion_number(void);	sqlite3_libversion_number() 
interface returns an integer equal to SQLITE_VERSION_NUMBER.

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


Mac OS X now uses the v2 API and newer dylib path. SQLite/version 
now contains version number as a tuple!

Versions 3.3.9 (first released 4-Jan-2007) onwards. Mac OS X used 
to ship with a really old version (3.0.8) dating from late 2004.

Ashley, there is still a problem with Click/Button and over.
The colour is irreversibly changing to the default in your demo

I think makeing CONNECT handling this case implicit would make a 
lot of sense to make it simpler for users. So the programmer know, 
it's possible to alway call CONNECT/CREATE. What do you think?

There is already a handler for this case but only if all databases 
are given upfront in a block.

I now just commented the line that checks if a database is already 
connected. At least it now works but I'm not sure if this has some 
undesireable side-effects.

Ok, some more findings. I think the best way is to make a copy of 
the SQLite object for each database file. Than things are independent. 
The only thing to solve is to find an elegant way to select which 
SQLite object/connection to use without having to pre-fix all calls.

Maybe something like a current database.

Could you use (say) Truecrypt to host the data files on a encrypted 
partition?

I see one problem, if the to-be-opened database doesn't exists yet 
it needs to be created. This can only be done by a call to sqlite3_open 
and not via the ATTACH sql command.

And than the returned DB handle has to be used for all actions against 
this database file. It's much like a file handler.

I see two options:

1. We enhance the driver to be able to handle more database handles 
at the same time. This needs a way to select a database handle as 
the current one.


2. We make the driver as a prototype object which carries everything 
for one database handle. Than we need a way how to state which instance 
to use.

Banging my head against a wall!

This works:

SQL reduce [{UPDATE notes SET note=?, up_date=? WHERE book=? AND 
chapter=? AND section=?} note_text now bk_tit/1 bk_tit/2 bk_tit/3]
But this does not:

SQL reduce ["Update fdbooks SET up_date=? WHERE ref_number=?" now 
ref]
nor this:

SQL reduce [{UPDATE notes SET note=?, up_date=? WHERE book=? AND 
chapter=? AND section=?} note_text now bk_tit/1 bk_tit/2 bk_tit/3] 
Any ideas?

I found part of the problem...

If I'm updating multiple records it fails to update but if update 
one record it updates.
I'm using a transaction here. do I need to place each var in ()?

Hello, I am experimenting with SQLite and I have a question:

How do I store a web page retrieved with READ onto a SQLite field 
?

how do you search for a string within a column. Is that what they 
refer to as full text search? (fts)

Let me first make sure i need fts...
I want to find a sub-string within a larger string:

for example: find the the rows that contain word "table" in a column 
called f_text one row of which might contain  "When inserting into 
an fts table, if no docid is provided,"
This would be a hit since table is contained in the string.

Thanks, I try it out in the morning. 

I googled but could not find it. I've had trouble finding a good 
source of docs for sqlite before.
w3schools looks good. 
Thanks again all!

This also brings me a question: what about SQLite and UNICODE ?

I got the 'like' command working with a text string but I can not 
fiqure out how to use variables...
something like:

rslts: sql reduce [{SELECT * FROM books WHERE ftext LIKE '%?%' } 
var]

A concatenation like 
like '%' || ? || '%'
shoud work

And how could I figure this out myself. Is this syntax a part of 
the sqlite.r interface or sqlite itself?

You need a primer in sql ...

And once you learn sql, then you can look at the dialect .... which 
is a simple substitution thing.

SQLite has a CONTAINS extension?

a ||  b 
is the sqlite syntax for concatenation of two strings.
http://www.sqlite.org/lang_expr.html

It gives different results...


>> reslts: sql [{select * from fdbooks where ftext like '%aluminum%' 
|| '%ladder%'}]

== [[9 "FFP-LADDERS" "1-PORTABLE LADDERS" "3." " CONSTRUCTION OF 
PORTABLE ALUMINUM LADDERS^/" "" 4-Dec-2008/15:29:19
] [10 "FFP-LADD...


>> reslts: sql [{select * from fdbooks where ftext like '%ladder%aluminum%' 
}]
== [[11 "FFP-LADDERS" "1-PORTABLE LADDERS" "3.1.1" {

Solid Beam Aluminum Construction- This type of ladder has a solid 
side rail co...

If you want to search for a multiword string, use LIKE '%aluminum 
ladder%', then use OR to add other clauses.
The choices above, with examples:
1: like '%aluminum%ladder%'
2: like '%aluminum ladder%'
3: like '%aluminum%' and like '%ladder%'
4: like '%aluminum%' or like '%ladder%'
The || operator means string concatenation, not or.

insert and update use "?" so you can use variables. 


reslts: sql reduce [{select * from fdbooks where ftext like '%'||?||'%'} 
srch]
== []
works when srch is a word!
but not when it contains a string!

Have you considered whether it is a casing issue?

That is a good approach anyways, as it will help prevent SQL injection 
attacks.

A bit OT: Has anybody an idea how a "schema driven" database export 
does/could work?


I have an applicaiton that uses some tables, and records are linked 
by primary index IDs. Now I want to export a record and all its dependend 
records either into a new database or over the network to some other 
process.


Because ID ranges are different in the export target database or 
on the remote server, I need to rewrite the old IDs with the new 
ones.


At the moment I have a hand written, very app specific (and error 
prone) function for this. But I would like to do this in a much more 
generic fashion. Maybe just specifcing the relationship with some 
simple dialect and than have a generic function collecting everything.

rewriting IDs? A risky business :-) I have never done anything like 
that.

How else will you do it if you transfer one set of related records 
from database A to a database B?

Why not transfer the old ID to a new  indexed field oldID?