public void DeriveParameters()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
testSubject.CommandText = "call 1 + cast(? as integer)";
testSubject.CommandType = global::System.Data.CommandType.StoredProcedure;
testSubject.DeriveParameters();
HsqlParameterCollection parameters = testSubject.Parameters;
Assert.AreEqual(1, parameters.Count);
HsqlParameter parameter = parameters[0];
Assert.AreEqual(DbType.Int32, parameter.DbType);
Assert.AreEqual(ParameterDirection.Input, parameter.Direction);
Assert.AreEqual(false, parameter.IsNullable);
Assert.AreEqual(0, parameter.Offset);
Assert.AreEqual("@p1", parameter.ParameterName);
Assert.AreEqual(10, parameter.Precision);
Assert.AreEqual(HsqlProviderType.Integer, parameter.ProviderType);
Assert.AreEqual(0, parameter.Scale);
Assert.AreEqual(4, parameter.Size);
Assert.AreEqual("", parameter.SourceColumn);
Assert.AreEqual(false, parameter.SourceColumnNullMapping);
Assert.AreEqual(DataRowVersion.Default, parameter.SourceVersion);
Assert.AreEqual("NULL", parameter.ToSqlLiteral());
Assert.AreEqual(null, parameter.Value);
}
}
public virtual void BeginTransaction()
{
using (HsqlConnection testSubject = new HsqlConnection())
{
testSubject.Open();
using (HsqlTransaction transaction = testSubject.BeginTransaction())
{
}
}
object[] expected = new object[]
{
IsolationLevel.Chaos, false,
IsolationLevel.ReadCommitted, true,
IsolationLevel.ReadUncommitted, true,
IsolationLevel.RepeatableRead, true,
IsolationLevel.Serializable, true,
IsolationLevel.Snapshot, true,
IsolationLevel.Unspecified, true
};
IsolationLevel isolationLevel;
bool isolationLevelIsSupported;
for (int i = 0; i < expected.Length; i += 2)
{
isolationLevel = (IsolationLevel)expected[i];
isolationLevelIsSupported = (bool)expected[i + 1];
TestBeginTransaction(isolationLevel, isolationLevelIsSupported);
}
}
public void CommandType()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
CommandType actual = testSubject.CommandType;
Assert.AreEqual(System.Data.CommandType.Text, actual);
testSubject.CommandType = System.Data.CommandType.StoredProcedure;
Assert.AreEqual(System.Data.CommandType.StoredProcedure, actual);
testSubject.CommandType = System.Data.CommandType.TableDirect;
Assert.AreEqual(System.Data.CommandType.TableDirect, actual);
Common.HsqlWarningEventArgs warning = null;
testSubject.Warning += delegate(object sender, Common.HsqlWarningEventArgs args) {
warning = args;
};
testSubject.CommandType = (CommandType)100;
Assert.IsNotNull(warning);
Assert.AreEqual(System.Data.CommandType.Text, actual);
}
}
/// <summary>
/// Fills the table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 4);
string catalogName = restrictions[0];
string schemaNamePattern = restrictions[1];
string tableNamePattern = restrictions[2];
string constraintNamePattern = restrictions[3];
if (WantsIsNull(schemaNamePattern) ||
WantsIsNull(tableNamePattern) ||
WantsIsNull(constraintNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("TABLE_CATALOG", "=", catalogName))
.Append(And("TABLE_SCHEMA", "LIKE", schemaNamePattern))
.Append(And("TABLE_NAME", "LIKE", tableNamePattern))
.Append(And("CONSTRAINT_NAME", "LIKE", constraintNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string constraintCatalog = (string)values[0];
string constraintSchema = (string)values[1];
string constraintName = (string)values[2];
string constraintType = (string)values[3];
string tableCatalog = (string)values[4];
string tableSchema = (string)values[5];
string tableName = (string)values[6];
string isDeferrable = (string)values[7];
string initiallyDeferred = (string)values[8];
string checkClause = (string)values[9];
AddRow(
table,
constraintCatalog,
constraintSchema,
constraintName,
constraintType,
tableCatalog,
tableSchema,
tableName,
isDeferrable,
initiallyDeferred,
checkClause
);
}
}
}
public virtual void ChangeDatabase()
{
using (HsqlConnection testSubject = new HsqlConnection("DataSource=mem:test2"))
{
string databaseName = "test1";
testSubject.ChangeDatabase(databaseName);
testSubject.Open();
}
using (HsqlConnection testSubject = new HsqlConnection("DataSource=mem:test2"))
{
testSubject.Open();
string databaseName = "test1";
try
{
testSubject.ChangeDatabase(databaseName);
Assert.Fail("it is not expected that it is legal to change database while a connection is open.");
}
catch (Exception)
{
}
}
}
/// <summary>
/// Fills the table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
AddRow(table, CN.MetaDataCollections, 0, 0);
AddRow(table, CN.DataSourceInformation, 0, 0);
AddRow(table, CN.DataTypes, 0, 0);
AddRow(table, CN.Restrictions, 0, 0);
AddRow(table, CN.ReservedWords, 0, 0);
AddRow(table, HCN.Users, 1, 1);
AddRow(table, HCN.Databases, 1, 1);
AddRow(table, HCN.Tables, 4, 3);
AddRow(table, HCN.Columns, 4, 4);
AddRow(table, HCN.Views, 3, 3);
AddRow(table, HCN.ViewColumns, 4, 4);
AddRow(table, HCN.ProcedureParameters, 4, 4);
AddRow(table, HCN.Procedures, 4, 3);
AddRow(table, HCN.PrimaryKeyColumns, 5, 5);
AddRow(table, HCN.PrimaryKeys, 4, 4);
AddRow(table, HCN.ForeignKeyColumns, 5, 5);
AddRow(table, HCN.ForeignKeys, 4, 4);
AddRow(table, HCN.TableCheckConstraints, 4, 4);
AddRow(table, HCN.TableTriggers, 4, 4);
AddRow(table, HCN.IndexColumns, 5, 5);
AddRow(table, HCN.Indexes, 6, 4);
AddRow(table, HCN.Sequences, 3, 3);
AddRow(table, HCN.UserDefinedTypes, 2, 1);
AddRow(table, HCN.UniqueConstraints, 4, 4);
AddRow(table, HCN.UniqueConstraintColumns, 5, 5);
AddRow(table, HCN.Schemas, 3, 2);
}
/// <summary>
/// Fills a <c>Databases</c> metadata collection table using the
/// given connection and restrictions.
/// </summary>
/// <param name="connection">The connection from which to fill the table.</param>
/// <param name="table">The table to fill.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
string databaseNamePattern = GetRestrictions(restrictions, 1)[0];
if (WantsIsNull(databaseNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query.Append(And("value", "LIKE", databaseNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string databaseName = (string)values[0];
AddRow(table, databaseName, null, null);
}
}
}
public void Cancel()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
testSubject.Cancel(); // no-op.
}
}
/// <summary>
/// Fills the tables table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 4);
string catalogName = restrictions[0];
string schemaPattern = restrictions[1];
string tablePattern = restrictions[2];
string typePattern = restrictions[3];
if (schemaPattern != null && schemaPattern.StartsWith("\"") && schemaPattern.EndsWith("\""))
{
schemaPattern = schemaPattern.Substring(1, schemaPattern.Length - 2);
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("TABLE_CAT", "=", catalogName))
.Append(And("TABLE_SCHEM", "LIKE", schemaPattern))
.Append(And("TABLE_NAME", "LIKE", tablePattern));
if (typePattern != null)
{
string[] types = typePattern.Split(Comma);
if (types.Length == 1)
{
query.Append(And("TABLE_TYPE", "LIKE", types[0]));
}
else
{
query.Append(And("TABLE_TYPE", "IN", ToSQLInList(types)));
}
}
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string tableCatalog = (string)values[0];
string tableSchema = (string)values[1];
string tableName = (string)values[2];
string tableType = (string)values[3];
AddRow(
table,
tableCatalog,
tableSchema,
tableName,
tableType);
}
}
}
/// <summary>
/// Fills the procedures table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 4);
string catalogName = restrictions[0];
string specificSchemaPattern = restrictions[1];
string specificNamePattern = restrictions[2];
string typePattern = restrictions[3];
if (WantsIsNull(specificSchemaPattern) ||
WantsIsNull(specificNamePattern) ||
WantsIsNull(typePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("SPECIFIC_CATALOG", "=", catalogName))
.Append(And("SPECIFIC_SCHEMA", "LIKE", specificSchemaPattern))
.Append(And("SPECIFIC_NAME", "LIKE", specificNamePattern))
.Append(And("ROUTINE_TYPE", "LIKE", typePattern))
.Append(" ORDER BY 1, 2, 3");
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string specificCatalog = (string)values[0];
string specificSchema = (string)values[1];
string specifcName = (string)values[2];
string routineCatalog = (string)values[3];
string routineSchema = (string)values[4];
string routineName = (string)values[5];
string routineType = (string)values[6];
DateTime?created = (DateTime?)values[7];
DateTime?lastAltered = (DateTime?)values[8];
AddRow(
table,
specificCatalog,
specificSchema,
specifcName,
routineCatalog,
routineSchema,
routineName,
routineType,
created,
lastAltered);
}
}
}
/// <summary>
/// Constructs a new <c>HsqlDatabaseMetaData</c> instance
/// for the given connection.
/// </summary>
/// <param name="connection">The connection.</param>
public HsqlDatabaseMetaData(HsqlConnection connection)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
m_connection = connection;
}
public void AddBatch()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
testSubject.AddBatch();
//testSubject.C
}
}
/// <summary>
/// Returns the collection of currently valid initial schema names,
/// given the specified context.
/// </summary>
/// <param name="context">
/// An <see cref="ITypeDescriptorContext"></see> whose <c>Instance</c>
/// property supplies the <c>HsqlConnectionStringBuilder</c> use to
/// connect to a data source to retrieve the currently valid initial
/// schema names.
/// </param>
/// <returns>
/// A <see cref="TypeConverter.StandardValuesCollection"/> that holds
/// collection of currently valid initial schema names.
/// </returns>
public override TypeConverter.StandardValuesCollection GetStandardValues(
ITypeDescriptorContext context)
{
if (!IsStandardValuesSupported(context))
{
return(null);
}
List <string> values = new List <string>();
try
{
HsqlConnectionStringBuilder builder
= (HsqlConnectionStringBuilder)context.Instance;
// TODO: this is sub-optimal, but is currently the best (only?)
// solution to the problem of how to avoid creating and/or
// leaving open embedded database instances.
if (IsEmbeddedProtocol(builder))
{
builder = new HsqlConnectionStringBuilder(
builder.ConnectionString);
builder.AutoShutdown = true;
builder.IfExists = true;
}
using (HsqlConnection connection = new HsqlConnection())
{
connection.ConnectionString = builder.ConnectionString;
using (HsqlCommand command = new HsqlCommand(
connection,
SchemaQuery))
{
connection.Open();
using (HsqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
values.Add(reader.GetString(0));
}
}
}
}
}
catch (Exception exception)
{
#if DEBUG
Debug.WriteLine(exception);
#endif
}
return(new TypeConverter.StandardValuesCollection(values));
}
public void CreateParameter()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
HsqlParameter parameter = testSubject.CreateParameter();
IDbParmeter idbparameter = (testSubject as IDbCommand).CreateParameter();
DbParameter dbparameter = (testSubject as DbCommand).CreateParameter();
}
}
/// <summary>
/// Fills the table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 3);
string catalogName = restrictions[0];
string schemaNamePattern = restrictions[1];
string sequenceNamePattern = restrictions[2];
if (WantsIsNull(schemaNamePattern) ||
WantsIsNull(sequenceNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("SEQUENCE_CATALOG", "=", catalogName))
.Append(And("SEQUENCE_SCHEMA", "LIKE", schemaNamePattern))
.Append(And("SEQUENCE_NAME", "LIKE", sequenceNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string sequenceCatalog = (string)values[0];
string sequenceSchema = (string)values[1];
string sequenceName = (string)values[2];
string dtdIdentifier = (string)values[3];
string maximumValue = (string)values[4];
string minimumValue = (string)values[5];
string increment = (string)values[6];
string cycleOption = (string)values[7];
string startWith = (string)values[8];
AddRow(
table,
sequenceCatalog,
sequenceSchema,
sequenceName,
dtdIdentifier,
maximumValue,
minimumValue,
increment,
cycleOption,
startWith
);
}
}
}
/// <summary>
/// Fills a <c>DataTypes</c> metadata collection table
/// using the given connection and restrictions.
/// </summary>
/// <param name="connection">The connection from which to fill the table.</param>
/// <param name="table">The table to file.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
using (HsqlDataReader reader = Execute(connection, sql))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string providerTypeName = (string)values[0];
int providerDbType = (int)values[1];
int? columnSize = (int?)values[2];
string literalPrefix = (string)values[3];
string literalSuffix = (string)values[4];
string createParameters = (string)values[5];
short nullability = (short)(int)values[6];
bool? isCaseSensitive = (bool?)values[7];
short searchability = (short)(int)values[8];
bool? isUnsigned = (bool?)values[9];
bool? isFixedPrecisionScale = (bool?)values[10];
bool? isAutoIncrementable = (bool?)values[11];
//string localTypeName = (string) values[12];
short?minimumScale = (short?)(int?)values[13];
short?maximumScale = (short?)(int?)values[14];
//int? sqlDataType = (int?) values[15];
//int? dateTimeSub = (int?)values[16];
//int? numPrecRadix = (int?)values[17];
//int? typeSub = (int?)values[18];
string createFormat = ToCreateFormat(providerTypeName, createParameters);
string dataType = Convert.ToString(HsqlConvert.ToDataType(providerDbType));
bool isBestMatch = IsBestMatchProviderTypeName(providerTypeName);
bool isFixedLength = IsFixedLength(providerDbType);
bool isLong = IsLongProviderType(providerDbType);
bool isNullable = IsNullable(nullability);
bool isSearchable = IsSearchable(searchability);
bool isSearchableWithLike = IsSearchableWithLike(searchability);
bool isConcurrencyType = false;
bool isLiteralSupported = true;
AddRow(table,
providerTypeName, providerDbType,
columnSize,
createFormat, createParameters,
dataType, isAutoIncrementable, isBestMatch,
isCaseSensitive, isFixedLength, isFixedPrecisionScale, isLong,
isNullable, isSearchable, isSearchableWithLike, isUnsigned,
maximumScale, minimumScale, isConcurrencyType,
isLiteralSupported, literalPrefix, literalSuffix);
}
}
}
public virtual void Clone()
{
string connectionString = "DataSource=mem:test";
using (HsqlConnection testSubject = new HsqlConnection(connectionString))
{
HsqlConnection copy = testSubject.Clone();
Assert.AreEqual(connectionString, testSubject.ConnectionString);
}
}
public virtual void ExecuteNonQuery()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
testSubject.CommandText = ";";
int expected = 0;
int actual = testSubject.ExecuteNonQuery();
Assert.AreEqual(expected, actual);
}
}
public void CommandTimeout()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
int actual = testSubject.CommandTimeout;
Assert.AreEqual(30, actual);
}
}
/// <summary>
/// Fills the view columns table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 4);
string catalogName = restrictions[0];
string viewSchemaPattern = restrictions[1];
string viewNamePattern = restrictions[2];
string columnNamePattern = restrictions[3];
if (WantsIsNull(viewSchemaPattern) ||
WantsIsNull(viewNamePattern) ||
WantsIsNull(columnNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("VIEW_CATALOG", "=", catalogName))
.Append(And("VIEW_SCHEMA", "LIKE", viewSchemaPattern))
.Append(And("VIEW_NAME", "LIKE", viewNamePattern))
.Append(And("COLUMN_NAME", "LIKE", columnNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string viewCatalog = (string)values[0];
string viewSchema = (string)values[1];
string viewName = (string)values[2];
string tableCatalog = (string)values[3];
string tableSchema = (string)values[4];
string tableName = (string)values[5];
string columnName = (string)values[6];
AddRow(
table,
viewCatalog,
viewSchema,
viewName,
tableCatalog,
tableSchema,
tableName,
columnName);
}
}
}
public virtual void ExecuteScalar()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
testSubject.CommandText = "call database();";
object expected = "mem:test";
object actual = testSubject.ExecuteScalar();
Assert.AreEqual(expected, actual);
}
}
public virtual void UpdatedRowSource()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
UpdateRowSource expected = UpdateRowSource.Both;
UpdateRowSource actual = testSubject.UpdatedRowSource;
Assert.AreEqual(expected, actual);
Assert.Fail("TODO");
}
}
public virtual void Prepare()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
testSubject.CommandText = "select * from information_schema.system_tables";
testSubject.Prepare();
bool expected = true;
bool actual = testSubject.IsPrepared;
Assert.AreEqual(expected, actual);
}
}
/// <summary>
/// Produces a schema table filled with data rows
/// satisfying the given <c>restrictions</c>.
/// </summary>
/// <remarks>
/// By default, invokes <see cref="CreateTable()"/> to create
/// a table with the expected column collection, invokes
/// <see cref="FillTable(HsqlConnection,DataTable,string[])"/>
/// to populate the table's row collection, and then sets
/// every data column in the table's column collection to
/// <see cref="DataColumn.ReadOnly"/>.
/// </remarks>
/// <param name="connection">The connection.</param>
/// <param name="restrictions">The restrictions.</param>
/// <returns>
/// A schema table filled with data rows satisfying
/// the given <c>restrictions</c>.
/// </returns>
public virtual DataTable GetSchema(HsqlConnection connection,
string[] restrictions)
{
DataTable table = CreateTable();
FillTable(connection, table, restrictions);
foreach (DataColumn column in table.Columns)
{
column.ReadOnly = true;
}
return(table);
}
public virtual void StatementCompleted()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
testSubject.StatementCompleted += delegate(object sender, StatementCompletedEventArgs e)
{
Assert.AreNotSame(testSubject, sender);
Assert.AreEqual(0, e.RecordCount);
};
//
Assert.Fail("TODO");
}
}
public virtual void CreateCommand()
{
using (HsqlConnection testSubject = new HsqlConnection())
{
HsqlCommand command = testSubject.CreateCommand();
Assert.AreSame(testSubject, command.Connection);
Assert.AreEqual(string.Empty, command.CommandText);
Assert.AreEqual(CommandType.Text, command.CommandType);
Assert.AreEqual(true, command.DesignTimeVisible);
Assert.AreEqual(false, command.IsPrepared);
Assert.AreEqual(UpdateRowSource.Both, command.UpdatedRowSource);
Assert.AreEqual(null, command.Transaction);
}
}
static HsqlConnection NewConnection()
{
HsqlConnectionStringBuilder builder = new HsqlConnectionStringBuilder();
builder.Protocol = ConnectionProtocol.Mem;
builder.Path = "test";
builder.UserId = "SA";
builder.Password = "";
HsqlConnection connection = new HsqlConnection(builder.ToString());
connection.Open();
return(connection);
}
/// <summary>
/// Executes an SQL SELECT of the form
/// <c>SELECT * FROM <table> [WHERE <where*gt;]</c>.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table name.</param>
/// <param name="where">The where clause.</param>
/// <returns>A new data reader holding the result.</returns>
protected HsqlDataReader ExecuteSelect(
HsqlConnection connection,
string table,
string where)
{
StringBuilder select = new StringBuilder("SELECT * FROM ");
select.Append(table);
if (!string.IsNullOrEmpty(where))
{
select.Append(" WHERE ").Append(where);
}
return(Execute(connection, select.ToString()));
}
public void CommandText()
{
using (HsqlConnection connection = NewConnection())
using (HsqlCommand testSubject = connection.CreateCommand())
{
Assert.AreEqual(string.Empty, testSubject.CommandText);
testSubject.CommandText = "select * from information_schema.system_tables";
Assert.AreEqual("select * from information_schema.system_tables", testSubject.CommandText);
testSubject.CommandText = null;
Assert.AreEqual(string.Empty, testSubject.CommandText);
}
}
/// <summary>
/// Translates the given schema name value.
/// </summary>
/// <remarks>
///
/// </remarks>
/// <param name="connection">The connection.</param>
/// <param name="schemaName">Name of the schema.</param>
/// <returns></returns>
protected string TranslateSchema(HsqlConnection connection,
string schemaName)
{
if (connection.Settings.DefaultSchemaQualification &&
(WantsIsNull(schemaName)))
{
string defaultSchema = connection.Session.ExecuteScalarDirect(
DefaultSchemaQuery) as string;
return((string.IsNullOrEmpty(defaultSchema))
? schemaName : defaultSchema);
}
else
{
return(schemaName);
}
}
/// <summary>
/// Fills a Users metadata collection table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 1);
string userNamePattern = restrictions[0];
if(WantsIsNull(userNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query.Append(And("SA.AUTHORIZATION_NAME", "LIKE", userNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
short? uid = (short?) values[0];
string userName = (string) values[1];
DateTime? createDate = (DateTime?) values[2];
DateTime? updateDate = (DateTime?) values[3];
string authorizationType = (string)values[4];
bool? isAdmin = (bool?)values[5];
AddRow(
table,
uid,
userName,
createDate,
updateDate,
authorizationType,
isAdmin);
}
}
}
/// <summary>
/// Fills the table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
AddRow(table, CN.MetaDataCollections, 0, 0);
AddRow(table, CN.DataSourceInformation, 0, 0);
AddRow(table, CN.DataTypes, 0, 0);
AddRow(table, CN.Restrictions, 0, 0);
AddRow(table, CN.ReservedWords, 0, 0);
AddRow(table, HCN.Users, 1, 1);
AddRow(table, HCN.Databases, 1, 1);
AddRow(table, HCN.Tables, 4, 3);
AddRow(table, HCN.Columns, 4, 4);
AddRow(table, HCN.Views, 3, 3);
AddRow(table, HCN.ViewColumns, 4, 4);
AddRow(table, HCN.ProcedureParameters, 4, 4);
AddRow(table, HCN.Procedures, 4, 3);
AddRow(table, HCN.PrimaryKeyColumns, 5, 5);
AddRow(table, HCN.PrimaryKeys, 4, 4);
AddRow(table, HCN.ForeignKeyColumns, 5, 5);
AddRow(table, HCN.ForeignKeys, 4, 4);
AddRow(table, HCN.TableCheckConstraints, 4, 4);
AddRow(table, HCN.TableTriggers, 4, 4);
AddRow(table, HCN.IndexColumns, 5, 5);
AddRow(table, HCN.Indexes, 6, 4);
AddRow(table, HCN.Sequences, 3, 3);
AddRow(table, HCN.UserDefinedTypes, 2, 1);
AddRow(table, HCN.UniqueConstraints, 4, 4);
AddRow(table, HCN.UniqueConstraintColumns, 5, 5);
AddRow(table, HCN.Schemas, 3, 2);
}
/// <summary>
/// Initializes a new instance of the
/// <see cref="HsqlEnlistment"/> class.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="transaction">The transaction.</param>
public HsqlEnlistment(HsqlConnection connection, Transaction transaction)
{
m_dbConnection = connection;
m_systemTransaction = transaction;
}
/// <summary>
/// Fills the view columns table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 4);
string catalogName = restrictions[0];
string viewSchemaPattern = restrictions[1];
string viewNamePattern = restrictions[2];
string columnNamePattern = restrictions[3];
if(WantsIsNull(viewSchemaPattern)
||WantsIsNull(viewNamePattern)
||WantsIsNull(columnNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("VIEW_CATALOG", "=", catalogName))
.Append(And("VIEW_SCHEMA", "LIKE", viewSchemaPattern))
.Append(And("VIEW_NAME", "LIKE", viewNamePattern))
.Append(And("COLUMN_NAME", "LIKE", columnNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string viewCatalog = (string) values[0];
string viewSchema = (string) values[1];
string viewName = (string)values[2];
string tableCatalog = (string)values[3];
string tableSchema = (string)values[4];
string tableName = (string)values[5];
string columnName = (string)values[6];
AddRow(
table,
viewCatalog,
viewSchema,
viewName,
tableCatalog,
tableSchema,
tableName,
columnName);
}
}
}
/// <summary>
/// Fills the views table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 3);
string catalogName = restrictions[0];
string schemaNamePattern = restrictions[1];
string tableNamePattern = restrictions[2];
if (WantsIsNull(schemaNamePattern)
|| WantsIsNull(tableNamePattern))
{
return;
}
StringBuilder query = ToQueryPrefix("INFORMATION_SCHEMA.SYSTEM_VIEWS");
query
//.Append(And("TABLE_CATALOG", "=", catalogName))
.Append(And("TABLE_SCHEMA", "LIKE", schemaNamePattern))
.Append(And("TABLE_NAME", "LIKE", tableNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
AddRow(
table,
(string)values[0],
(string)values[1],
(string)values[2],
(string)values[3],
(string)values[4],
(string)values[5]);
}
}
}
/// <summary>
/// Fills the table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 3);
string catalogName = restrictions[0];
string schemaNamePattern = restrictions[1];
string sequenceNamePattern = restrictions[2];
if (WantsIsNull(schemaNamePattern)
|| WantsIsNull(sequenceNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("SEQUENCE_CATALOG", "=", catalogName))
.Append(And("SEQUENCE_SCHEMA", "LIKE", schemaNamePattern))
.Append(And("SEQUENCE_NAME", "LIKE", sequenceNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string sequenceCatalog = (string)values[0];
string sequenceSchema = (string)values[1];
string sequenceName = (string)values[2];
string dtdIdentifier = (string)values[3];
string maximumValue = (string)values[4];
string minimumValue = (string)values[5];
string increment = (string)values[6];
string cycleOption = (string)values[7];
string startWith = (string)values[8];
AddRow(
table,
sequenceCatalog,
sequenceSchema,
sequenceName,
dtdIdentifier,
maximumValue,
minimumValue,
increment,
cycleOption,
startWith
);
}
}
}
/// <summary>
/// Fills a <c>Databases</c> metadata collection table using the
/// given connection and restrictions.
/// </summary>
/// <param name="connection">The connection from which to fill the table.</param>
/// <param name="table">The table to fill.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
string databaseNamePattern = GetRestrictions(restrictions, 1)[0];
if (WantsIsNull(databaseNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query.Append(And("value", "LIKE", databaseNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string databaseName = (string)values[0];
AddRow(table, databaseName, null, null);
}
}
}
/// <summary>
/// Fills the given metadata collection table using
/// the given connection and restrictions.
/// </summary>
/// <remarks>
/// It is expected that the <c>table</c> was obtained by
/// invoking <see cref="CreateTable"/>. Otherwise, it is
/// the responsibility of the caller to correctly populate
/// the table's column collection before passing the table
/// to this method.
/// </remarks>
/// <param name="connection">
/// The connection from which to fill the table.
/// </param>
/// <param name="table">
/// The table to fill.
/// </param>
/// <param name="restrictions">
/// The restrictions used to fill the table.
/// </param>
public abstract void FillTable(
HsqlConnection connection,
DataTable table,
string[] restrictions);
/// <summary>
/// Produces a schema table filled with data rows
/// satisfying the given <c>restrictions</c>.
/// </summary>
/// <remarks>
/// By default, invokes <see cref="CreateTable()"/> to create
/// a table with the expected column collection, invokes
/// <see cref="FillTable(HsqlConnection,DataTable,string[])"/>
/// to populate the table's row collection, and then sets
/// every data column in the table's column collection to
/// <see cref="DataColumn.ReadOnly"/>.
/// </remarks>
/// <param name="connection">The connection.</param>
/// <param name="restrictions">The restrictions.</param>
/// <returns>
/// A schema table filled with data rows satisfying
/// the given <c>restrictions</c>.
/// </returns>
public virtual DataTable GetSchema(HsqlConnection connection,
string[] restrictions)
{
DataTable table = CreateTable();
FillTable(connection, table, restrictions);
foreach (DataColumn column in table.Columns)
{
column.ReadOnly = true;
}
return table;
}
/// <summary>
/// Gets the schema.
/// </summary>
/// <param name="connection">Representing data source</param>
/// <param name="restrictions">
/// The restrictions; typically ignored.
/// </param>
/// <returns>
/// A cached version of the underlying metadata collection.
/// The underlying collection is lazily loaded and cached
/// for reuse for the lifetime of this object.
/// </returns>
public override DataTable GetSchema(
HsqlConnection connection,
string[] restrictions)
{
if (m_table == null)
{
m_table = CreateTable();
FillTable(connection, m_table, restrictions);
foreach (DataColumn column in m_table.Columns)
{
column.ReadOnly = true;
}
}
return m_table;
}
/// <summary>
/// Fills the procedure parameters table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 4);
string catalogName = restrictions[0];
string specificSchemaPattern = restrictions[1];
string specificNamePattern = restrictions[2];
string columnNamePattern = restrictions[3];
if (WantsIsNull(specificSchemaPattern)
|| WantsIsNull(specificNamePattern)
|| WantsIsNull(columnNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("SPECIFIC_CATALOG", "=", catalogName))
.Append(And("SPECIFIC_SCHEMA", "LIKE", specificSchemaPattern))
.Append(And("SPECIFIC_NAME", "LIKE", specificNamePattern))
.Append(And("COLUMN_NAME", "LIKE", columnNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string specificCatalog = (string) values[0];
string specificSchema = (string)values[1];
string specificName = (string)values[2];
int ordinalPosition = (int)values[3];
string parameterMode = (string)values[4];
string isResult = (string)values[5];
string asLocator = (string)values[6];
string parameterName = (string)values[7];
string dataType = (string)values[8];
int? characterMaximumLength = (int?)values[9];
int? characterOctetLength = (int?)values[10];
string collationCatalog = (string)values[11];
string collationSchema = (string)values[12];
string collationName = (string)values[13];
string characterSetCatalog = (string)values[14];
string characterSetSchema = (string)values[15];
string characterSetName = (string)values[16];
int? numericPrecision = (int?)values[17];
short? numericPrecisionRadix = (short?)(int?) values[18];
int? numericScale = (int?)values[19];
short? datetimePrecision = (short?)(int?)values[20];
string intervalType = (string)values[21];
short? intervalPrecision = (short?)(int?)values[22];
string parameterDirection = (string) values[23];
AddRow(
table,
specificCatalog,
specificSchema,
specificName,
ordinalPosition,
parameterMode,
isResult,
asLocator,
parameterName,
dataType,
characterMaximumLength,
characterOctetLength,
collationCatalog,
collationSchema,
collationName,
characterSetCatalog,
characterSetSchema,
characterSetName,
numericPrecision,
numericPrecisionRadix,
numericScale,
datetimePrecision,
intervalType,
intervalPrecision,
parameterDirection);
}
}
}
/// <summary>
/// Fills a <c>DataTypes</c> metadata collection table
/// using the given connection and restrictions.
/// </summary>
/// <param name="connection">The connection from which to fill the table.</param>
/// <param name="table">The table to file.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
using (HsqlDataReader reader = Execute(connection, sql))
{
object[] values = new object[reader.FieldCount];
while(reader.Read())
{
reader.GetValues(values);
string providerTypeName = (string) values[0];
int providerDbType = (int) values[1];
int? columnSize = (int?) values[2];
string literalPrefix = (string) values[3];
string literalSuffix = (string)values[4];
string createParameters = (string)values[5];
short nullability = (short)(int)values[6];
bool? isCaseSensitive = (bool?)values[7];
short searchability = (short)(int)values[8];
bool? isUnsigned = (bool?)values[9];
bool? isFixedPrecisionScale = (bool?)values[10];
bool? isAutoIncrementable = (bool?)values[11];
//string localTypeName = (string) values[12];
short? minimumScale = (short?)(int?)values[13];
short? maximumScale = (short?)(int?)values[14];
//int? sqlDataType = (int?) values[15];
//int? dateTimeSub = (int?)values[16];
//int? numPrecRadix = (int?)values[17];
//int? typeSub = (int?)values[18];
string createFormat = ToCreateFormat(providerTypeName, createParameters);
string dataType = Convert.ToString(HsqlConvert.ToDataType(providerDbType));
bool isBestMatch = IsBestMatchProviderTypeName(providerTypeName);
bool isFixedLength = IsFixedLength(providerDbType);
bool isLong = IsLongProviderType(providerDbType);
bool isNullable = IsNullable(nullability);
bool isSearchable = IsSearchable(searchability);
bool isSearchableWithLike = IsSearchableWithLike(searchability);
bool isConcurrencyType = false;
bool isLiteralSupported = true;
AddRow(table,
providerTypeName, providerDbType,
columnSize,
createFormat, createParameters,
dataType, isAutoIncrementable, isBestMatch,
isCaseSensitive, isFixedLength, isFixedPrecisionScale, isLong,
isNullable, isSearchable, isSearchableWithLike, isUnsigned,
maximumScale, minimumScale, isConcurrencyType,
isLiteralSupported, literalPrefix, literalSuffix);
}
}
}
static HsqlConnection NewConnection()
{
HsqlConnectionStringBuilder builder = new HsqlConnectionStringBuilder();
builder.Protocol = ConnectionProtocol.Mem;
builder.Path = "test";
builder.UserId = "SA";
builder.Password = "";
HsqlConnection connection = new HsqlConnection(builder.ToString());
connection.Open();
return connection;
}
/// <summary>
/// Fills the procedures table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 4);
string catalogName = restrictions[0];
string specificSchemaPattern = restrictions[1];
string specificNamePattern = restrictions[2];
string typePattern = restrictions[3];
if (WantsIsNull(specificSchemaPattern)
||WantsIsNull(specificNamePattern)
||WantsIsNull(typePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("SPECIFIC_CATALOG", "=", catalogName))
.Append(And("SPECIFIC_SCHEMA", "LIKE", specificSchemaPattern))
.Append(And("SPECIFIC_NAME", "LIKE", specificNamePattern))
.Append(And("ROUTINE_TYPE", "LIKE", typePattern))
.Append(" ORDER BY 1, 2, 3");
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string specificCatalog = (string) values[0];
string specificSchema = (string)values[1];
string specifcName = (string)values[2];
string routineCatalog = (string)values[3];
string routineSchema = (string)values[4];
string routineName = (string)values[5];
string routineType = (string)values[6];
DateTime? created = (DateTime?)values[7];
DateTime? lastAltered = (DateTime?)values[8];
AddRow(
table,
specificCatalog,
specificSchema,
specifcName,
routineCatalog,
routineSchema,
routineName,
routineType,
created,
lastAltered);
}
}
}
/// <summary>
/// Fills the given <c>Indexes</c> metadata collection table
/// using the given connection and restrictions.
/// </summary>
/// <param name="connection">The connection from which to fill the table.</param>
/// <param name="table">The table to fill.</param>
/// <param name="restrictions">The restrictions to apply.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 6);
string catalogName = restrictions[0];
string schemaNamePattern = restrictions[1];
string tableNamePattern = restrictions[2];
string indexNamePattern = restrictions[3];
string isUnique = restrictions[4];
string isPrimaryKey = restrictions[5];
if (WantsIsNull(schemaNamePattern)
|| WantsIsNull(tableNamePattern)
|| WantsIsNull(indexNamePattern)
|| WantsIsNull(isUnique)
|| WantsIsNull(isPrimaryKey))
{
return;
}
if (isUnique != null)
{
isUnique = isUnique.ToUpperInvariant();
}
if (isPrimaryKey != null)
{
isPrimaryKey = isPrimaryKey.ToUpperInvariant();
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("TABLE_CATALOG", "=", catalogName))
.Append(And("TABLE_SCHEMA", "LIKE", schemaNamePattern))
.Append(And("TABLE_NAME", "LIKE", tableNamePattern))
.Append(And("INDEX_NAME", "LIKE", indexNamePattern))
.Append(And("UNIQUE", "=", isUnique))
.Append(And("PRIMARY_KEY", "=", isPrimaryKey))
.Append(" ORDER BY 5, 6, 7, 8");
// table_catalog, table_schema, table_name, index_name
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string constraintCatalog = (string)values[0];
string constraintSchema = (string)values[1];
string constraintName = (string)values[2];
string constraintType = (string)values[3];
string tableCatalog = (string)values[4];
string tableSchema = (string)values[5];
string tableName = (string)values[6];
string indexName = (string)values[7];
bool unique = (bool)values[8];
bool primaryKey = (bool)values[9];
AddRow(
table,
constraintCatalog,
constraintSchema,
constraintName,
constraintType,
tableCatalog,
tableSchema,
tableName,
indexName,
unique,
primaryKey);
}
}
}
/// <summary>
/// Fills the given <c>ForeignKeys</c> metadata collection
/// table using the given connection and restrictions.
/// </summary>
/// <param name="connection">The connection from which to fill the table.</param>
/// <param name="table">The table to file.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 4);
string catalogName = restrictions[0];
string schemaNamePattern = restrictions[1];
string tableNamePattern = restrictions[2];
string constraintNamePattern = restrictions[3];
if (WantsIsNull(schemaNamePattern)
|| WantsIsNull(tableNamePattern)
|| WantsIsNull(constraintNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
#if CATALOG_RESTRICTIONS
.Append(And("TABLE_CATALOG", "=", catalogName))
#endif
.Append(And("TABLE_SCHEMA", "LIKE", schemaNamePattern))
.Append(And("TABLE_NAME", "LIKE", tableNamePattern))
.Append(And("CONSTRAINT_NAME", "LIKE", constraintNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string constraintCatalog = (string)values[0];
string constraintSchema = (string)values[1];
string constraintName = (string)values[2];
string constraintType = (string)values[3];
string tableCatalog = (string)values[4];
string tableSchema = (string)values[5];
string tableName = (string)values[6];
string uniqueTableCatalog = (string)values[7];
string uniqueTableSchema = (string)values[8];
string uniqueTableName = (string)values[9];
string isDeferrable = (string)values[10];
string initiallyDeferred = (string)values[11];
string matchOption = (string)values[12];
string updateRule = (string)values[13];
string deleteRule = (string)values[14];
AddRow(
table,
constraintCatalog,
constraintSchema,
constraintName,
constraintType,
tableCatalog,
tableSchema,
tableName,
uniqueTableCatalog,
uniqueTableSchema,
uniqueTableName,
isDeferrable,
initiallyDeferred,
matchOption,
updateRule,
deleteRule
);
}
}
}
/// <summary>
/// Fills the given <c>Columns</c> metadata collection table
/// using the given connection and restrictions.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table to fill.</param>
/// <param name="restrictions">The restrictions to apply.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 4);
string catalog = restrictions[0];
string schemaNamePattern = restrictions[1];
string tableNamePattern = restrictions[2];
string columnNamePattern = restrictions[3];
if (WantsIsNull(tableNamePattern)
|| WantsIsNull(columnNamePattern))
{
return;
}
schemaNamePattern = TranslateSchema(connection, schemaNamePattern);
StringBuilder query = new StringBuilder(sql)
#if CATALOG_RESTRICTIONS
.Append(And("TABLE_CAT", "=", catalog))
#endif
.Append(And("TABLE_SCHEM", "LIKE", schemaNamePattern))
.Append(And("TABLE_NAME", "LIKE", tableNamePattern))
.Append(And("COLUMN_NAME", "LIKE", columnNamePattern));
HsqlDataReader reader;
string collation = "UCS_BASIC";
const int icatalog = 0;
const int ischema = 1;
const int itable = 2;
const int icolumn = 3;
const int iordinal = 4;
//
const int idefault = 5;
const int iis_nullable = 6;
const int idatatype = 7;
//
const int icharmaxlen = 8;
const int icharoctlen = 9;
//
const int inumprec = 10;
const int inumprecrad = 11;
const int inumscale = 12;
//
const int idatetimeprec = 13;
const int iintervaltype = 14;
const int iintervalprec = 15;
//
const int icharsetcat = 16;
const int icharsetschem = 17;
const int icharsetname = 18;
//
const int icollationcat = 19;
const int icollationschem = 20;
//const int icollationname = 21;
//
const int idomaincat = 22;
const int idomainschem = 23;
const int idomainname = 24;
//
const int iudtcat = 25;
const int iudtschem = 26;
const int iudtname = 27;
//
const int iscopecat = 28;
const int iscopeschem = 29;
const int iscopename = 30;
//
const int imaxcardinality = 31;
//
const int idtdidentifier = 32;
//
const int iis_selfref = 33;
//
const int iis_identity = 34;
const int iidentitygen = 35;
const int iidentitystart = 36;
const int iidentityinc = 37;
const int iidentitymax = 38;
const int iidentitymin = 39;
const int iidentitycycle = 40;
//
const int iis_generated = 41;
const int igenerationexp = 42;
//
const int iis_updatable = 43;
//
const int idecldatatype = 44;
const int ideclnumprec = 45;
const int ideclnumscale = 46;
//
const int iprimarykey = 47;
using (reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string catalogName = (string)values[icatalog];
string schemaName = (string)values[ischema];
string tableName = (string)values[itable];
string columnName = (string)values[icolumn];
int ordinalPosition = (int)values[iordinal];
//
string defaultValue = (string)values[idefault];
string isNullable = (string)values[iis_nullable];
string dataType = (string)values[idatatype];
//
int? charMaxLength = (int?)values[icharmaxlen];
int? charOctetLength = (int?)values[icharoctlen];
//
//int? ni;
int? numPrecision = (int?)values[inumprec];
short? numPrecRadix = (short?)(int?)values[inumprecrad];
int? numScale = (int?)values[inumscale];
//
int? dateTimePrecision = (int?)values[idatetimeprec];
//
string intervalType = (string)values[iintervaltype];
short? intervalPrecision = (short?)(int?)values[iintervalprec];
//
string characterSetCatalog = (string)values[icharsetcat];
string characterSetSchema = (string)values[icharsetschem];
string characterSetName = (string)values[icharsetname];
//
string collationCatalog = (string)values[icollationcat];
string collationSchema = (string)values[icollationschem];
string collationName = collation; // values[21];
// TODO
string domainCatalog = (string)values[idomaincat];
string domainSchema = (string)values[idomainschem];
string domainName = (string)values[idomainname];
// TODO
string udtCatalog = (string)values[iudtcat];
string udtSchema = (string)values[iudtschem];
string udtName = (string)values[iudtname];
// TODO
string scopeCatalog = (string)values[iscopecat];
string scopeSchema = (string)values[iscopeschem];
string scopeName = (string)values[iscopename];
//
int? maximumCardinality = (int?)values[imaxcardinality];
//
string dtdIdentifier = (string)values[idtdidentifier];
//
string isSelfReferencing = (string)values[iis_selfref];
// TODO: Efficient implementation requires a new HQLDB system table.
// Otherwise, we need to execute an empty select against the
// column's table and read the ResultSetMetaData to tell if
// autoIncrement is true for the column...ugh.
string isIdentity = (string)values[iis_identity];
string identityGeneration = (string)values[iidentitygen];
string identityStart = (string)values[iidentitystart];
string identityIncrement = (string)values[iidentityinc];
string identityMaximum = (string)values[iidentitymax];
string identityMinimum = (string)values[iidentitymin];
string identityCycle = (string)values[iidentitycycle];
//
string isGenerated = (string)values[iis_generated];
string generationExpression = (string)values[igenerationexp];
string isUpdatable = (string)values[iis_updatable];
string declaredDataType = (string)values[idecldatatype];
int? declaredNumericPrecision = (int?)values[ideclnumprec];
int? declaredNumericScale = (int?)values[ideclnumscale];
bool primaryKey = (bool)values[iprimarykey];
AddRow(table,
catalogName,
schemaName,
tableName,
columnName,
ordinalPosition,
defaultValue,
isNullable,
dataType,
charMaxLength,
charOctetLength,
numPrecision,
numPrecRadix,
numScale,
dateTimePrecision,
intervalType,
intervalPrecision,
characterSetCatalog,
characterSetSchema,
characterSetName,
collationCatalog,
collationSchema,
collationName,
domainCatalog,
domainSchema,
domainName,
udtCatalog,
udtSchema,
udtName,
scopeCatalog,
scopeSchema,
scopeName,
maximumCardinality,
dtdIdentifier,
isSelfReferencing,
isIdentity,
identityGeneration,
identityStart,
identityIncrement,
identityMaximum,
identityMinimum,
identityCycle,
isGenerated,
generationExpression,
isUpdatable,
declaredDataType,
declaredNumericPrecision,
declaredNumericScale,
primaryKey);
}
}
}
/// <summary>
/// Fills the user defined types table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
// TODO
}
/// <summary>
/// Executes the specified SQL query.
/// </summary>
/// <param name="connection">The connection on which to execute
/// the query.</param>
/// <param name="sql">The SQL query to execute.</param>
/// <returns>A new data reader holding the result</returns>
protected HsqlDataReader Execute(HsqlConnection connection, string sql)
{
return new HsqlDataReader(connection.Session.ExecuteDirect(sql));
}
/// <summary>
/// Fills the table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 5);
string catalogName = restrictions[0];
string schemaNamePattern = restrictions[1];
string tableNamePattern = restrictions[2];
string constraintNamePattern = restrictions[3];
string columnNamePattern = restrictions[4];
if (WantsIsNull(schemaNamePattern)
|| WantsIsNull(tableNamePattern)
|| WantsIsNull(constraintNamePattern)
|| WantsIsNull(columnNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("TABLE_CATALOG", "=", catalogName))
.Append(And("TABLE_SCHEMA", "LIKE", schemaNamePattern))
.Append(And("TABLE_NAME", "LIKE", tableNamePattern))
.Append(And("CONSTRAINT_NAME", "LIKE", constraintNamePattern))
.Append(And("COLUMN_NAME", "LIKE", columnNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string constraintCatalog = (string)values[0];
string constraintSchema = (string)values[1];
string constraintName = (string)values[2];
string tableCatalog = (string)values[3];
string tableSchema = (string)values[4];
string tableName = (string)values[5];
string columnName = (string)values[6];
int ordinalPosition = (int)values[7];
AddRow(
table,
constraintCatalog,
constraintSchema,
constraintName,
tableCatalog,
tableSchema,
tableName,
columnName,
ordinalPosition);
}
}
}
/// <summary>
/// Translates the given schema name value.
/// </summary>
/// <remarks>
///
/// </remarks>
/// <param name="connection">The connection.</param>
/// <param name="schemaName">Name of the schema.</param>
/// <returns></returns>
protected string TranslateSchema(HsqlConnection connection,
string schemaName)
{
if (connection.Settings.DefaultSchemaQualification
&& (WantsIsNull(schemaName)))
{
string defaultSchema = connection.Session.ExecuteScalarDirect(
DefaultSchemaQuery) as string;
return (string.IsNullOrEmpty(defaultSchema))
? schemaName : defaultSchema;
}
else
{
return schemaName;
}
}
/// <summary>
/// Fills the given <c>IndexColumns</c> metadata collection table
/// using the given connection and restrictions.
/// </summary>
/// <param name="connection">The connection from which to fill the table.</param>
/// <param name="table">The table to fill.</param>
/// <param name="restrictions">The restrictions to apply.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 5);
string catalogName = restrictions[0];
string schemaNamePattern = restrictions[1];
string tableNamePattern = restrictions[2];
string indexNamePattern = restrictions[3];
string columnNamePattern = restrictions[4];
if (WantsIsNull(tableNamePattern)
|| WantsIsNull(indexNamePattern)
|| WantsIsNull(columnNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
#if CATALOG_RESTRICTIONS
.Append(And("TABLE_CATALOG", "=", catalogName))
#endif
.Append(And("TABLE_SCHEMA", "LIKE", schemaNamePattern))
.Append(And("TABLE_NAME", "LIKE", tableNamePattern))
.Append(And("INDEX_NAME", "LIKE", indexNamePattern))
.Append(And("COLUMN_NAME", "LIKE", columnNamePattern))
.Append(" ORDER BY 5, 6, 7, 8, 10");
// table_catalog, table_schema, table_name, index_name, ordinal_position
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string constraintCatalog = (string) values[0];
string constraintSchema = (string) values[1];
string constraintName = (string) values[2];
string constraintType = (string)values[3];
string tableCatalog = (string) values[4];
string tableSchema = (string) values[5];
string tableName = (string) values[6];
string indexName = (string)values[7];
string columnName = (string) values[8];
int ordinalPosition = (int) values[9];
short keyType = (short) (int) values[10];
AddRow(
table,
constraintCatalog,
constraintSchema,
constraintName,
constraintType,
tableCatalog,
tableSchema,
tableName,
indexName,
columnName,
ordinalPosition,
keyType);
}
}
}
/// <summary>
/// Fills the table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 4);
string catalogName = restrictions[0];
string schemaNamePattern = restrictions[1];
string tableNamePattern = restrictions[2];
string triggerNamePattern = restrictions[3];
if (WantsIsNull(schemaNamePattern)
|| WantsIsNull(tableNamePattern)
|| WantsIsNull(triggerNamePattern))
{
return;
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("TABLE_CATALOG", "=", catalogName))
.Append(And("TABLE_SCHEMA", "LIKE", schemaNamePattern))
.Append(And("TABLE_NAME", "LIKE", tableNamePattern))
.Append(And("TRIGGER_NAME", "LIKE", triggerNamePattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string triggerCatalog = (string)values[0];
string triggerSchema = (string)values[1];
string triggerName = (string)values[2];
string tableCatalog = (string)values[3];
string tableSchema = (string)values[4];
string baseObjectType = (string)values[5];
string tableName = (string)values[6];
string columnName = (string)values[7];
string referencingNames = (string)values[8];
string whenClause = (string)values[9];
string status = (string)values[10];
string description = (string)values[11];
string actionType = (string)values[12];
string triggerBody = (string)values[13];
AddRow(
table,
triggerCatalog,
triggerSchema,
triggerName,
tableCatalog,
tableSchema,
baseObjectType,
tableName,
columnName,
referencingNames,
whenClause,
status,
description,
actionType,
triggerBody
);
}
}
}
/// <summary>
/// Fills a <c>DataSourceInformationCollection</c> table using the given connection and restrictions.
/// </summary>
/// <param name="connection">The connection from which to fill the table (ignored).</param>
/// <param name="table">The table to fill.</param>
/// <param name="restrictions">The restrictions (ignored).</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
AddRow(
table,
ValueOf.CompositeIndentiferSeparatorPattern,
ValueOf.DataSourceProductName,
ValueOf.DataSourceProductVersion,
ValueOf.DataSourceProductVersionNormalized,
ValueOf.HsqlGroupByBehavior,
ValueOf.IdentifierPattern,
ValueOf.HsqlIdentifierCase,
ValueOf.OrderByColumnsInSelect,
ValueOf.ParameterMarkerFormat,
ValueOf.ParameterMarkerPattern,
ValueOf.ParameterNameMaxLength,
ValueOf.ParameterNamePattern,
ValueOf.QuotedIdentifierPattern,
ValueOf.QuotedIdentifierCase,
ValueOf.StatementSeparatorPattern,
ValueOf.StringLiteralPattern,
ValueOf.HsqlSupportedJoinOperators);
}
/// <summary>
/// Fills the tables table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 4);
string catalogName = restrictions[0];
string schemaPattern = restrictions[1];
string tablePattern = restrictions[2];
string typePattern = restrictions[3];
if (schemaPattern != null && schemaPattern.StartsWith("\"") && schemaPattern.EndsWith("\""))
{
schemaPattern = schemaPattern.Substring(1, schemaPattern.Length - 2);
}
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("TABLE_CAT", "=", catalogName))
.Append(And("TABLE_SCHEM", "LIKE", schemaPattern))
.Append(And("TABLE_NAME", "LIKE", tablePattern));
if (typePattern != null)
{
string[] types = typePattern.Split(Comma);
if (types.Length == 1)
{
query.Append(And("TABLE_TYPE", "LIKE", types[0]));
}
else
{
query.Append(And("TABLE_TYPE", "IN", ToSQLInList(types)));
}
}
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
string tableCatalog = (string) values[0];
string tableSchema = (string)values[1];
string tableName = (string)values[2];
string tableType = (string)values[3];
AddRow(
table,
tableCatalog,
tableSchema,
tableName,
tableType);
}
}
}
/// <summary>
/// Fills a ReservedWords metadata collection table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
DataRowCollection rows = table.Rows;
for (int i = 0; i < ReservedWords.Length; i++)
{
DataRow row = table.NewRow();
row[MDCN.ReservedWord] = ReservedWords[i];
rows.Add(row);
}
}
/// <summary>
/// Fills a <c>Restrictions</c> metadata collection table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
//
AddRow(table, HCN.Users, "User_Name", "@p1", null, 1);
//
AddRow(table, HCN.Databases, "Name", "@p1", null, 1);
//
AddRow(table, HCN.Tables, "Database", "@p1", null, 1);
AddRow(table, HCN.Tables, "Schema", "@p2", null, 2);
AddRow(table, HCN.Tables, "Table", "@p3", null, 3);
AddRow(table, HCN.Tables, "TableType", "@p4", null, 4);
//
AddRow(table, HCN.Columns, "Database", "@p1", null, 1);
AddRow(table, HCN.Columns, "Schema", "@p2", null, 2);
AddRow(table, HCN.Columns, "Table", "@p3", null, 3);
AddRow(table, HCN.Columns, "Column", "@p4", null, 4);
//
AddRow(table, HCN.ViewColumns, "Database", "@p1", null, 1);
AddRow(table, HCN.ViewColumns, "Schema", "@p2", null, 2);
AddRow(table, HCN.ViewColumns, "Table", "@p3", null, 3);
AddRow(table, HCN.ViewColumns, "Column", "@p4", null, 4);
//
AddRow(table, HCN.Views, "Database", "@p1", null, 1);
AddRow(table, HCN.Views, "Schema", "@p2", null, 2);
AddRow(table, HCN.Views, "Table", "@p3", null, 3);
//
AddRow(table, HCN.ProcedureParameters, "Database", "@p1", null, 1);
AddRow(table, HCN.ProcedureParameters, "Schema", "@p2", null, 2);
AddRow(table, HCN.ProcedureParameters, "Name", "@p3", null, 3);
AddRow(table, HCN.ProcedureParameters, "Parameter", "@p4", null, 4);
//
AddRow(table, HCN.Procedures, "Database", "@p1", null, 1);
AddRow(table, HCN.Procedures, "Schema", "@p2", null, 2);
AddRow(table, HCN.Procedures, "Name", "@p3", null, 3);
AddRow(table, HCN.Procedures, "Type", "@p4", null, 4);
//
AddRow(table, HCN.IndexColumns, "Database", "@p1", null, 1);
AddRow(table, HCN.IndexColumns, "Schema", "@p2", null, 2);
AddRow(table, HCN.IndexColumns, "Table", "@p3", null, 3);
AddRow(table, HCN.IndexColumns, "IndexName", "@p4", null, 4);
AddRow(table, HCN.IndexColumns, "Column", "@p5", null, 5);
//
AddRow(table, HCN.Indexes, "Database", "@p1", null, 1);
AddRow(table, HCN.Indexes, "Schema", "@p2", null, 2);
AddRow(table, HCN.Indexes, "Table", "@p3", null, 3);
AddRow(table, HCN.Indexes, "Name", "@p4", null, 4);
AddRow(table, HCN.Indexes, "IsUnique", "@p5", null, 5);
AddRow(table, HCN.Indexes, "IsPrimary", "@p6", null, 6);
//
AddRow(table, HCN.UserDefinedTypes, "assembly_name", "@p1", null, 1);
AddRow(table, HCN.UserDefinedTypes, "udt_name", "@p2", null, 2);
//
AddRow(table, HCN.PrimaryKeyColumns, "Database", "@p1", null, 1);
AddRow(table, HCN.PrimaryKeyColumns, "Schema", "@p2", null, 2);
AddRow(table, HCN.PrimaryKeyColumns, "Table", "@p3", null, 3);
AddRow(table, HCN.PrimaryKeyColumns, "Constraint", "@p4", null, 4);
AddRow(table, HCN.PrimaryKeyColumns, "Column", "@p5", null, 5);
//
AddRow(table, HCN.PrimaryKeys, "Database", "@p1", null, 1);
AddRow(table, HCN.PrimaryKeys, "Schema", "@p2", null, 2);
AddRow(table, HCN.PrimaryKeys, "Table", "@p3", null, 3);
AddRow(table, HCN.PrimaryKeys, "Name", "@p4", null, 4);
//
AddRow(table, HCN.ForeignKeyColumns, "Database", "@p1", null, 1);
AddRow(table, HCN.ForeignKeyColumns, "Schema", "@p2", null, 2);
AddRow(table, HCN.ForeignKeyColumns, "Table", "@p3", null, 3);
AddRow(table, HCN.ForeignKeyColumns, "Constraint", "@p4", null, 4);
AddRow(table, HCN.ForeignKeyColumns, "Column", "@p5", null, 5);
//
AddRow(table, HCN.ForeignKeys, "Database", "@p1", null, 1);
AddRow(table, HCN.ForeignKeys, "Schema", "@p2", null, 2);
AddRow(table, HCN.ForeignKeys, "Table", "@p3", null, 3);
AddRow(table, HCN.ForeignKeys, "Name", "@p4", null, 4);
//
AddRow(table, HCN.TableCheckConstraints, "Database", "@p1", null, 1);
AddRow(table, HCN.TableCheckConstraints, "Schema", "@p2", null, 2);
AddRow(table, HCN.TableCheckConstraints, "Table", "@p3", null, 3);
AddRow(table, HCN.TableCheckConstraints, "Name", "@p4", null, 4);
//
AddRow(table, HCN.TableTriggers, "Database", "@p1", null, 1);
AddRow(table, HCN.TableTriggers, "Schema", "@p2", null, 2);
AddRow(table, HCN.TableTriggers, "Table", "@p3", null, 3);
AddRow(table, HCN.TableTriggers, "Name", "@p4", null, 4);
//
AddRow(table, HCN.Sequences, "Database", "@p1", null, 1);
AddRow(table, HCN.Sequences, "Schema", "@p2", null, 2);
AddRow(table, HCN.Sequences, "Name", "@p3", null, 3);
//
AddRow(table, HCN.UniqueConstraints, "Database", "@p1", null, 1);
AddRow(table, HCN.UniqueConstraints, "Schema", "@p2", null, 2);
AddRow(table, HCN.UniqueConstraints, "Table", "@p3", null, 3);
AddRow(table, HCN.UniqueConstraints, "Name", "@p4", null, 4);
//
AddRow(table, HCN.UniqueConstraintColumns, "Database", "@p1", null, 1);
AddRow(table, HCN.UniqueConstraintColumns, "Schema", "@p2", null, 2);
AddRow(table, HCN.UniqueConstraintColumns, "Table", "@p3", null, 3);
AddRow(table, HCN.UniqueConstraintColumns, "Constraint", "@p4", null, 4);
AddRow(table, HCN.UniqueConstraintColumns, "Column", "@p5", null, 5);
//
AddRow(table, HCN.Schemas, "Database", "@p1", null, 1);
AddRow(table, HCN.Schemas, "Name", "@p2", null, 2);
AddRow(table, HCN.Schemas, "Owner", "@p3", null, 3);
}
/// <summary>
/// Constructs a new <c>HsqlDatabaseMetaData</c> instance
/// for the given connection.
/// </summary>
/// <param name="connection">The connection.</param>
public HsqlDatabaseMetaData(HsqlConnection connection)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
m_connection = connection;
}
/// <summary>
/// Fills the given data table.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table.</param>
/// <param name="restrictions">The restrictions.</param>
public override void FillTable(HsqlConnection connection,
DataTable table, string[] restrictions)
{
restrictions = GetRestrictions(restrictions, 3);
string catalogName = restrictions[0];
string schemaNamePattern = restrictions[1];
string schemaOwnerPattern = restrictions[2];
StringBuilder query = new StringBuilder(sql);
query
//.Append(And("catalog_name", "=", catalogName))
.Append(And("schema_name", "LIKE", schemaNamePattern))
.Append(And("schema_owner", "LIKE", schemaOwnerPattern));
using (HsqlDataReader reader = Execute(connection, query.ToString()))
{
object[] values = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(values);
catalogName = (string)values[0];
string schemaName = (string)values[1];
string schemaOwner = (string)values[2];
string dcsCatalog = (string)values[3];
string dcsSchema = (string)values[4];
string dcsName = (string)values[5];
string sqlPath = (string)values[6];
AddRow(
table,
catalogName,
schemaName,
schemaOwner,
dcsCatalog,
dcsSchema,
dcsName,
sqlPath);
}
}
}
/// <summary>
/// Executes an SQL SELECT of the form
/// <c>SELECT * FROM <table> [WHERE <where*gt;]</c>.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="table">The table name.</param>
/// <param name="where">The where clause.</param>
/// <returns>A new data reader holding the result.</returns>
protected HsqlDataReader ExecuteSelect(
HsqlConnection connection,
string table,
string where)
{
StringBuilder select = new StringBuilder("SELECT * FROM ");
select.Append(table);
if (!string.IsNullOrEmpty(where))
{
select.Append(" WHERE ").Append(where);
}
return Execute(connection, select.ToString());
}
