[katello-devel] Katello schema - documentation and questions

[Bryan Kearney]

On 07/27/2012 10:29 AM, Scott Seago wrote:
> On 07/27/2012 04:14 AM, 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.
> Note that, with rails, direct SQL modification can result in
> inconsistent database even with foreign keys. This bypasses validation,
> hooks, etc. I know for a fact that direct SQL modification will result
> in invalid data with conductor on issues unrelated to foreign keys --
> I'd suspect katello is similar.
>
> We need to be clear in our documentation that customers (and TAMs!) need
> to use the rails console if they need to manipulate data outside of the
> app. Basically for rails/ActiveRecord, "rails console" == "psql" --
> anything else will corrupt data!
>
> That said, I'm still in favor of using actual database foreign keys (as
> long as we do it in a database-independent way -- i.e. works for PG,
> mysql, sqlite, which I'm assuming the below gems will do).


We hit this in candlepin recently. What we also learned with postgres is 
that foreign keys do not give you indexes by default.

-- bk