-
Notifications
You must be signed in to change notification settings - Fork 115
/
index.js
141 lines (118 loc) · 3.77 KB
/
index.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
141
import fu from '@/lib/utils/fileIo'
// We can import workers like so because of worker-loader:
// https://webpack.js.org/loaders/worker-loader/
import Worker from './_worker.js'
// Use promise-worker in order to turn worker into the promise based one:
// https://github.com/nolanlawson/promise-worker
import PromiseWorker from 'promise-worker'
function getNewDatabase () {
const worker = new Worker()
return new Database(worker)
}
export default {
getNewDatabase
}
let progressCounterIds = 0
class Database {
constructor (worker) {
this.dbName = null
this.schema = null
this.worker = worker
this.pw = new PromiseWorker(worker)
this.importProgresses = {}
worker.addEventListener('message', e => {
const progress = e.data.progress
if (progress !== undefined) {
const id = e.data.id
this.importProgresses[id].dispatchEvent(new CustomEvent('progress', {
detail: progress
}))
}
})
}
shutDown () {
this.worker.terminate()
}
createProgressCounter (callback) {
const id = progressCounterIds++
this.importProgresses[id] = new EventTarget()
this.importProgresses[id].addEventListener('progress', e => { callback(e.detail) })
return id
}
deleteProgressCounter (id) {
delete this.importProgresses[id]
}
async addTableFromCsv (tabName, data, progressCounterId) {
const result = await this.pw.postMessage({
action: 'import',
data,
progressCounterId,
tabName
})
if (result.error) {
throw new Error(result.error)
}
this.dbName = this.dbName || 'database'
this.refreshSchema()
}
async loadDb (file) {
const fileContent = file ? await fu.readAsArrayBuffer(file) : null
const res = await this.pw.postMessage({ action: 'open', buffer: fileContent })
if (res.error) {
throw new Error(res.error)
}
this.dbName = file ? fu.getFileName(file) : 'database'
this.refreshSchema()
}
async refreshSchema () {
const getSchemaSql = `
WITH columns as (
SELECT
a.tbl_name,
json_group_array(
json_object('name', b.name,'type', IIF(b.type = '', 'N/A', b.type))
) as column_json
FROM sqlite_master a, pragma_table_info(a.name) b
WHERE a.type in ('table','view') AND a.name NOT LIKE 'sqlite_%' group by tbl_name
)
SELECT json_group_array(json_object('name',tbl_name, 'columns', json(column_json))) objects
FROM columns;
`
const result = await this.execute(getSchemaSql)
this.schema = JSON.parse(result.values.objects[0])
}
async execute (commands) {
await this.pw.postMessage({ action: 'reopen' })
const results = await this.pw.postMessage({ action: 'exec', sql: commands })
if (results.error) {
throw new Error(results.error)
}
// if it was more than one select - take only the last one
return results[results.length - 1]
}
async export (fileName) {
const data = await this.pw.postMessage({ action: 'export' })
if (data.error) {
throw new Error(data.error)
}
fu.exportToFile(data, fileName)
}
async validateTableName (name) {
if (name.startsWith('sqlite_')) {
throw new Error("Table name can't start with sqlite_")
}
if (/[^\w]/.test(name)) {
throw new Error('Table name can contain only letters, digits and underscores')
}
if (/^(\d)/.test(name)) {
throw new Error("Table name can't start with a digit")
}
await this.execute(`BEGIN; CREATE TABLE "${name}"(id); ROLLBACK;`)
}
sanitizeTableName (tabName) {
return tabName
.replace(/[^\w]/g, '_') // replace everything that is not letter, digit or _ with _
.replace(/^(\d)/, '_$1') // add _ at beginning if starts with digit
.replace(/_{2,}/g, '_') // replace multiple _ with one _
}
}