789558 - Design and implement database for imaging service

Status: RESOLVED FIXED

(Infrastructure & Operations :: RelOps: General, task)

Description

[(not currently active) Ted Mielczarek]

The imaging service will need a DB to store info about devices and other things. A high-level view of the schema is in the etherpad in the URL field.

Comment 1

[Dustin J. Mitchell [:dustin] (he/him)]

Ted, I've got some good feedback from Sheeri.  I'll start getting this DB set up, and do a bit more thinking about how best to implement it.  There are open questions on whether to use numeric or string keys, and whether to partition the logs table (which precludes foreign keys in that table).

When you've selected a framework, let me know, and I'll formulate the schema with the appropriate syntax (sqlalchemy?).

Comment 2

[(not currently active) Ted Mielczarek]

I'm not a big ORM fan, honestly, but I'll let you know what I wind up doing.

Comment 3

[Dustin J. Mitchell [:dustin] (he/him)]

I didn't say ORM!  Sqlalchemy has a great abstraction layer underneath its ORM layer.

Comment 4

[Dustin J. Mitchell [:dustin] (he/him)]

Attachment: schema.sql

OK, here's the schema I've come up with.  I haven't loaded it into the imagingservice db yet, but I can when requested.

This will keep logs for 2 weeks, and will do well with "recent" log queries, even in the face of a lot of log entries.

Comment 5

[(not currently active) Ted Mielczarek]

(In reply to Dustin J. Mitchell [:dustin] from comment #1)
> When you've selected a framework, let me know, and I'll formulate the schema
> with the appropriate syntax (sqlalchemy?).

After review, sqlalchemy seems fine. If you can provide the schema in that form that'd be great.

Comment 6

[(not currently active) Ted Mielczarek]

Comment on attachment 660114 [details]
schema.sql

>  -- path to the board's power relay; format TBD; NULL=no control
>  relay_info varchar(64)

I wonder if we shouldn't make this slightly larger, as the proposed format for this data is <fqdn>:bankX:relayY
per: https://mana.mozilla.org/wiki/display/IT/Power+Control+Relay+Board

the example from that page:
key = system.relay.0 value = panda-relay-01.build.scl1.mozilla.com:bank2:relay4

which is about 50 characters right there.

Comment 7

[Dustin J. Mitchell [:dustin] (he/him)]

I'll make it TEXT, since we don't need to index it.

Comment 8

[Dustin J. Mitchell [:dustin] (he/him)]

Attachment: schema.py

Here's the python version.  Note that this omits indices and whatnot - it's not a good source for creating a new production DB, but should be adequate to create a test DB.

Comment 9

[Dustin J. Mitchell [:dustin] (he/him)]

Attachment: schema.py

corrected version

Comment 10

[Dustin J. Mitchell [:dustin] (he/him)]

Attachment: schema.sql

Updated with relay_info as type text

Comment 11

[Dustin J. Mitchell [:dustin] (he/him)]

I had to futz a little to get MySQL to swallow it.  I've also renamed the DB to black_mobile_magic.

Comment 12

[Dustin J. Mitchell [:dustin] (he/him)]

Attachment: schema.py

Comment 13

[Dustin J. Mitchell [:dustin] (he/him)]

Attachment: schema.sql

Comment 14

[Dustin J. Mitchell [:dustin] (he/him)]

I've put this on the staging db cluster as well, which may be accessible from the MPT VPN, and at worst we can set up a temporary VM for you to work on.

You can test access via MPT VPN with 
 nc -vz stage-rw-vip.db.scl3.mozilla.com 3306
Let me know if you'd like a VM set up.

Username and db name are the same; I'll send the password out of band.

Comment 15

[(not currently active) Ted Mielczarek]

We discussed this on IRC, but we need to add a TEXT 'config' column to the boards table.

Comment 16

[Dustin J. Mitchell [:dustin] (he/him)]

Added in my hg repo, and added in the live DB (prod and stage):

mysql> explain boards;
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name              | varchar(32)      | NO   |     | NULL    |                |
| fqdn              | varchar(256)     | NO   |     | NULL    |                |
| inventory_id      | int(11)          | NO   |     | NULL    |                |
| status            | varchar(32)      | YES  |     | NULL    |                |
| mac_address       | varchar(10)      | NO   |     | NULL    |                |
| imaging_server_id | int(10) unsigned | NO   | MUL | NULL    |                |
| relay_info        | text             | YES  |     | NULL    |                |
| boot_config       | text             | YES  |     | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

At some point we'll need to not re-open this bug for every schema change ;)

Comment 17

[(not currently active) Ted Mielczarek]

Yeah, sorry, just figured I'd get that out of the way for the initial cut. Thanks!