sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] DateTimeCol with microsecond resolution.

[Oleg B. <ph...@ph...>]

On Tue, Feb 01, 2011 at 01:54:03PM -0500, Ben Timby wrote:
> Will registering a converter work both
> ways? i.e. the value is transformed to string for storage and back
> again for retrieval?

   No, converters work one way - from python data type to SQL string.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] DateTimeCol with microsecond resolution.

[Ben T. <bt...@gm...>]

On Tue, Feb 1, 2011 at 1:24 PM, Oleg Broytman <ph...@ph...> wrote:
> On Tue, Feb 01, 2011 at 09:13:35PM +0300, Oleg Broytman wrote:
>> def DateTimeConverter(value, db):
>>     return "'%s'" % value.isoformat
>
>   Oops, sorry, a bug:
>
>    return "'%s'" % value.isoformat()
>
>>
>> registerConverter(datetime.datetime, DateTimeConverter)

Thank you as always Oleg for your prompt reply.

I will give this a try, meanwhile I have created my own column type:

--
class ISO8601Validator(validators.Validator):
   def from_python(self, value, state):
       if value is not None:
           value = value.strftime('%Y-%m-%d %H:%M:%S.%f')
       return value

   def to_python(self, value, state):
       if value is not None:
           value = datetime.datetime.strptime(value, '%Y-%m-%d %H:%M:%S.%f')
       return value

class SOISO8601Col(SOStringCol):
   def createValidators(self):
       return [ISO8601Validator()] + \
           super(SOISO8601Col, self).createValidators()

class ISO8601Col(StringCol):
   baseClass = SOISO8601Col
--

This seems to work for me. Will registering a converter work both
ways? i.e. the value is transformed to string for storage and back
again for retrieval?

Re: [SQLObject] DateTimeCol with microsecond resolution.

[Oleg B. <ph...@ph...>]

On Tue, Feb 01, 2011 at 09:13:35PM +0300, Oleg Broytman wrote:
> def DateTimeConverter(value, db):
>     return "'%s'" % value.isoformat

   Oops, sorry, a bug:

    return "'%s'" % value.isoformat()

> 
> registerConverter(datetime.datetime, DateTimeConverter)

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] DateTimeCol with microsecond resolution.

[Oleg B. <ph...@ph...>]

On Tue, Feb 01, 2011 at 12:26:34PM -0500, Ben Timby wrote:
> I really need to store date/time values with millisecond resolution.
> What would be the easiest way to achieve this? I found the following
> patch:
> 
> http://permalink.gmane.org/gmane.comp.python.sqlobject/5106
> 
> But I am loath to apply a patch to SQLObject since my code must be portable.

   You couldn't apply it anyway - it's too old.

> Must I create my own column type? Is there any way to override the
> storage method of DateTimeCol? I tried passing a datetimeFormat to the
> DateTimeCol.__init__() but this did not have the desired effect. I am
> assuming the TIMESTAMP data type of sqlite does not support this level
> of resolution? If so, I suppose I must store the date as a string in
> ISO 8601 format?

   You don't understand all subtle details of SQLObject (which is ok).
The architecture works this way: to convert data to an SQL string column
validator's .from_python() method is called, and whatever it returns
(including a simple string) is passed to converters (see converters.py).
   If you want to override datetime converter for all datetime columns -
just register your own converter:

def DateTimeConverter(value, db):
    return "'%s'" % value.isoformat

registerConverter(datetime.datetime, DateTimeConverter)

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] DateTimeCol with microsecond resolution.

[Ben T. <bt...@gm...>]

I really need to store date/time values with millisecond resolution.
What would be the easiest way to achieve this? I found the following
patch:

http://permalink.gmane.org/gmane.comp.python.sqlobject/5106

But I am loath to apply a patch to SQLObject since my code must be portable.

Must I create my own column type? Is there any way to override the
storage method of DateTimeCol? I tried passing a datetimeFormat to the
DateTimeCol.__init__() but this did not have the desired effect. I am
assuming the TIMESTAMP data type of sqlite does not support this level
of resolution? If so, I suppose I must store the date as a string in
ISO 8601 format?

Thanks.

Re: [SQLObject] Joins Not Working

[Oleg B. <ph...@ph...>]

On Sun, Jan 30, 2011 at 06:33:41PM -0500, Markos Kapes wrote:
> With regard to your first question, exactly how much else info would be helpful?

   A short test program, as shortest as possible, that demonstrates the
problem. 2-3 SQLObject classes, every one with 1-2 columns and joins,
commands to create and populate the tables - a few rows would be enough
- and commands to select some information. Provide your results for the
program and expected results.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Joins Not Working

[Markos K. <mk...@gm...>]

<html>
<head>
<title>SHOW FULL COLUMNS FROM title; </title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body><h1>SHOW FULL COLUMNS FROM title; </h1>
<table border=1 cellspacing=1 cellpadding=0><tr>
<th>Field</th><th>Type</th><th>Collation</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th><th>Privileges</th><th>Comment</th></tr>
<tr>
<td>id</td><td>int(11)</td><td></td><td>NO</td><td>PRI</td><td></td><td>auto_increment</td><td>select,insert,update,references</td><td>(null)</td></tr>
<tr>
<td>isbn</td><td>varchar(10)</td><td>utf8_unicode_ci</td><td>NO</td><td>MUL</td><td> </td><td>(null)</td><td>select,insert,update,references</td><td>(null)</td></tr>
<tr>
<td>booktitle</td><td>text</td><td>utf8_unicode_ci</td><td>YES</td><td>MUL</td><td></td><td>(null)</td><td>select,insert,update,references</td><td>(null)</td></tr>
<tr>
<td>publisher</td><td>varchar(50)</td><td>utf8_unicode_ci</td><td>NO</td><td>(null)</td><td> </td><td>(null)</td><td>select,insert,update,references</td><td>(null)</td></tr>
<tr>
<td>release_date</td><td>varchar(255)</td><td>utf8_unicode_ci</td><td>YES</td><td>(null)</td><td></td><td>(null)</td><td>select,insert,update,references</td><td>(null)</td></tr>
<tr>
<td>tag</td><td>text</td><td>utf8_unicode_ci</td><td>YES</td><td>(null)</td><td></td><td>(null)</td><td>select,insert,update,references</td><td>(null)</td></tr>
<tr>
<td>kind_id</td><td>int(11)</td><td></td><td>YES</td><td>MUL</td><td></td><td>(null)</td><td>select,insert,update,references</td><td>(null)</td></tr>
<tr>
<td>edition</td><td>int(11)</td><td></td><td>YES</td><td>(null)</td><td></td><td>(null)</td><td>select,insert,update,references</td><td>(null)</td></tr>
<tr>
<td>type</td><td>varchar(25)</td><td>utf8_unicode_ci</td><td>YES</td><td>(null)</td><td></td><td>(null)</td><td>select,insert,update,references</td><td>(null)</td></tr>
</table>
</body></html>

Re: [SQLObject] problem with tables

[Oleg B. <ph...@ph...>]

On Wed, Jan 19, 2011 at 10:07:23PM +0100, Petr Jake?? wrote:
> thanks for giving me a lesson.
> 
> I mean lesson about kindness and helpfulness, not lesson about programming..
> 
> Cheers
> 
> Petr

   Thank you for the nice words!

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] problem with tables

[Petr J. <pet...@tp...>]

>
>
>   This sets connection only for the table.
>
>   And now, when you have the connection to the specific database - call
>
> Person.createTable()
>
> Oleg,

thanks for giving me a lesson.

I mean lesson about kindness and helpfulness, not lesson about programming..

Cheers

Petr

Re: [SQLObject] problem with tables

[Oleg B. <ph...@ph...>]

Hi!

On Wed, Jan 19, 2011 at 06:18:21PM +0100, fortuna123 Gazeta.pl wrote:
> class Person(SQLObject):
> ...     fname = StringCol()
> ...     mi = StringCol(length=1, default=None)
> ...     lname = StringCol()
> ...
> >>> Person.createTable()
> 
> This create table in base who don't have name but i have base who i create
> write this

   I don't think the table is in a DB without a name. All DB operations
require a connection to a DB; either you have a connection or SQLObject
complains.

> createdb fortuna_bib and i don't know how i create class in this base.

   Create a connection to that database. There are a few ways to do it
in SQLObject:

sqlobject.sqlhub.processConnection = connectionForURI('postgres:/fortuna_bib')

   sqlhub is the global connection that's shared between all SQLObject
tables.
   Or do

Person.setConnection('postgres:/fortuna_bib')

   This sets connection only for the table.

   And now, when you have the connection to the specific database - call

Person.createTable()

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] problem with tables

[fortuna123 Gazeta.p. <for...@ga...>]

Hello i have problem with tables when i write

class Person(SQLObject):
...     fname = StringCol()
...     mi = StringCol(length=1, default=None)
...     lname = StringCol()
...
>>> Person.createTable()

This create table in base who don't have name but i have base who i create
write this

createdb fortuna_bib and i don't know how i create class in this base.

I'm sorry for my english.
Thank you for help me if you can.

                                                       Rafał Fortuniak

Re: [SQLObject] Joins not working...

[Oleg B. <ph...@ph...>]

On Mon, Jan 17, 2011 at 09:34:25PM -0500, Markos Kapes wrote:
> There's probably an embarrassingly obvious answer.... I have a class
> that I have written one way that fails in its joins. call it file1. I
> wrote another class to do the same thing, and it works. I've done
> diffs on the segments in question, and I still get an
> "OperationalError 1054 Column name None does not exist". Could it be
> the different connection would cause two different results?

   Probably no, but it's hard to say without looking into all related
details - tables declarations, SQL queries.

> --------fle 1---------------- DOESN'T WORK
> from sqlobject import *
> from components import db
> from SQLObjectWithFormGlue import SQLObjectWithFormGlue

   SQLObjectWithFormGlue is imported but is not used. Seems you edited
the code to post here and lost some important details.

> #_connection = db.SQLObjconnect()
> 	
> class Title(SQLObject):
>     class sqlmeta:
>         fromDatabase = True
> 
>     booktitle=UnicodeCol(default=None)

   Do you really need both fromDatabase and columns declarations? Are
there more columns that fromDatabase draws from the database?

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Joins not working...

[Markos K. <mk...@gm...>]

There's probably an embarrassingly obvious answer.... I have a class
that I have written one way that fails in its joins. call it file1. I
wrote another class to do the same thing, and it works. I've done
diffs on the segments in question, and I still get an
"OperationalError 1054 Column name None does not exist". Could it be
the different connection would cause two different results?

Thanks,
--Markos

 Here are the files:
--------fle 1---------------- DOESN'T WORK
from sqlobject import *
from components import db
from SQLObjectWithFormGlue import SQLObjectWithFormGlue

#_connection = db.SQLObjconnect()
	
class Title(SQLObject):
    class sqlmeta:
        fromDatabase = True

    _connection = db.conn() #just returns connection string....

    booktitle=UnicodeCol(default=None)
    books = MultipleJoin('Book')
    author = RelatedJoin('Author',
intermediateTable='author_title',createRelatedTable=True)
    categorys = MultipleJoin('Category')
    kind = ForeignKey('Kind')
    listTheseKeys=('kind')

 ---file2--------------------- WORKS!
from etc import *

from sqlobject import *
from sqlobject.sqlbuilder import *

#Set up db connection
connection = connectionForURI('mysql://%s:%s@%s:3306/%s?debug=1&logger=MyLogger&loglevel=debug&use_unicode=1&charset=utf8'
% (dbuser,dbpass,dbhost,dbname))
sqlhub.processConnection = connection


class Title(SQLObject):
    class sqlmeta:
        fromDatabase = True
    booktitle=UnicodeCol(default=None)
    books = MultipleJoin('Book')
    author = RelatedJoin('Author',
intermediateTable='author_title',createRelatedTable=True)
    categorys = MultipleJoin('Category')
    kind = ForeignKey('Kind')
    listTheseKeys=('kind')

Re: [SQLObject] Altering/updating a database

[Imri G. <lor...@gm...>]

On Fri, Jan 14, 2011 at 2:57 AM, Petr Jakeš <pet...@tp...> wrote:

> Yes  we are using addColumn method and it works great (Firebird SQL).
>
> The other question still remains: "How to find if the table was updated
> already". We are maintaining hundreds of the databases with the same
> structure on different machines and we were not sure where the table was
> already updated and where not.
>
>
The code I listed is more complicated, but it's mainly there to achieve a
similar goal (it has a few more tricks though). Yours might be better, as a
matter of programming philosophy: yours is "it's better to ask forgiveness"
while mine is "look before you leap".

Cheers,
Imri

-- 
Imri Goldberg
--------------------------------------
http://plnnr.com/ - automatic trip planning
http://www.algorithm.co.il/blogs/
--------------------------------------
-- insert signature here ----

Re: [SQLObject] Altering/updating a database

[Petr J. <pet...@tp...>]

>   It could be as simple as
>
> Table.sqlmeta.addColumn(UnicodeCol('name'), changeSchema=True)
>
>
Yes  we are using addColumn method and it works great (Firebird SQL).

The other question still remains: "How to find if the table was updated
already". We are maintaining hundreds of the databases with the same
structure on different machines and we were not sure where the table was
already updated and where not.

Because of that, we were using following (but I do not know if it is the
best way):?

from kinterbasdb import ProgrammingError

try:

        Table.select()[0]
    except IndexError:
       # this means table Table is OK, new column(s) was created already, no
data in the column
        pass
    except ProgrammingError:
       Table.sqlmeta.addColumn(UnicodeCol('name'), changeSchema=True)

Cheers

Petr

Re: [SQLObject] Altering/updating a database

[Oleg B. <ph...@ph...>]

On Thu, Jan 13, 2011 at 09:36:03PM +0000, Timothy W. Grove wrote:
> I want to update an existing database by adding a new column 
> (UnicodeCol) to an existing table. Can anyone suggest a way to 
> automatically accomplish this through sqlobject ?

   It could be as simple as

Table.sqlmeta.addColumn(UnicodeCol('name'), changeSchema=True)

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Altering/updating a database

[Imri G. <lor...@gm...>]

Hi
I've had the same problem some time ago, however I was (and still am) using
mysql, so YMMV.
I asked a similar question on this mailing list, and Oleg gave an example of
what he was using.
What I did was adapt Oleg's code to some "generic" db updating code:
(this might need further adaptation to your needs).

g_used_classnums = 0

def col_params(*args, **kwargs):
    return (args, kwargs)

def get_table_class_for_name(python_name, style = None):
    global g_used_classnums
    if style is None:
        style = sqlobject.DefaultStyle()

    table_name = python_name

    s = '''
class Temp%d(model.SQLObject):
    class sqlmeta:
        table = style.pythonClassToDBTable(table_name)
        fromDatabase = True''' % g_used_classnums
    g = globals().copy()
    g['style'] = style
    g['table_name'] = table_name
    exec s in g, locals()
    temp_class = locals()['Temp%d' % g_used_classnums]
    g_used_classnums += 1
    return temp_class

def add_column(table_name, col_name, col_type, col_params, style = None):
    if style is None:
        style = sqlobject.DefaultStyle()
    temp_class = get_table_class_for_name(table_name, style)

    db_col_name = style.pythonAttrToDBColumn(col_name)
    py_col_name = style.dbColumnToPythonAttr(db_col_name)

    args, kwargs = col_params
    if 'dbName' not in kwargs:
        kwargs['dbName'] = db_col_name
    if py_col_name in temp_class.sqlmeta.columns:
        return

    if col_type == sqlobject.ForeignKey:
        if py_col_name + 'ID' in temp_class.sqlmeta.columns:
            return
        col = col_type(*args, **kwargs)
    else:
        col = col_type(col_name, *args, **kwargs)
    print ('adding column "%s"... ' % col_name),
    temp_class.sqlmeta.addColumn(col, changeSchema = True)
    print 'done.'

I also have other similar functions: del_column, del_table, add_table,
add_all_table_indexes, table_exists & table_has_column

If there is interest I will publish here the the full code.

Cheers,
Imri

PS lately I've been thinking of writing add_column in a smarter fashion,
it's quite doable. It can discover automatically the missing columns and
their properties and add them. But this is a subject for another discussion
:)

On Thu, Jan 13, 2011 at 11:36 PM, Timothy W. Grove <tim...@si...>wrote:

> Hi folks,
>
> I have an application which I am developing under Windows7, python2.7
> and wxpython2.9. The application uses sqlobject-0.13.0-py2.7 with an
> sqlite database.
>
> I want to update an existing database by adding a new column
> (UnicodeCol) to an existing table. Can anyone suggest a way to
> automatically accomplish this through sqlobject ? I haven't found it too
> difficult to update an existing database with a completely new table,
> but adding a new column to an existing table is causing me some
> headaches. I haven't said much specifically about the application, but
> was wondering if there was a generally accepted "best" approach to this
> problem? Thanks.
>
> Best regards,
> Tim
>
>
> ------------------------------------------------------------------------------
> Protect Your Site and Customers from Malware Attacks
> Learn about various malware tactics and how to avoid them. Understand
> malware threats, the impact they can have on your business, and how you
> can protect your company and customers by using code signing.
> http://p.sf.net/sfu/oracle-sfdevnl
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>



-- 
Imri Goldberg
--------------------------------------
http://plnnr.com/ - automatic trip planning
http://www.algorithm.co.il/blogs/
--------------------------------------
-- insert signature here ----

[SQLObject] Altering/updating a database

[Timothy W. G. <tim...@si...>]

Hi folks,

I have an application which I am developing under Windows7, python2.7 
and wxpython2.9. The application uses sqlobject-0.13.0-py2.7 with an 
sqlite database.

I want to update an existing database by adding a new column 
(UnicodeCol) to an existing table. Can anyone suggest a way to 
automatically accomplish this through sqlobject ? I haven't found it too 
difficult to update an existing database with a completely new table, 
but adding a new column to an existing table is causing me some 
headaches. I haven't said much specifically about the application, but 
was wondering if there was a generally accepted "best" approach to this 
problem? Thanks.

Best regards,
Tim

Re: [SQLObject] using alternateIDs as the joining id found in a Related Join's otherColumn

[Oleg B. <ph...@ph...>]

On Tue, Jan 04, 2011 at 03:34:25PM +0300, Oleg Broytman wrote:
>    Anyway, the following program works for me:
> 
> class User(SQLObject):
>     class sqlmeta:
>         table = 'users'
> 
>     username = UnicodeCol(alternateID=True, varchar=False, length=8, alternateMethodName='by_username')
>     roles = SQLRelatedJoin('Role', intermediateTable='user_roles', createRelatedTable=False)
> 
> class Role(SQLObject):
>     class sqlmeta:
>         table = 'roles'
> 
>     name = UnicodeCol(varchar=False, length=8)
>     users = SQLRelatedJoin('User', intermediateTable='user_roles', createRelatedTable=False)
> 
> class UserRoles(SQLObject):
>     class sqlmeta:
>         table = 'user_roles'
> 
>     username = UnicodeCol(dbName='users_id', notNull=True, varchar=False, length=8)
>     role = ForeignKey('Role', dbName='roles_id', notNull=True, cascade=True)
>     unique = index.DatabaseIndex(username, role, unique=True)
> 
> User.createTable()
> Role.createTable()
> UserRoles.createTable()
> 
> user = User(username='test')
> role = Role(name='test')
> user.addRole(role)
> #role.addUser(user)
> 
> print user.roles
> print list(user.roles)
> 
> print role.users
> print list(role.users)

   Or this one (uses joinColumn/otherColumn instead of dbNames):

class User(SQLObject):
    class sqlmeta:
        table = 'users'

    username = UnicodeCol(alternateID=True, varchar=False, length=8, alternateMethodName='by_username')
    roles = SQLRelatedJoin('Role', intermediateTable='user_roles', createRelatedTable=False, joinColumn='username', otherColumn='role_id')

class Role(SQLObject):
    class sqlmeta:
        table = 'roles'

    name = UnicodeCol(varchar=False, length=8)
    users = SQLRelatedJoin('User', intermediateTable='user_roles', createRelatedTable=False, joinColumn='role_id', otherColumn='username')

class UserRoles(SQLObject):
    class sqlmeta:
        table = 'user_roles'

    username = UnicodeCol(notNull=True, varchar=False, length=8)
    role = ForeignKey('Role', notNull=True, cascade=True)
    unique = index.DatabaseIndex(username, role, unique=True)

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] using alternateIDs as the joining id found in a Related Join's otherColumn

[Oleg B. <ph...@ph...>]

@All: Happy New Year, folks!

On Thu, Dec 30, 2010 at 05:50:30PM -0800, Simon Laalo wrote:
> class User(SQLObject):
> 	class sqlmeta:
>       	table = 'users'
> 	username = UnicodeCol(alternateID=True, varchar=False, length=8,alternateMethodName='by_username')
> 	roles = SQLRelatedJoin('Role', joinColumn='username', intermediateTable='user_roles', createRelatedTable=False, orderBy='ordinal')
> 
> 
> class Role(SQLObject):
> 	class sqlmeta:
>       	table = 'roles'
> 	name = UnicodeCol(varchar=False, length=8)
> 
> 	users = SQLRelatedJoin('User', intermediateTable='user_roles', createRelatedTable=False, otherColumn='username')
> 
> class UserRoles(SQLObject):
>     class sqlmeta:
>         table = 'user_roles'
>     
>     username = ForeignKey('User',notNull=True, cascade=True)
>     role = ForeignKey('Role', notNull=True, cascade=True)
>     ordinal = IntCol(notNone=True)
>     unique = index.DatabaseIndex(username, role, unique=True)
> 
> and when I call role.users I get the following error:
> 
> ProgrammingError: operator does not exist: integer = character LINE 1: ...user_roles, roles WHERE ((users.id = user_roles.username...

   Aha, I see problems with this approach. First, ForeignKey is
implemented using an INT column referencing id column of the
corresponding table, so ForeignKey cannot be used in the intermediate
table. It has to be replaced with a UnicodeCol, and you loose `cascade`.
   Anyway, the following program works for me:

class User(SQLObject):
    class sqlmeta:
        table = 'users'

    username = UnicodeCol(alternateID=True, varchar=False, length=8, alternateMethodName='by_username')
    roles = SQLRelatedJoin('Role', intermediateTable='user_roles', createRelatedTable=False)

class Role(SQLObject):
    class sqlmeta:
        table = 'roles'

    name = UnicodeCol(varchar=False, length=8)
    users = SQLRelatedJoin('User', intermediateTable='user_roles', createRelatedTable=False)

class UserRoles(SQLObject):
    class sqlmeta:
        table = 'user_roles'

    username = UnicodeCol(dbName='users_id', notNull=True, varchar=False, length=8)
    role = ForeignKey('Role', dbName='roles_id', notNull=True, cascade=True)
    unique = index.DatabaseIndex(username, role, unique=True)

User.createTable()
Role.createTable()
UserRoles.createTable()

user = User(username='test')
role = Role(name='test')
user.addRole(role)
#role.addUser(user)

print user.roles
print list(user.roles)

print role.users
print list(role.users)

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] 'removing something' in a many-to-many relationship

[Timothy W. G. <tim...@si...>]

I've come up with a solution to my own question below, but if anyone has 
any better ideas, I'd love to hear them.

On 31/12/2010 12:45 AM, Timothy W. Grove wrote:
> Here are the bare-bones of two classes which are related through a 
> many-to-many relationship:
>
>     class SignEntry(SQLObject):
>         components = RelatedJoin("Component")
>
>     class Component(SQLObject):
>        code = StringCol(default="", unique=True)
>        sign_entries = RelatedJoin("SignEntry")
>
>
> With 'se' representing a particular 'SignEntry' object, the code 
> 'se.components' reveals the internals of that object:
>
>     [<Component 7 code='48'>, <Component 8 code='24'>, <Component 8
>     code='24'>]
>
> Now, I wish to remove just one <Component 8 code='24'> from this 
> object, but 'se.removeComponent(8)' removes both. Is there a (simple) 
> way to achieve what I wish?

Well, I've thought of a 'simple' way to accomplish this, but I'm not 
sure I'm altogether comfortable with it. First, I determine how many of 
the same 'components' there are in the 'sign entry'. If there are more 
than one, I delete them all and then add back in the others I want to 
remain. That is a good enough solution for this year. Bring on 2011 !!!

>
> I'm using sqlobject-0.13.0-py2.7 on Windows 7.
>
> Thanks in advance for any advice.
>
> Best regards (and HAPPY NEW YEAR - ALMOST!),
> Tim Grove
>
>
>
> ------------------------------------------------------------------------------
> Learn how Oracle Real Application Clusters (RAC) One Node allows customers
> to consolidate database storage, standardize their database environment, and,
> should the need arise, upgrade to a full multi-node Oracle RAC database
> without downtime or disruption
> http://p.sf.net/sfu/oracle-sfdevnl
>
>
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: [SQLObject] using alternateIDs as the joining id found in a Related Join's otherColumn

[Simon L. <sl...@re...>]

Hi, 

Thanks for the quick reply.  Unfortunately, I am still having problems getting it to work.  I'll answer your questions in order and then tell you what I've done and the error's I'm getting.

> First, why do you want this at all? Why not allow SQLObject to do internal referencing itself?...
> BTW, do you need many-to-many or one-to-many which is implemented by MultipleJoin

I need to make the intermediate table myself using the alternateID 'username' so as to be able to use the info from that table with fewer joins and to simplify some triggers.


What I've implemented:

class User(SQLObject):
	class sqlmeta:
      	table = 'users'
	username = UnicodeCol(alternateID=True, varchar=False, length=8,alternateMethodName='by_username')
	roles = SQLRelatedJoin('Role', joinColumn='username', intermediateTable='user_roles', createRelatedTable=False, orderBy='ordinal')


class Role(SQLObject):
	class sqlmeta:
      	table = 'roles'
	name = UnicodeCol(varchar=False, length=8)

	users = SQLRelatedJoin('User', intermediateTable='user_roles', createRelatedTable=False, otherColumn='username')

class UserRoles(SQLObject):
    class sqlmeta:
        table = 'user_roles'
    
    username = ForeignKey('User',notNull=True, cascade=True)
    role = ForeignKey('Role', notNull=True, cascade=True)
    ordinal = IntCol(notNone=True)
    unique = index.DatabaseIndex(username, role, unique=True)

and when I call role.users I get the following error:

ProgrammingError: operator does not exist: integer = character LINE 1: ...user_roles, roles WHERE ((users.id = user_roles.username...

Am I missing something?

Thanks again for your assistance.
-Slaalo

-----Original Message-----
From: Oleg Broytman [mailto:ph...@ph...] 
Sent: Thursday, December 30, 2010 12:51 PM
To: sql...@li...
Subject: Re: [SQLObject] using alternateIDs as the joining id found in a Related Join's otherColumn

On Thu, Dec 30, 2010 at 12:26:50PM -0800, Simon Laalo wrote:
> I'm fairly new to SQLObject

   Welcome!

> I have a question about RelatedJoin: is it possible to use an alternateID as the data for the otherColumn.
> 
> I have something like the following
> 
> in the DB:
> users table with columns id ( int) and username (char 8)
> 
> roles table with columns id (int) and name (char 8)
> 
> user_roles table with columns username and role_id
> 
> in the model.py:
> class User(SQLObject):
>                 class sqlmeta:
>                                 table = 'users'
>                 username = UnicodeCol(alternateID=True, varchar=False, length=8,alternateMethodName='by_username')
> 
> 
> class Role(SQLObject):
>                 class sqlmeta:
>                                 table = 'roles'
>                 name = UnicodeCol(varchar=False, length=8)
> 
>                 users = RelatedJoin('User', intermediateTable='user_roles',
>                          joinColumn='role_id', otherColumn='username')
> 
> but this isn't working because when I try to get role.users it attempts to find users whose ID is their username.
> 
> Is there a way for this to work using the user's alternateID username in the join table?

   First, why do you want this at all? Why not allow SQLObject to do
internal referencing itself?
   Well, if you still want to do the work manually, well... it has to be
performed manually.
   RelatedJoin is many-to-many relation (BTW, do you need many-to-many
or one-to-many which is implemented by MultipleJoin) and works using an
intermediate table that stores references to both tables. By default the
intermediate table is created with INT columns to store references to
id's. If you want to store references to a UnicodeCol you have to create
the intermediate table yourself:
http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship
   And joinColumn has to be "username" in this case.

   I've never tried doing something like this, so I'm not sure it will
work.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

------------------------------------------------------------------------------
Learn how Oracle Real Application Clusters (RAC) One Node allows customers
to consolidate database storage, standardize their database environment, and, 
should the need arise, upgrade to a full multi-node Oracle RAC database 
without downtime or disruption
http://p.sf.net/sfu/oracle-sfdevnl
_______________________________________________
sqlobject-discuss mailing list
sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: [SQLObject] 'removing something' in a many-to-many relationship

[Timothy W. G. <tim...@si...>]

> I'm using sqlobject-0.13.0-py2.7 on Windows 7.
Also, my database is SQLite3.

[SQLObject] 'removing something' in a many-to-many relationship

[Timothy W. G. <tim...@si...>]

Here are the bare-bones of two classes which are related through a 
many-to-many relationship:

    class SignEntry(SQLObject):
         components = RelatedJoin("Component")

    class Component(SQLObject):
        code = StringCol(default="", unique=True)
        sign_entries = RelatedJoin("SignEntry")


With 'se' representing a particular 'SignEntry' object, the code 
'se.components' reveals the internals of that object:

    [<Component 7 code='48'>, <Component 8 code='24'>, <Component 8
    code='24'>]

Now, I wish to remove just one <Component 8 code='24'> from this object, 
but 'se.removeComponent(8)' removes both. Is there a (simple) way to 
achieve what I wish?

I'm using sqlobject-0.13.0-py2.7 on Windows 7.

Thanks in advance for any advice.

Best regards (and HAPPY NEW YEAR - ALMOST!),
Tim Grove

Re: [SQLObject] using alternateIDs as the joining id found in a Related Join's otherColumn

[Oleg B. <ph...@ph...>]

On Thu, Dec 30, 2010 at 12:26:50PM -0800, Simon Laalo wrote:
> I'm fairly new to SQLObject

   Welcome!

> I have a question about RelatedJoin: is it possible to use an alternateID as the data for the otherColumn.
> 
> I have something like the following
> 
> in the DB:
> users table with columns id ( int) and username (char 8)
> 
> roles table with columns id (int) and name (char 8)
> 
> user_roles table with columns username and role_id
> 
> in the model.py:
> class User(SQLObject):
>                 class sqlmeta:
>                                 table = 'users'
>                 username = UnicodeCol(alternateID=True, varchar=False, length=8,alternateMethodName='by_username')
> 
> 
> class Role(SQLObject):
>                 class sqlmeta:
>                                 table = 'roles'
>                 name = UnicodeCol(varchar=False, length=8)
> 
>                 users = RelatedJoin('User', intermediateTable='user_roles',
>                          joinColumn='role_id', otherColumn='username')
> 
> but this isn't working because when I try to get role.users it attempts to find users whose ID is their username.
> 
> Is there a way for this to work using the user's alternateID username in the join table?

   First, why do you want this at all? Why not allow SQLObject to do
internal referencing itself?
   Well, if you still want to do the work manually, well... it has to be
performed manually.
   RelatedJoin is many-to-many relation (BTW, do you need many-to-many
or one-to-many which is implemented by MultipleJoin) and works using an
intermediate table that stores references to both tables. By default the
intermediate table is created with INT columns to store references to
id's. If you want to store references to a UnicodeCol you have to create
the intermediate table yourself:
http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship
   And joinColumn has to be "username" in this case.

   I've never tried doing something like this, so I'm not sure it will
work.

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.