89 lines
3.3 KiB
SQL
89 lines
3.3 KiB
SQL
CREATE DATABASE IF NOT EXISTS webgis_spbu
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
USE webgis_spbu;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
role ENUM('admin', 'publik') NOT NULL DEFAULT 'publik',
|
|
full_name VARCHAR(150) NOT NULL,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS spbu (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
no_spbu VARCHAR(100) NOT NULL,
|
|
status VARCHAR(50) NOT NULL,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS points (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
kode_point VARCHAR(100) NOT NULL,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS jalan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
status VARCHAR(50) NOT NULL,
|
|
panjang DOUBLE DEFAULT 0,
|
|
geom LONGTEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS parsil (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
status VARCHAR(50) NOT NULL,
|
|
luas DOUBLE DEFAULT 0,
|
|
geom LONGTEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS masjid (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
pic VARCHAR(150) NOT NULL,
|
|
radius DOUBLE DEFAULT 500,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
radius_layanan DOUBLE DEFAULT 500,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS penduduk_miskin (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama_kk VARCHAR(150) NOT NULL,
|
|
jumlah_anggota INT DEFAULT 1,
|
|
alamat TEXT NOT NULL,
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
penghasilan VARCHAR(50),
|
|
kondisi_rumah VARCHAR(50),
|
|
sumber_air VARCHAR(50),
|
|
skor_kerentanan DOUBLE DEFAULT 0,
|
|
jenis_bantuan VARCHAR(100),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT INTO users (username, password_hash, role, full_name, is_active) VALUES
|
|
('admin', '$2y$10$X/2pN5ag6A/SmFlm/bZJq.V1Ul41mm3lX1PNDY0ZBncinKhEnQvpO', 'admin', 'Administrator WebGIS', 1),
|
|
('publik', '$2y$10$X/2pN5ag6A/SmFlm/bZJq.V1Ul41mm3lX1PNDY0ZBncinKhEnQvpO', 'publik', 'Pengguna Publik', 1)
|
|
ON DUPLICATE KEY UPDATE
|
|
password_hash = VALUES(password_hash),
|
|
role = VALUES(role),
|
|
full_name = VALUES(full_name),
|
|
is_active = VALUES(is_active);
|