In search of perfection…

USE [master]
 
GO
 
/****** Object:  Database [PowerDNS]    Script Date: 10/17/2008 12:15:22 ******/
 
CREATE DATABASE [PowerDNS] ON PRIMARY
( NAME = N'PowerDNS_Data', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAPowerDNS_Data.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
    LOG ON
( NAME = N'PowerDNS_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAPowerDNS_Log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.SP_DBCMPTLEVEL @dbname = N'PowerDNS', @new_cmptlevel = 90
GO
IF ( 1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') )
    BEGIN
        EXEC [PowerDNS].[dbo].[SP_FULLTEXT_DATABASE] @ACTION = 'disable'
    END
GO
ALTER DATABASE [PowerDNS] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [PowerDNS] SET ANSI_NULLS OFF
GO
ALTER DATABASE [PowerDNS] SET ANSI_PADDING OFF
GO
ALTER DATABASE [PowerDNS] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [PowerDNS] SET ARITHABORT OFF
GO
ALTER DATABASE [PowerDNS] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [PowerDNS] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [PowerDNS] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [PowerDNS] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [PowerDNS] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [PowerDNS] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [PowerDNS] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [PowerDNS] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [PowerDNS] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [PowerDNS] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [PowerDNS] SET ENABLE_BROKER
GO
ALTER DATABASE [PowerDNS] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [PowerDNS] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [PowerDNS] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [PowerDNS] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [PowerDNS] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [PowerDNS] SET READ_WRITE
GO
ALTER DATABASE [PowerDNS] SET RECOVERY FULL
GO
ALTER DATABASE [PowerDNS] SET MULTI_USER
GO
ALTER DATABASE [PowerDNS] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [PowerDNS] SET DB_CHAINING OFF
GO
 
USE [PowerDNS]
GO
/****** Object:  Table [dbo].[domains]    Script Date: 10/17/2008 12:21:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[domains]
    (
      [id] [INT] IDENTITY(1, 1)
                 NOT NULL,
      [name] [VARCHAR](255) NOT NULL,
      [type] [VARCHAR](6) NOT NULL,
      [master] [VARCHAR](40) NOT NULL
                             DEFAULT ( '' ),
      [account] [VARCHAR](40) NOT NULL
                              DEFAULT ( '' ),
      [notified_serial] [INT] NULL,
      [last_check] [INT] NULL,
      [status] [CHAR](1) NOT NULL
                         DEFAULT ( 'A' ),
      CONSTRAINT [pk_domains_id] PRIMARY KEY CLUSTERED ( [id] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY],
      CONSTRAINT [unq_domains_name] UNIQUE NONCLUSTERED ( [name] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[records]    Script Date: 10/17/2008 12:21:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[records]
    (
      [id] [INT] IDENTITY(1, 1)
                 NOT NULL,
      [domain_id] [INT] NOT NULL,
      [name] [VARCHAR](255) NOT NULL,
      [type] [VARCHAR](6) NOT NULL,
      [ttl] [INT] NULL,
      [prio] [INT] NULL,
      [content] [VARCHAR](255) NOT NULL,
      [change_date] [INT] NULL,
      CONSTRAINT [pk_records_id] PRIMARY KEY CLUSTERED ( [id] ASC )
        WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
               IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
               ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
    )
ON  [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[supermasters]    Script Date: 10/17/2008 12:21:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[supermasters]
    (
      [ip] [VARCHAR](40) NOT NULL,
      [nameserver] [VARCHAR](255) NOT NULL,
      [account] [VARCHAR](40) NOT NULL
                              DEFAULT ( '' )
    )
ON  [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[records]
        WITH CHECK
ADD CONSTRAINT [fk_records_domainid] FOREIGN KEY ( [domain_id] ) REFERENCES [dbo].[domains] ( [id] ) ON UPDATE CASCADE
        ON DELETE CASCADE
GO
ALTER TABLE [dbo].[records]
        CHECK CONSTRAINT [fk_records_domainid]
 
USE [master]
GO
CREATE LOGIN [powerdns] WITH PASSWORD= N'secret', DEFAULT_DATABASE= [PowerDNS], DEFAULT_LANGUAGE= [us_english], CHECK_EXPIRATION=
    OFF, CHECK_POLICY= OFF
GO
ALTER LOGIN [powerdns] ENABLE
GO
USE [PowerDNS]
GO
CREATE  USER [powerdns] FOR LOGIN [powerdns]
GO
GRANT DELETE ON [dbo].[domains] TO [powerdns]
GO
GRANT INSERT ON [dbo].[domains] TO [powerdns]
GO
GRANT REFERENCES ON [dbo].[domains] TO [powerdns]
GO
GRANT SELECT ON [dbo].[domains] TO [powerdns]
GO
GRANT UPDATE ON [dbo].[domains] TO [powerdns]
GO
GRANT DELETE ON [dbo].[records] TO [powerdns]
GO
GRANT INSERT ON [dbo].[records] TO [powerdns]
GO
GRANT REFERENCES ON [dbo].[records] TO [powerdns]
GO
GRANT SELECT ON [dbo].[records] TO [powerdns]
GO
GRANT UPDATE ON [dbo].[records] TO [powerdns]
GO
GRANT SELECT ON [dbo].[supermasters] TO [powerdns]
GO

USE
[PowerDNS]
GO
INSERT
INTO domains (name, type) VALUES ('example.com', 'NATIVE');
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'example.com','localhost postmaster@example.com 1','SOA',86400,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'example.com','ns1.outerin.net','NS',86400,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'example.com','ns2.outerin.net','NS',86400,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'www.example.com','192.168.1.100','A',120,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'mail.example.com','192.168.1.252','A',120,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'localhost.example.com','127.0.0.1','A',120,NULL);
INSERT
INTO records (domain_id, name, content, type,ttl,prio) VALUES (1,'example.com','mail.example.com','MX',120,25);