[PEAK] bulletins example OF DOOM, revisited

[Phillip J. Eby]

At 06:08 PM 4/14/04 -0400, Stephen C. Waterbury wrote:

>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.:

It's case-insensitive but not case-preserving.  Oracle does the same thing, 
only it "smushes" them to upper case.  I don't really have any good 
solution available for peak.storage at the low level; I expect it to be 
resolved by peak.query which will handle SQL generation and result 
translations.  At the moment, peak.storage is only slightly higher-level 
than the DBAPI.


>
>      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)
>          )

Note that this isn't a correct translation.  Under SQLite, INSERT OR 
REPLACE will update the table if an item with the same primary key already 
exists.  I think there may be a Postgres incantation you can use to do the 
same thing, but I'm not sure.


>      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,

Ditto.


>      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,

And again.


>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
>+            )

Not bad.  I'll probably add something similar to PEAK.


>
>      syntax = naming.URL.Sequence(
>-        ('//',), (user, (':', passwd), '@'), server, ('/', db)
>+        ('//',), (user, (':', passwd), '@'), (server, '/'), db,
>      )

I'm can't use this part, though.  For example, it's wrong for Sybase, which 
*must* have a server.  I'll have to look at creating a separate PostgresURL 
type.  I guess it's end times for GenericSQL_URL.