[SQL-CVS] [ sqlobject-Patches-1353728 ] define foreign keys in Style

[SourceForge.net <no...@so...>]

Patches item #1353728, was opened at 2005-11-11 08:20
Message generated for change (Comment added) made by linux4u
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540674&aid=1353728&group_id=74338

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: None
Group: None
Status: Open
Resolution: Invalid
Priority: 5
Submitted By: Pau Aliagas (linux4u)
Assigned to: Oleg Broytmann (phd)
Summary: define foreign keys in Style

Initial Comment:

I don't like to use attrID all along the code to access related tables, I want to be able use the nomeclature I like taking advantage of the nice Style idea. This is especially true when you create RelatedJoins when you use a nomeclature like I do (table.id_table), but it's the same for all the nomeclatures you can imagine.

In styles.py it warns:

    def instanceAttrToIDAttr(self, attr):
        # @@: Right now, because of how names are created for foreign
        # keys, you can't really change this style.
        return attr + "ID"

With this patch you can define how you want the foreign keys generated, they won't be mandatorily generated as attrID.

Example:

class LongStyle(Style):
        """
        This style is derived from Style, that does not do any translation,
        but constructs the table ids like id_table.
        """
        def instanceAttrToIDAttr(self, attr):
                return "id_" + attr

        def tableReference(self, table):
                # str.rsplit is python 2.4+ only
                if version_info >= (2, 4):
                        return "id_" + table.rsplit(".", 1)[-1]
                else:
                        return "id_" + rsplit(table, ".", 1)[-1]


class mm_server(SQLObject):
        class sqlmeta:
                table = "smsarena.mm_server"
                style = LongStyle(longID = True)
        _connection = conn_smsarena
        server = StringCol(alternateID = True, varchar = True, length = 255, alternateMethodName = "by_server")
        tz = StringCol(length = 20, default = 'CET', notNone = True)
        conutry_code = StringCol(length = 2, varchar = False, default = '', notNone = True)
        # until now I had to do this
        # mm_server_aliases = SQLMultipleJoin("mm_server_alias", joinColumn = "mm_serverID", orderBy = "server")
        # now it's perfect!
        mm_server_aliases = SQLMultipleJoin("mm_server_alias", orderBy = "server")

class mm_server_alias(SQLObject):
        class sqlmeta:
                table = "smsarena.mm_server_alias"
                style = LongStyle(longID = True)
        _connection = conn_smsarena
        # until now I had to do this
        # mm_server = ForeignKey("mm_server", dbName = "id_mm_server")
        mm_server = ForeignKey("mm_server")
        server = StringCol(alternateID = True, varchar = True, length = 255, alternateMethodName = "by_server")

I hope it's clear :)

Pau

----------------------------------------------------------------------

>Comment By: Pau Aliagas (linux4u)
Date: 2006-02-09 17:42

Message:
Logged In: YES 
user_id=140558

I'm very busy but again will try to find some time to fix it.
I'm using it every day without a glitch, even using the
clause attributes like in the Complex example (new type of
joins *toMany).
I'll update it to latest SVN just in case this is the
problem, I did it with that day's current.

Please do not close it, I really intend to pass the tests,
I'm already using it.

----------------------------------------------------------------------

Comment By: Oleg Broytmann (phd)
Date: 2006-02-09 17:11

Message:
Logged In: YES 
user_id=4799

test_auto.py, for example fails:
    def test_dynamicJoin(self):
        col = KeyCol('personID', foreignKey='Person')
        Phone.sqlmeta.addColumn(col, changeSchema=True)
        join = MultipleJoin('Phone')
        Person.sqlmeta.addJoin(join)
        for phone in Phone.select('all'):
            if phone.phone.startswith('555'):
                phone.person = Person.selectBy(name='tim')[0]
            else:
                phone.person = Person.selectBy(name='bob')[0]
        l = [p.phone for p in
Person.selectBy(name='tim')[0].phones]
        l.sort()
E       assert l == ['555-394-2930', '555-555-5555']
>       assert [] == ['555-394-2930', '555-555-5555']

[sqlobject/tests/test_auto.py:61]


----------------------------------------------------------------------

Comment By: Pau Aliagas (linux4u)
Date: 2006-01-30 19:10

Message:
Logged In: YES 
user_id=140558

I'll recheck it, it really passed all the tests applied to
the latest svn. I tell you something tomorrow.

----------------------------------------------------------------------

Comment By: Pau Aliagas (linux4u)
Date: 2006-01-30 19:07

Message:
Logged In: YES 
user_id=140558

I'll recheck it, it really passed all the tests applied to
the latest svn. I tell you something tomorrow.

----------------------------------------------------------------------

Comment By: Oleg Broytmann (phd)
Date: 2006-01-30 18:57

Message:
Logged In: YES 
user_id=4799

test_auto.py, test_auto_old.py and test_basic.py failed.

----------------------------------------------------------------------

Comment By: Pau Aliagas (linux4u)
Date: 2006-01-18 17:04

Message:
Logged In: YES 
user_id=140558

This is the final patch against latest svn, it includes
everything in one single patch. It passes the tests.

----------------------------------------------------------------------

Comment By: Pau Aliagas (linux4u)
Date: 2006-01-18 14:17

Message:
Logged In: YES 
user_id=140558

Ok, I have taken my time to understand pydist, test, etc,
and here I am back with the working solution :)

Now the resulting SQLobject passes all the tests.

I attach an incremental patch so that you can see more
clearly the changes done to fix the previous one.

I've needed a new method in the Style class,
instanceIDAttrToAttr, that is the complementary to the
already known instanceAttrToIDAttr. If you need to transform
back and forth field names, you need them both.

I haven't tried to port it to the latest svn yet, this is
against the same release than the first patch, but it should
apply more or less clearly as this are very isolated areas
in the code. When I do it I'll post it here.

Sorry for the delay, it has not been easy for me to
understand more deeply the code and, even less, to know how
the tests work :)

----------------------------------------------------------------------

Comment By: Pau Aliagas (linux4u)
Date: 2006-01-03 13:18

Message:
Logged In: YES 
user_id=140558

It fails because the test is not right, it still has the ID
postfix hardcoded. My fault, I did no send a patch for
tests. This is the piece that fails:

    # Select by usual way
    s = TestWorkKey.selectBy(composerID=c.id,
title='Symphony No. 9')

I have not looked at tests until now, but it looks like
there should be tests for the different provides styles, in
this case, if you accept the patch, at least a test for the
LongStyle provided and for the current naming styles.

I'll try to cook up something, but it can take a little
time. Any guidelines will be appreciated (I've just read the
docs ;)

----------------------------------------------------------------------

Comment By: Oleg Broytmann (phd)
Date: 2006-01-03 12:23

Message:
Logged In: YES 
user_id=4799

test_selectBy_foreignKey.py fails:

    def test1():
        setupClass([TestComposerKey,
                    TestWorkKey])
    
        c = TestComposerKey(name='Mahler, Gustav')
>       w1 = TestWorkKey(composer=c, title='Symphony No. 9')

[/home/phd/work/SQLObject/SQLObject-patches/sqlobject/tests/test_selectBy_foreignKey.py:19]
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
_ _ _ _ _ _ _ _ _ _ 

    def __init__(self, **kw):
        # The get() classmethod/constructor uses a magic keyword
        # argument when it wants an empty object, fetched
from the
        # database.  So we have nothing more to do in that case:
        if kw.has_key('_SO_fetch_no_create'):
            return
    
        post_funcs = []
        self.sqlmeta.send(events.RowCreateSignal, kw,
post_funcs)
    
        # Pass the connection object along if we were given one.
        if kw.has_key('connection'):
            self._connection = kw['connection']
            self.sqlmeta._perConnection = True
            del kw['connection']
    
        self._SO_writeLock = threading.Lock()
    
        if kw.has_key('id'):
            id = self.sqlmeta.idType(kw['id'])
            del kw['id']
        else:
            id = None
    
>       self._create(id, **kw)

[/home/phd/work/SQLObject/SQLObject-patches/sqlobject/main.py:1210]
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
_ _ _ _ _ _ _ _ _ _ 

    def _create(self, id, **kw):
    
        self.sqlmeta._creating = True
        self._SO_createValues = {}
        self._SO_validatorState = SQLObjectState(self)
    
        # First we do a little fix-up on the keywords we were
        # passed:
        for column in self.sqlmeta.columnList:
    
            # Then we check if the column wasn't passed in, and
            # if not we try to get the default.
            if not kw.has_key(column.name) and not
kw.has_key(column.foreignName):
                default = column.default
    
                # If we don't get it, it's an error:
                if default is NoDefault:
E                   raise TypeError, "%s() did not get
expected keyword argument %s" % (self.__class__.__name__,
column.name)
>                   TypeError: TestWorkKey() did not get
expected keyword argument composerID

[/home/phd/work/SQLObject/SQLObject-patches/sqlobject/main.py:1231]
- - - - - - - - - - - - -  test1: recorded stdout - - - - -
- - - - - - - - - 
 1/QueryOne:  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_work_key'
 1/QueryR  :  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_work_key'
 1/COMMIT  :  auto
 1/QueryOne:  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_composer_key'
 1/QueryR  :  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_composer_key'
 1/COMMIT  :  auto
 1/QueryOne:  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_work_key'
 1/QueryR  :  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_work_key'
 1/COMMIT  :  auto
 1/QueryOne:  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_composer_key'
 1/QueryR  :  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_composer_key'
 1/COMMIT  :  auto
 1/QueryOne:  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_composer_key'
 1/QueryR  :  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_composer_key'
 1/COMMIT  :  auto
 1/Query   :  CREATE TABLE test_composer_key (
    id SERIAL PRIMARY KEY,
    name TEXT
)
 1/QueryR  :  CREATE TABLE test_composer_key (
    id SERIAL PRIMARY KEY,
    name TEXT
)
 1/COMMIT  :  auto
 1/QueryOne:  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_work_key'
 1/QueryR  :  SELECT COUNT(relname) FROM pg_class WHERE
relname = 'test_work_key'
 1/COMMIT  :  auto
 1/Query   :  CREATE TABLE test_work_key (
    work_id SERIAL PRIMARY KEY,
    composer_id INT,
    title TEXT
)
 1/QueryR  :  CREATE TABLE test_work_key (
    work_id SERIAL PRIMARY KEY,
    composer_id INT,
    title TEXT
)
 1/COMMIT  :  auto
 1/Query   :  ALTER TABLE test_work_key ADD CONSTRAINT
composer_id_exists FOREIGN KEY (composer_id) REFERENCES
test_composer_key (id) 
 1/QueryR  :  ALTER TABLE test_work_key ADD CONSTRAINT
composer_id_exists FOREIGN KEY (composer_id) REFERENCES
test_composer_key (id) 
 1/COMMIT  :  auto
 1/QueryIns:  INSERT INTO test_composer_key (id, name)
VALUES (1, 'Mahler, Gustav')
 1/COMMIT  :  auto
 1/QueryOne:  SELECT name FROM test_composer_key WHERE id = 1
 1/QueryR  :  SELECT name FROM test_composer_key WHERE id = 1
 1/COMMIT  :  auto

----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=540674&aid=1353728&group_id=74338