@@ -1133,12 +1133,126 @@ bool QgsPostgresProvider::uniqueData(QString schemaName,
1133
1133
return isUnique;
1134
1134
}
1135
1135
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
+
1136
1196
// This function will return in the cols variable the
1137
1197
// underlying view and columns for each column in
1138
1198
// mSchemaName.mTableName.
1139
1199
1140
1200
void QgsPostgresProvider::findColumns (tableCols& cols)
1141
1201
{
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
1142
1256
// This sql is derived from the one that defines the view
1143
1257
// 'information_schema.view_column_usage' in PostgreSQL, with a few
1144
1258
// mods to suit our purposes.
@@ -1339,6 +1453,7 @@ void QgsPostgresProvider::findColumns(tableCols& cols)
1339
1453
}
1340
1454
}
1341
1455
PQclear(result);
1456
+ #endif // 0
1342
1457
}
1343
1458
1344
1459
// Returns the minimum value of an attribute
0 commit comments