Re: Simple persistent object storage: seeking advice on storing user defined, runtime defined abstract datatypes into Oracle

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 Received on Tue Nov 18 1997 - 00:00:00 CET