class Manage(Base):
__tablename__ = "manage"
userName = Column(VARCHAR(100),
ForeignKey('puser.userName'),
primary_key=True,
nullable=False)
courseDescriptor = Column(VARCHAR(256),
ForeignKey('course.courseDescriptor'),
primary_key=True,
nullable=False)
type = Column(BIT(1), nullable=False)
newNotice = Column(BIT(1), nullable=False)
editNotice = Column(BIT(1), nullable=False)
deleteNotice = Column(BIT(1), nullable=False)
newHomework = Column(BIT(1), nullable=False)
editHomework = Column(BIT(1), nullable=False)
deleteHomework = Column(BIT(1), nullable=False)
gradeHomework = Column(BIT(1), nullable=False)
def __init__(self, username, descriptor, type, newnotice, editnotice,
deletenotice, newhw, edithw, delhw, grahw):
self.userName = username
self.courseDescriptor = descriptor
self.type = type
self.newNotice = newnotice
self.editNotice = editnotice
self.deleteNotice = deletenotice
self.newHomework = newhw
self.editHomework = edithw
self.deleteHomework = delhw
self.gradeHomework = grahw
class Organization(db.Model):
__tablename__ = 'organization'
id = db.Column('id', db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.CHAR(30), nullable=False)
git = db.Column(db.CHAR(15), unique=True, nullable=False)
repos = db.Column(db.Integer, nullable=False, default=0)
teams = db.Column(db.Integer, nullable=False, default=0)
gists = db.Column(db.Integer, nullable=False, default=0)
members = db.Column(db.Integer, nullable=False, default=0)
plan = db.Column(db.Integer, default=1, nullable=False)
balance = db.Column(db.Float, default=0.0, nullable=False)
location = db.Column(db.String(200))
create = db.Column(db.DateTime, default=datetime.now)
cost = db.Column(db.Integer, nullable=False, default=0)
allow = db.Column(BIT(1), nullable=False, default=0)
def __init__(self, name, git, members=0):
self.name = name
self.git = git
self.members = members
def set_args(self, **kwargs):
for k, v in kwargs.iteritems():
setattr(self, k, v)
db.session.add(self)
db.session.commit()
def add_balance(self, balance):
raise NotImplementedError
class ServiceEndpoint(Base):
__tablename__ = "service_endpoint"
row_id = Column("row_id", Integer, primary_key=True, autoincrement=True)
service_row_id = Column("service_row_id",
Integer,
ForeignKey("service.row_id", ondelete="CASCADE"),
nullable=False)
org_id = Column("org_id", VARCHAR(128), nullable=False)
service_id = Column("service_id", VARCHAR(128), nullable=False)
group_id = Column("group_id", VARCHAR(256), nullable=False)
endpoint = Column("endpoint", VARCHAR(256), default=null)
is_available = Column("is_available", BIT(1), default=null)
last_check_timestamp = Column("last_check_timestamp",
TIMESTAMP(timezone=False),
nullable=True,
default=null)
next_check_timestamp = Column("next_check_timestamp",
TIMESTAMP(timezone=False),
nullable=True,
default=datetime.utcnow())
failed_status_count = Column("failed_status_count", Integer, default=1)
row_created = Column("row_created",
TIMESTAMP(timezone=False),
nullable=True,
default=datetime.utcnow())
row_updated = Column("row_updated",
TIMESTAMP(timezone=False),
nullable=True,
default=datetime.utcnow())
Index("ServiceFK_idx", service_row_id)
class Tpaymoney(Base):
__tablename__ = 'tpaymoney'
ID = db.Column(db.INTEGER, nullable=False)
PayType = db.Column(db.INTEGER, nullable=False)
PayMoney = db.Column(db.INTEGER, nullable=False)
PayGameMoney = db.Column(db.INTEGER, nullable=False)
SortNum = db.Column(db.INTEGER, nullable=False, server_default=Text("0"))
IsEnable = db.Column(BIT(1), nullable=False)
PartnerID = db.Column(db.INTEGER, primary_key=True)
class Wordform(Base):
__tablename__ = 'wordforms'
wordform_id = Column(BIGINT(20), primary_key=True)
wordform = Column(VARCHAR(255), unique=True)
has_analysis = Column(BIT(1))
wordform_lowercase = Column(VARCHAR(255), nullable=False, index=True)
def __init__(self, wordform):
self.wordform = wordform
self.has_analysis = False
self.wordform_lowercase = wordform.lower()
class Reference(Base):
__tablename__ = "Reference"
referenceName = Column(VARCHAR(100), nullable=False)
file = Column(VARCHAR(256), nullable=False)
upLoadTime = Column(DATETIME(fsp=2), primary_key=True)
downloadable = Column(BIT(1), nullable=False)
courseDescriptor = Column(VARCHAR(256), ForeignKey('Course.courseDescriptor'), primary_key=True, nullable=False)
def __init__(self, rename, uptime, candownload, descritpor):
self.referenceName = rename
self.upLoadTime = uptime
self.downloadable = candownload
self.courseDescriptor = descritpor
class Mailmsg(Base):
__tablename__ = 'mailmsg'
idMail = Column(INTEGER(11), primary_key=True)
idFrom = Column(ForeignKey(u'user.idUser'), index=True)
idTo = Column(ForeignKey(u'user.idUser'), nullable=False, index=True)
time = Column(DateTime, nullable=False)
content = Column(String(4096), nullable=False)
# da li je poruka procitana
readFlag = Column(BIT(1), nullable=False)
user = relationship(u'User', primaryjoin='Mailmsg.idFrom == User.idUser')
user1 = relationship(u'User', primaryjoin='Mailmsg.idTo == User.idUser')
class CertificationDetail(db.Model):
__bind_key__ = 'hrms'
__tablename__ = 'certification_details'
ID = db.Column(db.BigInteger, primary_key=True)
CERTIFICATION_DATE = db.Column(db.String(500))
CERTFICATION_VERSION = db.Column(db.String(500))
PERCENTAGE = db.Column(db.String(500))
TRAINED_COURSE_NAME = db.Column(db.String(500))
INSTITUTION_NAME = db.Column(db.String(500))
INSTITUTION_LOCATION = db.Column(db.String(500))
COURSE_DURATION = db.Column(db.String(500))
CERTIFICATION_NO = db.Column(db.String(500))
CERTIFICATION_NAME = db.Column(db.String(500))
CERT_NAME_TYPE = db.Column(db.ForeignKey('certification_name_type.ID'), index=True)
CERT_TYPE = db.Column(db.ForeignKey('certification_type.ID'), index=True)
employeeId = db.Column(db.ForeignKey('employee.SID'), index=True)
IS_ENABLE = db.Column(BIT(1))
IS_SUCCESS = db.Column(BIT(1))
certification_name_type = db.relationship('CertificationNameType', primaryjoin='CertificationDetail.CERT_NAME_TYPE == CertificationNameType.ID', backref='certification_details')
certification_type = db.relationship('CertificationType', primaryjoin='CertificationDetail.CERT_TYPE == CertificationType.ID', backref='certification_details')
employee = db.relationship('Employee', primaryjoin='CertificationDetail.employeeId == Employee.SID', backref='certification_details')
class DBUser(db.Model):
__tablename__ = 'USERS'
USERNAME = db.Column(db.String(50), primary_key=True)
PASSWORD = db.Column(db.String(50), unique=False)
DESCRIPTION = db.Column(db.String(120), unique=False)
ENABLED = db.Column(BIT(1))
def __init__(self, username, password, description):
self.USERNAME = username
self.PASSWORD = password
self.DESCRIPTION = description
def __repr__(self):
return '<User %r>' % self.USERNAME
class EmployeeProjectDetail(db.Model):
__bind_key__ = 'hrms'
__tablename__ = 'employee_project_details'
ID = db.Column(db.BigInteger, primary_key=True)
PROJECT_NAME = db.Column(db.String(500))
PROJECT_DESCRIPTION = db.Column(db.String(500))
KEY_CONTRIBUTION = db.Column(db.String(500))
START_DATE = db.Column(db.String(50))
END_DATE = db.Column(db.String(50))
ROLE_ID = db.Column(db.ForeignKey('role.ID'), index=True)
employment_id = db.Column(db.ForeignKey('employment_history.ID'), index=True)
idx = db.Column(db.Integer)
CLIENT_NAME = db.Column(db.String(100))
EMPLOYEE_ID = db.Column(db.ForeignKey('employee.SID'), index=True)
EMPLOYMENT_HISTORY_ID = db.Column(db.ForeignKey('employment_history.ID'), index=True)
IS_ENABLE = db.Column(BIT(1))
IS_SUCCESS = db.Column(BIT(1))
employee = db.relationship('Employee', primaryjoin='EmployeeProjectDetail.EMPLOYEE_ID == Employee.SID', backref='employee_project_details')
employment_history = db.relationship('EmploymentHistory', primaryjoin='EmployeeProjectDetail.EMPLOYMENT_HISTORY_ID == EmploymentHistory.ID', backref='employmenthistory_employee_project_details')
role = db.relationship('Role', primaryjoin='EmployeeProjectDetail.ROLE_ID == Role.ID', backref='employee_project_details')
employment = db.relationship('EmploymentHistory', primaryjoin='EmployeeProjectDetail.employment_id == EmploymentHistory.ID', backref='employmenthistory_employee_project_details_0')
class Employee(db.Model):
__bind_key__ = 'hrms'
__tablename__ = 'employee'
SID = db.Column(db.BigInteger, primary_key=True)
EMPLOYEE_ID = db.Column(db.String(255))
FIRST_NAME = db.Column(db.String(255))
MIDDLE_NAME = db.Column(db.String(255))
LAST_NAME = db.Column(db.String(255))
PASSWORD = db.Column(db.String(255))
GENDER = db.Column(db.String(255))
DATA_OF_BIRTH = db.Column(db.Date)
DATE_OF_JOINING = db.Column(db.Date)
DESIGNATION = db.Column(db.String(255))
OFFICE_EMAIL_ID = db.Column(db.String(255))
PERSONAL_EMAIL_ID = db.Column(db.String(255))
ALTERNATE_EMAIL_ID = db.Column(db.String(255))
EMERGENCY_NO = db.Column(db.String(255))
TOTAL_EXPERIENCE = db.Column(db.String(255))
MOBILE_NO = db.Column(db.String(255))
OFFICE_NO = db.Column(db.String(255))
HOME_NO = db.Column(db.String(255))
PAN_NO = db.Column(db.String(255))
UAN_NO = db.Column(db.String(255))
PF_NO = db.Column(db.String(255))
NOTICE_PERIOD = db.Column(db.String(255))
UserId = db.Column(db.String(255))
Is_Save_OR_UPDATE = db.Column(BIT(1))
DEPARTMENT_ID = db.Column(db.ForeignKey('departmant.ID'), index=True)
DESIGNATION_ID = db.Column(db.ForeignKey('designation.ID'), index=True)
Manager_ID = db.Column(db.String(255))
User_Name = db.Column(db.String(255))
COUNTRY_ID = db.Column(db.ForeignKey('country.ID'), index=True)
STATE_ID = db.Column(db.ForeignKey('state.ID'), index=True)
Status = db.Column(db.String(255))
ROLE_OF_EMPLOYEE = db.Column(db.String(255))
Nationality = db.Column(db.String(255))
#Additional fields added
BLOOD_GROUP = db.Column(db.String(255))
BANK_ACCOUNT_NO = db.Column(db.String(255))
ENGINEERING_OR_NON_ENGINEERING = db.Column(db.String(255))
# TOTAL_RELEVANT_EXPERIENCE = db.Column(db.String(255))
# TOTAL_RELEVANT_EXPERIENCE_DATE = db.Column(db.String(255))
country = db.relationship('Country', primaryjoin='Employee.COUNTRY_ID == Country.ID', backref='employees')
departmant = db.relationship('Departmant', primaryjoin='Employee.DEPARTMENT_ID == Departmant.ID', backref='employees')
designation = db.relationship('Designation', primaryjoin='Employee.DESIGNATION_ID == Designation.ID', backref='employees')
state = db.relationship('State', primaryjoin='Employee.STATE_ID == State.ID', backref='employees')
class MdDay(Base):
__tablename__ = 'md_day'
stock = Column(CHAR(6), primary_key=True, nullable=False)
d_day = Column(Date, primary_key=True, nullable=False, index=True)
open = Column(Float)
high = Column(Float)
low = Column(Float)
close = Column(Float)
volume = Column(BIGINT(20))
amount = Column(BIGINT(20))
free_turn = Column(Float)
adj_factor = Column(Float)
is_suspension = Column(BIT(1))
is_maxupordown = Column(TINYINT(4))
is_st = Column(TINYINT(4), nullable=False, server_default=text("'0'"))
class Product(Base):
__tablename__ = 'products'
ProductID = Column(INTEGER(11), primary_key=True)
ProductName = Column(String(40), nullable=False, index=True)
SupplierID = Column(ForeignKey('suppliers.SupplierID'), index=True)
CategoryID = Column(ForeignKey('categories.CategoryID'), index=True)
QuantityPerUnit = Column(String(20))
UnitPrice = Column(DECIMAL(10, 4))
UnitsInStock = Column(SMALLINT(2), server_default=text("0"))
UnitsOnOrder = Column(SMALLINT(2), server_default=text("0"))
ReorderLevel = Column(SMALLINT(2), server_default=text("0"))
Discontinued = Column(BIT(1), nullable=False)
category = relationship('Category')
supplier = relationship('Supplier')
class Verify(db.Model):
__tablename__ = 'verify'
git = db.Column(db.CHAR(10), primary_key=True)
email = db.Column(db.String(200), primary_key=True)
stub = db.Column(db.CHAR(20), primary_key=True)
name = db.Column(db.CHAR(30), nullable=False)
admin = db.Column(BIT(1), nullable=False, default=0)
created = db.Column(db.DateTime, default=datetime.now)
def __init__(self, stub, email, name, git, admin=0):
self.stub = stub
self.email = email
self.name = name
self.git = git
self.admin = admin
def delete(self):
db.session.delete(self)
db.session.commit()
class Team(db.Model):
__tablename__ = 'team'
__table_args__ = (db.UniqueConstraint('oid', 'name',
name='uix_oid_name'), )
id = db.Column('id', db.Integer, primary_key=True, autoincrement=True)
name = db.Column(db.CHAR(30), nullable=False)
display = db.Column(db.CHAR(30), nullable=False)
oid = db.Column(db.Integer, nullable=False)
members = db.Column(db.Integer, nullable=False, default=0)
pic = db.Column(db.String(255), default='default.png')
repos = db.Column(db.Integer, default=0)
create = db.Column(db.DateTime, default=datetime.now)
private = db.Column(BIT(1), nullable=False, default=0)
def __init__(self, oid, name, display, private=0, members=0):
self.oid = oid
self.name = name
self.private = private
self.display = display
self.members = members
class TeamMembers(db.Model):
__tablename__ = 'team_members'
__table_args__ = (db.UniqueConstraint('tid', 'uid', name='uix_tid_uid'), )
id = db.Column('id', db.Integer, primary_key=True, autoincrement=True)
tid = db.Column(db.Integer, nullable=False)
uid = db.Column(db.Integer, nullable=False)
admin = db.Column(BIT(1), nullable=False, default=0)
join = db.Column(db.DateTime, default=datetime.now)
def __init__(self, tid, uid, admin=0):
self.tid = tid
self.uid = uid
self.admin = admin
def delete(self):
db.session.delete(self)
db.session.commit()
def set_as_admin(self):
self.admin = 1
class Addres(db.Model):
__bind_key__ = 'hrms'
__tablename__ = 'address'
ID = db.Column(db.BigInteger, primary_key=True)
LINE1 = db.Column(db.String(100))
LINE2 = db.Column(db.String(100))
LINE3 = db.Column(db.String(100))
COUNTRY_ID = db.Column(db.ForeignKey('country.ID'), index=True)
STATE_ID = db.Column(db.ForeignKey('state.ID'), index=True)
CITY = db.Column(db.String(100))
PINCODE = db.Column(db.BigInteger)
IS_ENABLE = db.Column(BIT(1))
ADDRESS_TYPE = db.Column(db.ForeignKey('address_type.ID'), index=True)
EMPLOYEE_ID = db.Column(db.ForeignKey('employee.SID'), index=True)
EFFECTIVE_DATE = db.Column(db.String(50))
TILL_DATE = db.Column(db.String(50))
address_type = db.relationship('AddressType', primaryjoin='Addres.ADDRESS_TYPE == AddressType.ID', backref='address')
country = db.relationship('Country', primaryjoin='Addres.COUNTRY_ID == Country.ID', backref='address')
employee = db.relationship('Employee', primaryjoin='Addres.EMPLOYEE_ID == Employee.SID', backref=backref('address', uselist=False))
state = db.relationship('State', primaryjoin='Addres.STATE_ID == State.ID', backref='address')
class EducationDetail(db.Model):
__bind_key__ = 'hrms'
__tablename__ = 'education_details'
ID = db.Column(db.BigInteger, primary_key=True)
QUALIFICATION_ID = db.Column(db.ForeignKey('qualification_type.ID'), nullable=False, index=True)
DEGREE_ID = db.Column(db.ForeignKey('degree_type.ID'), index=True)
COLLEGE_NAME = db.Column(db.String(500))
JOINING_DATE = db.Column(db.String(255))
END_DATE = db.Column(db.String(255))
PERCENTAGE = db.Column(db.String(50))
IS_ENABLE = db.Column(BIT(1))
GRADE = db.Column(db.String(50))
employeeId = db.Column(db.ForeignKey('employee.SID'), index=True)
SPECILIZATION = db.Column(db.String(500))
UNIVERSITY_NAME = db.Column(db.String(500))
UNIVERSITY_ID = db.Column(db.ForeignKey('university_type.ID'), index=True)
DegreeType = db.Column(db.String(500))
degree_type = db.relationship('DegreeType', primaryjoin='EducationDetail.DEGREE_ID == DegreeType.ID', backref='education_details')
qualification_type = db.relationship('QualificationType', primaryjoin='EducationDetail.QUALIFICATION_ID == QualificationType.ID', backref='education_details')
university_type = db.relationship('UniversityType', primaryjoin='EducationDetail.UNIVERSITY_ID == UniversityType.ID', backref='education_details')
employee = db.relationship('Employee', primaryjoin='EducationDetail.employeeId == Employee.SID', backref='education_details')
class Movie(db.Model):
__tablename__ = 'movies'
movie_id = Column(BIGINT(20), primary_key=True)
budget = Column(BIGINT(20))
homepage = Column(Text)
imdb_id = Column(Text)
original_language = Column(Text)
original_title = Column(Text)
overview = Column(Text)
popularity = Column(Float(asdecimal=True))
poster_path = Column(Text)
release_date = Column(DateTime)
revenue = Column(Float(asdecimal=True))
runtime = Column(Float(asdecimal=True))
status = Column(Text)
tagline = Column(Text)
title = Column(Text)
vote_average = Column(Float(asdecimal=True))
vote_count = Column(Float(asdecimal=True))
qualify = Column(BIT(1))
production_companys = relationship('ProductionCompany', secondary='production_companies_to_movies')
class EmployeeFamilyDetail(db.Model):
__bind_key__ = 'hrms'
__tablename__ = 'employee_family_details'
EMP_FAMILY_ID = db.Column(db.BigInteger, primary_key=True)
FIRST_NAME = db.Column(db.String(255))
MIDDLE_NAME = db.Column(db.String(255))
LAST_NAME = db.Column(db.String(255))
RELATIONSHIP_ID = db.Column(db.ForeignKey('relationship.ID'), index=True)
GENDER_ID = db.Column(db.ForeignKey('gender.ID'), index=True)
DATE_OF_BIRTH = db.Column(db.String(255))
EMAIL_ID = db.Column(db.String(255))
MOBILE_NO = db.Column(db.String(255))
OCCUPATION = db.Column(db.String(255))
PASSPORTID = db.Column(db.String(255))
IS_ENABLE = db.Column(BIT(1))
EMP_ID = db.Column(db.ForeignKey('employee.SID'), nullable=False, index=True)
ADHAR_DOCUMENT = db.Column(LONGBLOB)
ADHAR_FILENAME = db.Column(db.String(255))
AADHAR_NO = db.Column(db.String(80))
employee = db.relationship('Employee', primaryjoin='EmployeeFamilyDetail.EMP_ID == Employee.SID', backref='employee_family_details')
gender = db.relationship('Gender', primaryjoin='EmployeeFamilyDetail.GENDER_ID == Gender.ID', backref='employee_family_details')
relationship = db.relationship('Relationship', primaryjoin='EmployeeFamilyDetail.RELATIONSHIP_ID == Relationship.ID', backref='employee_family_details')
class Comment(Base):
comment_id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.ForeignKey('user.user_id'), index=True)
message_id = db.Column(db.ForeignKey('message.message_id'), index=True)
content = db.Column(db.Text)
reply_to = db.Column(db.Integer, nullable=False)
reply_to_id = db.Column(db.ForeignKey('user.user_id'), index=True)
hidden = db.Column(BIT(1), nullable=False, server_default=db.text("b'0'"))
message = db.relationship('Message')
user1 = db.relationship('User', foreign_keys=[user_id])
user2 = db.relationship('User', foreign_keys=[reply_to_id])
@staticmethod
def insert_comment(user_id, msg_id, content, reply_to, reply_to_id):
comment = Comment(user_id=user_id,
message_id=msg_id,
content=content,
reply_to=reply_to,
reply_to_id=reply_to_id)
db.session.add(comment)
db.session.commit()
return comment.comment_id
@staticmethod
def find_by_id(comment_id):
"""return comment of the certain comment_id"""
result = Comment.query.filter_by(comment_id=comment_id).all()
return result
@staticmethod
def find_original_comment(msg_id, offset, length):
"""return all comments of message msg_id
this static method also prepares for comments pagination
"""
result = db.session.query(Comment, User).join(User, User.user_id == Comment.user_id) \
.filter(Comment.message_id == msg_id, Comment.hidden == 0, Comment.reply_to == 0) \
.order_by(Comment.comment_id.desc()).limit(length).offset(offset).all()
return result
@staticmethod
def hide_comment(comment_id):
"""hide the comment of the certain comment_id"""
result = Comment.query.filter_by(comment_id=comment_id).first()
if result is not None:
result.hidden = 1
db.session.commit()
return result.comment_id
@staticmethod
def show_comment(comment_id):
"""show the comment of the certain comment_id"""
result = Comment.query.filter_by(comment_id=comment_id).first()
if result is not None:
result.hidden = 0
db.session.commit()
return result.comment_id
@staticmethod
def count_original_comment(msg_id):
"""return the number of comments of message msg_id"""
return Comment.query \
.filter(Comment.message_id == msg_id, Comment.hidden == 0, Comment.reply_to == 0) \
.count()
@staticmethod
def get_statistics(self_id):
"""return basic statistics of author's(self_id) comments"""
total = Comment.query.filter_by(user_id=self_id).all()
result = [0, 0, 0, 0]
for m in total:
result[0] += 1
if m.hidden:
result[1] += 1
if m.reply_to == 0:
result[2] += 1
else:
result[3] += 1
return result
@staticmethod
def find_reply_by_comment(reply_to):
"""find replies to a certain comment"""
# reply_to can also constraint the message that the reply belongs to
result = db.session.query(Comment, User).join(User, User.user_id == Comment.user_id) \
.filter(Comment.reply_to == reply_to, Comment.hidden == 0).all()
return result
@staticmethod
def find_reply_to(user_id, offset, length):
"""query replies to user_id, return records from offset to offset + length"""
result = db.session.query(Comment, User.nickname, User.avatar) \
.join(User, User.user_id == Comment.user_id) \
.filter(Comment.reply_to_id == user_id, Comment.hidden == 0, Comment.user_id != user_id) \
.order_by(Comment.comment_id.desc()).limit(length).offset(offset).all()
return result
@staticmethod
def count_reply_to(user_id):
"""return the number of comments that replied to user_id"""
return Comment.query\
.filter(Comment.reply_to_id == user_id, Comment.hidden == 0, Comment.user_id != user_id)\
.count()
@staticmethod
def find_self_comment(self_id, offset, length):
"""query author's(self_id) own comments, return records from offset to offset + length"""
result = db.session.query(Comment, User.nickname, User.avatar) \
.join(User, User.user_id == Comment.reply_to_id) \
.filter(Comment.user_id == self_id) \
.order_by(Comment.comment_id.desc()).limit(length).offset(offset).all()
return result
@staticmethod
def count_self_comment(self_id):
return Comment.query.filter(Comment.user_id == self_id).count()
Column('name', String(128)), Column('nodeDepth', Integer),
Column('fullPathName', String(512)),
Column('leftNode', Integer), Column('rightNode', Integer),
Column('parentID', Integer), Column('createTime', DateTime),
Column('timeStamp', DateTime))
t_customer = Table('customer', metadata, Column('firstName', String(50)),
Column('lastName', String(50)), Column('title', String(50)),
Column('company', String(50)),
Column('companyRegistrationNumber', String(10)),
Column('customerTypeID', Integer),
Column('discountID', Integer),
Column('taxCategoryID', Integer),
Column('customerID',
Integer), Column('createTime', DateTime),
Column('timeStamp', DateTime), Column('archived', BIT(5)),
Column('dob', String(25)), Column('address', String(128)),
Column('address1', String(128)),
Column('address2', String(128)),
Column('city', String(128)), Column('state', String(50)),
Column('zip', String(14)), Column('stateCode', String(2)),
Column('email', String(128)))
t_ecom_products = Table('ecom_products', metadata, Column('id', Integer),
Column('createdAt', DateTime),
Column('updatedAt', DateTime),
Column('isVisible', BIT(5)),
Column('visibility', String(7)),
Column('hasMatrix', BIT(5)),
Column('data01', String(255)),
Column('data02', String(255)),
t_srl_races = Table(
'srl_races', metadata, Column('id', INTEGER(11), primary_key=True),
Column('srl_id', String(45), nullable=False, unique=True),
Column('goal', String(200), nullable=False),
Column(
'timestamp',
DateTime,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")),
Column('message', String(200)))
t_srlnick = Table('srlnick', metadata,
Column('discord_user_id', BIGINT(20), primary_key=True),
Column('srl_nick', String(200), index=True),
Column('twitch_name', String(200), index=True),
Column('srl_verified', BIT(1)))
t_tournament_results = Table('tournament_results', metadata,
Column('id', INTEGER(11), primary_key=True),
Column('srl_id', String(45, 'utf8_bin')),
Column('episode_id', String(45, 'utf8_bin')),
Column('permalink', String(200, 'utf8_bin')),
Column('spoiler', String(200, 'utf8_bin')),
Column('event', String(45, 'utf8_bin')),
Column('status', String(45, 'utf8_bin')),
Column('results_json', JSON),
Column('week', String(45, 'utf8_bin')),
Column('written_to_gsheet', TINYINT(4)))
t_twitch_channels = Table('twitch_channels', metadata,
Column('channel', String(200), primary_key=True),
class MySQLEngineSpec(BaseEngineSpec, BasicParametersMixin):
engine = "mysql"
engine_name = "MySQL"
max_column_name_length = 64
default_driver = "mysqldb"
sqlalchemy_uri_placeholder = (
"mysql://*****:*****@host:port/dbname[?key=value&key=value...]")
encryption_parameters = {"ssl": "1"}
column_type_mappings = (
(
re.compile(r"^int.*", re.IGNORECASE),
INTEGER(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^tinyint", re.IGNORECASE),
TINYINT(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^mediumint", re.IGNORECASE),
MEDIUMINT(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^decimal", re.IGNORECASE),
DECIMAL(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^float", re.IGNORECASE),
FLOAT(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^double", re.IGNORECASE),
DOUBLE(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^bit", re.IGNORECASE),
BIT(),
GenericDataType.NUMERIC,
),
(
re.compile(r"^tinytext", re.IGNORECASE),
TINYTEXT(),
GenericDataType.STRING,
),
(
re.compile(r"^mediumtext", re.IGNORECASE),
MEDIUMTEXT(),
GenericDataType.STRING,
),
(
re.compile(r"^longtext", re.IGNORECASE),
LONGTEXT(),
GenericDataType.STRING,
),
)
_time_grain_expressions = {
None:
"{col}",
"PT1S":
"DATE_ADD(DATE({col}), "
"INTERVAL (HOUR({col})*60*60 + MINUTE({col})*60"
" + SECOND({col})) SECOND)",
"PT1M":
"DATE_ADD(DATE({col}), "
"INTERVAL (HOUR({col})*60 + MINUTE({col})) MINUTE)",
"PT1H":
"DATE_ADD(DATE({col}), "
"INTERVAL HOUR({col}) HOUR)",
"P1D":
"DATE({col})",
"P1W":
"DATE(DATE_SUB({col}, "
"INTERVAL DAYOFWEEK({col}) - 1 DAY))",
"P1M":
"DATE(DATE_SUB({col}, "
"INTERVAL DAYOFMONTH({col}) - 1 DAY))",
"P3M":
"MAKEDATE(YEAR({col}), 1) "
"+ INTERVAL QUARTER({col}) QUARTER - INTERVAL 1 QUARTER",
"P1Y":
"DATE(DATE_SUB({col}, "
"INTERVAL DAYOFYEAR({col}) - 1 DAY))",
"1969-12-29T00:00:00Z/P1W":
"DATE(DATE_SUB({col}, "
"INTERVAL DAYOFWEEK(DATE_SUB({col}, "
"INTERVAL 1 DAY)) - 1 DAY))",
}
type_code_map: Dict[int,
str] = {} # loaded from get_datatype only if needed
custom_errors: Dict[Pattern[str], Tuple[str, SupersetErrorType, Dict[
str, Any]]] = {
CONNECTION_ACCESS_DENIED_REGEX: (
__('Either the username "%(username)s" or the password is incorrect.'
),
SupersetErrorType.CONNECTION_ACCESS_DENIED_ERROR,
{
"invalid": ["username", "password"]
},
),
CONNECTION_INVALID_HOSTNAME_REGEX: (
__('Unknown MySQL server host "%(hostname)s".'),
SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
{
"invalid": ["host"]
},
),
CONNECTION_HOST_DOWN_REGEX: (
__('The host "%(hostname)s" might be down and can\'t be reached.'
),
SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
{
"invalid": ["host", "port"]
},
),
CONNECTION_UNKNOWN_DATABASE_REGEX: (
__('Unable to connect to database "%(database)s".'),
SupersetErrorType.CONNECTION_UNKNOWN_DATABASE_ERROR,
{
"invalid": ["database"]
},
),
SYNTAX_ERROR_REGEX: (
__('Please check your query for syntax errors near "%(server_error)s". '
"Then, try running your query again."),
SupersetErrorType.SYNTAX_ERROR,
{},
),
}
@classmethod
def convert_dttm(
cls,
target_type: str,
dttm: datetime,
db_extra: Optional[Dict[str, Any]] = None) -> Optional[str]:
tt = target_type.upper()
if tt == utils.TemporalType.DATE:
return f"STR_TO_DATE('{dttm.date().isoformat()}', '%Y-%m-%d')"
if tt == utils.TemporalType.DATETIME:
datetime_formatted = dttm.isoformat(sep=" ",
timespec="microseconds")
return f"""STR_TO_DATE('{datetime_formatted}', '%Y-%m-%d %H:%i:%s.%f')"""
return None
@classmethod
def adjust_database_uri(cls,
uri: URL,
selected_schema: Optional[str] = None) -> URL:
if selected_schema:
uri = uri.set(database=parse.quote(selected_schema, safe=""))
return uri
@classmethod
def get_datatype(cls, type_code: Any) -> Optional[str]:
if not cls.type_code_map:
# only import and store if needed at least once
# pylint: disable=import-outside-toplevel
import MySQLdb
ft = MySQLdb.constants.FIELD_TYPE
cls.type_code_map = {
getattr(ft, k): k
for k in dir(ft) if not k.startswith("_")
}
datatype = type_code
if isinstance(type_code, int):
datatype = cls.type_code_map.get(type_code)
if datatype and isinstance(datatype, str) and datatype:
return datatype
return None
@classmethod
def epoch_to_dttm(cls) -> str:
return "from_unixtime({col})"
@classmethod
def _extract_error_message(cls, ex: Exception) -> str:
"""Extract error message for queries"""
message = str(ex)
try:
if isinstance(ex.args, tuple) and len(ex.args) > 1:
message = ex.args[1]
except (AttributeError, KeyError):
pass
return message
@classmethod
def get_column_spec(
cls,
native_type: Optional[str],
db_extra: Optional[Dict[str, Any]] = None,
source: utils.ColumnTypeSource = utils.ColumnTypeSource.GET_TABLE,
column_type_mappings: Tuple[ColumnTypeMapping,
...] = column_type_mappings,
) -> Optional[ColumnSpec]:
column_spec = super().get_column_spec(native_type)
if column_spec:
return column_spec
return super().get_column_spec(
native_type, column_type_mappings=column_type_mappings)
@classmethod
def get_cancel_query_id(cls, cursor: Any, query: Query) -> Optional[str]:
"""
Get MySQL connection ID that will be used to cancel all other running
queries in the same connection.
:param cursor: Cursor instance in which the query will be executed
:param query: Query instance
:return: MySQL Connection ID
"""
cursor.execute("SELECT CONNECTION_ID()")
row = cursor.fetchone()
return row[0]
@classmethod
def cancel_query(cls, cursor: Any, query: Query,
cancel_query_id: str) -> bool:
"""
Cancel query in the underlying database.
:param cursor: New cursor instance to the db of the query
:param query: Query instance
:param cancel_query_id: MySQL Connection ID
:return: True if query cancelled successfully, False otherwise
"""
try:
cursor.execute(f"KILL CONNECTION {cancel_query_id}")
except Exception: # pylint: disable=broad-except
return False
return True
class ContractRoomTypeModel(Base):
__tablename__ = 'contract_roomtype'
__table_args__ = {'mysql_charset': 'utf8', 'mysql_engine': 'InnoDB'}
id = Column(INTEGER, primary_key=True, autoincrement=True)
merchant_id = Column(INTEGER, nullable=False)
hotel_id = Column(INTEGER, nullable=False)
base_hotel_id = Column(INTEGER, nullable=False)
roomtype_id = Column(INTEGER, nullable=False)
base_roomtype_id = Column(INTEGER, nullable=False)
pay_type = Column(TINYINT, nullable=False, default=0)
weekday_base_price = Column(INTEGER)
weekend_base_price = Column(INTEGER)
weekday_sell_price = Column(INTEGER)
weekend_sell_price = Column(INTEGER)
cancel_rule = Column(VARCHAR)
retain_num = Column(INTEGER)
breakfast = Column(VARCHAR(50))
remark = Column(TEXT)
is_delete = Column(BIT(1), nullable=False, default=0)
@classmethod
def get_by_hotel(cls, session, hotel_id):
query = session.query(ContractRoomTypeModel)\
.filter(ContractRoomTypeModel.hotel_id == hotel_id,
ContractRoomTypeModel.is_delete == 0)
return query.all()
@classmethod
def get_by_hotel_roomtype_pay_type(cls, session, hotel_id, roomtype_id,
pay_type):
query = session.query(ContractRoomTypeModel)\
.filter(ContractRoomTypeModel.hotel_id == hotel_id,
ContractRoomTypeModel.roomtype_id == roomtype_id,
ContractRoomTypeModel.pay_type == pay_type,
ContractRoomTypeModel.is_delete == 0)
return query.first()
@classmethod
def new(self, session, hotel_id, roomtype_id, pay_type, **kwargs):
contract = ContractRoomTypeModel(hotel_id=hotel_id,
roomtype_id=roomtype_id,
pay_type=pay_type,
**kwargs)
session.add(contract)
session.commit()
return contract
@classmethod
def update(self, session, hotel_id, roomtype_id, pay_type, **kwargs):
session.query(ContractRoomTypeModel)\
.filter(ContractRoomTypeModel.hotel_id == hotel_id,
ContractRoomTypeModel.roomtype_id == roomtype_id,
ContractRoomTypeModel.pay_type == pay_type,
ContractRoomTypeModel.is_delete == 0)\
.update(kwargs)
session.commit()
def todict(self):
return ObjectDict(
id=self.id,
merchant_id=self.merchant_id,
hotel_id=self.hotel_id,
base_hotel_id=self.base_hotel_id,
roomtype_id=self.roomtype_id,
base_roomtype_id=self.base_roomtype_id,
pay_type=self.pay_type,
weekday_base_price=self.weekday_base_price,
weekend_base_price=self.weekend_base_price,
weekday_sell_price=self.weekday_sell_price,
weekend_sell_price=self.weekend_sell_price,
retain_num=self.retain_num,
breakfast=self.breakfast,
remark=self.remark,
cancel_rule=self.cancel_rule,
)
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata
t_alphabetical_list_of_products = Table(
'alphabetical list of products', metadata,
Column('ProductID', INTEGER(11), server_default=text("'0'")),
Column('ProductName', String(40)), Column('SupplierID', INTEGER(11)),
Column('CategoryID', INTEGER(11)), Column('QuantityPerUnit', String(20)),
Column('UnitPrice', DECIMAL(10, 4), server_default=text("'0.0000'")),
Column('UnitsInStock', SMALLINT(2), server_default=text("'0'")),
Column('UnitsOnOrder', SMALLINT(2), server_default=text("'0'")),
Column('ReorderLevel', SMALLINT(2), server_default=text("'0'")),
Column('Discontinued', BIT(1), server_default=text("'b''0'''")),
Column('CategoryName', String(15)))
class Category(Base):
__tablename__ = 'categories'
CategoryID = Column(INTEGER(11), primary_key=True)
CategoryName = Column(String(15), nullable=False, index=True)
Description = Column(MEDIUMTEXT)
Picture = Column(LONGBLOB)
t_category_sales_for_1997 = Table('category sales for 1997', metadata,
Column('CategoryName', String(15)),
Column('CategorySales', Float(25, True)))
class MySQLEngineSpec(BaseEngineSpec):
engine = "mysql"
engine_name = "MySQL"
max_column_name_length = 64
column_type_mappings: Tuple[
Tuple[
Pattern[str],
Union[TypeEngine, Callable[[Match[str]], TypeEngine]],
GenericDataType,
],
...,
] = (
(re.compile(r"^int.*", re.IGNORECASE), INTEGER(), GenericDataType.NUMERIC,),
(re.compile(r"^tinyint", re.IGNORECASE), TINYINT(), GenericDataType.NUMERIC,),
(
re.compile(r"^mediumint", re.IGNORECASE),
MEDIUMINT(),
GenericDataType.NUMERIC,
),
(re.compile(r"^decimal", re.IGNORECASE), DECIMAL(), GenericDataType.NUMERIC,),
(re.compile(r"^float", re.IGNORECASE), FLOAT(), GenericDataType.NUMERIC,),
(re.compile(r"^double", re.IGNORECASE), DOUBLE(), GenericDataType.NUMERIC,),
(re.compile(r"^bit", re.IGNORECASE), BIT(), GenericDataType.NUMERIC,),
(re.compile(r"^tinytext", re.IGNORECASE), TINYTEXT(), GenericDataType.STRING,),
(
re.compile(r"^mediumtext", re.IGNORECASE),
MEDIUMTEXT(),
GenericDataType.STRING,
),
(re.compile(r"^longtext", re.IGNORECASE), LONGTEXT(), GenericDataType.STRING,),
)
_time_grain_expressions = {
None: "{col}",
"PT1S": "DATE_ADD(DATE({col}), "
"INTERVAL (HOUR({col})*60*60 + MINUTE({col})*60"
" + SECOND({col})) SECOND)",
"PT1M": "DATE_ADD(DATE({col}), "
"INTERVAL (HOUR({col})*60 + MINUTE({col})) MINUTE)",
"PT1H": "DATE_ADD(DATE({col}), " "INTERVAL HOUR({col}) HOUR)",
"P1D": "DATE({col})",
"P1W": "DATE(DATE_SUB({col}, " "INTERVAL DAYOFWEEK({col}) - 1 DAY))",
"P1M": "DATE(DATE_SUB({col}, " "INTERVAL DAYOFMONTH({col}) - 1 DAY))",
"P0.25Y": "MAKEDATE(YEAR({col}), 1) "
"+ INTERVAL QUARTER({col}) QUARTER - INTERVAL 1 QUARTER",
"P1Y": "DATE(DATE_SUB({col}, " "INTERVAL DAYOFYEAR({col}) - 1 DAY))",
"1969-12-29T00:00:00Z/P1W": "DATE(DATE_SUB({col}, "
"INTERVAL DAYOFWEEK(DATE_SUB({col}, "
"INTERVAL 1 DAY)) - 1 DAY))",
}
type_code_map: Dict[int, str] = {} # loaded from get_datatype only if needed
custom_errors = {
CONNECTION_ACCESS_DENIED_REGEX: (
__('Either the username "%(username)s" or the password is incorrect.'),
SupersetErrorType.CONNECTION_ACCESS_DENIED_ERROR,
),
CONNECTION_INVALID_HOSTNAME_REGEX: (
__('Unknown MySQL server host "%(hostname)s".'),
SupersetErrorType.CONNECTION_INVALID_HOSTNAME_ERROR,
),
CONNECTION_HOST_DOWN_REGEX: (
__('The host "%(hostname)s" might be down and can\'t be reached.'),
SupersetErrorType.CONNECTION_HOST_DOWN_ERROR,
),
CONNECTION_UNKNOWN_DATABASE_REGEX: (
__(
'We were unable to connect to your database named "%(database)s". '
"Please verify your database name and try again."
),
SupersetErrorType.CONNECTION_UNKNOWN_DATABASE_ERROR,
),
}
@classmethod
def convert_dttm(cls, target_type: str, dttm: datetime) -> Optional[str]:
tt = target_type.upper()
if tt == utils.TemporalType.DATE:
return f"STR_TO_DATE('{dttm.date().isoformat()}', '%Y-%m-%d')"
if tt == utils.TemporalType.DATETIME:
datetime_formatted = dttm.isoformat(sep=" ", timespec="microseconds")
return f"""STR_TO_DATE('{datetime_formatted}', '%Y-%m-%d %H:%i:%s.%f')"""
return None
@classmethod
def adjust_database_uri(
cls, uri: URL, selected_schema: Optional[str] = None
) -> None:
if selected_schema:
uri.database = parse.quote(selected_schema, safe="")
@classmethod
def get_datatype(cls, type_code: Any) -> Optional[str]:
if not cls.type_code_map:
# only import and store if needed at least once
import MySQLdb
ft = MySQLdb.constants.FIELD_TYPE
cls.type_code_map = {
getattr(ft, k): k for k in dir(ft) if not k.startswith("_")
}
datatype = type_code
if isinstance(type_code, int):
datatype = cls.type_code_map.get(type_code)
if datatype and isinstance(datatype, str) and datatype:
return datatype
return None
@classmethod
def epoch_to_dttm(cls) -> str:
return "from_unixtime({col})"
@classmethod
def _extract_error_message(cls, ex: Exception) -> str:
"""Extract error message for queries"""
message = str(ex)
try:
if isinstance(ex.args, tuple) and len(ex.args) > 1:
message = ex.args[1]
except (AttributeError, KeyError):
pass
return message
@classmethod
def get_column_spec( # type: ignore
cls,
native_type: Optional[str],
source: utils.ColumnTypeSource = utils.ColumnTypeSource.GET_TABLE,
column_type_mappings: Tuple[
Tuple[
Pattern[str],
Union[TypeEngine, Callable[[Match[str]], TypeEngine]],
GenericDataType,
],
...,
] = column_type_mappings,
) -> Union[ColumnSpec, None]:
column_spec = super().get_column_spec(native_type)
if column_spec:
return column_spec
return super().get_column_spec(
native_type, column_type_mappings=column_type_mappings
)
class ContractSpecPriceModel(Base):
__tablename__ = 'contract_spec_price'
__table_args__ = {'mysql_charset': 'utf8', 'mysql_engine': 'InnoDB'}
id = Column(INTEGER, primary_key=True, autoincrement=True)
hotel_id = Column(INTEGER, nullable=False)
roomtype_id = Column(INTEGER, nullable=False)
pay_type = Column(TINYINT, nullable=False, default=0)
start_date = Column(DATE, nullable=False)
end_date = Column(DATE, nullable=False)
price = Column(INTEGER, nullable=False, default=0)
remark = Column(VARCHAR(200))
is_delete = Column(BIT(1), nullable=False, default=0)
@classmethod
def get_by_id(cls, session, id):
query = session.query(ContractSpecPriceModel)\
.filter(ContractSpecPriceModel.id == id,
ContractSpecPriceModel.is_delete == 0
)
return query.first()
@classmethod
def get_by_hotel(cls, session, hotel_id):
query = session.query(ContractSpecPriceModel)\
.filter(ContractSpecPriceModel.hotel_id == hotel_id,
ContractSpecPriceModel.is_delete == 0
)
return query.all()
@classmethod
def get_by_hotel_roomtype_pay_type(cls, session, hotel_id, roomtype_id,
pay_type):
query = session.query(ContractSpecPriceModel)\
.filter(ContractSpecPriceModel.hotel_id == hotel_id,
ContractSpecPriceModel.roomtype_id == roomtype_id,
ContractSpecPriceModel.pay_type == pay_type,
ContractSpecPriceModel.is_delete == 0
)
return query.all()
@classmethod
def new(cls, session, hotel_id, roomtype_id, pay_type, **kwargs):
contract = ContractSpecPriceModel(hotel_id=hotel_id,
roomtype_id=roomtype_id,
pay_type=pay_type,
**kwargs)
session.add(contract)
session.commit()
return contract
@classmethod
def update(cls, session, id, **kwargs):
session.query(ContractSpecPriceModel)\
.filter(ContractSpecPriceModel.id == id)\
.update(kwargs)
session.commit()
def todict(self):
return ObjectDict(
id=self.id,
hotel_id=self.hotel_id,
roomtype_id=self.roomtype_id,
pay_type=self.pay_type,
start_date=self.start_date,
end_date=self.end_date,
price=self.price,
remark=self.remark,
)
