/// <summary>
/// Creates the text of the SQL command to create a table in SQLite
/// </summary>
/// <param name="table">Table Metadata to create</param>
/// <returns>The DML sentence (SQL) to create the given table in a SQLite schema</returns>
private string CreateTableDML(TableMetaData table)
{
StringBuilder stmtBuilder = new StringBuilder();
string primary_keys_string = string.Empty;
stmtBuilder.Append("CREATE TABLE " + SchemaTablesMetaData.EscapeIdentifier(table.tableName) + " (");
for (int i = 0; i < table.columns.Count; i++)
{
ColumnMetaData column = table.columns[i];
stmtBuilder.Append(SchemaTablesMetaData.EscapeIdentifier(column.columnName));
stmtBuilder.Append(" ");
stmtBuilder.Append(ConvertDbTypeToSQLiteType(column));
if (!column.isNullable)
{
stmtBuilder.Append(" NOT NULL");
}
if (column.hasDefault)
{
stmtBuilder.Append(" DEFAULT ");
if (IsLiteralType(column))
{
stmtBuilder.Append(SchemaTablesMetaData.EscapeIdentifier(column.defaultValue));
}
else
{
stmtBuilder.Append(column.defaultValue);
}
}
if (column.hasForeignKey)
{
stmtBuilder.Append(String.Format(" REFERENCES {0} ({1})", SchemaTablesMetaData.EscapeIdentifier(column.fkTable), SchemaTablesMetaData.EscapeIdentifier(column.fkColumn)));
}
if (column.isPrimaryKey)
{
if (string.IsNullOrEmpty(primary_keys_string))
{
primary_keys_string = SchemaTablesMetaData.EscapeIdentifier(column.columnName);
}
else
{
primary_keys_string = String.Format("{0},{1}", primary_keys_string, SchemaTablesMetaData.EscapeIdentifier(column.columnName));
}
}
if (i + 1 < table.columns.Count || !string.IsNullOrEmpty(primary_keys_string))
{
stmtBuilder.Append(", ");
}
}
if (!string.IsNullOrEmpty(primary_keys_string))
{
stmtBuilder.Append(String.Format("PRIMARY KEY({0})", primary_keys_string));
}
stmtBuilder.Append(")");
return(stmtBuilder.ToString());
}
/// <summary>
/// Creates a SQL INSERT sentence to insert a row of data to a table in the target database.
/// </summary>
/// <param name="table">Table metadata from the table to copy</param>
/// <returns>SQL Sentence of the insertion in the target database</returns>
private string CreateInsertQuery(TableMetaData table)
{
string csvColumList = string.Empty;
string csvParamsList = string.Empty;
foreach (ColumnMetaData column in table.columns)
{
csvColumList = String.Format("{0}{1},", csvColumList, SchemaTablesMetaData.EscapeIdentifier(column.columnName));
csvParamsList = String.Format("{0}@{1},", csvParamsList, SchemaTablesMetaData.HexString(column.columnName));
}
string query = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", SchemaTablesMetaData.EscapeIdentifier(table.tableName), csvColumList.Remove(csvColumList.Length - 1), csvParamsList.Remove(csvParamsList.Length - 1));
return(query);
}