Skip to content

Commit 5324e89

Browse files
author
mhugent
committedJul 15, 2007
Fix that adresses ticket #477 'Loading a PostGIS view is very slow'
git-svn-id: http://svn.osgeo.org/qgis/trunk@7071 c8812cc2-4d05-0410-92ff-de0c093fc19c
1 parent 31e49a2 commit 5324e89

File tree

2 files changed

+129
-0
lines changed

2 files changed

+129
-0
lines changed
 

‎src/providers/postgres/qgspostgresprovider.cpp

Lines changed: 115 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1133,12 +1133,126 @@ bool QgsPostgresProvider::uniqueData(QString schemaName,
11331133
return isUnique;
11341134
}
11351135

1136+
int QgsPostgresProvider::SRCFromViewColumn(const QString& ns, const QString& relname, const QString& attname_table, const QString& attname_view, const QString& viewDefinition, SRC& result) const
1137+
{
1138+
QString newViewDefSql = "SELECT definition FROM pg_views WHERE schemaname = '" + ns + "' AND viewname = '" + relname + "'";
1139+
PGresult* newViewDefResult = PQexec(connection, (const char*)(newViewDefSql.utf8()));
1140+
int numEntries = PQntuples(newViewDefResult);
1141+
1142+
if(numEntries > 0) //relation is a view
1143+
{
1144+
QString newViewDefinition(PQgetvalue(newViewDefResult, 0, 0));
1145+
1146+
QString newAttNameView = attname_table;
1147+
QString newAttNameTable = attname_table;
1148+
1149+
//find out the attribute name of the underlying table/view
1150+
if (newViewDefinition.contains("AS"))
1151+
{
1152+
QRegExp s("(\\w+) " + QString("AS ") + QRegExp::escape(attname_table));
1153+
if (s.indexIn(newViewDefinition) != -1)
1154+
{
1155+
newAttNameTable = s.cap(1);
1156+
}
1157+
}
1158+
1159+
QString viewColumnSql = "SELECT table_schema, table_name, column_name FROM information_schema.view_column_usage WHERE view_schema = '" + ns + "' AND view_name = '" + relname + "' AND column_name = '" + newAttNameTable +"'";
1160+
PGresult* viewColumnResult = PQexec(connection, (const char*)(viewColumnSql.utf8()));
1161+
if(PQntuples(viewColumnResult) > 0)
1162+
{
1163+
QString newTableSchema = PQgetvalue(viewColumnResult, 0, 0);
1164+
QString newTableName = PQgetvalue(viewColumnResult, 0, 1);
1165+
int retvalue = SRCFromViewColumn(newTableSchema, newTableName, newAttNameTable, newAttNameView, newViewDefinition, result);
1166+
PQclear(viewColumnResult);
1167+
return retvalue;
1168+
}
1169+
else
1170+
{
1171+
PQclear(viewColumnResult);
1172+
return 1;
1173+
}
1174+
1175+
}
1176+
1177+
PQclear(newViewDefResult);
1178+
1179+
//relation is table, we just have to add the type
1180+
QString typeSql = "SELECT pg_type.typname FROM pg_attribute, pg_class, pg_namespace, pg_type WHERE pg_class.relname = '" + relname + "' AND pg_namespace.nspname = '" + ns + "' AND pg_attribute.attname = '" + attname_table + "' AND pg_attribute.attrelid = pg_class.oid AND pg_class.relnamespace = pg_namespace.oid AND pg_attribute.atttypid = pg_type.oid";
1181+
PGresult* typeSqlResult = PQexec(connection, (const char*)(typeSql.utf8()));
1182+
if(PQntuples(typeSqlResult) < 1)
1183+
{
1184+
return 1;
1185+
}
1186+
QString type = PQgetvalue(typeSqlResult, 0, 0);
1187+
PQclear(typeSqlResult);
1188+
1189+
result.schema=ns;
1190+
result.relation=relname;
1191+
result.column=attname_table;
1192+
result.type=type;
1193+
return 0;
1194+
}
1195+
11361196
// This function will return in the cols variable the
11371197
// underlying view and columns for each column in
11381198
// mSchemaName.mTableName.
11391199

11401200
void QgsPostgresProvider::findColumns(tableCols& cols)
11411201
{
1202+
QString viewColumnSql = "SELECT table_schema, table_name, column_name FROM information_schema.view_column_usage WHERE view_schema = '" + mSchemaName + "' AND view_name = '" + mTableName + "'";
1203+
PGresult* viewColumnResult = PQexec(connection, (const char*)(viewColumnSql.utf8()));
1204+
1205+
//find out view definition
1206+
QString viewDefSql = "SELECT definition FROM pg_views WHERE schemaname = '" + mSchemaName + "' AND viewname = '" + mTableName + "'";
1207+
PGresult* viewDefResult = PQexec(connection, (const char*)(viewDefSql.utf8()));
1208+
if(PQntuples(viewDefResult) < 1)
1209+
{
1210+
PQclear(viewDefResult);
1211+
return;
1212+
}
1213+
QString viewDefinition(PQgetvalue(viewDefResult, 0, 0));
1214+
PQclear(viewDefResult);
1215+
1216+
QString ns, relname, attname_table, attname_view;
1217+
SRC columnInformation;
1218+
1219+
for(int i = 0; i < PQntuples(viewColumnResult); ++i)
1220+
{
1221+
ns = PQgetvalue(viewColumnResult, i, 0);
1222+
relname = PQgetvalue(viewColumnResult, i, 1);
1223+
attname_table = PQgetvalue(viewColumnResult, i, 2);
1224+
1225+
//find out original attribute name
1226+
attname_view = attname_table;
1227+
1228+
//examine if the column name has been renamed in the view with AS
1229+
if (viewDefinition.contains("AS"))
1230+
{
1231+
// This regular expression needs more testing. Since the view
1232+
// definition comes from postgresql and has been 'standardised', we
1233+
// don't need to deal with everything that the user could put in a view
1234+
// definition. Does the regexp have to deal with the schema??
1235+
1236+
QRegExp s(".* \"?" + QRegExp::escape(relname) +
1237+
"\"?\\.\"?" + QRegExp::escape(attname_table) +
1238+
"\"? AS \"?(\\w+)\"?,* .*");
1239+
1240+
QgsDebugMsg(viewDefinition + "\n" + s.pattern());
1241+
1242+
if (s.indexIn(viewDefinition) != -1)
1243+
{
1244+
attname_view = s.cap(1);
1245+
qWarning("original view column name was: " + attname_view);
1246+
}
1247+
}
1248+
1249+
SRCFromViewColumn(ns, relname, attname_table, attname_view, viewDefinition, columnInformation);
1250+
cols.insert(std::make_pair(attname_view, columnInformation));
1251+
QgsDebugMsg("Inserting into cols (for key " + attname_view + " ): " + columnInformation.schema + "." + columnInformation.relation + "." + columnInformation.column + "." + columnInformation.type);
1252+
}
1253+
PQclear(viewColumnResult);
1254+
1255+
#if 0
11421256
// This sql is derived from the one that defines the view
11431257
// 'information_schema.view_column_usage' in PostgreSQL, with a few
11441258
// mods to suit our purposes.
@@ -1339,6 +1453,7 @@ void QgsPostgresProvider::findColumns(tableCols& cols)
13391453
}
13401454
}
13411455
PQclear(result);
1456+
#endif //0
13421457
}
13431458

13441459
// Returns the minimum value of an attribute

‎src/providers/postgres/qgspostgresprovider.h

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -483,6 +483,20 @@ class QgsPostgresProvider:public QgsVectorDataProvider
483483
// Function that populates the given cols structure.
484484
void findColumns(tableCols& cols);
485485

486+
/**Helper function that collects information about the origin and type of a view column.
487+
Inputs are information about the column in the underlying table
488+
(from information_schema.view_column_usage), the attribute name
489+
in the view and the view definition. For view columns that refer
490+
to other views, this function calls itself until a table entry is found.
491+
@param ns namespace of underlying table
492+
@param relname name of underlying relation
493+
@param attname attribute name in underlying table
494+
@param viewDefinition definition of this view
495+
@param result
496+
@return 0 in case of success*/
497+
int SRCFromViewColumn(const QString& ns, const QString& relname, const QString& attname_table,
498+
const QString& attname_view, const QString& viewDefinition, SRC& result) const;
499+
486500
bool ready;
487501
std::ofstream pLog;
488502

0 commit comments

Comments
 (0)
Please sign in to comment.