[katello-devel] Katello schema - documentation and questions

[Scott Seago]

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).
> 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