[Tutor] Fwd: Which databases allow lists as record fields?

[DoanVietTrungAtGmail]

I read your help (thanks, Peter and Alan) and thought that relational
database technology didn't naturally map to my problem domain. Here, at
each simulation timestep each entity has a run-time variable number of
pointers to other entities. Representing each entity as a table seems
unwieldy to me.

So, I searched further and now write about what I've learnt, in case
someone with a similar problem finds this thread in future.

One possible set of solutions is the family of "NoSQL
databases<http://en.wikipedia.org/wiki/NoSQL>".
Redis is one such DB, of "key-value" type, and in Pypi there is a package
called redis 2.7.2 <https://pypi.python.org/pypi/redis/2.7.2>. Another type
is "graph databases", and in Pypi there's a package called redis-graph
1.0<https://pypi.python.org/pypi/redis_graph/1.0>built on top of
Redis.

I've also just installed Scipy because Scipy handles sparse
matrices<http://www.scipy.org/SciPyPackages/Sparse>.
My simulation has N entities, I'll try representing it as an NxN sparse
matrix, each row stores pointers in some sparse format to the rows it talks
to.

And then there's also NxN bitarray <https://pypi.python.org/pypi/bitarray/>,
where on each row a bit is 1 if this row points to this column, to save
space there's "compressed bitarray".  I also read about "Judy sparse
array", touted as fast and memory efficient, but last night I read the
author of the package PyJudy
<http://stackoverflow.com/questions/550204/judy-array-for-managed-languages/550464#550464>writing
in 2009 that he hasn't maintained it for "a few years", that put me off.

Which one to use, I don't know yet. I am about to start experimenting with
Scipy sparse matrix.

Trung
==============

On Thu, Mar 14, 2013 at 8:13 PM, Alan Gauld <alan.gauld at btinternet.com>wrote:

> On 14/03/13 01:39, DoanVietTrungAtGmail wrote:
>
>>     You don't. You create a second table to hold the list.
>>     Then in the second table you include be reference back to the first.
>>
>> assuming I do it that way, how to deal with variable-length list? Most
>> lists have 10^3, but some can grow to perhaps 10^7 items. A fixed record
>> structure to fit the longest possible record would make the database
>> table sparse and, presumably, waste space.
>>
>
> I'm not sure what you mean here but the tables are not fixed size. Usually
> you are only limited by the size of your hard drive.
>
> To give an example if you have an object MyType with two of these list
> type attributes called foo and bar. We have two instances of MyType, A and
> B. A has 10 foos and 50 bars while B has 500 foos and 1000 bars.
>
> Create a table MyType and populate it with A and B
>
> Create a Table MyType_foo and populate it with 10 rows referencing A and
> 500 rows referencing B
>
> Create a table Mytype_bar and populate it with 50 rows referencing A and
> 1000 rows referencing bar
>
> Now to see all the foos for A use
>
> select value from foo where parent = A
>
> or for B use
>
> select value from foo whee parent = B
>
> and the same for bar...
>
> You can keep adding rows to foo or bar for as long as ypou like.
> Youb can add new MyType rows and then add more rows to MyType_foo and bar
> which reference thiose new MyTypes.
>
> There is no limit to how many items you add until your disk fills up!
>
>
>  An alternative I thought of was to use SQL Server fields for XML, or
>> varchar variable-length character strings containing 10^9 characters,
>> then to read the list I'd parse the XML or split the string into literal
>> integers then convert to int. I wanted to avoid this computation cost if
>> possible.
>>
>
> You can do this but you might as well just store everything in XML in a
> flat file. You lose the power of the database query language to
> search/filter the results and give yourself a huge
> development/test/maintenance task.
>
> The people who build databases are likely much better at this stuff that
> you or me. Use what they give you.
>
>
>
> --
> Alan G
> Author of the Learn to Program web site
> http://www.alan-g.me.uk/
>
> ______________________________**_________________
> Tutor maillist  -  Tutor at python.org
> To unsubscribe or change subscription options:
> http://mail.python.org/**mailman/listinfo/tutor<http://mail.python.org/mailman/listinfo/tutor>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20130325/e6f41c42/attachment.html>