Database

From SOWNWiki
Jump to: navigation, search

Here is the data dictionary for the SOWN database (sown_data). Please note: This page is not accurate as the database keeps changing.

To access the database directly ssh into sown-radius, and su to root. Type mysql and you will be given a mysql prompt.

\r sown_data

will connect you to the database, from which you can just type in standard sql queries.

chain

Table comments: Table of chains

+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| name           | varchar(32) | NO   |     |         |                |
| desc           | text        | YES  |     | NULL    |                |
| order_location | int(3)      | NO   |     |         |                | The location in the chain to place this line
+----------------+-------------+------+-----+---------+----------------+

chain_rules

Table comments: Stores all the database rules

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| chain_id  | int(11)      | YES  |     | NULL    |                |
| user      | varchar(235) | YES  |     | NULL    |                |
| d         | varchar(128) | YES  |     | NULL    |                |
| dport     | int(5)       | YES  |     | NULL    |                |
| proto     | varchar(128) | YES  |     | NULL    |                |
| action    | varchar(128) | YES  |     | NULL    |                |
| rule_name | varchar(32)  | NO   |     |         |                |
| rule_desc | text         | NO   |     |         |                |
+-----------+--------------+------+-----+---------+----------------+

groups

Table comments: This table stores all the availible groups

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| name        | varchar(32) | NO   |     |         |       |
| description | text        | YES  |     | NULL    |       |
| created_on  | datetime    | NO   |     |         |       |
| created_by  | int(11)     | YES  |     | NULL    |       |
| id          | int(11)     | NO   | PRI | 0       |       |
+-------------+-------------+------+-----+---------+-------+

inventory

Table comments: This table stores all the things that are constant about a n

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| barcode       | varchar(255) | YES  |     | NULL    |                |
| type          | varchar(255) | YES  |     | NULL    |                |
| model         | varchar(255) | YES  |     | NULL    |                |
| written_off   | datetime     | NO   |     |         |                |
| hardware_desc | text         | NO   |     |         |                |
| price         | varchar(24)  | YES  |     | NULL    |                |
| link_to_wiki  | varchar(235) | NO   |     |         |                |
| added_by      | varchar(235) | NO   |     |         |                |
| purchased_on  | datetime     | NO   |     |         |                |
| state         | varchar(255) | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

node_admin

Table comments: This stores the administrator of a node over time

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| node_id    | int(11)      | NO   |     |         |                |
| username   | varchar(235) | NO   |     |         |                |
| start_date | datetime     | NO   |     |         |                |
| end_date   | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

node_interfaces

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| node_id     | int(11)      | YES  |     | NULL    |                |
| interface   | varchar(255) | YES  |     | NULL    |                |
| ipv4_addr   | varchar(255) | YES  |     | NULL    |                |
| ipv4_subnet | int(11)      | YES  |     | NULL    |                |
| ipv6_addr   | varchar(255) | YES  |     | NULL    |                |
| ipv6_subnet | int(11)      | YES  |     | NULL    |                |
| offer_dhcp  | int(1)       | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

nodes

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| type        | varchar(255) | YES  |     | NULL    |       |
| name        | varchar(255) | YES  |     | NULL    |       |
| description | text         | YES  |     | NULL    |       |
| groups      | int(11)      | YES  |     | NULL    |       |
| id          | int(11)      | NO   | PRI | 0       |       |
+-------------+--------------+------+-----+---------+-------+


users

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment | sown/ecs/eduroam ... etc
| username       | varchar(235) | NO   | MUL |         |                |
| name           | varchar(255) | YES  |     | NULL    |                |
| userlevel      | int(11)      | YES  |     | NULL    |                | -1: Banned, 0: User, 1-4: Reserved, 5: SOWN Admin
| groups         | int(11)      | YES  |     | NULL    |                |
| email          | varchar(255) | YES  |     | NULL    |                |
| email_verified | int(11)      | YES  |     | NULL    |                | we need this to identify off campus people, so email has to be verified.
| node_lock      | int(11)      | YES  |     | NULL    |                | Home node ID, sown admins are home on all nodes.
| address        | longtext     | YES  |     | NULL    |                |
| postcode       | varchar(10)  | YES  |     | NULL    |                |
| date_joined    | datetime     | NO   |     |         |                |
| last_logged_in | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

users_mac

Table comments: This table is used to speed up the creation of the daily mac

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| username | varchar(235) | NO   |     |         |       |
| mac      | varchar(17)  | NO   |     |         |       |
+----------+--------------+------+-----+---------+-------+