-
-
Notifications
You must be signed in to change notification settings - Fork 9
/
update.js
executable file
·140 lines (129 loc) · 4.49 KB
/
update.js
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
#!/usr/bin/env node
// https://cirosantilli.com/sequelize-example
const assert = require('assert');
const path = require('path');
const { DataTypes, Op } = require('sequelize');
const common = require('./common')
const sequelize = common.sequelize(__filename, process.argv[2], { define: { timestamps: false } })
;(async () => {
const Inverses = sequelize.define('Inverses',
{
myValue: {
type: DataTypes.INTEGER,
primaryKey: true,
},
inverse: {
type: DataTypes.INTEGER,
},
name: {
type: DataTypes.STRING,
},
},
{ timestamps: false }
);
await Inverses.sync({ force: true })
async function reset() {
await sequelize.truncate({ cascade: true })
await Inverses.create({ myValue: 2, inverse: -2, name: 'two' });
await Inverses.create({ myValue: 3, inverse: -3, name: 'three' });
await Inverses.create({ myValue: 5, inverse: -5, name: 'five' });
}
await reset()
let rows
// Initial state.
rows = await Inverses.findAll({ order: [['myValue', 'ASC']]})
common.assertEqual(rows, [
{ myValue: 2, inverse: -2, name: 'two' },
{ myValue: 3, inverse: -3, name: 'three' },
{ myValue: 5, inverse: -5, name: 'five' },
])
// Update to fixed myValue.
await Inverses.update(
{ inverse: 0, },
{ where: { myValue: { [Op.gt]: 2 } } },
);
rows = await Inverses.findAll({ order: [['myValue', 'ASC']]})
common.assertEqual(rows, [
{ myValue: 2, inverse: -2, name: 'two' },
{ myValue: 3, inverse: 0, name: 'three' },
{ myValue: 5, inverse: 0, name: 'five' },
])
await reset()
// Update to match another column.
await Inverses.update(
{ inverse: sequelize.col('myValue'), },
{ where: { myValue: { [Op.gt]: 2 } } },
);
rows = await Inverses.findAll({ order: [['myValue', 'ASC']]})
common.assertEqual(rows, [
{ myValue: 2, inverse: -2, name: 'two' },
{ myValue: 3, inverse: 3, name: 'three' },
{ myValue: 5, inverse: 5, name: 'five' },
])
await reset()
// Update to match another column with modification.
await Inverses.update(
{ inverse: sequelize.fn('1 + ', sequelize.col('myValue')), },
{ where: { myValue: { [Op.gt]: 2 } } },
);
rows = await Inverses.findAll({ order: [['myValue', 'ASC']]})
common.assertEqual(rows, [
{ myValue: 2, inverse: -2, name: 'two' },
{ myValue: 3, inverse: 4, name: 'three' },
{ myValue: 5, inverse: 6, name: 'five' },
])
await reset()
// A string function test.
await Inverses.update(
{ name: sequelize.fn('upper', sequelize.col('name')), },
{ where: { myValue: { [Op.gt]: 2 } } },
);
rows = await Inverses.findAll({ order: [['myValue', 'ASC']]})
common.assertEqual(rows, [
{ myValue: 2, inverse: -2, name: 'two' },
{ myValue: 3, inverse: -3, name: 'THREE' },
{ myValue: 5, inverse: -5, name: 'FIVE' },
])
await reset()
// Operator without parenthesis, e.g. col1 + col2. Possible with the "where" hack mentioned at:
// https://stackoverflow.com/questions/39946586/sequelize-sum-between-two-columns-in-model/62493655#62493655
// but that is so ugly I wonder if I should just use literal instead.
// * https://stackoverflow.com/questions/39946586/sequelize-sum-between-two-columns-in-model
// * https://stackoverflow.com/questions/48778789/addition-and-subtraction-assignment-operator-with-sequelize
await Inverses.update(
{ inverse: sequelize.where(sequelize.col('myValue'), '*', sequelize.col('inverse')), },
{ where: { myValue: { [Op.gt]: 2 } } },
);
rows = await Inverses.findAll({ order: [['myValue', 'ASC']]})
common.assertEqual(rows, [
{ myValue: 2, inverse: -2, name: 'two' },
{ myValue: 3, inverse: -9, name: 'three' },
{ myValue: 5, inverse: -25, name: 'five' },
])
await reset()
// Equivalent literal version.
// We have to manually quote for PostgreSQL because of the upper case V.
await Inverses.update(
{ inverse: sequelize.literal('"myValue" * "inverse"'), },
{ where: { myValue: { [Op.gt]: 2 } } },
);
rows = await Inverses.findAll({ order: [['myValue', 'ASC']]})
common.assertEqual(rows, [
{ myValue: 2, inverse: -2, name: 'two' },
{ myValue: 3, inverse: -9, name: 'three' },
{ myValue: 5, inverse: -25, name: 'five' },
])
await reset()
// With a literal multiplier instead.
await Inverses.update(
{ inverse: sequelize.where(sequelize.col('myValue'), '*', -2), },
{ where: { myValue: { [Op.gt]: 2 } } },
);
rows = await Inverses.findAll({ order: [['myValue', 'ASC']]})
common.assertEqual(rows, [
{ myValue: 2, inverse: -2, name: 'two' },
{ myValue: 3, inverse: -6, name: 'three' },
{ myValue: 5, inverse: -10, name: 'five' },
])
await reset()
})().finally(() => { return sequelize.close() });