6 |
6 |
7 import datetime |
7 import datetime |
8 import json |
8 import json |
9 import uuid |
9 import uuid |
10 |
10 |
11 from sqlalchemy import Column, Integer, String, DateTime, Table, Index, text |
11 from sqlalchemy import Column, Integer, String, DateTime, Text, Table, Index, text, ForeignKey |
|
12 from sqlalchemy.orm import relationship, backref |
12 from sqlalchemy.sql import func |
13 from sqlalchemy.sql import func |
13 from sqlalchemy.dialects.postgresql import UUID, JSON |
14 from sqlalchemy.dialects.postgresql import UUID, JSON |
14 |
15 |
15 from database import Base, engine |
16 from database import Base, engine |
16 |
17 |
17 #def get_table_create_stmt(): |
|
18 # return ( |
|
19 # "CREATE TABLE IF NOT EXISTS annotations ( " |
|
20 # "id serial PRIMARY KEY, " |
|
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 |
18 |
29 class Annotation(Base): |
19 class Annotation(Base): |
30 __tablename__ = 'annotations' |
20 __tablename__ = 'annotation' |
31 |
21 |
32 id = Column(Integer, primary_key=True, nullable=False) |
22 id = Column(Integer, primary_key=True, nullable=False) |
33 uuid = Column(UUID, unique=True, nullable=False) |
23 uuid = Column(UUID, unique=True, nullable=False) |
34 created = Column(DateTime, nullable=False, server_default=text("(now() at time zone 'utc')") ) |
24 created = Column(DateTime, nullable=False, server_default=text("(now() at time zone 'utc')") ) |
35 ts = Column(DateTime(timezone=True), nullable=False) |
25 ts = Column(DateTime(timezone=True), nullable=False) |
36 event = Column(String(255), nullable=False) |
26 event_code = Column(String(255), ForeignKey('event.code'), nullable=False) |
37 channel = Column(String(255), nullable=False) |
27 channel = Column(String(255), nullable=False) |
38 content = Column(JSON) |
28 content = Column(JSON) |
39 |
29 |
40 Index('idx_event', Annotation.event) |
30 Index('idx_annotation_event', Annotation.event_code) |
41 Index('idx_channel', Annotation.channel) |
31 Index('idx_annotation_channel', Annotation.channel) |
42 Index('idx_ts', Annotation.ts) |
32 Index('idx_annotation_ts', Annotation.ts) |
43 |
33 |
44 |
34 |
45 def insert_annot_async(params, conn): |
35 def insert_annot_async(params, conn): |
46 |
36 |
47 content = params.get('content', {}) |
37 content = params.get('content', {}) |
57 |
47 |
58 defer = conn.runOperation(stmt.string, stmt.params) |
48 defer = conn.runOperation(stmt.string, stmt.params) |
59 defer.addCallback(lambda _: stmt.params) |
49 defer.addCallback(lambda _: stmt.params) |
60 |
50 |
61 return defer |
51 return defer |
|
52 |
|
53 |
|
54 class Event(Base): |
|
55 __tablename__ = 'event' |
|
56 |
|
57 id = Column(Integer, primary_key=True, nullable=False) |
|
58 code = Column(String(255), unique=True, nullable=False) |
|
59 label = Column(String(2048), nullable=False) |
|
60 description = Column(Text(), nullable=True) |
|
61 |
|
62 sessions = relationship("EventSession", order_by="EventSession.order", backref="event") |
|
63 |
|
64 def __unicode__(self): |
|
65 return self.code |
|
66 |
|
67 Index('idx_event_code', Event.code) |
|
68 |
|
69 class EventSession(Base): |
|
70 __tablename__ = 'event_session' |
|
71 |
|
72 id = Column(Integer, primary_key=True, nullable=False) |
|
73 event_id = Column(Integer, ForeignKey(Event.id), nullable=False) |
|
74 project_id = Column(String(2048), nullable=True) |
|
75 order = Column(Integer, nullable=False, default=0) |
|
76 categories_json = Column(JSON, nullable=True) |
|
77 |
|
78 |
|
79 Index('idx_event_session_order', EventSession.order) |