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

World: r3wp

[SQLite] C library embeddable DB .

Robert
24-Mar-2006
[275]
Next question, before I start using SQLit and RebGUI now. How do 
you handle the identification of table rows? Do you display the record 
ID or can the record ID be a hidden entry?
Ashley
24-Mar-2006
[276]
I display the record ID (which maps to a unique entity ID such as 
patient#, order#, ect) and which the end-user is usually interested 
in seeing anyway (as it ties in to their other systems). If you want 
to hide the column in RebGUI then just give it a width specification 
of 0 and ensure it is not the last column.
Ingo
25-Mar-2006
[277x2]
... or you could explicitly name all columns you want to display 
play in your select statement

	select * from persons; 

becomes

	select firstname, lastname from person;
the concatenation operator || does not work. If I do:

	SELECT firstname || lastname AS name from person;

I still get blocks of 2 values, not one.
Ashley
25-Mar-2006
[279x3]
Replace the column-text block in the SQL function with:

	[(
		either direct [
			[*column-text (sid) idx]
		][
			[
				s: v: *column-text (sid) idx
				while [s: find s {""}] [change/part s "" 2]
				load v
			]
		]
	)]

I've added this to the next build.
0.1.8 available at: http://www.dobeash.com/SQLite/sqlite.r


Two main fixes are date bind value handling and concatenated string 
handling (both of which were posted previously as code snippets).
0.1.9 posted with improved concatenation handling logic. In addition 
to handling strings you can now also do:

	SQL "select name||100 from customers"

and

	SQL {select id||'"-'"||name from customers}


Most REBOL types are supported (in concatenation type operations) 
although blocks may cause problems.
Ingo
26-Mar-2006
[282x2]
thanks Ashley. I already found out, that it works when using direct. 
Now going to check the new versions.
BTW, is there a speed gain when using direct? I only save strings 
atm, anyway.
Ashley
26-Mar-2006
[284]
Direct: definite speed gain as MOLD / LOAD is bypassed for each and 
every value bound / retrieved. /flat (if your data structure can 
use it) is also faster and uses less memory - although the gain is 
more noticeable with larger numbers of rows.
Ingo
30-Mar-2006
[285]
Is it possible to check, wether a database is already connected?
Ashley
30-Mar-2006
[286]
DATABASE function?
Thør
2-Apr-2006
[287]
initial sync...
Ingo
3-Apr-2006
[288x2]
Right, that's it! Don't know why I haven't seen it ;-)
Although, if you are not connected to any database, it raises an 
error just like any other function. 

I think it would be helpful to have a function to just check, maybe 
like ...


either none? database [ ... you hev to connect ....][ ... check whether 
the right database is corrected ...]
Robert
3-Apr-2006
[290x2]
Value Binding: Is something like this possible as well?

	SQL ["select * from ? where col_1 = ?" mytable 1]
IMO it would be very handy, because those statements can be prepared 
and assigned to rebol words, for simple access in an application. 
It the SQL statement changes, I only have to do it at one place.
Ashley
3-Apr-2006
[292]
Not sure, havn't tried yet.
Ingo
5-Apr-2006
[293x3]
I got an error in the 'sql func ...


** Script Error: length? expected series argument of type: series 
port tuple bitset struct
** Where: switch
** Near: *bind-text sid i val length?

the database is opened with /direct refinement.

The call is:
	sql ["select * from person where guid = ?" guid1]


Where I know, that the dataset with this guid exists, because I have 
just got it from another selsct.
The dataset contains only strings, some of them empty.


Well, this is it: ["h-o-h.org_20060326_182311691_1224" "Urte" "Hermann" 
"Urmeli" "" "" "" "" "" "" "" "" "" "" "Opera ID: 359" "" "" ""]

And I am using the right guid.

Any ideas?
Uuups, seems I fed the wrong data ... 

guid1 in the previous post was not the full guid as a thought, but 
the first character of said guid.
So, the error in one small message: 

>> sql ["select * from person where guid = ?" #"a"]

** Script Error: length? expected series argument of type: series 
port tuple bitset struct
** Where: switch
** Near: *bind-text sid i val length?
Ashley
5-Apr-2006
[296]
If you're using CONNECT/direct then char is not an available datatype 
(and you would have had the same problem trying to insert a char). 
What does the raw data look like? I suspect it is: {#"a"}
Ingo
6-Apr-2006
[297]
No, actually it is something like "abcdef", I had one 'first too 
many in my code. That was the problem.

It''s just that I stumbled about the error, and didn't know what 
to make out of it. At least at first.
Robert
7-Apr-2006
[298x4]
Is it somehow possible to encrypt the content of databases?
Ok, there seem to be some companies that offer special SQLite DLLs. 
Ashley, maybe you can have a look to see if the DLL interface is 
compatible? I think there shouldn't be big changes necessary. Take 
a look at: www.sqlcrypt.com
It's just adding two APIs to SQLite.
I'm going to check it out.
Ashley
7-Apr-2006
[302]
You could always encrypt selective columns client-side (e.g. sql 
reduce ["insert into t values (?,?)" key encrypt string])
Robert
7-Apr-2006
[303]
Yes, but for querying I need to encrypt the data as well to be able 
to select it. Don't know if this is a big performance hit.
Ashley
7-Apr-2006
[304]
If it's only TEXT types you need to encrypt then we could always 
add a /secure refinement to CONNECT that would force encrypt / decrypt 
on all TEXT bind variables. Performance wouldn't be too bad as values 
would only be encrypted [once] on INSERT and SELECT, the actual query 
(which could potentially hit millions of rows) would be comparing 
encrypted strings and would only need to decrypt strings that form 
part of the result set. Very similiar to the overhead that MOLD/LOAD 
currently incur when not using the /direct refinement.
Graham
7-Apr-2006
[305x3]
Does sqlite have a "like" command for searching within text fields 
?
If so, then searching on text fields would be problematic if they 
were encrypted.
I was thinking of encrypting my own text fields for security, but 
came across this little problem.
Ashley
7-Apr-2006
[308]
Yes on both counts. Really depends on what kind of data needs to 
be encrypted; if it's passwords and personal information that is 
indirectly referenced (i.e. on / by other key column(s) ) then client-side 
encryption makes sense.
Pekr
8-Apr-2006
[309]
I am not sure it is wise to encrypt all text fields .... usually 
you may require to encrypt password field or something like that, 
but all fields at once? Maybe if it really makes implementation simpler 
....
Anton
8-Apr-2006
[310]
You'd better ask Robert how wise he is.
Robert
8-Apr-2006
[311x2]
IMO encryption should be on a lower level, on the file level. I don't 
want to care about using encryption in my application, I just want 
to set a flag that the database file gets encrypted.
Background: I'm building up a massive benchmark database for cost 
data. And I need to encrypt all information in it.
Robert
22-Apr-2006
[313x2]
I tried to use: CREATE TABLE IF NOT EXISTS but I get an error if 
I use this line and the table already exists.
The SQLite docs say:

 If the optional IF NOT EXISTS clause is present and another table 
 with the same name aleady exists, then this command becomes a no-op.
Robert
23-Apr-2006
[315x5]
Did anybody tested to open sqlite.r generated databases with a database 
manger? I can't. In one tool I can see the database but not tables, 
even I have created one.
And if I use the TABLES command I get:
>> print tables

material CREATE TABLE material (_id INTEGER PRIMARY KEY AUTOINCREMENT,_version,_benchmark,_prev,_next,_product_id,name,price,pcdl,pudl,netto,nudl,scrap,loss,process_loss,scrap_re

cycle_rate,scrap_recycle,srcdl,srudl,process_loss_recycle_rate,process_loss_recycle,plrcdl,plurdl) 
sqlite_sequence CREATE TABLE sqlite_sequence(name,seq)
The docs state that I get beck the number of columns and rows... 
Any idea?
Using sqlite.r I can acess the table and get back my values.
And I can't get back the columns name. sqlite/columns returns an 
empty block.
Ashley
23-Apr-2006
[320x2]
anybody tested to open sqlite.r generated databases with a database 
manger?
 Not sure what you mean here. The following works fine for me:

	c:\> sqlite3.exe my-db.db

docs state that I get back the number of columns and rows
 ... no longer the case (docs need to be updated)


Most efficient way to get number of rows is sql "select count(*) 
from my-table"


Number of columns can be found by: (length? describe "my-table") 
/ 6

I can't get back the columns name

 ... use the DESCRIBE command, or CONNECT/info (which then populates 
 sqlite/columns and sqlite/widths for every query; or alternatly, 
 set sqlite/col-info? true / false for a particular query).
A simple rows function:

	set 'rows make function! [
		"Return row count."
		table [string!]
	][
		first sql/flat/direct reform ["select count(*) from" table]
	]

just add it to the body of the sqlite context.
Robert
24-Apr-2006
[322x2]
Thanks.
WRT encryption. I found one implementation (you need to buy a license) 
that supports transparent encryption. IIRC I posted the link some 
time ago. I will have a look at the C code and there at the storage 
stuff to see how hard it is to add an AES encryption of storage pages. 
IMO it can't be that hard.
Ashley
29-Apr-2006
[324]
0.2.0 available at: http://www.dobeash.com/SQLite/


Incorporates minor fixes / changes discussed since 0.1.9, with documentation 
updated to match (some content was also moved from the old Driver 
Guide to the new User Guide).