Prepared Statements

[larryxiao]

http://dev.mysql.com/doc/refman/5.7/en/innodb-memcached-setup.html http://dev.mysql.com/doc/refman/5.7/en/innodb-memcached-internals.html

[larryxiao]

• [ ] create postgres memcached catalog
  • http://www.postgresql.org/docs/9.1/static/catalogs.html
  • /src/postgres/backend/catalog/information_schema.sql
  • /src/postgres/backend/catalog/system_views.sql
• [ ] process memcached commands

[larryxiao]

• http://stackoverflow.com/questions/7022755/whats-the-difference-between-a-catalog-and-a-schema-in-a-relational-database/17943883#17943883
  • 

[larryxiao]

• Sid: Some useful files to understand how responses are sent back to the client in wire protocol
  • Sending SELECT query results - https://github.com/cmu-db/peloton/blob/master/src/postgres/backend/access/common/printtup.cpp
  • Message formats - https://github.com/cmu-db/peloton/blob/master/src/postgres/backend/libpq/pqformat.cpp

[larryxiao]

MySQL

create table test ( k VARCHAR(40) PRIMARY KEY, d VARCHAR(400) );
insert into test (k, d) values ('123123', '1\' ');
select * from test;
insert into test (k, d) values ('123123', '1\' ') ON DUPLICATE KEY UPDATE d='2\' ';
select * from test;

PostgresSQL

create table test ( k VARCHAR(40) PRIMARY KEY, d VARCHAR(400) );
insert into test (k, d) values ('123123', '1');
insert into test (k, d) values ('123123', '2');
insert into test (k, d) values ('123123', '2') on conflict do nothing;
insert into test (k, d) values ('123123', '2') on conflict (k) do update set d = excluded.d;

[larryxiao]

• escape sql query string http://www.postgresql.org/docs/9.2/static/libpq-exec.html

[larryxiao]

• http://www.postgresql.org/docs/9.5/static/functions-string.html
• https://github.com/memcached/memcached/blob/master/doc/protocol.txt

[larryxiao]

• issues with Peloton

`INSERT ... ON CONFLICT DO UPDATE` is supported in PostgreSQL 9.5

`ERROR:  transaction failed` in Peloton

larryxiao=# select * from test;
   k    | d 
--------+---
 123123 | 1
 123124 | 1
 4      | 1
(3 rows)

larryxiao=#  update test set d=CONCAT('prepend',d) where k='4';                     
ERROR:  Peloton exception :: Type INVALID does not match with VARCHARType INVALID can't be cast as VARCHAR...
larryxiao=# select * from test;

[larryxiao]

https://github.com/memcached/memcached/blob/master/doc/protocol.txt
setup prepared statements
GET, SET, ADD, REPLACE, DELETE, CAS, INCR, DECR, APPEND, PREPEND
FLUSH_ALL, STATS, VERSION, VERBOSITY not implemented
TODO directly return response from sql as defined in Memcached protocol
TODO CAS semantic: gets -> unique_cas_token

CREATE FUNCTION test() RETURNS varchar AS $$
    select * from test
    RETURNING 'ab';
$$ LANGUAGE SQL;

DROP TABLE test;
CREATE TABLE test ( key VARCHAR(200) PRIMARY KEY, value VARCHAR(2048), flag smallint, size smallint );

DEALLOCATE GET;
PREPARE GET (text) AS SELECT key, flag, size, value FROM TEST WHERE key = $1;

DEALLOCATE SET;
PREPARE SET (text, text, smallint, smallint) AS
  INSERT INTO test (key, value, flag, size) VALUES ($1, $2, $3, $4) ON CONFLICT (key) DO UPDATE SET value = excluded.value, flag = excluded.flag, size = excluded.size;

DEALLOCATE ADD;
PREPARE ADD (text, text, smallint, smallint) AS
  INSERT INTO test (key, value, flag, size) VALUES ($1, $2, $3, $4) ON CONFLICT (key) DO UPDATE SET value = excluded.value, flag = excluded.flag, size = excluded.size;

DEALLOCATE REPLACE;
PREPARE REPLACE (text, text, smallint, smallint) AS
  UPDATE test SET value = $2, flag = $3, size = $4 WHERE key=$1;

=========================

DEALLOCATE APPEND;
PREPARE APPEND (text, text) AS
  UPDATE test SET value=CONCAT(value,$2) WHERE key=$1;

DEALLOCATE PREPEND;
PREPARE PREPEND (text, text) AS
  UPDATE test SET value=CONCAT($2,value) WHERE key=$1;

DEALLOCATE INCR;
PREPARE INCR (text) AS
  UPDATE test SET value=CAST(value as int)+1 WHERE key=$1;

DEALLOCATE DECR;
PREPARE DECR (text) AS
  UPDATE test SET value=CAST(value as int)+1 WHERE key=$1;

DEALLOCATE DELETE;
PREPARE DELETE (text) AS
  DELETE FROM test WHERE key=$1;

DEALLOCATE CAS;
PREPARE CAS (text, text, text) AS
  UPDATE test SET value = case when v = '' then 'Y' else 'N' end;