[MediaWiki-commits] [Gerrit] operations/puppet[production]: labs: Add db structure for keeping info about labsdb accounts

2016-12-05 Thread Yuvipanda (Code Review) 
Yuvipanda has submitted this change and it was merged.

Change subject: labs: Add db structure for keeping info about labsdb accounts
..


labs: Add db structure for keeping info about labsdb accounts

Bug: T149933
Change-Id: If98e7877151173623b96b168d44a82464b6e59e0
---
A modules/role/files/labs/db/maintain-dbusers.sql
1 file changed, 20 insertions(+), 0 deletions(-)

Approvals:
  Jcrespo: Looks good to me, but someone else must approve
  Yuvipanda: Looks good to me, approved
  jenkins-bot: Verified



diff --git a/modules/role/files/labs/db/maintain-dbusers.sql 
b/modules/role/files/labs/db/maintain-dbusers.sql
new file mode 100644
index 000..da867b7
--- /dev/null
+++ b/modules/role/files/labs/db/maintain-dbusers.sql
@@ -0,0 +1,20 @@
+-- Keep state about tool / user accounts on tool labs and their mysql
+-- account counterparts. This is the canonical store of this information.
+-- Schema is set up to be able to trivially query the following things:
+--  1. What labsdb hosts does this tool / user *not* have an account on.
+--  2. What's the mysql username / password for this tool / user.
+CREATE TABLE accounts(
+id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
+mysql_username VARCHAR(255) NOT NULL,
+type enum('tool', 'user') NOT NULL,
+username VARCHAR(255) NOT NULL,
+password VARCHAR(255) NOT NULL,
+) CHARSET=utf8mb4;
+CREATE UNIQUE INDEX account_type ON accounts(type, username);
+
+CREATE TABLE account_hosts(
+id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
+account_id INT UNSIGNED NOT NULL,
+hostname VARCHAR(255) NOT NULL,
+) CHARSET=utf8mb4;
+CREATE INDEX account_host_status ON accounts(account_id, hostname);

-- 
To view, visit https://gerrit.wikimedia.org/r/325268
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings

Gerrit-MessageType: merged
Gerrit-Change-Id: If98e7877151173623b96b168d44a82464b6e59e0
Gerrit-PatchSet: 4
Gerrit-Project: operations/puppet
Gerrit-Branch: production
Gerrit-Owner: Yuvipanda 
Gerrit-Reviewer: Jcrespo 
Gerrit-Reviewer: Yuvipanda 
Gerrit-Reviewer: jenkins-bot <>

___
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits

[MediaWiki-commits] [Gerrit] operations/puppet[production]: labs: Add db structure for keeping info about labsdb accounts

2016-12-05 Thread Yuvipanda (Code Review) 
Yuvipanda has uploaded a new change for review.

  https://gerrit.wikimedia.org/r/325268

Change subject: labs: Add db structure for keeping info about labsdb accounts
..

labs: Add db structure for keeping info about labsdb accounts

Bug: T149933
Change-Id: If98e7877151173623b96b168d44a82464b6e59e0
---
A modules/role/files/labs/db/maintain-dbusers.sql
1 file changed, 20 insertions(+), 0 deletions(-)


  git pull ssh://gerrit.wikimedia.org:29418/operations/puppet 
refs/changes/68/325268/1

diff --git a/modules/role/files/labs/db/maintain-dbusers.sql 
b/modules/role/files/labs/db/maintain-dbusers.sql
new file mode 100644
index 000..73df709
--- /dev/null
+++ b/modules/role/files/labs/db/maintain-dbusers.sql
@@ -0,0 +1,20 @@
+-- Keep state about tool / user accounts on tool labs and their mysql
+-- account counterparts. This is the canonical store of this information.
+-- Schema is set up to be able to trivially query the following things:
+--  1. What labsdb hosts does this tool / user *not* have an account on.
+--  2. What's the mysql username / password for this tool / user.
+CREATE TABLE accounts(
+id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
+mysql_username VARCHAR(255) NOT NULL,
+type VARCHAR(255) NOT NULL, -- 'tool' or 'user'
+username VARCHAR(255) NOT NULL,
+password VARCHAR(255) NOT NULL,
+) CHARSET=utf8mb4;
+CREATE UNIQUE INDEX account_type ON accounts(type, username);
+
+CREATE TABLE accounts_hosts(
+id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
+account_id INT UNSIGNED NOT NULL,
+hostname VARCHAR(255) NOT NULL,
+) CHARSET=utf8mb4;
+CREATE INDEX account_host_status ON accounts(account_id, hostname);

-- 
To view, visit https://gerrit.wikimedia.org/r/325268
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings

Gerrit-MessageType: newchange
Gerrit-Change-Id: If98e7877151173623b96b168d44a82464b6e59e0
Gerrit-PatchSet: 1
Gerrit-Project: operations/puppet
Gerrit-Branch: production
Gerrit-Owner: Yuvipanda 

___
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits