Set default schema

[Ryan J Ollos]

The issue described in comment:11:ticket:12643 can be easily reproduced by running the tests using a database URI that doesn't specify a schema (postgres://tracuser:tracpassword@localhost:5432/trac), and the following patch:

trac/test.py

@@ -284 +284 @@
         # Assume the schema 'tractest' for PostgreSQL
         if scheme == 'postgres' and \
                 not db_prop.get('params', {}).get('schema'):
-            dburi += ('&' if '?' in dburi else '?') + 'schema=tractest'
+            pass
+            # dburi += ('&' if '?' in dburi else '?') + 'schema=tractest'
         elif scheme == 'sqlite' and db_prop['path'] != ':memory:' and \
                 not db_prop.get('params', {}).get('synchronous'):
             # Speed-up tests with SQLite database

Do we want to set the public schema by default?

[Ryan J Ollos]

Possible changes: [fcc95dcae/rjollos.git] (log:rjollos.git:t12838_public_schema). Tests pass for me with Postgres 9.6.3. I still need to check that backup works correctly.

[Jun Omae]

We could use ANY (current_schemas(FALSE)) rather than self.schema.

trac/db/postgres_backend.py

@@ -389 +389 @@
     def get_table_names(self):
         rows = self.execute("""
             SELECT table_name FROM information_schema.tables
-            WHERE table_schema=%s""", (self.schema,))
+            WHERE table_schema = ANY (current_schemas(FALSE))""")
         return [row[0] for row in rows]
 
     def has_table(self, table):
         rows = self.execute("""
             SELECT EXISTS (SELECT * FROM information_schema.columns
-                           WHERE table_schema=%s AND table_name=%s)
-            """, (self.schema, table))
+                           WHERE table_schema = ANY (current_schemas(FALSE))
+                           AND table_name=%s)
+            """, (table,))
         return rows[0][0]
 
     def like(self):

psql (9.3.16, server 9.1.20)
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

trac=> SELECT current_schemas(FALSE);
 current_schemas
-----------------
 {public}
(1 row)

trac=> SET search_path TO trac_1_2;
SET
trac=> SELECT current_schemas(FALSE);
 current_schemas
-----------------
 {trac_1_2}
(1 row)

trac=> SET search_path TO public;
SET
trac=> SELECT current_schemas(FALSE);
 current_schemas
-----------------
 {public}
(1 row)

[Ryan J Ollos]

That looks like a good change to make. If we really want to keep the statement Trac uses the public schema by default (TracEnvironment#PostgreSQLConnectionString), it looks like additional changes will also be needed: tags/trac-1.2.1/trac/db/postgres_backend.py@:121#L115​.

$ trac-admin testenv1 initenv "psql without schema" postgres://tracuser:tracpassword@localhost:5432/trac
Creating and Initializing Project
Initenv for '/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/testenv1' failed.
Failed to create environment.
no schema has been selected to create in
LINE 1: CREATE TABLE "system" (
                     ^

Traceback (most recent call last):
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/admin/console.py", line 500, in do_initenv
    options=options)
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/core.py", line 136, in __call__
    self.__init__(*args, **kwargs)
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/env.py", line 300, in __init__
    self.create(options)
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/env.py", line 592, in create
    DatabaseManager(self).init_db()
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/db/api.py", line 340, in init_db
    connector.init_db(**args)
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/db/postgres_backend.py", line 127, in init_db
    cursor.execute(stmt)
  File "/Users/rjollos/Documents/Workspace/trac-dev/teo-rjollos.git/trac/db/util.py", line 73, in execute
    return self.cursor.execute(sql)
ProgrammingError: no schema has been selected to create in
LINE 1: CREATE TABLE "system" (
                     ^

How about log:rjollos.git:t12838_public_schema.1 (1.2-stable)? (Minus the change to test.py). A subset of the changes would be needed for 1.0-stable, and a superset for trunk.

[Jun Omae]

Additional changes in [5ffb6716a/jomae.git]. initenv works well with public schema. I've suggested to use current_schemas() but public schema is unexpectedly used when the specified schema is not created yet. I reverted uses of current_schemas() in the changes.

[Ryan J Ollos]

Moving this to 1.2-stable unless anyone thinks it's necessary to fix on 1.0-stable. The simple workaround is to just append ?schema=public to the database URI.

[Ryan J Ollos]

Committed to 1.2-stable in r16130. Merged to trunk in r16131.