[katello-devel] Katello schema - documentation and questions

[Miroslav Suchy]

While working on APIDocumentationEfforts [1] I wanted some nice 
documentation of our schema. I find we have none, so I created one:
   http://miroslav.suchy.cz/katello/katelloschema.html
It is created by PostgreSQL Autodoc [2].

To my surprise it does not contain foreign keys and very few indexes.
First I thought it is bug in Autodoc. But then - to my horor - I find it 
is not bug. We do not have foreign keys and indexes on most tables!

So we have two issues.

1) Indexes:
E.g. table user_notices, which purpose is IMHO always to live between 
two JOINS and is school example where indexes should be used, do not 
have indexes.
I can go through schema and suggest where we should create indexes.
I do not expect that it would cause any problem.
Objections here?

2) Foreign keys:
I read about foreign keys and ruby and I learned that community around 
Rails think that application level is best/enough to keep data 
consistency. So I expect a lot of discussion here.
Let me share my point of view here:
IMO data consistency should be work of database engine.
There are two examples:
a) when our code is fatally interrupted. E.g.

src/app/models/glue/pulp/repo.rb:
   def destroy_repo_orchestration
     pre_queue.create(:name => "remove product content : #{self.name}", 
:priority => 1, :action => [self, :del_content])
[.. FATAL CRASH HERE ...]
     pre_queue.create(:name => "delete pulp repo : #{self.name}", 
:priority => 2, :action => [self, :destroy_repo])
   end

You will end up with repository with product id, which does not exist.
Foreign keys with ON DELETE CASCADE solve this.

b) But even if we solve all issues in our code (e.g start using 
transactions), there is problems with humans.
During my 5 years in RHN Satellite team I seen non trivial numbers of 
Customers issues, where either customer or even TAM(!) run directly SQL 
commands, which without foreign keys would result in inconsistent database.
Using foreign keys seems doable:
  https://github.com/matthuhiggins/foreigner
  https://github.com/jenseng/immigrant
So it should be basically two gems, few lines of code and some testing.

Your opinions?


[1] https://fedorahosted.org/katello/wiki/APIDocumentationEfforts
[2] http://www.rbt.ca/autodoc/ (available in Fedora as package)

Mirek