Alchemy-Database入门

Alchemy Database 是一个低延迟、高 TPS 的 SQL 数据库，构建在 NoSQL 服务器 Redis 之上。通过深度嵌入 Lua 语言提供数据存储端的脚本开发。支持非结构化的数据存储，性能非常之强悍。
在一个单核的3G CPU 使用 PC3200 (400MHZ) 的机器上的测试结果：
● 95K INSERT/sec, 95K SELECT/sec, 90K UPDATE/sec, 100K DELETE/sec
● Range Queries returning 10 rows: 40K/sec
● 2 Table Joins returning 10 rows: 20K/sec
● Lua script performing read and write: 85K/sec

它的官方网站是：
https://code.google.com/archive/p/alchemydatabase/

1. 安装Alchemy Database

源代码下载：https://github.com/JakSprats/Alchemy-Database
安装很简单，直接make就可以，安装完进入目录如下：

进入redis/src目录，里面有alchemy-server和alchemy-cli工具，与redis的server和client类似。
如果之前已经安装过redis，那么就修改一下alchemy-server的启动端口。然后用alchemy-cli就可以连接上alchemy-server。

2. 启动alchemy-server

命令：./alchemy-server ../redis.conf
注意如果默认端口6379被占用，那么需要修改redis.conf的端口号。

3. alchemy支持的SQL数据类型

目前alchemy支持的SQL数据类型有UNSIGNED INT, UNSIGNED LONG, U128 (128 bit INTEGER), TEXT, and FLOAT.
Datetime数据类型用 UNSIGNED INT/LONG或者FLOAT来表示。
TEXT利用变量长度存储和比较，可以适应mysql中各种类型的数据类型，包括VARCHAR, CHAR, BLOB等。

4. alchemy支持的SQL

Create/Drop Operations:
  ● CREATE TABLE customer
(id INT, group_id INT, name TEXT, phone text, age INT);
  ● CREATE INDEX i_cg ON customer (group_id);
  ● CREATE INDEX i_cga ON customer (group_id, age);
  ● CREATE UNIQUE INDEX iu_cg ON customer (group_id);
  ● DROP TABLE customer
  ● DROP INDEX cust_group_ind
  ● DESC customer
  ● DUMP customer
  ● DUMP customer TO MYSQL 
  ● DUMP customer TO FILE fname
  ● ALTER TABLE customer ADD COLUMN state INT
Single Row Operations
  ● INSERT INTO customer VALUES (1,300,’John Doe’,’301-933-1891’,30);
  ● SELECT group_id, phone FROM customer WHERE id = 1
  ● DELETE FROM customer WHERE id = 1
  ● UPDATE customer SET phone = ‘703-933-1891’, group_id = 4 WHERE id = 1
  ● INSERT INTO customer VALUES (1,300,’John Doe’,’301-933-1891’,30) RETURN SIZE;
  ● REPLACE INTO customer VALUES (1,300,’John Doe’,’703-387-7777’,30)
  ● INSERT INTO customer VALUES (1,300,’John Doe’,’703-387-7777’,30) ON DUPLICATE KEY UPDATE SET phone = ’703-387-7777’
  ● INSERT INTO customer VALUES (1,300,’John Doe’,’301-933-1891’,30) (2,300,’Jane Smith’,’301-946-2222’,22) (1,300,’John Doe’,’202-933-444’,55)
  ● INSERT INTO customer (id, name, phone) VALUES (1,’John Doe’,’301-933-1891’); 
Index Operations
  ● SELECT group_id, phone FROM customer WHERE group_id BETWEEN 300 AND 400 [ORDER BY column LIMIT n OFFSET m]
  ● SELECT group_id, phone FROM customer WHERE group_id IN (300, 301, 307, 311) [ORDER BY column LIMIT n OFFSET m]
  ● DELETE FROM customer WHERE group_id BETWEEN 300 AND 400 [ORDER BY column LIMIT n OFFSET m]
  ● UPDATE customer SET phone = ‘703-933-1891’, group_id = 4 WHERE group_id = 300 [ORDER BY column LIMIT n OFFSET m]
JOINS

Alchemy目前还不支持join操作，但是可以通过连接表的方式来实现join。

SELECT c.name, g.slogan FROM customer c, group g WHERE c.group_id = g.id AND g.id BETWEEN 300 AND 400 [ORDER BY table.column LIMIT n OFFSET m]

其他可以参考官方网站：
https://code.google.com/archive/p/alchemydatabase/wikis/CommandReference.wiki#Beyond_SQL

5. alchemy客户端使用

启动alchemy-cli，这里连接的是6800端口，执行一下4句SQL：

CREATE TABLE customer (id INT, group_id INT, name TEXT, phone text, age INT);
INSERT INTO customer VALUES (1,300,'John Doe','301-933-1891',30);
SELECT group_id, phone FROM customer WHERE id = 1;
UPDATE customer SET phone = '703-933-1891', group_id = 4 WHERE id = 1;

6. REST API的客户端

从alchemy0.2开始，AlchemyDB的客户端已经切换到REST API，它是通过http协议来访问server，例如：http://server:ip/GET/XYZ来返回数据。用/符号代替sql语句中的空格。

例如：http://192.168.144.120:6800/SELECT/*/FROM/customer/WHERE/id=1

*2
$30
id, group_id, name, phone, age
$31
1,4,'JohnDoe','703-933-1891',30

第一行返回的是数据行数，例子中返回2条（一条字段名，一条值）
以下每两行分别返回：行的字符数，以及行的值。

具体可以看官方文档：
https://code.google.com/archive/p/alchemydatabase/wikis/RestAPI.wiki