[katello-devel] Katello schema - documentation and questions

[Hugh Brock]

On Wed, Aug 01, 2012 at 04:57:48PM +0200, Lukas Zapletal wrote:
> Ok so it's doable. What is your approach in Cloud Engine by the way?
> 
We're in the same boat you guys are. As in, "Hey, one of these days we
really ought to go add a whole bunch of foreign keys and indexes to
the database..."

--H


> 
> On Wed, Aug 01, 2012 at 08:33:22AM -0400, Hugh Brock wrote:
> > 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
> > 
> > _______________________________________________
> > 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