public void Upgrade(IDotEntityTransaction transaction)
{
Db.AddColumn <MenuItem, string>(nameof(MenuItem.Description), "", transaction);
Db.AddColumn <MenuItem, string>(nameof(MenuItem.ExtraData), "", transaction);
//execute the following only if it's not a fresh install
if (transaction.CurrentlyRanVersions.All(x => x.GetType() != typeof(Version1)))
{
var parentMenuItemIdCol = DotEntityDb.Provider.SafeEnclose("ParentMenuItemId");
var parentIdCol = DotEntityDb.Provider.SafeEnclose(nameof(MenuItem.ParentId));
var menuItemTable = DotEntityDb.GetTableNameForType <MenuItem>();
Db.AddColumn <MenuItem, int>(nameof(MenuItem.ParentId), 0, transaction);
Db.Query($"UPDATE {menuItemTable} SET {parentIdCol}={parentMenuItemIdCol}", null, transaction);
Db.DropColumn <MenuItem>("ParentMenuItemId", transaction);
}
var cartTable = DotEntityDb.Provider.SafeEnclose(DotEntityDb.GetTableNameForType <Cart>());
var orderTable = DotEntityDb.Provider.SafeEnclose(DotEntityDb.GetTableNameForType <Order>());
var selectedShippingOptionCol = DotEntityDb.Provider.SafeEnclose(nameof(Cart.SelectedShippingOption));
var query =
$"UPDATE {cartTable} SET {selectedShippingOptionCol}='[{{\"name\":\"' + {selectedShippingOptionCol} + '\"}}]'";
Db.Query(query, null, transaction);
query =
$"UPDATE {orderTable} SET {selectedShippingOptionCol}='[{{\"name\":\"' + {selectedShippingOptionCol} + '\"}}]'";
Db.Query(query, null, transaction);
}
public IList <User> GetSubscribers(string subscriptionGuid, object data)
{
var dataSerialized = Serialized(data);
var userTable = DotEntityDb.Provider.SafeEnclose(DotEntityDb.GetTableNameForType <User>());
var subscriptionTable = DotEntityDb.Provider.SafeEnclose(DotEntityDb.GetTableNameForType <Subscription>());
var idCol = DotEntityDb.Provider.SafeEnclose(nameof(User.Id));
var userIdCol = DotEntityDb.Provider.SafeEnclose(nameof(Subscription.UserId));
var emailCol = DotEntityDb.Provider.SafeEnclose(nameof(Subscription.Email));
var guidCol = DotEntityDb.Provider.SafeEnclose(nameof(Subscription.SubscriptionGuid));
var dataCol = DotEntityDb.Provider.SafeEnclose(nameof(Subscription.Data));
var activeCol = DotEntityDb.Provider.SafeEnclose(nameof(User.Active));
var deletedCol = DotEntityDb.Provider.SafeEnclose(nameof(User.Deleted));
var dataColString = data == null ? $"{dataCol} IS NULL" : $"{dataCol}=@data";
var query =
$"SELECT * FROM {userTable} WHERE {activeCol}=@active AND {deletedCol}=@deleted AND {idCol} IN (SELECT {userIdCol} FROM {subscriptionTable} WHERE {guidCol}=@subscriptionGuid AND {dataColString} AND {userIdCol} IS NOT NULL) OR " +
$"{emailCol} IN (SELECT {emailCol} FROM {subscriptionTable} WHERE {guidCol}=@subscriptionGuid AND {dataColString} AND {emailCol} IS NOT NULL)";
using (var result = EntitySet.Query(query, new { subscriptionGuid, data = dataSerialized, active = true, deleted = false }))
{
return(result.SelectAllAs <User>().ToList());
}
//Repository.Join<User>("Email", "Email")
// .Join<User>("UserId", "Id", SourceColumn.Parent)
// .Relate<User>((subscription, user) =>
// {
// if(!users.Contains(user))
// users.Add(user);
// })
// .Where(x => x.SubscriptionGuid == subscriptionGuid && x.Data == dataSerialized)
// .SelectNested();
//return users;
}
public static IList <string> GetInstalledVersions(string callingContextName)
{
if (callingContextName.IsNullEmptyOrWhiteSpace())
{
throw new ArgumentNullException(nameof(callingContextName));
}
return(DotEntityDb.GetAppliedVersions(callingContextName));
}
public static void InitDatabase(IDatabaseSettings dbSettings)
{
if (dbSettings.HasSettings())
{
DotEntityDb.GlobalTableNamePrefix = TablePrefix;
DotEntityDb.Initialize(dbSettings.ConnectionString, GetProvider(dbSettings.ProviderName));
}
}
public override string GenerateSelect <T>(out IList <QueryInfo> parameters, List <Expression <Func <T, bool> > > where = null, Dictionary <Expression <Func <T, object> >, RowOrder> orderBy = null, int page = 1, int count = int.MaxValue)
{
parameters = new List <QueryInfo>();
var builder = new StringBuilder();
var tableName = DotEntityDb.GetTableNameForType <T>();
var whereString = "";
if (where != null)
{
var parser = new ExpressionTreeParser();
var whereStringBuilder = new List <string>();
foreach (var wh in where)
{
whereStringBuilder.Add(parser.GetWhereString(wh));
}
parameters = parser.QueryInfoList;
whereString = string.Join(" AND ", whereStringBuilder).Trim();
}
var orderByStringBuilder = new List <string>();
var orderByString = "";
if (orderBy != null)
{
var parser = new ExpressionTreeParser();
foreach (var ob in orderBy)
{
orderByStringBuilder.Add(parser.GetOrderByString(ob.Key) + (ob.Value == RowOrder.Descending ? " DESC" : ""));
}
orderByString = string.Join(", ", orderByStringBuilder).Trim(',');
}
// make the query now
builder.Append($"SELECT {QueryParserUtilities.GetSelectColumnString(new List<Type>() { typeof(T) })} FROM ");
builder.Append(tableName.ToEnclosed());
if (!string.IsNullOrEmpty(whereString))
{
builder.Append(" WHERE " + whereString);
}
if (!string.IsNullOrEmpty(orderByString))
{
builder.Append(" ORDER BY " + orderByString);
}
if (page > 1 || count != int.MaxValue)
{
var offset = (page - 1) * count;
builder.Append($" LIMIT {offset},{count}");
}
var query = builder.ToString().Trim() + ";";
return(query);
}
public int GetPoints(int userId)
{
var tableName = DotEntityDb.Provider.SafeEnclose(DotEntityDb.GetTableNameForType <UserPoint>());
var query = $"SELECT SUM({nameof(UserPoint.Points)}) FROM {tableName} WHERE UserId=@UserId";
using (var resulting = EntitySet.Query(query, new { UserId = userId }))
{
return(resulting.SelectScalerAs <int>());
}
}
public static IEnumerable <PropertyInfo> GetDatabaseUsableProperties(this Type type)
{
var excludedColumns = DotEntityDb.GetIgnoredColumns(type);
var allProps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).Where(x => !x.GetAccessors()[0].IsVirtual);
if (excludedColumns != null)
{
allProps = allProps.Where(prop => !excludedColumns.Contains(prop.Name));
}
return(type.IsAnonymousType() ? allProps : allProps.Where(x => x.CanWrite));
}
public void Init()
{
DotEntityDb.Initialize(SqliteConnectionString, new SqliteDatabaseProvider());
var createProduct = @"CREATE TABLE Product
( Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
ProductName TEXT NOT NULL,
ProductDescription TEXT NULL,
DateCreated TEXT NOT NULL,
Price NUMERIC NOT NULL,
IsActive NUMERIC NOT NULL);";
EntitySet.Query(createProduct, null);
}
public void Downgrade(IDotEntityTransaction transaction)
{
Db.DropColumn <MenuItem>(nameof(MenuItem.Description), transaction);
Db.DropColumn <MenuItem>(nameof(MenuItem.ExtraData), transaction);
var parentMenuItemIdCol = DotEntityDb.Provider.SafeEnclose("ParentMenuItemId");
var parentIdCol = DotEntityDb.Provider.SafeEnclose(nameof(MenuItem.ParentId));
var menuItemTable = DotEntityDb.GetTableNameForType <MenuItem>();
Db.AddColumn <MenuItem, int>("ParentMenuItemId", 0, transaction);
Db.Query($"UPDATE {menuItemTable} SET {parentMenuItemIdCol}={parentIdCol}", null, transaction);
Db.DropColumn <MenuItem>(nameof(MenuItem.ParentId), transaction);
}
public void Downgrade(IDotEntityTransaction transaction)
{
Db.DropTable <EntityTag>(transaction);
Db.DropColumn <MenuItem>(nameof(MenuItem.OpenInNewWindow), transaction);
var parentCategoryIdCol = DotEntityDb.Provider.SafeEnclose(nameof(Category.ParentCategoryId));
var parentIdCol = DotEntityDb.Provider.SafeEnclose(nameof(Category.ParentId));
var categoryTable = DotEntityDb.GetTableNameForType <Category>();
Db.AddColumn <Category, int>(nameof(Category.ParentCategoryId), 0, transaction);
Db.Query($"UPDATE {categoryTable} SET {parentCategoryIdCol}={parentIdCol}", null, transaction);
Db.DropColumn <Category>(nameof(Category.ParentId), transaction);
Db.DropColumn <ContentPage>(nameof(ContentPage.ParentId), transaction);
}
public decimal GetBalance(int userId)
{
var tableName = DotEntityDb.GetTableNameForType <StoreCredit>();
var creditColumnName = DotEntityDb.Provider.SafeEnclose(nameof(StoreCredit.Credit));
var userIdColumnName = DotEntityDb.Provider.SafeEnclose(nameof(StoreCredit.UserId));
var availableOnColumnName = DotEntityDb.Provider.SafeEnclose(nameof(StoreCredit.AvailableOn));
var expiresOnColumnName = DotEntityDb.Provider.SafeEnclose(nameof(StoreCredit.ExpiresOn));
var lockedColumnName = DotEntityDb.Provider.SafeEnclose(nameof(StoreCredit.Locked));
var query = $"SELECT SUM({creditColumnName}) FROM {tableName} WHERE {userIdColumnName}=@UserId AND {lockedColumnName}=@Locked AND {availableOnColumnName}<=@AvailableOn AND ({expiresOnColumnName} IS NULL OR {expiresOnColumnName}>@ExpiresOn)";
using (var result = EntitySet.Query(query, new { UserId = userId, AvailableOn = DateTime.UtcNow, ExpiresOn = DateTime.UtcNow, Locked = false }))
{
return(result.SelectScalerAs <decimal>());
}
}
public void Init()
{
DotEntityDb.Initialize(MySqlConnectionString, new MySqlDatabaseProvider("mytest"));
var createProduct = @"CREATE TABLE Product
( Id INT NOT NULL AUTO_INCREMENT,
ProductName TEXT NOT NULL,
ProductDescription TEXT NULL,
DateCreated DATETIME NOT NULL,
Price NUMERIC(18,0) NOT NULL,
IsActive TINYINT(1) NOT NULL,
PRIMARY KEY (Id));";
EntitySet.Query(createProduct, null);
}
public static bool IsValidConnection(string providerName, string connectionString)
{
DotEntityDb.Initialize(connectionString, GetProvider(providerName));
try
{
DotEntityDb.Provider.Connection.Open();
return(true);
}
catch
{
return(false);
}
finally
{
DotEntityDb.Provider.Connection.Close();
}
}
public void Upgrade(IDotEntityTransaction transaction)
{
Db.CreateTable <EntityTag>(transaction);
//execute the following only if it's not a fresh install
if (transaction.CurrentlyRanVersions.All(x => x.GetType() != typeof(Version1)))
{
Db.AddColumn <MenuItem, bool>(nameof(MenuItem.OpenInNewWindow), false, transaction);
var parentCategoryIdCol = DotEntityDb.Provider.SafeEnclose(nameof(Category.ParentCategoryId));
var parentIdCol = DotEntityDb.Provider.SafeEnclose(nameof(Category.ParentId));
var categoryTable = DotEntityDb.GetTableNameForType <Category>();
Db.AddColumn <Category, int>(nameof(Category.ParentId), 0, transaction);
Db.Query($"UPDATE {categoryTable} SET {parentIdCol}={parentCategoryIdCol}", null, transaction);
Db.DropColumn <Category>(nameof(Category.ParentCategoryId), transaction);
Db.AddColumn <ContentPage, int>(nameof(ContentPage.ParentId), 0, transaction);
}
}
public Dictionary <OrderStatus, int> GetOrderCountsByStatus()
{
var tableName = DotEntityDb.GetTableNameForType <Order>();
var enclosedTableName = DotEntityDb.Provider.SafeEnclose(tableName);
var orderStatusCounts = new Dictionary <OrderStatus, int>();
foreach (OrderStatus status in System.Enum.GetValues(typeof(OrderStatus)))
{
orderStatusCounts.Add(status, 0);
}
using (var result = EntitySet.Query($"SELECT OrderStatus, COUNT(*) AS OrderCount FROM {enclosedTableName} GROUP BY OrderStatus", null))
{
var totals = result.SelectAllAs <OrderStatusTotal>().ToList();
foreach (var t in totals)
{
orderStatusCounts[t.OrderStatus] = t.OrderCount;
}
}
return(orderStatusCounts);
}
public static void AppendVersions(bool excludePlugins = false)
{
if (_versionsAdded)
{
return;
}
IDatabaseVersion[] appVersions =
{
new Version1(),
new Version1A(),
new Version1B(),
new Version1C(),
new Version1D(),
new Version1E(),
new Version1F()
};
DotEntityDb.EnqueueVersions(DatabaseContextKey, appVersions);
if (!excludePlugins)
{
//the plugin versions
var pluginInfos = PluginLoader.GetAvailablePlugins();
foreach (var pluginInfo in pluginInfos)
{
try
{
var versions = pluginInfo.LoadPluginInstance <IPlugin>().GetDatabaseVersions().ToArray();
if (versions.Any())
{
DotEntityDb.EnqueueVersions(pluginInfo.SystemName, versions);
}
}
catch
{
// ignored
}
}
}
_versionsAdded = true;
}
public void Init()
{
DotEntityDb.Initialize(MsSqlConnectionString,
new SqlServerDatabaseProvider());
var createProduct = @"CREATE TABLE Product
( Id INT NOT NULL IDENTITY(1,1),
ProductName NVARCHAR(MAX) NOT NULL,
ProductDescription NVARCHAR(MAX) NULL,
DateCreated DATETIME NOT NULL,
Price NUMERIC(18,0) NOT NULL,
IsActive BIT NOT NULL,
PRIMARY KEY CLUSTERED (Id ASC));";
EntitySet.Query(createProduct, null);
var createCategory = @"CREATE TABLE Category
( Id INT NOT NULL IDENTITY(1,1),
CategoryName NVARCHAR(MAX) NOT NULL,
CategoryType INT NOT NULL,
PRIMARY KEY CLUSTERED (Id ASC));";
EntitySet.Query(createCategory, null);
}
public static IDictionary <string, List <string> > GetInstalledVersions()
{
return(DotEntityDb.GetAllAppliedVersions());
}
public SqliteDatabaseProvider()
{
QueryGenerator = new SqliteQueryGenerator();
TypeMapProvider = new SqliteTypeMapProvider();
DotEntityDb.MapTableNameForType <SqliteMaster>("sqlite_master");
}
public static void UpgradeDatabase(string callingContextName, bool excludePlugins = false)
{
AppendVersions(excludePlugins);
DotEntityDb.UpdateDatabaseToLatestVersion(callingContextName);
}
public void Upgrade(IDotEntityTransaction transaction)
{
Db.AddColumn <OrderFulfillment, bool>(nameof(OrderFulfillment.Locked), true, transaction);
#region MultiStore and Catalog
Db.CreateTable <Store>(transaction);
Db.CreateTable <Catalog>(transaction);
Db.CreateTable <EntityStore>(transaction);
Db.CreateTable <ProductCatalog>(transaction);
Db.CreateTable <CatalogCountry>(transaction);
Db.CreateConstraint(Relation.Create <Store, EntityStore>("Id", "StoreId"), transaction, true);
Db.CreateConstraint(Relation.Create <Product, ProductCatalog>("Id", "ProductId"), transaction, true);
Db.CreateConstraint(Relation.Create <Catalog, ProductCatalog>("Id", "CatalogId"), transaction, true);
//run following only if it's not a fresh install
if (transaction.CurrentlyRanVersions.All(x => x.GetType() != typeof(Version1)))
{
//add a default store
var store = new Store()
{
Name = "Primary Store",
Live = true
};
EntitySet <Store> .Insert(store, transaction);
var storeId = 1; //this will be the store id for new store
//next create a default catalog
var catalog = new Catalog()
{
Name = "Primary Catalog",
IsCountrySpecific = false
};
EntitySet <Catalog> .Insert(catalog, transaction);
EntitySet <EntityStore> .Insert(new EntityStore()
{
EntityId = 1,
StoreId = 1,
EntityName = nameof(Catalog)
}, transaction);
Db.AddColumn <Setting, int>(nameof(Setting.StoreId), storeId, transaction);
Db.AddColumn <Order, int>(nameof(Order.StoreId), storeId, transaction);
//assign all products to this catalog
var productTableName = DotEntityDb.Provider.SafeEnclose(DotEntityDb.GetTableNameForType <Product>());
var productCatalogTableName = DotEntityDb.Provider.SafeEnclose(DotEntityDb.GetTableNameForType <ProductCatalog>());
var productIdCol = DotEntityDb.Provider.SafeEnclose(nameof(ProductCatalog.ProductId));
var catalogIdCol = DotEntityDb.Provider.SafeEnclose(nameof(ProductCatalog.CatalogId));
var idCol = DotEntityDb.Provider.SafeEnclose(nameof(ProductCatalog.Id));
var query =
$"INSERT INTO {productCatalogTableName}({productIdCol}, {catalogIdCol}) SELECT {idCol}, 1 FROM {productTableName}";
Db.Query(query, null, transaction);
//update active status of plugins
var pluginSetting = EntitySet <Setting> .Where(x =>
x.GroupName == nameof(PluginSettings) && x.Key == nameof(PluginSettings.SitePlugins)).Select().FirstOrDefault();
if (pluginSetting != null)
{
var pluginStatuses = JsonConvert.DeserializeObject <IList <PluginStatus> >(pluginSetting.Value);
foreach (var pluginStatus in pluginStatuses)
{
pluginStatus.ActiveStoreIds = pluginStatus.Active ? new List <int> {
store.Id
} : new List <int>();
}
pluginSetting.Value = JsonConvert.SerializeObject(pluginStatuses);
EntitySet <Setting> .Update(pluginSetting, transaction);
}
//update widgets
pluginSetting = EntitySet <Setting> .Where(x =>
x.GroupName == nameof(PluginSettings) && x.Key == nameof(PluginSettings.SiteWidgets)).Select().FirstOrDefault();
if (pluginSetting != null)
{
var widgetStatuses = JsonConvert.DeserializeObject <IList <WidgetStatus> >(pluginSetting.Value);
foreach (var ws in widgetStatuses)
{
ws.StoreId = storeId;
}
pluginSetting.Value = JsonConvert.SerializeObject(widgetStatuses);
EntitySet <Setting> .Update(pluginSetting, transaction);
}
//update store ids to 0 for plugin settings as it is now global from this version
EntitySet <Setting> .Update(new { storeId = 0 }, x => x.GroupName == nameof(PluginSettings), transaction);
//update menus
MoveEntityToStore <Menu>(storeId, transaction);
//update content pages
MoveEntityToStore <ContentPage>(storeId, transaction);
}
#endregion
}
public MySqlQueryGenerationTests()
{
DotEntityDb.Initialize(MySqlConnectionString, new MySqlDatabaseProvider("mytest"), SelectQueryMode.Wildcard);
generator = DotEntityDb.Provider.QueryGenerator;
}
public void Setup()
{
BaseSetup();
DotEntityDb.Initialize(ConnectionString, new SqlServerDatabaseProvider());
_queryCache = EntitySet.GetQueryCache();
}
public SqlServerQueryGenerationTests()
{
DotEntityDb.Initialize(MsSqlConnectionString, new SqlServerDatabaseProvider(), SelectQueryMode.Wildcard);
generator = DotEntityDb.Provider.QueryGenerator;
}
public SqliteQueryGenerationTests()
{
DotEntityDb.Initialize(SqliteConnectionString, new SqliteDatabaseProvider(), SelectQueryMode.Wildcard);
_generator = DotEntityDb.Provider.QueryGenerator;
}
public static void CleanupDatabase(string callingContextName)
{
AppendVersions();
DotEntityDb.UpdateDatabaseToVersion(callingContextName, null);
}