// Handle GO after a statement in SQL Server
bool SqlParser::SqlServerGoDelimiter(bool just_remove)
{
if(Source(SQL_SQL_SERVER, SQL_SYBASE) == false || Target(SQL_SQL_SERVER, SQL_SYBASE) == true)
return false;
Token *stmt_end = GetLastToken();
// Optional ; can go before GO
Token *semi = GetNextCharToken(';', L';');
Token *go = GetNextWordToken("GO", L"GO", 2);
if(go == NULL)
{
// ; is processed at statement parser
if(semi != NULL)
PushBack(semi);
return false;
}
// The delimiter ; does not already exist, add it otherwise just remove GO
if(semi == NULL && just_remove == false)
AppendNoFormat(stmt_end, ";", L";", 1);
Token::Change(go, " ", L" ", 1);
return true;
}
// Return last token
Token* SqlParser::GetLastToken(Token *last)
{
if(last != NULL)
return last;
return GetLastToken();
}
// Add statement delimiter if not set when source is SQL Server, Sybase ASE
void SqlParser::SqlServerAddStmtDelimiter(bool force)
{
if(Source(SQL_SQL_SERVER, SQL_SYBASE) == false)
return;
// Add the delimiter in statements in procedural block only
if(_spl_scope == 0 && force == false)
{
// Special case when a single statement followed by GO, then add ; even if we are no inside procedure scope
if(!LookNext("GO", L"GO", 2))
return;
}
// Check if the delimiter already set
Token *semi = GetNextCharToken(';', L';');
if(semi != NULL)
{
PushBack(semi);
return;
}
// Do not set if comma followed
Token *comma = GetNextCharToken(',', L',');
if(comma != NULL)
{
PushBack(comma);
return;
}
AppendNoFormat(GetLastToken(), ";", L";", 1);
}
// MODIFIED BY option in EXPORT command
bool SqlParser::Db2ParseModifiedByOptions(Token **colsep_out)
{
bool exists = false;
Token *start = NULL;
// Options
while(true)
{
Token *next = GetNextToken();
if(next == NULL)
break;
if(start == NULL)
start = next;
// COLDELx where x is a single characater column delimiter
if(next->Compare("COLDEL", L"COLDEL", 0, 6) == true)
{
Token *del = NULL;
// x is a special punctuation character it is selected as a separate token
if(next->len == 6)
{
del = GetNextToken();
if(colsep_out != NULL)
*colsep_out = del;
}
exists = true;
}
else
// NOCHARDEL
if(next->Compare("NOCHARDEL", L"NOCHARDEL", 9) == true)
{
exists = true;
}
// Unknown option
else
{
PushBack(next);
break;
}
}
if(exists == true)
{
// Remove options
if(_target == SQL_ORACLE)
Token::Remove(start, GetLastToken());
}
return exists;
}
// Get the specified character token or the last selected
Token* SqlParser::GetNextCharOrLastToken(const char ch, const wchar_t wch)
{
Token *out = GetNextCharToken(ch, wch);
// Not found
if(out == NULL)
out = GetLastToken();
return out;
}
// SQL Server, Sybase ASE EXEC procedure statement
void SqlParser::ParseSqlServerExecProcedure(Token *execute, Token *name)
{
if(execute == NULL || name == NULL)
return;
ConvertIdentifier(name, SQL_IDENT_OBJECT);
if(Target(SQL_MARIADB, SQL_MYSQL))
TOKEN_CHANGE(execute, "CALL");
int params = 0;
// Parse parameters
// Sybase ASE does not use () around parameters
while(true)
{
Token *next = GetNextToken();
if(next == NULL)
break;
// If there are no params, we can have conflict with next statement as there may be no delimiters in Transact-SQL
// If there is at least one parameter, no conflict is possible for subsequent params as comma defines whether it exists or not
if(params == 0 && !IsValidAlias(next))
{
PushBack(next);
break;
}
ConvertIdentifier(next, SQL_IDENT_VAR);
// OUTPUT qualifier can be specified for parameter in Sybase ASE
Token *output = GetNext("OUTPUT", L"OUTPUT", 6);
if(output != NULL && Target(SQL_MARIADB, SQL_MYSQL))
Token::Remove(output);
Token *comma = GetNext(',', L',');
if(comma == NULL)
break;
params++;
}
// When there are no parameters MySQL, MariaDB allow as CALL name() and CALL name
if(params > 0)
{
if(Target(SQL_MARIADB, SQL_MYSQL))
{
APPEND_NOFMT(name, "(");
APPEND_NOFMT(GetLastToken(), ")");
}
}
}
// Teradata HELP STATISTICS statement
bool SqlParser::ParseTeradataHelpStatistics(Token *help, Token *statistics)
{
if(help == NULL || statistics == NULL)
return false;
// Table name
Token *table_name = GetNextIdentToken(SQL_IDENT_OBJECT);
// Comment for other databases
if(_target != SQL_TERADATA)
Comment(help, Nvl(GetNext(';', L';'), GetLastToken()));
return true;
}
// DB2 RESULT_SET_LOCATOR declaration
bool SqlParser::ParseDb2ResultSetLocatorDeclaration(Token *declare, Token *name, Token *result_set_locator)
{
if(result_set_locator == NULL)
return false;
// VARYING keyword
/*Token *varying */ (void) GetNextWordToken("VARYING", L"VARYING", 7);
_spl_declared_rs_locators.Add(name);
// Remove the entire declaration for other databases
if(_target != SQL_DB2)
Token::Remove(declare, Nvl(GetNextCharToken(';', L';'), GetLastToken()));
return true;
}
// Any Transact-SQL statement can be followed by ; and GO, any combination of them or nothing at all
void SqlParser::SqlServerDelimiter()
{
if(Source(SQL_SQL_SERVER, SQL_SYBASE) == false || Target(SQL_SQL_SERVER, SQL_SYBASE) == true)
return;
Token *last = GetLastToken();
// Optional ; can go before GO
Token *semi = GetNextCharToken(';', L';');
// Optional GO to terminate the batch
Token *go = GetNextWordToken("GO", L"GO", 2);
// No ; delimiter
if(semi == NULL)
AppendNoFormat(last, ";", L";", 1);
Token::Remove(go);
}
// Parse MySQL CREATE TABLE storage clause
bool SqlParser::ParseMysqlStorageClause(Token *table_name, Token **id_start, Token **comment_out)
{
bool exists = false;
// Auto_increment start value
Token *auto_start = NULL;
while(true)
{
Token *next = GetNextToken();
if(next == NULL)
break;
// ENGINE = type
if(next->Compare("ENGINE", L"ENGINE", 6) == true)
{
// Equal sign = is optional in the clause
Token *equal = GetNextCharToken('=', L'=');
Token *type = GetNextToken();
if(_target != SQL_MYSQL)
{
Token::Remove(next);
Token::Remove(equal);
Token::Remove(type);
}
exists = true;
continue;
}
else
// AUTO_INCREMENT = start table option
if(next->Compare("AUTO_INCREMENT", L"AUTO_INCREMENT", 14) == true)
{
// Equal sign = is optional in the clause
Token *equal = GetNextCharToken('=', L'=');
auto_start = GetNextNumberToken();
if(_target != SQL_MYSQL)
{
Token::Remove(next);
Token::Remove(equal);
Token::Remove(auto_start);
}
exists = true;
continue;
}
else
// DEFAULT CHARSET
if(next->Compare("DEFAULT", L"DEFAULT", 7) == true)
{
Token *option = GetNextToken();
if(option == NULL)
break;
// CHARSET
if(option->Compare("CHARSET", L"CHARSET", 7) == true)
{
Token *equal = GetNextCharToken('=', L'=');
Token *value = GetNextIdentToken();
if(_target != SQL_MYSQL)
Token::Remove(next, value);
}
else
// CHARACTER SET
if(option->Compare("CHARACTER", L"CHARACTER", 9) == true)
{
Token *set = GetNextWordToken("SET", L"SET", 3);
Token *equal = GetNextCharToken('=', L'=');
Token *value = GetNextIdentToken();
if(_target != SQL_MYSQL)
Token::Remove(next, value);
}
exists = true;
continue;
}
else
// COLLATE = value
if(next->Compare("COLLATE", L"COLLATE", 7) == true)
{
Token *equal = GetNextCharToken('=', L'=');
Token *value = GetNextIdentToken();
if(_target != SQL_MYSQL)
Token::Remove(next, value);
exists = true;
continue;
}
else
// COMMENT = 'table comment'
if(next->Compare("COMMENT", L"COMMENT", 7) == true)
{
// Equal sign = is optional in the clause
Token *equal = GetNextCharToken('=', L'=');
Token *text = GetNextToken();
if(comment_out != NULL)
*comment_out = text;
// Remove from CREATE TABLE
if(_target != SQL_MYSQL)
Token::Remove(next, text);
exists = true;
continue;
}
else
// PACK_KEYS = 0 | 1 | DEFAULT
if(next->Compare("PACK_KEYS", L"PACK_KEYS", 9) == true)
{
// Optional =
Token *equal = GetNextCharToken('=', L'=');
Token *value = GetNextToken();
if(_target != SQL_MYSQL)
Token::Remove(next, value);
exists = true;
continue;
}
else
// ROW_FORMAT = type | DEFAULT
if(next->Compare("ROW_FORMAT", L"ROW_FORMAT", 10) == true)
{
// Optional =
Token *equal = GetNextCharToken('=', L'=');
Token *value = GetNextToken();
if(_target != SQL_MYSQL)
Token::Remove(next, value);
exists = true;
continue;
}
// Not a MySQL stoage clause
PushBack(next);
break;
}
if(id_start != NULL)
*id_start = auto_start;
// Restart sequence for PostgreSQL and Greenplum
if(auto_start != NULL && (_target == SQL_POSTGRESQL || _target == SQL_GREENPLUM))
{
// Try to get ;
Token *semi = GetNextCharToken(';', L';');
Token *last = (semi != NULL) ? semi : GetLastToken();
// Append ALTER SEQUENCE command
Append(last, "\n\nALTER SEQUENCE ", L"\n\nALTER SEQUENCE ", 17);
// Add sequence name
Token *seq_name = AppendIdentifier(table_name, "_seq", L"_seq", 4);
Append(last, seq_name);
Append(last, " RESTART WITH ", L" RESTART WITH ", 14);
AppendCopy(last, auto_start);
Append(last, ";", L";", 1);
}
return exists;
}
// Temporary table options
bool SqlParser::ParseTempTableOptions(Token *table_name, Token **start_out, Token **end_out, bool *no_data)
{
bool exists = false;
Token *start = NULL;
while(true)
{
Token *next = GetNextToken();
if(next == NULL)
break;
if(start == NULL)
start = next;
// ON COMMIT PRESERVE | DELETE ROWS in Oracle, DB2; ON ROLLBACK PRESERVE | DELETE ROWS in DB2
if(next->Compare("ON", L"ON", 2) == true)
{
Token *commit = GetNextWordToken("COMMIT", L"COMMIT", 6);
Token *rollback = NULL;
if(commit == NULL)
rollback = GetNextWordToken("ROLLBACK", L"ROLLBACK", 8);
if(commit == NULL && rollback == NULL)
break;
Token *delete_ = GetNextWordToken("DELETE", L"DELETE", 6);
Token *preserve = NULL;
if(delete_ == NULL)
preserve = GetNextWordToken("PRESERVE", L"PRESERVE", 8);
Token *rows = GetNextWordToken("ROWS", L"ROWS", 4);
if(_target == SQL_SQL_SERVER)
Token::Remove(next, rows);
else
// Oracle does not support ON ROLLBACK, but DELETE ROWS in default on rollback
if(_target == SQL_ORACLE && rollback != NULL)
{
if(delete_ != NULL)
Token::Remove(next, rows);
else
Comment(next, rows);
}
exists = true;
continue;
}
else
// NOT LOGGED in DB2
if(next->Compare("NOT", L"NOT", 3) == true)
{
Token *logged = GetNextWordToken("LOGGED", L"LOGGED", 6);
if(logged != NULL)
{
if(_target == SQL_ORACLE)
Token::Remove(next, logged);
exists = true;
continue;
}
}
else
// WITH REPLACE, WITH NO DATA in DB2
if(next->Compare("WITH", L"WITH", 4) == true)
{
Token *replace = GetNextWordToken("REPLACE", L"REPLACE", 7);
Token *no = NULL;
if(replace == NULL)
no = GetNextWordToken("NO", L"NO", 2);
// WITH REPLACE in DB2
if(replace != NULL)
{
if(Target(SQL_DB2) == false)
Token::Remove(next, replace);
_spl_declared_tables_with_replace.Add(table_name);
exists = true;
continue;
}
else
// WITH NO DATA in DB2
if(no != NULL)
{
Token *data = GetNextWordToken("DATA", L"DATA", 4);
if(data != NULL)
{
if(_target == SQL_ORACLE)
Token::Remove(next, data);
if(no_data != NULL)
*no_data = true;
exists = true;
continue;
}
}
}
else
// DEFINITION ONLY in DB2
if(next->Compare("DEFINITION", L"DEFINITION", 10) == true)
{
Token *only = GetNextWordToken("ONLY", L"ONLY", 4);
if(only != NULL)
{
if(_target == SQL_ORACLE)
Token::Remove(next, only);
if(no_data != NULL)
*no_data = true;
exists = true;
continue;
}
}
else
// IN tablespace in DB2
if(next->Compare("IN", L"IN", 2) == true)
{
Token *tablespace_name = GetNextToken();
if(tablespace_name != NULL)
{
if(_target == SQL_ORACLE)
Token::Remove(next, tablespace_name);
exists = true;
continue;
}
}
// Not a temporary table clause
PushBack(next);
break;
}
if(exists == true)
{
if(start_out != NULL)
*start_out = start;
if(end_out != NULL)
*end_out = GetLastToken();
}
return exists;
}
// DB2 CREATE DATABASE statement
bool SqlParser::Db2CreateDatabase(Token *create, Token * /*database*/, Token * /*name*/)
{
bool exists = false;
// CREATE DATABASE options
while(true)
{
Token *next = GetNextToken();
if(next == NULL)
break;
// BUFFERPOOL name
if(next->Compare("BUFFERPOOL", L"BUFFERPOOL", 10) == true)
{
Token *name = GetNextToken();
if(Target(SQL_DB2, SQL_ORACLE) == false)
Token::Remove(next, name);
exists = true;
continue;
}
else
// INDEXBP name
if(next->Compare("INDEXBP", L"INDEXBP", 7) == true)
{
Token *name = GetNextToken();
if(Target(SQL_DB2, SQL_ORACLE) == false)
Token::Remove(next, name);
exists = true;
continue;
}
else
// STOGROUP name
if(next->Compare("STOGROUP", L"STOGROUP", 8) == true)
{
Token *name = GetNextToken();
if(Target(SQL_DB2, SQL_ORACLE) == false)
Token::Remove(next, name);
exists = true;
continue;
}
else
// CCSID ASCII | EBCDIC | UNICODE
if(next->Compare("CCSID", L"CCSID", 5) == true)
{
Token *code = GetNextToken();
if(Target(SQL_DB2, SQL_ORACLE) == false)
Token::Remove(next, code);
exists = true;
continue;
}
PushBack(next);
break;
}
// Comment the entire statement for Oracle
if(_target == SQL_ORACLE)
{
Token *end = GetLastToken(GetNextCharToken(';', L';'));
Comment(create, end);
}
return exists;
}
// Various SET options such as SET CURRENT SCHEMA
bool SqlParser::ParseDb2SetOptions(Token *set)
{
bool exists = false;
if(set == NULL)
return false;
// CURRENT is optional (SET SCHEMA is allowed)
Token *current = GetNextWordToken("CURRENT", L"CURRENT", 7);
Token *option = GetNextToken();
if(option == NULL)
{
PushBack(current);
return false;
}
// SET [CURRENT] PATH = list
if(option->Compare("PATH", L"PATH", 4) == true)
{
// Optional =
/*Token *equal */ (void) GetNextCharToken('=', L'=');
// Comma-separated list of values
while(true)
{
// Schema name
/*Token *name */ (void) GetNextToken();
Token *comma = GetNextCharToken(',', L',');
if(comma == NULL)
break;
}
// Remove the statement in Oracle
if(_target == SQL_ORACLE)
Token::Remove(set, Nvl(GetNextCharToken(';', L';'), GetLastToken()));
exists = true;
}
else
// SET [CURRENT] SCHEMA = name
if(option->Compare("SCHEMA", L"SCHEMA", 6) == true)
{
// Optional =
Token *equal = GetNextCharToken('=', L'=');
// Schema name
/*Token *name */ (void) GetNextToken();
// ALTER SESSION SET CURRENT_SCHEMA = name in Oracle
if(_target == SQL_ORACLE)
{
Prepend(set, "ALTER SESSION ", L"ALTER SESSION ", 14);
Token::Remove(current);
Token::Change(option, "CURRENT_SCHEMA", L"CURRENT_SCHEMA", 14);
if(equal == NULL)
AppendNoFormat(option, " =", L" =", 2);
}
exists = true;
}
// Not a SET option
else
{
PushBack(option);
PushBack(current);
}
return exists;
}
// SQL Server SET options, SET ANSI_NULLS ON i.e
bool SqlParser::ParseSqlServerSetOptions(Token *set)
{
if(set == NULL)
return false;
bool exists = false;
bool comment = false;
bool remove = false;
Token *option = GetNextToken();
if(option == NULL)
return false;
// SET ANSI_NULLS ON | OFF
if(option->Compare("ANSI_NULLS", L"ANSI_NULLS", 10) == true)
{
/*Token *value */ (void) GetNextToken();
if(_target != SQL_SQL_SERVER)
comment = true;
exists = true;
}
else
// SET ANSI_PADDING ON | OFF
if(option->Compare("ANSI_PADDING", L"ANSI_PADDING", 12) == true)
{
/*Token *value */ (void) GetNextToken();
if(_target != SQL_SQL_SERVER)
comment = true;
exists = true;
}
else
// SET NOCOUNT ON | OFF
if(option->Compare("NOCOUNT", L"NOCOUNT", 7) == true)
{
/*Token *value */ (void) GetNextToken();
if(_target != SQL_SQL_SERVER)
remove = true;
exists = true;
}
else
// SET QUOTED_IDENTIFIER ON | OFF
if(option->Compare("QUOTED_IDENTIFIER", L"QUOTED_IDENTIFIER", 17) == true)
{
/*Token *value */ (void) GetNextToken();
if(_target != SQL_SQL_SERVER)
comment = true;
exists = true;
}
// Not a SET option
else
PushBack(option);
if(exists == true)
{
Token *last = Nvl(GetNextCharToken(';', L';'), GetLastToken());
if(comment == true)
Comment(set, last);
else
if(remove == true)
Token::Remove(set, last);
// Remove following GO if any
SqlServerGoDelimiter(true);
}
return exists;
}
