r3wp [groups: 83 posts: 189283]
  • Home
  • Script library
  • AltME Archive
  • Mailing list
  • Articles Index
  • Site search
 

World: r3wp

[SQLite] C library embeddable DB .

Chris
21-May-2009
[1051x3]
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?
Janko
21-May-2009
[1054]
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
Gregg
21-May-2009
[1055x2]
The OS caches things too, don't forget.
It shouldn't be hard to devise a small benchmark though.
RobertS
22-May-2009
[1057]
.
amacleod
26-May-2009
[1058]
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?
Janko
26-May-2009
[1059]
Check if you have the right path to file.. and make it an absolute 
path.. and if you have the right version .. sqlite != sqlite3
amacleod
26-May-2009
[1060]
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...
Janko
26-May-2009
[1061]
I got similar error but I don't know if it was for the relative path 
or not the right version of dll / so
amacleod
26-May-2009
[1062]
I changed all the paths to absolute paths...it seems to have done 
the trick. Thanks for the hint, Janko!
Janko
26-May-2009
[1063]
youre welcome
jack-ort
10-Sep-2009
[1064x2]
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]]
>>
ManuM
11-Sep-2009
[1066]
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
jack-ort
11-Sep-2009
[1067]
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.
Robert
11-Sep-2009
[1068]
Why not make the JOIN on the Rebol side?
ManuM
11-Sep-2009
[1069]
Jack, finally I saw the problem


The return of sql isn't two blocks of string, it's two blocks of 
word. So 1124prgr isn,t a valid word and isn't a valid integer

>> a: sql "select jobid, role from roles where userid = 'cda6'"
>> type? first first a
==word


You need to add the /direct refinement to sql and it returns strings

>> sql/direct "select jobid || role from roles where userid = 'cda6'" 
==[["1124prgr"]["1125 test"]]
jack-ort
14-Sep-2009
[1070]
Robert - thanks...that's what I eventually did.


Manu - thank you for making clear what the /direct refinement does...I 
had read about that in the SQLite Driver Guide, didn't understand 
it since REBOL is still new to me, and forgot about it.  Works perfectly! 
 So much to learn....
jack-ort
15-Sep-2009
[1071]
Is there a problem with:

http://www.dobeash.com/

seems like domain is up for sale ??
Graham
15-Sep-2009
[1072]
Probably hasn't paid renewal fees!
GiuseppeC
7-Oct-2009
[1073x2]
It is back online again.
Does REBOL SQLite support SQLLite3 ?
Pekr
7-Oct-2009
[1075]
yes, it does ...
jrichards
23-Nov-2009
[1076]
Is it possible to use the sqlite driver for a file that resides on 
the internet?
Robert
24-Nov-2009
[1077]
As long as you can access it via a normal file-path, yes. Can be 
slow but should work.
Janko
24-Nov-2009
[1078]
stupid me, I only now realized sqlite driver supports prepared statements 
too and I was concatenating strings all along.
Chris
24-Nov-2009
[1079]
I seem to recall a note about locking issues over a network path, 
so ymmv.
Pekr
6-Jan-2010
[1080]
Does following mean, we could have trouble to propelry sort (using 
collations) because of UTF-8?


http://stackoverflow.com/questions/181037/case-insensitive-utf-8-string-collation-for-sqlite-c-c
BrianH
6-Jan-2010
[1081x2]
The sorting problem with collations applies to Unicode, not just 
UTF-whatever, so it is a problem. Mostly a problem of size: The actual 
colation sorting code is small, but the collation data is large. 
Add more than one or two and REBOL gets huge.
A solution would be to move the sorting out into the host code, where 
it can be made to use any system-specific sorting code, which should 
work for platforms with good Unicode support like Windows, OS X and 
some Linux distros. The problem with that is that the data would 
probably need to be migrated to the host format before the host routines 
could be used; string! is not UTF-8 or UTF-16 internally, it is UCS-8 
(in theory), UCS-16 or UCS-32 (in theory), all fixed-length encodings. 
That would add a lot of overhead to sorting.
joannak
9-Jan-2010
[1083x2]
ICU seems to have such library with nice license... Unfortunately 
the Binary-only package for one platform is like 10Megabytes..
http://icu-project.org/download/4.2.html#ICU4C
This unifoce srting seems to be a mess.. Well, technically they do 
have some standards, but trying to even think any decent size implementation 
of that.. 
http://www.unicode.org/reports/tr10/
james_nak
12-Jan-2010
[1085]
Is there a method to "dump" the contents of a sqlite db for back-up 
purposes using the the driver?
Robert
12-Jan-2010
[1086]
why not just copy the file?
james_nak
12-Jan-2010
[1087]
Robert, Well, that's what I do do now but I was wondering if there 
was also a command.
Gabriele
13-Jan-2010
[1088x2]
James, if you have the sqlite command available, you can do:
sqlite /path/to/file.db .dump >/path/to/dumpfile.sql
james_nak
15-Jan-2010
[1090]
Thanks Gabriele. I was looking for a programatic way but I think 
I'll stick to just copying the file.
Gabriele
16-Jan-2010
[1091]
well, that is programmatic. .dump is a command for the sqlite utility. 
if you want to do it from REBOL, you have to code it yourself, as 
it's not a SQL command.
jrichards
21-Jan-2010
[1092]
Is this an accepted method of populating the fields of a view form 
or is there a more precise method?

btn-next: func[][
   if (counter  < (length? db)) [
    counter: counter + 1
    lname/text: db/:counter/2
    fname/text: db/:counter/3
    spouse/text: db/:counter/4
    email/text: db/:counter/5
    hphone/text: db/:counter/6
    cphone/text: db/:counter/7
    addr/text: db/:counter/8
    city/text: db/:counter/9
    state/text: db/:counter/10
    zip/text: db/:counter/11
    
    show window
    ]
]
Henrik
21-Jan-2010
[1093]
you can wrap the fields in a panel and use:

set-face my-panel [db/:counter/2 db/:counter/3 ...]
Robert
21-Jan-2010
[1094]
And, I have abstracted this idea in that a function walks over the 
set-words of an object and use these as columnnames.
Henrik
21-Jan-2010
[1095x2]
in the VID extension kit you can say:

set-face my-panel object

object: get-face my-panel
hmm... actually not really. forget that. I'm distracted. :-)
amacleod
21-Jan-2010
[1097]
Henrik, What's the latest versionof Ext kit?
Henrik
21-Jan-2010
[1098]
003. Unfortunately, it's moving too slow. I won't have any time to 
do updates for a while.
Gregg
21-Jan-2010
[1099]
There is no standard I know of for mapping data to faces. A number 
of us have rolled our own systems over time, each with our own critieria 
and design aesthetic.


As a simple starting point, consider setting up declarative mappings 
and driving a data-exchange loop. e.g.

face-field-map: [
    lname 2 fname 3 spouse 4 
    email 5 hphone 6 cphone 7 
    addr 8 city 9 state 10 zip 11
]

foreach [face-name field-index] face-field-map [
    set-face get face-name  pick db/:counter field-index
]
jrichards
21-Jan-2010
[1100]
Thanks Gregg, as a newbie I was just trying to get an idea as to 
how others are handling this.  I was thinking that there had to be 
a more concise method than the one I was using.