DepSeek File Structure - v0 by Vercel

then give me a database for it

355d ago

DepSeek Database Schema

Here's the complete SQL script to create the database schema for the DepSeek cloud storage system:

-- -----------------------------------------------------
-- Schema depseek
-- -----------------------------------------------------
CREATE DATABASE IF NOT EXISTS `depseek` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `depseek`;

-- -----------------------------------------------------
-- Table `users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('user', 'admin') NOT NULL DEFAULT 'user',
  `status` ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'inactive',
  `email_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `verification_token` VARCHAR(255) NULL,
  `storage_limit` BIGINT NOT NULL DEFAULT 1073741824, -- 1GB default
  `profile_image` VARCHAR(255) NULL,
  `last_login` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `folders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `folders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `parent_id` INT NULL,
  `folder_name` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_folders_users_idx` (`user_id` ASC),
  INDEX `fk_folders_folders_idx` (`parent_id` ASC),
  CONSTRAINT `fk_folders_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_folders_folders`
    FOREIGN KEY (`parent_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `files`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `files` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `folder_id` INT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_size` BIGINT NOT NULL,
  `file_type` VARCHAR(50) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(100) NOT NULL,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `encryption_key` VARCHAR(255) NULL,
  `checksum` VARCHAR(64) NULL,
  `version` INT NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_files_users_idx` (`user_id` ASC),
  INDEX `fk_files_folders_idx` (`folder_id` ASC),
  CONSTRAINT `fk_files_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_files_folders`
    FOREIGN KEY (`folder_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `file_shares`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `file_shares` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `shared_by` INT NOT NULL,
  `shared_with` INT NOT NULL,
  `permission_level` ENUM('view', 'edit') NOT NULL DEFAULT 'view',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_file_shares_files_idx` (`file_id` ASC),
  INDEX `fk_file_shares_users1_idx` (`shared_by` ASC),
  INDEX `fk_file_shares_users2_idx` (`shared_with` ASC),
  CONSTRAINT `fk_file_shares_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users1`
    FOREIGN KEY (`shared_by`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users2`
    FOREIGN KEY (`shared_with`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `public_links`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `public_links` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `download_count` INT NOT NULL DEFAULT 0,
  `max_downloads` INT NULL,
  `password` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL,
  `expires_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `token_UNIQUE` (`token` ASC),
  INDEX `fk_public_links_files_idx` (`file_id` ASC),
  INDEX `fk_public_links_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_public_links_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_public_links_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `file_versions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `file_versions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `version` INT NOT NULL,
  `file_size` BIGINT NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `encryption_key` VARCHAR(255) NULL,
  `checksum` VARCHAR(64) NULL,
  `created_by` INT NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_file_versions_files_idx` (`file_id` ASC),
  INDEX `fk_file_versions_users_idx` (`created_by` ASC),
  CONSTRAINT `fk_file_versions_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_versions_users`
    FOREIGN KEY (`created_by`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `user_activities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_activities` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `activity_type` VARCHAR(50) NOT NULL,
  `description` TEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_user_activities_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_user_activities_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `sessions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` VARCHAR(128) NOT NULL,
  `user_id` INT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `payload` TEXT NOT NULL,
  `last_activity` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_sessions_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_sessions_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `password_resets`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(100) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `expires_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `email_index` (`email` ASC)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `settings`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `setting_key_UNIQUE` (`setting_key` ASC)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `user_settings`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `user_setting_unique` (`user_id` ASC, `setting_key` ASC),
  CONSTRAINT `fk_user_settings_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Insert default admin user
-- -----------------------------------------------------
INSERT INTO `users` (
  `first_name`, 
  `last_name`, 
  `email`, 
  `password`, 
  `role`, 
  `status`, 
  `email_verified`, 
  `storage_limit`, 
  `created_at`
) VALUES (
  'Admin', 
  'User', 
  'admin@depseek.com', 
  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- password: password
  'admin', 
  'active', 
  1, 
  5368709120, -- 5GB
  NOW()
);

-- -----------------------------------------------------
-- Insert default settings
-- -----------------------------------------------------
INSERT INTO `settings` (`setting_key`, `setting_value`, `created_at`) VALUES
('site_name', 'DepSeek', NOW()),
('site_description', 'Secure Cloud Storage System', NOW()),
('max_upload_size', '104857600', NOW()), -- 100MB
('allowed_file_types', 'jpg,jpeg,png,gif,pdf,doc,docx,xls,xlsx,ppt,pptx,txt,zip,rar,mp3,mp4,avi,mov', NOW()),
('default_storage_limit', '1073741824', NOW()), -- 1GB
('enable_registration', '1', NOW()),
('enable_email_verification', '1', NOW()),
('enable_file_encryption', '1', NOW()),
('maintenance_mode', '0', NOW()),
('version', '1.0.0', NOW());

-- -----------------------------------------------------
-- Create triggers
-- -----------------------------------------------------

-- Update timestamps on user update
DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- Update timestamps on folder update
DELIMITER //
CREATE TRIGGER before_folder_update
BEFORE UPDATE ON folders
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- Update timestamps on file update
DELIMITER //
CREATE TRIGGER before_file_update
BEFORE UPDATE ON files
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- Update timestamps on file_shares update
DELIMITER //
CREATE TRIGGER before_file_share_update
BEFORE UPDATE ON file_shares
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- Update timestamps on settings update
DELIMITER //
CREATE TRIGGER before_setting_update
BEFORE UPDATE ON settings
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- Update timestamps on user_settings update
DELIMITER //
CREATE TRIGGER before_user_setting_update
BEFORE UPDATE ON user_settings
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

Database Schema Explanation

This schema includes all the necessary tables for the DepSeek cloud storage system:

Core Tables

users - Stores user account information
- Basic user details (name, email, password)
- Account status and verification
- Storage limits
- Timestamps for creation, updates, and soft deletion
folders - Manages folder structure
- Hierarchical structure with parent-child relationships
- User ownership
- Soft deletion support
files - Stores file metadata
- File details (name, size, type, path)
- Encryption information
- Version tracking
- Soft deletion support
file_shares - Manages file sharing between users
- Permission levels (view/edit)
- Expiration dates
- Tracking who shared with whom

Additional Features

public_links - For generating public download links
- Secure tokens
- Download limits and tracking
- Optional password protection
- Expiration dates
file_versions - For version control of files
- Tracks all versions of a file
- Stores metadata for each version
- Records who created each version

System Tables

user_activities - Audit log of user actions
- Tracks all user activities
- Records IP addresses and user agents
- Timestamps for when activities occurred
sessions - Manages user sessions
- Secure session handling
- Tracks session data
- Records IP addresses and user agents
password_resets - Handles password reset requests
- Secure tokens
- Expiration dates
settings - Global application settings
- Configurable system parameters
- Version information
user_settings - User-specific settings
- Personalization options
- User preferences

Database Features

Foreign Key Constraints - Ensures data integrity
Indexes - Optimizes query performance
Triggers - Automatically updates timestamps
Default Values - Sets sensible defaults
Enumerations - Restricts values to valid options

How to Import

To import this database schema:

Create a new file named depseek_schema.sql and paste the SQL code above
Use the MySQL command line:
mysql -u username -p depseek < depseek_schema.sql
Or use a tool like phpMyAdmin:
- Create a new database named depseek
- Go to the "Import" tab
- Select the depseek_schema.sql file
- Click "Go" to import

Default Credentials

After importing, you can log in with these default credentials:

Email: admin@depseek.com
Password: password

Make sure to change these credentials immediately after your first login for security reasons.

355d ago

Error

SQL query:

CREATE TABLE IF NOT EXISTS file_shares (
id INT NOT NULL AUTO_INCREMENT,
file_id INT NOT NULL,
shared_by INT NOT NULL,
shared_with INT NOT NULL,
permission_level ENUM('view', 'edit') NOT NULL DEFAULT 'view',
created_at DATETIME NOT NULL,
updated_at DATETIME NULL,
expires_at DATETIME NULL,
PRIMARY KEY (id),
INDEX fk_file_shares_files_idx (file_id ASC),
INDEX fk_file_shares_users1_idx (shared_by ASC),
INDEX fk_file_shares_users2_idx (shared_with ASC),
CONSTRAINT fk_file_shares_files
FOREIGN KEY (file_id)
REFERENCES files (id)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT fk_file_shares_users1
FOREIGN KEY (shared_by)
REFERENCES users (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT fk_file_shares_users2
FOREIGN KEY (shared_with)
REFERENCES users (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE = InnoDB
CREATE DA[...]

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE DATABASE IF NOT EXISTS depseek DEFAULT CHARACTER SET utf8mb4 COLLATE ut' at line 30

Show full message

355d ago

SQL Error Solution

It looks like you're encountering an error because the SQL script contains multiple statements, and your MySQL client is trying to execute them all at once without proper separation. Here's how to fix it:

Option 1: Execute Statements Individually

Break down the SQL script and execute each statement separately:

First, create the database:

CREATE DATABASE IF NOT EXISTS `depseek` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `depseek`;

Then create each table one by one, starting with the users table:

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('user', 'admin') NOT NULL DEFAULT 'user',
  `status` ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'inactive',
  `email_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `verification_token` VARCHAR(255) NULL,
  `storage_limit` BIGINT NOT NULL DEFAULT 1073741824,
  `profile_image` VARCHAR(255) NULL,
  `last_login` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC)
) ENGINE = InnoDB;

Then create the folders table:

CREATE TABLE IF NOT EXISTS `folders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `parent_id` INT NULL,
  `folder_name` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_folders_users_idx` (`user_id` ASC),
  INDEX `fk_folders_folders_idx` (`parent_id` ASC),
  CONSTRAINT `fk_folders_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_folders_folders`
    FOREIGN KEY (`parent_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

Then create the files table:

CREATE TABLE IF NOT EXISTS `files` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `folder_id` INT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_size` BIGINT NOT NULL,
  `file_type` VARCHAR(50) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(100) NOT NULL,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `encryption_key` VARCHAR(255) NULL,
  `checksum` VARCHAR(64) NULL,
  `version` INT NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_files_users_idx` (`user_id` ASC),
  INDEX `fk_files_folders_idx` (`folder_id` ASC),
  CONSTRAINT `fk_files_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_files_folders`
    FOREIGN KEY (`folder_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

Then create the file_shares table:

CREATE TABLE IF NOT EXISTS `file_shares` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `shared_by` INT NOT NULL,
  `shared_with` INT NOT NULL,
  `permission_level` ENUM('view', 'edit') NOT NULL DEFAULT 'view',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_file_shares_files_idx` (`file_id` ASC),
  INDEX `fk_file_shares_users1_idx` (`shared_by` ASC),
  INDEX `fk_file_shares_users2_idx` (`shared_with` ASC),
  CONSTRAINT `fk_file_shares_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users1`
    FOREIGN KEY (`shared_by`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users2`
    FOREIGN KEY (`shared_with`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

Continue with each table one by one in this manner.

Option 2: Use Proper Delimiters in phpMyAdmin

If you're using phpMyAdmin:

Go to the "Import" tab
Check the box that says "Allow the interruption of an import"
Upload your SQL file and click "Go"

Option 3: Use MySQL Command Line with Source Command

If you're using the MySQL command line:

First create the database:

CREATE DATABASE IF NOT EXISTS depseek;
USE depseek;

Then use the source command to execute the SQL file:

source /path/to/your/depseek_schema.sql

Option 4: Simplified Schema Without Triggers

If you're still having issues, here's a simplified version without the triggers that you can execute all at once:

-- Create database
CREATE DATABASE IF NOT EXISTS `depseek` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `depseek`;

-- Create users table
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('user', 'admin') NOT NULL DEFAULT 'user',
  `status` ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'inactive',
  `email_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `verification_token` VARCHAR(255) NULL,
  `storage_limit` BIGINT NOT NULL DEFAULT 1073741824,
  `profile_image` VARCHAR(255) NULL,
  `last_login` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC)
) ENGINE = InnoDB;

-- Create folders table
CREATE TABLE IF NOT EXISTS `folders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `parent_id` INT NULL,
  `folder_name` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_folders_users_idx` (`user_id` ASC),
  INDEX `fk_folders_folders_idx` (`parent_id` ASC),
  CONSTRAINT `fk_folders_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_folders_folders`
    FOREIGN KEY (`parent_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create files table
CREATE TABLE IF NOT EXISTS `files` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `folder_id` INT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_size` BIGINT NOT NULL,
  `file_type` VARCHAR(50) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(100) NOT NULL,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `encryption_key` VARCHAR(255) NULL,
  `checksum` VARCHAR(64) NULL,
  `version` INT NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_files_users_idx` (`user_id` ASC),
  INDEX `fk_files_folders_idx` (`folder_id` ASC),
  CONSTRAINT `fk_files_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_files_folders`
    FOREIGN KEY (`folder_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create file_shares table
CREATE TABLE IF NOT EXISTS `file_shares` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `shared_by` INT NOT NULL,
  `shared_with` INT NOT NULL,
  `permission_level` ENUM('view', 'edit') NOT NULL DEFAULT 'view',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_file_shares_files_idx` (`file_id` ASC),
  INDEX `fk_file_shares_users1_idx` (`shared_by` ASC),
  INDEX `fk_file_shares_users2_idx` (`shared_with` ASC),
  CONSTRAINT `fk_file_shares_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users1`
    FOREIGN KEY (`shared_by`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users2`
    FOREIGN KEY (`shared_with`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create public_links table
CREATE TABLE IF NOT EXISTS `public_links` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `download_count` INT NOT NULL DEFAULT 0,
  `max_downloads` INT NULL,
  `password` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL,
  `expires_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `token_UNIQUE` (`token` ASC),
  INDEX `fk_public_links_files_idx` (`file_id` ASC),
  INDEX `fk_public_links_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_public_links_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_public_links_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create file_versions table
CREATE TABLE IF NOT EXISTS `file_versions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `version` INT NOT NULL,
  `file_size` BIGINT NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `encryption_key` VARCHAR(255) NULL,
  `checksum` VARCHAR(64) NULL,
  `created_by` INT NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_file_versions_files_idx` (`file_id` ASC),
  INDEX `fk_file_versions_users_idx` (`created_by` ASC),
  CONSTRAINT `fk_file_versions_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_versions_users`
    FOREIGN KEY (`created_by`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create user_activities table
CREATE TABLE IF NOT EXISTS `user_activities` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `activity_type` VARCHAR(50) NOT NULL,
  `description` TEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_user_activities_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_user_activities_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create sessions table
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` VARCHAR(128) NOT NULL,
  `user_id` INT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `payload` TEXT NOT NULL,
  `last_activity` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_sessions_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_sessions_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create password_resets table
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(100) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `expires_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `email_index` (`email` ASC)
) ENGINE = InnoDB;

-- Create settings table
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `setting_key_UNIQUE` (`setting_key` ASC)
) ENGINE = InnoDB;

-- Create user_settings table
CREATE TABLE IF NOT EXISTS `user_settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `user_setting_unique` (`user_id` ASC, `setting_key` ASC),
  CONSTRAINT `fk_user_settings_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Insert default admin user
INSERT INTO `users` (
  `first_name`, 
  `last_name`, 
  `email`, 
  `password`, 
  `role`, 
  `status`, 
  `email_verified`, 
  `storage_limit`, 
  `created_at`
) VALUES (
  'Admin', 
  'User', 
  'admin@depseek.com', 
  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 
  'admin', 
  'active', 
  1, 
  5368709120, 
  NOW()
);

-- Insert default settings
INSERT INTO `settings` (`setting_key`, `setting_value`, `created_at`) VALUES
('site_name', 'DepSeek', NOW()),
('site_description', 'Secure Cloud Storage System', NOW()),
('max_upload_size', '104857600', NOW()),
('allowed_file_types', 'jpg,jpeg,png,gif,pdf,doc,docx,xls,xlsx,ppt,pptx,txt,zip,rar,mp3,mp4,avi,mov', NOW()),
('default_storage_limit', '1073741824', NOW()),
('enable_registration', '1', NOW()),
('enable_email_verification', '1', NOW()),
('enable_file_encryption', '1', NOW()),
('maintenance_mode', '0', NOW()),
('version', '1.0.0', NOW());

Common Issues and Solutions

Multiple SQL statements: Many MySQL clients require you to execute one statement at a time or use a special delimiter for multiple statements.
Triggers and stored procedures: These require changing the delimiter temporarily, which can cause issues in some clients.
MariaDB vs MySQL syntax differences: There might be slight syntax differences between MariaDB and MySQL. The error message indicates you're using MariaDB.
ASC keyword: Some older versions of MySQL/MariaDB might have issues with the explicit ASC keyword in index definitions. You can try removing it if needed.

Let me know if you're still encountering issues, and I can provide more specific guidance based on the MySQL client you're using.

355d ago

how to run and see output in browser

355d ago

Code editing is not availableDuplicate this chat to use a more powerful code editor.

then give me a database for it

355d ago

DepSeek Database Schema

Here's the complete SQL script to create the database schema for the DepSeek cloud storage system:

-- -----------------------------------------------------
-- Schema depseek
-- -----------------------------------------------------
CREATE DATABASE IF NOT EXISTS `depseek` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `depseek`;

-- -----------------------------------------------------
-- Table `users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('user', 'admin') NOT NULL DEFAULT 'user',
  `status` ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'inactive',
  `email_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `verification_token` VARCHAR(255) NULL,
  `storage_limit` BIGINT NOT NULL DEFAULT 1073741824, -- 1GB default
  `profile_image` VARCHAR(255) NULL,
  `last_login` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `folders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `folders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `parent_id` INT NULL,
  `folder_name` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_folders_users_idx` (`user_id` ASC),
  INDEX `fk_folders_folders_idx` (`parent_id` ASC),
  CONSTRAINT `fk_folders_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_folders_folders`
    FOREIGN KEY (`parent_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `files`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `files` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `folder_id` INT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_size` BIGINT NOT NULL,
  `file_type` VARCHAR(50) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(100) NOT NULL,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `encryption_key` VARCHAR(255) NULL,
  `checksum` VARCHAR(64) NULL,
  `version` INT NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_files_users_idx` (`user_id` ASC),
  INDEX `fk_files_folders_idx` (`folder_id` ASC),
  CONSTRAINT `fk_files_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_files_folders`
    FOREIGN KEY (`folder_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `file_shares`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `file_shares` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `shared_by` INT NOT NULL,
  `shared_with` INT NOT NULL,
  `permission_level` ENUM('view', 'edit') NOT NULL DEFAULT 'view',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_file_shares_files_idx` (`file_id` ASC),
  INDEX `fk_file_shares_users1_idx` (`shared_by` ASC),
  INDEX `fk_file_shares_users2_idx` (`shared_with` ASC),
  CONSTRAINT `fk_file_shares_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users1`
    FOREIGN KEY (`shared_by`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users2`
    FOREIGN KEY (`shared_with`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `public_links`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `public_links` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `download_count` INT NOT NULL DEFAULT 0,
  `max_downloads` INT NULL,
  `password` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL,
  `expires_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `token_UNIQUE` (`token` ASC),
  INDEX `fk_public_links_files_idx` (`file_id` ASC),
  INDEX `fk_public_links_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_public_links_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_public_links_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `file_versions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `file_versions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `version` INT NOT NULL,
  `file_size` BIGINT NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `encryption_key` VARCHAR(255) NULL,
  `checksum` VARCHAR(64) NULL,
  `created_by` INT NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_file_versions_files_idx` (`file_id` ASC),
  INDEX `fk_file_versions_users_idx` (`created_by` ASC),
  CONSTRAINT `fk_file_versions_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_versions_users`
    FOREIGN KEY (`created_by`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `user_activities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_activities` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `activity_type` VARCHAR(50) NOT NULL,
  `description` TEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_user_activities_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_user_activities_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `sessions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` VARCHAR(128) NOT NULL,
  `user_id` INT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `payload` TEXT NOT NULL,
  `last_activity` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_sessions_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_sessions_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `password_resets`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(100) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `expires_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `email_index` (`email` ASC)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `settings`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `setting_key_UNIQUE` (`setting_key` ASC)
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `user_settings`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `user_setting_unique` (`user_id` ASC, `setting_key` ASC),
  CONSTRAINT `fk_user_settings_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- -----------------------------------------------------
-- Insert default admin user
-- -----------------------------------------------------
INSERT INTO `users` (
  `first_name`, 
  `last_name`, 
  `email`, 
  `password`, 
  `role`, 
  `status`, 
  `email_verified`, 
  `storage_limit`, 
  `created_at`
) VALUES (
  'Admin', 
  'User', 
  'admin@depseek.com', 
  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', -- password: password
  'admin', 
  'active', 
  1, 
  5368709120, -- 5GB
  NOW()
);

-- -----------------------------------------------------
-- Insert default settings
-- -----------------------------------------------------
INSERT INTO `settings` (`setting_key`, `setting_value`, `created_at`) VALUES
('site_name', 'DepSeek', NOW()),
('site_description', 'Secure Cloud Storage System', NOW()),
('max_upload_size', '104857600', NOW()), -- 100MB
('allowed_file_types', 'jpg,jpeg,png,gif,pdf,doc,docx,xls,xlsx,ppt,pptx,txt,zip,rar,mp3,mp4,avi,mov', NOW()),
('default_storage_limit', '1073741824', NOW()), -- 1GB
('enable_registration', '1', NOW()),
('enable_email_verification', '1', NOW()),
('enable_file_encryption', '1', NOW()),
('maintenance_mode', '0', NOW()),
('version', '1.0.0', NOW());

-- -----------------------------------------------------
-- Create triggers
-- -----------------------------------------------------

-- Update timestamps on user update
DELIMITER //
CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- Update timestamps on folder update
DELIMITER //
CREATE TRIGGER before_folder_update
BEFORE UPDATE ON folders
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- Update timestamps on file update
DELIMITER //
CREATE TRIGGER before_file_update
BEFORE UPDATE ON files
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- Update timestamps on file_shares update
DELIMITER //
CREATE TRIGGER before_file_share_update
BEFORE UPDATE ON file_shares
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- Update timestamps on settings update
DELIMITER //
CREATE TRIGGER before_setting_update
BEFORE UPDATE ON settings
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

-- Update timestamps on user_settings update
DELIMITER //
CREATE TRIGGER before_user_setting_update
BEFORE UPDATE ON user_settings
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END//
DELIMITER ;

Database Schema Explanation

This schema includes all the necessary tables for the DepSeek cloud storage system:

Core Tables

users - Stores user account information
- Basic user details (name, email, password)
- Account status and verification
- Storage limits
- Timestamps for creation, updates, and soft deletion
folders - Manages folder structure
- Hierarchical structure with parent-child relationships
- User ownership
- Soft deletion support
files - Stores file metadata
- File details (name, size, type, path)
- Encryption information
- Version tracking
- Soft deletion support
file_shares - Manages file sharing between users
- Permission levels (view/edit)
- Expiration dates
- Tracking who shared with whom

Additional Features

public_links - For generating public download links
- Secure tokens
- Download limits and tracking
- Optional password protection
- Expiration dates
file_versions - For version control of files
- Tracks all versions of a file
- Stores metadata for each version
- Records who created each version

System Tables

user_activities - Audit log of user actions
- Tracks all user activities
- Records IP addresses and user agents
- Timestamps for when activities occurred
sessions - Manages user sessions
- Secure session handling
- Tracks session data
- Records IP addresses and user agents
password_resets - Handles password reset requests
- Secure tokens
- Expiration dates
settings - Global application settings
- Configurable system parameters
- Version information
user_settings - User-specific settings
- Personalization options
- User preferences

Database Features

Foreign Key Constraints - Ensures data integrity
Indexes - Optimizes query performance
Triggers - Automatically updates timestamps
Default Values - Sets sensible defaults
Enumerations - Restricts values to valid options

How to Import

To import this database schema:

Create a new file named depseek_schema.sql and paste the SQL code above
Use the MySQL command line:
mysql -u username -p depseek < depseek_schema.sql
Or use a tool like phpMyAdmin:
- Create a new database named depseek
- Go to the "Import" tab
- Select the depseek_schema.sql file
- Click "Go" to import

Default Credentials

After importing, you can log in with these default credentials:

Email: admin@depseek.com
Password: password

Make sure to change these credentials immediately after your first login for security reasons.

Error

SQL query:

Show full message

355d ago

SQL Error Solution

Option 1: Execute Statements Individually

Break down the SQL script and execute each statement separately:

First, create the database:

CREATE DATABASE IF NOT EXISTS `depseek` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `depseek`;

Then create each table one by one, starting with the users table:

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('user', 'admin') NOT NULL DEFAULT 'user',
  `status` ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'inactive',
  `email_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `verification_token` VARCHAR(255) NULL,
  `storage_limit` BIGINT NOT NULL DEFAULT 1073741824,
  `profile_image` VARCHAR(255) NULL,
  `last_login` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC)
) ENGINE = InnoDB;

Then create the folders table:

CREATE TABLE IF NOT EXISTS `folders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `parent_id` INT NULL,
  `folder_name` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_folders_users_idx` (`user_id` ASC),
  INDEX `fk_folders_folders_idx` (`parent_id` ASC),
  CONSTRAINT `fk_folders_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_folders_folders`
    FOREIGN KEY (`parent_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

Then create the files table:

CREATE TABLE IF NOT EXISTS `files` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `folder_id` INT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_size` BIGINT NOT NULL,
  `file_type` VARCHAR(50) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(100) NOT NULL,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `encryption_key` VARCHAR(255) NULL,
  `checksum` VARCHAR(64) NULL,
  `version` INT NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_files_users_idx` (`user_id` ASC),
  INDEX `fk_files_folders_idx` (`folder_id` ASC),
  CONSTRAINT `fk_files_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_files_folders`
    FOREIGN KEY (`folder_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

Then create the file_shares table:

CREATE TABLE IF NOT EXISTS `file_shares` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `shared_by` INT NOT NULL,
  `shared_with` INT NOT NULL,
  `permission_level` ENUM('view', 'edit') NOT NULL DEFAULT 'view',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_file_shares_files_idx` (`file_id` ASC),
  INDEX `fk_file_shares_users1_idx` (`shared_by` ASC),
  INDEX `fk_file_shares_users2_idx` (`shared_with` ASC),
  CONSTRAINT `fk_file_shares_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users1`
    FOREIGN KEY (`shared_by`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users2`
    FOREIGN KEY (`shared_with`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

Continue with each table one by one in this manner.

Option 2: Use Proper Delimiters in phpMyAdmin

If you're using phpMyAdmin:

Go to the "Import" tab
Check the box that says "Allow the interruption of an import"
Upload your SQL file and click "Go"

Option 3: Use MySQL Command Line with Source Command

If you're using the MySQL command line:

First create the database:

CREATE DATABASE IF NOT EXISTS depseek;
USE depseek;

Then use the source command to execute the SQL file:

source /path/to/your/depseek_schema.sql

Option 4: Simplified Schema Without Triggers

If you're still having issues, here's a simplified version without the triggers that you can execute all at once:

-- Create database
CREATE DATABASE IF NOT EXISTS `depseek` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `depseek`;

-- Create users table
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('user', 'admin') NOT NULL DEFAULT 'user',
  `status` ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'inactive',
  `email_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `verification_token` VARCHAR(255) NULL,
  `storage_limit` BIGINT NOT NULL DEFAULT 1073741824,
  `profile_image` VARCHAR(255) NULL,
  `last_login` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC)
) ENGINE = InnoDB;

-- Create folders table
CREATE TABLE IF NOT EXISTS `folders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `parent_id` INT NULL,
  `folder_name` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_folders_users_idx` (`user_id` ASC),
  INDEX `fk_folders_folders_idx` (`parent_id` ASC),
  CONSTRAINT `fk_folders_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_folders_folders`
    FOREIGN KEY (`parent_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create files table
CREATE TABLE IF NOT EXISTS `files` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `folder_id` INT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_size` BIGINT NOT NULL,
  `file_type` VARCHAR(50) NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `mime_type` VARCHAR(100) NOT NULL,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `encryption_key` VARCHAR(255) NULL,
  `checksum` VARCHAR(64) NULL,
  `version` INT NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `deleted_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_files_users_idx` (`user_id` ASC),
  INDEX `fk_files_folders_idx` (`folder_id` ASC),
  CONSTRAINT `fk_files_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_files_folders`
    FOREIGN KEY (`folder_id`)
    REFERENCES `folders` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create file_shares table
CREATE TABLE IF NOT EXISTS `file_shares` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `shared_by` INT NOT NULL,
  `shared_with` INT NOT NULL,
  `permission_level` ENUM('view', 'edit') NOT NULL DEFAULT 'view',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_file_shares_files_idx` (`file_id` ASC),
  INDEX `fk_file_shares_users1_idx` (`shared_by` ASC),
  INDEX `fk_file_shares_users2_idx` (`shared_with` ASC),
  CONSTRAINT `fk_file_shares_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users1`
    FOREIGN KEY (`shared_by`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_shares_users2`
    FOREIGN KEY (`shared_with`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create public_links table
CREATE TABLE IF NOT EXISTS `public_links` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `token` VARCHAR(64) NOT NULL,
  `download_count` INT NOT NULL DEFAULT 0,
  `max_downloads` INT NULL,
  `password` VARCHAR(255) NULL,
  `created_at` DATETIME NOT NULL,
  `expires_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `token_UNIQUE` (`token` ASC),
  INDEX `fk_public_links_files_idx` (`file_id` ASC),
  INDEX `fk_public_links_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_public_links_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_public_links_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create file_versions table
CREATE TABLE IF NOT EXISTS `file_versions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,
  `version` INT NOT NULL,
  `file_size` BIGINT NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `is_encrypted` TINYINT(1) NOT NULL DEFAULT 0,
  `encryption_key` VARCHAR(255) NULL,
  `checksum` VARCHAR(64) NULL,
  `created_by` INT NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_file_versions_files_idx` (`file_id` ASC),
  INDEX `fk_file_versions_users_idx` (`created_by` ASC),
  CONSTRAINT `fk_file_versions_files`
    FOREIGN KEY (`file_id`)
    REFERENCES `files` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_file_versions_users`
    FOREIGN KEY (`created_by`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create user_activities table
CREATE TABLE IF NOT EXISTS `user_activities` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `activity_type` VARCHAR(50) NOT NULL,
  `description` TEXT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_user_activities_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_user_activities_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create sessions table
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` VARCHAR(128) NOT NULL,
  `user_id` INT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `payload` TEXT NOT NULL,
  `last_activity` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_sessions_users_idx` (`user_id` ASC),
  CONSTRAINT `fk_sessions_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Create password_resets table
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(100) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `created_at` DATETIME NOT NULL,
  `expires_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `email_index` (`email` ASC)
) ENGINE = InnoDB;

-- Create settings table
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `setting_key_UNIQUE` (`setting_key` ASC)
) ENGINE = InnoDB;

-- Create user_settings table
CREATE TABLE IF NOT EXISTS `user_settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NOT NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `user_setting_unique` (`user_id` ASC, `setting_key` ASC),
  CONSTRAINT `fk_user_settings_users`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
) ENGINE = InnoDB;

-- Insert default admin user
INSERT INTO `users` (
  `first_name`, 
  `last_name`, 
  `email`, 
  `password`, 
  `role`, 
  `status`, 
  `email_verified`, 
  `storage_limit`, 
  `created_at`
) VALUES (
  'Admin', 
  'User', 
  'admin@depseek.com', 
  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 
  'admin', 
  'active', 
  1, 
  5368709120, 
  NOW()
);

-- Insert default settings
INSERT INTO `settings` (`setting_key`, `setting_value`, `created_at`) VALUES
('site_name', 'DepSeek', NOW()),
('site_description', 'Secure Cloud Storage System', NOW()),
('max_upload_size', '104857600', NOW()),
('allowed_file_types', 'jpg,jpeg,png,gif,pdf,doc,docx,xls,xlsx,ppt,pptx,txt,zip,rar,mp3,mp4,avi,mov', NOW()),
('default_storage_limit', '1073741824', NOW()),
('enable_registration', '1', NOW()),
('enable_email_verification', '1', NOW()),
('enable_file_encryption', '1', NOW()),
('maintenance_mode', '0', NOW()),
('version', '1.0.0', NOW());

Common Issues and Solutions

Multiple SQL statements: Many MySQL clients require you to execute one statement at a time or use a special delimiter for multiple statements.
Triggers and stored procedures: These require changing the delimiter temporarily, which can cause issues in some clients.
MariaDB vs MySQL syntax differences: There might be slight syntax differences between MariaDB and MySQL. The error message indicates you're using MariaDB.
ASC keyword: Some older versions of MySQL/MariaDB might have issues with the explicit ASC keyword in index definitions. You can try removing it if needed.

Let me know if you're still encountering issues, and I can provide more specific guidance based on the MySQL client you're using.

355d ago

how to run and see output in browser

355d ago

Code editing is not availableDuplicate this chat to use a more powerful code editor.