= 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.
 * [http://map.funkfeuer.at/wien 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)
}}}