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); }