class LayoutDiscuss(object):
def __init__(self):
pass
layout_discuss = Table('layout_discuss', metadata,
Column('id', Integer, primary_key=True, nullable=False, unique=True),
Column('layout_id', Integer, ForeignKey('layouts.id', ondelete='CASCADE'), nullable=False, ),
Column('parent_id', Integer, ForeignKey('layout_discuss.id', ondelete='SET NULL'), nullable=True, ),
Column('crdate', DateTime(timezone=True), nullable=False, server_default=func.now()),
Column('lastupdate', DateTime(timezone=True), nullable=False),
Column('content', TEXT, nullable=False, server_default=''),
Column('setts', TEXT, nullable=False, server_default='{}'),
Column('user_id', Integer, nullable=True),
Column('user_name', VARCHAR(250), nullable=True),
Column('is_read', Boolean(), nullable=False, server_default=sa_false()),
extend_existing=True,
)
mapper(LayoutDiscuss, layout_discuss, properties={
'layout': relationship(
Layouts, backref='discussions',
order_by=Layouts.id,
cascade='all,delete,delete-orphan',
single_parent=True,
),
'parent': relationship(
LayoutDiscuss, backref='children',
cascade='all,delete,delete-orphan',
single_parent=True,
remote_side=layout_discuss.c.id,
class FeeSchedule(db.Model):
"""This class manages all of the base data about a fee schedule.
Fee schedule holds the data related to filing type and fee code which is used to calculate the fees for a filing
"""
__tablename__ = 'fee_schedules'
__table_args__ = (db.UniqueConstraint('filing_type_code',
'corp_type_code',
'fee_code',
name='unique_fee_sched_1'), )
fee_schedule_id = db.Column(db.Integer,
primary_key=True,
autoincrement=True)
filing_type_code = db.Column(db.String(10),
ForeignKey('filing_types.code'),
nullable=False)
corp_type_code = db.Column(db.String(10),
ForeignKey('corp_types.code'),
nullable=False)
fee_code = db.Column(db.String(10),
ForeignKey('fee_codes.code'),
nullable=False)
fee_start_date = db.Column('fee_start_date',
db.Date,
default=date.today(),
nullable=False)
fee_end_date = db.Column('fee_end_date',
db.Date,
default=None,
nullable=True)
future_effective_fee_code = db.Column(db.String(10),
ForeignKey('fee_codes.code'),
nullable=True)
priority_fee_code = db.Column(db.String(10),
ForeignKey('fee_codes.code'),
nullable=True)
service_fee_code = db.Column(db.String(10),
ForeignKey('fee_codes.code'),
nullable=True)
variable = db.Column(Boolean(),
default=False,
comment='Flag to indicate if the fee is variable')
filing_type = relationship(FilingType,
foreign_keys=[filing_type_code],
lazy='joined',
innerjoin=True)
corp_type = relationship(CorpType,
foreign_keys=[corp_type_code],
lazy='joined',
innerjoin=True)
fee = relationship(FeeCode,
foreign_keys=[fee_code],
lazy='select',
innerjoin=True)
future_effective_fee = relationship(
FeeCode,
foreign_keys=[future_effective_fee_code],
lazy='select',
innerjoin=False)
priority_fee = relationship(FeeCode,
foreign_keys=[priority_fee_code],
lazy='select',
innerjoin=False)
service_fee = relationship(FeeCode,
foreign_keys=[service_fee_code],
lazy='select',
innerjoin=False)
@declared_attr
def distribution_codes(cls): # pylint:disable=no-self-argument, # noqa: N805
"""Distribution code relationship."""
return relationship('DistributionCode',
secondary='distribution_code_links',
backref='fee_schedules',
lazy='dynamic')
def __str__(self):
"""Override to string."""
return f'{self.corp_type_code} - {self.filing_type_code}'
@classmethod
def find_by_filing_type_and_corp_type(cls,
corp_type_code: str,
filing_type_code: str,
valid_date: datetime = None):
"""Given a filing_type_code and corp_type, this will return fee schedule."""
if not valid_date:
valid_date = date.today()
fee_schedule = None
if filing_type_code and corp_type_code:
query = cls.query.filter_by(filing_type_code=filing_type_code). \
filter_by(corp_type_code=corp_type_code). \
filter(FeeSchedule.fee_start_date <= valid_date). \
filter((FeeSchedule.fee_end_date.is_(None)) | (FeeSchedule.fee_end_date >= valid_date))
fee_schedule = query.one_or_none()
return fee_schedule
@classmethod
def find_by_id(cls, fee_schedule_id: int):
"""Find and return fee schedule by id."""
return cls.query.get(fee_schedule_id)
@classmethod
def find_all(cls,
corp_type_code: str = None,
filing_type_code: str = None,
description: str = None):
"""Find all fee schedules matching the filters."""
valid_date = date.today()
query = cls.query.filter(FeeSchedule.fee_start_date <= valid_date). \
filter((FeeSchedule.fee_end_date.is_(None)) | (FeeSchedule.fee_end_date >= valid_date))
if filing_type_code:
query = query.filter_by(filing_type_code=filing_type_code)
if corp_type_code:
query = query.filter_by(corp_type_code=corp_type_code)
if description:
# TODO arrive at a better search
descriptions = description.replace(' ', '%')
query = query.join(CorpType,
CorpType.code == FeeSchedule.corp_type_code). \
join(FilingType, FilingType.code == FeeSchedule.filing_type_code)
query = query.filter(
or_(
func.lower(FilingType.description).contains(
descriptions.lower()),
func.lower(CorpType.description).contains(
descriptions.lower())))
return query.all()
def save(self):
"""Save fee schedule."""
db.session.add(self)
db.session.commit()
["releases.name", "releases.version"],
onupdate="CASCADE",
),
Index("rel_class_name_idx", "name"),
Index("rel_class_version_id_idx", "version"),
Index("rel_class_name_version_idx", "name", "version"),
Index("rel_class_trove_id_idx", "trove_id"),
)
packages = Table(
"packages",
db.metadata,
Column("name", UnicodeText(), primary_key=True, nullable=False),
Column("stable_version", UnicodeText()),
Column("normalized_name", UnicodeText()),
Column("autohide", Boolean(), server_default=sql.true()),
Column("comments", Boolean(), server_default=sql.true()),
Column("bugtrack_url", UnicodeText()),
Column(
"hosting_mode",
UnicodeText(),
nullable=False,
server_default="pypi-explicit",
),
Column(
"created",
DateTime(),
nullable=False,
server_default=sql.func.now(),
),
class Ingredient(Base):
__tablename__ = 'ingredient'
id = Column(Integer, primary_key=True)
tag = Column(String(50), unique=True)
name = Column(String(50), unique=True)
is_allergen = Column(Boolean(50), default=False)
class ExpenseSheet(Node, ExpenseCompute):
"""
Model representing a whole ExpenseSheet
An expensesheet is related to a company and an employee (one user may
have multiple expense sheets if it has multiple companies)
:param company_id: The user's company id
:param user_id: The user's id
:param year: The year the expense is related to
:param month: The month the expense is related to
:param status: Status of the sheet
:param comments: Comments added to this expense sheet
:param status_user: The user related to statuschange
:param lines: expense lines of this sheet
"""
__tablename__ = 'expense_sheet'
__table_args__ = default_table_args
__mapper_args__ = {'polymorphic_identity': 'expensesheet'}
__colanderalchemy_config__ = {"validator": deferred_unique_expense}
id = Column(
ForeignKey('node.id'),
primary_key=True,
info={"colanderalchemy": forms.EXCLUDED},
)
month = Column(Integer,
info={
"colanderalchemy": {
"title": u"Mois",
"widget": forms.get_month_select_widget({}),
"validator": colander.OneOf(range(1, 13)),
"default": forms.default_month,
}
})
year = Column(
Integer,
info={
'colanderalchemy': {
"title":
u"Année",
"widget":
forms.get_year_select_deferred(query_func=get_available_years),
"validator":
colander.Range(min=0,
min_err=u"Veuillez saisir une année valide"),
"default":
forms.default_year,
}
})
paid_status = Column(
String(10),
default='waiting',
info={
'colanderalchemy': {
"title": u"Statut du paiement de la note de dépense",
'widget': deform.widget.SelectWidget(values=PAID_STATES),
'validator': colander.OneOf(dict(PAID_STATES).keys())
}
})
justified = Column(
Boolean(),
default=False,
info={'colanderalchemy': {
'title': u"Justificatifs reçus",
}})
status = Column(
String(10),
default='draft',
info={
'colanderalchemy': {
"title":
u"Statut de la note de dépense",
'validator':
colander.OneOf(ALL_STATES),
'widget':
deform.widget.SelectWidget(values=zip(ALL_STATES, ALL_STATES)),
}
})
status_user_id = Column(
Integer,
ForeignKey("accounts.id"),
info={
'colanderalchemy': {
"title": u"Dernier utilisateur à avoir modifié le document",
'widget': get_deferred_user_choice()
},
"export": forms.EXCLUDED,
})
status_date = Column(Date(),
default=datetime.date.today,
onupdate=datetime.date.today,
info={
'colanderalchemy': {
"title":
u"Date du dernier changement de statut",
},
})
purchase_exported = Column(Boolean(),
default=False,
info={
'colanderalchemy': {
'title':
u"Les achats ont déjà été exportés ?"
},
})
expense_exported = Column(Boolean(),
default=False,
info={
'colanderalchemy': {
'title':
u"Les frais ont déjà été exportés ?"
},
})
company_id = Column(Integer,
ForeignKey("company.id", ondelete="cascade"),
info={"colanderalchemy": forms.EXCLUDED})
user_id = Column(Integer,
ForeignKey("accounts.id"),
info={"colanderalchemy": forms.EXCLUDED})
# Relationships
lines = relationship("ExpenseLine",
back_populates="sheet",
cascade="all, delete-orphan",
order_by="ExpenseLine.date",
info={"colanderalchemy": {
"title": u"Dépenses"
}})
kmlines = relationship(
"ExpenseKmLine",
back_populates="sheet",
cascade="all, delete-orphan",
order_by="ExpenseKmLine.date",
info={"colanderalchemy": {
"title": u"Dépenses kilométriques"
}})
company = relationship("Company",
backref=backref(
"expenses",
order_by="ExpenseSheet.month",
cascade="all, delete-orphan",
))
user = relationship(
"User",
primaryjoin="ExpenseSheet.user_id==User.id",
info={
'colanderalchemy': forms.EXCLUDED,
},
backref=backref("expenses",
order_by="ExpenseSheet.month",
info={
'colanderalchemy': forms.EXCLUDED,
'export': {
'exclude': True
},
},
cascade="all, delete-orphan"),
)
status_user = relationship(
"User",
primaryjoin="ExpenseSheet.status_user_id==User.id",
info={
'colanderalchemy': forms.EXCLUDED,
})
communications = relationship("Communication",
back_populates="expense_sheet",
order_by="desc(Communication.date)",
cascade="all, delete-orphan",
info={
'colanderalchemy': forms.EXCLUDED,
})
state_manager = _build_action_manager()
justified_state_manager = _build_justified_state_manager()
def __json__(self, request):
return dict(id=self.id,
name=self.name,
created_at=self.created_at.isoformat(),
updated_at=self.updated_at.isoformat(),
company_id=self.company_id,
user_id=self.user_id,
paid_status=self.paid_status,
justified=self.justified,
status=self.status,
status_user_id=self.status_user_id,
status_date=self.status_date.isoformat(),
lines=[line.__json__(request) for line in self.lines],
kmlines=[line.__json__(request) for line in self.kmlines],
month=self.month,
month_label=strings.month_name(self.month),
year=self.year,
attachments=[
f.__json__(request) for f in self.children
if f.type_ == 'file'
])
def set_status(self, status, request, **kw):
"""
set the status of a task through the state machine
"""
return self.state_manager.process(status, self, request, **kw)
def check_status_allowed(self, status, request, **kw):
return self.state_manager.check_allowed(status, self, request)
def set_justified_status(self, status, request, **kw):
"""
set the signed status of a task through the state machine
"""
return self.justified_state_manager.process(status, self, request,
**kw)
def check_justified_status_allowed(self, status, request, **kw):
return self.justified_state_manager.check_allowed(
status, self, request)
def get_company_id(self):
"""
Return the if of the company associated to this model
"""
return self.company_id
# Payment stuff
def record_payment(self, **kw):
"""
Record a payment for the current expense
"""
logger.debug("Recording a payment")
resulted = kw.pop('resulted', False)
payment = ExpensePayment()
for key, value in kw.iteritems():
setattr(payment, key, value)
logger.info(u"Amount : {0}".format(payment.amount))
self.payments.append(payment)
user_id = kw.get('user_id')
return self.check_resulted(force_resulted=resulted, user_id=user_id)
def check_resulted(self, force_resulted=False, user_id=None):
"""
Check if the expense is resulted or not and set the appropriate status
"""
logger.debug(u"-> There still to pay : %s" % self.topay())
if self.topay() <= 0 or force_resulted:
self.paid_status = 'resulted'
elif len(self.payments) > 0:
self.paid_status = 'paid'
else:
self.paid_status = "waiting"
return self
def duplicate(self, year, month):
sheet = ExpenseSheet()
sheet.month = month
sheet.year = year
sheet.user_id = self.user_id
sheet.company_id = self.company_id
sheet.lines = [line.duplicate(sheet) for line in self.lines]
sheet.kmlines = [line.duplicate(sheet) for line in self.kmlines]
return sheet
# Import create_engine function
from sqlalchemy import Table, Column, String, Integer, Float, Boolean, insert, select
# Create an engine to the census database
engine = create_engine('sqlite:///:memory:')
metadata = Metadata()
connection = engine.connect()
#----------------------------------------------------------------------------------------------------
# Define a new table with a name, count, amount, and valid column: data
data = Table('data', metadata, Column('name', String(255)),
Column('count', Integer()), Column('amount', Float()),
Column('valid', Boolean()))
# Use the metadata to create the table
metadata.create_all(engine)
# Build an insert statement to insert a record into the data table: stmt
stmt = insert(data).values(name='Anna', count=1, amount=1000.00, valid=True)
# Execute the statement via the connection: results
results = connection.execute(stmt)
# Print result rowcount
print(results.rowcount)
# Build a select statement to validate the insert
stmt = select([data]).where(data.columns.name == 'Anna')
# Print the result of executing the query.
class Entity(BaseModel): # pylint: disable=too-few-public-methods, too-many-instance-attributes
"""This is the Entity model for the Auth service."""
__tablename__ = 'entities'
id = Column(Integer, primary_key=True)
business_identifier = Column('business_identifier',
String(75),
unique=True,
nullable=False)
pass_code = Column('pass_code', String(75), unique=False, nullable=True)
pass_code_claimed = Column('pass_code_claimed', Boolean(), default=False)
business_number = Column('business_number', String(100), nullable=True)
name = Column('name', String(250), nullable=True)
corp_type_code = Column(String(15),
ForeignKey('corp_types.code'),
nullable=False)
folio_number = Column('folio_number',
String(50),
nullable=True,
index=True)
status = Column(String(), nullable=True)
last_modified_by = Column(String(), nullable=True)
last_modified = Column(DateTime, default=None, nullable=True)
contacts = relationship('ContactLink', back_populates='entity')
corp_type = relationship('CorpType',
foreign_keys=[corp_type_code],
lazy='joined',
innerjoin=True)
affiliations = relationship('Affiliation',
cascade='all,delete,delete-orphan',
lazy='joined')
@classmethod
def find_by_business_identifier(cls, business_identifier):
"""Return the first entity with the provided business identifier."""
return cls.query.filter_by(
business_identifier=business_identifier).one_or_none()
@classmethod
def create_from_dict(cls, entity_info: dict):
"""Create a new Entity from the provided dictionary."""
if entity_info:
entity = Entity(**camelback2snake(entity_info))
entity.pass_code = passcode_hash(entity.pass_code)
current_app.logger.debug(
f'Creating entity from dictionary {entity_info}')
entity.save()
return entity
return None
@classmethod
def find_by_entity_id(cls, entity_id):
"""Find an Entity instance that matches the provided id."""
return cls.query.filter_by(id=entity_id).first()
def reset(self):
"""Reset an Entity back to init state."""
self.pass_code_claimed = False
self.folio_number = None
self.name = 'Test ' + self.business_identifier + ' Name'
self.created_by_id = None
self.created = None
self.modified_by_id = None
self.modified = None
self.save()
class User(Base):
id = Column(Integer, primary_key=True, index=True)
email = Column(String, unique=True, index=True)
hashed_password = Column(String)
is_active = Column(Boolean(), default=True)
def test_boolean_with_mssql(self, registry_plugin):
report = MigrationReport(registry_plugin.migration, [])
res = report.init_modify_type(
[None, None, 'test', 'other', {},
BIT(), Boolean()])
assert res is True
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True)
username = Column(String(128), nullable=False, index=True)
email = Column(String(256), nullable=False, index=True)
public = Column(Boolean())
admin = Column(Boolean())
password = Column(String)
description = Column(Unicode(10000))
created = Column(DateTime)
forumUsername = Column(String(128))
forumId = Column(Integer)
ircNick = Column(String(128))
twitterUsername = Column(String(128))
redditUsername = Column(String(128))
location = Column(String(128))
confirmation = Column(String(128))
passwordReset = Column(String(128))
passwordResetExpiry = Column(DateTime)
backgroundMedia = Column(String(512))
bgOffsetX = Column(Integer)
bgOffsetY = Column(Integer)
mods = relationship('Mod', order_by='Mod.created')
packs = relationship('ModList', order_by='ModList.created')
following = relationship('Mod', secondary=mod_followers, backref='user.id')
dark_theme = Column(Boolean())
def set_password(self, password):
self.password = bcrypt.hashpw(password.encode('utf-8'),
bcrypt.gensalt()).decode('utf-8')
def __init__(self, username, email, password):
self.email = email
self.username = username
self.password = bcrypt.hashpw(password.encode('utf-8'),
bcrypt.gensalt()).decode('utf-8')
self.public = False
self.admin = False
self.created = datetime.now()
self.twitterUsername = ''
self.forumUsername = ''
self.ircNick = ''
self.description = ''
self.backgroundMedia = ''
self.bgOffsetX = 0
self.bgOffsetY = 0
self.dark_theme = False
def __repr__(self):
return '<User %r>' % self.username
# Flask.Login stuff
# We don't use most of these features
def is_authenticated(self):
return True
def is_active(self):
return True
def is_anonymous(self):
return False
def get_id(self):
return self.username
class DataAccessLayer():
connection = None
engine = None
conn_string = None
metadata = MetaData()
def init(self,
host='localhost',
user='******',
passwd='root',
db='test',
port=3306):
"""初始化"""
self.conn_string = "mysql+mysqldb://%s:%s@%s:%s/%s" % (user, passwd,
host, port, db)
self.engine = create_engine(self.conn_string, pool_recycle=3600)
print "创建Schema START"
self.metadata.drop_all(self.engine)
self.metadata.create_all(self.engine)
print "创建Schema END"
self.connection = self.engine.connect()
######################################################
# Schema定义
######################################################
###################################################### 表COOKIES
cookies = Table(
"COOKIES",
metadata,
Column("cookie_id", Integer(), primary_key=True),
Column("cookie_name", String(50)), # index = True
Column("cookie_recipe_url", String(255)),
Column("cookie_sku", String(55)),
Column("quantity", Integer()),
Column("unit_cost", Numeric(12, 2)),
# 检查约束
CheckConstraint("unit_cost >= 0.0", name="unit_cost_positive"),
# # 索引
Index("ix_cookies_cookie_name", "cookie_name"),
Index("ix_cookie_sku_name", "cookie_sku", "cookie_name"))
# Index("ix_cookies_cookie_name", cookies.c.cookie_name) # 使用Table中字段
# Index("ix_cookie_sku_name", cookies.c.cookie_sku, cookies.c.cookie_name)
###################################################### 表USERS
users = Table(
"USERS",
metadata,
Column("user_id", Integer()), # primary_key = True
Column("username", String(15), nullable=False), # unique = True
Column("email_address", String(255), nullable=False),
Column("phone", String(20), nullable=False),
Column("password", String(25), nullable=False),
Column("create_on", DateTime(), default=datetime.now),
Column("update_on",
DateTime(),
default=datetime.now,
onupdate=datetime.now),
# 主键约束
PrimaryKeyConstraint("user_id", name="user_pk"),
# 唯一性约束
UniqueConstraint("username", name="uix_username"),
)
###################################################### 表ORDERS
orders = Table(
"ORDERS",
metadata,
Column("order_id", Integer, primary_key=True),
Column("user_id", ForeignKey("USERS.user_id")), # 外键
Column("shipped", Boolean(), default=False))
###################################################### 表LINE_ITEMS
line_items = Table(
"LINE_ITEMS",
metadata,
Column("line_items_id", Integer(), primary_key=True),
Column("order_id"), # ForeignKey("ORDERS.order_id")
Column("cookie_id", ForeignKey("COOKIES.cookie_id")), # 外键
Column("quantity", Integer()),
Column("extended_cost", Numeric(12, 2)),
# 外键约束
ForeignKeyConstraint(["order_id"], ["ORDERS.order_id"]))
###################################################### 表EMPLOYEE
employee = Table("EMPLOYEE", metadata,
Column("id", Integer(), primary_key=True),
Column("manager", None, ForeignKey("EMPLOYEE.id")),
Column("name", String(255), unique=True))
class Mod(Base):
__tablename__ = 'mod'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey('user.id'))
user = relationship('User', backref=backref('mod', order_by=id))
game_id = Column(Integer, ForeignKey('game.id'))
game = relationship('Game', back_populates='mods')
shared_authors = relationship('SharedAuthor')
name = Column(String(100), index=True)
description = Column(Unicode(100000))
short_description = Column(Unicode(1000))
approved = Column(Boolean())
published = Column(Boolean())
donation_link = Column(String(512))
external_link = Column(String(512))
license = Column(String(128))
votes = Column(Integer())
created = Column(DateTime)
updated = Column(DateTime)
background = Column(String(512))
bgOffsetX = Column(Integer)
bgOffsetY = Column(Integer)
medias = relationship('Media')
default_version_id = Column(Integer)
versions = relationship('ModVersion',
order_by="desc(ModVersion.sort_index)")
downloads = relationship('DownloadEvent',
order_by="desc(DownloadEvent.created)")
follow_events = relationship('FollowEvent',
order_by="desc(FollowEvent.created)")
referrals = relationship('ReferralEvent',
order_by="desc(ReferralEvent.created)")
source_link = Column(String(256))
follower_count = Column(Integer, nullable=False, server_default=text('0'))
download_count = Column(Integer, nullable=False, server_default=text('0'))
followers = relationship('User',
viewonly=True,
secondary=mod_followers,
backref='mod.id')
ckan = Column(Boolean)
def background_thumb(self):
if (_cfg('thumbnail_size') == ''):
return self.background
thumbnailSizesStr = _cfg('thumbnail_size').split('x')
thumbnailSize = (int(thumbnailSizesStr[0]), int(thumbnailSizesStr[1]))
split = os.path.split(self.background)
thumbPath = os.path.join(split[0], 'thumb_' + split[1])
fullThumbPath = os.path.join(
os.path.join(_cfg('storage'), thumbPath.replace('/content/', '')))
fullImagePath = os.path.join(_cfg('storage'),
self.background.replace('/content/', ''))
if not os.path.exists(fullThumbPath):
thumbnail.create(fullImagePath, fullThumbPath, thumbnailSize)
return thumbPath
def default_version(self):
versions = [
v for v in self.versions if v.id == self.default_version_id
]
if len(versions) == 0:
return None
return versions[0]
def __init__(self):
self.created = datetime.now()
self.updated = datetime.now()
self.approved = False
self.published = False
self.votes = 0
self.follower_count = 0
self.download_count = 0
def __repr__(self):
return '<Mod %r %r>' % (self.id, self.name)
def test_render_literal_bool(self):
self._literal_round_trip(Boolean(), [True, False], [True, False])
def define_tables(meta):
services = Table(
'services', meta,
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('deleted', Boolean),
Column('id', Integer, primary_key=True, nullable=False),
Column('host', String(255)),
Column('binary', String(255)),
Column('topic', String(255)),
Column('report_count', Integer, nullable=False),
Column('disabled', Boolean),
Column('availability_zone', String(255)),
Column('disabled_reason', String(255)),
Column('modified_at', DateTime(timezone=False)),
mysql_engine='InnoDB'
)
consistencygroups = Table(
'consistencygroups', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True, name=None)),
Column('id', String(36), primary_key=True, nullable=False),
Column('user_id', String(255)),
Column('project_id', String(255)),
Column('host', String(255)),
Column('availability_zone', String(255)),
Column('name', String(255)),
Column('description', String(255)),
Column('volume_type_id', String(255)),
Column('status', String(255)),
Column('cgsnapshot_id', String(36)),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
cgsnapshots = Table(
'cgsnapshots', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True, name=None)),
Column('id', String(36), primary_key=True, nullable=False),
Column('consistencygroup_id', String(36),
ForeignKey('consistencygroups.id'),
nullable=False),
Column('user_id', String(255)),
Column('project_id', String(255)),
Column('name', String(255)),
Column('description', String(255)),
Column('status', String(255)),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
volumes = Table(
'volumes', meta,
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('deleted', Boolean),
Column('id', String(36), primary_key=True, nullable=False),
Column('ec2_id', String(255)),
Column('user_id', String(255)),
Column('project_id', String(255)),
Column('host', String(255)),
Column('size', Integer),
Column('availability_zone', String(255)),
Column('status', String(255)),
Column('attach_status', String(255)),
Column('scheduled_at', DateTime),
Column('launched_at', DateTime),
Column('terminated_at', DateTime),
Column('display_name', String(255)),
Column('display_description', String(255)),
Column('provider_location', String(256)),
Column('provider_auth', String(256)),
Column('snapshot_id', String(36)),
Column('volume_type_id', String(36)),
Column('source_volid', String(36)),
Column('bootable', Boolean),
Column('provider_geometry', String(255)),
Column('_name_id', String(36)),
Column('encryption_key_id', String(36)),
Column('migration_status', String(255)),
Column('replication_status', String(255)),
Column('replication_extended_status', String(255)),
Column('replication_driver_data', String(255)),
Column('consistencygroup_id', String(36),
ForeignKey('consistencygroups.id')),
Column('provider_id', String(255)),
Column('multiattach', Boolean),
mysql_engine='InnoDB'
)
volume_attachment = Table(
'volume_attachment', meta,
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('deleted', Boolean),
Column('id', String(36), primary_key=True, nullable=False),
Column('volume_id', String(36), ForeignKey('volumes.id'),
nullable=False),
Column('attached_host', String(255)),
Column('instance_uuid', String(36)),
Column('mountpoint', String(255)),
Column('attach_time', DateTime),
Column('detach_time', DateTime),
Column('attach_mode', String(36)),
Column('attach_status', String(255)),
mysql_engine='InnoDB'
)
snapshots = Table(
'snapshots', meta,
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('deleted', Boolean),
Column('id', String(36), primary_key=True, nullable=False),
Column('volume_id', String(36),
ForeignKey('volumes.id', name='snapshots_volume_id_fkey'),
nullable=False),
Column('user_id', String(255)),
Column('project_id', String(255)),
Column('status', String(255)),
Column('progress', String(255)),
Column('volume_size', Integer),
Column('scheduled_at', DateTime),
Column('display_name', String(255)),
Column('display_description', String(255)),
Column('provider_location', String(255)),
Column('encryption_key_id', String(36)),
Column('volume_type_id', String(36)),
Column('cgsnapshot_id', String(36),
ForeignKey('cgsnapshots.id')),
Column('provider_id', String(255)),
mysql_engine='InnoDB'
)
snapshot_metadata = Table(
'snapshot_metadata', meta,
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('deleted', Boolean),
Column('id', Integer, primary_key=True, nullable=False),
Column('snapshot_id', String(36), ForeignKey('snapshots.id'),
nullable=False),
Column('key', String(255)),
Column('value', String(255)),
mysql_engine='InnoDB'
)
quality_of_service_specs = Table(
'quality_of_service_specs', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True, name=None)),
Column('id', String(36), primary_key=True, nullable=False),
Column('specs_id', String(36),
ForeignKey('quality_of_service_specs.id')),
Column('key', String(255)),
Column('value', String(255)),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
volume_types = Table(
'volume_types', meta,
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('deleted', Boolean),
Column('id', String(36), primary_key=True, nullable=False),
Column('name', String(255)),
Column('qos_specs_id', String(36),
ForeignKey('quality_of_service_specs.id')),
Column('is_public', Boolean),
Column('description', String(255)),
mysql_engine='InnoDB'
)
volume_type_projects = Table(
'volume_type_projects', meta,
Column('id', Integer, primary_key=True, nullable=False),
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('volume_type_id', String(36),
ForeignKey('volume_types.id')),
Column('project_id', String(255)),
Column('deleted', Boolean(create_constraint=True, name=None)),
UniqueConstraint('volume_type_id', 'project_id', 'deleted'),
mysql_engine='InnoDB',
)
volume_metadata = Table(
'volume_metadata', meta,
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('deleted', Boolean),
Column('id', Integer, primary_key=True, nullable=False),
Column('volume_id', String(36), ForeignKey('volumes.id'),
nullable=False),
Column('key', String(255)),
Column('value', String(255)),
mysql_engine='InnoDB'
)
volume_type_extra_specs = Table(
'volume_type_extra_specs', meta,
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('deleted', Boolean),
Column('id', Integer, primary_key=True, nullable=False),
Column('volume_type_id', String(36),
ForeignKey('volume_types.id',
name='volume_type_extra_specs_ibfk_1'),
nullable=False),
Column('key', String(255)),
Column('value', String(255)),
mysql_engine='InnoDB'
)
quotas = Table(
'quotas', meta,
Column('id', Integer, primary_key=True, nullable=False),
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('deleted', Boolean),
Column('project_id', String(255)),
Column('resource', String(255), nullable=False),
Column('hard_limit', Integer),
mysql_engine='InnoDB'
)
iscsi_targets = Table(
'iscsi_targets', meta,
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('deleted', Boolean),
Column('id', Integer, primary_key=True, nullable=False),
Column('target_num', Integer),
Column('host', String(255)),
Column('volume_id', String(36), ForeignKey('volumes.id'),
nullable=True),
mysql_engine='InnoDB'
)
quota_classes = Table(
'quota_classes', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True,
name=None)),
Column('id', Integer(), primary_key=True),
Column('class_name', String(255), index=True),
Column('resource', String(255)),
Column('hard_limit', Integer(), nullable=True),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
quota_usages = Table(
'quota_usages', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True,
name=None)),
Column('id', Integer(), primary_key=True),
Column('project_id', String(255), index=True),
Column('resource', String(255)),
Column('in_use', Integer(), nullable=False),
Column('reserved', Integer(), nullable=False),
Column('until_refresh', Integer(), nullable=True),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
reservations = Table(
'reservations', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True,
name=None)),
Column('id', Integer(), primary_key=True),
Column('uuid', String(36), nullable=False),
Column('usage_id',
Integer(),
ForeignKey('quota_usages.id'),
nullable=False),
Column('project_id', String(255), index=True),
Column('resource', String(255)),
Column('delta', Integer(), nullable=False),
Column('expire', DateTime(timezone=False)),
Index('reservations_deleted_expire_idx',
'deleted', 'expire'),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
volume_glance_metadata = Table(
'volume_glance_metadata',
meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True, name=None)),
Column('id', Integer(), primary_key=True, nullable=False),
Column('volume_id', String(36), ForeignKey('volumes.id')),
Column('snapshot_id', String(36),
ForeignKey('snapshots.id')),
Column('key', String(255)),
Column('value', Text),
mysql_engine='InnoDB'
)
backups = Table(
'backups', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True, name=None)),
Column('id', String(36), primary_key=True, nullable=False),
Column('volume_id', String(36), nullable=False),
Column('user_id', String(255)),
Column('project_id', String(255)),
Column('host', String(255)),
Column('availability_zone', String(255)),
Column('display_name', String(255)),
Column('display_description', String(255)),
Column('container', String(255)),
Column('status', String(255)),
Column('fail_reason', String(255)),
Column('service_metadata', String(255)),
Column('service', String(255)),
Column('size', Integer()),
Column('object_count', Integer()),
Column('parent_id', String(36)),
mysql_engine='InnoDB'
)
transfers = Table(
'transfers', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean),
Column('id', String(36), primary_key=True, nullable=False),
Column('volume_id', String(36), ForeignKey('volumes.id'),
nullable=False),
Column('display_name', String(255)),
Column('salt', String(255)),
Column('crypt_hash', String(255)),
Column('expires_at', DateTime(timezone=False)),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
# Sqlite needs to handle nullable differently
is_nullable = (meta.bind.name == 'sqlite')
encryption = Table(
'encryption', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True, name=None)),
Column('cipher', String(255)),
Column('control_location', String(255), nullable=is_nullable),
Column('key_size', Integer),
Column('provider', String(255), nullable=is_nullable),
# NOTE(joel-coffman): The volume_type_id must be unique or else the
# referenced volume type becomes ambiguous. That is, specifying the
# volume type is not sufficient to identify a particular encryption
# scheme unless each volume type is associated with at most one
# encryption scheme.
Column('volume_type_id', String(36), nullable=is_nullable),
# NOTE (smcginnis): nullable=True triggers this to not set a default
# value, but since it's a primary key the resulting schema will end up
# still being NOT NULL. This is avoiding a case in MySQL where it will
# otherwise set this to NOT NULL DEFAULT ''. May be harmless, but
# inconsistent with previous schema.
Column('encryption_id', String(36), primary_key=True, nullable=True),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
volume_admin_metadata = Table(
'volume_admin_metadata', meta,
Column('created_at', DateTime),
Column('updated_at', DateTime),
Column('deleted_at', DateTime),
Column('deleted', Boolean),
Column('id', Integer, primary_key=True, nullable=False),
Column('volume_id', String(36), ForeignKey('volumes.id'),
nullable=False),
Column('key', String(255)),
Column('value', String(255)),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
initiator_data = Table(
'driver_initiator_data', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('id', Integer, primary_key=True, nullable=False),
Column('initiator', String(255), index=True, nullable=False),
Column('namespace', String(255), nullable=False),
Column('key', String(255), nullable=False),
Column('value', String(255)),
UniqueConstraint('initiator', 'namespace', 'key'),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
return [consistencygroups,
cgsnapshots,
volumes,
volume_attachment,
snapshots,
snapshot_metadata,
quality_of_service_specs,
volume_types,
volume_type_projects,
iscsi_targets,
quotas,
services,
volume_metadata,
volume_type_extra_specs,
quota_classes,
quota_usages,
reservations,
volume_glance_metadata,
backups,
transfers,
encryption,
volume_admin_metadata,
initiator_data]
class DataTypes(Base):
__tablename__ = 'datatypes'
id = Column(Integer, primary_key=True)
name = Column(String(50))
unit = Column(String(50))
isexception = Column(Boolean())
def test_boolean_with_postgres(self, registry_plugin):
report = MigrationReport(registry_plugin.migration, [])
res = report.init_modify_type(
[None, None, 'test', 'other', {},
Integer(), Boolean()])
assert res is False
class Instance(BASE, NovaBase):
"""Represents a guest VM."""
__tablename__ = 'instances'
__table_args__ = (
Index('instances_host_deleted_idx',
'host', 'deleted'),
Index('instances_reservation_id_idx',
'reservation_id'),
Index('instances_terminated_at_launched_at_idx',
'terminated_at', 'launched_at'),
Index('instances_uuid_deleted_idx',
'uuid', 'deleted'),
Index('instances_task_state_updated_at_idx',
'task_state', 'updated_at'),
Index('instances_host_node_deleted_idx',
'host', 'node', 'deleted')
)
injected_files = []
id = Column(Integer, primary_key=True, autoincrement=True)
@property
def name(self):
try:
base_name = CONF.instance_name_template % self.id
except TypeError:
# Support templates like "uuid-%(uuid)s", etc.
info = {}
# NOTE(russellb): Don't use self.iteritems() here, as it will
# result in infinite recursion on the name property.
for column in iter(object_mapper(self).columns):
key = column.name
# prevent recursion if someone specifies %(name)s
# %(name)s will not be valid.
if key == 'name':
continue
info[key] = self[key]
try:
base_name = CONF.instance_name_template % info
except KeyError:
base_name = self.uuid
return base_name
def _extra_keys(self):
return ['name']
user_id = Column(String(255), nullable=True)
project_id = Column(String(255), nullable=True)
image_ref = Column(String(255), nullable=True)
kernel_id = Column(String(255), nullable=True)
ramdisk_id = Column(String(255), nullable=True)
hostname = Column(String(255), nullable=True)
launch_index = Column(Integer, nullable=True)
key_name = Column(String(255), nullable=True)
key_data = Column(Text)
power_state = Column(Integer, nullable=True)
vm_state = Column(String(255), nullable=True)
task_state = Column(String(255), nullable=True)
memory_mb = Column(Integer, nullable=True)
vcpus = Column(Integer, nullable=True)
root_gb = Column(Integer, nullable=True)
ephemeral_gb = Column(Integer, nullable=True)
# This is not related to hostname, above. It refers
# to the nova node.
host = Column(String(255), nullable=True) # , ForeignKey('hosts.id'))
# To identify the "ComputeNode" which the instance resides in.
# This equals to ComputeNode.hypervisor_hostname.
node = Column(String(255), nullable=True)
# *not* flavor_id
instance_type_id = Column(Integer, nullable=True)
user_data = Column(Text, nullable=True)
reservation_id = Column(String(255), nullable=True)
scheduled_at = Column(DateTime, nullable=True)
launched_at = Column(DateTime, nullable=True)
terminated_at = Column(DateTime, nullable=True)
availability_zone = Column(String(255), nullable=True)
# User editable field for display in user-facing UIs
display_name = Column(String(255), nullable=True)
display_description = Column(String(255), nullable=True)
# To remember on which host an instance booted.
# An instance may have moved to another host by live migration.
launched_on = Column(Text, nullable=True)
locked = Column(Boolean, nullable=True)
os_type = Column(String(255), nullable=True)
architecture = Column(String(255), nullable=True)
vm_mode = Column(String(255), nullable=True)
uuid = Column(String(36), unique=True)
root_device_name = Column(String(255), nullable=True)
default_ephemeral_device = Column(String(255), nullable=True)
default_swap_device = Column(String(255), nullable=True)
config_drive = Column(String(255), nullable=True)
# User editable field meant to represent what ip should be used
# to connect to the instance
access_ip_v4 = Column(types.IPAddress(), nullable=True)
access_ip_v6 = Column(types.IPAddress(), nullable=True)
auto_disk_config = Column(Boolean(), nullable=True)
progress = Column(Integer, nullable=True)
# EC2 instance_initiated_shutdown_terminate
# True: -> 'terminate'
# False: -> 'stop'
# Note(maoy): currently Nova will always stop instead of terminate
# no matter what the flag says. So we set the default to False.
shutdown_terminate = Column(Boolean(), default=False, nullable=True)
# EC2 disable_api_termination
disable_terminate = Column(Boolean(), default=False, nullable=True)
# OpenStack compute cell name. This will only be set at the top of
# the cells tree and it'll be a full cell name such as 'api!hop1!hop2'
cell_name = Column(String(255), nullable=True)
engine = create_engine('sqlite:///:memory:')
metadata = Metadata()
# Reflect census table from the engine: census
census = Table('census', metadata, autoload=True, autoload_with=engine)
connection = engine.connect()
#----------------------------------------------------------------------------------------------------
# Import Table, Column, String, Integer, Float, Boolean from sqlalchemy
from sqlalchemy import Table, Column, String, Integer, Float, Boolean
# Define a new table with a name, count, amount, and valid column: data
data = Table('data', metadata, Column('name', String(255)),
Column('count', Integer()), Column('amount', Float()),
Column('valid', Boolean()))
# Use the metadata to create the table
metadata.create_all(engine)
# Print table details
print(repr(data))
# Define a new table with a name, count, amount, and valid column: data
data = Table('data', metadata, Column('name', String(255), unique=True),
Column('count', Integer(), default=1), Column('amount', Float()),
Column('valid', Boolean(), default=False))
# Use the metadata to create the table
metadata.create_all(engine)
class User(db.Model, UserMixin):
__tablename__ = "users"
id: int = Column(Integer,
server_default=FetchedValue(),
primary_key=True,
unique=True,
nullable=False)
email: str = Column(VARCHAR(255), unique=True)
first_name: str = Column(VARCHAR(255), nullable=False)
password: str = Column(VARCHAR(255), nullable=False)
confirmed_at: datetime = Column(DateTime())
active: bool = Column(Boolean())
birthday: datetime = Column(DateTime())
language: str = Column(VARCHAR(5), default="en", nullable=False)
uuid: str = Column(UUID,
nullable=False,
server_default=FetchedValue(),
unique=True)
roles: List[Role] = relationship(Role,
secondary="roles_users",
backref=backref("User", lazy="dynamic"))
families: List[Family] = relationship("Family",
secondary="users_families",
backref=backref("User",
lazy="dynamic"))
@property
def last_login_ip(self):
try:
return AuditEvent.query.filter(
and_(
AuditEvent.type_id == audit_event_type_to_id["last_login"],
AuditEvent.user_id == self.id)).order_by("when").limit(
1).one().ip
except Exception:
return ""
@last_login_ip.setter
def last_login_ip(self, value):
AuditEvent(audit_event_type_to_id["last_login"], self.id, value)
self.last_login_ip = value
@property
def last_login_at(self):
try:
return AuditEvent.query.filter(
and_(
AuditEvent.type_id == audit_event_type_to_id["last_login"],
AuditEvent.user_id == self.id)).order_by("when").limit(
1).one().when
except Exception as e:
sentry_sdk.capture_exception(e)
return ""
@last_login_at.setter
def last_login_at(self, value):
# This is already set when last_login_ip is set
return None
@property
def current_login_at(self):
# TODO:
return
@current_login_at.setter
def current_login_at(self, value):
# TODO:
self.current_login_at = value
@property
def current_login_ip(self):
# TODO:
return
@current_login_ip.setter
def current_login_ip(self, value):
# TODO:
self.current_login_ip = value
def __init__(self, email, username, password, active=False):
self.email = email
self.username = username
self.password = password
self.active = active
class Machine(Base):
"""Configured virtual machines to be used as guests."""
__tablename__ = "machines"
id = Column(Integer(), primary_key=True)
name = Column(String(255), nullable=False)
label = Column(String(255), nullable=False)
ip = Column(String(255), nullable=False)
platform = Column(String(255), nullable=False)
tags = relationship("Tag", secondary=machines_tags, single_parent=True,
backref="machine")
options = Column(String(255), nullable=True)
interface = Column(String(255), nullable=True)
snapshot = Column(String(255), nullable=True)
locked = Column(Boolean(), nullable=False, default=False)
locked_changed_on = Column(DateTime(timezone=False), nullable=True)
status = Column(String(255), nullable=True)
status_changed_on = Column(DateTime(timezone=False), nullable=True)
resultserver_ip = Column(String(255), nullable=False)
resultserver_port = Column(String(255), nullable=False)
def __repr__(self):
return "<Machine('{0}','{1}')>".format(self.id, self.name)
def to_dict(self):
"""Converts object to dict.
@return: dict
"""
d = {}
for column in self.__table__.columns:
value = getattr(self, column.name)
if isinstance(value, datetime):
d[column.name] = value.strftime("%Y-%m-%d %H:%M:%S")
else:
d[column.name] = value
# Tags are a relation so no column to iterate.
d["tags"] = [tag.name for tag in self.tags]
return d
def to_json(self):
"""Converts object to JSON.
@return: JSON data
"""
return json.dumps(self.to_dict())
def is_analysis(self):
"""Is this an analysis machine? Generally speaking all machines are
analysis machines, however, this is not the case for service VMs.
Please refer to the services auxiliary module."""
for tag in self.tags:
if tag.name == "service":
return
return True
def __init__(self, name, label, ip, platform, options, interface,
snapshot, resultserver_ip, resultserver_port):
self.name = name
self.label = label
self.ip = ip
self.platform = platform
self.options = options
self.interface = interface
self.snapshot = snapshot
self.resultserver_ip = resultserver_ip
self.resultserver_port = resultserver_port
class TypeCompileTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = mysql.dialect()
@testing.combinations(
# column type, args, kwargs, expected ddl
# e.g. Column(Integer(10, unsigned=True)) ==
# 'INTEGER(10) UNSIGNED'
(mysql.MSNumeric, [], {}, "NUMERIC"),
(mysql.MSNumeric, [None], {}, "NUMERIC"),
(mysql.MSNumeric, [12], {}, "NUMERIC(12)"),
(
mysql.MSNumeric,
[12, 4],
{
"unsigned": True
},
"NUMERIC(12, 4) UNSIGNED",
),
(
mysql.MSNumeric,
[12, 4],
{
"zerofill": True
},
"NUMERIC(12, 4) ZEROFILL",
),
(
mysql.MSNumeric,
[12, 4],
{
"zerofill": True,
"unsigned": True
},
"NUMERIC(12, 4) UNSIGNED ZEROFILL",
),
(mysql.MSDecimal, [], {}, "DECIMAL"),
(mysql.MSDecimal, [None], {}, "DECIMAL"),
(mysql.MSDecimal, [12], {}, "DECIMAL(12)"),
(mysql.MSDecimal, [12, None], {}, "DECIMAL(12)"),
(
mysql.MSDecimal,
[12, 4],
{
"unsigned": True
},
"DECIMAL(12, 4) UNSIGNED",
),
(
mysql.MSDecimal,
[12, 4],
{
"zerofill": True
},
"DECIMAL(12, 4) ZEROFILL",
),
(
mysql.MSDecimal,
[12, 4],
{
"zerofill": True,
"unsigned": True
},
"DECIMAL(12, 4) UNSIGNED ZEROFILL",
),
(mysql.MSDouble, [None, None], {}, "DOUBLE"),
(
mysql.MSDouble,
[12, 4],
{
"unsigned": True
},
"DOUBLE(12, 4) UNSIGNED",
),
(
mysql.MSDouble,
[12, 4],
{
"zerofill": True
},
"DOUBLE(12, 4) ZEROFILL",
),
(
mysql.MSDouble,
[12, 4],
{
"zerofill": True,
"unsigned": True
},
"DOUBLE(12, 4) UNSIGNED ZEROFILL",
),
(mysql.MSReal, [None, None], {}, "REAL"),
(mysql.MSReal, [12, 4], {
"unsigned": True
}, "REAL(12, 4) UNSIGNED"),
(mysql.MSReal, [12, 4], {
"zerofill": True
}, "REAL(12, 4) ZEROFILL"),
(
mysql.MSReal,
[12, 4],
{
"zerofill": True,
"unsigned": True
},
"REAL(12, 4) UNSIGNED ZEROFILL",
),
(mysql.MSFloat, [], {}, "FLOAT"),
(mysql.MSFloat, [None], {}, "FLOAT"),
(mysql.MSFloat, [12], {}, "FLOAT(12)"),
(mysql.MSFloat, [12, 4], {}, "FLOAT(12, 4)"),
(mysql.MSFloat, [12, 4], {
"unsigned": True
}, "FLOAT(12, 4) UNSIGNED"),
(mysql.MSFloat, [12, 4], {
"zerofill": True
}, "FLOAT(12, 4) ZEROFILL"),
(
mysql.MSFloat,
[12, 4],
{
"zerofill": True,
"unsigned": True
},
"FLOAT(12, 4) UNSIGNED ZEROFILL",
),
(mysql.MSInteger, [], {}, "INTEGER"),
(mysql.MSInteger, [4], {}, "INTEGER(4)"),
(mysql.MSInteger, [4], {
"unsigned": True
}, "INTEGER(4) UNSIGNED"),
(mysql.MSInteger, [4], {
"zerofill": True
}, "INTEGER(4) ZEROFILL"),
(
mysql.MSInteger,
[4],
{
"zerofill": True,
"unsigned": True
},
"INTEGER(4) UNSIGNED ZEROFILL",
),
(mysql.MSBigInteger, [], {}, "BIGINT"),
(mysql.MSBigInteger, [4], {}, "BIGINT(4)"),
(mysql.MSBigInteger, [4], {
"unsigned": True
}, "BIGINT(4) UNSIGNED"),
(mysql.MSBigInteger, [4], {
"zerofill": True
}, "BIGINT(4) ZEROFILL"),
(
mysql.MSBigInteger,
[4],
{
"zerofill": True,
"unsigned": True
},
"BIGINT(4) UNSIGNED ZEROFILL",
),
(mysql.MSMediumInteger, [], {}, "MEDIUMINT"),
(mysql.MSMediumInteger, [4], {}, "MEDIUMINT(4)"),
(
mysql.MSMediumInteger,
[4],
{
"unsigned": True
},
"MEDIUMINT(4) UNSIGNED",
),
(
mysql.MSMediumInteger,
[4],
{
"zerofill": True
},
"MEDIUMINT(4) ZEROFILL",
),
(
mysql.MSMediumInteger,
[4],
{
"zerofill": True,
"unsigned": True
},
"MEDIUMINT(4) UNSIGNED ZEROFILL",
),
(mysql.MSTinyInteger, [], {}, "TINYINT"),
(mysql.MSTinyInteger, [1], {}, "TINYINT(1)"),
(mysql.MSTinyInteger, [1], {
"unsigned": True
}, "TINYINT(1) UNSIGNED"),
(mysql.MSTinyInteger, [1], {
"zerofill": True
}, "TINYINT(1) ZEROFILL"),
(
mysql.MSTinyInteger,
[1],
{
"zerofill": True,
"unsigned": True
},
"TINYINT(1) UNSIGNED ZEROFILL",
),
(mysql.MSSmallInteger, [], {}, "SMALLINT"),
(mysql.MSSmallInteger, [4], {}, "SMALLINT(4)"),
(
mysql.MSSmallInteger,
[4],
{
"unsigned": True
},
"SMALLINT(4) UNSIGNED",
),
(
mysql.MSSmallInteger,
[4],
{
"zerofill": True
},
"SMALLINT(4) ZEROFILL",
),
(
mysql.MSSmallInteger,
[4],
{
"zerofill": True,
"unsigned": True
},
"SMALLINT(4) UNSIGNED ZEROFILL",
),
)
def test_numeric(self, type_, args, kw, res):
"Exercise type specification and options for numeric types."
type_inst = type_(*args, **kw)
self.assert_compile(type_inst, res)
# test that repr() copies out all arguments
self.assert_compile(eval("mysql.%r" % type_inst), res)
@testing.combinations(
(mysql.MSChar, [1], {}, "CHAR(1)"),
(mysql.NCHAR, [1], {}, "NATIONAL CHAR(1)"),
(mysql.MSChar, [1], {
"binary": True
}, "CHAR(1) BINARY"),
(mysql.MSChar, [1], {
"ascii": True
}, "CHAR(1) ASCII"),
(mysql.MSChar, [1], {
"unicode": True
}, "CHAR(1) UNICODE"),
(
mysql.MSChar,
[1],
{
"ascii": True,
"binary": True
},
"CHAR(1) ASCII BINARY",
),
(
mysql.MSChar,
[1],
{
"unicode": True,
"binary": True
},
"CHAR(1) UNICODE BINARY",
),
(mysql.MSChar, [1], {
"charset": "utf8"
}, "CHAR(1) CHARACTER SET utf8"),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"binary": True
},
"CHAR(1) CHARACTER SET utf8 BINARY",
),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"unicode": True
},
"CHAR(1) CHARACTER SET utf8",
),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"ascii": True
},
"CHAR(1) CHARACTER SET utf8",
),
(
mysql.MSChar,
[1],
{
"collation": "utf8_bin"
},
"CHAR(1) COLLATE utf8_bin",
),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"collation": "utf8_bin"
},
"CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"binary": True
},
"CHAR(1) CHARACTER SET utf8 BINARY",
),
(
mysql.MSChar,
[1],
{
"charset": "utf8",
"collation": "utf8_bin",
"binary": True
},
"CHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
),
(mysql.MSChar, [1], {
"national": True
}, "NATIONAL CHAR(1)"),
(
mysql.MSChar,
[1],
{
"national": True,
"charset": "utf8"
},
"NATIONAL CHAR(1)",
),
(
mysql.MSChar,
[1],
{
"national": True,
"charset": "utf8",
"binary": True
},
"NATIONAL CHAR(1) BINARY",
),
(
mysql.MSChar,
[1],
{
"national": True,
"binary": True,
"unicode": True
},
"NATIONAL CHAR(1) BINARY",
),
(
mysql.MSChar,
[1],
{
"national": True,
"collation": "utf8_bin"
},
"NATIONAL CHAR(1) COLLATE utf8_bin",
),
(
mysql.MSString,
[1],
{
"charset": "utf8",
"collation": "utf8_bin"
},
"VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_bin",
),
(
mysql.MSString,
[1],
{
"national": True,
"collation": "utf8_bin"
},
"NATIONAL VARCHAR(1) COLLATE utf8_bin",
),
(
mysql.MSTinyText,
[],
{
"charset": "utf8",
"collation": "utf8_bin"
},
"TINYTEXT CHARACTER SET utf8 COLLATE utf8_bin",
),
(
mysql.MSMediumText,
[],
{
"charset": "utf8",
"binary": True
},
"MEDIUMTEXT CHARACTER SET utf8 BINARY",
),
(mysql.MSLongText, [], {
"ascii": True
}, "LONGTEXT ASCII"),
(
mysql.ENUM,
["foo", "bar"],
{
"unicode": True
},
"""ENUM('foo','bar') UNICODE""",
),
(String, [20], {
"collation": "utf8"
}, "VARCHAR(20) COLLATE utf8"),
)
@testing.exclude("mysql", "<", (4, 1, 1), "no charset support")
def test_charset(self, type_, args, kw, res):
"""Exercise CHARACTER SET and COLLATE-ish options on string types."""
type_inst = type_(*args, **kw)
self.assert_compile(type_inst, res)
@testing.combinations(
(mysql.MSBit(), "BIT"),
(mysql.MSBit(1), "BIT(1)"),
(mysql.MSBit(63), "BIT(63)"),
)
def test_bit_50(self, type_, expected):
"""Exercise BIT types on 5.0+ (not valid for all engine types)"""
self.assert_compile(type_, expected)
@testing.combinations(
(BOOLEAN(), "BOOL"),
(Boolean(), "BOOL"),
(mysql.TINYINT(1), "TINYINT(1)"),
(mysql.TINYINT(1, unsigned=True), "TINYINT(1) UNSIGNED"),
)
def test_boolean_compile(self, type_, expected):
self.assert_compile(type_, expected)
def test_timestamp_fsp(self):
self.assert_compile(mysql.TIMESTAMP(fsp=5), "TIMESTAMP(5)")
@testing.combinations(
([TIMESTAMP], {}, "TIMESTAMP NULL"),
([mysql.MSTimeStamp], {}, "TIMESTAMP NULL"),
(
[
mysql.MSTimeStamp(),
DefaultClause(sql.text("CURRENT_TIMESTAMP")),
],
{},
"TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP",
),
(
[mysql.MSTimeStamp,
DefaultClause(sql.text("CURRENT_TIMESTAMP"))],
{
"nullable": False
},
"TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp,
DefaultClause(sql.text("'1999-09-09 09:09:09'")),
],
{
"nullable": False
},
"TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09'",
),
(
[
mysql.MSTimeStamp(),
DefaultClause(sql.text("'1999-09-09 09:09:09'")),
],
{},
"TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09'",
),
(
[
mysql.MSTimeStamp(),
DefaultClause(
sql.text("'1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP")),
],
{},
"TIMESTAMP NULL DEFAULT '1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp,
DefaultClause(
sql.text("'1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP")),
],
{
"nullable": False
},
"TIMESTAMP NOT NULL DEFAULT '1999-09-09 09:09:09' "
"ON UPDATE CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp(),
DefaultClause(
sql.text("CURRENT_TIMESTAMP "
"ON UPDATE CURRENT_TIMESTAMP")),
],
{},
"TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP "
"ON UPDATE CURRENT_TIMESTAMP",
),
(
[
mysql.MSTimeStamp,
DefaultClause(
sql.text("CURRENT_TIMESTAMP "
"ON UPDATE CURRENT_TIMESTAMP")),
],
{
"nullable": False
},
"TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP "
"ON UPDATE CURRENT_TIMESTAMP",
),
)
def test_timestamp_defaults(self, spec, kw, expected):
"""Exercise funky TIMESTAMP default syntax when used in columns."""
c = Column("t", *spec, **kw)
Table("t", MetaData(), c)
self.assert_compile(schema.CreateColumn(c), "t %s" % expected)
def test_datetime_generic(self):
self.assert_compile(mysql.DATETIME(), "DATETIME")
def test_datetime_fsp(self):
self.assert_compile(mysql.DATETIME(fsp=4), "DATETIME(4)")
def test_time_generic(self):
"""Exercise TIME."""
self.assert_compile(mysql.TIME(), "TIME")
def test_time_fsp(self):
self.assert_compile(mysql.TIME(fsp=5), "TIME(5)")
def test_time_result_processor(self):
eq_(
mysql.TIME().result_processor(None, None)(datetime.timedelta(
seconds=35, minutes=517, microseconds=450)),
datetime.time(8, 37, 35, 450),
)
class ClienteLep(db.Model):
__tablename__ = 'cliente_lep'
id = Column(Integer(), primary_key=True, autoincrement=True)
cliente_id = Column(Integer(), nullable=False)
lep_id = Column(Integer(), nullable=False)
activo = Column(Boolean(True), nullable=False)
class FeedbackForm(Base):
"""A form is generated for every employee-to-employee feedback entry.
Another form is also generated when a manager summarises for their
subordinate.
"""
__tablename__ = "forms"
id = Column(Integer, FORM_ID_SEQ, primary_key=True)
to_username = Column(Unicode(length=32))
to_user = relationship(
"User",
foreign_keys=[to_username],
primaryjoin="User.username == " "FeedbackForm.to_username",
backref="received_forms",
)
from_username = Column(Unicode(length=32))
from_user = relationship(
"User",
foreign_keys=[from_username],
primaryjoin="User.username == " "FeedbackForm.from_username",
backref="contributed_forms",
)
period_id = Column(Integer, ForeignKey("periods.id"))
period = relationship("Period")
# must be joined loaded to ensure __repr__ can be called anytime without
# db fetch or transaction error
answers = relationship(
"FeedbackAnswer",
back_populates="form",
lazy="joined",
cascade="all, delete-orphan",
)
is_summary = Column(Boolean(name="b_is_summary"), default=False)
is_draft = Column(Boolean(name="b_is_draft"), default=False)
approved_by_username = Column(Unicode(length=32), nullable=True)
__table_args__ = (
CheckConstraint("from_username != to_username", name="from_neq_to"),
CheckConstraint(
"from_username != approved_by_username", name="from_neq_approved"
),
CheckConstraint("to_username != approved_by_username", name="to_neq_approved"),
)
def __repr__(self):
return "FeedbackForm(period_id=%s, answer_ids=%s)" % (
self.period_id,
", ".join([str(a.id) for a in self.answers]),
)
def check_validity(self, session):
"""Once a single summary is made for any given period, individual
feedback cannot be contributed."""
existing_summary = (
session.query(FeedbackForm)
.filter(
FeedbackForm.id != self.id,
FeedbackForm.to_username == self.to_username,
FeedbackForm.period_id == self.period_id,
FeedbackForm.is_summary == True,
) # noqa
.one_or_none()
)
if existing_summary:
raise CheckError(
"Existing summary form id %s for period id %s "
"created by %s, please delete to add new "
"individual feedback or modify the existing "
"summary."
% (existing_summary.id, self.period_id, existing_summary.from_username)
)
class GroupChat(Base):
__tablename__ = f'{Base.TABLENAME_PREFIX}group_chats'
chat_id = Column(BigInteger, primary_key=True)
name = Column(String(length=255), nullable=False)
is_enabled = Column(Boolean(create_constraint=True), nullable=False)
is_migration_conflicted = Column(
Boolean(create_constraint=True), nullable=False, default=False, server_default=false()
)
last_triggered = Column(UTCDateTime)
last_given_titles_count = Column(Integer)
participants = relationship('Participant', back_populates='chat', lazy='dynamic')
inevitable_titles = relationship('InevitableTitle', back_populates='chat', lazy='dynamic')
shuffled_titles = relationship('ShuffledTitle', back_populates='chat', lazy='dynamic')
@classmethod
def get_by_id(cls, session, chat_id) -> Optional[GroupChat]:
return session.query(GroupChat).filter_by(chat_id=chat_id).one_or_none()
def get_participant(self, user_id) -> Optional[Participant]:
return self.participants.filter_by(user_id=user_id).one_or_none()
def get_participants_ordered(self, ordered_ids: List[int]) -> List[Participant]:
session: Session = Session.object_session(self)
from .participant import Participant
query = session.query(
Participant
).filter(
Participant.id.in_(ordered_ids)
)
participants_map = {p.id: p for p in query.all()}
return [participants_map[i] for i in ordered_ids]
def get_participant_ids(self) -> List[int]:
session: Session = Session.object_session(self)
from .participant import Participant
query = session.query(
Participant.id
).filter(
Participant.is_active,
Participant.chat == self
).order_by(
Participant.id
)
return [i for (i, ) in query]
def dequeue_shuffled_titles(self, limit: int) -> List[ShuffledTitle]:
if not limit:
return []
from . import ShuffledTitle
query = self.shuffled_titles.filter(ShuffledTitle.roll_order.isnot(None))
query = query.order_by(ShuffledTitle.roll_order)
titles: List[ShuffledTitle] = query[:limit]
title_count = len(titles)
session: Session = Session.object_session(self)
old_ids: List[Tuple[int]] = []
if title_count < limit:
query_old = session.query(ShuffledTitle.id).filter_by(chat=self)
query_old = query_old.filter(ShuffledTitle.roll_order.is_(None))
old_ids = query_old.all()
for t in titles:
t.roll_order = None
if old_ids:
new_queue = (
dict(chat_id=self.chat_id, id=i, roll_order=random.randrange(2**16))
for i, in old_ids
)
session.bulk_update_mappings(ShuffledTitle, new_queue)
session.commit()
remainder_limit = limit - title_count
more_titles = query[:remainder_limit]
for t in more_titles:
t.roll_order = None
titles += more_titles
session.commit()
return titles
def __repr__(self) -> str:
return ('<GroupChat('
f'chat_id={self.chat_id}, '
f'name="{self.name}", '
f'is_enabled={self.is_enabled}, '
f'last_triggered="{self.last_triggered}", '
f'last_given_titles_count="{self.last_given_titles_count}"'
')>')
class BaseExpenseLine(DBBASE, PersistentACLMixin):
"""
Base models for expense lines
:param type: Column for polymorphic discrimination
:param date: Date of the expense
:param description: description of the expense
:param code: analytic code related to this expense
:param valid: validation status of the expense
:param sheet_id: id of the expense sheet this expense is related to
"""
__tablename__ = 'baseexpense_line'
__table_args__ = default_table_args
__mapper_args__ = dict(
polymorphic_on="type",
polymorphic_identity="line",
with_polymorphic='*',
)
id = Column(
Integer,
primary_key=True,
info={"colanderalchemy": forms.EXCLUDED},
)
type = Column(
String(30),
nullable=False,
info={'colanderalchemy': forms.EXCLUDED},
)
date = Column(
Date(),
default=datetime.date.today,
info={'colanderalchemy': {
'title': u"Date"
}},
)
description = Column(
String(255),
info={'colanderalchemy': {
'title': u"Description"
}},
default="",
)
category = Column(Enum('1', '2', name='category'), default='1')
valid = Column(Boolean(),
default=True,
info={"colanderalchemy": {
"title": u"Valide ?"
}})
type_id = Column(Integer,
info={
'colanderalchemy': {
'validator':
forms.get_deferred_select_validator(ExpenseType),
'missing':
colander.required,
"title":
u"Type de dépense",
}
})
sheet_id = Column(Integer,
ForeignKey("expense_sheet.id", ondelete="cascade"),
info={'colanderalchemy': forms.EXCLUDED})
type_object = relationship(
"ExpenseType",
primaryjoin='BaseExpenseLine.type_id==ExpenseType.id',
uselist=False,
foreign_keys=type_id,
info={'colanderalchemy': forms.EXCLUDED})
def __json__(self, request):
return dict(
id=self.id,
date=self.date,
description=self.description,
category=self.category,
valid=self.valid,
type_id=self.type_id,
sheet_id=self.sheet_id,
)
Column("comments", JSON, nullable=False),
Column("created_on", DateTime(), default=datetime.now()))
articles_comments = Table(
"articles_comments", metadata,
Column("id", Integer(), primary_key=True, autoincrement=True),
Column("a_comment_id", UUID, nullable=False), Column("articles", UUID),
Column("username", String(8), nullable=False),
Column("email", String(32), nullable=False),
Column("comment", JSON(), nullable=False),
Column("created_on", DateTime(), default=datetime.now))
articles = Table(
"article", metadata,
Column("id", Integer(), primary_key=True, autoincrement=True),
Column("artiles_id", UUID, nullable=False),
Column("name", String(8), nullable=False),
Column("content", Text(), nullable=False),
Column("public", Boolean, default=True),
Column("created_on", DateTime(), default=datetime.now),
Column("updated_on",
DateTime(),
default=datetime.now,
onupdate=datetime.now))
series = Table("series", metadata,
Column("id", Integer(), primary_key=True, autoincrement=True),
Column("name", String(8), nullable=False),
Column("public", Boolean(), default=True),
Column("serie_id", UUID, nullable=False),
Column("chidren", ARRAY(UUID)))
class TrainerDatas(Node):
__tablename__ = "trainer_datas"
__table_args__ = default_table_args
__mapper_args__ = {'polymorphic_identity': 'trainerdata'}
id = Column(ForeignKey('node.id'),
primary_key=True,
info={
'colanderalchemy': {
'exclude': True,
'title': u"Identifiant Autonomie"
},
})
# User account associated with this dataset
user_id = Column(ForeignKey('accounts.id'),
info={
'export': {
'exclude': True
},
})
user = relationship(
"User",
primaryjoin='User.id==TrainerDatas.user_id',
info={
'colanderalchemy':
get_excluded_colanderalchemy(u'Compte utilisateur'),
'export': {
'exclude': True
},
},
)
# Profil professionnel
specialty = Column(Text(),
info={
'colanderalchemy': {
'title': u"Spécialité",
"description":
u"Votre spécialité - Votre cœur de métier, \
champ de compétence et domaines d'expertise (3 lignes au maximum)",
"section": u"Profil Professionnel",
}
})
linkedin = Column(String(255),
info={
'colanderalchemy': {
'title':
u"Réseau Sociaux - Adresse du profil linkedin",
"section": u"Profil Professionnel",
}
})
viadeo = Column(String(255),
info={
'colanderalchemy': {
'title':
u"Réseau Sociaux - Adresse du profil Viadeo",
"section": u"Profil Professionnel",
}
})
career = Column(
Text(),
info={
'colanderalchemy': {
'title':
u"Votre parcours professionnel en 3 dates ou périodes",
"description": u"Par exemple pour date : en 1991 - Par \
exemple pour période : de 1991 à 1995",
"section": u"Profil Professionnel",
}
})
qualifications = Column(
Text(),
info={
'colanderalchemy': {
'title':
u"Votre qualification ou/et diplôme le plus pertinent",
"description": u"2 lignes maximum",
"section": u"Profil Professionnel",
}
})
background = Column(Text(),
info={
'colanderalchemy': {
'title': u"Votre formation de formateur",
"section": u"Profil Professionnel",
}
})
references = Column(
Text(),
info={
'colanderalchemy': {
'title': u"Vos références de missions de formation effectuées",
"description": u"5 références maximum en mentionnant nom du \
client, contexte de l'intervention, année",
"section": u"Profil Professionnel",
}
})
# Section "Concernant votre activité de formation"
motivation = Column(
Text(),
info={
'colanderalchemy': {
'title': u"Quelle est votre motivation, pourquoi faites-vous \
de la formation ?",
"description": u"3 lignes maximum",
"section": u"Concernant votre activité de formation",
}
})
approach = Column(Text(),
info={
'colanderalchemy': {
'title':
u"Concernant votre activité de formation",
"description":
u"3 lignes maximum, ne pas entrer dans la \
méthodologie",
"section":
u"Concernant votre activité de formation",
}
})
# Section: Un petit peu de vous
temperament = Column(
Text(),
info={
'colanderalchemy': {
'title': u"Caractère",
"description":
u"1 à 3 lignes, par ex : sens de la créativité, \
aimer les gens et croire en leur potentiel, aime maîtriser son sujet \
parfaitement ...",
"section": u"Un petit peu de vous",
}
})
indulgence = Column(Text(),
info={
'colanderalchemy': {
'title':
u"Ce qui vous inspire le plus d'indulgence",
"description":
u"1 à 3 lignes, par ex : la peur d'un \
environnement ou d'un outil, les difficultés des personnes à s'exprimer, \
l’échec lié à une prise de risque ...",
"section": u"Un petit peu de vous",
}
})
sound = Column(Text(),
info={
"colanderalchemy": {
"title": u"Le son, le bruit que vous aimez",
"description":
u"1 à 3 lignes, par ex : le café qui coule le \
matin, le son de l'élastique de ma chemise cartonnée qui contient mon \
programme de formation...",
"section": u"Un petit peu de vous",
}
})
object_ = Column(Text(),
info={
"colanderalchemy": {
"title": u"Si vous étiez un objet, vous seriez ?",
"description":
u"1 à 3 lignes, par ex : une agrafeuse pour \
faire du lien, un micro pour écouter, une lampe pour éclairer",
"section": u"Un petit peu de vous",
}
})
active = Column(Boolean(),
info={
"colanderalchemy": {
"title":
u"Fiche active ?",
"description":
u"Cette fiche formateur est-elle active ?",
}
})
class User(Base):
__tablename__ = 'user'
__table_args__ = {
'mysql_charset': 'utf8',
'mysql_engine': 'InnoDB',
}
uid = Column(Integer, primary_key=True, doc=u'用户id')
email = Column(String(128), unique=True, nullable=False, doc=u'邮箱')
password = Column(String(128), nullable=False, doc=u'密码')
firstname = Column(String(16), doc=u'用户姓')
lastname = Column(String(16), doc=u'用户名')
username = column_property(firstname + " " + lastname)
name = Column(String(32), doc=u'昵称')
short_desc = Column(Unicode(256), doc=u'用户标语')
birthday = Column(Time(), doc=u'出生日期')
sex = Column(Boolean(), doc=u'性别, 0男1女', info=u'性别')
qq = Column(String(16), doc=u'qq')
telphone = Column(String(32), doc=u'联系方式，电话')
is_admin = Column(Boolean, default=False, doc=u'是否为管理员')
create_time = Column(Time(), default=datetime.now, doc=u'创建时间')
subjects = relationship('UserSubject', backref='sub_atteners', doc=u'关注话题')
works = relationship('Work', backref="work_owner", doc=u"作品")
sources = relationship('Source', backref="source_owner", doc=u"资源")
articles = relationship('Article', backref='article_owner', doc=u'文章')
questions = relationship('Question', backref='question_owner', doc=u'问题')
answer = relationship('Answer', backref='anwser_owner', doc=u'回答')
attends = relationship('User',
secondary='user_user_attend',
primaryjoin=uid == Useruser.uid,
secondaryjoin=uid == Useruser.attend_uid,
backref='attenders')
def __init__(self, email, password, firstname=None, lastname=None):
self.email = email
self.password = hashlib.md5(password).digest()
self.firstname = firstname
self.lastname = lastname
def __repr__(self):
return 'User:<uid: %s, username:%s, email: %s>' % (
self.uid, self.username, self.email)
@validates('email')
def validate_email(self, key, email):
email_r = '\w+@\w+\.\w+'
if re.match(email_r, email):
return email
else:
raise ValueError("not correct email")
@staticmethod
def login(email, password):
"""
TODO: unittest
return User instance or None
"""
pw = hashlib.md5(password).digest()
user = db_session.query(User).filter(User.email == email).filter(
User.password == pw).first()
return user
@staticmethod
def register(email, password, firstname, lastname):
"""
register new user
TODO: 设计输出, try ..except
"""
user = User(email, password)
user.firstname = firstname
user.lastname = lastname
db_session.add(user).commit()
return user
class Instance(BASE, NovaBase):
"""Represents a guest vm."""
__tablename__ = 'instances'
injected_files = []
id = Column(Integer, primary_key=True, autoincrement=True)
@property
def name(self):
try:
base_name = FLAGS.instance_name_template % self.id
except TypeError:
# Support templates like "uuid-%(uuid)s", etc.
info = {}
for key, value in self.iteritems():
# prevent recursion if someone specifies %(name)s
# %(name)s will not be valid.
if key == 'name':
continue
info[key] = value
try:
base_name = FLAGS.instance_name_template % info
except KeyError:
base_name = self.uuid
if getattr(self, '_rescue', False):
base_name += "-rescue"
return base_name
user_id = Column(String(255))
project_id = Column(String(255))
image_ref = Column(String(255))
kernel_id = Column(String(255))
ramdisk_id = Column(String(255))
server_name = Column(String(255))
# image_ref = Column(Integer, ForeignKey('images.id'), nullable=True)
# kernel_id = Column(Integer, ForeignKey('images.id'), nullable=True)
# ramdisk_id = Column(Integer, ForeignKey('images.id'), nullable=True)
# ramdisk = relationship(Ramdisk, backref=backref('instances', order_by=id))
# kernel = relationship(Kernel, backref=backref('instances', order_by=id))
# project = relationship(Project, backref=backref('instances', order_by=id))
launch_index = Column(Integer)
key_name = Column(String(255))
key_data = Column(Text)
power_state = Column(Integer)
vm_state = Column(String(255))
task_state = Column(String(255))
memory_mb = Column(Integer)
vcpus = Column(Integer)
root_gb = Column(Integer)
ephemeral_gb = Column(Integer)
hostname = Column(String(255))
host = Column(String(255)) # , ForeignKey('hosts.id'))
# *not* flavor_id
instance_type_id = Column(Integer)
user_data = Column(Text)
reservation_id = Column(String(255))
scheduled_at = Column(DateTime)
launched_at = Column(DateTime)
terminated_at = Column(DateTime)
availability_zone = Column(String(255))
# User editable field for display in user-facing UIs
display_name = Column(String(255))
display_description = Column(String(255))
# To remember on which host an instance booted.
# An instance may have moved to another host by live migraiton.
launched_on = Column(Text)
locked = Column(Boolean)
os_type = Column(String(255))
architecture = Column(String(255))
vm_mode = Column(String(255))
uuid = Column(String(36))
root_device_name = Column(String(255))
default_ephemeral_device = Column(String(255), nullable=True)
default_swap_device = Column(String(255), nullable=True)
config_drive = Column(String(255))
# User editable field meant to represent what ip should be used
# to connect to the instance
access_ip_v4 = Column(String(255))
access_ip_v6 = Column(String(255))
auto_disk_config = Column(Boolean())
progress = Column(Integer)
# EC2 instance_initiated_shutdown_teminate
# True: -> 'terminate'
# False: -> 'stop'
shutdown_terminate = Column(Boolean(), default=True, nullable=False)
# EC2 disable_api_termination
disable_terminate = Column(Boolean(), default=False, nullable=False)
index=True),
Column("tokentype", Unicode(30), default=u'HOTP', index=True),
Column("user_pin", Unicode(512), default=u''),
Column("user_pin_iv", Unicode(32), default=u''),
Column("so_pin", Unicode(512), default=u''),
Column("so_pin_iv", Unicode(32), default=u''),
Column("resolver", Unicode(120), default=u'', index=True),
Column("resolver_type", Unicode(120), default=u''),
Column("user_id", Unicode(320), default=u'', index=True),
Column("pin_seed", Unicode(32), default=u''),
Column("otplen", Integer(), default=6),
Column("pin_hash", Unicode(512), default=u''),
Column("key_enc", Unicode(1024), default=u''),
Column("key_iv", Unicode(32), default=u''),
Column("maxfail", Integer(), default=10),
Column("active", Boolean(), nullable=False, default=True),
Column("revoked", Boolean(), default=False),
Column("locked", Boolean(), default=False),
Column("failcount", Integer(), default=0),
Column("count", Integer(), default=0),
Column("count_window", Integer(), default=10),
Column("sync_window", Integer(), default=1000),
Column("rollout_state", Unicode(10), default=u''))
tokeninfo_table = Table("tokeninfo", metadata,
Column("id", Integer, primary_key=True),
Column("Key", Unicode(255), nullable=False),
Column("Value", UnicodeText(), default=u''),
Column("Type", Unicode(100), default=u''),
Column("Description", Unicode(2000), default=u''),
Column("token_id", Integer()))
