Skip to content

Commit ef66692

Browse files
taozhi8833998taozhi8833998
committed
feat: support create index in sqlite
1 parent 0226ab3 commit ef66692

6 files changed

Lines changed: 70 additions & 6 deletions

File tree

‎pegjs/mariadb.pegjs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -316,7 +316,7 @@ column_order_list
316316
}
317317

318318
column_order_item
319-
= c:expr o:(KW_ASC / KW_DESC)? {
319+
= c:expr __ o:(KW_ASC / KW_DESC)? {
320320
return {
321321
...c,
322322
order_by: o && o.toLowerCase(),

‎pegjs/mysql.pegjs‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -513,7 +513,8 @@ column_order_list
513513
}
514514

515515
column_order_item
516-
= c:expr o:(KW_ASC / KW_DESC)? { return {
516+
= c:expr __ o:(KW_ASC / KW_DESC)? {
517+
return {
517518
...c,
518519
order_by: o && o.toLowerCase(),
519520
}

‎pegjs/sqlite.pegjs‎

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -226,6 +226,7 @@ cmd_stmt
226226
create_stmt
227227
= create_table_stmt
228228
/ create_db_stmt
229+
/ create_index_stmt
229230
/ create_trigger_stmt
230231
/ create_view_stmt
231232

@@ -386,6 +387,57 @@ create_db_stmt
386387
}
387388
}
388389

390+
column_order_list
391+
= head:column_order_item tail:(__ COMMA __ column_order_item)* {
392+
return createList(head, tail)
393+
}
394+
395+
column_order_item
396+
= c:expr __ ce:collate_expr? __ o:(KW_ASC / KW_DESC)? {
397+
return {
398+
...c,
399+
collate: ce,
400+
order_by: o && o.toLowerCase(),
401+
}
402+
}
403+
/ column_order
404+
405+
column_order
406+
= c:column_ref __ ce:collate_expr? __ o:(KW_ASC / KW_DESC)? {
407+
return {
408+
...c,
409+
collate: ce,
410+
order_by: o && o.toLowerCase(),
411+
}
412+
}
413+
414+
create_index_stmt
415+
= a:KW_CREATE __
416+
kw:(KW_UNIQUE)? __
417+
t:KW_INDEX __
418+
ife:if_not_exists_stmt? __
419+
n:table_name __
420+
um:index_type? __
421+
on:KW_ON __
422+
ta:table_name __ LPAREN __ cols:column_order_list __ RPAREN __
423+
where:where_clause? {
424+
return {
425+
tableList: Array.from(tableList),
426+
columnList: columnListTableAlias(columnList),
427+
ast: {
428+
type: a[0].toLowerCase(),
429+
index_type: kw && kw.toLowerCase(),
430+
keyword: t.toLowerCase(),
431+
if_not_exists: ife,
432+
index: { schema: n.db, name: n.table },
433+
on_kw: on[0].toLowerCase(),
434+
table: ta,
435+
index_columns: cols,
436+
where,
437+
}
438+
}
439+
}
440+
389441
view_with
390442
= KW_WITH __ c:("CASCADED"i / "LOCAL"i) __ "CHECK"i __ "OPTION" {
391443
return `with ${c.toLowerCase()} check option`

‎pegjs/transactsql.pegjs‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -309,7 +309,8 @@ column_order_list
309309
}
310310

311311
column_order_item
312-
= LBRAKE __ c:column_ref __ RBRAKE __ o:(KW_ASC / KW_DESC) { return {
312+
= LBRAKE __ c:column_ref __ RBRAKE __ o:(KW_ASC / KW_DESC) {
313+
return {
313314
...c,
314315
order_by: o.toLowerCase()
315316
}

‎src/create.js‎

Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -177,15 +177,19 @@ function createExtensionToSQL(stmt) {
177177

178178
function createIndexToSQL(stmt) {
179179
const {
180-
concurrently, filestream_on: fileStream, keyword, include, index_columns: indexColumns,
180+
concurrently, filestream_on: fileStream, keyword, if_not_exists: ifNotExists, include, index_columns: indexColumns,
181181
index_type: indexType, index_using: indexUsing, index, on, index_options: indexOpt, algorithm_option: algorithmOpt, lock_option: lockOpt, on_kw: onKw, table, tablespace, type, where,
182182
with: withExpr, with_before_where: withBeforeWhere,
183183
} = stmt
184184
const withIndexOpt = withExpr && `WITH (${indexOptionListToSQL(withExpr).join(', ')})`
185185
const includeColumns = include && `${toUpper(include.keyword)} (${include.columns.map(col => identifierToSql(col)).join(', ')})`
186+
let indexName = index
187+
if (index) {
188+
indexName = typeof index === 'string' ? identifierToSql(index) : [identifierToSql(index.schema), identifierToSql(index.name)].filter(hasVal).join('.')
189+
}
186190
const sql = [
187-
toUpper(type), toUpper(indexType), toUpper(keyword), toUpper(concurrently),
188-
identifierToSql(index), toUpper(onKw), tableToSQL(table), ...indexTypeToSQL(indexUsing),
191+
toUpper(type), toUpper(indexType), toUpper(keyword), toUpper(ifNotExists), toUpper(concurrently),
192+
indexName, toUpper(onKw), tableToSQL(table), ...indexTypeToSQL(indexUsing),
189193
`(${columnOrderListToSQL(indexColumns)})`, includeColumns, indexOptionListToSQL(indexOpt).join(' '), alterExprToSQL(algorithmOpt), alterExprToSQL(lockOpt),
190194
commonOptionConnector('TABLESPACE', literalToSQL, tablespace),
191195
]

‎test/sqlite.spec.js‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -200,4 +200,10 @@ describe('sqlite', () => {
200200
`
201201
expect(getParsedSql(sql)).to.be.equal(`SELECT SUM("Hours Spent") AS "Total Hours" FROM "Work_Records" WHERE "Partner ID" = (SELECT "Partner ID" FROM "Employees" WHERE "Firstname" = 'John' AND "Lastname" = 'Smith')`)
202202
})
203+
it('should support create index', () => {
204+
let sql = 'CREATE INDEX visits_url_index ON visits (url);'
205+
expect(getParsedSql(sql)).to.be.equal('CREATE INDEX "visits_url_index" ON "visits" ("url")')
206+
sql = 'CREATE INDEX if not exists schema_name.visits_url_index ON visits (url collate cn asc) where id > 10;'
207+
expect(getParsedSql(sql)).to.be.equal('CREATE INDEX IF NOT EXISTS "schema_name"."visits_url_index" ON "visits" ("url" COLLATE cn ASC) WHERE "id" > 10')
208+
})
203209
})

0 commit comments

Comments
 (0)