src/driver/mysql/MysqlQueryRunner.ts
//Mysql的查询运行器，在一个单独的数据库连接中执行数据库操作
//这里的数据库连接指的是底层连接，而不是typeorm的Connection
export class MysqlQueryRunner implements QueryRunner {
//驱动对象
driver: MysqlDriver;
//typeorm连接对象
connection: Connection;
//孤立的实体管理器，只对当前查询运行器有效
manager: EntityManager;
//指明这个查询运行器的底层连接是否已经被释放
//释放之后不能再执行数据库操作
isReleased = false;
//是否执行事务
isTransactionActive = false;
//存储临时用户数据，可以在订阅者中访问
data = {};
//用来执行操作的底层数据库连接
protected databaseConnection: any;
//第一次获取底层数据库连接的Promise
protected databaseConnectionPromise: Promise<any>;
//内存sql模式，sql存储在内存中，只存储DDL
protected sqlMemoryMode: boolean = false;
//内存sql模式开启，存储sql的数组，up为创建语句，down为移除语句
protected sqlsInMemory: (string|{ up: string, down: string })[] = [];
//在主从复制模式下指明运行模式，非主从复制模式被忽略
protected mode: "master"|"slave";
//使用驱动实例、模式构造
constructor(driver: MysqlDriver, mode: "master"|"slave" = "master") {
this.driver = driver;
this.connection = driver.connection;
this.mode = mode;
}
//连接方法，从驱动实例获取底层数据库连接
connect(): Promise<any> {
//底层数据库连接存在，直接返回
if (this.databaseConnection)
return Promise.resolve(this.databaseConnection);
//承诺存在，直接返回
if (this.databaseConnectionPromise)
return this.databaseConnectionPromise;
//从模式且主从复制开启
if (this.mode === "slave" && this.driver.isReplicated) {
//使用驱动获取从服务器连接
//注意：箭头函数继承父作用域this，在then里面设置了获取到的连接
this.databaseConnectionPromise = this.driver.obtainSlaveConnection().then(connection => {
this.databaseConnection = connection;
return this.databaseConnection;
});
} else {
//使用驱动获取主服务器连接，即当主从复制不开启也是获取主服务器连接
this.databaseConnectionPromise = this.driver.obtainMasterConnection().then(connection => {
this.databaseConnection = connection;
return this.databaseConnection;
});
}
return this.databaseConnectionPromise;
}
//释放底层数据库连接，释放之后不再能执行查询
release(): Promise<void> {
this.isReleased = true;
if (this.databaseConnection)
//底层连接执行释放
this.databaseConnection.release();
return Promise.resolve();
}
//开启事物
async startTransaction(): Promise<void> {
//只能开启一次
if (this.isTransactionActive)
throw new TransactionAlreadyStartedError();
this.isTransactionActive = true;
//mysql的开启事物语句
await this.query("START TRANSACTION");
}
//提交事务
async commitTransaction(): Promise<void> {
if (!this.isTransactionActive)
throw new TransactionNotStartedError();
//mysql的提交事务语句
await this.query("COMMIT");
this.isTransactionActive = false;
}
//回滚事务
async rollbackTransaction(): Promise<void> {
if (!this.isTransactionActive)
throw new TransactionNotStartedError();
//mysql的回滚事务语句
await this.query("ROLLBACK");
this.isTransactionActive = false;
}
//执行mysql的原始sql语句，可以执行参数数组
//一般在query中使用？表示参数，参数为字符串数组，按照下标设置参数
//其他的insert、update、delete等方法都调用了这个方法
//这个方法应该是调用底层数据库连接的唯一方法
query(query: string, parameters?: any[]): Promise<any> {
//已释放不能执行
if (this.isReleased)
throw new QueryRunnerAlreadyReleasedError();
return new Promise(async (ok, fail) => {
try {
//执行connect方法，保证底层连接存在，只有第一次执行会获取到底层连接
const databaseConnection = await this.connect();
//使用Connection的logger记录日志
this.driver.connection.logger.logQuery(query, parameters, this);
//操作开始时间
const queryStartTime = +new Date();
//直接使用底层数据库连接执行，即mysql这个npm包中的连接对象
databaseConnection.query(query, parameters, (err: any, result: any) => {
//如果查询时间超时，记录慢查询日志，mysql本身有慢查询功能，这里是另外的实现
const maxQueryExecutionTime = this.driver.connection.options.maxQueryExecutionTime;
const queryEndTime = +new Date();
const queryExecutionTime = queryEndTime - queryStartTime;
if (maxQueryExecutionTime && queryExecutionTime > maxQueryExecutionTime)
this.driver.connection.logger.logQuerySlow(queryExecutionTime, query, parameters, this);
//出现错误记录错误日志
if (err) {
this.driver.connection.logger.logQueryError(err, query, parameters, this);
return fail(new QueryFailedError(query, parameters, err));
}
//返回查询结果,返回的是mysql包里面的packet类型
ok(result);
});
} catch (err) {
fail(err);
}
});
}
//执行查询，返回原始数据流
//这个数据流类型为Query，继承了可读流,注意这个可读流的数据事件为packet而不是data
//可读流写成字符串，还是packet数据
stream(query: string, parameters?: any[], onEnd?: Function, onError?: Function): Promise<ReadStream> {
if (this.isReleased)
throw new QueryRunnerAlreadyReleasedError();
return new Promise(async (ok, fail) => {
try {
const databaseConnection = await this.connect();
this.driver.connection.logger.logQuery(query, parameters, this);
//不提供回调，则返回查询数据流
const stream = databaseConnection.query(query, parameters);
if (onEnd) stream.on("end", onEnd);
if (onError) stream.on("error", onError);
ok(stream);
} catch (err) {
fail(err);
}
});
}
//执行插入语句
//tablePath：表名、实体名
//keyValues：键值对
async insert(tablePath: string, keyValues: ObjectLiteral): Promise<InsertResult> {
//获取所有键
const keys = Object.keys(keyValues);
//使用''包裹，用逗号连接，'需要转义
//获取列名
const columns = keys.map(key => `\`${key}\``).join(", ");
//获取插入值的占位符？
const values = keys.map(key => "?").join(",");
//获取值数组
const parameters = keys.map(key => keyValues[key]);
//自动生成列元数据，如自动生成主键
const generatedColumns = this.connection.hasMetadata(tablePath) ? this.connection.getMetadata(tablePath).generatedColumns : [];
//生成预编译语句，包含表名、列名列表、值占位符
const sql = `INSERT INTO \`${this.escapeTablePath(tablePath)}\`(${columns}) VALUES (${values})`;
//执行查询
const result = await this.query(sql, parameters);
//遍历所有自动生成列，获取生成键值对，reduce初始值为空对象
const generatedMap = generatedColumns.reduce((map, generatedColumn) => {
//如果是主键且执行结果有插入id则返回id，否则返回输出参数对应值
const value = generatedColumn.isPrimary && result.insertId ? result.insertId : keyValues[generatedColumn.databaseName];
if (!value) return map;
//合并map，map为一个键值对象
return OrmUtils.mergeDeep(map, generatedColumn.createValueMap(value));
}, {} as ObjectLiteral);
//注意返回的为一个对象
return {
//插入结果
result: result,
//自动生成的键值对
generatedMap: Object.keys(generatedMap).length > 0 ? generatedMap : undefined
};
}
//执行更新操作
//tablePath：表路径，包含数据库名
//valuesMap：要更新的价值对
//conditions：更新条件键值对
async update(tablePath: string, valuesMap: ObjectLiteral, conditions: ObjectLiteral): Promise<void> {
//获取拼接后的更新值的键值对字符串，包含了值占位符？
const updateValues = this.parametrize(valuesMap).join(", ");
//获取条件键值对字符串，包含了值占位符？
const conditionString = this.parametrize(conditions).join(" AND ");
//拼接sql
const sql = `UPDATE \`${this.escapeTablePath(tablePath)}\` SET ${updateValues} ${conditionString ? (" WHERE " + conditionString) : ""}`;
//获取条件值数组
const conditionParams = Object.keys(conditions).map(key => conditions[key]);
//获取更新值数组
const updateParams = Object.keys(valuesMap).map(key => valuesMap[key]);
//拼接所有的值，按照占位符顺序，先更新值，再条件值
const allParameters = updateParams.concat(conditionParams);
//执行操作
await this.query(sql, allParameters);
}
//执行删除操作
//tablePath：表路径，包含数据库名
//conditions：删除条件
async delete(tablePath: string, conditions: ObjectLiteral|string, maybeParameters?: any[]): Promise<void> {
//获取包含占位符？的条件字符串，以AND连接
const conditionString = typeof conditions === "string" ? conditions : this.parametrize(conditions).join(" AND ");
//获取值数组，对应占位符？，除了在对象中直接执行键值对，也可以在第三个参数中执行值数组
const parameters = conditions instanceof Object ? Object.keys(conditions).map(key => (conditions as ObjectLiteral)[key]) : maybeParameters;
//拼接sql
const sql = `DELETE FROM \`${this.escapeTablePath(tablePath)}\` WHERE ${conditionString}`;
//执行sql
await this.query(sql, parameters);
}
//在封闭表中插入，即只插入自动生成对额封闭表，而实体表的插入已经完成，封闭表只管理子孙关系
//tablePath：表路径，包含数据库名
//newEntityId：插入实体id
//parentId：所属父实体id
//hasLevel：实体是否包含level，即TreeLevelColumn装饰器装饰的列
//返回一个级别值
async insertIntoClosureTable(tablePath: string, newEntityId: any, parentId: any, hasLevel: boolean): Promise<number> {
// 包含级别列名时
if (hasLevel) {
await this.query(
//向封闭表中插入祖先、子孙、级别三个值
`INSERT INTO \`${this.escapeTablePath(tablePath)}\`(\`ancestor\`, \`descendant\`, \`level\`) ` +
//插入值为查询结果，第一个查询为查询父实体的所有祖先，其level+1
`SELECT \`ancestor\`, ${newEntityId}, \`level\` + 1 FROM \`${this.escapeTablePath(tablePath)}\` WHERE \`descendant\` = ${parentId} ` +
//不去重的合并查询结果，这个好像是新生成实体自身的关系
`UNION ALL SELECT ${newEntityId}, ${newEntityId}, 1`
);
} else {
//不包含级别列时
await this.query(
`INSERT INTO \`${this.escapeTablePath(tablePath)}\`(\`ancestor\`, \`descendant\`) ` +
`SELECT \`ancestor\`, ${newEntityId} FROM \`${this.escapeTablePath(tablePath)}\` WHERE \`descendant\` = ${parentId} ` +
`UNION ALL SELECT ${newEntityId}, ${newEntityId}`
);
}
//如果包含级别列
if (hasLevel) {
//查询子孙为父实体的最高级别
const results: ObjectLiteral[] = await this.query(`SELECT MAX(\`level\`) as \`level\` FROM \`${this.escapeTablePath(tablePath)}\` WHERE \`descendant\` = ${parentId}`);
//返回级别+1
return results && results[0] && results[0]["level"] ? parseInt(results[0]["level"]) + 1 : 1;
} else {
return -1;
}
}
//加载给定表名的结构数据，指定表名，为getTables方法的特殊情况
async getTable(tableName: string): Promise<Table|undefined> {
const tables = await this.getTables([tableName]);
return tables.length > 0 ? tables[0] : undefined;
}
//加载给定表名数组的结构数据，返回Table数组
async getTables(tablePaths: string[]): Promise<Table[]> {
//内存模式不支持
if (this.sqlMemoryMode)
throw new Error(`Loading table is not supported in sql memory mode`);
if (!tablePaths || !tablePaths.length)
return [];
//获取单纯的表名数组，不包括数据库名
const tableNames = tablePaths.map(tablePath => {
return tablePath.indexOf(".") === -1 ? tablePath : tablePath.split(".")[1];
});
//获取数据库名数组
const dbNames = tablePaths
//过滤出带数据库名的路径
.filter(tablePath => tablePath.indexOf(".") !== -1)
//返回数据库名数组
.map(tablePath => tablePath.split(".")[0]);
//如果在获取到的数据库名数组中没有发现驱动默认的数据库名，push
if (this.driver.database && !dbNames.find(dbName => dbName === this.driver.database))
dbNames.push(this.driver.database);
//数据库名拼接字符串
const databaseNamesString = dbNames.map(dbName => `'${dbName}'`).join(", ");
//表名拼接字符串
const tableNamesString = tableNames.map(tableName => `'${tableName}'`).join(", ");
//查询表
const tablesSql = `SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA IN (${databaseNamesString}) AND TABLE_NAME IN (${tableNamesString})`; // todo(dima): fix, remove IN, apply AND and OR like in Mssql
//查询列
const columnsSql = `SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA IN (${databaseNamesString})`;
//查询索引
const indicesSql = `SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA IN (${databaseNamesString}) AND INDEX_NAME != 'PRIMARY' ORDER BY SEQ_IN_INDEX`;
//查询外键
const foreignKeysSql = `SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA IN (${databaseNamesString}) AND REFERENCED_COLUMN_NAME IS NOT NULL`;
//在一个Promise中执行所有查询
const [dbTables, dbColumns, dbIndices, dbForeignKeys]: ObjectLiteral[][] = await Promise.all([
//注意Promise.all参数为Promsie对象数组，如果对异步函数使用await，则返回的不是Promise，所以这个不能使用Promise
this.query(tablesSql),
this.query(columnsSql),
this.query(indicesSql),
this.query(foreignKeysSql)
]);
if (!dbTables.length)
return [];
//mariadb数据库
const isMariaDb = this.driver.options.type === "mariadb";
//创建Table类对象
return Promise.all(dbTables.map(async dbTable => {
//创建对象
const table = new Table(dbTable["TABLE_NAME"]);
//设置数据库名
table.database = dbTable["TABLE_SCHEMA"];
//主键列数组
const primaryKeys: ObjectLiteral[] = await this.query(`SHOW INDEX FROM \`${dbTable["TABLE_SCHEMA"]}\`.\`${dbTable["TABLE_NAME"]}\` WHERE Key_name = 'PRIMARY'`);
//设置Table的列数组
table.columns = dbColumns
//过滤出指定表的列
.filter(dbColumn => dbColumn["TABLE_NAME"] === table.name)
//遍历返回TableColumn对象数组
.map(dbColumn => {
//创建列对象
const tableColumn = new TableColumn();
//设置列名
tableColumn.name = dbColumn["COLUMN_NAME"];
//设置小写列类型
const columnType = dbColumn["COLUMN_TYPE"].toLowerCase();
//获取类型中长度括号
const endIndex = columnType.indexOf("(");
//设置列类型不包括括号
tableColumn.type = endIndex !== -1 ? columnType.substring(0, endIndex) : columnType;
//列默认值不存在
if (dbColumn["COLUMN_DEFAULT"] === null || dbColumn["COLUMN_DEFAULT"] === undefined
|| (isMariaDb && dbColumn["COLUMN_DEFAULT"] === "NULL")) {
tableColumn.default = undefined;
} else {
//设置列默认值
tableColumn.default = dbColumn["COLUMN_DEFAULT"];
}
//是否可为空
tableColumn.isNullable = dbColumn["IS_NULLABLE"] === "YES";
//是否为主键
tableColumn.isPrimary = dbColumn["COLUMN_KEY"].indexOf("PRI") !== -1;
//是否唯一
tableColumn.isUnique = dbColumn["COLUMN_KEY"].indexOf("UNI") !== -1;
//是否是自动生成列，mysql只支持auto_increment自动生成
tableColumn.isGenerated = dbColumn["EXTRA"].indexOf("auto_increment") !== -1;
//列注解
tableColumn.comment = dbColumn["COLUMN_COMMENT"];
//精度
tableColumn.precision = dbColumn["NUMERIC_PRECISION"];
//标度
tableColumn.scale = dbColumn["NUMERIC_SCALE"];
//列级别字符集
tableColumn.charset = dbColumn["CHARACTER_SET_NAME"];
//校对名称
tableColumn.collation = dbColumn["COLLATION_NAME"];
//整型列，包括各种int、year
if (tableColumn.type === "int" || tableColumn.type === "tinyint"
|| tableColumn.type === "smallint" || tableColumn.type === "mediumint"
|| tableColumn.type === "bigint" || tableColumn.type === "year") {
//设置列长度
const length = columnType.substring(columnType.indexOf("(") + 1, columnType.indexOf(")"));
tableColumn.length = length ? length.toString() : "";
} else {
//非整型列
tableColumn.length = dbColumn["CHARACTER_MAXIMUM_LENGTH"] ? dbColumn["CHARACTER_MAXIMUM_LENGTH"].toString() : "";
}
//枚举类型，mysql原生支持枚举类型
if (tableColumn.type === "enum") {
//获取原始类型
const colType = dbColumn["COLUMN_TYPE"];
//获取枚举值字符串数组
const items = colType.substring(colType.indexOf("(") + 1, colType.indexOf(")")).split(",");
//去头去尾
tableColumn.enum = (items as string[]).map(item => {
return item.substring(1, item.length - 1);
});
}
//日期类型，注意：日期不是设置长度，而是精度
if (tableColumn.type === "datetime" || tableColumn.type === "time" || tableColumn.type === "timestamp") {
//设置日期精度
tableColumn.precision = dbColumn["DATETIME_PRECISION"];
}
return tableColumn;
});
//设置主键列对象数组
table.primaryKeys = primaryKeys.map(primaryKey => {
return new TablePrimaryKey(primaryKey["Key_name"], primaryKey["Column_name"]);
});
//设置外键列对象数组
table.foreignKeys = dbForeignKeys
//先过滤出指定表名的数组
.filter(dbForeignKey => dbForeignKey["TABLE_NAME"] === table.name)
//创建外键对象，指定约束名称
.map(dbForeignKey => new TableForeignKey(dbForeignKey["CONSTRAINT_NAME"], [], [], "", "")); // todo: fix missing params
//设置索引数组
table.indices = dbIndices
//先过滤出指定表名，且未包含在外键、主键中的索引
.filter(dbIndex => {
return dbIndex["TABLE_NAME"] === table.name &&
(!table.foreignKeys.find(foreignKey => foreignKey.name === dbIndex["INDEX_NAME"])) &&
(!table.primaryKeys.find(primaryKey => primaryKey.name === dbIndex["INDEX_NAME"]));
})
//获取索引名称数组
.map(dbIndex => dbIndex["INDEX_NAME"])
//过滤出唯一值，这个方法可以记住
.filter((value, index, self) => self.indexOf(value) === index)
//遍历索引名称
.map(dbIndexName => {
//过滤出指定名称索引，因为上面进行了去重
const currentDbIndices = dbIndices.filter(dbIndex => dbIndex["TABLE_NAME"] === table.name && dbIndex["INDEX_NAME"] === dbIndexName);
//获取列名数组
const columnNames = currentDbIndices.map(dbIndex => dbIndex["COLUMN_NAME"]);
// 当只有一个指定名称索引，且为unique索引
if (currentDbIndices.length === 1 && currentDbIndices[0]["NON_UNIQUE"] === 0) {
//查找出列名等于索引名且列名等于索引中指定列名的列
const column = table.columns.find(column => column.name === currentDbIndices[0]["INDEX_NAME"] && column.name === currentDbIndices[0]["COLUMN_NAME"]);
if (column) {
//设置列的unique属性，这种情况不创建新的TableIndex对象
column.isUnique = true;
return;
}
}
//返回TableIndex对象，指定表名、索引名、列名数组、是否为unique索引
return new TableIndex(dbTable["TABLE_NAME"], dbIndexName, columnNames, currentDbIndices[0]["NON_UNIQUE"] === 0);
})
//移除空元素？，这个方法也要记一下
.filter(index => !!index) as TableIndex[]; // remove empty returns
return table;
}));
}
//检查指定名称数据库是否存在
async hasDatabase(database: string): Promise<boolean> {
//mysql系统表查询
const result = await this.query(`SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '${database}'`);
return result.length ? true : false;
}
//指定名称表是否存在
async hasTable(tableOrPath: Table|string): Promise<boolean> {
//解析表路径，获取对象包含了数据库名、表名
const parsedTablePath = this.parseTablePath(tableOrPath);
const sql = `SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '${parsedTablePath.database}' AND TABLE_NAME = '${parsedTablePath.tableName}'`;
const result = await this.query(sql);
return result.length ? true : false;
}
//表是否包含指定名称列
async hasColumn(tableOrPath: Table|string, column: TableColumn|string): Promise<boolean> {
//解析表路径
const parsedTablePath = this.parseTablePath(tableOrPath);
//获取列名
const columnName = column instanceof TableColumn ? column.name : column;
//查询语句
const sql = `SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '${parsedTablePath.database}' AND TABLE_NAME = '${parsedTablePath.tableName}' AND COLUMN_NAME = '${columnName}'`;
const result = await this.query(sql);
return result.length ? true : false;
}
//创建数据库，如果不存在
createDatabase(database: string): Promise<void[]> {
return this.query(`CREATE DATABASE IF NOT EXISTS ${database}`); // todo(dima): IT SHOULD NOT EXECUTE "IF NOT EXIST" if user already has a database (privileges issue)
}
//创建schema，mysql不支持
createSchema(schemas: string[]): Promise<void[]> {
return Promise.resolve([]);
}
//创建一个新的表，参数为Table对象
async createTable(table: Table): Promise<void> {
//拼接列定义字符串数组
const columnDefinitions = table.columns.map(column => this.buildCreateColumnSql(column, false)).join(", ");
//建表sql，包含列定义，没有闭合括号
let sql = `CREATE TABLE \`${this.escapeTablePath(table)}\` (${columnDefinitions}`;
//获取非自动生成主键列，自动生成主键列已经包含在列定义中了
const primaryKeyColumns = table.columns.filter(column => column.isPrimary && !column.isGenerated);
if (primaryKeyColumns.length > 0)
//拼接主键定义、引擎
sql += `, PRIMARY KEY(${primaryKeyColumns.map(column => `\`${column.name}\``).join(", ")})`;
sql += `) ENGINE=${table.engine || "InnoDB"}`;
//恢复sql，不知道什么作用
const revertSql = `DROP TABLE \`${this.escapeTablePath(table)}\``;
return this.schemaQuery(sql, revertSql);
}
//删除指定表
async dropTable(tableOrPath: Table|string): Promise<void> {
const sql = `DROP TABLE \`${this.escapeTablePath(tableOrPath)}\``;
return this.query(sql);
}
//在指定表中添加列，这个也是DDL语句，可以被存储在内存中
async addColumn(tableOrPath: Table|string, column: TableColumn): Promise<void> {
const sql = `ALTER TABLE \`${this.escapeTablePath(tableOrPath)}\` ADD ${this.buildCreateColumnSql(column, false)}`;
const revertSql = `ALTER TABLE \`${this.escapeTablePath(tableOrPath)}\` DROP \`${column.name}\``;
return this.schemaQuery(sql, revertSql);
}
//添加多个列
async addColumns(tableOrName: Table|string, columns: TableColumn[]): Promise<void> {
const queries = columns.map(column => this.addColumn(tableOrName as any, column));
await Promise.all(queries);
}
//重命名指定表中指定列
async renameColumn(tableOrName: Table|string, oldTableColumnOrName: TableColumn|string, newTableColumnOrName: TableColumn|string): Promise<void> {
let table: Table|undefined = undefined;
//获取Table对象
if (tableOrName instanceof Table) {
table = tableOrName;
} else {
table = await this.getTable(tableOrName); // todo: throw exception, this wont work because of sql memory enabled. remove support by table name
if (!table)
throw new Error(`Table ${tableOrName} was not found.`);
}
//获取更新前的TableColumn对象
let oldColumn: TableColumn|undefined = undefined;
if (oldTableColumnOrName instanceof TableColumn) {
oldColumn = oldTableColumnOrName;
} else {
oldColumn = table.columns.find(column => column.name === oldTableColumnOrName);
}
if (!oldColumn)
throw new Error(`Column "${oldTableColumnOrName}" was not found in the "${tableOrName}" table.`);
let newColumn: TableColumn|undefined = undefined;
if (newTableColumnOrName instanceof TableColumn) {
newColumn = newTableColumnOrName;
} else {
//如果只是指定名称，则克隆更新前列对象，设置新的列名
newColumn = oldColumn.clone();
newColumn.name = newTableColumnOrName;
}
//执行更改方法
return this.changeColumn(table, oldColumn, newColumn);
}
//更改表的指定列
async changeColumn(tableOrName: Table|string, oldTableColumnOrName: TableColumn|string, newColumn: TableColumn): Promise<void> {
//有病吧，两个方法都来一遍？
let table: Table|undefined = undefined;
if (tableOrName instanceof Table) {
table = tableOrName;
} else {
table = await this.getTable(tableOrName);
}
if (!table)
throw new Error(`Table ${tableOrName} was not found.`);
let oldColumn: TableColumn|undefined = undefined;
if (oldTableColumnOrName instanceof TableColumn) {
oldColumn = oldTableColumnOrName;
} else {
oldColumn = table.columns.find(column => column.name === oldTableColumnOrName);
}
if (!oldColumn)
throw new Error(`Column "${oldTableColumnOrName}" was not found in the "${tableOrName}" table.`);
//如果新列不是unique
if (newColumn.isUnique === false && oldColumn.isUnique === true)
//删除索引即可，注意这里bureturn，后面还要执行
await this.query(`ALTER TABLE \`${this.escapeTablePath(table)}\` DROP INDEX \`${oldColumn.name}\``);
//使用alter table ... change ...语句
const sql = `ALTER TABLE \`${this.escapeTablePath(table)}\` CHANGE \`${oldColumn.name}\` ${this.buildCreateColumnSql(newColumn, oldColumn.isPrimary)}`;
const revertSql = `ALTER TABLE \`${this.escapeTablePath(table)}\` CHANGE \`${oldColumn.name}\` ${this.buildCreateColumnSql(oldColumn, oldColumn.isPrimary)}`;
//执行DDL语句
return this.schemaQuery(sql, revertSql);
}
//一次改变多个列
async changeColumns(table: Table, changedColumns: { newColumn: TableColumn, oldColumn: TableColumn }[]): Promise<void> {
//注意这里遍历执行async方法没有使用foreach，而是是使用map，Promise.all
const updatePromises = changedColumns.map(async changedColumn => {
return this.changeColumn(table, changedColumn.oldColumn, changedColumn.newColumn);
});
await Promise.all(updatePromises);
}
//删除表中指定列
async dropColumn(table: Table, column: TableColumn): Promise<void> {
const sql = `ALTER TABLE \`${this.escapeTablePath(table)}\` DROP \`${column.name}\``;
const revertSql = `ALTER TABLE \`${this.escapeTablePath(table)}\` ADD ${this.buildCreateColumnSql(column, false)}`;
return this.schemaQuery(sql, revertSql);
}
//删除多个列
async dropColumns(table: Table, columns: TableColumn[]): Promise<void> {
const dropPromises = columns.map(column => this.dropColumn(table, column));
await Promise.all(dropPromises);
}
//更新表的主键，这一步在什么情况下执行？，创建表时已经包含主键了
async updatePrimaryKeys(table: Table): Promise<void> {
//如果表没有自动生成列
if (!table.hasGeneratedColumn)
//只删除主键列
await this.query(`ALTER TABLE \`${this.escapeTablePath(table)}\` DROP PRIMARY KEY`);
//获取非自动生成主键列
const primaryColumnNames = table.columns
.filter(column => column.isPrimary && !column.isGenerated)
.map(column => "`" + column.name + "`");
//添加主键列
if (primaryColumnNames.length > 0) {
const sql = `ALTER TABLE \`${this.escapeTablePath(table)}\` ADD PRIMARY KEY (${primaryColumnNames.join(", ")})`;
const revertSql = `ALTER TABLE \`${this.escapeTablePath(table)}\` DROP PRIMARY KEY`;
return this.schemaQuery(sql, revertSql);
}
}
//在指定表中创建外键列
async createForeignKey(tableOrPath: Table|string, foreignKey: TableForeignKey): Promise<void> {
//拼接外键列名
const columnNames = foreignKey.columnNames.map(column => "`" + column + "`").join(", ");
//拼接引用列名
const referencedColumnNames = foreignKey.referencedColumnNames.map(column => "`" + column + "`").join(",");
//添加外键约束
let sql = `ALTER TABLE \`${this.escapeTablePath(tableOrPath)}\` ADD CONSTRAINT \`${foreignKey.name}\` ` +
`FOREIGN KEY (${columnNames}) ` +
//引用表名
`REFERENCES \`${foreignKey.referencedTableName}\`(${referencedColumnNames})`;
//如果包含onDelete属性，拼接它
//on delete 可以决定当删除主表时如何处理从表记录
//"RESTRICT"：如果从表引用了要删除的主表，则不允许删除
//"CASCADE"：删除所有引用的从表记录
//"SET NULL"：在从表中将外键列设为null，外键列为not null时会出问题
//"DEFAULT
if (foreignKey.onDelete) sql += " ON DELETE " + foreignKey.onDelete;
const revertSql = `ALTER TABLE \`${this.escapeTablePath(tableOrPath)}\` DROP FOREIGN KEY \`${foreignKey.name}\``;
//执行DDL语句
return this.schemaQuery(sql, revertSql);
}
//创建多个外键列
async createForeignKeys(tableOrName: Table|string, foreignKeys: TableForeignKey[]): Promise<void> {
const promises = foreignKeys.map(foreignKey => this.createForeignKey(tableOrName as any, foreignKey));
await Promise.all(promises);
}
//删除指定外键，只删除约束，不删除列
async dropForeignKey(tableOrPath: Table|string, foreignKey: TableForeignKey): Promise<void> {
//删除外键约束
const sql = `ALTER TABLE \`${this.escapeTablePath(tableOrPath)}\` DROP FOREIGN KEY \`${foreignKey.name}\``;
//外键列名
const columnNames = foreignKey.columnNames.map(column => "`" + column + "`").join(", ");
//引用列名
const referencedColumnNames = foreignKey.referencedColumnNames.map(column => "`" + column + "`").join(",");
//恢复sql
let revertSql = `ALTER TABLE \`${this.escapeTablePath(tableOrPath)}\` ADD CONSTRAINT \`${foreignKey.name}\` ` +
`FOREIGN KEY (${columnNames}) ` +
`REFERENCES \`${foreignKey.referencedTableName}\`(${referencedColumnNames})`;
if (foreignKey.onDelete) revertSql += " ON DELETE " + foreignKey.onDelete;
return this.schemaQuery(sql, revertSql);
}
//删除多个外键列
async dropForeignKeys(tableOrName: Table|string, foreignKeys: TableForeignKey[]): Promise<void> {
const promises = foreignKeys.map(foreignKey => this.dropForeignKey(tableOrName as any, foreignKey));
await Promise.all(promises);
}
//创建索引
async createIndex(table: Table|string, index: TableIndex): Promise<void> {
//拼接索引包含的列名
const columns = index.columnNames.map(columnName => "`" + columnName + "`").join(", ");
//索引可以设置是否为unique
const sql = `CREATE ${index.isUnique ? "UNIQUE " : ""}INDEX \`${index.name}\` ON \`${this.escapeTablePath(table)}\`(${columns})`;
//恢复sql
const revertSql = `ALTER TABLE \`${this.escapeTablePath(table)}\` DROP INDEX \`${index.name}\``;
//执行DDL
await this.schemaQuery(sql, revertSql);
}
//删除索引
async dropIndex(tableOrPath: Table|string, index: TableIndex|string): Promise<void> {
//索引名称
const indexName = index instanceof TableIndex ? index.name : index;
const sql = `ALTER TABLE \`${this.escapeTablePath(tableOrPath)}\` DROP INDEX \`${indexName}\``;
//这个用法有些不统一！毕竟schemaQuery里面有个缓存过程
if (index instanceof TableIndex) {
const columns = index.columnNames.map(columnName => "`" + columnName + "`").join(", ");
const revertSql = `CREATE ${index.isUnique ? "UNIQUE " : ""}INDEX \`${index.name}\` ON \`${this.escapeTablePath(tableOrPath)}\`(${columns})`;
await this.schemaQuery(sql, revertSql);
} else {
await this.query(sql);
}
}
//截断表
async truncate(tableOrPath: Table|string): Promise<void> {
await this.query(`TRUNCATE TABLE \`${this.escapeTablePath(tableOrPath)}\``);
}
//从当前连接数据库中移除所有的表，这个方法根第一个参数有关系吗？
async clearDatabase(tables?: string[], database?: string): Promise<void> {
//开启事务
await this.startTransaction();
try {
//关闭外键检查sql
const disableForeignKeysCheckQuery = `SET FOREIGN_KEY_CHECKS = 0;`;
//直接从数据库中查询出删除表的sql语句
const dropTablesQuery = `SELECT concat('DROP TABLE IF EXISTS \`', table_schema, '\`.\`', table_name, '\`;') AS query FROM information_schema.tables WHERE table_schema = '${database}'`;
//启用外键检查sql
const enableForeignKeysCheckQuery = `SET FOREIGN_KEY_CHECKS = 1;`;
//关闭外键检查
await this.query(disableForeignKeysCheckQuery);
//获取删除sql结果数组
const dropQueries: ObjectLiteral[] = await this.query(dropTablesQuery);
//执行删除表sql
await Promise.all(dropQueries.map(query => this.query(query["query"])));
//关闭外键检查
await this.query(enableForeignKeysCheckQuery);
//提交事务
await this.commitTransaction();
} catch (error) {
try {
//出错，回滚
await this.rollbackTransaction();
} catch (rollbackError) { }
throw error;
}
}
//启用内存sql存储，一般只存储DDL语句
enableSqlMemory(): void {
this.sqlMemoryMode = true;
}
//禁用内存sql存储
disableSqlMemory(): void {
this.sqlsInMemory = [];
this.sqlMemoryMode = false;
}
//获取内存存储sql
getMemorySql(): (string|{ up: string, down: string })[] {
return this.sqlsInMemory;
}
//执行结构sql，即DDL
//upQuery：创建sql
//downQuery：移除sql
protected async schemaQuery(upQuery: string, downQuery: string): Promise<void> {
//内存sql模式开启，则只存储sql，不执行，什么时候执行？
if (this.sqlMemoryMode === true) {
this.sqlsInMemory.push({ up: upQuery, down: downQuery });
return Promise.resolve() as Promise<any>;
}
//执行创建sql，没看出这个downQuery什么作用
await this.query(upQuery);
}
//解析表路径，返回个对象，包含数据库名、表名
protected parseTablePath(tableOrPath: Table|string) {
if (tableOrPath instanceof Table) {
return {
database: tableOrPath.database || this.driver.database,
tableName: tableOrPath.name
};
} else {
return {
database: tableOrPath.indexOf(".") !== -1 ? tableOrPath.split(".")[0] : this.driver.database,
tableName: tableOrPath.indexOf(".") !== -1 ? tableOrPath.split(".")[1] : tableOrPath
};
}
}
//转义表路径，直接获取数据库名.表名
protected escapeTablePath(tableOrPath: Table|string): string {
if (tableOrPath instanceof Table)
return tableOrPath.database ? `${tableOrPath.database}\`.\`${tableOrPath.name}` : `${tableOrPath.name}`;
return tableOrPath.split(".").map(i => `${i}`).join("\`.\`");
}
//参数化给定对象的属性名，返回数组，字符串为key=？,包含占位符
protected parametrize(objectLiteral: ObjectLiteral): string[] {
//获取一个数组，包含值占位符，在update中使用，也可以在where中使用
return Object.keys(objectLiteral).map(key => `\`${key}\`=?`);
}
//构建创建列的sql，第二个参数为是否跳过主键
protected buildCreateColumnSql(column: TableColumn, skipPrimary: boolean) {
//列名+完整类型
let c = "`" + column.name + "` " + this.connection.driver.createFullType(column);
//枚举值
if (column.enum)
c += "(" + column.enum.map(value => "'" + value + "'").join(", ") + ")";
//列字符集
if (column.charset)
c += " CHARACTER SET " + column.charset;
//列校对
if (column.collation)
c += " COLLATE " + column.collation;
//not null
if (column.isNullable !== true)
c += " NOT NULL";
//unique
if (column.isUnique === true)
c += " UNIQUE";
//自动生成主键列，非自动生成主键在最后设置
if (column.isGenerated && column.isPrimary && !skipPrimary)
c += " PRIMARY KEY";
//increment
if (column.isGenerated === true && column.generationStrategy === "increment") // don't use skipPrimary here since updates can update already exist primary without auto inc.
c += " AUTO_INCREMENT";
//列注解
if (column.comment)
c += " COMMENT '" + column.comment + "'";
//列默认值
if (column.default !== undefined && column.default !== null)
c += " DEFAULT " + column.default;
return c;
}
}
QueryRunner为typeorm中执行数据库操作的最底层对象，所有的其他对象EntityManager、Repository、QueryBuilder都在内部使用了QueryRunner
QueryRunner内部调用了底层连接，如nodejs的mysql包中的连接对象，来执行操作，核心方法为query，其他所有方法DML、DDL都是通过query方法执行
QueryRunner中封装了很多DDL语句，如数据库、表、列、索引、外键等的创建、更改、删除操作
