Re: [pysqlite] what is best data structure

This is a MIME-encapsulated message.

----__Part.20090715033812.60FFB235
Content-Type: text/plain; charset="UTF-8"

Hello Dinesh and Dennis,

Although it is obviously against the rules of database normalization, I've also used e 'bypass' like this. This dealt with lists of about one hundred values on average and with a maximum size of 8 positions for each value. In my case the reason to do this is to gain speed. It works much faster when dealing with highly volatile data, where the insert and delete activity is dominant over queries.

It may be relevant to know that you can still access the data in a normalized form, even if stored denormalized. You can define a view which presents the data as if each value is stored in an individual row. I'd prefer to explain this further in SQL (rather than in text) and wiil attach an example. This can also be viewed at http://sqmediumlite.org/spam/pysqlitemail090715.html.

Regards, Edzard.

--- [email protected] wrote:

From: Dennis Lee Bieber <[email protected]>
To: list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
Subject: Re: [pysqlite] what is best data structure
Date: Tue, 14 Jul 2009 09:45:01 -0700

On Tue, 14 Jul 2009 08:44:50 -0700, "Dinesh B Vadhia"
<[email protected]> declaimed the
following in gmane.comp.python.db.pysqlite.user:

> Using Python, lists of integers are stored in sqlite as strings ie. "123 234 345 456 567 678 789 890 901 1012 1123 etc." .  The strings can be very long and of variable length.  Next, I want a non-Python program (eg. C/C++) to be able to read the sqlite db (ie. data portability).  Is there a faster data structure than a list or a more compact format than character strings available to store the lists of integers in a sqlite db and ensure portability?
>

	Short answer: don't store "lists" in a field

	Longer answer: read up on database normalization; other than some
special data types in, I think PostgreSQL, you should not have a field
containing multiple values, nor should you have repeating groups
(multiple fields that store the same meaning/type of data)

	Instead of (I'm assuming auto-increment primary keys):

pkey1|something|else|long, list, of, items
pkey2|another|record|with, stuff

	Define a separate table of three items: local-key, foreign-key,
number;  You now have:

pkey1|something|else
pkey2|another|record

and

lkey1|fkey1|long
lkey2|fkey1|list
lkey3|fkey1|of
lkey4|fkey1|items
lkey5|fkey2|with
lkey6|fkey2|stuff

where fkey/n/ = pkey/n/

	What you have not mentioned is if the order of "long list of items"
is important -- if it is, and you modify a value that changes the sort
order you may have to perform a sequence of "delete from second-table
where foreign-key = XXX", followed by a series of "insert into
second-table (foreign-key, item-value) values (XXX, nnn)". Of course, if
you just need the items in a natural sort order (ascending or
descending) it may not matter -- you can do that on retrieval.

select main-table.data1, main-table.data2, second-table.item from
main-table inner join second-table on main-table.pkey =
second-table.fkey order by main-table.pkey, second-table.item

-- 
	Wulfraed	Dennis Lee Bieber		KD6MOG
	[email protected]		[email protected]
		HTTP://wlfraed.home.netcom.com/
	(Bestiaria Support Staff:		[email protected])
		HTTP://www.bestiaria.com/

_______________________________________________
list-pysqlite mailing list
list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite



----__Part.20090715033812.60FFB235
Content-Type: application/octet-stream; name="pysqlitemail090715.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="pysqlitemail090715.sql"

LyoKVGhpcyBzY3JpcHQgc2hvd3MgaG93IGEgbGlzdCBvZiB2YWx1ZXMgdGhhdCBpcyBzdG9yZWQg
aW4KYSBzaW5nbGUgZmllbGQsIGNhbiBiZSBhY2Nlc3NlZCBmcm9tIFNRTCwgYXMgaWYgaXQgd2Vy
ZQpzdG9yZWQgd2l0aCBlYWNoIGVsZW1lbnQgaW4gYSBzZXBlcmF0ZSByb3cuCihzZWUgaHR0cDov
L2l0c3lzdGVtZW50d2lja2x1bmcuZGUvcGlwZXJtYWlsL2xpc3QtcHlzcWxpdGUvMjAwOS1KdWx5
LzAwMDQ0NC5odG1sKQoqLwoKLS0gU2V0dXAgc29tZSBpbnB1dCBkYXRhIChhcHByLiAxMDAwIHZh
bHVlcykKY3JlYXRlIHRhYmxlIGlucHV0ZGF0YSAodmFsdWUgaW50ZWdlcik7Cmluc2VydCBpbnRv
IGlucHV0ZGF0YSB2YWx1ZXMgKDEpOwppbnNlcnQgaW50byBpbnB1dGRhdGEgc2VsZWN0IHZhbHVl
ICsgdmFsdWUgZnJvbSBpbnB1dGRhdGE7Cmluc2VydCBpbnRvIGlucHV0ZGF0YSBzZWxlY3QgdmFs
dWUgKyB2YWx1ZSBmcm9tIGlucHV0ZGF0YTsKaW5zZXJ0IGludG8gaW5wdXRkYXRhIHNlbGVjdCB2
YWx1ZSArIHZhbHVlIGZyb20gaW5wdXRkYXRhOwppbnNlcnQgaW50byBpbnB1dGRhdGEgc2VsZWN0
IHZhbHVlICsgdmFsdWUgZnJvbSBpbnB1dGRhdGE7Cmluc2VydCBpbnRvIGlucHV0ZGF0YSBzZWxl
Y3QgdmFsdWUgKyB2YWx1ZSBmcm9tIGlucHV0ZGF0YTsKaW5zZXJ0IGludG8gaW5wdXRkYXRhIHNl
bGVjdCB2YWx1ZSArIHZhbHVlIGZyb20gaW5wdXRkYXRhOwppbnNlcnQgaW50byBpbnB1dGRhdGEg
c2VsZWN0IHZhbHVlICsgdmFsdWUgZnJvbSBpbnB1dGRhdGE7Cmluc2VydCBpbnRvIGlucHV0ZGF0
YSBzZWxlY3QgdmFsdWUgKyB2YWx1ZSBmcm9tIGlucHV0ZGF0YTsKaW5zZXJ0IGludG8gaW5wdXRk
YXRhIHNlbGVjdCB2YWx1ZSArIHZhbHVlIGZyb20gaW5wdXRkYXRhOwppbnNlcnQgaW50byBpbnB1
dGRhdGEgc2VsZWN0IHZhbHVlICsgdmFsdWUgZnJvbSBpbnB1dGRhdGE7CgotLSBzYXZlIHRoZSBk
YXRhIGFzIGEgbGlzdCBvZiBmaXhlZC1zaXplIGVsZW1lbnRzLCAKLS0gdGhpcyBleGFtcGxlIGFz
c3VtZXMgYSBtYXhpbXVtIGVsZW1lbnQgc2l6ZSBvZiA0IGRpZ2l0cy4KY3JlYXRlIHRhYmxlIGNs
dXN0ZXJlZCAoCiAgICBpZCBpbnRlZ2VyIHByaW1hcnkga2V5LAogICAgbGlzdCB2YXJjaGFyICg5
OTk5OSkKICAgICk7CgppbnNlcnQgaW50byBjbHVzdGVyZWQKc2VsZWN0CiAgICAxIGFzIGlkLCAK
ICAgIGdyb3VwX2NvbmNhdCAoc3Vic3RyICh2YWx1ZSB8fCAnICAgICcsIDEsIDQpLCAnJykgYXMg
bGlzdApmcm9tIGlucHV0ZGF0YTsKCi0tIHRoZSBzb2x1dGlvbiBpcyBkcml2ZW4gYnkgYW4gYXV4
aWxhcnkgdGFibGUgb2YgaW50ZWdlcnMsCi0tIHdpdGggYXQgbGVhc3QgYXMgbWFueSBlbGVtZW50
cyBhcyB0ZSBtYXhpbXVtIGxpc3Qgc2l6ZS4KLS0gdGhpcyBjYXNlIGNvbnZlbmllbnRseSB1c2Vz
IHRoZSByb3dpZCBvZiB0aGUgaW5wdXQgZGF0YS4KY3JlYXRlIHZpZXcgY291bnRlciBhcyBzZWxl
Y3Qgcm93aWQgIGFzIGkgZnJvbSBpbnB1dGRhdGE7CgotLSBwcmVzZW50IHRoZSBkYXRhIGluIGEg
bm9ybWFsaXplZCBmb3JtCmNyZWF0ZSB2aWV3IHZub3JtYWxpemVkIGFzCnNlbGVjdAogICAgY2x1
c3RlcmVkLmlkLAogICAgY291bnRlci5pLAogICAgY2FzdCAoCiAgICAgICAgc3Vic3RyIChjbHVz
dGVyZWQubGlzdCwgNCAqIGNvdW50ZXIuaSAtIDMsIDQpIAogICAgICAgIGFzIGludGVnZXIpIGFz
IHZhbHVlCmZyb20gY2x1c3RlcmVkCmpvaW4gY291bnRlcgpvbiBjb3VudGVyLmkgPD0gbGVuZ3Ro
IChjbHVzdGVyZWQubGlzdCkgLyA0OwoKLS0gZm9yIGV4YW1wbGUsIHNlbGVjdCB0aGUgODg4dGgg
ZWxlbWVudApzZWxlY3QgKiBmcm9tIHZub3JtYWxpemVkCndoZXJlIGlkID0gMSBhbmQgaSA9IDg4
ODsKCg==

----__Part.20090715033812.60FFB235
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
list-pysqlite mailing list
list-pysqlite-FR6EJeJVuqdwc357pe9rcyQmJico6nz3epZhswDD4dQ@public.gmane.org
http://itsystementwicklung.de/cgi-bin/mailman/listinfo/list-pysqlite
----__Part.20090715033812.60FFB235--