Kaiping District Data Migration work

Select* fromT_cmts_disk;--Lesson Preparation Resources (copy resource directory)SelectPath_content fromT_cmts_diskwhereMediatype_id='002'and stage_id=?   and subject_id=? -- ; PATH_ID:0000--Old ResourcesSelectStage_id,stage_name fromT_stype_stage; --Learning SectionSelectStage_id,stage_name fromDsideal_db_tskp.t_dm_stage; --4Primary School0001  5Junior High School0002  6: High School:0003SelectSubject_id,subject_name fromT_stype_subject; --disciplinesSelectsubject_id,subject_name,stage_id fromDsideal_db_tskp.t_dm_subject;SelectVersion_id,version_name fromT_stype_version; --versionSelectTm_id,tm_name fromT_stype_tm; --TextbookSelectNode_id,node_name,node_code fromT_stype_node; --Chapter Catalogue Table--Resource TableSelectT1.cmtsresourceid,--resource ID GUID t1.createtime,--Create Time T1.fileext,--extension T1.filename,--file name T1.filesize,--file size, integer t1.ismultifile,--is not a compound file T1.title,--title T1.categoryid,--Application Type T1.fileext,--extension t1.new_mediatype_id,--Media type T1.new_mediatype_name,--Media type t1.new_stage_id,--The learning section of the target library is t1.new_subject_id,--the subject T1.versionid of the target library,--version T1.nodeid,--Junction t1.new_user_id,--User ID T1.new_user_name,--User name T1.new_path--New Resource Path fromT_cmts_cmtsresource T1whereIsdelete=0Limit -;-- ----------------------------------------------------------------Use zyk_ts;-- ======================================================================================================--Media Type--fixed the implementation of the full corresponding update T_stype_mediatypeSetMediatype_name='Image' whereMediatype_id='002'; update T_stype_mediatypeSetMediatype_name='other' whereMediatype_id='007'ALTER TABLE t_cmts_cmtsresource add column new_mediatype_id varchar ( $ALTER TABLE t_cmts_cmtsresource add column new_mediatype_name varchar ( $);--Delete 270 not checked delete fromT_cmts_cmtsresourcewhereFileext notinch(SelectExtension_name fromdsideal_db_tskp.t_resource_extension);--Add the corresponding media type in the new table idupdate T_cmts_cmtsresource t1 INNER join dsideal_db_tskp.t_resource_extension T2 on T1.fileext=t2.extension_nameSetT1.new_mediatype_id=t2.extension_id;--Add the corresponding media type name in the new table update T_cmts_cmtsresource t1 INNER join dsideal_db_tskp.t_resource_extension T2 on T1.fileext=t2.extension_nameSetT1.new_mediatype_name=T2.mediatype_name;-- ======================================================================================================--Learn section name ALTER TABLE t_cmts_cmtsresource add column stage_name varchar ( -); update T_cmts_cmtsresource t1 inner join t_stype_stage T2 on T1.stageid=t2.stage_idSetT1.stage_name=T2.stage_name;--account name ALTER TABLE t_cmts_cmtsresource add column subject_name varchar ( -); update T_cmts_cmtsresource t1 inner join T_stype_subject T2 on T1.subjectid=t2.subject_idSetT1.subject_name=T2.subject_name;--in the new table, the section idalter table t_cmts_cmtsresource Add column new_stage_id varchar ( -);--The Learning section of the goal idupdate T_cmts_cmtsresource T1SetT1.new_stage_id=4 whereStageid='0001'; update T_cmts_cmtsresource t1SetT1.new_stage_id=5 whereStageid='0002'; update T_cmts_cmtsresource t1SetT1.new_stage_id=6 whereStageid='0003';--The account in the new Table Idalter table t_cmts_cmtsresource Add column new_subject_id varchar ( -);--Target's account idupdate T_cmts_cmtsresource t1 inner join Dsideal_db_tskp.t_dm_subject T2 on T1.subject_name=t2.subject_name and T1.new_stage_id=t2.stage_idSetT1.new_subject_id=t2.subject_id;-- ======================================================================================================--the new version ID--ALTER TABLE t_cmts_cmtsresource add column new_scheme_id varchar ( $);--version ID of the target--Update T_cmts_cmtsresource t1 inner join Dsideal_db_tskp.t_resource_scheme T2 on t1.versionid=t2.old_version_idSetT1.new_scheme_id=t2.scheme_id;--new version name ALTER TABLE t_cmts_cmtsresource add column new_scheme_name varchar ( $);-- ======================================================================================================--the new complete path to ALTER TABLE t_cmts_cmtsresource add column new_path varchar ( -); Update T_cmts_cmtsresourceSetMediatypeid='002';--Old resource update T_cmts_cmtsresource T1SetT1.new_path=concat ('d:/dsideal_cmts/tomcat_7.0.34/webapps/cmts/uploadfiles/resources', T1.filepath)whereT1.resfrom is NULL;--New Resource update T_cmts_cmtsresource t1 inner join T_cmts_disk T2 on T1.stageid=t2.stage_id and t1.subjectid=t2.subject_id and t1.mediatypeid=t2.mediatype_idSetT1.new_path=concat (T2.path_content,t1.filepath)whereT1.resfrom isNotNULL;-- ======================================================================================================--User name ALTER TABLE t_cmts_cmtsresource add column new_user_id varchar ( -ALTER TABLE t_cmts_cmtsresource add column new_user_name varchar ( -); update T_cmts_cmtsresource t1 inner join Dsideal_db_tskp.t_sys_loginperson T2 on T1.userid=t2.old_user_uuidSetT1.new_user_id=t2.person_id; update t_cmts_cmtsresource t1 inner join Dsideal_db_tskp.t_sys_loginperson T2 on T1.userid=t2.old_user_uuidSetT1.new_user_name=T2.person_name;--======================================================================================================--Application type: APP_TYPE_ID:2The default is footage--Res_type:2--Media type: Resource_type_name,resource_type--Meterial_type: Same as media type--Bk_type,bk_type_name--Release_status:1--parent_structure_name:-1--Reource_size_int:-1--Lesson Type:001Courseware--002Lesson plans1--003Study case2--006Micro-Classroom3--007Video Classroom4--007Video Classroom5ALTER TABLE t_cmts_cmtsresource add column new_thumb_status varchar ( -ALTER TABLE t_cmts_cmtsresource add column new_preview_status varchar ( -ALTER TABLE t_cmts_cmtsresource add column new_thumb_id varchar ( -); update T_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_resource_extension T2 on T1.fileext=t2.extension_nameSetT1.new_thumb_status=t2.thumb_status, T1.new_preview_status=t2.preview_status, t1.new_thumb_id=t2.thumb_id;-- ======================================================================================================--identified as from Kaiping ALTER TABLE dsideal_db_tskp.t_resource_base add column FROM_KPintALTER TABLE dsideal_db_tskp.t_resource_info add column FROM_KPint;--record the original path ALTER TABLE dsideal_db_tskp.t_resource_base add column Old_file_path varchar ( -);-- ======================================================================================================--Ask the front-end person to be small: Change the type of lesson preparation to:--002Lesson plans1--003Study case2--006Micro-Classroom3--007Video Classroom4--007Video Classroom5-- ======================================================================================================--Kuancheng The copyright information, etc. need to be modified. -- ======================================================================================================SelectResource_id_int,resource_id_char,resource_title,resource_size,resource_size_int,resource_type,resource_ type_name,resource_category,resource_page,create_time,person_name,create_person,b_use,update_logo,ts,source_id , extension,file_id,file_md5,file_sha1,thumb_id,thumb_md5,thumb_sha1,pinyin,product_id,scheme_id_char,scheme_id , Structure_code,structure_id_char,structure_id,material_type,is_single,is_old,preview_status,down_count,check_ Status,check_message,thumb_status,old_file_path,is_multifile,chuli,is_3_2,parent_name,width,height,for_ Urlencoder_url,for_iso_url,release_status,res_type,bk_type,bk_type_name,m3u8_status,m3u8_url,stage_id,subject_ ID, FROM_KP fromDsideal_db_tskp.t_resource_base limit1;SelectId,resource_id_int,resource_id_char,resource_title,resource_type_name, Resource_format,resource_page,reso Urce_size,resource_size_int,create_time, Down_count,file_id,thumb_id,resource_type,structure_id,person_id,pers ON_NAME,IDENTITY_ID, Group_id,preview_status,scheme_id_int,ts,thumb_status,update_ts,for_urlencoder_url,for_is O_url, Width,height,parent_structure_name,release_status,res_type,bk_type,bk_type_name,material_type,m3u8_stat US, M3U8_URL,APP_TYPE_ID,STAGE_ID,SUBJECT_ID,FROM_KP fromDsideal_db_tskp.t_resource_info;

Kaiping District Data Migration work