public virtual string SerializeSelect(SQLSelect select)
{
if (select.Tables.Count == 0)
throw new Exceptions.DatabaseObjectsException("The table has not been set.");
var tokens = new TokenSerializer();
tokens.Add("SELECT");
tokens.Add(SerializeBeforeSelectFields(select));
tokens.Add(SerializeSelectFields(select.Fields));
tokens.Add("FROM");
tokens.Add(SerializeSelectTables(select.Tables));
tokens.Add(SerializeAfterSelectTables(select));
if (select.Where != null && !select.Where.IsEmpty)
{
tokens.Add("WHERE");
tokens.Add(SerializeConditions(select.Where));
}
if (select.GroupBy != null && !select.GroupBy.IsEmpty)
{
tokens.Add("GROUP BY");
tokens.Add(SerializeSelectGroupByFields(select.GroupBy));
}
if (select.OrderBy != null && !select.OrderBy.IsEmpty)
{
tokens.Add("ORDER BY");
tokens.Add(SerializeSelectOrderByFields(select.OrderBy));
}
if (select.Having != null && !select.Having.IsEmpty)
{
tokens.Add("HAVING");
tokens.Add(SerializeSelectHavingConditions(select.Having));
}
return tokens.ToString();
}
public override string SerializeSelect(SQLSelect select)
{
var tokens = new TokenSerializer();
tokens.Add(base.SerializeSelect(select));
if (select.PerformLocking)
tokens.Add("FOR UPDATE");
return tokens.ToString();
}
public string SerializeInsert(SQLInsert insert)
{
if (String.IsNullOrEmpty(insert.TableName))
throw new Exceptions.DatabaseObjectsException("TableName property has not been set.");
else if (insert.Fields.Count == 0)
throw new Exceptions.DatabaseObjectsException("Field values have not been set.");
string fieldNames = String.Join(", ", insert.Fields.Select(field => SerializeIdentifier(field.Name)).ToArray());
string fieldValues = String.Join(", ", insert.Fields.Select(field => SerializeValue(field.Value)).ToArray());
var tokens = new TokenSerializer();
tokens.Add("INSERT INTO");
tokens.Add(SerializeIdentifier(insert.TableName));
tokens.Add("(" + fieldNames + ")");
tokens.Add("VALUES");
tokens.Add("(" + fieldValues + ")");
return tokens.ToString();
}
public string SerializeInsertFromSelect(SQLInsertFromSelect insertFromSelect)
{
if (String.IsNullOrEmpty(insertFromSelect.TableName))
throw new Exceptions.DatabaseObjectsException("TableName property has not been set.");
var tokens = new TokenSerializer();
tokens.Add("INSERT INTO");
tokens.Add(SerializeIdentifier(insertFromSelect.TableName));
if (insertFromSelect.Fields.Count > 0)
{
var fields = String.Join(", ", insertFromSelect.Fields.Cast<string>().Select(fieldName => SerializeIdentifier(fieldName)).ToArray());
tokens.Add("(" + fields + ")");
}
tokens.Add(SerializeSelect(insertFromSelect.Source));
return tokens.ToString();
}
public virtual string SerializeIndexField(SQLIndexField indexField)
{
var tokens = new TokenSerializer();
tokens.Add(SerializeIdentifier(indexField.Name));
tokens.Add(SerializeOrderBy(indexField.Order));
return tokens.ToString();
}
public virtual string SerializeIndexFields(SQLIndexFields fields)
{
var tokens = new TokenSerializer(", ");
foreach (SQLIndexField objField in fields)
tokens.Add(SerializeIndexField(objField));
return tokens.ToString();
}
public virtual string SerializeCreateTable(SQLCreateTable createTable)
{
if (String.IsNullOrEmpty(createTable.Name))
throw new Exceptions.DatabaseObjectsException("TableName has not been set.");
var tokens = new TokenSerializer();
tokens.Add("CREATE TABLE");
tokens.Add(SerializeIdentifier(createTable.Name));
tokens.Add("(" + SerializeTableFields(createTable.Fields, includeColumnModifier: false) + ")");
return tokens.ToString();
}
public virtual string SerializeDelete(SQLDelete delete)
{
if (String.IsNullOrEmpty(delete.TableName))
throw new Exceptions.DatabaseObjectsException("TableName property has not been set.");
var tokens = new TokenSerializer();
tokens.Add("DELETE FROM");
tokens.Add(SerializeIdentifier(delete.TableName));
if (delete.Where != null && !delete.Where.IsEmpty)
{
tokens.Add("WHERE");
tokens.Add(SerializeConditions(delete.Where));
}
return tokens.ToString();
}
public virtual string SerializeCaseExpression(SQLCaseExpression expression)
{
if (expression.Cases.Count() == 0)
throw new InvalidOperationException("There are no cases for the CASE statement");
var tokens = new TokenSerializer();
tokens.Add("CASE");
if (expression.InputExpression != null)
tokens.Add(expression.InputExpression.SQL(this));
var cases = string.Join(" ", expression.Cases.Select(objCase => SerializeCaseExpressionCase(objCase)).ToArray());
tokens.Add(cases);
if (expression.ElseResult != null)
{
tokens.Add("ELSE");
tokens.Add(expression.ElseResult.SQL(this));
}
tokens.Add("END");
return tokens.ToString();
}
public virtual string SerializeCreateIndex(SQLCreateIndex createIndex)
{
//Although the index name is optional with SQL Server it is not optional with MySQL or Pervasive
if (String.IsNullOrEmpty(createIndex.Name))
throw new Exceptions.DatabaseObjectsException("IndexName has not been set.");
else if (String.IsNullOrEmpty(createIndex.TableName))
throw new Exceptions.DatabaseObjectsException("TableName has not been set.");
var tokens = new TokenSerializer();
tokens.Add("CREATE");
tokens.Add(SerializeCreateIndexModifier(createIndex));
tokens.Add("INDEX");
tokens.Add(SerializeIdentifier(createIndex.Name));
tokens.Add("ON");
tokens.Add(SerializeIdentifier(createIndex.TableName));
tokens.Add("(" + SerializeIndexFields(createIndex.Fields) + ")");
return tokens.ToString();
}
public virtual string SerializeUpdate(SQLUpdate update)
{
if (String.IsNullOrEmpty(update.TableName))
throw new Exceptions.DatabaseObjectsException("TableName property has not been set.");
else if (update.Fields.Count == 0)
throw new Exceptions.DatabaseObjectsException("Field values have not been set.");
else if (update.Fields.Any(field => String.IsNullOrEmpty(field.Name)))
throw new Exceptions.DatabaseObjectsException("Field Name has not been set.");
var fieldNameAndValues = String.Join(", ", update.Fields.Select(field => SerializeIdentifier(field.Name) + " = " + SerializeValue(field.Value)).ToArray());
var tokens = new TokenSerializer();
tokens.Add("UPDATE");
tokens.Add(SerializeIdentifier(update.TableName));
tokens.Add("SET");
tokens.Add(fieldNameAndValues);
if (update.Where != null && !update.Where.IsEmpty)
{
tokens.Add("WHERE");
tokens.Add(SerializeConditions(update.Where));
}
return tokens.ToString();
}
public virtual string SerializeUnion(SQLUnion union)
{
if (union.SelectStatements.Count() < 2)
throw new Exceptions.DatabaseObjectsException("The select statements have not been set");
var tokens = new TokenSerializer();
int index = 0;
foreach (var statement in union.SelectStatements)
{
if (index > 0)
tokens.Add(SerializeUnionType(union.UnionTypes[index - 1]));
tokens.Add("(" + SerializeSelect(statement) + ")");
index++;
}
if (union.OrderBy != null && !union.OrderBy.IsEmpty)
{
tokens.Add("ORDER BY");
tokens.Add(SerializeSelectOrderByFields(union.OrderBy));
}
return tokens.ToString();
}
/// <summary>
/// </summary>
/// <param name="includeColumnModifier">
/// Indicates whether the ADD, MODIFY or DROP modifiers are required for each column.
/// When utilised from SQLCreateTable this will always be false. However, for SQLAlterTable this will be true.</param>
/// <returns></returns>
public virtual string SerializeTableFields(SQLTableFields tableFields, bool includeColumnModifier)
{
var tokens = new TokenSerializer();
//Include mode when altering a table, otherwise when creating a table the mode is not required.
if (includeColumnModifier)
tokens.Add(SerializeAlterTableFieldsModifier(tableFields.AlterMode));
var fieldsTokens = new TokenSerializer(", ");
foreach (SQLTableFieldBase field in tableFields)
{
var fieldToken = new TokenSerializer();
if (includeColumnModifier)
fieldToken.Add(SerializeAlterTableFieldModifier(tableFields.AlterMode));
fieldToken.Add(field.SQL(this, tableFields.AlterMode));
fieldsTokens.Add(fieldToken.ToString());
}
tokens.Add(fieldsTokens.ToString());
return tokens.ToString();
}
public virtual string SerializeTableField(SQLTableField field, SQLTableFields.AlterModeType alterMode)
{
var tokens = new TokenSerializer();
if (alterMode == SQLTableFields.AlterModeType.Drop)
tokens.Add(SerializeTableFieldAsName(field));
else
{
tokens.Add(SerializeTableFieldAsName(field));
tokens.Add(SerializeTableFieldDataType(field));
tokens.Add(SerializeTableFieldCollationOption(field));
tokens.Add(SerializeTableFieldDefaultOption(field));
tokens.Add(SerializeTableFieldNullableOption(field));
tokens.Add(SerializeTableFieldKeyTypeOption(field));
}
return tokens.ToString();
}
public override string SerializeSelect(SQLSelect select)
{
var tokens = new TokenSerializer();
tokens.Add(base.SerializeSelect(select));
if (select.Top > 0)
tokens.Add("LIMIT " + select.Top.ToString());
return tokens.ToString();
}