public void SqlSchemaWriter_ModifyNodeType()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt0 = CreateNodeType(ed, null, "NT0", "NT0C", 1);
NodeType nt1 = CreateNodeType(ed, nt0, "NT1", "NT1C", 2);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.ModifyNodeType(nt0, nt0.Parent, "NT0Cmod");
writer.ModifyNodeType(nt1, nt1.Parent, "NT1Cmod");
string expectedSql = @"
-- Modify NodeType: NT0 (original ClassName = 'NT0C')
UPDATE [dbo].[SchemaPropertySets] SET
[ParentId] = null
,[ClassName] = 'NT0Cmod'
WHERE PropertySetId = 1
GO
-- Modify NodeType: NT1 (original ClassName = 'NT1C')
UPDATE [dbo].[SchemaPropertySets] SET
[ParentId] = 1
,[ClassName] = 'NT1Cmod'
WHERE PropertySetId = 2
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_CreateNodeType_More()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0Class", 1);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.CreateNodeType(nt, "NT1", "NT1Class");
writer.CreateNodeType(nt, "NT2", "NT2Class");
string expectedSql = @"
-- Create NodeType NT0/NT1
DECLARE @parentId int
SELECT @parentId = [PropertySetId] FROM [dbo].[SchemaPropertySets] WHERE [Name] = 'NT0'
INSERT INTO [dbo].[SchemaPropertySets] ([ParentId], [Name], [PropertySetTypeId], [ClassName]) VALUES (@parentId, 'NT1', 1, 'NT1Class')
GO
-- Create NodeType NT0/NT2
DECLARE @parentId int
SELECT @parentId = [PropertySetId] FROM [dbo].[SchemaPropertySets] WHERE [Name] = 'NT0'
INSERT INTO [dbo].[SchemaPropertySets] ([ParentId], [Name], [PropertySetTypeId], [ClassName]) VALUES (@parentId, 'NT2', 1, 'NT2Class')
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_RemovePropertyTypeFromNodeType_String()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0Class", 1);
PropertyType pt = CreatePropertyType(ed, "PT0", DataType.String, 2);
ed.AddPropertyTypeToPropertySet(pt, nt);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.RemovePropertyTypeFromPropertySet(pt, nt);
string expectedSql = @"
-- Reset property values
UPDATE dbo.FlatProperties
SET nvarchar_1 = NULL
WHERE Id IN (SELECT dbo.FlatProperties.Id FROM dbo.Nodes
INNER JOIN dbo.Versions ON dbo.Versions.NodeId = dbo.Nodes.NodeId
INNER JOIN dbo.FlatProperties ON dbo.Versions.VersionId = dbo.FlatProperties.VersionId
WHERE (dbo.Nodes.NodeTypeId = 1) AND (dbo.FlatProperties.Page = 0))
-- Remove PropertyType 'PT0' from PropertySet 'NT0'
DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertyTypeId = 2 AND PropertySetId = 1
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_RemovePropertyTypeFromNodeType_Reference()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0Class", 1);
PropertyType pt = CreatePropertyType(ed, "PT0", DataType.Reference, 2);
ed.AddPropertyTypeToPropertySet(pt, nt);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.RemovePropertyTypeFromPropertySet(pt, nt);
string expectedSql = @"
-- Reset property value: NT0.PT0:Reference
DELETE FROM dbo.ReferenceProperties WHERE ReferencePropertyId IN (SELECT dbo.ReferenceProperties.ReferencePropertyId FROM dbo.Nodes
INNER JOIN dbo.Versions ON dbo.Versions.NodeId = dbo.Nodes.NodeId
INNER JOIN dbo.ReferenceProperties ON dbo.Versions.VersionId = dbo.ReferenceProperties.VersionId
WHERE (dbo.Nodes.NodeTypeId = 1) AND (dbo.ReferenceProperties.PropertyTypeId = 2))
-- Remove PropertyType 'PT0' from PropertySet 'NT0'
DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertyTypeId = 2 AND PropertySetId = 1
GO" ;
string sql = writer.GetSqlScript();
AssertScriptsAreEqual(expectedSql, sql);;
}
public void SqlSchemaWriter_RemovePropertyTypeFromNodeType_Text()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0Class", 1);
PropertyType pt = CreatePropertyType(ed, "PT0", DataType.Text, 2);
ed.AddPropertyTypeToPropertySet(pt, nt);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.RemovePropertyTypeFromPropertySet(pt, nt);
string expectedSql = @"-- Reset property value: NT0.PT0:Text
DELETE FROM dbo.TextPropertiesNVarchar WHERE TextPropertyNVarcharId IN (SELECT dbo.TextPropertiesNVarchar.TextPropertyNVarcharId FROM dbo.Nodes
INNER JOIN dbo.Versions ON dbo.Versions.NodeId = dbo.Nodes.NodeId
INNER JOIN dbo.TextPropertiesNVarchar ON dbo.Versions.VersionId = dbo.TextPropertiesNVarchar.VersionId
WHERE (dbo.Nodes.NodeTypeId = 1) AND (dbo.TextPropertiesNVarchar.PropertyTypeId = 2))
-- Reset property value: NT0.PT0:Text
DELETE FROM dbo.TextPropertiesNText WHERE TextPropertyNTextId IN (SELECT dbo.TextPropertiesNText.TextPropertyNTextId FROM dbo.Nodes
INNER JOIN dbo.Versions ON dbo.Versions.NodeId = dbo.Nodes.NodeId
INNER JOIN dbo.TextPropertiesNText ON dbo.Versions.VersionId = dbo.TextPropertiesNText.VersionId
WHERE (dbo.Nodes.NodeTypeId = 1) AND (dbo.TextPropertiesNText.PropertyTypeId = 2))
-- Remove PropertyType 'PT0' from PropertySet 'NT0'
DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertyTypeId = 2 AND PropertySetId = 1
GO
";
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_CreateContentListType()
{
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.CreateContentListType("LT1");
string expectedSql = @"
-- CreateContentListType: LT1
INSERT INTO [dbo].[SchemaPropertySets] ([Name], [PropertySetTypeId]) VALUES ('LT1', 2)
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_CreateRootNodeType_WithoutClassName()
{
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.CreateNodeType(null, "NT1", null);
string expectedSql = @"
-- Create NodeType without parent and handler: NT1
INSERT INTO [dbo].[SchemaPropertySets] ([Name], [PropertySetTypeId]) VALUES ('NT1', 1)
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_CreatePropertyType()
{
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.CreatePropertyType("PT1", DataType.String, 1, false);
string expectedSql = @"
-- Create PropertyType 'PT1', String
INSERT INTO [dbo].[SchemaPropertyTypes] ([Name], [DataTypeId], [Mapping], [IsContentListProperty]) VALUES ('PT1', 1, 1, 0)
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_CreatePermissionType()
{
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.CreatePermissionType("PERM1");
string expectedSql = @"
-- Create PermissionType 'PERM1'
INSERT INTO [dbo].[SchemaPermissionTypes] ([Name]) VALUES ('PERM1')
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_DeletePropertyType()
{
SchemaEditor ed = new SchemaEditor();
PropertyType pt = CreatePropertyType(ed, "PT0", DataType.String, 1);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.DeletePropertyType(pt);
string expectedSql = @"
-- Delete PropertyType 'PT0'
DELETE FROM [dbo].[SchemaPropertyTypes] WHERE PropertyTypeId = 1
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_DeletePermissionType()
{
SchemaEditor ed = new SchemaEditor();
PermissionType perm = CreatePermissionType(ed, "PERM0", 1);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.DeletePermissionType(perm);
string expectedSql = @"
-- Delete PermissionType: PERM0
DELETE FROM [dbo].[SchemaPermissionTypes] WHERE PermissionId = 1
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_DeleteContentListType()
{
SchemaEditor ed = new SchemaEditor();
var lt = CreateContentListType(ed, "LT0", 1);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.DeleteContentListType(lt);
string expectedSql = @"
-- Delete ContentListType 'LT0'
DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertySetId = 1
DELETE FROM [dbo].[SchemaPropertySets] WHERE PropertySetId = 1
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_DeleteNodeType()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0C", 1);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.DeleteNodeType(nt);
string expectedSql = @"
-- Delete NodeType 'NT0'
DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertySetId = 1
DELETE FROM [dbo].[SchemaPropertySets] WHERE PropertySetId = 1
GO" ;
string sql = writer.GetSqlScript();
AssertScriptsAreEqual(expectedSql, sql);;
}
public void SqlSchemaWriter_CreateRootNodeType_WithClassName()
{
SchemaEditor ed = new SchemaEditor();
CreateNodeType(ed, null, "NT0", "NT0Class", 1);
NodeType nt = ed.NodeTypes[0];
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.CreateNodeType(null, "NT1", "NT1Class");
string expectedSql = @"
-- Create NodeType without parent: NT1
INSERT INTO [dbo].[SchemaPropertySets] ([Name], [PropertySetTypeId], [ClassName]) VALUES ('NT1', 1, 'NT1Class')
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_CreateNodeType_WithoutClassName()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0Class", 1);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.CreateNodeType(nt, "NT1", null);
string expectedSql = @"
-- Create NodeType without handler: NT0/NT1
DECLARE @parentId int
SELECT @parentId = [PropertySetId] FROM [dbo].[SchemaPropertySets] WHERE [Name] = 'NT0'
INSERT INTO [dbo].[SchemaPropertySets] ([ParentId], [Name], [PropertySetTypeId]) VALUES (@parentId, 'NT1', 1)
GO" ;
string sql = writer.GetSqlScript();
AssertScriptsAreEqual(expectedSql, sql);;
}
public void SqlSchemaWriter_RemovePropertyTypeFromNodeType_String()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0Class", 1);
PropertyType pt = CreatePropertyType(ed, "PT0", DataType.String, 2);
ed.AddPropertyTypeToPropertySet(pt, nt);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.RemovePropertyTypeFromPropertySet(pt, nt);
string expectedSql = @"
-- Remove PropertyType 'PT0' from PropertySet 'NT0'
DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertyTypeId = 2 AND PropertySetId = 1
GO" ;
string sql = writer.GetSqlScript();
AssertScriptsAreEqual(expectedSql, sql);;
}
public void SqlSchemaWriter_DeleteNodeTypeWithProperties()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0C", 1);
PropertyType pt1 = CreatePropertyType(ed, "PT0", DataType.String, 2);
PropertyType pt2 = CreatePropertyType(ed, "PT1", DataType.String, 3);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.DeleteNodeType(nt);
string expectedSql = @"
-- Delete NodeType 'NT0'
DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertySetId = 1
DELETE FROM [dbo].[SchemaPropertySets] WHERE PropertySetId = 1
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_ModifyNodeType_NullClass()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0C", 1);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.ModifyNodeType(nt, nt.Parent, null);
string expectedSql = @"
-- Modify NodeType: NT0 (original ClassName = 'NT0C')
UPDATE [dbo].[SchemaPropertySets] SET
[ParentId] = null
,[ClassName] = null
WHERE PropertySetId = 1
GO" ;
string sql = writer.GetSqlScript();
AssertScriptsAreEqual(expectedSql, sql);;
}
public void SqlSchemaWriter_UpdatePropertyTypeDeclarationState()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0Class", 1);
PropertyType pt = CreatePropertyType(ed, "PT0", DataType.String, 2);
ed.AddPropertyTypeToPropertySet(pt, nt);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.UpdatePropertyTypeDeclarationState(pt, nt, true);
string expectedSql = @"
-- Update PropertyType declaration: NT0.PT0. Set IsDeclared = true
UPDATE [dbo].[SchemaPropertySetsPropertyTypes] SET
[IsDeclared] = 1
WHERE PropertySetId = 1 AND PropertyTypeId = 2
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_AddPropertyTypeToContentListType()
{
SchemaEditor ed = new SchemaEditor();
var lt = CreateContentListType(ed, "LT0", 1);
PropertyType pt = CreateContentListPropertyType(ed, DataType.String, 0, 2);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.AddPropertyTypeToPropertySet(pt, lt, false);
string expectedSql = @"
-- Add PropertyType to PropertySet (LT0.#String_0 : String)
DECLARE @propertyTypeId int
SELECT @propertyTypeId = [PropertyTypeId] FROM [dbo].[SchemaPropertyTypes] WHERE [Name] = '#String_0'
DECLARE @ownerId int
SELECT @ownerId = [PropertySetId] FROM [dbo].[SchemaPropertySets] WHERE [Name] = 'LT0'
INSERT INTO [dbo].[SchemaPropertySetsPropertyTypes] ([PropertyTypeId], [PropertySetId], [IsDeclared]) VALUES (@propertyTypeId, @ownerId, 0)
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_AddPropertyTypeToNodeType_Inherited()
{
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0Class", 1);
PropertyType pt = CreatePropertyType(ed, "PT0", DataType.String, 2);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.AddPropertyTypeToPropertySet(pt, nt, false);
string expectedSql = @"
-- Add PropertyType to PropertySet (NT0.PT0 : String)
DECLARE @propertyTypeId int
SELECT @propertyTypeId = [PropertyTypeId] FROM [dbo].[SchemaPropertyTypes] WHERE [Name] = 'PT0'
DECLARE @ownerId int
SELECT @ownerId = [PropertySetId] FROM [dbo].[SchemaPropertySets] WHERE [Name] = 'NT0'
INSERT INTO [dbo].[SchemaPropertySetsPropertyTypes] ([PropertyTypeId], [PropertySetId], [IsDeclared]) VALUES (@propertyTypeId, @ownerId, 0)
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
public void SqlSchemaWriter_RemovePropertyTypeFromNodeType_More()
{
//CreateTestNodeForRemovePropertyType();
SchemaEditor ed = new SchemaEditor();
NodeType nt = CreateNodeType(ed, null, "NT0", "NT0Class", 1);
PropertyType pt0 = CreatePropertyType(ed, "PT0", DataType.String, 2);
PropertyType pt1 = CreatePropertyType(ed, "PT1", DataType.String, 3);
PropertyType pt2 = CreatePropertyType(ed, "PT2", DataType.String, 4);
ed.AddPropertyTypeToPropertySet(pt0, nt);
ed.AddPropertyTypeToPropertySet(pt1, nt);
ed.AddPropertyTypeToPropertySet(pt2, nt);
var writer = SqlSchemaWriterAccessor.Create();
writer.Open();
writer.RemovePropertyTypeFromPropertySet(pt0, nt);
writer.RemovePropertyTypeFromPropertySet(pt1, nt);
writer.RemovePropertyTypeFromPropertySet(pt2, nt);
#region Bad script
//-- Reset property value: NT0.PT0:String
//UPDATE dbo.FlatProperties SET nvarchar_1 = NULL
//WHERE Id IN (SELECT dbo.FlatProperties.Id FROM dbo.Nodes
// INNER JOIN dbo.Versions ON dbo.Versions.NodeId = dbo.Nodes.NodeId
// INNER JOIN dbo.FlatProperties ON dbo.Versions.VersionId = dbo.FlatProperties.VersionId
// WHERE (dbo.Nodes.NodeTypeId = 1) AND (dbo.FlatProperties.Page = 0))
//-- Remove PropertyType 'PT0' from PropertySet 'NT0'
//DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertyTypeId = 2 AND PropertySetId = 1
//GO
//-- Reset property value: NT0.PT1:String
//UPDATE dbo.FlatProperties SET nvarchar_2 = NULL
//WHERE Id IN (SELECT dbo.FlatProperties.Id FROM dbo.Nodes
// INNER JOIN dbo.Versions ON dbo.Versions.NodeId = dbo.Nodes.NodeId
// INNER JOIN dbo.FlatProperties ON dbo.Versions.VersionId = dbo.FlatProperties.VersionId
// WHERE (dbo.Nodes.NodeTypeId = 1) AND (dbo.FlatProperties.Page = 0))
//-- Remove PropertyType 'PT1' from PropertySet 'NT0'
//DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertyTypeId = 3 AND PropertySetId = 1
//GO
//-- Reset property value: NT0.PT2:String
//UPDATE dbo.FlatProperties SET nvarchar_3 = NULL
//WHERE Id IN (SELECT dbo.FlatProperties.Id FROM dbo.Nodes
// INNER JOIN dbo.Versions ON dbo.Versions.NodeId = dbo.Nodes.NodeId
// INNER JOIN dbo.FlatProperties ON dbo.Versions.VersionId = dbo.FlatProperties.VersionId
// WHERE (dbo.Nodes.NodeTypeId = 1) AND (dbo.FlatProperties.Page = 0))
//-- Remove PropertyType 'PT2' from PropertySet 'NT0'
//DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertyTypeId = 4 AND PropertySetId = 1
//GO
#endregion
string expectedSql = @"
-- Reset property values
UPDATE dbo.FlatProperties
SET nvarchar_1 = NULL,
nvarchar_2 = NULL,
nvarchar_3 = NULL
WHERE Id IN (SELECT dbo.FlatProperties.Id FROM dbo.Nodes
INNER JOIN dbo.Versions ON dbo.Versions.NodeId = dbo.Nodes.NodeId
INNER JOIN dbo.FlatProperties ON dbo.Versions.VersionId = dbo.FlatProperties.VersionId
WHERE (dbo.Nodes.NodeTypeId = 1) AND (dbo.FlatProperties.Page = 0))
-- Remove PropertyType 'PT0' from PropertySet 'NT0'
DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertyTypeId = 2 AND PropertySetId = 1
GO
-- Remove PropertyType 'PT1' from PropertySet 'NT0'
DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertyTypeId = 3 AND PropertySetId = 1
GO
-- Remove PropertyType 'PT2' from PropertySet 'NT0'
DELETE FROM [dbo].[SchemaPropertySetsPropertyTypes] WHERE PropertyTypeId = 4 AND PropertySetId = 1
GO" ;
string sql = writer.GetSqlScript();
Assert.IsTrue(ScriptsAreEqual(sql, expectedSql));
}
