资产管理(一)

建立数据库

drop database link;
create database if not exists link default charset utf8mb4 collate utf8mb4_general_ci;

grant all privileges on link.* to link@'localhost' identified by '@link';
flush privileges

set foreign_key_checks=0;
set foreign_key_checks=1;

数据表

# 机房 机柜 机架 主机
from . import db

class Location(db.Model):
    __tablename__ = 'location'
    uuid = db.Column(db.String(40), primary_key=True)
    name = db.Column(db.String(20))
    id = db.Column(db.String(20), unique=True)
    description = db.Column(db.Text)
    cabinets = db.relationship('Cabinet', backref='location')

class Cabinet(db.Model):
    __tablename__ = 'cabinet'
    uuid = db.Column(db.String(40), primary_key=True)
    id = db.Column(db.String(20))
    capacity = db.Column(db.Integer, default=42)
    description = db.Column(db.Text)
    location_uuid = db.Column(db.String(40), db.ForeignKey('location.uuid', onupdate='CASCADE', ondelete='SET NULL'))
    racks = db.relationship('Rack', backref='cabinet')

class Rack(db.Model):
    __tablename__ = 'rack'
    uuid = db.Column(db.String(40), primary_key=True)
    id = db.Column(db.Integer)
    cabinet_uuid = db.Column(db.String(40), db.ForeignKey('cabinet.uuid',  onupdate='CASCADE', ondelete='SET NULL'))
    host_uuid = db.Column(db.String(40), db.ForeignKey('host.uuid',  onupdate='CASCADE', ondelete='SET NULL'))

class Host(db.Model):
    __tablename__ = 'host'
    uuid = db.Column(db.String(40), primary_key=True)
    name = db.Column(db.String(20), unique=True, index=True)
    size = db.Column(db.Integer, nullable=True, default=1)
    category = db.Column(db.String(20), nullable=True, default='host')
    description = db.Column(db.Text)
    racks = db.relationship('Rack', backref='host')

手工建立模拟数据

from app import db
from app.models import *
from sqlalchemy import func, desc
import uuid, random

db.create_all()

#建立location
l_bj = Location(uuid=uuid.uuid4(), name='北京', id='bj', description='北京')
l_sh = Location(uuid=uuid.uuid4(), name='上海', id='sh', description='上海')

db.session.add_all([l_bj, l_sh])
db.session.commit()

#建立cabinet
c_bj = [Cabinet(uuid=uuid.uuid4(), id='bj0'+str(id), capacity=random.choice([42]), description='bj0'+str(id)) for id in xrange(1, 8)]
c_sh = [Cabinet(uuid=uuid.uuid4(), id='sh0'+str(id), capacity=random.choice([42]), description='sh0'+str(id)) for id in xrange(1, 4)]

db.session.add_all(c_bj)
db.session.add_all(c_sh)
db.session.commit()

#建立rack，并关联对应的cabinet
for id in ['bj01','bj02', 'bj03', 'bj04', 'bj05', 'bj06', 'bj07', 'sh01', 'sh02', 'sh03']:
    cabinet = Cabinet.query.filter(Cabinet.id==id).first()
    racks = [Rack(uuid=uuid.uuid4(), id=i, cabinet=cabinet) for i in xrange(1, cabinet.capacity+1)]
    db.session.add_all(racks)
    db.session.commit()

#建立host
for i in xrange(11, 81):
    name = random.choice(['bj01','bj02', 'bj03', 'bj04', 'bj05', 'bj06', 'bj07'])
    host = Host(uuid=uuid.uuid4(), name=name+'-'+str(i), size=random.choice([1,2]), category='host', description=name+'-'+str(i))
    db.session.add(host)

for i in xrange(11, 21):
    name = random.choice(['sh01','sh02', 'sh03'])
    host = Host(uuid=uuid.uuid4(), name=name+'-'+str(i), size=random.choice([1,2]), category='host', description=name+'-'+str(i))
    db.session.add(host)

db.session.commit()

#关联cabinet和location
for id in ['bj', 'sh']:
    location = Location.query.filter(Location.id==id).first()
    for cabinet in Cabinet.query.filter(Cabinet.id.like('%'+id+'%')):
        cabinet.location = location
        db.session.commit()

#关联host和rack
interval = 1
for id in ['bj01','bj02', 'bj03', 'bj04', 'bj05', 'bj06', 'bj07']:
    start = 0
    racks = Rack.query.join(Cabinet).filter(Rack.cabinet_uuid==Cabinet.uuid).filter(Cabinet.id==id).order_by(Rack.id)
    for host in Host.query.filter(Host.name.like('%'+id+'%')).order_by(Host.name):
        end = start + host.size
        host.racks = racks[start:end]
        start = end + interval
        db.session.commit()