--
-- copy-acs-tables.sql
--
-- by randyg@arsdigita.com in July 1999 for HP MiniPress
-- commented and edited by philg@mit.edu on October 30, 1999

-- warning: this script is not complete; you have to think about which
-- ACS tables and columns you're actually using.  Use this script as a model.

-- assume that the online site has a username of "minipress".


insert into users (USER_ID,FIRST_NAMES,LAST_NAME,PRIV_NAME,EMAIL,PRIV_EMAIL,EMAIL_BOUNCING_P,CONVERTED_P,PASSWORD,URL,ON_VACATION_UNTIL,LAST_VISIT,SECOND_TO_LAST_VISIT,N_SESSIONS,REGISTRATION_DATE,REGISTRATION_IP,APPROVING_USER,BANNING_USER,BANNING_NOTE) 
(select USER_ID,FIRST_NAMES,LAST_NAME,PRIV_NAME,EMAIL,PRIV_EMAIL,EMAIL_BOUNCING_P,CONVERTED_P,PASSWORD,URL,ON_VACATION_UNTIL,LAST_VISIT,SECOND_TO_LAST_VISIT,N_SESSIONS,REGISTRATION_DATE,REGISTRATION_IP,APPROVING_USER,BANNING_USER,BANNING_NOTE 
from minipress.users where user_id > 2);

- this will need to be modified to not try to 
-overwrite existing group types
insert into user_group_types (
GROUP_TYPE,				    
PRETTY_NAME,				    
PRETTY_PLURAL,				    
APPROVAL_POLICY,			    
DEFAULT_NEW_MEMBER_POLICY		    
) (select 
GROUP_TYPE,				    
PRETTY_NAME,				    
PRETTY_PLURAL,				    
APPROVAL_POLICY,			    
DEFAULT_NEW_MEMBER_POLICY		    
from minipress.user_group_types
where not group_type = 'administration'
);


- this will also need to be modified to only import non-common groups
insert into user_groups (
GROUP_ID,				    
GROUP_TYPE,				    
GROUP_NAME,					    
REGISTRATION_DATE,			    
CREATION_USER,				    
CREATION_IP_ADDRESS,			    
APPROVED_P,					    
ACTIVE_P,
EXISTENCE_PUBLIC_P,				    
NEW_MEMBER_POLICY,			    
EMAIL_ALERT_P,					    
MULTI_ROLE_P,					    
GROUP_ADMIN_PERMISSIONS_P)
(select GROUP_ID,				    
GROUP_TYPE,				    
GROUP_NAME,					    
REGISTRATION_DATE,			    
CREATION_USER,				    
CREATION_IP_ADDRESS,			    
APPROVED_P,					    
ACTIVE_P,					    
EXISTENCE_PUBLIC_P,				    
NEW_MEMBER_POLICY,			    
EMAIL_ALERT_P,					    
MULTI_ROLE_P,					    
GROUP_ADMIN_PERMISSIONS_P			    
from minipress.user_groups
where group_id > 2);



insert into user_group_type_fields (
GROUP_TYPE,				    
COLUMN_NAME,				    
PRETTY_NAME,				    
COLUMN_TYPE,				    
COLUMN_ACTUAL_TYPE,			    
COLUMN_EXTRA					    
)
( select GROUP_TYPE,				    
COLUMN_NAME,				    
PRETTY_NAME,				    
COLUMN_TYPE,				    
COLUMN_ACTUAL_TYPE,			    
COLUMN_EXTRA					    
from minipress.user_group_type_fields);


insert into user_group_map_queue (
GROUP_ID,				    
USER_ID,			    
IP_ADDRESS,					    
QUEUE_DATE)
(select GROUP_ID,				    
USER_ID,			    
IP_ADDRESS,					    
QUEUE_DATE 				    
from minipress.user_group_map_queue);
 

insert into user_group_roles (
GROUP_ID,				    
ROLE,					    
CREATION_DATE,				    
CREATION_USER,				    
CREATION_IP_ADDRESS)			    
(select GROUP_ID,				    
ROLE,					    
CREATION_DATE,				    
CREATION_USER,				    
CREATION_IP_ADDRESS			    
from minipress.user_group_roles);
 

insert into user_group_actions ( 					       
GROUP_ID,				    
ACTION,				    
CREATION_DATE,				    
CREATION_USER,				    
CREATION_IP_ADDRESS)			    
(select GROUP_ID,				    
ACTION,				    
CREATION_DATE,				    
CREATION_USER,				    
CREATION_IP_ADDRESS
from minipress.user_group_actions);



insert into user_group_action_role_map (
GROUP_ID,				    
ROLE,					    
ACTION,				    
CREATION_DATE,				    
CREATION_USER,				    
CREATION_IP_ADDRESS)
(select GROUP_ID,				    
ROLE,					    
ACTION,				    
CREATION_DATE,				    
CREATION_USER,				    
CREATION_IP_ADDRESS			    
from minipress.user_group_action_role_map);




insert into user_group_map (
GROUP_ID,				    
USER_ID,			    
ROLE,						    
REGISTRATION_DATE,
MAPPING_USER,				    
MAPPING_IP_ADDRESS)			    
(select GROUP_ID,				    
USER_ID,			    
ROLE,						    
REGISTRATION_DATE,			    
MAPPING_USER,				    
MAPPING_IP_ADDRESS 			    
from minipress.user_group_map);



insert into administration_info (
GROUP_ID,
MODULE,
SUBMODULE,
URL)
(select GROUP_ID,
MODULE,
SUBMODULE,
URL
from minipress.administration_info where group_id > 3);


---------------------------------
--- bboard stuff

insert into bboard_topics (
TOPIC,					    
BACKLINK,					    
BACKLINK_TITLE,				    
BLATHER,				    
RESTRICTED_P,					    
NS_PERM_GROUP,					    
NS_PERM_GROUP_ADDED_FOR_FORUM,			    
PRIMARY_MAINTAINER_ID,			    
SUBJECT_LINE_SUFFIX,				    
NOTIFY_OF_NEW_POSTINGS_P,			    
PRE_POST_CAVEAT,			    
MODERATION_POLICY,				    
RESTRICT_TO_WORKGROUP_P,		    
USERS_CAN_INITIATE_THREADS_P,			    
POLICY_STATEMENT,				    
PRESENTATION_TYPE,				    
Q_AND_A_SORT_ORDER,			    
Q_AND_A_CATEGORIZED_P,				    
Q_AND_A_NEW_DAYS,				    
Q_AND_A_SOLICIT_CATEGORY_P,			    
Q_AND_A_CATS_USER_EXTENSIBLE_P,		    
Q_AND_A_USE_INTEREST_LEVEL_P,			    
Q_AND_A_SHOW_CATS_ONLY_P,			    
CUSTOM_SORT_KEY_P,				    
CUSTOM_SORT_KEY_NAME,				    
CUSTOM_SORT_KEY_TYPE,				    
CUSTOM_SORT_ORDER,			    
CUSTOM_SORT_NOT_FOUND_TEXT,			    
CUSTOM_SORT_SOLICIT_P,				    
CUSTOM_SORT_SOLICIT_PRETTY_P,			    
CUSTOM_SORT_PRETTY_NAME,		    
CUSTOM_SORT_PRETTY_EXPLANATION,		    
CUSTOM_SORT_VALIDATION_CODE,			    
CATEGORY_CENTRIC_P,				    
UPLOADS_ANTICIPATED,				    
ACTIVE_P)					    
(select TOPIC,					    
BACKLINK,					    
BACKLINK_TITLE,				    
BLATHER,				    
RESTRICTED_P,					    
NS_PERM_GROUP,					    
NS_PERM_GROUP_ADDED_FOR_FORUM,			    
PRIMARY_MAINTAINER_ID,			    
SUBJECT_LINE_SUFFIX,				    
NOTIFY_OF_NEW_POSTINGS_P,			    
PRE_POST_CAVEAT,			    
MODERATION_POLICY,				    
RESTRICT_TO_WORKGROUP_P,		    
USERS_CAN_INITIATE_THREADS_P,			    
POLICY_STATEMENT,				    
PRESENTATION_TYPE,				    
Q_AND_A_SORT_ORDER,			    
Q_AND_A_CATEGORIZED_P,				    
Q_AND_A_NEW_DAYS,				    
Q_AND_A_SOLICIT_CATEGORY_P,			    
Q_AND_A_CATS_USER_EXTENSIBLE_P,		    
Q_AND_A_USE_INTEREST_LEVEL_P,			    
Q_AND_A_SHOW_CATS_ONLY_P,			    
CUSTOM_SORT_KEY_P,				    
CUSTOM_SORT_KEY_NAME,				    
CUSTOM_SORT_KEY_TYPE,				    
CUSTOM_SORT_ORDER,			    
CUSTOM_SORT_NOT_FOUND_TEXT,			    
CUSTOM_SORT_SOLICIT_P,				    
CUSTOM_SORT_SOLICIT_PRETTY_P,			    
CUSTOM_SORT_PRETTY_NAME,		    
CUSTOM_SORT_PRETTY_EXPLANATION,		    
CUSTOM_SORT_VALIDATION_CODE,			    
CATEGORY_CENTRIC_P,				    
UPLOADS_ANTICIPATED,			    
ACTIVE_P					    
from minipress.bboard_topics);
 
insert into bboard_authorized_maintainers ( 					   
TOPIC,					    
USER_ID)
(select topic, user_id from minipress.bboard_authorized_maintainers);
 					       
insert into bboard_workgroup (
USER_ID,				    
GROUP_ID,					    
TOPIC)
(select user_id, group_id, topic from minipress.bboar_workgroup);


insert into bboard_q_and_a_categories (
TOPIC,					    
CATEGORY				    
) (select topic, category from minipress.bboard_q_and_a_categories);
 					       
insert into bboard_bozo_patterns  (
TOPIC,					    
THE_REGEXP,				    
SCOPE,						    
MESSAGE_TO_USER,			    
CREATION_DATE,				    
CREATION_USER,				    
CREATION_COMMENT)
(select TOPIC					    
THE_REGEXP,				    
SCOPE,						    
MESSAGE_TO_USER,			    
CREATION_DATE,				    
CREATION_USER,				    
CREATION_COMMENT				    
from minipress.bboard_bozo_patterns);



insert into bboard (
MSG_ID,				    
REFERS_TO,					    
TOPIC,					    
CATEGORY,					    
ORIGINATING_IP,				    
USER_ID,			    
ONE_LINE,					    
MESSAGE,				    
HTML_P,					    
NOTIFY,					    
POSTING_TIME,					    
EXPIRATION_DAYS,			    
INTEREST_LEVEL,				    
SORT_KEY,					    
CUSTOM_SORT_KEY,			    
CUSTOM_SORT_KEY_PRETTY,			    
EPA_REGION,					    
USPS_ABBREV,					    
FIPS_COUNTY_CODE,				    
ZIP_CODE)					   
(select MSG_ID,				    
REFERS_TO,					    
TOPIC,					    
CATEGORY,					    
ORIGINATING_IP,				    
USER_ID,			    
ONE_LINE,					    
MESSAGE,				    
HTML_P,					    
NOTIFY,					    
POSTING_TIME,					    
EXPIRATION_DAYS,			    
INTEREST_LEVEL,				    
SORT_KEY,					    
CUSTOM_SORT_KEY,			    
CUSTOM_SORT_KEY_PRETTY,			    
EPA_REGION,					    
USPS_ABBREV,					    
FIPS_COUNTY_CODE,				    
ZIP_CODE					    
from minipress.bboard


insert into msg_id_generator (LAST_MSG_ID) (select last_msg_id from minipress.msg_id_generator);



insert into bboard_email_alerts (   
USER_ID,			    
TOPIC,					    
VALID_P,				    
FREQUENCY,					    
KEYWORDS)					    
(select USER_ID,			    
TOPIC,					    
VALID_P,				    
FREQUENCY,					    
KEYWORDS 					    
from minipress.bboard_email_alerts);



insert into bboard_email_alerts_updates (
WEEKLY,					    
WEEKLY_TOTAL,					    
DAILY,						    
DAILY_TOTAL,					    
MONTHU,					    
MONTHU_TOTAL)					    			
(select WEEKLY,					    
WEEKLY_TOTAL,					    
DAILY,						    
DAILY_TOTAL,					    
MONTHU,					    
MONTHU_TOTAL					    
from minipress.bboard_email_alerts_updates);

-- you'll have to also copy files around in the file system

insert into bboard_uploaded_files (   
BBOARD_UPLOAD_ID,			    
MSG_ID,				   
FILE_TYPE,					    
FILE_EXTENSION,				    
N_BYTES,				    
CLIENT_FILE,			    
FILE_STUB,				    
CAPTION,				    
THUMBNAIL_STUB,				    
ORIGINAL_WIDTH,				    
 ORIGINAL_HEIGHT)				 
(select BBOARD_UPLOAD_ID,			    
MSG_ID,				   
FILE_TYPE,					    
FILE_EXTENSION,				    
N_BYTES,				    
CLIENT_FILE,			    
FILE_STUB,				    
CAPTION,				    
THUMBNAIL_STUB,				    
ORIGINAL_WIDTH,				    
ORIGINAL_HEIGHT				    
from minipress.bboard_uploaded_files);

