playframework数据库管理工具 笔记（2）

# --- ！Ups 标记的sql 表示正常状态下会执行的脚本

# --- ！Downs 标记的sql表示异常时会执行的脚本（这里一般写成和上面对应的回滚sql）

1.处理并发的evolution script

现在有两个程序员A和B，他们同时提交了2.sql：

# create table User
# --- !Ups

CREATE TABLE qic_db.User (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    fullname varchar(255) NOT NULL,
    isAdmin boolean NOT NULL,
    PRIMARY KEY (id)
);

# --- !Downs
DROP TABLE qic_db.User;

# add anther column to User6
# --- !Ups

ALTER TABLE User6 ADD company varchar(255);


# --- !Downs
ALTER TABLE User6 drop company;

那么A在更新的时候就会要求merge it ，现在他只需要

# create table User
# ADD anther column to User6
# --- !Ups

CREATE TABLE qic_db.User (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    fullname varchar(255) NOT NULL,
    isAdmin boolean NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE User6 ADD company varchar(255);

# --- !Downs
DROP TABLE qic_db.User;
ALTER TABLE User6 drop company;

实验表明：play先会回滚A的2.sql ，执行

DROP TABLE qic_db.User;

然后再执行合并后的2.sql。

2.处理错误的evolution script

当出现了一个错误的evolution script 3.sql：

# Add another column to User
  
# --- !Ups
ALTER TABLE User ADD company varchar(255);
 
# --- !Downs
ALTER TABLE User DROP company;

Play 会提示你

你需要修改3.sql，点击”make it resolved“然后执行你修改过的sql。

3.在prod(生产模式)下的evolution commands

在prod环境中 如果目录下有evolution script 没有被执行过，运行run命令时play会提示 

~        _            _ 
~  _ __ | | __ _ _  _| |
~ | '_ \| |/ _' | || |_|
~ |  __/|_|\____|\__ (_)
~ |_|            |__/   
~
~ play! master-localbuild, http://www.playframework.org
~ framework ID is prod
~
~ Ctrl+C to stop
~ 
13:33:22 INFO  ~ Starting ~/test
13:33:22 INFO  ~ Precompiling ...
13:33:24 INFO  ~ Connected to jdbc:mysql://localhost
13:33:24 WARN  ~ 
13:33:24 WARN  ~ Your database is not up to date.
13:33:24 WARN  ~ Use `play evolutions` command to manage database evolutions.
13:33:24 ERROR ~ 
 
@662c6n234
Can't start in PROD mode with errors
 
Your database needs evolution!
An SQL script will be run on your database.
 
play.db.Evolutions$InvalidDatabaseRevision
	at play.db.Evolutions.checkEvolutionsState(Evolutions.java:323)
	at play.db.Evolutions.onApplicationStart(Evolutions.java:197)
	at play.Play.start(Play.java:452)
	at play.Play.init(Play.java:298)
	at play.server.Server.main(Server.java:141)
Exception in thread "main" play.db.Evolutions$InvalidDatabaseRevision
	at play.db.Evolutions.checkEvolutionsState(Evolutions.java:323)
	at play.db.Evolutions.onApplicationStart(Evolutions.java:197)
	at play.Play.start(Play.java:452)
	at play.Play.init(Play.java:298)
	at play.server.Server.main(Server.java:141)

要求你先执行 play evolutions

$ play evolutions
~        _            _ 
~  _ __ | | __ _ _  _| |
~ | '_ \| |/ _' | || |_|
~ |  __/|_|\____|\__ (_)
~ |_|            |__/   
~
~ play! master-localbuild, http://www.playframework.org
~ framework ID is gbo
~
~ Connected to jdbc:mysql://localhost
~ Application revision is 3 [15ed3f5] and Database revision is 0 [da39a3e]
~
~ Your database needs evolutions!
 
# ----------------------------------------------------------------------------
 
# --- Rev:1,Ups - 6b21167
 
CREATE TABLE User (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    email varchar(255) NOT NULL,
    password varchar(255) NOT NULL,
    fullname varchar(255) NOT NULL,
    isAdmin boolean NOT NULL,
    PRIMARY KEY (id)
);
 
# --- Rev:2,Ups - 9cf7e12
  
ALTER TABLE User ADD age INT;
CREATE TABLE Post (
    id bigint(20) NOT NULL AUTO_INCREMENT,
    title varchar(255) NOT NULL,
    content text NOT NULL,
    postedAt date NOT NULL,
    author_id bigint(20) NOT NULL,
    FOREIGN KEY (author_id) REFERENCES User(id),
    PRIMARY KEY (id)
);
 
# --- Rev:3,Ups - 15ed3f5
 
ALTER TABLE User ADD company varchar(255);
 
# ----------------------------------------------------------------------------
 
~ Run `play evolutions:apply` to automatically apply this script to the db
~ or apply it yourself and mark it done using `play evolutions:markApplied`
~

如果你想让play执行你的sql脚本请输入：play evolutions:apply

如果你想手动执行，play会自动忽略没有执行过sql脚本，你需要输入：play evolutions:markApplied

如果有任何错误需要回滚到以前的状态 你需要输入:play evolutions: resolve

值得一提的是：如果你删除了之前执行过的 evolution script，再次执行脚本命令时，play会自动执行被删除的sql #---! Downs下的命令