public static void UpdateTableStructure(this SQLiteConnection @this, string targetTable, SQLiteTable newStructure)
{
newStructure.Name = targetTable + "_temp";
@this.CreateTable(newStructure);
@this.CopyAllData(targetTable, newStructure.Name);
@this.DropTable(targetTable);
@this.RenameTable(newStructure.Name, targetTable);
}
public void CreateTable(SQLiteTable table)
{
StringBuilder sb = new System.Text.StringBuilder();
sb.Append("create table if not exists `");
sb.Append(table.TableName);
sb.AppendLine("`(");
bool firstRecord = true;
foreach (SQLiteColumn col in table.Columns)
{
if (col.ColumnName.Trim().Length == 0)
{
throw new Exception("Column name cannot be blank.");
}
if (firstRecord)
{
firstRecord = false;
}
else
{
sb.AppendLine(",");
}
sb.Append(col.ColumnName);
sb.Append(" ");
if (col.AutoIncrement)
{
sb.Append("integer primary key autoincrement");
continue;
}
switch (col.ColDataType)
{
case ColType.Text:
sb.Append("text"); break;
case ColType.Integer:
sb.Append("integer"); break;
case ColType.Decimal:
sb.Append("decimal"); break;
case ColType.DateTime:
sb.Append("datetime"); break;
case ColType.BLOB:
sb.Append("blob"); break;
}
if (col.PrimaryKey)
{
sb.Append(" primary key");
}
else if (col.NotNull)
{
sb.Append(" not null");
}
else if (col.DefaultValue.Length > 0)
{
sb.Append(" default ");
if (col.DefaultValue.Contains(" ") || col.ColDataType == ColType.Text || col.ColDataType == ColType.DateTime)
{
sb.Append("'");
sb.Append(col.DefaultValue);
sb.Append("'");
}
else
{
sb.Append(col.DefaultValue);
}
}
}
sb.AppendLine(");");
_cmd.CommandText = sb.ToString();
_cmd.ExecuteNonQuery();
}
public static void CreateTable(this SQLiteConnection @this, SQLiteTable table)
{
var sb = new StringBuilder();
sb.Append("create table if not exists `");
sb.Append(table.Name);
sb.AppendLine("`(");
bool firstRecord = true;
foreach (var col in table.Columns)
{
if (string.IsNullOrWhiteSpace(col.Name))
{
throw new Exception("Column name cannot be blank.");
}
if (firstRecord)
{
firstRecord = false;
}
else
{
sb.AppendLine(",");
}
sb.Append(col.Name);
sb.Append(" ");
if (col.AutoIncrement)
{
sb.Append("integer primary key autoincrement");
continue;
}
switch (col.Type)
{
case ColumnType.Text:
sb.Append("text"); break;
case ColumnType.Integer:
sb.Append("integer"); break;
case ColumnType.Decimal:
sb.Append("decimal"); break;
case ColumnType.DateTime:
sb.Append("datetime"); break;
case ColumnType.Blob:
sb.Append("blob"); break;
}
if (col.PrimaryKey)
{
sb.Append(" primary key");
}
else if (col.NotNull)
{
sb.Append(" not null");
}
else if (!string.IsNullOrEmpty(col.DefaultValue))
{
sb.Append(" default ");
if (col.DefaultValue.Contains(" ") || col.Type == ColumnType.Text || col.Type == ColumnType.DateTime)
{
sb.Append("'");
sb.Append(col.DefaultValue);
sb.Append("'");
}
else
{
sb.Append(col.DefaultValue);
}
}
}
sb.AppendLine(");");
@this.ExecuteNonQuery(sb.ToString());
}
