WaterServer/配置代码.txt

sudo systemctl start mysqld
sudo systemctl start sshd
sudo systemctl start accessserver.service
sudo systemctl start x11vnc
nohup python3.8 AccessServer.py &
nohup x11vnc -auth guess -forever -loop -noxdamage -repeat -rfbauth HelloWorld123 -rfbport 5900 -shared &









###########补元传感器表###########
CREATE TABLE IF NOT EXISTS sensor_data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  SampleTime DATETIME NOT NULL,
  RecvTime DATETIME NOT NULL,
  NodeName VARCHAR(255) NOT NULL,
  NodeValue FLOAT NOT NULL,
  NodeType VARCHAR(255),
  NodeUnit VARCHAR(255),
  DeviceID INT NOT NULL,
  ChannelID INT NOT NULL,
  ProjectID INT NOT NULL
);

ALTER TABLE sensor_data ADD INDEX idx_recvtime_deviceid_channelid (RecvTime, DeviceID, ChannelID);

ALTER DATABASE water CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE sensor_data CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


###########大事件表###############

CREATE TABLE IF NOT EXISTS project_milestones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year YEAR NOT NULL,
    date DATE NOT NULL,
    title VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    pos VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


#############一张图表################
CREATE TABLE ProjectTypes (     id INT AUTO_INCREMENT PRIMARY KEY,     type_name VARCHAR(255) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE WaterManagementProjects (     id INT AUTO_INCREMENT PRIMARY KEY,     special BOOLEAN,     type INT,     longitude DECIMAL(10, 6),     latitude DECIMAL(10, 6),     name VARCHAR(255),     name_pri VARCHAR(255),     text1 TEXT,     text2 TEXT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE `YangtzeRiverWaterLevels` (
  `location` VARCHAR(255) NOT NULL,
  `flood_protection_level` FLOAT NOT NULL,
  `warning_level` FLOAT NOT NULL,
  `guarantee_level` FLOAT NOT NULL,
  `remarks` TEXT,
  PRIMARY KEY (`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `YangtzeRiverWaterLevels` (`location`, `flood_protection_level`, `warning_level`, `guarantee_level`, `remarks`) VALUES
('螺山', 31.00, 32.00, 34.01, NULL),
('新堤', 30.50, 31.50, 33.59, NULL),
('套口', 29.50, 30.50, 32.73, NULL),
('补元', 28.50, 29.50, 31.84, '补元退洪闸管理所闸底高程24.00米为黄海高程，加上水尺读数0.00m，再加2.02m就为吴淞高程。');