Design and implement database for imaging service

RESOLVED FIXED

Status

RESOLVED FIXED
6 years ago
5 years ago

People

(Reporter: ted, Assigned: dustin)

Tracking

Details

(URL)

Attachments

(2 attachments, 4 obsolete attachments)

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.
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?).
Summary: Design database schema for imaging service → Design and implement database for imaging service
I'm not a big ORM fan, honestly, but I'll let you know what I wind up doing.
I didn't say ORM!  Sqlalchemy has a great abstraction layer underneath its ORM layer.
Depends on: 789978
Created attachment 660114 [details]
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.
(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.
Blocks: 797826
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.
I'll make it TEXT, since we don't need to index it.
Created attachment 668038 [details]
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.
Created attachment 668039 [details]
schema.py

corrected version
Attachment #668038 - Attachment is obsolete: true
Created attachment 668040 [details]
schema.sql

Updated with relay_info as type text
Attachment #660114 - Attachment is obsolete: true
I had to futz a little to get MySQL to swallow it.  I've also renamed the DB to black_mobile_magic.
Created attachment 668063 [details]
schema.py
Attachment #668039 - Attachment is obsolete: true
Created attachment 668064 [details]
schema.sql
Attachment #668040 - Attachment is obsolete: true
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.
Status: NEW → RESOLVED
Last Resolved: 6 years ago
Resolution: --- → FIXED
We discussed this on IRC, but we need to add a TEXT 'config' column to the boards table.
Status: RESOLVED → REOPENED
Resolution: FIXED → ---
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 ;)
Status: REOPENED → RESOLVED
Last Resolved: 6 years ago6 years ago
Resolution: --- → FIXED
Yeah, sorry, just figured I'd get that out of the way for the initial cut. Thanks!
Component: Server Operations: RelEng → RelOps
Product: mozilla.org → Infrastructure & Operations
You need to log in before you can comment on or make changes to this bug.