Surendra Sharma

Surendra Sharma

Search This Blog

Saturday, December 3, 2016

LexBlog To Sitecore - Part 2 - SQL Server database for LexBlog


This is second article in a series for LexBlog to Sitecore. You can refer first article for LexBlog basics from here.

This second article is about creating SQL server database for LexBlog, mapping details and required script.

We will migrate content from LexBlog XML file to Sitecore in two phases as 

Phase-I => From LexBlog XML to SQL Server
Phase-II => From SQL Server to Sitecore

lexblog to sitecore
LexBlog To Sitecore

From LexBlog XML to SQL Server


We can store this XML file to any database but in our case we have to store in SQL server. We need to create database for holding this information. 

Here is a schema diagram for holding these Lexblog XML file details as

Database Schema


Database Schema for LexBlog
Database Schema for LexBlog


Tables Details


Author – Store all authors of blog site
Category – Store all categories of this blog site
Item_Author – Store authors details mapping for particular articles
Item_Category – Store categories details mapping for particular articles
Item_Tag – Store tags mapping details for particular articles
MainItem – This is main table which store blog article related details
Tag – Store all tags for this blog site
Title_Description – Store only title and description of this blog site.

Scripts


We need tables and stored producers for holding LexBlog details. You can run below scripts for the same.

Scripts to create tables


/****** Object:  Table [dbo].[Author]    Script Date: 11/16/2016 2:49:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Author](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [author_id] [int] NULL,
       [author_login] [nvarchar](250) NULL,
       [author_email] [nvarchar](100) NULL,
       [author_display_name] [nvarchar](500) NULL,
       [author_first_name] [nvarchar](100) NULL,
       [author_last_name] [nvarchar](100) NULL,
       [blogId] [int] NULL,
 CONSTRAINT [PK_Author_1] 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]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Category]    Script Date: 11/16/2016 2:49:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [term_id] [int] NULL,
       [category_nicename] [nvarchar](200) NULL,
       [category_parent] [nvarchar](200) NULL,
       [cat_name] [nvarchar](200) NULL,
       [blogId] [int] NULL,
 CONSTRAINT [PK_Category] 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]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Item_Author]    Script Date: 11/16/2016 2:49:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Item_Author](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [post_id] [int] NULL,
       [author_id] [int] NULL,
       [niceName] [nvarchar](200) NULL,
       [blogId] [int] NULL,
 CONSTRAINT [PK_Item_Author] 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]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Item_Category]    Script Date: 11/16/2016 2:49:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Item_Category](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [post_id] [int] NULL,
       [category_id] [int] NULL,
       [nicename] [nvarchar](200) NULL,
       [blogId] [int] NULL,
 CONSTRAINT [PK_Item_Category] 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]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Item_Tag]    Script Date: 11/16/2016 2:49:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Item_Tag](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [post_id] [int] NULL,
       [tag_id] [int] NULL,
       [nicename] [nvarchar](200) NULL,
       [blogId] [int] NULL,
 CONSTRAINT [PK_Item_Tag] 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]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[MainItem]    Script Date: 11/16/2016 2:49:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MainItem](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [title] [nvarchar](200) NULL,
       [post_id] [int] NULL,
       [post_date] [datetime] NULL,
       [main_content] [nvarchar](max) NULL,
       [blogId] [int] NULL,
       [post_status] [nvarchar](200) NULL,
       [post_link] [nvarchar](400) NULL,
       [post_name] [nvarchar](200) NULL,
 CONSTRAINT [PK_MainItem] 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]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Tag]    Script Date: 11/16/2016 2:49:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tag](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [term_id] [int] NULL,
       [tag_slug] [nvarchar](200) NULL,
       [tag_name] [nvarchar](200) NULL,
       [blogId] [int] NULL,
 CONSTRAINT [PK_Tag] 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]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Title_Description]    Script Date: 11/16/2016 2:49:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Title_Description](
       [Id] [int] IDENTITY(1,1) NOT NULL,
       [title] [nvarchar](200) NULL,
       [description] [nvarchar](500) NULL,
 CONSTRAINT [PK_Title_Description] 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]
) ON [PRIMARY]

GO

Scripts for creating stored procedure


Run below script to create required stored procedures in LexBlog database.

/****** Object:  StoredProcedure [dbo].[ItemAuthorInsert]    Script Date: 11/16/2016 2:50:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ItemAuthorInsert]
       -- Add the parameters for the stored procedure here
       @Category nvarchar(200),
       @post_id int,
       @nicename nvarchar(200),
       @blogId int
AS
BEGIN

 declare @author_Id int
 Select @author_Id = author_id from Author where author_login = @Category
 Insert into Item_Author(post_id, author_id, niceName, blogId) values(@post_id, @author_Id, @nicename, @blogId)

END


GO
/****** Object:  StoredProcedure [dbo].[ItemCategoryInsert]    Script Date: 11/16/2016 2:50:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ItemCategoryInsert]
       -- Add the parameters for the stored procedure here
       @Category nvarchar(200),
       @post_id int,
       @nicename nvarchar(200),
       @blogId int
AS
BEGIN

 declare @category_Id int
 Select @category_Id = term_id from Category where category_nicename = @Category
 Insert into Item_Category(post_id, category_id, nicename, blogId) values(@post_id, @category_Id, @nicename, @blogId)

END


GO
/****** Object:  StoredProcedure [dbo].[ItemTagInsert]    Script Date: 11/16/2016 2:50:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ItemTagInsert]
       -- Add the parameters for the stored procedure here
       @Category nvarchar(200),
       @post_id int,
       @nicename nvarchar(200),
       @blogId int
AS
BEGIN

 declare @tag_Id int
 Select @tag_Id = term_id from Tag where tag_name = @Category
 Insert into Item_Tag(post_id, tag_Id, nicename, blogId) values(@post_id, @tag_Id, @nicename, @blogId)

END


GO
/****** Object:  StoredProcedure [dbo].[SP_AuthorsAdd]    Script Date: 11/16/2016 2:50:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_AuthorsAdd]
       -- Add the parameters for the stored procedure here
       @author_id int,
       @author_login nvarchar(250),
       @author_email nvarchar(100),
       @author_display_name nvarchar(500),
       @author_first_name nvarchar(100),
       @author_last_name nvarchar(100),
       @blogId       int
AS
BEGIN
       Insert INTO Author (author_id,author_login,author_email,author_display_name,author_first_name,author_last_name, blogId) VALUES (@author_id,@author_login,@author_email,@author_display_name,@author_first_name,@author_last_name, @blogId)
END


GO
/****** Object:  StoredProcedure [dbo].[SP_CategoryAdd]    Script Date: 11/16/2016 2:50:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_CategoryAdd]
       -- Add the parameters for the stored procedure here
       @term_id int,
       @category_nicename nvarchar(200),
       @category_parent nvarchar(200),
       @cat_name nvarchar(200),
       @blogId int
AS
BEGIN
       Insert INTO Category (term_id,category_nicename,category_parent,cat_name, blogId) VALUES (@term_id,@category_nicename,@category_parent,@cat_name, @blogId)
END


GO
/****** Object:  StoredProcedure [dbo].[SP_MainItemAdd]    Script Date: 11/16/2016 2:50:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_MainItemAdd]
       -- Add the parameters for the stored procedure here
       @title nvarchar(200),
       @post_id int,
       @post_date datetime,
       @main_content nvarchar(max),
       @blogId int,
       @post_name  nvarchar(200),
       @post_status nvarchar(200),
       @post_link nvarchar(400)
AS
BEGIN
       Insert INTO MainItem (title,post_id,post_date,main_content, blogId, post_name, post_status, post_link) VALUES (@title,@post_id,@post_date,@main_content, @blogId, @post_name, @post_status, @post_link)
END


GO
/****** Object:  StoredProcedure [dbo].[SP_TagAdd]    Script Date: 11/16/2016 2:50:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_TagAdd]
       -- Add the parameters for the stored procedure here
       @term_id int,
       @tag_slug nvarchar(200),
       @tag_name nvarchar(200),
       @blogId int
AS
BEGIN
       Insert INTO Tag (term_id,tag_slug,tag_name,blogId) VALUES (@term_id,@tag_slug,@tag_name,@blogId)
END


GO
/****** Object:  StoredProcedure [dbo].[SP_TitleDescInsert]    Script Date: 11/16/2016 2:50:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_TitleDescInsert]
       -- Add the parameters for the stored procedure here
       @title nvarchar(200),
       @description nvarchar(500),
       @new_identity INT OUTPUT
AS
BEGIN

 Insert into Title_Description(title, description) values (@title, @description)
 SELECT @new_identity = SCOPE_IDENTITY()
 SELECT @new_identity AS blogId
 return

END


GO



  

XML and SQL Mapping Details


In previous sections, we walk through with XML file, its different sections along with database schema and tables. 

Let see how to map XML files fields with database tables.

Basic details are mapped as

LexBlog Basic Details Mapping with SQL Server
LexBlog Basic Details Mapping with SQL Server


Authors are mapped as

LexBlog Authors Mapping with SQL Server
LexBlog Authors Mapping with SQL Server


Categories are mapped as

LexBlog Categories Mapping with SQL Server
LexBlog categories Mapping with SQL Server


Tags are mapped as

LexBlog Tags Mapping with SQL Server
LexBlog Tags Mapping with SQL Server


Blog items contains article description along with its associated tags, categories and authors. So this is the main table of this LexBlog migration process. So here is mapping of this XML section with SQL tables as

LexBlog Items Mapping with SQL Server
LexBlog Items Mapping with SQL Server



That’s it for the part II. In next part I, will walk through with our strategy for moving this LexBlog Site into SQL Server and Sitecore.

I hope you like this Sitecore lesson. Stay tuned for more Sitecore related articles.

Till that happy sitecoring :)

Please leave your comments or share this article if it’s useful for you.