[Jboss-svn-commits] JBL Code SVN: r1041 - trunk/forge/portal-extensions/forge-forums/scripts

[<jbo...@li...>]

Author: adamw
Date: 2005-09-03 06:02:18 -0400 (Sat, 03 Sep 2005)
New Revision: 1041

Added:
   trunk/forge/portal-extensions/forge-forums/scripts/fix.sql
Modified:
   trunk/forge/portal-extensions/forge-forums/scripts/all.sql
   trunk/forge/portal-extensions/forge-forums/scripts/conv.sql
Log:
Imporved scripts with null handling and poll import


Modified: trunk/forge/portal-extensions/forge-forums/scripts/all.sql
===================================================================
--- trunk/forge/portal-extensions/forge-forums/scripts/all.sql	2005-09-02 16:19:50 UTC (rev 1040)
+++ trunk/forge/portal-extensions/forge-forums/scripts/all.sql	2005-09-03 10:02:18 UTC (rev 1041)
@@ -4,7 +4,8 @@
 \. portal-pure.sql
 \. conv.sql
 \. portal-keys.sql
+\. fix.sql
 
--- AFTER ADDING THE KEYS: UPDATING POST COUNT
--- UPDATE jbp_forums_posters posters  SET posters.jbp_post_count = (SELECT COUNT(posts.jbp_id) 
--- FROM jbp_forums_posts posts WHERE posts.jbp_poster_id = posters.jbp_id);
+-- TODO:
+-- - migrate topics watch
+-- - lost fields - what's with them?

Modified: trunk/forge/portal-extensions/forge-forums/scripts/conv.sql
===================================================================
--- trunk/forge/portal-extensions/forge-forums/scripts/conv.sql	2005-09-02 16:19:50 UTC (rev 1040)
+++ trunk/forge/portal-extensions/forge-forums/scripts/conv.sql	2005-09-03 10:02:18 UTC (rev 1041)
@@ -63,16 +63,28 @@
 		n.post_time, n.post_subject, n.post_text, n.enable_html,
 		n.poster_id FROM phpbb_posts n;
 
--- Hibernate doesn't like null-like dates.
-UPDATE jbp_forums_posts SET jbp_create_date='1970-01-01 00:00:00' WHERE jbp_create_date='0000-00-00 00:00:00';
-UPDATE jbp_forums_posts SET jbp_edit_date='1970-01-01 00:00:00' WHERE jbp_edit_date='0000-00-00 00:00:00';
+-- Copying pools
+INSERT INTO jbp_forums_polls (jbp_poll_id, jbp_title, jbp_length, jbp_creation_date)
+	SELECT n.vote_id, n.vote_text, n.vote_length, date(concat(n.vote_start, '-01-01'))
+		FROM phpbb_vote_desc n;
 
+UPDATE jbp_forums_topics SET jbp_poll = 
+	(SELECT n.vote_id FROM phpbb_vote_desc n WHERE n.topic_id = jbp_id)
+	WHERE (SELECT COUNT(*) FROM phpbb_vote_desc n WHERE n.topic_id = jbp_id) = 1;
+
 -- Copying pool options
+INSERT INTO jbp_forums_poll_option (jbp_poll_id, jbp_votes, jbp_question, 
+		jbp_poll_option_position)
+	SELECT n.vote_desc_id, n.vote_result, n.vote_option_text,
+		(SELECT COUNT(jbp_poll_id) FROM jbp_forums_poll_option WHERE 
+			jbp_poll_id = n.vote_desc_id)
+		FROM phpbb_vote_results n;
 
 -- Copying pool votes
+INSERT INTO jbp_forums_poll_voted (jbp_poll_id, jbp_poll_voted)
+	SELECT n.vote_desc_id, n.vote_user_id
+		FROM phpbb_vote_voters n;
 
--- Copying pools
-
 -- Updating the "admin" account to be the same as on labs.
 UPDATE jbp_users SET jbp_password = "0E6C6B302E803D8F03C2906211D39486" WHERE jbp_uname = "admin";
 

Added: trunk/forge/portal-extensions/forge-forums/scripts/fix.sql
===================================================================
--- trunk/forge/portal-extensions/forge-forums/scripts/fix.sql	2005-09-02 16:19:50 UTC (rev 1040)
+++ trunk/forge/portal-extensions/forge-forums/scripts/fix.sql	2005-09-03 10:02:18 UTC (rev 1041)
@@ -0,0 +1,47 @@
+-- Hibernate doesn't like null-like dates.
+UPDATE jbp_forums_posts SET jbp_create_date='1970-01-01 00:00:00' WHERE jbp_create_date='0000-00-00 00:00:00';
+UPDATE jbp_forums_posts SET jbp_edit_date='1970-01-01 00:00:00' WHERE jbp_edit_date='0000-00-00 00:00:00';
+
+-- Portal doesn't like "null" here.
+UPDATE jbp_forums_forums SET jbp_prune_next=0 WHERE jbp_prune_next IS NULL;
+
+-- Deleting forums with no category.
+DELETE FROM jbp_forums_forums WHERE jbp_category_id IS NULL;
+
+-- Deleting posts with no topic.
+DELETE FROM jbp_forums_posts WHERE jbp_topic_id IS NULL;
+
+-- Inserting a "dummy" user which will be the poster of posts, topics with no poster.
+-- Nobody can log in as that user because his password is illegal - not a md5 hash.
+INSERT INTO jbp_users (jbp_uname, jbp_password, jbp_regdate, jbp_realemail, jbp_fakeemail, 
+	jbp_viewrealemail, jbp_enabled) VALUES
+	('Past user', 'A text that is not an md5 hash.', now(), 'no-email', 'no-email',
+		0, 1);
+
+SET @dummy_user_id = (SELECT u.jbp_uid FROM jbp_users u WHERE u.jbp_uname = 'Past user');
+
+INSERT INTO jbp_forums_posters (jbp_id, jbp_user_id, jbp_post_count)
+	VALUES (@dummy_user_id, @dummy_user_id, 0);
+
+INSERT INTO jbp_role_membership (jbp_rid, jbp_uid) VALUES
+	(2, @dummy_user_id);
+
+-- Updating posts that have been posted by a null user to be posted by the dummy user just
+-- created.
+UPDATE jbp_forums_posts SET jbp_poster_id = @dummy_user_id WHERE jbp_poster_id IS NULL;
+
+-- Updating topics without a first post so they have the youngest post as a first post.
+UPDATE jbp_forums_topics t SET t.jbp_first_post_id = 
+	(SELECT MIN(p.jbp_create_date) FROM jbp_forums_posts p WHERE p.jbp_topic_id = t.jbp_id)
+	WHERE t.jbp_first_post_id IS NULL;
+
+-- Deleting the topics without a first posts which haven't been successfully updated.
+DELETE FROM jbp_forums_topics WHERE jbp_first_post_id IS NULL;
+
+-- Updating topics that have been created by a null user to be created by the dummy user.
+UPDATE jbp_forums_topics SET jbp_poster = @dummy_user_id WHERE jbp_poster IS NULL;
+
+-- Updating post count
+UPDATE jbp_forums_posters posters  SET posters.jbp_post_count = (SELECT COUNT(posts.jbp_id) 
+FROM jbp_forums_posts posts WHERE posts.jbp_poster_id = posters.jbp_id);
+