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

BUG=skia:
R=mtklein@google.com

Author: jcgregorio@google.com

Review URL: https://codereview.chromium.org/235373002

git-svn-id: http://skia.googlecode.com/svn/trunk@14175 2bbb7eff-a529-9590-31e7-b0007b416f81

6 files changed, 163 insertions, 36 deletions

diff --git a/experimental/webtry/DESIGN.md b/experimental/webtry/DESIGN.md
index 66c7f1f798..349a10e495 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,
+      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.
diff --git a/experimental/webtry/README.md b/experimental/webtry/README.md
index c7573110f6..d5bc6189b4 100644
--- a/experimental/webtry/README.md
+++ b/experimental/webtry/README.md
@@ -8,11 +8,11 @@ immediately. To make sandboxing easier this must be built w/GPU off.
 Running Locally
 ===============
 
-$ GYP_GENERATORS=ninja  ./gyp_skia  gyp/webtry.gyp gyp/most.gyp -Dskia_gpu=0
-$ ninja -C out/Debug webtry
-$ cd experimental/webtry
-$ go build webtry.go
-$ ./webtry
+    $ GYP_GENERATORS=ninja  ./gyp_skia  gyp/webtry.gyp gyp/most.gyp -Dskia_gpu=0
+    $ ninja -C out/Debug webtry
+    $ cd experimental/webtry
+    $ go build webtry.go
+    $ ./webtry
 
 Then visit http://localhost:8000 in your browser.
 
@@ -23,52 +23,58 @@ Full Server Setup
 
 Create a GCE instance:
 
-gcutil --project=google.com:skia-buildbots addinstance skia-webtry-b \
-  --zone=us-central2-b --external_ip_address=108.170.220.126 \
-  --service_account=default \
-  --service_account_scopes="https://www.googleapis.com/auth/devstorage.full_control" \
-  --network=default --machine_type=n1-standard-1 --image=backports-debian-7-wheezy-v20140331 \
-  --persistent_boot_disk
+    gcutil --project=google.com:skia-buildbots addinstance skia-webtry-b \
+      --zone=us-central2-b --external_ip_address=108.170.220.126 \
+      --service_account=default \
+      --service_account_scopes="https://www.googleapis.com/auth/devstorage.full_control" \
+      --network=default --machine_type=n1-standard-1 --image=backports-debian-7-wheezy-v20140331 \
+      --persistent_boot_disk
 
 SSH into the instance:
 
-  gcutil --project=google.com:skia-buildbots ssh --ssh_user=default skia-webtry-b
+    gcutil --project=google.com:skia-buildbots ssh --ssh_user=default skia-webtry-b
 
 
+Do once
+-------
+
 The following things only need to be done once
-----------------------------------------------
 
 1. sudo apt-get install git schroot debootstrap
 2. git clone https://skia.googlesource.com/skia
 3. Add the following to /etc/fstab and reboot:
 
-  none /dev/shm tmpfs rw,nosuid,nodev,noexec 0 0
+    none /dev/shm tmpfs rw,nosuid,nodev,noexec 0 0
 
 The above will allow ninja to run. See http://stackoverflow.com/questions/2009278/python-multiprocessing-permission-denied
 
 4. Add the following to the /etc/schroot/minimal/fstab:
 
-  /home/webtry/inout             /inout  none    rw,bind         0       0
+    /home/webtry/inout             /inout  none    rw,bind         0       0
 
 5. Change /etc/monit/monitrc to:
 
-  set daemon 2
+    set daemon 2
 
 then run the following so it applies:
 
-  sudo /etc/init.d/monit restart
+    sudo /etc/init.d/monit restart
 
 This means that monit will poll every two seconds that our application is up and running.
 
+Do the first time
+-----------------
+
 Do the following the first time you setup a machine, and each time you want to update the code running on the server
---------------------------------------------------------------------------------------------------------------------
 
-cd ~/skia/experimental/webtry/setup
-./webtry_setup.sh
+    cd ~/skia/experimental/webtry/setup
+    ./webtry_setup.sh
+
 
+Once, after setup
+-----------------
 
-Do these steps only once, but only after running webtry_setup.sh the first time
--------------------------------------------------------------------------------
+Do this step only once, but only after running webtry_setup.sh the first time
 
-1. sudo debootstrap --variant=minbase wheezy /srv/chroot/webtry
+    sudo debootstrap --variant=minbase wheezy /srv/chroot/webtry
 
diff --git a/experimental/webtry/TODO b/experimental/webtry/TODO
deleted file mode 100644
index 81382cda5d..0000000000
--- a/experimental/webtry/TODO
+++ /dev/null
@@ -1,11 +0,0 @@
- - Add -port flag to webtry.go.
- - Add flag for inout directory to webtry.go.
- - In webtry.go add mutexes per hash, to avoid conflicts of writing the same file at the same time.
- - Don't allow #macros in user code.
- - Limit the size of the user code submitted.
- - Add font support in the c++ template.
- - Add in all the Sk header files that could be needed.
- - Clean up web page appearance.
- - Add inline links to doxygen.
- - Set wait cursor when doing the compiling.
- - Add monitoring and probing (nagios).
diff --git a/experimental/webtry/TODO.md b/experimental/webtry/TODO.md
new file mode 100644
index 0000000000..57a061b734
--- /dev/null
+++ b/experimental/webtry/TODO.md
@@ -0,0 +1,22 @@
+Public facing feature requests
+------------------------------
+ - return printf output
+ - permalinks for fiddles
+ - ability to render against multiple targets (gpu/cpu)
+ - versioning (which version of skia was this run against)
+ - serve up a iframe-able page
+ - magnifying glass (both client side and server side)
+ - specify scale, rotate, clip (possibly animating over a range)
+ - change canvas size from 300x300 to other sizes
+
+
+Implementation details
+----------------------
+ - Add flag for inout directory to webtry.go.
+ - In webtry.go add mutexes per hash, to avoid conflicts of writing the same file at the same time.
+ - Don't allow #macros in user code.
+ - Limit the size of the user code submitted.
+ - Add font support in the c++ template.
+ - Add inline links to doxygen.
+ - Add monitoring and probing (nagios).
+ - sanitize the file name in the output.
diff --git a/experimental/webtry/setup/continue_install b/experimental/webtry/setup/continue_install
index 0db794b13d..be3c005a09 100755
--- a/experimental/webtry/setup/continue_install
+++ b/experimental/webtry/setup/continue_install
@@ -24,6 +24,9 @@ else
   tar -xzf go1.2.1.linux-amd64.tar.gz
 fi
 export GOROOT=$HOME/go
+mkdir=$HOME/golib
+export GOPATH=$HOME/golib
+
 export PATH=$PATH:$GOROOT/bin
 
 mkdir /home/webtry/cache
@@ -53,4 +56,5 @@ ninja -C out/Debug webtry
 
 cd experimental/webtry
 
+go get github.com/go-sql-driver/mysql
 go build webtry.go
diff --git a/experimental/webtry/webtry.go b/experimental/webtry/webtry.go
index d0588d558a..fb53b593e3 100644
--- a/experimental/webtry/webtry.go
+++ b/experimental/webtry/webtry.go
@@ -3,10 +3,12 @@ package main
 import (
 	"bytes"
 	"crypto/md5"
+	"database/sql"
 	"encoding/base64"
 	"encoding/json"
 	"flag"
 	"fmt"
+	_ "github.com/go-sql-driver/mysql"
 	"io/ioutil"
 	"log"
 	"net/http"
@@ -28,11 +30,15 @@ var (
 
 	// index is the main index.html page we serve.
 	index []byte
+
+	// db is the database, nil if we don't have an SQL database to store data into.
+	db *sql.DB = nil
 )
 
 // flags
 var (
 	useChroot = flag.Bool("use_chroot", false, "Run the compiled code in the schroot jail.")
+	port      = flag.String("port", ":8000", "HTTP service address (e.g., ':8000')")
 )
 
 // lineNumbers adds #line numbering to the user's code.
@@ -63,6 +69,32 @@ func init() {
 	if err != nil {
 		panic(err)
 	}
+
+	// Connect to MySQL server. First, get the password from the metadata server.
+	// See https://developers.google.com/compute/docs/metadata#custom.
+	req, err := http.NewRequest("GET", "http://metadata/computeMetadata/v1/instance/attributes/password", nil)
+	if err != nil {
+		panic(err)
+	}
+	client := http.Client{}
+	req.Header.Add("X-Google-Metadata-Request", "True")
+	if resp, err := client.Do(req); err == nil {
+		password, err := ioutil.ReadAll(resp.Body)
+		if err != nil {
+			log.Printf("ERROR: Failed to read password from metadata server: %q\n", err)
+			panic(err)
+		}
+		// The IP address of the database is found here:
+		//    https://console.developers.google.com/project/31977622648/sql/instances/webtry/overview
+		// And 3306 is the default port for MySQL.
+		db, err = sql.Open("mysql", fmt.Sprintf("webtry:%s@tcp(173.194.83.52:3306)/webtry", password))
+		if err != nil {
+			log.Printf("ERROR: Failed to open connection to SQL server: %q\n", err)
+			panic(err)
+		}
+	} else {
+		log.Printf("INFO: Failed to find metadata, unable to connect to MySQL server (Expected when running locally): %q\n", err)
+	}
 }
 
 // userCode is used in template expansion.
@@ -155,6 +187,15 @@ func reportError(w http.ResponseWriter, r *http.Request, err error, message stri
 	w.Write(resp)
 }
 
+func writeToDatabase(hash string, code string) {
+	if db == nil {
+		return
+	}
+	if _, err := db.Exec("INSERT INTO webtry (code, hash) VALUES(?, ?)", code, hash); err != nil {
+		log.Printf("ERROR: Failed to insert code into database: %q\n", err)
+	}
+}
+
 // mainHandler handles the GET and POST of the main page.
 func mainHandler(w http.ResponseWriter, r *http.Request) {
 	if r.Method == "GET" {
@@ -166,11 +207,13 @@ func mainHandler(w http.ResponseWriter, r *http.Request) {
 			reportError(w, r, err, "Failed to read a request body.")
 			return
 		}
-		hash, err := expandCode(LineNumbers(string(b)))
+		code := string(b)
+		hash, err := expandCode(LineNumbers(code))
 		if err != nil {
 			reportError(w, r, err, "Failed to write the code to compile.")
 			return
 		}
+		writeToDatabase(hash, code)
 		message, err := doCmd(fmt.Sprintf(RESULT_COMPILE, hash, hash), true)
 		if err != nil {
 			reportError(w, r, err, "Failed to compile the code:\n"+message)
@@ -222,5 +265,5 @@ func main() {
 	flag.Parse()
 
 	http.HandleFunc("/", mainHandler)
-	log.Fatal(http.ListenAndServe(":8000", nil))
+	log.Fatal(http.ListenAndServe(*port, nil))
 }