[katello-devel] Katello schema - documentation and questions

[Justin Sherrill]

+1

On 07/27/2012 12:10 PM, Mike McCune wrote:
> agreed, +1 to FKs and indexes
>
> On 07/27/2012 06:26 AM, Lukas Zapletal wrote:
>> +1 for adding foreign keys.
>>
>> LZ
>>
>> On Fri, Jul 27, 2012 at 10:14:21AM +0200, Miroslav Suchy wrote:
>>> 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
>>>
>>> _______________________________________________
>>> katello-devel mailing list
>>> katello-devel at redhat.com
>>> https://www.redhat.com/mailman/listinfo/katello-devel
>>
>
> _______________________________________________
> katello-devel mailing list
> katello-devel at redhat.com
> https://www.redhat.com/mailman/listinfo/katello-devel