Skip to content

Commit

Permalink
Potential fix for ticket #477 (slowness opening postgres databases that
Browse files Browse the repository at this point in the history 
contain lots of views). Needs some testing, which I'll do in a day or
so.


git-svn-id: http://svn.osgeo.org/qgis/trunk/qgis@6610 c8812cc2-4d05-0410-92ff-de0c093fc19c
  • Loading branch information
g_j_m committed Feb 17, 2007
1 parent 03c9a3c commit b965865
Showing 1 changed file with 14 additions and 28 deletions.
42 changes: 14 additions & 28 deletions src/providers/postgres/qgspostgresprovider.cpp
View file
Expand Up @@ -1159,8 +1159,9 @@ void QgsPostgresProvider::findColumns(tableCols& cols)
" nt.nspname AS table_schema, "
" t.relname AS table_name, "
" a.attname AS column_name, "
" t.relkind as table_type, "
" typ.typname as column_type "
" t.relkind AS table_type, "
" typ.typname AS column_type, "
" vs.definition AS view_definition "
"FROM "
" pg_namespace nv, "
" pg_class v, "
Expand All @@ -1170,7 +1171,8 @@ void QgsPostgresProvider::findColumns(tableCols& cols)
" pg_namespace nt, "
" pg_attribute a,"
" pg_user u, "
" pg_type typ "
" pg_type typ, "
" pg_views vs "
"WHERE "
" nv.oid = v.relnamespace AND "
" v.relkind = 'v'::\"char\" AND "
Expand All @@ -1188,7 +1190,9 @@ void QgsPostgresProvider::findColumns(tableCols& cols)
" t.oid = a.attrelid AND "
" dt.refobjsubid = a.attnum AND "
" nv.nspname NOT IN ('pg_catalog', 'information_schema' ) AND "
" a.atttypid = typ.oid";
" a.atttypid = typ.oid AND "
" nv.nspname = vs.schemaname AND "
" v.relname = vs.viewname";

// A structure to store the results of the above sql.
typedef std::map<QString, TT> columnRelationsType;
Expand All @@ -1213,41 +1217,24 @@ void QgsPostgresProvider::findColumns(tableCols& cols)
temp.column_name = PQgetvalue(result, i, 5);
temp.table_type = PQgetvalue(result, i, 6);
temp.column_type = PQgetvalue(result, i, 7);
QString viewDef = PQgetvalue(result, i, 8);

// BUT, the above SQL doesn't always give the correct value for the view
// column name (that's because that information isn't available directly
// from the database), mainly when the view column name has been renamed
// using 'AS'. To fix this we need to look in the view definition and
// adjust the view column name if necessary.


QString viewQuery = "SELECT definition FROM pg_views "
"WHERE schemaname = '" + temp.view_schema + "' AND "
"viewname = '" + temp.view_name + "'";

// Maintain a cache of the above SQL.
QString viewDef;
if (!viewDefs.contains(viewQuery))
{
PGresult* r = PQexec(connection, (const char*)(viewQuery.utf8()));
if (PQntuples(r) > 0)
viewDef = PQgetvalue(r, 0, 0);
else
QgsDebugMsg("Failed to get view definition for " + temp.view_schema + "." + temp.view_name);
viewDefs[viewQuery] = viewDef;
}

viewDef = viewDefs.value(viewQuery);

// Now pick the view definiton apart, looking for
// temp.column_name to the left of an 'AS'.

// This regular expression needs more testing. Since the view
// definition comes from postgresql and has been 'standardised', we
// don't need to deal with everything that the user could put in a view
// definition. Does the regexp have to deal with the schema??
if (!viewDef.isEmpty())
{
// This regular expression needs more testing. Since the view
// definition comes from postgresql and has been 'standardised', we
// don't need to deal with everything that the user could put in a view
// definition. Does the regexp have to deal with the schema??

QRegExp s(".* \"?" + QRegExp::escape(temp.table_name) +
"\"?\\.\"?" + QRegExp::escape(temp.column_name) +
"\"? AS \"?(\\w+)\"?,* .*");
Expand All @@ -1257,7 +1244,6 @@ void QgsPostgresProvider::findColumns(tableCols& cols)
if (s.indexIn(viewDef) != -1)
{
temp.view_column_name = s.cap(1);
//std::cerr<<__FILE__<<__LINE__<<' '<<temp.view_column_name.toLocal8Bit().data()<<'\n';
}
}

Expand Down

0 comments on commit b965865

Please sign in to comment.