class HelpCategory(SAFRSBase, Base):
__tablename__ = "help_category"
help_category_id = Column(SMALLINT(5), primary_key=True)
name = Column(CHAR(64), nullable=False, unique=True)
parent_category_id = Column(SMALLINT(5))
url = Column(Text, nullable=False)
class ConnInfo(Base):
__tablename__ = 'CONN_INFO'
id = Column(name='ID', type_=INTEGER(), primary_key=True)
name = Column(name='NAME', type_=String(256), unique=True, nullable=False)
"""
连接类型
1 Redis
password 密码，host 主机地址，port 端口， db 连接库，path None
2 Redis Cluster
password 密码，host "[{'host':'127.0.0.1','port':6379}]"，port None， db None， path None
3 zookeeper
password None，host '127.0.0.1:2181,127.0.0.1:2181'，port None， db None， path zk路径
"""
type = Column(name='TYPE', type_=SMALLINT(), nullable=False, default=0)
password = Column(name='PWD', type_=String(256), nullable=True)
host = Column(name='HOST', type_=String(1024), nullable=False)
port = Column(name='PORT', type_=INTEGER(), nullable=True)
db = Column(name='DB', type_=SMALLINT(), nullable=True)
path = Column(name='PATH', type_=String(256), nullable=True)
def __init__(self,
id=None,
name=None,
type=None,
password=None,
host=None,
port=None,
db=None,
path=None):
self.id = id
self.name = name
self.type = type
self.password = password
self.host = host
self.port = port
self.db = db
self.path = path
def __repr__(self):
return "<ConnInfo(id='%d',name='%s',password='******',host='%s',port=%s,db=%s,path=%s)>" % (
self.id, self.name, self.password, self.host, self.port, self.db,
self.path)
def as_dict(self):
return {
'id': self.id,
'name': self.name,
'type': self.type,
'password': self.password,
'host': self.host,
'port': self.port,
'db': self.db,
'path': self.path
}
def get_twitter_table(table_name, metadata):
twitter_table = Table(table_name, metadata,
Column('t_id', BIGINT(), primary_key=True),
Column('t_tweets', String(), nullable=False),
Column('t_id', BIGINT(), primary_key=True),
Column('t_tweets', String(), nullable=False),
Column('t_len', SMALLINT(), nullable=False),
Column('t_date', DATE(), nullable=False),
Column('t_source', String(100), nullable=False),
Column('t_likes', SMALLINT(), nullable=False),
Column('t_retweet', SMALLINT(), nullable=False),
Column('t_sentiment', SMALLINT(), nullable=False))
return twitter_table
class Payment(SAFRSBase, Base):
__tablename__ = "payment"
payment_id = Column(SMALLINT(5), primary_key=True)
customer_id = Column(ForeignKey("customer.customer_id",
onupdate="CASCADE"),
nullable=False,
index=True)
staff_id = Column(ForeignKey("staff.staff_id", onupdate="CASCADE"),
nullable=False,
index=True)
rental_id = Column(ForeignKey("rental.rental_id",
ondelete="SET NULL",
onupdate="CASCADE"),
index=True)
amount = Column(DECIMAL(5, 2), nullable=False)
payment_date = Column(DateTime, nullable=False)
last_update = Column(
TIMESTAMP,
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
customer = relationship("Customer")
rental = relationship("Rental")
staff = relationship("Staff")
class FilmText(SAFRSBase, Base):
__tablename__ = "film_text"
__table_args__ = (Index("idx_title_description", "title", "description"), )
film_id = Column(SMALLINT(6), primary_key=True)
title = Column(String(255), nullable=False)
description = Column(Text)
class FilmText(SAFRSBase, Base):
__tablename__ = 'film_text'
__table_args__ = (Index('idx_title_description', 'title', 'description'), )
film_id = Column(SMALLINT(6), primary_key=True)
title = Column(String(255), nullable=False)
description = Column(Text)
class Payment(SAFRSBase, Base):
__tablename__ = 'payment'
payment_id = Column(SMALLINT(5), primary_key=True)
customer_id = Column(ForeignKey('customer.customer_id',
onupdate='CASCADE'),
nullable=False,
index=True)
staff_id = Column(ForeignKey('staff.staff_id', onupdate='CASCADE'),
nullable=False,
index=True)
rental_id = Column(ForeignKey('rental.rental_id',
ondelete='SET NULL',
onupdate='CASCADE'),
index=True)
amount = Column(DECIMAL(5, 2), nullable=False)
payment_date = Column(DateTime, nullable=False)
last_update = Column(
TIMESTAMP,
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
customer = relationship('Customer')
rental = relationship('Rental')
staff = relationship('Staff')
def get_st_view(meta):
return Table(
'space_time_view', meta, Column('id', UUID()),
Column('dataset_type_ref', SMALLINT()),
Column('spatial_extent',
Geometry(from_text='ST_GeomFromGeoJSON', name='geometry')),
Column('temporal_extent', TSTZRANGE()))
class HelpTopic(SAFRSBase, Base):
__tablename__ = "help_topic"
help_topic_id = Column(INTEGER(10), primary_key=True)
name = Column(CHAR(64), nullable=False, unique=True)
help_category_id = Column(SMALLINT(5), nullable=False)
description = Column(Text, nullable=False)
example = Column(Text, nullable=False)
url = Column(Text, nullable=False)
class Country(SAFRSBase, Base):
__tablename__ = 'country'
country_id = Column(SMALLINT(5), primary_key=True)
country = Column(String(50), nullable=False)
last_update = Column(
TIMESTAMP,
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
class Actor(SAFRSBase, Base):
__tablename__ = 'actor'
actor_id = Column(SMALLINT(5), primary_key=True)
first_name = Column(String(45), nullable=False)
last_name = Column(String(45), nullable=False, index=True)
last_update = Column(
TIMESTAMP,
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
class Languages(Base): # 定义一个类，继承Base
__tablename__ = 'Languages'
__table_args__ = ({"schema": "dbo"})
# books = relationship('Books') 如果有和其他表的关系就用relationship
languageIncId = Column(INT(), primary_key=True)
languageSqlId = Column(SMALLINT(), primary_key=True)
languageCode = Column(NVARCHAR(10), nullable=True)
languageName = Column(NVARCHAR(40), nullable=True)
isDeleted = Column(Boolean(), nullable=False)
isoLanguageCode = Column(NVARCHAR(10), nullable=True)
isSupported = Column(Boolean(), nullable=False)
translationTag = Column(NVARCHAR(40), nullable=True)
class City(SAFRSBase, Base):
__tablename__ = 'city'
city_id = Column(SMALLINT(5), primary_key=True)
city = Column(String(50), nullable=False)
country_id = Column(ForeignKey('country.country_id', onupdate='CASCADE'),
nullable=False,
index=True)
last_update = Column(
TIMESTAMP,
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
country = relationship('Country')
class Film(SAFRSBase, Base):
__tablename__ = 'film'
film_id = Column(SMALLINT(5), primary_key=True)
title = Column(String(255), nullable=False, index=True)
description = Column(Text)
release_year = Column(YEAR(4))
language_id = Column(ForeignKey('language.language_id',
onupdate='CASCADE'),
nullable=False,
index=True)
original_language_id = Column(ForeignKey('language.language_id',
onupdate='CASCADE'),
index=True)
rental_duration = Column(TINYINT(3),
nullable=False,
server_default=text("'3'"))
rental_rate = Column(DECIMAL(4, 2),
nullable=False,
server_default=text("'4.99'"))
length = Column(SMALLINT(5))
replacement_cost = Column(DECIMAL(5, 2),
nullable=False,
server_default=text("'19.99'"))
rating = Column(Enum('G', 'PG', 'PG-13', 'R', 'NC-17'),
server_default=text("'G'"))
special_features = Column(SET)
last_update = Column(
TIMESTAMP,
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
language = relationship(
'Language', primaryjoin='Film.language_id == Language.language_id')
original_language = relationship(
'Language',
primaryjoin='Film.original_language_id == Language.language_id')
class MetaMovies(BASE, Extended):
__tablename__ = "meta_movies"
id = Column(Integer, primary_key=True)
title = ZdbColumn(FULLTEXT(), nullable=False)
year = ZdbColumn(SMALLINT(), nullable=False)
rating = ZdbColumn(SMALLINT(), nullable=False)
plot = ZdbColumn(FULLTEXT())
director = ZdbColumn(String())
genres = ZdbColumn(ARRAY(String(32)))
actors = ZdbColumn(ARRAY(String(64)))
meta = ZdbColumn(MutableJson())
def __init__(self, title, year, rating, plot, director, genres, actors,
meta):
self.title = title
self.year = year
self.rating = rating
self.plot = plot
self.director = director
self.genres = genres
self.actors = actors
self.meta = meta
class User(SAFRSBase, Base):
__tablename__ = 'user'
Host = Column(CHAR(60, 'utf8_bin'), primary_key=True, nullable=False, server_default=text("''"))
User = Column(CHAR(32, 'utf8_bin'), primary_key=True, nullable=False, server_default=text("''"))
Select_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Insert_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Update_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Delete_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Create_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Drop_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Reload_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Shutdown_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Process_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
File_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Grant_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
References_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Index_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Alter_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Show_db_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Super_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Create_tmp_table_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Lock_tables_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Execute_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Repl_slave_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Repl_client_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Create_view_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Show_view_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Create_routine_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Alter_routine_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Create_user_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Event_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Trigger_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
Create_tablespace_priv = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
ssl_type = Column(ENUM('', 'ANY', 'X509', 'SPECIFIED'), nullable=False, server_default=text("''"))
ssl_cipher = Column(LargeBinary, nullable=False)
x509_issuer = Column(LargeBinary, nullable=False)
x509_subject = Column(LargeBinary, nullable=False)
max_questions = Column(INTEGER(11), nullable=False, server_default=text("'0'"))
max_updates = Column(INTEGER(11), nullable=False, server_default=text("'0'"))
max_connections = Column(INTEGER(11), nullable=False, server_default=text("'0'"))
max_user_connections = Column(INTEGER(11), nullable=False, server_default=text("'0'"))
plugin = Column(CHAR(64, 'utf8_bin'), nullable=False, server_default=text("'mysql_native_password'"))
authentication_string = Column(Text(collation='utf8_bin'))
password_expired = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
password_last_changed = Column(TIMESTAMP)
password_lifetime = Column(SMALLINT(5))
account_locked = Column(ENUM('N', 'Y'), nullable=False, server_default=text("'N'"))
class Addres(SAFRSBase, Base):
__tablename__ = 'address'
address_id = Column(SMALLINT(5), primary_key=True)
address = Column(String(50), nullable=False)
address2 = Column(String(50))
district = Column(String(20), nullable=False)
city_id = Column(ForeignKey('city.city_id', onupdate='CASCADE'),
nullable=False,
index=True)
postal_code = Column(String(10))
phone = Column(String(20), nullable=False)
location = Column(NullType, nullable=False, index=True)
last_update = Column(
TIMESTAMP,
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
city = relationship('City')
class ChargeRecord(BaseDDL):
'''
charge_record ddl
'''
__tablename__ = 'charge_record'
charge_record_id = Column(BIGINT(), primary_key=True)
out_bill_no = Column(BIGINT())
order_no = Column(BIGINT())
charge_status = Column(SMALLINT())
message = Column(VARCHAR(100))
finish_time = Column(INT())
ctime = Column(INT())
utime = Column(INT())
def __repr__(self):
return "<charge_record:charge_record_id={charge_record_id},out_bill_no={out_bill_no}," \
"order_no={orderno},charge_status={cs},message={message},finish_time={ftime}," \
"ctime={ctime},utime={utime}>".format(
charge_record_id=self.charge_record_id, out_bill_no=self.out_bill_no, orderno=self.order_no,
cs=self.charge_status, message=self.message, ftime=self.finish_time, ctime=self.ctime, utime=self.utime)
class Customer(SAFRSBase, Base):
__tablename__ = 'customer'
customer_id = Column(SMALLINT(5), primary_key=True)
store_id = Column(ForeignKey('store.store_id', onupdate='CASCADE'),
nullable=False,
index=True)
first_name = Column(String(45), nullable=False)
last_name = Column(String(45), nullable=False, index=True)
email = Column(String(50))
address_id = Column(ForeignKey('address.address_id', onupdate='CASCADE'),
nullable=False,
index=True)
active = Column(TINYINT(1), nullable=False, server_default=text("'1'"))
create_date = Column(DateTime, nullable=False)
last_update = Column(
TIMESTAMP,
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
address = relationship('Addres')
store = relationship('Store')
class TbExecCmd(Base):
"""
命令运行信息表
"""
__tablename__ = 'tb_execcmd'
datatime = Column(String(8))
func_id = Column(String(40))
seq = Column(BigInteger())
memo = Column(String(100))
exec_cmd = Column(Text())
flag = Column(SMALLINT())
err_msg = Column(Text())
start_time = Column(DateTime())
end_time = Column(DateTime())
exec_elapsed = Column(BigInteger())
exec_date = Column(Date())
business_param = Column(String(40))
__table_args__ = (
PrimaryKeyConstraint('datatime', 'seq', 'func_id', 'business_param'),
{},
)
class Actor(SAFRSBase, Base):
__tablename__ = "actor"
actor_id = Column(SMALLINT(5), primary_key=True)
first_name = Column(String(45), nullable=False)
last_name = Column(String(45), nullable=False, index=True)
last_update = Column(
TIMESTAMP,
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
t_actor_info = Table(
"actor_info",
metadata,
Column("actor_id", SMALLINT(5), server_default=text("'0'")),
Column("first_name", String(45)),
Column("last_name", String(45)),
Column("film_info", Text),
)
class Addres(SAFRSBase, Base):
__tablename__ = "address"
address_id = Column(SMALLINT(5), primary_key=True)
address = Column(String(50), nullable=False)
address2 = Column(String(50))
district = Column(String(20), nullable=False)
city_id = Column(ForeignKey("city.city_id", onupdate="CASCADE"),
nullable=False,
class Actor(SAFRSBase, Base):
__tablename__ = 'actor'
actor_id = Column(SMALLINT(5), primary_key=True)
first_name = Column(String(45), nullable=False)
last_name = Column(String(45), nullable=False, index=True)
last_update = Column(
TIMESTAMP,
nullable=False,
server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
t_actor_info = Table(
'actor_info', metadata,
Column('actor_id', SMALLINT(5), server_default=text("'0'")),
Column('first_name', String(45)), Column('last_name', String(45)),
Column('film_info', Text))
class Addres(SAFRSBase, Base):
__tablename__ = 'address'
address_id = Column(SMALLINT(5), primary_key=True)
address = Column(String(50), nullable=False)
address2 = Column(String(50))
district = Column(String(20), nullable=False)
city_id = Column(ForeignKey('city.city_id', onupdate='CASCADE'),
nullable=False,
index=True)
postal_code = Column(String(10))
class Files(BASE, Extended):
__tablename__ = "files"
id = Column(BigInteger, primary_key=True)
resource_id = ZdbColumn(Integer())
file_name = Column(String())
file_path = Column(String())
file_ext = ZdbColumn(String(8))
file_format = ZdbColumn(Integer())
file_isdir = ZdbColumn(Boolean())
file_size = ZdbColumn(BigInteger())
file_modified = Column(DateTime())
file_perm = Column(SMALLINT())
searchable = ZdbColumn(FULLTEXT(41))
meta_info = ZdbColumn(MutableJson())
meta_movie_id = ZdbColumn(Integer())
meta_movie = None
ix_resource_id = Index("ix_resource_id", resource_id)
ix_host_id_file_path = Index("ix_resource_id_file_path", resource_id,
file_path)
ix_meta_movie_id = Index("ix_meta_movie_id", meta_movie_id)
# CREATE INDEX ix_file_searchable_gin ON files USING gin(searchable gin_trgm_ops);
# ix_file_searchable_gin = Index("ix_file_searchable_gin", searchable, postgresql_using="gin", postgresql_ops={
# "searchable": "gin_trgm_ops"
# })
def get_json(self, depth=0):
json = super(BASE, self).get_json()
json["resource"] = {"address": self.resource.server.address}
return json
def get_meta_movie(self):
if self.meta_movie_id is None:
return
from findex_gui.web import db
self.meta_movie = db.session.query(MetaMovies).filter(
MetaMovies.id == self.meta_movie_id).first()
return self.meta_movie
@property
def file_name_human(self):
return "%s%s%s" % (self.file_name, "." if not self.file_isdir
and self.file_ext is not None else "",
self.file_ext if self.file_ext is not None else "")
@property
def file_modified_human(self):
return self.file_modified.strftime("%d %b %Y")
@property
def file_format_human(self):
return FileCategories().name_by_id(self.file_format)
@property
def file_size_human(self):
return humanfriendly.format_size(self.file_size)
@property
def path_dir(self):
return "%s:%d%s" % (self.resource.server.address, self.resource.port,
self.file_path)
@property
def path_file(self):
return "%s:%d%s%s%s" % (
self.resource.server.address, self.resource.port, self.file_path,
self.file_name_human, "/" if self.file_isdir else "")
@property
def path_direct(self):
display_url = self.resource.display_url
if display_url.endswith("/"):
display_url = display_url[:-1]
if display_url:
return display_url + self.file_url
else:
return "%s://%s:%s%s%s" % (FileProtocols().name_by_id(
self.resource.protocol), self.resource.server.address,
self.resource.port,
self.resource.basepath, self.file_url)
@property
def file_url(self):
return "%s%s" % (self.file_path, self.file_name_human)
)
permaban = Table(
'permaban',
metadata,
Column('userid', Integer(), primary_key=True, nullable=False),
Column('reason', Text(), nullable=False),
default_fkey(['userid'], ['login.userid'], name='permaban_userid_fkey'),
)
premiumpurchase = Table(
'premiumpurchase',
metadata,
Column('token', String(), primary_key=True, nullable=False),
Column('email', String(), nullable=False),
Column('terms', SMALLINT(), nullable=False),
)
profile = Table(
'profile',
metadata,
Column('userid', Integer(), primary_key=True, nullable=False),
Column('username', String(length=40), nullable=False, unique=True),
Column('full_name', String(length=100), nullable=False),
Column('catchphrase',
String(length=200),
nullable=False,
server_default=''),
Column('artist_type',
String(length=100),
nullable=False,