I'm having trouble wrapping my head around a solution here. Any advice is appreciated.
I'm working on a SQLite-based app for keeping track of PC BIOS releases. One obvious requirement is to be able to track and document change history. So, using this sample schema/data code: create table bios_release ( oid integer primary key, parent_id integer, group_id integer, version text, bios_name text ); insert into bios_release values (1, null, 1, '1.10', 'PC1_v1.1'); insert into bios_release values (2, null, 2, '1.10', 'PC2_v1.1'); insert into bios_release values (3, 1, 1, '1.20', 'PC1_v1.2'); insert into bios_release values (4, null, 3, '1.10', 'PC3_v1.1'); insert into bios_release values (5, 3, 1, '1.30', 'PC1_v1.3'); /* irrelevant to the discussion, but provided for clarity (?) */ create table bios_group ( oid integer primary key, group_name text ); insert into bios_group values (1, 'PC1 Group'); insert into bios_group values (2, 'PC2 Group'); insert into bios_group values (3, 'PC3 Group'); I see two ways to go to know which releases. The obvious way is to simply maintain a history group with an ID number that gets stored in each member BIOS release record. A simple: select * from bios_release where group = 1 order by version desc; will get me all the members of the PC1 Group in descending order. That's fine, but, creating, naming, and maintaining the groups is a hassle for the administrators. The other option I'm considering is chaining each new bios_release to its predecessor by storing the predecessor OID in the parent_id column. Doing it this way makes some sense from the admin process POV because usually, the admin has a new BIOS with a set of known properties, and not necessarily any knowledge of what group it might belong to, or whether or not she needs to create a new group for this BIOS. So, finally, the question: What might the SQL look like to retrieve a list of predecessors for 'PC1_v1.3'? Sure, I could prepare a stmt in the app (Perl), and walk the chain in some fashion. Is this result even possible using plain SQL? TIA -Clark ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
