[pacman-dev] Hypotetic pacman's db structure (SQL)

[JJDaNiMoTh]

On Fri, 9 Nov 2007 17:26:01 +0100
JJDaNiMoTh <jjdanimoth at gmail.com> wrote:

> Hello to all.
[cut]
> [1] http://rafb.net/p/XxK62l68.html (Version 0.01)

Ok, there's 2nd version [1].
Thank you for posting, excuse me if I don't reply at your mail but I don't want to spam this ml.
With this schema, creating view is simple:
- For main information, we will query the name table;
- For dependencies informations, we will query depends table like this:
SELECT name.name, dep.version [...] FROM name name, depends dep WHERE dep.id_name = ( 
SELECT id FROM name WHERE name = 'package') AND name.id = dep.id_name
( there can be errors ^_^ but the way is this )
- For conflicts, provides and replaces, the way is the same.

Some answer:
tardo:
> 1. What is the point of the table "groups"? Seems awfully redundant to me.
Yes, I removed it.

> 2. Some of the columns I don't get. What is depends.{major,same}?
> Can you provide an example?
How I can tell that foo package depends on boo>=3.2 ? This is the best way I've thinked..
If major is True, then the > is added; if same is true, = is added. ( It's clear that we need function.. * )

> 3. Have you thought about indexing? I strongly suggest indexing 
> name.{name,desc} at least.
Yes, but now I think it isn't the moment.. I want to have a solid structure and useful functions that work on it, later we can think about improving speed :)

> 4. AUR uses somewhat of a similar table for depends. Is there a better way
> to do this? (I can't think of any, but there has to be!)
I don't know how is AUR db. I will see its schema soon ( it can be useful ).

aaron:

> 1) splitting the description out isn't going to gain anything. I'd
> recommend against it
Yes, I removed it and merged with name table.

> 2) optional deps are missing (new feature for 3.1)
I don't know this feature; but an 'optional' field, boolean, is added.

> 3) this does not take the local db into account, so things like
> installdate are missing (nullable)
> 4) a majority of the "description" table should be nullable. pacman
> does not break if csize or isize are missing, for example
True, now these fields are nullable (but we need to have few nullable fields.. because, as I view database, a nullable field is useless). 
About local db account, you've see that, with this schemas, pacman needs to store in a text file the files owned by a package. I think that insert *all* files that are stored in filesystem on db, will make it too slowly. So, for this the text files are the best solutions (IMHO)

Andrew:
I add your table :D

* = I'm writing these functions with sqlite syntax. I work on Postgresql, using SQLite isn't natural for me. I hope that SQLite have all requisities that we need. 
I'm writing this schema with SQLite syntax because it's the smallest and powerful db system that I (little ) know.

Thanks to all

[1] http://rafb.net/p/5wnvht46.html Version 0.02

-- 
JJDaNiMoTh - ArchLinux Trusted User
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: not available
URL: <http://archlinux.org/pipermail/pacman-dev/attachments/20071109/18eb75c2/attachment.pgp>