public void ImplicitIndex() { var db = new TestDb(); db.CreateTable <NoAttributes>(CreateFlags.ImplicitIndex); TableMapping mapping = db.GetMapping <NoAttributes>(); TableMapping.Column column = mapping.Columns[2]; Assert.AreEqual("IndexedId", column.Name); Assert.IsTrue(column.Indices.Any()); }
public void WithoutImplicitMapping() { var db = new TestDb(); db.CreateTable <NoAttributes>(); TableMapping mapping = db.GetMapping <NoAttributes>(); Assert.IsNull(mapping.PK); TableMapping.Column column = mapping.Columns[2]; Assert.AreEqual("IndexedId", column.Name); Assert.IsFalse(column.Indices.Any()); Assert.Throws(typeof(AssertionException), () => CheckPK(db)); }
public void CreateVeryAdvancedTable() { var db = new OrmTestSession(); db.CreateTable <VeryAdvancedTable>(); TableMapping mapping = db.GetMapping <VeryAdvancedTable>(); var sql = mapping.GetCreateSql(); var correct = @"CREATE TABLE [VeryAdvancedTable] ( [Id] integer NOT NULL CHECK (Id <= 25), [DiffName] varchar(255) COLLATE RTrim, [IsWorking] integer NOT NULL DEFAULT(1), [AnotherId] integer NOT NULL, CONSTRAINT PK_MyPrimaryKey PRIMARY KEY ([AnotherId], [Id] Desc), CHECK (Id <= 10) );"; Assert.AreEqual(correct, sql); Assert.AreEqual("Id <= 10", mapping.Checks.First()); Assert.AreEqual(2, mapping.PrimaryKey.Columns.Length); Assert.AreEqual("AnotherId", mapping.PrimaryKey.Columns[0].Name); Assert.AreEqual("Id", mapping.PrimaryKey.Columns[1].Name); TableMapping.Column idCol = mapping.Columns.First(c => c.Name == "Id"); Assert.AreEqual("Id <= 25", idCol.Checks.First()); Assert.AreEqual("PK_MyPrimaryKey", idCol.PrimaryKey.Name); Assert.AreEqual(Direction.Desc, idCol.PrimaryKey.Direction); TableMapping.Column difCol = mapping.Columns.First(c => c.Name == "DiffName"); Assert.AreEqual(255, difCol.MaxStringLength); Assert.AreEqual(Collation.RTrim, difCol.Collation); TableMapping.Column workinCol = mapping.Columns.First(c => c.Name == "IsWorking"); Assert.IsFalse(workinCol.IsNullable); Assert.AreEqual("1", workinCol.DefaultValue); Assert.IsFalse(mapping.Columns.Any(c => c.Name == "IgnoredColumn")); }
public IEnumerable <T> ExecuteDeferredQuery <T>(TableMapping map) { if (_conn.Trace) { Debug.WriteLine("Executing Query: " + this); } var stmt = Prepare(); try { var cols = new TableMapping.Column[SQLite3.ColumnCount(stmt)]; for (int i = 0; i < cols.Length; i++) { var name = SQLite3.ColumnName16(stmt, i); cols[i] = map.FindColumn(name); } while (SQLite3.Step(stmt) == SQLite3.Result.Row) { var obj = Activator.CreateInstance(map.MappedType); for (int i = 0; i < cols.Length; i++) { if (cols[i] == null) { continue; } var colType = SQLite3.ColumnType(stmt, i); var val = ReadCol(stmt, i, colType, cols[i].ColumnType); cols[i].SetValue(obj, val); } OnInstanceCreated(obj); yield return((T)obj); } } finally { SQLite3.Finalize(stmt); } }
/// <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(); }
/// <summary> /// Get all changes fro SQLite Database /// </summary> /// <param name="schema">All Tables</param> /// <param name="lastModifiedDate">Changes since this date</param> internal IEnumerable<SQLiteOfflineEntity> GetChanges(OfflineSchema schema, DateTime lastModifiedDate) { List<SQLiteOfflineEntity> lstChanges = new List<SQLiteOfflineEntity>(); using (SQLiteConnection connection = new SQLiteConnection(localFilePath)) { try { foreach (var ty in schema.Collections) { // Get mapping from my type var map = manager.GetMapping(ty); // Create query to select changes var querySelect = SQLiteConstants.SelectChanges; var columnsDcl = new List<String>(); string pkColumnName = string.Empty; // Foreach columns, create the tsql command to execute foreach (var c in map.Columns) { if (!c.IsPK) columnsDcl.Add("[s].[" + c.Name + "]"); // If it's the PK, add it from Tracking (because of deleted items not in real table if (c.IsPK) { columnsDcl.Add("[t].[" + c.Name + "]"); pkColumnName = c.Name; } } var decl = string.Join(",\n", columnsDcl.ToArray()); querySelect = String.Format(querySelect, map.TableName, pkColumnName, decl); // Prepare command using (var stmt = connection.Prepare(querySelect)) { try { // Set Values BindParameter(stmt, 1, lastModifiedDate); // Get mapping form the statement var cols = new TableMapping.Column[map.Columns.Length]; // Foreach column, get the property in my object for (int i = 0; i < cols.Length; i++) { var name = stmt.ColumnName(i); var c = map.FindColumn(name); if (c != null) cols[i] = map.FindColumn(name); } // While row is available //while (await stmt.StepAsync().AsTask().ConfigureAwait(false)) while (stmt.Step() == SQLiteResult.ROW) { // Create the object SQLiteOfflineEntity obj = (SQLiteOfflineEntity)Activator.CreateInstance(map.MappedType); for (int i = 0; i < cols.Length; i++) { if (cols[i] == null) continue; // Read the column var val = ReadCol(stmt, i, cols[i].ColumnType); // Set the value cols[i].SetValue(obj, val); } // Read the Oem Properties var newIndex = map.Columns.Count(); obj.ServiceMetadata = new OfflineEntityMetadata(); obj.ServiceMetadata.IsTombstone = (Boolean)ReadCol(stmt, newIndex, typeof(Boolean)); obj.ServiceMetadata.Id = (String)ReadCol(stmt, newIndex + 1, typeof(String)); obj.ServiceMetadata.ETag = (String)ReadCol(stmt, newIndex + 2, typeof(String)); String absoluteUri = (String)ReadCol(stmt, newIndex + 3, typeof(String)); obj.ServiceMetadata.EditUri = String.IsNullOrEmpty(absoluteUri) ? null : new Uri(absoluteUri); lstChanges.Add(obj); } } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } finally { stmt.Reset(); stmt.ClearBindings(); } } } } catch (Exception ex) { Debug.WriteLine(ex.Message); throw; } } return lstChanges; }
/// <summary> /// 将表格转换成字段 /// </summary> /// <typeparam name="TKey">表格主键作为字典的 key </typeparam> /// <typeparam name="TValue">表格每一条数据作为字典的 value </typeparam> /// <param name="func"></param> /// <returns></returns> public Dictionary <TKey, TValue> TableAsDictionary <TKey, TValue>(Func <TValue, TKey> func) where TValue : new() { #if RUNINSERVER var e = TableAsList <TValue>(); int n = e.Count; Dictionary <TKey, TValue> dir = new Dictionary <TKey, TValue>(n); for (var i = 0; i < n; i++) { var value = e[i]; dir.Add(func(value), value); } return(dir); #else var map = GetMapping(typeof(TValue)); var connection = _connection; var str = typeof(TValue).Name; connection.CommandText = "select * from " + str; connection.Prepare(); var n = connection.GetColumnCont(); var dir = new Dictionary <TKey, TValue>(n); try { var cols = new TableMapping.Column[n]; for (var i = 0; i < n; i++) { var name = connection.ColumnName16(i); var col = map.FindColumn(name); cols[i] = col; if (col == null) { Debug.LogError("table:" + str + " ,col:" + name + " is Null"); throw new Exception(); } } var t = map.MappedType; while (connection.Step() == SQLite3.Result.Row) { var obj = Activator.CreateInstance(t); for (var i = 0; i < n; i++) { var col = cols[i]; var val = connection.ReadCol(i, col.ColumnType); col.FieldInfo.SetValue(obj, val); } var tv = (TValue)obj; var tk = func(tv); dir.Add(tk, tv); } } catch (Exception e) { Debug.LogErrorFormat("{0}\n : {1}", str, e); } finally { connection.FinalizeStmt(); } return(dir); #endif }
public List <T> TableAsList <T>() where T : new() { var map = GetMapping(typeof(T)); var str = typeof(T).Name; var query = "select * from " + str; #if !RUNINSERVER var connection = _connection; connection.CommandText = query; connection.Prepare(); var n = connection.GetColumnCont(); var list = new List <T>(n); try { var cols = new TableMapping.Column[n]; for (var i = 0; i < n; i++) { var name = connection.ColumnName16(i); var col = map.FindColumn(name); cols[i] = col; if (col == null) { Debug.LogError("table:" + str + " ,col:" + name + " is Null"); throw new Exception(); } } var t = map.MappedType; while (connection.Step() == SQLite3.Result.Row) { var obj = Activator.CreateInstance(t); for (var i = 0; i < n; i++) { var col = cols[i]; var val = connection.ReadCol(i, col.ColumnType); col.FieldInfo.SetValue(obj, val); } list.Add((T)obj); } } catch (Exception e) { Debug.LogErrorFormat("{0}\n : {1}", str, e); } finally { connection.FinalizeStmt(); } return(list); #else System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.Connection = _connection; cmd.CommandText = query; //cmd.Prepare(); Debug.Log("query:" + query); System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); int n = reader.FieldCount; Debug.Log("FieldCount:" + n + " reader.StepCount:" + reader.StepCount); //cmd. List <T> list = new List <T>(n); try { var cols = new TableMapping.Column[n]; for (int i = 0; i < cols.Length; i++) { var name = reader.GetName(i); cols[i] = map.FindColumn(name); //if (cols[i] == null) Debug.LogError("table:" + typeof(T).Name + " ,col:" + name + " " + cols[i].Name); } //for (int j=0;j<reader.StepCount;j++) while (reader.Read()) { //Debug.Log("int read:"+ cols.Length); var obj = Activator.CreateInstance(map.MappedType); for (int i = 0; i < cols.Length; i++) { //if (cols[i] == null) // continue; var colType = reader.GetFieldType(i); object val = reader.GetValue(i);; // _connection.ReadCol(i, colType, cols[i].ColumnType); //Debug.Log("val:" + i + " " + val); if (colType == typeof(double)) { //val = 2.5f;//reader.GetFloat(i); float vv = (float)((double)val); cols[i].FieldInfo.SetValue(obj, vv); } else { cols[i].FieldInfo.SetValue(obj, val); } } list.Add((T)obj); } } catch (Exception e) { Debug.LogErrorFormat("{0}\n : {1}", typeof(T).Name, e); } finally { //_connection.Close(); } return(list); #endif }