Hi, I am looking for a small embeddable database that allows lists as
datatype, similar to the internal database of a Prolog programming system.
I.e. the data are organized in tables, so it is a relational database. But
an element in a table can be a list of variable length, eg. a string list or
a number list
Any idea? Could SQlite do that with some magic tricky things?

Andreas Kochenburger wrote:
Firebird is an example of an RDBMS that has a built-in datatype that
more or less does what you describe. It has a datype that is stored
like a BLOB, but it is structured like a multi-dimensional array. There
is an API that allows you to fetch "slices" of the array into
application arrays. You can also reference individual elements of the
array in SQL expressions.

However, this is a proprietary extension, not standard SQL. Standard
programming interfaces like JDBC, ODBC, Perl DBI, PHP PDO, etc. have no
clue how to fetch array slices and return them to the calling
application. You'd have to do all your programming in C, and learn
Firebird's proprietary API for fetching array slices.

Anyway, it's generally considered a bad practice to store multiple
values in one column. It breaks rules of normalization. (NB: this is
true for traditional RDBMS, but not necessarily for experimental
technology such as "network databases").

Normalization says that if you have multiple values associated with an
entity, you should create a dependent table. Each value in the list
should go in a separate row of the additional table. These rows also
include a foreign key referencing the row in the primary table with
which the list is associated.

Some people resist the database normalization guideline above, and store
lists as a single column, containing long strings with comma-separated
values.

The disadvantages of using this list-in-a-string design include:

- The list has a hard maximum length limit, because the column itself
must have a size limit. But it's hard to predict how many values you
can put in the list, if values are of variable length themselves.
- You can't easily use the list-separator character as part of the
values in the list.
- You need to fetch the list data into your application to do many
calculations on it, e.g. counting the number of values in the list,
calculating the minimum/maximum/average value, etc.
- You need to fetch the entire list even if you're only interested in a
small portion of it.
- Inserting or deleting a value from the list requires fetching the
list, altering it, and then updating it. Keeping the list in sorted
order is additional work.
- No way to use referential integrity, if the values in the list need to
reference other database entities.
- No way to ensure values in the list are valid. What would prevent
someone from entering "1,2,3,banana,5" into a column that is supposed to
list integers?
- No way to associate additional information with each element of the list.

Any primitive "list" datatype in a database should account for these
disadvantages. That would be difficult. Even Firebird's arrays
implementation fails on several of these points.

Regards,
Bill K.

"Bill Karwin" <bill (AT) karwin (DOT) com> wrote

Unless I miss my guess, Firebird's BLOBS are very much like DEC Rdb/VMS's
BLOBS.

DEC Rdb/VMS still survives as Oracle/Rdb.

If I'm right in the above, then the "slices" you refer to are "segments",
and they are physical components of the BLOBS rather than logical
components. You could certainly store a list in a BLOB if you wanted to.
But then again you could store anything else, also.

My question is this:

Why not split out the array of lists into a table of its own, and reference
the lists via a FK? In short, what can you do with lists that you can't do
just as conveniently with tables?

David Cressey wrote:
No, Firebird supports "array" as a distinct datatype. Arrays are
logical datatypes in Firebird. They are not the same thing as physical
blob segments.

Internally, arrays are implemented as blobs, but there are SQL
extensions to declare logical arrays with multiple dimensions. There is
also expression syntax for referencing single elements or "slices" which
are sub-arrays (e.g. "elements 4 through 6").

I'm pretty confident I know what I'm talking about in this case. I
worked for InterBase for five years. Firebird is based on a forked
source tree of InterBase 6.0 beta.

Right; using a normalized table structure is a better solution. That
was the point of my post.

Regards,
Bill K.

"Bill Karwin" <bill (AT) karwin (DOT) com> wrote

Thanks for clearing that up. I've never worked with Firebird or Interbase.
I downloaded Firebird
about a month ago, but I haven't gotten around to fooling around with it.
There are some ways in which it's reminiscent of Rdb. When I mentioned
BLOBS, I figured they were like Rdb BLOBS.


Agreed. While I don't take normalization as some sort of holy grail, there
are some rpoblems it deals with neatly. This appears to be one of them.

David Cressey wrote:
It wouldn't be surprising, because the same software architect developed
both RDB/ELN and InterBase! :-)

http://www.netfrastructure.com/main....b&o=management

Regards,
Bill K.

Andreas Kochenburger wrote:
--The IBM U2 databases (UniVerse and UniData)
www-306.ibm.com/software/data/u2 are the leaders in MultiValue category
of databases
--An open source product with the same MultiValue model is OpenQM
--There are several other MultiValue databases including jBASE and
Revelation
--Cache' from InterSystems is another possibility, supporting the
Multivalue Model and the multi-dimensional MUMPS model (in addition to
SQL)

best wishes. --dawn

Andreas Kochenburger wrote:

PostgreSQL has an array data type and several language extensions to assist
in using them.

However, I would echo all of Bill's warning, with this conclusion about
putting lists into columns: "Abandon hope all ye who enter here."
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)

Thanks to all for your responses.

However Prolog is a powerful programming language and command environment,
and one of its key features is indeed the built-in database which can
"digest" lists. I say: "Have hope all ye who enter here!"

It works! ;-))
I am just looking for such a database without the other Prolog stuff.

Andreas Kochenburger wrote:
Much to the chagrin of many relational proponents, eh?

You might be interested in reading the new IDC white paper entitled
"Because Not All Data is Flat: IBM's U2 Extended Relational DBMSs".
(I'm not sure why he opted to use the oft inflammatory "flat" word
except that the subject will trip emotions whether he uses such terms
or not, it seems.)

This paper is posted on the right had side of the U2 home page:
http://www.ibm.com/software/data/u2 and I think the URL is

ftp://ftp.software.ibm.com/software/...ers/202452.pdf

You can see that the U2 products are positioned along with other
not-exactly-relational dbms's as an "embedded" database. However, many
companies use such RDBMS's for enterprise systems, so this is a bit of
a misleading term.

Cheers! --dawn