private void RenderPropertyReference(string propertyReference)
{
var col = _mappings.FindColumnWithPropertyName(propertyReference);
if (col != null)
{
_sb.Append(col.Name);
}
else
{
_sb.Append(propertyReference);
}
}
/// <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();
}
private ISectionInformation[] ResolveGroupInformation()
{
UpdateSelect();
UpdateFilterClause();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
for (var i = 0; i < _groupDescriptions.Count; i++)
{
if (i > 0)
{
sb.Append(", ");
}
var propertyName = _groupDescriptions[i].PropertyName;
var col = _propertyMappings.FindColumnWithPropertyName(propertyName);
if (col != null)
{
propertyName = col.Name;
}
sb.Append(propertyName);
}
var groupingField = _groupingField;
if (groupingField == null)
{
if (ActualSchema != null &&
ActualSchema.PrimaryKey != null &&
ActualSchema.PrimaryKey.Length > 0)
{
groupingField = ActualSchema.PrimaryKey[0];
int index = -1;
for (var i = 0; i < ActualSchema.PropertyNames.Length; i++)
{
if (ActualSchema.PropertyNames[i] == groupingField)
{
index = i;
break;
}
}
if (index == -1 || !IsIntegral(ActualSchema.PropertyTypes[index]))
{
groupingField = null;
}
}
}
if (groupingField == null)
{
throw new NotSupportedException("You must specify a valid grouping column to use to accumulate the count of groups for the target table.");
}
sb.Append(", COUNT(" + groupingField + ") as " + groupingField);
AddTableExpression(sb);
AddFilterClause(sb);
sb.Append(" GROUP BY ");
for (var i = 0; i < _groupDescriptions.Count; i++)
{
if (i > 0)
{
sb.Append(", ");
}
var propertyName = _groupDescriptions[i].PropertyName;
var col = _propertyMappings.FindColumnWithPropertyName(propertyName);
if (col != null)
{
propertyName = col.Name;
}
sb.Append(propertyName);
}
AddOrderByClause(sb);
EnsureSpecific();
var t = (Task)_specific.Invoke(_connection, new object[] { sb.ToString(), new object[] { } });
t.Wait();
var resultProp = t.GetType().GetTypeInfo().GetDeclaredProperty("Result");
var data = (IList)resultProp.GetValue(t);
List <ISectionInformation> groupInformation = new List <ISectionInformation>();
List <string> groupNames = new List <string>();
foreach (var group in _groupDescriptions)
{
groupNames.Add(group.PropertyName);
}
var groupNamesArray = groupNames.ToArray();
int currentIndex = 0;
foreach (var group in data)
{
AddGroup(groupInformation, groupNames,
groupNamesArray, currentIndex,
group, groupingField);
}
return(groupInformation.ToArray());
}
/// <summary>
/// Updates the table in the database to match the latest database
/// Adds and removes colums
/// Adds data
/// Removes data if parameter equals true
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="removeOld"></param>
public void UpdateTable <T>(bool removeOld) where T : Model, new()
{
TableMapping cvcTableMapping = _connectionVersionCheck.GetMapping <T>();
List <SQLiteConnection.ColumnInfo> cColumnInfo = _connection.GetTableInfo(typeof(T).ToString());
//Columns that need to be added
List <TableMapping.Column> addColumns = cvcTableMapping.Columns.Where(x => cColumnInfo.Where(y => y.Name.Equals(x.Name)).Count() == 0).ToList();
//Add each column
foreach (TableMapping.Column c in addColumns)
{
//Debug.Log(typeof(T) + " / " + c.Name + " / " + c.ColumnType.Name);
string query = string.Format("ALTER TABLE {0} ADD {1} {2}", typeof(T).ToString(), c.Name, GetDatabaseType(c.ColumnType.Name));
//Debug.Log(query);
_connection.Execute(query);
}
//Get updated table mapping
cvcTableMapping = _connectionVersionCheck.GetMapping <T>();
//Get updated column info
cColumnInfo = _connection.GetTableInfo(typeof(T).ToString());
//Columns that need to be removed
List <SQLiteConnection.ColumnInfo> removeColumns = cColumnInfo.Where(x => cvcTableMapping.Columns.Where(y => y.Name.Equals(x.Name)).Count() == 0).ToList();
//If there are columns that need to be removed
if (removeColumns.Count > 0)
{
//Create backup of table
string query1 = "CREATE TEMPORARY TABLE table_backup (";
query1 += cvcTableMapping.FindColumnWithPropertyName("id").Name + " PRIMARY KEY NOT NULL, ";
foreach (TableMapping.Column c in cvcTableMapping.Columns)
{
if (c.Name != "id")
{
query1 += c.Name + ",";
}
}
query1 = query1.Remove(query1.Length - 1);
query1 += ")";
//insert backup data into table
string query2 = "INSERT INTO table_backup SELECT id, ";
foreach (TableMapping.Column c in cvcTableMapping.Columns)
{
if (c.Name != "id")
{
query2 += c.Name + ",";
}
}
query2 = query2.Remove(query2.Length - 1);
query2 += " from " + typeof(T).ToString();
//Drop table with columns that need to be removed
string query3 = "DROP TABLE " + typeof(T).ToString();
//Create new table acording to new database structure
string query4 = "CREATE TABLE \"" + typeof(T).ToString() + "\"('id' INTEGER PRIMARY KEY NOT NULL, ";
foreach (TableMapping.Column c in cvcTableMapping.Columns)
{
if (c.Name != "id")
{
query4 += "'" + c.Name + "'" + GetDatabaseType(c.ColumnType.Name.ToString()) + ",";
}
}
query4 = query4.Remove(query4.Length - 1);
query4 += ")";
//Insert backup data in new table
string query5 = "INSERT INTO " + typeof(T).ToString() + " SELECT * FROM table_backup";
//Remove backup table
string query6 = "DROP TABLE table_backup";
_connection.Execute(query1);
_connection.Execute(query2);
_connection.Execute(query3);
_connection.Execute(query4);
_connection.Execute(query5);
_connection.Execute(query6);
}
//List of data to be cecked
List <T> checkList = _connectionVersionCheck.Table <T>().ToList();
//Foreach row in the new database table
foreach (T t in checkList)
{
List <T> test = _connection.Table <T>().ToList();
int id = t.id;
//Get corresponding data entry
List <T> temp = test.Where(x => (x.id) == (id)).ToList();
//If the corresponding data entry is found
if (temp.Count() > 0)
{
//Copy data to the excisting entry
T old = temp.First();
old.Copy(t);
_connection.Update(old);
}
else
{
//No corresponding data entry is found
//Insert new data entry
_connection.Insert(t);
}
}
//If old values need to be removed
if (removeOld)
{
//Get all the data entries
List <T> deleteList = _connection.Table <T>().ToList();
//Select data entries not found in the new database
deleteList = deleteList.Where(x => _connectionVersionCheck.Table <T>().Where(y => y.id == x.id).Count() == 0 && x.id != 0).ToList();
//Foreach data entry that needs to be deleted
foreach (T t in deleteList)
{
//Remove from database
_connection.Delete(t);
}
}
}
public bool IsDatabaseColumn(string propertyName)
{
return(TableMapping.FindColumnWithPropertyName(propertyName) != null);
}
public string GetColumnName(string propertyName)
{
return(TableMapping.FindColumnWithPropertyName(propertyName).Name);
}
