dubuntu-team team mailing list archive

[Charl Wentzel <charl.wentzel@xxxxxxxxxxxxxx>]

Hi Jay

I had some thoughts on the database.  To my surprise I discovered that
it is far simpler than I initially thought!  The core of the database
could be as little as three tables:

Package:
 - package_id (long) - autonumber, primary key
 - package_name (char 30)
 - description (text)
 - repository (text)

This basically duplicates your apt-repository so it easy to access with
queries.  

The package_name, description and repository fields copies the apt data.
The package_id is a autonumber field for use as primary key... database
best practice, don't use names as primary keys.

Category:
 - category_id (long) - autonumber, primary key
 - parent_id (long)
 - category_name (char 30)
 - description (text)
 - is_parent (bool)
 - is_package_group (bool)
 
This is the stuff we are creating.  This table contains all search
categories, including package groups.

Category_name and description is the details we assign.  parent_id is
used to construct the parent-child relationship in the search category
and search results tree views.

is_parent is just a helper field to make it easier to find the end nodes
in the tree.

is_package_group indicates that it is a tree element that will be used
in the search results window to categorize the search results.

Package_Category:
 - Package-id (long)
 - Category-id (long)
 
This is nothing more than a link table.  It links the packages to the
categories/package groups/search categories.

That's it!!


To demonstrate how this could be used in the gui here's a bit of seudo
code.  This is not the only way it could be done, and ther is probably
more efficient ways, but it does illustrate how the database is used.

A. Building the search tree

// Single recursive all starting with root (= NULL)
BuildTree( NULL );

func BuildTree( long $parent_id )
{
  $branch-list = Select * from category where parent=$parent

  foreach $branch in $branch-list
  {
    // Add the branch to the tree
    AddtoTree( $parent, $branch );

    // Create child branches - recursive
    BuildTree( $branch )
  }
}

B. Return search results (not categorised)

// Fetch all selected items from root branches of Search Tree
$selected-target-env    = GetSelected( "target environment");
$selected-language      = GetSelected( "language");
$selected-tool-category = GetSelected( "tools category");

// Fetch all "end-nodes" of package group tree
$package-group-list =
   Select unique category_name, category_id, description
     from category
     inner join package 
       on (package.package_id = package_category.package_id)
     where isnull(is_parent) AND
       (package_category in $selected-target-env) AND
       (package_category in $selected-language) AND
       (package_category in $selected-tool-category)

Show( $package-group-list )

To show it categorised you'll first have to build the package-group
tree, but I'm not going to try and do it now to limit the size of this
e-mail.  Basically the same idea as the search category branches above
but with the added where clause: "(is_package_group = true) AND..."

etc, etc, etc...

So three simple tables is enough to do all the stuff.  And the API
(among other things) will be a simple wrapper for queries like this so
that whoever designs a gui won't have to mess with the database
directly.

What do you think?

Regards
Charl