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-Apr-2006
[323]
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).
BrianH
30-Apr-2006
[325]
I was just reading the User Guide on that site and I noticed that 
there were some parts of the docs on the DESCRIBE function that you 
seemed to have some questions about, particularly the meaning of 
some of the returned columns. (Sorry if I am in error about that.)


- On the table column listing, the notnull column refers to whether 
you are allowed to insert a NULL in that column. The pk column tells 
you whether the column is a primary key. Primary keys in SQLite are 
always integer and are their way of doing autonumber fields.


- On the indexes listing, a unique index also makes sure that the 
combination of values that the index applies to won't be duplicated 
in the table.
Ashley
30-Apr-2006
[326]
Thanks, I've updated the page (replacing my shorthand for a flag, 
?, with the word flag). If anyone has a description of the columns 
for FKeys just holler and I'll add it in.
BrianH
1-May-2006
[327x2]
I'm not sure it matters yet. SQLite doesn't check foreign key constraints 
yet, so any such constraints are just documentation right now.
Still, here they are (best estimate based on reading the SQLite C 
source):

- id: The index of the foreign key in the list of foreign keys for 
the table, 0-based (integer)

- seq: The index of the column referenced in the foreign key, 0-based 
(integer)
- table: The name of the referenced table (string)
- from: The column name in the local table (string)
- to: The column name in the referenced table (string)
Robert
1-May-2006
[329]
id: Isn't ID only unique for every table? Yours read like ID is unique 
for the complete database.
BrianH
1-May-2006
[330x6]
No, this is a diagnostic table generated by a pragma statement. It 
isn't really a table internally. Still, the key for this "table" 
is (id,seq) and it is generated by a pragma that takes the table 
name as a parameter, so it is a seperate "table" for each real table.
It really is a linked list internally - that's why I called it that 
:)
Each "row" of the "table" refers to a column referenced by a foreign 
key that may refer to more than one column.
And a table may have more than one foreign key.
And all of these foreign keys are currently meaningless because they 
aren't currently checked by SQLite - they are just documentation.
Still, you could make a tool that actually read these foreign keys 
and checked them externally if you want...
Robert
1-May-2006
[336]
IIRC you can even access those internal tables of SQLite and query 
them.
BrianH
1-May-2006
[337x2]
Well, for the most part this database metadata isn't really stored 
in tables in SQLite. Instead queries about the metadata are generated 
from internal structures when needed, by pragma statements. Still, 
the effect is the same.
It's kind of cool really when you read how this is done. I guess 
things can be a lot more interesting for a single-user embedded database 
engine. You can see where SQLite gets its high performance.
Robert
1-May-2006
[339]
I have to take a look at the C code. But #pragma stuff is compile 
time...
BrianH
1-May-2006
[340x4]
No, not C pragma, a set of PRAGMA statements that SQLite extensions 
to SQL, for settings and diagnostics.
...that _are_ SQLite extensions...
Check http://www.sqlite.org/pragma.html
The statement that returns the result set we were talking about is:
    PRAGMA foreign_key_list(table-name);
Ashley
1-May-2006
[344]
Thanks, updated page.
Robert
1-May-2006
[345]
Ah, ok. Now I get it.
Terry
4-May-2006
[346]
 wtcSQLite allows you to add/edit/delete indexes, fields, tables, 
 triggers, views, data, and manage multiple database aliases without 
 knowing any SQL at all. 
http://www.zend.com/php5/contest/contest.php?id=74&single=1

(it's phpmyadmin for sqlite)
Robert
8-May-2006
[347x2]
Q: If I have a table with let's say 1 million records and I do a 
query that returns 250.000 records as result set. Do I get all of 
them copied into Rebol? Or can I access this result set "pagewise"?
What's the best pattern to handle such hughe tables?
Terry
8-May-2006
[349]
The best pattern for such huge tables?
MySQL
Robert
8-May-2006
[350x2]
No, no option for me.
So, what can be done is SELECT ... LIMIT n OFFSET m. At least this 
can handle the result set in defined block sizes on the application 
level. Of course paging requires a new query each time.
Ashley
8-May-2006
[352]
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.
Robert
8-May-2006
[353]
I have no concurrency issue. Yes, this might be a good solution too.
Sunanda
8-May-2006
[354]
Could you use a cursor?

(That would work with many other SQLs....Not sure about MySQL's support 
for them)
Robert
8-May-2006
[355]
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.
Sunanda
8-May-2006
[356]
That's a pity.
Robert
22-May-2006
[357x2]
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?
Pekr
22-May-2006
[359]
output to csv, import?
Robert
22-May-2006
[360]
This mean on every update the user has to do this cycle... don't 
think this is an option.
BrianH
22-May-2006
[361]
Adding is easy - I think SQLite supports ALTER TABLE ADD COLUMN.
Pekr
22-May-2006
[362]
well, how often do you adjust your tables? ;-) then it is not good 
design imo ...
BrianH
22-May-2006
[363]
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.
Robert
22-May-2006
[364]
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.
Pekr
22-May-2006
[365]
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 ...
Robert
22-May-2006
[366]
That's what I do, see above. I just want to make my app handling 
cases where the structure changes.
Pekr
22-May-2006
[367]
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 ....
Robert
22-May-2006
[368x2]
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.
BrianH
22-May-2006
[370]
You could rig something up with the DESCRIBE function. Or you could 
specify the /info refinement to CONNECT.
Robert
22-May-2006
[371x2]
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.