// Кнопка подтверждения удаления
private async void confirmDeleteButton_Click(object sender, EventArgs e)
{
DeleteResult deleteResult = DeleteResult.Failed;
DbTable tableName = TableNameToEnumConverter.Convert(tableComboBox.AccessibilityObject.Value?.ToString());
IDbTableManager dbTableManager = GetTableManager(tableName);
string recordId = deleteRecordIdTextBox.Text;
string resultMessage = "Something went wrong, try again";
if (string.IsNullOrEmpty(recordId))
{
resultMessage = "Record ID field is required, try again";
}
else
{
deleteResult = await dbTableManager.DeleteRecord(int.Parse(recordId));
if (deleteResult == DeleteResult.RecordNotFound)
{
resultMessage = "Record not found, try again";
}
else if (deleteResult == DeleteResult.Success)
{
resultMessage = "Record was successfully deleted";
}
}
MessageBox.Show(resultMessage, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
if (deleteResult == DeleteResult.Success)
{
deletePanel.Visible = false;
deleteRecordIdTextBox.Text = "";
await DataGridDataInitialize(tableName);
}
}
public Dictionary <UbjectMetadata, object> GetObjectsIncMetadata(Type type, List <Tuple <string, string, string> > filterProperties = null)
{
Dictionary <UbjectMetadata, object> ubjects = new Dictionary <UbjectMetadata, object>();
IDbTableManager databaseTable = DbManager.GetDbTableManagerForObject(type, false);
if (databaseTable != null)
{
ubjects = databaseTable.GetObjects(type, filterProperties);
if (ubjects.Count > 0)
{
var objectAnatomyDefinition = GetObjectAnatomyDefinition(ubjects.Values.First());
foreach (var collectionObject in objectAnatomyDefinition.CollectionReferenceTypeObjects)
{
IEnumerator enumerator = collectionObject.Value.GetEnumerator();
Type collectionItemType = enumerator.GetType().GetGenericArguments()[0];
IDbTableManager childDatabaseTable = DbManager.GetDbTableManagerForObject(collectionItemType, false);
if (childDatabaseTable != null)
{
foreach (KeyValuePair <UbjectMetadata, object> ubject in ubjects)
{
List <Tuple <string, string, string> > mappingFilterCondition = new List <Tuple <string, string, string> >();
mappingFilterCondition.Add(new Tuple <string, string, string>(parentTableKey, databaseTable.TableName, "="));
mappingFilterCondition.Add(new Tuple <string, string, string>(parentObjectKey, ubject.Key.PrimaryKey.ToString(), "="));
mappingFilterCondition.Add(new Tuple <string, string, string>(childTableKey, childDatabaseTable.TableName, "="));
List <UbjectMapping> ubjectMappings = GetObjects <UbjectMapping>(mappingFilterCondition);
List <object> childObjects = new List <object>();
foreach (var ubjectMapping in ubjectMappings)
{
List <Tuple <string, string, string> > childObjectFilterCondition = new List <Tuple <string, string, string> >();
childObjectFilterCondition.Add(new Tuple <string, string, string>(UbjectMetadata.PRIMARY_KEY, ubjectMapping.ChildObjectKey, "="));
childObjects.AddRange(GetObjectsIncMetadata(collectionItemType, childObjectFilterCondition).Values);
}
PropertyInfo propertyInfo = type.GetProperty(collectionObject.Key, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
MethodInfo methodInfo = propertyInfo.PropertyType.GetMethod("Add");
object collectionInstance = Activator.CreateInstance(propertyInfo.PropertyType);
foreach (object childObject in childObjects)
{
methodInfo.Invoke(collectionInstance, new object[] { childObject });
}
propertyInfo.SetValue(ubject.Value, collectionInstance, null);
}
}
}
}
}
return(ubjects);
}
public void RemoveAllObjects <T>()
{
IDbTableManager databaseTable = DbManager.GetDbTableManagerForObject(typeof(T));
if (databaseTable != null)
{
databaseTable.DeleteAllObjects();
}
}
public override IDbTableManager CreateDatabaseTable(string tableName, List <PropertyInfo> properties)
{
IDbTableManager databaseTable = Utilities.CreateDbTableManager(this);
databaseTable.TableName = (string.IsNullOrEmpty(tableName) ? Guid.NewGuid().ToString().ToUpper() : tableName);
StringBuilder commandText = new StringBuilder();
commandText.Append(string.Format("{0} `{1}`.`{2}`", "CREATE TABLE ", SchemaName, databaseTable.TableName));
commandText.Append("(");
commandText.Append(string.Format("`{0}` {1} {2} {3} {4}, ", UbjectMetadata.PRIMARY_KEY, "INT(11)", "UNIQUE", "NOT NULL", "AUTO_INCREMENT").Trim());
commandText.Append(string.Format("`{0}` {1} {2} {3} {4}, ", UbjectMetadata.UBJECT_HASH, "VARCHAR(100)", "", "NOT NULL", "").Trim());
commandText.Append(string.Format("`{0}` {1} {2} {3} {4}, ", UbjectMetadata.UBJECT_TIMESTAMP, "BIGINT", "", "NOT NULL", "").Trim());
foreach (PropertyInfo property in properties)
{
if ((property.PropertyType == typeof(int)) ||
(property.PropertyType == typeof(int?)))
{
commandText.Append(string.Format("`{0}` {1} {2}, ", property.Name.ToUpper(), "INT", "DEFAULT NULL").Trim());
}
else if ((property.PropertyType == typeof(long)) ||
(property.PropertyType == typeof(long?)))
{
commandText.Append(string.Format("`{0}` {1} {2}, ", property.Name.ToUpper(), "BIGINT", "DEFAULT NULL").Trim());
}
else if ((property.PropertyType == typeof(float)) ||
(property.PropertyType == typeof(float?)))
{
commandText.Append(string.Format("`{0}` {1} {2}, ", property.Name.ToUpper(), "FLOAT", "DEFAULT NULL").Trim());
}
else
{
commandText.Append(string.Format("`{0}` {1} {2}, ", property.Name.ToUpper(), "VARCHAR(" + VarCharFieldSize + ")", "DEFAULT NULL").Trim());
}
databaseTable.Columns.Add(property.Name.ToUpper());
}
commandText.Append(string.Format("{0}(`{1}`), ", "PRIMARY KEY", UbjectMetadata.PRIMARY_KEY));
List <PropertyInfo> indexedProperties = properties.GetIndexedProperties();
foreach (PropertyInfo property in indexedProperties)
{
commandText.Append(string.Format("{0} `IDX_{1}` (`{2}` ASC), ", "INDEX", property.Name.ToUpper(), property.Name.ToUpper()));
}
commandText.Append(string.Format("{0} `IDX_{1}` (`{2}` ASC) ", "INDEX", UbjectMetadata.UBJECT_TIMESTAMP, UbjectMetadata.UBJECT_TIMESTAMP));
commandText.Append(")");
commandText.Append("ENGINE = InnoDB DEFAULT CHARSET = utf8;");
dbQueryExecutor.ExecuteNonQuery(commandText);
return(databaseTable);
}
public void RemoveObject(object data, List <string> filterProperties)
{
Dictionary <UbjectMetadata, object> objects = new Dictionary <UbjectMetadata, object>();
IDbTableManager databaseTable = DbManager.GetDbTableManagerForObject(data.GetType());
if (databaseTable != null)
{
objects = databaseTable.GetObjects(data.GetType(), CreateCompleteFilterFromPropertyAndData(data, filterProperties));
if (objects.Count > 0)
{
var objectAnatomyDefinition = GetObjectAnatomyDefinition(objects.Values.First());
foreach (var collectionObject in objectAnatomyDefinition.CollectionReferenceTypeObjects)
{
IEnumerator enumerator = collectionObject.Value.GetEnumerator();
Type collectionItemType = enumerator.GetType().GetGenericArguments()[0];
IDbTableManager mappingDatabaseTable = DbManager.GetDbTableManagerForObject(typeof(UbjectMapping), false);
IDbTableManager childDatabaseTable = DbManager.GetDbTableManagerForObject(collectionItemType, false);
if ((mappingDatabaseTable != null) && (childDatabaseTable != null))
{
foreach (KeyValuePair <UbjectMetadata, object> ubject in objects)
{
List <Tuple <string, string, string> > mappingFilterCondition = new List <Tuple <string, string, string> >();
mappingFilterCondition.Add(new Tuple <string, string, string>(parentTableKey, databaseTable.TableName, "="));
mappingFilterCondition.Add(new Tuple <string, string, string>(parentObjectKey, ubject.Key.PrimaryKey.ToString(), "="));
mappingFilterCondition.Add(new Tuple <string, string, string>(childTableKey, childDatabaseTable.TableName, "="));
Dictionary <UbjectMetadata, UbjectMapping> objectMappings = GetObjectsIncMetadata <UbjectMapping>(mappingFilterCondition);
List <UbjectMetadata> childObjectsMetadata = new List <UbjectMetadata>();
foreach (var objectMapping in objectMappings.Values)
{
List <Tuple <string, string, string> > childObjectFilterCondition = new List <Tuple <string, string, string> >();
childObjectFilterCondition.Add(new Tuple <string, string, string>(UbjectMetadata.PRIMARY_KEY, objectMapping.ChildObjectKey, "="));
List <UbjectMetadata> ubjectMetadata = new List <UbjectMetadata>(GetObjectsIncMetadata(collectionItemType, childObjectFilterCondition).Keys);
childObjectsMetadata.AddRange(ubjectMetadata);
}
childObjectsMetadata.ForEach(x => childDatabaseTable.DeleteObject(x));
objectMappings.Keys.ToList().ForEach(x => mappingDatabaseTable.DeleteObject(x));
}
}
}
objects.Keys.ToList().ForEach(x => databaseTable.DeleteObject(x));
}
}
}
public Tuple <string, string> PersistObject(object data, List <string> filterProperties = null)
{
string primaryKey = string.Empty;
IDbTableManager databaseTable = DbManager.GetDbTableManagerForObject(data.GetType());
if (databaseTable != null)
{
bool objectUpdated = false;
var objectAnatomyDefinition = GetObjectAnatomyDefinition(data);
if (filterProperties != null)
{
var objects = databaseTable.GetObjects(data.GetType(), CreateCompleteFilterFromPropertyAndData(data, filterProperties));
if (objects.Count == 1)
{
databaseTable.UpdateObject(objectAnatomyDefinition.PropertiesAndValues, objects.Keys.First().PrimaryKey);
objectUpdated = true;
}
else if (objects.Count != 0)
{
throw new Exception(string.Format("Multiple existing objects found while attempting to persist object. Filter: {0} Number of objects returned: {1}",
string.Join(", ", filterProperties),
objects.Count));
}
}
if (!objectUpdated)
{
primaryKey = databaseTable.AddObject(objectAnatomyDefinition.PropertiesAndValues).ToString();
}
foreach (KeyValuePair <string, ICollection> collectionObject in objectAnatomyDefinition.CollectionReferenceTypeObjects)
{
foreach (var collectionItem in collectionObject.Value)
{
Tuple <string, string> childObjectKey = PersistObject(collectionItem);
PersistObject(new UbjectMapping(databaseTable.TableName, primaryKey, childObjectKey.Item1, childObjectKey.Item2));
}
}
}
return(new Tuple <string, string>(databaseTable.TableName, primaryKey));
}
public override IDbTableManager CreateDatabaseTable(string tableName, List <PropertyInfo> properties)
{
IDbTableManager databaseTable = Utilities.CreateDbTableManager(this);
databaseTable.TableName = (string.IsNullOrEmpty(tableName) ? Guid.NewGuid().ToString().ToUpper() : tableName);
StringBuilder commandText = new StringBuilder();
commandText.Append(string.Format("{0} '{1}'", "CREATE TABLE", databaseTable.TableName));
commandText.Append("(");
commandText.Append(string.Format("{0} {1} {2} {3},", UbjectMetadata.PRIMARY_KEY, "INTEGER", "PRIMARY KEY", "AUTOINCREMENT").Trim());
commandText.Append(string.Format("{0} {1} {2} {3},", UbjectMetadata.UBJECT_HASH, "CHAR(100)", "", "NOT NULL").Trim());
commandText.Append(string.Format("{0} {1} {2} {3},", UbjectMetadata.UBJECT_TIMESTAMP, "BIGINT", "", "NOT NULL").Trim());
commandText.Append(string.Join(", ", properties.Select(x => string.Format("{0} {1}", x.Name.ToUpper(), "TEXT"))).Trim());
properties.ForEach(x => databaseTable.Columns.Add(x.Name.ToUpper()));
commandText.Append(");");
dbQueryExecutor.ExecuteNonQuery(commandText);
commandText.Clear();
commandText.Append(
string.Format(
"CREATE INDEX IDX_{0}_{1} ON '{2}' ({3});",
UbjectMetadata.UBJECT_TIMESTAMP,
databaseTable.TableName.Replace("-", ""),
databaseTable.TableName,
UbjectMetadata.UBJECT_TIMESTAMP));
foreach (PropertyInfo property in properties.GetIndexedProperties())
{
commandText.Append(
string.Format(
"{0} IDX_{1}_{2} ON '{3}' ({4});",
"CREATE INDEX",
property.Name.ToUpper(),
databaseTable.TableName.Replace("-", ""),
databaseTable.TableName,
property.Name.ToUpper()));
}
dbQueryExecutor.ExecuteNonQuery(commandText);
return(databaseTable);
}
/// <summary>
/// Check then add primary keys to schema table
/// </summary>
private void SetPrimaryKeys(SyncTable schemaTable, IDbTableManager dbManagerTable)
{
// Get PrimaryKey
var schemaPrimaryKeys = dbManagerTable.GetPrimaryKeys();
if (schemaPrimaryKeys == null || schemaPrimaryKeys.Any() == false)
{
throw new MissingPrimaryKeyException(schemaTable.TableName);
}
// Set the primary Key
foreach (var rowColumn in schemaPrimaryKeys.OrderBy(r => r.Ordinal))
{
var columnKey = schemaTable.Columns.FirstOrDefault(sc => sc == rowColumn);
if (columnKey == null)
{
throw new MissingColumnException(rowColumn.ColumnName, schemaTable.TableName);
}
schemaTable.PrimaryKeys.Add(columnKey.ColumnName);
}
}
/// <summary>
/// Check then add primary keys to schema table
/// </summary>
private async Task SetPrimaryKeysAsync(SyncTable schemaTable, IDbTableManager dbManagerTable)
{
// Get PrimaryKey
var schemaPrimaryKeys = await dbManagerTable.GetPrimaryKeysAsync().ConfigureAwait(false);
if (schemaPrimaryKeys == null || schemaPrimaryKeys.Any() == false)
{
throw new MissingPrimaryKeyException(schemaTable.TableName);
}
// Set the primary Key
foreach (var rowColumn in schemaPrimaryKeys.OrderBy(r => r.Ordinal))
{
// Find the column in the schema columns
var columnKey = schemaTable.Columns.FirstOrDefault(sc => sc.EqualsByName(rowColumn));
if (columnKey == null)
{
throw new MissingPrimaryKeyColumnException(rowColumn.ColumnName, schemaTable.TableName);
}
schemaTable.PrimaryKeys.Add(columnKey.ColumnName);
}
}
public virtual List <IDbTableManager> GetAllDbTableManagers()
{
List <IDbTableManager> dbTableManagers = new List <IDbTableManager>();
using (var tableConnection = Utilities.CreateDbConnection(this))
{
IDbCommand showTableCommand = tableConnection.CreateCommand();
showTableCommand.CommandText = ShowTablesCommand;
tableConnection.Open();
IDataReader tableReader = showTableCommand.ExecuteReader();
using (var columnsConnection = Utilities.CreateDbConnection(this))
{
columnsConnection.Open();
while (tableReader.Read())
{
for (int tableFieldIndex = 0; tableFieldIndex < 1 /*tableReader.FieldCount*/; tableFieldIndex++)
{
IDbTableManager dbTableManager = Utilities.CreateDbTableManager(this);
dbTableManager.TableName = tableReader.GetValue(tableFieldIndex).ToString();
IDbCommand showColumnsCommand = columnsConnection.CreateCommand();
showColumnsCommand.CommandText = ShowColumnsCommand(dbTableManager.TableName);
IDataReader schemaReader = showColumnsCommand.ExecuteReader();
while (schemaReader.Read())
{
for (int schemaFieldIndex = 0; schemaFieldIndex < schemaReader.FieldCount; schemaFieldIndex++)
{
string fieldName = schemaReader.GetName(schemaFieldIndex).ToString();
if (fieldName == ColumnCommandFieldName)
{
string columnName = schemaReader.GetValue(schemaFieldIndex).ToString();
if (!UbjectMetadata.IsMetadataField(columnName))
{
dbTableManager.Columns.Add(columnName);
}
break;
}
}
}
schemaReader.Close();
dbTableManagers.Add(dbTableManager);
}
}
columnsConnection.Close();
}
tableReader.Close();
tableConnection.Close();
}
return(dbTableManagers);
}
/// <summary>
/// Generate the DmTable configuration from a given columns list
/// Validate that all columns are currently supported by the provider
/// </summary>
private void FillSyncTableWithColumns(SetupTable setupTable, SyncTable schemaTable, IEnumerable <SyncColumn> columns, IDbTableManager dbManagerTable)
{
schemaTable.OriginalProvider = this.ProviderTypeName;
schemaTable.SyncDirection = setupTable.SyncDirection;
var ordinal = 0;
// Eventually, do not raise exception here, just we don't have any columns
if (columns == null || columns.Any() == false)
{
return;
}
// Delete all existing columns
if (schemaTable.PrimaryKeys.Count > 0)
{
schemaTable.PrimaryKeys.Clear();
}
if (schemaTable.Columns.Count > 0)
{
schemaTable.Columns.Clear();
}
IEnumerable <SyncColumn> lstColumns;
// Validate columns list from setup table if any
if (setupTable.Columns != null && setupTable.Columns.Count > 1)
{
lstColumns = new List <SyncColumn>();
foreach (var setupColumn in setupTable.Columns)
{
// Check if the columns list contains the column name we specified in the setup
var column = columns.FirstOrDefault(c => c.ColumnName.Equals(setupColumn, SyncGlobalization.DataSourceStringComparison));
if (column == null)
{
throw new MissingColumnException(setupColumn, schemaTable.TableName);
}
else
{
((List <SyncColumn>)lstColumns).Add(column);
}
}
}
else
{
lstColumns = columns;
}
foreach (var column in lstColumns.OrderBy(c => c.Ordinal))
{
// First of all validate if the column is currently supported
if (!this.Metadata.IsValid(column))
{
throw new UnsupportedColumnTypeException(column.ColumnName, column.OriginalTypeName, this.ProviderTypeName);
}
var columnNameLower = column.ColumnName.ToLowerInvariant();
if (columnNameLower == "sync_scope_name" ||
columnNameLower == "scope_timestamp" ||
columnNameLower == "scope_is_local" ||
columnNameLower == "scope_last_sync" ||
columnNameLower == "create_scope_id" ||
columnNameLower == "update_scope_id" ||
columnNameLower == "create_timestamp" ||
columnNameLower == "update_timestamp" ||
columnNameLower == "timestamp" ||
columnNameLower == "sync_row_is_tombstone" ||
columnNameLower == "last_change_datetime" ||
columnNameLower == "sync_scope_name" ||
columnNameLower == "sync_scope_name"
)
{
throw new UnsupportedColumnTypeException(column.ColumnName, column.OriginalTypeName, this.ProviderTypeName);
}
// Validate max length
column.MaxLength = this.Metadata.ValidateMaxLength(column.OriginalTypeName, column.IsUnsigned, column.IsUnicode, column.MaxLength);
// Gets the datastore owner dbType (could be SqlDbtype, MySqlDbType, SqliteDbType, NpgsqlDbType & so on ...)
var datastoreDbType = this.Metadata.ValidateOwnerDbType(column.OriginalTypeName, column.IsUnsigned, column.IsUnicode, column.MaxLength);
// once we have the datastore type, we can have the managed type
var columnType = this.Metadata.ValidateType(datastoreDbType);
// and the DbType
column.DbType = (int)this.Metadata.ValidateDbType(column.OriginalTypeName, column.IsUnsigned, column.IsUnicode, column.MaxLength);
// Gets the owner dbtype (SqlDbType, OracleDbType, MySqlDbType, NpsqlDbType & so on ...)
// Sqlite does not have it's own type, so it's DbType too
column.OriginalDbType = datastoreDbType.ToString();
// Validate if column should be readonly
column.IsReadOnly = this.Metadata.ValidateIsReadonly(column);
// set position ordinal
column.Ordinal = ordinal;
ordinal++;
// Validate the precision and scale properties
if (this.Metadata.IsNumericType(column.OriginalTypeName))
{
if (this.Metadata.SupportScale(column.OriginalTypeName))
{
var(p, s) = this.Metadata.ValidatePrecisionAndScale(column);
column.Precision = p;
column.PrecisionSpecified = true;
column.Scale = s;
column.ScaleSpecified = true;
}
else
{
column.Precision = this.Metadata.ValidatePrecision(column);
column.PrecisionSpecified = true;
column.ScaleSpecified = false;
}
}
// if setup table has no columns, we add all columns from db
// otherwise check if columns exist in the data source
if (setupTable.Columns == null || setupTable.Columns.Count <= 0 || setupTable.Columns.Contains(column.ColumnName))
{
schemaTable.Columns.Add(column);
}
// If column does not allow null value and is not compute
// We will not be able to insert a row, so raise an error
else if (!column.AllowDBNull && !column.IsCompute && !column.IsReadOnly && string.IsNullOrEmpty(column.DefaultValue))
{
throw new Exception($"Column {column.ColumnName} is not part of your setup. But it seems this columns is mandatory in your data source.");
}
}
}
