SixthProject/BBend-Test-JWT/app/models.py

# coding: utf-8
from sqlalchemy import Column, DateTime, Integer, Numeric, String
from flask_sqlalchemy import SQLAlchemy
#使用Werkzeug的security加密
from werkzeug.security import generate_password_hash,check_password_hash

#與當前項目相關的模型文件，即所有的實體類在此編寫
from manage import db

class User(db.Model):
    __tablename__ = 'user'

    sn = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String(30), nullable=False)
    lastname = db.Column(db.String(30), nullable=False)
    mail = db.Column(db.String(50), nullable=False)
    phone = db.Column(db.String(20), nullable=False)
    username = db.Column(db.String(30), nullable=False, unique=True)
    password = db.Column(db.String(40), nullable=False)
    status = db.Column(db.Integer, nullable=False, info='0:admin;1:new;2:old')
    isActive = db.Column(db.Boolean, nullable=False, default=True)

    purchase_info = db.relationship('PurchaseInfo', backref='user', uselist=False)

    node_info = db.relationship('NodeInfo', backref='user', uselist=False)

    farm_info = db.relationship('FarmInfo', backref='user', uselist=False)

    permission = db.relationship('Permissions', backref='user', uselist=False)

    node_imgs = db.relationship('NodeImage', backref='user', lazy='dynamic')

    node_positions = db.relationship('NodePosition', backref='user', lazy='dynamic')

    module_sn_numbers = db.relationship('ModuleSerialNumber', backref='user', lazy='dynamic')



#哈希加密版本
# class User(db.Model):
#     __tablename__ = 'user'
#
#     sn = db.Column(db.Integer, primary_key=True)
#     firstname = db.Column(db.String(30), nullable=False)
#     lastname = db.Column(db.String(30), nullable=False)
#     mail = db.Column(db.String(50), nullable=False)
#     phone = db.Column(db.String(20), nullable=False)
#     username = db.Column(db.String(30), nullable=False, unique=True)
#     password = db.Column(db.Text, nullable=False)
#     status = db.Column(db.Integer, nullable=False, info='0:admin;1:new;2:old')
#     isActive = db.Column(db.Boolean, nullable=False, default=True)
#
#     purchase_info = db.relationship('PurchaseInfo', backref='user', uselist=False)
#
#     node_info = db.relationship('NodeInfo', backref='user', uselist=False)
#
#     farm_info = db.relationship('FarmInfo', backref='user', uselist=False)
#
#     permission = db.relationship('Permissions', backref='user', uselist=False)
#
#     node_imgs = db.relationship('NodeImage', backref='user', lazy='dynamic')
#
#     node_positions = db.relationship('NodePosition', backref='user', lazy='dynamic')
#
#     module_sn_numbers = db.relationship('ModuleSerialNumber', backref='user', lazy='dynamic')

    # def __init__(self, *args, **kwargs):
    #     username = kwargs.get('username')
    #     password = kwargs.get('password')
    #
    #     self.username = username
    #     self.password = generate_password_hash(password)
    #
    # # 这里的参数是hash过的参数以及原始传入hash
    # def check_hash_password(self,raw_password):
    #     result = check_password_hash(self.password, raw_password)
    #     return result
    #     # 得到验证結果　　


class PurchaseInfo(db.Model):
    __tablename__ = 'purchase_info'

    sn = db.Column(db.Integer, primary_key=True)
    loc_length = db.Column(db.Text, nullable=False)
    loc_width = db.Column(db.Text, nullable=False)
    location = db.Column(db.Text, nullable=False)
    monitor_num = db.Column(db.Integer, nullable=False)
    connect = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.sn'))
    datetime = db.Column(db.DateTime, nullable=False)


class NodeInfo(db.Model):
    __tablename__ = 'node_info'

    sn = db.Column(db.Integer, primary_key=True)
    node_info = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.sn'))


class FarmInfo(db.Model):
    __tablename__ = 'farm_info'

    sn = db.Column(db.Integer, primary_key=True)
    path = db.Column(db.Text, nullable=False, default='0')
    location = db.Column(db.Text, nullable=False, default='0')
    loc_lat = db.Column(db.Text, nullable=False, default='0')
    loc_lng = db.Column(db.Text, nullable=False, default='0')
    user_id = db.Column(db.Integer, db.ForeignKey('user.sn'))
    datetime = db.Column(db.DateTime, nullable=False)

class NodeImage(db.Model):
    __tablename__ = 'node_img'

    sn = db.Column(db.Integer, primary_key=True)
    node_name = db.Column(db.String(10), nullable=False)
    path = db.Column(db.Text, nullable=False, default='0')
    user_id = db.Column(db.Integer, db.ForeignKey('user.sn'))
    datetime = db.Column(db.DateTime, nullable=False)

class NodePosition(db.Model):
    __tablename__ = 'node_position'

    sn = db.Column(db.Integer, primary_key=True)
    node_name = db.Column(db.String(10), nullable=False)
    pos_lat = db.Column(db.Text, nullable=False)
    pos_lng = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.sn'))
    datetime = db.Column(db.DateTime, nullable=False)

class ModuleSerialNumber(db.Model):
    __tablename__ = 'module_sn_number'

    sn = db.Column(db.Integer, primary_key=True)
    module_name = db.Column(db.String(30), nullable=False)
    sn_number = db.Column(db.Text, nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey('user.sn'))
    datetime = db.Column(db.DateTime, nullable=False)

#權限表
class Permissions(db.Model):
    __tablename__ = 'permissions'

    sn = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('user.sn'))
    add_domain = db.Column(db.Integer, nullable=False, default=0)
    company_profile = db.Column(db.Integer, nullable=False, default=0)
    product_info = db.Column(db.Integer, nullable=False, default=0)
    erp = db.Column(db.Integer, nullable=False, default=0)
    rma = db.Column(db.Integer, nullable=False, default=0)
    download_data = db.Column(db.Integer, nullable=False, default=0)
    gps = db.Column(db.Integer, nullable=False, default=0)
    sensor = db.Column(db.Integer, nullable=False, default=0)
    Actuator = db.Column(db.Integer, nullable=False, default=0)
    diary_log = db.Column(db.Integer, nullable=False, default=0)
    repair_log = db.Column(db.Integer, nullable=False, default=0)
    set_permission = db.Column(db.Integer, nullable=False, default=0)
    register = db.Column(db.Integer, nullable=False, default=0)
    blacklist = db.Column(db.Integer, nullable=False, default=0)
    back_manage = db.Column(db.Integer, nullable=False, default=0)
    module_test = db.Column(db.Integer, nullable=False, default=0)

class SensorList(db.Model):
    __tablename__ = 'sensor_list'

    sn = db.Column(db.Integer, primary_key=True)
    ch_name = db.Column(db.String(20), nullable=False)
    en_name = db.Column(db.String(20), nullable=False)
    version = db.Column(db.String(10), nullable=False)
    datetime = db.Column(db.DateTime, nullable=False)


    #自定義序列化方法，方便在前端拿數據
    def toDict(self):
        return {'ch_name':self.ch_name, 'en_name':self.en_name, 'version':self.version}

class ItemList(db.Model):
    __tablename__ = 'item_list'

    sn = db.Column(db.Integer, primary_key=True)
    ip = db.Column(db.Text, nullable=False)
    pymysql = db.Column(db.Integer, nullable=False)
    eventlet = db.Column(db.Integer, nullable=False)
    datetime = db.Column(db.DateTime, nullable=False)

db.create_all()