61 lines
1.9 KiB
Python
61 lines
1.9 KiB
Python
#!/usr/bin/env python3
|
|
"""
|
|
创建files表的脚本
|
|
"""
|
|
|
|
from sqlalchemy import create_engine, text
|
|
import os
|
|
|
|
# 数据库配置
|
|
DATABASE_URL = os.getenv("DATABASE_URL", "mysql+pymysql://mytest_db:mytest_db@101.126.85.76:3306/mytest_db")
|
|
|
|
def create_files_table():
|
|
"""创建files表"""
|
|
engine = create_engine(DATABASE_URL)
|
|
|
|
# 创建files表的SQL语句
|
|
create_table_sql = """
|
|
CREATE TABLE IF NOT EXISTS files (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
user_id INT NOT NULL,
|
|
filename VARCHAR(255) NOT NULL,
|
|
original_filename VARCHAR(255) NOT NULL,
|
|
file_path VARCHAR(500) NOT NULL,
|
|
file_size BIGINT NOT NULL,
|
|
mime_type VARCHAR(100) NOT NULL,
|
|
file_hash VARCHAR(64) NOT NULL,
|
|
is_public BOOLEAN DEFAULT FALSE,
|
|
download_count BIGINT DEFAULT 0,
|
|
description TEXT,
|
|
tags TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
last_accessed_at TIMESTAMP NULL,
|
|
INDEX idx_user_id (user_id),
|
|
INDEX idx_filename (filename),
|
|
INDEX idx_file_hash (file_hash),
|
|
INDEX idx_created_at (created_at),
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
"""
|
|
|
|
try:
|
|
with engine.connect() as connection:
|
|
# 执行创建表语句
|
|
connection.execute(text(create_table_sql))
|
|
connection.commit()
|
|
print("files表创建成功")
|
|
|
|
# 检查表是否创建成功
|
|
result = connection.execute(text("SHOW TABLES LIKE 'files'"))
|
|
if result.fetchone():
|
|
print("files表验证成功")
|
|
else:
|
|
print("files表验证失败")
|
|
|
|
except Exception as e:
|
|
print(f"创建files表失败: {e}")
|
|
raise
|
|
|
|
if __name__ == "__main__":
|
|
create_files_table() |