public void CreateTable( string schemaName, string tableName, ColInfo cols, string definition, bool isView, bool alter, Exec e )
{
Schema schema = GetSchema( schemaName, true, e );
bool isView1; string definition1;
long tableId = tableId = ReadTableId( schema, tableName, out isView1, out definition1 );
if ( alter )
{
if ( tableId == 0 || !isView1 ) e.Error( "View does not exist" );
}
else if ( Normal && schema.GetCachedTable( tableName ) != null || tableId >= 0 )
e.Error( "Table/View " + schemaName + "." + tableName + " already exists" );
var r = new RowCursor( SysTable );
r.V[1].L = schema.Id;
r.V[2].O = tableName;
r.V[3].L = isView ? 1 : 0;
r.V[4].O = isView ? definition.Trim() : "";
if ( alter ) r.Update( tableId ); else tableId = r.Insert();
if ( !isView) SaveColumns( tableId, cols );
if ( isView ) ResetCache();
}
public void DropView( string schemaName, string name, Exec e )
{
Schema schema = GetSchema( schemaName, true, e );
bool isView; string definition;
long vid = ReadTableId( schema, name, out isView, out definition );
if ( vid < 0 ) e.Error( schemaName + "." + name + " not found" );
if ( !isView ) e.Error( schemaName + "." + name + " is not a view" );
Sql( "DELETE FROM sys.Table WHERE Id = " + vid );
ResetCache();
}
public override void CheckNames( Exec e )
{
var set = new G.HashSet<string>();
for ( int i = 0; i < ColumnCount; i += 1 )
{
string name = Exps[ i ].Name;
if ( name == "" ) e.Error( "Unnamed expression" );
else if ( set.Contains(name) ) e.Error( "Duplicate name: " + name );
set.Add( name );
}
}
public void AlterTable( string schemaName, string tableName, G.List<AlterAction> alist, Exec e )
{
Table t = (Table) GetTable( schemaName, tableName, e );
var names = new G.List<string>( t.Cols.Names );
var types = new G.List<DataType>( t.Cols.Types );
var map = new G.List<int>();
for ( int i = 0; i < names.Count; i += 1 ) map.Add( i );
foreach ( AlterAction aa in alist )
{
int ix = names.IndexOf( aa.Name );
if ( aa.Operation != Action.Add && ix == -1 )
e.Error( "Column " + aa.Name + " not found" );
switch ( aa.Operation )
{
case Action.Add:
if ( ix != -1 ) e.Error( "Column " + aa.Name + " already exists" );
names.Add( aa.Name );
types.Add( aa.Type );
map.Add( 0 );
Sql( "INSERT INTO sys.Column( Table, Name, Type ) VALUES ( " + t.TableId + "," + Util.Quote(aa.Name) + "," + (int)aa.Type + ")" );
break;
case Action.Drop:
names.RemoveAt( ix );
types.RemoveAt( ix );
map.RemoveAt( ix );
Sql( "DELETE FROM sys.Column WHERE Table = " + t.TableId + " AND Name = " + Util.Quote(aa.Name) );
Sql( "EXEC sys.DroppedColumn(" + t.TableId + "," + ix + ")" );
break;
case Action.ColumnRename:
names.RemoveAt( ix );
names.Insert( ix, aa.NewName );
Sql( "UPDATE sys.Column SET Name = " + Util.Quote(aa.NewName) + " WHERE Table=" + t.TableId + " AND Name = " + Util.Quote(aa.Name) );
break;
case Action.Modify:
if ( DTI.Base( aa.Type ) != DTI.Base( types[ ix ] ) )
e.Error( "Modify cannot change base type" );
if ( DTI.Scale( aa.Type ) != DTI.Scale( types[ ix ] ) )
e.Error( "Modify cannot change scale" );
types.RemoveAt( ix );
types.Insert( ix, aa.Type );
Sql( "UPDATE sys.Column SET Type = " + (int)aa.Type + " WHERE Table=" + t.TableId + " AND Name = " + Util.Quote(aa.Name) );
Sql( "EXEC sys.ModifiedColumn(" + t.TableId + "," + ix + ")" );
break;
}
}
var newcols = ColInfo.New( names, types );
t.AlterData( newcols, map.ToArray() );
Sql( "EXEC sys.RecreateModifiedIndexes()" );
t.OpenIndexes();
ResetCache();
}
public Table GetTable( string schemaName, string name, Exec e )
{
var result = GetTableOrView( schemaName, name, e );
if ( result is Table ) return (Table)result;
e.Error( name + " is a view not a table" );
return null;
}
// RENAME
public void RenameSchema( string sch, string sch1, Exec e )
{
Schema schema = GetSchema( sch, true, e );
if ( GetSchemaId( sch1, false, e ) >= 0 ) e.Error( "Schema already exists : " + sch1 );
Sql( "UPDATE FROM sys.Schema SET Name=" + sch1 + " WHERE Id = " + schema.Id );
ResetCache();
}
public void CheckLabelsDefined(Exec e)
{
if (LabelUndefined != 0)
{
e.Error("Undefined Goto Label");
}
}
// Schema methods Create/Drop Schema, Table, View, Function, Procedure, Index etc.
public void CreateSchema( string schemaName, Exec e )
{
if ( Normal && GetSchemaId( schemaName, false, e ) >= 0 )
e.Error( "Schema already exists : " + schemaName );
var r = new RowCursor( SysSchema );
r.V[1].O = schemaName;
r.Insert();
}
public EXCEPTION(G.List <Exp> parms, Exec e)
{
if (parms.Count != 0)
{
e.Error("EXCEPTION takes no parameters");
}
Type = DataType.String;
}
public LASTID(G.List <Exp> parms, Exec e)
{
if (parms.Count != 0)
{
e.Error("LASTID takes no parameters");
}
Type = DataType.Bigint;
}
public LEN(G.List <Exp> args, Exec e)
{
if (args.Count != 1)
{
e.Error(this + "takes one argument");
}
E = args[0];
Type = DataType.Bigint;
}
public StdExp(G.List <Exp> arg, DataType type, DataType [] types, Exec e)
{
Arg = arg.ToArray();
Type = type;
Types = types;
if (Arg.Length != Types.Length)
{
e.Error(this + " takes " + Types.Length + " argument(s)");
}
}
public override void Convert( DataType [] types, Exec e )
{
for ( int i = 0; i < types.Length; i += 1 )
{
if ( !Convert( i, types[ i ] ) )
{
e.Error( "Data type conversion error to " + DTI.Name( types[ i ] ) );
}
}
}
public override void Convert( DataType [] types, Exec e )
{
for ( int i = 0; i < types.Length; i += 1 )
{
DataType assignType = types[ i ];
Exp conv = Exps[i].Convert( assignType );
if ( conv == null ) e.Error( "Assign data type error" );
Exps[i] = conv;
}
}
public override void Convert( DataType [] types, Exec e )
{
for ( int i = 0; i < types.Length; i += 1 )
{
DataType assignType = types[ i ];
Exp conv = Exps[ i ].Convert( assignType );
if ( conv == null ) e.Error( "Assign data type error" );
Exps[ i ] = conv;
}
Dvs = Util.GetDVList( Exps.ToArray() ); // Not very elegant that this operation is done twice.
}
public void DropIndex( string schemaName, string tableName, string indexName, Exec e )
{
Table t = (Table)GetTable( schemaName, tableName, e );
long tid = t.TableId;
long indexId = GetIndexId( tid, indexName );
if ( indexId == 0 ) e.Error( "Index not found" );
t.CloseAndDeleteIndex( indexId );
Sql( "DELETE FROM sys.IndexCol WHERE Table = " + tid + " AND Index=" + indexId );
Sql( "DELETE FROM sys.Index WHERE id=" + indexId);
t.OpenIndexes();
ResetCache();
}
public void DropRoutine( string schemaName, string name, bool func, Exec e )
{
int schemaId = GetSchemaId( schemaName, true, e );
var qname = Util.Quote( name );
string tname = func ? "Function" : "Procedure";
string exists = (string) ScalarSql( "SELECT Name from sys." + tname
+ " where Name=" + qname + " AND Schema=" + schemaId )._O;
if ( exists == null ) e.Error( name + " does not exist" );
Sql( "DELETE FROM sys." + tname + " WHERE Name = " + qname + " AND Schema = " + schemaId );
ResetCache();
}
public int ColumnIx(string name, Exec e)
{
int n = CI.Count;
for (int i = 0; i < n; i += 1)
{
if (CI.Name[i] == name)
{
return(i);
}
}
e.Error("Column " + name + " not found");
return(0);
}
// Stored procedures and functions.
public void CreateRoutine( string schemaName, string name, string definition, bool func, bool alter, Exec e )
{
int schemaId = GetSchemaId( schemaName, true, e );
name = Util.Quote( name );
string tname = func ? "Function" : "Procedure";
string exists = (string) ScalarSql( "SELECT Name from sys." + tname
+ " where Name=" + name + " AND Schema=" + schemaId )._O;
if ( exists != null && !alter ) e.Error( tname + " " + name + " already exists" );
else if ( exists == null && alter ) e.Error( tname + " " + name + " does not exist" );
if ( alter )
{
Sql( "UPDATE sys." + tname + " SET Definition = " + Util.Quote(definition.Trim())
+ " WHERE Name = " + name + " AND Schema = " + schemaId );
}
else
{
Sql( "INSERT INTO sys." + tname + "( Schema, Name, Definition ) VALUES ( "
+ schemaId + "," + name + "," + Util.Quote(definition.Trim()) + ")" );
}
ResetCache();
}
Schema GetSchema( string name, bool mustExist, Exec e )
{
Schema result;
SchemaDict.TryGetValue( name, out result );
if ( result != null ) return result;
int id = ReadSchemaId( name );
if ( id < 0 )
{
if ( mustExist) e.Error( "Schema not found: " + name );
return null;
}
result = new Schema( name, id );
SchemaDict[ name ] = result;
return result;
}
public TableExpression GetTableOrView( string schemaName, string name, Exec e )
{
Schema schema = GetSchema( schemaName, true, e );
TableExpression result = schema.GetCachedTable( name );
if ( result != null ) return result;
bool isView; string definition;
long tid = ReadTableId( schema, name, out isView, out definition );
if ( tid < 0 ) e.Error( schemaName + "." + name + " not found" );
if ( isView )
{
TableExpression te = e.LoadView( definition, schemaName + "." + name );
te.TableId = tid;
schema.TableDict[ name ] = te;
return te;
}
else
{
// Fetch the Column Information from the SysColumn table.
var names = new G.List<string>();
var types = new G.List<DataType>();
names.Add( "Id" ); types.Add( DataType.Bigint ); // Add the pre-defined "id" column.
// Use SysColumnIndex to avoid scanning the entire SysColumn table.
var start = new LongStart( tid );
var r = new RowCursor( SysColumn );
foreach( IndexFileRecord ixr in SysColumnIndex.From( start.Compare, false ) )
{
if ( ixr.Col[0].L == tid )
{
r.Get( ixr.Col[1].L );
names.Add( (string) r.V[2]._O );
types.Add( (DataType)r.V[3].L );
}
else break;
}
Table t = new Table( this, schema, name, ColInfo.New(names,types), tid );
t.OpenIndexes();
return t;
}
}
public Block GetRoutine( string schemaName, string name, bool func, Exec e )
{
Schema schema = GetSchema( schemaName, true, e );
string cname = name + ( func ? "F" : "P" );
// See if routine is cached.
Block result; if( schema.BlockDict.TryGetValue( cname, out result ) ) return result;
string sql = (string) ScalarSql( "SELECT Definition from sys." + (func?"Function":"Procedure")
+ " where Name=" + Util.Quote(name) + " AND Schema=" + schema.Id )._O;
if ( sql == null ) e.Error( "Function not found " + name );
result = SqlExec.LoadRoutine( func, sql, this, schemaName + "." + name );
// System.Console.WriteLine( "Loaded " + schemaName + "." + name );
schema.BlockDict[ cname ] = result;
return result;
}
public void RenameObject( string objtype, string sch, string name, string newsch, string newname, Exec e )
{
Table t = objtype == "TABLE" ? GetTable( sch, name, e ) : null;
string error = (string)ScalarSql( "SELECT sys.RenameObject( " + Util.Quote(objtype) + "," +
Util.Quote(sch) + "," + Util.Quote(name) + "," + Util.Quote(newsch) + "," + Util.Quote(newname) + ")" )._O;
if ( error != "" ) e.Error( error );
else if ( t != null )
{
Schema s = GetSchema( sch, true, e );
Schema ns = GetSchema( newsch, true, e );
s.TableDict.Remove( name );
ns.TableDict[ newname ] = t;
}
else
{
ResetCache();
}
}
// Indexes.
public void CreateIndex( string schemaName, string tableName, string indexName, string [] names, Exec e )
{
Table t = (Table)GetTable( schemaName, tableName, e );
long tid = t.TableId;
long indexId = GetIndexId( tid, indexName );
if ( indexId != 0 ) e.Error( "Index already exists" );
int [] colIx = new int[ names.Length ];
for ( int i=0; i < names.Length; i +=1 )
{
colIx[ i ] = t.ColumnIx( names[i], e );
}
// Create the index.
{
var r = new RowCursor( SysIndex );
r.V[1].L = tid;
r.V[2].O = indexName;
indexId = r.Insert();
}
// Create the index columns.
{
var r = new RowCursor( SysIndexCol );
r.V[1].L = tid;
r.V[2].L = indexId;
for ( int i = 0; i < names.Length; i += 1 )
{
r.V[3].L = colIx[ i ];
r.Insert();
}
}
if ( Normal )
{
t.OpenIndexes();
t.InitIndex( indexId );
}
}