openstack通过sqlalchemy修改数据库的表结构
更改数据库的方法
在开发 Openstack 项目的过程中, 对 models class 进行直接修改是不被允许的.
这不符合持续集成的规范, 也可能导致原始数据的丢失. 所以我们会使用一种类似
打补丁的方式来对 Openstack 项目的数据库进行持续更新, 这也就是为什么在
/opt/stack/nova/nova/db/sqlalchemy/migrate_repo/versions 路径下存在这么多文
件的原因.
为数据库添加一张或多张新表
当需要为 Openstack 项目新添一张表时, 我们会在
/opt/stack/nova/nova/db/sqlalchemy/migrate_repo/versions
目录下新建一个文件, 并且需要为文件名指定一个有序的编号,
EG. 016_add_new_table.py
from sqlalchemy import Boolean, Column, DateTime, BigInteger
from sqlalchemy import MetaData, String, Table
from oslo_log import log as logging
LOG = logging.getLogger(__name__)
def define_tables(meta):
# 定义一个 Table 对象
new_table_name = Table(
"new_table_name", meta,
Column("created_at", DateTime),
Column("updated_at", DateTime),
Column("deleted_at", DateTime),
Column("deleted", Boolean),
mysql_engine="InnoDB")
...
return [new_table_name, ...]
def upgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
# create all tables
# Take care on create order for those with FK dependencies
tables = define_tables(meta)
# 循环创建表列表
for table in tables:
try:
table.create()
except Exception:
LOG.info(_LE('Exception while creating table.'))
raise
def downgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
tables = define_tables(meta)
tables.reverse()
for table in tables:
table.drop()- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
删除一张或多张表
from sqlalchemy import MetaData
from sqlalchemy import Table
def upgrade(migrate_engine):
meta = MetaData(migrate_engine)
meta.reflect(migrate_engine)
table_names = ['compute_node_stats', 'compute_nodes', 'instance_actions',
'instance_actions_events', 'instance_faults', 'migrations']
for table_name in table_names:
# 创建表对象, 然后在通过表对象来调用 drop() 方法实现删除.
table = Table('dump_' + table_name, meta)
table.drop(checkfirst=True)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
为旧表添加一个字段
from sqlalchemy import Column, MetaData, String, Table
NEW_COLUMN_NAME = 'initiator_name'
def upgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
# 定义一个表对象, 因为是更新操作, 所以表 exsi_hypervisors 需要已经存在于数据库中
exsi_hypervisors = Table('exsi_hypervisors', meta, autoload=True)
# 定义一个字段对象
initiator_protocol = Column(NEW_COLUMN_NAME, String(length=255))
# 如果表中还没有该字段, 则添加一个新的字段
if not hasattr(exsi_hypervisors.c, NEW_COLUMN_NAME):
# 表对象调用 create_column() 方法来将字段插入
exsi_hypervisors.create_column(initiator_protocol)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
为旧表更新一个字段
from sqlalchemy import Column, MetaData, String, Table
NEW_COLUMN_NAME = 'initiator_name'
def upgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
# 获取一个表对象
exsi_hypervisors = Table('exsi_hypervisors', meta, autoload=True)
# 如果表对象中已经存在了 metadata_reserve 属性(字段), 则 alter 该属性(字段)
if hasattr(exsi_hypervisors.c, 'metadata_reserve'):
# 获取 metadata_reserve 属性对象
exsi_hypervisors_metadate_reserve = getattr(exsi_hypervisors.c,
'metadata_reserve')
# 通过属性对象来调用 alter() 方法, 并且传入需要更新的字段名和类型作为实参
exsi_hypervisors_metadate_reserve.alter(name='initiator_protocol',
type=String(255))- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
为旧表初始化一条新的记录
from datetime import datetime
from uuid import uuid4
from sqlalchemy import MetaData, Table
def upgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
# 定义要插入的记录数据
values = [{'created_at': datetime.utcnow(),
'id': str(uuid4()),
'group': 'global',
'setting_option': 'cpu_over_allocate',
'setting_value': '6',
'description': 'Over allocate CPU'}]
# 创建一个 table 对象, 该表必须是已经存在于数据库内的表, 才能够被插入
system_settings = Table('system_settings', meta,
autoload=True)
# 通过表对象来调用 insert() 方法实现插入数据
for value in values:
system_settings.insert().values(value).execute()
def downgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
system_settings = Table('system_settings', meta,
autoload=True)
try:
system_settings.delete().\
where(system_settings.c.setting_option == 'cpu_over_allocate').\
execute()
except Exception as e:
raise e- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
最后
在实现了数据库修改的文件之后执行指令:
serviceName-manager db dync- 1
- 1
就能够对现有的数据库进行更新.
浙公网安备 33010602011771号