/// <summary>
/// Adds definition of tables and indexes we want in our database
/// </summary>
private void BuildMetadataObjects()
{
TableMetadata table;
List <String> indexColumnNames = new List <string>();
// Create a UniqueKey column definition which will be used by all tables as an "AutoIncrement" primary key.
// Note that we don't flag it as a NOT NULL attribute because the AutoInc column creation for SQLite will do it automatically.
IntColumnMetadata UniqueKeyColumnMetadata = new IntColumnMetadata("UniqueKey", 19, false);
UniqueKeyColumnMetadata.IsIdentityColumn = true;
// ------------------------------------//
// OverstayVioActions table definition //
// ------------------------------------//
table = CreateTableMetadata("OverstayVioActions");
// Add UniqueKey which will be the "AutoIncrement" field and primary key
table.AddField(UniqueKeyColumnMetadata);
// Now add normal columns
table.AddField(new IntColumnMetadata("CustomerID", 19, false));
table.AddField(new IntColumnMetadata("MeterID", 19, false));
table.AddField(new IntColumnMetadata("AreaID", 19, false));
table.AddField(new IntColumnMetadata("BayNumber", 19, false));
table.AddField(new IntColumnMetadata("RBACUserID", 19, false));
table.AddField(new DateTimeColumnMetadata("EventTimestamp", 0, false, false));
table.AddField(new StringColumnMetadata("ActionTaken", 30, false));
// Don't need to add defintion for PrimaryKey Index of the AutoInc column because SQLite takes care of it on its own.
// Add Regular Indexes
indexColumnNames.Clear();
indexColumnNames.Add("CustomerID");
indexColumnNames.Add("MeterID");
indexColumnNames.Add("BayNumber");
indexColumnNames.Add("EventTimestamp");
new IndexMetadata(table, "IDX_" + table.Name + "_1", indexColumnNames.ToArray(), false, false);
indexColumnNames.Clear();
indexColumnNames.Add("CustomerID");
indexColumnNames.Add("MeterID");
indexColumnNames.Add("BayNumber");
new IndexMetadata(table, "IDX_" + table.Name + "_2", indexColumnNames.ToArray(), false, false);
// -------------------------------------------------------------------------
}
/// <summary>
/// FldDefFromSchemaColumn
/// Implements SQL Server specific version of inherited method.
/// </summary>
/// <param name="iSchemaColumn"></param>
/// <returns></returns>
public override ColumnMetadata FldDefFromSchemaColumn(DataRow iSchemaColumn)
{
ColumnMetadata loFldDef;
if (iSchemaColumn == null)
{
throw new Exception("FldDefFromSchemaColumn passed null iSchemaColumn");
}
// map sql server column types to ColumnMetadata descendants
string dataTypeAsString = Convert.ToString(iSchemaColumn["DATA_TYPE"]);
DbType DbType = DbType.String;
// Now translate the known values to a DbType
if (string.Compare(dataTypeAsString, "integer", true) == 0)
{
DbType = DbType.Int64;
}
else if (string.Compare(dataTypeAsString, "timestamp", true) == 0) // SQL Server uses "datetime"
{
DbType = DbType.DateTime;
}
else if (string.Compare(dataTypeAsString, "varchar", true) == 0)
{
DbType = DbType.String;
}
else if (string.Compare(dataTypeAsString, "blob", true) == 0)
{
DbType = DbType.Binary;
}
else if (string.Compare(dataTypeAsString, "boolean", true) == 0)
{
DbType = DbType.Boolean;
}
else
{
throw new Exception("Unhandled schema column datatype: " + dataTypeAsString);
}
Type mappedDbType = MapDbTypeToClrType(DbType);
// start w/ most likely type, varchar
if (mappedDbType == typeof(string))
{
loFldDef = new StringColumnMetadata();
loFldDef.Size = Convert.ToInt32(iSchemaColumn["CHARACTER_MAXIMUM_LENGTH"].ToString());
}
else if (mappedDbType == typeof(DateTime))
{
loFldDef = new DateTimeColumnMetadata();
}
else if (mappedDbType == typeof(Boolean))
{
loFldDef = new BooleanColumnMetadata();
}
else if (mappedDbType == typeof(Int64))
{
loFldDef = new IntColumnMetadata();
loFldDef.Size = 19; // maximum decimal size of 64-bit number
}
else if (mappedDbType == typeof(Int32))
{
loFldDef = new IntColumnMetadata();
loFldDef.Size = 10; // maximum decimal size of 32-bit number
}
else if (mappedDbType == typeof(Int16))
{
loFldDef = new IntColumnMetadata();
loFldDef.Size = 5; // maximum decimal size of 16-bit number
}
else if (mappedDbType == typeof(SByte))
{
loFldDef = new IntColumnMetadata();
loFldDef.Size = 3; // maximum decimal size of 8-bit number
}
else if (mappedDbType == typeof(Decimal))
{
loFldDef = new RealColumnMetadata();
loFldDef.Size = Convert.ToInt32(iSchemaColumn["NUMERIC_PRECISION"].ToString()); // Use NUMERIC_PRECISION and NUMERIC_SCALE?
}
else if (mappedDbType == typeof(Single)) // real is the same as float
{
loFldDef = new RealColumnMetadata();
loFldDef.Size = Convert.ToInt32(iSchemaColumn["NUMERIC_PRECISION"].ToString()); // Use NUMERIC_PRECISION and NUMERIC_SCALE?
}
else if (mappedDbType == typeof(Byte[]))
{
loFldDef = new BlobColumnMetadata();
loFldDef.Size = 8000; // Convert.ToInt32(iSchemaColumn["NUMERIC_PRECISION"].ToString()); // Use NUMERIC_PRECISION and NUMERIC_SCALE?
}
else
{
return(null);
}
loFldDef.Name = iSchemaColumn["COLUMN_NAME"].ToString();
try
{
if ((bool)iSchemaColumn["IS_NULLABLE"] == false)
{
loFldDef.dbNotNull = true;
}
else if (string.Compare(iSchemaColumn["IS_NULLABLE"].ToString(), "NO", true) == 0)
{
loFldDef.dbNotNull = true;
}
}
catch { }
return(loFldDef);
}
