lc_veterans/doc/db_upgrade/upgrade_20221224.sql

CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `view_all_code` AS
SELECT
    `tb`.`device_type` AS `device_type`,
    `tb`.`device_type_name` AS `device_type_name`,
    `tb`.`device_id` AS `device_id`,
    `tb`.`device_name` AS `device_name`,
    `tb`.`sn` AS `sn`,
    `tb`.`address` AS `address`,
    `tb`.`lamp_pole_name` AS `lamp_pole_name`,
    `tb`.`lamp_pole_sn` AS `lamp_pole_sn`,
    `tb`.`lamp_pole_id` AS `lamp_pole_id`,
    `tb`.`install_time` AS `install_time`,
    `tb`.`create_time` AS `create_time`,
    `tb`.`is_deleted` AS `is_deleted`,
    `tb`.`tenant_id` AS `tenant_id`
FROM
    (
        SELECT
            1 AS `device_type`,
            '摄像机' AS `device_type_name`,
            `device_camera`.`id` AS `device_id`,
            `device_camera`.`device_name` AS `device_name`,
            `device_camera`.`device_sn` AS `sn`,
            `device_camera`.`lamp_pole_location` AS `address`,
            `device_camera`.`lamp_pole_name` AS `lamp_pole_name`,
            `device_camera`.`lamp_pole_sn` AS `lamp_pole_sn`,
            `device_camera`.`lamp_pole_id` AS `lamp_pole_id`,
            `device_camera`.`install_time` AS `install_time`,
            `device_camera`.`create_time` AS `create_time`,
            `device_camera`.`is_deleted` AS `is_deleted`,
            `device_camera`.`tenant_id` AS `tenant_id`
        FROM
            `device_camera`
        UNION
        SELECT
            2 AS `device_type`,
            '网关' AS `device_type_name`,
            `device_wisdom_gateway`.`id` AS `device_id`,
            `device_wisdom_gateway`.`gateway_name` AS `device_name`,
            `device_wisdom_gateway`.`gateway_sn` AS `sn`,
            `device_wisdom_gateway`.`lamp_pole_location` AS `address`,
            `device_wisdom_gateway`.`lamp_pole_name` AS `lamp_pole_name`,
            `device_wisdom_gateway`.`lamp_pole_sn` AS `lamp_pole_sn`,
            `device_wisdom_gateway`.`lamp_pole_id` AS `lamp_pole_id`,
            `device_wisdom_gateway`.`gateway_install_time` AS `install_time`,
            `device_wisdom_gateway`.`create_time` AS `create_time`,
            `device_wisdom_gateway`.`is_deleted` AS `is_deleted`,
            `device_wisdom_gateway`.`tenant_id` AS `tenant_id`
        FROM
            `device_wisdom_gateway`
        UNION
        SELECT
            3 AS `device_type`,
            '灯控' AS `device_type_name`,
            `device_light_control`.`id` AS `device_id`,
            `device_light_control`.`name` AS `device_name`,
            `device_light_control`.`sn` AS `sn`,
            `device_light_control`.`lamp_pole_location` AS `address`,
            `device_light_control`.`lamp_pole_name` AS `lamp_pole_name`,
            `device_light_control`.`lamp_pole_sn` AS `lamp_pole_sn`,
            `device_light_control`.`lamp_pole_id` AS `lamp_pole_id`,
            `device_light_control`.`install_time` AS `install_time`,
            `device_light_control`.`create_time` AS `create_time`,
            `device_light_control`.`is_deleted` AS `is_deleted`,
            `device_light_control`.`tenant_id` AS `tenant_id`
        FROM
            `device_light_control`
        UNION
        SELECT
            4 AS `device_type`,
            '配电箱' AS `device_type_name`,
            `device_switch_box`.`id` AS `device_id`,
            `device_switch_box`.`box_name` AS `device_name`,
            `device_switch_box`.`box_sn` AS `sn`,
            `device_switch_box`.`box_location` AS `address`,
            NULL AS `lamp_pole_name`,
            NULL AS `lamp_pole_sn`,
            NULL AS `lamp_pole_id`,
            `device_switch_box`.`install_time` AS `install_time`,
            `device_switch_box`.`create_time` AS `create_time`,
            `device_switch_box`.`is_deleted` AS `is_deleted`,
            `device_switch_box`.`tenant_id` AS `tenant_id`
        FROM
            `device_switch_box`
        UNION
        SELECT
            5 AS `device_type`,
            '信息屏' AS `device_type_name`,
            `device_info_board`.`id` AS `device_id`,
            `device_info_board`.`info_name` AS `device_name`,
            `device_info_board`.`sn` AS `sn`,
            `device_info_board`.`lamp_pole_location` AS `address`,
            `device_info_board`.`lamp_pole_name` AS `lamp_pole_name`,
            `device_info_board`.`lamp_pole_sn` AS `lamp_pole_sn`,
            `device_info_board`.`lamp_pole_id` AS `lamp_pole_id`,
            `device_info_board`.`install_time` AS `install_time`,
            `device_info_board`.`create_time` AS `create_time`,
            `device_info_board`.`is_deleted` AS `is_deleted`,
            `device_info_board`.`tenant_id` AS `tenant_id`
        FROM
            `device_info_board`
        UNION
        SELECT
            6 AS `device_type`,
            '环境监测' AS `device_type_name`,
            `device_opto_sensor`.`id` AS `device_id`,
            `device_opto_sensor`.`name` AS `device_name`,
            `device_opto_sensor`.`sn` AS `sn`,
            `device_opto_sensor`.`lamp_pole_location` AS `address`,
            `device_opto_sensor`.`lamp_pole_name` AS `lamp_pole_name`,
            `device_opto_sensor`.`lamp_pole_sn` AS `lamp_pole_sn`,
            `device_opto_sensor`.`lamp_pole_id` AS `lamp_pole_id`,
            `device_opto_sensor`.`install_time` AS `install_time`,
            `device_opto_sensor`.`create_time` AS `create_time`,
            `device_opto_sensor`.`is_deleted` AS `is_deleted`,
            `device_opto_sensor`.`tenant_id` AS `tenant_id`
        FROM
            `device_opto_sensor`
        UNION
        SELECT
            7 AS `device_type`,
            '集控器' AS `device_type_name`,
            `device_zigbee`.`id` AS `device_id`,
            `device_zigbee`.`name` AS `device_name`,
            `device_zigbee`.`sn` AS `sn`,
            `device_zigbee`.`lamp_pole_location` AS `address`,
            `device_zigbee`.`lamp_pole_name` AS `lamp_pole_name`,
            `device_zigbee`.`lamp_pole_sn` AS `lamp_pole_sn`,
            `device_zigbee`.`lamp_pole_id` AS `lamp_pole_id`,
            `device_zigbee`.`install_time` AS `install_time`,
            `device_zigbee`.`create_time` AS `create_time`,
            `device_zigbee`.`is_deleted` AS `is_deleted`,
            `device_zigbee`.`tenant_id` AS `tenant_id`
        FROM
            `device_zigbee`
        UNION
        SELECT
            8 AS `device_type`,
            '一键告警终端' AS `device_type_name`,
            `device_a_key_alarm_terminal`.`id` AS `device_id`,
            `device_a_key_alarm_terminal`.`terminal_name` AS `device_name`,
            `device_a_key_alarm_terminal`.`terminal_sn` AS `sn`,
            `device_a_key_alarm_terminal`.`lamp_pole_location` AS `address`,
            `device_a_key_alarm_terminal`.`lamp_pole_name` AS `lamp_pole_name`,
            `device_a_key_alarm_terminal`.`lamp_pole_sn` AS `lamp_pole_sn`,
            `device_a_key_alarm_terminal`.`lamp_pole_id` AS `lamp_pole_id`,
            `device_a_key_alarm_terminal`.`install_time` AS `install_time`,
            `device_a_key_alarm_terminal`.`create_time` AS `create_time`,
            `device_a_key_alarm_terminal`.`is_deleted` AS `is_deleted`,
            `device_a_key_alarm_terminal`.`tenant_id` AS `tenant_id`
        FROM
            `device_a_key_alarm_terminal`
        UNION
        SELECT
            9 AS `device_type`,
            '一键告警服务端' AS `device_type_name`,
            `device_a_key_alarm_serve`.`id` AS `device_id`,
            `device_a_key_alarm_serve`.`serve_name` AS `device_name`,
            `device_a_key_alarm_serve`.`serve_sn` AS `sn`,
            NULL AS `address`,
            NULL AS `lamp_pole_name`,
            NULL AS `lamp_pole_sn`,
            NULL AS `lamp_pole_id`,
            `device_a_key_alarm_serve`.`install_time` AS `install_time`,
            `device_a_key_alarm_serve`.`create_time` AS `create_time`,
            `device_a_key_alarm_serve`.`is_deleted` AS `is_deleted`,
            `device_a_key_alarm_serve`.`tenant_id` AS `tenant_id`
        FROM
            `device_a_key_alarm_serve`
        UNION
        SELECT
            10 AS `device_type`,
            '变压器' AS `device_type_name`,
            `device_transformer`.`id` AS `device_id`,
            `device_transformer`.`trans_name` AS `device_name`,
            `device_transformer`.`trans_sn` AS `sn`,
            `device_transformer`.`install_location` AS `address`,
            NULL AS `lamp_pole_name`,
            NULL AS `lamp_pole_sn`,
            NULL AS `lamp_pole_id`,
            NULL AS `install_time`,
            `device_transformer`.`create_time` AS `create_time`,
            `device_transformer`.`is_deleted` AS `is_deleted`,
            `device_transformer`.`tenant_id` AS `tenant_id`
        FROM
            `device_transformer`
        UNION
        SELECT
            11 AS `device_type`,
            '灯杆' AS `device_type_name`,
            `device_lamp_pole`.`id` AS `device_id`,
            `device_lamp_pole`.`pole_name` AS `device_name`,
            `device_lamp_pole`.`pole_sn` AS `sn`,
            `device_lamp_pole`.`install_location` AS `address`,
            NULL AS `lamp_pole_name`,
            NULL AS `lamp_pole_sn`,
            NULL AS `lamp_pole_id`,
            `device_lamp_pole`.`install_time` AS `install_time`,
            `device_lamp_pole`.`create_time` AS `create_time`,
            `device_lamp_pole`.`is_deleted` AS `is_deleted`,
            `device_lamp_pole`.`tenant_id` AS `tenant_id`
        FROM
            `device_lamp_pole`
        UNION
        SELECT
            12 AS `device_type`,
            '抓拍单元' AS `device_type_name`,
            `a`.`id` AS `device_id`,
            `a`.`capture_name` AS `device_name`,
            `a`.`capture_sn` AS `sn`,
            `a`.`way_name` AS `address`,
            `pole`.`pole_name` AS `lamp_pole_name`,
            `pole`.`pole_sn` AS `lamp_pole_sn`,
            `a`.`lamp_pole_id` AS `lamp_pole_id`,
            `a`.`install_time` AS `install_time`,
            `a`.`create_time` AS `create_time`,
            `a`.`is_deleted` AS `is_deleted`,
            `a`.`tenant_id` AS `tenant_id`
        FROM
            (
                `device_capture_unit` `a`
                LEFT JOIN `device_lamp_pole` `pole` ON (
                    (
                        `pole`.`id` = `a`.`lamp_pole_id`
                    )
                )
            )
        UNION
        SELECT
            13 AS `device_type`,
            '雪水传感器' AS `device_type_name`,
            `b`.`id` AS `device_id`,
            `b`.`name` AS `device_name`,
            `b`.`sn` AS `sn`,
            NULL AS `address`,
            NULL AS `lamp_pole_name`,
            NULL AS `lamp_pole_sn`,
            NULL AS `lamp_pole_id`,
            `b`.`install_time` AS `install_time`,
            `b`.`create_time` AS `create_time`,
            `b`.`is_deleted` AS `is_deleted`,
            `b`.`tenant_id` AS `tenant_id`
        FROM
            `device_bridge_sensor` `b`
        UNION
        SELECT
            14 AS `device_type`,
            'IP音柱' AS `device_type_name`,
            `ipcast`.`id` AS `device_id`,
            `ipcast`.`cast_name` AS `device_name`,
            `ipcast`.`cast_sn` AS `sn`,
            `p`.`install_location` AS `address`,
            `p`.`pole_name` AS `lamp_pole_name`,
            `p`.`pole_sn` AS `lamp_pole_sn`,
            `p`.`id` AS `lamp_pole_id`,
            `ipcast`.`install_time` AS `install_time`,
            `ipcast`.`create_time` AS `create_time`,
            `ipcast`.`is_deleted` AS `is_deleted`,
            `ipcast`.`tenant_id` AS `tenant_id`
        FROM
            (
                `device_ip_broadcast` `ipcast`
                LEFT JOIN `device_lamp_pole` `p` ON (
                    (
                        `p`.`id` = `ipcast`.`lamp_pole_id`
                    )
                )
            )
        UNION
        SELECT
            15 AS `device_type`,
            '弯道传感器' AS `device_type_name`,
            `con`.`id` AS `device_id`,
            `con`.`sensor_name` AS `device_name`,
            `con`.`sensor_sn` AS `sn`,
            `pole`.`install_location` AS `address`,
            `pole`.`pole_name` AS `lamp_pole_name`,
            `pole`.`pole_sn` AS `lamp_pole_sn`,
            `con`.`lamp_pole_id` AS `lamp_pole_id`,
            `con`.`install_time` AS `install_time`,
            `con`.`create_time` AS `create_time`,
            `con`.`is_deleted` AS `is_deleted`,
            `con`.`tenant_id` AS `tenant_id`
        FROM
            (
                (
                    `device_curve_sensor` `con`
                    LEFT JOIN `device_lamp_pole` `pole` ON (
                        (
                            `con`.`lamp_pole_id` = `pole`.`id`
                        )
                    )
                )
                LEFT JOIN `device_wisdom_gateway` `gat` ON (
                    (
                        `con`.`gateway_id` = `gat`.`id`
                    )
                )
            )
    ) `tb`