protected override string GetDeviceIndexesExpression(TableVar ATableVar)
{
return
(String.Format
(
DeviceIndexesExpression == String.Empty ?
@"
select
libname as TableSchema,
memname as TableName,
indxname as IndexName,
name as ColumnName,
indxpos as OrdinalPosition,
case when unique = 'yes' then 1 else 0 end as IsUnique,
0 as IsDescending
from DICTIONARY.INDEXES
where libname = '{0}'
{1}
order by libname, memname, indxname, indxpos
" :
DeviceIndexesExpression,
Schema,
ATableVar == null ? String.Empty : String.Format("and c.memname = '{0}'", ToSQLIdentifier(ATableVar).ToUpper())
));
}
protected override void InternalVerifyInsertStatement(TableVar ATable, IRow ARow, InsertStatement AStatement)
{
SelectExpression LSelectExpression = AStatement.Values as SelectExpression;
if (LSelectExpression != null)
{
if (LSelectExpression.FromClause is CalculusFromClause)
{
SelectExpression LNestedSelectExpression = ((CalculusFromClause)LSelectExpression.FromClause).TableSpecifiers[0].TableExpression as SelectExpression;
if (LNestedSelectExpression != null)
{
LSelectExpression.FromClause = new CalculusFromClause(((CalculusFromClause)LNestedSelectExpression.FromClause).TableSpecifiers[0]);
if (LSelectExpression.WhereClause == null)
{
LSelectExpression.WhereClause = LNestedSelectExpression.WhereClause;
}
else
{
LSelectExpression.WhereClause.Expression =
new BinaryExpression(LSelectExpression.WhereClause.Expression, "iAnd", LNestedSelectExpression.WhereClause.Expression);
}
}
}
}
}
protected override string GetDeviceIndexesExpression(TableVar tableVar)
{
return
(String.Format
(
DeviceIndexesExpression == String.Empty
?
@"
select
tn.nspname as TableSchema,
tc.relname as TableName,
ic.relname as IndexName,
ca.attname as ColumnName,
ca.attnum as OrdinalPosition,
i.indisunique as IsUnique,
case when i.indoption[0]=3 then TRUE else FALSE end as IsDescending
from pg_index i
join pg_class tc on tc.oid = i.indrelid
join pg_class ic on ic.oid = i.indexrelid
join pg_attribute ca on ca.attrelid = ic.oid
join pg_namespace tn on tn.oid = tc.relnamespace
where tn.nspname not in ('information_schema','pg_catalog','pg_toast')
{0}
{1}
order by tn.nspname, tc.relname, ic.relname, ca.attnum
"
:
DeviceIndexesExpression,
Schema == String.Empty ? String.Empty : String.Format("and tn.nspname = '{0}'", Schema),
tableVar == null
? String.Empty
: String.Format("and tc.relname = '{0}'", ToSQLIdentifier(tableVar).ToLower())
));
}
protected override string GetDeviceIndexesExpression(TableVar tableVar)
{
return
(String.Format
(
DeviceIndexesExpression == String.Empty
?
@"
select
i.table_owner as TableSchema,
i.table_name as TableName,
i.index_name as IndexName,
c.column_name as ColumnName,
c.column_position as OrdinalPosition,
case when i.uniqueness = 'UNIQUE' then 1 else 0 end as IsUnique,
case when c.descend = 'DESC' then 1 else 0 end as IsDescending
from all_indexes i, all_ind_columns c
where i.owner = c.index_owner
and i.index_name = c.index_name
and i.index_type = 'NORMAL'
{0}
{1}
order by i.table_owner, i.table_name, i.index_name, c.column_position
"
:
DeviceIndexesExpression,
Schema == String.Empty ? String.Empty : String.Format("and i.table_owner = '{0}'", Schema),
tableVar == null
? String.Empty
: String.Format("and c.table_name = '{0}'", ToSQLIdentifier(tableVar).ToUpper())
));
}
protected override string GetDeviceTablesExpression(TableVar tableVar)
{
return
(String.Format
(
DeviceTablesExpression == String.Empty
?
@"
select
c.owner as TableSchema,
c.table_name as TableName,
c.column_name as ColumnName,
c.column_id as OrdinalPosition,
c.table_name as TableTitle,
c.column_name as ColumnTitle,
c.data_type as NativeDomainName,
c.data_type as DomainName,
NVL(c.char_col_decl_length, 0) as Length,
case when c.nullable = 'N' then 0 else 1 end as IsNullable,
case when c.data_type in ('BLOB', 'CLOB') then 1 else 0 end as IsDeferred
from all_tab_columns c
join all_tables t on c.owner = t.owner and c.table_name = t.table_name
where 1 = 1 {0} {1}
order by c.owner, c.table_name, c.column_id
"
:
DeviceTablesExpression,
Schema == String.Empty ? String.Empty : String.Format("and c.owner = '{0}'", Schema),
tableVar == null
? String.Empty
: String.Format("and c.table_name = '{0}'", ToSQLIdentifier(tableVar).ToUpper())
));
}
public override void DetermineDataType(Plan plan)
{
DetermineModifiers(plan);
_dataType = new Schema.TableType();
_tableVar = new Schema.ResultTableVar(this);
_tableVar.Owner = plan.User;
DataType.Columns.Add(new Schema.Column("Sequence", plan.DataTypes.SystemInteger));
DataType.Columns.Add(new Schema.Column("LogName", plan.DataTypes.SystemString));
foreach (Schema.Column column in DataType.Columns)
{
TableVar.Columns.Add(new Schema.TableVarColumn(column));
}
TableVar.Keys.Add(new Schema.Key(new Schema.TableVarColumn[] { TableVar.Columns["Sequence"] }));
TableVar.DetermineRemotable(plan.CatalogDeviceSession);
Order = Compiler.FindClusteringOrder(plan, TableVar);
// Ensure the order exists in the orders list
if (!TableVar.Orders.Contains(Order))
{
TableVar.Orders.Add(Order);
}
}
protected override string GetDeviceForeignKeysExpression(TableVar tableVar)
{
return
(DeviceForeignKeysExpression != String.Empty
? base.GetDeviceForeignKeysExpression(tableVar)
: String.Empty); // Cannot find a way to get foreign key information from the Cache SQL dictionaries...
}
//public SASRetrieve(Operator AOperator, D4.ClassDefinition AClassDefinition) : base(AOperator, AClassDefinition){}
//public SASRetrieve(Operator AOperator, D4.ClassDefinition AClassDefinition, bool AIsSystem) : base(AOperator, AClassDefinition, AIsSystem){}
public override Statement Translate(DevicePlan ADevicePlan, PlanNode APlanNode)
{
SQLDevicePlan LDevicePlan = (SQLDevicePlan)ADevicePlan;
TableVar LTableVar = ((TableVarNode)APlanNode).TableVar;
if (LTableVar is BaseTableVar)
{
SQLRangeVar LRangeVar = new SQLRangeVar(LDevicePlan.GetNextTableAlias());
foreach (Schema.TableVarColumn LColumn in LTableVar.Columns)
{
LRangeVar.Columns.Add(new SQLRangeVarColumn(LColumn, LRangeVar.Name, LDevicePlan.Device.ToSQLIdentifier(LColumn), LDevicePlan.Device.ToSQLIdentifier(LColumn.Name)));
}
LDevicePlan.CurrentQueryContext().RangeVars.Add(LRangeVar);
SelectExpression LSelectExpression = new SelectExpression();
LSelectExpression.FromClause = new AlgebraicFromClause(new TableSpecifier(new TableExpression(D4.MetaData.GetTag(LTableVar.MetaData, "Storage.Schema", LDevicePlan.Device.Schema), LDevicePlan.Device.ToSQLIdentifier(LTableVar)), LRangeVar.Name));
//LSelectExpression.FromClause = new CalculusFromClause(new TableSpecifier(new TableExpression(D4.MetaData.GetTag(LTableVar.MetaData, "Storage.Schema", LDevicePlan.Device.Schema), LDevicePlan.Device.ToSQLIdentifier(LTableVar)), LRangeVar.Name));
LSelectExpression.SelectClause = new SelectClause();
foreach (TableVarColumn LColumn in LTableVar.Columns)
{
LSelectExpression.SelectClause.Columns.Add(LDevicePlan.GetRangeVarColumn(LColumn.Name, true).GetColumnExpression());
}
LSelectExpression.SelectClause.Distinct =
(LTableVar.Keys.Count == 1) &&
Convert.ToBoolean(D4.MetaData.GetTag(LTableVar.Keys[0].MetaData, "Storage.IsImposedKey", "false"));
return(LSelectExpression);
}
else
{
return(LDevicePlan.Device.TranslateExpression(LDevicePlan, APlanNode.Nodes[0], false));
}
}
public override void DetermineDataType(Plan plan)
{
DetermineModifiers(plan);
_dataType = new Schema.TableType();
_tableVar = new Schema.ResultTableVar(this);
_tableVar.Owner = plan.User;
DataType.Columns.Add(new Schema.Column("Library_Name", plan.DataTypes.SystemString));
DataType.Columns.Add(new Schema.Column("Name", plan.DataTypes.SystemString));
DataType.Columns.Add(new Schema.Column("TimeStamp", plan.DataTypes.SystemDateTime));
DataType.Columns.Add(new Schema.Column("IsDotNetAssembly", plan.DataTypes.SystemBoolean));
DataType.Columns.Add(new Schema.Column("ShouldRegister", plan.DataTypes.SystemBoolean));
foreach (Schema.Column column in DataType.Columns)
{
TableVar.Columns.Add(new Schema.TableVarColumn(column));
}
TableVar.Keys.Add(new Schema.Key(new Schema.TableVarColumn[] { TableVar.Columns["Name"] }));
TableVar.DetermineRemotable(plan.CatalogDeviceSession);
Order = Compiler.FindClusteringOrder(plan, TableVar);
// Ensure the order exists in the orders list
if (!TableVar.Orders.Contains(Order))
{
TableVar.Orders.Add(Order);
}
}
protected override string GetDeviceTablesExpression(TableVar ATableVar)
{
return
(String.Format
(
DeviceTablesExpression == String.Empty ?
@"
select
C.libname as TableSchema,
C.memname as TableName,
C.name as ColumnName,
C.npos as OrdinalPosition,
T.memlabel as TableTitle,
C.label as ColumnTitle,
C.type as NativeDomainName,
C.type as DomainName,
C.length as Length,
0 as IsNullable,
0 as IsDeferred
from DICTIONARY.COLUMNS as C, DICTIONARY.TABLES as T
where C.libname = '{0}'
and C.libname = T.libname
and C.memname = T.memname
{1}
order by C.libname, C.memname, C.npos
" :
DeviceTablesExpression,
Schema,
ATableVar == null ? String.Empty : String.Format("and c.memname = '{0}'", ToSQLIdentifier(ATableVar).ToUpper())
));
}
protected override string GetDeviceTablesExpression(TableVar tableVar)
{
return
(String.Format
(
@"
select
t.table_schem as ""TableSchema"",
t.table_name as ""TableName"",
c.column_name as ""ColumnName"",
c.ordinal_position as ""OrdinalPosition"",
t.table_name as ""TableTitle"",
c.column_name as ""ColumnTitle"",
c.type_name as ""NativeDomainName"",
c.type_name as ""DomainName"",
c.column_size as ""Length"",
case c.nullable when 0 then 0 else 1 end as ""IsNullable"",
case when c.type_name in ('BLOB', 'CLOB') then 1 else 0 end as ""IsDeferred""
from
tables t join columns c
on t.table_name = c.table_name
where (t.table_type = 'TABLE' or t.table_type = 'VIEW') {0}
order by t.table_name, c.ordinal_position
" ,
tableVar == null ? String.Empty : String.Format("and t.table_name = '{0}'", ToSQLIdentifier(tableVar))
));
}
/// <summary>Ensures that the given row is valid.</summary>
/// <param name='oldRow'>A <see cref="Row"/> containing the original values for the row.</param>
/// <param name='newRow'>A <see cref="Row"/> containing the changed values for the row.</param>
/// <param name='columnName'>The name of the column which changed in <paramref name="newRow"/>. If empty, the change affected more than one column.</param>
/// <returns>A boolean value indicating whether any change was made to <paramref name="newRow"/>.</returns>
public bool Validate(IRow oldRow, IRow newRow, string columnName)
{
if ((_internalProcess != null) && TableVar.IsValidateCallRemotable(columnName))
{
IRow localOldRow = oldRow;
if ((oldRow != null) && oldRow.HasNonNativeValues())
{
localOldRow = new Row(_internalProcess.ValueManager, oldRow.DataType);
MarshalRow(oldRow, localOldRow);
}
IRow localNewRow = newRow;
if (newRow.HasNonNativeValues())
{
localNewRow = new Row(_internalProcess.ValueManager, newRow.DataType);
MarshalRow(newRow, localNewRow);
}
_plan._process._session._server.AcquireCacheLock(_plan._process, LockMode.Shared);
try
{
bool changed = TableNode.Validate(_internalProgram, localOldRow, localNewRow, null, columnName);
if (changed && !Object.ReferenceEquals(newRow, localNewRow))
{
MarshalRow(localNewRow, newRow);
}
return(changed);
}
finally
{
_plan._process._session._server.ReleaseCacheLock(_plan._process, LockMode.Shared);
}
}
else
{
RemoteRowBody oldBody = new RemoteRowBody();
if (oldRow != null)
{
_plan._process.EnsureOverflowReleased(oldRow);
oldBody.Data = oldRow.AsPhysical;
}
_plan._process.EnsureOverflowReleased(newRow);
RemoteRowBody newBody = new RemoteRowBody();
newBody.Data = newRow.AsPhysical;
RemoteProposeData proposeData = _cursor.Validate(oldBody, newBody, columnName, _plan._process.GetProcessCallInfo());
_plan._programStatisticsCached = false;
if (proposeData.Success)
{
newRow.ValuesOwned = false; // do not clear the overflow streams because the row is effectively owned by the server during the validate call
newRow.AsPhysical = proposeData.Body.Data;
newRow.ValuesOwned = true;
}
return(proposeData.Success);
}
}
protected override Statement TranslateDropIndex(SQLDevicePlan plan, TableVar tableVar, Order order)
{
var statement = new DropIndexStatement();
statement.TableSchema = MetaData.GetTag(tableVar.MetaData, "Storage.Schema", Schema);
statement.TableName = ToSQLIdentifier(tableVar);
statement.IndexSchema = MetaData.GetTag(order.MetaData, "Storage.Schema", String.Empty);
statement.IndexName = GetIndexName(statement.TableName, order);
return(statement);
}
public Statement EmitColumnHandler(TableVar tableVar, TableVarColumn column, EmitMode mode)
{
AttachStatement statement = new AttachStatement();
statement.MetaData = MetaData;
statement.OperatorName = _operator.OperatorName;
statement.EventSourceSpecifier = new ColumnEventSourceSpecifier(tableVar.Name, column.Name);
statement.EventSpecifier = new EventSpecifier();
statement.EventSpecifier.EventType = _eventType;
return(statement);
}
/// <summary>Requests the default values for a new row in the cursor.</summary>
/// <param name='row'>A <see cref="Row"/> to be filled in with default values.</param>
/// <returns>A boolean value indicating whether any change was made to <paramref name="row"/>.</returns>
public bool Default(IRow row, string columnName)
{
if ((_internalProcess != null) && TableVar.IsDefaultCallRemotable(columnName))
{
// create a new row based on FInternalProcess, and copy the data from
IRow localRow = row;
if (row.HasNonNativeValues())
{
localRow = new Row(_internalProcess.ValueManager, row.DataType);
MarshalRow(row, localRow);
}
_plan._process._session._server.AcquireCacheLock(_plan._process, LockMode.Shared);
try
{
bool changed = TableNode.Default(_internalProgram, null, localRow, null, columnName);
if (changed && !Object.ReferenceEquals(localRow, row))
{
MarshalRow(localRow, row);
}
return(changed);
}
finally
{
_plan._process._session._server.ReleaseCacheLock(_plan._process, LockMode.Shared);
}
}
else
{
_plan._process.EnsureOverflowReleased(row);
RemoteRowBody body = new RemoteRowBody();
body.Data = row.AsPhysical;
RemoteProposeData proposeData = _cursor.Default(body, columnName, _plan._process.GetProcessCallInfo());
_plan._programStatisticsCached = false;
if (proposeData.Success)
{
row.ValuesOwned = false; // do not clear the overflow streams because the row is effectively owned by the server for the course of the default call.
row.AsPhysical = proposeData.Body.Data;
row.ValuesOwned = true;
}
return(proposeData.Success);
}
}
protected override string GetDeviceIndexesExpression(TableVar tableVar)
{
return
(String.Format
(
@"
select
t.table_schem as ""TableSchema"",
t.table_name as ""TableName"",
i.index_name as ""IndexName"",
i.column_name as ""ColumnName"",
c.ordinal_position as ""OrdinalPosition"",
case i.non_unique when 0 then 1 else 0 end as ""IsUnique"",
case i.asc_or_desc when 'D' then 1 else 0 end as ""IsDescending""
from tables t join tablestatistics i on t.table_name = i.table_name
where t.table_type = 'TABLE' and i.index_name is not null {0}
order by t.table_name, i.index_name, i.ordinal_position;
" ,
tableVar == null ? String.Empty : String.Format("and t.table_name = '{0}'", ToSQLIdentifier(tableVar))
));
}
protected override string GetDeviceTablesExpression(TableVar tableVar)
{
return
(DeviceTablesExpression != String.Empty
? base.GetDeviceTablesExpression(tableVar)
:
String.Format
(
@"
select
so.""schema"" as TableSchema,
so.name as TableName,
sc.name as ColumnName,
sc.colid as OrdinalPosition,
so.name as TableTitle,
sc.name as ColumnTitle,
snt.name as NativeDomainName,
st.usertype as DomainName,
convert(integer, sc.length) as Length,
convert(bit, 0) as IsNullable,
case when snt.name in ('%Library.Text', '%Library.BinaryStream') then 1 else 0 end as IsDeferred
from %TSQL_sys.objects as so
join %TSQL_sys.columns as sc on sc.id = so.id
join %TSQL_sys.types as st on st.usertype = sc.usertype
join %TSQL_sys.types as snt on st.usertype = snt.name
where (so.type = 'U' or so.type = 'V')
{0}
{1}
order by so.""schema"", so.name, sc.colid
" ,
Schema == String.Empty
? String.Empty
: String.Format(@"and so.""schema"" = '{0}'", Schema), // Don't use ToSQLIdentifier, Schema is a string and will be expressed in the target dialect
tableVar == null
? String.Empty
: String.Format("and so.name = '{0}'", ToSQLIdentifier(tableVar))
));
}
protected override string GetDeviceForeignKeysExpression(TableVar tableVar)
{
return
(String.Format
(
DeviceForeignKeysExpression == String.Empty
?
@"
select
tc.table_schema as ConstraintSchema,
tc.constraint_name as ConstraintName,
tc.table_name as SourceTableName,
kcu.column_name as SourceColumnName,
ccu.table_schema as TargetTableSchema,
ccu.table_name as TargetTableName,
ccu.column_name as TargetColumnName,
c.ordinal_position as OrdinalPosition
from information_schema.table_constraints tc
join information_schema.key_column_usage kcu on tc.constraint_name = kcu.constraint_name
join information_schema.constraint_column_usage ccu on ccu.constraint_name = tc.constraint_name
join information_schema.columns c on
c.table_schema = ccu.table_schema
and ccu.table_name = c.table_name
and c.column_name = ccu.column_name
where constraint_type = 'FOREIGN KEY'
and c.table_schema not in ('information_schema','pg_catalog','pg_toast')
{0}
{1}
order by tc.table_schema, tc.constraint_name, c.ordinal_position
"
:
DeviceForeignKeysExpression,
Schema == String.Empty ? String.Empty : String.Format("and tc.table_schema = '{0}'", Schema),
tableVar == null
? String.Empty
: String.Format("and tc.table_name = '{0}'", ToSQLIdentifier(tableVar).ToLower())
));
}
protected override string GetDeviceTablesExpression(TableVar tableVar)
{
return
(String.Format
(
DeviceTablesExpression == String.Empty
?
@"
select
table_schema as TableSchema,
table_name as TableName,
column_name as ColumnName,
ordinal_position as OrdinalPosition,
table_name as TableTitle,
column_name as ColumnTitle,
data_type as NativeDomainName,
data_type as DomainName,
case
when character_maximum_length is not null then character_maximum_length
when numeric_precision is not null then numeric_precision
when datetime_precision is not null then datetime_precision
else 0
end as Length,
case when is_nullable = 'NO' then 0 else 1 end as IsNullable,
case when data_type in ('text', 'bytea') then 1 else 0 end as IsDeferred
from information_schema.columns
where table_schema not in ('information_schema','pg_catalog','pg_toast')
{0} {1}
order by table_schema, table_name, ordinal_position
"
:
DeviceTablesExpression,
Schema == String.Empty ? String.Empty : String.Format("and table_schema = '{0}'", Schema),
tableVar == null
? String.Empty
: String.Format("and table_name = '{0}'", ToSQLIdentifier(tableVar).ToLower())
));
}
protected override string GetDeviceForeignKeysExpression(TableVar tableVar)
{
return
(String.Format
(
DeviceForeignKeysExpression == String.Empty
?
@"
select
c.owner as ConstraintSchema,
c.constraint_name as ConstraintName,
c.owner as SourceTableSchema,
c.table_name as SourceTableName,
cc.column_name as SourceColumnName,
c.r_owner as TargetTableSchema,
ic.table_name as TargetTableName,
ic.column_name as TargetColumnName,
cc.position as OrdinalPosition
from all_constraints c, all_cons_columns cc, all_ind_columns ic
where c.owner = cc.owner
and c.constraint_name = cc.constraint_name
and c.r_owner = ic.table_owner
and c.r_constraint_name = ic.index_name
and cc.position = ic.column_position
and c.constraint_type = 'R'
{0}
{1}
order by c.owner, c.constraint_name, cc.position
"
:
DeviceForeignKeysExpression,
Schema == String.Empty ? String.Empty : String.Format("and c.owner = '{0}'", Schema),
tableVar == null
? String.Empty
: String.Format("and c.table_name = '{0}'", ToSQLIdentifier(tableVar).ToUpper())
));
}
public override void DetermineDataType(Plan plan)
{
DetermineModifiers(plan);
_dataType = new Schema.TableType();
_tableVar = new Schema.ResultTableVar(this);
_tableVar.Owner = plan.User;
DataType.Columns.Add(new Schema.Column("Path", (Schema.ScalarType)Compiler.ResolveCatalogIdentifier(plan, "System.Platform.FileName", true)));
foreach (Schema.Column column in DataType.Columns)
{
TableVar.Columns.Add(new Schema.TableVarColumn(column));
}
TableVar.Keys.Add(new Schema.Key(new Schema.TableVarColumn[] { TableVar.Columns["Path"] }));
TableVar.DetermineRemotable(plan.CatalogDeviceSession);
Order = Compiler.FindClusteringOrder(plan, TableVar);
// Ensure the order exists in the orders list
if (!TableVar.Orders.Contains(Order))
{
TableVar.Orders.Add(Order);
}
}
/// <summary> Returns a DataTable that describes the column metadata of the IDataReader. </summary>
/// <remarks>
/// The following Dataphor tags can be used to override column attributes.
/// DAEDataReader.ColumnSize : The maximum possible length of a value in the column.
/// The default is System.Int32.MaxValue.
/// DAEDataReader.IsReadOnly : True if the column can be modified; otherwise false. The default is false.
/// DAEDataReader.IsLong : True if the column is a (BLOB) otherwise false. The default is false.
/// DAEDataReader.IsAutoIncrement : When true the column assigns values to new rows in fixed increments.
/// Otherwise the column does not assign values to new rows in fixed increments. The default is false.
/// DAEDataReader.NumericPrecision : Only applies to data types of SystemDecimal or descendents of SystemDecimal.
/// The maximum precision of the column. The default is 28.
/// DAEDataReader.NumericScale : Only applies to data types of SystemDecimal or descendents of SystemDecimal.
/// The number of digits to the right of the decimal point. The default is 4.
/// </remarks>
/// <returns> A DataTable that describes the column metadata. </returns>
public override DataTable GetSchemaTable()
{
DataTable result = new DataTable("SchemaTable");
result.Columns.Add(CreateSchemaColumn("ColumnName", typeof(String)));
result.Columns.Add(CreateSchemaColumn("ColumnOrdinal", typeof(Int32)));
result.Columns.Add(CreateSchemaColumn("ColumnSize", typeof(Int32)));
result.Columns.Add(CreateSchemaColumn("NumericPrecision", typeof(Int32)));
result.Columns.Add(CreateSchemaColumn("NumericScale", typeof(Int32)));
result.Columns.Add(CreateSchemaColumn("DataType", typeof(Type)));
result.Columns.Add(CreateSchemaColumn("ProviderType", typeof(Type)));
result.Columns.Add(CreateSchemaColumn("IsLong", typeof(Boolean)));
result.Columns.Add(CreateSchemaColumn("AllowDBNull", typeof(Boolean)));
result.Columns.Add(CreateSchemaColumn("IsReadOnly", typeof(Boolean)));
result.Columns.Add(CreateSchemaColumn("IsRowVersion", typeof(Boolean)));
result.Columns.Add(CreateSchemaColumn("IsUnique", typeof(Boolean)));
result.Columns.Add(CreateSchemaColumn("IsKeyColumn", typeof(Boolean)));
result.Columns.Add(CreateSchemaColumn("IsAutoIncrement", typeof(Boolean)));
result.Columns.Add(CreateSchemaColumn("BaseSchemaName", typeof(String)));
result.Columns.Add(CreateSchemaColumn("BaseCatalogName", typeof(String)));
result.Columns.Add(CreateSchemaColumn("BaseTableName", typeof(String)));
result.Columns.Add(CreateSchemaColumn("BaseColumnName", typeof(String)));
TableType type = (TableType)_cursor.Plan.DataType;
TableVar var = _cursor.Plan.TableVar;
DataTypes dataTypes = _cursor.Plan.Process.DataTypes;
int ordinal = 1;
foreach (TableVarColumn sourceColumn in var.Columns)
{
DataRow targetRow = result.NewRow();
targetRow["ColumnName"] = sourceColumn.Name;
targetRow["ColumnOrdinal"] = ordinal;
//If only it were so simple.
targetRow["ColumnSize"] = GetColumnSize(sourceColumn);
if (sourceColumn.DataType.Is(_cursor.Plan.Process.DataTypes.SystemDecimal))
{
targetRow["NumericPrecision"] = GetNumericPrecision(sourceColumn);
targetRow["NumericScale"] = GetNumericScale(sourceColumn);
}
else
{
targetRow["NumericPrecision"] = DBNull.Value;
targetRow["NumericScale"] = DBNull.Value;
}
Type nativeType = (Type)_nativeTypes[ordinal - 1];
targetRow["DataType"] = nativeType;
targetRow["ProviderType"] = sourceColumn.DataType;
targetRow["IsLong"] = GetIsLong(sourceColumn, dataTypes);
// No way to determine whether the column is read-only at this point (read only to Dataphor means that some operator will reject an update)
targetRow["IsReadOnly"] = GetIsReadOnly(sourceColumn);
bool isUnique = false;
bool isKeyColumn = false;
foreach (Key key in var.Keys)
{
if (key.Columns.Contains(sourceColumn.Name))
{
isKeyColumn = true;
if (key.Columns.Count == 1)
{
isUnique = true;
break;
}
}
}
// IsUnique only makes sense at the column level if a key is composed
// of exclusively this column... so look for that.
targetRow["IsUnique"] = isUnique;
// Whatever "row version" means... according to the SqlReader IsRowVersion equals IsUnique.
targetRow["IsRowVersion"] = isUnique;
// Look for a key which contains this column
targetRow["IsKeyColumn"] = isKeyColumn;
// Allow for DAE provider.
targetRow["AllowDBNull"] = !isUnique;
// If only it was so simple :-)
targetRow["IsAutoIncrement"] = GetIsAutoIncrement(sourceColumn);
// Doesn't really map well into Dataphor's metaphor
targetRow["BaseSchemaName"] = DBNull.Value;
targetRow["BaseCatalogName"] = DBNull.Value;
// Once again these are overly simplistic as this column could come from any mapping into the base tables
targetRow["BaseTableName"] = DBNull.Value;
result.Rows.Add(targetRow);
ordinal++;
}
return(result);
}
public TableVarEventHandlers(TableVar tableVar) : base()
{
_tableVar = tableVar;
}
public UpdateOperation(TableVar tableVar, IRow oldRow, IRow newRow, BitArray valueFlags) : base(tableVar)
{
_oldRow = oldRow;
_newRow = newRow;
_valueFlags = valueFlags;
}
// TODO: Compile row types for each index, saving column indexes to prevent the need for lookup during insert, update, and delete.
private void InternalInitialize(ServerProcess AProcess)
{
Schema.RowType LKeyRowType;
Schema.RowType LDataRowType;
// Create the indexes required to store data as described by the given table type
// Determine Fanout, Capacity, Clustering Key, KeyLength, DataLength
FClusteringKey = TableVar.FindClusteringOrder(AProcess.Plan);
//Schema.Key LClusteringKey = TableVar.FindClusteringKey();
//FClusteringKey = new Schema.Order(LClusteringKey, AProcess.Plan);
LKeyRowType = new Schema.RowType(FClusteringKey.Columns);
LDataRowType = new Schema.RowType();
foreach (Schema.Column LColumn in TableVar.DataType.Columns)
{
if (!FClusteringKey.Columns.Contains(LColumn.Name))
{
LDataRowType.Columns.Add(new Schema.Column(LColumn.Name, LColumn.DataType));
}
}
// Add an internal identifier for uniqueness of keys in nonunique indexes
#if USEINTERNALID
FInternalIDColumn = new Schema.TableVarColumn(new Schema.Column(CInternalIDColumnName, AProcess.Plan.Catalog.DataTypes.SystemGuid), Schema.TableVarColumnType.InternalID);
LDataRowType.Columns.Add(FInternalIDColumn.Column);
#endif
// Create the Clustered index
FClusteredIndex =
new TableBufferIndex
(
AProcess,
FClusteringKey,
LKeyRowType,
LDataRowType,
true,
FFanout,
FCapacity
);
Indexes.Add(FClusteredIndex);
// DataLength and DataColumns for all non clustered indexes is the key length and columns of the clustered key
LDataRowType = LKeyRowType;
// Create non clustered indexes for each key and order (unique sets)
Schema.Order LKey;
foreach (Schema.Key LNonClusteredKey in TableVar.Keys)
{
if (!FClusteringKey.Includes(LNonClusteredKey))
{
LKey = new Schema.Order(LNonClusteredKey, AProcess.Plan);
if (!Indexes.Contains(LKey))
{
LKeyRowType = new Schema.RowType(LKey.Columns);
Indexes.Add
(
new TableBufferIndex
(
AProcess,
LKey,
LKeyRowType,
LDataRowType,
false,
FFanout,
FCapacity
)
);
}
}
}
foreach (Schema.Order LOrder in TableVar.Orders)
{
// This is a potentially non-unique index, so add a GUID to ensure uniqueness of the key in the BTree
LKey = new Schema.Order(LOrder);
#if USEINTERNALID
if (!LKey.Includes(LClusteringKey))
{
Schema.OrderColumn LUniqueColumn = new Schema.OrderColumn(FInternalIDColumn, true);
LUniqueColumn.Sort = ((Schema.ScalarType)LUniqueColumn.Column.DataType).GetUniqueSort(AProcess.Plan);
LKey.Columns.Add(LUniqueColumn);
}
#endif
if (!Indexes.Contains(LKey))
{
LKeyRowType = new Schema.RowType(LKey.Columns);
Indexes.Add
(
new TableBufferIndex
(
AProcess,
LKey,
LKeyRowType,
LDataRowType,
false,
FFanout,
FCapacity
)
);
}
}
}
protected override string GetDeviceIndexesExpression(TableVar ATableVar)
{
switch (Product)
{
default:
case DB2.DB2TextEmitter.UDB: // UDB is the default
return
(String.Format
(
DeviceIndexesExpression == String.Empty ?
@"select i.tabschema as TableSchema,
i.tabname as TableName,
i.indname as IndexName,
ic.colname as ColumnName,
ic.colseq as OrdinalPosition,
case when i.uniquerule in ('P', 'U') then 1 else 0 end as IsUnique,
case when ic.colorder = 'D' then 1 else 0 end as IsDescending
from syscat.indexes i join syscat.tables t on i.tabschema = t.tabschema and i.tabname = t.tabname
join syscat.indexcoluse ic on i.indschema = ic.indschema and i.indname = ic.indname
where (t.type = 'T' or t.type = 'V')
and t.tabschema = {0}
{1}
order by TableName, i.iid, OrdinalPosition;
" :
DeviceIndexesExpression,
Schema == String.Empty ? "current schema" : String.Format("'{0}'", Schema),
ATableVar == null ? String.Empty : String.Format("and t.tabname = '{0}'", ToSQLIdentifier(ATableVar))
));
case DB2.DB2TextEmitter.ISeries:
return
(String.Format
(
DeviceIndexesExpression == String.Empty ?
@"
select
I.TABLE_SCHEMA as ""TableSchema"",
I.TABLE_NAME as ""TableName"",
I.INDEX_NAME as ""IndexName"",
K.COLUMN_NAME as ""ColumnName"",
K.ORDINAL_POSITION as ""OrdinalPosition"",
case when I.IS_UNIQUE = 'U' then 1 else 0 end as ""IsUnique"",
case when K.ORDERING = 'A' then 0 else 1 end as ""IsDescending""
from QSYS2.SYSTABLES T
join QSYS2.SYSINDEXES I
on T.TABLE_SCHEMA = I.TABLE_SCHEMA
and T.TABLE_NAME = I.TABLE_NAME
join QSYS2.SYSKEYS K
on I.INDEX_SCHEMA = K.INDEX_SCHEMA
and I.INDEX_NAME = K.INDEX_NAME
where T.TABLE_TYPE in ('T', 'P')
and T.TABLE_SCHEMA = '{0}'
{1}
union
select
K.TABLE_SCHEMA as ""TableSchema"",
K.TABLE_NAME as ""TableName"",
K.CONSTRAINT_NAME as ""IndexName"",
C.COLUMN_NAME as ""ColumnName"",
C.ORDINAL_POSITION as ""OrdinalPosition"",
1 as ""IsUnique"",
0 as ""IsDescending""
from QSYS2.SYSTABLES T
join QSYS2.SYSCST K
on T.TABLE_SCHEMA = K.TABLE_SCHEMA
and T.TABLE_NAME = K.TABLE_NAME
join QSYS2.SYSKEYCST C
on K.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA
and K.CONSTRAINT_NAME = C.CONSTRAINT_NAME
where T.TABLE_TYPE in ('T', 'P')
and K.CONSTRAINT_TYPE = 'PRIMARY KEY'
and T.TABLE_SCHEMA = '{0}'
{1}
order by ""TableSchema"", ""TableName"", ""OrdinalPosition""
" :
DeviceIndexesExpression,
Schema,
ATableVar == null ? String.Empty : String.Format("and \"TableName\" = '{0}'", ToSQLIdentifier(ATableVar))
));
}
}
protected override string GetDeviceTablesExpression(TableVar ATableVar)
{
switch (Product)
{
default:
case DB2.DB2TextEmitter.UDB:
return
(String.Format
(
DeviceTablesExpression == String.Empty ?
@"
select
c.tabschema as TableSchema,
c.tabname as TableName,
c.colname as ColumnName,
c.colno as OrdinalPosition,
c.tabname as TableTitle,
c.colname as ColumnTitle,
c.typename as NativeDomainName,
c.typename as DomainName,
c.length as Length,
case when c.nulls = 'Y' then 1 else 0 end as IsNullable,
case when c.typename in ('CLOB', 'BLOB') then 1 else 0 end as IsDeferred
from syscat.columns c join syscat.tables t on c.tabschema = t.tabschema and c.tabname = t.tabname
where (t.type = 'T' or t.type = 'V')
and t.tabschema = {0}
{1}
order by TableSchema, TableName, OrdinalPosition;
" :
DeviceTablesExpression,
Schema == String.Empty ? "current schema" : String.Format(@"'{0}'", Schema),
ATableVar == null ? String.Empty : String.Format("and t.tabname = '{0}'", ToSQLIdentifier(ATableVar))
));
case DB2.DB2TextEmitter.ISeries:
return
(String.Format
(
DeviceTablesExpression == String.Empty ?
@"select C.TABLE_SCHEMA as ""TableSchema"",
C.TABLE_NAME as ""TableName"",
C.COLUMN_NAME as ""ColumnName"",
C.ORDINAL_POSITION as ""OrdinalPosition"",
case when T.TABLE_TEXT is null then T.TABLE_NAME else T.TABLE_TEXT end as ""TableTitle"",
case when C.COLUMN_TEXT is null then C.COLUMN_NAME else C.COLUMN_TEXT end as ""ColumnTitle"",
C.DATA_TYPE as ""NativeDomainName"",
C.DATA_TYPE as ""DomainName"",
C.LENGTH as ""Length"",
case when C.IS_NULLABLE = 'Y' then 1 else 0 end as ""IsNullable"",
case when C.DATA_TYPE in ('BLOB', 'CLOB') then 1 else 0 end as ""IsDeferred""
from QSYS2.SYSTABLES as T
join QSYS2.SYSCOLUMNS as C
on T.TABLE_SCHEMA = C.TABLE_SCHEMA
and T.TABLE_NAME = C.TABLE_NAME
where T.TABLE_TYPE in ('T', 'P')
and T.TABLE_SCHEMA = '{0}'
{1}
order by ""TableSchema"", ""TableName"", ""OrdinalPosition""
" :
DeviceTablesExpression,
Schema,
ATableVar == null ? String.Empty : String.Format("and \"TableName\" = '{0}'", ToSQLIdentifier(ATableVar))
));
}
}
protected override string GetDeviceTablesExpression(TableVar tableVar)
{
return
(DeviceTablesExpression != String.Empty
?
base.GetDeviceTablesExpression(tableVar)
:
String.Format
(
(
_majorVersion < 9
?
@"
select
su.name as TableSchema,
so.name as TableName,
sc.name as ColumnName,
sc.colorder as OrdinalPosition,
so.name as TableTitle,
sc.name as ColumnTitle,
snt.name as NativeDomainName,
st.name as DomainName,
convert(integer, sc.length) as Length,
sc.isnullable as IsNullable,
case when snt.name in ('text', 'ntext', 'image') then 1 else 0 end as IsDeferred
from sysobjects as so
join sysusers as su on so.uid = su.uid
join syscolumns as sc on sc.id = so.id
join systypes as st on st.xusertype = sc.xusertype
join systypes as snt on st.xtype = snt.xusertype
where (so.xtype = 'U' or so.xtype = 'V')
and OBJECTPROPERTY(so.id, 'IsMSShipped') = 0
{0}
{1}
order by su.name, so.name, sc.colid
"
:
@"
select
ss.name as TableSchema,
so.name as TableName,
sc.name as ColumnName,
sc.column_id as OrdinalPosition,
so.name as TableTitle,
sc.name as ColumnTitle,
snt.name as NativeDomainName,
st.name as DomainName,
convert(integer, sc.max_length) as Length,
sc.is_nullable as IsNullable,
case when snt.name in ('text', 'ntext', 'image') then 1 else 0 end as IsDeferred
from sys.objects as so
join sys.schemas ss on so.schema_id = ss.schema_id
join sys.columns as sc on sc.object_id = so.object_id
join sys.types as st on st.user_type_id = sc.user_type_id
join sys.types as snt on st.system_type_id = snt.system_type_id
where (so.type = 'U' or so.type = 'V')
and so.is_ms_shipped = 0
{0}
{1}
order by ss.name, so.name, sc.column_id
"
),
tableVar == null
? String.Empty
: String.Format("and so.name = '{0}'", ToSQLIdentifier(tableVar)),
_shouldReconcileRowGUIDCol
? String.Empty
:
(
_majorVersion < 9
? "and COLUMNPROPERTY(so.id, sc.name, 'IsRowGUIDCol') = 0"
: "and sc.is_rowguidcol = 0"
)
));
}
protected override string GetDeviceForeignKeysExpression(TableVar tableVar)
{
return
(DeviceForeignKeysExpression != String.Empty
?
base.GetDeviceForeignKeysExpression(tableVar)
:
String.Format
(
_majorVersion < 9
?
@"
select
su.name as ConstraintSchema,
so.name as ConstraintName,
ssu.name as SourceTableSchema,
sso.name as SourceTableName,
ssc.name as SourceColumnName,
tsu.name as TargetTableSchema,
tso.name as TargetTableName,
tsc.name as TargetColumnName,
keyno OrdinalPosition
from sysforeignkeys as sfk
join sysobjects as so on sfk.constid = so.id
join sysusers as su on su.uid = so.uid
join sysobjects as sso on sfk.fkeyid = sso.id
join sysusers as ssu on ssu.uid = sso.uid
join syscolumns as ssc on ssc.colid = sfk.fkey and ssc.id = sfk.fkeyid
join sysobjects as tso on sfk.rkeyid = tso.id
join sysusers as tsu on tsu.uid = tso.uid
join syscolumns as tsc on tsc.colid = sfk.rkey and tsc.id = sfk.rkeyid
where 1 = 1
{0}
order by ConstraintSchema, ConstraintName, OrdinalPosition
"
:
@"
select
ss.name as ConstraintSchema,
so.name as ConstraintName,
sss.name as SourceTableSchema,
sso.name as SourceTableName,
ssc.name as SourceColumnName,
tss.name as TargetTableSchema,
tso.name as TargetTableName,
tsc.name as TargetColumnName,
sfkc.constraint_column_id OrdinalPosition
from sys.foreign_keys as sfk
join sys.foreign_key_columns as sfkc on sfk.object_id = sfkc.constraint_object_id
join sys.objects as so on sfk.object_id = so.object_id
join sys.schemas as ss on ss.schema_id = so.schema_id
join sys.objects as sso on sfk.parent_object_id = sso.object_id
join sys.schemas as sss on sss.schema_id = sso.schema_id
join sys.columns as ssc on ssc.column_id = sfkc.parent_column_id and ssc.object_id = sfk.parent_object_id
join sys.objects as tso on sfk.referenced_object_id = tso.object_id
join sys.schemas as tss on tso.schema_id = tss.schema_id
join sys.columns as tsc on tsc.column_id = sfkc.referenced_column_id and tsc.object_id = sfk.referenced_object_id
where 1 = 1
{0}
order by ConstraintSchema, ConstraintName, OrdinalPosition
" ,
tableVar == null ? String.Empty : "and sso.name = '" + ToSQLIdentifier(tableVar) + "'"
));
}
protected override string GetDeviceIndexesExpression(TableVar tableVar)
{
return
(DeviceIndexesExpression != String.Empty
?
base.GetDeviceIndexesExpression(tableVar)
:
(
_majorVersion < 9
?
String.Format
(
@"
select
su.name as TableSchema,
so.name as TableName,
si.name as IndexName,
sc.name as ColumnName,
sik.keyno as OrdinalPosition,
INDEXPROPERTY(so.id, si.name, 'IsUnique') as IsUnique,
{0} /* if ServerVersion >= 8.0, otherwise 0 as IsDescending */
from sysobjects as so
join sysusers as su on so.uid = su.uid
join sysindexes as si on si.id = so.id
join sysindexkeys as sik on sik.id = so.id and sik.indid = si.indid
join syscolumns as sc on sc.id = so.id and sc.colid = sik.colid
where (so.xtype = 'U' or so.xtype = 'V')
and OBJECTPROPERTY(so.id, 'isMSShipped') = 0
and INDEXPROPERTY(so.id, si.name, 'IsStatistics') = 0
{1} /* if ServerVersion >= 8.0, otherwise empty string */
{2}
order by su.name, so.name, si.indid, sik.keyno
" ,
IsMSSQL70
? "0 as IsDescending"
: "INDEXKEY_PROPERTY(so.id, si.indid, sik.keyno, 'IsDescending') as IsDescending",
IsMSSQL70
? String.Empty
: "and INDEXKEY_PROPERTY(so.id, si.indid, sik.keyno, 'IsDescending') is not null",
tableVar == null
? String.Empty
: String.Format("and so.name = '{0}'", ToSQLIdentifier(tableVar))
)
:
String.Format
(
@"
select
ss.name as TableSchema,
so.name as TableName,
si.name as IndexName,
sc.name as ColumnName,
sik.key_ordinal as OrdinalPosition,
si.is_unique IsUnique,
sik.is_descending_key IsDescending
from sys.objects as so
join sys.schemas as ss on so.schema_id = ss.schema_id
join sys.indexes as si on si.object_id = so.object_id
join sys.index_columns as sik on sik.object_id = so.object_id and sik.index_id = si.index_id
join sys.columns as sc on sc.object_id = so.object_id and sc.column_id = sik.column_id
where (so.type = 'U' or so.type = 'V')
and so.is_ms_shipped = 0
and INDEXPROPERTY(so.object_id, si.name, 'IsStatistics') = 0
{0}
order by ss.name, so.name, si.index_id, sik.key_ordinal
" ,
tableVar == null
? String.Empty
: String.Format("and so.name = '{0}'", ToSQLIdentifier(tableVar))
)
));
}
