SQL: Ctags doesn't detect CREATE TABLE IF NOT EXISTS

[LawrenceJGD]

When I use CREATE TABLE IF NOT EXISTS in SQL to create a table Ctags does not create any label for that table, the same problem occurs in Geany.

---

The name of the parser: SQL

The command line you used to run ctags: $ ctags --options=NONE test.sql

The content of input file:

CREATE TABLE IF NOT EXISTS test (
    id     INTEGER NOT NULL PRIMARY KEY,
    mytext TEXT    NOT NULL
);

CREATE TABLE another_test (
    id    INTEGER NOT NULL PRIMARY KEY,
    mynum INTEGER
);

The tags output you are not satisfied with:

!_TAG_FILE_FORMAT       2       /extended format; --format=1 will not append ;" to lines/
!_TAG_FILE_SORTED       1       /0=unsorted, 1=sorted, 2=foldcase/
!_TAG_OUTPUT_EXCMD      mixed   /number, pattern, mixed, or combineV2/
!_TAG_OUTPUT_FILESEP    slash   /slash or backslash/
!_TAG_OUTPUT_MODE       u-ctags /u-ctags or e-ctags/
!_TAG_PATTERN_LENGTH_LIMIT      96      /0 for no limit/
!_TAG_PROC_CWD  /home/myusername/       //
!_TAG_PROGRAM_AUTHOR    Universal Ctags Team    //
!_TAG_PROGRAM_NAME      Universal Ctags /Derived from Exuberant Ctags/
!_TAG_PROGRAM_URL       https://ctags.io/       /official site/
!_TAG_PROGRAM_VERSION   5.9.0   /p5.9.20210110.0/
another_test    test.sql        /^CREATE TABLE another_test ($/;"       t
id      test.sql        /^    id    INTEGER NOT NULL PRIMARY KEY,$/;"   E       table:another_test
mynum   test.sql        /^    mynum INTEGER$/;" E       table:another_test

The tags output you expect:

!_TAG_FILE_FORMAT       2       /extended format; --format=1 will not append ;" to lines/
!_TAG_FILE_SORTED       1       /0=unsorted, 1=sorted, 2=foldcase/
!_TAG_OUTPUT_EXCMD      mixed   /number, pattern, mixed, or combineV2/
!_TAG_OUTPUT_FILESEP    slash   /slash or backslash/
!_TAG_OUTPUT_MODE       u-ctags /u-ctags or e-ctags/
!_TAG_PATTERN_LENGTH_LIMIT      96      /0 for no limit/
!_TAG_PROC_CWD  /home/myusername/       //
!_TAG_PROGRAM_AUTHOR    Universal Ctags Team    //
!_TAG_PROGRAM_NAME      Universal Ctags /Derived from Exuberant Ctags/
!_TAG_PROGRAM_URL       https://ctags.io/       /official site/
!_TAG_PROGRAM_VERSION   5.9.0   /p5.9.20210110.0/
another_test    test.sql        /^CREATE TABLE another_test ($/;"       t
id      test.sql        /^    id     INTEGER NOT NULL PRIMARY KEY,$/;"  E       table:test
id      test.sql        /^    id    INTEGER NOT NULL PRIMARY KEY,$/;"   E       table:another_test
mynum   test.sql        /^    mynum INTEGER$/;" E       table:another_test
mytext  test.sql        /^    mytext TEXT    NOT NULL$/;"       E       table:test
test    test.sql        /^CREATE TABLE IF NOT EXISTS test ($/;" t

The version of ctags:

$ ctags --version
Universal Ctags 5.9.0(p5.9.20210110.0), Copyright (C) 2015 Universal Ctags Team
Universal Ctags is derived from Exuberant Ctags.
Exuberant Ctags 5.8, Copyright (C) 1996-2009 Darren Hiebert
  Compiled: Jan 15 2021, 00:04:58
  URL: https://ctags.io/
  Optional compiled features: +wildcards, +regex, +iconv, +option-directory, +xpath, +json, +interactive, +sandbox, +yaml, +packcc

How do you get ctags binary: From Manjaro Linux "ctags" package

[masatake]

Thankj you for reporting.

Is this MySQL specific phrase? I'm not familiar with SQL. So I would like to know the URL that explains the phrase, "IF NOT EXISTS".

[ksamborski]

Is this MySQL specific phrase? I'm not familiar with SQL. So I would like to know the URL that explains the phrase, "IF NOT EXISTS".

Not only MySQL and here it's described:
https://dev.mysql.com/doc/refman/8.0/en/create-table.html
https://www.postgresql.org/docs/current/sql-createtable.html
https://sqlite.org/lang_createtable.html

[masatake]

If we can assume "EXISTS" comes after "NOT" after "IF", we can solve this issue easily.

[yamato@control]~/var/ctags-github% git diff |cat
git diff |cat
diff --git a/parsers/sql.c b/parsers/sql.c
index dc7049e00..e2cbde1a5 100644
--- a/parsers/sql.c
+++ b/parsers/sql.c
@@ -2015,7 +2015,21 @@ static void parseTable (tokenInfo *const token)
 
 	/* This could be a database, owner or table name */
 	readIdentifier (name);
+
 	readToken (token);
+	if (isKeyword (name, KEYWORD_if)
+		&& (isType (token, TOKEN_IDENTIFIER)
+			&& strcasecmp ("not", vStringValue (token->string)) == 0))
+	{
+		readToken (token);
+		if (isType (token, TOKEN_IDENTIFIER)
+			&& strcasecmp ("exists", vStringValue (token->string)) == 0)
+		{
+			readIdentifier (name);
+			readToken (token);
+		}
+	}
+
 	if (isType (token, TOKEN_PERIOD))
 	{
 		/*
[yamato@control]~/var/ctags-github% cat input.sql 
cat input.sql 
CREATE TABLE IF NOT EXISTS test (
    id     INTEGER NOT NULL PRIMARY KEY,
    mytext TEXT    NOT NULL
);

CREATE TABLE another_test (
    id    INTEGER NOT NULL PRIMARY KEY,
    mynum INTEGER
);
[yamato@control]~/var/ctags-github%  ./ctags --options=NONE -o - input.sql 
 ./ctags --options=NONE -o - input.sql 
ctags: Notice: No options will be read from files or environment
another_test	input.sql	/^CREATE TABLE another_test ($/;"	t
id	input.sql	/^    id     INTEGER NOT NULL PRIMARY KEY,$/;"	E	table:test
id	input.sql	/^    id    INTEGER NOT NULL PRIMARY KEY,$/;"	E	table:another_test
mynum	input.sql	/^    mynum INTEGER$/;"	E	table:another_test
mytext	input.sql	/^    mytext TEXT    NOT NULL$/;"	E	table:test
test	input.sql	/^CREATE TABLE IF NOT EXISTS test ($/;"	t

[masatake]

@ksamborski, thank you. It seems that we can assume "IF NOT EXISTS" or nothing after "table" keyworkd.
When I found a time, I will make a pull request.

[masatake]

I made a pull request. See #2961.