(My)SQL schema discussion

[jessepeterson]

I'd like to start some discussion about the decisions made with the MySQL (and potentially other RDBMS) schemas. Specifically about column types and table relationships.

I pointed out some things in the schema.sql under the storage backend in NanoMDM. But things like: to binary-column or not (leaning not). Which primary keys to use for command responses (currently just enrollment ID and result status). Check out the schema file for more info.

Otherwise just a general discussion about SQL schema as related to MDM and NanoMDM.

[jessepeterson]

The device queue in NanoMDM's mysql storage backend uses an SQL VIEW to query the next command for a device. To consider folding this VIEW back into a real query instead of a view.

Reasons for a VIEW:

• Can replace the view with your own specific VIEW if you so pleased without changing code
• Matches the same queries that devices do when using the queue manually (e.g. for debugging or inspection)
• Consistent query for future API usage (e.g. command/queue inspection) — see Enrollment (device) API #6

Reasons against a VIEW:

• Just more complexity/another abstraction to manage (including performance/indexing etc.)
• Also hides complexity in the code of the view (could also be a positive)

And I'm sure more reasons both for and against.

VIEW use in code:

nanomdm/storage/mysql/queue.go

Line 89 in fce958e

`SELECT command_uuid, request_type, command FROM view_queue WHERE id = ? AND active = 1 AND `+statusWhere+` LIMIT 1;`,

View in schema:

nanomdm/storage/mysql/schema.sql

Lines 217 to 239 in fce958e

	CREATE OR REPLACE VIEW view_queue AS
	SELECT
	q.id,
	q.created_at,
	q.active,
	q.priority,
	c.command_uuid,
	c.request_type,
	c.command,
	r.updated_at AS result_updated_at,
	r.status,
	r.result
	FROM
	enrollment_queue AS q
	INNER JOIN commands AS c
	ON q.command_uuid = c.command_uuid
	LEFT JOIN command_results r
	ON r.command_uuid = q.command_uuid AND r.id = q.id
	ORDER BY
	q.priority DESC,
	q.created_at;

The idea here is to eliminate the VIEW usage in the queue code (essentially just embed the VIEW query into queue.go. The VIEW itself in the schema can stay it just won't be used by nanomdm code.

Thoughts?

[tomaswallentinus]

Is there a reason not to use a stored procedure instead? That way you have cache as well.

[jessepeterson]

Is there a reason not to use a stored procedure instead? That way you have cache as well.

I think the issue we want to go the other way (removing database-specific complexities from core code). In other words: think about environments that can't or won't support stored procedures or VIEWs, etc. Just plain ol' CRUD queries. (Even think about environments that don't allow foreign key constraints).

[jessepeterson]

Removal of the VIEW from the MySQL backend was implemented in #59 FYI.

[jessepeterson]

See discussion about queue management (i.e. command deletion) over in #43.