[katello-devel] Katello schema - documentation and questions

[Hugh Brock]

On Mon, Jul 30, 2012 at 12:02:57PM +0200, Lukas Zapletal wrote:
> By the way, what are the biggest concerns in regard to using foreign
> keys and rails? What is the issue?
> 
Heh... the issue is that Active Record is quite happy to define
relationships that would traditionally have required proper
referential constraints in a database, without defining any
constraints. So there is absolutely nothing stopping me (or a bug in
Active Record) from deleting rows in the DB that are referenced by
other rows, which can break your whole app horribly.

The solution is simply to add proper constraints in your
migrations. However most folks in the modern web-scale world seem
happy to live with this level of risk and don't bother with the
constraints, despite the fact that it makes us dinosaurs
uncomfortable...

--Hugh


> 
> 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
> 
> -- 
> Later,
> 
>  Lukas "lzap" Zapletal
>  #katello #systemengine
> 
> _______________________________________________
> katello-devel mailing list
> katello-devel at redhat.com
> https://www.redhat.com/mailman/listinfo/katello-devel

-- 
== Hugh Brock, hbrock at redhat.com                                   ==
== Engineering Manager, Cloud BU                                   ==
== Aeolus Project: Manage virtual infrastructure across clouds.    ==
== http://aeolusproject.org                                        ==

"I know that you believe you understand what you think I said, but I’m
not sure you realize that what you heard is not what I meant."
--Robert McCloskey