/// <summary>
/// Builds the DatabaseSchema for a specified database
/// </summary>
/// <param name="connectionString">The OleDb connection to use</param>
/// <param name="providerType">The DbProviderType to set the DatabaseSchema to</param>
/// <returns></returns>
public static DatabaseSchema CreateDatabaseSchema(string connectionString, Adapdev.Data.DbType databaseType, DbProviderType providerType, string schemaFilter, Adapdev.IProgressCallback progress)
{
int recordCount = 0;
_callback = progress as Adapdev.IProgressCallback;
if (_callback != null) {
_callback.SetText("Obtaining Schema Details","");
_callback.SetAutoClose(ProgressAutoCloseTypes.WaitOnError);
}
DatabaseSchema ds = null;
switch(providerType)
{
case DbProviderType.OLEDB:
case DbProviderType.SQLSERVER:
case DbProviderType.ORACLE:
ds = new OleDbSchemaBuilder(_callback, ref recordCount).BuildDatabaseSchema(connectionString, databaseType, providerType, schemaFilter);
break;
// case DbProviderType.MYSQL:
// ds = new MySqlSchemaBuilder(_callback, ref recordCount).BuildDatabaseSchema(connectionString, databaseType, providerType, schemaFilter);
// break;
}
return ds;
}
public static bool IncrProgress(Adapdev.IProgressCallback _callback, string message, ref int count)
{
if (_callback != null)
{
if (_callback.IsAborting) return false;
_callback.SetText(message);
_callback.StepTo(count++);
}
return true;
}
public static bool StartProgress (Adapdev.IProgressCallback _callback, string message, int max, ref int stepto)
{
if (max > 0)
{
if (_callback != null)
{
if (_callback.IsAborting) return false;
_callback.SetText(message,"");
_callback.SetRange(0, max);
_callback.StepTo(stepto = 0);
}
}
return true;
}
/// <summary>
/// Gets the specified
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static char GetPreDelimeter(Adapdev.Data.DbType type)
{
switch (type)
{
case Adapdev.Data.DbType.ACCESS:
return DialectConstants.ACCESS_PREDELIM;
case Adapdev.Data.DbType.SQLSERVER:
return DialectConstants.SQLSERVER_PREDELIM;
case Adapdev.Data.DbType.ORACLE:
return DialectConstants.ORACLE_PREDELIM;
case Adapdev.Data.DbType.MYSQL:
return DialectConstants.MYSQL_PREDELIM;
default:
throw new Exception("DbType " + type + " not supported currently.");
}
}
public static bool EndProgress (Adapdev.IProgressCallback _callback, string message, bool ok)
{
if (_callback != null)
{
if (_callback.IsAborting) return false;
_callback.SetText(message,"");
_callback.SetRange(0, 1);
if (ok)
{
_callback.StepTo(1);
}
else
{
_callback.StepTo(0);
_callback.AddMessage(ProgressMessageTypes.Critical,"No database schema information found.");
}
}
return true;
}
/// <summary>
/// Surrounds the object with the proper, datastore-specific markup.
/// </summary>
/// <param name="o"></param>
/// <param name="type"></param>
/// <returns></returns>
/// <example>
/// If the passed in object is a date, and the DbType is Access, then the returned value would be #date#.
/// In contrast, if the DbType is SqlServer, then the returned value would be 'date'.
/// </example>
public static string DressUp(object o, Adapdev.Data.DbType type)
{
string ro = "";
if (Util.IsNumeric(o))
{
ro = o.ToString();
}
else if (Util.IsDateTime(o))
{
ro = QueryHelper.GetDateDelimeter(type) + o.ToString() + QueryHelper.GetDateDelimeter(type);
}
// else if(o is Boolean)
// {
// ro = o.ToString().ToLower();
// }
else
{
ro = QueryHelper.GetStringDelimeter(type) + o.ToString() + QueryHelper.GetStringDelimeter(type);
}
return ro;
}
// private DatabaseSchema CreateMySqlDatabaseSchema(string connectionString)
// {
// DataTable schemaTables = this.GetMySqlSchema(connectionString);
//
// DatabaseSchema di = new DatabaseSchema();
// MySqlConnection c = new MySqlConnection(connectionString);
// di.Name = c.Database;
// c = null;
//
// foreach (DataRow dr in schemaTables.Rows)
// {
// TableSchema ti = CreateMySqlTableSchema(dr);
// CreateColumnSchemas(ti, connectionString, Adapdev.Data.DbType.MYSQL);
//
// DataTable columns = this.GetMySqlColumnSchema(connectionString, ti.Name);
//
// foreach(DataRow columnRow in columns.Rows)
// {
// if (columnRow["Key"] + "" == "PRI")
// {
// ti[columnRow["Field"].ToString()].IsPrimaryKey = true;
// }
// else if (columnRow["Key"] + "" == "MUL")
// {
// ti[columnRow["Field"].ToString()].IsForeignKey = true;
// }
// }
// di.AddTable(ti);
// }
//
// return di;
// }
//
/// <summary>
/// Creates the ColumnSchemas for a specified table
/// </summary>
/// <param name="ts">The TableSchema to add the ColumnSchema to</param>
/// <param name="connectionString">The OleDb connectionstring to use</param>
private void CreateColumnSchemas(TableSchema ts, string connectionString, Adapdev.Data.DbType databaseType)
{
DataTable dt = this.GetReaderSchema(connectionString, ts.Name, databaseType);
if (!(dt == null))
{
foreach (DataRow dr in dt.Rows)
{
ColumnSchema ci = new ColumnSchema();
ci.Alias = (string) dr["ColumnName"];
ci.AllowNulls = (bool) dr["AllowDBNull"];
ci.DataTypeId = (int) dr["ProviderType"];
ci.DataType = ProviderInfoManager.GetInstance().GetNameById(this.dbProviderType, ci.DataTypeId);
ci.DefaultTestValue = ProviderInfoManager.GetInstance().GetTestDefaultById(this.dbProviderType, ci.DataTypeId);
ci.DefaultValue = ProviderInfoManager.GetInstance().GetDefaultById(this.dbProviderType, ci.DataTypeId);
ci.IsAutoIncrement = (bool) dr["IsAutoIncrement"];
ci.IsForeignKey = false;
ci.IsPrimaryKey = false;
ci.IsUnique = (bool) dr["IsUnique"];
ci.Length = (int) dr["ColumnSize"];
ci.Name = (string) dr["ColumnName"];
ci.NetType = dr["DataType"].ToString();
ci.Ordinal = (int) dr["ColumnOrdinal"];
ci.IsReadOnly = (bool) dr["IsReadOnly"];
// hack because MySql has the same provider type for
// strings and blobs, which results in blob
// default and test values being incorrectly assigned to
// string columns
if((ci.DataTypeId == 252 && ci.NetType.Equals("System.String")) || ci.DataTypeId == 254)
{
ci.DataTypeId = 253;
ci.DataType = ProviderInfoManager.GetInstance().GetNameById(this.dbProviderType, ci.DataTypeId);
ci.DefaultTestValue = ProviderInfoManager.GetInstance().GetTestDefaultById(this.dbProviderType, ci.DataTypeId);
ci.DefaultValue = ProviderInfoManager.GetInstance().GetDefaultById(this.dbProviderType, ci.DataTypeId);
}
ts.AddColumn(ci);
}
}
}
public DataTable GetReaderSchema(string oledbConnectionString, string tableName, Adapdev.Data.DbType databaseType)
{
return GetReaderSchema(new OleDbConnection(), new OleDbCommand(), oledbConnectionString, databaseType, tableName);
}
public override DatabaseSchema BuildDatabaseSchema(string connectionString, Adapdev.Data.DbType databaseType, Adapdev.Data.DbProviderType providerType, string schemaFilter)
{
DataTable schemaTables = this.GetOleDbSchema(_callback, connectionString, OleDbSchemaGuid.Tables, "",schemaFilter,"","");
DatabaseSchema di = new DatabaseSchema();
if (schemaTables != null)
{
if (schemaTables.Rows.Count > 0)
{
//TODO: Note sure if this is valid. It does not work for Oracle DB's
di.Name = schemaTables.Rows[0]["TABLE_CATALOG"].ToString();
if (di.Name == String.Empty) di.Name = "Unknown";
// Build the base schema information
if (!StartProgress(_callback, "Building Table Details",schemaTables.Rows.Count,ref recordCount)) return null;
foreach (DataRow dr in schemaTables.Rows)
{
TableType tableType = TableTypeConverter.Convert(dr["TABLE_TYPE"].ToString());
if (tableType == TableType.TABLE || tableType == TableType.VIEW)
{
if (!IncrProgress(_callback, dr["TABLE_NAME"].ToString(), ref recordCount)) return null;
TableSchema ti = CreateTableSchema(dr);
CreateColumnSchemas(ti, connectionString, databaseType);
if(ti.Columns.Count > 0) di.AddTable(ti);
}
}
// Get the primary key information
DataTable pkeys = this.GetOleDbSchema(_callback, connectionString, OleDbSchemaGuid.Primary_Keys, "",schemaFilter,"","");
if (pkeys != null)
{
if (!StartProgress(_callback, "Building Primary Key Details",pkeys.Rows.Count,ref recordCount)) return null;
foreach (DataRow dr in pkeys.Rows)
{
string pkTable = dr["TABLE_NAME"].ToString();
if (!IncrProgress(_callback, dr["TABLE_NAME"].ToString(), ref recordCount)) return null;
TableSchema tip = di[pkTable];
if (tip != null)
{
ColumnSchema ci = tip[dr["COLUMN_NAME"].ToString()];
if (ci != null)
{
ci.IsPrimaryKey = true;
tip.AddColumn(ci);
}
}
}
}
// Get the foreign key information
DataTable fkeys = this.GetOleDbSchema(_callback, connectionString, OleDbSchemaGuid.Foreign_Keys, "",schemaFilter,"","");
if (fkeys != null)
{
if (!StartProgress(_callback, "Building Foreign Key Details",fkeys.Rows.Count,ref recordCount)) return null;
foreach (DataRow dr in fkeys.Rows)
{
string fkTable = dr["FK_TABLE_NAME"].ToString();
if (!IncrProgress(_callback, dr["FK_TABLE_NAME"].ToString(), ref recordCount)) return null;
TableSchema tif = di[fkTable];
if (tif != null)
{
ColumnSchema ci = tif[dr["FK_COLUMN_NAME"].ToString()];
if (ci != null)
{
ci.IsForeignKey = true;
tif.AddColumn(ci);
}
}
}
}
// Setup the Progress Display if one is defined.
if (fkeys != null)
{
if (!StartProgress(_callback, "Building Foreign Key Relationships",fkeys.Rows.Count,ref recordCount)) return null;
foreach (DataRow dr in fkeys.Rows)
{
if (!IncrProgress(_callback, dr["PK_TABLE_NAME"].ToString(), ref recordCount)) return null;
// Get the name of the primary key table
string pkTable = dr["PK_TABLE_NAME"].ToString();
// Get the name of the foreign key table
string fkTable = dr["FK_TABLE_NAME"].ToString();
// Get the name of the foreign key column
string fkColumn = dr["FK_COLUMN_NAME"].ToString();
// Get the table containing the primary key
TableSchema tif = di[pkTable];
// Get the table containing the foreign key
TableSchema fk = di[fkTable];
if (tif != null)
{
// Get the primary key
ColumnSchema ci = tif[dr["PK_COLUMN_NAME"].ToString()];
// Get the foreign key
ColumnSchema cf = fk[fkColumn];
if (ci != null)
{
// Add the association to the table and column containing the foreign key
ci.ForeignKeyTables.Add(new ForeignKeyAssociation(ci, cf ,fk));
}
}
}
}
if (!EndProgress(_callback, "Finished Loading Tables",true)) return null;
}
else
{
if (!EndProgress(_callback, "No database schema information found.",false)) return null;
}
}
else
{
if (!EndProgress(_callback, "No database schema information found.",false)) return null;
}
return di;
}
/// <summary>
/// Gets the OLE db schema.
/// </summary>
/// <param name="oledbConnectionString">Oledb connection string.</param>
/// <param name="guid">GUID.</param>
/// <param name="filterCatalog">Filter catalog.</param>
/// <param name="filterSchema">Filter schema.</param>
/// <param name="filterName">Name of the filter.</param>
/// <param name="filterType">Filter type.</param>
/// <returns></returns>
public DataTable GetOleDbSchema(Adapdev.IProgressCallback _callback, string oledbConnectionString, Guid guid, string filterCatalog, string filterSchema, string filterName, string filterType)
{
DataTable schemaTable = null;
OleDbConnection conn = new OleDbConnection(oledbConnectionString);
conn.Open();
try
{
schemaTable = conn.GetOleDbSchemaTable(guid, GetFilters(guid, filterCatalog, filterSchema, filterName, filterType));
}
catch (Exception ex)
{
if (_callback != null) _callback.AddMessage(ProgressMessageTypes.Critical, "Error obtaining Schema Information: " + ex.Message);
}
conn.Close();
return schemaTable;
}
public OleDbSchemaBuilder(Adapdev.IProgressCallback callback, ref int recordCount)
{
this._callback = callback;
this.recordCount = recordCount;
}
/// <summary>
/// See <see cref="Adapdev.Text.Indexing.IIndex.Update"/> for details.
/// </summary>
/// <param name="record">existing record that should have its index information updated</param>
/// <remarks>reference comparison is always used</remarks>
public void Update(Adapdev.Text.Indexing.IRecord record)
{
Remove(record);
Add(record);
}
/// <summary>
/// See <see cref="Adapdev.Text.Indexing.IIndex.Add"/> for details.
/// </summary>
/// <param name="record">record that should be indexed</param>
/// <remarks>
/// Indexes all the fields included in the
/// <see cref="Fields"/> collection. Notice
/// however that the record is never automatically
/// reindexed should its fields change or should
/// the collection of indexed fields (<see cref="Fields"/>)
/// change.<br />
/// The application is always responsible for calling
/// <see cref="Update"/> in such cases.
/// </remarks>
public void Add(Adapdev.Text.Indexing.IRecord record)
{
foreach (IndexedField field in _fields)
{
IndexByField(record, field);
}
}
/// <summary>
/// Builds the DatabaseSchema for a specified database
/// </summary>
/// <param name="oledbConnectionString">The OleDb connection to use</param>
/// <returns></returns>
public static DatabaseSchema CreateDatabaseSchema(string oledbConnectionString, Adapdev.Data.DbType databaseType, DbProviderType providerType)
{
return SchemaBuilder.CreateDatabaseSchema(oledbConnectionString, databaseType, providerType, "", null);
}
public abstract DatabaseSchema BuildDatabaseSchema(string connectionString, Adapdev.Data.DbType databaseType, Adapdev.Data.DbProviderType providerType, string schemaFilter);
private DataTable GetReaderSchema(IDbConnection cn, IDbCommand cmd, string connectionString, Adapdev.Data.DbType databaseType, string tableName)
{
DataTable schemaTable = null;
try
{
cn.ConnectionString = connectionString;
cn.Open();
// Please Note: Use the GetPre and GetPostDelimiters here as in the case of
// Oracle using [ ] around a Column Name is not valid.
cmd.Connection = cn;
cmd.CommandText = "SELECT * FROM " + QueryHelper.GetPreDelimeter(databaseType) + tableName + QueryHelper.GetPostDelimeter(databaseType);
IDataReader myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
schemaTable = myReader.GetSchemaTable();
myReader.Close();
cn.Close();
}
catch (Exception ex)
{
schemaTable = null;
if (_callback != null) _callback.AddMessage(ProgressMessageTypes.Warning, "Could not load Column information for " + tableName + ". " + ex.Message);
}
return schemaTable;
}
/// <summary>
/// Delegates to <see cref="Adapdev.Text.Indexing.FullText.FullTextSearchIndex.Search"/>.
/// </summary>
/// <param name="index">index</param>
/// <returns></returns>
/// <exception cref="ArgumentException">if the
/// index argument is not of the correct type</exception>
public Adapdev.Text.Indexing.SearchResult Evaluate(Adapdev.Text.Indexing.IIndex index)
{
FullTextSearchIndex ftindex = index as FullTextSearchIndex;
if (null == ftindex)
{
throw new ArgumentException("FullTextSearchExpression objects can be evaluated against FullTextSearchIndex objects only!");
}
return ftindex.Search(this);
}
/// <summary>
/// Builds the DatabaseSchema for a specified database
/// </summary>
/// <param name="oledbConnectionString">The OleDb connection to use</param>
/// <param name="providerType">The DbProviderType to set the DatabaseSchema to</param>
/// <returns></returns>
public static DatabaseSchema CreateDatabaseSchema(string oledbConnectionString, string databaseType, string providerType, string schemaFilter, Adapdev.IProgressCallback progress)
{
return SchemaBuilder.CreateDatabaseSchema(oledbConnectionString, DbTypeConverter.Convert(databaseType), DbProviderTypeConverter.Convert(providerType), schemaFilter, progress);
}
/// <summary>
/// See <see cref="Adapdev.Text.Indexing.IIndex.Remove"/> for details.
/// </summary>
/// <param name="record">record that should be removed from the index</param>
/// <remarks>reference comparison is always used</remarks>
public void Remove(Adapdev.Text.Indexing.IRecord record)
{
foreach (Postings postings in _postings.Values)
{
postings.Remove(record);
}
}
/// <summary>
/// Creates the ColumnSchemas for a specified table
/// </summary>
/// <param name="ts">The TableSchema to add the ColumnSchema to</param>
/// <param name="oledbConnectionString">The OleDb connectionstring to use</param>
public void CreateColumnSchemas(TableSchema ts, string oledbConnectionString, Adapdev.Data.DbType databaseType)
{
DataTable dt = this.GetReaderSchema(oledbConnectionString, ts.Name, databaseType);
if (!(dt == null))
{
foreach (DataRow dr in dt.Rows)
{
ColumnSchema ci = new ColumnSchema();
ci.Alias = (string) dr["ColumnName"];
ci.AllowNulls = (bool) dr["AllowDBNull"];
ci.DataTypeId = (int) dr["ProviderType"];
ci.DataType = ProviderInfoManager.GetInstance().GetNameById(dbProviderType, ci.DataTypeId);
ci.DefaultTestValue = ProviderInfoManager.GetInstance().GetTestDefaultById(this.dbProviderType, ci.DataTypeId);
ci.DefaultValue = ProviderInfoManager.GetInstance().GetDefaultById(this.dbProviderType, ci.DataTypeId);
ci.IsAutoIncrement = (bool) dr["IsAutoIncrement"];
ci.IsForeignKey = false;
ci.IsPrimaryKey = false;
ci.IsUnique = (bool) dr["IsUnique"];
ci.Length = (int) dr["ColumnSize"];
ci.Name = (string) dr["ColumnName"];
ci.NetType = dr["DataType"].ToString();
ci.Ordinal = (int) dr["ColumnOrdinal"];
ci.IsReadOnly = (bool) dr["IsReadOnly"];
ts.AddColumn(ci);
}
}
}
/// <summary>
/// When the expression passed as argument is an instance
/// of FullTextSearchExpression this method behaves exactly
/// as <see cref="Search(FullTextSearchExpression)" />, otherwise
/// it behaves as expression.Evaluate(this).
/// </summary>
/// <param name="expression">search expression</param>
/// <returns>the result of applying the search against this index</returns>
public Adapdev.Text.Indexing.SearchResult Search(Adapdev.Text.Indexing.ISearchExpression expression)
{
FullTextSearchExpression ftexpression = expression as FullTextSearchExpression;
if (null != ftexpression)
{
return Search(ftexpression);
}
return expression.Evaluate(this);
}
public override DatabaseSchema BuildDatabaseSchema(string connectionString, Adapdev.Data.DbType databaseType, Adapdev.Data.DbProviderType providerType, string schemaFilter)
{
return this.CreateMySqlDatabaseSchema(connectionString);
}
