-- ---------------------------------------------------------------- use zyk_ts; -- ====================================================================================================== alter table t_cmts_cmtsresource add column new_mediatype_id varchar(36); alter table t_cmts_cmtsresource add column new_mediatype_name varchar(36); alter table t_cmts_cmtsresource add column new_scheme_id_char varchar(36); alter table t_cmts_cmtsresource add column new_scheme_id_int int; alter table t_cmts_cmtsresource add column new_structure_code  varchar(36); alter table t_cmts_cmtsresource add column new_structure_id_char varchar(36); alter table t_cmts_cmtsresource add column new_structure_id int; alter table t_cmts_cmtsresource modify column new_path varchar(256); alter table t_cmts_cmtsresource add column stage_name varchar(30); alter table t_cmts_cmtsresource add column subject_name varchar(30); alter table t_cmts_cmtsresource add column new_stage_id varchar(30); alter table t_cmts_cmtsresource add column new_subject_id varchar(30); alter table t_cmts_cmtsresource add column new_user_id varchar(128); alter table t_cmts_cmtsresource add column new_user_name varchar(128); alter table t_cmts_cmtsresource add column new_thumb_status varchar(128); alter table t_cmts_cmtsresource add column new_preview_status varchar(128); alter table t_cmts_cmtsresource add column new_thumb_id varchar(128); alter table dsideal_db_tskp.t_resource_base add column from_Kp int; alter table dsideal_db_tskp.t_resource_info add column from_Kp int; alter table dsideal_db_tskp.t_resource_my_info add column from_Kp int; alter table dsideal_db_tskp.t_resource_structure add column from_kp int; alter table dsideal_db_tskp.t_resource_base drop column old_file_path; alter table dsideal_db_tskp.t_resource_base add column old_file_path varchar(256); select * from dsideal_db_tskp.t_resource_structure limit 1 -- ====================================================================================================== -- 媒体类型 -- 修正一下实现完整对应 update t_stype_mediatype set mediatype_name='图片' where mediatype_id='002'; update t_stype_mediatype set mediatype_name='其他' where mediatype_id='007'; -- 删除270个没有检查到的 delete from t_cmts_cmtsresource where fileext not in (select extension_name from dsideal_db_tskp.t_resource_extension); -- 添加新表中对应的媒体类型ID update t_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_resource_extension t2  on t1.fileext=t2.extension_name set  t1.new_mediatype_id=t2.extension_id; -- 添加新表中对应的媒体类型名称 update t_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_resource_extension t2  on t1.fileext=t2.extension_name set  t1.new_mediatype_name=t2.mediatype_name; -- 检查是不是存在不一致的地方 :OK -- select * from t_cmts_cmtsresource t1 where t1.fileext not in (select t2.EXTENSION_NAME  from dsideal_db_tskp.t_resource_extension t2 ) -- ====================================================================================================== -- 新的完整的路径 update t_cmts_cmtsresource set mediatypeid='002'; -- 旧资源 update t_cmts_cmtsresource t1  set t1.new_path=CONCAT('D:/dsideal_cmts/Tomcat_7.0.34/webapps/cmts/uploadfiles/resources',t1.filepath) where t1.resfrom is null; -- 新资源 update t_cmts_cmtsresource t1 set t1.new_path=concat(f_get_pathcontent(t1.stageid,t1.subjectid,t1.mediatypeid),t1.filepath) where t1.resfrom is not null; -- select f_get_pathcontent('0001','0017','002'); --  select t1.stageid,t1.subjectid,t1.mediatypeid from t_cmts_cmtsresource t1 where t1.cmtsresourceid='002925AF-2807-4331-BBAC-C1B658E0BBD1' -- select * from t_cmts_disk t1 where t1.stage_id='0001' and t1.subject_id='0017' and t1.mediatype_id='002' -- select * from t_cmts_cmtsresource where new_path is null :6339条,难道要删除掉它们？？ delete from t_cmts_cmtsresource where new_path is null; -- 如何解释？？ -- select * from t_cmts_disk where subject_id>15 -- select * from t_cmts_cmtsresource where subjectid>15 -- select * from t_stype_subject where subject_id>15 CREATE FUNCTION f_get_pathcontent(v_statge_id varchar(128),v_subject_id varchar(128),v_mediatypeid varchar(128))  RETURNS varchar(512) CHARSET utf8 BEGIN      DECLARE v_app_name varchar(1000);          Select t1.path_content into v_app_name from t_cmts_disk t1 where t1.stage_id=v_statge_id and t1.subject_id=v_subject_id and t1.mediatype_id=v_mediatypeid;      RETURN v_app_name; END -- ====================================================================================================== -- 学段名称 update t_cmts_cmtsresource t1 inner join t_stype_stage t2   on t1.stageid=t2.stage_id set  t1.stage_name=t2.stage_name ; -- 科目名称 update t_cmts_cmtsresource t1 inner join t_stype_subject t2 on t1.subjectid=t2.subject_id set t1.subject_name=t2.subject_name ; -- 在新的表中的学段ID -- 目标的学段ID update t_cmts_cmtsresource t1 set t1.new_stage_id=4 where stageid='0001'; update t_cmts_cmtsresource t1 set t1.new_stage_id=5 where stageid='0002'; update t_cmts_cmtsresource t1 set t1.new_stage_id=6 where stageid='0003'; -- 在新的表中的科目ID -- 目标的科目ID update 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_id set t1.new_subject_id=t2.subject_id; -- ====================================================================================================== -- ====================================================================================================== -- 用户名称 update t_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_sys_loginperson t2   on t1.userid=t2.old_user_uuid set  t1.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_uuid set  t1.new_user_name=t2.person_name ; -- ====================================================================================================== -- 根据扩展名更新一下此扩展名的缩略图和预览是不是要生成？ update t_cmts_cmtsresource t1 inner join dsideal_db_tskp.t_resource_extension t2   on t1.fileext=t2.extension_name set   t1.new_thumb_status=t2.thumb_status , t1.new_preview_status=t2.preview_status , t1.new_thumb_id=t2.thumb_id ; -- ====================================================================================================== -- 标识为来自开平 -- ====================================================================================================== -- 要求前端人员小蒙：将备课的类型修改为： -- 002    教案     1 -- 003    学案     2    -- 006    微课堂   3 -- 007    视频课堂 4 -- 007    视频课堂 5 -- ====================================================================================================== -- 宽城的版权信息等需要修改。 -- ====================================================================================================== -- 最后记得要将t_resource_base  t_resource_info t_resource_my_info 的主键取消掉自动增长 -- 应用类型:     app_type_id:2 默认是素材 --                          res_type :2                         -- 媒体类型:resource_type_name,resource_type                         -- meterial_type:和媒体类型一样 --                          bk_type,bk_type_name                         -- release_status:1                       -- parent_structure_name:-1                       -- reource_size_int :-1 -- 备课类型:     -- 001    课件     1 -- 002    教案     2 -- 003    学案     3    -- 006    微课堂   4 -- 007    视频课堂 5 -- ====================================================================================================== -- 目标的版本ID -- 新的版本名称 delete from t_cmts_cmtsresource where nodeid not in (select node_id from dsideal_db_tskp.t_resource_structure_new); -- ====================================================================================================== -- ====================================================================================================== -- ====================================================================================================== -- ====================================================================================================== -- 下面是要执行的语句 -- 1、 -- update t_cmts_cmtsresource  t1 inner join dsideal_db_tskp.t_resource_structure_new  t2 on t1.versionid=t2.version_id -- set t1.new_scheme_id_char=t2.scheme_id_char,t1.new_scheme_id_int=t2.scheme_id_int; -- 2、 -- update t_cmts_cmtsresource  t1 inner join dsideal_db_tskp.t_resource_structure_new  t2 on t1.nodeid=t2.node_id -- set t1.new_structure_id=t2.structure_id,t1.new_structure_code=t2.structure_code,t1.new_structure_id_char=t2.structure_id_char; -- 检查t_resource_structure_new的合法性 select distinct t1.nodeid from t_cmts_cmtsresource t1 where t1.nodeid not in (select t2.node_id from dsideal_db_tskp.t_resource_structure_new t2); select count(distinct t1.nodeid) from t_cmts_cmtsresource t1 ; select count(1) from dsideal_db_tskp.t_resource_structure_new; select t1.structure_id from dsideal_db_tskp.t_resource_structure_new t1 where t1.structure_id  not in (select t2.structure_id from dsideal_db_tskp.t_resource_structure t2); -- 3、 insert into dsideal_db_tskp.t_resource_base (             -- 1             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,             -- 4             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,             -- 7             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,             -- 10             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 ) select     t1.nodeid,                 -- 1                 t1.cmtsresourceid,t1.title,-1,-1,                            -- 资源ID GUID   标题  文件尺寸 文件尺寸大小                 t1.new_mediatype_id,t1.new_mediatype_name,-1,0,    -- 媒体类型 媒体类型    给平台使用-1，资源页数                 t1.createtime,    t1.new_user_name,        t1.new_user_id    ,    1,'-1','2014051914330000833',        -- 用户ID         用户姓名                 创建时间                                                  -- 4               1,t1.fileext,'','','',new_thumb_id,                -- 扩展名                 '','','','-1',t2.scheme_id_char,                 t2.scheme_id_int,t2.structure_code,t2.structure_id_char,t2.structure_id,                                  -- 7                 t1.new_mediatype_id,1,-1,t1.new_preview_status,0,                 1,'',t1.new_thumb_status,t1.new_path,                 t1.ismultifile,-1,-1,'',0,0,                                  -- 10                 '','',1,2,                 (case categoryid when '001' then 1 when '002' then 2 when '003' then 3 when '006' then 4 when '007' then 5 end),                 (case categoryid when '001' then '课件' when '002' then '教案' when '003' then '学案' when '006' then '微课堂' when '007' then '视频课堂' end),                 -1,'',t1.new_stage_id,    -- 目标库的学段                 t1.new_subject_id,1      -- 目的库的学科                                                  from t_cmts_cmtsresource t1 , dsideal_db_tskp.t_resource_structure t2         where        t1.nodeid=t2.node_id and         t1.isdelete=0 and t1.new_path is not null; -- -================================================================================================== -- select * from dsideal_db_tskp.t_resource_structure_new limit 1 -- 4、 insert into dsideal_db_tskp.t_resource_info (              resource_id_int,resource_id_char,resource_title,resource_type_name,              resource_format,resource_page,resource_size,resource_size_int,create_time,              down_count,file_id,thumb_id,resource_type,structure_id,person_id,person_name,identity_id,                           group_id,preview_status,scheme_id_int,ts,thumb_status,update_ts,for_urlencoder_url,for_iso_url,              width,height,parent_structure_name,release_status,res_type,bk_type,bk_type_name,material_type,m3u8_status,              m3u8_url,app_type_id,stage_id,subject_id,from_kp ) select t1.resource_id_int,t1.resource_id_char,t1.resource_title,t1.resource_type_name,              t1.extension,t1.resource_page,t1.resource_size,t1.resource_size_int,t1.create_time,              t1.down_count,t1.file_id,t1.thumb_id,t1.resource_type,t1.structure_id,t1.create_person,t1.person_name,5,              2,t1.preview_status,t1.scheme_id,t1.ts,t1.thumb_status,-1,t1.for_urlencoder_url,t1.for_iso_url,              t1.width,t1.height,t1.parent_name,t1.release_status,t1.resource_type,t1.bk_type,t1.bk_type_name,t1.material_type,t1.m3u8_status,             t1.m3u8_url,2,t1.stage_id,t1.subject_id,t1.from_kp  from dsideal_db_tskp.t_resource_base t1 where from_kp=1; -- ====================================================================================================== -- 5、 insert into dsideal_db_tskp.t_resource_my_info (         resource_id_int,resource_id_char,resource_title,resource_type,resource_size_int,resource_format,         person_id,identity_id,resource_page,ts,down_count,type_id,update_ts,structure_id,scheme_id_int,preview_status,         thumb_status,for_urlencoder_url,for_iso_url,table_pk,resource_type_name,file_id,thumb_id,create_time,resource_size,         group_id,width,height,parent_structure_name,down_type,res_type,bk_type,bk_type_name,m3u8_status,m3u8_url,         app_type_id,b_delete,stage_id,subject_id ) select t1.resource_id_int,t1.resource_id_char,t1.resource_title,t1.resource_type,t1.resource_size_int,t1.extension,            t1.create_person,5,t1.resource_page,t1.ts,t1.down_count,6,-1,t1.structure_id,t1.scheme_id,t1.preview_status, t1.thumb_status,t1.for_urlencoder_url,t1.for_iso_url,t1.resource_id_int,t1.resource_type_name,t1.file_id,t1.thumb_id,t1.create_time,t1.resource_size,             2,t1.width,t1.height,t1.parent_name,'1',2,t1.bk_type,t1.bk_type_name,t1.m3u8_status,t1.m3u8_url,             2,0,t1.stage_id,t1.subject_id  from dsideal_db_tskp.t_resource_base t1 where from_kp=1; -- ====================================================================================================== -- ====================================================================================================== -- ====================================================================================================== delete from dsideal_db_tskp.t_resource_base where from_kp=1; delete from dsideal_db_tskp.t_resource_info where from_kp=1; delete from dsideal_db_tskp.t_resource_my_info where from_kp=1;