neo4j Cypher语句（CQL）练习

CQL虽然看起来挺容易懂，实际上还是挺难写的，跟SQL的直观完全不能比较，其复杂度的来源可能是图的结构引起的，而非本身语言设计的问题。当然，不可否认，neo4j中CQL的设计还是略微的有一些不足。本次的练习是基于《Graph Databases（2013）》的第5章，确切说是从p105开始的3个例子。

这里提到了3个现实的图数据库模型，分别是社交关系模型（Social Networks），权限控制模型（Access Control）和物流模型（Logistics）。具体的模型应用的背景，可以去参考书中的描述，书可以去官网下载电子版。

社交关系模型的图结构如下，

权限控制模型的图结构如下，

物流模型的图结构如下，

其中，物流模型中不同颜色的边表示不同的时间段，时间段估计是用unix time_t类型记录的一个长整数，如下图

首先要先建立3个图数据库的基本数据，这里使用CQL中的CREATE即可。

社交网络模型的建图语句如下

CREATE 
// User
(uc:User {name : 'Charlie'}), 
(ub:User {name : 'Ben'}), 
(us:User {name : 'Sarah'}), 
(ua:User {name : 'Arnold'}), 
(ue:User {name : 'Emily'}), 
(ug:User {name : 'Gordon'}), 
(uk:User {name : 'Kate'}),
// Company
(ca:Company {name : 'Acme, Inc.'}), 
(cs:Company {name : 'Startup, Ltd.'}),
// Interest
(im:Interest {name : 'Medicine'}), 
(ic:Interest {name : 'Cars'}), 
(ir:Interest {name : 'REST'}), 
(ig:Interest {name : 'Graphs'}), 
(ij:Interest {name : 'Java'}), 
(it:Interest {name : 'Travel'}), 
(id:Interest {name : 'Design'}), 
(ia:Interest {name : 'Art'}), 
(im1:Interest {name : 'Music'}), 
(id1:Interest {name : 'Drama'}),
// Project
(pn:Project {name : 'Next Gen Platform'}), 
(pq:Project {name : 'Quantum Leap'}), 
(pp:Project {name : 'Phoenix'}),
// WORKS_FOR
(uc)-[:WORKS_FOR]->(ca),
(ub)-[:WORKS_FOR]->(ca),
(us)-[:WORKS_FOR]->(ca),
(ua)-[:WORKS_FOR]->(cs),
(ue)-[:WORKS_FOR]->(cs),
(ug)-[:WORKS_FOR]->(cs),
(uk)-[:WORKS_FOR]->(cs),
// WORKED_ON
(uc)-[:WORKED_ON]->(pn),
(ub)-[:WORKED_ON]->(pn),
(us)-[:WORKED_ON]->(pn),
(us)-[:WORKED_ON]->(pq),
(ua)-[:WORKED_ON]->(pp),
(ue)-[:WORKED_ON]->(pn),
(ue)-[:WORKED_ON]->(pq),
(uk)-[:WORKED_ON]->(pq),
(uk)-[:WORKED_ON]->(pp),
// INTERESTED_IN
(uc)-[:INTERESTED_IN]->(im),
(uc)-[:INTERESTED_IN]->(ic),
(uc)-[:INTERESTED_IN]->(ig),
(ub)-[:INTERESTED_IN]->(ir),
(ub)-[:INTERESTED_IN]->(ig),
(us)-[:INTERESTED_IN]->(ir),
(us)-[:INTERESTED_IN]->(ig),
(us)-[:INTERESTED_IN]->(ij),
(ua)-[:INTERESTED_IN]->(ir),
(ua)-[:INTERESTED_IN]->(ig),
(ua)-[:INTERESTED_IN]->(ij),
(ua)-[:INTERESTED_IN]->(it),
(ue)-[:INTERESTED_IN]->(id),
(ue)-[:INTERESTED_IN]->(ia),
(ug)-[:INTERESTED_IN]->(ig),
(ug)-[:INTERESTED_IN]->(im1),
(uk)-[:INTERESTED_IN]->(im1),
(uk)-[:INTERESTED_IN]->(id1)
;

CREATE INDEX ON :User(name);
CREATE INDEX ON :Company(name);
CREATE INDEX ON :Interest(name);
CREATE INDEX ON :Project(name);

权限控制模型的建图语句如下

CREATE 
// Admin
(ab:Admin {name : 'Ben'}), 
(as1:Admin {name : 'Sarah'}), 
(al:Admin {name : 'Liz'}), 
(ap:Admin {name : 'Phil'}), 
// Group
(g1:Group {name : 'Group1'}), 
(g2:Group {name : 'Group2'}),
(g3:Group {name : 'Group3'}),
(g4:Group {name : 'Group4'}),
(g5:Group {name : 'Group5'}),
(g6:Group {name : 'Group6'}),
(g7:Group {name : 'Group7'}),
// Company
(ca:Company {name : 'Acme'}), 
(cs:Company {name : 'Spinoff'}), 
(cs1:Company {name : 'Startup'}), 
(cs2:Company {name : 'Skunk-workz'}), 
(cb:Company {name : 'Big Co'}), 
(ca1:Company {name : 'Aquired Ltd.'}), 
(cs3:Company {name : 'Subsid\'ry'}), 
(co:Company {name : 'One-Man Shop'}), 
(cd:Company {name : 'Dev Shop'}), 
// Employee
(ea:Employee {name : 'Arnold'}), 
(ec:Employee {name : 'Charlie'}), 
(ee:Employee {name : 'Emily'}),
(eg:Employee {name : 'Gordon'}),
(el:Employee {name : 'Lucy'}),
(ek:Employee {name : 'Kate'}),
(ea1:Employee {name : 'Alister'}),
(ee1:Employee {name : 'Eve'}),
(eg1:Employee {name : 'Gary'}),
(eb:Employee {name : 'Bill'}),
(em:Employee {name : 'Mary'}),
// Account
(n1:Account {name : 'Account1'}), 
(n2:Account {name : 'Account2'}), 
(n3:Account {name : 'Account3'}), 
(n4:Account {name : 'Account4'}), 
(n5:Account {name : 'Account5'}), 
(n6:Account {name : 'Account6'}), 
(n7:Account {name : 'Account7'}), 
(n8:Account {name : 'Account8'}), 
(n9:Account {name : 'Account9'}), 
(n10:Account {name : 'Account10'}), 
(n11:Account {name : 'Account11'}), 
(n12:Account {name : 'Account12'}), 
// MEMBER_OF
(ab)-[:MEMBER_OF]->(g1),
(ab)-[:MEMBER_OF]->(g3),
(as1)-[:MEMBER_OF]->(g2),
(as1)-[:MEMBER_OF]->(g3),
(al)-[:MEMBER_OF]->(g4),
(al)-[:MEMBER_OF]->(g5),
(al)-[:MEMBER_OF]->(g6),
(ap)-[:MEMBER_OF]->(g7),
// ALLOWED_INHERIT
(g1)-[:ALLOWED_INHERIT]->(ca),
(g3)-[:ALLOWED_INHERIT]->(cs1),
(g4)-[:ALLOWED_INHERIT]->(cb),
(g7)-[:ALLOWED_INHERIT]->(cs3),
// ALLOWED_DO_NOT_INHERIT
(g2)-[:ALLOWED_DO_NOT_INHERIT]->(ca),
(g6)-[:ALLOWED_DO_NOT_INHERIT]->(co),
// DENIED
(g2)-[:DENIED]->(cs2),
(g5)-[:DENIED]->(ca1),
// CHILD_OF
(cs)-[:CHILD_OF]->(ca),
(cs2)-[:CHILD_OF]->(cs1),
(ca1)-[:CHILD_OF]->(cb),
(cs3)-[:CHILD_OF]->(ca1),
(co)-[:CHILD_OF]->(cs3),
(cd)-[:CHILD_OF]->(cs3),
// WORKS_FOR
(ea)-[:WORKS_FOR]->(ca),
(ec)-[:WORKS_FOR]->(ca),
(ee)-[:WORKS_FOR]->(cs),
(eg)-[:WORKS_FOR]->(cs1),
(el)-[:WORKS_FOR]->(cs1),
(ek)-[:WORKS_FOR]->(cs2),
(ea1)-[:WORKS_FOR]->(cb),
(ee1)-[:WORKS_FOR]->(ca1),
(eg1)-[:WORKS_FOR]->(cs3),
(eb)-[:WORKS_FOR]->(co),
(em)-[:WORKS_FOR]->(cd),
// HAS_ACCOUNT
(ea)-[:HAS_ACCOUNT]->(n1),
(ea)-[:HAS_ACCOUNT]->(n2),
(ec)-[:HAS_ACCOUNT]->(n3),
(eg)-[:HAS_ACCOUNT]->(n4),
(el)-[:HAS_ACCOUNT]->(n5),
(ee)-[:HAS_ACCOUNT]->(n6),
(ek)-[:HAS_ACCOUNT]->(n7),
(ea1)-[:HAS_ACCOUNT]->(n8),
(ee1)-[:HAS_ACCOUNT]->(n9),
(eb)-[:HAS_ACCOUNT]->(n10),
(eg1)-[:HAS_ACCOUNT]->(n11),
(em)-[:HAS_ACCOUNT]->(n12)
; 

物流模型的建图语句如下，

CREATE 
// Parcel Center
(pc1:Center {name : 'Parcel Center-1'}), 
(pc2:Center {name : 'Parcel Center-2'}), 
// Delivery Base
(db1:Base {name : 'Delivery Base1'}), 
(db2:Base {name : 'Delivery Base2'}),
(db3:Base {name : 'Delivery Base3'}),
// Delivery Area
(da1:Area {name : 'Delivery Area1'}), 
(da2:Area {name : 'Delivery Area2'}),
(da3:Area {name : 'Delivery Area3'}),
(da4:Area {name : 'Delivery Area4'}), 
// Delivery Segment
(ds1:Segment {name : 'Delivery Segment1'}), 
(ds2:Segment {name : 'Delivery Segment2'}),
(ds3:Segment {name : 'Delivery Segment3'}),
(ds4:Segment {name : 'Delivery Segment4'}), 
(ds5:Segment {name : 'Delivery Segment5'}), 
(ds6:Segment {name : 'Delivery Segment6'}),
(ds7:Segment {name : 'Delivery Segment7'}),
(ds8:Segment {name : 'Delivery Segment8'}), 
// CONNECTED_TO
(pc1)-[:CONNECTED_TO {cost:3, start_date:1350255600000, end_date:1350860400000}]->(db1),
(pc1)-[:CONNECTED_TO {cost:2, start_date:1350860400000, end_date:1351465200000}]->(db1),
(pc1)-[:CONNECTED_TO {cost:6, start_date:1351465200000, end_date:1352070000000}]->(db1),

(pc1)-[:CONNECTED_TO {cost:3, start_date:1350255600000, end_date:1350860400000}]->(db2),
(pc1)-[:CONNECTED_TO {cost:2, start_date:1350860400000, end_date:1351465200000}]->(db2),
(pc1)-[:CONNECTED_TO {cost:6, start_date:1351465200000, end_date:1352070000000}]->(db2),

(pc1)-[:CONNECTED_TO {cost:6, start_date:1351465200000, end_date:1352070000000}]->(db3),

(pc2)-[:CONNECTED_TO {cost:5, start_date:1350860400000, end_date:1351465200000}]->(db1),

(pc2)-[:CONNECTED_TO {cost:3, start_date:1350255600000, end_date:1350860400000}]->(db2),
(pc2)-[:CONNECTED_TO {cost:2, start_date:1350860400000, end_date:1351465200000}]->(db2),
(pc2)-[:CONNECTED_TO {cost:6, start_date:1351465200000, end_date:1352070000000}]->(db2),

(pc2)-[:CONNECTED_TO {cost:3, start_date:1350255600000, end_date:1350860400000}]->(db3),
(pc2)-[:CONNECTED_TO {cost:2, start_date:1350860400000, end_date:1351465200000}]->(db3),

// DELIVERY_ROUTE
(db1)-[:DELIVERY_ROUTE {cost:3, start_date:1350255600000, end_date:1350860400000}]->(da1),
(db1)-[:DELIVERY_ROUTE {cost:2, start_date:1350860400000, end_date:1351465200000}]->(da1),
(db1)-[:DELIVERY_ROUTE {cost:6, start_date:1351465200000, end_date:1352070000000}]->(da1),
(db1)-[:DELIVERY_ROUTE {cost:3, start_date:1350255600000, end_date:1350860400000}]->(da4),

(db2)-[:DELIVERY_ROUTE {cost:2, start_date:1350860400000, end_date:1351465200000}]->(da4),
(db2)-[:DELIVERY_ROUTE {cost:6, start_date:1351465200000, end_date:1352070000000}]->(da4),
(db2)-[:DELIVERY_ROUTE {cost:5, start_date:1350255600000, end_date:1350860400000}]->(da3),
(db2)-[:DELIVERY_ROUTE {cost:6, start_date:1351465200000, end_date:1352070000000}]->(da2),

(db3)-[:DELIVERY_ROUTE {cost:5, start_date:1350860400000, end_date:1351465200000}]->(da3),
(db3)-[:DELIVERY_ROUTE {cost:5, start_date:1351465200000, end_date:1352070000000}]->(da3),
(db3)-[:DELIVERY_ROUTE {cost:3, start_date:1350255600000, end_date:1350860400000}]->(da2),
(db3)-[:DELIVERY_ROUTE {cost:2, start_date:1350860400000, end_date:1351465200000}]->(da2),

(da1)-[:DELIVERY_ROUTE {cost:3, start_date:1350255600000, end_date:1350860400000}]->(ds1),
(da1)-[:DELIVERY_ROUTE {cost:6, start_date:1351465200000, end_date:1352070000000}]->(ds1),
(da1)-[:DELIVERY_ROUTE {cost:3, start_date:1350255600000, end_date:1350860400000}]->(ds2),
(da1)-[:DELIVERY_ROUTE {cost:6, start_date:1351465200000, end_date:1352070000000}]->(ds2),
(da1)-[:DELIVERY_ROUTE {cost:3, start_date:1350255600000, end_date:1350860400000}]->(ds7),
(da1)-[:DELIVERY_ROUTE {cost:3, start_date:1350255600000, end_date:1350860400000}]->(ds8),

(da4)-[:DELIVERY_ROUTE {cost:2, start_date:1350860400000, end_date:1351465200000}]->(ds1),
(da4)-[:DELIVERY_ROUTE {cost:2, start_date:1350860400000, end_date:1351465200000}]->(ds2),
(da4)-[:DELIVERY_ROUTE {cost:2, start_date:1350860400000, end_date:1351465200000}]->(ds7),
(da4)-[:DELIVERY_ROUTE {cost:6, start_date:1351465200000, end_date:1352070000000}]->(ds7),
(da4)-[:DELIVERY_ROUTE {cost:2, start_date:1350860400000, end_date:1351465200000}]->(ds8),
(da4)-[:DELIVERY_ROUTE {cost:6, start_date:1351465200000, end_date:1352070000000}]->(ds8),
(da4)-[:DELIVERY_ROUTE {cost:3, start_date:1350255600000, end_date:1350860400000}]->(ds5),

(da3)-[:DELIVERY_ROUTE {cost:2, start_date:1350860400000, end_date:1351465200000}]->(ds5),
(da3)-[:DELIVERY_ROUTE {cost:6, start_date:1351465200000, end_date:1352070000000}]->(ds5),
(da3)-[:DELIVERY_ROUTE {cost:3, start_date:1350255600000, end_date:1350860400000}]->(ds6),
(da3)-[:DELIVERY_ROUTE {cost:2, start_date:1350860400000, end_date:1351465200000}]->(ds6),
(da3)-[:DELIVERY_ROUTE {cost:6, start_date:1351465200000, end_date:1352070000000}]->(ds6),
(da3)-[:DELIVERY_ROUTE {cost:6, start_date:1351465200000, end_date:1352070000000}]->(ds3),
(da3)-[:DELIVERY_ROUTE {cost:6, start_date:1351465200000, end_date:1352070000000}]->(ds4),

(da2)-[:DELIVERY_ROUTE {cost:3, start_date:1350255600000, end_date:1350860400000}]->(ds3),
(da2)-[:DELIVERY_ROUTE {cost:2, start_date:1350860400000, end_date:1351465200000}]->(ds3),
(da2)-[:DELIVERY_ROUTE {cost:3, start_date:1350255600000, end_date:1350860400000}]->(ds4),
(da2)-[:DELIVERY_ROUTE {cost:2, start_date:1350860400000, end_date:1351465200000}]->(ds4)
; 

以上建图语句中，只有社交网络模型（第一个）使用了索引，其他的没有加进去。neo4j的索引有一些坑，我用的是2.0.2版本，其中提供了2种索引，一种叫做index，一种叫做legacy index。两种的差异并不是一个取代另一个，而是有特定场景的。index可以用于match语句中，但是start语句中只可以支持legacy index，文档中有一段很不起眼的话“In general, the START clause is only really needed when using legacy indexes”。还有，index可以很方便的通过create index创建，但是legacy index就悲剧了，从文档的例子来看，只能通过写Java代码来创建。建立index应该是属于DDL语句，需要由DBA来操作，这样看来，index应该是neo4j需要加强的地方，把legacy index的很多特性逐步的迁移过来。

例子1，在社交网络模型场景中，列出所有对Java感兴趣的人名

MATCH (n:User), (i:Interest)
WHERE (n)-[:INTERESTED_IN]-> (i)
AND   i.name = 'Java'
RETURN n.name;

结果是Sarah，Arnold。

例子2，在权限控制模型中，列出所有Sarah无权管理的用户（人名）

MATCH paths=(admin:Admin)-[:MEMBER_OF]->()-[:DENIED]->()<-[:CHILD_OF*0..3]-(company)
      <-[:WORKS_FOR]-(employee)
WHERE admin.name = 'Sarah'
RETURN employee.name
UNION
MATCH (admin:Admin),paths=(company:Company)<-[:WORKS_FOR]-(employee:Employee)
where (NOT ((admin)-[:MEMBER_OF]->()-[:ALLOWED_INHERIT]->()
<-[:CHILD_OF*0..3]-(company)))
and   (NOT ((admin)-[:MEMBER_OF]->()-[:ALLOWED_DO_NOT_INHERIT]->(company)))
and   admin.name = 'Sarah'
RETURN distinct employee.name;

结果有7个人，不列举了。这个CQL的写法是，先列出Sarah被Denied的账户有哪些，然后还有Sarah不能访问到的账户有哪些，取并集即可。