World: r3wp

Event = TCP/IP event?

yes

Hm... normaly there should be event/data

normally there is, but not always. 

Most socket connections are static, they stay connected for hours 
or longer.

Reading thru SQLite changelog found this - http://sqlite.org/rtree.html
... not sure of its practical usability, but some of you might find 
it interesting :-)

I love the sqlite motto  :-)   

Small. Fast. Reliable. Choose any three.

rebol.com motto - Web 3.0 starts here. Smarter, faster, better.

only that fast (at sqlite) is still a little problematic to me

SQLIte is fast for simple to middle local stuff. I have few obstacles 
with it 


1) it stores everything in one file. You can't use simplicity of 
file-system for simple back-up purposes. Attaching DBs (max 10 precompiled 
value) is not an option, as then transactions are not atomic

2) it is not secure - can't be secured easily, because encryption 
is not part of the package

3) serverless (both advantage = no install, but also disadvantage). 
It provides locking. They claim multiple instances of app can access 
one file, but I did not find more info on that. Dunno how granular 
locking you can do. You have to create server front-end yourself 
...

I use it for very simple task just so it takes case for locking of 
messages to bots that can come from multiple processes.. but at my 
small/cheap VPS that I use for bots update/insert/delete takes multiple 
seconds which is very bad.. all rebol writes/deletes which it does 
with normal files return imediately on same VPS and yesterday I tried 
rebDB and it was also much much faster for insert/update/delete (almost 
no noticable delay) for the same amount of data (300 lines) as sqlite.. 
funny thing is that sqlite delays the same at these operations if 
there is 300 or 5000 rows in table

(I tried rebDB on same VPS - localy where comp. is fast I notice 
no delay at sqlite either)

and this is no problem with rebol binding as the delays are the same 
if I go to sqlite3 shell

Have you tried put BEGIN transaction and END transaction outer commands? 
Because if not, then it commits each time. The difference is going 
to be ... well, drastic ...

yes, I know for that .. that would come into play if I would be doing 
10 inserts for example ,.. with begin commit it would take just 1/10 
of time as without , but I am doing a simple sql scentence here. 
and I tried begin/commit also with this.

ah, single query?

yes, and nothing big.. 5 simple rows

show me the table structure, show me the query :-)

I talked about this in detail a while ago.. now I optimised the problem 
so it's not a biggie any more but I am still mad that all rebol files 
can do changes in moments and when I need to update the sqlite page 
halts for noricable time

any query I try :) .. I talked about this to you already :) .. look 
a little up at 14-Apr

Janko - I did small test for you. With no indices the speed was: 
0:00:00.516 and I used LIKE expressions, which need to do searches 
in terms of field values .... 

REBOL []

print "Starting test for Janko ..."

do %sqlite.r

attempt [delete %janko.db]

connect/direct/create %janko.db

sql {
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
)

}

sql "BEGIN"

for i 1 1000 1 [

 sql reduce ["insert into domains values(?,?,?,?,?,?,?)" i i i i i 
 i i]
]

sql "COMMIT"

start: now/time/precise


sql {update domains set user = 'u3' where domain like '%1%' and user 
like '%1%'}

print now/time/precise - start

disconnect %janko.db

halt

hm... mucho interesante :)

I will try this on my local computers and then on that VPS.. and 
report you back :)

btw - what is VPS?

virtual private server ... like you can buy here .. www.linode.com 
or sliceshare.com

it's like you have your own computer that you can reinstall stuff 
or OS .. separated from others but it's running on virtualisation 
software so there are many such separate computers per one real computer 
, so it's *cheaper* than paying for having a full server

ah, virtual machine, ok ...

you can put cheyenne there or any other custom server you want

yes

hm.. I have a question for SQL / sqlite experts : 

I have a query with two JOINS . There is parent table which has 2 
subtables ... in each subtable I need to aggregate (SUM) some value 
... 


select i.*, SUM(ib.price * ib.qty) as amount, SUM(ip.amount) as payed_amount
	from invoice_sent as i 

  left outer join invoice_sent_b as ib on i.id = ib.id_invoice_sent

  left outer join invoice_sent_p as ip on i.id = ip.id_invoice_sent
	group by i.id order by i.title;


The problem is tha because of join , the amount is correct , is the 
sum of values in invoice_sent_b , but payed_amount is multiplied 
by the number of rows invoice_sent_b has . 


I understand why this happens, but I don't know how to prevent it, 
if I want to get all data in one query.


( I know how to solve the prolem , but it's outside the DB so it's 
more of  a hack  -- I also ger COUNT of _b table rows and divide 
second SUM by it on client )

ah, my method with count won't work because count also multiplies 
if I have more than 1 columnt in second subtable

This guy has totally the same problem but no answer http://forums.devshed.com/db2-development-114/left-outer-join-3-tables-sum-field-of-2nd-3rdt-588801.html

found solution. I need to join with subqueries:

				{SELECT * FROM } table { as i 
					LEFT OUTER JOIN
					( 

      SELECT id_} table { as ibs_paren, SUM(price * qty) as amount 
							FROM } table {_b ib
							GROUP BY id_} table {
					) ibs ON i.id = ibs_paren
					LEFT OUTER JOIN
					(

      SELECT id_} table { as ips_paren, SUM(ip.amount) as payed_amount 
							FROM } table {_p ip
							GROUP BY ip.id_} table {
					) ips ON i.id = ips_paren
				order by title;

Has anyone done any benchmarks for running scripts from a DB instead 
of from the filesystem?

Say, where there are a moderate number of moderate sized scripts 
- could it be quicker just to fire up an SQLite connection and access 
the scripts/modules from records as opposed to flat file?

Perhaps particularly a CGI environment where each instance is a separate 
process?

If you had persistent connection and if sqlite does some caching 
it could be faster, but if you need to open a connection on each 
request I think it would be much slower because that is more expensive 
(I assume).. it probably also matters how many scripts do you need 
to run per request

The OS caches things too, don't forget.

It shouldn't be hard to devise a small benchmark though.

.

I seem to get an error with sqlite after using "call" to start an 
external program.

** User Error: SQLite out of memory
** Near: make error! reform ["SQLite" error]

Anyone experience this?

Check if you have the right path to file.. and make it an absolute 
path.. and if you have the right version .. sqlite != sqlite3

It works fine until I try to call another app. Paths do not change 
but I'm not using absolute paths. I'll need to test that...

I got similar error but I don't know if it was for the relative path 
or not the right version of dll / so

I changed all the paths to absolute paths...it seems to have done 
the trick. Thanks for the hint, Janko!

youre welcome

I apologize if this is a repeat of an earlier message - I thought 
I submitted, but never saw it appear.


Question: With REBOL/View 2.7.6 under Windows XP, using SQLite driver 
v1.0.6, should I be able to use the concatenate operator (||) in 
a SELECT statement?  Query works under the sqlite3 command line, 
and a similar SELECT without the concatenate returns values as expected:

>> sql "select jobid, role from roles where userid = 'cda6'"
== [[1124 prgr] [1125 test]]

>> sql "select jobid || role from roles where userid = 'cda6'"
** Syntax Error: Invalid integer -- 1124prgr
** Near: (line 1) 1124prgr
>>

Reversing the column order in my concatenate makes it work, but not 
what I want:

>> sql "select role || jobid from roles where userid = 'cda6'"
== [[prgr1124] [test1125]]
>>

Try this >>sql "select '' || jobid || role from roles where userid 
= 'cda6'"

I think the problem is the first column is a integer, so SQLite driver 
think the result is a integer ( invalid integer ), with '' the first 
column is a string

Thanks for the suggestion Manu.  Sounded good to me, but I get the 
same error even with the empty string at the front:


>> sql "select '' || jobid || role from roles where userid = 'cda6'"
** Syntax Error: Invalid integer -- 1124prgr
** Near: (line 1) 1124prgr
>>


even though it appears to be integer, jobid variable is text, according 
to SQLite:
>> sql "select typeof(jobid) from roles"
== [[text] [text] [text] [text]]
>>
Again, thanks!  I welcome any other suggestions.

Why not make the JOIN on the Rebol side?