# Database layout

PostgreSQL

**Tables**
```
bootstrap-> \dt
           List of relations
 Schema |   Name   | Type  |   Owner   
--------+----------+-------+-----------
 public | switches | table | bootstrap
```


**Table structure**
```
bootstrap=> \d switches
                                      Table "public.switches"
      Column       |          Type          |                       Modifiers                       
-------------------+------------------------+-------------------------------------------------------
 id                | integer                | not null default nextval('switches_id_seq'::regclass)
 hostname          | character varying(20)  | not null
 distro_name       | character varying(100) | not null
 distro_phy_port   | character varying(100) | not null
 mgmt_addr         | character varying(15)  | not null
 mgmt_cidr         | smallint               | not null
 mgmt_gw           | character varying(15)  | not null
 mgmt_vlan         | smallint               | not null
 last_config_fetch | integer                | 
 current_mac       | character varying(17)  | default NULL::character varying
 model             | character varying(20)  | 
 ztp_addr          | character varying(15)  | 
 ztp_cidr          | smallint               | 
 ztp_gw            | character varying(15)  | 
Indexes:
    "switches_pkey" PRIMARY KEY, btree (id)
```


**Sample content in DB**
```
bootstrap=> select * from switches;
 id |  hostname   |   distro_name   | distro_phy_port | mgmt_addr  | mgmt_cidr |  mgmt_gw   | mgmt_vlan | last_config_fetch |    current_mac    | model | ztp_addr | ztp_cidr | ztp_gw 
----+-------------+-----------------+-----------------+------------+-----------+------------+-----------+-------------------+-------------------+-------+----------+----------+--------
  1 | e-00-0-test | distro-test     | ge-0/0/0        | 10.0.200.2 |        24 | 10.0.200.1 |       300 |                   |                   |       |          |          | 
  2 | e-00-1-test | distro-test     | ge-0/0/3        | 10.0.200.3 |        24 | 10.0.200.1 |       300 |                   |                   |       |          |          | 
  3 | e-00-2-test | distro-test     | ge-0/0/6        | 10.0.200.4 |        24 | 10.0.200.1 |       300 |                   |                   |       |          |          | 
  4 | e-60-0-test | distro-test     | ge-0/0/9        | 10.0.200.5 |        24 | 10.0.200.1 |       300 |                   |                   |       |          |          | 
  6 | e-01-2      | distro-test-new | ge-0/0/3        | 10.0.0.32  |        24 | 10.0.0.1   |       300 |        1424311409 |                   |       |          |          | 
  5 | e-01-1      | distro-test-new | ge-0/0/0        | 10.0.0.31  |        24 | 10.0.0.1   |       300 |        1424311417 | AA:BB:CC:DD:EE:FF |       |          |          | 
(6 rows)
```


**Connect to DB from CLI**
```
j@lappie:~/git/tgmanage$ psql -U bootstrap -d bootstrap -W
Password for user bootstrap: 
psql (9.3.5)
Type "help" for help.

bootstrap=> 
```


**Sample procedure to insert content to DB**
```
bootstrap=> insert into switches (hostname, distro_name, distro_phy_port, mgmt_addr, mgmt_cidr, mgmt_gw, mgmt_vlan) values 
bootstrap-> ('e-01-1', 'distro-test-new', 'ge-0/0/0', '10.0.0.31', '24', '10.0.0.1', '300'),
bootstrap-> ('e-01-2', 'distro-test-new', 'ge-0/0/3', '10.0.0.32', '24', '10.0.0.1', '300');
INSERT 0 2
```



## Detailed description of table "switches" fields:
* id: autoincreasing integer used to identify the database row
* hostname: the unique edge switchs hostname - example: edge01
* distro_name: the distro switch hostname - example: distro01
* distro_phy_port: The distro switch's physical port - example: ge-3/1/0
* mgmt_addr: The management IP - will be configured under vlan set in "mgmt_vlan" - example: 10.20.30.40
* mgmt_cidr: CIDR mask on management subnet - example: 28
* mgmt_vlan: VLAN id at the management VLAN - example: 100
* last_config_fetch: unix timestamp of the last time the config were fetched by the switch - example: 11041551
* current_mac: MAC address of the edge switch - example: 0f:1f:2f:3f:4f:5f
* model: edge switch model - used to select template - example: ex2200