6 |
6 |
7 import datetime |
7 import datetime |
8 import json |
8 import json |
9 import uuid |
9 import uuid |
10 |
10 |
11 def get_table_create_stmt(): |
11 from sqlalchemy import Column, Integer, String, DateTime, Table, Index, text |
12 return ( |
12 from sqlalchemy.sql import func |
13 "CREATE TABLE IF NOT EXISTS annotations ( " |
13 from sqlalchemy.dialects.postgresql import UUID, JSON |
14 "id serial PRIMARY KEY, " |
14 |
15 "uuid uuid UNIQUE, " |
15 from database import Base, engine |
16 "created timestamp default (now() at time zone 'utc') NOT NULL, " |
16 |
17 "ts timestamptz NOT NULL, " |
17 #def get_table_create_stmt(): |
18 "event varchar(255) NOT NULL, " |
18 # return ( |
19 "channel varchar(255) NOT NULL, " |
19 # "CREATE TABLE IF NOT EXISTS annotations ( " |
20 "content json);" |
20 # "id serial PRIMARY KEY, " |
21 ) |
21 # "uuid uuid UNIQUE, " |
|
22 # "created timestamp default (now() at time zone 'utc') NOT NULL, " |
|
23 # "ts timestamptz NOT NULL, " |
|
24 # "event varchar(255) NOT NULL, " |
|
25 # "channel varchar(255) NOT NULL, " |
|
26 # "content json);" |
|
27 # ) |
|
28 |
|
29 class Annotation(Base): |
|
30 __tablename__ = 'annotations' |
|
31 |
|
32 id = Column(Integer, primary_key=True, nullable=False) |
|
33 uuid = Column(UUID, unique=True, nullable=False) |
|
34 created = Column(DateTime, nullable=False, server_default=text("(now() at time zone 'utc')") ) |
|
35 ts = Column(DateTime(timezone=True), nullable=False) |
|
36 event = Column(String(255), nullable=False) |
|
37 channel = Column(String(255), nullable=False) |
|
38 content = Column(JSON) |
|
39 |
|
40 Index('idx_event', Annotation.event) |
|
41 Index('idx_channel', Annotation.channel) |
|
42 Index('idx_ts', Annotation.ts) |
|
43 |
22 |
44 |
23 def insert_annot_async(params, conn): |
45 def insert_annot_async(params, conn): |
24 |
46 |
25 content = params.get('content', {}) |
47 content = params.get('content', {}) |
26 if not isinstance(content, basestring): |
48 if not isinstance(content, basestring): |
29 params['uuid'] = uuid.uuid4() |
51 params['uuid'] = uuid.uuid4() |
30 |
52 |
31 if 'ts' not in params: |
53 if 'ts' not in params: |
32 params['ts'] = datetime.utcnow() |
54 params['ts'] = datetime.utcnow() |
33 |
55 |
34 defer = conn.runOperation("INSERT INTO annotations (uuid, ts, event, channel, content) VALUES (%(uuid)s, %(ts)s, %(event)s, %(channel)s, %(content)s)", params) |
56 stmt = Annotation.__table__.insert().values(**params).compile(engine) |
35 defer.addCallback(lambda _: params) |
57 |
|
58 defer = conn.runOperation(stmt.string, stmt.params) |
|
59 defer.addCallback(lambda _: stmt.params) |
36 |
60 |
37 return defer |
61 return defer |