Posted by Anonymous Mon 12th Mar 2007 13:56 - Syntax is SQL - 75 views
Download | New Post | Modify | Hide line numbers
  1. /* SVN FILE: $Id:$ */
  2. /**
  3. * eShopStart.
  4. *
  5. * Database Table Schema.
  6. *
  7. * MySQL Version 5
  8. *
  9. * eShopStart
  10. * Copyright (c)  2006, eShopStart.
  11. *                      1 St James Square, Hoyland,
  12. *                      Barnsley S74 9AA
  13. *
  14. * Redistribution of this file is strictly prohibited.
  15. *
  16. * @filesource
  17. * @copyright     Copyright (c) 2006, eShopStart.
  18. * @link          http://eshopstart.com eShopStart
  19. * @package       eShopStart
  20. * @subpackage
  21. * @since         eShopStart v 2.0.0
  22. * @version       $Revision:$
  23. * @modifiedby    $LastChangedBy:$
  24. * @lastmodified  $LastChangedDate:$
  25. * @license       http://eshopstart.com/licenses/license.php License
  26. */
  27.  
  28.  
  29. /*!40101 SET NAMES utf8 */;
  30.  
  31. /*!40101 SET SQL_MODE=''*/;
  32.  
  33. CREATE DATABASE IF NOT EXISTS `eshopstart`;
  34. USE `eshopstart`;
  35.  
  36. /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  37. /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  38.  
  39.  
  40.  
  41. /*
  42.   -----------------------------------------------------------------------
  43.     System Tables.
  44.   -----------------------------------------------------------------------
  45. */
  46. /* Config table
  47. * holds system configurations and defaults */
  48. DROP TABLE IF EXISTS `configs`;
  49. CREATE TABLE `configs` (
  50.     `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  51.     `name` varchar(255) NOT NULL DEFAULT '',
  52.     `value` varchar(255) NOT NULL DEFAULT '',
  53.     PRIMARY KEY  (`id`),
  54.     UNIQUE KEY `name` (`name`)
  55. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  56.  
  57. /* Sessions table */
  58. DROP TABLE IF EXISTS `sessions`;
  59. CREATE TABLE `sessions` (
  60.     `id` varchar(255) NOT NULL DEFAULT '',
  61.     `data` text,
  62.     `expires` int(11) UNSIGNED DEFAULT NULL,
  63.     PRIMARY KEY  (`id`)
  64. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  65.  
  66.  
  67.  
  68. /*
  69.   -----------------------------------------------------------------------
  70.     Locale tables.
  71.   -----------------------------------------------------------------------
  72. */
  73. /* Table structure for table `countries` */
  74. DROP TABLE IF EXISTS `countries`;
  75. CREATE TABLE `countries` (
  76.     `id` int(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  77.     `iso` char(2) NOT NULL,
  78.     `name` varchar(80) NOT NULL,
  79.     `printable_name` varchar(80) NOT NULL,
  80.     `iso3` char(3) DEFAULT NULL,
  81.     `numcode` smallint(6) DEFAULT NULL,
  82.     PRIMARY KEY  (`id`)
  83. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  84.  
  85. /* Table structure for table `currencies` */
  86. DROP TABLE IF EXISTS `currencies`;
  87. CREATE TABLE `currencies` (
  88.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  89.     `country_id` int(3) UNSIGNED NOT NULL,
  90.     `base_rate` float(4,2) DEFAULT NULL,
  91.     `value` varchar(200) DEFAULT NULL,
  92.     PRIMARY KEY  (`id`),
  93.     KEY `FK_currency__country` (`country_id`),
  94.     CONSTRAINT `currencies_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
  95. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  96.  
  97.  
  98.  
  99. /*
  100.   -----------------------------------------------------------------------
  101.     Client tables.
  102.   -----------------------------------------------------------------------
  103. */
  104. /*Table structure for table `clients` */
  105. DROP TABLE IF EXISTS `clients`;
  106. CREATE TABLE `clients` (
  107.     `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  108.     `name` varchar(255) DEFAULT NULL,
  109.     `no_of_licence_seats` int(3) UNSIGNED DEFAULT NULL,
  110.     `contact_name` varchar(255) DEFAULT NULL,
  111.     `phone_number` varchar(255) DEFAULT NULL,
  112.     `email_address` varchar(255) DEFAULT NULL,
  113.     `enabled` tinyint(1) UNSIGNED DEFAULT NULL,
  114.     PRIMARY KEY  (`id`)
  115. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  116.  
  117. /* Table structure for table `client_configs` */
  118. DROP TABLE IF EXISTS `client_configs`;
  119. CREATE TABLE `client_configs` (
  120.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  121.     `client_id` int(11) UNSIGNED NOT NULL,
  122.     `variable` varchar(255) DEFAULT NULL,
  123.     `value` varchar(255) DEFAULT NULL,
  124.     PRIMARY KEY  (`id`),
  125.     KEY `FK_client_config__client` (`client_id`),
  126.     CONSTRAINT `client_configs_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
  127. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  128.  
  129. /* Table structure for table `sites` */
  130. DROP TABLE IF EXISTS `sites`;
  131. CREATE TABLE `sites` (
  132.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  133.     `client_id` int(11) UNSIGNED NOT NULL,
  134.     `name` varchar(255) NOT NULL DEFAULT '',
  135.     `theme` varchar(255) NOT NULL DEFAULT '',
  136.     `url` varchar(255) NOT NULL DEFAULT '',
  137.     PRIMARY KEY  (`id`),
  138.     UNIQUE KEY `url` (`url`),
  139.     KEY `FK_site__client` (`client_id`),
  140.     CONSTRAINT `sites_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
  141. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  142.  
  143.  
  144.  
  145. /*
  146.   -----------------------------------------------------------------------
  147.     User tables.
  148.   -----------------------------------------------------------------------
  149. */
  150. /* Users table */
  151. DROP TABLE IF EXISTS `users`;
  152. CREATE TABLE `users` (
  153.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  154.     `username` varchar(30) NOT NULL,
  155.     `first_name` varchar(255) NOT NULL,
  156.     `last_name` varchar(255) NOT NULL,
  157.     `password` char(32) NOT NULL,
  158.     `home_address_id` int(11) UNSIGNED DEFAULT NULL,
  159.     `work_address_id` int(11) UNSIGNED DEFAULT NULL,
  160.     `last_login` datetime  NOT NULL DEFAULT '0000-00-00 00:00:00',
  161.     `last_log_ip` varchar(15) DEFAULT NULL,
  162.     `avatar` varchar(150) DEFAULT '',
  163.     `email` varchar(255) DEFAULT '' COMMENT 'primary email addr for password retrieval',
  164.     `sig` text DEFAULT '',
  165.     `occupation` varchar(100) DEFAULT '',
  166.     `enabled` tinyint(1) UNSIGNED DEFAULT '1',
  167.     `confirmed` tinyint(1) UNSIGNED DEFAULT '0',
  168.     `confirm_code` varchar(32) DEFAULT '0',
  169.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  170.     PRIMARY KEY  (`id`),
  171.     KEY `FK_user__home_address` (`home_address_id`),
  172.     KEY `FK_user__work_address` (`work_address_id`),
  173.     CONSTRAINT `users_ibfk_1` FOREIGN KEY (`home_address_id`) REFERENCES `addresses` (`id`),
  174.     CONSTRAINT `users_ibfk_2` FOREIGN KEY (`work_address_id`) REFERENCES `addresses` (`id`)
  175. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  176.  
  177. /* User prefs table */
  178. DROP TABLE IF EXISTS `user_prefs`;
  179. CREATE TABLE user_prefs (
  180.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  181.     `user_id` int(11) UNSIGNED NOT NULL,
  182.     `name` varchar(255) NOT NULL,
  183.     `value` varchar(255) NOT NULL,
  184.     PRIMARY KEY  (`id`),
  185.     KEY `FK_user_pref__user` (`user_id`),
  186.     CONSTRAINT `user_prefs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  187. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  188.  
  189. /* User Ranks table */
  190. DROP TABLE IF EXISTS `user_ranks`;
  191. CREATE TABLE `user_ranks` (
  192.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  193.     `user_id` int(11) UNSIGNED NOT NULL,
  194.     `rank_id` smallint(3) UNSIGNED NOT NULL,
  195.     PRIMARY KEY (`id`),
  196.     KEY `FK_user_rank__user` (`user_id`),
  197.     KEY `FK_user_rank__rank` (`rank_id`),
  198.     CONSTRAINT `user_ranks_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  199.     CONSTRAINT `user_ranks_ibfk_2` FOREIGN KEY (`rank_id`) REFERENCES `ranks` (`id`)
  200. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  201.  
  202. /* Rank details table */
  203. DROP TABLE IF EXISTS `ranks`;
  204. CREATE TABLE `ranks` (
  205.     `id` smallint(3) UNSIGNED NOT NULL AUTO_INCREMENT,
  206.     `posts` smallint(5) UNSIGNED NOT NULL COMMENT 'number of post required to reach rank automatically',
  207.     `name` varchar(40) NOT NULL,
  208.     `graphic` varchar(100) DEFAULT NULL COMMENT 'filename of graphic associated with rank',
  209.     PRIMARY KEY (`id`)
  210. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  211.  
  212. /* Group details table */
  213. DROP TABLE IF EXISTS `groups`;
  214. CREATE TABLE `groups` (
  215.     `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  216.     `name` varchar(255) NOT NULL DEFAULT '',
  217.     `description` varchar(255) NOT NULL DEFAULT '',
  218.     `graphic` varchar(255) DEFAULT NULL COMMENT 'filename of graphic associated with group',
  219.     PRIMARY KEY (`id`)
  220. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  221.  
  222. /* User/Groups membership table */
  223. DROP TABLE IF EXISTS `user_groups`;
  224. CREATE TABLE `user_groups` (
  225.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  226.     `user_id` int(11) UNSIGNED NOT NULL,
  227.     `group_id` mediumint(8) UNSIGNED NOT NULL,
  228.     PRIMARY KEY (`id`),
  229.     KEY `FK_user_group__user` (`user_id`),
  230.     KEY `FK_user_group__group` (`group_id`),
  231.     CONSTRAINT `user_groups_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  232.     CONSTRAINT `user_groups_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`)
  233. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  234.  
  235. /* Table structure for table `addresses` */
  236. DROP TABLE IF EXISTS `addresses`;
  237. CREATE TABLE `addresses` (
  238.     `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  239.     `user_id` int(10) UNSIGNED NOT NULL COMMENT 'User ID',
  240.     `addr1` varchar(255) DEFAULT NULL,
  241.     `addr2` varchar(255) DEFAULT NULL,
  242.     `addr3` varchar(255) DEFAULT NULL,
  243.     `addr4` varchar(255) DEFAULT NULL,
  244.     `addr5` varchar(255) DEFAULT NULL,
  245.     `city` varchar(255) DEFAULT NULL,
  246.     `state` varchar(255) DEFAULT NULL,
  247.     `country_id` int(3) UNSIGNED NOT NULL,
  248.     `postcode` varchar(100) DEFAULT NULL,
  249.     `tel` varchar(255) DEFAULT NULL,
  250.     `tel2` varchar(255) DEFAULT NULL,
  251.     `tel3` varchar(255) DEFAULT NULL,
  252.     `mobile` varchar(255) DEFAULT NULL,
  253.     `mobile2` varchar(255) DEFAULT NULL,
  254.     `fax` varchar(255) DEFAULT NULL,
  255.     `fax2` varchar(255) DEFAULT NULL,
  256.     `email` varchar(255) DEFAULT NULL,
  257.     `email2` varchar(255) DEFAULT NULL,
  258.     `web` varchar(255) DEFAULT NULL,
  259.     `web2` varchar(255) DEFAULT NULL,
  260.     `msn` varchar(255) DEFAULT NULL,
  261.     `icq` varchar(15) DEFAULT NULL,
  262.     `skype` varchar(255) DEFAULT NULL,
  263.     `longitude` varchar(10) DEFAULT NULL COMMENT 'Geocode longitude coord',
  264.     `latitude` varchar(10) DEFAULT NULL COMMENT 'Geocode latitude coord',
  265.     `created` timestamp NULL DEFAULT NULL,
  266.     `modified` timestamp NULL DEFAULT NULL,
  267.     PRIMARY KEY  (`id`),
  268.     KEY `FK_address__user` (`user_id`),
  269.     KEY `FK_address__country` (`country_id`),
  270.     CONSTRAINT `addresses_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  271.     CONSTRAINT `addresses_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
  272. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  273.  
  274. /* Add the superadmin group */
  275. INSERT INTO `groups` (`id`,`name`,`description`) VALUES ('1','superadmins','Site Administrators Group');
  276. /* Add the guest user */
  277. INSERT INTO `users` (`id`,`username`,`password`,`first_name`,`last_name`) VALUES ('1','guest',md5('guest'),'Guest','User');
  278.  
  279.  
  280.  
  281. /*
  282.   -----------------------------------------------------------------------
  283.     User Privileges and Access Control tables.
  284.   -----------------------------------------------------------------------
  285. */
  286. /* Access Controls */
  287. DROP TABLE IF EXISTS `acos`;
  288. CREATE TABLE `acos` (
  289.     `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  290.     `model` varchar(255) NOT NULL DEFAULT '',
  291.     `object_id` int(10) UNSIGNED NULL DEFAULT NULL,
  292.     `alias` varchar(255) NOT NULL DEFAULT '',
  293.     `lft` int(10) NULL DEFAULT NULL,
  294.     `rght` int(10) NULL DEFAULT NULL,
  295.     PRIMARY KEY(id)
  296. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  297.  
  298. DROP TABLE IF EXISTS `aros_acos`;
  299. CREATE TABLE `aros_acos` (
  300.     `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  301.     `aro_id` int(10) UNSIGNED NOT NULL,
  302.     `aco_id` int(10) UNSIGNED NOT NULL,
  303.     `_create` tinyint(1) NOT NULL DEFAULT 0,
  304.     `_read` tinyint(1) NOT NULL DEFAULT 0,
  305.     `_update` tinyint(1) NOT NULL DEFAULT 0,
  306.     `_delete` tinyint(1) NOT NULL DEFAULT 0,
  307.     PRIMARY KEY(id)
  308. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  309.  
  310. DROP TABLE IF EXISTS `aros`;
  311. CREATE TABLE `aros` (
  312.     `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  313.     `model` varchar(255) NOT NULL DEFAULT '',
  314.     `foreign_key` int(10) UNSIGNED NULL DEFAULT NULL,
  315.     `alias` varchar(255) NOT NULL DEFAULT '',
  316.     `lft` int(10) NULL DEFAULT NULL,
  317.     `rght` int(10) NULL DEFAULT NULL,
  318.     PRIMARY KEY(id)
  319. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  320.  
  321.  
  322.  
  323.  
  324. /*
  325.   -----------------------------------------------------------------------
  326.     Images and Image Cache Tables.
  327.   -----------------------------------------------------------------------
  328. */
  329. /* Images table */
  330. DROP TABLE IF EXISTS `images`;
  331. CREATE TABLE `images` (
  332.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  333.     `client_id` int(11) UNSIGNED NOT NULL,
  334.     `user_id` int(11) UNSIGNED NOT NULL,
  335.     `filename` varchar(100) NOT NULL DEFAULT '',
  336.     `type` char(3) NOT NULL DEFAULT '',
  337.     `title` varchar(255) DEFAULT NULL,
  338.     `description` text DEFAULT NULL,
  339.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  340.     PRIMARY KEY  (`id`),
  341.     KEY `FK_image__client` (`client_id`),
  342.     KEY `FK_image__user` (`user_id`),
  343.     CONSTRAINT `images_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  344.     CONSTRAINT `images_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  345. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  346.  
  347. /* Image Cache table */
  348. DROP TABLE IF EXISTS `cached_images`;
  349. CREATE TABLE `cached_images` (
  350.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  351.     `cache_code` varchar(36) NOT NULL,
  352.     `image_id` int(11) UNSIGNED NOT NULL,
  353.     `scale` varchar(10) DEFAULT NULL,
  354.     `x_dim` smallint(4) UNSIGNED NOT NULL DEFAULT '0',
  355.     `y_dim` smallint(4) UNSIGNED NOT NULL DEFAULT '0',
  356.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  357.     PRIMARY KEY  (`id`),
  358.     KEY `FK_cached_image__image` (`image_id`),
  359.   CONSTRAINT `cached_images_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`)
  360. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  361.  
  362.  
  363.  
  364. /*
  365.   -----------------------------------------------------------------------
  366.     File Tables.
  367.   -----------------------------------------------------------------------
  368. */
  369. /* file categories table */
  370. DROP TABLE IF EXISTS `file_categories`;
  371. CREATE TABLE `file_categories` (
  372.     `id` int(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  373.     `client_id` int(11) UNSIGNED NOT NULL,
  374.     `user_id` int(11) UNSIGNED NOT NULL,
  375.     `name` varchar(255) DEFAULT NULL,
  376.     `title` varchar(100) DEFAULT NULL,
  377.     `description` varchar(255) DEFAULT NULL,
  378.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  379.     PRIMARY KEY  (`id`),
  380.     KEY `FK_file_category__client` (`client_id`),
  381.     KEY `FK_file_category__user` (`user_id`),
  382.     CONSTRAINT `file_categories_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  383.     CONSTRAINT `file_categories_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  384. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  385.  
  386. /* file categories table */
  387. DROP TABLE IF EXISTS `files`;
  388. CREATE TABLE `files` (
  389.     `id` int(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  390.     `client_id` int(11) UNSIGNED NOT NULL,
  391.     `user_id` int(11) UNSIGNED NOT NULL,
  392.     `file_category_id` int(11) UNSIGNED NOT NULL,
  393.     `name` varchar(255) DEFAULT NULL COMMENT 'filename of the file',
  394.     `path` varchar(100) DEFAULT NULL COMMENT 'path to file',
  395.     `title` varchar(255) DEFAULT NULL,
  396.     `description` varchar(255) DEFAULT NULL,
  397.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  398.     PRIMARY KEY  (`id`),
  399.     KEY `FK_file__client` (`client_id`),
  400.     KEY `FK_file__user` (`user_id`),
  401.     KEY `FK_file__file_category` (`user_id`),
  402.     CONSTRAINT `files_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  403.     CONSTRAINT `files_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  404.     CONSTRAINT `files_ibfk_3` FOREIGN KEY (`file_category_id`) REFERENCES `file_categories` (`id`)
  405. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  406.  
  407.  
  408.  
  409. /*
  410.   -----------------------------------------------------------------------
  411.     Website Tables - Website Site Content.
  412.   -----------------------------------------------------------------------
  413. */
  414. /* Page categories table */
  415. DROP TABLE IF EXISTS `page_categories`;
  416. CREATE TABLE `page_categories` (
  417.     `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  418.     `client_id` int(11) UNSIGNED NOT NULL,
  419.     `user_id` int(11) UNSIGNED NOT NULL,
  420.     `name` varchar(100) DEFAULT NULL,
  421.     `title` varchar(100) DEFAULT NULL,
  422.     `description` varchar(255) DEFAULT NULL,
  423.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  424.     `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  425.     PRIMARY KEY  (`id`),
  426.     KEY `FK_page_category__client` (`client_id`),
  427.     KEY `FK_page_category__user` (`user_id`),
  428.     CONSTRAINT `page_categories_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  429.     CONSTRAINT `page_categories_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  430. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  431.  
  432. /* Pages table */
  433. DROP TABLE IF EXISTS `pages`;
  434. CREATE TABLE `pages` (
  435.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  436.     `page_category_id` smallint(5) UNSIGNED NOT NULL,
  437.     `parentid` int(11) UNSIGNED NOT NULL COMMENT 'id of the parent',
  438.     `user_id` int(11) UNSIGNED NOT NULL,
  439.     `name` varchar(255) DEFAULT NULL,
  440.     `title` varchar(255) DEFAULT NULL,
  441.     `url` varchar(100) DEFAULT NULL,
  442.     `description` varchar(255) DEFAULT NULL,
  443.     `keywords` varchar(255) DEFAULT NULL,
  444.     `content` blob DEFAULT NULL,
  445.     `allow_edit` tinyint(1) UNSIGNED DEFAULT '0',
  446.     `allow_comment` tinyint(1) UNSIGNED DEFAULT '0',
  447.     `enabled` tinyint(1) UNSIGNED DEFAULT '1',
  448.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  449.     `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  450.     PRIMARY KEY  (`id`),
  451.     KEY `FK_page__page_category` (`page_category_id`),
  452.     KEY `FK_page__parent` (`parentid`),
  453.     KEY `FK_page__user` (`user_id`),
  454.     CONSTRAINT `pages_ibfk_1` FOREIGN KEY (`page_category_id`) REFERENCES `page_categories` (`id`),
  455.     CONSTRAINT `pages_ibfk_2` FOREIGN KEY (`parentid`) REFERENCES `pages` (`id`),
  456.     CONSTRAINT `pages_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  457. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  458.  
  459. /* Page Cache table */
  460. DROP TABLE IF EXISTS `cached_pages`;
  461. CREATE TABLE `cached_pages` (
  462.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  463.     `page_id` int(11) UNSIGNED NOT NULL,
  464.     `cache_file` char(36) DEFAULT NULL,
  465.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  466.     `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  467.     PRIMARY KEY  (`id`),
  468.     KEY `FK_cached_page__page` (`page_id`),
  469.     CONSTRAINT `cached_pages_ibfk_1` FOREIGN KEY (`page_id`) REFERENCES `pages` (`id`)
  470. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  471.  
  472. /* Email Templates table */
  473. DROP TABLE IF EXISTS `mail_templates`;
  474. CREATE TABLE `mail_templates` (
  475.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  476.     `user_id` int(11) UNSIGNED NOT NULL,
  477.     `name` varchar(100) NOT NULL DEFAULT '',
  478.     `type` enum('html','text') DEFAULT NULL COMMENT 'email format type',
  479.     `content` blob DEFAULT NULL,
  480.     PRIMARY KEY  (`id`),
  481.     KEY `FK_mail_template__user` (`user_id`),
  482.     CONSTRAINT `mail_templates_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  483. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  484.  
  485. /* Web contacts table */
  486. DROP TABLE IF EXISTS `web_contacts`;
  487. CREATE TABLE `web_contacts` (
  488.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  489.     `client_id` int(11) UNSIGNED NOT NULL DEFAULT '0',
  490.     `full_name` varchar(255) NOT NULL DEFAULT '',
  491.     `company` varchar(255) DEFAULT NULL,
  492.     `tel` varchar(40) DEFAULT NULL,
  493.     `url` varchar(120) DEFAULT NULL,
  494.     `email` varchar(120) NOT NULL DEFAULT '',
  495.     `message` text NOT NULL,
  496.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  497.     `read` tinyint(1) UNSIGNED DEFAULT '0',
  498.     PRIMARY KEY  (`id`),
  499.     KEY `FK_web_contact__client` (`client_id`),
  500.     CONSTRAINT `web_contacts_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
  501. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  502.  
  503. /* Comments table */
  504. DROP TABLE IF EXISTS `comments`;
  505. CREATE TABLE `comments` (
  506.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  507.     `client_id` int(11) UNSIGNED NOT NULL,
  508.     `user_id` int(11) UNSIGNED NOT NULL,
  509.     `parentid` int(11) UNSIGNED DEFAULT NULL,
  510.     `objectid` int(11) UNSIGNED DEFAULT NULL,
  511.     `type` enum('page','image','blog','product','paste','bookmark','file') DEFAULT NULL COMMENT 'object type that this comment is related to',
  512.     `username` varchar(30) DEFAULT NULL COMMENT 'used for non members',
  513.     `moderated` tinyint(1) DEFAULT '0',
  514.     `title` varchar(255) DEFAULT NULL,
  515.     `comment` text NOT NULL,
  516.     `url` varchar(255) DEFAULT NULL,
  517.     `ip_address` varchar(15) DEFAULT NULL,
  518.     `country_id` int(3) UNSIGNED NOT NULL,
  519.     `longitude` varchar(10) DEFAULT NULL COMMENT 'Geocode longitude coord',
  520.     `latitude` varchar(10) DEFAULT NULL COMMENT 'Geocode latitude coord',
  521.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  522.     PRIMARY KEY  (`id`),
  523.     KEY `FK_comment__client` (`client_id`),
  524.     KEY `FK_comment__user` (`user_id`),
  525.     KEY `FK_comment__country` (`country_id`),
  526.     CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  527.     CONSTRAINT `comments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  528.     CONSTRAINT `comments_ibfk_3` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
  529. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  530.  
  531.  
  532.  
  533.  
  534. /*
  535.   -----------------------------------------------------------------------
  536.     Store Tables.
  537.   -----------------------------------------------------------------------
  538. */
  539. /* `stores` Table layout */
  540. DROP TABLE IF EXISTS `stores`;
  541. CREATE TABLE `stores` (
  542.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  543.     `client_id` int(11) UNSIGNED NOT NULL DEFAULT '0',
  544.     `name` varchar(255) NOT NULL DEFAULT '',
  545.     `enabled` tinyint(1) UNSIGNED DEFAULT '0',
  546.     `site_name` varchar(255) DEFAULT NULL,
  547.     PRIMARY KEY  (`id`),
  548.     KEY `FK_store__client` (`client_id`),
  549.     CONSTRAINT `stores_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
  550. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  551.  
  552. /* `product_categories` Table layout */
  553. DROP TABLE IF EXISTS `product_categories`;
  554. CREATE TABLE `product_categories` (
  555.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  556.     `store_id` int(11) UNSIGNED NOT NULL DEFAULT '0',
  557.     `parentid` int(11) UNSIGNED DEFAULT NULL,
  558.     `title` varchar(255) NOT NULL DEFAULT '',
  559.     `description` text DEFAULT '',
  560.     `image_id` int(11) UNSIGNED DEFAULT NULL,
  561.     PRIMARY KEY  (`id`),
  562.     KEY `FK_product_category__store` (`store_id`),
  563.     KEY `FK_product_category__image` (`image_id`),
  564.     CONSTRAINT `product_categories_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
  565.     CONSTRAINT `product_categories_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`)
  566. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  567.  
  568. /* `products` Table layout */
  569. DROP TABLE IF EXISTS `products`;
  570. CREATE TABLE `products` (
  571.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  572.     `store_id` int(11) UNSIGNED NOT NULL,
  573.     `product_category_id` int(11) UNSIGNED NOT NULL,
  574.     `name` varchar(255) NOT NULL DEFAULT '',
  575.     `description` blob NOT NULL,
  576.     `image_id` int(11) UNSIGNED DEFAULT NULL,
  577.     `price` float(4,2) NOT NULL DEFAULT '0',
  578.     `tax` float(2,4) DEFAULT '0.175' COMMENT 'Value Added Tax rate',
  579.     `weight` float DEFAULT '0',
  580.     `ref` varchar(30) DEFAULT NULL,
  581.     `post` float(4,2) DEFAULT '0',
  582.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  583.     `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  584.     PRIMARY KEY  (`id`),
  585.     KEY `FK_product__store` (`store_id`),
  586.     KEY `FK_product__product_category` (`product_category_id`),
  587.     KEY `FK_product__image` (`image_id`),
  588.     CONSTRAINT `products_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
  589.     CONSTRAINT `products_ibfk_2` FOREIGN KEY (`product_category_id`) REFERENCES `product_categories` (`id`),
  590.     CONSTRAINT `products_ibfk_3` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`)
  591. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  592.  
  593. /* Table layout for `variations` */
  594. DROP TABLE IF EXISTS `variations`;
  595. CREATE TABLE `variations` (
  596.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  597.     `store_id` int(11) UNSIGNED NOT NULL,
  598.     `product_id` int(11) UNSIGNED NOT NULL,
  599.     `price` float(4,2) DEFAULT '0',
  600.     `tax` float(2,4) DEFAULT '0',
  601.     `weight` float DEFAULT '0',
  602.     `ref` varchar(30) DEFAULT NULL,
  603.     `post` float(4,2) DEFAULT '0',
  604.     `name` varchar(255) DEFAULT NULL,
  605.     `description` text DEFAULT NULL,
  606.     PRIMARY KEY  (`id`),
  607.     KEY `FK_variation__store` (`store_id`),
  608.     KEY `FK_variation__product` (`product_id`),
  609.     CONSTRAINT `variations_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
  610.     CONSTRAINT `variations_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
  611. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  612.  
  613. /* Table layout for `orders` */
  614. DROP TABLE IF EXISTS `orders`;
  615. CREATE TABLE `orders` (
  616.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  617.     `store_id` int(11) UNSIGNED NOT NULL,
  618.     `user_id` int(11) UNSIGNED NOT NULL,
  619.     `parentid` int(11) UNSIGNED DEFAULT NULL COMMENT 'ID of parent for orders with multiple components',
  620.     `order_code` varchar(255) NOT NULL DEFAULT '' COMMENT 'unique id usually from the payment processor',
  621.     `product_id` int(11) UNSIGNED NOT NULL,
  622.     `variation_id` int(11) UNSIGNED DEFAULT NULL,
  623.     `qty` int(11) UNSIGNED NOT NULL DEFAULT '1',
  624.     `price` float NOT NULL DEFAULT '0',
  625.     `tax` float DEFAULT '0',
  626.     `weight` float DEFAULT '0',
  627.     `post` float DEFAULT '0',
  628.     `despatched` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  629.     `firstname` varchar(255) NOT NULL DEFAULT '',
  630.     `lastname` varchar(255) NOT NULL DEFAULT '',
  631.     `delivery_address_id` int(11) UNSIGNED DEFAULT NULL,
  632.     `billing_address_id` int(11) UNSIGNED DEFAULT NULL,
  633.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  634.     `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  635.     PRIMARY KEY  (`id`),
  636.     KEY `FK_order__store` (`store_id`),
  637.     KEY `FK_order__user` (`user_id`),
  638.     KEY `FK_order__product` (`product_id`),
  639.     KEY `FK_order__variation` (`variation_id`),
  640.     KEY `FK_order__delivery_address` (`delivery_address_id`),
  641.     KEY `FK_order__billing_address` (`billing_address_id`),
  642.     CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
  643.     CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  644.     CONSTRAINT `orders_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  645.     CONSTRAINT `orders_ibfk_4` FOREIGN KEY (`variation_id`) REFERENCES `variations` (`id`),
  646.     CONSTRAINT `orders_ibfk_5` FOREIGN KEY (`delivery_address_id`) REFERENCES `addresses` (`id`),
  647.     CONSTRAINT `orders_ibfk_6` FOREIGN KEY (`billing_address_id`) REFERENCES `addresses` (`id`)
  648. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  649.  
  650. /* Table layout for `transactions` */
  651. DROP TABLE IF EXISTS `transactions`;
  652. CREATE TABLE `transactions` (
  653.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  654.     `code` varchar(50) DEFAULT NULL,
  655.     `store_id` int(11) UNSIGNED NOT NULL,
  656.     `detail` text NOT NULL DEFAULT '',
  657.     `session_id` varchar(32) NOT NULL DEFAULT '',
  658.     `order_id` int(11) UNSIGNED NOT NULL,
  659.     `status` varchar(10) DEFAULT NULL,
  660.     PRIMARY KEY  (`id`),
  661.     KEY `FK_transaction__store` (`store_id`),
  662.     KEY `FK_transaction__order` (`order_id`),
  663.     CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `stores` (`id`),
  664.     CONSTRAINT `transactions_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`)
  665. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  666.  
  667.  
  668.  
  669.  
  670. /*
  671.   -----------------------------------------------------------------------
  672.     Gallery Tables.
  673.   -----------------------------------------------------------------------
  674. */
  675. /* Galleries table */
  676. DROP TABLE IF EXISTS `galleries`;
  677. CREATE TABLE `galleries` (
  678.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  679.     `client_id` int(11) UNSIGNED NOT NULL,
  680.     `user_id` int(11) UNSIGNED NOT NULL,
  681.     `title` varchar(255) DEFAULT NULL,
  682.     `name` varchar(255) NOT NULL,
  683.     `description` text NOT NULL DEFAULT '',
  684.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  685.     PRIMARY KEY  (`id`),
  686.     KEY `FK_gallery__client` (`client_id`),
  687.     KEY `FK_gallery__user` (`user_id`),
  688.     CONSTRAINT `galleries_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  689.     CONSTRAINT `galleries_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  690. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  691.  
  692. /* Gallery images table */
  693. DROP TABLE IF EXISTS `gallery_images`;
  694. CREATE TABLE `gallery_images` (
  695.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  696.     `gallery_id` int(11) UNSIGNED NOT NULL,
  697.     `image_id` int(11) UNSIGNED NOT NULL,
  698.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  699.     PRIMARY KEY  (`id`),
  700.     KEY `FK_gallery_image__gallery` (`gallery_id`),
  701.     KEY `FK_gallery_image__image` (`image_id`),
  702.     CONSTRAINT `gallery_images_ibfk_1` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`),
  703.     CONSTRAINT `gallery_images_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `images` (`id`)
  704. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  705.  
  706.  
  707.  
  708.  
  709. /*
  710.   -----------------------------------------------------------------------
  711.     Blog Tables.
  712.   -----------------------------------------------------------------------
  713. */
  714. /* Blog Archives table */
  715. DROP TABLE IF EXISTS `blogs`;
  716. CREATE TABLE `blogs` (
  717.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  718.     `client_id` int(11) UNSIGNED NOT NULL,
  719.     `user_id` int(11) UNSIGNED NOT NULL,
  720.     `title` varchar(255) DEFAULT NULL,
  721.     `name` varchar(255) NOT NULL,
  722.     `description` text NOT NULL DEFAULT '',
  723.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  724.     PRIMARY KEY  (`id`),
  725.     KEY `FK_blog__client` (`client_id`),
  726.     KEY `FK_blog__user` (`user_id`),
  727.     CONSTRAINT `blogs_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  728.     CONSTRAINT `blogs_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  729. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  730.  
  731. /* Blog Archives table */
  732. DROP TABLE IF EXISTS `archives`;
  733. CREATE TABLE `archives` (
  734.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  735.     `client_id` int(11) UNSIGNED NOT NULL,
  736.     `user_id` int(11) UNSIGNED NOT NULL,
  737.     `blog_id` int(11) UNSIGNED NOT NULL,
  738.     `title` varchar(255) DEFAULT NULL,
  739.     `name` varchar(255) NOT NULL,
  740.     `content` blob NOT NULL DEFAULT '',
  741.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  742.     `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  743.     PRIMARY KEY  (`id`),
  744.     KEY `FK_archive__client` (`client_id`),
  745.     KEY `FK_archive__user` (`user_id`),
  746.     KEY `FK_archive__blog` (`blog_id`),
  747.     CONSTRAINT `archives_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  748.     CONSTRAINT `archives_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  749.     CONSTRAINT `archives_ibfk_3` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`)
  750. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  751.  
  752.  
  753.  
  754.  
  755. /*
  756.   -----------------------------------------------------------------------
  757.     Bookmarks Tables.
  758.   -----------------------------------------------------------------------
  759. */
  760. /* Bookmark Categories table */
  761. DROP TABLE IF EXISTS `bookmark_categories`;
  762. CREATE TABLE `bookmark_categories` (
  763.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  764.     `client_id` int(11) UNSIGNED NOT NULL,
  765.     `user_id` int(11) UNSIGNED NOT NULL,
  766.     `parentid` int(11) UNSIGNED NOT NULL,
  767.     `name` varchar(255) NOT NULL,
  768.     `description` varchar(255) NOT NULL DEFAULT '',
  769.     `image` varchar(255) DEFAULT NULL,
  770.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  771.     PRIMARY KEY  (`id`),
  772.     KEY `FK_bookmark_category__client` (`client_id`),
  773.     KEY `FK_bookmark_category__user` (`user_id`),
  774.     CONSTRAINT `bookmark_categories_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  775.     CONSTRAINT `bookmark_categories_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  776. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  777.  
  778. /* Bookmarks table */
  779. DROP TABLE IF EXISTS `bookmarks`;
  780. CREATE TABLE `bookmarks` (
  781.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  782.     `user_id` int(11) UNSIGNED NOT NULL,
  783.     `bookmark_category_id` int(11) UNSIGNED NOT NULL,
  784.     `name` varchar(255) NOT NULL,
  785.     `url` varchar(255) NOT NULL,
  786.     `description` varchar(255) NOT NULL DEFAULT '',
  787.     `image` varchar(255) DEFAULT NULL,
  788.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  789.     PRIMARY KEY  (`id`),
  790.     KEY `FK_bookmark__user` (`user_id`),
  791.     KEY `FK_bookmark__bookmark_category` (`bookmark_category_id`),
  792.     CONSTRAINT `bookmarks_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  793.     CONSTRAINT `bookmarks_ibfk_2` FOREIGN KEY (`bookmark_category_id`) REFERENCES `bookmark_categories` (`id`)
  794. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  795.  
  796.  
  797.  
  798.  
  799. /*
  800.   -----------------------------------------------------------------------
  801.     Notes Tables.
  802.   -----------------------------------------------------------------------
  803. */
  804. /* Note Categories table */
  805. DROP TABLE IF EXISTS `note_categories`;
  806. CREATE TABLE `note_categories` (
  807.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  808.     `client_id` int(11) UNSIGNED NOT NULL,
  809.     `user_id` int(11) UNSIGNED NOT NULL,
  810.     `parentid` int(11) UNSIGNED NOT NULL,
  811.     `name` varchar(255) NOT NULL,
  812.     `description` varchar(255) NOT NULL DEFAULT '',
  813.     `image` varchar(255) DEFAULT NULL,
  814.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  815.     `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  816.     PRIMARY KEY  (`id`),
  817.     KEY `FK_note_category__client` (`client_id`),
  818.     KEY `FK_note_category__user` (`user_id`),
  819.     CONSTRAINT `note_categories_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  820.     CONSTRAINT `note_categories_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  821. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  822.  
  823. /* Notes table */
  824. DROP TABLE IF EXISTS `notes`;
  825. CREATE TABLE `notes` (
  826.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  827.     `user_id` int(11) UNSIGNED NOT NULL,
  828.     `note_category_id` int(11) UNSIGNED NOT NULL,
  829.     `name` varchar(255) NOT NULL,
  830.     `content` text NOT NULL DEFAULT '',
  831.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  832.     `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  833.     PRIMARY KEY  (`id`),
  834.     KEY `FK_note__user` (`user_id`),
  835.     KEY `FK_note__note_category` (`note_category_id`),
  836.     CONSTRAINT `notes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  837.     CONSTRAINT `notes_ibfk_2` FOREIGN KEY (`note_category_id`) REFERENCES `note_categories` (`id`)
  838. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  839.  
  840.  
  841.  
  842. /*
  843.   -----------------------------------------------------------------------
  844.     Tasks Tables.
  845.   -----------------------------------------------------------------------
  846. */
  847. /* Table structure for table `task_categories` */
  848. DROP TABLE IF EXISTS `task_categories`;
  849. CREATE TABLE `task_categories` (
  850.     `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  851.     `client_id` int(10) UNSIGNED NOT NULL,
  852.     `user_id` int(11) UNSIGNED NOT NULL,
  853.     `name` varchar(255) DEFAULT NULL,
  854.     `description` varchar(255) DEFAULT NULL,
  855.     PRIMARY KEY  (`id`),
  856.     KEY `FK_task_category__client` (`client_id`),
  857.     CONSTRAINT `task_categories_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
  858. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  859.  
  860. /* Table structure for table `tasks` */
  861. DROP TABLE IF EXISTS `tasks`;
  862. CREATE TABLE `tasks` (
  863.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  864.     `client_id` int(11) UNSIGNED NOT NULL,
  865.     `task_category_id` int(11) UNSIGNED DEFAULT NULL,
  866.     `user_id` int(11) UNSIGNED DEFAULT NULL,
  867.     `name` varchar(255) DEFAULT NULL,
  868.     `description` text,
  869.     `scheduled_for` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  870.     `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  871.     `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  872.     PRIMARY KEY  (`id`),
  873.     KEY `FK_task__task_category` (`task_category_id`),
  874.     KEY `FK_task__client` (`client_id`),
  875.     KEY `FK_task__user` (`user_id`),
  876.     CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`task_category_id`) REFERENCES `task_categories` (`id`),
  877.     CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  878.     CONSTRAINT `tasks_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  879. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  880.  
  881.  
  882.  
  883. /*
  884.   -----------------------------------------------------------------------
  885.     Adserve Tables.
  886.   -----------------------------------------------------------------------
  887. */
  888. /* Banner Ads table */
  889. DROP TABLE IF EXISTS `adserve_ads`;
  890. CREATE TABLE `adserve_ads` (
  891.     `id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  892.     `client_id` int(11) UNSIGNED NOT NULL,
  893.     `url` varchar(255) DEFAULT '',
  894.     `alt` varchar(255) DEFAULT '',
  895.     `type` varchar(20) NOT NULL DEFAULT 'swf',
  896.     `width` mediumint(8) NOT NULL DEFAULT '0',
  897.     `height` mediumint(8) NOT NULL DEFAULT '0',
  898.     PRIMARY KEY  (`id`),
  899.     KEY `FK_adserve_ad__client` (`client_id`),
  900.     CONSTRAINT `adserve_ads_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
  901. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  902.  
  903.  
  904.  
  905.  
  906. /*
  907.   -----------------------------------------------------------------------
  908.     Visitor Tracker tables.
  909.   -----------------------------------------------------------------------
  910. */
  911. /* Visits table */
  912. DROP TABLE IF EXISTS `visits`;
  913. CREATE TABLE `visits` (
  914.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  915.     `client_id` int(11) UNSIGNED NOT NULL,
  916.     `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  917.     `ip` char(15) DEFAULT NULL,
  918.     `country_id` int(3) UNSIGNED DEFAULT NULL,
  919.     `host` varchar(75) DEFAULT NULL,
  920.     `referer` varchar(250) DEFAULT NULL,
  921.     `referer_host` varchar(75) DEFAULT NULL,
  922.     `client` varchar(250) DEFAULT NULL,
  923.     `browser` varchar(25) DEFAULT NULL,
  924.     `os` varchar(25) DEFAULT NULL,
  925.     `engine` varchar(25) DEFAULT NULL,
  926.     `terms` varchar(150) DEFAULT NULL,
  927.     `entry` varchar(150) DEFAULT NULL,
  928.     `mail` varchar(255) DEFAULT NULL COMMENT 'an ID used for mail promotions',
  929.     PRIMARY KEY  (`id`),
  930.     KEY `FK_visit__client` (`client_id`),
  931.     KEY `FK_visit__country` (`country_id`),
  932.     CONSTRAINT `visits_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  933.     CONSTRAINT `visits_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
  934. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  935.  
  936. /* Visitor Tracks table */
  937. DROP TABLE IF EXISTS `tracks`;
  938. CREATE TABLE `tracks` (
  939.     `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  940.     `visit_id` int(11) UNSIGNED NOT NULL,
  941.     `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  942.     `page` varchar(150) DEFAULT NULL,
  943.     `query` varchar(150) DEFAULT NULL,
  944.     PRIMARY KEY  (`id`),
  945.     KEY `FK_track__visit` (`visit_id`),
  946.     CONSTRAINT `tracks_ibfk_1` FOREIGN KEY (`visit_id`) REFERENCES `visits` (`id`)
  947. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  948.  
  949. /* Downloads tracking table */
  950. DROP TABLE IF EXISTS `dl_visits`;
  951. CREATE TABLE dl_visits (
  952.     `id` INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  953.     `client_id` int(11) UNSIGNED NOT NULL,
  954.     `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  955.     `ip` char(15) DEFAULT NULL,
  956.     `country_id` int(3) UNSIGNED DEFAULT NULL,
  957.     `host` varchar(75) DEFAULT NULL,
  958.     `referer` varchar(250) DEFAULT NULL,
  959.     `referer_host` varchar(75) DEFAULT NULL,
  960.     `client` varchar(250) DEFAULT NULL,
  961.     `browser` varchar(25) DEFAULT NULL,
  962.     `os` varchar(25) DEFAULT NULL,
  963.     `engine` varchar(25) DEFAULT NULL,
  964.     `terms` varchar(150) DEFAULT NULL,
  965.     `file` varchar(150) NOT NULL,
  966.     `size` int(11) DEFAULT NULL,
  967.     `mail` varchar(255) DEFAULT NULL,
  968.     PRIMARY KEY  (`id`),
  969.     KEY `FK_dl_visit__client` (`client_id`),
  970.     KEY `FK_dl_visit__country` (`country_id`),
  971.     CONSTRAINT `dl_visits_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  972.     CONSTRAINT `dl_visits_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
  973. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  974.  
  975.  
  976.  
  977. /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  978. /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  979.  
  980.  

PermaLink to this entry https://pastebin.co.uk/11764
Posted by Anonymous Mon 12th Mar 2007 13:56 - Syntax is SQL - 75 views
Download | New Post | Modify | Hide line numbers

 

Comments: 0