[PEAK] bulletins example OF DOOM, revisited

Stephen C. Waterbury golux at comcast.net
Wed Apr 14 18:08:23 EDT 2004


Well, there's good news and bad news.  The good news is that
I finally got the psycopg driver to work with a domain socket
(yay!).  I'll let you decide whether you want to use my
solution ... it's not all that pretty, but it works.  ;)

$ peak runIni bulletins adduser zaphod 42 Zaphod Beeblebrox
$ peak runIni bulletins showusers
User          Name
------------  -----------------------------------
zaphod        Zaphod Beeblebrox

The patches are for:
* bulletins (ini file)
* bulletins.storage
* peak.storage.SQL

The bad news is that I had to do some froobius modifications
to bulletins.storage (see attached patch) because of
PostgreSQL forcing all names to be lower-case in its catalog,
so the camel-case identifiers in sqlite-ddl.sql get smushed when
PostgreSQL creates the tables, causing dissonance with the way
PEAK handles identifiers like 'loginId', etc.

I'm not sure whether this behavior of PostgreSQL violates
the identifier case-insensitivity requirement of SQL92 or
not:  if either the command interface of PostgreSQL or
the psycopg or PgSQL driver is used directly, the API *is*
case-insensitive -- e.g.:

------------------------------------------------------------
$ psql bulletins
Welcome to psql 7.4, the PostgreSQL interactive terminal.

bulletins=# \d users
            Table "public.users"
   Column  |       Type        | Modifiers
----------+-------------------+-----------
  loginid  | character varying | not null
  fullname | character varying |
  password | character varying |
Indexes:
     "users_pkey" primary key, btree (loginid)

bulletins=# \q

$ python
Python 2.3.3 (#1, Dec 24 2003, 12:01:08)
[GCC 3.2.2 20030222 (Red Hat Linux 3.2.2-5)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
 >>> from pyPgSQL import PgSQL
 >>> conn = PgSQL.connect(user='waterbug', database='bulletins')
 >>> curs.execute('select * from users where loginid = %s', ('zaphod',))
                                             ^^^^^^^
 >>> curs.fetchall()
[['zaphod', 'Zaphod Beeblebrox', '42']]
 >>> curs.execute('select * from users where loginId = %s', ('zaphod',))
                                             ^^^^^^^
 >>> curs.fetchall()
[['zaphod', 'Zaphod Beeblebrox', '42']]
 >>> import psycopg
 >>> conn = psycopg.connect(user='waterbug', database='bulletins')
 >>> curs = conn.cursor()
 >>> curs.execute('select * from users where loginId = %s', ('zaphod',))
                                             ^^^^^^^
 >>> curs.fetchall()
[('zaphod', 'Zaphod Beeblebrox', '42')]

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

... but bulletins.storage does have a problem before my patch:

------------------------------------------------------------
$ peak runIni bulletins showusers
User          Name
------------  -----------------------------------
Traceback (most recent call last):
   File "/usr/local/bin/peak", line 4, in ?
     commands.runMain( commands.Bootstrap )
   File "/usr/local/lib/python2.3/site-packages/peak/running/commands.py", line 70, in runMain
     result = factory().run()
   File "/home/waterbug/sandbox/PEAKsb/bulletins/src/bulletins/commands.py", line 48, in run
     for user in self.Users.getAll():
   File "/home/waterbug/sandbox/PEAKsb/bulletins/src/bulletins/storage.py", line 72, in getAll
     return [self.preloadState(row.loginId, self.stateFromRow(row))
AttributeError: 'rowStruct' object has no attribute 'loginId'
-------------------------------------------------------------

(Of course, there might be a better way to fix it than my patch. ;)

Another way to handle that problem could be to use a
mapping module (probably something like
'loginId' <-> 'login_id' [rather than 'loginid'],
since it would be easier to invert).  I have a module
that does exactly that for table names in the O-R mapping
I use for my Twisted application ... for attribute names
I just keep them lower-case (our naming convention is not
to use any upper-case letters in attribute names, anyway).

For now, I've given up on trying to make a PEAK driver
for pyPgSQL ... I think pyPgSQL's approach is so different
from what PEAK is expecting that it would require significant
work and more knowledge of PEAK innards than I have or can
reasonably expect to acquire in the near future.  Besides,
psycopg works just fine.  :)

Cheers,
Steve
-------------- next part --------------
Index: bulletins
===================================================================
RCS file: /cvsroot/PEAK/examples/bulletins/bulletins,v
retrieving revision 1.2
diff -u -r1.2 bulletins
--- bulletins	2003/06/30 19:20:55	1.2
+++ bulletins	2004/04/14 20:37:42
@@ -1,4 +1,4 @@
-#!invoke peak runIni
+#!/usr/local/bin/peak runIni
 
 [Load Settings From]
 # This loads the general program settings - don't change:
@@ -7,10 +7,11 @@
 # From this point on, you can modify as appropriate:
 
 [bulletins]
-databaseURL = 'sqlite:///tmp/bulletins.db'
+# databaseURL = 'sqlite:///tmp/bulletins.db'
+databaseURL = 'psycopg:waterbug at bulletins'
 databaseDDL = config.fileNearModule('bulletins', 'sqlite-ddl.sql')
 
 [peak.logs]
 # Set default logging to stderr, DEBUG level
-* = logs.LogStream(stream=importString('sys.stderr'), level=logs.DEBUG)
+* = logs.LogStream(stream=importString('sys.stderr'), levelName='DEBUG')
 
-------------- next part --------------
Index: storage.py
===================================================================
RCS file: /cvsroot/PEAK/examples/bulletins/src/bulletins/storage.py,v
retrieving revision 1.4
diff -u -r1.4 storage.py
--- storage.py	2003/09/30 23:04:31	1.4
+++ storage.py	2004/04/14 20:41:23
@@ -45,21 +45,21 @@
     defaultClass = User
 
     def _load(self, oid, ob):
-        row = ~self.db("select * from users where loginId = %s", (oid,))
+        row = ~self.db("select * from users where loginid = %s", (oid,))
         return self.stateFromRow(row)
 
     def stateFromRow(self,row):
         return dict(
             Items(
-                loginId = row.loginId,
-                fullName = row.fullName,
+                loginId = row.loginid,
+                fullName = row.fullname,
                 password = row.password,
             )
         )
 
     def _save(self, ob):
-        self.db("""INSERT OR REPLACE INTO users
-                (loginId, fullName, password) VALUES (%s, %s, %s)""",
+        self.db("""INSERT INTO users
+                (loginid, fullname, password) VALUES (%s, %s, %s)""",
             (ob.loginId, ob.fullName, ob.password)
         )
 
@@ -69,7 +69,7 @@
         return oid
 
     def getAll(self):
-        return [self.preloadState(row.loginId, self.stateFromRow(row))
+        return [self.preloadState(row.loginid, self.stateFromRow(row))
             for row in self.db("select * from users")
         ]
 
@@ -106,7 +106,7 @@
         )
 
     def _save(self, ob):
-        self.db("""INSERT OR REPLACE INTO bulletins
+        self.db("""INSERT INTO bulletins
                 (id, category, fullText, postedBy, postedOn, editedBy,
                  editedOn, hidden) VALUES (%d, %s, %s, %s, %s, %s, %s, %d)""",
             (ob._p_oid, self.CategoryDM.oidFor(ob.category), ob.fullText,
@@ -123,7 +123,7 @@
 
 class CategoryDM(storage.EntityDM):
 
-    db = binding.bindTo(DATABASE)
+    db = binding.Obtain(DATABASE)
     BulletinDM = binding.Obtain(storage.DMFor(Bulletin))
     bulletinsForCategory = binding.Obtain('BulletinDM/forCategory')
 
@@ -149,7 +149,7 @@
         )
 
     def _save(self, ob):
-        self.db("""INSERT OR REPLACE INTO categories
+        self.db("""INSERT INTO categories
                 (pathName, title, sortPosn, sortBulletinsBy, postingTemplate,
                 editingTemplate) VALUES (%s, %s, %d, %s, %s, %s)""",
             (ob.pathName, ob.title, ob.sortPosn,
-------------- next part --------------
Index: SQL.py
===================================================================
RCS file: /cvsroot/PEAK/src/peak/storage/SQL.py,v
retrieving revision 1.71
diff -u -r1.71 SQL.py
--- SQL.py	2004/02/05 00:34:00	1.71
+++ SQL.py	2004/04/14 19:18:47
@@ -460,10 +460,16 @@
     def _open(self):
 
         a = self.address
-
-        return self.API.connect(
-            host=a.server, database=a.db, user=a.user, password=a.passwd
-        )
+        if not a.server:
+            # no server => domain socket connection
+            # so no passwd needed either
+            return self.API.connect(
+                database=a.db, user=a.user
+            )
+        else:
+            return self.API.connect(
+                host=a.server, database=a.db, user=a.user, password=a.passwd
+            )
 
 
     def txnTime(self):
@@ -639,14 +645,14 @@
     class passwd(naming.URL.Field):
         pass
 
-    class server(naming.URL.RequiredField):
+    class server(naming.URL.Field):
         pass
 
     class db(naming.URL.Field):
         pass
 
     syntax = naming.URL.Sequence(
-        ('//',), (user, (':', passwd), '@'), server, ('/', db)
+        ('//',), (user, (':', passwd), '@'), (server, '/'), db,
     )
 
 


More information about the PEAK mailing list