silverp1/pb — pull request #93 by buhman (2014-12-26 02:10:11+00:00)
Title: vanity pastes

Fixes #49.

Needs:

```bash
varnishadm ban 'req.http.host == ptpb.pw && req.url == /'
```

```sql
DROP TABLE IF EXISTS vanity;
CREATE TABLE vanity (
label TINYBLOB NOT NULL,
digest BINARY(20) NOT NULL,
secret BINARY(16) NOT NULL,
content BLOB NOT NULL,
PRIMARY KEY (label(39)),
UNIQUE KEY (digest),
UNIQUE KEY (secret)
)
ENGINE = InnoDB;

DELIMITER @@

DROP PROCEDURE IF EXISTS paste_insert_vanity@@
CREATE PROCEDURE paste_insert_vanity (
p_label TINYBLOB,
p_secret BINARY(16),
p_content MEDIUMBLOB
)
BEGIN
INSERT vanity (label, digest, secret, content)
VALUES (p_label, UNHEX(SHA1(p_content)), p_secret, p_content);
END;
@@

DROP PROCEDURE IF EXISTS paste_put@@
CREATE PROCEDURE paste_put (
p_secret BINARY(16),
p_content MEDIUMBLOB,
OUT p_id MEDIUMINT,
OUT p_digest BINARY(20),
OUT p_label TINYBLOB
)
BEGIN
START TRANSACTION;
UPDATE paste
SET digest = UNHEX(SHA1(p_content)), content = p_content
WHERE secret = p_secret;
UPDATE private
SET digest = UNHEX(SHA1(p_content)), content = p_content
WHERE secret = p_secret;
UPDATE private
SET digest = UNHEX(SHA1(p_content)), content = p_content
WHERE secret = p_secret;
/* .. */
SELECT id INTO p_id
FROM paste
WHERE secret = p_secret;
SELECT digest INTO p_digest
FROM private
WHERE secret = p_secret;
SELECT label INTO p_label
FROM vanity
WHERE secret = p_secret;
COMMIT;
END;
@@

DROP PROCEDURE IF EXISTS paste_delete@@
CREATE PROCEDURE paste_delete (
p_secret BINARY(16),
OUT p_id MEDIUMINT,
OUT p_digest BINARY(20),
OUT p_label TINYBLOB
)
BEGIN
START TRANSACTION;
SELECT id INTO p_id
FROM paste
WHERE secret = p_secret;
SELECT digest INTO p_digest
FROM private
WHERE secret = p_secret;
SELECT label INTO p_label
FROM vanity
WHERE secret = p_secret;
/* .. */
DELETE
FROM paste
WHERE secret = p_secret;
DELETE
FROM private
WHERE secret = p_secret;
DELETE
FROM vanity
WHERE secret = p_secret;
COMMIT;
END;
@@

DROP PROCEDURE IF EXISTS paste_get_stats@@
CREATE PROCEDURE paste_get_stats (
OUT p_count INT,
OUT p_length INT
)
BEGIN
SELECT COUNT(*), SUM(LENGTH(content)) INTO p_count, p_length
FROM (
SELECT content FROM paste
UNION
SELECT content FROM private
UNION
SELECT content FROM vanity
) AS p;
END;
@@

DROP PROCEDURE IF EXISTS paste_get_digest@@
CREATE PROCEDURE paste_get_digest (
p_digest BINARY(20),
OUT p_id MEDIUMINT,
OUT p_label TINYBLOB,
OUT p_exists BIT(1)
)
BEGIN
SELECT id INTO p_id
FROM paste
WHERE digest = p_digest;
SELECT label INTO p_label
FROM vanity
WHERE digest = p_digest;
SELECT 1 INTO p_exists
FROM private
WHERE digest = p_digest;
END;
@@

DROP PROCEDURE IF EXISTS paste_get_content_vanity@@
CREATE PROCEDURE paste_get_content_vanity (
p_label TINYBLOB,
OUT p_content MEDIUMBLOB
)
BEGIN
SELECT content INTO p_content
FROM vanity
WHERE label = p_label;
END;
@@
```