[OpenSER-Devel] Re: [OpenSER-Users] New db_berkeley module

[Henning Westerholt]

On Friday 12 October 2007, William Quan wrote:
> Apparently there is much discussion on the topic of 'natural key' vs.
> 'surrogate key' in the DB world out there :) .
> I spent a just a few minutes reviewing basic arguements on
> [http://en.wikipedia.org/wiki/Surrogate_key ].
> Having read this, I am more curious and will have to go back and look
> for old threads in this group to learn what your motivations are.
> From what I read so far, I don't think you are supposed to use the id in
> application queries; that was a misunderstanding on my part.
> Further, I should have clarified by concisely stating that db_berkeley
> relies on a 'natural key' schema. As a consequence, I considered each
> table and attempted to define the 'natural' keys for that table. I may
> have made some mistakes along the way. METADATA_KEY indicates which
> columns comprise the 'natural' (composite) key.

Hello William,

interesting link. My database theory lectures were way to long ago.. ;-)

> >> 2. cursor() - where you iterate over each row, do the join on any
> >> columns you want, and create a result set.
> >> As implemented, without the id columns, the queries are implemented with
> >> get() which implies a natural join, or exact string equality on the
> >> 'key', which is in most cases a composite key comprised of the
> >> METADATA_KEY columns seperated by a delimiter.
> >
> > It is not possible to use only one key of the set instead of all? E.g.
> > use only the username, or the id?
>
> Consistency is the key here. If you are inserting data into table
> subscriber with a key like 'wiquan|domain.com', then  you cant later
> query with only the username. The db_berkeley module will fill in any
> missing keys with 'NULL' so the actual key used for the get() would look
> like 'wiquan|NULL' which would not yield a result row.

Ah, ok. Perhaps we should add a note about this to the module documentation. 
So its not possible later on to change the use_domain parameter if you want 
to keep your existing database content.

> > [...]
> >> Aside, I started looking at the code for the openserctl cmds today, and
> >> I think I need to add some fifo cmds to the modules since openser is
> >> actually running at the time the openserctl util is being invoked. This
> >> means the DBs are open and some data may not be commited to disk, etc. I
> >> thought I'd use the carrierroute module as the starting example for
> >> implemented such fifo commands, but I need a few more days to get all
> >> those command implemeted/tested. I will continue on this path over the
> >> next few days, such that there will be parity between the db modules
> >> from the perspective of the openserctl cmds.
> >
> > Ok, so you want to implement some kind of "flush data" parameter?
>
> Yes, something like that. How do you feel about a 'reload' cmd, which is
> a close() that flushes the cache to disk, followed by an open().
> But there are other 'core' cmds that will require a live db such as:
> 1. add
> 2. rm
> 3. showdb

The dbtext module notices db changes in the background, and reload it content 
automatically. Don't know if this is possible with berkeley db, so a reload 
fifo command is fine too. Yes, for the openserctl command you need the 
functionality you described.

Cheers,

Henning