1 | 'use strict'
|
2 |
|
3 | const TYPES = require('./datatypes').TYPES
|
4 | const declareType = require('./datatypes').declare
|
5 | const objectHasProperty = require('./utils').objectHasProperty
|
6 |
|
7 | const MAX = 65535
|
8 | const JSON_COLUMN_ID = 'JSON_F52E2B61-18A1-11d1-B105-00805F49916B'
|
9 |
|
10 | function Table (name) {
|
11 | if (name) {
|
12 | const parsed = Table.parseName(name)
|
13 | this.name = parsed.name
|
14 | this.schema = parsed.schema
|
15 | this.database = parsed.database
|
16 | this.path = (this.database ? `[${this.database}].` : '') + (this.schema ? `[${this.schema}].` : '') + `[${this.name}]`
|
17 | this.temporary = this.name.charAt(0) === '#'
|
18 | }
|
19 |
|
20 | this.columns = []
|
21 | this.rows = []
|
22 |
|
23 | Object.defineProperty(this.columns, 'add', {
|
24 | value (name, column, options) {
|
25 | if (column == null) {
|
26 | throw new Error('Column data type is not defined.')
|
27 | }
|
28 | if (column instanceof Function) {
|
29 | column = column()
|
30 | }
|
31 |
|
32 | options = options || {}
|
33 | column.name = name
|
34 | column.nullable = options.nullable
|
35 | column.primary = options.primary
|
36 | if (objectHasProperty(options, 'length')) {
|
37 | column.length = options.length
|
38 | }
|
39 |
|
40 | return this.push(column)
|
41 | }
|
42 | }
|
43 | )
|
44 |
|
45 | Object.defineProperty(this.rows, 'add', {
|
46 | value () {
|
47 | return this.push(Array.prototype.slice.call(arguments))
|
48 | }
|
49 | }
|
50 | )
|
51 | }
|
52 |
|
53 |
|
54 |
|
55 |
|
56 |
|
57 | Table.prototype._makeBulk = function _makeBulk () {
|
58 | for (let i = 0; i < this.columns.length; i++) {
|
59 | const col = this.columns[i]
|
60 | switch (col.type) {
|
61 | case TYPES.Date:
|
62 | case TYPES.DateTime:
|
63 | case TYPES.DateTime2:
|
64 | for (let j = 0; j < this.rows.length; j++) {
|
65 | const dateValue = this.rows[j][i]
|
66 | if (typeof dateValue === 'string' || typeof dateValue === 'number') {
|
67 | const date = new Date(dateValue)
|
68 | if (isNaN(date.getDate())) {
|
69 | throw new TypeError('Invalid date value passed to bulk rows')
|
70 | }
|
71 | this.rows[j][i] = date
|
72 | }
|
73 | }
|
74 | break
|
75 |
|
76 | case TYPES.Xml:
|
77 | col.type = TYPES.NVarChar(MAX).type
|
78 | break
|
79 |
|
80 | case TYPES.UDT:
|
81 | case TYPES.Geography:
|
82 | case TYPES.Geometry:
|
83 | col.type = TYPES.VarBinary(MAX).type
|
84 | break
|
85 |
|
86 | default:
|
87 | break
|
88 | }
|
89 | }
|
90 |
|
91 | return this
|
92 | }
|
93 |
|
94 | Table.prototype.declare = function declare () {
|
95 | const pkey = this.columns.filter(col => col.primary === true).map(col => col.name)
|
96 | const cols = this.columns.map(col => {
|
97 | const def = [`[${col.name}] ${declareType(col.type, col)}`]
|
98 |
|
99 | if (col.nullable === true) {
|
100 | def.push('null')
|
101 | } else if (col.nullable === false) {
|
102 | def.push('not null')
|
103 | }
|
104 |
|
105 | if (col.primary === true && pkey.length === 1) {
|
106 | def.push('primary key')
|
107 | }
|
108 |
|
109 | return def.join(' ')
|
110 | })
|
111 |
|
112 | const constraint = pkey.length > 1 ? `, constraint PK_${this.temporary ? this.name.substr(1) : this.name} primary key (${pkey.join(', ')})` : ''
|
113 | return `create table ${this.path} (${cols.join(', ')}${constraint})`
|
114 | }
|
115 |
|
116 | Table.fromRecordset = function fromRecordset (recordset, name) {
|
117 | const t = new this(name)
|
118 |
|
119 | for (const colName in recordset.columns) {
|
120 | if (objectHasProperty(recordset.columns, colName)) {
|
121 | const col = recordset.columns[colName]
|
122 |
|
123 | t.columns.add(colName, {
|
124 | type: col.type,
|
125 | length: col.length,
|
126 | scale: col.scale,
|
127 | precision: col.precision
|
128 | }, {
|
129 | nullable: col.nullable
|
130 | })
|
131 | }
|
132 | }
|
133 |
|
134 | if (t.columns.length === 1 && t.columns[0].name === JSON_COLUMN_ID) {
|
135 | for (let i = 0; i < recordset.length; i++) {
|
136 | t.rows.add(JSON.stringify(recordset[i]))
|
137 | }
|
138 | } else {
|
139 | for (let i = 0; i < recordset.length; i++) {
|
140 | t.rows.add.apply(t.rows, t.columns.map(col => recordset[i][col.name]))
|
141 | }
|
142 | }
|
143 |
|
144 | return t
|
145 | }
|
146 |
|
147 | Table.parseName = function parseName (name) {
|
148 | const length = name.length
|
149 | let cursor = -1
|
150 | let buffer = ''
|
151 | let escaped = false
|
152 | const path = []
|
153 |
|
154 | while (++cursor < length) {
|
155 | const char = name.charAt(cursor)
|
156 | if (char === '[') {
|
157 | if (escaped) {
|
158 | buffer += char
|
159 | } else {
|
160 | escaped = true
|
161 | }
|
162 | } else if (char === ']') {
|
163 | if (escaped) {
|
164 | escaped = false
|
165 | } else {
|
166 | throw new Error('Invalid table name.')
|
167 | }
|
168 | } else if (char === '.') {
|
169 | if (escaped) {
|
170 | buffer += char
|
171 | } else {
|
172 | path.push(buffer)
|
173 | buffer = ''
|
174 | }
|
175 | } else {
|
176 | buffer += char
|
177 | }
|
178 | }
|
179 |
|
180 | if (buffer) {
|
181 | path.push(buffer)
|
182 | }
|
183 |
|
184 | switch (path.length) {
|
185 | case 1:
|
186 | return {
|
187 | name: path[0],
|
188 | schema: null,
|
189 | database: null
|
190 | }
|
191 |
|
192 | case 2:
|
193 | return {
|
194 | name: path[1],
|
195 | schema: path[0],
|
196 | database: null
|
197 | }
|
198 |
|
199 | case 3:
|
200 | return {
|
201 | name: path[2],
|
202 | schema: path[1],
|
203 | database: path[0]
|
204 | }
|
205 |
|
206 | default:
|
207 | throw new Error('Invalid table name.')
|
208 | }
|
209 | }
|
210 |
|
211 | module.exports = Table
|