/// <summary>
/// 読込ボタン
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog dlg = new OpenFileDialog();
dlg.Filter = "edmx files (*.edmx)|*.edmx|All files (*.*)|*.*";
dlg.FilterIndex = 0;
if (dlg.ShowDialog() != DialogResult.OK)
return;
_filename = dlg.FileName;
_doc = new EXDocument();
_doc.Load(_filename);
_tables = new List<TableMapping>();
var els = _doc * "edmx:Mappings" * "EntitySetMapping";
foreach (var el in els)
{
TableMapping tm = new TableMapping();
_tables.Add(tm);
tm.EntityName = el / "EntityTypeMapping" % "TypeName";
tm.EntityName = tm.EntityName.Substring(tm.EntityName.IndexOf('.') + 1);
tm.EntitySetName = el % "Name";
tm.StoreName = el / "EntityTypeMapping" / "MappingFragment" % "StoreEntitySet";
}
listBox1.DataSource = _tables;
EXElement el2 = _doc * "EntitySet";
string name = el2 % "EntityType";
_modelName = name.Substring(0, name.IndexOf('.') );
}
public static void Main (string[] args)
{
var db = InitDb();
db.InsertAll(CreateCustomers(5,5),"OR REPLACE");
var customersFromDb = db.Table<Customer>().ToList();
var ordersFromDb = db.Table<Order>().ToList();
PrintInfo(customersFromDb,"customers");
PrintInfo(ordersFromDb, "orders");
var map = new TableMapping(typeof(Customer));
var q = string.Format("update {0} set Id = 999 where Id = 1",map.TableName);
db.Query(map,q,null);
customersFromDb = db.Table<Customer>().Where(x => x.Id == 999).ToList();
ordersFromDb = db.Table<Order>().Where(x => x.CustomerId == 999).ToList();
PrintInfo(customersFromDb,"customers");
PrintInfo(ordersFromDb, "orders");
db.DeleteAll<Customer>();
customersFromDb = db.Table<Customer>().ToList();
ordersFromDb = db.Table<Order>().ToList();
PrintInfo(customersFromDb,"customers");
PrintInfo(ordersFromDb, "orders");
}
public void Create_Database()
{
var map = new TableMapping (typeof(SqlDbType), new List<PropertyInfo> ());
helper.Create ();
var tables = helper.Query (map, "SELECT name FROM sqlite_master WHERE type='table'", new object[0]);
// have we created the tables we want?
Assert.IsTrue (tables.Count >= 2, "Error: This means that the database has not been created correctly");
// now, do our tables contain the correct columns?
var columnNames = helper.GetTableInfo ("location");
Assert.IsTrue (columnNames.Count > 0, "Error: This means that the database has not been created correctly");
// Build a HashSet of all of the column names we want to look for
HashSet<String> locationColumnHashSet = new HashSet<String> ();
locationColumnHashSet.Add ("LocationId");
locationColumnHashSet.Add ("city_name");
locationColumnHashSet.Add ("coord_lat");
locationColumnHashSet.Add ("coord_long");
locationColumnHashSet.Add ("location_setting");
var columnList = columnNames.ToList ();
var columnNameIndex = 0;
do {
var columnName = columnList [columnNameIndex].Name;
locationColumnHashSet.Remove (columnName);
columnNameIndex++;
} while(columnNameIndex < columnList.Count);
// if this fails, it means that your database doesn't contain all of the required location
// entry columns
Assert.IsTrue (locationColumnHashSet.Count == 0, "Error: The database doesn't contain all of the required location entry columns");
}
public TableOrViewTreeNode(ProjectSchemaTreeNode parent, TableMapping tableOrViewMapping)
: base(parent)
{
this.TableMapping = tableOrViewMapping;
Initialize();
}
/// <summary>
/// Reads an <see cref="ExcelFile"/> from the specified stream as an <see cref="ITable"/> instance.
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static ITable Read(Stream file, TableMapping mapping = null)
{
var excelFile = new ExcelFile(file);
// Infer or load mappings from the spreadsheet if they are not specified explicitly
if (mapping == null)
throw new NotImplementedException("Add support for inferring the mappings for single sheet imports or loading the mappings from a manifest sheet.");
return new Table(excelFile, mapping, null);
}
internal static string SqlType(TableMapping.Column p)
{
Type clrType = p.ColumnType;
int len = p.MaxStringLength;
if (clrType == typeof(Char))
{
clrType = typeof (String);
len = 1;
}
if (clrType == typeof (Boolean) ||
clrType == typeof (Byte) ||
clrType == typeof (UInt16) ||
clrType == typeof (SByte) ||
clrType == typeof (Int16) ||
clrType == typeof (Int32) ||
clrType.GetTypeInfo().IsEnum)
{
return "integer";
}
if (clrType == typeof (UInt32) ||
clrType == typeof (Int64))
{
return "bigint";
}
if (clrType == typeof (Single) ||
clrType == typeof (Double) ||
clrType == typeof (Decimal))
{
return "float";
}
if (clrType == typeof(String))
{
return (len <= 0)
? "text"
: "varchar(" + len + ")";
}
if (clrType == typeof (DateTime))
{
return "datetime";
}
if (clrType == typeof (Byte[]))
{
return "blob";
}
if (clrType == typeof (Guid))
{
return "varchar(30)";
}
throw new NotSupportedException("Don't know about " + clrType);
}
public void NamedCompositePrimaryKeyTableTest()
{
var db = new OrmTestSession();
db.CreateTable<NamedCompositePrimaryKeyTable>();
var sql = new TableMapping(typeof(NamedCompositePrimaryKeyTable)).GetCreateSql();
var correct = @"CREATE TABLE [NamedCompositePrimaryKeyTable] (
[FirstId] integer NOT NULL,
[SecondId] integer NOT NULL,
CONSTRAINT PK_Alfred
PRIMARY KEY ([FirstId], [SecondId])
);";
Assert.AreEqual(correct, sql);
}
public void TableMapping()
{
TableMapping table = new TableMapping(typeof(SysProduct));
Console.WriteLine(table.TableName);
foreach (var col in table.Columns)
{
Console.WriteLine(col.ColumnName+" "+col.IsPrimaryKey);
}
DefaultSqlBuilder sqlb = new DefaultSqlBuilder(table);
Console.WriteLine(sqlb.BuildDelete());
Console.WriteLine(sqlb.BuildInsert());
Console.WriteLine(sqlb.BuildUpdate());
}
public void CreateSimpleTable()
{
using (var db = new OrmTestSession())
{
db.CreateTable<SimpleTable>();
var sql = new TableMapping(typeof(SimpleTable)).GetCreateSql();
var correct = @"CREATE TABLE [SimpleTable] (
[Id] integer NOT NULL,
[Name] text ,
[BigInt] bigint NOT NULL,
[IsWorking] integer NOT NULL
);";
Assert.AreEqual(correct, sql);
}
}
public ColumnMapping CreateColumnMapping(TableMapping table)
{
string random = this.Random.Next().ToString();
string columnName = "Column{0}".FormatString(random);
string fieldName = "Field{0}".FormatString(random);
DataTypeMapping dataType = table.ContainingProject.DataTypeMappings.Random(this.Random);
bool nullable = (dataType.Nullable ? this.Random.NextBool() : false);
ColumnMapping column = new ColumnMapping() {
ColumnName = columnName,
DatabaseDataType = dataType.DatabaseDataType,
DataType = dataType.ApplicationDataType,
FieldName = fieldName,
Nullable = nullable,
NullableInDatabase = nullable
};
return column;
}
public TableMapping CreateTableMapping(Project project)
{
string random = this.Random.Next().ToString();
string tableName = "Table{0}".FormatString(random);
string className = "Class{0}".FormatString(random);
TableMapping table = new TableMapping() {
ClassName = className,
SchemaName = "dbo",
TableName = tableName
};
for (int i = 0; i < this.Random.Next(1, 25); i++)
table.ColumnMappings.Add(CreateColumnMapping(table));
table.ColumnMappings.First().PrimaryKey = true;
table.JoinToProject(project);
return table;
}
public void CreateTokenizedSimpleTable()
{
using (var db = new OrmTestSession())
{
db.CreateTable<TokenizedSimpleTable>();
var sql = new TableMapping(typeof(TokenizedSimpleTable)).GetCreateSql();
var correct =
@"CREATE VIRTUAL TABLE [TokenizedSimpleTable] USING FTS4 (
[Id] integer NOT NULL,
[Name] text ,
[BigInt] bigint NOT NULL,
[IsWorking] integer NOT NULL,
tokenize=porter
);";
Assert.AreEqual(correct, sql);
}
}
public void UpdateWithOnUpdateCascade()
{
var ownerObjs = Enumerable.Range(1,3)
.Select(i => new TestObjWithOne2Many {Id = i,
ObjList = Enumerable.Range(1,5)
.Select(x => new TestDependentObj{Id = (i*10) + x,
OwnerId = i,
Text = "Test" + ((int)(i*10) + x)})
.ToList()}).ToList();
_db.InsertAll(ownerObjs);
var map = new TableMapping(typeof(TestObjWithOne2Many));
var q = string.Format("update {0} set Id = 10 where Id = {1}",map.TableName, ownerObjs[0].Id);
_db.Query(map,q,null);
var parents = _db.Table<TestObjWithOne2Many>().Where(x => x.Id == 10).ToList();
var childs = _db.Table<TestDependentObj>().Where(x => x.OwnerId == 10).ToList();
Assert.AreEqual(1, parents.Count());
Assert.AreEqual(5, childs.Count());
}
/// <summary>
/// Generates the commands that correspond to this operation.
/// </summary>
/// <returns> The commands that correspond to this operation. </returns>
public virtual IEnumerable <ModificationCommand> GenerateModificationCommands([CanBeNull] IModel model)
{
Debug.Assert(
Columns.Length == Values.GetLength(1),
$"The number of values doesn't match the number of keys (${Columns.Length})");
var properties = model != null
? TableMapping.GetTableMapping(model, Table, Schema)?.GetPropertyMap()
: null;
for (var i = 0; i < Values.GetLength(0); i++)
{
var modifications = new ColumnModification[Columns.Length];
for (var j = 0; j < Columns.Length; j++)
{
modifications[j] = new ColumnModification(
Columns[j], originalValue: null, value: Values[i, j], property: properties?.Find(Columns[j]),
isRead: false, isWrite: true, isKey: true, isCondition: false, sensitiveLoggingEnabled: true);
}
yield return(new ModificationCommand(Table, Schema, modifications, sensitiveLoggingEnabled: true));
}
}
public static ITableMappingOperation BuildTable(TableMapping tableMapping)
{
switch (tableMapping.Destination)
{
case "BANK_FIN_PRD_PROSP_DATA":
return(new BankFinPrdProspData());
case "FIN_PRD_RPT_DATA":
return(new FinPrdRrtData());
case "DISC_ACCE_CFP_DATA":
return(new DiscAcceCfpData());
case "RATE_REP_DATA":
return(new RateRepData());
case "RES_INFO":
return(new ResInfo());
default:
throw new Exception("Luna.DataSync.Core.TableMappingFactory.BuildTable() got a unknow table destination" + tableMapping.Destination);
}
}
public void CreateAdvancedTable()
{
var db = new OrmTestSession();
db.CreateTable <AdvancedTable>();
TableMapping mapping = db.GetMapping <AdvancedTable>();
var sql = mapping.GetCreateSql();
var correct =
@"CREATE TABLE [AdvancedTable] (
[Id] integer CONSTRAINT PK_MyPrimaryKey PRIMARY KEY Desc ON CONFLICT Fail NOT NULL,
[IsWorking] integer UNIQUE ON CONFLICT Rollback
);";
Assert.AreEqual(correct, sql);
Assert.AreEqual(2, mapping.Columns.Count);
Assert.IsNotNull(mapping.Columns[1].Unique);
Assert.AreEqual(true, mapping.Columns.First(c => c.Name == "IsWorking").IsNullable);
Assert.AreEqual(1, mapping.PrimaryKey.Columns.Length);
Assert.AreEqual(ConflictResolution.Fail, mapping.OnPrimaryKeyConflict);
}
public DbSchema()
{
Products = TableMapping.Build <ProductPoco> ()
.TableName("Product")
.PrimaryKey(x => x.Id, autoIncrement: true)
.ToMapping();
Orders = TableMapping.Build <OrderPoco> ()
.TableName("Order")
.PrimaryKey(x => x.Id, autoIncrement: true)
.ToMapping();
OrderLines = TableMapping.Build <OrderLinePoco> ()
.TableName("OrderLine")
.PrimaryKey(x => x.Id, autoIncrement: true)
.Index("IX_OrderProduct", x => x.OrderId, x => x.ProductId)
.ToMapping();
OrderHistory = TableMapping.Build <OrderHistoryPoco> ()
.TableName("OrderHistory")
.PrimaryKey(x => x.Id, autoIncrement: true)
.ToMapping();
}
protected override IEnumerable <MigrationOperation> Remove(TableMapping source, DiffContext diffContext)
{
var type = source.GetRootType();
MigrationOperation operation;
if (!type.IsUserDefinedType())
{
var dropOperation = new DropTableOperation {
Schema = source.Schema, Name = source.Name
};
diffContext.AddDrop(source, dropOperation);
operation = dropOperation;
}
else
{
operation = new DropUserDefinedTypeOperation {
Schema = source.Schema, Name = source.Name
};
}
operation.AddAnnotations(MigrationsAnnotations.ForRemove(source.EntityTypes[0]));
yield return(operation);
}
public virtual bool ColumnIsTableOrForeignKeyToTable(
ReportColumnMapping column,
TableMapping joinTable)
{
if (column.KnownTable == joinTable.KnownTableName)
{
return(true);
}
// not sure if this will be needed. dont delete yet
// // if check if the foreign keys match
// var relationships = GetTableRelationships(column.KnownTable , joinTable.KnownTableName).ToList();
//
// foreach (var r in relationships)
// {
// if (r.Table1.KnownTableName == joinTable.KnownTableName
// && r.Table2.KnownTableName == column.KnownTable)
// {
// if (r.Table1Column == joinTable.PrimaryKey
// && r.Table2Column == column.FieldName)
// {
// return true;
// }
// }
// else if (r.Table2.KnownTableName == joinTable.KnownTableName
// && r.Table1.KnownTableName == column.KnownTable)
// {
// if (r.Table2Column == joinTable.PrimaryKey
// && r.Table1Column == column.FieldName)
// {
// return true;
// }
// }
// }
return(false);
}
public void Create <T, S>(T pExample, ReceiveRecordDelegate <S> pReceiveGoesHere)
{
try
{
string result = "";
bool isAuto = false;
string tblName = typeof(T).ToString();
var tblType = typeof(T);
// TableMapping map = AkeakeState.DB.Connection.GetMapping(tblType);
TableMapping map = GameManager.instance.database.Connection.GetMapping(tblType);
var PrimaryKeys = map.Columns.Where <TableMapping.Column>(
x =>
{
if (x.IsAutoInc)
{
isAuto = true; // a bit shonky
}
return(x.IsPK);
}
).ToList <TableMapping.Column>();
string pkName = PrimaryKeys.First <TableMapping.Column>().Name;
string jsnString = JsonConvert.SerializeObject(pExample);
jsnString = ReplacePKName(jsnString, pkName, isAuto);
Debug.Log("OK " + jsnString);
jsnString = "{\"CREATE\":\"" + tblName + "\",\"EXAMPLE\":" + jsnString + "}";
Post <S>(jsnString, pReceiveGoesHere);
// Return is via pReceiveGoesHere
}
catch (Exception ex)
{
throw (ex);
}
}
protected virtual object GetKeyParams(TableMapping mapping, object id)
{
int tableKeyCount = mapping.Table.Keys.Count;
if (tableKeyCount == 1)
{
//TODO Some Single Value Type is No Primitive or Value Type , Now Simple Implement With Special Compare
if (id.GetType().Equals(typeof(string)) || id.GetType().IsPrimitive || id.GetType().IsValueType)
{
return(new Dictionary <string, object>
{
{ mapping.Table.Keys[0].Name, id }
});
}
else
{
return(id);
}
}
else if (tableKeyCount > 1)
{
if (id.GetType().Equals(typeof(string)) || id.GetType().IsPrimitive || id.GetType().IsValueType)
{
throw new FoxOneException("table {0} has more than one field as key, so you must pass more then one param by Dictonary or Entity Class", mapping.Table.Name);
}
else
{
return(id);
}
}
if (tableKeyCount == 0)
{
throw new FoxOneException("the key of table {0} is absent, so you can't call dao's select or delete method", mapping.Table.Name);
}
return(id);
}
/// <summary>
/// 获取表超时时间
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public int GetTableTimeout <T>()
where T : class
{
//如果设置为空返回null
if (_Settings == null || _Settings.Length == 0)
{
return(60);
}
//通过Namespace与ClassName来获取映射的表名
string Namespace = typeof(T).Namespace;
string ClassName = typeof(T).Name;
var settings = new List <TableSetting>(_Settings);
TableSetting setting = settings.Find(p => p.Namespace == Namespace);
if (setting != null)
{
if (setting.Mappings != null && setting.Mappings.Length > 0)
{
//查询mapping的表名
var mappings = new List <TableMapping>(setting.Mappings);
TableMapping mapping = mappings.Find(p => p.ClassName == ClassName);
if (mapping != null)
{
return(mapping.Timeout);
}
return(60);
}
return(60);
}
return(60);
}
public void CreateEnumTable()
{
using (var db = new OrmTestSession())
{
db.CreateTable <EnumTable>();
var sql = new TableMapping(typeof(EnumTable)).GetCreateSql();
var correct =
@"CREATE TABLE [EnumTable] (
[Type] varchar(1) NOT NULL,
[Age] integer NOT NULL,
[Kind] text NOT NULL
);";
Assert.AreEqual(correct, sql);
db.Insert(new EnumTable
{
Type = EnumTable.PersonType.Child,
Age = EnumTable.PersonAge.ChildAge,
Kind = EnumTable.PersonKind.Junior
});
}
}
private string BuildSqlCmdTextForInsertData(
Dictionary <string, object> destColumnNameValueMappings,
TableMapping tableMapping)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO {0} ( ", tableMapping.Destination);
foreach (ColumnMapping columnMapping in tableMapping.ColumnMappings)
{
sb.AppendFormat("{0}, ", columnMapping.Destination);
}
sb.Remove(sb.Length - 2, 2);
sb.Append(") VALUES (");
foreach (ColumnMapping columnMapping in tableMapping.ColumnMappings) //build sql: insert into xx_table (filed1, ...., filedn) values (:filed1, ...., :fieldn)
{
sb.AppendFormat("{0}, ", GetParaSpliter() + columnMapping.Destination);
}
sb.Remove(sb.Length - 2, 2);
sb.Append(")");
return(sb.ToString());
}
/// <summary>
/// Delete all messages from this folder.
/// </summary>
internal void DeleteAll()
{
TableMapping map = CIX.DB.GetMapping <CIXMessage>();
if (map != null)
{
TableMapping.Column column = map.FindColumnWithPropertyName("TopicID");
StringBuilder deleteCmd = new StringBuilder();
if (IsRootFolder)
{
int[] subFolders = SubFolderIDs();
if (subFolders.Length == 0)
{
return;
}
deleteCmd.AppendFormat("delete from {0} where ", map.TableName);
for (int c = 0; c < subFolders.Length; ++c)
{
if (c > 0)
{
deleteCmd.Append(" or ");
}
deleteCmd.AppendFormat("{0}={1}", column.Name, subFolders[c]);
}
}
else
{
deleteCmd.AppendFormat("delete from {0} where {1}={2}", map.TableName, column.Name, ID);
}
lock (CIX.DBLock)
{
CIX.DB.Execute(deleteCmd.ToString());
}
}
Invalidate();
}
public TableMapping[] OrderTableMappings()
{
List <TableMapping> tableRefs = tableMappings.ToList();
List <TableMapping> tableRefOrder = new List <TableMapping>();
while (tableRefs.Count > 0)
{
TableMapping toRemove = null;
foreach (TableMapping tableMapping in tableRefs)
{
List <TableMapping> sourceMappings = tableMapping.ColumnMappings
.Where(c => c.GetType() == typeof(TableColumnMapping))
.Select(c => (TableColumnMapping)c)
.Where(c => tableRefs.Contains(c.SourceTableMapping))
.Select(c => c.SourceTableMapping)
.ToList();
if (sourceMappings.Count == 0)
{
toRemove = tableMapping;
break;
}
}
if (toRemove == null)
{
throw new ContainsCycleException("Cycle found in table mappings.");
}
tableRefs.Remove(toRemove);
tableRefOrder.Add(toRemove);
}
return(tableRefOrder.ToArray());
}
public long ReadDataAndCallback(TableMapping tableMapping, Func <IDataRecord, TableMapping, string, string> callback, string ftpUrl, out string msg)
{
long numOfRows = 0;
msg = string.Format("Source table: {0} \n", tableMapping.Source);
var cmd = GetDbCommand(BuildSelectCmdText(tableMapping), _conn);
cmd.CommandTimeout = _sqlCommandTimeout;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var ret = callback(reader, tableMapping, ftpUrl);
msg += ret;
if (!string.IsNullOrEmpty(ret))
{
numOfRows++;
}
}
}
return(numOfRows);
}
private IDictionary <string, string> MapColumnsToAliases(
IReadOnlyList <MemberBinding> selectBindings,
TableMapping <TEntity, TContext> mappings,
IList <string> selectLines)
{
var noOfBindings = selectBindings.Count;
var aliasRegex = new Regex(@".+ AS \[(?<alias>[A-Za-z0-9_]+)\]$");
var aliasMappings = new Dictionary <string, string>();
if (selectLines == null ||
selectLines.Count < noOfBindings + 2 ||
!selectLines[0].Trim().StartsWith("SELECT", StringComparison.OrdinalIgnoreCase) ||
!selectLines[noOfBindings + 1].Trim().StartsWith("FROM", StringComparison.OrdinalIgnoreCase))
{
throw new InvalidOperationException("Unexpected SQL generated in Select And Update");
}
for (var idx = 0; idx < noOfBindings; idx++)
{
var selectLine = selectLines[idx + 1].Trim().TrimEnd(',');
if (!aliasRegex.IsMatch(selectLine))
{
throw new InvalidOperationException("Unexpected SQL generated in Select And Update");
}
var alias = aliasRegex.Match(selectLine).Groups["alias"].Value;
var binding = selectBindings[idx];
var propertyName = binding.Member.Name;
var columnName = mappings.ColumnMappings[propertyName];
aliasMappings.Add(columnName, alias);
}
return(aliasMappings);
}
internal BasicFieldInfo(DataEntityMapping tableMapping, bool customName, string name)
{
if (tableMapping == null)
{
throw new System.ArgumentNullException(nameof(tableMapping));
}
if (name == null)
{
throw new System.ArgumentNullException(nameof(name));
}
_tableMapping = tableMapping;
if (customName)
{
_dataField = new CustomFieldMapping(name, tableMapping);
}
else
{
_dataField = TableMapping.FindDataEntityField(name);
if (_dataField == null)
{
_dataField = new CustomFieldMapping(name, tableMapping);
}
}
}
/// <summary>
/// This method is used to perform a mass-migration on a table in the database.
/// Essentially, this method renames the table, creates a new table using the same
/// name, and copies all the data from the old table to the new.
/// </summary>
/// <typeparam name="T"></typeparam>
private void RecreateTable <T>() where T : class
{
// Get the in-memory table mapping
TableMapping table = EntityCache.GetTableMap(typeof(T));
// Rename table
var newName = table.TableName + "_old";
Database.Execute($"ALTER TABLE `{table.TableName}` RENAME TO `{newName}`");
// Create new table
Database.CreateTable <T>();
// Select from old table, and import to the new table
// NOTE: had to do this the slow way because LowRpmRange_EngineBrake kept
// throwing a constraing failure (not null).
var items = Database.Query <T>($"SELECT * FROM `{newName}`");
var set = new DbSet <T>(Database);
set.AddRange(items);
// Drop old table
Database.Execute($"DROP TABLE `{newName}`");
}
[ResourceConsumption(ResourceScope.Assembly | ResourceScope.Machine)] // MappedRootType constructor call.
internal MappedTable(MappedMetaModel model, TableMapping mapping, Type rowType)
{
this.model = model;
this.mapping = mapping;
this.rowType = new MappedRootType(model, this, mapping.RowType, rowType);
}
private static TableMapping ReadTableMapping(XmlReader reader)
{
System.Diagnostics.Debug.Assert(reader.NodeType == XmlNodeType.Element);
if (!IsInNamespace(reader) || reader.LocalName != XmlMappingConstant.Table)
{
throw Error.UnexpectedElement(XmlMappingConstant.Table, String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}{1}{2}", reader.Prefix, String.IsNullOrEmpty(reader.Prefix) ? "" : "/", reader.LocalName));
}
ValidateAttributes(reader, new[] {
XmlMappingConstant.Name,
XmlMappingConstant.Member
});
TableMapping tm = new TableMapping();
tm.TableName = OptionalAttribute(reader, XmlMappingConstant.Name);
tm.Member = OptionalAttribute(reader, XmlMappingConstant.Member);
if (!reader.IsEmptyElement)
{
reader.ReadStartElement();
reader.MoveToContent();
while (reader.NodeType != XmlNodeType.EndElement)
{
if (reader.NodeType == XmlNodeType.Whitespace || !IsInNamespace(reader))
{
reader.Skip();
continue;
}
switch (reader.LocalName)
{
case XmlMappingConstant.Type:
if (tm.RowType != null)
{
goto default;
}
tm.RowType = ReadTypeMapping(null, reader);
break;
default:
throw Error.UnrecognizedElement(String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}{1}{2}", reader.Prefix, String.IsNullOrEmpty(reader.Prefix) ? "" : "/", reader.LocalName));
}
reader.MoveToContent();
}
if (reader.LocalName != XmlMappingConstant.Table)
{
throw Error.UnexpectedElement(XmlMappingConstant.Table, String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}{1}{2}", reader.Prefix, String.IsNullOrEmpty(reader.Prefix) ? "" : "/", reader.LocalName));
}
reader.ReadEndElement();
}
else
{
System.Diagnostics.Debug.Assert(false, "Table has no content");
reader.Skip();
}
return(tm);
}
public void TestSqlMapping()
{
MyMoney m = new MyMoney();
Account a = new Account();
a.Name = "Bank of America";
m.Accounts.AddAccount(a);
Transaction t = new Transaction();
t.Account = a;
t.Date = DateTime.Now;
t.Amount = -65.00M;
t.Payee = m.Payees.FindPayee("Costco", true);
t.Category = m.Categories.FindCategory("Food");
t.Memo = "something";
m.Transactions.AddTransaction(t);
string dbPath = System.IO.Path.GetTempPath();
string dbName = System.IO.Path.Combine(dbPath, "Test.MyMoney.db");
if (System.IO.File.Exists(dbName))
{
System.IO.File.Delete(dbName);
}
SqliteDatabase db = new SqliteDatabase();
db.DatabasePath = dbName;
db.Create();
db.Save(m);
// test we can add a column to the Transactions table.
TableMapping mapping = new TableMapping()
{
TableName = "Transactions"
};
mapping.ObjectType = typeof(Transaction);
var c = new ColumnMapping()
{
ColumnName = "Foo",
SqlType = typeof(SqlChars),
AllowNulls = true,
MaxLength = 20
};
mapping.Columns.Add(c);
db.CreateOrUpdateTable(mapping);
// make sure the new column exists
var metadata = db.LoadTableMetadata(mapping.TableName);
var d = (from i in metadata.Columns where i.ColumnName == "Foo" select i).FirstOrDefault();
Assert.IsNotNull(d);
Assert.AreEqual(d.MaxLength, 20);
// test we can change the max length
c.MaxLength = 50;
db.CreateOrUpdateTable(mapping);
// make sure the new column has new length
metadata = db.LoadTableMetadata(mapping.TableName);
d = (from i in metadata.Columns where i.ColumnName == "Foo" select i).FirstOrDefault();
Assert.IsNotNull(d);
Assert.AreEqual(d.MaxLength, 50);
// test we can drop the column
mapping.Columns.Remove(c);
db.CreateOrUpdateTable(mapping);
// verify it's gone!
metadata = db.LoadTableMetadata(mapping.TableName);
d = (from i in metadata.Columns where i.ColumnName == "Foo" select i).FirstOrDefault();
Assert.IsNull(d);
// test we can still load the modified database!
MyMoney test = db.Load(null);
Account b = test.Accounts.FindAccount(a.Name);
Assert.IsNotNull(b);
Transaction s = test.Transactions.GetTransactionsFrom(b).FirstOrDefault();
Assert.IsNotNull(s);
Assert.AreEqual(t.Date, s.Date);
Assert.AreEqual(t.Amount, s.Amount);
Assert.AreEqual(t.CategoryFullName, s.CategoryFullName);
Assert.AreEqual(t.PayeeName, s.PayeeName);
Assert.AreEqual(t.Memo, s.Memo);
}
public DefaultSqlBuilder(TableMapping tableMapping)
{
_tableMapping = tableMapping;
}
public void CreateIndexesSqlTest()
{
var tableMap = new TableMapping(typeof(IndexedTable));
var sql = tableMap.Indexes.Single().GetCreateSql("IndexedTable");
var correct = @"CREATE UNIQUE INDEX [IX_TabelIndex] on [IndexedTable] (
[Indexed]
);";
Assert.AreEqual(correct, sql);
var tblIdx = tableMap.Indexes.Where(i => i.IndexName == "IX_TabelIndex").ToArray();
Assert.AreEqual(1, tblIdx.Count());
Assert.AreEqual(true, tblIdx.First().Unique);
}
public bool InsertUpdateTableMapping(TableMapping item)
{
return(new DLTableMapping().InsertUpdateObject(item));
}
public static NotNullConstraintViolationException New(SQLiteException exception, TableMapping mapping,
object obj)
{
return(new NotNullConstraintViolationException(exception.Result, exception.Message, mapping, obj));
}
internal Table(ExcelFile file, TableMapping mapping, Table parent)
{
this.file = file;
this.Name = mapping.Name;
this.Identifier = mapping.Identifier;
this.Parent = parent;
parent.children.Add(this);
this.ParentIdentifier = mapping.ParentIdentifier;
// Find the specified worksheet
var sheet = file.workbook.Descendants<Sheet>().FirstOrDefault(s => s.Name.Value == mapping.Name);
if (sheet == null)
throw new ArgumentException("A worksheet named '" + mapping.Name + "' was not found.");
this.worksheet = ((WorksheetPart)file.spreadsheet.WorkbookPart.GetPartById(sheet.Id)).Worksheet;
// Load child tables
foreach (var child in mapping.Children)
children.Add(new Table(file, child, this));
}
internal static TableMapping.PrimaryKeyDefinition GetPrimaryKey(
IEnumerable<TableMapping.Column> columns,
out TableMapping.Column autoIncCol)
{
TableMapping.PrimaryKeyDefinition primaryKey = null;
autoIncCol = null;
var pkCols = columns.Where(c => c.PrimaryKey != null).OrderBy(c => c.PrimaryKey.Order).ToArray();
if (pkCols.Any())
{
primaryKey = new TableMapping.PrimaryKeyDefinition
{
Columns = pkCols,
Name = GetPrimaryKeyName(pkCols)
};
var autoInc = primaryKey.Columns.Where(c => c.IsAutoIncrement).ToArray();
if (autoInc.Count() > 1)
{
throw new SqliteException((int)SQLiteErrorCode.Error,
"Only one property can be an auto incrementing primary key");
}
autoIncCol = autoInc.FirstOrDefault();
}
return primaryKey;
}
public TableTreeNode(TableMapping tableMapping)
{
_tableMapping = tableMapping;
Initialize();
}
public TableModelHelper(DataTable ClassDef, TableMapping TableDef)
{
_dt_Condition = InitConditionTable();
_dt_ClassDef = ClassDef;
_cad_TableDef = TableDef;
}
protected NotNullConstraintViolationException(SQLite3.Result r, string message, TableMapping mapping,
object obj) : base(r, message)
{
if (mapping != null && obj != null)
{
this.Columns = from c in mapping.Columns
where c.IsNullable == false && c.GetValue(obj) == null
select c;
}
}
private void DisableTriggers(TableMapping map, SQLiteConnection connection)
{
try
{
String triggerDeleteQuery = String.Format(SQLiteConstants.DeleteTriggerAfterDelete, map.TableName);
String triggerInsertQuery = String.Format(SQLiteConstants.DeleteTriggerAfterInsert, map.TableName);
String triggerUpdateQuery = String.Format(SQLiteConstants.DeleteTriggerAfterUpdate, map.TableName);
using (var statement = connection.Prepare(triggerDeleteQuery))
{
statement.Step();
}
using (var statement = connection.Prepare(triggerInsertQuery))
{
statement.Step();
}
using (var statement = connection.Prepare(triggerInsertQuery))
{
statement.Step();
}
//await connection.ExecuteStatementAsync(triggerDeleteQuery).AsTask().ConfigureAwait(false);
//await connection.ExecuteStatementAsync(triggerInsertQuery).AsTask().ConfigureAwait(false);
//await connection.ExecuteStatementAsync(triggerUpdateQuery).AsTask().ConfigureAwait(false);
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
throw;
}
}
public static NotNullConstraintViolationException New(SQLite3.Result r, string message, TableMapping mapping,
object obj)
{
return(new NotNullConstraintViolationException(r, message, mapping, obj));
}
public void CreateEnumTable()
{
using (var db = new OrmTestSession())
{
db.CreateTable<EnumTable>();
var sql = new TableMapping(typeof(EnumTable)).GetCreateSql();
var correct = @"CREATE TABLE [EnumTable] (
[Type] varchar(1) NOT NULL,
[Age] integer NOT NULL,
[Kind] text NOT NULL
);";
Assert.AreEqual(correct, sql);
db.Insert(
new EnumTable
{
Type = EnumTable.PersonType.Child,
Age = EnumTable.PersonAge.ChildAge,
Kind = EnumTable.PersonKind.Junior
});
}
}
public void CreateTableHeatmap()
{
TableMapping TableMapping = new TableMapping();
LegendHeatmap.LegendCollection.Add("LegendHeatmapLegend");
TableMapping.HeaderMapping = new HeaderMapping()
{
PropertyName = "EmpName", DisplayName = "Employee Name", ColumnStyle = new ColumnStyle()
{
Width = 100, TextAlign = HeatMapTextAlign.Right
}
};
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "January", DisplayName = "January"
});
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "February", DisplayName = "February"
});
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "March", DisplayName = "March"
});
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "April", DisplayName = "April"
});
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "May", DisplayName = "May"
});
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "June", DisplayName = "June"
});
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "July", DisplayName = "July"
});
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "August", DisplayName = "August"
});
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "September", DisplayName = "September"
});
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "October", DisplayName = "October"
});
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "November", DisplayName = "November"
});
TableMapping.ColumnMapping.Add(new HeaderMapping()
{
PropertyName = "December", DisplayName = "December"
});
LegendHeatmap.ItemsMapping = TableMapping;
LegendHeatmap.ItemsSource = GetLegendSource();
}
internal static string CompileFromClause(params Type[] types) =>
"FROM " + string.Join(", ", types.Select(x => "\"" + TableMapping.Get(x).TableName + "\""));
protected string Ftp2SS(IDataRecord obj, TableMapping tableMapping, string ftpUrl)
{
//1, find on FTP, if exception insert failure record or update the roginal record satus to failure
//2, call webservice, if success, insert successed record or update previous failed record to successed on destination db
//when need sync file before lastsyncedTime, if file has been synced, return. then synced.
var table = TableMappingFactory.BuildTable(tableMapping);
if (tableMapping.IsCheckFileSynced)
{
if (table.IsFileSynced(tableMapping, obj))
{
return("");
}
}
Stream responseStream;
var fileId = table.GetFileId(obj);
var file = obj[tableMapping.PathColumn].ToString().Replace(@"\", "/");
if (string.IsNullOrEmpty(file))
{
table.AddOrUpdate(tableMapping, obj, 0);
return(string.Format("sync failed. source table {0} column {1} is empty: \n", tableMapping.Source, tableMapping.PathColumn));
}
var uri = ftpUrl + file;
try
{
responseStream = FileUtil.GetStreamFromFTP(uri);
}
catch (Exception ex)
{
table.AddOrUpdate(tableMapping, obj, 0);
return(string.Format("{0} sync failed. Get exception from FTP \n source table: {1} \n {2} \n", uri, tableMapping.Source, ex));
}
using (var ms = new MemoryStream())
{
const int Length = 2048;
var buffer = new Byte[Length];
int read = 0;
while (responseStream != null && (read = responseStream.Read(buffer, 0, Length)) > 0)
{
ms.Write(buffer, 0, read);
}
var ext = Path.GetExtension(file);
var subDirectoryPath = file.Substring(0, file.LastIndexOf("/"));
var path = subDirectoryPath.IndexOf("/") == 0
? string.Format("{0}{1}", tableMapping.DestinationFilePath, subDirectoryPath)
: string.Format("{0}/{1}", tableMapping.DestinationFilePath, subDirectoryPath);
bool success;
try
{
success = FileUtil.CallStorageService(path, ms.ToArray(), string.Format("{0}{1}", fileId, ext));
}
catch (Exception ex)
{
throw new CustomException(string.Format("Get exception when call storage service\n " +
"webserviceUrl: {0} \n fileId: {1} \n source table: {2} \n {3} \n", ConfigHelper.GetEndpointClientAddress("StorageServiceImplPort"), fileId, tableMapping.Source, ex));
}
finally
{
if (responseStream != null)
{
responseStream.Close();
}
}
try
{
if (success)
{
table.AddOrUpdate(tableMapping, obj);
}
}
catch (Exception ex)
{
throw new CustomException(string.Format("Get exception when insert FileDB service\n " +
"fileId: {0} \n destination table: {1} \n {2} \n", fileId, tableMapping.Destination, ex));
}
}
return(string.Format("{0} sync succeed \n", uri));
}
public TableViewController (TableMapping table, SQLiteConnection connection)
{
if (table == null)
throw new ArgumentNullException ("table");
if (connection == null)
throw new ArgumentNullException ("connection");
Table = table;
Connection = connection;
Rows = new List<object>();
UITable = new UITableView (SQLiteAdmin.GetTableRect(), UITableViewStyle.Plain);
UITable.DataSource = new Data (this);
GetMoreData();
UITable.ReloadData();
}
protected override IEnumerable <MigrationOperation> Diff(TableMapping source, TableMapping target, DiffContext diffContext)
{
var operations = base.Diff(source, target, diffContext);
foreach (var migrationOperation in operations)
{
if (migrationOperation is AlterTableOperation tableOperation)
{
if (tableOperation.IsChangeTrackingEnabled())
{
yield return(new EnableChangeTrackingForTableOperation(target.Name, target.Schema, tableOperation.ChangeTrackingTrackColumns()));
}
else if (tableOperation.OldTable.IsChangeTrackingEnabled())
{
yield return(new DisableChangeTrackingForTableOperation(target.Name, target.Schema));
}
else
{
yield return(migrationOperation);
}
}
else
{
yield return(migrationOperation);
}
}
}
/// <summary>
/// TreeNode结点,右键菜单选中事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmsSheetNode_ItemClicked(Object sender, ToolStripItemClickedEventArgs e)
{
//获取所点击的结点名称
String sheetName = treeViewExcels.SelectedNode.Text;
//复制表名
if (e.ClickedItem.Text.Contains(ConstantText.CopySheetName))
{
Clipboard.SetText(sheetName);
return;
}
//映射该表
if (e.ClickedItem.Text.Contains(ConstantText.MappingTable))
{
//实例化弹出映射窗体
TableMapping tableMapping = new TableMapping(sheetName, mExcelFileInfos[treeViewExcels.SelectedNode.Parent.Text], this);
tableMapping.ShowDialog(this);
//改变所选节点,映射状态
XMLHelper xmlHelper = new XMLHelper("TableMapping.xml");
if (xmlHelper.GetTableNameMapping(sheetName) != null)
{
treeViewExcels.SelectedNode.ForeColor = Color.Red;
treeViewExcels.SelectedNode.ToolTipText = ConstantText.MappingExist;
}
else
{
treeViewExcels.SelectedNode.ForeColor = Color.Black;
treeViewExcels.SelectedNode.ToolTipText = ConstantText.ClickToCopy;
}
}
}
public static void Delete(int id, Type type)
{
var tableMapping = new TableMapping(type);
DBAccess.Connection.Delete(id, tableMapping);
}
private void PrintOutTableSchema(TableMapping mapping)
{
var columns = mapping.Columns;
MyLogger.Information (logTag, string.Format ("{0} Table Created. Columns: {1}", mapping.TableName, string.Join (", ", columns.Select (x => x.Name).ToArray ())));
}
