Crimson: Added SQL indexes

go/src/infra/crimson/sql/schema.sql

 -- Copyright 2016 The Chromium Authors. All Rights Reserved.
 --
 -- Use of this source code is governed by a BSD-style
 -- license that can be found in the LICENSE file or at
 -- https://developers.google.com/open-source/licenses/bsd
 
 -- Create tables in the crimson DB.
 
 CREATE TABLE ip_range (
   site varchar(20) NOT NULL,

[sheyang, 2016/07/01 21:55:26]

Is this a PK?

[pgervais, 2016/07/01 22:26:36]

It will be part of it (in another CL).

   vlan varchar(20),
   start_ip varchar(34) NOT NULL,
   end_ip varchar(34) NOT NULL
 ) ENGINE=INNODB;
 
+CREATE index ip_range_start_ip_idx ON ip_range(start_ip);

[sheyang, 2016/07/01 21:55:26]

The purpose of index start_ip and end_ip is for querying with where clause I
guess. Would storing ip as varchar still work? And how about ipv6?

[pgervais, 2016/07/01 22:26:36]

That's correct. 
IP and mac addresses are stored as hexadecimal strings, that's why it's 34
characters long (IPv6: 16bytes*2 + '0x' prefix).
With this representation, lexicographical order sorts IPv6 and IPv4 addresses in
two separate blocks.

+CREATE index ip_ragne_start_ip_idx ON ip_range(end_ip);
+
 CREATE TABLE host (
   site varchar(20) NOT NULL,

[sheyang, 2016/07/01 21:55:26]

Is this a FK?

[pgervais, 2016/07/01 22:26:36]

It looks like a foreign key but it's not. There is no SQL-friendly way to join
the two tables. An IP address corresponds to an IP range but it's not a join...

   hostname varchar(63) NOT NULL,
   mac_addr varchar(14) NOT NULL,
   ip varchar(34) NOT NULL,
   boot_class varchar(20),
   PRIMARY KEY (mac_addr)
 ) ENGINE=INNODB;
+
+CREATE index host_site_hostname_idx ON host(site, hostname);
+CREATE index host_site_ip_idx ON host(site, ip);