Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | The GetSchemaTable method must verify the base table name (for a column) actually refers to a base table before attempting to query its metadata. Pursuant to [baf42ee135]. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | 63ae5401bfd3fbba2468b9f7cdaf8625 |
| User & Date: | mistachkin 2018-03-09 17:45:03 |
Context
|
2018-03-09
| ||
| 21:47 | Fix typos in the data reader class that could lead to them returning the wrong value for GetDatabaseName / GetTableName methods. check-in: 785601b768 user: mistachkin tags: trunk | |
| 17:45 | The GetSchemaTable method must verify the base table name (for a column) actually refers to a base table before attempting to query its metadata. Pursuant to [baf42ee135]. check-in: 63ae5401bf user: mistachkin tags: trunk | |
| 17:41 | Further improvements to the catalog name and master table name handling in the connection class. check-in: 9bb5fe6f96 user: mistachkin tags: trunk | |
Changes
Changes to System.Data.SQLite/SQLite3.cs.
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
....
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
|
int len = 0;
return UTF8ToString(UnsafeNativeMethods.sqlite3_column_table_name_interop(stmt._sqlite_stmt, index, ref len), len);
#else
return UTF8ToString(UnsafeNativeMethods.sqlite3_column_table_name(stmt._sqlite_stmt, index), -1);
#endif
}
internal override void ColumnMetaData(string dataBase, string table, string column, ref string dataType, ref string collateSequence, ref bool notNull, ref bool primaryKey, ref bool autoIncrement)
{
IntPtr dataTypePtr = IntPtr.Zero;
IntPtr collSeqPtr = IntPtr.Zero;
int nnotNull = 0;
int nprimaryKey = 0;
int nautoInc = 0;
SQLiteErrorCode n;
................................................................................
n = UnsafeNativeMethods.sqlite3_table_column_metadata_interop(_sql, ToUTF8(dataBase), ToUTF8(table), ToUTF8(column), ref dataTypePtr, ref collSeqPtr, ref nnotNull, ref nprimaryKey, ref nautoInc, ref dtLen, ref csLen);
#else
dtLen = -1;
csLen = -1;
n = UnsafeNativeMethods.sqlite3_table_column_metadata(_sql, ToUTF8(dataBase), ToUTF8(table), ToUTF8(column), ref dataTypePtr, ref collSeqPtr, ref nnotNull, ref nprimaryKey, ref nautoInc);
#endif
if (n != SQLiteErrorCode.Ok) throw new SQLiteException(n, GetLastError());
dataType = UTF8ToString(dataTypePtr, dtLen);
collateSequence = UTF8ToString(collSeqPtr, csLen);
notNull = (nnotNull == 1);
primaryKey = (nprimaryKey == 1);
autoIncrement = (nautoInc == 1);
}
internal override object GetObject(SQLiteStatement stmt, int index)
{
switch (ColumnAffinity(stmt, index))
{
case TypeAffinity.Int64:
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
>
>
|
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
....
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
|
int len = 0;
return UTF8ToString(UnsafeNativeMethods.sqlite3_column_table_name_interop(stmt._sqlite_stmt, index, ref len), len);
#else
return UTF8ToString(UnsafeNativeMethods.sqlite3_column_table_name(stmt._sqlite_stmt, index), -1);
#endif
}
internal override bool DoesTableExist(
string dataBase,
string table
)
{
string dataType = null; /* NOT USED */
string collateSequence = null; /* NOT USED */
bool notNull = false; /* NOT USED */
bool primaryKey = false; /* NOT USED */
bool autoIncrement = false; /* NOT USED */
return ColumnMetaData(
dataBase, table, null, false, ref dataType,
ref collateSequence, ref notNull, ref primaryKey,
ref autoIncrement);
}
internal override bool ColumnMetaData(string dataBase, string table, string column, bool canThrow, ref string dataType, ref string collateSequence, ref bool notNull, ref bool primaryKey, ref bool autoIncrement)
{
IntPtr dataTypePtr = IntPtr.Zero;
IntPtr collSeqPtr = IntPtr.Zero;
int nnotNull = 0;
int nprimaryKey = 0;
int nautoInc = 0;
SQLiteErrorCode n;
................................................................................
n = UnsafeNativeMethods.sqlite3_table_column_metadata_interop(_sql, ToUTF8(dataBase), ToUTF8(table), ToUTF8(column), ref dataTypePtr, ref collSeqPtr, ref nnotNull, ref nprimaryKey, ref nautoInc, ref dtLen, ref csLen);
#else
dtLen = -1;
csLen = -1;
n = UnsafeNativeMethods.sqlite3_table_column_metadata(_sql, ToUTF8(dataBase), ToUTF8(table), ToUTF8(column), ref dataTypePtr, ref collSeqPtr, ref nnotNull, ref nprimaryKey, ref nautoInc);
#endif
if (canThrow && (n != SQLiteErrorCode.Ok)) throw new SQLiteException(n, GetLastError());
dataType = UTF8ToString(dataTypePtr, dtLen);
collateSequence = UTF8ToString(collSeqPtr, csLen);
notNull = (nnotNull == 1);
primaryKey = (nprimaryKey == 1);
autoIncrement = (nautoInc == 1);
return (n == SQLiteErrorCode.Ok);
}
internal override object GetObject(SQLiteStatement stmt, int index)
{
switch (ColumnAffinity(stmt, index))
{
case TypeAffinity.Int64:
|
Changes to System.Data.SQLite/SQLiteBase.cs.
251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 |
internal abstract string ColumnName(SQLiteStatement stmt, int index);
internal abstract TypeAffinity ColumnAffinity(SQLiteStatement stmt, int index);
internal abstract string ColumnType(SQLiteStatement stmt, int index, ref TypeAffinity nAffinity);
internal abstract int ColumnIndex(SQLiteStatement stmt, string columnName);
internal abstract string ColumnOriginalName(SQLiteStatement stmt, int index);
internal abstract string ColumnDatabaseName(SQLiteStatement stmt, int index);
internal abstract string ColumnTableName(SQLiteStatement stmt, int index);
internal abstract void ColumnMetaData(string dataBase, string table, string column, ref string dataType, ref string collateSequence, ref bool notNull, ref bool primaryKey, ref bool autoIncrement);
internal abstract void GetIndexColumnExtendedInfo(string database, string index, string column, ref int sortMode, ref int onError, ref string collationSequence);
internal abstract object GetObject(SQLiteStatement stmt, int index);
internal abstract double GetDouble(SQLiteStatement stmt, int index);
internal abstract Boolean GetBoolean(SQLiteStatement stmt, int index);
internal abstract SByte GetSByte(SQLiteStatement stmt, int index);
internal abstract Byte GetByte(SQLiteStatement stmt, int index);
|
> | |
251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 |
internal abstract string ColumnName(SQLiteStatement stmt, int index);
internal abstract TypeAffinity ColumnAffinity(SQLiteStatement stmt, int index);
internal abstract string ColumnType(SQLiteStatement stmt, int index, ref TypeAffinity nAffinity);
internal abstract int ColumnIndex(SQLiteStatement stmt, string columnName);
internal abstract string ColumnOriginalName(SQLiteStatement stmt, int index);
internal abstract string ColumnDatabaseName(SQLiteStatement stmt, int index);
internal abstract string ColumnTableName(SQLiteStatement stmt, int index);
internal abstract bool DoesTableExist(string dataBase, string table);
internal abstract bool ColumnMetaData(string dataBase, string table, string column, bool canThrow, ref string dataType, ref string collateSequence, ref bool notNull, ref bool primaryKey, ref bool autoIncrement);
internal abstract void GetIndexColumnExtendedInfo(string database, string index, string column, ref int sortMode, ref int onError, ref string collationSequence);
internal abstract object GetObject(SQLiteStatement stmt, int index);
internal abstract double GetDouble(SQLiteStatement stmt, int index);
internal abstract Boolean GetBoolean(SQLiteStatement stmt, int index);
internal abstract SByte GetSByte(SQLiteStatement stmt, int index);
internal abstract Byte GetByte(SQLiteStatement stmt, int index);
|
Changes to System.Data.SQLite/SQLiteDataReader.cs.
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 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 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 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 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 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 |
return result;
}
///////////////////////////////////////////////////////////////////////////
internal DataTable GetSchemaTable(bool wantUniqueInfo, bool wantDefaultValue)
{
CheckClosed();
if (_throwOnDisposed) SQLiteCommand.Check(_command);
//
// BUGFIX: We need to quickly scan all the fields in the current
// "result set" to see how many distinct tables are actually
// involved. This information is necessary so that some
// intelligent decisions can be made when constructing the
// metadata below. For example, we need to be very careful
// about flagging a particular column as "unique" just
// because it was in its original underlying database table
// if there are now multiple tables involved in the
// "result set". See ticket [7e3fa93744] for more detailed
// information.
//
Dictionary<ColumnParent, List<int>> parentToColumns = null;
Dictionary<int, ColumnParent> columnToParent = null;
GetStatementColumnParents(
_command.Connection._sql, _activeStatement, _fieldCount,
ref parentToColumns, ref columnToParent);
DataTable tbl = new DataTable("SchemaTable");
DataTable tblIndexes = null;
DataTable tblIndexColumns;
DataRow row;
string temp;
string strCatalog = String.Empty;
string strTable = String.Empty;
string strColumn = String.Empty;
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add(SchemaTableColumn.ColumnName, typeof(String));
tbl.Columns.Add(SchemaTableColumn.ColumnOrdinal, typeof(int));
tbl.Columns.Add(SchemaTableColumn.ColumnSize, typeof(int));
tbl.Columns.Add(SchemaTableColumn.NumericPrecision, typeof(int));
tbl.Columns.Add(SchemaTableColumn.NumericScale, typeof(int));
tbl.Columns.Add(SchemaTableColumn.IsUnique, typeof(Boolean));
tbl.Columns.Add(SchemaTableColumn.IsKey, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.BaseServerName, typeof(string));
tbl.Columns.Add(SchemaTableOptionalColumn.BaseCatalogName, typeof(String));
tbl.Columns.Add(SchemaTableColumn.BaseColumnName, typeof(String));
tbl.Columns.Add(SchemaTableColumn.BaseSchemaName, typeof(String));
tbl.Columns.Add(SchemaTableColumn.BaseTableName, typeof(String));
tbl.Columns.Add(SchemaTableColumn.DataType, typeof(Type));
tbl.Columns.Add(SchemaTableColumn.AllowDBNull, typeof(Boolean));
tbl.Columns.Add(SchemaTableColumn.ProviderType, typeof(int));
tbl.Columns.Add(SchemaTableColumn.IsAliased, typeof(Boolean));
tbl.Columns.Add(SchemaTableColumn.IsExpression, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.IsAutoIncrement, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.IsRowVersion, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.IsHidden, typeof(Boolean));
tbl.Columns.Add(SchemaTableColumn.IsLong, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.IsReadOnly, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.ProviderSpecificDataType, typeof(Type));
tbl.Columns.Add(SchemaTableOptionalColumn.DefaultValue, typeof(object));
tbl.Columns.Add("DataTypeName", typeof(string));
tbl.Columns.Add("CollationType", typeof(string));
tbl.BeginLoadData();
for (int n = 0; n < _fieldCount; n++)
{
SQLiteType sqlType = GetSQLiteType(_flags, n);
row = tbl.NewRow();
DbType typ = sqlType.Type;
// Default settings for the column
row[SchemaTableColumn.ColumnName] = GetName(n);
row[SchemaTableColumn.ColumnOrdinal] = n;
row[SchemaTableColumn.ColumnSize] = SQLiteConvert.DbTypeToColumnSize(typ);
row[SchemaTableColumn.NumericPrecision] = SQLiteConvert.DbTypeToNumericPrecision(typ);
row[SchemaTableColumn.NumericScale] = SQLiteConvert.DbTypeToNumericScale(typ);
row[SchemaTableColumn.ProviderType] = sqlType.Type;
row[SchemaTableColumn.IsLong] = false;
row[SchemaTableColumn.AllowDBNull] = true;
row[SchemaTableOptionalColumn.IsReadOnly] = false;
row[SchemaTableOptionalColumn.IsRowVersion] = false;
row[SchemaTableColumn.IsUnique] = false;
row[SchemaTableColumn.IsKey] = false;
row[SchemaTableOptionalColumn.IsAutoIncrement] = false;
row[SchemaTableColumn.DataType] = GetFieldType(n);
row[SchemaTableOptionalColumn.IsHidden] = false;
row[SchemaTableColumn.BaseSchemaName] = _baseSchemaName;
strColumn = columnToParent[n].ColumnName;
if (String.IsNullOrEmpty(strColumn) == false) row[SchemaTableColumn.BaseColumnName] = strColumn;
row[SchemaTableColumn.IsExpression] = String.IsNullOrEmpty(strColumn);
row[SchemaTableColumn.IsAliased] = (String.Compare(GetName(n), strColumn, StringComparison.OrdinalIgnoreCase) != 0);
temp = columnToParent[n].TableName;
if (String.IsNullOrEmpty(temp) == false) row[SchemaTableColumn.BaseTableName] = temp;
temp = columnToParent[n].DatabaseName;
if (String.IsNullOrEmpty(temp) == false) row[SchemaTableOptionalColumn.BaseCatalogName] = temp;
string dataType = null;
// If we have a table-bound column, extract the extra information from it
if (String.IsNullOrEmpty(strColumn) == false)
{
string baseCatalogName = String.Empty;
if (row[SchemaTableOptionalColumn.BaseCatalogName] != DBNull.Value)
baseCatalogName = (string)row[SchemaTableOptionalColumn.BaseCatalogName];
string baseTableName = String.Empty;
if (row[SchemaTableColumn.BaseTableName] != DBNull.Value)
baseTableName = (string)row[SchemaTableColumn.BaseTableName];
string baseColumnName = String.Empty;
if (row[SchemaTableColumn.BaseColumnName] != DBNull.Value)
baseColumnName = (string)row[SchemaTableColumn.BaseColumnName];
string collSeq = null;
bool bNotNull = false;
bool bPrimaryKey = false;
bool bAutoIncrement = false;
string[] arSize;
// Get the column meta data
_command.Connection._sql.ColumnMetaData(
baseCatalogName,
baseTableName,
strColumn,
ref dataType, ref collSeq, ref bNotNull, ref bPrimaryKey, ref bAutoIncrement);
if (bNotNull || bPrimaryKey) row[SchemaTableColumn.AllowDBNull] = false;
bool allowDbNull = (bool)row[SchemaTableColumn.AllowDBNull];
row[SchemaTableColumn.IsKey] = bPrimaryKey && CountParents(parentToColumns) <= 1;
row[SchemaTableOptionalColumn.IsAutoIncrement] = bAutoIncrement;
row["CollationType"] = collSeq;
// For types like varchar(50) and such, extract the size
arSize = dataType.Split('(');
if (arSize.Length > 1)
{
dataType = arSize[0];
arSize = arSize[1].Split(')');
if (arSize.Length > 1)
{
arSize = arSize[0].Split(',', '.');
if (sqlType.Type == DbType.Binary || SQLiteConvert.IsStringDbType(sqlType.Type))
{
row[SchemaTableColumn.ColumnSize] = Convert.ToInt32(arSize[0], CultureInfo.InvariantCulture);
}
else
{
row[SchemaTableColumn.NumericPrecision] = Convert.ToInt32(arSize[0], CultureInfo.InvariantCulture);
if (arSize.Length > 1)
row[SchemaTableColumn.NumericScale] = Convert.ToInt32(arSize[1], CultureInfo.InvariantCulture);
}
}
}
if (wantDefaultValue)
{
// Determine the default value for the column, which sucks because we have to query the schema for each column
using (SQLiteCommand cmdTable = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA [{0}].TABLE_INFO([{1}])",
baseCatalogName,
baseTableName
), _command.Connection))
using (DbDataReader rdTable = cmdTable.ExecuteReader())
{
// Find the matching column
while (rdTable.Read())
{
if (String.Compare(baseColumnName, rdTable.GetString(1), StringComparison.OrdinalIgnoreCase) == 0)
{
if (rdTable.IsDBNull(4) == false)
row[SchemaTableOptionalColumn.DefaultValue] = rdTable[4];
break;
}
}
}
}
// Determine IsUnique properly, which is a pain in the butt!
if (wantUniqueInfo)
{
if (baseCatalogName != strCatalog
|| baseTableName != strTable)
{
strCatalog = baseCatalogName;
strTable = baseTableName;
tblIndexes = _command.Connection.GetSchema("Indexes", new string[] {
baseCatalogName,
null,
baseTableName,
null });
}
foreach (DataRow rowIndexes in tblIndexes.Rows)
{
tblIndexColumns = _command.Connection.GetSchema("IndexColumns", new string[] {
baseCatalogName,
null,
baseTableName,
(string)rowIndexes["INDEX_NAME"],
null
});
foreach (DataRow rowColumnIndex in tblIndexColumns.Rows)
{
if (String.Compare(SQLiteConvert.GetStringOrNull(rowColumnIndex["COLUMN_NAME"]), strColumn, StringComparison.OrdinalIgnoreCase) == 0)
{
//
// BUGFIX: Make sure that we only flag this column as "unique"
// if we are not processing of some kind of multi-table
// construct (i.e. a join) because in that case we must
// allow duplicate values (refer to ticket [7e3fa93744]).
//
if (parentToColumns.Count == 1 && tblIndexColumns.Rows.Count == 1 && allowDbNull == false)
row[SchemaTableColumn.IsUnique] = rowIndexes["UNIQUE"];
// If its an integer primary key and the only primary key in the table, then its a rowid alias and is autoincrement
// NOTE: Currently commented out because this is not always the desired behavior. For example, a 1:1 relationship with
// another table, where the other table is autoincrement, but this one is not, and uses the rowid from the other.
// It is safer to only set Autoincrement on tables where we're SURE the user specified AUTOINCREMENT, even if its a rowid column.
if (tblIndexColumns.Rows.Count == 1 && (bool)rowIndexes["PRIMARY_KEY"] == true && String.IsNullOrEmpty(dataType) == false &&
String.Compare(dataType, "integer", StringComparison.OrdinalIgnoreCase) == 0)
{
// row[SchemaTableOptionalColumn.IsAutoIncrement] = true;
}
break;
}
}
}
}
if (String.IsNullOrEmpty(dataType))
{
TypeAffinity affin = TypeAffinity.Uninitialized;
dataType = _activeStatement._sql.ColumnType(_activeStatement, n, ref affin);
}
if (String.IsNullOrEmpty(dataType) == false)
row["DataTypeName"] = dataType;
}
tbl.Rows.Add(row);
}
if (_keyInfo != null)
_keyInfo.AppendSchemaTable(tbl);
tbl.AcceptChanges();
tbl.EndLoadData();
return tbl;
}
/// <summary>
/// Retrieves the column as a string
/// </summary>
/// <param name="i">The index of the column.</param>
/// <returns>string</returns>
|
| | | | | | | | | | | | | | | | > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > > | | | | | | | | | | | | | > | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | < | | | | | | | | > | | | | | | | | | | | | | | | | | | | | | | | | | | | | < > | > | < | | | | | | > | | | | | | | | > | | | | | | | |
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 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 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 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 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 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 |
return result;
}
///////////////////////////////////////////////////////////////////////////
internal DataTable GetSchemaTable(bool wantUniqueInfo, bool wantDefaultValue)
{
CheckClosed();
if (_throwOnDisposed) SQLiteCommand.Check(_command);
//
// BUGFIX: We need to quickly scan all the fields in the current
// "result set" to see how many distinct tables are actually
// involved. This information is necessary so that some
// intelligent decisions can be made when constructing the
// metadata below. For example, we need to be very careful
// about flagging a particular column as "unique" just
// because it was in its original underlying database table
// if there are now multiple tables involved in the
// "result set". See ticket [7e3fa93744] for more detailed
// information.
//
Dictionary<ColumnParent, List<int>> parentToColumns = null;
Dictionary<int, ColumnParent> columnToParent = null;
SQLiteBase sql = _command.Connection._sql;
GetStatementColumnParents(
sql, _activeStatement, _fieldCount,
ref parentToColumns, ref columnToParent);
DataTable tbl = new DataTable("SchemaTable");
DataTable tblIndexes = null;
DataTable tblIndexColumns;
DataRow row;
string temp;
string strCatalog = String.Empty;
string strTable = String.Empty;
string strColumn = String.Empty;
tbl.Locale = CultureInfo.InvariantCulture;
tbl.Columns.Add(SchemaTableColumn.ColumnName, typeof(String));
tbl.Columns.Add(SchemaTableColumn.ColumnOrdinal, typeof(int));
tbl.Columns.Add(SchemaTableColumn.ColumnSize, typeof(int));
tbl.Columns.Add(SchemaTableColumn.NumericPrecision, typeof(int));
tbl.Columns.Add(SchemaTableColumn.NumericScale, typeof(int));
tbl.Columns.Add(SchemaTableColumn.IsUnique, typeof(Boolean));
tbl.Columns.Add(SchemaTableColumn.IsKey, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.BaseServerName, typeof(string));
tbl.Columns.Add(SchemaTableOptionalColumn.BaseCatalogName, typeof(String));
tbl.Columns.Add(SchemaTableColumn.BaseColumnName, typeof(String));
tbl.Columns.Add(SchemaTableColumn.BaseSchemaName, typeof(String));
tbl.Columns.Add(SchemaTableColumn.BaseTableName, typeof(String));
tbl.Columns.Add(SchemaTableColumn.DataType, typeof(Type));
tbl.Columns.Add(SchemaTableColumn.AllowDBNull, typeof(Boolean));
tbl.Columns.Add(SchemaTableColumn.ProviderType, typeof(int));
tbl.Columns.Add(SchemaTableColumn.IsAliased, typeof(Boolean));
tbl.Columns.Add(SchemaTableColumn.IsExpression, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.IsAutoIncrement, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.IsRowVersion, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.IsHidden, typeof(Boolean));
tbl.Columns.Add(SchemaTableColumn.IsLong, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.IsReadOnly, typeof(Boolean));
tbl.Columns.Add(SchemaTableOptionalColumn.ProviderSpecificDataType, typeof(Type));
tbl.Columns.Add(SchemaTableOptionalColumn.DefaultValue, typeof(object));
tbl.Columns.Add("DataTypeName", typeof(string));
tbl.Columns.Add("CollationType", typeof(string));
tbl.BeginLoadData();
for (int n = 0; n < _fieldCount; n++)
{
SQLiteType sqlType = GetSQLiteType(_flags, n);
row = tbl.NewRow();
DbType typ = sqlType.Type;
// Default settings for the column
row[SchemaTableColumn.ColumnName] = GetName(n);
row[SchemaTableColumn.ColumnOrdinal] = n;
row[SchemaTableColumn.ColumnSize] = SQLiteConvert.DbTypeToColumnSize(typ);
row[SchemaTableColumn.NumericPrecision] = SQLiteConvert.DbTypeToNumericPrecision(typ);
row[SchemaTableColumn.NumericScale] = SQLiteConvert.DbTypeToNumericScale(typ);
row[SchemaTableColumn.ProviderType] = sqlType.Type;
row[SchemaTableColumn.IsLong] = false;
row[SchemaTableColumn.AllowDBNull] = true;
row[SchemaTableOptionalColumn.IsReadOnly] = false;
row[SchemaTableOptionalColumn.IsRowVersion] = false;
row[SchemaTableColumn.IsUnique] = false;
row[SchemaTableColumn.IsKey] = false;
row[SchemaTableOptionalColumn.IsAutoIncrement] = false;
row[SchemaTableColumn.DataType] = GetFieldType(n);
row[SchemaTableOptionalColumn.IsHidden] = false;
row[SchemaTableColumn.BaseSchemaName] = _baseSchemaName;
strColumn = columnToParent[n].ColumnName;
if (String.IsNullOrEmpty(strColumn) == false) row[SchemaTableColumn.BaseColumnName] = strColumn;
row[SchemaTableColumn.IsExpression] = String.IsNullOrEmpty(strColumn);
row[SchemaTableColumn.IsAliased] = (String.Compare(GetName(n), strColumn, StringComparison.OrdinalIgnoreCase) != 0);
temp = columnToParent[n].TableName;
if (String.IsNullOrEmpty(temp) == false) row[SchemaTableColumn.BaseTableName] = temp;
temp = columnToParent[n].DatabaseName;
if (String.IsNullOrEmpty(temp) == false) row[SchemaTableOptionalColumn.BaseCatalogName] = temp;
string dataType = null;
// If we have a table-bound column, extract the extra information from it
if (String.IsNullOrEmpty(strColumn) == false)
{
string baseCatalogName = String.Empty;
if (row[SchemaTableOptionalColumn.BaseCatalogName] != DBNull.Value)
baseCatalogName = (string)row[SchemaTableOptionalColumn.BaseCatalogName];
string baseTableName = String.Empty;
if (row[SchemaTableColumn.BaseTableName] != DBNull.Value)
baseTableName = (string)row[SchemaTableColumn.BaseTableName];
if (sql.DoesTableExist(baseCatalogName, baseTableName))
{
string baseColumnName = String.Empty;
if (row[SchemaTableColumn.BaseColumnName] != DBNull.Value)
baseColumnName = (string)row[SchemaTableColumn.BaseColumnName];
string collSeq = null;
bool bNotNull = false;
bool bPrimaryKey = false;
bool bAutoIncrement = false;
string[] arSize;
// Get the column meta data
_command.Connection._sql.ColumnMetaData(
baseCatalogName,
baseTableName,
strColumn,
true,
ref dataType, ref collSeq, ref bNotNull, ref bPrimaryKey, ref bAutoIncrement);
if (bNotNull || bPrimaryKey) row[SchemaTableColumn.AllowDBNull] = false;
bool allowDbNull = (bool)row[SchemaTableColumn.AllowDBNull];
row[SchemaTableColumn.IsKey] = bPrimaryKey && CountParents(parentToColumns) <= 1;
row[SchemaTableOptionalColumn.IsAutoIncrement] = bAutoIncrement;
row["CollationType"] = collSeq;
// For types like varchar(50) and such, extract the size
arSize = dataType.Split('(');
if (arSize.Length > 1)
{
dataType = arSize[0];
arSize = arSize[1].Split(')');
if (arSize.Length > 1)
{
arSize = arSize[0].Split(',', '.');
if (sqlType.Type == DbType.Binary || SQLiteConvert.IsStringDbType(sqlType.Type))
{
row[SchemaTableColumn.ColumnSize] = Convert.ToInt32(arSize[0], CultureInfo.InvariantCulture);
}
else
{
row[SchemaTableColumn.NumericPrecision] = Convert.ToInt32(arSize[0], CultureInfo.InvariantCulture);
if (arSize.Length > 1)
row[SchemaTableColumn.NumericScale] = Convert.ToInt32(arSize[1], CultureInfo.InvariantCulture);
}
}
}
if (wantDefaultValue)
{
// Determine the default value for the column, which sucks because we have to query the schema for each column
using (SQLiteCommand cmdTable = new SQLiteCommand(HelperMethods.StringFormat(CultureInfo.InvariantCulture, "PRAGMA [{0}].TABLE_INFO([{1}])",
baseCatalogName,
baseTableName
), _command.Connection))
using (DbDataReader rdTable = cmdTable.ExecuteReader())
{
// Find the matching column
while (rdTable.Read())
{
if (String.Compare(baseColumnName, rdTable.GetString(1), StringComparison.OrdinalIgnoreCase) == 0)
{
if (rdTable.IsDBNull(4) == false)
row[SchemaTableOptionalColumn.DefaultValue] = rdTable[4];
break;
}
}
}
}
// Determine IsUnique properly, which is a pain in the butt!
if (wantUniqueInfo)
{
if (baseCatalogName != strCatalog || baseTableName != strTable)
{
strCatalog = baseCatalogName;
strTable = baseTableName;
tblIndexes = _command.Connection.GetSchema("Indexes", new string[] {
baseCatalogName,
null,
baseTableName,
null
});
}
foreach (DataRow rowIndexes in tblIndexes.Rows)
{
tblIndexColumns = _command.Connection.GetSchema("IndexColumns", new string[] {
baseCatalogName,
null,
baseTableName,
(string)rowIndexes["INDEX_NAME"],
null
});
foreach (DataRow rowColumnIndex in tblIndexColumns.Rows)
{
if (String.Compare(SQLiteConvert.GetStringOrNull(rowColumnIndex["COLUMN_NAME"]), strColumn, StringComparison.OrdinalIgnoreCase) == 0)
{
//
// BUGFIX: Make sure that we only flag this column as "unique"
// if we are not processing of some kind of multi-table
// construct (i.e. a join) because in that case we must
// allow duplicate values (refer to ticket [7e3fa93744]).
//
if (parentToColumns.Count == 1 && tblIndexColumns.Rows.Count == 1 && allowDbNull == false)
row[SchemaTableColumn.IsUnique] = rowIndexes["UNIQUE"];
// If its an integer primary key and the only primary key in the table, then its a rowid alias and is autoincrement
// NOTE: Currently commented out because this is not always the desired behavior. For example, a 1:1 relationship with
// another table, where the other table is autoincrement, but this one is not, and uses the rowid from the other.
// It is safer to only set Autoincrement on tables where we're SURE the user specified AUTOINCREMENT, even if its a rowid column.
//if (tblIndexColumns.Rows.Count == 1 && (bool)rowIndexes["PRIMARY_KEY"] == true && String.IsNullOrEmpty(dataType) == false &&
// String.Compare(dataType, "integer", StringComparison.OrdinalIgnoreCase) == 0)
//{
// // row[SchemaTableOptionalColumn.IsAutoIncrement] = true;
//}
break;
}
}
}
}
}
if (String.IsNullOrEmpty(dataType))
{
TypeAffinity affin = TypeAffinity.Uninitialized;
dataType = _activeStatement._sql.ColumnType(_activeStatement, n, ref affin);
}
if (String.IsNullOrEmpty(dataType) == false)
row["DataTypeName"] = dataType;
}
tbl.Rows.Add(row);
}
if (_keyInfo != null)
_keyInfo.AppendSchemaTable(tbl);
tbl.AcceptChanges();
tbl.EndLoadData();
return tbl;
}
/// <summary>
/// Retrieves the column as a string
/// </summary>
/// <param name="i">The index of the column.</param>
/// <returns>string</returns>
|