diff --git a/config/application.rb b/config/application.rb index fc33cf4..459c867 100644 --- a/config/application.rb +++ b/config/application.rb @@ -1,41 +1,42 @@ require File.expand_path('../boot', __FILE__) require 'rails/all' # Production doesn't use bundler # you've limited to :test, :development, or :production. if ENV['RAILS_ENV'] != 'production' Bundler.require(*Rails.groups) else # Dependencies to load before starting rails in production require 'jquery-rails' require 'beaneater' end module Base class Application < Rails::Application # Settings in config/environments/* take precedence over those specified here. # Application configuration should go into files in config/initializers # -- all .rb files in that directory are automatically loaded. # Store/Read localtime from the database config.active_record.default_timezone = :local + config.active_record.schema_format = :sql # The default locale is :en and all translations from config/locales/*.rb,yml are auto loaded. # config.i18n.load_path += Dir[Rails.root.join('my', 'locales', '*.{rb,yml}').to_s] # config.i18n.default_locale = :de config.autoload_paths << Rails.root.join('lib') config.x = {} end def self.settings Application.config.x end def self.bean @bean ||= Bean::Client.new( YAML.load_file(Rails.root.join('config', 'beanstalk.yml'))[Rails.env].symbolize_keys[:host] ) end end diff --git a/db/constraints.sql b/db/constraints.sql new file mode 100644 index 0000000..9128250 --- /dev/null +++ b/db/constraints.sql @@ -0,0 +1,2 @@ +ALTER TABLE `records` ADD CONSTRAINT `records_ibfk_1` FOREIGN KEY (`domain_id`) +REFERENCES `domains` (`id`) ON DELETE CASCADE; diff --git a/db/pdns_schema.README b/db/pdns_schema.README new file mode 100644 index 0000000..16e8355 --- /dev/null +++ b/db/pdns_schema.README @@ -0,0 +1,26 @@ +Record +====== + +content: This is the 'right hand side' of a DNS record. For an A record, this +is the IP address for example. + +ttl: TTL of this record, in seconds. Must be a real value, no checking is +performed. + +prio: For MX records, this should be the priority of the mail exchanger +specified. + +qtype: The ASCII representation of the qtype of this record. Examples are 'A', +'MX', 'SOA', 'AAAA'. Make sure that this field returns an exact answer - +PowerDNS won't recognise 'A ' as 'A'. This can be achieved by using a VARCHAR +instead of a CHAR. + +domain_id: Each domain must have a unique domain_id. No two domains may share a +domain_id, all records in a domain should have the same. A number. + +name: Actual name of a record. Must not end in a '.' and be fully qualified - +it is not relative to the name of the domain! + +disabled: If set to true, this record is hidden from DNS clients, but can still +be modified from the REST API. See Disabled data. (Available since version +3.4.0.) diff --git a/db/pdns_schema.sql b/db/pdns_schema.sql new file mode 100644 index 0000000..002fe92 --- /dev/null +++ b/db/pdns_schema.sql @@ -0,0 +1,90 @@ +CREATE TABLE domains ( + id INT AUTO_INCREMENT, + name VARCHAR(255) NOT NULL, + master VARCHAR(128) DEFAULT NULL, + last_check INT DEFAULT NULL, + type VARCHAR(6) NOT NULL, + notified_serial INT DEFAULT NULL, + account VARCHAR(40) DEFAULT NULL, + PRIMARY KEY (id) +) Engine=InnoDB; + +CREATE UNIQUE INDEX name_index ON domains(name); + + +CREATE TABLE records ( + id INT AUTO_INCREMENT, + domain_id INT DEFAULT NULL, + name VARCHAR(255) DEFAULT NULL, + type VARCHAR(10) DEFAULT NULL, + content VARCHAR(64000) DEFAULT NULL, + ttl INT DEFAULT NULL, + prio INT DEFAULT NULL, + change_date INT DEFAULT NULL, + disabled TINYINT(1) DEFAULT 0, + ordername VARCHAR(255) BINARY DEFAULT NULL, + auth TINYINT(1) DEFAULT 1, + PRIMARY KEY (id) +) Engine=InnoDB; + +CREATE INDEX nametype_index ON records(name,type); +CREATE INDEX domain_id ON records(domain_id); +CREATE INDEX recordorder ON records (domain_id, ordername); + + +CREATE TABLE supermasters ( + ip VARCHAR(64) NOT NULL, + nameserver VARCHAR(255) NOT NULL, + account VARCHAR(40) NOT NULL, + PRIMARY KEY (ip, nameserver) +) Engine=InnoDB; + + +CREATE TABLE comments ( + id INT AUTO_INCREMENT, + domain_id INT NOT NULL, + name VARCHAR(255) NOT NULL, + type VARCHAR(10) NOT NULL, + modified_at INT NOT NULL, + account VARCHAR(40) NOT NULL, + comment VARCHAR(64000) NOT NULL, + PRIMARY KEY (id) +) Engine=InnoDB; + +CREATE INDEX comments_domain_id_idx ON comments (domain_id); +CREATE INDEX comments_name_type_idx ON comments (name, type); +CREATE INDEX comments_order_idx ON comments (domain_id, modified_at); + + +CREATE TABLE domainmetadata ( + id INT AUTO_INCREMENT, + domain_id INT NOT NULL, + kind VARCHAR(32), + content TEXT, + PRIMARY KEY (id) +) Engine=InnoDB; + +CREATE INDEX domainmetadata_idx ON domainmetadata (domain_id, kind); + + +CREATE TABLE cryptokeys ( + id INT AUTO_INCREMENT, + domain_id INT NOT NULL, + flags INT NOT NULL, + active BOOL, + content TEXT, + PRIMARY KEY(id) +) Engine=InnoDB; + +CREATE INDEX domainidindex ON cryptokeys(domain_id); + + +CREATE TABLE tsigkeys ( + id INT AUTO_INCREMENT, + name VARCHAR(255), + algorithm VARCHAR(50), + secret VARCHAR(255), + PRIMARY KEY (id) +) Engine=InnoDB; + +CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm); diff --git a/db/structure.sql b/db/structure.sql new file mode 100644 index 0000000..fd52323 --- /dev/null +++ b/db/structure.sql @@ -0,0 +1,162 @@ +-- MySQL dump 10.15 Distrib 10.0.20-MariaDB, for debian-linux-gnu (x86_64) +-- +-- Host: localhost Database: webns +-- ------------------------------------------------------ +-- Server version 10.0.20-MariaDB-3 + +/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; +/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; +/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; +/*!40101 SET NAMES utf8 */; +/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; +/*!40103 SET TIME_ZONE='+00:00' */; +/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; +/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; +/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; +/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; + +-- +-- Table structure for table `comments` +-- + +DROP TABLE IF EXISTS `comments`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `comments` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `domain_id` int(11) NOT NULL, + `name` varchar(255) NOT NULL, + `type` varchar(10) NOT NULL, + `modified_at` int(11) NOT NULL, + `account` varchar(40) NOT NULL, + `comment` mediumtext NOT NULL, + PRIMARY KEY (`id`), + KEY `comments_domain_id_idx` (`domain_id`), + KEY `comments_name_type_idx` (`name`,`type`), + KEY `comments_order_idx` (`domain_id`,`modified_at`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `cryptokeys` +-- + +DROP TABLE IF EXISTS `cryptokeys`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `cryptokeys` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `domain_id` int(11) NOT NULL, + `flags` int(11) NOT NULL, + `active` tinyint(1) DEFAULT NULL, + `content` text, + PRIMARY KEY (`id`), + KEY `domainidindex` (`domain_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `domainmetadata` +-- + +DROP TABLE IF EXISTS `domainmetadata`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `domainmetadata` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `domain_id` int(11) NOT NULL, + `kind` varchar(32) DEFAULT NULL, + `content` text, + PRIMARY KEY (`id`), + KEY `domainmetadata_idx` (`domain_id`,`kind`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `domains` +-- + +DROP TABLE IF EXISTS `domains`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `domains` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `master` varchar(128) DEFAULT NULL, + `last_check` int(11) DEFAULT NULL, + `type` varchar(6) NOT NULL, + `notified_serial` int(11) DEFAULT NULL, + `account` varchar(40) DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `name_index` (`name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `records` +-- + +DROP TABLE IF EXISTS `records`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `records` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `domain_id` int(11) DEFAULT NULL, + `name` varchar(255) DEFAULT NULL, + `type` varchar(10) DEFAULT NULL, + `content` mediumtext, + `ttl` int(11) DEFAULT NULL, + `prio` int(11) DEFAULT NULL, + `change_date` int(11) DEFAULT NULL, + `disabled` tinyint(1) DEFAULT '0', + `ordername` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, + `auth` tinyint(1) DEFAULT '1', + PRIMARY KEY (`id`), + KEY `nametype_index` (`name`,`type`), + KEY `domain_id` (`domain_id`), + KEY `recordorder` (`domain_id`,`ordername`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `supermasters` +-- + +DROP TABLE IF EXISTS `supermasters`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `supermasters` ( + `ip` varchar(64) NOT NULL, + `nameserver` varchar(255) NOT NULL, + `account` varchar(40) NOT NULL, + PRIMARY KEY (`ip`,`nameserver`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `tsigkeys` +-- + +DROP TABLE IF EXISTS `tsigkeys`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `tsigkeys` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) DEFAULT NULL, + `algorithm` varchar(50) DEFAULT NULL, + `secret` varchar(255) DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `namealgoindex` (`name`,`algorithm`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; + +/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; +/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; +/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; +/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; +/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; +/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; +/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; + +-- Dump completed on 2015-10-17 13:59:45