Re: _guess_columns patch

Jonathan --

This looks terrific, thanks!  I'll look at it more closely later
tonight (I hope).  In particular, I need to think about how such a
feature would and should play with inheritance, projections, etc.

j

On Tue, May 17, 2005 at 10:49:41AM -0600, Jonathan Ellis wrote:
> Here is a patch that adds a _guess_columns field to PyDO; when True, it 
> reads column/unique/seq info from the database.  Only implemented for 
> postgresql right now; figured the rest could wait until Jacob signs off on 
> the approach.
> 
> Patch is against 2.0a2 since SF.net anon CVS doesn't seem to be working.
> 
> -Jonathan
> 
> diff -ru PyDO-2.0a2-orig/src/PyDO2/ PyDO-2.0a2/src/PyDO2/ | grep -v "Only 
> in"
> diff -ru PyDO-2.0a2-orig/src/PyDO2/base.py PyDO-2.0a2/src/PyDO2/base.py
> --- PyDO-2.0a2-orig/src/PyDO2/base.py   2005-05-12 10:48:32.000000000 -0400
> +++ PyDO-2.0a2/src/PyDO2/base.py        2005-05-18 00:52:11.271722536 -0400
> @@ -46,7 +46,32 @@
>      """
> 
>      def __init__(cls, cl_name, bases, namespace):
> +        if cls._guess_columns:
> +            cls._fields, cls._unique = 
> cls.getDBI().describeTable(cls.getTable(
> ))
> +        else:
> +            # initializes cls._fields, cls._unique
> +            cls._init_fields(bases, namespace)
> +
> +        # We now have all the inherited declarations; figure out
> +        # sequences and additional unique constraints.
> +        cls._sequenced={}
> +        for f in cls._fields.itervalues():
> +            if f.sequence:
> +                cls._sequenced[f.name]=f.sequence
> +            if f.unique:
> +                cls._unique.add(f.name)
> +
> +        # this doesn't need to be
> +        cls._unique=frozenset(cls._unique)
> +
> +        # add attribute access to fields
> +        if cls.use_attributes:
> +            for name in cls._fields:
> +                if not hasattr(cls, name):
> +                    # a field is also a descriptor
> +                    setattr(cls, name, cls._fields[name])
> 
> +    def _init_fields(cls, bases, namespace):
>          # create Field objects declared locally in this class
>          # and store them in a temporary dict
>          fielddict={}
> @@ -74,7 +99,7 @@
>          revbases=[x for x in bases[::-1] if x not in (object, dict)]
> 
>          cls._fields={}
> -        uniqueset=set()
> +        cls._unique=set()
>          for b in revbases:
>              flds=getattr(b, '_fields', None)
>              if flds:
> @@ -89,7 +114,7 @@
>                  uniq=set(_setize(x) for x in uniq)
>                  if cls._is_projection:
>                      uniq=_restrict(fielddict, uniq)
> -                uniqueset.update(uniq)
> +                cls._unique.update(uniq)
> 
>          # If a field is declared upstream and you redeclare it in a
>          # subclass as a simple field (just a fieldname), then the
> @@ -98,26 +123,8 @@
>          updatefields=((x, y) for x, y in fielddict.iteritems() \
>                        if not (x in cls._fields and x in simplefields))
>          cls._fields.update(updatefields)
> -        uniqueset.update(_setize(x) for x in namespace.get('unique', ()))
> -
> -        # We now have all the inherited declarations, and figure out
> -        # sequences and additional unique constraints.
> -        cls._sequenced={}
> -        for f in cls._fields.itervalues():
> -            if f.sequence:
> -                cls._sequenced[f.name]=f.sequence
> -            if f.unique:
> -                uniqueset.add(f.name)
> +        cls._unique.update(_setize(x) for x in namespace.get('unique', ()))
> 
> -        # this doesn't need to be
> -        cls._unique=frozenset(uniqueset)
> -
> -        # add attribute access to fields
> -        if cls.use_attributes:
> -            for name in cls._fields:
> -                if not hasattr(cls, name):
> -                    # a field is also a descriptor
> -                    setattr(cls, name, cls._fields[name])
> 
>  class PyDO(dict):
>      """ Base class for PyDO data classes."""
> @@ -125,6 +132,7 @@
>      __metaclass__=_metapydo
>      _is_projection=False
>      _guess_tablename=True
> +    _guess_columns=False
>      mutable=True
>      use_attributes=True
>      connectionAlias=None
> @@ -328,6 +336,8 @@
>      @classmethod
>      def getDBI(cls):
>          """return the database interface"""
> +        if not cls.connectionAlias:
> +            raise KeyError, "class %s must declare a DBI alias" % 
> cls.__name__
>          conn=getConnection(cls.connectionAlias)
>          return conn
> 
> diff -ru PyDO-2.0a2-orig/src/PyDO2/dbi.py PyDO-2.0a2/src/PyDO2/dbi.py
> --- PyDO-2.0a2-orig/src/PyDO2/dbi.py    2005-05-05 12:05:04.000000000 -0400
> +++ PyDO-2.0a2/src/PyDO2/dbi.py 2005-05-18 01:49:00.521437800 -0400
> @@ -171,9 +171,11 @@
>          raise NotImplementedError
> 
>      def describeTable(self, table):
> -        """returns a table description for the given table.
> -        The description is a 3-tuple of fields, unique constraints,
> -        and sequences/auto_increment for the table in question"""
> +        """
> +        for the given table, returns a 2-tuple of
> +        (dict of Field objects keyed by name,
> +         list of multicolumn unique constraints (sets of Fields))
> +        """
>          raise NotImplementedError
> 
> 
> diff -ru PyDO-2.0a2-orig/src/PyDO2/drivers/psycopgconn.py 
> PyDO-2.0a2/src/PyDO2/d
> rivers/psycopgconn.py
> --- PyDO-2.0a2-orig/src/PyDO2/drivers/psycopgconn.py    2005-05-05 
> 12:02:14.0000
> 00000 -0400
> +++ PyDO-2.0a2/src/PyDO2/drivers/psycopgconn.py 2005-05-18 
> 01:28:43.565443232 -0
> 400
> @@ -12,6 +12,7 @@
>  from PyDO2.operators import BindingConverter
>  from PyDO2.dbtypes import DATE, TIMESTAMP, BINARY, INTERVAL, \
>       date_formats, timestamp_formats
> +from PyDO2.field import Field
> 
>  import time
>  import datetime
> @@ -19,10 +20,10 @@
>  import psycopg
> 
>  if psycopg.__version__[:3] >= '1.9':
> -   #psycopg version two.
> -   psycopg_version=2
> +    #psycopg version two.
> +    psycopg_version=2
>  else:
> -   psycopg_version=1
> +    psycopg_version=1
> 
>  try:
>     import mx.DateTime
> @@ -147,5 +148,65 @@
>              raise PyDOError, "could not get value for sequence %s!" % name
>          return res[0]
> 
> +    def describeTable(self, table):
> +        sql = """
> +        SELECT a.attname, a.attnum
> +        FROM pg_catalog.pg_attribute a
> +        WHERE a.attrelid = %s::regclass
> +          AND a.attnum > 0
> +          AND NOT a.attisdropped
> +        ORDER BY a.attnum
> +        """
> +        fields = {}
> +        cur = self.conn.cursor()
> +        if self.verbose:
> +            debug("SQL: %s", (sql,))
> +        cur.execute(sql, (table,))
> +        for row in cur.fetchall():
> +            if self.verbose:
> +                debug("Found column %s" % list(row))
> +            fields[row[1]] = Field(row[0])
> +
> +        sql = """
> +        SELECT indkey
> +        FROM pg_catalog.pg_index i
> +        WHERE i.indrelid = %s::regclass
> +          AND i.indisunique
> +        """
> +        unique = set()
> +        if self.verbose:
> +            debug("SQL: %s", (sql,))
> +        cur.execute(sql, (table,))
> +        for row in cur.fetchall():
> +            L = [int(i) for i in row[0].split(' ')]
> +            if self.verbose:
> +                debug("Found unique index on %s" % L)
> +            if len(L) == 1:
> +                fields[L[0]].unique = True
> +            else:
> +                unique.add(frozenset([fields[i] for i in L]))
> +
> +        sql = """
> +        SELECT relname
> +        FROM pg_class
> +        WHERE relname like '%s_%%_seq'
> +          AND relkind = 'S'
> +        """ % table
> +        if self.verbose:
> +            debug("SQL: %s", (sql,))
> +        cur.execute(sql)
> +        for row in cur.fetchall():
> +            maybecolname = row[0][len(table) + 1:-4]
> +            for field in fields.values():
> +                if field.name == maybecolname:
> +                    if self.verbose:
> +                        debug("Found sequence %s on %s" % (row[0], 
> field.name))
> +                    field.sequence = row[0]
> +                    break
> +
> +        cur.close()
> +        d = {}
> +        for f in fields.values():
> +            d[f.name] = f
> +        return (d, unique)
> 
> -
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=7412&alloc_id=16344&op=click
> _______________________________________________
> Skunkweb-list mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/skunkweb-list
> 

-- 
Jacob Smullyan

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFCii77uqamFyFXXLIRAhD/AKCfkIPI6LOML39PN/etDpRVZbKb6gCfcdyc
u3kQc0gX6A/fbKSSVV+20L4=
=Na3f
-----END PGP SIGNATURE-----