sqlobject-discuss — All things SQLObject-related

Re: [SQLObject] SQLObject 0.11.0

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

On Wed, Aug 12, 2009 at 02:24:12PM +0400, Oleg Broytmann wrote:
> I'm pleased to announce version 0.11.0

   The branch 0.9 is declared obsolete and no longer maintained (until
someone would take it).

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Intermediate table in RelatedJoins

[Miguel T. <mig...@gm...>]

Hi Oleg.

I think I didn't explain myself well. The example I provided has two
relationships. A->B, B->A, both different - so they can't (or
shouldn't) use the same table.

I guess with sqlobject it would something like

class A (SQLObject):
    Bs = sqlobject.RelatedJoin ('B')

class B (SQLObject):
    As = sqlobject.RelatedJoin ('A')

after the corresponding create tables was called then the tables "a",
"b", "a_b" and "b_a" should (I think) be created.

You stated in a previous email (one minute before this one) that
sqlobject doesn't support multiple intermediate tables. Maybe that's
what missing. I guess I'll can try to have a go at it, but my guess is
that I'll need some coaching (so I might come to annoy you all some
more :) ).

Best regards,

Miguel Tavares

2009/8/12 Oleg Broytmann <ph...@ph...>:
> On Wed, Aug 12, 2009 at 10:20:43AM +0100, Miguel Tavares wrote:
>> Like A having several B and B having several different A?
>
>   RelatedJoins do exactly that. It is a many-to-many relation.
>
> Oleg.
> --
>     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
>           Programmers don't die, they just GOSUB without RETURN.
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>

[SQLObject] SQLObject 0.11.0

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

Hello!

I'm pleased to announce version 0.11.0, the first stable release of 0.11 branch
of SQLObject.


What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).


Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

Download:
http://cheeseshop.python.org/pypi/SQLObject/0.11.0

News and changes:
http://sqlobject.org/News.html


What's New
==========

News since 0.10
-----------------

Features & Interface
~~~~~~~~~~~~~~~~~~~~

* Dropped support for Python 2.3. The minimal version of Python for
  SQLObject is 2.4 now.

* Dropped support for PostgreSQL 7.2. The minimal supported version of
  PostgreSQL is 7.3 now.

* New magic attribute 'j' similar to 'q' was added that automagically does
  join with the other table in MultipleJoin or RelatedJoin.

* SQLObject can now create and drop a database in MySQL, PostgreSQL, SQLite
  and Firebird/Interbase.

* Added some support for schemas in PostgreSQL.

* Added DecimalStringCol - similar to DecimalCol but stores data as strings
  to work around problems in some drivers and type affinity problem in
  SQLite.

* Added sqlobject.include.hashcol.HashCol - a column type that automatically
  hashes anything going into it, and returns out an object that hashes
  anything being compared to itself. Basically, it's good for really simple
  one-way password fields, and it even supports the assignment of None to
  indicate no password set. By default, it uses the md5 library for
  hashing, but this can be changed in a HashCol definition.

* RowDestroyedSignal and RowUpdatedSignal were added.

Minor features
~~~~~~~~~~~~~~

* Use reversed() in manager/command.py instead of .__reversed__().

* Minor change in logging to console - logger no longer stores the output
  file, it gets the file from module sys every time by name; this means
  logging will use new sys.stdout (or stderr) in case the user changed
  them.

* Changed the order of testing of SQLite modules - look for external
  PySQLite2 before sqlite3.

For a more complete list, please see the news:
http://sqlobject.org/News.html

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Intermediate table in RelatedJoins

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

On Wed, Aug 12, 2009 at 10:20:43AM +0100, Miguel Tavares wrote:
> Like A having several B and B having several different A?

   RelatedJoins do exactly that. It is a many-to-many relation.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Intermediate table in RelatedJoins

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

On Wed, Aug 12, 2009 at 10:14:38AM +0100, Miguel Tavares wrote:
> But creating the two table might be exactly was is intended
> Hum... shouldn't it create a target_capability too?

   No. SQLObject can use only one intermediate table. Wanna extend this?
Patches (with tests) should be put to the SF tracker.

   But I wonder - what could be the use for a second intermediate table?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Intermediate table in RelatedJoins

[Miguel T. <mig...@gm...>]

Hi Oleg,

Hum... shouldn't it create a target_capability too?

Aren't there scenarios where the two relation might be independent?
Like A having several B and B having several different A?

Sorry for the missing VirtualMachine class.
##############################################################################
class VirtualMachine (sqlobject.SQLObject):
    """
    Virtual machine (with it's DB representation)
    """
    name = sqlobject.StringCol (unique=True)
    script = sqlobject.StringCol ()
    IP = sqlobject.StringCol ()
    username = sqlobject.StringCol ()
    rpms = sqlobject.IntCol ()
##############################################################################

Best regards,
Miguel Tavares

2009/8/12 Oleg Broytmann <ph...@ph...>
>
> On Wed, Aug 12, 2009 at 09:28:33AM +0100, Jos?? Miguel Pereira Tavares wrote:
> > sqlobject/main.py:
> > 1469            if join.soClass.__name__ > join.otherClass.__name__:
> > 1470                continue
> > 1471            joins.append(join)
> >
> > What this lines seam to do is stop any Many-to-Many relations where the
> > first class as a "lower" name than the second on.
> >
> > Is this intended? If so why?
>
>   To prevent the intermediate table to be created twice.
>
> > Example snipet:
>
>   Works for me (without VirtualMachine class):
>
> Capability.createTable()
> Target.createTable()
>
>   prints
>
>  1/QueryR  :  CREATE TABLE capability (
>    id INTEGER PRIMARY KEY,
>    name TEXT,
>    description TEXT
> )
>  2/Query   :  CREATE TABLE capability_target (
> capability_id INT NOT NULL,
> target_id INT NOT NULL
> )
>  2/QueryR  :  CREATE TABLE capability_target (
> capability_id INT NOT NULL,
> target_id INT NOT NULL
> )
>  3/Query   :  CREATE TABLE target (
>    id INTEGER PRIMARY KEY,
>    name TEXT,
>    i_p TEXT
> )
>
>
> Target.createTable()
> Capability.createTable()
>
>   prints
>
>  1/QueryR  :  CREATE TABLE target (
>    id INTEGER PRIMARY KEY,
>    name TEXT,
>    i_p TEXT
> )
>  2/QueryR  :  CREATE TABLE capability (
>    id INTEGER PRIMARY KEY,
>    name TEXT,
>    description TEXT
> )
>  3/Query   :  CREATE TABLE capability_target (
> capability_id INT NOT NULL,
> target_id INT NOT NULL
> )
>
>   Ok?
>
> Oleg.
> --
>     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
>           Programmers don't die, they just GOSUB without RETURN.
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss

Re: [SQLObject] Possible bug on the Many-to-Many relationships.

[Miguel T. <mig...@gm...>]

Hi Simon,
But creating the two table might be exactly was is intended, or it might
make more sense to have class Z have a reference to A than the other way
around.

What do you think?

Best regards,

Miguel Tavares

2009/8/12 Simon Cross <hod...@gm...>

> 2009/8/12 José Miguel Pereira Tavares <mig...@gm...>:
> > When trying to create a Many-to-Many relationship between two classes I
> > came across the following bug (I presume): when checking for the join
> > tables that need to be created the following lines are run:
> >
> > sqlobject/main.py:
> > 1469            if join.soClass.__name__ > join.otherClass.__name__:
> > 1470                continue
> > 1471            joins.append(join)
> >
> > What this lines seam to do is stop any Many-to-Many relations where the
> > first class as a "lower" name than the second on.
> >
> > Is this intended? If so why?
>
> It is intended. It's there to stop SQLObject attempting to create the
> join table twice when the tables for the both the classes in the join
> are created.
>
> Without it,
>
>  Capability.createTable()
>  Target.createTable()
>
> would attempt to create the mapping table twice.
>
> Schiavo
> Simon
>
>

Re: [SQLObject] Possible bug on the Many-to-Many relationships.

[Simon C. <hod...@gm...>]

2009/8/12 José Miguel Pereira Tavares <mig...@gm...>:
> When trying to create a Many-to-Many relationship between two classes I
> came across the following bug (I presume): when checking for the join
> tables that need to be created the following lines are run:
>
> sqlobject/main.py:
> 1469            if join.soClass.__name__ > join.otherClass.__name__:
> 1470                continue
> 1471            joins.append(join)
>
> What this lines seam to do is stop any Many-to-Many relations where the
> first class as a "lower" name than the second on.
>
> Is this intended? If so why?

It is intended. It's there to stop SQLObject attempting to create the
join table twice when the tables for the both the classes in the join
are created.

Without it,

  Capability.createTable()
  Target.createTable()

would attempt to create the mapping table twice.

Schiavo
Simon

Re: [SQLObject] Intermediate table in RelatedJoins

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

On Wed, Aug 12, 2009 at 09:28:33AM +0100, Jos?? Miguel Pereira Tavares wrote:
> sqlobject/main.py:
> 1469            if join.soClass.__name__ > join.otherClass.__name__:
> 1470                continue
> 1471            joins.append(join)
> 
> What this lines seam to do is stop any Many-to-Many relations where the 
> first class as a "lower" name than the second on.
> 
> Is this intended? If so why?

   To prevent the intermediate table to be created twice.

> Example snipet:

   Works for me (without VirtualMachine class):

Capability.createTable()
Target.createTable()

   prints

 1/QueryR  :  CREATE TABLE capability (
    id INTEGER PRIMARY KEY,
    name TEXT,
    description TEXT
)
 2/Query   :  CREATE TABLE capability_target (
capability_id INT NOT NULL,
target_id INT NOT NULL
)
 2/QueryR  :  CREATE TABLE capability_target (
capability_id INT NOT NULL,
target_id INT NOT NULL
)
 3/Query   :  CREATE TABLE target (
    id INTEGER PRIMARY KEY,
    name TEXT,
    i_p TEXT
)


Target.createTable()
Capability.createTable()

   prints

 1/QueryR  :  CREATE TABLE target (
    id INTEGER PRIMARY KEY,
    name TEXT,
    i_p TEXT
)
 2/QueryR  :  CREATE TABLE capability (
    id INTEGER PRIMARY KEY,
    name TEXT,
    description TEXT
)
 3/Query   :  CREATE TABLE capability_target (
capability_id INT NOT NULL,
target_id INT NOT NULL
)

   Ok?

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Possible bug on the Many-to-Many relationships.

[José M. P. T. <mig...@gm...>]

Hi all!

I'm quite new to sqlobject, so forgive me if I'm raising a issue that 
has already been discussed (as excuse I present only a technical issue: 
sourceforge is not cooperating when trying to search the list archives).

When trying to create a Many-to-Many relationship between two classes I 
came across the following bug (I presume): when checking for the join 
tables that need to be created the following lines are run:

sqlobject/main.py:
1469            if join.soClass.__name__ > join.otherClass.__name__:
1470                continue
1471            joins.append(join)

What this lines seam to do is stop any Many-to-Many relations where the 
first class as a "lower" name than the second on.

Is this intended? If so why?

Example snipet:

##############################################################################
class Capability (sqlobject.SQLObject):
    """
    A Hardware capability (or profile). Groups virtual machines.
    """
    name = sqlobject.StringCol ()
    description = sqlobject.StringCol ()
    vm = sqlobject.RelatedJoin ('VirtualMachine')
    targets = sqlobject.RelatedJoin ('Target')

class Target (sqlobject.SQLObject):
    """
    A physical host, capable of running one or more of the defined 
capabilities. 
    """
    name = sqlobject.StringCol ()
    IP = sqlobject.StringCol ()
    capabilities = sqlobject.RelatedJoin ('Capability')
##############################################################################


The table target_capability is not created. I believe it should be but 
the check in main.py:1469 stop it from happening.

Thanks in advance for your time,
Jose Miguel Pereira Tavares

Re: [SQLObject] RealatedJoin

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

On Tue, Aug 11, 2009 at 07:40:12AM +0200, Tom Coetser wrote:
> class Member(SQLObject):
>     name = StringCol(alternateID=True)
>     roles = SQLRelatedJoin('Role', intermediateTable='member_role',
>                                createRelatedTable=False)
> 
> class Role(SQLObject):
>     name = StringCol(alternateID=True)
>     members = SQLRelatedJoin('Member', intermediateTable='member_role',
>                                createRelatedTable=False)
> 
> class MemberRole(SQLObject):
>     member = ForeignKey('Member', notNull=True, cascade=False)
>     role = ForeignKey('Role', notNull=True, cascade=False)
> 
> When I call destroySelf() on a member though, I would like to **not** have 
> that member deleted if it still has any roles assigned to it. I tried setting 
> the cascade=False option in the intermediate table, but this does not help 
> because it looks like the base destroySelf() method automatically deletes any 
> RelatedJoins.

   First thing I can guess your are using a backend that doesn't support
CASCADE (SQLite?) With SQLite, you can CREATE a TRIGGER to prevent deletion
from the intermediate table, but you have to do it yourself.
   .destroySelf() actually doesn't rely on the backend support for CASCADE;
instead it uses it itself, but not on the RelatedJoin's intermediate table
even if the table is declared explicitly. The simplest way I see is to
override .destroySelf:

class Member(SQLObject):
    name = StringCol(alternateID=True)
    roles = SQLRelatedJoin('Role', intermediateTable='member_role',
                               createRelatedTable=False)

    def destroySelf(self):
        if list(self.roles): # or self.roles.count()
            raise RuntimeError('Cannot delete a member (%s) that has roles' %
self.id)
        super(Member, self).destroySelf()

class Role(SQLObject):
    name = StringCol(alternateID=True)
    members = SQLRelatedJoin('Member', intermediateTable='member_role',
                               createRelatedTable=False)

    def destroySelf(self):
        try:
            self.members[0]
        except IndexError:
            super(Role, self).destroySelf()
        else:
            raise RuntimeError('Cannot delete a role (%s) that has members' %
self.id)

   I showed three different ways to check for RelatedJoin. This of course only
works with .destroySelf - other means will happily delete rows.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Allowing DateTime to be specified as a string...

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

On Tue, Aug 11, 2009 at 04:53:12PM -0700, Ray Van Dolson wrote:
> It seems like this worked in the past, but I could just be imagining
> things :)

   Yes, long ago all column types accepted strings and passed them to the
backend as is. Later it was decided to make conversion stricter.

> I could write a simple function to check whether or not I'm on a column
> in my CSV file that has a date/time value, generate a datetime object
> from it and pass it along as the value for updating.

   I'd very much recommend that way. With this you can check that the
values are really date/time values in your preferred format.

> The other option (and what I ended up doing for now) is to modify the
> DateTimeValidator

   You don't need to modify the builtin validator. You can create your own
column type (inherited from DateTimeCol) with your own validator (inherited
from DateTimeValidator). Or you can pass a validator to the column, and
your validator will be stacked on top of the DateTimeValidator. See
examples at
http://svn.colorstudy.com/SQLObject/trunk/sqlobject/tests/test_validation.py

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] Getting odd syntax error...

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

On Tue, Aug 11, 2009 at 02:07:17PM -0700, Ray Van Dolson wrote:
> On Tue, Aug 11, 2009 at 01:12:08PM -0700, Ray Van Dolson wrote:
> > Using sqlobject 0.9.9 with pymssql 1.0.1 on RHEL 5.3 (the
> > aforementioned have been installed with yum via either the base OS
> > packaeges or EPEL).
> 
> Egg on my face.  This appears to be a known issue with pymssql 1.0.1.
> Upgrading to 1.0.2 fixed the problem:
> 
>   - fixed severe bug introduced in 1.0.0 that caused some queries to be
>     truncated or overwritten with binary garbage - many thanks to Igor
>     Nazarenko who found the exact cause of the bug,

   Good!

> Sorry for the noise all.

   Ok.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] SQLObject 0.11.0b1

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

On Mon, Aug 10, 2009 at 07:09:39PM -0700, Daniel Fetchinson wrote:
> > I'm pleased to announce version 0.11.0b1, the first beta release of 0.11
> > branch
> > of SQLObject.
> 
> Thanks Oleg!
> 
> My application runs without issue using the new version.

   Thank you for the report!

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Allowing DateTime to be specified as a string...

[Ray V. D. <rva...@es...>]

Hi all;

It seems like this worked in the past, but I could just be imagining
things :)

I have a column in my database defined as a DateTimeCol with no default
and where NULL is allowed.  I'm parsing a CSV file and one of the
columns in the CSV file corresponds with this DateTimeCol.

The CSV value is a string of time.struct_time (ie parseable by
strptime()).

When I update a row or create a new entry passing this string as the
value for the column, the validator returns an error because it's not a
datetime object.

I scratch my head here because I'm *sure* I have used my script in the
past with similar date strings and sqlobject has transparently inserted
them into my database without problem...

In any case, in the here and now I'm trying to figure out the best way
to handle this.

I could write a simple function to check whether or not I'm on a column
in my CSV file that has a date/time value, generate a datetime object
from it and pass it along as the value for updating.  The downside here
is that I then need to know ahead of time which columns will have date
values in them and I'd like to not have to manually track and code for
such scenarios if possible.

The other option (and what I ended up doing for now) is to modify the
DateTimeValidator to automatically convert struct_time strings into
datetime objects with the following bit of code:

            if isinstance(value, basestring):
                try:
                    if len(value) == 0:
                        return None
                    else:
                        try:
                            return datetime.datetime.fromtimestamp(time.mktime(time.strptime(value)))
                        except OverflowError:
                            return datetime.datetime.fromtimestamp(0)
                except:
                    raise validators.Invalid("expected a date/time string of type struct_time in the DateTimeCol '%s', got %s %r instead" % \
                        (self.name, type(value), value), value, state)

This gets the job done for me and even generates a NULL if the string
was zero length, and also handles situations where the Unix timestamp
can't be properly generated due to an overflow error.

Anyone have any thoughts?  Is this something that would be appropriate
for the main codebase or should I be shunting this into my own custom
validator and keeping it external?

Thanks in advance,
Ray

Re: [SQLObject] Getting odd syntax error...

[Ray V. D. <rva...@es...>]

On Tue, Aug 11, 2009 at 01:12:08PM -0700, Ray Van Dolson wrote:
> Using sqlobject 0.9.9 with pymssql 1.0.1 on RHEL 5.3 (the
> aforementioned have been installed with yum via either the base OS
> packaeges or EPEL).

Egg on my face.  This appears to be a known issue with pymssql 1.0.1.
Upgrading to 1.0.2 fixed the problem:

  - fixed severe bug introduced in 1.0.0 that caused some queries to be
    truncated or overwritten with binary garbage - many thanks to Igor
    Nazarenko who found the exact cause of the bug,

Sorry for the noise all.

Ray

[SQLObject] Getting odd syntax error...

[Ray V. D. <rva...@es...>]

Using sqlobject 0.9.9 with pymssql 1.0.1 on RHEL 5.3 (the
aforementioned have been installed with yum via either the base OS
packaeges or EPEL).

I have a class defined similar to the following:

class SystemsInfo(SQLObject):
  class sqlmeta:
    style = MixedCaseStyle(longID=True)
    fromDatabase = False
    table = 'SystemsInfo'
    idType = str
    idName = 'SystemName'
    lazyUpdate = True

  Department = StringCol(length=30, default=None)
  Vendor = StringCol(length=80, default=None)
  Model = StringCol(length=80, default=None)
  ... (lots more columns)

And eventually a very simple query as follows:

  a = SystemsInfo.select(SystemsInfo.q.id == "SYSNAME")
  item = a.getOne()

Debug output shows the following:

 1/Select  :  SELECT SystemsInfo.SystemName, SystemsInfo.Department,
  SystemsInfo.Vendor, SystemsInfo.Model FROM SystemsInfo WHERE
  ((SystemsInfo.SystemName) = ('SYSNAME'))
 1/QueryR  :  SELECT SystemsInfo.SystemName, SystemsInfo.Department,
  SystemsInfo.Vendor, SystemsInfo.Model FROM SystemsInfo WHERE
  ((SystemsInfo.SystemName) = ('SYSNAME'))

(Truncated some of the columns obviously)

But I get the following backtrace:

Traceback (most recent call last):
  File "./matrix", line 206, in ?
    main(sys.argv)
  File "./matrix", line 171, in main
    item = a.getOne()
  File "/usr/lib/python2.4/site-packages/sqlobject/sresults.py", line 255, in getOne
    results = list(self)
  File "/usr/lib/python2.4/site-packages/sqlobject/sresults.py", line 162, in __iter__
    return iter(list(self.lazyIter()))
  File "/usr/lib/python2.4/site-packages/sqlobject/sresults.py", line 170, in lazyIter
    return conn.iterSelect(self)
  File "/usr/lib/python2.4/site-packages/sqlobject/dbconnection.py", line 410, in iterSelect
    select, keepConnection=False)
  File "/usr/lib/python2.4/site-packages/sqlobject/dbconnection.py", line 798, in __init__
    self.dbconn._executeRetry(self.rawconn, self.cursor, self.query)
  File "/usr/lib/python2.4/site-packages/sqlobject/dbconnection.py", line 344, in _executeRetry
    return cursor.execute(query)
  File "/usr/lib/python2.4/site-packages/pymssql.py", line 188, in execute
    raise OperationalError, e[0]
pymssql.OperationalError: SQL Server message 170, severity 15, state 1, line 1:
Line 1: Incorrect syntax near '='.

Now, it appears to me the syntax shown in the debug output is valid, an
indeed running it from isql seems to work fine.

However, I inspected the query via tcpdump/wireshark and noticed that
in the packet, the portion of the query after WHERE
((SystemsInfo.SystemName) = is some funky unprintable characters and
certainly not the name of the system...

Other queries seem to show the same thing happening... strings I am
providing to sqlobject are getting transmitted as gibberish even though
sqlobject's debug output shows them as valid.

Almost seems like a locale type issue...

Anyone have any ideas on what the fix is here?

Thanks,
Ray

Re: [SQLObject] RealatedJoin (was: ManyToMany)

[Tom C. <su...@ic...>]

Hi Oleg,

I got sidetracked on something else and am only now able to get back to this. 
Thank you for your reply.

On Friday 31 July 2009 12:29:55 Oleg Broytmann wrote:
> On Fri, Jul 31, 2009 at 09:36:19AM +0200, Tom Coetser wrote:
> > I am trying to get my head around using a ManyToMany relation between
[snip]
>    The worst problem with ManyToMany is not documentation. The code was
> added by the original author before he left the project, and I don't know
> if the code by any means complete. I never used it. Consider it an
> unfinished experimental code. Use RelatedJoin or SQLRelatedJoin instead.

Noted, thank you.

[snip]

I am now using this option but have a question w.r.t. destroySelf(). Given the 
following:

-------- cut ---------------------
class Member(SQLObject):
    name = StringCol(alternateID=True)
    roles = SQLRelatedJoin('Role', intermediateTable='member_role',
                               createRelatedTable=False)

class Role(SQLObject):
    name = StringCol(alternateID=True)
    members = SQLRelatedJoin('Member', intermediateTable='member_role',
                               createRelatedTable=False)

class MemberRole(SQLObject):
    member = ForeignKey('Member', notNull=True, cascade=False)
    role = ForeignKey('Role', notNull=True, cascade=False)
-------- cut ---------------------

I can create members and roles and add roles to members using the addRole() 
auto method and vice versa for members to roles.

When I call destroySelf() on a member though, I would like to **not** have 
that member deleted if it still has any roles assigned to it. I tried setting 
the cascade=False option in the intermediate table, but this does not help 
because it looks like the base destroySelf() method automatically deletes any 
RelatedJoins.

So the question is: is it possible to manage the intermediate table myself, 
still use the RelatedJoin functionality to get the benefit of the auto
add....()  methods, but somehow indicate to destroySelf() whether deletes on 
records in the intermediate table should be cascaded or not?
In other words, can I have my cake AND eat it? :-)


Thanks,
 Tom

Re: [SQLObject] SQLObject 0.11.0b1

[Daniel F. <fet...@go...>]

> I'm pleased to announce version 0.11.0b1, the first beta release of 0.11
> branch
> of SQLObject.

Thanks Oleg!

My application runs without issue using the new version.

Cheers,
Daniel

-- 
Psss, psss, put it down! - http://www.cafepress.com/putitdown

[SQLObject] SQLObject 0.11.0b1

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

Hello!

I'm pleased to announce version 0.11.0b1, the first beta release of 0.11 branch
of SQLObject.


What is SQLObject
=================

SQLObject is an object-relational mapper.  Your database tables are described
as classes, and rows are instances of those classes.  SQLObject is meant to be
easy to use and quick to get started with.

SQLObject supports a number of backends: MySQL, PostgreSQL, SQLite,
Firebird, Sybase, MSSQL and MaxDB (also known as SAPDB).


Where is SQLObject
==================

Site:
http://sqlobject.org

Development:
http://sqlobject.org/devel/

Mailing list:
https://lists.sourceforge.net/mailman/listinfo/sqlobject-discuss

Archives:
http://news.gmane.org/gmane.comp.python.sqlobject

Download:
http://cheeseshop.python.org/pypi/SQLObject/0.11.0b1

News and changes:
http://sqlobject.org/News.html


What's New
==========

News since 0.10
-----------------

Features & Interface
~~~~~~~~~~~~~~~~~~~~

* Dropped support for Python 2.3. The minimal version of Python for
  SQLObject is 2.4 now.

* Dropped support for PostgreSQL 7.2. The minimal supported version of
  PostgreSQL is 7.3 now.

* New magic attribute 'j' similar to 'q' was added that automagically does
  join with the other table in MultipleJoin or RelatedJoin.

* SQLObject can now create and drop a database in MySQL, PostgreSQL, SQLite
  and Firebird/Interbase.

* Added some support for schemas in PostgreSQL.

* Added DecimalStringCol - similar to DecimalCol but stores data as strings
  to work around problems in some drivers and type affinity problem in
  SQLite.

* Added sqlobject.include.hashcol.HashCol - a column type that automatically
  hashes anything going into it, and returns out an object that hashes
  anything being compared to itself. Basically, it's good for really simple
  one-way password fields, and it even supports the assignment of None to
  indicate no password set. By default, it uses the md5 library for
  hashing, but this can be changed in a HashCol definition.

* RowDestroyedSignal and RowUpdatedSignal were added.

Minor features
~~~~~~~~~~~~~~

* Use reversed() in manager/command.py instead of .__reversed__().

* Minor change in logging to console - logger no longer stores the output
  file, it gets the file from module sys every time by name; this means
  logging will use new sys.stdout (or stderr) in case the user changed
  them.

* Changed the order of testing of SQLite modules - look for external
  PySQLite2 before sqlite3.

For a more complete list, please see the news:
http://sqlobject.org/News.html

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] memcache patch attached.. gmpy patch under way

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

On Thu, Jul 30, 2009 at 09:10:31PM -0400, Stef Telford wrote:
> disable the writelocks.

   Why?! Locks protect critical data in multithreading programs, they
shouldn't be disabled.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] RealatedJoin (was: ManyToMany)

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

On Fri, Jul 31, 2009 at 09:36:19AM +0200, Tom Coetser wrote:
> I am trying to get my head around using a ManyToMany relation between tables 
> and hope to get some guidance here since the available docs are a bit sparse 
> on this (no offence - I know how difficult it is to keep docs up to 
> date :-) )

   The worst problem with ManyToMany is not documentation. The code was
added by the original author before he left the project, and I don't know if
the code by any means complete. I never used it. Consider it an unfinished
experimental code. Use RelatedJoin or SQLRelatedJoin instead.

> class Member(SQLObject):
>     name = StringCol(alternateID=True)
>     roles = ManyToMany('Role')
> 
> class Role(SQLObject):
>     name = StringCol(alternateID=True)
>     members = ManyToMany('Member')
> 
> I am testing with a SQLite backend for what it's worth.
> 
> Q1:
> Is there a way, by adding additional params to the ManyToMany(...) 
> declarations for example, to restrict the same role to be added multiple 
> times to the same member and vice versa?
> 
> Q2:
> Is there a way to restrict/cascade deletes of a role or member if that role or 
> member has a link to the other?
> 
> Q3:
> When deleting a role or member using the .destroySelf() method, the link 
> record in the intermediate table is not deleted. Is there a standard way to 
> do this? I suppose an answer to Q2 will also address this.

   What you want could be achieved by a RelatedJoin with an explicit
intermediate table:
http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship
   In the intermediate table you can set cascade, add unique index, and so
on.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

[SQLObject] Guidance on ManyToMany

[Tom C. <su...@ic...>]

Hi all,

I am trying to get my head around using a ManyToMany relation between tables 
and hope to get some guidance here since the available docs are a bit sparse 
on this (no offence - I know how difficult it is to keep docs up to 
date :-) )

Here is what I have:

class Member(SQLObject):
    name = StringCol(alternateID=True)
    roles = ManyToMany('Role')

class Role(SQLObject):
    name = StringCol(alternateID=True)
    members = ManyToMany('Member')

I am testing with a SQLite backend for what it's worth.

Q1:
Is there a way, by adding additional params to the ManyToMany(...) 
declarations for example, to restrict the same role to be added multiple 
times to the same member and vice versa?

Q2:
Is there a way to restrict/cascade deletes of a role or member if that role or 
member has a link to the other?

Q3:
When deleting a role or member using the .destroySelf() method, the link 
record in the intermediate table is not deleted. Is there a standard way to 
do this? I suppose an answer to Q2 will also address this.

I am adding a sample python file, and the output from an iPython session, 
below to illustrate the questions if that would help.


Thanks,
  Tom


------------- cut : ManyToMany.py -----------------------------------
import os
from sqlobject import *

myDir = os.path.realpath(os.path.dirname(__file__))
dbURI = 'sqlite://%s/ManyToMany.sqlite' % myDir
sqlhub.processConnection = connectionForURI(dbURI)

class Member(SQLObject):
    name = StringCol(alternateID=True)
    roles = ManyToMany('Role')

class Role(SQLObject):
    name = StringCol(alternateID=True)
    members = ManyToMany('Member')

def initDB():
    tables = (Member, Role)
    for t in tables:
        t.createTable(ifNotExists=True)

def addSamples():
    members = ('John', 'Paul', 'George', 'Ringo')
    roles = ('drums', 'lead', 'rythm', 'bass', 'vocals')

    for m in members:
        Member(name=m)
    for r in roles:
        Role(name=r)

    Member.byName('John').roles.add(Role.byName('rythm'))
    Member.byName('Paul').roles.add(Role.byName('bass'))
    Member.byName('George').roles.add(Role.byName('lead'))
    Member.byName('Ringo').roles.add(Role.byName('drums'))
------------- cut : ManyToMany.py -----------------------------------

------------- cut : iPython session ---------------------------------
In [1]: import ManyToMany as mm

In [2]: mm.initDB()

In [3]: mm.addSamples()

In [4]: vocals = mm.Role.byName('vocals')

In [5]: john = mm.Member.byName('John')

In [6]: john.roles.add(vocals)

-----> Q1: Added the 'vocals' role to john, and here it is:

In [7]: list(john.roles)
Out[7]: [<Role 3 name='rythm'>, <Role 5 name='vocals'>]

In [8]: john.roles.add(vocals)

-----> Q1: ... but we can add it a second time and now have the same role 
twice. Some way to specify a unique index on the intermediate table could 
avoid this and would raise a trapable exception.

In [9]: list(john.roles)
Out[9]: [<Role 3 name='rythm'>, <Role 5 name='vocals'>, <Role 5 
name='vocals'>]

------> Q1: Here is the corresponding rows from the intermediate table for 
the 'vocals' role

In [10]: Q = "SELECT * from member_role where role_id=5"

In [11]: mm.Role._connection.queryAll(Q)
Out[11]: [(1, 5), (1, 5)]

------> Q3: Destroy the 'vocals' role

In [12]: vocals.destroySelf()

------> Q3: It's gone from john's roles

In [13]: list(john.roles)
Out[13]: [<Role 3 name='rythm'>]

------> Q3: ... but both records are still in the intermediate table

In [14]: mm.Role._connection.queryAll(Q)
Out[14]: [(1, 5), (1, 5)]

------> Here we add a new role 'foo'. It may just be an SQLite 
related 'feature', but this new role will get the same id as the just 
deleted 'vocals' role had.

In [15]: foo = mm.Role(name='foo')

In [16]: foo
Out[16]: <Role 5 name='foo'>

------> ... and due to this, john will automatically get 2 'foo' roles added 
to his list of roles - not good :-(

In [17]: list(john.roles)
Out[17]: [<Role 3 name='rythm'>, <Role 5 name='foo'>, <Role 5 name='foo'>]

------------- cut : iPython session ---------------------------------

Re: [SQLObject] SQLObject Speed ?

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

On Thu, Jul 30, 2009 at 11:44:31AM -0400, Stef Telford wrote:
> <     decimal = PyImport_ImportModule("decimal");
> ---
> >     decimal = PyImport_ImportModule("gmpy");
>
>    Kind of a 'no-brainer' there but, since psycopg is a compiled  
> extension, I don't see how we can monkey patch the C lib :(

   Well, I think it's too low-level. I am sure psycopg is better than that.
Python is dynamic, and I'm sure it's possible to find a dynamic way around
builtin module names.
   Let's look around. import psycopg2._psycopg; print dir(psycopg2._psycopg)...
aha, types and adapters. There is a DECIMAL type converter, and there is a
psycopg2._psycopg.string_types dict where the converter is registered under
the key 1700. What 1700? grep 1700 *

pgtypes.h:#define NUMERICOID 1700
typecast_builtins.c:static long int typecast_NUMBER_types[] = {20, 23, 21, 701, 700, 1700, 0};
typecast_builtins.c:static long int typecast_DECIMAL_types[] = {1700, 0};

   Seems 1700 is a builtin index for DECIMAL type converter. I don't know
how exactly type converters are used; I am sure you can find this out by
reading psycopgmodule.c or by asking a psycopg mailing list. Probably you
need to replace DECIMAL or 1700 type converter, or both. Something like

psycopg2._psycopg.DECIMAL = my_decimal_converter
   or
psycopg2._psycopg.string_types[1700] = my_decimal_converter

   Exact details will require a lot of homework, sure.

>    Lastly, I have also hacked my version of SQLObject to disable the  
> local cache, so that SO can use memcache (by subclassing the sqlobject  
> and then making custom get/setattr methods). It would be nice if there  
> was a way to toggle the cache on/off at the SO level (if there is, I  
> haven't found it sadly).

   There is no way currently. Why not to add it? Extend your hack so it'd
be possible to configure caching using a flag in sqlmeta; let's call it
useCache = True/False (defaults to True).
   Publish your patch.

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] SQLObject Speed ?

[Simon C. <hod...@gm...>]

On Thu, Jul 30, 2009 at 5:39 PM, Stef Telford<st...@um...> wrote:
>   Well.. sadly.. how realistic is this ? No offense meant to anyone, but,
> gmpy has been around since (at least) python 2.4 (perhaps earlier ? not sure
> 100%) and it still hasn't been accepted in. I do agree that fixing Decimal
> at a fundamental level would be MUCH nicer, but, the BDFL (Guido) seems to
> not be moving on it. That bug was opened on 2008-03 .. over a year ago.

Agreed that getting any substantial change into Python is going to be
a bit of pain for anyone with commit access, but I'm don't think
getting a C implementation of an existing module in is going to be
anywhere near as difficult as getting a whole new library accepted
(and one that introduces a new somewhat dodgy C library dependency at
that).

> I think that the quickest (no pun intended) is to fix it in the adaptor if
> possible. Jst my 2c :)

Sure. But it would be cool to have a C implementation of Decimal in
standard Python. :)

Schiavo
Simon