sqlobject-cvs — CVS notification

[SQL-CVS] r774 - trunk/SQLObject/sqlobject/manager

[<sub...@co...>]

Author: ianb
Date: 2005-05-06 16:29:30 +0000 (Fri, 06 May 2005)
New Revision: 774

Modified:
   trunk/SQLObject/sqlobject/manager/command.py
Log:
Added better help messages; added upgrade command

Modified: trunk/SQLObject/sqlobject/manager/command.py
===================================================================
--- trunk/SQLObject/sqlobject/manager/command.py	2005-05-06 16:14:45 UTC (rev 773)
+++ trunk/SQLObject/sqlobject/manager/command.py	2005-05-06 16:29:30 UTC (rev 774)
@@ -4,6 +4,7 @@
 import re
 import os
 import sys
+import textwrap
 try:
     from paste import pyconfig
     from paste import CONFIG
@@ -165,6 +166,8 @@
     required_args = []
     description = None
 
+    help = ''
+
     def __classinit__(cls, new_args):
         if cls.__bases__ == (object,):
             # This abstract base class
@@ -179,6 +182,10 @@
 
     def run(self):
         self.parser.usage = "%%prog [options]\n%s" % self.summary
+        if self.help:
+            help = textwrap.fill(
+                self.help, int(os.environ.get('COLUMNS', 80))-4)
+            self.parser.usage += '\n' + help
         self.parser.prog = '%s %s' % (
             os.path.basename(self.invoked_as),
             self.command_name)
@@ -420,6 +427,11 @@
 
     name = 'status'
     summary = 'Show status of classes vs. database'
+    help = ('This command checks the SQLObject definition and checks if '
+            'the tables in the database match.  It can always test for '
+            'missing tables, and on some databases can test for the '
+            'existance of other tables.  Column types are not currently '
+            'checked.')
 
     parser = standard_parser(simulate=False)
 
@@ -512,6 +524,9 @@
 
     name = 'execute'
     summary = 'Execute SQL statements'
+    help = ('Runs SQL statements directly in the database, with no '
+            'intervention.  Useful when used with a configuration file.  '
+            'Each argument is executed as an individual statement.')
 
     parser = standard_parser(find_modules=False)
     parser.add_option('--stdin',
@@ -563,7 +578,15 @@
 class CommandRecord(Command):
 
     name = 'record'
-    summary = 'Record state of table definitions'
+    summary = 'Record historical information about the database status'
+    help = ('Record state of table definitions.  The state of each '
+            'table is written out to a separate file in a directory, '
+            'and that directory forms a "version".  A table is also '
+            'added to you datebase (%s) that reflects the version the '
+            'database is currently at.  Use the upgrade command to '
+            'sync databases with code.'
+            % SQLObjectVersionTable.sqlmeta.table)
+               
     parser = standard_parser()
     parser.add_option('--output-dir',
                       help="Base directory for recorded definitions",
@@ -778,15 +801,135 @@
         if self.options.db_record:
             self.update_db(version, self.connection())
 
+class CommandUpgrade(CommandRecord):
+
+    name = 'upgrade'
+    summary = 'Update the database to a new version (as created by record)'
+    help = ('This command runs scripts (that you write by hand) to '
+            'upgrade a database.  The database\'s current version is in '
+            'the sqlobject_version table (use record --force-db-version '
+            'if a database does not have a sqlobject_version table), '
+            'and upgrade scripts are in the version directory you are '
+            'upgrading FROM, named upgrade_DBNAME_VERSION.sql, like '
+            '"upgrade_mysql_2004-12-01b.sql".')
+
+    parser = standard_parser(find_modules=False)
+    parser.add_option('--upgrade-to',
+                      help="Upgrade to the given version (default: newest version)",
+                      dest="upgrade_to",
+                      metavar="VERSION")
+    parser.add_option('--output-dir',
+                      help="Base directory for recorded definitions",
+                      dest="output_dir",
+                      metavar="DIR",
+                      default=None)
+
+    upgrade_regex = re.compile(r'^upgrade_([a-z]*)_([^.]*)\.sql$', re.I)
+
+    def command(self):
+        v = self.options.verbose
+        sim = self.options.simulate
+        if self.options.upgrade_to:
+            version_to = self.options.upgrade_to
+        else:
+            version_to = os.path.basename(self.find_last_version())
+        current = self.current_version()
+        if v:
+            print 'Current version: %s' % current
+        version_list = self.make_plan(current, version_to)
+        if not version_list:
+            print 'Database up to date'
+            return
+        if v:
+            print 'Plan:'
+            for next_version, upgrader in version_list:
+                print '  Use %s to upgrade to %s' % (
+                    self.shorten_filename(upgrader), next_version)
+        conn = self.connection()
+        for next_version, upgrader in version_list:
+            f = open(upgrader)
+            sql = f.read()
+            f.close()
+            if v:
+                print "Running:"
+                print sql
+                print '-'*60
+            if not sim:
+                conn.query(sql)
+            self.update_db(next_version, conn)
+        print 'Done.'
+                
+
+    def current_version(self):
+        conn = self.connection()
+        if not conn.tableExists(SQLObjectVersionTable.sqlmeta.table):
+            print 'No sqlobject_version table!'
+            sys.exit(1)
+        versions = list(SQLObjectVersionTable.select(connection=conn))
+        if not versions:
+            print 'No rows in sqlobject_version!'
+            sys.exit(1)
+        if len(versions) > 1:
+            print 'Ambiguous sqlobject_version_table'
+            sys.exit(1)
+        return versions[0].version
+
+    def make_plan(self, current, dest):
+        if current == dest:
+            return []
+        dbname = self.connection().dbName
+        next_version, upgrader = self.best_upgrade(current, dest, dbname)
+        if not upgrader:
+            print 'No way to upgrade from %s to %s' % (current, dest)
+            print ('(you need a %s/upgrade_%s_%s.sql script)'
+                   % (current, dbname, dest))
+            sys.exit(1)
+        plan = [(next_version, upgrader)]
+        if next_version == dest:
+            return plan
+        else:
+            return plan + self.make_plan(next_version, dest)
+
+    def best_upgrade(self, current, dest, target_dbname):
+        current_dir = os.path.join(self.base_dir(), current)
+        if self.options.verbose > 1:
+            print ('Looking in %s for upgraders'
+                   % self.shorten_filename(current_dir))
+        upgraders = []
+        for fn in os.listdir(current_dir):
+            match = self.upgrade_regex.search(fn)
+            if not match:
+                if self.verbose > 1:
+                    print 'Not an upgrade script: %s' % fn
+                continue
+            dbname = match.group(1)
+            version = match.group(2)
+            if dbname != target_dbname:
+                if self.verbose > 1:
+                    print 'Not for this database: %s (want %s)' % (
+                        dbname, target_dbname)
+                continue
+            if version > dest:
+                if self.verbose > 1:
+                    print 'Version too new: %s (only want %s)' % (
+                        version, dest)
+            upgraders.append((version, os.path.join(current_dir, fn)))
+        if not upgraders:
+            if self.options.verbose > 1:
+                print 'No upgraders found in %s' % current_dir
+            return None, None
+        upgraders.sort()
+        return upgraders[-1]        
+        
 def update_sys_path(paths, verbose):
     if isinstance(paths, (str, unicode)):
         paths = [paths]
     for path in paths:
         path = os.path.abspath(path)
         if path not in sys.path:
-            if verbose:
+            if verbose > 1:
                 print 'Adding %s to path' % path
-            sys.path.append(path)
+            sys.path.insert(0, path)
 
 if __name__ == '__main__':
     the_runner.run(sys.argv)

[SQL-CVS] r773 - trunk/SQLObject/sqlobject

[<sub...@co...>]

Author: ianb
Date: 2005-05-06 16:14:45 +0000 (Fri, 06 May 2005)
New Revision: 773

Modified:
   trunk/SQLObject/sqlobject/sresults.py
Log:
doh, typo

Modified: trunk/SQLObject/sqlobject/sresults.py
===================================================================
--- trunk/SQLObject/sqlobject/sresults.py	2005-05-06 16:11:25 UTC (rev 772)
+++ trunk/SQLObject/sqlobject/sresults.py	2005-05-06 16:14:45 UTC (rev 773)
@@ -141,7 +141,7 @@
 
     def __iter__(self):
         if self.ops.get('connection'):
-            conn = self.opts.get('connection')
+            conn = self.ops.get('connection')
         else:
             conn = self.sourceClass._connection
         return conn.iterSelect(self)

[SQL-CVS] r772 - trunk/SQLObject/sqlobject

[<sub...@co...>]

Author: ianb
Date: 2005-05-06 16:11:25 +0000 (Fri, 06 May 2005)
New Revision: 772

Modified:
   trunk/SQLObject/sqlobject/dbconnection.py
Log:
Handle access from class (where obj is None, type is class)

Modified: trunk/SQLObject/sqlobject/dbconnection.py
===================================================================
--- trunk/SQLObject/sqlobject/dbconnection.py	2005-05-06 16:10:41 UTC (rev 771)
+++ trunk/SQLObject/sqlobject/dbconnection.py	2005-05-06 16:11:25 UTC (rev 772)
@@ -812,7 +812,7 @@
         # I'm a little surprised we have to do this, but apparently
         # the object's private dictionary of attributes doesn't
         # override this descriptor.
-        if obj.__dict__.has_key('_connection'):
+        if obj and obj.__dict__.has_key('_connection'):
             return obj.__dict__['_connection']
         return self.getConnection()
 

[SQL-CVS] r771 - trunk/SQLObject/sqlobject

[<sub...@co...>]

Author: ianb
Date: 2005-05-06 16:10:41 +0000 (Fri, 06 May 2005)
New Revision: 771

Modified:
   trunk/SQLObject/sqlobject/sresults.py
Log:
Better handling of case where no explicit ConnectionHub connection has been provided

Modified: trunk/SQLObject/sqlobject/sresults.py
===================================================================
--- trunk/SQLObject/sqlobject/sresults.py	2005-05-05 22:59:56 UTC (rev 770)
+++ trunk/SQLObject/sqlobject/sresults.py	2005-05-06 16:10:41 UTC (rev 771)
@@ -140,7 +140,10 @@
                 return list(self.clone(start=start, end=start+1))[0]
 
     def __iter__(self):
-        conn = self.ops.get('connection', self.sourceClass._connection)
+        if self.ops.get('connection'):
+            conn = self.opts.get('connection')
+        else:
+            conn = self.sourceClass._connection
         return conn.iterSelect(self)
 
     def accumulate(self, *expressions):

[SQL-CVS] r770 - trunk/SQLObject/sqlobject/manager

[<sub...@co...>]

Author: ianb
Date: 2005-05-05 22:59:56 +0000 (Thu, 05 May 2005)
New Revision: 770

Modified:
   trunk/SQLObject/sqlobject/manager/command.py
Log:
Added record command, to record the database status at a certain point in time

Modified: trunk/SQLObject/sqlobject/manager/command.py
===================================================================
--- trunk/SQLObject/sqlobject/manager/command.py	2005-05-05 22:58:15 UTC (rev 769)
+++ trunk/SQLObject/sqlobject/manager/command.py	2005-05-05 22:59:56 UTC (rev 770)
@@ -1,6 +1,7 @@
 #!/usr/bin/env python
 import optparse
 import fnmatch
+import re
 import os
 import sys
 try:
@@ -8,11 +9,61 @@
     from paste import CONFIG
 except ImportError:
     pyconfig = None
+    CONFIG = {}
+import time
 
 import sqlobject
+from sqlobject import col
 from sqlobject.util import moduleloader
 from sqlobject.declarative import DeclarativeMeta
 
+class SQLObjectVersionTable(sqlobject.SQLObject):
+    """
+    This table is used to store information about the database and
+    its version (used with record and update commands).
+    """
+    class sqlmeta:
+        table = 'sqlobject_db_version'
+    version = col.StringCol()
+    updated = col.DateTimeCol(default=col.DateTimeCol.now)
+
+def db_differences(soClass, conn):
+    """
+    Returns the differences between a class and the table in a
+    connection.  Returns [] if no differences are found.  This
+    function does the best it can; it can miss many differences.
+    """
+    # @@: Repeats a lot from CommandStatus.command, but it's hard
+    # to actually factor out the display logic.  Or I'm too lazy
+    # to do so.
+    diffs = []
+    if not conn.tableExists(soClass.sqlmeta.table):
+        diffs.append('Does not exist in database')
+    else:
+        try:
+            columns = conn.columnsFromSchema(soClass.sqlmeta.table,
+                                             soClass)
+        except AttributeError:
+            # Database does not support reading columns
+            pass
+        else:
+            existing = {}
+            for col in columns:
+                col = col.withClass(soClass)
+                existing[col.dbName] = col
+            missing = {}
+            for col in soClass.sqlmeta._columns:
+                if existing.has_key(col.dbName):
+                    del existing[col.dbName]
+                else:
+                    missing[col.dbName] = col
+            for col in existing.values():
+                diffs.append('Database has extra column: %s'
+                             % col.dbName)
+            for col in missing.values():
+                diffs.append('Database missing column: %s' % col.dbName)
+    return diffs
+
 class CommandRunner(object):
 
     def __init__(self):
@@ -254,6 +305,15 @@
                 return response[0].lower() == 'y'
             print 'Y or N please'
 
+    def shorten_filename(self, fn):
+        """
+        Shortens a filename to make it relative to the current
+        directory (if it can).  For display purposes.
+        """
+        if fn.startswith(os.getcwd() + '/'):
+            fn = fn[len(os.getcwd())+1:]
+        return fn
+
 class CommandSQL(Command):
 
     name = 'sql'
@@ -499,7 +559,225 @@
                 sys.stdout.write("%r\t" % col)
             sys.stdout.write("\n")
         print
-                
+
+class CommandRecord(Command):
+
+    name = 'record'
+    summary = 'Record state of table definitions'
+    parser = standard_parser()
+    parser.add_option('--output-dir',
+                      help="Base directory for recorded definitions",
+                      dest="output_dir",
+                      metavar="DIR",
+                      default=None)
+    parser.add_option('--no-db-record',
+                      help="Don't record version to database",
+                      dest="db_record",
+                      action="store_false",
+                      default=True)
+    parser.add_option('--force-create',
+                      help="Create a new version even if appears to be "
+                      "identical to the last version",
+                      action="store_true",
+                      dest="force_create")
+    parser.add_option('--name',
+                      help="The name to append to the version.  The "
+                      "version should sort after previous versions (so "
+                      "any versions from the same day should come "
+                      "alphabetically before this version).",
+                      dest="version_name",
+                      metavar="NAME")
+    parser.add_option('--force-db-version',
+                      help="Update the database version, and include no "
+                      "database information.  This is for databases that "
+                      "were developed without any interaction with "
+                      "this tool, to create a 'beginning' revision.",
+                      metavar="VERSION_NAME",
+                      dest="force_db_version")
+
+    version_regex = re.compile(r'^\d\d\d\d-\d\d-\d\d')
+
+    def command(self):
+        if self.options.force_db_version:
+            self.command_force_db_version()
+            return
+        
+        v = self.options.verbose
+        sim = self.options.simulate
+        classes = self.classes()
+        if not classes:
+            print "No classes found!"
+            return
+
+        output_dir = self.find_output_dir()
+        version = os.path.basename(output_dir)
+        print "Creating version %s" % version
+        conns = []
+        files = {}
+        for cls in self.classes():
+            dbName = cls._connection.dbName
+            if cls._connection not in conns:
+                conns.append(cls._connection)
+            fn = os.path.join(cls.__name__
+                              + '_' + dbName + '.sql')
+            if sim:
+                continue
+            files[fn] = ''.join([
+                '-- Exported definition from %s\n'
+                % time.strftime('%Y-%m-%dT%H:%M:%S'),
+                '-- Class %s.%s\n'
+                % (cls.__module__, cls.__name__),
+                '-- Database: %s\n'
+                % dbName,
+                cls.createTableSQL().strip(),
+                '\n'])
+        last_version_dir = self.find_last_version()
+        if last_version_dir and not self.options.force_create:
+            if v > 1:
+                print "Checking %s to see if it is current" % last_version_dir
+            files_copy = files.copy()
+            for fn in os.listdir(last_version_dir):
+                if not fn.endswith('.sql'):
+                    continue
+                if not files_copy.has_key(fn):
+                    if v > 1:
+                        print "Missing file %s" % fn
+                    break
+                f = open(os.path.join(last_version_dir, fn), 'r')
+                content = f.read()
+                f.close()
+                if (self.strip_comments(files_copy[fn])
+                    != self.strip_comments(content)):
+                    if v > 1:
+                        print "Content does not match: %s" % fn
+                    break
+                del files_copy[fn]
+            else:
+                # No differences so far
+                if not files_copy:
+                    # Used up all files
+                    print ("Current status matches version %s"
+                           % os.path.basename(last_version_dir))
+                    return
+                if v > 1:
+                    print "Extra files: %s" % ', '.join(files_copy.keys())
+            if v:
+                print ("Current state does not match %s"
+                       % os.path.basename(last_version_dir))
+        if v > 1 and not last_version_dir:
+            print "No last version to check"
+        if not sim:
+            os.mkdir(output_dir)
+        if v:
+            print 'Making directory %s' % self.shorten_filename(output_dir)
+        files = files.items()
+        files.sort()
+        for fn, content in files:
+            if v:
+                print '  Writing %s' % self.shorten_filename(fn)
+            if not sim:
+                f = open(os.path.join(output_dir, fn), 'w')
+                f.write(content)
+                f.close()
+        all_diffs = []
+        for cls in self.classes():
+            for conn in conns:
+                diffs = db_differences(cls, conn)
+                for diff in diffs:
+                    if len(conns) > 1:
+                        diff = '  (%s).%s: %s' % (
+                            conn.uri(), cls.sqlmeta.table, diff)
+                    else:
+                        diff = '  %s: %s' % (cls.sqlmeta.table, diff)
+                    all_diffs.append(diff)
+        if all_diffs:
+            print 'Database does not match schema:'
+            print '\n'.join(all_diffs)
+            if self.options.db_record:
+                print '(Not updating database version)'
+        elif self.options.db_record:
+            for conn in conns:
+                self.update_db(version, conn)
+
+    def update_db(self, version, conn):
+        v = self.options.verbose
+        if not conn.tableExists(SQLObjectVersionTable.sqlmeta.table):
+            if v:
+                print ('Creating table %s'
+                       % SQLObjectVersionTable.sqlmeta.table)
+            sql = SQLObjectVersionTable.createTableSQL(connection=conn)
+            if v > 1:
+                print sql
+            if not self.options.simulate:
+                SQLObjectVersionTable.createTable(connection=conn)
+        if not self.options.simulate:
+            SQLObjectVersionTable.clearTable(connection=conn)
+            SQLObjectVersionTable(
+                version=version,
+                connection=conn)
+
+    def strip_comments(self, sql):
+        lines = [l for l in sql.splitlines()
+                 if not l.strip().startswith('--')]
+        return '\n'.join(lines)        
+
+    def base_dir(self):
+        base = self.options.output_dir
+        if base is None:
+            base = CONFIG.get('sqlobject_history_dir', '.')
+        return base
+
+    def find_output_dir(self):
+        today = time.strftime('%Y-%m-%d', time.localtime())
+        if self.options.version_name:
+            dir = os.path.join(self.base_dir(), today + '-' + 
+                               self.options.version_name)
+            if os.path.exists(dir):
+                print ("Error, directory already exists: %s"
+                       % dir)
+                sys.exit(1)
+            return dir
+        extra = ''
+        while 1:
+            dir = os.path.join(self.base_dir(), today + extra)
+            if not os.path.exists(dir):
+                return dir
+            if not extra:
+                extra = 'a'
+            else:
+                extra = chr(ord(extra)+1)
+    
+    def find_last_version(self):
+        names = []
+        for fn in os.listdir(self.base_dir()):
+            if not self.version_regex.search(fn):
+                continue
+            names.append(fn)
+        if not names:
+            return None
+        names.sort()
+        return os.path.join(self.base_dir(), names[-1])
+
+    def command_force_db_version(self):
+        v = self.options.verbose
+        sim = self.options.simulate
+        version = self.options.force_db_version
+        if not self.version_regex.search(version):
+            print "Versions must be in the format YYYY-MM-DD..."
+            print "You version %s does not fit this" % version
+            return
+        version_dir = os.path.join(self.base_dir(), version)
+        if not os.path.exists(version_dir):
+            if v:
+                print 'Creating %s' % self.shorten_filename(version_dir)
+            if not sim:
+                os.mkdir(version_dir)
+        elif v:
+            print ('Directory %s exists'
+                   % self.shorten_filename(version_dir))
+        if self.options.db_record:
+            self.update_db(version, self.connection())
+
 def update_sys_path(paths, verbose):
     if isinstance(paths, (str, unicode)):
         paths = [paths]

[SQL-CVS] r769 - trunk/SQLObject/sqlobject

[<sub...@co...>]

Author: ianb
Date: 2005-05-05 22:58:15 +0000 (Thu, 05 May 2005)
New Revision: 769

Modified:
   trunk/SQLObject/sqlobject/dbconnection.py
Log:
Fixes problem with per-instance connections and ConnectionHub

Modified: trunk/SQLObject/sqlobject/dbconnection.py
===================================================================
--- trunk/SQLObject/sqlobject/dbconnection.py	2005-05-05 21:46:28 UTC (rev 768)
+++ trunk/SQLObject/sqlobject/dbconnection.py	2005-05-05 22:58:15 UTC (rev 769)
@@ -809,6 +809,11 @@
         self.threadingLocal = threading_local()
 
     def __get__(self, obj, type=None):
+        # I'm a little surprised we have to do this, but apparently
+        # the object's private dictionary of attributes doesn't
+        # override this descriptor.
+        if obj.__dict__.has_key('_connection'):
+            return obj.__dict__['_connection']
         return self.getConnection()
 
     def __set__(self, obj, value):

[SQL-CVS] r768 - trunk/SQLObject/scripts

[<sub...@co...>]

Author: ianb
Date: 2005-05-05 21:46:28 +0000 (Thu, 05 May 2005)
New Revision: 768

Modified:
   trunk/SQLObject/scripts/sqlobject-admin
Log:
Better handle older versions of SQLObject being installed

Modified: trunk/SQLObject/scripts/sqlobject-admin
===================================================================
--- trunk/SQLObject/scripts/sqlobject-admin	2005-05-05 21:45:21 UTC (rev 767)
+++ trunk/SQLObject/scripts/sqlobject-admin	2005-05-05 21:46:28 UTC (rev 768)
@@ -2,7 +2,7 @@
 import sys
 import os
 try:
-    import sqlobject
+    import sqlobject.manager
 except ImportError:
     try:
         here = __file__
@@ -17,6 +17,11 @@
         print 'I cannot find the sqlobject module'
         print 'If SQLObject is installed, you may need to set $PYTHONPATH'
         sys.exit(3)
+    # Now we have to get rid of possibly stale modules from that import
+    # up there
+    for name, value in sys.modules.items():
+        if name.startswith('sqlobject'):
+            del sys.modules[name]
 
 from sqlobject.manager import command
 command.the_runner.run(sys.argv)

[SQL-CVS] r767 - trunk/SQLObject/sqlobject

[<sub...@co...>]

Author: ianb
Date: 2005-05-05 21:45:21 +0000 (Thu, 05 May 2005)
New Revision: 767

Modified:
   trunk/SQLObject/sqlobject/main.py
Log:
deleted inaccurate/old comment

Modified: trunk/SQLObject/sqlobject/main.py
===================================================================
--- trunk/SQLObject/sqlobject/main.py	2005-05-05 21:44:59 UTC (rev 766)
+++ trunk/SQLObject/sqlobject/main.py	2005-05-05 21:45:21 UTC (rev 767)
@@ -1047,8 +1047,6 @@
             return
 
         # Pass the connection object along if we were given one.
-        # Passing None for the ID tells __init__ we want to create
-        # a new object.
         if kw.has_key('connection'):
             self._connection = kw['connection']
             self._SO_perConnection = True

[SQL-CVS] r766 - trunk/SQLObject/sqlobject

[<sub...@co...>]

Author: ianb
Date: 2005-05-05 21:44:59 +0000 (Thu, 05 May 2005)
New Revision: 766

Modified:
   trunk/SQLObject/sqlobject/col.py
Log:
Added DateTimeCol.now(), which calls either DateTime.now, or datetime.now

Modified: trunk/SQLObject/sqlobject/col.py
===================================================================
--- trunk/SQLObject/sqlobject/col.py	2005-05-04 22:05:31 UTC (rev 765)
+++ trunk/SQLObject/sqlobject/col.py	2005-05-05 21:44:59 UTC (rev 766)
@@ -644,7 +644,6 @@
 
     def postgresCreateSQL(self):
         sql = SOKeyCol.postgresCreateSQL(self)
-        print [self, self.soClass]
         other = findClass(self.foreignKey, self.soClass.sqlmeta.registry)
         tName = other.sqlmeta.table
         idName = other.sqlmeta.idName
@@ -837,6 +836,16 @@
 
 class DateTimeCol(Col):
     baseClass = SODateTimeCol
+    def now():
+        if DATETIME_IMPLEMENTATION == 'datetime':
+            return datetime.datetime.now()
+        elif DATETIME_IMPLEMENTATION == 'mxdatetime':
+            return DateTime.now()
+        else:
+            assert 0, ("No datetime implementation available "
+                       "(DATETIME_IMPLEMENTATION=%r)"
+                       % DATETIME_IMPLEMENTATION)
+    now = staticmethod(now)
 
 class SODateCol(SOCol):
     dateFormat = '%Y-%m-%d'

[SQL-CVS] r765 - trunk/SQLObject/sqlobject/manager

[<sub...@co...>]

Author: ianb
Date: 2005-05-04 22:05:31 +0000 (Wed, 04 May 2005)
New Revision: 765

Modified:
   trunk/SQLObject/sqlobject/manager/command.py
Log:
Since config file can have sys_path, load it up more aggressively; also use default config

Modified: trunk/SQLObject/sqlobject/manager/command.py
===================================================================
--- trunk/SQLObject/sqlobject/manager/command.py	2005-05-04 02:50:08 UTC (rev 764)
+++ trunk/SQLObject/sqlobject/manager/command.py	2005-05-04 22:05:31 UTC (rev 765)
@@ -5,6 +5,7 @@
 import sys
 try:
     from paste import pyconfig
+    from paste import CONFIG
 except ImportError:
     pyconfig = None
 
@@ -69,11 +70,11 @@
                           help="The database connection URI",
                           metavar='URI',
                           dest='connection_uri')
-        if pyconfig:
-            parser.add_option('-f', '--config-file',
-                              help="The Paste config file that contains the database URI (in the database key)",
-                              metavar="FILE",
-                              dest="config_file")
+    if pyconfig:
+        parser.add_option('-f', '--config-file',
+                          help="The Paste config file that contains the database URI (in the database key)",
+                          metavar="FILE",
+                          dest="config_file")
     if find_modules:
         parser.add_option('-m', '--module',
                           help="Module in which to find SQLObject classes",
@@ -221,8 +222,9 @@
         if getattr(self.options, 'config_file', None):
             assert pyconfig, (
                 "The --config-file option should not be available without paste.pyconfig installed")
-            config = pyconfig.Config()
+            config = pyconfig.Config(with_default=True)
             config.load(self.options.config_file)
+            CONFIG.push_process_config(config)
             return config
         else:
             return None

[SQL-CVS] r756 - trunk/SQLObject/sqlobject

[<sub...@co...>]

Author: phd
Date: 2005-05-02 15:55:25 +0000 (Mon, 02 May 2005)
New Revision: 756

Modified:
   trunk/SQLObject/sqlobject/converters.py
Log:
Commited bugfix from the bug N 1123687. Thanks to als for testing.


Modified: trunk/SQLObject/sqlobject/converters.py
===================================================================
--- trunk/SQLObject/sqlobject/converters.py	2005-05-02 09:41:55 UTC (rev 755)
+++ trunk/SQLObject/sqlobject/converters.py	2005-05-02 15:55:25 UTC (rev 756)
@@ -1,3 +1,5 @@
+import array
+
 try:
     import mx.DateTime.ISO
     origISOStr = mx.DateTime.ISO.strGMT
@@ -20,7 +22,7 @@
     import Sybase
     NumericType=Sybase.NumericType
 except ImportError:
-    NumericType = None 
+    NumericType = None
 
 if type(1==1) == type(1):
     class BOOL(object):
@@ -89,6 +91,12 @@
 lookupConverter = converters.lookupConverter
 
 def StringLikeConverter(value, db):
+    if isinstance(value, array.array):
+        try:
+            value = value.tounicode()
+        except ValueError:
+            value = value.tostring()
+
     if db in ('mysql', 'postgres'):
         for orig, repl in sqlStringReplace:
             value = value.replace(orig, repl)
@@ -100,6 +108,7 @@
 
 registerConverter(type(""), StringLikeConverter)
 registerConverter(type(u""), StringLikeConverter)
+registerConverter(array.array, StringLikeConverter)
 
 def IntConverter(value, db):
     return repr(int(value))

[SQL-CVS] r752 - in trunk/SQLObject/sqlobject: . tests

[<sub...@co...>]

Author: phd
Date: 2005-04-26 13:52:45 +0000 (Tue, 26 Apr 2005)
New Revision: 752

Added:
   trunk/SQLObject/sqlobject/tests/test_SQLMultipleJoin.py
   trunk/SQLObject/sqlobject/tests/test_SQLRelatedJoin.py
Modified:
   trunk/SQLObject/sqlobject/joins.py
Log:
Added SQLMultipleJoin and SQLRelatedJoin colusmns that return SelectResults
instead of lists.


Modified: trunk/SQLObject/sqlobject/joins.py
===================================================================
--- trunk/SQLObject/sqlobject/joins.py	2005-04-26 13:33:38 UTC (rev 751)
+++ trunk/SQLObject/sqlobject/joins.py	2005-04-26 13:52:45 UTC (rev 752)
@@ -4,7 +4,7 @@
 import classregistry
 from col import popKey
 
-__all__ = ['MultipleJoin', 'RelatedJoin']
+__all__ = ['MultipleJoin', 'SQLMultipleJoin', 'RelatedJoin', 'SQLRelatedJoin']
 
 def getID(obj):
     try:
@@ -35,7 +35,7 @@
     def withClass(self, soClass):
         if self.kw.has_key('joinMethodName'):
             self._joinMethodName = self.kw['joinMethodName']
-            del self.kw['joinMethodName'] 
+            del self.kw['joinMethodName']
         return self.baseClass(soClass=soClass,
                               joinMethodName=self._joinMethodName,
                               **self.kw)
@@ -133,9 +133,20 @@
             conn = None
         return self._applyOrderBy([self.otherClass.get(id, conn) for (id,) in ids if id is not None], self.otherClass)
 
+class SOSQLMultipleJoin(SOMultipleJoin):
+
+    def performJoin(self, inst):
+        results=self.otherClass.select(getattr(self.otherClass.q, self.soClass.sqlmeta.style.dbColumnToPythonAttr(self.joinColumn))==inst.id)
+        if self.orderBy is NoDefault:
+            self.orderBy = self.otherClass.sqlmeta.defaultOrder
+        return results.orderBy(self.orderBy)
+
 class MultipleJoin(Join):
     baseClass = SOMultipleJoin
 
+class SQLMultipleJoin(Join):
+    baseClass = SOSQLMultipleJoin
+
 # This is a many-to-many join, with an intermediary table
 class SORelatedJoin(SOMultipleJoin):
 
@@ -191,9 +202,34 @@
             self.otherColumn,
             getID(other))
 
+class SOSQLRelatedJoin(SORelatedJoin):
+    def performJoin(self, inst):
+        options={
+            'otherTable' : self.otherClass.sqlmeta.table,
+            'otherID' : self.otherClass.sqlmeta.idName,
+            'interTable' : self.intermediateTable,
+            'table' : self.soClass.sqlmeta.table,
+            'ID' : self.soClass.sqlmeta.idName,
+            'joinCol' : self.joinColumn,
+            'otherCol' : self.otherColumn,
+            'idValue' : inst.id,
+        }
+        results = self.otherClass.select('''\
+%(otherTable)s.%(otherID)s = %(interTable)s.%(otherCol)s and
+%(interTable)s.%(joinCol)s = %(table)s.%(ID)s and
+%(table)s.%(ID)s = %(idValue)s''' % options, clauseTables=(
+            options['table'],
+            options['otherTable'],
+            options['interTable'],
+        ))
+        # TODO (michelts), apply order by on the selection
+        return results
+
 class RelatedJoin(MultipleJoin):
     baseClass = SORelatedJoin
 
+class SQLRelatedJoin(RelatedJoin):
+    baseClass = SOSQLRelatedJoin
+
 def capitalize(name):
     return name[0].capitalize() + name[1:]
-

Added: trunk/SQLObject/sqlobject/tests/test_SQLMultipleJoin.py
===================================================================
--- trunk/SQLObject/sqlobject/tests/test_SQLMultipleJoin.py	2005-04-26 13:33:38 UTC (rev 751)
+++ trunk/SQLObject/sqlobject/tests/test_SQLMultipleJoin.py	2005-04-26 13:52:45 UTC (rev 752)
@@ -0,0 +1,44 @@
+from sqlobject import *
+from sqlobject.tests.dbtest import *
+
+class Race(SQLObject):
+    name = StringCol()
+    fightersAsList = MultipleJoin('RFighter')
+    fightersAsSResult = SQLMultipleJoin('RFighter')
+
+class RFighter(SQLObject):
+    name = StringCol()
+    race = ForeignKey('Race')
+    power = IntCol()
+
+def createAllTables():
+    setupClass(Race)
+    setupClass(RFighter)
+
+def test_1():
+    createAllTables()
+    # create some races
+    human=Race(name='human')
+    saiyajin=Race(name='saiyajin')
+    hibrid=Race(name='hibrid (human with sayajin)')
+    namek=Race(name='namekuseijin')
+    # create some fighters
+    gokou=RFighter(name='Gokou (Kakaruto)', race=saiyajin, power=10)
+    vegeta=RFighter(name='Vegeta', race=saiyajin, power=9)
+    krilim=RFighter(name='Krilim', race=human, power=3)
+    yancha=RFighter(name='Yancha', race=human, power=2)
+    jackiechan=RFighter(name='Jackie Chan', race=human, power=2)
+    gohan=RFighter(name='Gohan', race=hibrid, power=8)
+    goten=RFighter(name='Goten', race=hibrid, power=7)
+    trunks=RFighter(name='Trunks', race=hibrid, power=8)
+    picollo=RFighter(name='Picollo', race=namek, power=6)
+    neil=RFighter(name='Neil', race=namek, power=5)
+
+    # testing the SQLMultipleJoin stuff
+    for i, j in zip(human.fightersAsList, human.fightersAsSResult):
+        assert i is j # the 2 ways should give the same result
+    assert namek.fightersAsSResult.count() == len(namek.fightersAsList)
+    assert saiyajin.fightersAsSResult.max('power') == 10
+    assert trunks in hibrid.fightersAsSResult
+    assert picollo not in hibrid.fightersAsSResult
+    assert hibrid.fightersAsSResult.sum('power') == 23

Added: trunk/SQLObject/sqlobject/tests/test_SQLRelatedJoin.py
===================================================================
--- trunk/SQLObject/sqlobject/tests/test_SQLRelatedJoin.py	2005-04-26 13:33:38 UTC (rev 751)
+++ trunk/SQLObject/sqlobject/tests/test_SQLRelatedJoin.py	2005-04-26 13:52:45 UTC (rev 752)
@@ -0,0 +1,44 @@
+from sqlobject import *
+from sqlobject.tests.dbtest import *
+
+class Fighter(SQLObject):
+    class sqlmeta:
+        idName='fighter_id' # test on a non-standard way
+    name = StringCol()
+    tourtments = RelatedJoin('Tourtment')
+
+class Tourtment(SQLObject):
+    class sqlmeta:
+        table='competition' # test on a non-standard way
+    name = StringCol()
+    fightersAsList = RelatedJoin('Fighter')
+    fightersAsSResult = SQLRelatedJoin('Fighter')
+
+def createAllTables():
+    setupClass(Fighter)
+    setupClass(Tourtment)
+
+def test_1():
+    createAllTables()
+    # create some tourtments
+    t1=Tourtment(name='Tourtment #1')
+    t2=Tourtment(name='Tourtment #2')
+    t3=Tourtment(name='Tourtment #3')
+    # create some fighters
+    gokou=Fighter(name='gokou')
+    vegeta=Fighter(name='vegeta')
+    gohan=Fighter(name='gohan')
+    trunks=Fighter(name='trunks')
+    # relating them
+    t1.addFighter(gokou)
+    t1.addFighter(vegeta)
+    t1.addFighter(gohan)
+    t2.addFighter(gokou)
+    t2.addFighter(vegeta)
+    t2.addFighter(trunks)
+    t3.addFighter(gohan)
+    t3.addFighter(trunks)
+    # do some selects
+    for i, j in zip(t1.fightersAsList, t1.fightersAsSResult):
+        assert i is j
+    assert len(t2.fightersAsList) == t2.fightersAsSResult.count()

[SQL-CVS] r751 - trunk/SQLObject/sqlobject

[<sub...@co...>]

Author: phd
Date: 2005-04-26 13:33:38 +0000 (Tue, 26 Apr 2005)
New Revision: 751

Modified:
   trunk/SQLObject/sqlobject/sresults.py
Log:
Added .filter().


Modified: trunk/SQLObject/sqlobject/sresults.py
===================================================================
--- trunk/SQLObject/sqlobject/sresults.py	2005-04-25 22:31:31 UTC (rev 750)
+++ trunk/SQLObject/sqlobject/sresults.py	2005-04-26 13:33:38 UTC (rev 751)
@@ -83,6 +83,13 @@
     def distinct(self):
         return self.clone(distinct=True)
 
+    def newClause(self, new_clause):
+        return self.__class__(self.sourceClass, new_clause,
+                              self.clauseTables, **self.ops)
+
+    def filter(self, filter_clause):
+        return self.newClause(sqlbuilder.AND(self.clause, filter_clause))
+
     def __getitem__(self, value):
         if type(value) is type(slice(1)):
             assert not value.step, "Slices do not support steps"

[SQL-CVS] r736 - in trunk/SQLObject: docs sqlobject sqlobject/tests

[<sub...@co...>]

Author: phd
Date: 2005-04-24 09:54:28 +0000 (Sun, 24 Apr 2005)
New Revision: 736

Modified:
   trunk/SQLObject/docs/SQLObjectSubqueries.txt
   trunk/SQLObject/sqlobject/sqlbuilder.py
   trunk/SQLObject/sqlobject/tests/test_subqueries.py
Log:
Changed IN() so it can be used for either IN-list or IN-subquery.


Modified: trunk/SQLObject/docs/SQLObjectSubqueries.txt
===================================================================
--- trunk/SQLObject/docs/SQLObjectSubqueries.txt	2005-04-24 08:42:19 UTC (rev 735)
+++ trunk/SQLObject/docs/SQLObjectSubqueries.txt	2005-04-24 09:54:28 UTC (rev 736)
@@ -19,3 +19,7 @@
 
 Select() is used instead of .select() because you need to control what
 columns and in what order the inner query returns.
+
+Avalable queries are IN(), NOTIN(), EXISTS(), NOTEXISTS(), SOME(), ANY()
+and ALL(). The last 3 are used with comparison operators, like this:
+"somevalue = ANY(Select(...))".

Modified: trunk/SQLObject/sqlobject/sqlbuilder.py
===================================================================
--- trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-24 08:42:19 UTC (rev 735)
+++ trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-24 09:54:28 UTC (rev 736)
@@ -603,9 +603,21 @@
 def NOT(op):
     return SQLPrefix("NOT", op)
 
-def IN(item, list):
+def _IN(item, list):
     return SQLOp("IN", item, list)
 
+def IN(item, list):
+    if isinstance(list, Select):
+        return INSubquery(item, list)
+    else:
+        return _IN(item, list)
+
+def NOTIN(item, list):
+    if isinstance(list, Select):
+        return NOTINSubquery(item, list)
+    else:
+        return NOT(_IN(item, list))
+
 def LIKE(expr, string):
     return SQLOp("LIKE", expr, string)
 

Modified: trunk/SQLObject/sqlobject/tests/test_subqueries.py
===================================================================
--- trunk/SQLObject/sqlobject/tests/test_subqueries.py	2005-04-24 08:42:19 UTC (rev 735)
+++ trunk/SQLObject/sqlobject/tests/test_subqueries.py	2005-04-24 09:54:28 UTC (rev 736)
@@ -27,13 +27,13 @@
 
 def test_1syntax_in():
     setup()
-    select = TestIn1.select(INSubquery(TestIn1.q.col1, Select(TestIn2.q.col2)))
+    select = TestIn1.select(IN(TestIn1.q.col1, Select(TestIn2.q.col2)))
     assert str(select) == \
         "SELECT test_in1.id, test_in1.col1 FROM test_in1 WHERE test_in1.col1 IN (SELECT test_in2.col2 FROM test_in2)"
 
 def test_2perform_in():
     insert()
-    select = TestIn1.select(INSubquery(TestIn1.q.col1, Select(TestIn2.q.col2)))
+    select = TestIn1.select(IN(TestIn1.q.col1, Select(TestIn2.q.col2)))
     assert select.count() == 2
 
 def test_3syntax_exists():

[SQL-CVS] r735 - trunk/SQLObject/docs

[<sub...@co...>]

Author: phd
Date: 2005-04-24 08:42:19 +0000 (Sun, 24 Apr 2005)
New Revision: 735

Modified:
   trunk/SQLObject/docs/SQLObjectSubqueries.txt
Log:
Minor documentation update.


Modified: trunk/SQLObject/docs/SQLObjectSubqueries.txt
===================================================================
--- trunk/SQLObject/docs/SQLObjectSubqueries.txt	2005-04-22 04:41:27 UTC (rev 734)
+++ trunk/SQLObject/docs/SQLObjectSubqueries.txt	2005-04-24 08:42:19 UTC (rev 735)
@@ -16,3 +16,6 @@
 
 Note the usage of Outer - this is the helper to allow refering to a table
 in the outer query.
+
+Select() is used instead of .select() because you need to control what
+columns and in what order the inner query returns.

[SQL-CVS] r734 - trunk/SQLObject/sqlobject/manager

[<sub...@co...>]

Author: ianb
Date: 2005-04-22 04:41:27 +0000 (Fri, 22 Apr 2005)
New Revision: 734

Modified:
   trunk/SQLObject/sqlobject/manager/command.py
Log:
WSGIKit renamed to Paste

Modified: trunk/SQLObject/sqlobject/manager/command.py
===================================================================
--- trunk/SQLObject/sqlobject/manager/command.py	2005-04-21 15:18:01 UTC (rev 733)
+++ trunk/SQLObject/sqlobject/manager/command.py	2005-04-22 04:41:27 UTC (rev 734)
@@ -4,7 +4,7 @@
 import os
 import sys
 try:
-    from wsgikit import pyconfig
+    from paste import pyconfig
 except ImportError:
     pyconfig = None
 
@@ -71,7 +71,7 @@
                           dest='connection_uri')
         if pyconfig:
             parser.add_option('-f', '--config-file',
-                              help="The WSGIKit config file that contains the database URI (in the database key)",
+                              help="The Paste config file that contains the database URI (in the database key)",
                               metavar="FILE",
                               dest="config_file")
     if find_modules:
@@ -220,7 +220,7 @@
     def config(self):
         if getattr(self.options, 'config_file', None):
             assert pyconfig, (
-                "The --config-file option should not be available without wsgikit.pyconfig installed")
+                "The --config-file option should not be available without paste.pyconfig installed")
             config = pyconfig.Config()
             config.load(self.options.config_file)
             return config

[SQL-CVS] r733 - in trunk/SQLObject: docs sqlobject sqlobject/tests

[<sub...@co...>]

Author: phd
Date: 2005-04-21 15:18:01 +0000 (Thu, 21 Apr 2005)
New Revision: 733

Added:
   trunk/SQLObject/docs/SQLObjectSubqueries.txt
   trunk/SQLObject/sqlobject/tests/test_subqueries.py
Modified:
   trunk/SQLObject/docs/SQLObject.txt
   trunk/SQLObject/sqlobject/sqlbuilder.py
Log:
Implemented subqueries (subselects).


Modified: trunk/SQLObject/docs/SQLObject.txt
===================================================================
--- trunk/SQLObject/docs/SQLObject.txt	2005-04-20 21:43:11 UTC (rev 732)
+++ trunk/SQLObject/docs/SQLObject.txt	2005-04-21 15:18:01 UTC (rev 733)
@@ -39,6 +39,7 @@
 .. include:: SQLObjectDBConnection.txt
 .. include:: SQLObjectExported.txt
 .. include:: SQLObjectJoins.txt
+.. include:: SQLObjectSubqueries.txt
 
 For more information on SQLBuilder, read the `SQLBuilder
 Documentation`_.

Added: trunk/SQLObject/docs/SQLObjectSubqueries.txt
===================================================================
--- trunk/SQLObject/docs/SQLObjectSubqueries.txt	2005-04-20 21:43:11 UTC (rev 732)
+++ trunk/SQLObject/docs/SQLObjectSubqueries.txt	2005-04-21 15:18:01 UTC (rev 733)
@@ -0,0 +1,18 @@
+Subqueries (subselects)
+-----------------------
+
+You can run queries with subqueries (subselects) on those DBMS that can do
+subqueries (MySQL supports subqueries from version 4.1).
+
+Use corresponding classess and functions from sqlbuilder::
+
+    from sqlobject.sqlbuilder import EXISTS, Select
+    select = Test1.select(EXISTS(Select(Test2.q.col2, where=(Outer(Test1).q.col1 == Test2.q.col2))))
+
+generates the query::
+
+   SELECT test1.id, test1.col1 FROM test1 WHERE
+   EXISTS (SELECT test2.col2 FROM test2 WHERE (test1.col1 = test2.col2))
+
+Note the usage of Outer - this is the helper to allow refering to a table
+in the outer query.

Modified: trunk/SQLObject/sqlobject/sqlbuilder.py
===================================================================
--- trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-20 21:43:11 UTC (rev 732)
+++ trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-21 15:18:01 UTC (rev 733)
@@ -848,7 +848,70 @@
 def FULLOUTERJOINUsing(table1, table2, using_columns):
     return SQLJoinUsing(table1, table2, "FULL OUTER JOIN", using_columns)
 
+
 ########################################
+## Subqueries (subselects)
+########################################
+
+class OuterField(Field):
+    def tablesUsedImmediate(self):
+        return []
+
+class OuterTable(Table):
+    FieldClass = OuterField
+
+    def __init__(self, table):
+        if hasattr(table, "sqlmeta"):
+            tableName = table.sqlmeta.table
+        else:
+            tableName = table
+            table = None
+        Table.__init__(self, tableName)
+        self.table = table
+
+class Outer:
+    def __init__(self, table):
+        self.q = OuterTable(table)
+
+
+class INSubquery(SQLExpression):
+    op = "IN"
+
+    def __init__(self, item, subquery):
+        self.item = item
+        self.subquery = subquery
+    def __sqlrepr__(self, db):
+        return "%s %s (%s)" % (sqlrepr(self.item, db), self.op, sqlrepr(self.subquery, db))
+
+class NOTINSubquery(INSubquery):
+    op = "NOT IN"
+
+
+class Subquery(SQLExpression):
+    def __init__(self, op, subquery):
+        self.op = op
+        self.subquery = subquery
+
+    def __sqlrepr__(self, db):
+        return "%s (%s)" % (self.op, sqlrepr(self.subquery, db))
+
+def EXISTS(subquery):
+    return Subquery("EXISTS", subquery)
+
+def NOTEXISTS(subquery):
+    return Subquery("NOT EXISTS", subquery)
+
+def SOME(subquery):
+    return Subquery("SOME", subquery)
+
+def ANY(subquery):
+    return Subquery("ANY", subquery)
+
+def ALL(subquery):
+    return Subquery("ALL", subquery)
+
+
+########################################
 ## Global initializations
 ########################################
 

Added: trunk/SQLObject/sqlobject/tests/test_subqueries.py
===================================================================
--- trunk/SQLObject/sqlobject/tests/test_subqueries.py	2005-04-20 21:43:11 UTC (rev 732)
+++ trunk/SQLObject/sqlobject/tests/test_subqueries.py	2005-04-21 15:18:01 UTC (rev 733)
@@ -0,0 +1,48 @@
+from sqlobject import *
+from sqlobject.sqlbuilder import *
+from sqlobject.tests.dbtest import *
+
+########################################
+## Subqueries (subselects)
+########################################
+
+class TestIn1(SQLObject):
+    col1 = StringCol()
+
+class TestIn2(SQLObject):
+    col2 = StringCol()
+
+def setup():
+    setupClass(TestIn1)
+    setupClass(TestIn2)
+
+def insert():
+    setup()
+    TestIn1(col1=None)
+    TestIn1(col1='')
+    TestIn1(col1="test")
+    TestIn2(col2=None)
+    TestIn2(col2='')
+    TestIn2(col2="test")
+
+def test_1syntax_in():
+    setup()
+    select = TestIn1.select(INSubquery(TestIn1.q.col1, Select(TestIn2.q.col2)))
+    assert str(select) == \
+        "SELECT test_in1.id, test_in1.col1 FROM test_in1 WHERE test_in1.col1 IN (SELECT test_in2.col2 FROM test_in2)"
+
+def test_2perform_in():
+    insert()
+    select = TestIn1.select(INSubquery(TestIn1.q.col1, Select(TestIn2.q.col2)))
+    assert select.count() == 2
+
+def test_3syntax_exists():
+    setup()
+    select = TestIn1.select(NOTEXISTS(Select(TestIn2.q.col2, where=(Outer(TestIn1).q.col1 == TestIn2.q.col2))))
+    assert str(select) == \
+        "SELECT test_in1.id, test_in1.col1 FROM test_in1 WHERE NOT EXISTS (SELECT test_in2.col2 FROM test_in2 WHERE (test_in1.col1 = test_in2.col2))"
+
+def test_4perform_exists():
+    insert()
+    select = TestIn1.select(EXISTS(Select(TestIn2.q.col2, where=(Outer(TestIn1).q.col1 == TestIn2.q.col2))))
+    assert len(list(select)) == 2

[SQL-CVS] r732 - trunk/SQLObject/docs

[<sub...@co...>]

Author: ianb
Date: 2005-04-20 21:43:11 +0000 (Wed, 20 Apr 2005)
New Revision: 732

Modified:
   trunk/SQLObject/docs/News.txt
Log:
Fixed typo

Modified: trunk/SQLObject/docs/News.txt
===================================================================
--- trunk/SQLObject/docs/News.txt	2005-04-20 21:07:43 UTC (rev 731)
+++ trunk/SQLObject/docs/News.txt	2005-04-20 21:43:11 UTC (rev 732)
@@ -74,7 +74,7 @@
 * Separated database drivers (PostgresConnection, MySQLConnection,
   etc.) into separate packages.  You can access the driver through
   URIs, like ``mysql://user:pass@host/dbname`` -- to set drivers after
-  class creation you should use `sqlobject.dbconnection.openURI()`.
+  class creation you should use `sqlobject.connectionForURI()`.
 
 * The ``SQLObject`` package has been renamed to ``sqlobject``.  This
   makes it similar to several other packages, and emphasizes the

[SQL-CVS] r731 - trunk/SQLObject/docs

[<sub...@co...>]

Author: ianb
Date: 2005-04-20 21:07:43 +0000 (Wed, 20 Apr 2005)
New Revision: 731

Modified:
   trunk/SQLObject/docs/SQLObjectJoins.txt
Log:
Minor reformatting

Modified: trunk/SQLObject/docs/SQLObjectJoins.txt
===================================================================
--- trunk/SQLObject/docs/SQLObjectJoins.txt	2005-04-20 12:00:32 UTC (rev 730)
+++ trunk/SQLObject/docs/SQLObjectJoins.txt	2005-04-20 21:07:43 UTC (rev 731)
@@ -4,25 +4,32 @@
 First look in the FAQ_, question "How can I do a LEFT JOIN?"
 
 Still here? Well. To perform a JOIN use one of the JOIN helpers from
-sqlobject.sqlbuilder. Pass an instance of the helper to .select() method.
-For example::
+sqlobject.sqlbuilder. Pass an instance of the helper to .select()
+method.  For example::
 
     from sqlobject.sqlbuilder import LEFTJOINOn
-    MyTable.select(join=LEFTJOINOn(Table1, Table2, Table1.q.name == Table2.q.value))
+    MyTable.select(
+        join=LEFTJOINOn(Table1, Table2, 
+                        Table1.q.name == Table2.q.value))
 
 will generate the query::
 
-    SELECT * FROM my_table, table1 LEFT JOIN table2 ON table1.name = table2.value;
+    SELECT * FROM my_table, table1 
+    LEFT JOIN table2 ON table1.name = table2.value;
 
-.. _FAQ: FAQ.html
+.. _FAQ: FAQ.html#how-can-i-do-a-left-join
 
-If you want to join with the primary table - leave the first table None::
+If you want to join with the primary table - leave the first table
+None::
 
-    MyTable.select(join=LEFTJOINOn(None, Table1, MyTable.q.name == Table1.q.value))
+    MyTable.select(
+        join=LEFTJOINOn(None, Table1, 
+                        MyTable.q.name == Table1.q.value))
 
 will generate the query::
 
-    SELECT * FROM my_table LEFT JOIN table2 ON my_table.name = table1.value;
+    SELECT * FROM my_table 
+    LEFT JOIN table2 ON my_table.name = table1.value;
 
 The join argument for .select() can be a JOIN() or a list/tuples of
 JOIN()s.
@@ -39,13 +46,14 @@
 
 will generate the query::
 
-    SELECT * FROM my_table, my_table AS my_table_alias WHERE my_table.name = my_table_alias.value;
+    SELECT * FROM my_table, my_table AS my_table_alias 
+    WHERE my_table.name = my_table_alias.value;
 
 Can I use LEFTJOIN() with aliases?
 ----------------------------------
 
-Sure! That's a situation the JOINs an aliases were primary developed for.
-Code::
+Sure! That's a situation the JOINs and aliases were primary developed
+for.  Code::
 
     from sqlobject.sqlbuilder import LEFTJOINOn, Alias
     alias = Alias(OtherTable, "other_table_alias")

[SQL-CVS] r730 - in trunk/SQLObject/sqlobject: . tests

[<sub...@co...>]

Author: phd
Date: 2005-04-20 12:00:32 +0000 (Wed, 20 Apr 2005)
New Revision: 730

Modified:
   trunk/SQLObject/sqlobject/dbconnection.py
   trunk/SQLObject/sqlobject/tests/test_aliases.py
Log:
Removed an excessive space before WHERE.


Modified: trunk/SQLObject/sqlobject/dbconnection.py
===================================================================
--- trunk/SQLObject/sqlobject/dbconnection.py	2005-04-19 11:41:17 UTC (rev 729)
+++ trunk/SQLObject/sqlobject/dbconnection.py	2005-04-20 12:00:32 UTC (rev 730)
@@ -351,7 +351,7 @@
         else:
             tables = select.tables
         q = "SELECT %s" % ", ".join([str(expression) for expression in expressions])
-        q += " FROM %s " % ", ".join(tables)
+        q += " FROM %s" % ", ".join(tables)
         if join: q += self._addJoins(select)
         q += " WHERE"
         q = self._addWhereClause(select, q, limit=0, order=0)
@@ -373,18 +373,18 @@
         else:
             q = 'SELECT '
         if ops.get('lazyColumns', 0):
-            q += "%s.%s FROM %s " % \
+            q += "%s.%s FROM %s" % \
                  (cls.sqlmeta.table, cls.sqlmeta.idName,
                   ", ".join(tables))
         else:
             columns = ", ".join(["%s.%s" % (cls.sqlmeta.table, col.dbName)
                                  for col in cls.sqlmeta._columns])
             if columns:
-                q += "%s.%s, %s FROM %s " % \
+                q += "%s.%s, %s FROM %s" % \
                      (cls.sqlmeta.table, cls.sqlmeta.idName, columns,
                       ", ".join(tables))
             else:
-                q += "%s.%s FROM %s " % \
+                q += "%s.%s FROM %s" % \
                      (cls.sqlmeta.table, cls.sqlmeta.idName,
                       ", ".join(tables))
 
@@ -413,11 +413,12 @@
         ops = select.ops
         join = ops.get('join')
         if type(join) is str:
-            return join
+            pass
         elif isinstance(join, sqlbuilder.SQLJoin):
-            return self.sqlrepr(join)
+            join_str = self.sqlrepr(join)
         else:
-            return ", ".join([self.sqlrepr(j) for j in join])
+            join_str = ", ".join([self.sqlrepr(j) for j in join])
+        return ' ' + join_str
 
     def _addWhereClause(self, select, startSelect, limit=1, order=1):
 

Modified: trunk/SQLObject/sqlobject/tests/test_aliases.py
===================================================================
--- trunk/SQLObject/sqlobject/tests/test_aliases.py	2005-04-19 11:41:17 UTC (rev 729)
+++ trunk/SQLObject/sqlobject/tests/test_aliases.py	2005-04-20 12:00:32 UTC (rev 730)
@@ -15,7 +15,7 @@
     alias = Alias(TestJoinAlias)
     select = TestJoinAlias.select(TestJoinAlias.q.parent == alias.q.name)
     assert str(select) == \
-        "SELECT test_join_alias.id, test_join_alias.name, test_join_alias.parent FROM test_join_alias, test_join_alias AS test_join_alias_alias1  WHERE (test_join_alias.parent = test_join_alias_alias1.name)"
+        "SELECT test_join_alias.id, test_join_alias.name, test_join_alias.parent FROM test_join_alias, test_join_alias AS test_join_alias_alias1 WHERE (test_join_alias.parent = test_join_alias_alias1.name)"
 
 def test_2perform_join():
     setupClass(TestJoinAlias)

[SQL-CVS] r729 - trunk/SQLObject/sqlobject

[<sub...@co...>]

Author: phd
Date: 2005-04-19 11:41:17 +0000 (Tue, 19 Apr 2005)
New Revision: 729

Modified:
   trunk/SQLObject/sqlobject/sqlbuilder.py
Log:
Fixed a bug - allow an aliased table to translate attribute name via
original table; for example alias.q.actID => "act_id".


Modified: trunk/SQLObject/sqlobject/sqlbuilder.py
===================================================================
--- trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-18 15:04:43 UTC (rev 728)
+++ trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-19 11:41:17 UTC (rev 729)
@@ -396,8 +396,14 @@
     _alias_lock = threading.Lock()
     _alias_counter = 0
 
-    def __init__(self, tableName, alias=None):
+    def __init__(self, table, alias=None):
+        if hasattr(table, "sqlmeta"):
+            tableName = table.sqlmeta.table
+        else:
+            tableName = table
+            table = None
         Table.__init__(self, tableName)
+        self.table = table
         if alias is None:
             self._alias_lock.acquire()
             try:
@@ -410,12 +416,12 @@
     def __getattr__(self, attr):
         if attr.startswith('__'):
             raise AttributeError
+        if self.table:
+            attr = getattr(self.table.q, attr).fieldName
         return self.FieldClass(self.tableName, attr, self.alias)
 
 class Alias:
     def __init__(self, table, alias=None):
-        if hasattr(table, "sqlmeta"):
-            table = table.sqlmeta.table
         self.q = AliasTable(table, alias)
 
 

[SQL-CVS] r728 - trunk/SQLObject/docs

[<sub...@co...>]

Author: phd
Date: 2005-04-18 15:04:43 +0000 (Mon, 18 Apr 2005)
New Revision: 728

Added:
   trunk/SQLObject/docs/SQLObjectJoins.txt
Modified:
   trunk/SQLObject/docs/FAQ.txt
   trunk/SQLObject/docs/SQLObject.txt
Log:
Added documentation for JOINs and aliases.


Modified: trunk/SQLObject/docs/FAQ.txt
===================================================================
--- trunk/SQLObject/docs/FAQ.txt	2005-04-18 14:36:32 UTC (rev 727)
+++ trunk/SQLObject/docs/FAQ.txt	2005-04-18 15:04:43 UTC (rev 728)
@@ -54,6 +54,18 @@
 .. raw:: html
    :file: ../examples/snippets/leftjoin-more-query.html
 
+SQL-wise
+~~~~~~~~
+
+Use LEFTJOIN() from sqlobject.sqlbuilder.
+
+
+How can I join a table with itself?
+-----------------------------------
+
+Use Alias from sqlobject.sqlbuilder.
+
+
 How Does Inheritance Work?
 --------------------------
 

Modified: trunk/SQLObject/docs/SQLObject.txt
===================================================================
--- trunk/SQLObject/docs/SQLObject.txt	2005-04-18 14:36:32 UTC (rev 727)
+++ trunk/SQLObject/docs/SQLObject.txt	2005-04-18 15:04:43 UTC (rev 728)
@@ -38,6 +38,7 @@
 .. include:: SQLObjectLegacy.txt
 .. include:: SQLObjectDBConnection.txt
 .. include:: SQLObjectExported.txt
+.. include:: SQLObjectJoins.txt
 
 For more information on SQLBuilder, read the `SQLBuilder
 Documentation`_.

Added: trunk/SQLObject/docs/SQLObjectJoins.txt
===================================================================
--- trunk/SQLObject/docs/SQLObjectJoins.txt	2005-04-18 14:36:32 UTC (rev 727)
+++ trunk/SQLObject/docs/SQLObjectJoins.txt	2005-04-18 15:04:43 UTC (rev 728)
@@ -0,0 +1,60 @@
+LEFT JOIN and other JOINs
+-------------------------
+
+First look in the FAQ_, question "How can I do a LEFT JOIN?"
+
+Still here? Well. To perform a JOIN use one of the JOIN helpers from
+sqlobject.sqlbuilder. Pass an instance of the helper to .select() method.
+For example::
+
+    from sqlobject.sqlbuilder import LEFTJOINOn
+    MyTable.select(join=LEFTJOINOn(Table1, Table2, Table1.q.name == Table2.q.value))
+
+will generate the query::
+
+    SELECT * FROM my_table, table1 LEFT JOIN table2 ON table1.name = table2.value;
+
+.. _FAQ: FAQ.html
+
+If you want to join with the primary table - leave the first table None::
+
+    MyTable.select(join=LEFTJOINOn(None, Table1, MyTable.q.name == Table1.q.value))
+
+will generate the query::
+
+    SELECT * FROM my_table LEFT JOIN table2 ON my_table.name = table1.value;
+
+The join argument for .select() can be a JOIN() or a list/tuples of
+JOIN()s.
+
+
+How can I join a table with itself?
+-----------------------------------
+
+Use Alias from sqlobject.sqlbuilder. Example::
+
+    from sqlobject.sqlbuilder import Alias
+    alias = Alias(MyTable, "my_table_alias")
+    MyTable.select(MyTable.q.name == alias.q.value)
+
+will generate the query::
+
+    SELECT * FROM my_table, my_table AS my_table_alias WHERE my_table.name = my_table_alias.value;
+
+Can I use LEFTJOIN() with aliases?
+----------------------------------
+
+Sure! That's a situation the JOINs an aliases were primary developed for.
+Code::
+
+    from sqlobject.sqlbuilder import LEFTJOINOn, Alias
+    alias = Alias(OtherTable, "other_table_alias")
+    MyTable.select(MyTable.q.name == OtherTable.q.value,
+        join=LEFTJOINOn(MyTable, alias, MyTable.col1 == alias.q.col2))
+
+will result in the query::
+
+    SELECT * FROM other_table,
+        my_table LEFT JOIN other_table AS other_table_alias
+    WHERE my_table.name == other_table.value AND
+        my_table.col1 = other_table_alias.col2.

[SQL-CVS] r727 - in trunk/SQLObject/sqlobject: . tests

[<sub...@co...>]

Author: phd
Date: 2005-04-18 14:36:32 +0000 (Mon, 18 Apr 2005)
New Revision: 727

Added:
   trunk/SQLObject/sqlobject/tests/test_aliases.py
Modified:
   trunk/SQLObject/sqlobject/sqlbuilder.py
Log:
Implemented table aliases; now one can join a table with itself.


Modified: trunk/SQLObject/sqlobject/sqlbuilder.py
===================================================================
--- trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-18 14:23:23 UTC (rev 726)
+++ trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-18 14:36:32 UTC (rev 727)
@@ -69,6 +69,7 @@
 
 import re, fnmatch
 import operator
+import threading
 from converters import sqlrepr, registerConverter, TRUE, FALSE
 
 safeSQLRE = re.compile(r'^[a-zA-Z][a-zA-Z0-9_\.]*$')
@@ -307,25 +308,40 @@
 ## Namespaces
 ########################################
 
-class TableSpace:
-    def __getattr__(self, attr):
-        if attr.startswith('__'):
-            raise AttributeError
-        return Table(attr)
+class Field(SQLExpression):
+    def __init__(self, tableName, fieldName):
+        self.tableName = tableName
+        self.fieldName = fieldName
+    def __sqlrepr__(self, db):
+        return self.tableName + "." + self.fieldName
+    def tablesUsedImmediate(self):
+        return [self.tableName]
+    def execute(self, executor):
+        return executor.field(self.tableName, self.fieldName)
 
+class SQLObjectField(Field):
+    def __init__(self, tableName, fieldName, original):
+        self.original = original
+        Field.__init__(self, tableName, fieldName)
+
+registerConverter(SQLObjectField, SQLExprConverter)
+
 class Table(SQLExpression):
+    FieldClass = Field
+
     def __init__(self, tableName):
         self.tableName = tableName
     def __getattr__(self, attr):
         if attr.startswith('__'):
             raise AttributeError
-        return Field(self.tableName, attr)
+        return self.FieldClass(self.tableName, attr)
     def __sqlrepr__(self, db):
         return str(self.tableName)
     def execute(self, executor):
         raise ValueError, "Tables don't have values"
 
 class SQLObjectTable(Table):
+    FieldClass = SQLObjectField
 
     def __init__(self, soClass):
         self.soClass = soClass
@@ -338,37 +354,71 @@
         if attr.startswith('__'):
             raise AttributeError
         if attr == 'id':
-            return SQLObjectField(self.tableName, self.soClass.sqlmeta.idName, attr)
+            return self.FieldClass(self.tableName, self.soClass.sqlmeta.idName, attr)
         else:
-            return SQLObjectField(self.tableName,
+            return self.FieldClass(self.tableName,
                                   self.soClass.sqlmeta._columnDict[attr].dbName,
                                   attr)
 
-class Field(SQLExpression):
-    def __init__(self, tableName, fieldName):
-        self.tableName = tableName
-        self.fieldName = fieldName
+class TableSpace:
+    TableClass = Table
+
+    def __getattr__(self, attr):
+        if attr.startswith('__'):
+            raise AttributeError
+        return self.TableClass(attr)
+
+class ConstantSpace:
+    def __getattr__(self, attr):
+        if attr.startswith('__'):
+            raise AttributeError
+        return SQLConstant(attr)
+
+
+########################################
+## Table aliases
+########################################
+
+class AliasField(Field):
+    def __init__(self, tableName, fieldName, alias):
+        Field.__init__(self, tableName, fieldName)
+        self.alias = alias
+
     def __sqlrepr__(self, db):
-        return self.tableName + "." + self.fieldName
+        return self.alias + "." + self.fieldName
+
     def tablesUsedImmediate(self):
-        return [self.tableName]
-    def execute(self, executor):
-        return executor.field(self.tableName, self.fieldName)
+        return ["%s AS %s" % (self.tableName, self.alias)]
 
-class SQLObjectField(Field):
-    def __init__(self, tableName, fieldName, original):
-        self.original = original
-        Field.__init__(self, tableName, fieldName)
+class AliasTable(Table):
+    FieldClass = AliasField
 
-registerConverter(SQLObjectField, SQLExprConverter)
+    _alias_lock = threading.Lock()
+    _alias_counter = 0
 
-class ConstantSpace:
+    def __init__(self, tableName, alias=None):
+        Table.__init__(self, tableName)
+        if alias is None:
+            self._alias_lock.acquire()
+            try:
+                AliasTable._alias_counter += 1
+                alias = "%s_alias%d" % (tableName, AliasTable._alias_counter)
+            finally:
+                self._alias_lock.release()
+        self.alias = alias
+
     def __getattr__(self, attr):
         if attr.startswith('__'):
             raise AttributeError
-        return SQLConstant(attr)
+        return self.FieldClass(self.tableName, attr, self.alias)
 
+class Alias:
+    def __init__(self, table, alias=None):
+        if hasattr(table, "sqlmeta"):
+            table = table.sqlmeta.table
+        self.q = AliasTable(table, alias)
 
+
 ########################################
 ## SQL Statements
 ########################################
@@ -599,8 +649,16 @@
 
 class SQLJoin(SQLExpression):
     def __init__(self, table1, table2, op=','):
-        if table1 and type(table1) <> str: table1 = table1.sqlmeta.table
-        if type(table2) <> str: table2 = table2.sqlmeta.table
+        if table1 and type(table1) <> str:
+            if isinstance(table1, Alias):
+                table1 = "%s AS %s" % (table1.q.tableName, table1.q.alias)
+            else:
+                table1 = table1.sqlmeta.table
+        if type(table2) <> str:
+            if isinstance(table2, Alias):
+                table2 = "%s AS %s" % (table2.q.tableName, table2.q.alias)
+            else:
+                table2 = table2.sqlmeta.table
         self.table1 = table1
         self.table2 = table2
         self.op = op

Added: trunk/SQLObject/sqlobject/tests/test_aliases.py
===================================================================
--- trunk/SQLObject/sqlobject/tests/test_aliases.py	2005-04-18 14:23:23 UTC (rev 726)
+++ trunk/SQLObject/sqlobject/tests/test_aliases.py	2005-04-18 14:36:32 UTC (rev 727)
@@ -0,0 +1,27 @@
+from sqlobject import *
+from sqlobject.sqlbuilder import *
+from sqlobject.tests.dbtest import *
+
+########################################
+## Table aliases and self-joins
+########################################
+
+class TestJoinAlias(SQLObject):
+    name = StringCol()
+    parent = StringCol()
+
+def test_1syntax():
+    setupClass(TestJoinAlias)
+    alias = Alias(TestJoinAlias)
+    select = TestJoinAlias.select(TestJoinAlias.q.parent == alias.q.name)
+    assert str(select) == \
+        "SELECT test_join_alias.id, test_join_alias.name, test_join_alias.parent FROM test_join_alias, test_join_alias AS test_join_alias_alias1  WHERE (test_join_alias.parent = test_join_alias_alias1.name)"
+
+def test_2perform_join():
+    setupClass(TestJoinAlias)
+    TestJoinAlias(name="grandparent", parent=None)
+    TestJoinAlias(name="parent", parent="grandparent")
+    TestJoinAlias(name="child", parent="parent")
+    alias = Alias(TestJoinAlias)
+    select = TestJoinAlias.select(TestJoinAlias.q.parent == alias.q.name)
+    assert select.count() == 2

[SQL-CVS] r726 - in trunk/SQLObject/sqlobject: . tests

[<sub...@co...>]

Author: phd
Date: 2005-04-18 14:23:23 +0000 (Mon, 18 Apr 2005)
New Revision: 726

Added:
   trunk/SQLObject/sqlobject/tests/test_joins_conditional.py
Modified:
   trunk/SQLObject/sqlobject/dbconnection.py
   trunk/SQLObject/sqlobject/main.py
   trunk/SQLObject/sqlobject/sqlbuilder.py
   trunk/SQLObject/sqlobject/sresults.py
Log:
Implemented LEFT/RIGHT/STRAIGHT INNER/OUTER/CROSS JOINs, either NATURAL or conditional.


Modified: trunk/SQLObject/sqlobject/dbconnection.py
===================================================================
--- trunk/SQLObject/sqlobject/dbconnection.py	2005-04-15 21:53:30 UTC (rev 725)
+++ trunk/SQLObject/sqlobject/dbconnection.py	2005-04-18 14:23:23 UTC (rev 726)
@@ -344,8 +344,16 @@
         """ Apply an accumulate function(s) (SUM, COUNT, MIN, AVG, MAX, etc...)
             to the select object.
         """
+        ops = select.ops
+        join = ops.get('join')
+        if join:
+            tables = self._fixTablesForJoins(select)
+        else:
+            tables = select.tables
         q = "SELECT %s" % ", ".join([str(expression) for expression in expressions])
-        q += " FROM %s WHERE" % ", ".join(select.tables)
+        q += " FROM %s " % ", ".join(tables)
+        if join: q += self._addJoins(select)
+        q += " WHERE"
         q = self._addWhereClause(select, q, limit=0, order=0)
         val = self.queryOne(q)
         if len(expressions) == 1:
@@ -354,29 +362,63 @@
 
     def queryForSelect(self, select):
         ops = select.ops
+        join = ops.get('join')
         cls = select.sourceClass
+        if join:
+            tables = self._fixTablesForJoins(select)
+        else:
+            tables = select.tables
         if ops.get('distinct', False):
             q = 'SELECT DISTINCT '
         else:
             q = 'SELECT '
         if ops.get('lazyColumns', 0):
-            q += "%s.%s FROM %s WHERE " % \
+            q += "%s.%s FROM %s " % \
                  (cls.sqlmeta.table, cls.sqlmeta.idName,
-                  ", ".join(select.tables))
+                  ", ".join(tables))
         else:
             columns = ", ".join(["%s.%s" % (cls.sqlmeta.table, col.dbName)
                                  for col in cls.sqlmeta._columns])
             if columns:
-                q += "%s.%s, %s FROM %s WHERE " % \
+                q += "%s.%s, %s FROM %s " % \
                      (cls.sqlmeta.table, cls.sqlmeta.idName, columns,
-                      ", ".join(select.tables))
+                      ", ".join(tables))
             else:
-                q += "%s.%s FROM %s WHERE " % \
+                q += "%s.%s FROM %s " % \
                      (cls.sqlmeta.table, cls.sqlmeta.idName,
-                      ", ".join(select.tables))
+                      ", ".join(tables))
 
+        if join: q += self._addJoins(select)
+        q += " WHERE"
         return self._addWhereClause(select, q)
 
+    def _fixTablesForJoins(self, select):
+        ops = select.ops
+        join = ops.get('join')
+        tables = select.tables
+        if type(join) is str:
+            return tables
+        else:
+            tables = tables[:] # maka a copy for modification
+            if isinstance(join, sqlbuilder.SQLJoin):
+                if join.table1 in tables: tables.remove(join.table1)
+                if join.table2 in tables: tables.remove(join.table2)
+            else:
+                for j in join:
+                    if j.table1 in tables: tables.remove(j.table1)
+                    if j.table2 in tables: tables.remove(j.table2)
+            return tables
+
+    def _addJoins(self, select):
+        ops = select.ops
+        join = ops.get('join')
+        if type(join) is str:
+            return join
+        elif isinstance(join, sqlbuilder.SQLJoin):
+            return self.sqlrepr(join)
+        else:
+            return ", ".join([self.sqlrepr(j) for j in join])
+
     def _addWhereClause(self, select, startSelect, limit=1, order=1):
 
         q = select.clause

Modified: trunk/SQLObject/sqlobject/main.py
===================================================================
--- trunk/SQLObject/sqlobject/main.py	2005-04-15 21:53:30 UTC (rev 725)
+++ trunk/SQLObject/sqlobject/main.py	2005-04-18 14:23:23 UTC (rev 726)
@@ -1161,8 +1161,8 @@
     def select(cls, clause=None, clauseTables=None,
                orderBy=NoDefault, limit=None,
                lazyColumns=False, reversed=False,
-               distinct=False,
-               connection=None):
+               distinct=False, connection=None,
+               join=None):
         return cls.SelectResultsClass(cls, clause,
                              clauseTables=clauseTables,
                              orderBy=orderBy,
@@ -1170,7 +1170,8 @@
                              lazyColumns=lazyColumns,
                              reversed=reversed,
                              distinct=distinct,
-                             connection=connection)
+                             connection=connection,
+                             join=join)
     select = classmethod(select)
 
     def selectBy(cls, connection=None, **kw):

Modified: trunk/SQLObject/sqlobject/sqlbuilder.py
===================================================================
--- trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-15 21:53:30 UTC (rev 725)
+++ trunk/SQLObject/sqlobject/sqlbuilder.py	2005-04-18 14:23:23 UTC (rev 726)
@@ -157,7 +157,7 @@
         except AssertionError:
             return '<%s %s>' % (
                 self.__class__.__name__, hex(id(self))[2:])
-        
+
     def __str__(self):
         return repr(self)
 
@@ -594,6 +594,197 @@
         return "'%s%s%s'" % (self.prefix, s, self.postfix)
 
 ########################################
+## SQL JOINs
+########################################
+
+class SQLJoin(SQLExpression):
+    def __init__(self, table1, table2, op=','):
+        if table1 and type(table1) <> str: table1 = table1.sqlmeta.table
+        if type(table2) <> str: table2 = table2.sqlmeta.table
+        self.table1 = table1
+        self.table2 = table2
+        self.op = op
+
+    def __sqlrepr__(self, db):
+        if self.table1:
+            return "%s%s %s" % (self.table1, self.op, self.table2)
+        else:
+            return "%s %s" % (self.op, self.table2)
+
+registerConverter(SQLJoin, SQLExprConverter)
+
+def JOIN(table1, table2):
+    return SQLJoin(table1, table2, " JOIN")
+
+def INNERJOIN(table1, table2):
+    return SQLJoin(table1, table2, " INNER JOIN")
+
+def CROSSJOIN(table1, table2):
+    return SQLJoin(table1, table2, " CROSS JOIN")
+
+def STRAIGHTJOIN(table1, table2):
+    return SQLJoin(table1, table2, " STRAIGHT JOIN")
+
+def LEFTJOIN(table1, table2):
+    return SQLJoin(table1, table2, " LEFT JOIN")
+
+def LEFTOUTERJOIN(table1, table2):
+    return SQLJoin(table1, table2, " LEFT OUTER JOIN")
+
+def NATURALJOIN(table1, table2):
+    return SQLJoin(table1, table2, " NATURAL JOIN")
+
+def NATURALLEFTJOIN(table1, table2):
+    return SQLJoin(table1, table2, " NATURAL LEFT JOIN")
+
+def NATURALLEFTOUTERJOIN(table1, table2):
+    return SQLJoin(table1, table2, " NATURAL LEFT OUTER JOIN")
+
+def RIGHTJOIN(table1, table2):
+    return SQLJoin(table1, table2, " RIGHT JOIN")
+
+def RIGHTOUTERJOIN(table1, table2):
+    return SQLJoin(table1, table2, " RIGHT OUTER JOIN")
+
+def NATURALRIGHTJOIN(table1, table2):
+    return SQLJoin(table1, table2, " NATURAL RIGHT JOIN")
+
+def NATURALRIGHTOUTERJOIN(table1, table2):
+    return SQLJoin(table1, table2, " NATURAL RIGHT OUTER JOIN")
+
+def FULLJOIN(table1, table2):
+    return SQLJoin(table1, table2, " FULL JOIN")
+
+def FULLOUTERJOIN(table1, table2):
+    return SQLJoin(table1, table2, " FULL OUTER JOIN")
+
+def NATURALFULLJOIN(table1, table2):
+    return SQLJoin(table1, table2, " NATURAL FULL JOIN")
+
+def NATURALFULLOUTERJOIN(table1, table2):
+    return SQLJoin(table1, table2, " NATURAL FULL OUTER JOIN")
+
+class SQLJoinConditional(SQLJoin):
+    """Conditional JOIN"""
+    def __init__(self, table1, table2, op, on_condition=None, using_columns=None):
+        """For condition you must give on_condition or using_columns but not both
+
+            on_condition can be a string or SQLExpression, for example
+                Table1.q.col1 == Table2.q.col2
+            using_columns can be a string or a list of columns, e.g.
+                (Table1.q.col1, Table2.q.col2)
+        """
+        if not on_condition and not using_columns:
+            raise TypeError, "You must give ON condition or USING columns"
+        if on_condition and using_columns:
+            raise TypeError, "You must give ON condition or USING columns but not both"
+        SQLJoin.__init__(self, table1, table2, op)
+        self.on_condition = on_condition
+        self.using_columns = using_columns
+
+    def __sqlrepr__(self, db):
+        if self.on_condition:
+            on_condition = self.on_condition
+            if hasattr(on_condition, "__sqlrepr__"):
+                on_condition = sqlrepr(on_condition, db)
+            join = "%s %s ON %s" % (self.op, self.table2, on_condition)
+            if self.table1:
+                join = "%s %s" % (self.table1, join)
+            return join
+        elif self.using_columns:
+            using_columns = []
+            for col in self.using_columns:
+                if hasattr(col, "__sqlrepr__"):
+                    col = sqlrepr(col, db)
+                using_columns.append(col)
+            using_columns = ", ".join()
+            join = "%s %s USING (%s)" % (self.op, self.table2, using_columns)
+            if self.table1:
+                join = "%s %s" % (self.table1, join)
+            return join
+        else:
+            RuntimeError, "Impossible error"
+
+registerConverter(SQLJoinConditional, SQLExprConverter)
+
+def INNERJOINConditional(table1, table2, on_condition=None, using_columns=None):
+    return SQLJoinConditional(table1, table2, "INNER JOIN", on_condition, using_columns)
+
+def LEFTJOINConditional(table1, table2, on_condition=None, using_columns=None):
+    return SQLJoinConditional(table1, table2, "LEFT JOIN", on_condition, using_columns)
+
+def LEFTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
+    return SQLJoinConditional(table1, table2, "LEFT OUTER JOIN", on_condition, using_columns)
+
+def RIGHTJOINConditional(table1, table2, on_condition=None, using_columns=None):
+    return SQLJoinConditional(table1, table2, "RIGHT JOIN", on_condition, using_columns)
+
+def RIGHTOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
+    return SQLJoinConditional(table1, table2, "RIGHT OUTER JOIN", on_condition, using_columns)
+
+def FULLJOINConditional(table1, table2, on_condition=None, using_columns=None):
+    return SQLJoinConditional(table1, table2, "FULL JOIN", on_condition, using_columns)
+
+def FULLOUTERJOINConditional(table1, table2, on_condition=None, using_columns=None):
+    return SQLJoinConditional(table1, table2, "FULL OUTER JOIN", on_condition, using_columns)
+
+class SQLJoinOn(SQLJoinConditional):
+    """Conditional JOIN ON"""
+    def __init__(self, table1, table2, op, on_condition):
+        SQLJoinConditional.__init__(self, table1, table2, op, on_condition)
+
+registerConverter(SQLJoinOn, SQLExprConverter)
+
+class SQLJoinUsing(SQLJoinConditional):
+    """Conditional JOIN USING"""
+    def __init__(self, table1, table2, op, using_columns):
+        SQLJoinConditional.__init__(self, table1, table2, op, None, using_columns)
+
+registerConverter(SQLJoinUsing, SQLExprConverter)
+
+def INNERJOINOn(table1, table2, on_condition):
+    return SQLJoinOn(table1, table2, "INNER JOIN", on_condition)
+
+def LEFTJOINOn(table1, table2, on_condition):
+    return SQLJoinOn(table1, table2, "LEFT JOIN", on_condition)
+
+def LEFTOUTERJOINOn(table1, table2, on_condition):
+    return SQLJoinOn(table1, table2, "LEFT OUTER JOIN", on_condition)
+
+def RIGHTJOINOn(table1, table2, on_condition):
+    return SQLJoinOn(table1, table2, "RIGHT JOIN", on_condition)
+
+def RIGHTOUTERJOINOn(table1, table2, on_condition):
+    return SQLJoinOn(table1, table2, "RIGHT OUTER JOIN", on_condition)
+
+def FULLJOINOn(table1, table2, on_condition):
+    return SQLJoinOn(table1, table2, "FULL JOIN", on_condition)
+
+def FULLOUTERJOINOn(table1, table2, on_condition):
+    return SQLJoinOn(table1, table2, "FULL OUTER JOIN", on_condition)
+
+def INNERJOINUsing(table1, table2, using_columns):
+    return SQLJoinUsing(table1, table2, "INNER JOIN", using_columns)
+
+def LEFTJOINUsing(table1, table2, using_columns):
+    return SQLJoinUsing(table1, table2, "LEFT JOIN", using_columns)
+
+def LEFTOUTERJOINUsing(table1, table2, using_columns):
+    return SQLJoinUsing(table1, table2, "LEFT OUTER JOIN", using_columns)
+
+def RIGHTJOINUsing(table1, table2, using_columns):
+    return SQLJoinUsing(table1, table2, "RIGHT JOIN", using_columns)
+
+def RIGHTOUTERJOINUsing(table1, table2, using_columns):
+    return SQLJoinUsing(table1, table2, "RIGHT OUTER JOIN", using_columns)
+
+def FULLJOINUsing(table1, table2, using_columns):
+    return SQLJoinUsing(table1, table2, "FULL JOIN", using_columns)
+
+def FULLOUTERJOINUsing(table1, table2, using_columns):
+    return SQLJoinUsing(table1, table2, "FULL OUTER JOIN", using_columns)
+
+########################################
 ## Global initializations
 ########################################
 

Modified: trunk/SQLObject/sqlobject/sresults.py
===================================================================
--- trunk/SQLObject/sqlobject/sresults.py	2005-04-15 21:53:30 UTC (rev 725)
+++ trunk/SQLObject/sqlobject/sresults.py	2005-04-18 14:23:23 UTC (rev 726)
@@ -31,6 +31,13 @@
         if ops.has_key('connection') and ops['connection'] is None:
             del ops['connection']
 
+    def __repr__(self):
+        return "<%s at %x>" % (self.__class__.__name__, id(self))
+
+    def __str__(self):
+        conn = self.ops.get('connection', self.sourceClass._connection)
+        return conn.queryForSelect(self)
+
     def _mungeOrderBy(self, orderBy):
         if isinstance(orderBy, str) and orderBy.startswith('-'):
             orderBy = orderBy[1:]

Added: trunk/SQLObject/sqlobject/tests/test_joins_conditional.py
===================================================================
--- trunk/SQLObject/sqlobject/tests/test_joins_conditional.py	2005-04-15 21:53:30 UTC (rev 725)
+++ trunk/SQLObject/sqlobject/tests/test_joins_conditional.py	2005-04-18 14:23:23 UTC (rev 726)
@@ -0,0 +1,48 @@
+from sqlobject import *
+from sqlobject.sqlbuilder import *
+from sqlobject.tests.dbtest import *
+
+########################################
+## Condiotional joins
+########################################
+
+class TestJoin1(SQLObject):
+    col1 = StringCol()
+
+class TestJoin2(SQLObject):
+    col2 = StringCol()
+
+def setup():
+    setupClass(TestJoin1)
+    setupClass(TestJoin2)
+
+def test_1syntax():
+    setup()
+    join = JOIN("table1", "table2")
+    assert str(join) == "table1 JOIN table2"
+    join = LEFTJOIN("table1", "table2")
+    assert str(join) == "table1 LEFT JOIN table2"
+    join = LEFTJOINOn("table1", "table2", "tabl1.col1 = table2.col2")
+    assert getConnection().sqlrepr(join) == "table1 LEFT JOIN table2 ON tabl1.col1 = table2.col2"
+
+def test_2select_syntax():
+    setup()
+    select = TestJoin1.select(
+        join=LEFTJOINConditional(None, TestJoin2,
+            on_condition=(TestJoin1.q.col1 == TestJoin2.q.col2))
+    )
+    assert str(select) == \
+        "SELECT test_join1.id, test_join1.col1 FROM test_join1 LEFT JOIN test_join2 ON (test_join1.col1 = test_join2.col2) WHERE 1 = 1"
+
+def test_3perform_join():
+    setup()
+    TestJoin1(col1="test1")
+    TestJoin1(col1="test2")
+    TestJoin1(col1="test3")
+    TestJoin2(col2="test1")
+    TestJoin2(col2="test2")
+
+    select = TestJoin1.select(
+        join=LEFTJOINOn(None, TestJoin2, TestJoin1.q.col1 == TestJoin2.q.col2)
+    )
+    assert select.count() == 3

[SQL-CVS] r721 - in trunk/SQLObject/sqlobject: . tests

[<sub...@co...>]

Author: phd
Date: 2005-04-11 19:32:23 +0000 (Mon, 11 Apr 2005)
New Revision: 721

Added:
   trunk/SQLObject/sqlobject/tests/test_selectBy_foreignKey.py
Modified:
   trunk/SQLObject/sqlobject/dbconnection.py
Log:
Applied the bugfix 1176359 by David M. Cook and Viktor Ferenczi
for .selectBy(foreignKey=value) instead of .selectBy(foreignKeyID=value).


Modified: trunk/SQLObject/sqlobject/dbconnection.py
===================================================================
--- trunk/SQLObject/sqlobject/dbconnection.py	2005-04-08 05:47:51 UTC (rev 720)
+++ trunk/SQLObject/sqlobject/dbconnection.py	2005-04-11 19:32:23 UTC (rev 721)
@@ -555,12 +555,24 @@
 
     def _SO_columnClause(self, soClass, kw):
         ops = {None: "IS"}
+	data = {}
+	if 'id' in kw:
+	    data[soClass.sqlmeta.idName] = kw['id']
+	else:
+	    for key, col in soClass.sqlmeta._columnDict.items():
+	    	if key in kw:
+		    data[col.dbName] = kw[key]
+	    	elif col.foreignName in kw:
+		    obj = kw[col.foreignName]
+		    if obj is None:
+		    	data[col.dbName] = None
+		    else:
+		    	data[col.dbName] = obj.id
         return ' AND '.join(
             ['%s %s %s' %
-             (soClass.sqlmeta._columnDict[key].dbName,
-              ops.get(value, "="), self.sqlrepr(value))
-             for key, value
-             in kw.items()])
+             (dbName, ops.get(value, "="), self.sqlrepr(value))
+             for dbName, value
+             in data.items()])
 
     def sqlrepr(self, v):
         return sqlrepr(v, self.dbName)

Added: trunk/SQLObject/sqlobject/tests/test_selectBy_foreignKey.py
===================================================================
--- trunk/SQLObject/sqlobject/tests/test_selectBy_foreignKey.py	2005-04-08 05:47:51 UTC (rev 720)
+++ trunk/SQLObject/sqlobject/tests/test_selectBy_foreignKey.py	2005-04-11 19:32:23 UTC (rev 721)
@@ -0,0 +1,33 @@
+from sqlobject import *
+from sqlobject.tests.dbtest import *
+
+class TestComposer(SQLObject):
+    name = StringCol()
+
+class TestWork(SQLObject):
+    class sqlmeta:
+        idName = "work_id"
+
+    composer = ForeignKey('TestComposer')
+    title = StringCol()
+
+def test1():
+    setupClass(TestComposer)
+    setupClass(TestWork)
+
+    c = TestComposer(name='Mahler, Gustav')
+    w1 = TestWork(composer=c, title='Symphony No. 9')
+    w2 = TestWork(composer=None, title=None)
+
+    # Select by usual way
+    s = TestWork.selectBy(composerID=c.id, title='Symphony No. 9')
+    assert s[0]==w1
+    # selectBy object
+    s = TestWork.selectBy(composer=c, title='Symphony No. 9')
+    assert s[0]==w1
+    # selectBy id
+    s = TestWork.selectBy(id=w1.id)
+    assert s[0]==w1
+    # is None handled correctly?
+    s = TestWork.selectBy(composer=None, title=None)
+    assert s[0]==w2