Re: Couple of questions...

        Hi Aaron,

"Aaron Freeman" <[email protected]> wrote:
> (1) Is there a debug flag somewhere that dump all SQL queries to the
> console?

Absolutely: set the environment variable MDL_ENABLE_DATABASE_LOGGING to
any true value. For details, see:
  http://modeling.sourceforge.net/UserGuide/env-vars-core.html


> (2) How are nested joins accomplished?
> For example, in the AuthorBooks schema, lets assume there is another table
> called publisher that has a 1-M relationship with books, like so (sorry
> about the formatting):
> 
[...
  Writer <-author----books->> Book <<-books----publisher-> Publisher
 ...]

> How would I get writers by publisher?
> 
> More specifically, is there an elegant way to do a nested join in a single
> query, or do I just have to do a single writer-book join and then iterate
> over the resultset for the second join?
> 

Yes, there is a dedicated way of doing this: use the dotted notation:

  >>> ec.fetch('Writer', 'books.publisher.bizName=="P1"')

returns every writer who published (at least) a book w/ publisher P1.

The generated SQL query is (postgresql here):

  SELECT DISTINCT t0.ID, t0.LAST_NAME, t0.FIRST_NAME, t0.AGE,
         t0.FK_WRITER_ID, t0.BIRTHDAY 
  FROM WRITER t0 
  INNER JOIN  ( BOOK t1
                INNER JOIN PUBLISHER t2 
                ON t1.FK_PUBLISHER=t2.ID )
  ON t0.ID=t1.FK_WRITER_ID
  WHERE t2.BIZ_NAME = 'P1';


> Code examples would be great!

  I'm including in the end of the message a PyModel named EAuthorBooks
  (E stands for Extended :) derived from the one used in the User's
  Guide, with sample data, demonstrating the approach.

        Regards,

        
-- Sébastien.



PS: you'll need to setup a database before running the example, e.g. w/
    postgresql:

   $ mdl_generate_DB_schema.py \
        --admin-dsn "localhost:template1:postgres:" \
        -v -C pymodel_extAutborBooks.py


------------------------------------------------------------------------
#! /usr/bin/env python
# -*- coding: iso-8859-1 -*-
"Extended AuthorBooks model, with a Publisher"

from Modeling.PyModel import *

##
# Defaults
AFloat.defaults['precision'] = 10
AFloat.defaults['scale'] = 2
AString.defaults['width'] = 40

Association.defaults['delete']=['nullify', 'nullify']

Entity.defaults['properties'] = [
  APrimaryKey('id', isClassProperty=0, isRequired=1, doc='PK')
]

##
# Adapt to fit your own configuration!!

_connDict = {'database': 'EAUTHOR_BOOKS', 'host':'localhost',
             'user':'postgres','password':''}

model = Model('EAuthorBooks',adaptorName='Postgresql',
                connDict=_connDict)

model.doc = ' ... '
model.version='0.1'
model.entities = [
  #
  Entity('Book',
         properties=[ AString('title', isRequired=1, columnName='title'),
                      AFloat('price'),
                       ],
         ),
  Entity('Writer',
         properties=[ AString('lastName',isRequired=1, width=30 ),
                      AString('firstName'),
                      AInteger('age', displayLabel='Age'),
                      ADateTime('birthday', usedForLocking=0),
                      ]
         ),
  Entity('Publisher',
         properties=[ AString('bizName',isRequired=1),
                      AString('location'),
                      ]
         ),
  ]
#---
model.associations=[
  Association('Book', 'Writer',
              relations=['author', 'books'],
              delete=['nullify', 'cascade'],
              keys=['FK_Writer_Id', 'id']),
  Association('Writer', 'Writer',
              relations=['pygmalion', None],
              delete=['nullify', None],
              keys=['FK_Writer_id', 'id']),
  Association('Book', 'Publisher', relations=['publisher', 'books'])
  ]

model.build()
model=model.component

if __name__=="__main__":
  #import pdb ; pdb.set_trace()
  
  from Modeling.dynamic import build
  build(model, define_properties=1)

  from Modeling.ModelSet import defaultModelSet
  defaultModelSet().addModel(model)

  from Modeling.EditingContext import EditingContext
  ec=EditingContext()

  from EAuthorBooks.Publisher import Publisher
  from EAuthorBooks.Writer import Writer
  from EAuthorBooks.Book import Book

  # Initialization
  p1=Publisher(); p1.bizName="P1"
  p2=Publisher(); p2.bizName="P2"

  w1=Writer(); w1.lastName="in p1"
  w2=Writer(); w2.lastName="in p2"
  w3=Writer(); w3.lastName="in p1 and p2"
  
  b1w1=Book(); b1w1.title="b1w1"
  b2w1=Book(); b2w1.title="b2w1"
  b1w2=Book(); b1w2.title="b1w2"
  b2w2=Book(); b2w2.title="b2w2"
  b1w3=Book(); b1w3.title="b1w3"
  b2w3=Book(); b2w3.title="b2w3"
  ec.insert(p1);
  ec.insert(p2); ec.insert(w1); ec.insert(w2); ec.insert(w3)
  ec.insert(b1w1); ec.insert(b2w1); ec.insert(b1w2); ec.insert(b2w2)
  ec.insert(b1w3); ec.insert(b2w3)

  w1.addToBooks(b1w1); b1w1.author=w1; w1.addToBooks(b2w1); b2w1.author=w1
  w2.addToBooks(b1w2); b1w2.author=w2; w2.addToBooks(b2w2); b2w2.author=w2
  w3.addToBooks(b1w3); b1w3.author=w3; w3.addToBooks(b2w3); b2w3.author=w3

  b1w1.publisher=p1; p1.addToBooks(b1w1)
  b2w1.publisher=p1; p1.addToBooks(b2w1);
  b1w2.publisher=p2; p2.addToBooks(b1w2)
  b2w2.publisher=p2; p2.addToBooks(b2w2);
  b1w3.publisher=p1; p1.addToBooks(b1w3)
  b2w3.publisher=p2; p2.addToBooks(b2w3);

  ec.saveChanges()

  # query
  ec=EditingContext()
  print [w.lastName
         for w in ec.fetch('Writer', 'books.publisher.bizName=="P1"')]
  # returns: w1 and w3
  ec=EditingContext()
  print [w.lastName
         for w in ec.fetch('Writer', 'books.publisher.bizName=="P2"')]
  # returns: w2 and w3
------------------------------------------------------------------------



-------------------------------------------------------
This SF.Net email is sponsored by: IBM Linux Tutorials
Free Linux tutorial presented by Daniel Robbins, President and CEO of
GenToo technologies. Learn everything from fundamentals to system
administration.http://ads.osdn.com/?ad_id70&alloc_id638&op=click