SixthProject/Sixth-Backend/app/models.py

# coding: utf-8
from sqlalchemy import Column, DateTime, Integer, Numeric, String
from flask_sqlalchemy import SQLAlchemy


#與當前項目相關的模型文件，即所有的實體類在此編寫
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 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}

db.create_all()