<commit_before>/*
This script imports data from a .TEXT 0.95 database 
into the Subtext database.
*/<commit_msg>Initial creation of the dotText095, all-or-nothing, import script.<commit_after>/*
This script imports data from a .TEXT 0.95 database 
into the Subtext database.

This script is written with the following assumptions:
    1) it is being run from the dotText schema (so be sure to connect to it)
    2) both DBs are on the same server and have the same user/pwd.
    3) i think there was something else?

TODOs:
	1) figure out how to take advantage of 2 seperate DB connections
		possibly by using the USE <databaseName> keyword?
	2) clean up this UGLY SQL and format it for readability.
	3) I'm sure there's a lot more to be done...

DECLARE @user_name varchar(30)
SELECT @user_name = user_name()

*/

-- subtext_Config
SET IDENTITY_INSERT SubtextData.dbo.subtext_Config ON
INSERT INTO SubtextData.dbo.subtext_Config 
( BlogID, UserName, Password, Email, Title, SubTitle, Skin, Application, Host, Author,
	TimeZone, IsActive, Language, ItemCount, LastUpdated, News, SecondaryCss, PostCount, 
	StoryCount, PingTrackCount, CommentCount, IsAggregated, Flag, SkinCssFile, 
	BlogGroup, LicenseUrl, DaysTillCommentsClose, CommentDelayInMinutes )
	SELECT 
		BlogID, UserName, Password, Email, Title, SubTitle, Skin, Application, Host, Author,
		TimeZone, IsActive, Language, ItemCount, LastUpdated, News, SecondaryCss, PostCount, 
		StoryCount, PingTrackCount, CommentCount, IsAggregated, Flag, SkinCssFile, BlogGroup, null, null, null
	FROM blog_Config
	WHERE 1=1
SET IDENTITY_INSERT SubtextData.dbo.subtext_Config OFF
GO

-- subtext_Content
SET IDENTITY_INSERT SubtextData.dbo.subtext_Content ON
INSERT INTO SubtextData.dbo.subtext_Content 
( ID, Title, DateAdded, SourceUrl, PostType, Author, Email, SourceName, BlogID, Description,
	DateUpdated, TitleUrl, Text, ParentID, FeedBackCount, PostConfig, EntryName, 
	ContentChecksumHash, DateSyndicated )
	SELECT 
		ID, Title, DateAdded, SourceUrl, PostType, Author, Email, SourceName, BlogID, Description,
		DateUpdated, TitleUrl, Text, ParentID, FeedBackCount, PostConfig, EntryName, null, null 
	FROM blog_Content
	WHERE 1=1
SET IDENTITY_INSERT SubtextData.dbo.subtext_Content OFF
GO

UPDATE SubtextData.dbo.subtext_Content
SET ParentID = NULL WHERE ParentID = -1
GO

UPDATE SubtextData.dbo.subtext_Content 
SET DateSyndicated = DateUpdated
-- Post is syndicated and active
WHERE PostConfig & 16 = 16 AND PostConfig & 1 = 1
GO

/*	Still need to update the ContentChecksumHash column
	for all of the imported Subtext records		*/

-- subtext_EntryViewCount
INSERT INTO SubtextData.dbo.subtext_EntryViewCount 
( EntryID, BlogID, WebCount, AggCount, WebLastUpdated, AggLastUpdated )
    SELECT 
        EntryID, BlogID, WebCount, AggCount, WebLastUpdated, AggLastUpdated
    FROM blog_EntryViewCount
    WHERE 1=1
GO

-- subtext_LinkCategories
SET IDENTITY_INSERT SubtextData.dbo.subtext_LinkCategories  ON
INSERT INTO SubtextData.dbo.subtext_LinkCategories 
( CategoryID, Title, Active, BlogID, CategoryType, Description )
    SELECT 
		CategoryID, Title, Active, BlogID, CategoryType, Description
    FROM blog_LinkCategories
SET IDENTITY_INSERT SubtextData.dbo.subtext_LinkCategories OFF
GO

-- subtext_KeyWords
SET IDENTITY_INSERT SubtextData.dbo.subtext_KeyWords ON
INSERT INTO SubtextData.dbo.subtext_KeyWords 
( KeyWordID, Word, Text, ReplaceFirstTimeOnly, OpenInNewWindow, Url, Title, BlogID, CaseSensitive )
    SELECT 
        KeyWordID, Word, Text, ReplaceFirstTimeOnly, OpenInNewWindow, Url, Title, BlogID, CaseSensitive
    FROM blog_KeyWords
SET IDENTITY_INSERT SubtextData.dbo.subtext_KeyWords OFF
GO

-- subtext_Images
/*
	Had to put brackets [ ] around the column name File b/c
	it is a SQL Server KEYWORD.  Seems to work OK this way tho.
*/
SET IDENTITY_INSERT SubtextData.dbo.subtext_Images ON
INSERT INTO SubtextData.dbo.subtext_Images 
( ImageID, Title, CategoryID, Width, Height, [File], Active, BlogID )
    SELECT
        ImageID, Title, CategoryID, Width, Height, [File], Active, BlogID
    FROM blog_Images
SET IDENTITY_INSERT SubtextData.dbo.subtext_Images OFF
GO

-- subtext_Links
/*
	Due to the FK constraint subtext_Links.PostID --> subtext_Content.ID,
	we have to first import all records w/ PostID <> -1, and then import
	the PostID == -1 records, but we fill these values w/ NULLs
*/
SET IDENTITY_INSERT SubtextData.dbo.subtext_Links ON
INSERT INTO SubtextData.dbo.subtext_Links 
( LinkID, Title, Url, Rss, Active, CategoryID, BlogID, PostID, NewWindow )
    SELECT
      LinkID, Title, Url, Rss, Active, CategoryID, BlogID, PostID, NewWindow  
    FROM blog_Links
    WHERE blog_Links.PostID <> -1

-- now to take care of the "-1" issue!
INSERT INTO SubtextData.dbo.subtext_Links 
( LinkID, Title, Url, Rss, Active, CategoryID, BlogID, PostID, NewWindow )
    SELECT
      LinkID, Title, Url, Rss, Active, CategoryID, BlogID, null, NewWindow  
    FROM blog_Links
    WHERE blog_Links.PostID = -1
SET IDENTITY_INSERT SubtextData.dbo.subtext_Links OFF
GO

-- subtext_URLs
SET IDENTITY_INSERT SubtextData.dbo.subtext_URLs ON
INSERT INTO SubtextData.dbo.subtext_URLs 
( UrlID, Url )
    SELECT
      UrlID, Url 
    FROM blog_URLs
SET IDENTITY_INSERT SubtextData.dbo.subtext_URLs OFF
GO

-- subtext_Referrals
/*
	Due to the stranded Referral records that we've seen coming from
	dotText095 db's, we need the extra WHERE clause below. This will
	prevent any Refferral records that have a bad UrlID from breaking 
	the FK constraint to the URLs table.
*/
INSERT INTO SubtextData.dbo.subtext_Referrals 
( EntryID, BlogID, UrlID, [Count], LastUpdated )
    SELECT
        EntryID, BlogID, UrlID, [Count], LastUpdated
    FROM blog_Referrals WHERE UrlID IN (SELECT UrlID FROM blog_URLs)
GO

--  DONE
{'fraction_non_alphanumeric': 0.07025436926803945, 'fraction_numerical': 0.006748572417373248, 'mean_word_length': 4.606207565470417, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1092153', 'n_tokens_mistral': 1955, 'n_tokens_neox': 1833, 'n_words': 626}