public override string[] GetCreateTableSqls(Table table)
{
var sqls = new List<string>();
var primaryKeyColumns = new List<string>();
//create table myTable (id int not null auto_increment, name VARCHAR(255) not null, primary key(id))
//create table
var sb = new StringBuilder();
sb.Append("create table ").Append(table.Name).AppendLine(" ( ");
int size = table.Columns.Count;
for (int i = 0; i < size; i++) {
sb.Append(GetColumnToSqlWhenCreate(table.Columns[i]));
if (i != size - 1) {
sb.AppendLine(",");
}
if (table.Columns[i].IsPrimaryKey) {
primaryKeyColumns.Add(table.Columns[i].ColumnName);
}
}
//primary keys
if (primaryKeyColumns.Count > 0) {
sb.AppendLine(String.Format(", primary key ({0})", StringHelper.Implode(primaryKeyColumns.ToArray(),",")));
}
sb.AppendLine(")");
sqls.Add(sb.ToString());
return sqls.ToArray();
}
public void ConstructorTest()
{
var table = new Table("name");
Assert.AreEqual("name", table.Name);
Assert.IsNotNull(table.Columns);
Assert.AreEqual(0, table.Columns.Count);
}
public string[] GetCreateTableSqls(Table table, Func<Column, string> getColumnToSqlWhenCreate, Func<string, string, string[], string> getPrimaryKeySql) {
var sqls = new List<string>();
var primaryKeyColumns = new List<string>();
var tableName = table.Name;
Column autoIncrementColumn = null;
//create table
var sb = new StringBuilder();
sb.Append("CREATE TABLE ").Append(table.Name).AppendLine(" (");
var size = table.Columns.Count;
for (var i = 0; i < size; i++) {
var column = table.Columns[i];
sb.Append(getColumnToSqlWhenCreate(column));
if (i != size - 1) {
sb.AppendLine(",");
}
if (column.IsAutoIncrement) {
autoIncrementColumn = column;
}
if (column.IsPrimaryKey) {
primaryKeyColumns.Add(column.ColumnName);
}
}
sb.AppendLine(")");
sqls.Add(sb.ToString());
SetAutoIncrementColumn(autoIncrementColumn, tableName, sqls);
SetPrimaryKey(primaryKeyColumns, sqls, tableName, getPrimaryKeySql);
return sqls.ToArray();
}
public override string[] GetCreateTableSqls(Table table) {
List<string> primaryKeyColumns = new List<string>();
//create table
StringBuilder sb = new StringBuilder();
sb.Append("create table ").Append(table.Name).AppendLine(" ( ");
int size = table.Columns.Count;
for (int i = 0; i < size; i++) {
Column col = table.Columns[i];
sb.Append(GetColumnToSqlWhenCreate(col));
if (col.IsPrimaryKey) {
primaryKeyColumns.Add(col.ColumnName);
}
if (i != size - 1) {
sb.AppendLine(",");
}
}
if (primaryKeyColumns.Count > 1) {
sb.Replace("primary key", "");
sb.AppendFormat(", primary key({0}) ", StringHelper.Implode(primaryKeyColumns.ToArray(), ","));
}
sb.AppendLine(")");
return new[] {sb.ToString()};
}
public override string[] GetCreateTableSqls(Table table) {
var sqls = new List<string>();
var primaryKeyColumns = new List<string>();
//create table
var sb = new StringBuilder();
sb.Append("create table ").Append(table.Name).AppendLine(" ( ");
int size = table.Columns.Count;
for (int i = 0; i < size; i++) {
sb.Append(GetColumnToSqlWhenCreate(table.Columns[i]));
if (i != size - 1) {
sb.AppendLine(",");
}
if (table.Columns[i].IsPrimaryKey) {
primaryKeyColumns.Add(table.Columns[i].ColumnName);
}
}
sb.AppendLine(")");
sqls.Add(sb.ToString());
//primary key
if (primaryKeyColumns.Count > 0) {
sqls.Add(GetPrimaryKeySql(table.Name, String.Format("pk_{0}", table.Name), primaryKeyColumns.ToArray()));
}
return sqls.ToArray();
}
public virtual void AddTable(string tableName, params FluentColumn[] columns) {
var table = new Table(tableName);
foreach (FluentColumn fcol in columns) {
table.Columns.Add(fcol.Object);
}
string[] sqls = Dialect.GetCreateTableSqls(table);
ExecuteSqls(sqls);
}
protected IEnumerable<string> GetColumnCommentsSql(Table table) {
foreach (var column in table.Columns) {
if (column.Comment == null) {
continue;
}
yield return GetAddCommentToColumnSql(table.Name, column.ColumnName, column.Comment);
}
}
public override string[] GetCreateTableSqls(Table table)
{
var sqls = new List<string>();
var primaryKeyColumns = new List<string>();
Column autoIncrement = null;
//create table
var sb = new StringBuilder();
sb.Append("create table ").Append(table.Name).AppendLine(" (");
int size = table.Columns.Count;
for (int i = 0; i < size; i++) {
sb.Append(GetColumnToSqlWhenCreate(table.Columns[i]));
if (i != size - 1) {
sb.AppendLine(",");
}
if (table.Columns[i].IsAutoIncrement) {
autoIncrement = table.Columns[i];
}
if (table.Columns[i].IsPrimaryKey) {
primaryKeyColumns.Add(table.Columns[i].ColumnName);
}
}
sb.AppendLine(")");
sqls.Add(sb.ToString());
//create sequence and trigger for the autoincrement
if (autoIncrement != null) {
string sequenceName = SequencePrefix + table.Name;
string triggerName = TriggerPrefix + table.Name;
//create sequence in case of autoincrement
sb = new StringBuilder();
sb.AppendFormat("create sequence {0} minvalue 1 maxvalue 999999999999999999999999999 ", sequenceName);
sb.Append("start with 1 increment by 1 cache 20");
sqls.Add(sb.ToString());
//create trigger to run the sequence
sb = new StringBuilder();
sb.AppendFormat("create or replace trigger \"{0}\" before insert on {1} for each row ", triggerName, table.Name);
sb.AppendFormat("when (new.{0} is null) ", autoIncrement.ColumnName);
sb.AppendFormat("begin select {0}.nextval into :new.{1} from dual; end {2};", sequenceName, autoIncrement.ColumnName, triggerName);
sqls.Add(sb.ToString());
}
//primary key
if (primaryKeyColumns.Count > 0) {
sqls.Add(GetPrimaryKeySql(table.Name, String.Format("{0}{1}", PrimaryKeyPrefix, table.Name), primaryKeyColumns.ToArray()));
}
//comments
sqls.AddRange(GetColumnCommentsSql(table));
return sqls.ToArray();
}
public void Can_create_table_sql()
{
Table table = new Table("myTable");
table.Columns.Add(Column.AutoIncrement("id").AsPrimaryKey().Object);
table.Columns.Add(Column.String("name").NotNull().Object);
string[] sqls = _dialect.GetCreateTableSqls(table);
AssertSql.AreEqual(GetResultFor_Can_create_table_sql(), sqls);
}
public abstract string[] GetCreateTableSqls(Table table);
public override string[] GetCreateTableSqls(Table table) {
return _postgreSqlTableDialect.GetCreateTableSqls(table, GetColumnToSqlWhenCreate, GetPrimaryKeySql);
}