====== Database schema ======
This is developed on Centos 6.x using the MySQL supplied with the distribution. The should be no specific features used dependent on minor release versions.
Although this uses MySQL at present, I would like it to be written to be portable to other databases with minimal effort.
===== Tables used =====
mysql> show tables;
+---------------+
| Tables_in_DNS |
+---------------+
| DOMAINNAME |
| FORWARDZONE |
| MEMBERS |
| TEAMMEMBERS |
| TEAMS |
| USERS |
| ZONEINFO |
+---------------+
DOMANNAME table contains the domain names this project can manage. \\
FORWARDZONE contains all the A, CNAME, MX, TXT, AAAA etc records. \\
MEMBERS describes the relationship between TEAMMEMBERS and the TEAMs they are part of. Used to control access to change records.\\
TEAMMEMBERS contains the users - probably superceeded by USERS. \\
TEAMS contains the Desktop, Network, security and server team definitions. \\
USERS describes the allowed users and their details. \\
ZONEINFO is for the metadata at the top of a zone file, it may be better merged in to DOMAINNAME, or maybe DOMAINNAME should become part of ZONEINFO. \\
===== Table Descriptions =====
mysql> describe DOMAINNAME;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| domainsuffix | varchar(254) | YES | | NULL | |
+--------------+--------------+------+-----+---------+----------------+
mysql> describe FORWARDZONE;
+--------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+-----------------------------+
| hostname | varchar(254) | NO | PRI | | |
| domainsuffix | varchar(254) | YES | | NULL | |
| description | varchar(254) | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| team | varchar(20) | YES | | NULL | |
| modified | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| recorddata | varchar(40) | YES | | NULL | |
| recordclass | varchar(2) | YES | | NULL | |
| recordtype | varchar(5) | YES | | NULL | |
+--------------+--------------+------+-----+-------------------+-----------------------------+
Example data is:-
+------------------+-------------+------------+--------------------------+-----------------------+---------------------------------+
| hostname | recordclass | recordtype | recorddata | domainsuffix | description |
+------------------+-------------+------------+--------------------------+-----------------------+---------------------------------+
| optiplex | IN | A | 192.168.1.1 | rainsbrook.pri | Optiplex in rack |
| quince | IN | A | 192.168.5.21 | rainsbrook.co.uk | Quince in virtual server |
| www.g1cwo.co.uk | IN | CNAME | quince.rainsbrook.co.uk. | g1cwo.co.uk | Website |
| newname | IN | CNAME | oldname | rainsbrook.pri | Alias from old server to new |
+------------------+-------------+------------+--------------------------+-----------------------+---------------------------------+
Mapping of records in to FORWARDZONE.
^ hostname ^ recordclass ^ recordtype ^ recorddata ^
| hostname | IN | A | ipaddress |
| hostname | IN | AAAA | ipaddress |
| hostname | IN | CNAME | hostname (no '.') |
| hostname | IN | CNAME | fqdn. (with '.') |
mysql> describe USERS;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| password | varchar(20) | NO | | NULL | |
| fullname | varchar(254) | NO | | NULL | |
| emailaddress | varchar(254) | NO | | NULL | |
+--------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> describe MEMBERS;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| username | varchar(20) | NO | | NULL | |
| memberofteam | varchar(20) | NO | | NULL | |
+--------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe TEAMS;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| team | varchar(20) | YES | | NULL | |
| teampassword | varchar(20) | YES | | NULL | |
| displayname | varchar(40) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> describe TEAMMEMBERS;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| username | varchar(20) | YES | | NULL | |
| password | char(25) | NO | | NULL | |
| fullname | varchar(40) | YES | | NULL | |
| emailaddress | varchar(40) | YES | | NULL | |
| memberof | varchar(40) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
select users.password,
members.memberofteam,
teams.displayname from
users,
members,
teams
where
users.username='andrew' and
members.username=users.username and
teams.team=members.memberofteam;
Thanks to Brian Cadwell for this suggestion.
FIXME - decide on the storage engine to use:-
MYISAM:
MYISAM supports Table-level Locking
MyISAM designed for need of speed
MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS
MyISAM stores its tables, data and indexes in diskspace using separate three different files. (tablename.FRM, tablename.MYD, tablename.MYI)
MYISAM not supports transaction. You cannot commit and rollback with MYISAM. Once you issue a command it’s done.
MYISAM supports fulltext search
You can use MyISAM, if the table is more static with lots of select and less update and delete.
INNODB:
InnoDB supports Row-level Locking
InnoDB designed for maximum performance when processing high volume of data
InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS
InnoDB stores its tables and indexes in a tablespace
InnoDB supports transaction. You can commit and rollback with InnoDB
Currently all tables use MyISAM for no better reason than it seems to be the default:-
mysql> SELECT table_name, engine, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES order by CREATE_TIME;
+---------------------------------------+--------+---------------------+
| table_name | engine | CREATE_TIME |
+---------------------------------------+--------+---------------------+
| DOMAINNAME | MyISAM | 2013-09-22 13:20:28 |
| MEMBERS | MyISAM | 2013-09-22 13:20:28 |
| USERS | MyISAM | 2013-09-22 13:20:29 |
| TEAMS | MyISAM | 2013-09-22 13:20:29 |
| TEAMMEMBERS | MyISAM | 2013-09-22 13:20:29 |
| ZONEINFO | MyISAM | 2013-09-22 13:20:30 |
| FORWARDZONE | MyISAM | 2014-03-19 09:28:38 |
| DELETEDFORWARDZONE | MyISAM | 2014-04-23 16:19:42 |
+---------------------------------------+--------+---------------------+