[katello-devel] Katello schema - documentation and questions

[Mike McCune]

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
>