File indexing completed on 2024-04-21 06:00:37
0001 CREATE TABLE `section` ( 0002 `section_id` INT NOT NULL AUTO_INCREMENT, 0003 `name` VARCHAR(50) NOT NULL, 0004 `description` VARCHAR(255) NULL, 0005 `is_active` INT(1) UNSIGNED NULL DEFAULT '1', 0006 `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, 0007 `deleted_at` TIMESTAMP NULL DEFAULT NULL, 0008 PRIMARY KEY (`section_id`) 0009 ) 0010 COMMENT='Every section has categories, see table section_categories. And every download belongs to a category and to a section.' 0011 COLLATE='latin1_swedish_ci' 0012 ; 0013 0014 CREATE TABLE `section_category` ( 0015 `section_category_id` INT NOT NULL AUTO_INCREMENT, 0016 `section_id` INT NOT NULL, 0017 `project_category_id` INT NOT NULL, 0018 PRIMARY KEY (`section_category_id`) 0019 ) 0020 COMMENT='every section has n categories' 0021 COLLATE='latin1_swedish_ci' 0022 ; 0023 0024 ALTER TABLE `section` 0025 ADD COLUMN `percent_of_support` INT UNSIGNED NULL DEFAULT NULL COMMENT 'How much of the supporter donations goes to this section' AFTER `description`; 0026 0027 0028 ALTER TABLE `section_category` 0029 ADD UNIQUE INDEX `uk_cat_id` (`project_category_id`); 0030 0031 CREATE TABLE `sponsor` ( 0032 `sponsor_id` INT(11) NOT NULL AUTO_INCREMENT, 0033 `member_id` INT(11) NOT NULL, 0034 `name` VARCHAR(50) NOT NULL, 0035 `fullname` VARCHAR(255) NOT NULL, 0036 `description` VARCHAR(255) NOT NULL, 0037 `amount` DOUBLE NOT NULL DEFAULT '0' COMMENT 'Sponsoring amount per month in $', 0038 `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 0039 `begin_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', 0040 `end_at` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00', 0041 `is_active` INT(10) UNSIGNED NOT NULL DEFAULT '1', 0042 PRIMARY KEY (`sponsor_id`) 0043 ) 0044 ENGINE=InnoDB 0045 ; 0046 0047 0048 CREATE TABLE `section_sponsor` ( 0049 `section_sponsor_id` INT NOT NULL AUTO_INCREMENT, 0050 `section_id` INT NOT NULL, 0051 `sponsor_id` INT NOT NULL, 0052 `percent_of_sponsoring` INT UNSIGNED NOT NULL COMMENT 'How much of the sponsoring goes to this section', 0053 PRIMARY KEY (`section_sponsor_id`) 0054 ) 0055 COLLATE='latin1_swedish_ci' 0056 ; 0057 0058 ALTER TABLE `section_sponsor` 0059 ADD UNIQUE INDEX `uk_section_sponsor` (`section_id`, `sponsor_id`); 0060 0061 0062 0063 0064 0065 /* 0066 Insert Data, if needed 0067 INSERT INTO section (name, description) VALUES ('Themes', 'Themes Section'); 0068 INSERT INTO section (name, description) VALUES ('Software', 'Software Section'); 0069 INSERT INTO section (name, description) VALUES ('Videos', 'Videos Section'); 0070 INSERT INTO section (name, description) VALUES ('Music', 'Music Section'); 0071 0072 SELECT * FROM section; 0073 0074 INSERT INTO section_category (project_category_id,section_id) 0075 SELECT c.project_category_id, 2 AS section_id FROM project_category c 0076 WHERE c.is_active = 1 0077 ; 0078 0079 */