World: r3wp

It all depends.


 250,000 integers vs 250,000 multi-column rows with large string values
	client device with 16MB RAM vs 'server' with 4GB RAM
	'local' query vs pulling the data over a network


There are plenty of optimization strategies. One technique, if concurrency 
is not an issue for you, is to have your query return 250,000 rowids 
then page through rows based on simple 'rowid in (...)' type queries. 
It's fast and efficient, but not too great if others are modifying 
the same table(s) at the same time.

I have no concurrency issue. Yes, this might be a good solution too.

Could you use a cursor?

(That would work with many other SQLs....Not sure about MySQL's support 
for them)

Yes, but SQLite doesn't has this concept. So you can only move forward 
through a result set. But the set isn't known at this stage.

That's a pity.

What's the best way to get back the columns of a table ONCE? I just 
need the column names in left-to-right order inside the table.

How do you solve the problem of schema changes? You develop an app 
and over time you see that some columns need to be added/removed 
from tables? What's the best strategy to do this?

output to csv, import?

This mean on every update the user has to do this cycle... don't 
think this is an option.

Adding is easy - I think SQLite supports ALTER TABLE ADD COLUMN.

well, how often do you adjust your tables? ;-) then it is not good 
design imo ...

In general, the best strategy can be to rename the table, create 
a new table with the old name and the new schema, and then make an 
import query to transform and transfer the data from the old table 
to the new. Then you can drop the old table.

If you have a complex app, it's impossible to get the tables right 
the first step. What I have done so far is to only put data into 
columns that is required for SELECT statements. All other data is 
stored as a rebol block in one generic _DATA column.

I mean - if you know that you very often will change structure, maybe 
you should go with Sentenced-like design ... storing pairs of attributes 
values ...

That's what I do, see above. I just want to make my app handling 
cases where the structure changes.

so guys use the trick, that they add some 20 spare columns, which 
they can use later, if there is change needed :-) and later on, after 
some period, they adjust table and code ....

Hmm... sounds good but is more like a brute force approach.

Just to repeat it here: What's the best way to get back the columns 
of a table ONCE? I just need the column names in left-to-right order 
inside the table.

You could rig something up with the DESCRIBE function. Or you could 
specify the /info refinement to CONNECT.

Yes, but than I always get back the column names. I can set the col-info? 
to TRUE and what select statement do I than use?

One that's fast and small.

By in left-to-right order, do you mean having the names returned 
in a single row, or will having them in a single column do?

Robert - sorry, do not understand what you are asking for - any short 
code example? (have not tried column names yet, sorry)

I have a table with columns: [A B C D AA] and I just need to get 
back them in order [A B C D AA] and not something like [A AA B C 
D]

ah, meeting in few minutes, oh my, sorry guys, got to go :-(

column names? they are not returned in the same order? isn't there 
their numerical position representation? You could write small function 
to iterate via those blocks and sort it ....

I think I can use as statement "SELECT * from <my-table-name> LIMIT 
1" to avoid doing a query that returns 100.000 records just to get 
the column names.

I will look into it once I am back, I would like to see what sqlite 
returns ...

DESCRIBE does what you want.

It returns info about a table, and by default returns info about 
the columns of the table, in the order they are declared in the table, 
including column names. From there, you can extract the names quite 
easily.

Yep, thanks. I missed this one.

extract/index DESCRIBE "table" 6 2

Ashley - Bobik is getting following error (not so with original sqlite3 
protocol):

>> sql "select * from kategorie"

== [[1 30 34 M30] [2 35 39 M35] [3 40 44 M40] [4 45 49 M45] [5 50 
54 M50] [6 55 59 M55] [7 60
 64 M60] [8 65 69 M65] [9 70 74 M70] [...
>> sql "select * from kartoteka"
** Script Error: Out of range or past end
** Where: sql
** Near: either all [block? v #"[" <> first s] [rejoin v] [v]
>>

DESCRIBE returns the column ID 0 based. As I map this either to block 
positions or objects and Rebol is 1 based how about adding an option 
to either get the results 0 based or 1 based as in Rebol?

hmm, it works for him via connect/direct ... what could the problem 
be?

problem found - there is some problem with empty fields .... he probably 
saved it using other driver, it did not contain NULL, it was empty 
.... so hence the crash?

hmm, detailed problem - he had zip code defined as integer in some 
sqlite editor. Then he has rebol form, with fields ... and he saved 
empty zip code field ... so he saved "" to db, where number was required 
...

how about adding an option to either get the results 0 based or 1 
based

 ... best done in your own script as that's a fairly specific and 
 [probably] uncommon requirement. Same goes for other 'one-off' requirements 
 like "all uppercase object names", or "all lowercase object names".

Does anyone understand this error?

>> sql "select * from comm"
** Script Error: Out of range or past end
** Where: sql
** Near: either all [block? v #"[" <> first s] [rejoin v] [v]
>>
>> sql "select guid from comm"

== [[h-o-h.org_20060326_182311681_3176] [h-o-h.org_20060326_182311681_7315] 
[h-o-h.org_20060326_182311701_2470] [h-o-h.org_2006032
6...
>> length? sql "select guid from comm"
== 541
>> probe tables

["comm" {CREATE TABLE comm ( guid, type, value, note, flags, keywords, 
reference, created, updated, deleted )} ;...

Furthermore, I sometimes have the problem, that errors in sql statements 
are not caught by try, if I call it from a view gui. 
Is this a known problem?

Looks like your data is tripping the driver up. Please add the following 
line:

	print mold s


prior to the "either all [block? v ...] line and post the last result 
back here.


As for the second issue, haven't experienced this myself. Do you 
have a small code snippet that exhibits the problem?

aaahhh, once again, I forgot /direct in my testing on the console

I'll try to find a minimal code to show the non-catched errors ...

Hi Ashley, while trying to find a minimal code example ... I found 
the error ... ;-) 

That's the error message ...
** User Error: SQLite SQL logic error or missing database
** Near: make error! reform ["SQLite" error]

And it was caused by:

      if string? face/user-data [
         if error? set/any 'err try [

            set pAddress-disp first rule compose [pAddress get guid = (face/user-data) 
            *]
	; rule creates an sql string and starts calls 'sql with it
	; yadda yadda yadda ...
         ]
      ][probe disarm err]

Do you find the error??? 

Somehow the [probe disarm err] block moved to the wrong if ... 


I don't know how this could trigger _this_ error, but after I moved 
the block the error has not occurred again.

can you try not to probe? Maybe printing somehow interferes with 
dll?

I have one suggestion. Trying to use sqlite for cgi, I have following 
dir structure:

\app
app.cgi

\app\system (sqlite.r, sqlite.dll, other app related "system" files)
\app\data (*.db)


I don't like sqlite driver putting .log file into caller directory 
= main app directory. Not sure where it belongs, if in \system, \data, 
or simply \log subdir, but the driver has no ability to set the path 
...

I thought that the same parameter could be used for DBs too - setting 
the path, but it is not so important, as I can connect %path-to-db/my-db.db, 
but as for logging, it just writes to "current dir"

I did following modifications to driver:

log-path: to-file copy ""

then replace/all "%sqlite.log" "join log-path %sqlite.log"


then in my cgi script I am able to do sqlite/log-path: %db/ to change 
location ...... maybe it would be usefull to even set db path and 
don't bother with paths, not sure ....

hmm, not sure I easily follow how dbs are opened in 'connect, so 
I skip the change to set path for dbs thru some variable ....

I don't understand the line: unless find first database %/ [insert 
first database what-dir], as it just changes path to first file, 
is that ok?