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)