class Course(Base):
__tablename__ = "course"
courseDescriptor = Column(VARCHAR(256), primary_key=True)
courseName = Column(VARCHAR(200), nullable=False)
courseId = Column(CHAR(20), nullable=False)
credit = Column(NUMERIC(3, 1), nullable=False)
semester = Column(CHAR(10), nullable=False)
startTime = Column(DATE, nullable=False)
endTime = Column(DATE, nullable=False)
courseStart = Column(TIME)
courseEnd = Column(TIME)
hotIndex = Column(INTEGER, nullable=False)
Image = Column(VARCHAR(256), nullable=False)
description = Column(VARCHAR(1000), nullable=False)
def __init__(self, course_descriptor, course_id, credit, semester,
start_time, end_time, course_start, course_end, hot_index,
image, description, cname):
self.courseDescriptor = course_descriptor
self.courseId = course_id
self.credit = credit
self.semester = semester
self.startTime = start_time
self.endTime = end_time
self.courseStart = course_start
self.courseEnd = course_end
self.hotIndex = hot_index
self.Image = image
self.description = description
self.courseName = cname
class HandInHomework(Base):
__tablename__ = "handInHomework"
submitUserName = Column(VARCHAR(100),
ForeignKey('puser.userName'),
primary_key=True,
nullable=False)
gradeUserName = Column(VARCHAR(100),
ForeignKey('puser.userName'),
nullable=True)
grades = Column(NUMERIC(5, 2))
handInTime = Column(DATETIME, primary_key=True)
fileName = Column(VARCHAR(256), nullable=False)
file = Column(VARCHAR(256), nullable=False)
courseDescriptor = Column(VARCHAR(256),
ForeignKey('course.courseDescriptor'),
nullable=False)
homeworkTitle = Column(VARCHAR(100),
ForeignKey('homework.homeworkTitle'),
nullable=False)
def __init__(self, susername, gusername, grade, hdintime, fil, descriptor,
hwtitle, filen):
self.submitUserName = susername
self.gradeUserName = gusername
self.grades = grade
self.handInTime = hdintime
self.file = fil
self.courseDescriptor = descriptor
self.homeworkTitle = hwtitle
self.fileName = filen
class DailyAggregate(Base):
__tablename__ = "daily_aggregates"
transaction_date = Column(BIGINT(unsigned=True),
nullable=False,
primary_key=True)
difficulty = Column(NUMERIC(32, unsigned=False), nullable=False)
transactions = Column(INTEGER(unsigned=True), nullable=False)
block = Column(INTEGER(unsigned=True), nullable=False)
class TracksTable(TimestampMixin, BASE):
"""
ORM class for the Tracks table
:ivar track_id: Primary key of Track Table
:vartype track_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar name: Name of Track
:vartype name: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar composer: Name of composer
:vartype composer: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar milliseconds: Length of track in milliseconds
:vartype milliseconds: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar bytes: size of tracks in bytes
:vartype bytes: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar unit_price: unit price of track
:vartype unit_price: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar album_id: Foregin key representing the album id this track belongs to
:vartype album_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar media_type_id: Foregin key representing the mediatype this track belongs to
:vartype media_type_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar genre_id: Foregin key representing the GenreId this track belongs to
:vartype genre_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
"""
__tablename__ = 'track'
track_id = Column(INTEGER(unsigned=True), name="TrackId", primary_key=True, autoincrement=True, nullable=False)
# NVARCHAR types
name = Column(NVARCHAR(200), name="Name", nullable=False)
composer = Column(NVARCHAR(220), name="Composer")
# Numeric and Integer Types
milliseconds = Column(INTEGER(unsigned=True), name="Milliseconds", nullable=False)
bytes = Column(INTEGER(unsigned=True), name="Bytes")
unit_price = Column(NUMERIC(10, 2), name="UnitPrice", nullable=False)
# Foreign Key Columns
album_id = Column(INTEGER(unsigned=True), ForeignKey('album.AlbumId', onupdate="NO ACTION", ondelete="NO ACTION"),
name="AlbumId", nullable=False, index=True)
media_type_id = Column(INTEGER(unsigned=True), ForeignKey('mediatype.MediaTypeId', onupdate="NO ACTION",
ondelete="NO ACTION"), name="MediaTypeId",
nullable=False, index=True)
genre_id = Column(INTEGER(unsigned=True), ForeignKey('genre.GenreId', onupdate="NO ACTION", ondelete="NO ACTION"),
name="GenreId")
class Balance(Base):
__tablename__ = "balances"
address_id = Column(INTEGER(unsigned=True),
ForeignKey('addresses.id'),
primary_key=True)
address = relationship("Address", foreign_keys=[address_id])
balance_date = Column(BIGINT(unsigned=True),
nullable=False,
primary_key=True)
earned = Column(Boolean(), nullable=False, primary_key=True)
delta = Column(NUMERIC(32, unsigned=False), nullable=False)
class Participation(Base):
__tablename__ = 'Participation'
studentUsername = Column(VARCHAR(100), ForeignKey('PUser.userName'), primary_key=True)
courseDescriptor = Column(VARCHAR(256), ForeignKey('Course.courseDescriptor'), primary_key=True)
finalGrade = Column(NUMERIC(5, 2))
signInTime = Column(DATETIME(fsp=2))
def __init__(self, sname, cd, fg, sit):
self.studentUsername = sname
self.courseDescriptor = cd
self.finalGrade = fg
self.signInTime = sit
class InvoiceLineTable(TimestampMixin, BASE):
"""
ORM class for the InvoiceLine Table
:ivar invoice_line_id: Primary key of InvoiceLine Table
:vartype invoice_line_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar unit_price: unit price of tracks
:vartype unit_price: class:`sqlalchemy.dialects.mysql.types.NUMERIC`
:ivar quantity: quantity of purchase of given track
:vartype quantity: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar invoice_id: Foreign key representing the invoice id
:vartype invoice_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar track_id: Foreign key representing the track id involved
:vartype track_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar invoice: The invoice which was involved in this invoice line
:vartype invoice: :class:`mservice.database_model.ORMClasses.InvoiceTable`
:ivar track: The track which was involved in this invoice line
:vartype track: :class:`mservice.database_model.ORMClasses.TracksTable`
"""
__tablename__ = 'invoiceline'
# Primary key
invoice_line_id = Column(INTEGER(unsigned=True), name="InvoiceLineId", primary_key=True, autoincrement=True,
nullable=False)
# Numeric & Int type
unit_price = Column(NUMERIC(10, 2), name="UnitPrice", nullable=False)
quantity = Column(INTEGER(unsigned=True), name="Quantity", nullable=False)
# Foreign Key
invoice_id = Column(INTEGER(unsigned=True), ForeignKey("invoice.InvoiceId", onupdate="NO ACTION",
ondelete="NO ACTION"), name="InvoiceId",
nullable=False, index=True)
track_id = Column(INTEGER(unsigned=True), ForeignKey("track.TrackId", onupdate="NO ACTION", ondelete="NO ACTION"),
name="TrackId", nullable=False, index=True)
# Relationships
invoice = relationship("InvoiceTable", backref=backref("track_associations", cascade="all, delete, delete-orphan"))
track = relationship("TracksTable", backref=backref("invoice_associations", cascade="all, delete, delete-orphan"))
class Data(BASE):
"""Class defining the iset_data table of the database."""
__tablename__ = 'iset_data'
__table_args__ = (PrimaryKeyConstraint('idx_datapoint', 'timestamp'), {
'mysql_engine': 'InnoDB'
})
idx_datapoint = Column(BIGINT(unsigned=True),
ForeignKey('iset_datapoint.idx_datapoint'),
nullable=False,
server_default='1')
timestamp = Column(BIGINT(unsigned=True), nullable=False, default='1')
value = Column(NUMERIC(40, 10), default=None)
class Data(BASE):
"""Class defining the pt_data table of the database."""
__tablename__ = 'pt_data'
__table_args__ = (PrimaryKeyConstraint('idx_datapoint', 'timestamp'), {
'mysql_engine': 'InnoDB'
})
idx_datapoint = Column(BIGINT(unsigned=True),
ForeignKey('pt_datapoint.idx_datapoint'),
index=True,
nullable=False,
server_default='1')
timestamp = Column(BIGINT(unsigned=True), nullable=False, default='1')
value = Column(NUMERIC(40, 10), nullable=False, default='1')
# Use cascade='delete,all' to propagate the deletion of a
# DataPoint onto its Data
datapoint = relationship(DataPoint,
backref=backref('data_checksum',
uselist=True,
cascade='delete,all'))
class InvoiceTable(TimestampMixin, BASE):
"""
ORM class for the Invoice Table
:ivar invoice_id: Primary key of Invoice Table
:vartype invoice_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar billing_address: BillingAddress of invoice
:vartype billing_address: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar billing_city: BillingCity of invoice
:vartype billing_city: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar billing_state: BillingState of invoice
:vartype billing_state: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar billing_country: BillingCountry of invoice
:vartype billing_country: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar billing_postal_code: BillingPostalCode of invoice
:vartype billing_postal_code: class:`sqlalchemy.dialects.mysql.types.NVARCHAR`
:ivar invoice_date: Date of invoice
:vartype invoice_date: class:`sqlalchemy.dialects.mysql.types.DATETIME`
:ivar total: Total cost of invoice
:vartype total: class:`sqlalchemy.dialects.mysql.types.NUMERIC`
:ivar customer_id: Foreign key representing the customer involved in this invoive
:vartype customer_id: class:`sqlalchemy.dialects.mysql.types.INTEGER`
:ivar purchased_tracks: List of tracks involved in this invoice
:vartype purchased_tracks: list
"""
__tablename__ = 'invoice'
# Primary Key
invoice_id = Column(INTEGER(unsigned=True), name="InvoiceId", primary_key=True, autoincrement=True, nullable=False)
# NVARCHAR data type
billing_address = Column(NVARCHAR(70), name="BillingAddress")
billing_city = Column(NVARCHAR(40), name="BillingCity")
billing_state = Column(NVARCHAR(40), name="BillingState")
billing_country = Column(NVARCHAR(40), name="BillingCountry")
billing_postal_code = Column(NVARCHAR(10), name="BillingPostalCode")
# DATETIME data type
invoice_date = Column(DATETIME, name="InvoiceDate", nullable=False)
# Numeric Data Type
total = Column(NUMERIC(10, 2), name="Total", nullable=False)
# Foreign Key
customer_id = Column(INTEGER(unsigned=True), ForeignKey("customer.CustomerId", onupdate="NO ACTION",
ondelete="NO ACTION"), name="CustomerId",
nullable=False, index=True)
# Relationships
purchased_tracks = relationship("TracksTable", backref=backref("invoices"), secondary="invoiceline")