public EntityInfo[] GetEntityDefinitions()
{
ValidateConnection();
var entities = new List <EntityInfo>();
using (var connection = new SqlCeConnection(m_connectionString))
using (var cmd = new SqlCeCommand(GetTablesSQL, connection))
{
connection.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var info = new EntityInfo();
var indexInfo = new Dictionary <string, IndexInfo>();
info.Entity = new EntityAttribute();
info.Entity.NameInStore = reader.GetString(0);
using (var indexCommand = new SqlCeCommand(
string.Format("SELECT INDEX_NAME, PRIMARY_KEY, COLUMN_NAME, COLLATION FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = '{0}'", info.Entity.NameInStore),
connection))
using (var indexReader = indexCommand.ExecuteReader())
{
while (indexReader.Read())
{
var indexName = indexReader.GetString(0);
var primaryKey = indexReader.GetBoolean(1);
var columnName = indexReader.GetString(2);
var sortOrder = indexReader.GetInt16(3) == 1 ? FieldSearchOrder.Ascending : FieldSearchOrder.Descending;
// collation of 1 == ascending, 2 == descending (based on a quick test, this might be incorrect)
// TODO: handle cases where a column is in multiple indexes (ORM doesn't support that scenario for now)
if (!indexInfo.ContainsKey(columnName))
{
indexInfo.Add(columnName, new IndexInfo()
{
ColumnName = columnName,
IndexName = indexName,
PrimaryKey = primaryKey,
SearchOrder = sortOrder
});
}
}
}
// TODO: look for primary key to set key scheme
info.Entity.KeyScheme = KeyScheme.None;
using (var fieldCommand = new SqlCeCommand(
string.Format("SELECT COLUMN_NAME, COLUMN_HASDEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, AUTOINC_SEED FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", info.Entity.NameInStore),
connection))
{
using (var fieldReader = fieldCommand.ExecuteReader())
{
while (fieldReader.Read())
{
var field = new FieldAttribute();
field.FieldName = fieldReader.GetString(0);
field.AllowsNulls = string.Compare(fieldReader.GetString(2), "YES", true) == 0;
field.DataType = fieldReader.GetString(3).ParseToDbType();
object val = fieldReader[4];
if (!val.Equals(DBNull.Value))
{
field.Length = Convert.ToInt32(val);
}
val = fieldReader[5];
if (!val.Equals(DBNull.Value))
{
field.Precision = Convert.ToInt32(val);
}
val = fieldReader[6];
if (!val.Equals(DBNull.Value))
{
field.Scale = Convert.ToInt32(val);
}
val = fieldReader[7];
if (!val.Equals(DBNull.Value))
{
// identity field, so it must be the PK (or part of it)
info.Entity.KeyScheme = KeyScheme.Identity;
}
// check for indexes
if (indexInfo.ContainsKey(field.FieldName))
{
var idx = indexInfo[field.FieldName];
if (idx.PrimaryKey)
{
field.IsPrimaryKey = true;
if (field.DataType == DbType.Guid)
{
info.Entity.KeyScheme = KeyScheme.GUID;
}
}
field.SearchOrder = idx.SearchOrder;
}
// TODO: populate the remainder of the field info
info.Fields.Add(field);
}
}
}
// check for references
using (var referenceSourceCommand = new SqlCeCommand(
string.Format(
"SELECT a.Constraint_name, a.TABLE_NAME, b.COLUMN_NAME " +
"FROM information_schema.table_constraints AS a " +
"INNER JOIN information_schema.KEY_COLUMN_USAGE AS b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME " +
"WHERE (a.TABLE_NAME = '{0}') AND (a.CONSTRAINT_TYPE = 'FOREIGN KEY')",
info.Entity.NameInStore),
connection))
{
string constraintName = null;
string localField = null;
string remoteTable = null;
string remoteFieldName = null;
bool referenceExists = false;
using (var srcReader = referenceSourceCommand.ExecuteReader())
{
while (srcReader.Read())
{
constraintName = (string)srcReader[0];
localField = (string)srcReader[2];
using (var referenceTargetCommand = new SqlCeCommand(
string.Format(
"SELECT a.UNIQUE_CONSTRAINT_TABLE_NAME, b.COLUMN_NAME " +
"FROM information_schema.REFERENTIAL_CONSTRAINTS AS a INNER JOIN " +
"information_schema.KEY_COLUMN_USAGE AS b ON a.Constraint_name = b.CONSTRAINT_NAME " +
"WHERE a.CONSTRAINT_NAME = '{0}'",
constraintName),
connection))
{
using (var targetReader = referenceTargetCommand.ExecuteReader())
{
while (targetReader.Read())
{
remoteTable = (string)targetReader[0];
remoteFieldName = (string)targetReader[1];
referenceExists = true;
break;
}
}
}
}
}
if (referenceExists)
{
var reference = new ReferenceInfo()
{
ReferenceTable = remoteTable,
LocalFieldName = localField,
RemoteFieldName = remoteFieldName
};
info.References.Add(reference);
}
}
entities.Add(info);
}
}
}
return(entities.ToArray());
}
public EntityInfo[] GetEntityDefinitions()
{
ValidateConnection();
var entities = new List <EntityInfo>();
// PRAGMA table_info(Clienti)
// PRAGMA index_list(Clienti)
using (var connection = new SQLiteConnection(m_connectionString))
using (var cmd = new SQLiteCommand(GetTablesSQL, connection))
{
connection.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var info = new EntityInfo();
info.Entity = new EntityAttribute();
info.Entity.NameInStore = reader.GetString(0);
if (string.Compare(info.Entity.NameInStore, "sqlite_sequence", true) == 0)
{
// this is the auto-increment meta table, skip it
continue;
}
using (var ticmd = new SQLiteCommand(string.Format("PRAGMA table_info({0})", info.Entity.NameInStore), connection))
using (var tireader = ticmd.ExecuteReader())
{
while (tireader.Read())
{
var cid = tireader["cid"];
var name = tireader["name"];
var type = tireader["type"];
var notnull = tireader["notnull"];
var dflt_value = tireader["dflt_value"];
var pk = tireader["pk"];
var field = new FieldAttribute();
field.FieldName = (string)name;
field.AllowsNulls = !Convert.ToBoolean(notnull);
field.IsPrimaryKey = Convert.ToBoolean(pk);
field.DataType = ((string)type).ParseToDbType(true);
// TODO: handle default values
// TODO: determine if we have auto-increment
info.Fields.Add(field);
}
}
// check for indexes (for sort order)
using (var idxcmd = new SQLiteCommand(string.Format("SELECT sql FROM sqlite_master WHERE type = 'index' AND tbl_name = '{0}'", info.Entity.NameInStore), connection))
using (var idxreader = idxcmd.ExecuteReader())
{
while (idxreader.Read())
{
if (idxreader[0] == DBNull.Value)
{
// PK or UNIQUE index
continue;
}
var sql = idxreader.GetString(0);
var indexInfo = sql.ParseToIndexInfo();
if (indexInfo.IsComposite)
{
Debug.WriteLine("Composite indexes not currently supported!");
continue;
}
var indexedField = (from f in info.Fields
where string.Compare(f.FieldName, indexInfo.Fields[0], true) == 0
select f).FirstOrDefault();
if (indexedField != null)
{
indexedField.SearchOrder = indexInfo.SearchOrder;
}
}
}
// check for references
using (var fkcmd = new SQLiteCommand(String.Format("PRAGMA foreign_key_list({0})", info.Entity.NameInStore), connection))
using (var fkreader = fkcmd.ExecuteReader())
{
while (fkreader.Read())
{
var reference = new ReferenceInfo();
reference.ReferenceTable = (string)fkreader["table"];
reference.LocalFieldName = (string)fkreader["from"];
reference.RemoteFieldName = (string)fkreader["to"];
info.References.Add(reference);
}
}
entities.Add(info);
}
}
}
return(entities.ToArray());
}
