tdf#122437: Combine commits for libreoffice-6-2

tdf#122437 mysqlc: fix foreign key name query

Correct query of the foreign key constraint name is required for
dropping foreign keys.

Reviewed-on: https://gerrit.libreoffice.org/65861
Tested-by: Jenkins
Reviewed-by: Tamás Bunth <btomi96@gmail.com>

tdf#122437 mysqlc: consider "types" parameter in..

..method XDatabaseMetadata::getTables() so it will return only those types
which are needed. This solves the problem that no tables appear in
"Relations.." window.

Reviewed-on: https://gerrit.libreoffice.org/66212
Tested-by: Jenkins
Reviewed-by: Tamás Bunth <btomi96@gmail.com>

tdf#122437 mysqlc: Fix foreign key references

Comparing mysqlc terminology (INFORMATION_SCHEMA) with the description
of the XDatabaseMetadata interface:
- columns with name "REFERENCED_*" (INF_SCHEMA) should be used for primary key
attributes (sdbc terminology).
- columns without any prefix (INF_SCHEMA) should be used for primary key
attributes (sdbc)

Change-Id: Id98b0672ec5a8a06039667a06cb0afd97b3ee205
a02537c78917583f574ad788098c65a9acd43078
d9780fd86834441485da927f85b9446c40951fcf
Reviewed-on: https://gerrit.libreoffice.org/66213
Tested-by: Jenkins
Reviewed-by: Tamás Bunth <btomi96@gmail.com>
Reviewed-on: https://gerrit.libreoffice.org/67416
Reviewed-by: Julien Nabet <serval2412@yahoo.fr>

1 files changed, 52 insertions, 24 deletions

diff --git a/connectivity/source/drivers/mysqlc/mysqlc_databasemetadata.cxx b/connectivity/source/drivers/mysqlc/mysqlc_databasemetadata.cxx
index 9acdbc952696..890f31de666a 100644
--- a/connectivity/source/drivers/mysqlc/mysqlc_databasemetadata.cxx
+++ b/connectivity/source/drivers/mysqlc/mysqlc_databasemetadata.cxx
@@ -829,14 +829,37 @@ Reference<XResultSet> SAL_CALL ODatabaseMetaData::getColumns(const Any& /*catalo
 Reference<XResultSet> SAL_CALL ODatabaseMetaData::getTables(const Any& /*catalog*/,
                                                             const OUString& schemaPattern,
                                                             const OUString& tableNamePattern,
-                                                            const Sequence<OUString>& /*types */)
+                                                            const Sequence<OUString>& types)
 {
-    OUString query(
+    OUStringBuffer buffer{
         "SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME,"
         "IF(STRCMP(TABLE_TYPE,'BASE TABLE'), TABLE_TYPE, 'TABLE') AS TABLE_TYPE, TABLE_COMMENT AS "
         "REMARKS "
         "FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA  LIKE '?' AND TABLE_NAME LIKE '?' "
-        "ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME");
+    };
+
+    if (types.getLength() == 1)
+    {
+        buffer.append("AND TABLE_TYPE LIKE '");
+        buffer.append(types[0]);
+        buffer.append("'");
+    }
+    else if (types.getLength() > 1)
+    {
+        buffer.append("AND (TABLE_TYPE LIKE '");
+        buffer.append(types[0]);
+        buffer.append("'");
+        for (sal_Int32 i = 1; i < types.getLength(); ++i)
+        {
+            buffer.append(" OR TABLE_TYPE LIKE '");
+            buffer.append(types[i]);
+            buffer.append("'");
+        }
+        buffer.append(")");
+    }
+
+    buffer.append(" ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME");
+    OUString query = buffer.makeStringAndClear();
 
     // TODO use prepared stmt instead
     // TODO escape schema, table name ?
@@ -905,18 +928,23 @@ Reference<XResultSet> SAL_CALL ODatabaseMetaData::getImportedKeys(const Any& /*c
                                          "org.openoffice.comp.helper.DatabaseMetaDataResultSet"),
                                      UNO_QUERY);
 
-    OUString query(
-        "SELECT refi.CONSTRAINT_CATALOG, k.COLUMN_NAME, "
-        " refi.UNIQUE_CONSTRAINT_CATALOG, "
-        " refi.UNIQUE_CONSTRAINT_SCHEMA, refi.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME, "
-        " refi.UPDATE_RULE, refi.DELETE_RULE, refi.CONSTRAINT_NAME "
-        " FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as refi"
-        " INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as k ON k.CONSTRAINT_NAME = "
-        "refi.CONSTRAINT_NAME "
-        " and k.TABLE_NAME = refi.TABLE_NAME "
-        " WHERE refi.CONSTRAINT_SCHEMA LIKE "
-        "'?' AND refi.TABLE_NAME='?'"); // TODO
-    query = query.replaceFirst("?", schema);
+    OUString query("SELECT refi.CONSTRAINT_CATALOG," // 1: foreign catalog
+                   " k.COLUMN_NAME," // 2: foreign column name
+                   " refi.UNIQUE_CONSTRAINT_CATALOG," // 3: primary catalog FIXME
+                   " k.REFERENCED_TABLE_SCHEMA," // 4: primary schema
+                   " refi.REFERENCED_TABLE_NAME," // 5: primary table name
+                   " k.REFERENCED_COLUMN_NAME," // 6: primary column name
+                   " refi.UPDATE_RULE, refi.DELETE_RULE," // 7,8: update, delete rule
+                   " refi.CONSTRAINT_NAME, " // 9: name of constraint itself
+                   " refi.TABLE_NAME, " // 10: foreign table name
+                   " refi.CONSTRAINT_SCHEMA " // 11: foreign schema name FIXME
+                   " FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as refi"
+                   " INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as k ON k.CONSTRAINT_NAME = "
+                   "refi.CONSTRAINT_NAME "
+                   " and k.TABLE_NAME = refi.TABLE_NAME "
+                   " WHERE k.REFERENCED_TABLE_SCHEMA LIKE "
+                   "'?' AND refi.TABLE_NAME='?'");
+    query = query.replaceFirst("?", schema); // TODO what if schema is NULL?
     query = query.replaceFirst("?", table);
 
     std::vector<std::vector<Any>> aRows;
@@ -929,22 +957,22 @@ Reference<XResultSet> SAL_CALL ODatabaseMetaData::getImportedKeys(const Any& /*c
         std::vector<Any> aRow{ Any() }; // 0. element is unused
 
         // primary key catalog
-        aRow.push_back(makeAny(xRow->getString(1)));
+        aRow.push_back(makeAny(xRow->getString(3)));
         // primary key schema
-        aRow.push_back(makeAny(schema));
+        aRow.push_back(makeAny(xRow->getString(4)));
         // primary key table
-        aRow.push_back(makeAny(table));
+        aRow.push_back(makeAny(xRow->getString(5)));
         // primary column name
-        aRow.push_back(makeAny(xRow->getString(2)));
+        aRow.push_back(makeAny(xRow->getString(6)));
 
         // fk table catalog
-        aRow.push_back(makeAny(xRow->getString(3)));
+        aRow.push_back(makeAny(xRow->getString(1)));
         // fk schema
-        aRow.push_back(makeAny(xRow->getString(4)));
+        aRow.push_back(makeAny(xRow->getString(11)));
         // fk table
-        aRow.push_back(makeAny(xRow->getString(5)));
+        aRow.push_back(makeAny(xRow->getString(10)));
         // fk column name
-        aRow.push_back(makeAny(xRow->getString(6)));
+        aRow.push_back(makeAny(xRow->getString(2)));
         // KEY_SEQ
         aRow.push_back(makeAny(sal_Int32{ 0 })); // TODO
         // update rule
@@ -952,7 +980,7 @@ Reference<XResultSet> SAL_CALL ODatabaseMetaData::getImportedKeys(const Any& /*c
         // delete rule
         aRow.push_back(makeAny(xRow->getShort(8)));
         // foreign key name
-        aRow.push_back(makeAny(xRow->getShort(9)));
+        aRow.push_back(makeAny(xRow->getString(9)));
         // primary key name
         aRow.push_back(makeAny(OUString{})); // TODO
         // deferrability