Connector/ODBC 5 commit: r408 - trunk/MYSQLPlus/MYSQLPlusLib

[pharvey]

Modified:
   trunk/MYSQLPlus/MYSQLPlusLib/MResultPlus.cpp
   trunk/MYSQLPlus/MYSQLPlusLib/MResultPlus.h
Log:
more work on SQLSpecialColumns

Modified: trunk/MYSQLPlus/MYSQLPlusLib/MResultPlus.cpp
===================================================================
--- trunk/MYSQLPlus/MYSQLPlusLib/MResultPlus.cpp	2006-06-27 23:40:37 UTC (rev 407)
+++ trunk/MYSQLPlus/MYSQLPlusLib/MResultPlus.cpp	2006-06-28 06:42:16 UTC (rev 408)
@@ -946,6 +946,86 @@
     MYODBCDbgReturn3( "%p", stringlistTables );
 }
 
+QStringList MResultPlus::getColumnsPrimaryKey( const QString &stringCatalog, const
QString &stringSchema, const QString &stringTable )
+{
+    QString     stringStatement;
+    QStringList stringlistColumns;
+    MYSQL_RES * pResult = NULL;
+    MYSQL_ROW   pRow;
+
+    MYODBCDbgEnter();
+
+    stringStatement = "SELECT COLUMN_NAME "
+                      "FROM   INFORMATION_SCHEMA.STATISTICS "
+                      "WHERE  TABLE_SCHEMA='" + stringCatalog + "' AND TABLE_NAME='" +
stringTable + "' AND INDEX_NAME='PRIMARY' "
+                      "ORDER BY SEQ_IN_INDEX ";
+
+    if ( mysql_query( getMySQL(), stringStatement.toUtf8().data() ) )
+    {
+        getDiagnostic()->doAppend( MDiagnostic::DIA_HY000, mysql_errno( getMySQL() ),
mysql_error( getMySQL() ) );
+        MYODBCDbgReturn3( "%p", stringlistTables );
+    }
+
+    pResult = mysql_use_result( getMySQL() );
+    if ( !pResult )
+    {
+        getDiagnostic()->doAppend( MDiagnostic::DIA_HY000, mysql_errno( getMySQL() ),
mysql_error( getMySQL() ) );
+        MYODBCDbgReturn3( "%p", stringlistTables );
+    }
+
+    while ( pRow = mysql_fetch_row( pResult ) )
+    {
+        stringlistTables += pRow[0];
+    }
+
+    mysql_free_result( pResult ); 
+
+    MYODBCDbgReturn3( "%p", stringlistTables );
+}
+
+QStringList MResultPlus::getColumnsUniqueIndex( const QString &stringCatalog, const
QString &stringSchema, const QString &stringTable, BOOLEAN bExcludePrimary )
+{
+    QString     stringStatement;
+    QString     stringIndexName;
+    QStringList stringlistColumns;
+    MYSQL_RES * pResult = NULL;
+    MYSQL_ROW   pRow;
+
+    MYODBCDbgEnter();
+
+    stringStatement  = "SELECT INDEX_NAME, COLUMN_NAME ";
+    stringStatement += "FROM   INFORMATION_SCHEMA.STATISTICS ";
+    stringStatement += "WHERE  TABLE_SCHEMA='" + stringCatalog + "' AND TABLE_NAME='" +
stringTable + "' AND NON_UNIQUE = 0 " + ( bExcludePrimary ? " AND INDEX_NAME != 'PRIMARY'
" : "" );
+    stringStatement += "ORDER BY INDEX_NAME, SEQ_IN_INDEX ";
+
+    if ( mysql_query( getMySQL(), stringStatement.toUtf8().data() ) )
+    {
+        getDiagnostic()->doAppend( MDiagnostic::DIA_HY000, mysql_errno( getMySQL() ),
mysql_error( getMySQL() ) );
+        MYODBCDbgReturn3( "%p", stringlistTables );
+    }
+
+    pResult = mysql_use_result( getMySQL() );
+    if ( !pResult )
+    {
+        getDiagnostic()->doAppend( MDiagnostic::DIA_HY000, mysql_errno( getMySQL() ),
mysql_error( getMySQL() ) );
+        MYODBCDbgReturn3( "%p", stringlistTables );
+    }
+
+    while ( pRow = mysql_fetch_row( pResult ) )
+    {
+        if ( stringIndexName.isNull() )
+            stringIndexName = pRow[0];
+        else if ( stringIndexName != pRow[0] )
+            break;
+
+        stringlistTables   += pRow[1];
+    }
+
+    mysql_free_result( pResult ); 
+
+    MYODBCDbgReturn3( "%p", stringlistTables );
+}
+
 SQLRETURN MResultPlus::doAppendTypeInfo( const QVariant &stringTypeName,
                                              const QVariant &nDataType,
                                              const QVariant &nColumnSize,
@@ -1943,18 +2023,42 @@
     unsigned int    nField;
     MYSQL_FIELD *   pField;
     SQLRETURN       nReturn = SQL_SUCCESS;
+    QStringList     stringColumns;
 
     /*!
         \internal
-        \todo
+        \note
 
-        Use given schema.
+        We try to use the following in order;
+
+        - primary key
+        - unique index
+
+        There may be other ways to get a unique rowid such as "UNIQUE" column attribute -
but
+        lets avoid tricks where we can.
     */
+    stringColumns = getColumnsPrimaryKey( stringCatalog, stringSchema, stringTable );
+    if ( !stringColumns.count() )
+        stringColumns = getColumnsUniqueIndex( stringCatalog, stringSchema, stringTable
);
+    if ( !stringColumns.count() )
+        MYODBCDbgReturn( SQL_SUCCESS );
+
     /*!
         \internal
         \note
 
-        Backtick is needed in case space in names BUT it does not work when 
+        We do not rely upon the MYSQL_FIELD to determine which columns to include
because;
+
+        - INFORMATION_SCHEMA is likely to remain more 'static' over time
+        - we can avoid bringing in extra columns when we use unique index when more than
one unique index 
+        although that should not happen too often - right? [grin]
+
+        However; we do use MYSQL_FIELD to get column details because we want to reuse the
code which 
+        translates the MYSQL_FIELD into the ODBC friendly IRD. [sigh]
+
+        Translating MYSQL_FIELD into an IRD may seem too expensive but it does keep a
somewhat complicated
+        (lots of rules and assumptions) bit of code isolated and besides, catalog
functions should never
+        be the 'big hit' for performance due to their nature.
     */
     stringSQL = "SELECT * FROM `" + stringCatalog + "`.`" + stringTable + "` LIMIT 0";
 
@@ -1972,24 +2076,11 @@
     MDescriptorIRD descriptor( getStatement() );
     descriptor.setCount( nFields, true );
 
-    /*!
-        \todo
-
-        Use given column filter.
-    */
     for ( nField = 0; nField < nFields; nField++ )
     {
-        /*!
-            \internal
-            \note
-             
-            This is going to seem a bit crazy bit we need to translate the the
MYSQL_FIELD into a descriptor and then map it into the
-            result row based upon the mapping detailed in the ODBC specification. We also
want to use a single function for all
-            mapping of MYSQL_FIELD into ODBC details to avoid having multiple functions
or code blocks getting out of synch.
+        MYSQL_FIELD *pField = mysql_fetch_field_direct( pResult, nField );
 
-            So we translate MYLSQ_FIELD into a descriptor and then map descriptor into
result row.
-        */            
-        nReturn = doLoadMetaDataField( nField + 1, mysql_fetch_field_direct( pResult,
nField ), &descriptor );
+        nReturn = doLoadMetaDataField( nField + 1, pField, &descriptor );
         if ( !SQL_SUCCEEDED( nReturn ) )
         {
             getDiagnostic()->doAppend( MDiagnostic::DIA_HY000, 0, tr("Failed to store
field descriptor information.") );

Modified: trunk/MYSQLPlus/MYSQLPlusLib/MResultPlus.h
===================================================================
--- trunk/MYSQLPlus/MYSQLPlusLib/MResultPlus.h	2006-06-27 23:40:37 UTC (rev 407)
+++ trunk/MYSQLPlus/MYSQLPlusLib/MResultPlus.h	2006-06-28 06:42:16 UTC (rev 408)
@@ -100,6 +100,8 @@
     QStringList getCatalogs( const QString &stringFilter );
     QStringList getSchemas( const QString &stringCatalogFilter, const QString
&stringFilterSchema );
     QStringList getTables( const QString &stringCatalog, const QString
&stringSchema, const QString &stringFilterTable );
+    QStringList getColumnsPrimaryKey( const QString &stringCatalog, const QString
&stringSchema, const QString &stringTable );
+    QStringList getColumnsUniqueIndex( const QString &stringCatalog, const QString
&stringSchema, const QString &stringTable, BOOLEAN bExcludePrimary = true );
 
     /*!
         \internal