public override void GetColumnsInfo(DBConnection connection, DBTableInfo tableInfo)
{
var query = string.Format("select * from all_tab_cols where table_name = '{0}'{1} order by column_id",
tableInfo.Name,
string.IsNullOrEmpty(tableInfo.Schema) ? null : $" and owner = '{tableInfo.Schema}'");
QResult list = connection.ExecuteQResult(query);
int iName = list.GetIndex("column_name");
int iType = list.GetIndex("data_type");
int iPrec = list.GetIndex("data_precision");
int iScale = list.GetIndex("data_scale");
int iLeng = list.GetIndex("data_length");
int iNull = list.GetIndex("nullable");
int iDefault = list.GetIndex("data_default");
foreach (object[] item in list.Values)
{
tableInfo.Columns.Add(new DBColumnInfo()
{
Name = item[iName].ToString(),
DataType = item[iType].ToString(),
Precision = item[iPrec].ToString(),
Scale = item[iScale].ToString(),
Length = item[iLeng].ToString(),
NotNull = item[iNull].Equals("N"),
Default = item[iDefault].ToString(),
});
}
}
public virtual void GetColumnsInfo(DBConnection connection, DBTableInfo tableInfo)
{
var query = string.Format("select * from information_schema.columns where table_name='{0}'{1} order by ordinal_position",
tableInfo.Name,
string.IsNullOrEmpty(tableInfo.Schema) ? null : $" and table_schema = '{tableInfo.Schema}'");
QResult list = connection.ExecuteQResult(query);
int iName = list.GetIndex("column_name");
int iType = list.GetIndex("data_type");
int iPrec = list.GetIndex("numeric_precision");
int iScale = list.GetIndex("numeric_scale");
int iLeng = list.GetIndex("character_maximum_length");
int iNull = list.GetIndex("is_nullable");
int iDefault = list.GetIndex("column_default");
foreach (object[] item in list.Values)
{
tableInfo.Columns.Add(new DBColumnInfo()
{
Name = item[iName].ToString(),
DataType = item[iType].ToString(),
Precision = item[iPrec].ToString(),
Scale = item[iScale].ToString(),
Length = item[iLeng].ToString(),
NotNull = iNull >= 0 && item[iNull].Equals("NO"),
Default = item[iDefault].ToString(),
});
}
}
public override IEnumerable <DBTableInfo> GetTablesInfo(DBConnection connection, string schemaName = null, string tableName = null)
{
var filterTable = tableName != null ? $" and tbl_name = '{tableName}'" : "";
var list = connection.ExecuteQResult($"select * from sqlite_master where type = 'table'{filterTable}");
int iName = list.GetIndex("tbl_name");
foreach (object[] item in list.Values)
{
var table = new DBTableInfo()
{
Name = item[iName].ToString(),
};
GetColumnsInfo(connection, table);
yield return(table);
}
}
public override IEnumerable <DBTableInfo> GetTablesInfo(DBConnection connection, string schemaName, string tableName = null)
{
var filter = schemaName != null ? $" where owner = '{schemaName.ToUpper()}'{(tableName != null ? $" and table_name = '{tableName.ToUpper()}'" : null)}" : null;
QResult list = connection.ExecuteQResult($"select * from all_tables{filter}");
int iSchema = list.GetIndex("owner");
int iName = list.GetIndex("table_name");
foreach (object[] item in list.Values)
{
var table = new DBTableInfo()
{
Schema = item[iSchema].ToString(),
Name = item[iName].ToString(),
};
GetColumnsInfo(connection, table);
yield return(table);
}
}
public override void GetColumnsInfo(DBConnection connection, DBTableInfo table)
{
var list = connection.ExecuteQResult($"PRAGMA table_info({table.Name})");
int iName = list.GetIndex("name");
int iType = list.GetIndex("type");
int iNull = list.GetIndex("notnull");
int iDefault = list.GetIndex("dflt_value");
foreach (object[] item in list.Values)
{
table.Columns.Add(new DBColumnInfo()
{
Name = item[iName].ToString(),
DataType = item[iType].ToString(),
NotNull = item[iNull].ToString().Equals("1"),
Default = item[iDefault].ToString(),
});
}
}
public virtual IEnumerable <DBTableInfo> GetTablesInfo(DBConnection connection, string schemaName = null, string tableName = null)
{
var tableFilter = !string.IsNullOrEmpty(tableName) ? $" and table_name = '{tableName}'" : string.Empty;
var schemaFilter = !string.IsNullOrEmpty(schemaName) ? $" where table_schema = '{schemaName}'{tableFilter}" : string.Empty;
QResult list = connection.ExecuteQResult($"select * from information_schema.tables{schemaFilter} order by table_name");
int iSchema = list.GetIndex("table_schema");
int iName = list.GetIndex("table_name");
int iIndex = list.GetIndex("table_type");
foreach (object[] item in list.Values)
{
var table = new DBTableInfo()
{
Schema = item[iSchema].ToString(),
Name = item[iName].ToString(),
View = item[iIndex].ToString().IndexOf("view", StringComparison.OrdinalIgnoreCase) >= 0,
};
GetColumnsInfo(connection, table);
GetConstraintInfo(connection, table);
yield return(table);
}
}
