dubuntu-team team mailing list archive

[Ryan Macnish <nisshh@xxxxxxxxxxx>]

Interesting, i cant really offer any suggestions since i dont know much C++ but im sure Jay will have a say.

> From: charl.wentzel@xxxxxxxxxxxxxx
> To: dubuntu-team@xxxxxxxxxxxxxxxxxxx
> Date: Sun, 12 Jul 2009 15:31:09 +0200
> Subject: [Dubuntu-team] Database design
> 
> 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
> 
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~dubuntu-team
> Post to     : dubuntu-team@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dubuntu-team
> More help   : https://help.launchpad.net/ListHelp

_________________________________________________________________
Need a new model in your life? Sell your car fast.
http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fninemsn%2Edomain%2Ecom%2Eau%2F%3Fs%5Fcid%3DFDMedia%3ANineMSN%5FHotmail%5FTagline&_t=774152450&_r=Domain_tagline&_m=EXT