Sākums Izpētīt Palīdzība
Pierakstīties
oldwine
/
BarLine1Sizing
1
0
Atdalīts 0
Faili Problēmas 0 Izmaiņu pieprasījumi 0 Vikivietne
Atzars: master
Atzari Tagi
BarLine1Sizing
/
sql
/
steel_making.sql

steel_making.sql 8.9 KB
Patstāvīgā saite Vēsture Neapstrādāts

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133 
  1. -- 新建炼钢数据采集数据库
    2. 

  2. CREATE DATABASE IF NOT EXISTS steelmaking_data DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    3. 

  3. USE steelmaking_data;
    4. 

  4. 

  5. -- 创建炼钢数据点表
    6. 

  6. CREATE TABLE data_points (
    7. 

  7.     id INT AUTO_INCREMENT PRIMARY KEY,
    8. 

  8.     ip_address VARCHAR(20) NOT NULL COMMENT 'PLC网络地址',
    9. 

  9.     name VARCHAR(100) NOT NULL COMMENT '数据点名称',
    10. 

  10.     type VARCHAR(20) NOT NULL COMMENT '数据类型(bool/int/dint/real)',
    11. 

  11.     db_number INT NOT NULL COMMENT 'DB块号',
    12. 

  12.     start_offset INT NOT NULL COMMENT '起始偏移量',
    13. 

  13.     bit_offset INT NOT NULL COMMENT '位偏移量(仅bool类型有意义)',
    14. 

  14.     size INT NOT NULL COMMENT '数据大小(字节)',
    15. 

  15.     read_allow BOOLEAN NOT NULL COMMENT '是否允许读取',
    16. 

  16.     write_allow BOOLEAN NOT NULL COMMENT '是否允许写入',
    17. 

  17.     frequency INT NOT NULL COMMENT '采集频率(ms)',
    18. 

  18.     group_id INT NOT NULL COMMENT '组别',
    19. 

  19.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    20. 

  20.     UNIQUE KEY (ip_address, name)
    21. 

  21. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='炼钢数据点配置表';
    22. 

  22. 

  23. -- 创建炼钢实时数据表
    24. 

  24. CREATE TABLE realtime_data (
    25. 

  25.     id BIGINT AUTO_INCREMENT PRIMARY KEY,
    26. 

  26.     point_id INT NOT NULL UNIQUE COMMENT '数据点ID',
    27. 

  27.     bool_value BOOLEAN NULL COMMENT '布尔值',
    28. 

  28.     int_value INT NULL COMMENT '整数值',
    29. 

  29.     real_value FLOAT NULL COMMENT '实数值',
    30. 

  30.     timestamp TIMESTAMP(3) NOT NULL COMMENT '数据时间戳(毫秒精度)',
    31. 

  31.     update_time TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '最后更新时间(毫秒精度)',
    32. 

  32.     FOREIGN KEY (point_id) REFERENCES data_points(id)
    33. 

  33. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='炼钢实时数据表';
    34. 

  34. 

  35. -- 创建炼钢历史数据表
    36. 

  36. CREATE TABLE historical_data (
    37. 

  37.     id BIGINT AUTO_INCREMENT PRIMARY KEY,
    38. 

  38.     point_id INT NOT NULL COMMENT '数据点ID',
    39. 

  39.     bool_value BOOLEAN NULL COMMENT '布尔值',
    40. 

  40.     int_value INT NULL COMMENT '整数值',
    41. 

  41.     real_value FLOAT NULL COMMENT '实数值',
    42. 

  42.     timestamp TIMESTAMP(3) NOT NULL COMMENT '数据时间戳(毫秒精度)',
    43. 

  43.     create_time TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '数据创建时间(毫秒精度)',
    44. 

  44.     FOREIGN KEY (point_id) REFERENCES data_points(id),
    45. 

  45.     INDEX (point_id, timestamp)
    46. 

  46. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='炼钢历史数据表';
    47. 

  47. 

  48. -- 插入炼钢PLC 172.16.1.20的数据点配置
    49. 

  49. INSERT INTO data_points (ip_address, name, type, db_number, start_offset, bit_offset, size, read_allow, write_allow, frequency, group_id) VALUES
    50. 

  50. ('172.16.1.20', '中间包连续测温温度', 'dint', 7, 4, 0, 4, TRUE, FALSE, 500, 1),
    51. 

  51. ('172.16.1.20', '中间包手动测温', 'dint', 4, 30, 0, 4, TRUE, FALSE, 500, 1),
    52. 

  52. ('172.16.1.20', '1流结晶器拉速', 'real', 6, 36, 0, 4, TRUE, FALSE, 500, 1),
    53. 

  53. ('172.16.1.20', '1流结晶器通钢量', 'real', 15, 0, 0, 4, TRUE, FALSE, 500, 1),
    54. 

  54. ('172.16.1.20', '2流结晶器拉速', 'real', 6, 40, 0, 4, TRUE, FALSE, 500, 1),
    55. 

  55. ('172.16.1.20', '2流结晶器通钢量', 'real', 15, 4, 0, 4, TRUE, FALSE, 500, 1),
    56. 

  56. ('172.16.1.20', '3流结晶器拉速', 'real', 6, 44, 0, 4, TRUE, FALSE, 500, 1),
    57. 

  57. ('172.16.1.20', '3流结晶器通钢量', 'real', 15, 8, 0, 4, TRUE, FALSE, 500, 1),
    58. 

  58. ('172.16.1.20', '4流结晶器拉速', 'real', 6, 48, 0, 4, TRUE, FALSE, 500, 1),
    59. 

  59. ('172.16.1.20', '4流结晶器通钢量', 'real', 15, 12, 0, 4, TRUE, FALSE, 500, 1),
    60. 

  60. ('172.16.1.20', '5流结晶器拉速', 'real', 6, 52, 0, 4, TRUE, FALSE, 500, 1),
    61. 

  61. ('172.16.1.20', '5流结晶器通钢量', 'real', 15, 16, 0, 4, TRUE, FALSE, 500, 1),
    62. 

  62. ('172.16.1.20', '6流结晶器拉速', 'real', 6, 56, 0, 4, TRUE, FALSE, 500, 1),
    63. 

  63. ('172.16.1.20', '6流结晶器通钢量', 'real', 15, 20, 0, 4, TRUE, FALSE, 500, 1),
    64. 

  64. ('172.16.1.20', '7流结晶器拉速', 'real', 6, 60, 0, 4, TRUE, FALSE, 500, 1),
    65. 

  65. ('172.16.1.20', '7流结晶器通钢量', 'real', 15, 24, 0, 4, TRUE, FALSE, 500, 1),
    66. 

  66. ('172.16.1.20', '8流结晶器拉速', 'real', 6, 64, 0, 4, TRUE, FALSE, 500, 1),
    67. 

  67. ('172.16.1.20', '8流结晶器通钢量', 'real', 15, 28, 0, 4, TRUE, FALSE, 500, 1),
    68. 

  68. ('172.16.1.20', '1流定尺', 'real', 6, 72, 0, 4, TRUE, FALSE, 500, 1),
    69. 

  69. ('172.16.1.20', '2流定尺', 'real', 6, 76, 0, 4, TRUE, FALSE, 500, 2),
    70. 

  70. ('172.16.1.20', '3流定尺', 'real', 6, 80, 0, 4, TRUE, FALSE, 500, 2),
    71. 

  71. ('172.16.1.20', '4流定尺', 'real', 6, 84, 0, 4, TRUE, FALSE, 500, 2),
    72. 

  72. ('172.16.1.20', '5流定尺', 'real', 6, 88, 0, 4, TRUE, FALSE, 500, 2),
    73. 

  73. ('172.16.1.20', '6流定尺', 'real', 6, 92, 0, 4, TRUE, FALSE, 500, 2),
    74. 

  74. ('172.16.1.20', '7流定尺', 'real', 6, 96, 0, 4, TRUE, FALSE, 500, 2),
    75. 

  75. ('172.16.1.20', '8流定尺', 'real', 6, 100, 0, 4, TRUE, FALSE, 500, 2);
    76. 

  76. 

  77. -- 插入炼钢PLC 172.16.1.21的数据点配置
    78. 

  78. INSERT INTO data_points (ip_address, name, type, db_number, start_offset, bit_offset, size, read_allow, write_allow, frequency, group_id) VALUES
    79. 

  79. ('172.16.1.21', '5#结晶器流量', 'real', 16, 232, 0, 4, TRUE, FALSE, 500, 1),
    80. 

  80. ('172.16.1.21', '5#结晶器水温差', 'real', 16, 236, 0, 4, TRUE, FALSE, 500, 1),
    81. 

  81. ('172.16.1.21', '5#二冷水总管压力', 'real', 16, 240, 0, 4, TRUE, FALSE, 500, 1),
    82. 

  82. ('172.16.1.21', '5#结晶器进水温度', 'real', 16, 244, 0, 4, TRUE, FALSE, 500, 1),
    83. 

  83. ('172.16.1.21', '5#结晶器水压', 'real', 16, 248, 0, 4, TRUE, FALSE, 500, 1),
    84. 

  84. ('172.16.1.21', '5#二冷水总管温度', 'real', 16, 252, 0, 4, TRUE, FALSE, 500, 1),
    85. 

  85. ('172.16.1.21', '5#水流量-1流-1段', 'real', 16, 0, 0, 4, TRUE, FALSE, 500, 1),
    86. 

  86. ('172.16.1.21', '5#水流量-1流-2段', 'real', 16, 4, 0, 4, TRUE, FALSE, 500, 1),
    87. 

  87. ('172.16.1.21', '5#水流量-1流-3段', 'real', 16, 8, 0, 4, TRUE, FALSE, 500, 1),
    88. 

  88. ('172.16.1.21', '5#水流量-1流-4段', 'real', 16, 12, 0, 4, TRUE, FALSE, 500, 1),
    89. 

  89. ('172.16.1.21', '5#水流量-1流-5段', 'real', 16, 16, 0, 4, TRUE, FALSE, 500, 1),
    90. 

  90. ('172.16.1.21', '5#水流量-2流-1段', 'real', 16, 20, 0, 4, TRUE, FALSE, 500, 1),
    91. 

  91. ('172.16.1.21', '5#水流量-2流-2段', 'real', 16, 24, 0, 4, TRUE, FALSE, 500, 1),
    92. 

  92. ('172.16.1.21', '5#水流量-2流-3段', 'real', 16, 28, 0, 4, TRUE, FALSE, 500, 1),
    93. 

  93. ('172.16.1.21', '5#水流量-2流-4段', 'real', 16, 32, 0, 4, TRUE, FALSE, 500, 1),
    94. 

  94. ('172.16.1.21', '5#水流量-2流-5段', 'real', 16, 36, 0, 4, TRUE, FALSE, 500, 1),
    95. 

  95. ('172.16.1.21', '5#水流量-3流-1段', 'real', 16, 40, 0, 4, TRUE, FALSE, 500, 1),
    96. 

  96. ('172.16.1.21', '5#水流量-3流-2段', 'real', 16, 44, 0, 4, TRUE, FALSE, 500, 1),
    97. 

  97. ('172.16.1.21', '5#水流量-3流-3段', 'real', 16, 48, 0, 4, TRUE, FALSE, 500, 1),
    98. 

  98. ('172.16.1.21', '5#水流量-3流-4段', 'real', 16, 52, 0, 4, TRUE, FALSE, 500, 2),
    99. 

  99. ('172.16.1.21', '5#水流量-3流-5段', 'real', 16, 56, 0, 4, TRUE, FALSE, 500, 2),
    100. 

  100. ('172.16.1.21', '5#水流量-4流-1段', 'real', 16, 60, 0, 4, TRUE, FALSE, 500, 2),
    101. 

  101. ('172.16.1.21', '5#水流量-4流-2段', 'real', 16, 64, 0, 4, TRUE, FALSE, 500, 2),
    102. 

  102. ('172.16.1.21', '5#水流量-4流-3段', 'real', 16, 68, 0, 4, TRUE, FALSE, 500, 2),
    103. 

  103. ('172.16.1.21', '5#水流量-4流-4段', 'real', 16, 72, 0, 4, TRUE, FALSE, 500, 2),
    104. 

  104. ('172.16.1.21', '5#水流量-4流-5段', 'real', 16, 76, 0, 4, TRUE, FALSE, 500, 2),
    105. 

  105. ('172.16.1.21', '5#水流量-5流-1段', 'real', 16, 80, 0, 4, TRUE, FALSE, 500, 2),
    106. 

  106. ('172.16.1.21', '5#水流量-5流-2段', 'real', 16, 84, 0, 4, TRUE, FALSE, 500, 2),
    107. 

  107. ('172.16.1.21', '5#水流量-5流-3段', 'real', 16, 88, 0, 4, TRUE, FALSE, 500, 2),
    108. 

  108. ('172.16.1.21', '5#水流量-5流-4段', 'real', 16, 92, 0, 4, TRUE, FALSE, 500, 2),
    109. 

  109. ('172.16.1.21', '5#水流量-5流-5段', 'real', 16, 96, 0, 4, TRUE, FALSE, 500, 2),
    110. 

  110. ('172.16.1.21', '5#水流量-6流-1段', 'real', 16, 100, 0, 4, TRUE, FALSE, 500, 2),
    111. 

  111. ('172.16.1.21', '5#水流量-6流-2段', 'real', 16, 104, 0, 4, TRUE, FALSE, 500, 2),
    112. 

  112. ('172.16.1.21', '5#水流量-6流-3段', 'real', 16, 108, 0, 4, TRUE, FALSE, 500, 2),
    113. 

  113. ('172.16.1.21', '5#水流量-6流-4段', 'real', 16, 112, 0, 4, TRUE, FALSE, 500, 2),
    114. 

  114. ('172.16.1.21', '5#水流量-6流-5段', 'real', 16, 116, 0, 4, TRUE, FALSE, 500, 2),
    115. 

  115. ('172.16.1.21', '5#水流量-7流-1段', 'real', 16, 120, 0, 4, TRUE, FALSE, 500, 2),
    116. 

  116. ('172.16.1.21', '5#水流量-7流-2段', 'real', 16, 124, 0, 4, TRUE, FALSE, 500, 2),
    117. 

  117. ('172.16.1.21', '5#水流量-7流-3段', 'real', 16, 128, 0, 4, TRUE, FALSE, 500, 3),
    118. 

  118. ('172.16.1.21', '5#水流量-7流-4段', 'real', 16, 132, 0, 4, TRUE, FALSE, 500, 3),
    119. 

  119. ('172.16.1.21', '5#水流量-7流-5段', 'real', 16, 136, 0, 4, TRUE, FALSE, 500, 3),
    120. 

  120. ('172.16.1.21', '5#水流量-8流-1段', 'real', 16, 140, 0, 4, TRUE, FALSE, 500, 3),
    121. 

  121. ('172.16.1.21', '5#水流量-8流-2段', 'real', 16, 144, 0, 4, TRUE, FALSE, 500, 3),
    122. 

  122. ('172.16.1.21', '5#水流量-8流-3段', 'real', 16, 148, 0, 4, TRUE, FALSE, 500, 3),
    123. 

  123. ('172.16.1.21', '5#水流量-8流-4段', 'real', 16, 152, 0, 4, TRUE, FALSE, 500, 3),
    124. 

  124. ('172.16.1.21', '5#水流量-8流-5段', 'real', 16, 156, 0, 4, TRUE, FALSE, 500, 3);
    125. 

  125. 

  126. -- 初始化炼钢实时数据表
    127. 

  127. -- dint类型数据点
    128. 

  128. INSERT INTO realtime_data (point_id, int_value, timestamp)
    129. 

  129. SELECT id, 0, NOW(3) FROM data_points WHERE type = 'dint';
    130. 

  130. 

  131. -- real类型数据点
    132. 

  132. INSERT INTO realtime_data (point_id, real_value, timestamp)
    133. 

  133. SELECT id, 0.0, NOW(3) FROM data_points WHERE type = 'real';
    134.