Exemple #1
0
def visit_merge(element, compiler, **kw):
    msql = "MERGE INTO %s " % compiler.process(element._target_table,
                                              asfrom=True)
    msql += "USING %s " % compiler.process(element._source_expr,
                                           asfrom=True)
    msql += "ON ( %s ) " % compiler.process(element._on)

    if element._merge_update_values is not None:
        cols = crud._get_crud_params(compiler, element._merge_update_values)
        msql += "\nWHEN MATCHED THEN UPDATE SET "
        msql += ', '.join(compiler.visit_column(c[0]) + '=' + c[1] for c in cols)
        if element._merge_delete:
            msql += "\nDELETE "
            if element._delete_where is not None:
                msql += " WHERE %s" % compiler.process(element._delete_where)
        else:
            if element._update_where is not None:
                msql += " WHERE %s" % compiler.process(element._update_where)
    else:
        if element._merge_delete:
            msql += "\nWHEN MATCHED THEN DELETE "
            if element._delete_where is not None:
                msql += "WHERE %s" % compiler.process(element._delete_where)
    if element._merge_insert_values is not None:
        cols = crud._get_crud_params(compiler, element._merge_insert_values)
        msql += "\nWHEN NOT MATCHED THEN INSERT "
        msql += "(%s) " % ', '.join(compiler.visit_column(c[0]) for c in cols)
        msql += "VALUES (%s) " % ', '.join(c[1] for c in cols)
        if element._insert_where is not None:
            msql += "WHERE %s" % compiler.process(element._insert_where)

    return msql
Exemple #2
0
def visit_merge(element, compiler, **kw):
    msql = "MERGE INTO %s " % compiler.process(element._target_table,
                                              asfrom=True)
    msql += "USING %s " % compiler.process(element._source_expr,
                                           asfrom=True)
    msql += "ON ( %s ) " % compiler.process(element._on)

    if element._merge_update_values is not None:
        cols = crud._get_crud_params(compiler, element._merge_update_values)
        msql += "\nWHEN MATCHED THEN UPDATE SET "
        msql += ', '.join(compiler.visit_column(c[0]) + '=' + c[1] for c in cols)
        if element._merge_delete:
            msql += "\nDELETE "
            if element._delete_where is not None:
                msql += " WHERE %s" % compiler.process(element._delete_where)
        else:
            if element._update_where is not None:
                msql += " WHERE %s" % compiler.process(element._update_where)
    else:
        if element._merge_delete:
            msql += "\nWHEN MATCHED THEN DELETE "
            if element._delete_where is not None:
                msql += "WHERE %s" % compiler.process(element._delete_where)
    if element._merge_insert_values is not None:
        cols = crud._get_crud_params(compiler, element._merge_insert_values)
        msql += "\nWHEN NOT MATCHED THEN INSERT "
        msql += "(%s) " % ', '.join(compiler.visit_column(c[0]) for c in cols)
        msql += "VALUES (%s) " % ', '.join(c[1] for c in cols)
        if element._insert_where is not None:
            msql += "WHERE %s" % compiler.process(element._insert_where)

    return msql
def _setup_crud_params(compiler, stmt, local_stmt_type, **kw):
	restore_isinsert = compiler.isinsert
	restore_isupdate = compiler.isupdate
	restore_isdelete = compiler.isdelete

	should_restore = (
		restore_isinsert or restore_isupdate or restore_isdelete
	) or len(compiler.stack) > 1

	if local_stmt_type is ISINSERT:
		compiler.isupdate = False
		compiler.isinsert = True
	elif local_stmt_type is ISUPDATE:
		compiler.isupdate = True
		compiler.isinsert = False
	elif local_stmt_type is ISDELETE:
		if not should_restore:
			compiler.isdelete = True
	else:
		assert False, "ISINSERT, ISUPDATE, or ISDELETE expected"

	try:
		if local_stmt_type in (ISINSERT, ISUPDATE):
			return crud._get_crud_params(compiler, stmt, **kw)
	finally:
		if should_restore:
			compiler.isinsert = restore_isinsert
			compiler.isupdate = restore_isupdate
			compiler.isdelete = restore_isdelete
Exemple #4
0
def visit_conditional_insert(element, compiler, **kwargs):
    # magic copied from sqlalchemy.sql.compiler.SQLCompiler.visit_insert
    compiler.isinsert = True
    try:
        # pylint: disable=E0611
        from sqlalchemy.sql import crud
        colparams = crud._get_crud_params(compiler, element)
    except ImportError:  # SQLAlchemy <= 1.0
        colparams = compiler._get_colparams(element)
    text = 'INSERT INTO %s' % compiler.process(element.table, asfrom=True)
    text += ' (%s)\n' % ', '.join(compiler.preparer.format_column(c[0])
            for c in colparams)
    text += 'SELECT %s\n' % ', '.join(c[1] for c in colparams)
    text += compiler.default_from()
    # default_from() returns '' for MySQL but that's wrong, MySQL requires 
    # FROM DUAL if there is a following WHERE clause.
    if isinstance(compiler.dialect, MySQLDialect):
        text += 'FROM DUAL\n'
    # We need FOR UPDATE in the inner SELECT for MySQL, to ensure we acquire an 
    # exclusive lock immediately, instead of acquiring a shared lock and then 
    # subsequently upgrading it to an exclusive lock, which is subject to 
    # deadlocks if another transaction is doing the same thing.
    nonexistence_clause = not_(exists(Select(
            columns=[sqltext('1')], from_obj=[element.table],
            whereclause=element.unique_condition, for_update=True)))
    text += 'WHERE ' + compiler.process(nonexistence_clause)
    return text
Exemple #5
0
    def __compileInsert(insert_stmt, compiler, **kw):
        '''
        Compiles INSERT statements.
        '''
        compiler.stack.append({
            'correlate_froms': set(),
            'asfrom_froms': set(),
            'selectable': insert_stmt,
        })
        compiler.isinsert = True
        crud_params = crud._get_crud_params(compiler, insert_stmt, **kw)

        if not crud_params:
            return

        if insert_stmt._has_multi_parameters \
                and not compiler.dialect.supports_multivalues_insert:
            return

        dml = 'INSERT INTO ' + compiler.preparer.format_table(insert_stmt.table)
        separator = ',' if SchemaGenerator.minified else ', '

        if insert_stmt.select is not None:
            dml += ' {}'.format(
                compiler.process(compiler._insert_from_select, **kw))
        elif insert_stmt._has_multi_parameters:
            dml += ' VALUES'

            if not SchemaGenerator.minified:
                dml += ' '

            dml += separator.join(
                '({})'.format(separator.join(
                    param[1] for param in crud_param_set
                ))
                for crud_param_set in crud_params
            )
        else:
            dml += ' VALUES'

            if not SchemaGenerator.minified:
                dml += ' '

            dml += '({})'.format(separator.join([
                param[1] for param in crud_params
            ]))

        compiler.stack.pop(-1)

        return dml + ';'
Exemple #6
0
    def __compileInsert(insert_stmt, compiler, **kw):
        '''
        Compiles INSERT statements.
        '''
        compiler.stack.append({
            'correlate_froms': set(),
            'asfrom_froms': set(),
            'selectable': insert_stmt,
        })
        compiler.isinsert = True
        crud_params = crud._get_crud_params(compiler, insert_stmt, **kw)

        if not crud_params:
            return

        if insert_stmt._has_multi_parameters \
                and not compiler.dialect.supports_multivalues_insert:
            return

        dml = 'INSERT INTO ' + compiler.preparer.format_table(
            insert_stmt.table)
        separator = ',' if SchemaGenerator.minified else ', '

        if insert_stmt.select is not None:
            dml += ' {}'.format(
                compiler.process(compiler._insert_from_select, **kw))
        elif insert_stmt._has_multi_parameters:
            dml += ' VALUES'

            if not SchemaGenerator.minified:
                dml += ' '

            dml += separator.join('({})'.format(
                separator.join(param[1] for param in crud_param_set))
                                  for crud_param_set in crud_params)
        else:
            dml += ' VALUES'

            if not SchemaGenerator.minified:
                dml += ' '

            dml += '({})'.format(
                separator.join([param[1] for param in crud_params]))

        compiler.stack.pop(-1)

        return dml + ';'
def compile_insertOnDuplicateKeyUpdate(insert_stmt, compiler, **kw):
    compiler.isinsert = True
    try:
        # pylint: disable=E0611
        from sqlalchemy.sql import crud
        colparams = crud._get_crud_params(compiler, insert_stmt)
    except ImportError:  # SQLAlchemy <= 1.0
        colparams = compiler._get_colparams(insert_stmt)
    if not colparams and \
            not compiler.dialect.supports_default_values and \
            not compiler.dialect.supports_empty_insert:
        raise exc.CompileError("The version of %s you are using does "
                                "not support empty inserts." %
                                compiler.dialect.name)
    preparer = compiler.preparer
    supports_default_values = compiler.dialect.supports_default_values
    text = "INSERT"
    prefixes = [compiler.process(x) for x in insert_stmt._prefixes]
    if prefixes:
        text += " " + " ".join(prefixes)
    text += " INTO " + preparer.format_table(insert_stmt.table)
    if colparams or not supports_default_values:
        text += " (%s)" % ', '.join([preparer.format_column(c[0])
                   for c in colparams])
    if compiler.returning or insert_stmt._returning:
        compiler.returning = compiler.returning or insert_stmt._returning
        returning_clause = compiler.returning_clause(insert_stmt, compiler.returning)
        if compiler.returning_precedes_values:
            text += " " + returning_clause
    if not colparams and supports_default_values:
        text += " DEFAULT VALUES"
    else:
        text += " VALUES (%s)" % \
                 ', '.join([c[1] for c in colparams])
    if compiler.returning and not compiler.returning_precedes_values:
        text += " " + returning_clause
    text += ' ON DUPLICATE KEY UPDATE ' + \
            ', '.join(
                    compiler.preparer.quote(c[0].name, c[0].quote) + '=' + c[1]
                  for c in colparams
            )
    return text
    def visit_update(self, update_stmt, **kw):
        if not self.dialect.supports_update:
            raise exc.CompileError(
                'ALTER UPDATE is not supported by this server version')

        compile_state = update_stmt._compile_state_factory(
            update_stmt, self, **kw)
        update_stmt = compile_state.statement

        render_extra_froms = []
        correlate_froms = {update_stmt.table}

        self.stack.append({
            "correlate_froms": correlate_froms,
            "asfrom_froms": correlate_froms,
            "selectable": update_stmt,
        })

        text = "ALTER TABLE "

        table_text = self.update_tables_clause(update_stmt, update_stmt.table,
                                               render_extra_froms, **kw)
        crud_params = crud._get_crud_params(self, update_stmt, compile_state,
                                            **kw)

        text += table_text
        text += " UPDATE "

        text += ", ".join(expr + "=" + value for c, expr, value in crud_params)

        if update_stmt._where_criteria:
            t = self._generate_delimited_and_list(update_stmt._where_criteria,
                                                  include_table=False,
                                                  **kw)
            if t:
                text += " WHERE " + t
        else:
            raise exc.CompileError('WHERE clause is required')

        self.stack.pop(-1)

        return text
    def visit_insert(self, insert_stmt, asfrom=False, **kw):
        """
        used to compile <sql.expression.Insert> expressions.

        this function wraps insert_from_select statements inside
         parentheses to be conform with earlier versions of CreateDB.
        """

        self.stack.append({
            'correlate_froms': set(),
            "asfrom_froms": set(),
            "selectable": insert_stmt
        })

        self.isinsert = True
        crud_params = crud._get_crud_params(self, insert_stmt, **kw)

        if not crud_params and \
                not self.dialect.supports_default_values and \
                not self.dialect.supports_empty_insert:
            raise NotImplementedError(
                "The '%s' dialect with current database version settings does "
                "not support empty inserts." % self.dialect.name)

        if insert_stmt._has_multi_parameters:
            if not self.dialect.supports_multivalues_insert:
                raise NotImplementedError(
                    "The '%s' dialect with current database "
                    "version settings does not support "
                    "in-place multirow inserts." % self.dialect.name)
            crud_params_single = crud_params[0]
        else:
            crud_params_single = crud_params

        preparer = self.preparer
        supports_default_values = self.dialect.supports_default_values

        text = "INSERT "

        if insert_stmt._prefixes:
            text += self._generate_prefixes(insert_stmt, insert_stmt._prefixes,
                                            **kw)

        text += "INTO "
        table_text = preparer.format_table(insert_stmt.table)

        if insert_stmt._hints:
            dialect_hints = dict([
                (table, hint_text)
                for (table, dialect), hint_text in insert_stmt._hints.items()
                if dialect in ('*', self.dialect.name)
            ])
            if insert_stmt.table in dialect_hints:
                table_text = self.format_from_hint_text(
                    table_text, insert_stmt.table,
                    dialect_hints[insert_stmt.table], True)

        text += table_text

        if crud_params_single or not supports_default_values:
            text += " (%s)" % ', '.join(
                [preparer.format_column(c[0]) for c in crud_params_single])

        if self.returning or insert_stmt._returning:
            self.returning = self.returning or insert_stmt._returning
            returning_clause = self.returning_clause(insert_stmt,
                                                     self.returning)

            if self.returning_precedes_values:
                text += " " + returning_clause

        if insert_stmt.select is not None:
            text += " (%s)" % self.process(self._insert_from_select, **kw)
        elif not crud_params and supports_default_values:
            text += " DEFAULT VALUES"
        elif insert_stmt._has_multi_parameters:
            text += " VALUES %s" % (", ".join(
                "(%s)" % (', '.join(c[1] for c in crud_param_set))
                for crud_param_set in crud_params))
        else:
            text += " VALUES (%s)" % \
                ', '.join([c[1] for c in crud_params])

        if self.returning and not self.returning_precedes_values:
            text += " " + returning_clause

        self.stack.pop(-1)

        return text
Exemple #10
0
    def visit_insert(self, insert_stmt, asfrom=False, **kw):
        """
        used to compile <sql.expression.Insert> expressions.

        this function wraps insert_from_select statements inside
         parentheses to be conform with earlier versions of CreateDB.
        """

        self.stack.append(
            {'correlate_froms': set(),
             "asfrom_froms": set(),
             "selectable": insert_stmt})

        self.isinsert = True
        crud_params = crud._get_crud_params(self, insert_stmt, **kw)

        if not crud_params and \
                not self.dialect.supports_default_values and \
                not self.dialect.supports_empty_insert:
            raise NotImplementedError(
                "The '%s' dialect with current database version settings does "
                "not support empty inserts." % self.dialect.name)

        if insert_stmt._has_multi_parameters:
            if not self.dialect.supports_multivalues_insert:
                raise NotImplementedError(
                    "The '%s' dialect with current database "
                    "version settings does not support "
                    "in-place multirow inserts." % self.dialect.name)
            crud_params_single = crud_params[0]
        else:
            crud_params_single = crud_params

        preparer = self.preparer
        supports_default_values = self.dialect.supports_default_values

        text = "INSERT "

        if insert_stmt._prefixes:
            text += self._generate_prefixes(insert_stmt,
                                            insert_stmt._prefixes, **kw)

        text += "INTO "
        table_text = preparer.format_table(insert_stmt.table)

        if insert_stmt._hints:
            dialect_hints = dict([
                (table, hint_text)
                for (table, dialect), hint_text in
                insert_stmt._hints.items()
                if dialect in ('*', self.dialect.name)
            ])
            if insert_stmt.table in dialect_hints:
                table_text = self.format_from_hint_text(
                    table_text,
                    insert_stmt.table,
                    dialect_hints[insert_stmt.table],
                    True
                )

        text += table_text

        if crud_params_single or not supports_default_values:
            text += " (%s)" % ', '.join([preparer.format_column(c[0])
                                         for c in crud_params_single])

        if self.returning or insert_stmt._returning:
            self.returning = self.returning or insert_stmt._returning
            returning_clause = self.returning_clause(
                insert_stmt, self.returning)

            if self.returning_precedes_values:
                text += " " + returning_clause

        if insert_stmt.select is not None:
            text += " (%s)" % self.process(self._insert_from_select, **kw)
        elif not crud_params and supports_default_values:
            text += " DEFAULT VALUES"
        elif insert_stmt._has_multi_parameters:
            text += " VALUES %s" % (
                ", ".join(
                    "(%s)" % (
                        ', '.join(c[1] for c in crud_param_set)
                    )
                    for crud_param_set in crud_params
                )
            )
        else:
            text += " VALUES (%s)" % \
                ', '.join([c[1] for c in crud_params])

        if self.returning and not self.returning_precedes_values:
            text += " " + returning_clause

        self.stack.pop(-1)

        return text
Exemple #11
0
    def visit_insert(self, insert_stmt, **kw):
        self.stack.append({
            'correlate_froms': set(),
            'asfrom_froms': set(),
            'selectable': insert_stmt
        })

        self.isinsert = True
        crud_params = crud._get_crud_params(self, insert_stmt, **kw)

        if not crud_params and \
                not self.dialect.supports_default_values and \
                not self.dialect.supports_empty_insert:
            raise exc.CompileError("The '%s' dialect with current database "
                                   "version settings does not support empty "
                                   "inserts." % self.dialect.name)

        if insert_stmt._has_multi_parameters:
            if not self.dialect.supports_multivalues_insert:
                raise exc.CompileError(
                    "The '%s' dialect with current database "
                    "version settings does not support "
                    "in-place multirow inserts." % self.dialect.name)
            crud_params_single = crud_params[0]
        else:
            crud_params_single = crud_params

        preparer = self.preparer
        supports_default_values = self.dialect.supports_default_values

        jsn = {"command": "advanced/insert"}

        if insert_stmt._prefixes:
            text += self._generate_prefixes(insert_stmt, insert_stmt._prefixes,
                                            **kw)

        # table_text = preparer.format_table(insert_stmt.table)
        table_text = insert_stmt.table._compiler_dispatch(self,
                                                          asfrom=True,
                                                          iscrud=True)

        if insert_stmt._hints:
            dialect_hints = dict([
                (table, hint_text)
                for (table, dialect), hint_text in insert_stmt._hints.items()
                if dialect in ('*', self.dialect.name)
            ])
            if insert_stmt.table in dialect_hints:
                table_text = self.format_from_hint_text(
                    table_text, insert_stmt.table,
                    dialect_hints[insert_stmt.table], True)

        jsn['table'] = table_text['table']

        jsn['schema'] = table_text.get('schema', DEFAULT_SCHEMA)

        if crud_params_single or not supports_default_values:
            jsn["fields"] = [
                preparer.format_column(c[0]) for c in crud_params_single
            ]
        if self.returning or insert_stmt._returning:
            self.returning = self.returning or insert_stmt._returning
            returning_clause = self.returning_clause(insert_stmt,
                                                     self.returning)

            if self.returning_precedes_values:
                jsn["returning_insert"] = returning_clause

        if insert_stmt.select is not None:
            jsn['values'] = self.process(self._insert_from_select, **kw)
            jsn['method'] = 'select'
        elif not crud_params and supports_default_values:
            jsn['values'] = " DEFAULT VALUES"
        elif insert_stmt._has_multi_parameters:
            jsn['values'] = [[c[1] for c in crud_param_set]
                             for crud_param_set in crud_params]
        else:
            jsn['values'] = [[c[1] for c in crud_params]]

        if self.returning and not self.returning_precedes_values:
            jsn["returning"] = returning_clause

        return jsn
Exemple #12
0
    def visit_insert(self, insert_stmt, **kw):
        self.stack.append(
            {'correlate_froms': set(),
             'asfrom_froms': set(),
             'selectable': insert_stmt})

        self.isinsert = True
        crud_params = crud._get_crud_params(self, insert_stmt, **kw)

        if not crud_params and \
                not self.dialect.supports_default_values and \
                not self.dialect.supports_empty_insert:
            raise exc.CompileError("The '%s' dialect with current database "
                                   "version settings does not support empty "
                                   "inserts." %
                                   self.dialect.name)

        if insert_stmt._has_multi_parameters:
            if not self.dialect.supports_multivalues_insert:
                raise exc.CompileError(
                    "The '%s' dialect with current database "
                    "version settings does not support "
                    "in-place multirow inserts." %
                    self.dialect.name)
            crud_params_single = crud_params[0]
        else:
            crud_params_single = crud_params

        preparer = self.preparer
        supports_default_values = self.dialect.supports_default_values

        jsn = {"command": "advanced/insert"}

        if insert_stmt._prefixes:
            text += self._generate_prefixes(insert_stmt,
                                            insert_stmt._prefixes, **kw)

        # table_text = preparer.format_table(insert_stmt.table)
        table_text = insert_stmt.table._compiler_dispatch(
            self, asfrom=True, iscrud=True)

        if insert_stmt._hints:
            dialect_hints = dict([
                                     (table, hint_text)
                                     for (table, dialect), hint_text in
                                     insert_stmt._hints.items()
                                     if dialect in ('*', self.dialect.name)
                                     ])
            if insert_stmt.table in dialect_hints:
                table_text = self.format_from_hint_text(
                    table_text,
                    insert_stmt.table,
                    dialect_hints[insert_stmt.table],
                    True
                )

        jsn['table'] = table_text['table']

        jsn['schema'] = table_text.get('schema', DEFAULT_SCHEMA)

        if crud_params_single or not supports_default_values:
            jsn["fields"] = [preparer.format_column(c[0])
                             for c in crud_params_single]
        if self.returning or insert_stmt._returning:
            self.returning = self.returning or insert_stmt._returning
            returning_clause = self.returning_clause(
                insert_stmt, self.returning)

            if self.returning_precedes_values:
                jsn["returning_insert"] = returning_clause

        if insert_stmt.select is not None:
            jsn['values'] = self.process(self._insert_from_select, **kw)
            jsn['method'] = 'select'
        elif not crud_params and supports_default_values:
            jsn['values'] = " DEFAULT VALUES"
        elif insert_stmt._has_multi_parameters:
            jsn['values'] = [[c[1] for c in crud_param_set]
                             for crud_param_set in crud_params]
        else:
            jsn['values'] = [[c[1] for c in crud_params]]

        if self.returning and not self.returning_precedes_values:
            jsn["returning"] = returning_clause

        return jsn