database.sql

   1 CREATE TABLE IF NOT EXISTS dns_soa (
   2         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   3         origin VARCHAR(255) NOT NULL,
   4         ns VARCHAR(255) NOT NULL,
   5         mbox VARCHAR(255) NOT NULL,
   6         serial INT(10) NOT NULL DEFAULT '1',
   7         refresh INT(10) NOT NULL DEFAULT '28800',
   8         retry INT(10) NOT NULL DEFAULT '7200',
   9         expire INT(10) NOT NULL DEFAULT '604800',
  10         minimum INT(10) NOT NULL DEFAULT '86400',
  11         ttl INT(10) NOT NULL DEFAULT '86400',
  12         active boolean NOT NULL DEFAULT 1,
  13         UNIQUE KEY origin (origin)
  14 ) ENGINE=InnoDB;
  15
  16 CREATE TABLE IF NOT EXISTS dns_rr (
  17         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  18         zone INT(10) NOT NULL,
  19         name VARCHAR(255) NOT NULL,
  20         data TEXT,
  21         aux INT(10) NOT NULL,
  22         ttl INT(10) NOT NULL DEFAULT '86400',
  23         type enum('A', 'AAAA', 'CNAME', 'MX', 'PTR', 'SRV', 'TXT', 'TLSA', 'NS', 'DS') DEFAULT NULL,
  24         active boolean NOT NULL DEFAULT 1
  25 ) ENGINE=InnoDB;
  26
  27 CREATE TABLE IF NOT EXISTS dns_sec (
  28         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  29         zone INT(10) NOT NULL,
  30         type enum('ZSK','KSK') DEFAULT NULL,
  31         algo VARCHAR(255) NOT NULL,
  32         public TEXT,
  33         private TEXT,
  34         dsset TEXT,
  35         active boolean NOT NULL DEFAULT 1
  36 ) ENGINE=InnoDB;
  37
  38 CREATE TABLE IF NOT EXISTS dns_user (
  39         userID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  40         username VARCHAR(255) NOT NULL,
  41         email VARCHAR(255) NOT NULL,
  42         password VARCHAR(255) NOT NULL,
  43         reseller INT(10) NOT NULL DEFAULT '0',
  44         status INT(10) NOT NULL DEFAULT '0',
  45         UNIQUE KEY dns_user (username, email, password)
  46 ) ENGINE=InnoDB;
  47
  48 CREATE TABLE IF NOT EXISTS dns_soa_to_user (
  49         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  50         userID INT(10) NOT NULL,
  51         soaID INT(10) NOT NULL,
  52         UNIQUE KEY dns_soa_to_user (userID, soaID)
  53 ) ENGINE=InnoDB;
  54
  55 CREATE TABLE IF NOT EXISTS dns_options (
  56         optionID int(255) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  57         optionName VARCHAR(255) NOT NULL DEFAULT '',
  58         optionValue MEDIUMTEXT,
  59         UNIQUE KEY optionName (optionName)
  60 ) ENGINE=InnoDB;
  61
  62 CREATE TABLE IF NOT EXISTS dns_api (
  63         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  64         userID INT(10) NOT NULL,
  65         apiKey VARCHAR(255) NOT NULL
  66 ) ENGINE=InnoDB;
  67
  68 CREATE TABLE IF NOT EXISTS dns_template (
  69         templateID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  70         userID INT(10) NOT NULL,
  71         template TEXT
  72 ) ENGINE=InnoDB;
  73
  74 CREATE TABLE IF NOT EXISTS dns_session (
  75         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  76         sessionID VARCHAR(255) NOT NULL DEFAULT '',
  77         expire INT(10) NOT NULL,
  78         sessionData TEXT
  79 ) ENGINE=InnoDB;
  80
  81 CREATE TABLE IF NOT EXISTS dns_permissions (
  82         permissionID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  83         permission VARCHAR(255) NOT NULL
  84 ) ENGINE=InnoDB;
  85
  86 CREATE TABLE IF NOT EXISTS dns_permissions_to_user (
  87         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  88         userID INT(10) NOT NULL,
  89         permissionID INT(10) NOT NULL
  90 ) ENGINE=InnoDB;
  91
  92 CREATE TABLE IF NOT EXISTS dns_language (
  93         id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  94         languageID INT(10) NOT NULL,
  95         languageItem VARCHAR(255) NOT NULL,
  96         languageValue VARCHAR(255) NOT NULL
  97 ) ENGINE=InnoDB;
  98
  99
 100
 101 ALTER TABLE dns_api ADD FOREIGN KEY (userID) REFERENCES dns_user (userID) ON DELETE CASCADE;
 102 ALTER TABLE dns_sec ADD FOREIGN KEY (zone) REFERENCES dns_soa (id) ON DELETE CASCADE;
 103 ALTER TABLE dns_rr ADD FOREIGN KEY (zone) REFERENCES dns_soa (id) ON DELETE CASCADE;
 104 ALTER TABLE dns_soa_to_user ADD FOREIGN KEY (userID) REFERENCES dns_user (userID) ON DELETE CASCADE;
 105 ALTER TABLE dns_soa_to_user ADD FOREIGN KEY (soaID) REFERENCES dns_soa (id) ON DELETE CASCADE;
 106 ALTER TABLE dns_template ADD FOREIGN KEY (userID) REFERENCES dns_user (userID) ON DELETE CASCADE;
 107 ALTER TABLE dns_permissions_to_user ADD FOREIGN KEY (userID) REFERENCES dns_user (userID) ON DELETE CASCADE;
 108 ALTER TABLE dns_permissions_to_user ADD FOREIGN KEY (permissionID) REFERENCES dns_permissions (permissionID) ON DELETE CASCADE;
 109
 110 INSERT INTO dns_options VALUES (1, 'dns_api_key', '0E2372C5-E5A3-424B-82E5-75AD723A9447');
 111 INSERT INTO dns_options VALUES (2, 'offline', '0');
 112 INSERT INTO dns_options VALUES (3, 'enable_debug', '1');
 113 INSERT INTO dns_options VALUES (4, 'dns_default_records', '{domain}:NS:ns1.stricted.de.\n{domain}:NS:ns2.stricted.de.\n{domain}:NS:ns3.stricted.de.\n{domain}:NS:ns4.stricted.de.\n{domain}:NS:ns5.stricted.de.\n{domain}:MX:mail.{domain}\n{domain}:A:84.200.248.52\n{domain}:AAAA:2001:1608:12:1::def\n*.{domain}:A:84.200.248.52\n*.{domain}:AAAA:2001:1608:12:1::def\n{domain}:TXT:\"v=spf1 mx -all\"');
 114 INSERT INTO dns_options VALUES (5, 'dns_soa_mbox', 'info.stricted.de.');
 115 INSERT INTO dns_options VALUES (6, 'dns_soa_ns', 'ns1.stricted.de.');
 116 INSERT INTO dns_options VALUES (7, 'dns_soa_ttl', '86400');
 117 INSERT INTO dns_options VALUES (8, 'dns_soa_refresh', '28800');
 118 INSERT INTO dns_options VALUES (9, 'dns_soa_retry', '7200');
 119 INSERT INTO dns_options VALUES (10, 'dns_soa_expire', '604800');
 120 INSERT INTO dns_options VALUES (11, 'dns_soa_minimum_ttl', '60');
 121 INSERT INTO dns_options VALUES (12, 'enable_dnssec', '1');
 122 INSERT INTO dns_options VALUES (13, 'cache_source_type', 'disk');
 123
 124
 125 INSERT INTO `dns_user` VALUES (1, 'admin', 'example@example.net', '$2a$08$XfcfTGc1LlmOHWUt/2sfNeFLEwqESy6wmrIIJMyQS1j5pwembqiae', '0', '2');