class Janko::Upsert

dba.stackexchange.com/questions/13468/most-idiomatic-way-to-implement-upsert-in-postgresql-nowadays stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291 stackoverflow.com/questions/17575489/postgresql-cte-upsert-returning-modified-rows

Public Instance Methods

cleanup() click to toggle source
# File lib/janko/upsert.rb, line 43
def cleanup
    return unless prepared?
    connection.exec("DEALLOCATE \"#{@prepared}\"")
    @prepared = nil
    self
end
default_state() click to toggle source
# File lib/janko/upsert.rb, line 13
def default_state
    { collector: MergeResult.new }
end
prepare() click to toggle source
# File lib/janko/upsert.rb, line 21
def prepare
    return(self) if prepared?
    @prepared = "upsert_#{SecureRandom.hex(8)}"
    connection.prepare(@prepared, query)
    self
end
process() click to toggle source
# File lib/janko/upsert.rb, line 35
def process
    raise(RuntimeError, "Can't #process without from_table") \
        unless read_from_table?
    result.clear
    collect_result(exec_query)
    self
end
push(*values) click to toggle source
# File lib/janko/upsert.rb, line 28
def push(*values)
    raise(RuntimeError, "Can't #push when reading from a table.") \
        if read_from_table?
    collect_result(exec_query(columns.pack(*values)))
    self
end
result() click to toggle source
# File lib/janko/upsert.rb, line 17
def result
    collector
end

Private Instance Methods

collect_result(tuples) click to toggle source
# File lib/janko/upsert.rb, line 56
def collect_result(tuples)
    return(self) if (tuples.count == 0)
    tuples.each { |t| result.push(t.delete(result_type_column), t) }
end
columns_to_binds_with_types() click to toggle source
# File lib/janko/upsert.rb, line 94
def columns_to_binds_with_types
    columns.to_typecast_binds
end
exec_query(*binds) click to toggle source
# File lib/janko/upsert.rb, line 69
def exec_query(*binds)
    return(connection.exec(query, *binds)) unless prepared?
    connection.exec_prepared(@prepared, *binds)
end
insert_columns() click to toggle source
# File lib/janko/upsert.rb, line 82
def insert_columns
    @insert_columns ||= columns.tagged(:insert)
end
insert_columns_with_defaults() click to toggle source
# File lib/janko/upsert.rb, line 90
def insert_columns_with_defaults
    insert_columns.to_list_with_defaults
end
insert_fragment() click to toggle source
# File lib/janko/upsert.rb, line 139
        def insert_fragment
            return(<<-END)
                INSERT INTO #{table} (#{insert_columns.to_list})
                SELECT #{insert_columns_with_defaults} FROM #{source_table}
                WHERE NOT EXISTS (SELECT 1 FROM upsert
                    WHERE #{insert_key_clause})
            END
        end
insert_key_clause() click to toggle source
# File lib/janko/upsert.rb, line 106
def insert_key_clause
    key_columns.to_conditions("upsert", source_table)
end
key_columns() click to toggle source
# File lib/janko/upsert.rb, line 74
def key_columns
    @key_columns ||= columns.tagged(:key)
end
prepared?() click to toggle source
# File lib/janko/upsert.rb, line 65
def prepared?
    not @prepared.nil?
end
query() click to toggle source
# File lib/janko/upsert.rb, line 110
def query
    return(query_returning_nothing) if select_columns.empty?
    return(query_returning_all) if (returning == "all")
    return(query_returning_inserts) if (returning == "inserted")
    return(query_returning_updates) if (returning == "updated")
    query_returning_nothing
end
query_returning_all() click to toggle source
# File lib/janko/upsert.rb, line 180
        def query_returning_all
            return(<<-END)
                #{query_with_returnable_tuples}
                SELECT 'inserted'
                    AS #{result_type_column}, #{select_columns.to_list}
                    FROM inserted
                UNION ALL
                SELECT 'updated'
                    AS #{result_type_column}, #{select_columns.to_list}
                    FROM upsert
            END
        end
query_returning_inserts() click to toggle source
# File lib/janko/upsert.rb, line 162
        def query_returning_inserts
            return(<<-END)
                #{query_with_returnable_tuples}
                SELECT 'inserted'
                    AS #{result_type_column}, #{select_columns.to_list}
                    FROM inserted
            END
        end
query_returning_nothing() click to toggle source
# File lib/janko/upsert.rb, line 148
        def query_returning_nothing
            return(<<-END)
                WITH #{source_fragment} upsert AS (#{upsert_fragment})
                #{insert_fragment}
            END
        end
query_returning_updates() click to toggle source
# File lib/janko/upsert.rb, line 171
        def query_returning_updates
            return(<<-END)
                #{query_with_returnable_tuples}
                SELECT 'updated'
                    AS #{result_type_column}, #{select_columns.to_list}
                    FROM upsert
            END
        end
query_with_returnable_tuples() click to toggle source
# File lib/janko/upsert.rb, line 155
        def query_with_returnable_tuples
            return(<<-END)
                WITH #{source_fragment} upsert AS (#{upsert_fragment}), 
                inserted AS (#{insert_fragment} RETURNING #{table}.*)
            END
        end
read_from_table?() click to toggle source
# File lib/janko/upsert.rb, line 61
def read_from_table?
    not @from_table.nil?
end
result_type_column() click to toggle source
# File lib/janko/upsert.rb, line 52
def result_type_column
    "__type"
end
select_columns() click to toggle source
# File lib/janko/upsert.rb, line 86
def select_columns
    @select_columns ||= columns.tagged(:select)
end
source_fragment() click to toggle source
# File lib/janko/upsert.rb, line 122
        def source_fragment
            return if read_from_table?
            return(<<-END)
                #{source_table} (#{columns.to_list}) AS (VALUES
                    (#{columns_to_binds_with_types})),
            END
        end
source_table() click to toggle source
# File lib/janko/upsert.rb, line 118
def source_table
    @from_table || "upsert_data"
end
update_clause() click to toggle source
# File lib/janko/upsert.rb, line 98
def update_clause
    update_columns.to_setters("upsert_updates", source_table)
end
update_columns() click to toggle source
# File lib/janko/upsert.rb, line 78
def update_columns
    @update_columns ||= columns.tagged(:update).not_tagged(:key)
end
update_key_clause() click to toggle source
# File lib/janko/upsert.rb, line 102
def update_key_clause
    key_columns.to_conditions("upsert_updates", source_table)
end
upsert_fragment() click to toggle source
# File lib/janko/upsert.rb, line 130
        def upsert_fragment
            return(<<-END)
                UPDATE #{table} upsert_updates
                SET #{update_clause} FROM #{source_table}
                WHERE (#{update_key_clause})
                RETURNING upsert_updates.*
            END
        end