[pacman-dev] Hypotetic pacman's db structure (SQL)
Hello to all. I'm writing an example databe structure for pacman db ( may be useful when someone decide to use SQLite as backend, and not simple text file). [1] Tell me your impression, if something can be made, revisioned or deleted. Views isn't complete, because I need some data stored in the db to test it; and, of course, I'm writing a simple script that parse all packages information stored under /var/lib/pacman to this db schemas ( if anyone wants help, it's welcome :D ) Thanks [1] http://rafb.net/p/XxK62l68.html (Version 0.01) -- JJDaNiMoTh - ArchLinux Trusted User
On Nov 9, 2007 10:26 AM, JJDaNiMoTh <jjdanimoth@gmail.com> wrote:
may be useful when someone decide to use SQLite as backend, and not simple text file
Ugh. I don't want to do this, personally. You're welcome to submit patches, but it's such a bad idea. Why do people like sqlite for this? BDB is probably a much better solution, for the record. A solution is not good simply because it supports SQL syntax.
Aaron Griffin wrote:
On Nov 9, 2007 10:26 AM, JJDaNiMoTh <jjdanimoth@gmail.com> wrote:
may be useful when someone decide to use SQLite as backend, and not simple text file
Ugh. I don't want to do this, personally. You're welcome to submit patches, but it's such a bad idea.
Why do people like sqlite for this? BDB is probably a much better solution, for the record. A solution is not good simply because it supports SQL syntax.
Well you seem to be changing your mind quite often here. You first said you hated SQLite, then said it might be somewhat decent... But that doesn't matter anyway. I prefer SQLite over BDB because I know SQL and it's relatively easy to use. Plus, anyone can access the sqlite db without needing to use pacman's API (should it be integrated). On the other hand, BDB has an unusual license clause (check the wikipedia entry for BDB) which may or may not go well with pacman. On to the actual SQL part: I'm impressed. Many people talk about wanting to do it, yet few come up with a good DB. Here are my comments: 1. What is the point of the table "groups"? Seems awfully redundant to me. 2. Some of the columns I don't get. What is depends.{major,same}? Can you provide an example? 3. Have you thought about indexing? I strongly suggest indexing name.{name,desc} at least. 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!) Great job on coming up with a schema! As soon as I graduate, I plan on hacking pacman for sqlite support... - tardo
On Nov 9, 2007 1:39 PM, tardo <tardo@nagi-fanboi.net> wrote:
Well you seem to be changing your mind quite often here. You first said you hated SQLite, then said it might be somewhat decent...
Citation? In all my history of knowing about sqlite, I have hated it, for the sheer fact that they do not support standard SQL (a standard from 1992 IIRC).
Plus, anyone can access the sqlite db without needing to use pacman's API (should it be integrated).
Do we want this though? If we're going to go far enough to make a binary DB like this, shouldn't we enforce some access standards? It only seems right to me.
On to the actual SQL part: I'm impressed. Many people talk about wanting to do it, yet few come up with a good DB. Here are my comments:
I'll bite here. First off, the reason I hate sqlite is because I actually know this stuff, and know it well. schema points: 1) splitting the description out isn't going to gain anything. I'd recommend against it 2) optional deps are missing (new feature for 3.1) 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
On Nov 9, 2007 1:49 PM, Aaron Griffin <aaronmgriffin@gmail.com> wrote:
On Nov 9, 2007 1:39 PM, tardo <tardo@nagi-fanboi.net> wrote:
Well you seem to be changing your mind quite often here. You first said you hated SQLite, then said it might be somewhat decent...
Citation? In all my history of knowing about sqlite, I have hated it, for the sheer fact that they do not support standard SQL (a standard from 1992 IIRC).
Dan found this: http://bbs.archlinux.org/viewtopic.php?pid=77668#p77668 However... errm, take a look at the date: 2005-03-29
JJDaNiMoTh wrote:
Hello to all.
I'm writing an example databe structure for pacman db ( may be useful when someone decide to use SQLite as backend, and not simple text file). [1]
Tell me your impression, if something can be made, revisioned or deleted. Views isn't complete, because I need some data stored in the db to test it; and, of course, I'm writing a simple script that parse all packages information stored under /var/lib/pacman to this db schemas ( if anyone wants help, it's welcome :D )
Thanks
[1] http://rafb.net/p/XxK62l68.html (Version 0.01)
------------------------------------------------------------------------
_______________________________________________ pacman-dev mailing list pacman-dev@archlinux.org http://archlinux.org/mailman/listinfo/pacman-dev
What's a text data type in SQLite? char or varchar? The text field sizes should match the current database schema[1]. I would put name.repository in a separate table (like group). description.id_{replaces,conflicts,provides} only allows for a one-to-one relationship, I can think of several packages (e.g. kernel26) that replace/conflict/provide multiple packages. I would replace those 3 fields with a table similar to depends, with the fields id, type, id_name, id_dep. type would be an integer representing the type of dependence e.g. 0 = replace, 1 = conflict, 2 = provide. I would combine the name and description tables. I don't think splitting them provides much benefit, it only adds more complexity to the SQL statements. (I've never used SQLite before so there could be benefits I'm unaware of) What do depends.{major,same} mean? [1] http://projects.archlinux.org/git/?p=pacman.git;a=blob;f=lib/libalpm/package... Andrew
On Fri, 9 Nov 2007 17:26:01 +0100 JJDaNiMoTh <jjdanimoth@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
On Fri, Nov 09, 2007 at 05:26:01PM +0100, JJDaNiMoTh <jjdanimoth@gmail.com> wrote:
I'm writing an example databe structure for pacman db ( may be useful when someone decide to use SQLite as backend, and not simple text file). [1]
last year i writed such a structure, but it was much more complex: http://git.frugalware.org/gitweb/gitweb.cgi?p=vmexam.git;a=blob;f=sql/pacman... and to be honest i must say i agree with Aaron about using an sql-based db for such a lowlevel purpose (pkgdb) would be something like using curl in libpacman - VMiklos
participants (5)
-
Aaron Griffin
-
Andrew Fyfe
-
JJDaNiMoTh
-
Miklos Vajna
-
tardo