// Convert interval +/- expression to DATEADD function void SqlParser::SqlServerToDateAdd(Token *op, Token *first, Token *first_end, Token *second, Token *second_end) { if(op == NULL || first == NULL || second == NULL) return; PREPEND(first, "DATEADD("); if(second->data_subtype == TOKEN_DT2_INTVL_MON) PREPEND_NOFMT(first, "mm"); else if(second->data_subtype == TOKEN_DT2_INTVL_DAY) PREPEND_NOFMT(first, "dd"); else if(second->data_subtype == TOKEN_DT2_INTVL_MIN) PREPEND_NOFMT(first, "mi"); else if(second->data_subtype == TOKEN_DT2_INTVL_SEC) PREPEND_NOFMT(first, "ss"); PREPEND_NOFMT(first, ", "); // Copy - sign only, + will not be used if(TOKEN_CMPC(op, '-')) PrependCopy(first, op); PrependCopy(first, second, second_end, false); PREPEND_NOFMT(first, ", "); APPEND_NOFMT(first_end, ")"); Token::Remove(op, second_end); }
// In DB2 trigger, column can be assigned without NEW correlation name void SqlParser::Db2TriggerCorrelatedName(Token *var) { if(var == NULL) return; bool exists = false; // Do not convert if the target name already set if(var->t_len > 0) return; if((_spl_new_correlation_name == NULL && Token::Compare(var, "NEW.", L"NEW.", 0, 4) == true) || (Token::Compare(var, _spl_new_correlation_name, 3) == true && Token::Compare(var, ".", L".", 3, 1) == true)) exists = true; if(exists) return; // In Oracle correlation name must be specified if(_target == SQL_ORACLE) { PrependNoFormat(var, ":", L":", 1); if(_spl_new_correlation_name != NULL) PrependCopy(var, _spl_new_correlation_name); else Prepend(var, "NEW", L"NEW", 3); PrependNoFormat(var, ".", L".", 1); } }
// Prepend the range of tokens (inclusively) Token* SqlParser::PrependCopy(Token *token, Token *first, Token *last, bool prepend_removed) { if(token == NULL || first == NULL) return NULL; Token *cur = first; Token *last_added = NULL; while(cur != NULL) { bool removed = cur->IsRemoved(); // Check whether we need to skip already removed tokens if(removed == false || (removed == true && prepend_removed == true)) last_added = PrependCopy(token, cur); // Prepend single token only if(first == last || last == NULL) break; // Tokens prepended inclusively if(cur == last) break; cur = cur->next; } return last_added; }
void SqlParser::PrependSpaceCopy(Token *token, Token *first, Token *last, bool prepend_removed) { if(token == NULL) return; PrependCopy(token, first, last, prepend_removed); PrependNoFormat(token, " ", L" ", 1); }
// Sybase ADS WHILE FETCH cur DO loop bool SqlParser::ParseSybaseWhileFetchStatement(Token *while_, Token *fetch, int scope) { // Curson name (it must be already declared) Token *cursor = GetNextIdentToken(); if(cursor == NULL) return false; // DO keyword starts the block Token *do_ = TOKEN_GETNEXTW("DO"); if(_target == SQL_SQL_SERVER) { TOKEN_CHANGE(do_, "BEGIN"); // FETCH the first row PREPEND(while_, "FETCH "); PrependCopy(while_, cursor); PREPEND(while_, " INTO;\n"); TOKEN_CHANGE(fetch, "@@FETCH_STATUS=0"); } ParseBlock(SQL_BLOCK_WHILE, true, scope, NULL); Token *end = TOKEN_GETNEXTW("END"); Token *end_while = (end != NULL) ? TOKEN_GETNEXTW("WHILE") : NULL; if(end != NULL) { if(_target == SQL_SQL_SERVER) { // Fetch the next row at the end of loop PREPEND(end, "FETCH "); PrependCopy(end, cursor); PREPEND(end, " INTO;\n"); Token::Remove(cursor); Token::Remove(end_while); } } return true; }
// 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; }
// 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; }