初始化数据库表结构
(self)
| 40 | return pymysql.connect(**self.config) |
| 41 | |
| 42 | def init_database(self): |
| 43 | """初始化数据库表结构""" |
| 44 | conn = self.get_connection() |
| 45 | cursor = conn.cursor() |
| 46 | |
| 47 | try: |
| 48 | # 用户表 |
| 49 | cursor.execute( |
| 50 | """ |
| 51 | CREATE TABLE IF NOT EXISTS users ( |
| 52 | user_id BIGINT PRIMARY KEY, |
| 53 | username VARCHAR(255), |
| 54 | full_name VARCHAR(255), |
| 55 | balance INT DEFAULT 1, |
| 56 | is_blocked TINYINT(1) DEFAULT 0, |
| 57 | invited_by BIGINT, |
| 58 | created_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 59 | last_checkin DATETIME NULL, |
| 60 | INDEX idx_username (username), |
| 61 | INDEX idx_invited_by (invited_by) |
| 62 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
| 63 | """ |
| 64 | ) |
| 65 | |
| 66 | # 邀请记录表 |
| 67 | cursor.execute( |
| 68 | """ |
| 69 | CREATE TABLE IF NOT EXISTS invitations ( |
| 70 | id INT AUTO_INCREMENT PRIMARY KEY, |
| 71 | inviter_id BIGINT NOT NULL, |
| 72 | invitee_id BIGINT NOT NULL, |
| 73 | created_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 74 | INDEX idx_inviter (inviter_id), |
| 75 | INDEX idx_invitee (invitee_id), |
| 76 | FOREIGN KEY (inviter_id) REFERENCES users(user_id), |
| 77 | FOREIGN KEY (invitee_id) REFERENCES users(user_id) |
| 78 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
| 79 | """ |
| 80 | ) |
| 81 | |
| 82 | # 验证记录表 |
| 83 | cursor.execute( |
| 84 | """ |
| 85 | CREATE TABLE IF NOT EXISTS verifications ( |
| 86 | id INT AUTO_INCREMENT PRIMARY KEY, |
| 87 | user_id BIGINT NOT NULL, |
| 88 | verification_type VARCHAR(50) NOT NULL, |
| 89 | verification_url TEXT, |
| 90 | verification_id VARCHAR(255), |
| 91 | status VARCHAR(50) NOT NULL, |
| 92 | result TEXT, |
| 93 | created_at DATETIME DEFAULT CURRENT_TIMESTAMP, |
| 94 | INDEX idx_user_id (user_id), |
| 95 | INDEX idx_type (verification_type), |
| 96 | INDEX idx_created (created_at), |
| 97 | FOREIGN KEY (user_id) REFERENCES users(user_id) |
| 98 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
| 99 | """ |
no test coverage detected