System.Data.SQLite
Check-in [1b9bdd6e1d]
Not logged in

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

Overview
Comment:More schema enhancements and bugfixes
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sourceforge
Files: files | file ages | folders
SHA1: 1b9bdd6e1dfd0ac9e488799581360653c5f34f70
User & Date: rmsimpson 2005-08-24 21:25:50
Context
2005-08-24
22:09
Interim 3.2.5 updates check-in: 99552d17a8 user: rmsimpson tags: sourceforge
21:25
More schema enhancements and bugfixes check-in: 1b9bdd6e1d user: rmsimpson tags: sourceforge
17:49
1.0.16 check-in: b3b5f9cbaa user: rmsimpson tags: sourceforge
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

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

838
839
840
841
842
843
844

845
846
847
848
849
850
851
...
870
871
872
873
874
875
876
877
878
































879
880
881
882
883
884
885
886
887
888
889
890
891
892

893

894
895



896
897
898
899
900
901
902
....
1327
1328
1329
1330
1331
1332
1333

1334
1335
1336
1337
1338
1339
1340
....
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362

1363
1364

1365
1366
1367






























1368
1369
1370
1371
1372
1373
1374
1375



1376
1377
1378
1379
1380
1381
1382
1383

1384
1385
1386
1387
1388
1389
1390
....
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539

1540
1541
1542
1543
1544

1545
1546
1547
1548
1549
1550
1551
1552
    /// <param name="strIndex">The name of the index to retrieve information for, can be null</param>
    /// <param name="strTable">The table to retrieve index information for, can be null</param>
    /// <returns>DataTable</returns>
    private DataTable Schema_Indexes(string strCatalog, string strIndex, string strTable)
    {
      DataTable tbl = new DataTable("Indexes");
      DataRow row;


      tbl.Locale = CultureInfo.InvariantCulture;
      tbl.Columns.Add("TABLE_CATALOG", typeof(string));
      tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
      tbl.Columns.Add("TABLE_NAME", typeof(string));
      tbl.Columns.Add("INDEX_CATALOG", typeof(string));
      tbl.Columns.Add("INDEX_SCHEMA", typeof(string));
................................................................................
      tbl.Columns.Add("FILTER_CONDITION", typeof(string));
      tbl.Columns.Add("INTEGRATED", typeof(bool));

      tbl.BeginLoadData();

      if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";

      using (SQLiteCommand cmd = new SQLiteCommand(String.Format(CultureInfo.CurrentCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] = 'index'", strCatalog), this))
      {
































        using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader())
        {
          while (rd.Read())
          {
            if (String.Compare(rd.GetString(1), strIndex, true, CultureInfo.CurrentCulture) == 0
            || strIndex == null)
            {
              if (String.Compare(rd.GetString(2), strTable, true, CultureInfo.CurrentCulture) == 0 
              || strTable == null)
              {
                row = tbl.NewRow();

                row["TABLE_CATALOG"] = strCatalog;
                row["TABLE_NAME"] = rd.GetString(2);

                row["INDEX_NAME"] = rd.GetString(1);


                tbl.Rows.Add(row);



              }
            }
          }
        }
      }

      tbl.AcceptChanges();
................................................................................
    /// <param name="strIndex">The index to restrict index information by (can be null)</param>
    /// <param name="strColumn">The source column to restrict index information by (can be null)</param>
    /// <returns>A DataTable containing the results</returns>
    private DataTable Schema_IndexColumns(string strCatalog, string strTable, string strIndex, string strColumn)
    {
      DataTable tbl = new DataTable("IndexColumns");
      DataRow row;


      tbl.Locale = CultureInfo.InvariantCulture;
      tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string));
      tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string));
      tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
      tbl.Columns.Add("TABLE_CATALOG", typeof(string));
      tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
................................................................................
      tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
      tbl.Columns.Add("INDEX_NAME", typeof(string));

      if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";

      tbl.BeginLoadData();

      using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.CurrentCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'index'", strCatalog), this))
      {
        using (SQLiteDataReader rdTable = cmdTable.ExecuteReader())
        {
          while (rdTable.Read())
          {
            if (String.IsNullOrEmpty(strTable) || String.Compare(strTable, rdTable.GetString(2), true, CultureInfo.CurrentCulture) == 0)
            {
              if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, rdTable.GetString(1), true, CultureInfo.CurrentCulture) == 0)
              {
                using (SQLiteCommand cmdIndex = new SQLiteCommand(String.Format(CultureInfo.CurrentCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rdTable.GetString(1)), this))
                {
                  using (SQLiteDataReader rdIndex = cmdIndex.ExecuteReader())

                  {
                    while (rdIndex.Read())

                    {
                      row = tbl.NewRow();
                      row["CONSTRAINT_CATALOG"] = strCatalog;






























                      row["CONSTRAINT_NAME"] = rdTable.GetString(1);
                      row["TABLE_CATALOG"] = strCatalog;
                      row["TABLE_NAME"] = rdTable.GetString(2);
                      row["COLUMN_NAME"] = rdIndex.GetString(2);
                      row["INDEX_NAME"] = rdTable.GetString(1);
                      row["ORDINAL_POSITION"] = rdIndex.GetInt32(1);

                      tbl.Rows.Add(row);



                    }
                  }
                }
              }
            }
          }
        }
      }


      tbl.EndLoadData();
      tbl.AcceptChanges();

      return tbl;
    }

................................................................................
              {
                using (SQLiteDataReader rdKey = cmdKey.ExecuteReader())
                {
                  while (rdKey.Read())
                  {
                    row = tbl.NewRow();
                    row["CONSTRAINT_CATALOG"] = strCatalog;
                    row["CONSTRAINT_NAME"] = String.Format(CultureInfo.CurrentCulture, "unnamed{0}", rdKey.GetInt64(0));
                    row["TABLE_CATALOG"] = strCatalog;
                    row["TABLE_NAME"] = rdTables.GetString(2);
                    row["CONSTRAINT_TYPE"] = "FOREIGN KEY";
                    row["IS_DEFERRABLE"] = false;
                    row["INITIALLY_DEFERRED"] = false;

                    row["FKEY_TO_CATALOG"] = strCatalog;
                    row["FKEY_TO_TABLE"] = rdKey.GetString(2);
                    row["FKEY_FROM_COLUMN"] = rdKey.GetString(3);
                    row["FKEY_TO_COLUMN"] = rdKey.GetString(4);


                    tbl.Rows.Add(row);
                  }
                }
              }
            }
          }
        }
      }







>







 







|

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
|
|
|
|
<
<
<
|

|
|
>
|
>

|
>
>
>







 







>







 







|

|

|

|

|

|

|
>

<
>



>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
|
|
|

|
>
>
>








>







 







|





>


<


>
|







838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
...
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918



919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
....
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
....
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400

1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
....
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613

1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
    /// <param name="strIndex">The name of the index to retrieve information for, can be null</param>
    /// <param name="strTable">The table to retrieve index information for, can be null</param>
    /// <returns>DataTable</returns>
    private DataTable Schema_Indexes(string strCatalog, string strIndex, string strTable)
    {
      DataTable tbl = new DataTable("Indexes");
      DataRow row;
      DataTable tblSchema;

      tbl.Locale = CultureInfo.InvariantCulture;
      tbl.Columns.Add("TABLE_CATALOG", typeof(string));
      tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
      tbl.Columns.Add("TABLE_NAME", typeof(string));
      tbl.Columns.Add("INDEX_CATALOG", typeof(string));
      tbl.Columns.Add("INDEX_SCHEMA", typeof(string));
................................................................................
      tbl.Columns.Add("FILTER_CONDITION", typeof(string));
      tbl.Columns.Add("INTEGRATED", typeof(bool));

      tbl.BeginLoadData();

      if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";

      using (SQLiteCommand cmdTables = new SQLiteCommand(String.Format(CultureInfo.CurrentCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'table'", strCatalog), this))
      {
        using (SQLiteDataReader rdTables = cmdTables.ExecuteReader())
        {
          while (rdTables.Read())
          {
            if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.CurrentCulture) == 0)
            {
              using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.CurrentCulture, "SELECT * FROM [{0}]", rdTables.GetString(2)), this))
              {
                using (SQLiteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                  tblSchema = rdTable.GetSchemaTable();
                  foreach (DataRow schemaRow in tblSchema.Rows)
                  {
                    if (Convert.ToBoolean(schemaRow[SchemaTableColumn.IsKey]) == true)
                    {
                      row = tbl.NewRow();
                      row["TABLE_CATALOG"] = strCatalog;
                      row["TABLE_NAME"] = rdTables.GetString(2);
                      row["INDEX_CATALOG"] = strCatalog;
                      row["INDEX_NAME"] = String.Format(CultureInfo.CurrentCulture, "PK_{0}_{1}", rdTables.GetString(2), schemaRow[SchemaTableColumn.BaseColumnName]);
                      row["PRIMARY_KEY"] = true;
                      row["UNIQUE"] = true;
                      if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, row["INDEX_NAME"].ToString(), true, CultureInfo.CurrentCulture) == 0)
                      {
                        tbl.Rows.Add(row);
                      }
                    }
                  }
                }
              }
              using (SQLiteCommand cmd = new SQLiteCommand(String.Format(CultureInfo.CurrentCulture, "PRAGMA [{0}].index_list([{1}])", strCatalog, rdTables.GetString(2)), this))
              {
                using (SQLiteDataReader rd = (SQLiteDataReader)cmd.ExecuteReader())
                {
                  while (rd.Read())
                  {
                    if (String.Compare(rd.GetString(1), strIndex, true, CultureInfo.CurrentCulture) == 0
                    || strIndex == null)
                    {



                      row = tbl.NewRow();

                      row["TABLE_CATALOG"] = strCatalog;
                      row["TABLE_NAME"] = rdTables.GetString(2);
                      row["INDEX_CATALOG"] = strCatalog;
                      row["INDEX_NAME"] = rd.GetString(1);
                      row["UNIQUE"] = rd.GetBoolean(2);

                      tbl.Rows.Add(row);
                    }
                  }
                }
              }
            }
          }
        }
      }

      tbl.AcceptChanges();
................................................................................
    /// <param name="strIndex">The index to restrict index information by (can be null)</param>
    /// <param name="strColumn">The source column to restrict index information by (can be null)</param>
    /// <returns>A DataTable containing the results</returns>
    private DataTable Schema_IndexColumns(string strCatalog, string strTable, string strIndex, string strColumn)
    {
      DataTable tbl = new DataTable("IndexColumns");
      DataRow row;
      DataTable tblSchema;

      tbl.Locale = CultureInfo.InvariantCulture;
      tbl.Columns.Add("CONSTRAINT_CATALOG", typeof(string));
      tbl.Columns.Add("CONSTRAINT_SCHEMA", typeof(string));
      tbl.Columns.Add("CONSTRAINT_NAME", typeof(string));
      tbl.Columns.Add("TABLE_CATALOG", typeof(string));
      tbl.Columns.Add("TABLE_SCHEMA", typeof(string));
................................................................................
      tbl.Columns.Add("ORDINAL_POSITION", typeof(int));
      tbl.Columns.Add("INDEX_NAME", typeof(string));

      if (String.IsNullOrEmpty(strCatalog)) strCatalog = "main";

      tbl.BeginLoadData();

      using (SQLiteCommand cmdTables = new SQLiteCommand(String.Format(CultureInfo.CurrentCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'table'", strCatalog), this))
      {
        using (SQLiteDataReader rdTables = cmdTables.ExecuteReader())
        {
          while (rdTables.Read())
          {
            if (String.IsNullOrEmpty(strTable) || String.Compare(rdTables.GetString(2), strTable, true, CultureInfo.CurrentCulture) == 0)
            {
              using (SQLiteCommand cmdTable = new SQLiteCommand(String.Format(CultureInfo.CurrentCulture, "SELECT * FROM [{0}]", rdTables.GetString(2)), this))
              {
                using (SQLiteDataReader rdTable = cmdTable.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                  tblSchema = rdTable.GetSchemaTable();
                  foreach (DataRow schemaRow in tblSchema.Rows)
                  {

                    if (Convert.ToBoolean(schemaRow[SchemaTableColumn.IsKey]) == true)
                    {
                      row = tbl.NewRow();
                      row["CONSTRAINT_CATALOG"] = strCatalog;
                      row["CONSTRAINT_NAME"] = String.Format(CultureInfo.CurrentCulture, "PK_{0}_{1}", rdTables.GetString(2), schemaRow[SchemaTableColumn.BaseColumnName]);
                      row["TABLE_CATALOG"] = strCatalog;
                      row["TABLE_NAME"] = rdTables.GetString(2);
                      row["COLUMN_NAME"] = schemaRow[SchemaTableColumn.BaseColumnName];
                      row["INDEX_NAME"] = row["CONSTRAINT_NAME"];
                      row["ORDINAL_POSITION"] = schemaRow[SchemaTableColumn.ColumnOrdinal];
                      if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, row["INDEX_NAME"].ToString(), true, CultureInfo.CurrentCulture) == 0)
                      {
                        tbl.Rows.Add(row);
                      }
                    }
                  }
                }
              }
              using (SQLiteCommand cmdIndexes = new SQLiteCommand(String.Format(CultureInfo.CurrentCulture, "SELECT * FROM [{0}].[sqlite_master] WHERE [type] LIKE 'index' AND [tbl_name] LIKE '{1}'", strCatalog, rdTables.GetString(2)), this))
              {
                using (SQLiteDataReader rdIndexes = cmdIndexes.ExecuteReader())
                {
                  while (rdIndexes.Read())
                  {
                    if (String.IsNullOrEmpty(strIndex) || String.Compare(strIndex, rdIndexes.GetString(1), true, CultureInfo.CurrentCulture) == 0)
                    {
                      using (SQLiteCommand cmdIndex = new SQLiteCommand(String.Format(CultureInfo.CurrentCulture, "PRAGMA [{0}].index_info([{1}])", strCatalog, rdIndexes.GetString(1)), this))
                      {
                        using (SQLiteDataReader rdIndex = cmdIndex.ExecuteReader())
                        {
                          while (rdIndex.Read())
                          {
                            row = tbl.NewRow();
                            row["CONSTRAINT_CATALOG"] = strCatalog;
                            row["CONSTRAINT_NAME"] = rdIndexes.GetString(1);
                            row["TABLE_CATALOG"] = strCatalog;
                            row["TABLE_NAME"] = rdIndexes.GetString(2);
                            row["COLUMN_NAME"] = rdIndex.GetString(2);
                            row["INDEX_NAME"] = rdIndexes.GetString(1);
                            row["ORDINAL_POSITION"] = rdIndex.GetInt32(1);

                            tbl.Rows.Add(row);
                          }
                        }
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }


      tbl.EndLoadData();
      tbl.AcceptChanges();

      return tbl;
    }

................................................................................
              {
                using (SQLiteDataReader rdKey = cmdKey.ExecuteReader())
                {
                  while (rdKey.Read())
                  {
                    row = tbl.NewRow();
                    row["CONSTRAINT_CATALOG"] = strCatalog;
                    row["CONSTRAINT_NAME"] = String.Format(CultureInfo.CurrentCulture, "FK_{0}_{1}_{2}", rdTables.GetString(2), rdKey.GetString(3), rdKey.GetString(4));
                    row["TABLE_CATALOG"] = strCatalog;
                    row["TABLE_NAME"] = rdTables.GetString(2);
                    row["CONSTRAINT_TYPE"] = "FOREIGN KEY";
                    row["IS_DEFERRABLE"] = false;
                    row["INITIALLY_DEFERRED"] = false;
                    row["FKEY_FROM_COLUMN"] = rdKey.GetString(3);
                    row["FKEY_TO_CATALOG"] = strCatalog;
                    row["FKEY_TO_TABLE"] = rdKey.GetString(2);

                    row["FKEY_TO_COLUMN"] = rdKey.GetString(4);

                    if (String.IsNullOrEmpty(strKeyName) || String.Compare(strKeyName, row["CONSTRAINT_NAME"].ToString(), true, CultureInfo.CurrentCulture) == 0)
                      tbl.Rows.Add(row);
                  }
                }
              }
            }
          }
        }
      }

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

542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
                        {
                          row[SchemaTableOptionalColumn.BaseCatalogName] = strCatalog;
                        }

                        row[SchemaTableColumn.AllowDBNull] = (!bNotNull && !bPrimaryKey);
                        row[SchemaTableColumn.IsUnique] = bPrimaryKey;
                        row[SchemaTableColumn.IsKey] = bPrimaryKey;
                        row[SchemaTableOptionalColumn.IsAutoIncrement] = (bPrimaryKey &&
                            String.Compare(strType, "integer", true, CultureInfo.InvariantCulture) == 0);
                        row[SchemaTableOptionalColumn.IsReadOnly] = (bool)row[SchemaTableOptionalColumn.IsAutoIncrement];
                        if (rdTable.IsDBNull(4) == false)
                          row[SchemaTableOptionalColumn.DefaultValue] = rdTable[4];
                        break;
                      }
                    }
                  }







|
<







542
543
544
545
546
547
548
549

550
551
552
553
554
555
556
                        {
                          row[SchemaTableOptionalColumn.BaseCatalogName] = strCatalog;
                        }

                        row[SchemaTableColumn.AllowDBNull] = (!bNotNull && !bPrimaryKey);
                        row[SchemaTableColumn.IsUnique] = bPrimaryKey;
                        row[SchemaTableColumn.IsKey] = bPrimaryKey;
                        row[SchemaTableOptionalColumn.IsAutoIncrement] = (String.Compare(strType, "autoincrement", true, CultureInfo.InvariantCulture) == 0);

                        row[SchemaTableOptionalColumn.IsReadOnly] = (bool)row[SchemaTableOptionalColumn.IsAutoIncrement];
                        if (rdTable.IsDBNull(4) == false)
                          row[SchemaTableOptionalColumn.DefaultValue] = rdTable[4];
                        break;
                      }
                    }
                  }