221 lines
8.4 KiB
MySQL
221 lines
8.4 KiB
MySQL
|
-- ScriptShare Database Initialization Script
|
||
|
|
-- This script sets up the initial database structure and default data
|
||
|
|
|
||
|
|
USE scriptshare;
|
||
|
|
|
||
|
|
-- Set proper character set and collation
|
||
|
|
ALTER DATABASE scriptshare CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Create users table if it doesn't exist
|
||
|
|
CREATE TABLE IF NOT EXISTS `users` (
|
||
|
|
`id` varchar(255) NOT NULL,
|
||
|
|
`email` varchar(255) NOT NULL UNIQUE,
|
||
|
|
`username` varchar(100) NOT NULL UNIQUE,
|
||
|
|
`displayName` varchar(255) NOT NULL,
|
||
|
|
`passwordHash` varchar(255) NOT NULL,
|
||
|
|
`avatarUrl` text,
|
||
|
|
`bio` text,
|
||
|
|
`isAdmin` boolean DEFAULT false,
|
||
|
|
`isModerator` boolean DEFAULT false,
|
||
|
|
`isVerified` boolean DEFAULT false,
|
||
|
|
`createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`updatedAt` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
INDEX `idx_users_email` (`email`),
|
||
|
|
INDEX `idx_users_username` (`username`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Create scripts table if it doesn't exist
|
||
|
|
CREATE TABLE IF NOT EXISTS `scripts` (
|
||
|
|
`id` varchar(255) NOT NULL,
|
||
|
|
`name` varchar(255) NOT NULL,
|
||
|
|
`description` text,
|
||
|
|
`content` longtext NOT NULL,
|
||
|
|
`authorId` varchar(255) NOT NULL,
|
||
|
|
`categories` json,
|
||
|
|
`tags` json,
|
||
|
|
`compatibleOs` json,
|
||
|
|
`language` varchar(50) DEFAULT 'bash',
|
||
|
|
`isApproved` boolean DEFAULT false,
|
||
|
|
`isPublic` boolean DEFAULT true,
|
||
|
|
`viewCount` int DEFAULT 0,
|
||
|
|
`downloadCount` int DEFAULT 0,
|
||
|
|
`createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`updatedAt` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
FOREIGN KEY (`authorId`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
||
|
|
INDEX `idx_scripts_author` (`authorId`),
|
||
|
|
INDEX `idx_scripts_approved` (`isApproved`),
|
||
|
|
INDEX `idx_scripts_public` (`isPublic`),
|
||
|
|
INDEX `idx_scripts_created` (`createdAt`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Create ratings table if it doesn't exist
|
||
|
|
CREATE TABLE IF NOT EXISTS `ratings` (
|
||
|
|
`id` varchar(255) NOT NULL,
|
||
|
|
`scriptId` varchar(255) NOT NULL,
|
||
|
|
`userId` varchar(255) NOT NULL,
|
||
|
|
`rating` int NOT NULL CHECK (rating >= 1 AND rating <= 5),
|
||
|
|
`comment` text,
|
||
|
|
`createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`updatedAt` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `unique_user_script_rating` (`scriptId`, `userId`),
|
||
|
|
FOREIGN KEY (`scriptId`) REFERENCES `scripts`(`id`) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
||
|
|
INDEX `idx_ratings_script` (`scriptId`),
|
||
|
|
INDEX `idx_ratings_user` (`userId`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Create script_analytics table if it doesn't exist
|
||
|
|
CREATE TABLE IF NOT EXISTS `script_analytics` (
|
||
|
|
`id` varchar(255) NOT NULL,
|
||
|
|
`scriptId` varchar(255) NOT NULL,
|
||
|
|
`eventType` varchar(50) NOT NULL,
|
||
|
|
`userId` varchar(255),
|
||
|
|
`userAgent` text,
|
||
|
|
`ipAddress` varchar(45),
|
||
|
|
`referrer` text,
|
||
|
|
`metadata` json,
|
||
|
|
`createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
FOREIGN KEY (`scriptId`) REFERENCES `scripts`(`id`) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE SET NULL,
|
||
|
|
INDEX `idx_analytics_script` (`scriptId`),
|
||
|
|
INDEX `idx_analytics_event` (`eventType`),
|
||
|
|
INDEX `idx_analytics_created` (`createdAt`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Create script_collections table if it doesn't exist
|
||
|
|
CREATE TABLE IF NOT EXISTS `script_collections` (
|
||
|
|
`id` varchar(255) NOT NULL,
|
||
|
|
`name` varchar(255) NOT NULL,
|
||
|
|
`description` text,
|
||
|
|
`authorId` varchar(255) NOT NULL,
|
||
|
|
`isPublic` boolean DEFAULT false,
|
||
|
|
`createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
`updatedAt` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
FOREIGN KEY (`authorId`) REFERENCES `users`(`id`) ON DELETE CASCADE,
|
||
|
|
INDEX `idx_collections_author` (`authorId`),
|
||
|
|
INDEX `idx_collections_public` (`isPublic`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Create collection_scripts table if it doesn't exist
|
||
|
|
CREATE TABLE IF NOT EXISTS `collection_scripts` (
|
||
|
|
`id` varchar(255) NOT NULL,
|
||
|
|
`collectionId` varchar(255) NOT NULL,
|
||
|
|
`scriptId` varchar(255) NOT NULL,
|
||
|
|
`addedAt` timestamp DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
UNIQUE KEY `unique_collection_script` (`collectionId`, `scriptId`),
|
||
|
|
FOREIGN KEY (`collectionId`) REFERENCES `script_collections`(`id`) ON DELETE CASCADE,
|
||
|
|
FOREIGN KEY (`scriptId`) REFERENCES `scripts`(`id`) ON DELETE CASCADE,
|
||
|
|
INDEX `idx_collection_scripts_collection` (`collectionId`),
|
||
|
|
INDEX `idx_collection_scripts_script` (`scriptId`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Create script_versions table if it doesn't exist
|
||
|
|
CREATE TABLE IF NOT EXISTS `script_versions` (
|
||
|
|
`id` varchar(255) NOT NULL,
|
||
|
|
`scriptId` varchar(255) NOT NULL,
|
||
|
|
`version` varchar(50) NOT NULL,
|
||
|
|
`content` longtext NOT NULL,
|
||
|
|
`changelog` text,
|
||
|
|
`createdAt` timestamp DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
PRIMARY KEY (`id`),
|
||
|
|
FOREIGN KEY (`scriptId`) REFERENCES `scripts`(`id`) ON DELETE CASCADE,
|
||
|
|
INDEX `idx_versions_script` (`scriptId`),
|
||
|
|
INDEX `idx_versions_created` (`createdAt`)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
||
|
|
|
||
|
|
-- Insert default admin user (password: admin123)
|
||
|
|
-- Note: In production, use proper password hashing
|
||
|
|
INSERT IGNORE INTO `users` (
|
||
|
|
`id`,
|
||
|
|
`email`,
|
||
|
|
`username`,
|
||
|
|
`displayName`,
|
||
|
|
`passwordHash`,
|
||
|
|
`isAdmin`,
|
||
|
|
`isModerator`,
|
||
|
|
`isVerified`
|
||
|
|
) VALUES (
|
||
|
|
'admin-default-001',
|
||
|
|
'admin@scriptshare.local',
|
||
|
|
'admin',
|
||
|
|
'System Administrator',
|
||
|
|
'$2b$10$8K5YBvK8H.UX3JQ2K9J9x.RQfFr6bF7UE9FJm.LrEY8K.QG8wH8G6', -- admin123
|
||
|
|
true,
|
||
|
|
true,
|
||
|
|
true
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Insert sample categories data
|
||
|
|
INSERT IGNORE INTO `script_collections` (
|
||
|
|
`id`,
|
||
|
|
`name`,
|
||
|
|
`description`,
|
||
|
|
`authorId`,
|
||
|
|
`isPublic`
|
||
|
|
) VALUES
|
||
|
|
('collection-system-001', 'System Administration', 'Essential system administration scripts', 'admin-default-001', true),
|
||
|
|
('collection-devops-001', 'DevOps Automation', 'CI/CD and deployment automation scripts', 'admin-default-001', true),
|
||
|
|
('collection-security-001', 'Security Tools', 'Security scanning and hardening scripts', 'admin-default-001', true),
|
||
|
|
('collection-backup-001', 'Backup & Recovery', 'Data backup and recovery automation', 'admin-default-001', true);
|
||
|
|
|
||
|
|
-- Insert sample script
|
||
|
|
INSERT IGNORE INTO `scripts` (
|
||
|
|
`id`,
|
||
|
|
`name`,
|
||
|
|
`description`,
|
||
|
|
`content`,
|
||
|
|
`authorId`,
|
||
|
|
`categories`,
|
||
|
|
`tags`,
|
||
|
|
`compatibleOs`,
|
||
|
|
`language`,
|
||
|
|
`isApproved`,
|
||
|
|
`isPublic`
|
||
|
|
) VALUES (
|
||
|
|
'script-welcome-001',
|
||
|
|
'System Information Script',
|
||
|
|
'A simple script to display system information including OS, CPU, memory, and disk usage.',
|
||
|
|
'#!/bin/bash\n\necho "=== System Information ==="\necho "Hostname: $(hostname)"\necho "OS: $(uname -s)"\necho "Kernel: $(uname -r)"\necho "Architecture: $(uname -m)"\necho ""\necho "=== CPU Information ==="\necho "CPU: $(lscpu | grep \"Model name\" | cut -d: -f2 | xargs)"\necho "Cores: $(nproc)"\necho ""\necho "=== Memory Information ==="\nfree -h\necho ""\necho "=== Disk Usage ==="\ndf -h\necho ""\necho "=== System Uptime ==="\nuptime',
|
||
|
|
'admin-default-001',
|
||
|
|
'["System Administration", "Monitoring"]',
|
||
|
|
'["system", "info", "monitoring", "diagnostics"]',
|
||
|
|
'["linux", "macos"]',
|
||
|
|
'bash',
|
||
|
|
true,
|
||
|
|
true
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Add the sample script to system collection
|
||
|
|
INSERT IGNORE INTO `collection_scripts` (
|
||
|
|
`id`,
|
||
|
|
`collectionId`,
|
||
|
|
`scriptId`
|
||
|
|
) VALUES (
|
||
|
|
'cs-001',
|
||
|
|
'collection-system-001',
|
||
|
|
'script-welcome-001'
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Create indexes for performance optimization
|
||
|
|
CREATE INDEX IF NOT EXISTS `idx_scripts_name` ON `scripts`(`name`);
|
||
|
|
CREATE INDEX IF NOT EXISTS `idx_scripts_language` ON `scripts`(`language`);
|
||
|
|
CREATE INDEX IF NOT EXISTS `idx_analytics_user` ON `script_analytics`(`userId`);
|
||
|
|
CREATE INDEX IF NOT EXISTS `idx_collections_name` ON `script_collections`(`name`);
|
||
|
|
|
||
|
|
-- Set up database optimization settings
|
||
|
|
SET GLOBAL innodb_buffer_pool_size = 268435456; -- 256MB
|
||
|
|
SET GLOBAL max_connections = 200;
|
||
|
|
SET GLOBAL innodb_file_per_table = 1;
|
||
|
|
|
||
|
|
-- Print initialization complete message
|
||
|
|
SELECT 'ScriptShare database initialization completed successfully!' as message;
|
||
|
|
SELECT COUNT(*) as total_users FROM users;
|
||
|
|
SELECT COUNT(*) as total_scripts FROM scripts;
|
||
|
|
SELECT COUNT(*) as total_collections FROM script_collections;
|