1
"""
2
Basic ORM models of the QCFractal database
3

4
Note: avoid circular import here by including the name of the class
5
in relations and foreign keys are a string (see TaskQueueORM.base_result_obj)
6
"""
7

8 4
import datetime
9

10
# from sqlalchemy.ext.declarative import declarative_base
11 4
from sqlalchemy import (
12
    JSON,
13
    BigInteger,
14
    Boolean,
15
    Column,
16
    DateTime,
17
    Enum,
18
    Float,
19
    ForeignKey,
20
    Index,
21
    Integer,
22
    LargeBinary,
23
    String,
24
)
25 4
from sqlalchemy.ext.hybrid import hybrid_property
26 4
from sqlalchemy.orm import relationship
27 4
from sqlalchemy.sql import text
28

29 4
from qcfractal.interface.models.task_models import ManagerStatusEnum, PriorityEnum, TaskStatusEnum
30 4
from qcfractal.interface.models.common_models import CompressionEnum
31 4
from qcfractal.storage_sockets.models.sql_base import Base, MsgpackExt
32

33

34 4
class AccessLogORM(Base):
35 4
    __tablename__ = "access_log"
36

37 4
    id = Column(Integer, primary_key=True)
38 4
    access_date = Column(DateTime, default=datetime.datetime.utcnow)
39 4
    access_method = Column(String, nullable=False)
40 4
    access_type = Column(String, nullable=False)
41

42
    # Note: no performance difference between varchar and text in postgres
43
    # will mostly have a serialized JSON, but not stored as JSON for speed
44 4
    extra_params = Column(String)
45

46
    # user info
47 4
    ip_address = Column(String)
48 4
    user_agent = Column(String)
49

50
    # extra computed geo data
51 4
    city = Column(String)
52 4
    country = Column(String)
53 4
    country_code = Column(String)
54 4
    ip_lat = Column(String)
55 4
    ip_long = Column(String)
56 4
    postal_code = Column(String)
57 4
    subdivision = Column(String)
58

59 4
    __table_args__ = (Index("access_type", "access_date"),)
60

61

62 4
class ServerStatsLogORM(Base):
63 4
    __tablename__ = "server_stats_log"
64

65 4
    id = Column(Integer, primary_key=True)
66 4
    timestamp = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)
67

68
    # Raw counts
69 4
    collection_count = Column(Integer)
70 4
    molecule_count = Column(Integer)
71 4
    result_count = Column(Integer)
72 4
    kvstore_count = Column(Integer)
73 4
    access_count = Column(Integer)
74

75
    # States
76 4
    result_states = Column(JSON)
77

78
    # Database
79 4
    db_total_size = Column(BigInteger)
80 4
    db_table_size = Column(BigInteger)
81 4
    db_index_size = Column(BigInteger)
82 4
    db_table_information = Column(JSON)
83

84 4
    __table_args__ = (Index("ix_server_stats_log_timestamp", "timestamp"),)
85

86

87 4
class VersionsORM(Base):
88 4
    __tablename__ = "versions"
89

90 4
    id = Column(Integer, primary_key=True)
91 4
    created_on = Column(DateTime, default=datetime.datetime.utcnow)
92 4
    elemental_version = Column(String, nullable=False)
93 4
    fractal_version = Column(String, nullable=False)
94 4
    engine_version = Column(String)
95

96

97 4
class KVStoreORM(Base):
98
    """TODO: rename to """
99

100 4
    __tablename__ = "kv_store"
101

102 4
    id = Column(Integer, primary_key=True)
103 4
    compression = Column(Enum(CompressionEnum), nullable=True)
104 4
    compression_level = Column(Integer, nullable=True)
105 4
    value = Column(JSON, nullable=True)
106 4
    data = Column(LargeBinary, nullable=True)
107

108

109 4
class MoleculeORM(Base):
110
    """
111
    The molecule DB collection is managed by pymongo, so far
112
    """
113

114 4
    __tablename__ = "molecule"
115

116 4
    id = Column(Integer, primary_key=True)
117 4
    molecular_formula = Column(String)
118 4
    molecule_hash = Column(String)
119

120
    # Required data
121 4
    schema_name = Column(String)
122 4
    schema_version = Column(Integer, default=2)
123 4
    symbols = Column(MsgpackExt, nullable=False)
124 4
    geometry = Column(MsgpackExt, nullable=False)
125

126
    # Molecule data
127 4
    name = Column(String, default="")
128 4
    identifiers = Column(JSON)
129 4
    comment = Column(String)
130 4
    molecular_charge = Column(Float, default=0)
131 4
    molecular_multiplicity = Column(Integer, default=1)
132

133
    # Atom data
134 4
    masses = Column(MsgpackExt)
135 4
    real = Column(MsgpackExt)
136 4
    atom_labels = Column(MsgpackExt)
137 4
    atomic_numbers = Column(MsgpackExt)
138 4
    mass_numbers = Column(MsgpackExt)
139

140
    # Fragment and connection data
141 4
    connectivity = Column(JSON)
142 4
    fragments = Column(MsgpackExt)
143 4
    fragment_charges = Column(JSON)  # Column(ARRAY(Float))
144 4
    fragment_multiplicities = Column(JSON)  # Column(ARRAY(Integer))
145

146
    # Orientation
147 4
    fix_com = Column(Boolean, default=False)
148 4
    fix_orientation = Column(Boolean, default=False)
149 4
    fix_symmetry = Column(String)
150

151
    # Extra
152 4
    provenance = Column(JSON)
153 4
    extras = Column(JSON)
154

155
    # def __str__(self):
156
    #     return str(self.id)
157

158 4
    __table_args__ = (
159
        Index("ix_molecule_hash", "molecule_hash", unique=False),  # dafault index is B-tree
160
        # TODO: no index on molecule_formula
161
    )
162

163
    # meta = {
164
    #
165
    #     'indexes': [
166
    #         {
167
    #             'fields': ('molecule_hash', ),
168
    #             'unique': False
169
    #         },  # should almost be unique
170
    #         {
171
    #             'fields': ('molecular_formula', ),
172
    #             'unique': False
173
    #         }
174
    #     ]
175
    # }
176

177

178
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
179

180

181 4
class KeywordsORM(Base):
182
    """
183
    KeywordsORM are unique for a specific program and name
184
    """
185

186 4
    __tablename__ = "keywords"
187

188 4
    id = Column(Integer, primary_key=True)
189 4
    hash_index = Column(String, nullable=False)
190 4
    values = Column(JSON)
191

192 4
    lowercase = Column(Boolean, default=True)
193 4
    exact_floats = Column(Boolean, default=False)
194 4
    comments = Column(String)
195

196 4
    __table_args__ = (Index("ix_keywords_hash_index", "hash_index", unique=True),)
197
    # meta = {'indexes': [{'fields': ('hash_index', ), 'unique': True}]}
198

199

200
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
201

202

203 4
class TaskQueueORM(Base):
204
    """A queue of tasks corresponding to a procedure
205

206
    Notes: don't sort query results without having the index sorted
207
           will impact the performance
208
    """
209

210 4
    __tablename__ = "task_queue"
211

212 4
    id = Column(Integer, primary_key=True)
213

214 4
    spec = Column(MsgpackExt, nullable=False)
215

216
    # others
217 4
    tag = Column(String, default=None)
218 4
    parser = Column(String, default="")
219 4
    program = Column(String)
220 4
    procedure = Column(String)
221 4
    status = Column(Enum(TaskStatusEnum), default=TaskStatusEnum.waiting)
222 4
    priority = Column(Integer, default=int(PriorityEnum.NORMAL))
223 4
    manager = Column(String, ForeignKey("queue_manager.name", ondelete="SET NULL"), default=None)
224 4
    error = Column(String)  # TODO: tobe removed - should be in results
225

226 4
    created_on = Column(DateTime, default=datetime.datetime.utcnow)
227 4
    modified_on = Column(DateTime, default=datetime.datetime.utcnow)
228

229
    # TODO: for back-compatibility with mongo, tobe removed
230 4
    @hybrid_property
231
    def base_result(self):
232 4
        return self.base_result_id
233

234 4
    @base_result.setter
235
    def base_result(self, val):
236 4
        self.base_result_id = int(val)
237

238
    # can reference ResultORMs or any ProcedureORM
239 4
    base_result_id = Column(Integer, ForeignKey("base_result.id", ondelete="cascade"), unique=True)
240 4
    base_result_obj = relationship("BaseResultORM", lazy="select")  # or lazy='joined'
241

242
    # An important special case is ORDER BY in combination with LIMIT n: an
243
    # explicit sort will have to process all the data to identify the first n
244
    # rows, but if there is an index matching the ORDER BY, the first n rows
245
    # can be retrieved directly, without scanning the remainder at all.
246 4
    __table_args__ = (
247
        Index("ix_task_queue_created_on", "created_on"),
248
        Index("ix_task_queue_keys", "status", "program", "procedure", "tag"),
249
        Index("ix_task_queue_manager", "manager"),
250
        Index("ix_task_queue_base_result_id", "base_result_id"),
251
        Index("ix_task_waiting_sort", text("priority desc,  created_on")),
252
    )
253

254

255
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
256

257

258 4
class ServiceQueueORM(Base):
259

260 4
    __tablename__ = "service_queue"
261

262 4
    id = Column(Integer, primary_key=True)
263

264 4
    status = Column(Enum(TaskStatusEnum), default=TaskStatusEnum.waiting)
265 4
    tag = Column(String, default=None)
266 4
    hash_index = Column(String, nullable=False)
267

268 4
    procedure_id = Column(Integer, ForeignKey("base_result.id"), unique=True)
269 4
    procedure_obj = relationship("BaseResultORM", lazy="joined")
270

271 4
    priority = Column(Integer, default=int(PriorityEnum.NORMAL))
272 4
    created_on = Column(DateTime, default=datetime.datetime.utcnow)
273 4
    modified_on = Column(DateTime, default=datetime.datetime.utcnow)
274

275 4
    extra = Column(MsgpackExt)
276

277 4
    __table_args__ = (
278
        Index("ix_service_queue_status", "status"),
279
        Index("ix_service_queue_priority", "priority"),
280
        Index("ix_service_queue_modified_on", "modified_on"),
281
        Index("ix_service_queue_status_tag_hash", "status", "tag"),
282
        Index("ix_service_queue_hash_index", "hash_index"),
283
    )
284

285

286
# ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
287

288

289 4
class UserORM(Base):
290

291 4
    __tablename__ = "user"
292

293 4
    id = Column(Integer, primary_key=True)
294

295 4
    username = Column(String, nullable=False, unique=True)  # indexed and unique
296 4
    password = Column(LargeBinary, nullable=False)
297 4
    permissions = Column(JSON)  # Column(ARRAY(String))
298

299

300 4
class QueueManagerLogORM(Base):
301

302 4
    __tablename__ = "queue_manager_logs"
303

304 4
    id = Column(Integer, primary_key=True)
305 4
    manager_id = Column(Integer, ForeignKey("queue_manager.id"), nullable=False)
306

307 4
    timestamp = Column(DateTime, default=datetime.datetime.utcnow, nullable=False)
308

309 4
    completed = Column(Integer, nullable=True)
310 4
    submitted = Column(Integer, nullable=True)
311 4
    failures = Column(Integer, nullable=True)
312

313 4
    total_worker_walltime = Column(Float, nullable=True)
314 4
    total_task_walltime = Column(Float, nullable=True)
315 4
    active_tasks = Column(Integer, nullable=True)
316 4
    active_cores = Column(Integer, nullable=True)
317 4
    active_memory = Column(Float, nullable=True)
318

319 4
    __table_args__ = (Index("ix_queue_manager_log_timestamp", "timestamp"),)
320

321

322 4
class QueueManagerORM(Base):
323
    """"""
324

325 4
    __tablename__ = "queue_manager"
326

327 4
    id = Column(Integer, primary_key=True)
328

329 4
    name = Column(String, unique=True)
330 4
    cluster = Column(String)
331 4
    hostname = Column(String)
332 4
    username = Column(String)
333 4
    uuid = Column(String)
334 4
    tag = Column(String)
335

336
    # Count at current time
337 4
    completed = Column(Integer, default=0)
338 4
    submitted = Column(Integer, default=0)
339 4
    failures = Column(Integer, default=0)
340 4
    returned = Column(Integer, default=0)
341

342 4
    total_worker_walltime = Column(Float, nullable=True)
343 4
    total_task_walltime = Column(Float, nullable=True)
344 4
    active_tasks = Column(Integer, nullable=True)
345 4
    active_cores = Column(Integer, nullable=True)
346 4
    active_memory = Column(Float, nullable=True)
347

348
    # Adapter Information
349 4
    configuration = Column(JSON, nullable=True)
350

351 4
    status = Column(Enum(ManagerStatusEnum), default=ManagerStatusEnum.inactive)
352

353 4
    created_on = Column(DateTime, default=datetime.datetime.utcnow)
354 4
    modified_on = Column(DateTime, default=datetime.datetime.utcnow)
355

356 4
    qcengine_version = Column(String)
357 4
    manager_version = Column(String)
358 4
    programs = Column(JSON)
359 4
    procedures = Column(JSON)
360

361 4
    __table_args__ = (Index("ix_queue_manager_status", "status"), Index("ix_queue_manager_modified_on", "modified_on"))

Read our documentation on viewing source code .

Loading