private void SetupSqlServer(ILogger logger)
{
//create the db
_dbSqlServer = new UmbracoDatabase(
"server=.\\SQLExpress;database=YOURDB;user id=YOURUSER;password=YOURPASS",
Constants.DatabaseProviders.SqlServer,
logger);
//drop the table
// note: DROP TABLE IF EXISTS is SQL 2016+
_dbSqlServer.Execute("IF OBJECT_ID('dbo.umbracoServer', 'U') IS NOT NULL DROP TABLE [umbracoServer]");
//re-create it
_dbSqlServer.Execute(@"CREATE TABLE [umbracoServer](
[id] [int] IDENTITY(1,1) NOT NULL,
[address] [nvarchar](500) NOT NULL,
[computerName] [nvarchar](255) NOT NULL,
[registeredDate] [datetime] NOT NULL CONSTRAINT [DF_umbracoServer_registeredDate] DEFAULT (getdate()),
[lastNotifiedDate] [datetime] NOT NULL,
[isActive] [bit] NOT NULL,
[isMaster] [bit] NOT NULL,
CONSTRAINT [PK_umbracoServer] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)");
}
/// <summary>
/// Update in the CompanyInvetoryAuditdb table the number of records uploaded
/// </summary>
/// <param name="auditId">The id of the audit record to update</param>
/// <param name="recordsCount">The number of items uploaded throught the file import</param>
public bool UpdateCompanyInvetoryAuditItem(int auditId, int recordsCount)
{
var sqlQuery = string.Format("UPDATE CompanyInvetoryAudit SET RecordsCount={0} WHERE Id ={1}", recordsCount, auditId);
try
{
db.Execute(new Sql(sqlQuery));
}
catch (Exception ex)
{
return(false);
}
return(true);
}
public void Can_Bulk_Insert_Native_Sql_Server_Bulk_Inserts()
{
//create the db
var dbSqlServer = new UmbracoDatabase(
"server=.\\SQLExpress;database=YOURDB;user id=YOURUSER;password=YOURPASSWORD",
Constants.DatabaseProviders.SqlServer,
new DebugDiagnosticsLogger());
//drop the table
dbSqlServer.Execute("DROP TABLE [umbracoServer]");
//re-create it
dbSqlServer.Execute(@"CREATE TABLE [umbracoServer](
[id] [int] IDENTITY(1,1) NOT NULL,
[address] [nvarchar](500) NOT NULL,
[computerName] [nvarchar](255) NOT NULL,
[registeredDate] [datetime] NOT NULL CONSTRAINT [DF_umbracoServer_registeredDate] DEFAULT (getdate()),
[lastNotifiedDate] [datetime] NOT NULL,
[isActive] [bit] NOT NULL,
[isMaster] [bit] NOT NULL,
CONSTRAINT [PK_umbracoServer] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)");
var data = new List <ServerRegistrationDto>();
for (var i = 0; i < 1000; i++)
{
data.Add(new ServerRegistrationDto
{
ServerAddress = "address" + i,
ServerIdentity = "computer" + i,
DateRegistered = DateTime.Now,
IsActive = true,
DateAccessed = DateTime.Now
});
}
var sqlServerSyntax = new SqlServerSyntaxProvider();
using (var tr = dbSqlServer.GetTransaction())
{
dbSqlServer.BulkInsertRecords(data, tr, sqlServerSyntax, useNativeSqlPlatformBulkInsert: true);
tr.Complete();
}
// Assert
Assert.That(dbSqlServer.ExecuteScalar <int>("SELECT COUNT(*) FROM umbracoServer"), Is.EqualTo(1000));
}
/// <summary>
/// Import the settings export model back into the database. This is destructive.
/// </summary>
/// <param name="model">Object of type <see cref="WorkflowSettingsExport"/></param>
public void ImportSettings(WorkflowSettingsExport model)
{
// delete first as this is an import and should be destructive
_database.Execute("DELETE FROM WorkflowSettings");
_database.Insert(new WorkflowSettingsPoco
{
DefaultApprover = model.DefaultApprover,
EditUrl = model.EditUrl,
Email = model.Email,
ExcludeNodes = model.ExcludeNodes,
FlowType = model.FlowType,
SendNotifications = model.SendNotifications,
SiteUrl = model.SiteUrl
});
}
public IHttpActionResult SaveDocTypeConfig(Dictionary <int, List <UserGroupPermissionsPoco> > model)
{
try
{
if (null != model)
{
UmbracoDatabase db = DatabaseContext.Database;
// set defaults for doctype - delete all previous if any model data exists
db.Execute("DELETE FROM WorkflowUserGroupPermissions WHERE ContentTypeId != 0");
if (model.Any())
{
foreach (KeyValuePair <int, List <UserGroupPermissionsPoco> > permission in model)
{
if (permission.Value.Any())
{
db.BulkInsertRecords(permission.Value, DatabaseContext.SqlSyntax);
}
}
}
}
}
catch (Exception ex)
{
string msg = $"Error saving config. {ex.Message}";
return(Content(HttpStatusCode.InternalServerError, ViewHelpers.ApiException(ex, msg)));
}
return(Ok());
}
public void LeakTest()
{
_database = DatabaseContext.Database; // creates a database
_database.Execute("CREATE TABLE foo (id INT)"); // opens a connection
Assert.IsNull(_database.Connection); // is immediately closed
_database.BeginTransaction(); // opens and maintains a connection
// the test is leaking a scope with a non-null database
var contextGuid = CallContext.LogicalGetData(ScopeProvider.ScopeItemKey).AsGuid();
Assert.AreNotEqual(Guid.Empty, contextGuid);
// only if Core.DEBUG_SCOPES are defined
//var contextScope = DatabaseContext.ScopeProvider.CallContextObjects[contextGuid] as NoScope;
//Assert.IsNotNull(contextScope);
//Assert.IsNotNull(contextScope.DatabaseOrNull);
//Assert.AreSame(_database, contextScope.DatabaseOrNull);
// save the connection
_connection = _database.Connection;
Assert.IsInstanceOf <StackExchange.Profiling.Data.ProfiledDbConnection>(_connection);
_connection = ((StackExchange.Profiling.Data.ProfiledDbConnection)_connection).InnerConnection;
// the connection is open
Assert.IsNotNull(_connection);
Assert.AreEqual(ConnectionState.Open, _connection.State);
}
public IHttpActionResult SaveConfig(Dictionary <int, List <UserGroupPermissionsPoco> > model)
{
try
{
UmbracoDatabase db = DatabaseContext.Database;
if (null != model && model.Any())
{
KeyValuePair <int, List <UserGroupPermissionsPoco> > permission = model.First();
db.Execute("DELETE FROM WorkflowUserGroupPermissions WHERE NodeId = @0", permission.Key);
if (permission.Value.Any())
{
db.BulkInsertRecords(permission.Value, DatabaseContext.SqlSyntax);
}
}
}
catch (Exception ex)
{
string msg = $"Error saving config. {ex.Message}";
return(Content(HttpStatusCode.InternalServerError, ViewHelpers.ApiException(ex, msg)));
}
return(Ok());
}
public void SetPermissions(SetAccountPermissionsRequest accountPermissions)
{
var entries = new List <AccountSettings>();
foreach (var permission in accountPermissions.Permissions)
{
var accountSettingEntry = new AccountSettings();
// Always default to empty for niw
accountSettingEntry.Notes = "";
// Default to read until we have functionality for other permissions
accountSettingEntry.Permission = Permissions.Read; // TODO: functionality for Write permissions
accountSettingEntry.AccountId = accountPermissions.AccountId;
accountSettingEntry.PropertyTypeAlias = permission.PropertyAlias;
accountSettingEntry.DocTypeAlias = permission.DoctypeAlias;
accountSettingEntry.IsBuiltInProperty = permission.IsBuiltInProperty;
accountSettingEntry.CreatedOn = DateTime.UtcNow;
accountSettingEntry.UpdatedOn = DateTime.UtcNow;
entries.Add(accountSettingEntry);
}
// Clear existing settings
var sql = new Sql("DELETE FROM GraphQL_AccountSettings WHERE AccountId=@0", accountPermissions.AccountId);
_database.Execute(sql);
// Insert in bulk so it's handled within a single query
_database.BulkInsertRecords(entries, ApplicationContext.Current.DatabaseContext.SqlSyntax);
}
private void RemoveMigrationFromDb(UmbracoDatabase db)
{
using (Transaction transaction = db.GetTransaction())
{
db.Execute("delete from umbracoMigration where version='{0}'", Constants.Version);
transaction.Complete();
}
}
public virtual void Initialize()
{
// Disable medium trust
var transform = TransformWebConfig("Release");
var assemblyPath = TestHelper.CurrentAssemblyDirectory;
assemblyPath = Path.Combine(assemblyPath, @"..\..\..\Umbraco.Web.UI\");
var webUiPath = Path.GetFullPath(new Uri(assemblyPath).LocalPath);
var installedPackagesConfig = string.Format("{0}App_Data\\packages\\installed\\installedPackages.config", webUiPath);
if (File.Exists(installedPackagesConfig))
{
File.Delete(installedPackagesConfig);
}
var databaseDataPath = string.Format(@"{0}\App_Data\Umbraco.sdf", webUiPath);
var connectionString = string.Format(@"Data Source={0}", databaseDataPath);
//Create the Sql CE database
using (var engine = new SqlCeEngine(connectionString))
{
if (File.Exists(databaseDataPath) == false)
{
engine.CreateDatabase();
}
}
var syntaxProvider = new SqlCeSyntaxProvider();
SqlSyntaxContext.SqlSyntaxProvider = syntaxProvider;
_database = new UmbracoDatabase(connectionString, Constants.DatabaseProviders.SqlCe, Mock.Of <ILogger>());
// First remove anything in the database
var creation = new DatabaseSchemaCreation(_database, Mock.Of <ILogger>(), syntaxProvider);
creation.UninstallDatabaseSchema();
// Then populate it with fresh data
_database.CreateDatabaseSchema(false);
_database.Execute("UPDATE umbracoUser SET userName = '******', userPassword = '******', userEmail = 'none' WHERE id = 0"); // password: test
// Recycle app pool so the new user can log in
//var webConfigFilePath = string.Format(@"{0}\web.config", webUiPath);
//File.SetLastWriteTime(webConfigFilePath, DateTime.Now);
// Disable medium trust
transform = TransformWebConfig("Release");
Driver = new FirefoxDriver();
BaseUrl = "http://localhost:61639/";
_verificationErrors = new StringBuilder();
}
private static void Upgrade00to01(UmbracoDatabase db)
{
//TODO: Add new columns to the ContactMessage table
//TODO: Add a new table called ContactorDbVersion
//Create a record in ContactorDbVersion and make it version 1
db.Execute(
"ALTER TABLE ContactMessage ADD " +
"PhoneNumber NVARCHAR(255) NULL, " +
"WebsiteUrl NVARCHAR(255) NULL, " +
"CompanyName NVARCHAR(255) NULL, " +
"Location NVARCHAR(255) NULL");
db.Execute(
"CREATE TABLE uContactorVersion (DbVersion INT)");
db.Execute(
"INSERT INTO uContactorVersion values(1)");
}
public void AddApplication(LatchApplication latchApplication)
{
using (var scope = db.GetTransaction())
{
var deleteQuery = Sql.Builder
.Append("DELETE FROM LatchApplication");
db.Execute(deleteQuery);
Insert(latchApplication);
scope.Complete();
}
}
/// <summary>
/// Removes the nodes from database.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="nodesToRemove">The nodes to remove.</param>
private void RemoveNodesFromDB(UmbracoDatabase db, IList <int> nodesToRemove)
{
// It has been removed from the content tree, remove every occurence from the db
if (nodesToRemove.Count > 0)
{
foreach (int nodeToRemove in nodesToRemove)
{
string delQuery = string.Format("DELETE FROM {0} WHERE nodeId=@0", Settings.APPROVE_IT_CHANGE_HISTORY_TABLE);
db.Execute(delQuery, nodeToRemove);
}
}
}
private static void MigrateDataIdsToUdis(UmbracoDatabase database)
{
string sql = @"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, cmsDocument.*
FROM cmsPropertyData
JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN umbracoNode ON umbracoNode.id = cmsContentVersion.ContentId
JOIN cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Umbraco.ContentPicker2')
AND (dataNvarchar IS NOT NULL OR dataInt IS NOT NULL)
AND (cmsDocument.published=1 OR cmsDocument.newest=1 OR cmsDocument.updateDate > (SELECT updateDate FROM cmsDocument AS innerDoc WHERE innerDoc.nodeId = cmsDocument.nodeId AND innerDoc.published=1 AND newest=1))
ORDER BY contentNodeId, cmsDataType.propertyEditorAlias";
var contentPickerDataToMigrate = database.Query <Row>(sql).ToList();
if (contentPickerDataToMigrate.Any())
{
foreach (var propertyData in contentPickerDataToMigrate)
{
int[] ids;
if (propertyData.dataInt != null)
{
ids = new[] { propertyData.dataInt.Value };
}
else if (propertyData.dataNvarchar != null && !propertyData.dataNvarchar.StartsWith("umb://"))
{
ids = propertyData.dataNvarchar.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToArray();
}
else
{
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias}");
continue;
}
string csv = string.Join(",", ids);
Guid[] uniqueIds = null;
string uniqueIdsCsv = string.Empty;
if (ids.Any())
{
uniqueIds = database.Query <Guid>($"SELECT uniqueId FROM umbracoNode WHERE id IN ({csv})").ToArray();
uniqueIdsCsv = string.Join(",", uniqueIds.Select(id => $"umb://document/{id:N}"));
}
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {csv} to {uniqueIdsCsv}");
database.Execute("UPDATE cmsPropertyData SET dataInt=NULL, dataNvarchar=@0 WHERE id=@1", uniqueIdsCsv, propertyData.id);
}
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis: migrated Umbraco.ContentPicker2 datatypes.");
}
}
private static void MigrateVortoDataIdsToUdis(UmbracoDatabase database)
{
string sql = @"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, cmsDocument.*
FROM cmsPropertyData
JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'dataType'
JOIN cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN umbracoNode ON umbracoNode.id = cmsContentVersion.ContentId
JOIN cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Our.Umbraco.Vorto')
AND cmsDataTypePreValues.value LIKE '%""propertyEditorAlias"": ""Umbraco.MultiUrlPicker""%'
AND (dataNtext IS NOT NULL) AND (dataNtext LIKE '%\""id\"":%')
AND(cmsDocument.published = 1 OR cmsDocument.newest = 1 OR cmsDocument.updateDate > (SELECT updateDate FROM cmsDocument AS innerDoc WHERE innerDoc.nodeId = cmsDocument.nodeId AND innerDoc.published = 1 AND newest = 1))
ORDER BY contentNodeId, cmsDataType.propertyEditorAlias";
var vortoUrlPickerDataToMigrate = database.Query <Row>(sql).ToList();
if (vortoUrlPickerDataToMigrate.Any())
{
foreach (var propertyData in vortoUrlPickerDataToMigrate)
{
string udiValue;
if (!string.IsNullOrEmpty(propertyData.dataNtext))
{
var vortoValue = JsonConvert.DeserializeObject <Our.Umbraco.Vorto.Models.VortoValue>(propertyData.dataNtext);
var udiValues = new Dictionary <string, object>();
foreach (var value in vortoValue.Values)
{
var valueMultiUrls = BuildMultiUrlLinks(value.Value.ToString());
udiValues[value.Key] = ToDataValue(valueMultiUrls);
}
vortoValue.Values = udiValues;
udiValue = ToDataValue(vortoValue);
}
else
{
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataNtext");
continue;
}
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {propertyData.dataNtext} to {udiValue}");
database.Execute("UPDATE cmsPropertyData SET dataNtext=@0 WHERE id=@1", udiValue, propertyData.id);
}
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis: migrated Our.Umbraco.Vorto datatypes containing MultiUrlPicker.");
}
}
public ActionResult DeleteFormSubmissionRecord(int submissionID)
{
bool success = true;
using (var scope = DatabaseConnection.GetTransaction())
{
try
{
DatabaseConnection.Execute("DELETE FROM FormStorageSubmissions WHERE submissionID=@0", submissionID);
}
catch (Exception ex)
{
Log.Error("Unable to delete from FormStorageSubmissions table : " + ex.Message);
success = false;
}
if (success)
{
try
{
DatabaseConnection.Execute("DELETE FROM FormStorageEntries WHERE submissionID=@0", submissionID);
}
catch (Exception ex)
{
Log.Error("Unable to delete from FormStorageEntries table : " + ex.Message);
success = false;
}
}
scope.Complete();
}
var result = new
{
success = success,
};
JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
string resultJSON = javaScriptSerializer.Serialize(result);
return(Content(resultJSON, "application/json"));
}
//[System.Web.Http.HttpPost]
public void SaveFile()
{
var myContext = Request.TryGetHttpContext();
List <string> keys = new List <string>();
if (myContext.Success)
{
HttpPostedFileBase myFile = myContext.Result.Request.Files["file"];
if (myFile == null)
{
throw new HttpException("invalid file");
}
else
{
StreamReader csvreader = new StreamReader(myFile.InputStream);
while (!csvreader.EndOfStream)
{
var line = csvreader.ReadLine();
if (line != "Key")
{
keys.Add(line);
}
}
}
UmbracoDatabase db = ApplicationContext.DatabaseContext.Database;
var remove = new Sql("DELETE FROM cmsDictionary");
int rem = db.Execute(remove);
foreach (string item in keys)
{
var insert = new Sql("INSERT INTO cmsDictionary VALUES (NEWID(), null,'" + item + "')");
int res = db.Execute(insert);
}
}
}
/// <summary>
/// Save the blob back to the database
/// </summary>
/// <param name="model"></param>
public void SavePromos(IEnumerable <PromoModel> model)
{
_database.Execute("DELETE FROM PromoteModules");
// stringify the json to store a simple blob
List <PromoPoco> toSave = model.Select((p, i) => new PromoPoco
{
Promo = JsonConvert.SerializeObject(p)
}).ToList();
foreach (PromoPoco m in toSave)
{
_database.Insert(m);
}
}
public void SaveUserLogins(int memberId, IEnumerable <UserLoginInfo> logins)
{
using (var t = _db.GetTransaction())
{
//clear out logins for member
_db.Execute("DELETE FROM ExternalLogins WHERE UserId=@userId", new { userId = memberId });
//add them all
foreach (var l in logins)
{
_db.Insert(new ExternalLoginDto
{
LoginProvider = l.LoginProvider,
ProviderKey = l.ProviderKey,
UserId = memberId
});
}
t.Complete();
}
}
private static void MigrateDataIdsToUdis(UmbracoDatabase database)
{
string sql = @"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, cmsDocument.*
FROM cmsPropertyData
JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN umbracoNode ON umbracoNode.id = cmsContentVersion.ContentId
JOIN cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Umbraco.MultiUrlPicker')
AND (dataNtext IS NOT NULL) AND (dataNtext LIKE '%""id"":%')
AND (cmsDocument.published=1 OR cmsDocument.newest=1 OR cmsDocument.updateDate > (SELECT updateDate FROM cmsDocument AS innerDoc WHERE innerDoc.nodeId = cmsDocument.nodeId AND innerDoc.published=1 AND newest=1))
ORDER BY contentNodeId, cmsDataType.propertyEditorAlias";
var urlPickerDataToMigrate = database.Query <Row>(sql).ToList();
if (urlPickerDataToMigrate.Any())
{
foreach (var propertyData in urlPickerDataToMigrate)
{
IEnumerable <Umbraco.Web.Models.Link> multiUrls;
if (!string.IsNullOrEmpty(propertyData.dataNtext))
{
multiUrls = BuildMultiUrlLinks(propertyData.dataNtext);
}
else
{
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataNtext");
continue;
}
var linksValue = ToDataValue(multiUrls);
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {propertyData.dataNtext} to {linksValue}");
database.Execute("UPDATE cmsPropertyData SET dataNtext=@0 WHERE id=@1", linksValue, propertyData.id);
}
LogHelper.Info(typeof(MultiUrlPickerIdToUdiMigrator), () => $"MigrateIdsToUdis: migrated Umbraco.MultiUrlPicker datatypes.");
}
}
public void EditOrder(Order order)
{
var orderPoco = _orderMapper.MapToPoco(order);
try
{
_db.BeginTransaction();
_db.Execute("DELETE FROM OrderedMeals Where OrderId = @0", orderPoco.Id);
foreach (var orderedMealPoco in orderPoco.OrderedMeals)
{
orderedMealPoco.OrderId = orderPoco.Id;
_db.Insert("OrderedMeals", "Id", orderedMealPoco);
}
_db.Update("Orders", "Id", orderPoco);
_db.CompleteTransaction();
}
catch (Exception)
{
_db.AbortTransaction();
throw;
}
}
private static void CreateDataBase(UmbracoDatabase db)
{
if (!db.TableExist("ContactMessage"))
{
db.CreateTable <ContactPoco>(false);
}
if (!db.TableExist("ContactSettings"))
{
db.CreateTable <ContactSettings>(false);
var settingsTwo = new List <ContactSettings>()
{
new ContactSettings()
{
ConfigName = "PageSize",
ConfigValue = "10",
ConfigText = "Page Size",
ConfigHelper = "",
ConfigSort = 9
},
new ContactSettings()
{
ConfigName = "AutoReplyMessage",
ConfigValue = "Thanks for contacting us",
ConfigText = "Auto Reply Message",
ConfigHelper = "",
ConfigSort = 3
},
new ContactSettings()
{
ConfigName = "TemplateNode",
ConfigValue = "",
ConfigText = "Auto Reply Template",
ConfigHelper = "",
ConfigSort = 4
},
new ContactSettings()
{
ConfigName = "SendNotificationTo",
ConfigValue = "",
ConfigText = "Send Notification To",
ConfigHelper = "*Use commas to include multiple email",
ConfigSort = 7
},
new ContactSettings()
{
ConfigName = "NotificationMessage",
ConfigValue = "You have new message from %name%",
ConfigText = "Notification Message",
ConfigHelper = "",
ConfigSort = 6
},
new ContactSettings()
{
ConfigName = "AutoReplySubject",
ConfigValue = "Thanks for contacting us %name%",
ConfigText = "Auto Reply Subject",
ConfigHelper = "",
ConfigSort = 2
},
new ContactSettings()
{
ConfigName = "NotificationSubject",
ConfigValue = "New message from %name%",
ConfigText = "Notification Subject",
ConfigHelper = "",
ConfigSort = 5
},
new ContactSettings()
{
ConfigName = "NotificationTemplateNode",
ConfigValue = "",
ConfigText = "Notification Template",
ConfigHelper = "",
ConfigSort = 8
},
new ContactSettings()
{
ConfigName = "SenderEmail",
ConfigValue = "*****@*****.**",
ConfigText = "Sender Email",
ConfigHelper = "",
ConfigSort = 0
},
new ContactSettings()
{
ConfigName = "DisplayNameSender",
ConfigValue = "Noreply You Website",
ConfigText = "Display Name Sender",
ConfigHelper = "",
ConfigSort = 1
}
};
db.BulkInsertRecords(settingsTwo);
db.Execute(
"CREATE TABLE uContactorVersion (DbVersion INT)");
db.Execute(
"INSERT INTO uContactorVersion values(1)");
}
}
private static void MigrateVortoArchetypeDataIdsToUdis(UmbracoDatabase database)
{
// Find content picker datatypes
string contentPickerSql = @"SELECT umbracoNode.uniqueID
FROM cmsDataType
JOIN umbracoNode ON umbracoNode.id = cmsDataType.nodeId
WHERE cmsDataType.propertyEditorAlias = 'Umbraco.ContentPicker2'";
var contentPickers = database.Query <Guid>(contentPickerSql).ToList();
foreach (var contentPicker in contentPickers)
{
// Find archetypes using content pickers
string archetypeSql = $@"SELECT umbracoNode.uniqueID, cmsDataTypePreValues.value
FROM cmsPropertyType
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'archetypeConfig'
JOIN umbracoNode ON umbracoNode.id = cmsPropertyType.dataTypeId
WHERE cmsDataType.propertyEditorAlias IN ('Imulus.Archetype')
AND cmsDataTypePreValues.value LIKE '%""dataTypeGuid"": ""{contentPicker}""%'";
var archetypes = database.Query <ArchetypeConfigRow>(archetypeSql).ToList();
foreach (var archetype in archetypes)
{
// Find Vorto properties of archetypes using content pickers
var sql = $@"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, cmsDocument.*
FROM cmsPropertyData
JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'dataType'
JOIN cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN umbracoNode ON umbracoNode.id = cmsContentVersion.ContentId
JOIN cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Our.Umbraco.Vorto')
AND cmsDataTypePreValues.value LIKE '%""propertyEditorAlias"": ""Imulus.Archetype""%'
AND cmsDataTypePreValues.value LIKE '%""guid"": ""{archetype.UniqueID}""%'
AND(dataNtext IS NOT NULL)
AND(cmsDocument.published = 1 OR cmsDocument.newest = 1 OR cmsDocument.updateDate > (SELECT updateDate FROM cmsDocument AS innerDoc WHERE innerDoc.nodeId = cmsDocument.nodeId AND innerDoc.published = 1 AND newest = 1))
ORDER BY contentNodeId, cmsDataType.propertyEditorAlias";
var vortoDataToMigrate = database.Query <Row>(sql).ToList();
var config = JsonConvert.DeserializeObject <Archetype.Models.ArchetypePreValue>(archetype.Value);
var propertyAliases = config.Fieldsets.SelectMany(fieldset => fieldset.Properties)
.Where(property => property.DataTypeGuid == contentPicker)
.Select(property => property.Alias);
if (vortoDataToMigrate.Any())
{
foreach (var propertyData in vortoDataToMigrate)
{
string udiValue;
if (!string.IsNullOrEmpty(propertyData.dataNtext))
{
// Vorto multilingual values
var vortoValue = JsonConvert.DeserializeObject <Our.Umbraco.Vorto.Models.VortoValue>(propertyData.dataNtext);
var udiValues = new Dictionary <string, object>();
foreach (var value in vortoValue.Values)
{
// Archetype fieldsets
var archetypeValue = JsonConvert.DeserializeObject <Archetype.Models.ArchetypeModel>(value.Value.ToString());
var fieldsets = archetypeValue.Fieldsets
.Where(fieldset => fieldset.Properties.Any(property =>
propertyAliases.Contains(property.Alias)));
foreach (var fieldset in fieldsets)
{
// Properties using content picker
var properties = fieldset.Properties.Where(property =>
propertyAliases.Contains(property.Alias));
foreach (var property in properties)
{
var intValue = property.GetValue <int>();
if (intValue > 0)
{
var uniqueId = database.FirstOrDefault <Guid>(
$"SELECT uniqueId FROM umbracoNode WHERE id = @0", intValue);
property.Value = $"umb://document/{uniqueId:N}";
}
else
{
property.Value = null;
}
}
}
udiValues[value.Key] = ToDataValue(archetypeValue);
}
vortoValue.Values = udiValues;
udiValue = ToDataValue(vortoValue);
}
else
{
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataNtext");
continue;
}
LogHelper.Info(typeof(ContentPickerIdToUdiMigrator), () => $"MigrateIdsToUdis (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {propertyData.dataNtext} to {udiValue}");
database.Execute("UPDATE cmsPropertyData SET dataNtext=@0 WHERE id=@1", udiValue, propertyData.id);
}
}
}
}
}
public void Update(TState entity)
{
var query = new Sql($"UPDATE TinifierState SET CurrentImage = {entity.CurrentImage}, AmounthOfImages = {entity.AmounthOfImages}, Status = {entity.Status} WHERE Id = {entity.Id}");
_database.Execute(query);
}
/// <summary>
/// Removes all users from the given group
/// </summary>
/// <param name="groupId">The group id</param>
public void DeleteUsersFromGroup(int groupId)
{
_database.Execute("DELETE FROM WorkflowUser2UserGroup WHERE GroupId = @0", groupId);
}
/// <summary>
/// Update State
/// </summary>
/// <param name="entity">TState</param>
public void Update(TState entity)
{
var query = new Sql("UPDATE TinifierState SET CurrentImage = @0, AmounthOfImages = @1, Status = @2 WHERE Id = @3", entity.CurrentImage, entity.AmounthOfImages, entity.Status, entity.Id);
_database.Execute(query);
}
private static void MigrateNestedContentDataIdsToUdis(UmbracoDatabase database)
{
//SELECT documents using media picker datatypes
var docTypeSql = @"SELECT cmsContentType.alias as docTypeAlias, cmsPropertyType.alias as propertyAlias FROM cmsDataType
JOIN cmsPropertyType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsContentType ON cmsContentType.nodeId = cmsPropertyType.contentTypeId
WHERE cmsDataType.propertyEditorAlias = 'Umbraco.MediaPicker2'";
var docTypes = database.Query <DoctypePropertyRow>(docTypeSql).ToList();
foreach (var docType in docTypes)
{
// SELECT NestedContent using selected doctypes by using their doctype alias an property alias
var sql = $@"SELECT cmsPropertyData.id, cmsPropertyData.contentNodeId, cmsPropertyType.alias, dataNvarchar, dataNtext, dataInt, cmsDocument.* --, cmsDataTypePreValues.*
FROM cmsPropertyData
JOIN cmsPropertyType ON cmsPropertyType.id = cmsPropertyData.propertytypeid
JOIN cmsDataType ON cmsDataType.nodeId = cmsPropertyType.dataTypeId
JOIN cmsDataTypePreValues ON cmsDataTypePreValues.datatypeNodeId = cmsDataType.nodeId AND cmsDataTypePreValues.alias = 'contentTypes'
JOIN cmsContentVersion ON cmsContentVersion.VersionId = cmsPropertyData.versionId
JOIN umbracoNode ON umbracoNode.id = cmsContentVersion.ContentId
JOIN cmsDocument ON cmsDocument.nodeId = umbracoNode.id
WHERE cmsDataType.propertyEditorAlias IN ('Umbraco.NestedContent', 'Our.Umbraco.NestedContent')
AND cmsDataTypePreValues.value LIKE '%""ncAlias"": ""{docType.DocTypeAlias}""%'
AND(dataNtext IS NOT NULL AND dataNtext LIKE '%""{docType.PropertyAlias}"":""%')
AND(cmsDocument.published = 1 OR cmsDocument.newest = 1 OR cmsDocument.updateDate > (SELECT updateDate FROM cmsDocument AS innerDoc WHERE innerDoc.nodeId = cmsDocument.nodeId AND innerDoc.published = 1 AND newest = 1))
ORDER BY contentNodeId, cmsDataType.propertyEditorAlias";
var dataToMigrate = database.Query <Row>(sql).ToList();
foreach (var propertyData in dataToMigrate)
{
if (string.IsNullOrEmpty(propertyData.dataNtext))
{
LogHelper.Info(typeof(MediaPickerIdToUdiMigrator), () => $"MigrateKeys (node id: {propertyData.contentNodeId}) skipping property {propertyData.alias} - null dataNtext");
continue;
}
var ncArray = JArray.Parse(propertyData.dataNtext);
var udiArray = new JArray();
foreach (var ncToken in ncArray)
{
var nc = (JObject)ncToken;
if (nc.HasValue(docType.PropertyAlias))
{
var ncValue = nc.Value <string>(docType.PropertyAlias);
var ids = ncValue.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Where(x => !x.StartsWith("umb://")).Select(int.Parse).ToArray();
string csv = string.Join(",", ids);
Guid[] uniqueIds = null;
string uniqueIdsCsv = string.Empty;
if (ids.Any())
{
uniqueIds = database.Query <Guid>($"SELECT uniqueId FROM umbracoNode WHERE id IN ({csv})").ToArray();
uniqueIdsCsv = string.Join(",", uniqueIds.Select(id => $"umb://media/{id:N}"));
}
nc[docType.PropertyAlias] = uniqueIdsCsv;
}
udiArray.Add(nc);
}
var udiNestedContent = JsonConvert.SerializeObject(udiArray);
LogHelper.Info(typeof(MediaPickerIdToUdiMigrator), () => $"MigrateKeys (node id: {propertyData.contentNodeId}) converting property {propertyData.alias} from {propertyData.dataNtext} to {udiNestedContent}");
database.Execute("UPDATE cmsPropertyData SET dataNtext=@0 WHERE id=@1", udiNestedContent, propertyData.id);
}
}
}
public void Update(TImageStatistic entity)
{
var query = new Sql($"UPDATE TinifierImagesStatistic SET NumberOfOptimizedImages = {entity.NumberOfOptimizedImages}, TotalNumberOfImages = {entity.TotalNumberOfImages}");
_database.Execute(query);
}
/// <summary>
/// Update Statistic
/// </summary>
/// <param name="entity">TImageStatistic</param>
public void Update(TImageStatistic entity)
{
var query = new Sql("UPDATE TinifierImagesStatistic SET NumberOfOptimizedImages = @0, TotalNumberOfImages = @1, TotalSavedBytes = @2", entity.NumberOfOptimizedImages, entity.TotalNumberOfImages, entity.TotalSavedBytes);
_database.Execute(query);
}
/// <summary>
/// Update currentMonthRequests in settings
/// </summary>
/// <param name="currentMonthRequests">currentMonthRequests</param>
public void Update(int currentMonthRequests)
{
var query = new Sql("UPDATE TinifierUserSettings SET CurrentMonthRequests = @0", currentMonthRequests);
_database.Execute(query);
}