public async Task Test1(TimeSpan offset)
{
//if (offset != DateTimeOffset.Now.Offset)
{
DatabaseFunction.SetDateOffset(offset);
}
using (var connection = this.database.Connect())
{
this.table1.Drop(connection);
await this.database.InitializeAsync(connection);
}
using (var connection = this.database.Connect())
{
Debug.WriteLine("a");
var date = DateTimeOffset.Now;
await this.TestUnixDate(date, connection, offset);
Debug.WriteLine("b");
var d2 = new DateTimeOffset(date.Year, date.Month, date.Day,
date.Hour, date.Minute, date.Second, TimeSpan.FromHours(-10.5));
await this.TestUnixDate(d2, connection, offset);
Debug.WriteLine("c");
var d3 = new DateTimeOffset(date.Year, date.Month, date.Day,
date.Hour, date.Minute, date.Second, TimeSpan.FromHours(0));
await this.TestUnixDate(d3, connection, offset);
}
}
public static List <DatabaseFunction> Functions(DataTable dt)
{
List <DatabaseFunction> list = new List <DatabaseFunction>();
var functionKeyMap = new FunctionKeyMap(dt);
foreach (DataRow row in dt.Rows)
{
DatabaseFunction fun = new DatabaseFunction();
fun.Name = row[functionKeyMap.Key].ToString();
if (!string.IsNullOrEmpty(functionKeyMap.OwnerKey))
{
fun.SchemaOwner = row[functionKeyMap.OwnerKey].ToString();
}
if (functionKeyMap.SqlKey != null)
{
fun.Sql = row[functionKeyMap.SqlKey].ToString();
}
if (functionKeyMap.LangKey != null)
{
fun.Language = row[functionKeyMap.LangKey].ToString();
}
if (functionKeyMap.ReturnKey != null)
{
fun.ReturnType = row[functionKeyMap.ReturnKey].ToString();
}
list.Add(fun);
}
return(list);
}
public DatabaseFunctionDTO ToDatabaseFunctionDTO(DatabaseFunction cItem, bool withColumns)
{
BizColumn bizColumn = new BizColumn();
var result = new DatabaseFunctionDTO();
result.ID = cItem.ID;
result.Name = cItem.FunctionName;
//result.Catalog = cItem.Catalog;
result.SchemaID = cItem.DBSchemaID;
result.DatabaseID = cItem.DBSchema.DatabaseInformationID;
result.RelatedSchema = cItem.DBSchema.Name;
//result.ReturnType = cItem.ReturnType;
//if (cItem.ValueCustomType != null)
// result.ValueCustomType = (ValueCustomType)cItem.ValueCustomType;
result.Type = (Enum_DatabaseFunctionType)cItem.Type;
result.Title = cItem.Title;
result.Enable = cItem.Enable;
//if (result.Title == null)
//{
// result.Title = cItem.FunctionName;
//}
if (withColumns)
{
result.DatabaseFunctionParameter = ToDatabaseFunctionParameterDTO(cItem);
}
// if (result.Type == Enum_DatabaseFunctionType.Function)
result.ReturnDotNetType = GetReturnType(cItem);
return(result);
}
private Type GetReturnType(DatabaseFunction cItem)
{
//اینجا میتوان برای استورد پروسیجر ها با یک خصوصیت تعیین کرد که کدوم پارامتر خروجی اصلی است
BizColumn bizColumn = new BizColumn();
DatabaseFunctionParameter parameter = null;
var output = (short)Enum_DatabaseFunctionParameterType.Output;
parameter = cItem.DatabaseFunctionParameter.FirstOrDefault(x => x.InputOutput == output);
if (parameter != null)
{
return(bizColumn.GetColumnDotNetType(parameter.DataType, false));
}
else
{
output = (short)Enum_DatabaseFunctionParameterType.InputOutput;
parameter = cItem.DatabaseFunctionParameter.FirstOrDefault(x => x.InputOutput == output);
if (parameter != null)
{
return(bizColumn.GetColumnDotNetType(parameter.DataType, false));
}
else
{
output = (short)Enum_DatabaseFunctionParameterType.ReturnValue;
parameter = cItem.DatabaseFunctionParameter.FirstOrDefault(x => x.InputOutput == output);
if (parameter != null)
{
return(bizColumn.GetColumnDotNetType(parameter.DataType, false));
}
}
}
return(null);
}
/// <summary>
/// Factory method that creates a new IFieldPredicate predicate: [FieldName] [Operator] [Value]
/// Example: WHERE FirstName = 'Foo'
/// </summary>
/// <typeparam name="T">The type of the entity.</typeparam>
/// <param name="expression">An expression that returns the left operand [FieldName].</param>
/// <param name="op">The comparison operator.</param>
/// <param name="value">The value for the predicate.</param>
/// <param name="not">Effectively inverts the comparison operator. Example: WHERE FirstName <> 'Foo'.</param>
/// <param name="useColumPrefix">Indicates to use or not column prefix on generated SQL</param>
/// <param name="databaseFunction">Apply database function to field</param>
/// <param name="databaseFunctionParameters">Parameters to the database function</param>
/// <returns>An instance of IFieldPredicate.</returns>
public static IFieldPredicate Field <T>(Expression <Func <T, object> > expression, Operator op, object value, bool not = false, bool useColumPrefix = true,
DatabaseFunction databaseFunction = DatabaseFunction.None, string databaseFunctionParameters = "") where T : class
{
var propertyInfo = ReflectionHelper.GetProperty(expression) as PropertyInfo;
return(propertyInfo != null?Field <T>(propertyInfo.Name, op, value, not, useColumPrefix, databaseFunction, databaseFunctionParameters) : null);
}
private DatabaseFunction ConvertToDatabaseFunction(Function customFunction)
{
string functionName = this.GetFunctionName(customFunction);
string schema = this.GetSchemaName(customFunction);
var databaseFunction = new DatabaseFunction(schema, functionName);
databaseFunction.ReturnType = this.ConvertToDatabaseType(customFunction.ReturnType);
foreach (var parameter in customFunction.Parameters)
{
databaseFunction.Parameters.Add(this.ConvertToDatabaseFunctionParameter(parameter));
}
var bodyGenerationAnnotation = this.GetStoreFunctionBodyGenerationInfoToStoreItem(customFunction.Annotations.OfType <StoreFunctionBodyGenerationInfoAnnotation>().SingleOrDefault());
if (bodyGenerationAnnotation != null)
{
databaseFunction.Annotations.Add(bodyGenerationAnnotation);
}
var bodyAnnotation = customFunction.Annotations.OfType <StoreFunctionBodyAnnotation>().SingleOrDefault();
if (bodyAnnotation != null)
{
databaseFunction.Body = bodyAnnotation.Body;
}
return(databaseFunction);
}
public override string GetDatabaseFunctionString(DatabaseFunction databaseFunction, string columnName, string functionParameters = "")
{
return(databaseFunction switch
{
DatabaseFunction.NullValue => $"IsNull({columnName}, {functionParameters})",
DatabaseFunction.Truncate => $"Truncate({columnName})",
_ => columnName,
});
/// <summary>
/// Checks if the database exist, if not then it will generate one
/// </summary>
private void CheckIfDBExist()
{
DatabaseFunction database = new DatabaseFunction(this);
if (!database.CheckIfDatabaseExist())
{
database.CreateDatabase();
}
}
protected override string GetColumnName(Type entityType, ISqlGenerator sqlGenerator, string propertyName, bool isDml = false, bool includePrefix = true)
{
var result = base.GetColumnName(entityType, sqlGenerator, propertyName, isDml, includePrefix);
if (!DatabaseFunction.Equals(DatabaseFunction.None))
{
result = sqlGenerator.Configuration.Dialect.GetDatabaseFunctionString(DatabaseFunction, result, DatabaseFunctionParameters);
}
return(result);
}
public void BuildFunction(DatabaseFunction databaseFunction)
{
try
{
var txt = _migrationGenerator.AddFunction(databaseFunction);
Clipboard.SetText(txt, TextDataFormat.UnicodeText);
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
}
private void CreateResult(ResultType resultType, DatabaseFunction function, string script)
{
var result = new CompareResult
{
SchemaObjectType = SchemaObjectType.Function,
ResultType = resultType,
Name = function.Name,
SchemaOwner = function.SchemaOwner,
Script = script
};
_results.Add(result);
}
private void UpdateEntityInModel(MyProjectEntities projectContext, int databaseID, DatabaseFunctionDTO function, List <DBSchema> listAddedSchema)
{
DBSchema dbSchema = null;
var schema = function.RelatedSchema;
dbSchema = listAddedSchema.FirstOrDefault(x => x.DatabaseInformationID == databaseID && x.Name == function.RelatedSchema);
if (dbSchema == null)
{
dbSchema = new DBSchema()
{
DatabaseInformationID = databaseID, Name = schema
};
dbSchema.SecurityObject = new SecurityObject();
dbSchema.SecurityObject.Type = (int)DatabaseObjectCategory.Schema;
projectContext.DBSchema.Add(dbSchema);
listAddedSchema.Add(dbSchema);
}
var dbFunction = projectContext.DatabaseFunction.Include("DatabaseFunctionParameter").FirstOrDefault(x => x.FunctionName == function.Name && x.DBSchema.DatabaseInformationID == databaseID);
if (dbFunction == null)
{
dbFunction = new DatabaseFunction();
projectContext.DatabaseFunction.Add(dbFunction);
dbFunction.FunctionName = function.Name;
dbFunction.Enable = true;
}
dbFunction.DBSchema = dbSchema;
dbFunction.Type = (short)function.Type;
foreach (var column in function.DatabaseFunctionParameter)
{
var dbColumn = dbFunction.DatabaseFunctionParameter.Where(x => x.ParamName == column.ParameterName).FirstOrDefault();
if (dbColumn == null)
{
dbColumn = new DatabaseFunctionParameter();
dbColumn.ParamName = column.ParameterName;
dbColumn.Enable = true;
dbFunction.DatabaseFunctionParameter.Add(dbColumn);
}
dbColumn.InputOutput = (short)column.InputOutput;
dbColumn.DataType = column.DataType;
dbColumn.ParamName = column.ParameterName;
}
var columnNames = function.DatabaseFunctionParameter.Select(x => x.ParameterName).ToList();
foreach (var dbColumn in dbFunction.DatabaseFunctionParameter.Where(x => !columnNames.Contains(x.ParamName)))
{
dbColumn.Enable = false;
}
}
public string BuildFunction(DatabaseFunction databaseFunction)
{
StringBuilder sb = new StringBuilder();
try
{
sb.AppendLine(m_migrationGenerator.AddFunction(databaseFunction));
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
return(sb.ToString());
}
private static DatabaseStoredProcedure FindStoredProcedureOrFunction(DatabaseSchema databaseSchema, string name, string owner, string package)
{
var sproc = databaseSchema.StoredProcedures.Find(delegate(DatabaseStoredProcedure x) { return(x.Name == name && x.SchemaOwner == owner && x.Package == package); });
if (sproc == null) //is it actually a function?
{
DatabaseFunction fun = databaseSchema.Functions.Find(delegate(DatabaseFunction f) { return(f.Name == name && f.SchemaOwner == owner && f.Package == package); });
if (fun != null)
{
return(fun);
}
}
return(sproc);
}
/// <summary>
/// 各プロパティによるソート方法の定義
/// </summary>
/// <returns></returns>
private static Dictionary <int, SortDefinition> MakeSortDictionary()
{
var dictionary = new Dictionary <int, SortDefinition>();
dictionary[(int)FileProperty.Id] = new SortDefinition(nameof(Record.Id));
dictionary[(int)FileProperty.DirectoryPath] = new SortDefinition
(DatabaseFunction.ToLower(nameof(Record.Directory)));
dictionary[(int)FileProperty.FullPath] = new SortDefinition
(DatabaseFunction.ToLower
(DatabaseFunction.Combine(nameof(Record.Directory), nameof(Record.FileName))));
dictionary[(int)FileProperty.FileName] = new SortDefinition
(DatabaseFunction.ToLower(nameof(Record.FileName)));
dictionary[(int)FileProperty.FileNameSequenceNumLeft] = new SortDefinition(
new[]
{
DatabaseFunction.ToLower(nameof(Record.PreNameShort)),
nameof(Record.NameNumberLeft),
nameof(Record.NameLength),
DatabaseFunction.ToLower(nameof(Record.PostNameLong)),
DatabaseFunction.ToLower(nameof(Record.Extension))
});
dictionary[(int)FileProperty.FileNameSequenceNumRight] = new SortDefinition(
new[]
{
DatabaseFunction.ToLower(nameof(Record.PreNameLong)),
nameof(Record.NameNumberRight),
nameof(Record.NameLength),
DatabaseFunction.ToLower(nameof(Record.PostNameShort)),
DatabaseFunction.ToLower(nameof(Record.Extension))
});
dictionary[(int)FileProperty.DateTimeCreated] = new SortDefinition(nameof(Record.DateCreated));
dictionary[(int)FileProperty.DateTimeModified] = new SortDefinition(nameof(Record.DateModified));
dictionary[(int)FileProperty.DateTimeRegistered] = new SortDefinition(nameof(Record.DateRegistered));
dictionary[(int)FileProperty.Width] = new SortDefinition(nameof(Record.Width));
dictionary[(int)FileProperty.Height] = new SortDefinition(nameof(Record.Height));
dictionary[(int)FileProperty.Size] = new SortDefinition(nameof(Record.Size));
dictionary[(int)FileProperty.Rating] = new SortDefinition(nameof(Record.Rating));
dictionary[(int)FileProperty.AspectRatio] = new SortDefinition
(DatabaseFunction.Divide(nameof(Record.Width), nameof(Record.Height)));
return(dictionary);
}
public override string AddFunction(DatabaseFunction databaseFunction)
{
if (string.IsNullOrEmpty(databaseFunction.Sql))
{
//the function.Sql contains the BEGIN to END statements, not the CREATE FUNCTION and arguments.
//for now, just comment
return("-- add function " + databaseFunction.Name);
}
var name = databaseFunction.Name;
var procWriter = new ProcedureWriter(name, true);
procWriter.AddReturns(databaseFunction.ReturnType);
WriteProcedure(databaseFunction, procWriter);
return(procWriter.End());
}
/// <summary>
/// グループの名前を作成
/// </summary>
/// <returns></returns>
private string GenerateGroupName(IDbConnection connection)
{
var baseName = this.Library.Searcher.GroupName;
var existingName = this.Table
.AsQueryable(connection)
.Where(DatabaseExpression.Is(DatabaseFunction.ToLower(nameof(Record.FileName)), DatabaseReference.StartsWith(baseName)))
.Select <string>(nameof(Record.FileName))
.OrderBy($"{DatabaseFunction.Length(nameof(Record.FileName))} DESC, {nameof(Record.FileName)} DESC")
.Take(1)
.FirstOrDefault();
var count = 0;
if (existingName != null)
{
var succeeded = false;
if (existingName.StartsWith(baseName))
{
var str = existingName.Substring(baseName.Length);
int num;
if (int.TryParse(str, out num))
{
count = num;
succeeded = true;
}
}
if (!succeeded)
{
int num;
var nameNumberRightString = regexRight.Match(existingName).ToString();
if (int.TryParse(nameNumberRightString, out num))
{
count = num;
succeeded = true;
}
}
}
var name = baseName + (count + 1).ToString();
return(name);
}
public virtual string AddFunction(DatabaseFunction databaseFunction)
{
var sql = databaseFunction.Sql;
if (string.IsNullOrEmpty(sql))
{
//without the sql, we can't do anything
return("-- add function " + databaseFunction.Name);
}
if (sql.TrimStart().StartsWith("FUNCTION ", StringComparison.OrdinalIgnoreCase))
{
return("CREATE " + sql + _sqlFormatProvider.RunStatements());
}
//helpfully, SqlServer includes the create statement
//MySQL doesn't, so this will need to be overridden
return(sql + _sqlFormatProvider.RunStatements());
}
private static DatabaseStoredProcedure CreateProcedureOrFunction(DatabaseSchema databaseSchema, bool isFunction)
{
DatabaseStoredProcedure sproc;
if (isFunction)
{
//functions are just a type of stored procedure
DatabaseFunction fun = new DatabaseFunction();
databaseSchema.Functions.Add(fun);
sproc = fun;
}
else
{
sproc = new DatabaseStoredProcedure();
databaseSchema.StoredProcedures.Add(sproc);
}
return(sproc);
}
/// <summary>
/// Factory method that creates a new IFieldPredicate predicate: [FieldName] [Operator] [Value]
/// Example: WHERE FirstName = 'Foo'
/// </summary>
/// <typeparam name="T">The type of the entity.</typeparam>
/// <param name="propertyName">Property name that returns the left operand [FieldName].</param>
/// <param name="op">The comparison operator.</param>
/// <param name="value">The value for the predicate.</param>
/// <param name="not">Effectively inverts the comparison operator. Example: WHERE FirstName <> 'Foo'.</param>
/// <param name="useColumPrefix">Indicates to use or not column prefix on generated SQL</param>
/// <param name="databaseFunction">Apply database function to field</param>
/// <param name="databaseFunctionParameters">Parameters to the database function</param>
/// <returns>An instance of IFieldPredicate.</returns>
public static IFieldPredicate Field <T>(string propertyName, Operator op, object value, bool not = false, bool useColumPrefix = true,
DatabaseFunction databaseFunction = DatabaseFunction.None, string databaseFunctionParameters = "") where T : class
{
var properties = ReflectionHelper.GetNestedProperties <T>(propertyName, '.', out string propertyInfoName);
var propertyInfo = typeof(T).GetProperties().SingleOrDefault(x => x.Name.Equals(propertyName, StringComparison.InvariantCultureIgnoreCase));
return(new FieldPredicate <T>
{
PropertyName = propertyInfo != null ? propertyInfo.Name : propertyInfoName,
Operator = op,
Value = value,
Not = not,
UseTableAlias = useColumPrefix,
DatabaseFunction = databaseFunction,
DatabaseFunctionParameters = databaseFunctionParameters,
Properties = properties
});
}
private static DatabaseStoredProcedure CreateProcedureOrFunction(DatabaseSchema databaseSchema, List <DatabaseArgument> args)
{
//if it's ordinal 0 and no name, it's a function not a sproc
DatabaseStoredProcedure sproc;
if (args.Find(delegate(DatabaseArgument arg) { return(arg.Ordinal == 0 && string.IsNullOrEmpty(arg.Name)); }) != null)
{
//functions are just a type of stored procedure
DatabaseFunction fun = new DatabaseFunction();
databaseSchema.Functions.Add(fun);
sproc = fun;
}
else
{
sproc = new DatabaseStoredProcedure();
databaseSchema.StoredProcedures.Add(sproc);
}
return(sproc);
}
/// <inheritdoc />
public TResult Lock <TDatabase, TResult>(DatabaseFunction <TDatabase, TResult> func)
where TDatabase : IDatabase
{
if (func == null)
{
throw new ArgumentNullException(nameof(func), "the function can't be null");
}
IDatabasePoolContainer container;
lock (_databaseIOPool)
{
if (!_databaseIOPool.TryGetValue(typeof(TDatabase), out container))
{
throw new KeyNotFoundException($"no database of type: '{typeof(TDatabase)}' registered.");
}
}
return(((IDatabasePoolContainer <TDatabase>)container).Lock(func));
}
/// <inheritdoc />
public TResult Lock <TResult>(DatabaseFunction <TDatabase, TResult> func)
{
TDatabase database;
_semaphore.Wait();
lock (_queue)
{
database = _queue.Dequeue();
}
TResult result = func.Invoke(database);
lock (_queue)
{
_queue.Enqueue(database);
}
_semaphore.Release();
return(result);
}
protected Mock <PropertyPredicate <T, T2> > Setup <T, T2>(string propertyName, Operator op, string propertyName2, bool not,
DatabaseFunction leftDatabaseFunction = DatabaseFunction.None, string leftFunctionParameters = null,
DatabaseFunction rightDatabaseFunction = DatabaseFunction.None, string rightFunctionParameters = null)
where T : class
where T2 : class
{
Mock <PropertyPredicate <T, T2> > predicate = new Mock <PropertyPredicate <T, T2> >();
predicate.Object.PropertyName = propertyName;
predicate.Object.PropertyName2 = propertyName2;
predicate.Object.Operator = op;
predicate.Object.Not = not;
predicate.CallBase = true;
predicate.Object.LeftDatabaseFunction = leftDatabaseFunction;
predicate.Object.LeftDatabaseFunctionParameters = leftFunctionParameters;
predicate.Object.RigthDatabaseFunction = rightDatabaseFunction;
predicate.Object.RigthDatabaseFunctionParameters = rightFunctionParameters;
predicate.Protected().Setup <string>("GetColumnName", typeof(T), Generator.Object, propertyName, ItExpr.IsAny <bool>(), ItExpr.IsAny <bool>()).Returns("fooCol").Verifiable();
predicate.Protected().Setup <string>("GetColumnName", typeof(T2), Generator.Object, propertyName2, ItExpr.IsAny <bool>(), ItExpr.IsAny <bool>()).Returns("fooCol2").Verifiable();
return(predicate);
}
private List <DatabaseFunctionColumnDTO> ToDatabaseFunctionParameterDTO(DatabaseFunction cItem)
{
BizColumn bizColumn = new BizColumn();
List <DatabaseFunctionColumnDTO> result = new List <DatabaseFunctionColumnDTO>();
foreach (var column in cItem.DatabaseFunctionParameter)
{
var item = new DatabaseFunctionColumnDTO()
{
ID = column.ID,
DataType = column.DataType,
Enable = column.Enable,
ParameterName = column.ParamName,
DotNetType = bizColumn.GetColumnDotNetType(column.DataType, false),
Order = column.Order ?? 0,
InputOutput = (Enum_DatabaseFunctionParameterType)column.InputOutput
};
result.Add(item);
}
return(result);
}
/// <inheritdoc />
public TResult Lock <TResult>(DatabaseFunction <TDatabase, TResult> func)
{
TDatabase database;
lock (this)
{
SpinWait.SpinUntil(() => { return(_queue.Count > 0); });
lock (_queue)
{
database = _queue.Dequeue();
}
}
TResult result = func.Invoke(database);
lock (_queue)
{
_queue.Enqueue(database);
}
return(result);
}
/// <inheritdoc />
public TResult Lock <TResult>(DatabaseFunction <TDatabase, TResult> func)
{
TDatabase database;
lock (_queue)
{
while (_queue.Count == 0)
{
Monitor.Wait(_queue);
}
database = _queue.Dequeue();
}
TResult result = func.Invoke(database);
lock (_queue)
{
_queue.Enqueue(database);
Monitor.Pulse(_queue);
}
return(result);
}
public override string DropFunction(DatabaseFunction databaseFunction)
{
return(null); //doesn't support it
}
public string AddFunction(DatabaseFunction function)
{
return(_migration.AddFunction(function));
}
public string DropFunction(DatabaseFunction function)
{
return(_migration.DropFunction(function));
}
