actionho-commons.db-queries — org.clojars.actionho/actionho-commons 0.10-SNAPSHOT

batch-update-skeleton clj

(batch-update-skeleton db-spec
                       temp-tables-columns-sql-str
                       update-sql-str
                       row-vec-seq)

Inputs: [db-spec temp-tables-columns-sql-str update-sql-str row-vec-seq :- [(ps/pred (fn* [p1__16661#] (vector? p1__16661#)))]]

Batch update a bunch of rows in a table. Works via the postgres COPY command. temp-tables-columns-sql-str is sql for create table columns, e.g. id BIGSERIAL PRIMARY KEY, bookmaker_ref_num BIGINT, bookmaker_ref_num_2 BIGINT

update-sql-str is sql for updating target table from source s once copy has been finished, e.g. UPDATE odds_infos oi SET bookmaker_ref_num = s.bookmaker_ref_num, bookmaker_ref_num_2 = s.bookmaker_ref_num_2 FROM source s WHERE oi.id = s.id

row-vec-seq is actual update data, in form of a vec of columns (like csv file), columns order is same as in temp-tables-columns-sql-str.

chunked-insert-rows clj

(chunked-insert-rows db-spec table-name vec-of-clj-maps)

A helper method for when needing to insert many rows as once.

chunked-select-rows-in clj

(chunked-select-rows-in db-spec
                        table-name
                        in-columns-vec
                        where-in-column-keyword
                        ids-seq)

For getting rows with the IN operator, but where the seq of ids to get is very large

delete-rows clj

(delete-rows db-spec table-name where-conds-vec)

Returns number of new records deleted

end-parens-regex clj

get-current-max-id clj

(get-current-max-id db-spec table-name)

Mostly for knowing what the id of the record inserted was, assumes id is a column that auto-increments with serial. From http://stackoverflow.com/questions/16936135/postgresql-select-from-max-id

get-int clj

(get-int in-seq)

For query results that should just contain an int as the info, extract that

get-numeric-column-name clj

(get-numeric-column-name column-name-keyword)

Needed for columns of REAL type as otherwise comparison can be wrong if not cast to numeric. Notes takes in keyword, not str. Needs to be converted back to keyword at end for honeysql's automatic handling of column-name-keywords

get-posix-search-where-cond clj

(get-posix-search-where-cond column-name-str regex-str)

get-rows-count clj

(get-rows-count db-spec table-name-str where-conds-vec)

get-single-record-from-sorted clj

(get-single-record-from-sorted db-spec
                               table-name
                               order-by-vecs-vec
                               where-conds-vec)

Returns a single record-map, first as sorted by order-by-vecs-vec, which is in honey-sql format of [[:column_name_1 :asc] [:column_name_2 :desc]], nil if nothing found. Table-name is string, where-conds-vec is where-conds-vec is a seq of honeysql where cond vecs.

handle-select-single-row-multiple-found clj

(handle-select-single-row-multiple-found where-conds-vec
                                         raw-result-seq
                                         multiple-found-keyword)

Inputs: [where-conds-vec raw-result-seq multiple-found-keyword :- multiple-found-keywords-type]

When multiple results found, handle as dictated by keyword, including any logging/notification for further inspection grep command to use to look through log files when in .../logs to find duplicate names to manually merge together grep "single record search: multiple results found for:" *

insert-rows clj

(insert-rows db-spec table-name vec-of-clj-maps)

Validates that number of clj maps sent into this method is number of records inserted by db. Expects clj maps to have keyword keys. Returns seq of record-maps inserted. This is opposed to the default return value of number of rows inserted.

multiple-found-keywords-type clj

order-by-helper clj

(order-by-helper in-hsql-parts hsql-order-by-vecs-vec)

process-rows-chunk clj

(process-rows-chunk db-spec
                    table-name
                    column-keywords-vec
                    where-conds-vec
                    chunk-size
                    processing-fn
                    n)

Meant to be used in a (->>) threading macro, where the table has too many rows to fit into memory, so each chunk of rows must be fully processed, then merged/reduced together.

processing-fn takes in one arg, rms-seq

select-rows clj

(select-rows db-spec table-name where-conds-vec)

(select-rows db-spec table-name column-keywords-vec where-conds-vec)

(select-rows db-spec table-name column-keywords-vec where-conds-vec limit-count)

(select-rows db-spec
             table-name
             column-keywords-vec
             where-conds-vec
             limit-count
             offset-num)

Returns lazy sequence of clj maps. where-conds-vec is a seq of honeysql where cond vecs. For more sophisticated selects that need joins or something, write honeysql right then and there, importing all hsql stuff as needed. No ability to select columns for the time being, can use custom hsql queries for that.

select-single-row clj

(select-single-row db-spec table-name where-conds-vec multiple-found-keyword)

(select-single-row db-spec
                   table-name
                   column-keywords-vec
                   where-conds-vec
                   multiple-found-keyword)

Inputs: ([db-spec table-name where-conds-vec multiple-found-keyword] [db-spec table-name column-keywords-vec where-conds-vec multiple-found-keyword :- (ps/pred (fn* [p1__16587#] (in? select-single-row-multiple-found-keywords-vec p1__16587#)))])

Checks that only one record is returned, and converts to to clj map with keyword keys. where-conds-vec is a seq of honeysql where cond vecs. multiple-found-keyword is to handle what happens when multiple results are found :nil - nil :first - if multiple give first one by ascending id :first-notify - same as :first but send email to myself :seq - return seq of records, sorted by ascending id, instead of first one If no rows found, returns nil. Better than an assert fail, as it's rarely a non-recoverable error, but normal condition that should be accounted for.

select-single-row-multiple-found-keywords-vec clj

send-sql-to-db clj

(send-sql-to-db sql-strs-vec cmd-type db-spec)

Inputs: [sql-strs-vec cmd-type :- (ps/enum :query :execute) db-spec]

start-parens-regex clj

update-rows clj

(update-rows db-spec table-name where-conds-vec update-map)

Returns seq of record-maps updated. where-conds-vec is a seq of honeysql where cond vecs. update-map is clj map with keyword keys of column/value to update to when record meets where cond.

where-conds-helper clj

(where-conds-helper in-hsql-parts hsql-where-vec)

For use within a -> macro where the honeysql 'where' helper can be may to work on a seq via 'apply'. honeysql's where fn is flexible enough for both [] [] [] and [:or [] [] []]