ali-sdk / ali-rds Goto Github PK

View Code? Open in Web Editor NEW

278.0 278.0 70.0 180 KB

Aliyun RDS client

License: Other

TypeScript 100.00%

ali-rds's Introduction

ali-sdk

SDK include all aliyun, taobao, tmall, alibaba open serivces.

Goal of this module

Let global users can all use ali's services more easily.

Support Services

aliyun

name	status	description	version	usage	origin api
OSS	DONE	Simple Store like S3	-	OSS Usage	OSS API
OTS	TODO	Table Store like HBase	-	OTS Usage	OTS API
OCS	ING	Cache Service like Memcached	-	OCS Usage	OCS protocol
ECS	TODO	Elastic Compute like EC2	-	ECS Usage	-
RDS	ING	Relational Database like MySQL	-	RDS Usage	-
DRDS	TODO	Distribute Relational Database	-	DRDS Usage	DRDS API
SLB	TODO	Server Load Balancer	-	SLB Usage	-
CDN	TODO	Content Delivery Network	v1.6 2014-10-01	CDN Usage	CDN API
SLS	TODO	Simple Log Service	-	SLS Usage	SLS API
MQS	TODO	Message Queue	2014-07-08	MQS Usage	MQS-API
OpenSearch	TODO	Search Engine	-	OpenSearch Usage	OpenSearch API
MTS	TODO	Multimedia Transcoding Service	v1501	MTS Usage	MTS API
ACE	TODO	Aliyun Cloud Engine	-	ACE Usage	[ACE API]
ESS	TODO	Elastic Scaling	-	ESS Usage	ESS API
OAS	TODO	Archive Service	-	OAS Usage	OAS API
KVStore	ING	Key-Value Store like Redis	-	KVStore Usage	KVStore API
ODPS	TODO	Data Processing like Hadoop	-	ODPS Usage	ODPS API
ADS	TODO	Analytic Database, Realtime OLAP	-	ADS Usage	[ADS API]

taobao

TBD

tmall

TBD

alibaba

TBD

License

MIT

ali-rds's People

Contributors

Stargazers

Watchers

ali-rds's Issues

db.query对应的原生sql如何打印

let rows = yield db.query('SELECT * FROM your_table WHERE id=:id', { id: 123 });
console.log(rows);

如何查看这个sql查询的原生sql，如果sql很复杂，需要获取原生sql查看问题

where 空数组问题

如果 where 是一个空的数组会报错，msyql 语法有问题。感觉这种情况是不是可以直接退出，不查询了。一个空的 where 就返回一个空就行了。

ali-rds/lib/operator.js

Line 193 in e4aed30

if (Array.isArray(value)) {

sqlMessage: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \')\' at line 1'
sqlState: '42000'
index: 0
sql: 'SELECT * FROM `table` WHERE `tableId` IN ()'

事务中间出错并不会自动回滚

'use strict'
const rds = require('ali-rds');

const db = rds({
    host: '',
    user: '',
    password: '',
    pool: true,
    port: 3306,
    database: "test"
});

require('co')(function *() {
    //例子1
    var result = yield db.beginTransactionScope(function*(conn) {
        yield conn.update('user', {
            email: '7'
        }, {
            where: {id: 54},
        });
        yield conn.insert('123', {a: 1});
        return {success: true};
    });

    //例子2
    let tran = yield db.beginTransaction();
    try {
        yield tran.update('user', {
            email: '2'
        }, {
            where: {id: 54},
        });
        throw new Error('123');
        yield tran.commit();
    } catch (err) {
        yield tran.rollback(); // rollback call won't throw err
        throw err;
    }
})

在上面2段代码中,如果事务中间执行出错期间并不会回滚, 自己也在打算写类似这样的库, 但是一直困扰mysql事务回滚的问题

pg中这么写是没有问题的

不知道你有研究过么,有什么好的解决方案, 麻烦你了

update 中带有单引号的字符串就多重转义

mysql.update('tablename', {id, name:"a'b"})

转义为

update tablename set name = 'a\\'b' where id=1

导致执行mysql报错

where condition with null in mysql

ali-rds/test/operator.test.js

Line 18 in e4aed30

 assert.equal(op._where({ id: 1, name2: null }), ' WHERE `id` = 1 AND `name2` = NULL'); 

should be:

 assert.equal(op._where({ id: 1, name2: null }), ' WHERE `id` = 1 AND `name2` IS NULL');

如何查看生成的sql语句

let list = await this.ctx.app.mysql.select('article',{
      where:{title:`%${title}%`},
      limit:count,
      offset:(p-1)*count
    });

如何查看生成的sql语句，用的是egg

how to select from two or more tables

select a.A,b.B from a,b where a.A=b.B

+

垃圾玩意，KPI产物

木有文档，各种没法用

mysql配置项说明不完全

rds是基于mysql库开发的，但在readme中没有体现相关配置。这样对于由前端进入nodejs开发的同学来说，可能就会忽略了这些东西。建议添加完善配置说明，或者追加mysql库的说明文档链接。

where 是否可以支持比较？

源码如下：

proto._where = function(where) {
  if (!where) {
    return '';
  }

  const wheres = [];
  const values = [];
  for (const key in where) {
    const value = where[key];
    if (Array.isArray(value)) {
      wheres.push('?? IN (?)');
    } else {
      wheres.push('?? = ?');
    }
    values.push(key);
    values.push(value);
  }
  if (wheres.length > 0) {
    return this.format(' WHERE ' + wheres.join(' AND '), values);
  }
  return '';

};

看起来仅支持 IN 和 =，但是 != 和其他常用比较运算符也很有用啊，为啥不能顺便支持呢？

还是说，官方推荐，其他比较运算符，自己写 SQL 呢？

typescript

能支持ts吗

连接sql server 有问题

代码
连接信息都是对的

  async sql() {

    const db = rds({
        host: '',
        port:,
        user: '',
        password: '',
        database: ""
      });
      
      let res = await db.query('SELECT * FROM HrmResource from id = 3');

    return res;
  }

错误信息

Handshake inactivity timeout
    at Handshake.<anonymous> (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/protocol/Protocol.js:164:17)
    at emitNone (events.js:106:13)
    at Handshake.emit (events.js:208:7)
    at Handshake._onTimeout (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/protocol/sequences/Sequence.js:129:8)
    at ontimeout (timers.js:482:11)
    at tryOnTimeout (timers.js:317:5)
    at Timer.listOnTimeout (timers.js:277:5)
    --------------------
    at Protocol._enqueue (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/protocol/Protocol.js:145:48)
    at Protocol.handshake (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/protocol/Protocol.js:52:23)
    at PoolConnection.connect (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/Connection.js:130:18)
    at Pool.getConnection (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/Pool.js:48:16)
    at /Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:29:7
    at new Promise (<anonymous>)
    at Pool.<anonymous> (/Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:12:10)
    at Pool.ret [as getConnection] (/Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:56:34)
    at Pool.query (/Users/hilter/work/npm/shellPortal/node_modules/mysql/lib/Pool.js:202:8)
    at /Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:29:7
    at new Promise (<anonymous>)
    at Pool.<anonymous> (/Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:12:10)
    at Pool.ret [as query] (/Users/hilter/work/npm/shellPortal/node_modules/ali-rds/node_modules/pify/index.js:56:34)
    at RDSClient.proto._query (/Users/hilter/work/npm/shellPortal/node_modules/ali-rds/lib/client.js:34:20)

如何监听触发器呢？

怎么输出执行的SQL语句呢？

作者你好！
不知道有没有提供方法输出当前执行的sql语句，方便日志调试。

support group by

请问能加上这个的扩展嘛谢谢虽然自己拼也可以解决(因为用了egg.js 然后发现egg-mysql插件又是用的ali-rds。。。)

count接口问题，报语法错误！！！

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' fund_id = 2' at line 1 (code: ER_PARSE_ERROR)


let queryResult = await this.app.mysql.count('fund_lp_relation', {
            where: {
                status: 1,
                fund_id: 2
            }
 });


let queryResult = await this.app.mysql.query(`SELECT COUNT(*) FROM fund_lp_relation WHERE status = 1 and fund_id = 2`)

上面2种写法都没毛病吧，但是用count接口就报如上错误

有计划升级，支持mysql8.0吗

支持AS语法（别名）吗？

使用 AS 指定列名，比如：

let rows = yield db.select('table-name', {
  columns: ['author AS owner'],
});

以上用法会报错：unknown column 'author AS owner'。
正确的用法是什么？能否支持？

这里为啥跳过了

ali-rds/lib/operator.js

Lines 163 to 166 in 004713c

 if (column in options.where) { 

 continue; 

 } 

 sets.push('?? = ?');

比如

update({ is_read: 1, gmt_modified: 'now()' }, {
  where: { is_read: 0, user_id: 'xxx' },
});

这种情况转换的 sql 会是

update xxx set gmt_modified = now() where is_read = 0 and user_id = 'xxx'

预期应该是

update xxx set gmt_modified = now(), is_read = 1 where is_read = 0 and user_id = 'xxx'

怎么进行多库联查？

假如我有 A, B两个数据库
A库有个admin_account表用于存管理员数据
B库有个comment_info表用于存评论数据
B库得comment_info表中有一个字段adminId对应着A库admin_account的id
现在我查询B库comment_info中的评论数据的时候，想联合查询出A表中id=adminId的数据。

使用mysql时是否支持读写分离？

kpi的产物。我能想象到当年的晋升答辩上，你们是如何吹的天花乱坠。。。

kpi的产物。我能想象到当年的晋级答辩上，你们是如何吹的天花乱坠。。。

select 不支持比较操作啊

建议换成 mysql2，它支持 JSON parse 和 caching_sha2_password

mysqljs/mysql 不支持 mysql 8 的默认认证插件caching_sha2_password；不支持 JSON 列的对象解析

operator.update, columns in where cant be updated

https://github.com/ali-sdk/ali-rds/blob/master/lib/operator.js#L161
code here will filter columns in where.

...
for (let i = 0; i < options.columns.length; i++) {
  const column = options.columns[i];
  // below here filter column in where
  if (column in options.where) {
    continue;
  }
  sets.push('?? = ?');
  values.push(column);
  values.push(row[column]);
}
...

support object param

query('select * from table where name = ? and id = ?', [name, id]) =>

query('select * from table where name = :name and id = :id', {name, id})

thread

doc fix: 'Can\'t not auto detect...'

just fix a doc miss: 'Can't not ...'

src: lib/operator.js: Line 152

throw new Error('Can\'t not auto detect update condition, please set options.where, or make sure obj.id exists');

Error: ER_BAD_FIELD_ERROR: Unknown column 'balance, out_sum' in 'field list'

get 方法不能匹配 null 字段

使用 this.app.mysql.get方法时，如果有字段 a 是 null，生成的 sql 代码是：

"SELECT * FROM page WHERE a = NULL"
这会导致匹配不到正确的数据，需要生成：

"SELECT * FROM page WHERE a is NULL"

select 不能支持sql函数如sum

如
select fund_id, sum(amount) as totalAmount from fund_token_order group by fund_id

这是合法的sql 但是在这个库里面无法操作
导致应用无法快速计算相同字段的数据和

update 有 bug

proto.update = function* (table, row, options) {
  // TODO: support multi rows
  options = options || {};
  if (!options.columns) {
    options.columns = Object.keys(row);
  }
  if (!options.where) {
    if (!('id' in row)) {
      throw new Error('Can not auto detect update condition, please set options.where, or make sure obj.id exists');
    }
    options.where = {
      id: row.id,
    };
  }

  const sets = [];
  const values = [];
  for (let i = 0; i < options.columns.length; i++) {
    const column = options.columns[i];
    if (column in options.where) {
      continue;
    }
    sets.push('?? = ?');
    values.push(column);
    values.push(row[column]);
  }
  const sql = this.format('UPDATE ?? SET ', [ table ]) +
    this.format(sets.join(', '), values) +
    this._where(options.where);
  debug('update(%j, %j, %j) \n=> %j', table, row, options, sql);
  return yield this.query(sql);
};

用例

update(
  'table',
  {
    status: 1,
  },
  {
    status: 0
  }
)

把所有未读的 status 改成已读，这里执行下来，sets 是个 []

请问update更新字段值如何使用表达式

row想用表达式但是这样子不行，请问该怎么做

当 where 和 row 中有相同值时, update 会报错

// 这种情况的意思是 查询 userid 等于 12  并且 key 等于 'world' 的然后改成 hello
mysql.update('table',{ key: 'hello' }, { where: { userid:12, key:'world'  } })

     Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `userid` = 1 AND `key` = 'wo' at line 1
      at Query.Sequence._packetToError (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
      at Query.ErrorPacket (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/protocol/sequences/Query.js:77:18)
      at Protocol._parsePacket (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/protocol/Protocol.js:278:3)
      at Parser.write (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/protocol/Parser.js:76:12)
      at Protocol.write (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/protocol/Protocol.js:38:16)
      at Socket.<anonymous> (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/Connection.js:91:28)
      at Socket.<anonymous> (test/fixtures/apps/api-utils-test/node_modules/mysql/lib/Connection.js:502:10)
      at addChunk (_stream_readable.js:263:12)
      at readableAddChunk (_stream_readable.js:250:11)
      at Socket.Readable.push (_stream_readable.js:208:10)
      at TCP.onread (net.js:597:20)

但这种情况在 mysql 是支持的

原因

发现是因为ali-rds 中判断了如果条件和 set 值相同，将会被过滤，导致 set 为空

select('table', {
  where: {
    id: ['882066002862186496', '882066002996400128']
  }
})

实际的调用应该是

select('table', {
  where: {
    id: [882066002862186496, 882066002996400128]
  }
})

let SQL = [ 'UPDATE ?? SET ' ];
  let VALUES = [ table ];

  const TEMPLATE = [];
  for (const key in SQL_CASE) {
    let templateSql = ' ?? = CASE ';
    VALUES.push(key);
    templateSql += SQL_CASE[key].when.join(' ');
    VALUES = VALUES.concat(SQL_CASE[key].then);
    templateSql += ' ELSE ?? END ';
    TEMPLATE.push(templateSql);
    VALUES.push(key);
  }

  SQL += TEMPLATE.join(' , ');

	if (column in options.where) {
	continue;
	}
	sets.push('?? = ?');