= Funkfeuer Graz Node Database =

Our current node database is a litte messy. We have 2 different Interfaces. One written in Perl using the Perl Template Engine and one which is based on Ruby on Rails. The latter is easier to undertsand but some major features are missing. The Perl interface is a complete mess but it is the only thing were we can change most of things.

Here is our current database schema:

{{{
mysql> show tables;
+--------------------+
| Tables_in_mappe    |
+--------------------+
| dnsalias           | 
| ip                 | 
| location           | 
| net                | 
| nettype            | 
| node               | 
| person             | 
| vhost              | 
+--------------------+
8 rows in set (0.00 sec)

mysql> describe dnsalias;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment | 
| ip_id | int(11)     | NO   |     | 0       |                | 
| name  | varchar(15) | NO   | UNI |         |                | 
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe ip;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment | 
| node_id    | int(11)     | NO   | MUL | 0       |                | 
| ip         | varchar(15) | NO   | UNI |         |                | 
| net_id     | int(11)     | YES  |     | NULL    |                | 
| name       | varchar(15) | YES  |     | NULL    |                | 
| snmp_mac   | varchar(12) | YES  |     | NULL    |                | 
| snmp_intnr | int(11)     | YES  |     | NULL    |                | 
+------------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

mysql> describe location;
+--------------+--------------+------+-----+---------------------+----------------+
| Field        | Type         | Null | Key | Default             | Extra          |
+--------------+--------------+------+-----+---------------------+----------------+
| id           | int(11)      | NO   | PRI | NULL                | auto_increment | 
| gps_lon      | varchar(10)  | YES  |     | NULL                |                | 
| gps_lat      | varchar(10)  | YES  |     | NULL                |                | 
| hidden       | tinyint(1)   | NO   |     | 0                   |                | 
| name         | varchar(20)  | NO   | UNI |                     |                | 
| street       | varchar(42)  | NO   |     |                     |                | 
| streetnr     | varchar(4)   | NO   |     |                     |                | 
| creator_ip   | varchar(15)  | YES  |     | NULL                |                | 
| time         | datetime     | NO   |     | 0000-00-00 00:00:00 |                | 
| comment      | text         | YES  |     | NULL                |                | 
| person_id    | int(11)      | YES  |     | NULL                |                | 
| gallery_link | varchar(128) | YES  |     | NULL                |                | 
| hastinc      | tinyint(1)   | NO   |     | 0                   |                | 
+--------------+--------------+------+-----+---------------------+----------------+
13 rows in set (0.01 sec)

mysql> describe net;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment | 
| netip       | varchar(15)  | NO   | UNI |         |                | 
| netmask     | varchar(15)  | NO   |     |         |                | 
| location_id | int(11)      | NO   |     | 0       |                | 
| comment     | varchar(100) | NO   |     |         |                | 
| nettype_id  | int(11)      | NO   |     | 0       |                | 
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> describe nettype;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| name    | varchar(55)  | NO   |     |         |                | 
| comment | varchar(100) | NO   |     |         |                | 
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe node;
+---------------+-------------+------+-----+------------+----------------+
| Field         | Type        | Null | Key | Default    | Extra          |
+---------------+-------------+------+-----+------------+----------------+
| id            | int(11)     | NO   | PRI | NULL       | auto_increment | 
| location_id   | int(11)     | NO   | MUL | 0          |                | 
| person_id     | int(11)     | YES  |     | NULL       |                | 
| name          | varchar(23) | NO   |     | NULL       |                | 
| comment       | text        | NO   |     | NULL       |                | 
| creator_ip    | varchar(15) | NO   |     |            |                | 
| time          | date        | NO   |     | 0000-00-00 |                | 
| smokeping     | tinyint(1)  | NO   |     | 0          |                | 
| snmp_ip       | int(11)     | YES  |     | NULL       |                | 
| snmp_lastseen | date        | YES  |     | NULL       |                | 
| hastinc       | tinyint(1)  | NO   |     | 0          |                | 
+---------------+-------------+------+-----+------------+----------------+
11 rows in set (0.00 sec)

mysql> describe person;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment | 
| nick      | varchar(23) | NO   |     |         |                | 
| firstname | varchar(42) | NO   |     |         |                | 
| lastname  | varchar(42) | NO   |     |         |                | 
| email     | varchar(50) | NO   | UNI |         |                | 
| tel       | varchar(20) | YES  |     | NULL    |                | 
| password  | varchar(40) | NO   |     |         |                | 
| checked   | tinyint(1)  | NO   |     | 0       |                | 
| admin     | tinyint(1)  | NO   |     | 0       |                | 
+-----------+-------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> describe vhost;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment | 
| ip_id | int(11)     | NO   |     | 0       |                | 
| name  | varchar(30) | NO   | UNI | NULL    |                | 
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
}}}