System.Data.SQLite
Check-in [14a95defbf]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Schema fixes
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sourceforge
Files: files | file ages | folders
SHA1: 14a95defbfa6c8567dedd2633a992b326e664519
User & Date: rmsimpson 2006-02-13 04:54:12
Context
2006-02-13
04:54
Bugfixes and 64-bit support check-in: 463c0eb0ca user: rmsimpson tags: sourceforge
04:54
Schema fixes check-in: 14a95defbf user: rmsimpson tags: sourceforge
04:53
Lots of bugfixes and 64-bit support check-in: 6b095c4d3e user: rmsimpson tags: sourceforge
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to System.Data.SQLite/SQLiteConnection.cs.

  1057   1057       /// <param name="strTable">The table to retrieve index information for, can be null</param>
  1058   1058       /// <returns>DataTable</returns>
  1059   1059       private DataTable Schema_Indexes(string strCatalog, string strTable, string strIndex)
  1060   1060       {
  1061   1061         DataTable tbl = new DataTable("Indexes");
  1062   1062         DataRow row;
  1063   1063         DataTable tblSchema;
         1064  +      string primaryKey;
  1064   1065   
  1065   1066         tbl.Locale = CultureInfo.InvariantCulture;
  1066   1067         tbl.Columns.Add("TABLE_CATALOG", typeof(string));
  1067   1068         tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
  1068   1069         tbl.Columns.Add("TABLE_NAME", typeof(string));
  1069   1070         tbl.Columns.Add("INDEX_CATALOG", typeof(string));
  1070   1071         tbl.Columns.Add("INDEX_SCHEMA", typeof(string));
................................................................................
  1095   1096   
  1096   1097         using (SQLiteCommand cmdTables = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'table'", strCatalog), this))
  1097   1098         {
  1098   1099           using (SQLiteDataReader rdTables = cmdTables.ExecuteReader())
  1099   1100           {
  1100   1101             while (rdTables.Read())
  1101   1102             {
         1103  +            primaryKey = "";
  1102   1104               if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0)
  1103   1105               {
  1104         -              using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}]", rdTables.GetString(2)), this))
         1106  +              // First, look for any rowid indexes -- which sqlite defines are INTEGER PRIMARY KEY columns.
         1107  +              // Such indexes are not listed in the indexes list but count as indexes just the same.
         1108  +              using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
  1105   1109                 {
  1106   1110                   using (SQLiteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
  1107   1111                   {
  1108   1112                     tblSchema = rdTable.GetSchemaTable(false);
  1109   1113                     foreach (DataRow schemaRow in tblSchema.Rows)
  1110   1114                     {
  1111         -                    if (Convert.ToBoolean(schemaRow[SchemaTableColumn.IsKey], CultureInfo.CurrentCulture) == true)
         1115  +                    if (schemaRow.IsNull("DeclaredType") == false)
  1112   1116                       {
  1113         -                      row = tbl.NewRow();
  1114         -                      row["TABLE_CATALOG"] = strCatalog;
  1115         -                      row["TABLE_NAME"] = rdTables.GetString(2);
  1116         -                      row["INDEX_CATALOG"] = strCatalog;
  1117         -                      row["INDEX_NAME"] = String.Format(CultureInfo.InvariantCulture, "sqlite_master_PK_{0}", rdTables.GetString(2));
  1118         -                      row["PRIMARY_KEY"] = true;
  1119         -                      row["UNIQUE"] = true;
  1120         -                      if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, row["INDEX_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0)
         1117  +                      if ((bool)schemaRow[SchemaTableColumn.IsKey] == true)
  1121   1118                         {
  1122         -                        tbl.Rows.Add(row);
  1123         -                        break;
         1119  +                        row = tbl.NewRow();
         1120  +
         1121  +                        row["TABLE_CATALOG"] = strCatalog;
         1122  +                        row["TABLE_NAME"] = rdTables.GetString(2);
         1123  +                        row["INDEX_CATALOG"] = strCatalog;
         1124  +                        row["PRIMARY_KEY"] = true;
         1125  +
         1126  +                        // If the primary key is of type INTEGER, then its a rowid and we need to make a fake index entry for it.
         1127  +                        if (String.Compare((string)schemaRow["DeclaredType"], "INTEGER", true, CultureInfo.InvariantCulture) == 0)
         1128  +                        {
         1129  +                          row["INDEX_NAME"] = String.Format(CultureInfo.InvariantCulture, "sqlite_master_PK_{0}", rdTables.GetString(2));
         1130  +                          row["UNIQUE"] = true;
         1131  +                          tbl.Rows.Add(row);
         1132  +                          break;
         1133  +                        }
         1134  +                        // Otherwise, record this primary key's index name so we can match it up later and mark it as a primary key
         1135  +                        else primaryKey = String.Format(CultureInfo.InvariantCulture, "sqlite_autoindex_{0}_", rdTables.GetString(2));
  1124   1136                         }
  1125   1137                       }
  1126   1138                     }
  1127   1139                   }
  1128   1140                 }
         1141  +
         1142  +              // Now fetch all the rest of the indexes.
  1129   1143                 using (SQLiteCommand cmd = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].index_list([{1}])", strCatalog, rdTables.GetString(2)), this))
  1130   1144                 {
  1131   1145                   using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader())
  1132   1146                   {
  1133   1147                     while (rd.Read())
  1134   1148                     {
  1135   1149                       if (String.Compare(rd.GetString(1), strIndex, true, CultureInfo.InvariantCulture) == 0
................................................................................
  1138   1152                         row = tbl.NewRow();
  1139   1153   
  1140   1154                         row["TABLE_CATALOG"] = strCatalog;
  1141   1155                         row["TABLE_NAME"] = rdTables.GetString(2);
  1142   1156                         row["INDEX_CATALOG"] = strCatalog;
  1143   1157                         row["INDEX_NAME"] = rd.GetString(1);
  1144   1158                         row["UNIQUE"] = rd.GetBoolean(2);
         1159  +                      
         1160  +                      if (rd.GetString(1).StartsWith(primaryKey, true, CultureInfo.InvariantCulture) == true)
         1161  +                        row["PRIMARY_KEY"] = true;
  1145   1162   
  1146   1163                         tbl.Rows.Add(row);
  1147   1164                       }
  1148   1165                     }
  1149   1166                   }
  1150   1167                 }
  1151   1168               }
................................................................................
  1394   1411         {
  1395   1412           using (SQLiteDataReader rdTables = cmdTables.ExecuteReader())
  1396   1413           {
  1397   1414             while (rdTables.Read())
  1398   1415             {
  1399   1416               if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.InvariantCulture) == 0)
  1400   1417               {
  1401         -              using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}]", rdTables.GetString(2)), this))
         1418  +              using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
  1402   1419                 {
  1403   1420                   using (SQLiteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
  1404   1421                   {
  1405   1422                     tblSchema = rdTable.GetSchemaTable(false);
  1406   1423                     foreach (DataRow schemaRow in tblSchema.Rows)
  1407   1424                     {
  1408         -                    if (Convert.ToBoolean(schemaRow[SchemaTableColumn.IsKey], CultureInfo.InvariantCulture) == true)
         1425  +                    if (schemaRow.IsNull("DeclaredType") == false)
  1409   1426                       {
  1410         -                      row = tbl.NewRow();
  1411         -                      row["CONSTRAINT_CATALOG"] = strCatalog;
  1412         -                      row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "sqlite_master_PK_{0}", rdTables.GetString(2));
  1413         -                      row["TABLE_CATALOG"] = strCatalog;
  1414         -                      row["TABLE_NAME"] = rdTables.GetString(2);
  1415         -                      row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.BaseColumnName];
  1416         -                      row["INDEX_NAME"] = row["CONSTRAINT_NAME"];
  1417         -                      row["ORDINAL_POSITION"] = schemaRow[SchemaTableColumn.ColumnOrdinal];
  1418         -                      if ((String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, row["INDEX_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0)
  1419         -                          && (String.IsNullOrEmpty(strColumn) || String.Compare(strColumn, row["COLUMN_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0))
         1427  +                      if (String.Compare((string)schemaRow["DeclaredType"], "INTEGER", true, CultureInfo.InvariantCulture) == 0
         1428  +                        && Convert.ToBoolean(schemaRow[SchemaTableColumn.IsKey], CultureInfo.InvariantCulture) == true)
  1420   1429                         {
  1421         -                        tbl.Rows.Add(row);
         1430  +                        row = tbl.NewRow();
         1431  +                        row["CONSTRAINT_CATALOG"] = strCatalog;
         1432  +                        row["CONSTRAINT_NAME"] = String.Format(CultureInfo.InvariantCulture, "sqlite_master_PK_{0}", rdTables.GetString(2));
         1433  +                        row["TABLE_CATALOG"] = strCatalog;
         1434  +                        row["TABLE_NAME"] = rdTables.GetString(2);
         1435  +                        row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.BaseColumnName];
         1436  +                        row["INDEX_NAME"] = row["CONSTRAINT_NAME"];
         1437  +                        row["ORDINAL_POSITION"] = schemaRow[SchemaTableColumn.ColumnOrdinal];
         1438  +                        if ((String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, row["INDEX_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0)
         1439  +                            && (String.IsNullOrEmpty(strColumn) || String.Compare(strColumn, row["COLUMN_NAME"].ToString(), true, CultureInfo.InvariantCulture) == 0))
         1440  +                        {
         1441  +                          tbl.Rows.Add(row);
         1442  +                          break;
         1443  +                        }
  1422   1444                         }
  1423   1445                       }
  1424   1446                     }
  1425   1447                   }
  1426   1448                 }
         1449  +
  1427   1450                 using (SQLiteCommand cmdIndexes = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'index' AND [tbl_name] LIKE '{1}'", strCatalog, rdTables.GetString(2)), this))
  1428   1451                 {
  1429   1452                   using (SQLiteDataReader rdIndexes = cmdIndexes.ExecuteReader())
  1430   1453                   {
  1431   1454                     while (rdIndexes.Read())
  1432   1455                     {
  1433   1456                       if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, rdIndexes.GetString(1), true, CultureInfo.InvariantCulture) == 0)
................................................................................
  1600   1623         {
  1601   1624           using (SQLiteDataReader rdTables = cmdTables.ExecuteReader())
  1602   1625           {
  1603   1626             while (rdTables.Read())
  1604   1627             {
  1605   1628               if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTables.GetString(2), true, CultureInfo.InvariantCulture) == 0)
  1606   1629               {
  1607         -              using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}]", rdTables.GetString(2)), this))
         1630  +              using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "SELECT * FROM [{0}].[{1}]", strCatalog, rdTables.GetString(2)), this))
  1608   1631                 {
  1609   1632                   using (SQLiteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
  1610   1633                   {
  1611   1634                     using (SQLiteCommand cmdKey = new SQLiteCommand(String.Format(CultureInfo.InvariantCulture, "PRAGMA [{0}].foreign_key_list([{1}])", strCatalog, rdTables.GetString(2)), this))
  1612   1635                     {
  1613   1636                       using (SQLiteDataReader rdKey = cmdKey.ExecuteReader())
  1614   1637                       {

Changes to System.Data.SQLite/SQLiteDataReader.cs.

   479    479         tbl.Columns.Add(SchemaTableOptionalColumn.IsAutoIncrement, typeof(Boolean));
   480    480         tbl.Columns.Add(SchemaTableOptionalColumn.IsRowVersion, typeof(Boolean));
   481    481         tbl.Columns.Add(SchemaTableOptionalColumn.IsHidden, typeof(Boolean));
   482    482         tbl.Columns.Add(SchemaTableColumn.IsLong, typeof(Boolean));
   483    483         tbl.Columns.Add(SchemaTableOptionalColumn.IsReadOnly, typeof(Boolean));
   484    484         tbl.Columns.Add(SchemaTableOptionalColumn.ProviderSpecificDataType, typeof(Type));
   485    485         tbl.Columns.Add(SchemaTableOptionalColumn.DefaultValue, typeof(object));
          486  +      tbl.Columns.Add("DeclaredType", typeof(string));
   486    487   
   487    488         tbl.BeginLoadData();
   488    489   
   489    490         SQLiteConnection cnn = (SQLiteConnection)_command.Connection;
   490    491   
   491    492         // Create a new command based on the original.  The only difference being that this new command returns
   492    493         // fully-qualified Database.Table.Column column names because of the above pragma
................................................................................
   559    560                         _command.Connection._sql.ColumnMetaData(
   560    561                   (string)row[SchemaTableOptionalColumn.BaseCatalogName],
   561    562                   (string)row[SchemaTableColumn.BaseTableName],
   562    563                   strColumn,
   563    564                   out dataType, out collSeq, out bNotNull, out bPrimaryKey, out bAutoIncrement);
   564    565   
   565    566                         if (bNotNull) row[SchemaTableColumn.AllowDBNull] = false;
          567  +
   566    568                         row[SchemaTableColumn.IsKey] = bPrimaryKey;
   567    569                         row[SchemaTableOptionalColumn.IsAutoIncrement] = bAutoIncrement;
          570  +
          571  +                      if (String.IsNullOrEmpty(dataType) == false)
          572  +                        row["DeclaredType"] = dataType;
          573  +
   568    574                         if (rdTable.IsDBNull(4) == false)
   569    575                           row[SchemaTableOptionalColumn.DefaultValue] = rdTable[4];
   570    576                         break;
   571    577                       }
   572    578                     }
   573    579                   }
   574    580                 }