public override void Format(StringBuilder ddl, DBSchema schema, DDLType ddlType)
{
if (ddlType == DDLType.Create)
{
var dataFile = Path.Combine(schema.Connection.Path, $"{schema.DataBase}.mdf");
ddl.AppendLine($"create database {schema.DataBase}");
ddl.AppendLine("on");
ddl.AppendLine($"(name = {schema.DataBase}_dat,");
ddl.AppendLine($"filename = '{dataFile}',");
ddl.AppendLine("size = 10, maxsize = unlimited, filegrowth = 5MB);");
ddl.AppendLine($"alter database {schema.DataBase} set recovery simple;");
if (!schema.Sequences.Contains("db_lob_seq"))
{
var lobSequence = new DBSequence("db_lob_seq");
schema.Sequences.Add(lobSequence);
Format(ddl, lobSequence, DDLType.Create);
}
ddl.AppendLine($"create table [db_lob]([oid] bigint not null primary key, [lob_data] varbinary(max));");
}
else if (ddlType == DDLType.Drop)
{
//ddl.AppendLine($"alter database {schema.DataBase} remove file {schema.DataBase}_dat;");
ddl.AppendLine($"drop database {schema.DataBase};");
}
}
public override void Format(StringBuilder ddl, DBSchema schema, DDLType ddlType)
{
string tsname = $"ts{schema.Name}";
if (ddlType == DDLType.Create)
{
ddl.Append($"begin\n");
ddl.Append($" execute immediate 'create tablespace {tsname} datafile ''{tsname}.dat'' size 10M autoextend on';\n");
ddl.Append($" execute immediate 'create temporary tablespace {tsname}_temp tempfile ''{tsname}_temp.dat'' size 5M autoextend on';\n");
ddl.Append($" execute immediate 'create user {schema.Name} identified by {schema.Name} default tablespace {tsname} temporary tablespace {tsname}_temp';\n");
ddl.Append($" execute immediate 'grant create session to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create table to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create view to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create sequence to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant create any procedure to {schema.Name}';\n");
ddl.Append($" execute immediate 'grant unlimited tablespace to {schema.Name}';\n");
if (!schema.Sequences.Contains("db_lob_seq"))
{
var lobSequence = new DBSequence("db_lob_seq");
schema.Sequences.Add(lobSequence);
Format(ddl, lobSequence, DDLType.Create);
}
ddl.AppendLine($"create table db_lob(oid number(18) not null primary key, lob_data blob);");
ddl.Append($"end;");
}
else if (ddlType == DDLType.Drop)
{
ddl.Append($"begin\n");
ddl.Append($" execute immediate 'drop user {schema.Name} CASCADE';\n");
ddl.Append($" execute immediate 'drop tablespace {tsname} including contents and datafiles';\n");
ddl.Append($" execute immediate 'drop tablespace {tsname}_temp including contents and datafiles';\n");
ddl.Append($"end;");
}
}
public override void Format(StringBuilder ddl, DBSequence sequence, DDLType ddlType)
{
switch (ddlType)
{
case DDLType.Create:
ddl.AppendLine($"insert into db_sequence(name, seq) values('{sequence.Name}', {sequence.Current - 1});");
break;
case DDLType.Alter:
ddl.AppendLine($"update db_sequence set seq = {sequence.Current} where name = '{sequence.Name}';");
break;
case DDLType.Drop:
ddl.AppendLine($"delete from db_sequence where name = '{sequence.Name}';");
break;
}
}
public override void Format(StringBuilder ddl, DBSequence sequence, DDLType ddlType)
{
switch (ddlType)
{
case DDLType.Create:
ddl.AppendLine($"create sequence {sequence.Name} as {FormatType(sequence.DBDataType, sequence.Size, sequence.Scale)} start with {sequence.Current} increment by {sequence.Increment};");
break;
case DDLType.Alter:
ddl.AppendLine($"alter sequence {sequence.Name} restart with {sequence.Current} increment by {sequence.Increment};");
break;
case DDLType.Drop:
ddl.AppendLine($"drop sequence {sequence.Name};");
break;
}
}
public override void Format(StringBuilder ddl, DBSequence sequence, DDLType ddlType)
{
switch (ddlType)
{
case DDLType.Create: //if not exists pg 9.5 or above
ddl.AppendLine($"create sequence {sequence.Name} start with {sequence.Current} increment by {sequence.Increment}");
break;
case DDLType.Alter:
ddl.AppendLine($"alter sequence {sequence.Name} restart with {sequence.Current + 1} increment by {sequence.Increment}");
break;
case DDLType.Drop:
ddl.AppendLine($"drop sequence {sequence.Name}");
break;
}
}
public override string SequenceCurrentValue(DBSequence sequence)
{
return($"select seq from db_sequence where name = '{sequence.Name}';");
}
public override string SequenceInline(DBSequence sequence)
{
return($"nextval('{sequence.Name}')");
}
public override string SequenceNextValue(DBSequence sequence)
{
return($"select nextval('{sequence.Name}')");
}
public override string SequenceCurrentValue(DBSequence sequence)
{
return($"select last_value from {sequence.Name}");
}
public override string SequenceCurrentValue(DBSequence sequence)
{
return($@"select current_value from sys.sequences where name = '{sequence.Name}'");
}
public override void Format(StringBuilder ddl, DBSequence sequence, DDLType ddlType)
{
throw new NotImplementedException();
}
public override string SequenceNextValue(DBSequence sequence)
{
return($@"update db_sequence set seq = seq + {sequence.Increment} where name = '{sequence.Name}';
select seq from db_sequence where name = '{sequence.Name}';");
}
public override string SequenceInline(DBSequence sequence)
{
return($"{sequence.Name}.nextval");
}
public override string SequenceNextValue(DBSequence sequence)
{
return($"select {sequence.Name}.nextval from dual");
}
public override string SequenceCurrentValue(DBSequence sequence)
{
return($"select {sequence.Name}.currval from dual");
}
public override string SequenceCurrentValue(DBSequence sequence)
{
return($"select currval('{sequence.Name}')");
}
public override string SequenceNextValue(DBSequence sequence)
{
throw new NotImplementedException();
}
public override string SequenceInline(DBSequence sequence)
{
throw new NotSupportedException();
}
public override string SequenceInline(DBSequence sequence)
{
return($"next value for {sequence.Name}");
}