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: |
1b9bdd6e1dfd0ac9e488799581360653 |
| 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
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;
}
}
}
|