317 lines
13 KiB
PHP
317 lines
13 KiB
PHP
<?php
|
||
/**
|
||
* 数据库安装脚本
|
||
* 访问此文件创建/更新数据库表
|
||
* 安装完成后请删除此文件
|
||
*/
|
||
|
||
// 数据库配置 - 根据你的 .env 文件配置
|
||
$host = '127.0.0.1';
|
||
$port = '3306';
|
||
$database = 'reminder';
|
||
$username = 'root';
|
||
$password = '';
|
||
|
||
// 尝试从 Laravel .env 文件读取配置
|
||
$envPath = __DIR__ . '/../.env';
|
||
if (file_exists($envPath)) {
|
||
$envContent = file_get_contents($envPath);
|
||
|
||
if (preg_match('/DB_HOST=(.*)/', $envContent, $matches)) {
|
||
$host = trim($matches[1]);
|
||
}
|
||
if (preg_match('/DB_PORT=(.*)/', $envContent, $matches)) {
|
||
$port = trim($matches[1]);
|
||
}
|
||
if (preg_match('/DB_DATABASE=(.*)/', $envContent, $matches)) {
|
||
$database = trim($matches[1]);
|
||
}
|
||
if (preg_match('/DB_USERNAME=(.*)/', $envContent, $matches)) {
|
||
$username = trim($matches[1]);
|
||
}
|
||
if (preg_match('/DB_PASSWORD=(.*)/', $envContent, $matches)) {
|
||
$password = trim($matches[1]);
|
||
}
|
||
}
|
||
|
||
$messages = [];
|
||
$error = '';
|
||
|
||
try {
|
||
$dsn = "mysql:host={$host};port={$port};dbname={$database};charset=utf8mb4";
|
||
$pdo = new PDO($dsn, $username, $password);
|
||
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
|
||
|
||
// 1. 创建 users 表
|
||
$sql = "
|
||
CREATE TABLE IF NOT EXISTS `users` (
|
||
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
||
`name` varchar(255) NOT NULL COMMENT '姓名',
|
||
`email` varchar(255) NOT NULL COMMENT '邮箱',
|
||
`email_verified_at` timestamp NULL DEFAULT NULL,
|
||
`password` varchar(255) NOT NULL COMMENT '密码',
|
||
`remember_token` varchar(100) DEFAULT NULL,
|
||
`created_at` timestamp NULL DEFAULT NULL,
|
||
`updated_at` timestamp NULL DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `users_email_unique` (`email`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
";
|
||
$pdo->exec($sql);
|
||
$messages[] = '✅ 用户表 users 创建/已存在';
|
||
|
||
// 2. 检查 patients 表是否存在
|
||
$stmt = $pdo->query("SHOW TABLES LIKE 'patients'");
|
||
$patientsTableExists = $stmt->rowCount() > 0;
|
||
|
||
if ($patientsTableExists) {
|
||
// 表已存在，检查是否有 user_id 字段
|
||
$stmt = $pdo->query("SHOW COLUMNS FROM `patients` LIKE 'user_id'");
|
||
$hasUserId = $stmt->rowCount() > 0;
|
||
|
||
if (!$hasUserId) {
|
||
// 检查是否有数据
|
||
$countStmt = $pdo->query("SELECT COUNT(*) FROM `patients`");
|
||
$count = $countStmt->fetchColumn();
|
||
|
||
if ($count > 0) {
|
||
// 有数据，需要先创建一个默认用户
|
||
// 检查是否已有用户
|
||
$userCountStmt = $pdo->query("SELECT COUNT(*) FROM `users`");
|
||
$userCount = $userCountStmt->fetchColumn();
|
||
|
||
if ($userCount == 0) {
|
||
// 创建默认管理员用户
|
||
$defaultPassword = password_hash('admin123', PASSWORD_BCRYPT);
|
||
$pdo->exec("INSERT INTO `users` (`name`, `email`, `password`, `created_at`, `updated_at`)
|
||
VALUES ('管理员', 'admin@example.com', '{$defaultPassword}', NOW(), NOW())");
|
||
$messages[] = '✅ 已创建默认用户: admin@example.com (密码: admin123)';
|
||
}
|
||
|
||
// 获取第一个用户的 ID
|
||
$firstUserStmt = $pdo->query("SELECT id FROM `users` ORDER BY id ASC LIMIT 1");
|
||
$firstUserId = $firstUserStmt->fetchColumn();
|
||
|
||
// 添加 user_id 字段（允许 NULL）
|
||
$pdo->exec("ALTER TABLE `patients` ADD COLUMN `user_id` bigint(20) unsigned NULL AFTER `id`");
|
||
$messages[] = '✅ 已添加 user_id 字段到 patients 表';
|
||
|
||
// 将现有数据关联到第一个用户
|
||
$pdo->exec("UPDATE `patients` SET `user_id` = {$firstUserId} WHERE `user_id` IS NULL");
|
||
$messages[] = "✅ 已将现有 {$count} 条患者数据关联到用户 ID: {$firstUserId}";
|
||
|
||
// 修改字段为 NOT NULL
|
||
$pdo->exec("ALTER TABLE `patients` MODIFY COLUMN `user_id` bigint(20) unsigned NOT NULL");
|
||
|
||
// 添加外键约束
|
||
try {
|
||
$pdo->exec("ALTER TABLE `patients` ADD KEY `patients_user_id_foreign` (`user_id`)");
|
||
$pdo->exec("ALTER TABLE `patients` ADD CONSTRAINT `patients_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE");
|
||
$messages[] = '✅ 已添加外键约束';
|
||
} catch (PDOException $e) {
|
||
// 外键可能已存在，忽略
|
||
$messages[] = '⚠️ 外键约束已存在或无法添加';
|
||
}
|
||
} else {
|
||
// 没有数据，直接添加字段和约束
|
||
$pdo->exec("ALTER TABLE `patients` ADD COLUMN `user_id` bigint(20) unsigned NOT NULL AFTER `id`");
|
||
try {
|
||
$pdo->exec("ALTER TABLE `patients` ADD KEY `patients_user_id_foreign` (`user_id`)");
|
||
$pdo->exec("ALTER TABLE `patients` ADD CONSTRAINT `patients_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE");
|
||
} catch (PDOException $e) {
|
||
// 忽略
|
||
}
|
||
$messages[] = '✅ 已添加 user_id 字段到 patients 表';
|
||
}
|
||
} else {
|
||
$messages[] = '✅ patients 表已有 user_id 字段';
|
||
}
|
||
} else {
|
||
// 创建新的 patients 表（包含 user_id）
|
||
$sql = "
|
||
CREATE TABLE `patients` (
|
||
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
||
`user_id` bigint(20) unsigned NOT NULL COMMENT '所属用户',
|
||
`name` varchar(255) NOT NULL COMMENT '姓名',
|
||
`gender` varchar(255) NOT NULL COMMENT '性别',
|
||
`age` int(11) NOT NULL COMMENT '年龄',
|
||
`diagnosis` varchar(255) NOT NULL COMMENT '出院诊断',
|
||
`discharge_date` date NOT NULL COMMENT '转诊时间',
|
||
`address` varchar(255) DEFAULT NULL COMMENT '户籍地址',
|
||
`phone` varchar(255) DEFAULT NULL COMMENT '联系方式',
|
||
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
|
||
`follow_up_count` int(11) NOT NULL DEFAULT 0 COMMENT '已随访次数',
|
||
`last_follow_up_date` date DEFAULT NULL COMMENT '上次随访日期',
|
||
`created_at` timestamp NULL DEFAULT NULL,
|
||
`updated_at` timestamp NULL DEFAULT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `patients_user_id_foreign` (`user_id`),
|
||
CONSTRAINT `patients_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
";
|
||
$pdo->exec($sql);
|
||
$messages[] = '✅ 患者表 patients 创建成功';
|
||
}
|
||
|
||
// 3. 创建 sessions 表
|
||
$sql = "
|
||
CREATE TABLE IF NOT EXISTS `sessions` (
|
||
`id` varchar(255) NOT NULL,
|
||
`user_id` bigint(20) unsigned DEFAULT NULL,
|
||
`ip_address` varchar(45) DEFAULT NULL,
|
||
`user_agent` text DEFAULT NULL,
|
||
`payload` longtext NOT NULL,
|
||
`last_activity` int(11) NOT NULL,
|
||
PRIMARY KEY (`id`),
|
||
KEY `sessions_user_id_index` (`user_id`),
|
||
KEY `sessions_last_activity_index` (`last_activity`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
";
|
||
$pdo->exec($sql);
|
||
$messages[] = '✅ 会话表 sessions 创建/已存在';
|
||
|
||
// 显示表结构
|
||
$stmt = $pdo->query("SHOW TABLES");
|
||
$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
|
||
$messages[] = '📋 当前数据库表: ' . implode(', ', $tables);
|
||
|
||
} catch (PDOException $e) {
|
||
$error = '❌ 数据库操作失败: ' . $e->getMessage();
|
||
}
|
||
?>
|
||
<!DOCTYPE html>
|
||
<html lang="zh-CN">
|
||
<head>
|
||
<meta charset="UTF-8">
|
||
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
||
<title>数据库安装 - 病例回访提醒系统</title>
|
||
<style>
|
||
* { margin: 0; padding: 0; box-sizing: border-box; }
|
||
body {
|
||
font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', 'Microsoft YaHei', sans-serif;
|
||
background: linear-gradient(135deg, #f0f4f8 0%, #e8f2ff 100%);
|
||
min-height: 100vh;
|
||
display: flex;
|
||
align-items: center;
|
||
justify-content: center;
|
||
padding: 20px;
|
||
}
|
||
.container {
|
||
background: #ffffff;
|
||
border-radius: 16px;
|
||
padding: 40px;
|
||
max-width: 650px;
|
||
width: 100%;
|
||
box-shadow: 0 20px 60px rgba(0, 102, 204, 0.15);
|
||
}
|
||
h1 {
|
||
color: #333;
|
||
font-size: 24px;
|
||
margin-bottom: 20px;
|
||
display: flex;
|
||
align-items: center;
|
||
gap: 12px;
|
||
}
|
||
.message {
|
||
padding: 12px 16px;
|
||
border-radius: 8px;
|
||
margin-bottom: 12px;
|
||
line-height: 1.6;
|
||
font-size: 14px;
|
||
}
|
||
.success {
|
||
background: rgba(16, 185, 129, 0.1);
|
||
border: 1px solid #10b981;
|
||
color: #059669;
|
||
}
|
||
.error {
|
||
background: rgba(239, 68, 68, 0.1);
|
||
border: 1px solid #ef4444;
|
||
color: #ef4444;
|
||
}
|
||
.warning {
|
||
background: rgba(245, 158, 11, 0.1);
|
||
border: 1px solid #f59e0b;
|
||
color: #d97706;
|
||
}
|
||
.info {
|
||
background: rgba(99, 102, 241, 0.1);
|
||
border: 1px solid #6366f1;
|
||
color: #666;
|
||
}
|
||
.info strong { color: #333; }
|
||
.btn {
|
||
display: inline-block;
|
||
padding: 12px 24px;
|
||
background: linear-gradient(135deg, #6366f1 0%, #8b5cf6 100%);
|
||
color: white;
|
||
text-decoration: none;
|
||
border-radius: 8px;
|
||
font-weight: 500;
|
||
transition: all 0.2s;
|
||
margin-top: 10px;
|
||
margin-right: 10px;
|
||
}
|
||
.btn:hover {
|
||
transform: translateY(-2px);
|
||
box-shadow: 0 4px 12px rgba(99, 102, 241, 0.4);
|
||
}
|
||
.btn-secondary {
|
||
background: linear-gradient(135deg, #10b981 0%, #059669 100%);
|
||
}
|
||
.config {
|
||
background: #f8fafc;
|
||
border-radius: 8px;
|
||
padding: 16px;
|
||
margin: 20px 0;
|
||
font-family: monospace;
|
||
font-size: 13px;
|
||
color: #666;
|
||
border: 1px solid #e2e8f0;
|
||
}
|
||
.config div { margin: 4px 0; }
|
||
.config span { color: #6366f1; }
|
||
</style>
|
||
</head>
|
||
<body>
|
||
<div class="container">
|
||
<h1>🏥 数据库安装/升级</h1>
|
||
|
||
<?php if (!empty($messages)): ?>
|
||
<?php foreach ($messages as $msg): ?>
|
||
<?php
|
||
$class = 'success';
|
||
if (strpos($msg, '⚠️') !== false) $class = 'warning';
|
||
if (strpos($msg, '📋') !== false) $class = 'info';
|
||
?>
|
||
<div class="message <?= $class ?>"><?= $msg ?></div>
|
||
<?php endforeach; ?>
|
||
<div class="message info">
|
||
<strong>⚠️ 安全提示：</strong>安装完成后，请删除此文件 (install.php) 以确保系统安全。
|
||
</div>
|
||
<div>
|
||
<a href="/login" class="btn">进入登录页 →</a>
|
||
<a href="/register" class="btn btn-secondary">注册新用户 →</a>
|
||
</div>
|
||
<?php endif; ?>
|
||
|
||
<?php if ($error): ?>
|
||
<div class="message error"><?= htmlspecialchars($error) ?></div>
|
||
<div class="config">
|
||
<div>当前数据库配置：</div>
|
||
<div>Host: <span><?= htmlspecialchars($host) ?></span></div>
|
||
<div>Port: <span><?= htmlspecialchars($port) ?></span></div>
|
||
<div>Database: <span><?= htmlspecialchars($database) ?></span></div>
|
||
<div>Username: <span><?= htmlspecialchars($username) ?></span></div>
|
||
</div>
|
||
<div class="message info">
|
||
<strong>解决方法：</strong><br>
|
||
1. 确保 MySQL 服务已启动<br>
|
||
2. 确保数据库 "<?= htmlspecialchars($database) ?>" 已创建<br>
|
||
3. 检查 .env 文件中的数据库配置是否正确
|
||
</div>
|
||
<?php endif; ?>
|
||
</div>
|
||
</body>
|
||
</html>
|