Common Lisp Package: CLSQL-HELPER

README:

CLSQL-Helper

A library providing a clutch of utilities to make working with clsql easier

  • single interface functions that make a best effort to read/write a date in (m)any formats
  • convert-to-clsql-date(time) and
  • simplified sql-expression interface
  • clsql-or(s), clsql-and(s) and clsql-exp which make building where expressions easier
  • clsql-mop help, find the primary keys of an object and query / test equality with these
  • A basic sql pretty printer, so that the code printed from log-database-query , which uses the clsql printer and allows easily recording queries executing on a given connection
  • coersion to/from clsql data types an value

Future

  • My company Acceleration.net is a significant contributer to clsql so I hope that some of these changes can be moved upstream eventually

Authors

;; Copyright (c) 2011 Russ Tyndall , Acceleration.net http://www.acceleration.net ;; All rights reserved. ;; ;; Redistribution and use in source and binary forms, with or without ;; modification, are permitted provided that the following conditions are ;; met: ;; ;; - Redistributions of source code must retain the above copyright ;; notice, this list of conditions and the following disclaimer. ;; ;; - Redistributions in binary form must reproduce the above copyright ;; notice, this list of conditions and the following disclaimer in the ;; documentation and/or other materials provided with the distribution. ;; ;; THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS ;; "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT ;; LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR ;; A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT ;; OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, ;; SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT ;; LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, ;; DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY ;; THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT ;; (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE ;; OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

FUNCTION

Public

AFTER-DAY-OF-MONTH (DATE DAY)

Are we past a specific day of the month

BEFORE-DAY-OF-MONTH (DATE DAY)

Are we past a specific day of the month

CLSQL-AND (&REST CLAUSES)

returns a CLSQL:SQL-AND for all non-nil clauses, no nil if there are no non-nil clauses

CLSQL-DATE/TIMES->UTIME (OBJ &OPTIONAL (TIMEZONE 0))

obj is either a wall-time or a date in local time. Converts to UTC and returns a utime. pass timezone nil to skip UTC conversion. if you are looking for the other it is clsql-sys:utime->time

CLSQL-DATETIME-EQUAL (X Y)

Tries to handle full datetime equality reguardless of the format (string datestamp, date, datetime, utime)

CLSQL-GET-VAL (SQL &KEY LOG)

alias of get-scalar

CLSQL-OR (&REST CLAUSES)

returns a CLSQL:SQL-AND for all non-nil clauses, no nil if there are no non-nil clauses

CLSQL-ORS (CLAUSES)

returns a CLSQL:SQL-AND for all non-nil clauses, no nil if there are no non-nil clauses

CURRENT-SQL-DATE

current date

CURRENT-SQL-TIME

current date and time

DATE-DIFF (D1 D2)

Gets the difference in days between two dates returns a negative number to indicate that d1 is after d2 returns a positive number to indicate that d2 is after d1

DAYS-IN-MONTH (&OPTIONAL (DATE (CURRENT-SQL-DATE)))

Return the number of days in the month of the date passed in

DB-OBJS (CLASS CMD &KEY PARAMS (MAKE-INSTANCES-FN #'MAKE-INSTANCES) LOG (COLUMN-MUNGER #'UNDERSCORES->LISP-SYMBOL))

retrieve objects of type class from the database using db-query

DB-QUERY (CMD &REST KEYS &KEY PARAMS LOG &ALLOW-OTHER-KEYS)

runs a db query sets :flatp to t if params are provided we build a command object (backend better support this)

DB-QUERY-PLISTS (CMD &REST KEYS &KEY PARAMS LOG)

Returns a list of plists that correspond to the query results

DB-SCALAR (CMD &REST KEYS &KEY PARAMS LOG)

Query a single value from the database

DB-SELECT (&REST SELECT-ARGS &AUX LOG)

Runs a clsql:select defaulting to :flatp T unnests any lists as part of the select list

DB-SELECT-SCALAR (&REST SELECT-ARGS)

query a single row from the database using clsql:select

DB-STRING (S &KEY (PREFIX ) (POSTFIX ) (WRAPPER ))

trims, nullifies, escapes and wraps in single quotes so that the string is ready to be spliced into a query (eg: with cl-interpol). returns 'foo' or NIL.

FIRST-OF-THE-MONTH (&OPTIONAL (DATE (CURRENT-SQL-DATE)))

returns the first of the month for the month/year of the date passed in

FIRST-OF-THE-MONTH? (&OPTIONAL (DATE (CURRENT-SQL-DATE)))

returns whether or not the date passed in is the first of the month

FORMAT-VALUE-FOR-DATABASE (D &OPTIONAL STREAM)

prints a correctly sql escaped value for postgres

LAST-OF-THE-MONTH (START-DATE &AUX (MONTH (DATE-MONTH START-DATE)))

Returns the first of next month eg: 2/14/2012->2/29/2012

MAKE-INSTANCE-PLIST (COLUMNS ROW)

Creates a plist intended to be passed to make-instance

MAKE-INSTANCES (CLASS COLUMNS ROWS)

From N rows and some column-names make N instances of class filling data from rows using make instance

MAKE-INSTANCES-SETTING-ACCESS (CLASS COLUMNS ROWS)

From N rows and column-name make N instances of class filling data from rows by creating instances and setting through access lib

MAKE-INSTANCES-SETTING-ACCESSORS (CLASS COLUMNS ROWS)

From N rows and column-name make N instances of class filling data from rows by creating instances and setting existing accessor functions

MONTH-DAY-STRING (D)

prints dates as January 3

MONTH-STRING (D)

Converts the date to the full name, January, February,etc

PRIMARY-KEY-WHERE-CLAUSES (OBJ &AUX (SLOTS (PRIMARY-KEY-SLOTS OBJ)))

Generates a where clause based on all of the primary keys of the object ex: pk1 = val1 and pk2 = val2 ...

SAVE! (DB-OBJ &KEY LOG)

saves the given object, then returns the saved object

TABLE-NAME-EXP (TABLE-NAME)

based on what is passed in, tries to figure out what the table name is

WITH-DATABASE-FUNCTION (FN CONNECT-SETTINGS &KEY POST-CONNECT-FN LOG)

opens a database connection with the given settings, and runs the function. connect-settings: a plist of connection info for clsql, also supports :post-connect-fn, a function to run after opening the connection post-connect-fn: a function of no arguments to run after opening the connection

Undocumented

CLSQL-ANDS (CLAUSES)

CLSQL-COLUMN-NAME (COLUMN &OPTIONAL TABLE)

CLSQL-EXP (S)

COERCE-VALUE-TO-DB-TYPE (VAL DB-TYPE)

COLUMN-NAME-STRING (COLUMN)

DAY-BEFORE (&OPTIONAL (DATE (CURRENT-SQL-DATE)))

DB-EXEC (CMD &KEY PARAMS LOG)

DB-OBJS-SELECT (CLASS COLUMNS &KEY SELECT-ARGS (MAKE-INSTANCES-FN #'MAKE-INSTANCES))

DB-TYPE-FROM-LISP-TYPE (TYPE &KEY (LENGTH 64) (SCALE 4) (PG-DEFAULT-INT-TYPE int8))

FIND-DIRTY-TEST (O SLOT-NAME)

FIRST-OF-NEXT-MONTH (&OPTIONAL (DATE (CURRENT-SQL-DATE)))

JOIN-SLOT? (SLOT-DEF)

LAST-MONTH (&OPTIONAL (DATE (CURRENT-SQL-DATE)))

NEXT-MONTH (&OPTIONAL (DATE (CURRENT-SQL-DATE)))

PRETTY-PRINT-SQL (SQL-COMMAND)

PRIMARY-KEY-SLOT-NAMES (OBJ)

TABLE-NAME-STRING (TABLE-NAME)

Private

%WITH-A-DATABASE (BODY-FN &KEY ((CONNECTION-SETTINGS *CONNECTION-SETTINGS*) *CONNECTION-SETTINGS*) POST-CONNECT-FN LOG)

If a database connection exists and it matches the passed in settings or the passed in settings are null, use it!, otherwise aquire a new database connection

CLAUSE-FOR-FROM-DATE-1 (&KEY ((FOR DATE)) ((FROM-DATE START)) ((BY STEP) '+A-DAY+) GENERATE)

iterates through dates from start by step HAS NO END CHECK eg an infinite loop unless you prevent it

CLAUSE-FOR-FROM-DATE-THRU-5 (&KEY ((FOR DATE)) ((FROM-DATE START)) ((THRU END)) ((BY STEP) '+A-DAY+) GENERATE)

iterates through dates from start to end inclusively

CLAUSE-FOR-FROM-DATE-TO-3 (&KEY ((FOR DATE)) ((FROM-DATE START)) ((TO END)) ((BY STEP) '+A-DAY+) GENERATE)

iterates through dates from start to end exclusive of end

CLAUSE-FOR-FROM-DATETIME-2 (&KEY ((FOR DATE)) ((FROM-DATETIME START)) ((BY STEP) '+A-DAY+) GENERATE)

iterates through dates from start by step HAS NO END CHECK eg an infinite loop unless you prevent it

CLAUSE-FOR-FROM-DATETIME-THRU-6 (&KEY ((FOR DATE)) ((FROM-DATETIME START)) ((THRU END)) ((BY STEP) '+A-DAY+) GENERATE)

iterates through dates from start to end inclusively

CLAUSE-FOR-FROM-DATETIME-TO-4 (&KEY ((FOR DATE)) ((FROM-DATETIME START)) ((TO END)) ((BY STEP) '(MAKE-DURATION DAY 1)) GENERATE)

iterates through dates from start to end exclusive of end

ENSURE-MIGRATION-TABLE

if it doesn't exist, create the migration table

MAKE-INSTANCES-SETTING-SLOT-VALUES (CLASS COLUMNS ROWS)

From N rows and column-name make N instances of class filling data from rows by creating instances and setting slot-values

MIGRATION-DONE-P (HASH)

returns non-nil if this hashed migration has been run

SAME-DATABASE-CONNECTION? (C1 C2)

determines whether or not two connections are the same by comparing their connection spec (eg '(server db user pass)) handles connection-settings, connection-specs and clsql:database

SQL-HASH (SQL-STATEMENT)

returns a hashed form of the query, as a string

TRIM-AND-NULLIFY (S)

trims the whitespace from a string returning nil if trimming produces an empty string or the string 'nil'

Undocumented

%CALL-PERHAPS-LOGGED (FN LOG &OPTIONAL (DATABASE *DEFAULT-DATABASE*))

%CLSQL-SUBCLAUSES (CLAUSES)

%COMMAND-IF-NEEDED (CMD PARAMS)

%DATE-TEST-FN-FINDER (START END &KEY DATE? TO?)

%DATE-TEST-FORM (DATE END-VAR TEST)

%DIRTY-BEFORE (NEW CLASS OBJECT SLOT &AUX (NAME (SLOT-DEFINITION-NAME SLOT)))

%LOG-FN-PERHAPS (LOG)

COERCE-CONNECTION-SPEC (C)

DEFAULT-LOG-FN (MSG)

ERROR-DB-OBJ-HAS-NO-KEYS (OBJ)

LOG-DATABASE-COMMAND-FN (BODY-FN &KEY LOG-FN (DATABASE *DEFAULT-DATABASE*))

MAKE-DIRTY-SLOT (NAME OLD NEW)

MAYBE-CALL (IT)

PRIMARY-KEY-SLOTS (OBJ)

RESET-DIRTY (O)

TRIM-WHITESPACE (S)

WARN-DB-OBJ-HAS-NO-KEYS (OBJ)

MACRO

Public

LOG-DATABASE-COMMAND ((&OPTIONAL LOG-FN-NAME (DATABASE '*DEFAULT-DATABASE*)) &BODY BODY)

MUST BE Inside a database connection, creates a lexical scope in which all sql commands executed on this connection are logged to a specific logger tries to format such that it will be readable in the log log-fn-name is a function/macro name that will be called with a string/array as (log-fn-name stuff)

WITH-A-DATABASE ((&OPTIONAL (CONNECTION-SETTINGS '*CONNECTION-SETTINGS*) &KEY POST-CONNECT-FN LOG) &BODY BODY)

If a database connection exists and it matches the passed in settings or the passed in settings are null, use it!, otherwise aquire a new database connection

WITH-COMMAND-LOGGING ((&KEY (DATABASE '*DEFAULT-DATABASE*)) &BODY BODY)

record commands to the *command-log-stream* for the duration of body then restore its previous binding

WITH-COMMAND-LOGGING-TO-STRING ((&KEY STRING (DATABASE '*DEFAULT-DATABASE*)) &BODY BODY)

Log the sql commands of the body to a string

WITH-DATABASE ((&OPTIONAL (CONNECTION-SETTINGS *CONNECTION-SETTINGS*) &KEY POST-CONNECT-FN LOG) &BODY BODY)

opens a database connection and executes the body connect-settings: a plist of connection info for clsql, also supports :post-connect-fn, a function to run after opening the connection post-connect-fn: a function of no arguments to run after opening the connection

Undocumented

CONVERT-TO-CLSQL-DATE! (&REST PLACES)

CONVERT-TO-CLSQL-DATETIME! (&REST PLACES)

Private

Undocumented

DEFMETHOD-WHEN-DEFINED (NAME ARGS &BODY BODY)

GENERIC-FUNCTION

Public

BY-COL (CLASS COLUMN COLVALUE)

fetchs the first row for the given class by id

BY-ID (CLASS ID &OPTIONAL COLNAME)

Fetchs the first row for the given class by id

CONVERT-TO-CLSQL-DATE (VAL)

Convert your value into a clsql:date structure

CONVERT-TO-CLSQL-DATETIME (VAL)

Converts a string timestamp into a clsql date time object Makes every possible effort to understand your date that will invariably be in some format it wont understand.

DATE-DAY (D)

Given an object that encodes a date, return the day component

DATE-MONTH (D)

Given an object that encodes a date, return the month component

DATE-YEAR (D)

Given an object that encodes a date, return the year component

DB-EQL (X Y &KEY TEST (TEST #'EQUALP))

Tries to determine if the objects are of the same type and have the same primary key values Many times objects which pass new-objectp are db-eql ,but once saved are no longer db-eql (due to using serial pkey)

ISO8601-DATESTAMP (D)

Given an object that encodes a date return an iso8601-datestamp representation of it

ISO8601-TIMESTAMP (D)

CLSQL has a function (I wrote) to do this, but I wanted more flexibility in output so that I could use this in more situations clsql:iso-timestamp is used only to write to database backends, so a very strict ISO is fine

NEW-OBJECT-P (OBJ)

Checks that primary keys have values and that the object with those primary key values exists in the database

SLOT-DIRTY? (OBJECT SLOT-NAME &KEY ALL? (ALL? NIL))

determines whether or not a slot on a given object is dirty slot-name can be a list and all? determines if we want to not if all of them or dirty or if any of them are dirty

Undocumented

MIGRATIONS (&REST SQL-STATEMENTS)

Private

%COERCE-TO-STEP (STEP)

Converts something in an iterate date iteration clause BY section to a valid clsql:duration if it can

COERCE-TO-DB-STRING-REPRESENTATION (S)

Convert an object into an unquoted string that the database understands mostly used to do the coercion in db-string

DB-OBJECT-KEY-SLOTS (O)

returns the primarky key-slots of the given object

MIGRATE (THING)

perform the migration, returns the number of statments executed

RELAXED-PARSE-FLOAT (STR &KEY TYPE (TYPE 'DOUBLE-FLOAT))

trys to read a value we hope to be a floating point number returns nil on failure The goal is to allow reading strings with spaces commas and dollar signs in them correctly

Undocumented

MESSAGE (CONDITION)

SETFMESSAGE (NEW-VALUE CONDITION)

OBJ (CONDITION)

SETFOBJ (NEW-VALUE CONDITION)

SLOT-ACCESSOR

Public

Undocumented

DIRTY-SLOTS (OBJECT)

SETFDIRTY-SLOTS (NEW-VALUE OBJECT)

DIRTY-TEST (OBJECT)

SETFDIRTY-TEST (NEW-VALUE OBJECT)

Private

Undocumented

NEW-VALUE (OBJECT)

SETFNEW-VALUE (NEW-VALUE OBJECT)

OLD-VALUE (OBJECT)

SETFOLD-VALUE (NEW-VALUE OBJECT)

SLOT-NAME (OBJECT)

SETFSLOT-NAME (NEW-VALUE OBJECT)

VARIABLE

Public

*COMMAND-LOG-STREAM*

a stream that we will record sql commands to in the body of

*MIGRATION-TABLE-NAME*

the table name to use for migrations

Undocumented

*CONNECTION-SETTINGS*

+A-DAY+

+A-MINUTE+

+A-MONTH+

+A-NEGATIVE-DAY+

+A-NEGATIVE-HOUR+

+A-NEGATIVE-MINUTE+

+A-NEGATIVE-MONTH+

+A-NEGATIVE-SECOND+

+A-NEGATIVE-YEAR+

+A-SECOND+

+A-YEAR+

+AN-HOUR+

Private

*RECORD-THIS-DIRTY-SLOT*

Should we record this slot as dirty?

Undocumented

*CLSQL-CODEBASE-LOADED*

*DEFAULT-LOG-FN*

*ISO8601-DATE-SEPARATOR*

*ISO8601-DATE-TIME-SEPARATOR*

*ISO8601-MICROSECONDS*

*ISO8601-TIME-SEPARATOR*

*ISO8601-TIMEZONE*

+COMMON-WHITE-SPACE-TRIMBAG+

+DATE-SEP+

+DATE-TIME-REGEX+

+ISO-8601-ISH-REGEX+

+ISO-8601-ISH-REGEX-STRING+

CLASS

Public

Undocumented

DIRTY-DB-SLOTS-MIXIN

Private

Undocumented

DIRTY-SLOT

CONDITION

Public

Undocumented

SAVE-FAILED

Private

Undocumented

DB-OBJECT-HAS-NO-KEYS