Eric Z. Ayers
eric.ayers@mindspring.com

Last Update: 7 August 1999


Introduction:

This module was meant to be a test of interfacing MySQL with Dents.
So far, it is the only SQL module that has been written (August 1999).
Please don't let the presence of this module hamper you from striking
out and writing a mod_mysql_XXX, or any other SQL module.  This module
is not a one-size fits all type of solution.  I am not a database
expert by any stretch of the imagination.


Overview:

This module stores RRs in a MySQL database. 

For most RRs, all you have to do is insert/update/delete rows in tables 
in the database - no reload necessary. 

Each zone is stored in its own table, with underscores substituted
for periods.

   zone_rr_XXX_XXX_ 
  
  EXCEPT: 

The SOA records are in a separate table named zone_soa.  I did
this mainly because the RDATA of SOA records is so different from 
most common RDATA definitions for RRs.  I never really planed to 
support all RRs (the really weird ones, like DT_LOC), but SOA seems
kind of important and doesn't really fit well into 2 character
strings! 


Design Decisions:

I've made a couple of decisions in implementing mod_mysql_eza that may 
not be to everyone's liking, such as:

- The SOA and NS records for the zone are cached, and not looked up
  each time.  The cache expires every hour, or the value of the
  MINIMUM field in the SOA, whichever comes first.

- The 'type' for the RR's are stored in integer format (as per RFC 1035)

  A  == 1
  NS == 2
  ...

  At first I did this because I wasn't sure how to go between the
  strings and integers easily, but then I found a function in
  dns_types.c that would help

  Eventually, the DENTS control facility will allow you to add/delete
  RRs.  Then you won't care how they are stored in the database.

  I'm  beginning to consider changing this to a character string...
  Pester me if you want this and I still haven't changed it.

  
- The RDATA is weakly typed as a varchar character string and a blob.
  Definately not a 3rd Normal Form database structure.  This will
  not support all RDATA types as described in the RFCs, but it 
  seems to work well enought for the commonly used types.


I'm thinking that when the control facility comes of age, that this
scheme will be more usable, but right now, it would really be a pain
to enter data w/o writing some kind of script or other front end to
inserting data into the database.  


Other models for an SQL driver which have been suggested:

 -  2 tables - one containing 1 row per zone with a numeric ID field
             one containing all the RRs

 - An sqlbind compatible table format

 - An msql_bind compatible table format

 - A module that doesn't cache SOA or NS records.

 - A module that stores each piece of RDATA in a separate row:
   e.g.  type=SOA1  rdata=mname
         type=SOA2  rdata=rname
            ...
 - A SQL driver that is independent of the database being used (sort
    of like ODBC or the perl DBI interface used to access the
    database)

 - Store each RR type in a separate table (break out tables into 
   3rd Normal Form)


--------------------------------------------------------------------------

Ideas for the future:

- Find a way to fill out the additional section of the query.  Right
  now it is blank because I cannot figure out how to get A records for
  the rest of the zone.
  
- You can load the mysql database by setting up this server as a secondary
  to an already configured DNS server and it will build the tables 
  that are necessary and populate them.



--------------------------------------------------------------------------

Table zone_soa         
	contains the SOA records for all zones 
        (it just fits into an SQL table better this way!)

	NOTE: not implemented yet!

create table zone_soa (
	domain char (128) primary key,   
	mname varchar (255),           
	rname varchar (255),
	serial integer unsigned,
	refresh integer,
	retry integer,
	expire integer,
	minimum integer unsigned
);

--------------------------------------------------------------------------

Table rr_<zone_name> 
        (change the . to _ characters)
	contains the other resource records for a single zone

	The following table describes what to fill in rdata_char and
	rdata_char.  The names are referenced from rfc1035.

	type	rdata_char	rdata_blob
	A       ADDRESS		n/a
	NS      NSDNAME		n/a
	MD      --- obsolete ----
	MF      --- obsolete ----
	CNAME   CNAME		n/a
	SOA     n/a		n/a 	---> see soa table
	MB      MADNAME		n/a
	MG      MGMNAME         n/a     
	MR      NEWNAME   
	NULL    n/a             <anything>
	WKS     ADDRESS	PROTOCOL(string)(space) BIT MAP(list of ones/zeroes)
	PTR     PTRDNAME
	HINFO   CPU		OS (string)
	MINFO   RMAILBX         EMAILBX  
	MX      PREFERENCE	EXCHANGE
	TXT     TXT-DATA
	AAAA    ADDRESS      


Here is an example of a populated table. Please note that I am not affiliated 
with whomever owns the zundel.com domain name.

#
# Table structure for table 'zone_rr_zundel_com_'
#
CREATE TABLE zone_rr_zundel_com_ (
  domain varchar(128) DEFAULT '' NOT NULL, # name for the RR minus the zonename
  ttl int(11),                             # time to live
  class smallint(6),                       # class (only 1 is valid)
  type smallint(6) DEFAULT '0' NOT NULL,   # one of the DT_XXX type definitions
  rdata_char varchar(255),                 # untyped RDATA field 1
  rdata_blob blob                          # untyped RDATA field 2
);

#
# Dumping data for table 'zone_rr_zundel_com_'
#

INSERT INTO zone_rr_zundel_com_ VALUES ('www',3600,1,1,'10.207.162.131','');
INSERT INTO zone_rr_zundel_com_ VALUES ('squash',3601,1,1,'10.207.162.130','');
INSERT INTO zone_rr_zundel_com_ VALUES ('squish',3601,1,1,'10.207.162.132','');
INSERT INTO zone_rr_zundel_com_ VALUES ('cricket',0,1,5,'www','');
INSERT INTO zone_rr_zundel_com_ VALUES ('www',0,1,16,'Some Descriptive text for www','');
INSERT INTO zone_rr_zundel_com_ VALUES ('www',0,1,13,'WWW CPU i986','Linux 4.5');
INSERT INTO zone_rr_zundel_com_ VALUES ('www',0,1,15,'10','FOO.BAR.COM');
INSERT INTO zone_rr_zundel_com_ VALUES ('www',0,1,14,'rmailbx.foo.com.','exchange.BAR.COM.');
INSERT INTO zone_rr_zundel_com_ VALUES ('www',0,1,22,'deadbeef0123456789abcdef','');
INSERT INTO zone_rr_zundel_com_ VALUES ('test',0,1,22,'a','');
INSERT INTO zone_rr_zundel_com_ VALUES ('',180,1,2,'ns1','');
INSERT INTO zone_rr_zundel_com_ VALUES ('',180,1,2,'ns2','');
INSERT INTO zone_rr_zundel_com_ VALUES ('ns1',182,1,1,'10.107.162.131','');
INSERT INTO zone_rr_zundel_com_ VALUES ('ns2',182,1,1,'10.107.162.130','');

--------------------------------------------------------------------------

Table rr_type
	This table is just for reference. (resource record types)

create table rr_type (
	type smallint primary key,
	mnemonic char(5)
);

insert into rr_type values (1, "A");
insert into rr_type values (2, "NS");
insert into rr_type values (3, "MD");
insert into rr_type values (4, "MF");
insert into rr_type values (5, "CNAME");
insert into rr_type values (6, "SOA");
insert into rr_type values (7, "MB");
insert into rr_type values (8, "MG");
insert into rr_type values (9, "MR");
insert into rr_type values (10, "NULL");
insert into rr_type values (11, "WKS");
insert into rr_type values (12, "PTR");
insert into rr_type values (13, "HINFO");
insert into rr_type values (14, "MINFO");
insert into rr_type values (15, "MX");
insert into rr_type values (16, "TXT");

=============================================================================

FYI, you have to setup a mysql database with a user name and password

# mysql mysql
mysql> insert into user values ("localhost", "dents", PASSWORD("mypass"),"Y","Y","Y","Y","Y","Y","Y","Y","Y","Y");
mysql> insert into db values ("localhost", "dents_test", "dents",
"Y","Y","Y","Y","Y","Y");
mysql> \q

$ mysqladmin reload




