From: Jerry Bakin
Subject: Simple persistent object storage: seeking advice on storing user defined, runtime defined abstract datatypes into Oracle
Date:
Message-ID: <64fgsa$kle1@adventure.segasoft.com>
I need to store/retrieve *arbitrary* user defined abstract data into Oracle.
Well these data are LISP lists, meaning that the type and number of fields
can vary with each record.
LISP lists consist of any number of about 10 different types of data
including pointers, numbers, strings, arrays, boolean values, and structure
fields. I won't know until runtime, and in fact, the user can create new
datatypes during execution itself.
(You might think of a personal information manager where the user gets to
define their own favorite table by dragging and dropping names, addresses,
phone number fields, or even defining their own fields. This isn't at all
what we're doing, but it's somewhat analogous.)
For each record, it is important to capture the exact type of each field, as
well as the original order of that field within the record.
Each record probably has a alphanumeric name of some sort.
I need some advice on how to efficiently design a database.
It strikes me that I could:
Create a table of RecordHeaders with records of (RecordName, RecordID)
where
RecordName is a string, and
RecordID is a unique sequence used to point to field records
Create a table of RecordFields with records of (RecordID, FieldIndex,
FieldID) where
RecordID is an entry from RecordHeaders,
FieldIndex is a unique sequence starting from 1 capturing the order of
the field within the original record,
FieldID is a unique sequence used to point to field values
For each field type, I could create a table to store the field values.
Something like:
NumberValues with records of (FieldID, NumberValue), or
StringValues with records of (FieldID, StringValue), or
PairValues with records of (FieldID, Pointer1Value, Pointer2Value)
This will work, but when I suggest this to the Database Administrators their
face gets to looking all twisted and funny, and I generally get the
impression they are wishing me into the cornfield.
I want to make the DBA's happy, but I do have my requirements.
What would make for a more effective database design?
Thank you,
Jerry Bakin
······@segasoft.com
From: Kelly Murray
Subject: Re: Simple persistent object storage: seeking advice on storing user defined, runtime defined abstract datatypes into Oracle
Date:
Message-ID: <64ksvt$h8o$1@news2.franz.com>
> I need to store/retrieve *arbitrary* user defined abstract data into Oracle.
You've got big round pegs (objects) going into tiny square holes (tables)...
>It strikes me that I could:
> Create a table of RecordHeaders with records of (RecordName, RecordID)
Sure, it is mostly your only real choice. I've done such contortions.
You're DBA is quite right, it's horrible, and will perform similarly,
perhaps 2-3 orders of magnitude slower (and bigger) than storing square pegs,
and loses the power of an RDB, which is efficient sorting/indexing of columns.
And then you're colleages will surely point and say how slow LISP is.
You need round holes, an OODB, and in fact, a LISP OODB.
Franz has one. Another one you should look into is Heiko Kirschke's PLOB.
Your efficient alternative is to make the data square pegs,
and just have an O-O interface to the RDB data, and thus it
must all be statically typed and statically defined/compiled.
-Kelly
From: John Arley Burns
Subject: Re: Simple persistent object storage: seeking advice on storing user defined, runtime defined abstract datatypes into Oracle
Date:
Message-ID: <wzn2j2wwmy.fsf@urquan-kohr-ah.mesas.com>
Something we use successfully:
We have a hash-approach, where we store a bunch of varying key,value
pairs. This is done is two tables. The first is the root table:
root_table:
class,root_id
The class is not really an object class, but something like
'bank_report','credit_history'.
The root_id is a unique identifier for this instance of the class. For
efficiency, the root_id is globally unique (across all classes) from a
root_id_sequence, and is the primary key.
field_table:
root_id, name, type, value
The field table stores the root_id that links all the fields to a
single 'object', the type is a general datatype, and the value is the
value of the name key. The root_id,name is the primary key.
This is a good design (and actually deployed) for one-level-deep
(associative array) data.
If you want to implement the lisp cons-cell strategy, you would
probably want to do something like:
lisp_pair_table:
cell_id
cal_sym
car_num
car_str
car_ptr
cdr_sym
cdr_num
cdr_str
cdr_ptr
Where cell_id is a globally unique identifier (from a sequence),
*_sym are symbol names.
*_num and *_str are self-explanatory.
*_ptr is the cell_id of a different cell.
When you run sql you generally want to get everything in a single
query if possible so the optimizer can do its spifiness. Fortunately
Oracle provides the 'connect by' clause (we must assume the data has
no loops). For instance, if I want to select all the elements
(including sub-elements) of a pair with id=123, I could do:
select *
from lisp_pair_table
connect by prior car_ptr = cell_id
start with cell_id = 123
union
select *
from lisp_pair_table
connect by prior cdr_ptr = cell_id
start with cell_id = 123
If you want an even more 'lispish' approach you can have a secondary
'atom' table so:
lisp_atom:
atom_id
atom_sym
atom_num
atom_str
lisp_pair:
pair_id
car_atom_id
car_pair_ptr
cdr_atom_id
cdr_pair_ptr
Your query now becomes, for the pairs:
select *
from lisp_pair
connect by prior car_ptr = pair_id
start with pair_id = 123
union
select *
from lisp_pair
connect by prior cdr_ptr = pair_id
start with pair_id = 123
For the atoms:
select *
from lisp_atom
where atom_id =
any (
select car_atom_id
from lisp_pair
connect by prior car_id = pair_id
start with pair_id = 123
union
select cdr_atom_id
from lisp_pair
connect by prior cdr_id = pair_id
start with pair_id = 123
)
Provided there are no loops in your data, this should work quite
nicely at a reasonable speed.
Caveat Emptor:
I had a 24-ounce Foster's Lager before writing this!
pax et bonum