World: r3wp

I am an new/old user so I am still on free versions of rebol.. I 
gues now is time to buy the SDK

I think you don't need the license

just download it and try it

http://www.rebol.net/builds/#section-1

At least what I was trying, there was the license message on boot, 
but I could use the /library

hu, I never saw that download page :)

wow.. first rebol download > 1MB :)

aha.. I am getting somewhere .. it alows it now but I get some error 
with .so .. maybe this is the reason cheyenne can't open it either 

>> do %sqlite.r
Script: "SQLite driver" (26-Nov-2008)
REBOL - Security Check:

Script requests permission to open a port for read/write on: libsqlite3.so
Yes, allow all, no, or quit? (Y/A/N/Q) Y
** Access Error: Cannot open sqlite3_prepare_v2

** Near: *prepare: make routine! [db [integer!] dbq [string!] len 
[integer!] stmt [struct! [[integer!]]] dummy [struct! [[integer..

you have to start it with the -s switch and good is to use the -q 
switch as well.

http://www.rebol.com/docs/words/wusage.html

-s probably means without asking for permissions... I tried now rebpro 
-s -q but I got the same .so error .. maybe I didn't prepare the 
.so files well ..I am just looking into docs

>> do %sqlite.r
** Access Error: Cannot open sqlite3_prepare_v2

** Near: *prepare: make routine! [db [integer!] dbq [string!] len 
[integer!] stmt [struct! [[integer!]]] dummy [struct! [[integer...

when I installed sqlite with apt-get install sqlite3 I got 2 files 
into /usr/lib/ directory   ./usr/lib/libsqlite3.so.0.8.6  and   ./usr/lib/libsqlite3.so.0 
... if I run rebol it doesn't find sqlite so I copied one of them 
(I tried with both, they are of same size btw) to directory where 
rebol / rebpro and sqlite.r is and renamed it to    libsqlite3.so 
  and then it seems to find it ... now this is just common sense 
as I am not that experienced with this on linux

ah... I looked at history of this chat and saw that there is debian 
package libsqlite3-dev ... I installed this and now I have libsqlite3.so 
.. it seems rebpro finds it without copying it to local directory 
now but it still gibes the same " Cannot open sqlite3_prepare_v2" 
error

ha.. I changed sqlite_prepare_v2 to sqlite_prepare in sqlite.r and 
now rebpro and cheyenne work!!

I hope this doesn't mean I something else won't work as it should

Have your application execute the following SQL statement and then 
look at the result : select sqlite_version(); 

AFAICT, sqlite3_prepare_v2 is available starting from SQLite 3.5.2.

aha.. stupid me.. apt-get usually doesn't provide latest versions 
but a more "stable" ones ... yes it's  3.3.8

You don't need to use apt-get for sqlite, just download the latest 
library and put it in your app folder near sqlite.r : http://www.sqlite.org/sqlite-3.6.11.so.gz
(I guess you'll need to rename it to libsqlite3.so).

thanks Doc!

I did not realize sqlite.r was set up to use mysql3.so (linux libs) 
Got my app running on linux witout a hitch...
Auto detects OS...great!

this might be usefull to users of sqlite.. I yesterday encountered 
the "db is locked" error and it got me a little worried, but with 
this (simple) aproach it seems to solve that http://itsystementwicklung.de/pipermail/list-pysqlite/2009-April/000380.html

.

I don't get this ... I started getting very long loading times with 
my webapp when I changed or inserted the and it was very fast before 
... now I saw that it's the sqlite making these delays.. this is 
not the problem of sqlite.r but the sqlite itself because I get the 
same behaviour with sqlite3 shell.


But I can't believe this , I am certain I am doing something wrong.. 
I remember sqlite can handle GB of data and is very fast, but in 
my case... I have 183 rows in a simple 5 column table (db file is 
10kb) .. if I do single update table X set y = ".." where Z = ".."; 
 it takes like 3 seconds. This updates just 1 row out of 183. Does 
anyone have any idea? I tried to do the "Vacuum" command but it's 
the same after it.

can you post whole query?

I also added the indexes now and it is maybe a little faster but 
on single where but on both that I need it looks more or less the 
same (select by same condition is imeddiate)

Pekr.. I will

This is a very small VPS, but I have 300kb raw rebol data structures 
in ordinary files, and I edit and seek them without any indexes and 
it works immediatelly..  I only moved this part of data to sqlite 
because it handeles the file locking ( these are sort of mailboxes 
so that the app and bots can communicate over them )

CREATE TABLE [domains] (
[id] INTEGER  NOT NULL PRIMARY KEY,
[domain] VARCHAR  NOT NULL,
[user] VARCHAR  NOT NULL,
[processed] DATETIME  NULL,
[ok_count] INT NOT NULL DEFAULT 0,
[fail_count] INT NOT NULL DEFAULT 0,
[error] TEXT NULL
)

test query is as straighforward as it can be: 


update domains set user = "u3" where domain = "www.todelete.com" 
and user = "u3";

I tried now with transaction .. it has no point here becauase it's 
just one query at the time ... but results were as expected.. update 
did finish imediatelly , but commit took the 1-3s time also

try to index domain, and maybe even user fields, or it will go sequentially 
thru all of record lines ...

btw - why do you set user="u3" for records, where user is already 
of "u3" value?

Because times vary from 1s to 5s I suspect taht vps's disk or something 
might be a little owerburdened, but as I said 300kb rebol (this is 
10kb) worked im ms range according to cheyenne

u3
 is just here for testing purposes, so I can repeat the query :)

I added the indexes to both, and each separate .. but it's roghly 
the same ... before indexes select returned imediatelly on the same 
where

I will insert random rows so I will have couple of 1000 , and then 
I will see what I get

OK, index you ID field, then also user and domain field

I did before when trying stuff

index|dom_user|domains|11|CREATE INDEX dom_user on domains ( domain, 
user )
index|dom1|domains|16|CREATE INDEX dom1 on domains ( domain )
index|user1|domains|21|CREATE INDEX user1 on domains ( user )

I would left out first index and add index for your primary key .... 
(not sure though :-)

simply put - how db chooses, which index to use? You have them separate 
as well as mixed. I would use mixed index (domain,user) only if those 
two fields would be defined as a primary key together ...

... well, I am not good at sql internals, so .... try various combinations, 
and you'll see ..

but I am sure even if I write that data inf ile as rebol blocks and 
load and foreach them to find the one it would take far less than 
1s , it's just 180 rows!!!! :)

(maybe I just need a better VPS)

hm.. very interesting results... and positive basically :) with 4000 
records insert time, and update time for the same query is just the 
same as with 183 , and select is fast as before...  then it's survivable 
.. I was afraid that if with so few rows it's so bad with couple 
more it will be unworkable

have to go..storm

If you update an index field, the index too has to be updated.

Do you open and close your db, as it is recommended in the link you 
posted?
Then you have to add the time for that too.

I had it without indexes at first , and later added indexes while 
I was trying various things, at 180 records there wasn't any noticable 
change. Well the result doesn't seem so bad to me right now.. if 
it has the same delay with 4000 records it's okey-ish. On my local 
computer which is much better than some small VPS I noticed no delays. 
I just realized that the delay at web-app was 3x bigger than this 
because I have 3 bots and each has it's own "mailbox" ... The solution 
for this situation will be affloading the inserts from the request 
process, for the future when things will need to scale up I will 
try doing this different anyway, that's why I was playing with actor 
like systems anyway

1s to 5s on 180 rows is bad. whatever you do.

I'm not sqlite user but I would probably tried to use EXPLAIN to 
see, what's going on when you do the query - http://www.sqlite.org/lang_explain.html

yes, I think so too.. it is interesting that the time looks the same 
on 4000 rows ...  maybe reason for this is also because I am on some 
very cheap VPS (12EUR, 180MB ram, ? CPU) ...

aha, thanks for idea.. I will use explain .. although it's so simple 
query that it shouldn't do any strange things I think