Patch: Auto-generate search_path statement for selected schema in query editor - Mailing list pgadmin-hackers

Hi all,

Recently I wrote a small patch for frmQuery.cpp in order to
auto-generate a "SET search_path TO ..." statement when opening the
query editor with a schema-related object selected in the object
browser, e.g. a table, function or sequence (or their respective
collections).
The reason being that in our company, after migrating from MSSQL Server
to pg, we use a single database for many of our customers, with one
schema per customer and identically named objects within those schemas.
E.g. cust_schema_01.table_x, cust_schema_02.table_x, cust_schema_03.table_x.
For maintenance and debugging, we connect to the database using the
postgres superuser account in pgAdmin3. Now in order to access table_x
within a certain customer's schema in the query editor, we always have
to prepend the schema name to the table name or issue a "SET search_path
TO cust_schema_nn,public" statement.
This is rather tedious, so I came up with a patch for pgAdmin3 that
tries to intelligently generate a search_path depending on the currently
selected object in the object browser as well as depending on the
existing search_path configured for the current database connection.
That way, we can easily open query editors under different schemas
without bothering about the search_path ourselves.

This is what my code does when opening a new query editor window:
- Check whether the currently selected object in the object browser is
of type pgSchema, pgSchemaObject or pgSchemaObjCollection or one of
their descendants which (if I'm not mistaken) means it does have a
schema associated with it.
- If so, it checks whether the schema belonging to this object is
already contained in the user's search_path (case-sensitively and
considering the $user placeholder).
- If the schema isn't already in the user's search_path, the code
generates a "SET search_path TO
<selected_schema>,<existing_search_path>" statement and has it written
into the newly opened query editor window.
- After that, it places the cursor to the end of the sql text so the
user can begin typing right away.

Example: the user's search_path is set via ALTER ROLE to
"foobar,public". Now the user selects a single table in the object
browser underneath the schema "cust_schema_03" and opens a new query
editor window. pgAdmin will now pre-fill the editor's input field with
"SET search_path TO cust_schema_03,foobar,public;" and place the cursor
two lines beneath that statement.

Tested on Windows XP Pro SP3 and Windows7 Pro only - I didn't bother to
create a build environment on my Linux box yet.
In case you deem this patch useful, find the diff output based on the
1.16.1 release source code attached below. There may occur usability
problems in combination with the existing "sticky SQL" option though. We
don't use the "sticky SQL" feature in our environment, so for now I
didn't spend too much thought on it.
Beware also that though being a developer, I'm really inexperienced in
C/C++ and completely new to wxWidgets and to the inner workings of
pgAdmin, so there may well be room for improvement in my code. Having
said that, I'd be willing to dig further into the pgAdmin3 code in order
to make this a configurable option, integrate it better into the
existing code etc. if need be. For now I egoistically tried to keep a
small footprint for easier patchability in future pgAdmin releases (just
in case this feature for one reason or another won't make it into the
release branch anytime soon).

What are your thoughts on this?

Cheers from Germany and thanks a lot for all your nice work on pgAdmin
and PostgreSQL.
Florian




frm\frmQuery.cpp, based on 1.16.1 release:

613a614,616
>     // Jump to the end of the input field after placing code in it
>     sqlQuery->DocumentEnd();
>
3161a3165,3217
>
>     // Try to find the schema "obj" belongs to and then get its name.
>     // If obj is not of type pgSchema, pgSchemaObjCollection or pgSchemaObject (or one of their descendants),
>     // it obviously doesn't belong to any schema.
>     wxString schemaName;
>     pgSchema *schema = dynamic_cast<pgSchema*>(obj);
>     if(schema)
>     {
>         // We have a schema right there. Easy.
>         schemaName = schema->GetName();
>     }
>     else
>     {
>         // It's not a schema, so check if it's a descendant of pgSchemaObjCollection (e.g. a "Tables" or "Functions"
node)
>         pgSchemaObjCollection *schemaObjColl = dynamic_cast<pgSchemaObjCollection*>(obj);
>         if(schemaObjColl)
>             schemaName = schemaObjColl->GetSchema()->GetName();
>         else
>         {
>             // Not a pgSchemaObjCollection either, so check if it's a descendant of pgSchemaObject (e.g. a single
tableor function) 
>             pgSchemaObject *schemaObj = dynamic_cast<pgSchemaObject*>(obj);
>             if(schemaObj)
>                 schemaName = schemaObj->GetSchema()->GetName();
>         }
>     }
>     if(!schemaName.IsEmpty())
>     {
>         // We found a schema for the selected object, so check if it's already contained in search_path and build a
"SETsearch_path" statement otherwise. 
>         // First though, check whether the schema name contains upper-case characters and therefore needs to be
enclosedin quotation marks. 
>         if(schemaName != schemaName.Lower())
>             schemaName = schemaName.Prepend(wxT("\"")).Append(wxT("\""));
>         // Now compare it to the current search_path
>         wxString searchPath = obj->GetDatabase()->GetSearchPath();
>         wxStringTokenizer searchPathTokenizer(searchPath, wxT(","));
>         bool schemaContainedInSearchPath = false;
>         while(searchPathTokenizer.HasMoreTokens())
>         {
>             wxString currentToken = searchPathTokenizer.GetNextToken();
>             if(currentToken == schemaName || (currentToken == wxT("\"$user\"") && schemaName ==
obj->GetConnection()->GetUser()))
>             {
>                 schemaContainedInSearchPath = true;
>                 break;
>             }
>         }
>         if(!schemaContainedInSearchPath)
>         {
>             // Schema not contained in search_path, so build a statement for it.
>             // Since a user's search_path is never empty (?), we can safely put a comma between our current schema
nameand the user's current search_path 
>             // without checking for an empty string.
>             qry = wxT("SET search_path TO ") + schemaName + wxT(",") + searchPath + wxT(";\n\n");
>         }
>     }
>
3163c3219,3220
<         qry = obj->GetSql(form->GetBrowser());
---
>         qry += obj->GetSql(form->GetBrowser());
>