private void GetTableOrViewInfo(SnowflakeDatabaseInfo databaseInfo, IsTableSourceToIgnore isTableSourceToIgnore, bool showTables, IDbConnection conn, ref IList <ITableSourceInfo> tables)
{
string sql = string.Format("SHOW {1} IN SCHEMA {0}", databaseInfo.Identifier, showTables ? "TABLES" : "VIEWS");
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, sql);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string tableName = (string)reader["name"];
if (!isTableSourceToIgnore(tableName))
{
string qualifiedTableName = GetQualifiedIdentifier(databaseInfo.Identifier, tableName);
tables.Add(new SnowflakeTableSourceInfo(DatabaseServices, databaseInfo, tableName, qualifiedTableName));
}
}
}
}
public abstract IEnumerable <ITableSourceInfo> ListTableSources(IDatabaseInfo database, IsTableSourceToIgnore isTableSourceToIgnore);
/// <summary>
/// Returns a list of table sources (e.g. tables, views) that belong to a given database.
/// The returned table sources must have different display names.
/// </summary>
/// <param name="database">Database from which we want to fetch the list of tables</param>
/// <param name="isTableSourceToIgnore">The delegate to call to see if the table source should be ignored and excluded from the returned list</param>
/// <returns>List of available table sources in the given database</returns>
/// <exception cref="System.Data.Common.DbException">if an error occurs while accessing the database</exception>
public override IEnumerable <ITableSourceInfo> ListTableSources(IDatabaseInfo database, IsTableSourceToIgnore isTableSourceToIgnore)
{
throw new NotImplementedException();
}
public override IEnumerable <ITableSourceInfo> ListTableSources(IDatabaseInfo database, IsTableSourceToIgnore isTableSourceToIgnore)
{
var databaseInfo = database as DatabaseInfo;
if (databaseInfo == null)
{
yield break;
}
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
if (!OwnerExists(conn, databaseInfo))
{
throw new IntrospectionServiceException("Inexistent database: " + databaseInfo.Schema);
}
string link = GetDatabaseLinkName(databaseInfo.DatabaseLink, databaseInfo.IsDatabaseLink);
string sql = "select OBJECT_NAME TABLE_NAME from all_objects" + link +
" where not (OBJECT_NAME like 'BIN$%$%') and (OBJECT_TYPE = 'TABLE' or OBJECT_TYPE = 'VIEW') and owner = "
+ DatabaseServices.ExecutionService.ParameterPrefix + "dbName";
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, sql);
cmd.CommandTimeout = QueryTimeout;
DatabaseServices.ExecutionService.CreateParameter(cmd, DatabaseServices.ExecutionService.ParameterPrefix + "dbName",
DbType.String, databaseInfo.Schema);
using (IDataReader reader = DatabaseServices.ExecutionService.ExecuteReader(cmd)) {
while (reader.Read())
{
string tableName = Convert.ToString(reader["TABLE_NAME"]);
if (!databaseInfo.IsDatabaseLink &&
(tableName.ToLowerInvariant().Equals("dual") || isTableSourceToIgnore(tableName)))
{
continue;
}
yield return
(new TableSourceInfo(DatabaseServices, databaseInfo, tableName, GetQualifiedTableName(databaseInfo, tableName)));
}
}
}
}
/// <summary>
/// Returns a list of table sources (e.g. tables, views) that belong to a given database.
/// The returned table sources must have different display names.
/// </summary>
/// <param name="database">Database from which we want to fetch the list of tables</param>
/// <param name="isTableSourceToIgnore">The delegate to call to see if the table source should be ignored and excluded from the returned list</param>
/// <returns>List of available table sources in the given database</returns>
/// <exception cref="System.Data.Common.DbException">if an error occurs while accessing the database</exception>
public override IEnumerable <ITableSourceInfo> ListTableSources(IDatabaseInfo database, IsTableSourceToIgnore isTableSourceToIgnore)
{
IList <ITableSourceInfo> tables = new List <ITableSourceInfo>();
CacheDatabaseInfo databaseInfo = database as CacheDatabaseInfo;
if (databaseInfo == null)
{
return(null);
}
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
string sql = string.Format(@"SELECT SqlQualifiedNameQ, SqlTableName FROM %Dictionary.CompiledClass
WHERE SqlSchemaName = '{0}'
AND ClassType IN ('persistent', 'view')", databaseInfo.Identifier);
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, sql);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string tableName = (string)reader["SqlTableName"];
string qualifiedTableName = (string)reader["SqlQualifiedNameQ"];
if (!isTableSourceToIgnore(tableName))
{
tables.Add(new CacheTableSourceInfo(DatabaseServices, databaseInfo, tableName, qualifiedTableName));
}
}
}
}
return(tables);
}
public override IEnumerable <ITableSourceInfo> ListTableSources(IDatabaseInfo database, IsTableSourceToIgnore isTableSourceToIgnore)
{
IList <ITableSourceInfo> tables = new List <ITableSourceInfo>();
iDB2DatabaseInfo databaseInfo = database as iDB2DatabaseInfo;
if (databaseInfo == null)
{
return(null);
}
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
string sql = "SELECT TABLE_NAME "
+ "FROM QSYS2.SYSTABLES "
+ "WHERE SYSTEM_TABLE = 'N' and SYSTEM_TABLE_SCHEMA = '" + database.Identifier + "'";
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, sql);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string tableName = (string)reader["TABLE_NAME"];
if (!isTableSourceToIgnore(tableName) && DoesNotContainProblematicChars(tableName))
{
string qualifiedTableName = GetQualifiedIdentifier(databaseInfo.Database, tableName);
tables.Add(new iDB2TableSourceInfo(DatabaseServices, databaseInfo, tableName, qualifiedTableName));
}
}
}
}
return(tables);
}
/// <summary>
/// Returns a list of table sources (e.g. tables, views) that belong to a given database.
/// The returned table sources must have different display names.
/// </summary>
/// <param name="database">Database from which we want to fetch the list of tables</param>
/// <param name="isTableSourceToIgnore">The delegate to call to see if the table source should be ignored and excluded from the returned list</param>
/// <returns>List of available table sources in the given database</returns>
/// <exception cref="System.Data.Common.DbException">if an error occurs while accessing the database</exception>
public override IEnumerable <ITableSourceInfo> ListTableSources(IDatabaseInfo database, IsTableSourceToIgnore isTableSourceToIgnore)
{
SnowflakeDatabaseInfo databaseInfo = database as SnowflakeDatabaseInfo;
if (databaseInfo == null)
{
return(null);
}
IList <ITableSourceInfo> tables = new List <ITableSourceInfo>();
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection())
{
//Fetch Tables
GetTableOrViewInfo(databaseInfo, isTableSourceToIgnore, true, conn, ref tables);
//Fetch Views
GetTableOrViewInfo(databaseInfo, isTableSourceToIgnore, false, conn, ref tables);
}
return(tables);
}
public override IEnumerable <ITableSourceInfo> ListTableSources(IDatabaseInfo database, IsTableSourceToIgnore isTableSourceToIgnore)
{
DatabaseInfo dbInfo = database as DatabaseInfo;
if (dbInfo == null)
{
throw new IntrospectionServiceException("Expected " + typeof(DatabaseInfo).FullName + " type but found " +
(database == null ? "NULL" : database.GetType().FullName));
}
if (!CheckDatabaseExists(dbInfo))
{
throw new IntrospectionServiceException("Database not found: " + dbInfo.Identifier);
}
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
using (IDbCommand cmd = CreateListTableSourcesCommand(dbInfo, conn)) {
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string tableName = Convert.ToString(reader["TABLE_NAME"]);
if (!dbInfo.IsLinkedServer && isTableSourceToIgnore(tableName))
{
continue;
}
string tableSchema = Convert.ToString(reader["TABLE_SCHEM"]);
yield return(new TableSourceInfo(DatabaseServices, dbInfo, tableName, tableSchema,
GetQualifiedTableName(dbInfo, tableName, tableSchema)));
}
}
}
}
}
public override IEnumerable <ITableSourceInfo> ListTableSources(IDatabaseInfo database, IsTableSourceToIgnore isTableSourceToIgnore)
{
IList <ITableSourceInfo> tables = new List <ITableSourceInfo>();
DB2ZOSDatabaseInfo databaseInfo = database as DB2ZOSDatabaseInfo;
if (databaseInfo == null)
{
return(null);
}
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
string sql = "SELECT NAME as TABLE_NAME "
+ "FROM SYSIBM.SYSTABLES "
+ "WHERE TYPE in ('T', 'V', 'P') and CREATOR = '" + database.Identifier + "' ";
//Console.WriteLine("ListTableSources SQL: " + sql);
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, sql);
cmd.CommandTimeout = QueryTimeout;
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string tableName = (string)reader["TABLE_NAME"];
if (!isTableSourceToIgnore(tableName) && DoesNotContainProblematicChars(tableName))
{
string qualifiedTableName = GetQualifiedIdentifier(databaseInfo.Database, tableName);
tables.Add(new DB2ZOSTableSourceInfo(DatabaseServices, databaseInfo, tableName, qualifiedTableName));
}
}
}
}
return(tables);
}
public override IEnumerable <ITableSourceInfo> ListTableSources(IDatabaseInfo database, IsTableSourceToIgnore isTableSourceToIgnore)
{
using (IDbConnection connection = GetConnection())
{
IDbCommand cmd = CreateCommand(connection, ListTableSourcesQuery());
CreateParameter(cmd, "dbname", DbType.String, database.Identifier);
using (IDataReader reader = cmd.ExecuteReader())
{
List <ITableSourceInfo> res = new List <ITableSourceInfo>();
while (reader.Read())
{
string name = (string)reader["table_name"];
if (!isTableSourceToIgnore(name)) // what's this? do I have to respect this?
{
res.Add(new PGTableSource(DatabaseServices, database, name));
}
}
return(res);
}
}
}
public override IEnumerable <ITableSourceInfo> ListTableSources(IDatabaseInfo database, IsTableSourceToIgnore isTableSourceToIgnore)
{
string paramPrefix = DatabaseServices.ExecutionService.ParameterPrefix;
IList <ITableSourceInfo> tables = new List <ITableSourceInfo>();
MySQLDatabaseInfo databaseInfo = database as MySQLDatabaseInfo;
if (databaseInfo == null)
{
return(null);
}
using (IDbConnection conn = DatabaseServices.TransactionService.CreateConnection()) {
string sql = string.Format(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = {0}
UNION
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = {0};", paramPrefix + "schema");
IDbCommand cmd = DatabaseServices.ExecutionService.CreateCommand(conn, sql);
cmd.CommandTimeout = QueryTimeout;
DatabaseServices.ExecutionService.CreateParameter(cmd, paramPrefix + "schema", DbType.String, databaseInfo.Name);
using (IDataReader reader = cmd.ExecuteReader()) {
while (reader.Read())
{
string tableName = (string)reader["TABLE_NAME"];
if (!isTableSourceToIgnore(tableName))
{
string qualifiedTableName = GetQualifiedIdentifier(databaseInfo.Name, tableName);
tables.Add(new MySQLTableSourceInfo(DatabaseServices, databaseInfo, tableName, qualifiedTableName));
}
}
}
}
return(tables);
}
