// DB2 FETCH FIRST n ROWS ONLY clause in SELECT statement
bool SqlParser::ParseDb2FetchFirstRowOnly(Token *fetch, Token **rowlimit_soptions, int *rowlimit)
{
if(fetch == NULL || Token::Compare(fetch, "FETCH", L"FETCH", 5) == false)
return false;
Token *first = GetNextWordToken("FIRST", L"FIRST", 5);
if(first == NULL)
return false;
// Optional number of rows, meaning 1 row if skipped
Token *num = GetNextToken();
Token *rows = NULL;
if(num == NULL)
return false;
// Check if num already points to ROW or ROWS
if(num->Compare("ROW", L"ROW", 3) == true || num->Compare("ROWS", L"ROWS", 4) == true)
{
rows = num;
num = NULL;
}
// ROWS or ROW
if(rows == NULL)
rows = GetNextToken();
Token *only = GetNextWordToken("ONLY", L"ONLY", 4);
if(only == NULL)
return false;
// Set rowlimit to output
if(num == NULL && rowlimit != NULL)
*rowlimit = 1;
else
if(num != NULL && rowlimit_soptions != NULL)
*rowlimit_soptions = num;
// ROWNUM is used in Oracle
if(_target == SQL_ORACLE)
{
Token::Remove(fetch);
Token::Remove(first, only);
}
return true;
}
// 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;
}
// Get next token if the previous is set
Token* SqlParser::GetNextWordToken(Token *prev, const char *str, const wchar_t *wstr, int len)
{
if(prev == NULL)
return NULL;
return GetNextWordToken(str, wstr, len);
}
// VALUES NEXTVAL seq INTO var pattern
bool SqlParser::Db2ValuesNextValIntoPattern(Token *values)
{
if(values == NULL || _target != SQL_ORACLE)
return false;
// NEXTVAL keyword
Token *nextval = GetNextWordToken("NEXTVAL", L"NEXTVAL", 7);
if(nextval == NULL)
return false;
// FOR keyword
Token *for_ = GetNextWordToken("FOR", L"FOR", 3);
// Sequence name
Token *seq_name = GetNextToken(for_);
// INTO keyword
Token *into = GetNextWordToken(seq_name, "INTO", L"INTO", 4);
// Variable name
Token *var = GetNextToken(into);
if(var == NULL)
{
PushBack(nextval);
return false;
}
// SELECT seq.NEXTVAL INTO var FROM dual in Oracle
if(_target == SQL_ORACLE)
{
Token::Change(values, "SELECT", L"SELECT", 6);
PrependCopy(nextval, seq_name);
PrependNoFormat(nextval, ".", L".", 1);
Token::Remove(for_, seq_name);
Append(var, " FROM ", L" FROM ", 6, values);
AppendNoFormat(var, "dual", L"dual", 4);
}
return true;
}
// PRIMARY INDEX hash partitioning clause
bool SqlParser::ParseTeradataPrimaryIndex(Token *unique, Token *primary, int obj_scope, Token *last_colname,
Token *last_colend)
{
if(primary == NULL)
return false;
Token *index = GetNextWordToken("INDEX", L"INDEX", 5);
if(index == NULL)
return false;
Token *open = GetNextCharToken('(', L'(');
// List of partitioning columns
while(true)
{
Token *column = GetNextToken();
if(column == NULL)
break;
Token *comma = GetNextCharToken(',', L',');
if(comma == NULL)
break;
}
Token *close = GetNextCharToken(open, ')', L')');
// Add UNIQUE constraint for other databases
if(unique != NULL && _target != SQL_TERADATA)
{
AppendNoFormat(last_colend, ",", L",", 1);
Append(last_colend, "\n", L"\n", 1, last_colname);
AppendCopy(last_colend, unique);
AppendNoFormat(last_colend, " ", L" ", 1);
AppendCopy(last_colend, open, close);
Token::Remove(unique, false);
}
// PARTITION BY HASH in Oracle
if(_target == SQL_ORACLE)
{
// In Oracle, temporary tables cannot be partitioned
if(obj_scope != SQL_SCOPE_TEMP_TABLE)
{
Token::Change(primary, "PARTITION", L"PARTITION", 9);
Token::Change(index, "BY HASH", L"BY HASH", 7);
}
else
Token::Remove(primary, close);
}
return true;
}
// FOR COLUMN system_name in OS/400 to specify 10-char system name to access by old apps (like 8-char file name in Windows for DOS apps)
bool SqlParser::ParseDb2ForColumn()
{
bool exists = false;
Token *for_ = GetNextWordToken("FOR", L"FOR", 3);
if(for_ == NULL)
return false;
// COLUMN keyword is optional
/*Token *column */ (void) GetNextWordToken("COLUMN", L"COLUMN", 6);
Token *system_name = GetNextToken();
if(system_name != NULL)
{
Token::Remove(for_, system_name);
exists = true;
}
return exists;
}
// 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);
}
// Check whether the token is valid column/table alias
bool SqlParser::IsValidAlias(Token *token)
{
if(token == NULL)
return false;
bool alias = true;
// DB2 allows almost any keyword as alias including WHERE, ORDER, SELECT
if(Source(SQL_ORACLE, SQL_SQL_SERVER, SQL_DB2, SQL_INFORMIX, SQL_SYBASE) == false)
{
// List of not valid aliases for all databases
const char **a_array = g_no_alias;
const wchar_t **w_array = g_no_alias_w;
int *size = g_no_alias_size;
int i = 0;
// Check is the word is valid for alias
while(size[i] != 0)
{
if(token->Compare(a_array[i], w_array[i], size[i]) == true)
{
alias = false;
break;
}
i++;
}
}
// Check Oracle list
if(_source == SQL_ORACLE)
{
// Words not allowed as alias in Oracle
if(Token::Compare(token, "END", L"END", 3) == true ||
Token::Compare(token, "MINUS", L"MINUS", 5) == true ||
Token::Compare(token, "ORDER", L"ORDER", 5) == true ||
Token::Compare(token, "RETURN", L"RETURN", 6) == true ||
Token::Compare(token, "SELECT", L"SELECT", 6) == true ||
Token::Compare(token, "UNION", L"UNION", 5) == true ||
Token::Compare(token, "UPDATE", L"UPDATE", 6) == true ||
Token::Compare(token, "WHERE", L"WHERE", 5) == true ||
Token::Compare(token, "WITH", L"WITH", 4) == true)
alias = false;
}
else
// Check SQL Server and Sybase ASE list
if(_source == SQL_SQL_SERVER || _source == SQL_SYBASE)
{
// Words not allowed as alias in SQL Server
if(Token::Compare(token, "DECLARE", L"DECLARE", 7) == true ||
Token::Compare(token, "ELSE", L"ELSE", 4) == true ||
Token::Compare(token, "END", L"END", 3) == true ||
Token::Compare(token, "EXEC", L"EXEC", 4) == true ||
Token::Compare(token, "FETCH", L"FETCH", 5) == true ||
Token::Compare(token, "IF", L"IF", 2) == true ||
Token::Compare(token, "GO", L"GO", 2) == true ||
Token::Compare(token, "ORDER", L"ORDER", 5) == true ||
Token::Compare(token, "RETURN", L"RETURN", 6) == true ||
Token::Compare(token, "SELECT", L"SELECT", 6) == true ||
Token::Compare(token, "UNION", L"UNION", 5) == true ||
Token::Compare(token, "UPDATE", L"UPDATE", 6) == true ||
Token::Compare(token, "WHERE", L"WHERE", 5) == true)
alias = false;
}
else
// For DB2 check the next word
if(_source == SQL_DB2)
{
Token *next = NULL;
// DB2 allows WHERE as alias, but does not allow for example WHERE ORDER BY
// Our parser currently does not allow WHERE as alias
if(token->Compare("WHERE", L"WHERE", 5) == true)
alias = false;
else
// GROUP BY or ORDER BY
if(token->Compare("GROUP", L"GROUP", 5) == true || token->Compare("ORDER", L"ORDER", 5) == true)
next = GetNextWordToken("BY", L"BY", 2);
else
// FETCH FIRST
if(token->Compare("FETCH", L"FETCH", 5) == true)
next = GetNextWordToken("FIRST", L"FIRST", 5);
else
// WITH UR, CS, RS, RR
if(token->Compare("WITH", L"WITH", 4) == true)
{
next = GetNextWordToken("UR", L"UR", 2);
if(next == NULL)
next = GetNextWordToken("CS", L"CS", 2);
if(next == NULL)
next = GetNextWordToken("RS", L"RS", 2);
if(next == NULL)
next = GetNextWordToken("RR", L"RR", 2);
}
if(next != NULL)
{
PushBack(next);
alias = false;
}
}
else
// Check Informix list
if(_source == SQL_INFORMIX)
{
// Words not allowed as alias in Informix
if(Token::Compare(token, "END", L"END", 3) == true ||
Token::Compare(token, "INTO", L"INTO", 4) == true ||
Token::Compare(token, "GROUP", L"GROUP", 5) == true ||
Token::Compare(token, "ORDER", L"ORDER", 5) == true ||
Token::Compare(token, "RETURN", L"RETURN", 6) == true ||
Token::Compare(token, "SELECT", L"SELECT", 6) == true ||
Token::Compare(token, "UNION", L"UNION", 5) == true ||
Token::Compare(token, "UPDATE", L"UPDATE", 6) == true ||
Token::Compare(token, "WHERE", L"WHERE", 5) == true)
alias = false;
}
else
// Check Teradata list
if(_source == SQL_TERADATA)
{
// Words not allowed as alias in Teradata
if(Token::Compare(token, "DO", L"DO", 2) == true ||
Token::Compare(token, "END", L"END", 3) == true ||
Token::Compare(token, "INTO", L"INTO", 4) == true ||
Token::Compare(token, "GROUP", L"GROUP", 5) == true ||
Token::Compare(token, "ORDER", L"ORDER", 5) == true ||
Token::Compare(token, "RETURN", L"RETURN", 6) == true ||
Token::Compare(token, "SELECT", L"SELECT", 6) == true ||
Token::Compare(token, "UNION", L"UNION", 5) == true ||
Token::Compare(token, "UPDATE", L"UPDATE", 6) == true ||
Token::Compare(token, "WHERE", L"WHERE", 5) == true)
alias = false;
}
else
// MySQL list
if(_source == SQL_MYSQL)
{
// Words not allowed as alias in MySQL
if(Token::Compare(token, "END", L"END", 3) == true ||
Token::Compare(token, "LIMIT", L"LIMIT", 5) == true ||
Token::Compare(token, "ORDER", L"ORDER", 5) == true ||
Token::Compare(token, "RETURN", L"RETURN", 6) == true ||
Token::Compare(token, "SELECT", L"SELECT", 6) == true ||
Token::Compare(token, "UNION", L"UNION", 5) == true ||
Token::Compare(token, "UPDATE", L"UPDATE", 6) == true ||
Token::Compare(token, "WHERE", L"WHERE", 5) == true)
alias = false;
}
else
// Check Sybase ASE list
if(_source == SQL_SYBASE)
{
// Words not allowed as alias in Sybase
if(Token::Compare(token, "DECLARE", L"DECLARE", 7) == true ||
Token::Compare(token, "END", L"END", 3) == true ||
Token::Compare(token, "EXEC", L"EXEC", 4) == true ||
Token::Compare(token, "FETCH", L"FETCH", 5) == true ||
Token::Compare(token, "GO", L"GO", 2) == true ||
Token::Compare(token, "ORDER", L"ORDER", 5) == true ||
Token::Compare(token, "RETURN", L"RETURN", 6) == true ||
Token::Compare(token, "SELECT", L"SELECT", 6) == true ||
Token::Compare(token, "UNION", L"UNION", 5) == true ||
Token::Compare(token, "UPDATE", L"UPDATE", 6) == true ||
Token::Compare(token, "WHERE", L"WHERE", 5) == true)
alias = false;
}
return alias;
}
// 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 for z/OS COMMENT ON tab (col IS 'comment' , ...)
bool SqlParser::ParseDb2Comment(Token *comment, Token *on, Token *name)
{
if(comment == NULL)
return false;
Token *open = GetNextCharToken('(', L'(');
if(open == NULL)
return false;
int num = 1;
// Multipe comma separated comments can be specified
while(true)
{
Token *col = GetNextIdentToken();
if(col == NULL)
break;
// IS keyword must follow now
Token *is = GetNextWordToken("IS", L"IS", 2);
if(is == NULL)
break;
// Comment text
Token *text = GetNextToken();
if(_target == SQL_SQL_SERVER)
{
Append(text, ", 'table', ", L", 'table', ", 11);
AppendCopy(text, name);
Append(text, ", 'column', ", L", 'column', ", 12);
AppendCopy(text, col);
Token::Remove(col);
Token::Remove(is);
}
else
if(_target == SQL_ORACLE)
{
if(num == 1)
{
Append(on, " COLUMN", L" COLUMN", 7);
Append(name, ".", L".", 1);
AppendCopy(name, col);
Token::Remove(col);
}
}
Token *comma = GetNextCharToken(',', L',');
if(comma == NULL)
break;
num++;
}
Token *close = GetNextCharToken(')', L')');
if(_target == SQL_SQL_SERVER)
{
Token::Change(comment, "EXECUTE", L"EXECUTE", 7);
AppendNoFormat(comment, " sp_addextendedproperty 'Comment',", L" sp_addextendedproperty 'Comment',", 34);
Token::Remove(on);
Token::Remove(name);
Token::Remove(open);
Token::Remove(close);
}
else
if(_target == SQL_ORACLE)
{
Token::Remove(open);
Token::Remove(close);
}
return true;
}
// Teradata CREATE TABLE options before column definition
bool SqlParser::ParseTeradataTableOptions()
{
bool exists = false;
// , goes after table name before even first option
while(true)
{
Token *comma = GetNextCharToken(',', L',');
if(comma == NULL)
break;
// Most options can start with NO keyword to disable option
Token *no = GetNextWordToken("NO", L"NO", 2);
Token *next = GetNextToken();
if(next == NULL)
{
if(no != NULL)
PushBack(no);
break;
}
// [NO] FALLBACK to store a row copy
if(next->Compare("FALLBACK", L"FALLBACK", 8) == true)
{
if(_target != SQL_TERADATA)
Token::Remove(comma, next);
exists = true;
continue;
}
else
// [NO] BEFORE JOURNAL to store before image
if(next->Compare("BEFORE", L"BEFORE", 6) == true)
{
Token *journal = GetNextWordToken("JOURNAL", L"JOURNAL", 7);
if(_target != SQL_TERADATA && journal != NULL)
Token::Remove(comma, journal);
exists = true;
continue;
}
else
// [NO] AFTER JOURNAL to store after image
if(next->Compare("AFTER", L"AFTER", 5) == true)
{
Token *journal = GetNextWordToken("JOURNAL", L"JOURNAL", 7);
if(_target != SQL_TERADATA && journal != NULL)
Token::Remove(comma, journal);
exists = true;
continue;
}
if(no != NULL)
PushBack(no);
break;
}
return exists;
}
// Parse DB2 partitioning clause
bool SqlParser::ParseDb2PartitioningClause(Token *partition, Token *by)
{
if(partition == NULL || by == NULL)
return false;
// RANGE is optional in DB2 z/OS
Token *range = GetNextWordToken("RANGE", L"RANGE", 5);
// RANGE keyword is required for Oracle
if(range == NULL && _target == SQL_ORACLE)
Append(by, " RANGE", L" RANGE", 6);
/*Token *open */ (void) GetNextCharToken('(', L'(');
// Comma separated list of columns
while(true)
{
Token *column = GetNextToken();
if(column == NULL)
break;
Token *comma = GetNextCharToken(',', L',');
if(comma == NULL)
break;
}
/*Token *close */ (void) GetNextCharToken(')', L')');
/*Token *open2 */ (void) GetNextCharToken('(', L'(');
// Comma separated list of partition definitions
while(true)
{
// PARTITION keyword
Token *partition2 = GetNextWordToken("PARTITION", L"PARTITION", 9);
if(partition2 == NULL)
break;
// Partition number
Token *num = GetNextToken();
if(num == NULL)
break;
// In Oracle a partition name is required so prepend 'p' to each number
if(_target == SQL_ORACLE)
PrependNoFormat(num, "p", L"p", 1);
// ENDING [AT]
Token *ending = GetNextWordToken("ENDING", L"ENDING", 6);
Token *at = GetNextWordToken("AT", L"AT", 2);
// VALUES LESS THAN in Oracle
if(_target == SQL_ORACLE)
{
Token::Change(ending, "VALUES LESS THAN", L"VALUES LESS THAN", 16);
Token::Remove(at);
}
// Each value is in ()
/*Token *open3 */ (void) GetNextCharToken('(', L'(');
/*Token *limit */ (void) GetNextToken();
/*Token *close3 */ (void) GetNextCharToken(')', L')');
Token *comma = GetNextCharToken(',', L',');
if(comma == NULL)
break;
}
/*Token *close2 */ (void) GetNextCharToken(')', L')');
return true;
}
// DB2 CREATE TABLESPACE
bool SqlParser::ParseDb2CreateTablespace(Token *create, Token *tablespace)
{
if(create == NULL || tablespace == NULL)
return false;
// Tablespace name
Token *name = GetNextToken();
if(name == NULL)
return false;
bool exists = false;
while(true)
{
Token *next = GetNextToken();
if(next == NULL)
break;
// IN database
if(next->Compare("IN", L"IN", 2) == true)
{
Token *database = GetNextToken();
if(database != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, database);
exists = true;
continue;
}
}
else
// DB2 z/OS USING STOGROUP
if(next->Compare("USING", L"USING", 5) == true)
{
Token *stogroup = GetNextWordToken("STOGROUP", L"STOGROUP", 8);
if(stogroup != NULL)
ParseDb2StogroupClause(name, next, stogroup, SQL_SCOPE_TABLESPACE);
exists = true;
continue;
}
else
// DB2 COMPRESS NO | YES option
if(next->Compare("COMPRESS", L"COMPRESS", 8) == true)
{
Token *value = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
else
// DB2 PCTFREE
if(next->Compare("PCTFREE", L"PCTFREE", 7) == true)
{
Token *value = GetNextToken();
// Oracle does not support PCTFREE in CREATE TABLESPACE
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
else
// DB2 z/OS FREEPAGE num
if(next->Compare("FREEPAGE", L"FREEPAGE", 8) == true)
{
Token *value = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
else
// DB2 z/OS BUFFERPOOL name
if(next->Compare("BUFFERPOOL", L"BUFFERPOOL", 10) == true)
{
Token *name = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, name);
exists = true;
continue;
}
else
// DB2 z/OS GBPCACHE CHANGED | ALL | SYSTME | NONE
if(next->Compare("GBPCACHE", L"GBPCACHE", 8) == true)
{
Token *value = GetNextToken();
if(value != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
}
else
// DB2 z/OS CLOSE NO | YES
if(next->Compare("CLOSE", L"CLOSE", 5) == true)
{
Token *value = GetNextToken();
if(value != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
}
else
// DB2 z/OS COPY YES | NO
if(next->Compare("COPY", L"COPY", 4) == true)
{
Token *value = GetNextToken();
if(value != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
}
else
// DB2 z/OS PIECESIZE num [K | M | G]
if(next->Compare("PIECESIZE", L"PIECESIZE", 9) == true)
{
Token *num = GetNextToken();
if(num != NULL)
{
// Size can be optionally followed by specifier K, M or G
Token *spec = Db2SizeSpecifier();
if(_target != SQL_DB2)
{
Token::Remove(next, num);
Token::Remove(spec);
}
exists = true;
continue;
}
}
else
// DB2 z/OS LOGGED option
if(next->Compare("LOGGED", L"LOGGED", 6) == true)
{
if(_target == SQL_ORACLE)
Token::Change(next, "LOGGING", L"LOGGING", 7);
else
if(_target != SQL_DB2)
Token::Remove(next);
exists = true;
continue;
}
else
// DB2 for z/OS NOT LOGGED
if(next->Compare("NOT", L"NOT", 3) == true)
{
Token *logged = GetNextWordToken("LOGGED", L"LOGGED", 6);
if(logged != NULL)
{
if(_target == SQL_ORACLE)
{
Token::Change(next, "NOLOGGING", L"NOLOGGING", 9);
Token::Remove(logged);
}
else
if(_target != SQL_DB2)
Token::Remove(next, logged);
exists = true;
continue;
}
}
else
// DB2 z/OS TRACKMOD NO | YES
if(next->Compare("TRACKMOD", L"TRACKMOD", 8) == true)
{
Token *value = GetNextToken();
if(value != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
}
else
// DB2 z/OS SEGSIZE num
if(next->Compare("SEGSIZE", L"SEGSIZE", 7) == true)
{
Token *num = GetNextToken();
if(num != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, num);
exists = true;
continue;
}
}
else
// DB2 z/OS LOCKSIZE ANY | TABLESPACE | TABLE | PAGE | ROW | LOB
if(next->Compare("LOCKSIZE", L"LOCKSIZE", 8) == true)
{
Token *value = GetNextToken();
if(value != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
}
else
// DB2 for z/OS LOCKMAX num | SYSTEM
if(next->Compare("LOCKMAX", L"LOCKMAX", 7) == true)
{
Token *value = GetNextToken();
if(value != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
}
else
// CCSID ASCII | UNICODE | EBCDIC
if(next->Compare("CCSID", L"CCSID", 5) == true)
{
Token *encoding = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, encoding);
exists = true;
continue;
}
else
// DB2 for z/OS MAXROWS num
if(next->Compare("MAXROWS", L"MAXROWS", 7) == true)
{
Token *num = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, num);
exists = true;
continue;
}
PushBack(next);
break;
}
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;
}
// Parse DB2 CREATE TABLE storage clause
bool SqlParser::ParseDb2StorageClause()
{
bool exists = false;
while(true)
{
Token *next = GetNextToken();
if(next == NULL)
break;
// IN tablespace
if(next->Compare("IN", L"IN", 2) == true)
{
// Get tablespace name (can include database name: dbname.tbsname)
Token *name = GetNextIdentToken();
// TABLESPACE name in Oracle
if(_target == SQL_ORACLE)
{
Token::Change(next, "TABLESPACE", L"TABLESPACE", 10);
// Name can contain database name, remove it
ConvertIdentRemoveLeadingPart(name);
}
else
if(_target != SQL_DB2)
Token::Remove(next, name);
exists = true;
continue;
}
else
// INDEX IN tablespace
if(next->Compare("INDEX", L"INDEX", 5) == true)
{
Token *in = GetNextWordToken("IN", L"IN", 2);
Token *name = GetNextIdentToken();
if(_target != SQL_DB2)
Token::Remove(next, name);
exists = true;
continue;
}
else
// COMPRESS YES | NO clause
if(next->Compare("COMPRESS", L"COMPRESS", 8) == true)
{
Token *yesno = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, yesno);
exists = true;
continue;
}
else
// CCSID ASCII | UNICODE | EBCDIC
if(next->Compare("CCSID", L"CCSID", 5) == true)
{
Token *encoding = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, encoding);
exists = true;
continue;
}
else
// DATA CAPTURE CHANGES | NONE
if(next->Compare("DATA", L"DATA", 4) == true)
{
Token *capture = GetNextWordToken("CAPTURE", L"CAPTURE", 7);
Token *option = NULL;
if(capture != NULL)
{
option = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, option);
exists = true;
continue;
}
}
else
// DB2 z/OS AUDIT CHANGES | NONE | ALL
if(next->Compare("AUDIT", L"AUDIT", 5) == true)
{
Token *option = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, option);
exists = true;
continue;
}
else
// DB2 z/OS WITH RESTRICT ON DROP
if(next->Compare("WITH", L"WITH", 4) == true)
{
Token *restrict = GetNextWordToken("RESTRICT", L"RESTRICT", 8);
Token *on = NULL;
Token *drop = NULL;
if(restrict != NULL)
{
on = GetNextWordToken("ON", L"ON", 2);
if(on != NULL)
drop = GetNextWordToken("DROP", L"DROP", 4);
if(_target != SQL_DB2)
Token::Remove(next, drop);
exists = true;
continue;
}
}
else
// DB2 z/OS NOT VOLATILE
if(next->Compare("NOT", L"NOT", 3) == true)
{
Token *volatile_ = GetNextWordToken("VOLATILE", L"VOLATILE", 8);
if(volatile_ != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, volatile_);
exists = true;
continue;
}
}
else
// DB2 z/OS VOLATILE
if(next->Compare("VOLATILE", L"VOLATILE", 8) == true)
{
if(_target != SQL_DB2)
Token::Remove(next);
exists = true;
continue;
}
else
// DB2 z/OS APPEND NO | YES
if(next->Compare("APPEND", L"APPEND", 6) == true)
{
Token *value = GetNextToken();
if(value != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
}
else
// PARTITION BY SIZE EVERY n G
if(next->Compare("PARTITION", L"PARTITION", 9) == true)
{
Token *by = GetNextWordToken("BY", L"BY", 2);
Token *size = GetNextWordToken(by, "SIZE", L"SIZE", 4);
// Partition by size
if(size != NULL)
{
// Optional EVERY n G
Token *every = GetNextWordToken("EVERY", L"EVERY", 5);
Token *num = GetNextToken(every);
Token *g = GetNextWordToken("G", L"G", 1);
if(_target != SQL_DB2)
{
Token::Remove(next, size);
Token::Remove(every, num);
Token::Remove(g);
}
exists = true;
continue;
}
else
if(ParseDb2PartitioningClause(next, by) == true)
{
exists = true;
continue;
}
}
// Not a DB2 storage clause
PushBack(next);
break;
}
return exists;
}
// 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;
}
// Parse Oracle CREATE TABLE, CREATE INDEX, PARTITION definition storage clause
bool SqlParser::ParseOracleStorageClause(int stmt_scope)
{
bool exists = false;
while(true)
{
Token *next = GetNextToken();
if(next == NULL)
break;
// SEGMENT CREATION IMMEDIATE | DEFERRED
if(next->Compare("SEGMENT", L"SEGMENT", 7) == true)
{
Token *creation = GetNextWordToken("CREATION", L"CREATION", 8);
Token *value = GetNextToken();
if(_target != SQL_ORACLE && creation != NULL)
Token::Remove(next, value);
exists = true;
continue;
}
else
// PCTFREE num
if(next->Compare("PCTFREE", L"PCTFREE", 7) == true)
{
Token *value = GetNextNumberToken();
if(_target != SQL_ORACLE && value != NULL)
Token::Remove(next, value);
exists = true;
continue;
}
else
// PCTUSED num
if(next->Compare("PCTUSED", L"PCTUSED", 7) == true)
{
Token *value = GetNextNumberToken();
if(_target != SQL_ORACLE && value != NULL)
Token::Remove(next, value);
exists = true;
continue;
}
else
// INITRANS num
if(next->Compare("INITRANS", L"INITRANS", 8) == true)
{
Token *value = GetNextNumberToken();
if(_target != SQL_ORACLE && value != NULL)
Token::Remove(next, value);
exists = true;
continue;
}
else
// MAXTRANS num
if(next->Compare("MAXTRANS", L"MAXTRANS", 8) == true)
{
Token *value = GetNextNumberToken();
if(_target != SQL_ORACLE && value != NULL)
Token::Remove(next, value);
exists = true;
continue;
}
else
// COMPRESS [BASIC] or COMPRESS num (for index-orginized tables and indexes)
if(next->Compare("COMPRESS", L"COMPRESS", 8) == true)
{
// Optional BASIC keyword
Token *basic = GetNextWordToken("BASIC", L"BASIC", 5);
Token *num = NULL;
// Check for a number
if(basic == NULL)
num = GetNextNumberToken();
if(_target != SQL_ORACLE)
{
Token::Remove(next);
Token::Remove(basic);
Token::Remove(num);
}
exists = true;
continue;
}
else
// NOCOMPRESS
if(next->Compare("NOCOMPRESS", L"NOCOMPRESS", 10) == true)
{
if(_target != SQL_ORACLE)
Token::Remove(next);
exists = true;
continue;
}
else
// NOCACHE
if(next->Compare("NOCACHE", L"NOCACHE", 7) == true)
{
if(_target != SQL_ORACLE)
Token::Remove(next);
exists = true;
continue;
}
else
// LOGGING
if(next->Compare("LOGGING", L"LOGGING", 7) == true)
{
if(_target != SQL_ORACLE)
Token::Remove(next);
exists = true;
continue;
}
else
// NOLOGGING
if(next->Compare("NOLOGGING", L"NOLOGGING", 9) == true)
{
if(_target != SQL_ORACLE)
Token::Remove(next);
exists = true;
continue;
}
else
// NOPARALLEL
if(next->Compare("NOPARALLEL", L"NOPARALLEL", 10) == true)
{
if(_target != SQL_ORACLE)
Token::Remove(next);
exists = true;
continue;
}
else
// PARALLEL num
if(next->Compare("PARALLEL", L"PARALLEL", 8) == true)
{
Token *value = GetNextNumberToken();
if(_target != SQL_ORACLE && value != NULL)
Token::Remove(next, value);
exists = true;
continue;
}
else
// NOMONITORING
if(next->Compare("NOMONITORING", L"NOMONITORING", 12) == true)
{
if(_target != SQL_ORACLE)
Token::Remove(next);
exists = true;
continue;
}
else
// TABLESPACE name
if(next->Compare("TABLESPACE", L"TABLESPACE", 10) == true)
{
Token *name = GetNextIdentToken();
// ON name
if(_target == SQL_SQL_SERVER)
Token::Change(next, "ON", L"ON", 2);
else
// IN name
if(_target == SQL_DB2)
Token::Change(next, "IN", L"IN", 2);
else
Token::Remove(next, name);
exists = true;
continue;
}
else
// STORAGE () clause
if(next->Compare("STORAGE", L"STORAGE", 7) == true)
{
exists = ParseOracleStorageClause(next);
continue;
}
else
// LOB (column) STORE AS (params)
if(next->Compare("LOB", L"LOB", 3) == true)
{
if(ParseOracleLobStorageClause(next) == true)
{
exists = true;
continue;
}
}
else
// Oracle partitioning clauses
if(ParseOraclePartitions(next, stmt_scope) == true)
{
exists = true;
continue;
}
else
// COMPUTE STATISTICS
if(next->Compare("COMPUTE", L"COMPUTE", 7) == true)
{
Token *statistics = GetNextWordToken("STATISTICS", L"STATISTICS", 10);
// Remove if not Oracle
if(_target != SQL_ORACLE)
Token::Remove(next, statistics);
exists = true;
continue;
}
else
// ENABLE ROW MOVEMENT
if(next->Compare("ENABLE", L"ENABLE", 6) == true)
{
Token *row = GetNextWordToken("ROW", L"ROW", 3);
if(row != NULL)
{
Token *movement = GetNextWordToken("MOVEMENT", L"MOVEMENT", 8);
// Remove if not Oracle
if(_target != SQL_ORACLE)
Token::Remove(next, movement);
exists = true;
continue;
}
}
else
// REVERSE index or primary key storage attribute
if(next->Compare("REVERSE", L"REVERSE", 7) == true)
{
// Remove if not Oracle
if(_target != SQL_ORACLE)
Token::Remove(next);
exists = true;
continue;
}
// Not an Oracle storage clause
PushBack(next);
break;
}
return exists;
}
// DB2 CREATE INDEX storage options
bool SqlParser::ParseDb2CreateIndexOptions()
{
bool exists = false;
while(true)
{
Token *next = GetNextToken();
if(next == NULL)
break;
// DB2 z/OS USING STOGROUP
if(next->Compare("USING", L"USING", 5) == true)
{
Token *stogroup = GetNextWordToken("STOGROUP", L"STOGROUP", 8);
if(stogroup != NULL)
ParseDb2StogroupClause(NULL, next, stogroup, SQL_SCOPE_INDEX);
exists = true;
continue;
}
else
// DB2 CLUSTER option
if(next->Compare("CLUSTER", L"CLUSTER", 7) == true)
{
if(_target != SQL_DB2)
Token::Remove(next);
exists = true;
continue;
}
else
// DB2 for z/OS NOT CLUSTER, NOT PADDED
if(next->Compare("NOT", L"NOT", 3) == true)
{
Token *cluster = GetNextWordToken("CLUSTER", L"CLUSTER", 7);
Token *padded = NULL;
if(cluster == NULL)
padded = GetNextWordToken("PADDED", L"PADDED", 6);
if(cluster != NULL || padded != NULL)
{
if(_target != SQL_DB2)
{
if(cluster != NULL)
Token::Remove(next, cluster);
else
if(padded != NULL)
Token::Remove(next, padded);
}
exists = true;
continue;
}
}
else
// DB2 COMPRESS NO | YES option
if(next->Compare("COMPRESS", L"COMPRESS", 8) == true)
{
Token *value = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
else
// DB2 ALLOW REVERSE SCAN
if(next->Compare("ALLOW", L"ALLOW", 5) == true)
{
/*Token *reverse */ (void) GetNextWordToken("REVERSE", L"REVERSE", 7);
Token *scan = GetNextWordToken("SCAN", L"SCAN", 4);
if(_target != SQL_DB2)
Token::Remove(next, scan);
exists = true;
continue;
}
else
// DB2 PCTFREE
if(next->Compare("PCTFREE", L"PCTFREE", 7) == true)
{
Token *value = GetNextToken();
int int_value = (value != NULL) ? value->GetInt() : 0;
// WITH (FILLFACTOR = 100 - val) in Greenplum
if(_target == SQL_GREENPLUM)
{
Token::Change(next, "WITH (FILLFACTOR =", L"WITH (FILLFACTOR =", 18);
Token::Change(value, 100 - int_value);
Append(value, ")", L")", 1);
}
else
// Oracle supports PCTFREE
if(Target(SQL_ORACLE, SQL_DB2) == false)
Token::Remove(next, value);
exists = true;
continue;
}
else
// DB2 MINPCTUSED
if(next->Compare("MINPCTUSED", L"MINPCTUSED", 10) == true)
{
Token *value = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
else
// DB2 z/OS FREEPAGE num
if(next->Compare("FREEPAGE", L"FREEPAGE", 8) == true)
{
Token *value = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
else
// DB2 z/OS BUFFERPOOL name
if(next->Compare("BUFFERPOOL", L"BUFFERPOOL", 10) == true)
{
Token *name = GetNextToken();
if(_target != SQL_DB2)
Token::Remove(next, name);
exists = true;
continue;
}
else
// DB2 SPECIFICATION ONLY (index will be commented)
if(next->Compare("SPECIFICATION", L"SPECIFICATION", 13) == true)
{
/*Token *only */ (void) GetNextWordToken("ONLY", L"ONLY", 4);
exists = true;
continue;
}
else
// DB2 z/OS GBPCACHE CHANGED | ALL | NONE
if(next->Compare("GBPCACHE", L"GBPCACHE", 8) == true)
{
Token *value = GetNextToken();
if(value != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
}
else
// DB2 z/OS CLOSE NO | YES
if(next->Compare("CLOSE", L"CLOSE", 5) == true)
{
Token *value = GetNextToken();
if(value != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
}
else
// DB2 z/OS COPY YES | NO
if(next->Compare("COPY", L"COPY", 4) == true)
{
Token *value = GetNextToken();
if(value != NULL)
{
if(_target != SQL_DB2)
Token::Remove(next, value);
exists = true;
continue;
}
}
else
// DB2 z/OS PIECESIZE num [K | M | G]
if(next->Compare("PIECESIZE", L"PIECESIZE", 9) == true)
{
Token *num = GetNextToken();
if(num != NULL)
{
// Size can be optionally followed by specifier K, M or G
Token *spec = Db2SizeSpecifier();
if(_target != SQL_DB2)
{
Token::Remove(next, num);
Token::Remove(spec);
}
exists = true;
continue;
}
}
else
// DB2 for z/OS PADDED option
if(next->Compare("PADDED", L"PADDED", 6) == true)
{
if(_target != SQL_DB2)
Token::Remove(next);
exists = true;
continue;
}
// Not an index clause
PushBack(next);
break;
}
return exists;
}
// Parse Informix CREATE TABLE storage clause
bool SqlParser::ParseInformixStorageClause()
{
bool exists = false;
while(true)
{
Token *next = GetNextToken();
if(next == NULL)
break;
// EXTENT SIZE num
if(next->Compare("EXTENT", L"EXTENT", 6) == true)
{
Token *size = GetNextWordToken("SIZE", L"SIZE", 4);
Token *num = GetNextToken(size);
if(_target != SQL_INFORMIX && num != NULL)
Token::Remove(next, num);
exists = true;
continue;
}
else
// FRAGMENT BY partitioning clause
if(next->Compare("FRAGMENT", L"FRAGMENT", 8) == true)
{
ParseInformixFragmentBy(next);
exists = true;
continue;
}
else
// IN dbspace
if(next->Compare("IN", L"IN", 2) == true)
{
// Get database space name
Token *name = GetNextToken();
// TABLESPACE name in Oracle
if(_target == SQL_ORACLE)
Token::Change(next, "TABLESPACE", L"TABLESPACE", 10);
else
// DB2 also uses IN keyword to specify a tablespace
if(Target(SQL_DB2, SQL_INFORMIX) == false)
Token::Remove(next, name);
exists = true;
continue;
}
else
// LOCK MODE PAGE | ROW | TABLE
if(next->Compare("LOCK", L"LOCK", 4) == true)
{
Token *mode = GetNextWordToken("MODE", L"MODE", 4);
Token *type = GetNextToken(mode);
if(_target != SQL_INFORMIX && type != NULL)
Token::Remove(next, type);
exists = true;
continue;
}
else
// NEXT SIZE num
if(next->Compare("NEXT", L"NEXT", 4) == true)
{
Token *size = GetNextWordToken("SIZE", L"SIZE", 4);
Token *num = GetNextToken(size);
if(_target != SQL_INFORMIX && num != NULL)
Token::Remove(next, num);
exists = true;
continue;
}
// Not a storage clause
PushBack(next);
break;
}
return exists;
}
// DB2 identity options in GENERATED ALWAYS or BY DEFAULT AS IDENTITY
bool SqlParser::ParseDb2GeneratedClause(Token *create, Token *table_name, Token *column, Token *generated,
Token **id_col, Token **id_start, Token **id_inc, bool *id_default)
{
if(generated == NULL)
return false;
Token *always = GetNextWordToken("ALWAYS", L"ALWAYS", 6);
Token *by = NULL;
Token *default_ = NULL;
if(always == NULL)
{
by = GetNextWordToken("BY", L"BY", 2);
default_ = GetNextWordToken("DEFAULT", L"DEFAULT", 7);
if(id_default != NULL)
*id_default = true;
}
else
{
if(id_default != NULL)
*id_default = false;
}
Token *as = GetNextWordToken("AS", L"AS", 2);
Token *identity = GetNextWordToken("IDENTITY", L"IDENTITY", 8);
// Identity parameters are optional
Token *open = GetNextCharToken('(', L'(');
Token *close = NULL;
Token *start_with = NULL;
Token *increment_by = NULL;
while(true)
{
bool exists = false;
if(open == NULL)
break;
Token *option = GetNextToken();
if(option == NULL)
break;
// START WITH
if(option->Compare("START", L"START", 5) == true)
{
/*Token *with */ (void) GetNextWordToken("WITH", L"WITH", 4);
start_with = GetNextNumberToken();
exists = true;
continue;
}
else
// INCREMENT BY
if(option->Compare("INCREMENT", L"INCREMENT", 9) == true)
{
/*Token *by */ (void) GetNextWordToken("BY", L"BY", 2);
increment_by = GetNextNumberToken();
exists = true;
continue;
}
else
// MINVALUE
if(option->Compare("MINVALUE", L"MINVALUE", 8) == true)
{
/*Token *value */ (void) GetNextNumberToken();
exists = true;
continue;
}
else
// MAXVALUE
if(option->Compare("MAXVALUE", L"MAXVALUE", 8) == true)
{
/*Token *value */ (void) GetNextNumberToken();
exists = true;
continue;
}
else
// NO CYCLE and NO ORDER
if(option->Compare("NO", L"NO", 2) == true)
{
/*Token *attr */ (void) GetNextToken();
exists = true;
continue;
}
else
// CACHE
if(option->Compare("CACHE", L"CACHE", 5) == true)
{
/*Token *value */ (void) GetNextNumberToken();
exists = true;
continue;
}
else
// CYCLE
if(option->Compare("CYCLE", L"CYCLE", 5) == true)
{
exists = true;
continue;
}
else
// Looks like comma is optional
if(option->Compare(',', L',') == true)
{
exists = true;
continue;
}
PushBack(option);
break;
}
if(open != NULL)
close = GetNextCharToken(')', L')');
// IDENTITY(start, inc) in SQL Server
if(_target == SQL_SQL_SERVER)
{
Token::Change(generated, "IDENTITY(", L"IDENTITY(", 9);
AppendCopy(generated, start_with);
if(increment_by != NULL)
{
Append(generated, ", ", L", ", 2);
AppendCopy(generated, increment_by);
}
Append(generated, ")", L")", 1);
if(always != NULL)
Token::Remove(always, close);
else
Token::Remove(by, close);
}
else
// Use a sequence and DEFAULT nextval for PostgreSQL and Greenplum
if(_target == SQL_POSTGRESQL || _target == SQL_GREENPLUM)
{
TokenStr seq_name(table_name);
AppendIdentifier(seq_name, "_seq", L"_seq", 4);
// Generate CREATE SEQUENCE before CREATE TABLE
Prepend(create, "CREATE SEQUENCE ", L"CREATE SEQUENCE ", 16);
PrependNoFormat(create, &seq_name);
if(start_with != NULL)
{
Prepend(create, " START WITH ", L" START WITH ", 12);
PrependCopy(create, start_with);
}
if(increment_by != NULL)
{
Prepend(create, " INCREMENT BY ", L" INCREMENT BY ", 14);
PrependCopy(create, increment_by);
}
Prepend(create, ";\n\n", L";\n\n", 3);
// Generate DEFAULT nextval('tablename_seq') clause
Token::Change(generated, "DEFAULT ", L"DEFAULT ", 8);
Append(generated, "nextval ('", L"nextval ('", 10);
AppendNoFormat(generated, &seq_name);
Append(generated, "')", L"')", 2);
if(always != NULL)
Token::Remove(always, close);
else
Token::Remove(by, close);
}
else
// Remove for other databases
if(_target != SQL_DB2)
{
Token::Remove(generated);
Token::Remove(always);
Token::Remove(by, default_);
Token::Remove(as, identity);
Token::Remove(open, close);
}
if(id_col != NULL)
*id_col = column;
if(id_start != NULL)
*id_start = start_with;
if(id_inc != NULL)
*id_inc = increment_by;
return true;
}
