/// <summary>
/// Update rows
/// </summary>
/// <param name="entity"></param>
/// <param name="modifiedProperties">The properties are modified</param>
/// <param name="where"></param>
public void PartialUpdateOnSubmit(TEntity entity, Expression <Func <TEntity, object> > modifiedProperties, Expression <Func <TEntity, bool> > where)
{
if (entity == null)
{
throw new ArgumentNullException($"argument {nameof(entity)} cannot be null");
}
List <string> names = new PropertyTranslator().Translate(modifiedProperties);
string _where = new QueryTranslator().Translate(where);
var gen = new SqlColumnValuePairCollection();
foreach (var propertyInfo in entity.GetType().GetProperties())
{
if (names.IndexOf(propertyInfo.Name) == -1)
{
continue;
}
object value = propertyInfo.GetValue(entity);
gen.Add(propertyInfo.Name, value);
}
SqlTemplate template = new SqlTemplate(formalName);
string update = template.Update(gen.Join(","), _where);
Context.CodeBlock.AppendLine <TEntity>(update);
var evt = new RowEvent
{
TypeName = typeof(TEntity).Name,
Operation = RowOperation.PartialUpdate,
Row = gen.ToDictionary(),
};
Context.RowEvents.Add(evt);
gen.Clear();
return;
}
public virtual PagedResult <UnitConversion> GetUnitConversions(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var builder = new SqlBuilder();
var search = builder.AddTemplate(SqlTemplate.UnitConversionSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
builder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
builder.OrderBy(s.ToExpression());
}
}
else
{
builder.OrderBy("FromUnitOfMeasure.Name");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.UnitConversionSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var unitConversions = connection.Query <UnitConversion, UnitOfMeasure, UnitOfMeasure, UnitConversion>(search.RawSql, (unitConversion, fromUnitOfMeasure, toUnitOfMeasure) => { unitConversion.FromUnitOfMeasure = fromUnitOfMeasure; unitConversion.ToUnitOfMeasure = toUnitOfMeasure; return(unitConversion); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <UnitConversion>(unitConversions, totalCount));
}
}
public virtual PagedResult <Property> GetProperties(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.PropertySearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Property.Name");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.PropertySearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var properties = connection.Query <Property, Site, Location, Property>(search.RawSql, (property, site, location) => { property.Site = site; property.Location = location; return(property); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <Property>(properties, totalCount));
}
}
public virtual PagedResult <AuditEntityConfiguration> GetAuditEntityConfigurations(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.AuditEntityConfigurationSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Name");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.AuditEntityConfigurationSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var auditEntityConfigurations = connection.Query <AuditEntityConfiguration>(search.RawSql, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <AuditEntityConfiguration>(auditEntityConfigurations, totalCount));
}
}
public virtual PagedResult <Log> GetLogs(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.LogSearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("CreatedOnUtc DESC");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.LogSearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var logs = connection.Query <Log, User, Log>(search.RawSql, (log, user) => { log.User = user; return(log); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <Log>(logs, totalCount));
}
}
public virtual PagedResult <Company> GetCompanies(string expression,
dynamic parameters,
int pageIndex = 0,
int pageSize = 2147483647,
IEnumerable <Sort> sort = null)
{
var searchBuilder = new SqlBuilder();
var search = searchBuilder.AddTemplate(SqlTemplate.CompanySearch(), new { skip = pageIndex * pageSize, take = pageSize });
if (!string.IsNullOrEmpty(expression))
{
searchBuilder.Where(expression, parameters);
}
if (sort != null)
{
foreach (var s in sort)
{
searchBuilder.OrderBy(s.ToExpression());
}
}
else
{
searchBuilder.OrderBy("Company.Name");
}
var countBuilder = new SqlBuilder();
var count = countBuilder.AddTemplate(SqlTemplate.CompanySearchCount());
if (!string.IsNullOrEmpty(expression))
{
countBuilder.Where(expression, parameters);
}
using (var connection = _dapperContext.GetOpenConnection())
{
var companys = connection.Query <Company, ValueItem, Company>(search.RawSql, (company, valueItem) => { company.CompanyType = valueItem; return(company); }, search.Parameters);
var totalCount = connection.Query <int>(count.RawSql, search.Parameters).Single();
return(new PagedResult <Company>(companys, totalCount));
}
}
public int ExecuteNonQuery(SqlTemplate sqlTemplate)
{
lock (_dbLocker)
{
SqliteSqlTemplate wrapper = GetSqliteSqlTemplate(sqlTemplate);
string sqlExpression = wrapper.SqlExpression;
using (var conn = new SQLiteConnection(_connectionString))
{
conn.SetPassword(_password);
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(sqlExpression, conn))
{
if (wrapper.Params != null && wrapper.Params.Length > 0)
{
cmd.Parameters.AddRange(wrapper.Params.ToArray());
}
int effectRows = cmd.ExecuteNonQuery();
return(effectRows);
}
}
}
}
public void SqlTemplates()
{
var sqlContext = new SqlContext(
new SqlServerSyntaxProvider(Options.Create(new GlobalSettings())),
DatabaseType.SqlServer2012,
Mock.Of <IPocoDataFactory>());
var sqlTemplates = new SqlTemplates(sqlContext);
// this can be used for queries that we know we'll use a *lot* and
// want to cache as a (static) template for ever, and ever - note
// that using a MemoryCache would allow us to set a size limit, or
// something equivalent, to reduce risk of memory explosion
var sql = sqlTemplates.Get("xxx", s => s
.SelectAll()
.From("zbThing1")
.Where("id=@id", new { id = SqlTemplate.Arg("id") })).Sql(new { id = 1 });
var sql2 = sqlTemplates.Get("xxx", x => throw new InvalidOperationException("Should be cached.")).Sql(1);
var sql3 = sqlTemplates.Get("xxx", x => throw new InvalidOperationException("Should be cached."))
.Sql(new { id = 1 });
}
public void ParseEmbedded()
{
var st = new SqlTemplate();
var type = GetType();
var rs = st.ParseEmbedded(type.Assembly, type.Namespace, "AreaX.Sql");
Assert.True(rs);
Assert.Equal("AreaX", st.Name);
Assert.Equal("select * from area where enable=1", st.Sql);
Assert.Equal(2, st.Sqls.Count);
var sql = st.Sqls["MySql"];
Assert.Equal("select * from area where `enable`=1", sql);
sql = st.Sqls["Sqlite"];
Assert.Equal("select * from area where 'enable'=1", sql);
sql = st.GetSql(DatabaseType.SqlServer);
Assert.Equal("select * from area where enable=1", st.Sql);
}
public static void Run()
{
ConnectionString conn = "my-database";
SqlTemplate select = "SELECT * FROM EMPLOYEES"
, sqlById = $"{select} WHERE ID = @Id"
, sqlByName = $"{select} WHERE LASTNAME = @LastName";
// queryById : object → IEnumerable<Employee>
var queryById = conn.Retrieve <Employee>(sqlById);
// queryByLastName : object → IEnumerable<Employee>
var queryByLastName = conn.Retrieve <Employee>(sqlByName);
// LookupEmployee : Guid → Option<Employee>
Option <Employee> LookupEmployee(Guid id)
=> queryById(new { Id = id }).FirstOrDefault();
// FindEmployeesByLastName : string → IEnumerable<Employee>
IEnumerable <Employee> FindEmployeesByLastName(string lastName)
=> queryByLastName(new { LastName = lastName });
}
public void TestBulkInserts()
{
string dbFilepath = "my.db";
DeleteDatabase(dbFilepath);
try
{
var manager = new SqliteDatabaseManager(dbFilepath, _password);
manager.Initialize();
CreateTableTemplate createTableTemplate = new CreateTableTemplate();
createTableTemplate.TableName = "student";
createTableTemplate.Version = 1;
createTableTemplate.CreateSql = string.Format("CREATE TABLE IF NOT EXISTS {0}(", "student") +
"name varchar(100) PRIMARY KEY," +
"age INTEGER)";
PrivateObject obj = new PrivateObject(manager);
obj.Invoke("CreateTableIfNotExists", createTableTemplate);
var version = manager.GetTableVersion("student");
Assert.AreEqual(1, version);
List <SqlTemplate> sqlTemplates = new List <SqlTemplate>();
for (int i = 0; i < 1000; i++)
{
SqlTemplate sqlTemplate = new SqlTemplate();
string sql = string.Format("INSERT INTO student values(?, ?)");
sqlTemplate.SqlExpression = sql;
sqlTemplate.Params = new object[] { "student" + i, 20 };
sqlTemplates.Add(sqlTemplate);
}
int result = manager.ExecuteDML(sqlTemplates);
Assert.AreEqual(1000, result);
}
finally
{
DeleteDatabase(dbFilepath);
}
}
internal Dictionary <string, object> GetScript()
{
var provisioningTSql = new Dictionary <string, object>();
var logic = new Dictionary <string, string>();
var removeExisting = new StringBuilder();
removeExisting.Append(SqlTemplate.Get("DisableConstraintCheck"));
removeExisting.Append(SqlTemplate.Get("DisableAllTrigger"));
foreach (var ti in _scopeDescription)
{
var script = new ScriptLogic(ti);
logic.Add(ti.Name, script.SelectChanges);
}
for (var i = _scopeDescription.Count - 1; i >= 0; i--)
{
removeExisting.Append("delete from " + _scopeDescription[i].Name + ";");
}
removeExisting.Append(SqlTemplate.Get("EnableAllTrigger"));
removeExisting.Append(SqlTemplate.Get("EnableConstraintCheck"));
provisioningTSql.Add("Logic", logic);
provisioningTSql.Add("Truncate", removeExisting.ToString());
return(provisioningTSql);
}
public int CreateTableIfNotExists(CreateTableTemplate createTableTemplate)
{
lock (_dbLocker)
{
string tableName = createTableTemplate.TableName;
int version = createTableTemplate.Version;
string createSql = createTableTemplate.CreateSql;
int dbTableVersion = GetTableVersion(tableName);
if (version > dbTableVersion)
{
int effectRows = 0;
SqlTemplate createSqlTemplate = new SqlTemplate();
createSqlTemplate.SqlExpression = createTableTemplate.CreateSql;
effectRows += DropTableIfExists(tableName);
effectRows += ExecuteNonQuery(createSqlTemplate);
effectRows += UpdateTableVersion(tableName, version);
return(effectRows);
}
else
{
return(0);
}
}
}
private SqliteSqlTemplate GetSqliteSqlTemplate(SqlTemplate sqlTemplate)
{
if (sqlTemplate == null || string.IsNullOrWhiteSpace(sqlTemplate.SqlExpression))
{
// no need to execute value
return(null);
}
SqliteSqlTemplate result = new SqliteSqlTemplate();
result.SqlExpression = sqlTemplate.SqlExpression;
List <SQLiteParameter> newParams = new List <SQLiteParameter>();
if (string.IsNullOrWhiteSpace(sqlTemplate.SqlExpression) || sqlTemplate.Params == null || sqlTemplate.Params.Length == 0)
{
return(result);
}
string sqlExpression = sqlTemplate.SqlExpression;
String[] sqlPieces = sqlExpression.Split('?');
sqlExpression = string.Join("", sqlPieces.Select((d, i) => d + (i == sqlPieces.Length - 1 ? "" : "{" + i + "}")));
for (int i = 0; i < sqlTemplate.Params.Length; i++)
{
object value = sqlTemplate.Params[i];
string name = string.Format("p{0}", i);
SQLiteParameter sQLiteParameter = new SQLiteParameter(name, value);
newParams.Add(sQLiteParameter);
sqlExpression = sqlExpression.Replace("{" + i + "}", "@" + name);
}
result.SqlExpression = sqlExpression;
result.Params = newParams.ToArray();
return(result);
}
private string EnsureUniqueNodeName(string nodeName, int id = 0)
{
var template = SqlContext.Templates.Get("Umbraco.Core.DataTypeDefinitionRepository.EnsureUniqueNodeName", tsql => tsql
.Select <NodeDto>(x => Alias(x.NodeId, "id"), x => Alias(x.Text, "name"))
.From <NodeDto>()
.Where <NodeDto>(x => x.NodeObjectType == SqlTemplate.Arg <Guid>("nodeObjectType")));
var sql = template.Sql(NodeObjectTypeId);
var names = Database.Fetch <SimilarNodeName>(sql);
return(SimilarNodeName.GetUniqueName(names, id, nodeName));
}
/// <inheritdoc />
public void SetLastLogin(string username, DateTime date)
{
// Important - these queries are designed to execute without an exclusive WriteLock taken in our distributed lock
// table. However due to the data that we are updating which relies on version data we cannot update this data
// without taking some locks, otherwise we'll end up with strange situations because when a member is updated, that operation
// deletes and re-inserts all property data. So if there are concurrent transactions, one deleting and re-inserting and another trying
// to update there can be problems. This is only an issue for cmsPropertyData, not umbracoContentVersion because that table just
// maintains a single row and it isn't deleted/re-inserted.
// So the important part here is the ForUpdate() call on the select to fetch the property data to update.
// Update the cms property value for the member
var sqlSelectTemplateProperty = SqlContext.Templates.Get("Umbraco.Core.MemberRepository.SetLastLogin1", s => s
.Select <PropertyDataDto>(x => x.Id)
.From <PropertyDataDto>()
.InnerJoin <PropertyTypeDto>().On <PropertyTypeDto, PropertyDataDto>((l, r) => l.Id == r.PropertyTypeId)
.InnerJoin <ContentVersionDto>().On <ContentVersionDto, PropertyDataDto>((l, r) => l.Id == r.VersionId)
.InnerJoin <NodeDto>().On <NodeDto, ContentVersionDto>((l, r) => l.NodeId == r.NodeId)
.InnerJoin <MemberDto>().On <MemberDto, NodeDto>((l, r) => l.NodeId == r.NodeId)
.Where <NodeDto>(x => x.NodeObjectType == SqlTemplate.Arg <Guid>("nodeObjectType"))
.Where <PropertyTypeDto>(x => x.Alias == SqlTemplate.Arg <string>("propertyTypeAlias"))
.Where <MemberDto>(x => x.LoginName == SqlTemplate.Arg <string>("username"))
.ForUpdate());
var sqlSelectProperty = sqlSelectTemplateProperty.Sql(Constants.ObjectTypes.Member, Constants.Conventions.Member.LastLoginDate, username);
var update = Sql()
.Update <PropertyDataDto>(u => u
.Set(x => x.DateValue, date))
.WhereIn <PropertyDataDto>(x => x.Id, sqlSelectProperty);
Database.Execute(update);
// Update the umbracoContentVersion value for the member
var sqlSelectTemplateVersion = SqlContext.Templates.Get("Umbraco.Core.MemberRepository.SetLastLogin2", s => s
.Select <ContentVersionDto>(x => x.Id)
.From <ContentVersionDto>()
.InnerJoin <NodeDto>().On <NodeDto, ContentVersionDto>((l, r) => l.NodeId == r.NodeId)
.InnerJoin <MemberDto>().On <MemberDto, NodeDto>((l, r) => l.NodeId == r.NodeId)
.Where <NodeDto>(x => x.NodeObjectType == SqlTemplate.Arg <Guid>("nodeObjectType"))
.Where <MemberDto>(x => x.LoginName == SqlTemplate.Arg <string>("username")));
var sqlSelectVersion = sqlSelectTemplateVersion.Sql(Constants.ObjectTypes.Member, username);
Database.Execute(Sql()
.Update <ContentVersionDto>(u => u
.Set(x => x.VersionDate, date))
.WhereIn <ContentVersionDto>(x => x.Id, sqlSelectVersion));
}
private ScopeTableCollection ExtractSchema(string[] schemaList)
{
var tablesInfo = new ScopeTableCollection();
var extractSchema = SqlTemplate.Get("ExtractSchema");
using (var con = new SqlConnection(_request.ConnectionString))
{
using (var cmd = new SqlCommand())
{
con.Open();
foreach (var sequence in schemaList)
{
try
{
cmd.Parameters.Clear();
cmd.Connection = con;
cmd.CommandText = extractSchema;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@objname", SqlDbType.NVarChar).Value = sequence;
ScopeColumnCollection tableInfo;
using (var reader = cmd.ExecuteReader())
{
tableInfo = new ScopeColumnCollection();
if (!reader.HasRows)
{
throw new Exception($"Table {sequence} does not exists.");
}
while (reader.Read())
{
tableInfo.Add(new ScopeColumn
{
Name = reader.GetString(0),
Type = Runtime.GetType(reader.GetString(1)),
Length = reader.GetInt32(2),
Primary = Runtime.CheckInternalKey(_request.CustomKeyDefinitions, sequence, reader.GetString(0), reader.GetBoolean(4))
});
}
}
var primaryExists = (from ScopeColumn ci in tableInfo where ci.Primary select ci.Primary).FirstOrDefault();
if (primaryExists)
{
tablesInfo.Add(new ScopeTable {
Name = sequence, Columns = tableInfo
});
}
else
{
if (!_skipList.ContainsKey(sequence))
{
_skipList.Add(sequence, "Table needs primary key or clustered index.");
}
}
}
catch (Exception ex)
{
if (!_skipList.ContainsKey(sequence))
{
_skipList.Add(sequence, ex.ToString());
}
}
}
}
_scopeDescription = tablesInfo;
return(tablesInfo);
}
}
private Sql <ISqlContext> SqlObjectTypeNotTrashed(ISqlContext sqlContext, Guid nodeObjectType)
{
var syntax = sqlContext.SqlSyntax;
var sqlTemplate = sqlContext.Templates.Get(Constants.SqlTemplates.NuCacheDatabaseDataSource.ObjectTypeNotTrashedFilter, s =>
s.Where <NodeDto>(x => x.NodeObjectType == SqlTemplate.Arg <Guid?>("nodeObjectType") && x.Trashed == SqlTemplate.Arg <bool>("trashed")));
var sql = sqlTemplate.Sql(nodeObjectType, false);
return(sql);
}
private Sql <ISqlContext> SqlWhereNodeIdX(ISqlContext sqlContext, int id)
{
var syntax = sqlContext.SqlSyntax;
var sqlTemplate = sqlContext.Templates.Get(Constants.SqlTemplates.NuCacheDatabaseDataSource.WhereNodeIdX, s =>
s.Where <NodeDto>(x => x.NodeId == SqlTemplate.Arg <int>("id"), "x"));
var sql = sqlTemplate.Sql(id);
return(sql);
}
public void TestInsertOrReplaceDatas()
{
string dbFilepath = "my.db";
DeleteDatabase(dbFilepath);
try
{
var manager = new SqliteDatabaseManager(dbFilepath, _password);
manager.Initialize();
CreateTableTemplate createTableTemplate = new CreateTableTemplate();
createTableTemplate.TableName = "student";
createTableTemplate.Version = 1;
createTableTemplate.CreateSql = string.Format("CREATE TABLE IF NOT EXISTS {0}(", "student") +
"name varchar(100) PRIMARY KEY," +
"age INTEGER)";
PrivateObject obj = new PrivateObject(manager);
obj.Invoke("CreateTableIfNotExists", createTableTemplate);
var version = manager.GetTableVersion("student");
Assert.AreEqual(1, version);
string json = @"[
{
""name"": ""marray"",
""age"": 20
},
{
""name"": ""Jack"",
""age"": 21
}
]";
TableInfo tableInfo = new TableInfo();
tableInfo.TableName = "student";
tableInfo.ColumnNames = new string[] { "name", "age" };
IEnumerable <Dictionary <string, object> > datas = JsonConvert.DeserializeObject <IEnumerable <Dictionary <string, object> > >(json);
var effectRows = manager.InsertOrReplaceDatas(tableInfo, datas);
Console.WriteLine("effectRows: " + effectRows);
SqlTemplate querySqlTemplate = new SqlTemplate();
querySqlTemplate.SqlExpression = "SELECT * FROM student";
var items = manager.QueryData(querySqlTemplate);
Assert.AreEqual(2, items.Count());
json = @"[
{
""name"": ""Jack"",
""age"": 30
}
]";
datas = JsonConvert.DeserializeObject <IEnumerable <Dictionary <string, object> > >(json);
effectRows = manager.InsertOrReplaceDatas(tableInfo, datas);
Console.WriteLine("effectRows: " + effectRows);
querySqlTemplate = new SqlTemplate();
querySqlTemplate.SqlExpression = "SELECT * FROM student";
items = manager.QueryData(querySqlTemplate);
Assert.AreEqual(30.ToString(), items.Last()["age"].ToString());
}
finally
{
DeleteDatabase(dbFilepath);
}
}
public bool ValidateLoginSession(int userId, Guid sessionId)
{
// with RepeatableRead transaction mode, read-then-update operations can
// cause deadlocks, and the ForUpdate() hint is required to tell the database
// to acquire an exclusive lock when reading
// that query is going to run a *lot*, make it a template
var t = SqlContext.Templates.Get("Umbraco.Core.UserRepository.ValidateLoginSession", s => s
.Select <UserLoginDto>()
.From <UserLoginDto>()
.Where <UserLoginDto>(x => x.SessionId == SqlTemplate.Arg <Guid>("sessionId"))
.ForUpdate());
var sql = t.Sql(sessionId);
var found = Database.Query <UserLoginDto>(sql).FirstOrDefault();
if (found == null || found.UserId != userId || found.LoggedOutUtc.HasValue)
{
return(false);
}
//now detect if there's been a timeout
if (DateTime.UtcNow - found.LastValidatedUtc > TimeSpan.FromMinutes(_globalSettings.TimeOutInMinutes))
{
//timeout detected, update the record
ClearLoginSession(sessionId);
return(false);
}
//update the validate date
found.LastValidatedUtc = DateTime.UtcNow;
Database.Update(found);
return(true);
}
}//method
public virtual SqlFragment BuildSqlForSqlExpression(SqlExpression expr)
{
switch (expr.SqlNodeType)
{
case SqlExpressionType.Alias:
var aliasExpr = (AliasedExpression)expr;
return(BuildLinqExpressionSql(aliasExpr.Expression)); //Alias will be added later
case SqlExpressionType.SqlFunction:
return(BuildSqlForSqlFunctionExpression((SqlFunctionExpression)expr));
case SqlExpressionType.Column:
var colExpr = (ColumnExpression)expr;
return(GetColumnRefSql(colExpr, forOutput: false));
case SqlExpressionType.SubSelect:
var subs = (SubSelectExpression)expr;
var subSelect = BuildSqlForSqlExpression(subs.Select);
return(CompositeSqlFragment.Parenthesize(subSelect));
case SqlExpressionType.Table:
var te = (TableExpression)expr;
return(te.TableInfo.SqlFullName);
case SqlExpressionType.TableFilter:
var tfe = (TableFilterExpression)expr;
var argParts = BuildSqls(tfe.Columns);
var sqlTempl = new SqlTemplate(tfe.Filter.EntityFilter.Template.StandardForm);
var part = sqlTempl.Format(argParts);
return(part);
case SqlExpressionType.ExternalValue:
var extValue = (ExternalValueExpression)expr;
var ph = CreateSqlPlaceHolder(extValue);
// it is already added to placeholders list
return(ph);
case SqlExpressionType.Group:
var ge = (GroupExpression)expr;
return(BuildLinqExpressionSql(ge.GroupedExpression));
case SqlExpressionType.OrderBy:
return(BuildOrderByMember((OrderByExpression)expr));
case SqlExpressionType.Aggregate:
return(BuildAggregateSql((AggregateExpression)expr));
case SqlExpressionType.Select:
var selectSql = BuildSelectSql((SelectExpression)expr);
return(CompositeSqlFragment.Parenthesize(selectSql));
case SqlExpressionType.DerivedTable:
// Looks like we never come here
//!!! investigate this
//TODO: investigate DerivedTable SQL
return(SqlTerms.Star);
default:
Util.Throw("SqlExpression->SQL not implemented, SqlNodeType: {0}", expr.SqlNodeType);
return(null); //never happens
}//switch
}//method
public void SqlTemplateArgs()
{
var mappers = new NPoco.MapperCollection {
new NullableDateMapper()
};
var factory = new FluentPocoDataFactory((type, iPocoDataFactory) => new PocoDataBuilder(type, mappers).Init());
var sqlContext = new SqlContext(new SqlServerSyntaxProvider(Options.Create(new GlobalSettings())), DatabaseType.SQLCe, factory);
var sqlTemplates = new SqlTemplates(sqlContext);
const string sqlBase = "SELECT [zbThing1].[id] AS [Id], [zbThing1].[name] AS [Name] FROM [zbThing1] WHERE ";
SqlTemplate template = sqlTemplates.Get("sql1", s => s.Select <Thing1Dto>().From <Thing1Dto>()
.Where <Thing1Dto>(x => x.Name == SqlTemplate.Arg <string>("value")));
Sql <ISqlContext> sql = template.Sql("foo");
Assert.AreEqual(sqlBase + "(([zbThing1].[name] = @0))", sql.SQL.NoCrLf());
Assert.AreEqual(1, sql.Arguments.Length);
Assert.AreEqual("foo", sql.Arguments[0]);
sql = template.Sql(123);
Assert.AreEqual(sqlBase + "(([zbThing1].[name] = @0))", sql.SQL.NoCrLf());
Assert.AreEqual(1, sql.Arguments.Length);
Assert.AreEqual(123, sql.Arguments[0]);
template = sqlTemplates.Get("sql2", s => s.Select <Thing1Dto>().From <Thing1Dto>()
.Where <Thing1Dto>(x => x.Name == SqlTemplate.Arg <string>("value")));
sql = template.Sql(new { value = "foo" });
Assert.AreEqual(sqlBase + "(([zbThing1].[name] = @0))", sql.SQL.NoCrLf());
Assert.AreEqual(1, sql.Arguments.Length);
Assert.AreEqual("foo", sql.Arguments[0]);
sql = template.Sql(new { value = 123 });
Assert.AreEqual(sqlBase + "(([zbThing1].[name] = @0))", sql.SQL.NoCrLf());
Assert.AreEqual(1, sql.Arguments.Length);
Assert.AreEqual(123, sql.Arguments[0]);
Assert.Throws <InvalidOperationException>(() => template.Sql(new { xvalue = 123 }));
Assert.Throws <InvalidOperationException>(() => template.Sql(new { value = 123, xvalue = 456 }));
var i = 666;
template = sqlTemplates.Get("sql3", s => s.Select <Thing1Dto>().From <Thing1Dto>()
.Where <Thing1Dto>(x => x.Id == i));
sql = template.Sql("foo");
Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf());
Assert.AreEqual(1, sql.Arguments.Length);
Assert.AreEqual("foo", sql.Arguments[0]);
sql = template.Sql(123);
Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf());
Assert.AreEqual(1, sql.Arguments.Length);
Assert.AreEqual(123, sql.Arguments[0]);
// but we cannot name them, because the arg name is the value of "i"
// so we have to explicitely create the argument
template = sqlTemplates.Get("sql4", s => s.Select <Thing1Dto>().From <Thing1Dto>()
.Where <Thing1Dto>(x => x.Id == SqlTemplate.Arg <int>("i")));
sql = template.Sql("foo");
Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf());
Assert.AreEqual(1, sql.Arguments.Length);
Assert.AreEqual("foo", sql.Arguments[0]);
sql = template.Sql(123);
Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf());
Assert.AreEqual(1, sql.Arguments.Length);
Assert.AreEqual(123, sql.Arguments[0]);
// and thanks to a patched visitor, this now works
sql = template.Sql(new { i = "foo" });
Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf());
Assert.AreEqual(1, sql.Arguments.Length);
Assert.AreEqual("foo", sql.Arguments[0]);
sql = template.Sql(new { i = 123 });
Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0))", sql.SQL.NoCrLf());
Assert.AreEqual(1, sql.Arguments.Length);
Assert.AreEqual(123, sql.Arguments[0]);
Assert.Throws <InvalidOperationException>(() => template.Sql(new { j = 123 }));
Assert.Throws <InvalidOperationException>(() => template.Sql(new { i = 123, j = 456 }));
// now with more arguments
template = sqlTemplates.Get("sql4a", s => s.Select <Thing1Dto>().From <Thing1Dto>()
.Where <Thing1Dto>(x => x.Id == SqlTemplate.Arg <int>("i") && x.Name == SqlTemplate.Arg <string>("name")));
sql = template.Sql(0, 1);
Assert.AreEqual(sqlBase + "((([zbThing1].[id] = @0) AND ([zbThing1].[name] = @1)))", sql.SQL.NoCrLf());
Assert.AreEqual(2, sql.Arguments.Length);
Assert.AreEqual(0, sql.Arguments[0]);
Assert.AreEqual(1, sql.Arguments[1]);
template = sqlTemplates.Get("sql4b", s => s.Select <Thing1Dto>().From <Thing1Dto>()
.Where <Thing1Dto>(x => x.Id == SqlTemplate.Arg <int>("i"))
.Where <Thing1Dto>(x => x.Name == SqlTemplate.Arg <string>("name")));
sql = template.Sql(0, 1);
Assert.AreEqual(sqlBase + "(([zbThing1].[id] = @0)) AND (([zbThing1].[name] = @1))", sql.SQL.NoCrLf());
Assert.AreEqual(2, sql.Arguments.Length);
Assert.AreEqual(0, sql.Arguments[0]);
Assert.AreEqual(1, sql.Arguments[1]);
// works, magic
template = sqlTemplates.Get("sql5", s => s.Select <Thing1Dto>().From <Thing1Dto>()
.WhereIn <Thing1Dto>(x => x.Id, SqlTemplate.ArgIn <int>("i")));
sql = template.Sql("foo");
Assert.AreEqual(sqlBase + "([zbThing1].[id] IN (@0))", sql.SQL.NoCrLf());
Assert.AreEqual(1, sql.Arguments.Length);
Assert.AreEqual("foo", sql.Arguments[0]);
sql = template.Sql(new[] { 1, 2, 3 });
Assert.AreEqual(sqlBase + "([zbThing1].[id] IN (@0,@1,@2))", sql.SQL.NoCrLf());
Assert.AreEqual(3, sql.Arguments.Length);
Assert.AreEqual(1, sql.Arguments[0]);
Assert.AreEqual(2, sql.Arguments[1]);
Assert.AreEqual(3, sql.Arguments[2]);
template = sqlTemplates.Get("sql5a", s => s.Select <Thing1Dto>().From <Thing1Dto>()
.WhereIn <Thing1Dto>(x => x.Id, SqlTemplate.ArgIn <int>("i"))
.Where <Thing1Dto>(x => x.Name == SqlTemplate.Arg <string>("name")));
sql = template.Sql("foo", "bar");
Assert.AreEqual(sqlBase + "([zbThing1].[id] IN (@0)) AND (([zbThing1].[name] = @1))", sql.SQL.NoCrLf());
Assert.AreEqual(2, sql.Arguments.Length);
Assert.AreEqual("foo", sql.Arguments[0]);
Assert.AreEqual("bar", sql.Arguments[1]);
sql = template.Sql(new[] { 1, 2, 3 }, "bar");
Assert.AreEqual(sqlBase + "([zbThing1].[id] IN (@0,@1,@2)) AND (([zbThing1].[name] = @3))", sql.SQL.NoCrLf());
Assert.AreEqual(4, sql.Arguments.Length);
Assert.AreEqual(1, sql.Arguments[0]);
Assert.AreEqual(2, sql.Arguments[1]);
Assert.AreEqual(3, sql.Arguments[2]);
Assert.AreEqual("bar", sql.Arguments[3]);
// note however that using WhereIn in a template means that the SQL is going
// to be parsed and arguments are going to be expanded etc - it *may* be a better
// idea to just add the WhereIn to a templated, immutable SQL template
// more fun...
template = sqlTemplates.Get("sql6", s => s.Select <Thing1Dto>().From <Thing1Dto>()
// do NOT do this, this is NOT a visited expression
//// .Append(" AND whatever=@0", SqlTemplate.Arg<string>("j"))
// does not work anymore - due to proper TemplateArg
//// instead, directly name the argument
////.Append("AND whatever=@0", "j")
////.Append("AND whatever=@0", "k")
// instead, explicitely create the argument
.Append("AND whatever=@0", SqlTemplate.Arg("j"))
.Append("AND whatever=@0", SqlTemplate.Arg("k")));
sql = template.Sql(new { j = new[] { 1, 2, 3 }, k = "oops" });
Assert.AreEqual(sqlBase.TrimEnd("WHERE ") + "AND whatever=@0,@1,@2 AND whatever=@3", sql.SQL.NoCrLf());
Assert.AreEqual(4, sql.Arguments.Length);
Assert.AreEqual(1, sql.Arguments[0]);
Assert.AreEqual(2, sql.Arguments[1]);
Assert.AreEqual(3, sql.Arguments[2]);
Assert.AreEqual("oops", sql.Arguments[3]);
}
public void WithTemplate()
{
SqlTemplates.Clear();
for (var i = 0; i < 100; i++)
{
var template = SqlTemplates.Get("test", s => s
.Select <NPocoFetchTests.Thing1Dto>()
.From <NPocoFetchTests.Thing1Dto>()
.Where <NPocoFetchTests.Thing1Dto>(x => x.Name == SqlTemplate.Arg <string>("name")));
var sql = template.Sql(new { name = "yada" });
var sqlString = sql.SQL; // force-build the SQL
}
}
public object Post([FromBody] dynamic req)
{
try
{
var request = Utils.parseXJSON(JObject.Parse(req.xjson.ToString()));
if (!AuthOk((Guid)request["sessionId"], (String)request["authToken"]))
{
return(NoAuthResponse());
}
var database = Program.BuhtaConfig.GetDatabase(request["dbName"].ToString());
if (database == null)
{
return(new ResponseObject()
{
error = $"invalid database '{request["dbName"]}'"
});
}
DbConnection conn;
if (database.Dialect == "mssql")
{
conn = new SqlConnection(database.ConnectionString);
}
else
if (database.Dialect == "mysql")
{
conn = new MySqlConnection(database.ConnectionString);
}
else
if (database.Dialect == "postgres")
{
conn = new NpgsqlConnection(database.ConnectionString);
}
else
{
return(new ResponseObject()
{
error = $"invalid database sql dialect '{database.Dialect}'"
});
}
var sqlTemplatePath = request["sqlTemplatePath"].ToString();
//var paramsObj = req.paramsObj.ToString();
//var sqlLines = SqlTemplate.emitSqlBatchFromTemplatePath(database.Dialect, sqlTemplatePath, JObject.Parse(paramsObj),HttpContext,Request);
var sqlLines = SqlTemplate.emitSqlBatchFromTemplatePath(database.Dialect, sqlTemplatePath, (JObject)request["paramsObj"], HttpContext, Request);
try
{
using (conn)
{
conn.Open();
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
using (JsonWriter jsonWriter = new JsonTextWriter(sw))
{
//ctx.WriteString(`{ "rowsets":[`);
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("rowsets");
jsonWriter.WriteStartArray();
foreach (string sql in sqlLines)
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
var reader = cmd.ExecuteReader();
if (reader.FieldCount == 0)
{
reader.Dispose();
cmd.Dispose();
continue;
}
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("columns");
jsonWriter.WriteStartArray();
for (int col = 0; col < reader.FieldCount; col++)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("name");
jsonWriter.WriteValue(reader.GetName(col));
jsonWriter.WritePropertyName("type");
jsonWriter.WriteValue(reader.GetDataTypeName(col));
jsonWriter.WriteEndObject();
}
jsonWriter.WriteEnd();
jsonWriter.WritePropertyName("rows");
jsonWriter.WriteStartArray();
while (true)
{
//jsonWriter.WriteStartArray();
while (reader.Read())
{
jsonWriter.WriteStartArray();
#region for
for (int colIndex = 0; colIndex < reader.FieldCount; colIndex++)
{
var value = reader[colIndex];
//reader.GetDataTypeName(37)
if (value is DBNull)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("t");
jsonWriter.WriteValue("N");
jsonWriter.WriteEndObject();
}
else
if (value is Array && (value as Array).Length == 16 && database.Dialect == "mysql") // это guid в mysql
{
var guid = new Guid(value as byte[]);
// jsonWriter.WriteValue("<Guid>" + Convert.ToBase64String(guid.ToByteArray()));
jsonWriter.WriteValue("<Guid>" + guid);
}
else
if (reader.GetDataTypeName(colIndex) == "BIT" && database.Dialect == "mysql") // это boolean в mysql
{
jsonWriter.WriteValue((UInt64)value != 0);
}
else
if (value is Array) // это BLOB
{
jsonWriter.WriteValue("<ArrayBuffer>" + Convert.ToBase64String((byte[])value));
}
else
if (value is Guid)
{
//jsonWriter.WriteValue("<Guid>" + Convert.ToBase64String(((Guid)value).ToByteArray()));
jsonWriter.WriteValue("<Guid>" + value);
}
else
if (value is DateTime)
{
var date = (DateTime)value;
//jsonWriter.WriteStartObject();
//jsonWriter.WritePropertyName("t");
//jsonWriter.WriteValue("D");
//jsonWriter.WritePropertyName("v");
if (date.TimeOfDay == TimeSpan.Zero)
{
jsonWriter.WriteValue("<Date>" + (date).ToString("yyyy-MM-dd"));
}
else
if (date.Year == 0 && date.Month == 1 && date.Day == 1)
{
jsonWriter.WriteValue("<Time>" + (date).ToString("HH:mm:ss.fff"));
}
else
{
jsonWriter.WriteValue("<Date>" + (date).ToString("yyyy-MM-dd HH:mm:ss.fff"));
}
//jsonWriter.WriteEndObject();
}
else
if (value is TimeSpan)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("t");
jsonWriter.WriteValue("T");
jsonWriter.WritePropertyName("h");
jsonWriter.WriteValue(((TimeSpan)value).Hours);
jsonWriter.WritePropertyName("m");
jsonWriter.WriteValue(((TimeSpan)value).Minutes);
jsonWriter.WritePropertyName("s");
jsonWriter.WriteValue(((TimeSpan)value).Seconds);
jsonWriter.WritePropertyName("ms");
jsonWriter.WriteValue(((TimeSpan)value).Milliseconds);
jsonWriter.WriteEndObject();
}
else
{
jsonWriter.WriteValue(value);
}
//Console.WriteLine(String.Format("{0}", reader[0]));
}
#endregion
jsonWriter.WriteEnd();
}
//jsonWriter.WriteEnd();
//if (database.Dialect != "mssql" || !reader.NextResult())
if (!reader.NextResult())
{
break;
}
else
{
jsonWriter.WriteEnd();
jsonWriter.WriteEndObject();
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("columns");
jsonWriter.WriteStartArray();
for (int col = 0; col < reader.FieldCount; col++)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("name");
jsonWriter.WriteValue(reader.GetColumnSchema()[col].ColumnName);
jsonWriter.WritePropertyName("type");
jsonWriter.WriteValue(reader.GetColumnSchema()[col].DataTypeName);
jsonWriter.WriteEndObject();
}
jsonWriter.WriteEnd();
jsonWriter.WritePropertyName("rows");
jsonWriter.WriteStartArray();
}
//Console.WriteLine("----- NEXT ------");
}
jsonWriter.WriteEnd();
jsonWriter.WriteEndObject();
reader.Dispose();
cmd.Dispose();
}
}
jsonWriter.WriteEnd();
jsonWriter.WriteEndObject();
}
return(new ResponseObject()
{
compressed = Utils.CompressToByteArray(sb.ToString())
});
//return new ResponseObject() { json = sb.ToString() }; // это вариант без компрессии
}
}
catch (Exception e)
{
return(new ResponseObject()
{
error = Utf16ToUtf8(e.Message)
});
}
}
catch (Exception e)
{
return(new { error = e.Message });
}
}
// we want arrays, we want them all loaded, not an enumerable
private Sql <ISqlContext> SqlContentSourcesSelect(Func <ISqlContext, Sql <ISqlContext> >?joins = null)
{
SqlTemplate sqlTemplate = SqlContext.Templates.Get(
Constants.SqlTemplates.NuCacheDatabaseDataSource.ContentSourcesSelect,
tsql =>
tsql.Select <NodeDto>(
x => Alias(x.NodeId, "Id"),
x => Alias(x.UniqueId, "Key"),
x => Alias(x.Level, "Level"),
x => Alias(x.Path, "Path"),
x => Alias(x.SortOrder, "SortOrder"),
x => Alias(x.ParentId, "ParentId"),
x => Alias(x.CreateDate, "CreateDate"),
x => Alias(x.UserId, "CreatorId"))
.AndSelect <ContentDto>(x => Alias(x.ContentTypeId, "ContentTypeId"))
.AndSelect <DocumentDto>(x => Alias(x.Published, "Published"), x => Alias(x.Edited, "Edited"))
.AndSelect <ContentVersionDto>(
x => Alias(x.Id, "VersionId"),
x => Alias(x.Text, "EditName"),
x => Alias(x.VersionDate, "EditVersionDate"),
x => Alias(x.UserId, "EditWriterId"))
.AndSelect <DocumentVersionDto>(x => Alias(x.TemplateId, "EditTemplateId"))
.AndSelect <ContentVersionDto>(
"pcver",
x => Alias(x.Id, "PublishedVersionId"),
x => Alias(x.Text, "PubName"),
x => Alias(x.VersionDate, "PubVersionDate"),
x => Alias(x.UserId, "PubWriterId"))
.AndSelect <DocumentVersionDto>("pdver", x => Alias(x.TemplateId, "PubTemplateId"))
.AndSelect <ContentNuDto>("nuEdit", x => Alias(x.Data, "EditData"))
.AndSelect <ContentNuDto>("nuPub", x => Alias(x.Data, "PubData"))
.AndSelect <ContentNuDto>("nuEdit", x => Alias(x.RawData, "EditDataRaw"))
.AndSelect <ContentNuDto>("nuPub", x => Alias(x.RawData, "PubDataRaw"))
.From <NodeDto>());
Sql <ISqlContext>?sql = sqlTemplate.Sql();
// TODO: I'm unsure how we can format the below into SQL templates also because right.Current and right.Published end up being parameters
if (joins != null)
{
sql = sql.Append(joins(sql.SqlContext));
}
sql = sql
.InnerJoin <ContentDto>().On <NodeDto, ContentDto>((left, right) => left.NodeId == right.NodeId)
.InnerJoin <DocumentDto>().On <NodeDto, DocumentDto>((left, right) => left.NodeId == right.NodeId)
.InnerJoin <ContentVersionDto>()
.On <NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId && right.Current)
.InnerJoin <DocumentVersionDto>()
.On <ContentVersionDto, DocumentVersionDto>((left, right) => left.Id == right.Id)
.LeftJoin <ContentVersionDto>(
j =>
j.InnerJoin <DocumentVersionDto>("pdver")
.On <ContentVersionDto, DocumentVersionDto>(
(left, right) => left.Id == right.Id && right.Published == true, "pcver", "pdver"),
"pcver")
.On <NodeDto, ContentVersionDto>((left, right) => left.NodeId == right.NodeId, aliasRight: "pcver")
.LeftJoin <ContentNuDto>("nuEdit").On <NodeDto, ContentNuDto>(
(left, right) => left.NodeId == right.NodeId && right.Published == false, aliasRight: "nuEdit")
.LeftJoin <ContentNuDto>("nuPub").On <NodeDto, ContentNuDto>(
(left, right) => left.NodeId == right.NodeId && right.Published == true, aliasRight: "nuPub");
return(sql);
}