FunkFeuer Database
Schema of current FunkFeuer DB (nicknamed "redeemer"). It was programmed and designed by Wolfgang Nagele. It was a very quick and fast implementation which has a few key strengths:
- It can generate DNS zone files.
- It can generate Asterisk VOIP config files based on the members tables.
- It can generate track when which IP as last pingable.
- It can generate smokeping entries.
- We have an internal whois service which connects to the DB.
- Our map connects to the DB and can display the status of each node.
However now after a few years we have a few more needs:
- We use public IPs and in IPv4 world these are limited. So we need to keep track of the who "hordes" IPv4 IPs and must have a way to get them back and re-distribute them to new nodes.
- Better network management and planning: link calculations and planning algos are needed. We would like to be able to send mails to people with recommendations on which link would benefit them. Imagine a radio engineer expert system consulting you. Ok, I know this is far fetched... but working on it.
These needs will be addressed by a future version of our node DB.
For now we have the following structure:
redeemer_wien=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+-------------------
public | devices | table | redeemer.frontend
public | ips | table | redeemer.frontend
public | members | table | redeemer.frontend
public | members_roles | table | redeemer.frontend
public | nodes | table | redeemer.frontend
public | roles | table | redeemer.frontend
public | voip_extensions | table | redeemer.frontend
public | voip_sip | table | redeemer.frontend
(8 rows)
##### some tables in detail
redeemer_wien=# \d members
Table "public.members"
Column | Type | Modifiers
------------------------+--------------------------+-----------
id | bigint | not null
nickname | character varying(100) | not null
password | character varying(255) | not null
firstname | character varying(50) |
lastname | character varying(50) |
street | character varying(255) |
housenumber | character varying(10) |
zip | character varying(10) |
town | character varying(255) |
telephone | character varying(25) |
mobilephone | character varying(25) |
fax | character varying(25) |
email | character varying(50) |
homepage | character varying(50) |
created | timestamp with time zone |
changed | timestamp with time zone |
redeemer_wien=# \d nodes
Table "public.nodes"
Column | Type | Modifiers
-------------+--------------------------+-----------------------
id | bigint | not null
name | character varying(250) | not null
gps_lat_deg | double precision |
gps_lat_min | double precision |
gps_lat_sec | double precision |
gps_lon_deg | double precision |
gps_lon_min | double precision |
gps_lon_sec | double precision |
map | boolean | not null default true
id_members | bigint | not null
created | timestamp with time zone |
changed | timestamp with time zone |
redeemer_wien=# \d devices
Table "public.devices"
Column | Type | Modifiers
------------------+-----------------------------+------------------------
id | bigint | not null
name | character varying(100) | not null
antenna | character varying(255) |
hardware | character varying(255) |
ssid | character varying(255) |
mac | character varying(17) |
smokeping | boolean | not null default true
last_seen | timestamp without time zone |
id_nodes | bigint |
id_members | bigint |
created | timestamp with time zone |
changed | timestamp with time zone |
delete_mail | timestamp without time zone |
delete_protected | boolean | not null default false
comment | character varying(8000) |
redeemer_wien=# \d ips
Table "public.ips"
Column | Type | Modifiers
----------------+------------------------+-----------
id | bigint | not null
ip | character varying(15) | not null
cidr | character varying(2) | not null
usage | character varying(100) | not null
dns_forward | boolean | not null
custom_forward | character varying(100) |
dns_reverse | boolean | not null
custom_reverse | character varying(100) |
id_devices | bigint |
id_members | bigint |
id_nodes | bigint |
redeemer_wien=# select * from roles;
id | name
----+-------------
1 | Admin
2 | Map
3 | Club Member
4 | Mentor
(4 rows)