Add design for sql storage, implementation of db that stores the data, not retrieving yet.

experimental/webtry/DESIGN.md

Index: experimental/webtry/DESIGN.md
diff --git a/experimental/webtry/DESIGN.md b/experimental/webtry/DESIGN.md
index 66c7f1f7986537a33f4d099e8e1aab257756a6c6..349a10e4956db7a3539e64f3a5770fcd88fcb26d 100644
--- a/experimental/webtry/DESIGN.md
+++ b/experimental/webtry/DESIGN.md
@@ -9,6 +9,7 @@ Allows trying out Skia code in the browser.
 
 Security
 --------
+
 We're putting a C++ compiler on the web, and promising to run the results of
 user submitted code, so security is a large concern. Security is handled in a
 layered approach, using a combination of seccomp-bpf, chroot jail and rlimits.
@@ -31,6 +32,7 @@ User submitted code is also restricted in the following ways:
 Architecture
 ------------
 
+
 The server runs on GCE, and consists of a Go Web Server that calls out to the
 c++ compiler and executes code in a chroot jail. See the diagram below:
 
@@ -114,6 +116,67 @@ calls:
     munmap
     brk
 
+Database
+--------
+
+Code submitted is stored in an SQL database so that it can be referenced
+later, i.e. we can let users bookmark their SkFiddles.
+
+The storage layer will be Cloud SQL (a cloud version of MySQL). Back of the
+envelope estimates of traffic come out to a price of a about $1/month.
+
+All passwords for MySQL are stored in valentine.
+
+To connect to the database from the skia-webtry-b server:
+
+    $ mysql --host=173.194.83.52 --user=root --password
+
+Initial setup of the database, the user, and the only table:
+
+    CREATE DATABASE webtry;
+    USE webtry;
+    CREATE USER 'webtry'@'%' IDENTIFIED BY '<password is in valentine>';
+    GRANT SELECT, INSERT, UPDATE ON webtry.webtry TO 'webtry'@'%';
+
+    CREATE TABLE webtry (
+      code      TEXT      DEFAULT ''                 NOT NULL,

[mtklein, 2014/04/11 18:12:40]

Image at the time of creation too?  If these are for bugs, it'd be good to have
that as a reference point.

Or, dare I suggest it, another table to support multiple timestamped (+ other
meta) images?

[jcgregorio, 2014/04/11 20:24:01]

Yeah, that should be a separate table with the git hash + code hash as the
index. Even then, I'm not sure about storing the image in the database.

Regardless, nothing I want to tackle in this CL.

On 2014/04/11 18:12:40, mtklein wrote:

+      create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP  NOT NULL,
+      hash      CHAR(64)  DEFAULT ''                 NOT NULL,
+      PRIMARY KEY(hash)
+    );
+
+Common queries webtry.go will use:
+
+    INSERT INTO webtry (code, hash) VALUES('int i = 0;...', 'abcdef...');
+
+    SELECT code, create_ts, hash FROM webtry WHERE hash='abcdef...';
+
+    SELECT code, create_ts, hash FROM webtry ORDER BY create_ts DESC LIMIT 2;
+
+    // To change the password for the webtry sql client:
+    SET PASSWORD for 'webtry'@'%' = PASSWORD('<password is in valentine>');
+
+    // Run before and after to confirm the password changed:
+    SELECT Host, User, Password FROM mysql.user;
+
+Password for the database will be stored in the metadata instance, if the
+metadata server can't be found, i.e. running locally, then data will not be
+stored.  To see the current password stored in metadata and the fingerprint:
+
+    gcutil  --project=google.com:skia-buildbots    getinstance skia-webtry-b
+
+To set the mysql password that webtry is to use:
+
+    gcutil  --project=google.com:skia-buildbots   setinstancemetadata skia-webtry-b --metadata=password:'[mysql client webtry password]' --fingerprint=[some fingerprint]
+
+To retrieve the password from the running instance just GET the right URL from
+the metadata server:
+
+    curl "http://metadata/computeMetadata/v1/instance/attributes/password" -H "X-Google-Metadata-Request: True"
+
+N.B. If you need to change the MySQL password that webtry uses, you must change
+it both in MySQL and the value stored in the metadata server.
+
 Installation
 ------------
 See the README file.