wsm
9bc5a1bd59
定点联系单模块新增 fixed_contact_20260730 与 fixed_contact_feedback_20260730，clear_all_data.sql 监督模块业务表分组缺这两张，导致清库后历史数据残留。
325 lines
12 KiB
SQL
325 lines
12 KiB
SQL
-- ============================================================
|
||
-- 数据库全量清理脚本
|
||
-- 目标：清除所有业务数据与流程运行时数据，
|
||
-- 保留用户/部门/角色/菜单/权限/字典等系统配置
|
||
-- 保留已设计的流程定义（可重新发布）
|
||
-- 效果：登录后可看到菜单，点进去是空的业务界面
|
||
--
|
||
-- 表来源：代码 @TableName 注解 + 数据库已存在
|
||
-- 部分表可能在某些环境中尚未创建，执行时若"doesn't exist"
|
||
-- 错误可忽略（该表当前环境无数据需清理）
|
||
--
|
||
-- ============================================================
|
||
-- 执行后必须执行的恢复操作：
|
||
-- 1. 同步用户到 Flowable 引擎：
|
||
-- PUT /act/process/extActProcess/doSyncUser
|
||
-- （act_id_* 表已清空，不执行此步则按角色/部门分配任务会失败）
|
||
-- 2. 重新发布流程定义（如有需要）：
|
||
-- 进入流程设计器 → 发布已设计好的流程
|
||
-- （ext_act_process.process_status 已重置为 0）
|
||
-- ============================================================
|
||
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ============================================================
|
||
-- 1. Flowable 引擎运行时表（当前运行中的流程实例数据）
|
||
-- ============================================================
|
||
TRUNCATE TABLE act_ru_actinst;
|
||
TRUNCATE TABLE act_ru_deadletter_job;
|
||
TRUNCATE TABLE act_ru_entitylink;
|
||
TRUNCATE TABLE act_ru_event_subscr;
|
||
TRUNCATE TABLE act_ru_execution;
|
||
TRUNCATE TABLE act_ru_external_job;
|
||
TRUNCATE TABLE act_ru_history_job;
|
||
TRUNCATE TABLE act_ru_identitylink;
|
||
TRUNCATE TABLE act_ru_job;
|
||
TRUNCATE TABLE act_ru_suspended_job;
|
||
TRUNCATE TABLE act_ru_task;
|
||
TRUNCATE TABLE act_ru_timer_job;
|
||
TRUNCATE TABLE act_ru_variable;
|
||
|
||
-- ============================================================
|
||
-- 2. Flowable 引擎历史表（已完成的流程实例归档数据）
|
||
-- ============================================================
|
||
TRUNCATE TABLE act_hi_actinst;
|
||
TRUNCATE TABLE act_hi_attachment;
|
||
TRUNCATE TABLE act_hi_comment;
|
||
TRUNCATE TABLE act_hi_detail;
|
||
TRUNCATE TABLE act_hi_entitylink;
|
||
TRUNCATE TABLE act_hi_identitylink;
|
||
TRUNCATE TABLE act_hi_procinst;
|
||
TRUNCATE TABLE act_hi_taskinst;
|
||
TRUNCATE TABLE act_hi_tsk_log;
|
||
TRUNCATE TABLE act_hi_varinst;
|
||
TRUNCATE TABLE act_evt_log;
|
||
|
||
-- ============================================================
|
||
-- 3. Flowable 引擎部署表（已发布的流程定义快照，重新发布会自动重建）
|
||
-- ============================================================
|
||
TRUNCATE TABLE act_re_procdef;
|
||
TRUNCATE TABLE act_re_deployment;
|
||
TRUNCATE TABLE act_re_model;
|
||
TRUNCATE TABLE act_ge_bytearray;
|
||
TRUNCATE TABLE act_procdef_info;
|
||
|
||
-- ============================================================
|
||
-- 4. Flowable 事件注册表运行时数据
|
||
-- flw_channel_definition / flw_event_* / flw_ev_* 保留
|
||
-- ============================================================
|
||
TRUNCATE TABLE flw_ru_batch;
|
||
TRUNCATE TABLE flw_ru_batch_part;
|
||
|
||
-- ============================================================
|
||
-- 5. Flowable 身份表（从 sys_user/sys_role 同步的缓存，清空后需重新同步）
|
||
-- act_id_property 保留（引擎配置元数据）
|
||
-- ============================================================
|
||
TRUNCATE TABLE act_id_user;
|
||
TRUNCATE TABLE act_id_group;
|
||
TRUNCATE TABLE act_id_membership;
|
||
TRUNCATE TABLE act_id_info;
|
||
TRUNCATE TABLE act_id_bytearray;
|
||
TRUNCATE TABLE act_id_priv;
|
||
TRUNCATE TABLE act_id_priv_mapping;
|
||
TRUNCATE TABLE act_id_token;
|
||
|
||
-- ============================================================
|
||
-- 6. 自定义 BPM 运行时数据
|
||
-- ============================================================
|
||
TRUNCATE TABLE ext_act_bpm_log;
|
||
TRUNCATE TABLE ext_act_bpm_file;
|
||
TRUNCATE TABLE ext_act_flow_data;
|
||
TRUNCATE TABLE ext_act_task_cc;
|
||
TRUNCATE TABLE ext_act_task_notification;
|
||
TRUNCATE TABLE ext_act_mutil_flow_data;
|
||
TRUNCATE TABLE task_approval_opinion;
|
||
|
||
-- ============================================================
|
||
-- 7. 流程部署快照（与 act_re_* 对应，重新发布流程时自动重建）
|
||
-- ============================================================
|
||
TRUNCATE TABLE ext_act_process_node_deploy;
|
||
|
||
-- ============================================================
|
||
-- 8. 表单设计器运行时数据
|
||
-- design_form 及其他 design_form_* 表保留（表单定义）
|
||
-- ============================================================
|
||
TRUNCATE TABLE ext_act_design_flow_data;
|
||
TRUNCATE TABLE design_form_data;
|
||
|
||
-- ============================================================
|
||
-- 9. 流程定义状态重置为"未发布"
|
||
-- 以下流程定义配置表保留不动：
|
||
-- ext_act_process / ext_act_process_node / ext_act_process_form
|
||
-- ext_act_process_node_auth / ext_act_process_node_reject_target
|
||
-- ext_act_listener / ext_act_expression
|
||
-- ============================================================
|
||
UPDATE ext_act_process SET process_status = 0;
|
||
|
||
-- ============================================================
|
||
-- 10. 监督模块业务表（代码中有 @TableName 定义）
|
||
-- ============================================================
|
||
-- 习语讲话
|
||
TRUNCATE TABLE bg_xi_speak;
|
||
TRUNCATE TABLE bg_xi_speak_feedback;
|
||
-- 把脉问诊
|
||
TRUNCATE TABLE bg_takepulse;
|
||
TRUNCATE TABLE bg_takepulse_feedback;
|
||
-- 党务管理
|
||
TRUNCATE TABLE bg_partymatter;
|
||
TRUNCATE TABLE bg_partymatter_feedback;
|
||
-- 党建检查与整改
|
||
TRUNCATE TABLE dj_inspect_improve;
|
||
TRUNCATE TABLE dj_inspection_rectify_list;
|
||
TRUNCATE TABLE dj_check;
|
||
TRUNCATE TABLE dj_meeting_record;
|
||
TRUNCATE TABLE dj_demo_life_meeting;
|
||
TRUNCATE TABLE dj_people_service;
|
||
TRUNCATE TABLE dj_officialdoc_distribute;
|
||
-- 纪律检查
|
||
TRUNCATE TABLE dq_inspect_task;
|
||
TRUNCATE TABLE dq_inspect_progress;
|
||
TRUNCATE TABLE dq_inspect_problem;
|
||
-- 改善提案
|
||
TRUNCATE TABLE improvement_proposal;
|
||
TRUNCATE TABLE improvement_institute_vote;
|
||
TRUNCATE TABLE improvement_dept_score;
|
||
-- 任务清单
|
||
TRUNCATE TABLE task_task;
|
||
TRUNCATE TABLE task_list;
|
||
TRUNCATE TABLE task_list_detial;
|
||
TRUNCATE TABLE task_list_favorite;
|
||
TRUNCATE TABLE task_list_permission;
|
||
-- 监督测试
|
||
TRUNCATE TABLE supervision_test;
|
||
-- 定点联系单
|
||
TRUNCATE TABLE fixed_contact_20260730;
|
||
TRUNCATE TABLE fixed_contact_feedback_20260730;
|
||
|
||
-- ============================================================
|
||
-- 11. JOA 流程业务演示表
|
||
-- ============================================================
|
||
TRUNCATE TABLE ext_biz_leave;
|
||
TRUNCATE TABLE joa_busines_strip;
|
||
TRUNCATE TABLE joa_doc_receiving;
|
||
TRUNCATE TABLE joa_doc_sending;
|
||
TRUNCATE TABLE joa_employee_leave;
|
||
TRUNCATE TABLE joa_loan;
|
||
TRUNCATE TABLE joa_overtime;
|
||
TRUNCATE TABLE joa_overtime_detail;
|
||
TRUNCATE TABLE joa_overtime_leave;
|
||
TRUNCATE TABLE joa_demo;
|
||
|
||
-- ============================================================
|
||
-- 12. Demo 模块测试表
|
||
-- ============================================================
|
||
TRUNCATE TABLE demo;
|
||
TRUNCATE TABLE demo_staff;
|
||
TRUNCATE TABLE demo_staff_group;
|
||
TRUNCATE TABLE demo_field_def_val_main;
|
||
TRUNCATE TABLE demo_field_def_val_sub;
|
||
TRUNCATE TABLE demo_sub_flow_hq;
|
||
TRUNCATE TABLE jeecg_order_main;
|
||
TRUNCATE TABLE jeecg_order_customer;
|
||
TRUNCATE TABLE jeecg_order_ticket;
|
||
TRUNCATE TABLE jeecg_tree_demo;
|
||
|
||
-- ============================================================
|
||
-- 13. OA 办公业务表
|
||
-- ============================================================
|
||
-- WPS
|
||
TRUNCATE TABLE oa_wps_file;
|
||
TRUNCATE TABLE oa_wps_file_version;
|
||
TRUNCATE TABLE oa_wps_file_watermark;
|
||
TRUNCATE TABLE oa_wps_user_acl;
|
||
-- 人事
|
||
TRUNCATE TABLE oa_become_regular;
|
||
TRUNCATE TABLE oa_dimission;
|
||
TRUNCATE TABLE oa_employee_award;
|
||
TRUNCATE TABLE oa_employee_certificate;
|
||
TRUNCATE TABLE oa_employee_education;
|
||
TRUNCATE TABLE oa_employee_experience;
|
||
TRUNCATE TABLE oa_employee_family;
|
||
TRUNCATE TABLE oa_employee_info;
|
||
TRUNCATE TABLE oa_employee_relations;
|
||
TRUNCATE TABLE oa_employment;
|
||
TRUNCATE TABLE oa_employment_apply;
|
||
TRUNCATE TABLE oa_interview;
|
||
TRUNCATE TABLE oa_promotion;
|
||
-- 出差/用车/请假/加班
|
||
TRUNCATE TABLE oa_business_travel;
|
||
TRUNCATE TABLE oa_business_travel_accompanied;
|
||
TRUNCATE TABLE oa_car_info;
|
||
TRUNCATE TABLE oa_car_use;
|
||
TRUNCATE TABLE oa_leave;
|
||
TRUNCATE TABLE oa_overtime;
|
||
TRUNCATE TABLE oa_security_vehicle;
|
||
-- 签到
|
||
TRUNCATE TABLE oa_sign_info;
|
||
TRUNCATE TABLE oa_sign_out;
|
||
TRUNCATE TABLE oa_sign_patch;
|
||
TRUNCATE TABLE oa_sign_rule;
|
||
-- 用品
|
||
TRUNCATE TABLE oa_supplies_detail;
|
||
TRUNCATE TABLE oa_supplies_use;
|
||
-- 公文
|
||
TRUNCATE TABLE oa_officialdoc_depart_user;
|
||
TRUNCATE TABLE oa_officialdoc_distribute;
|
||
TRUNCATE TABLE oa_officialdoc_issued;
|
||
TRUNCATE TABLE oa_officialdoc_organcode;
|
||
TRUNCATE TABLE oa_officialdoc_received;
|
||
TRUNCATE TABLE oa_officialdoc_seal;
|
||
TRUNCATE TABLE oa_officialdoc_temp;
|
||
|
||
-- ============================================================
|
||
-- 14. EOA 在线办公表
|
||
-- ============================================================
|
||
-- 聊天
|
||
TRUNCATE TABLE eoa_chat_group;
|
||
TRUNCATE TABLE eoa_chat_off_message;
|
||
TRUNCATE TABLE eoa_chat_on_message;
|
||
TRUNCATE TABLE eoa_chat_on_message_his;
|
||
TRUNCATE TABLE eoa_chat_session;
|
||
TRUNCATE TABLE eoa_chat_user;
|
||
TRUNCATE TABLE eoa_chat_user_group;
|
||
-- CMS
|
||
TRUNCATE TABLE eoa_cms_article;
|
||
TRUNCATE TABLE eoa_cms_article_read;
|
||
TRUNCATE TABLE eoa_cms_menu;
|
||
TRUNCATE TABLE eoa_cms_site;
|
||
-- 文件
|
||
TRUNCATE TABLE eoa_file;
|
||
TRUNCATE TABLE eoa_file_log;
|
||
-- 邮箱
|
||
TRUNCATE TABLE eoa_mailbox_attach;
|
||
TRUNCATE TABLE eoa_mailbox_category;
|
||
TRUNCATE TABLE eoa_mailbox_info;
|
||
TRUNCATE TABLE eoa_mailbox_receiver;
|
||
-- 会议
|
||
TRUNCATE TABLE eoa_metting;
|
||
TRUNCATE TABLE eoa_metting_device;
|
||
TRUNCATE TABLE eoa_metting_room;
|
||
TRUNCATE TABLE eoa_metting_sign;
|
||
-- 计划/门户
|
||
TRUNCATE TABLE eoa_plan;
|
||
TRUNCATE TABLE eoa_portal_site;
|
||
TRUNCATE TABLE eoa_portal_site_auth;
|
||
|
||
-- ============================================================
|
||
-- 15. 测试表（代码 @TableName + DB 中存在）
|
||
-- ============================================================
|
||
TRUNCATE TABLE ceshi_des_qingjia;
|
||
TRUNCATE TABLE ceshi_nongmin;
|
||
TRUNCATE TABLE ceshi_qingjia_dan;
|
||
TRUNCATE TABLE ceshi_ruzhi;
|
||
TRUNCATE TABLE ceshi_subqt;
|
||
TRUNCATE TABLE ce_fenzhi_zx;
|
||
TRUNCATE TABLE ce_paita_wangguan;
|
||
TRUNCATE TABLE joa_onl_leave;
|
||
TRUNCATE TABLE joa_onl_bthuiq;
|
||
TRUNCATE TABLE joa_onl_sxhuiq;
|
||
TRUNCATE TABLE oa_demo_bhan_wguan;
|
||
TRUNCATE TABLE rep_demo_dxtj;
|
||
TRUNCATE TABLE rep_demo_employee;
|
||
TRUNCATE TABLE rep_demo_gongsi;
|
||
TRUNCATE TABLE rep_demo_huizong;
|
||
TRUNCATE TABLE rep_demo_jianpiao;
|
||
TRUNCATE TABLE rep_demo_order_main;
|
||
TRUNCATE TABLE rep_demo_order_product;
|
||
TRUNCATE TABLE test_demo;
|
||
TRUNCATE TABLE test_enhance_select;
|
||
TRUNCATE TABLE test_main_table;
|
||
TRUNCATE TABLE test_note;
|
||
TRUNCATE TABLE test_online_link;
|
||
TRUNCATE TABLE test_order_customer;
|
||
TRUNCATE TABLE test_order_main;
|
||
TRUNCATE TABLE test_order_product;
|
||
TRUNCATE TABLE test_order_user;
|
||
TRUNCATE TABLE test_person;
|
||
TRUNCATE TABLE test_shoptype_tree;
|
||
TRUNCATE TABLE test_son_table;
|
||
TRUNCATE TABLE test_third_link;
|
||
TRUNCATE TABLE test_tree;
|
||
|
||
-- ============================================================
|
||
-- 16. 系统运行日志 / 消息 / API 调用记录
|
||
-- sys_files / sys_file_attachment 保留（头像、系统图标等文件）
|
||
-- jimu_report_export_log 保留（可选清除，见下方）
|
||
-- ============================================================
|
||
TRUNCATE TABLE sys_log;
|
||
TRUNCATE TABLE sys_data_log;
|
||
TRUNCATE TABLE sys_announcement;
|
||
TRUNCATE TABLE sys_announcement_send;
|
||
TRUNCATE TABLE sys_sms;
|
||
TRUNCATE TABLE sys_comment;
|
||
TRUNCATE TABLE sys_files_operate;
|
||
TRUNCATE TABLE open_api_log;
|
||
|
||
-- ============================================================
|
||
-- 17. （可选）取消注释以清除更多内容
|
||
-- ============================================================
|
||
-- TRUNCATE TABLE jimu_report_export_log;
|
||
-- TRUNCATE TABLE sys_file_attachment;
|
||
-- TRUNCATE TABLE sys_files;
|
||
-- TRUNCATE TABLE flyway_schema_history_1;
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|