As anyone following this guide to cl-perec has undoubtedly noticed, while I've spent considerable time exploring how to put data in the database, I've not spent any on getting all that precious data back out. Today, I shall remedy that.

Let us suppose your PHB wanders by and asks you what the title is of the report located in the file "/share/tps-reports/Testing". Not to worry, you can select a single instance using cl-perec:select-instance.¹

select-instance has a fairly simple calling syntax.

(select-instance (/var-name/ /class-of-instance/) (where [condition-form]))

For example, we can select our tps report by file name with:

(cl-perec:with-transaction (cl-perec:select-instance (report tps-report) (cl-perec:where (equal (report-path-of report) #p"/share/tps-reports/Testing"))))

Note that accessor methods on the instance variable are used to pick the SQL column, and an instance of the object to which we want to compare that column is used for our value. This is because the value-to-be-compared goes through all the lisp→sql conversion machinery.²

Of particular interest is the where clause, which is proper CL³. The upshot to this is that it's very easy to select based upon an arbitrary predicate. The downside is that, if cl-perec can't figure out how to translate your where clause, it will filter the parts it doesn't understand in Lisp. Failure to remember that may result in accidentally hosing your performance. #'cl-perec:start-sql-recording is very useful here for checking if things are being converted to SQL in the way you expect.

Simple, standard functions, such as 'equal, get translated to the obvious SQL, as do boolean expressions involving AND, NOT, and OR. Notably excepted, however, string= and string-equal are not translated into SQL.

As you might expect, user-defined equality predicates are not converted to SQL. As usual, however, cl-perec offers unexported functionality to extend itself.

Say, for instance, you want to be able to query report-path using wild pathnames, to fetch all the tps-reports in a certain directory, like so:

(cl-perec:with-transaction (cl-perec:select-instances (r tps-report) (cl-perec:where (pathname-match-p (report-path-of r) #p"/share/tps-reports/*"))))

A legitimate use case if I ever saw one! To make this work in SQL, we need to convert it to use the LIKE operator, or possibly a regular expression. Rummaging around in query/mapping.lisp, we find the definer for like which points us in a direction. Awesome!

A little trial and error gets us to a very rough start.

(cl-def:def (cl-perec::query-function :lisp-args t) pathname-match-p (path wild) "Convert a comparison to a :wild pathname into SQL. Does not support all possible :wild pathnames." (declare (cl-perec::persistent-type (cl-perec::forall (a) (function (a a) boolean)))) (let* ((wild (cl-perec::value-of wild)) (root (make-pathname :name nil :type nil :defaults wild)) (name (if (wild-pathname-p wild :name) "%" (pathname-name wild))) (type (if (wild-pathname-p wild :type) "%" (pathname-type wild)))) (cl-perec::sql-like :string (cl-perec::syntax-to-sql path) :pattern (format nil "~a~a~@[.~a~]" root name type) :case-sensitive-p t)))

path and wild are passed in as cl-perec 'syntax nodes'. syntax-to-sql converts a syntax node to SQL, and value-of, assuming a literal value was used, gets that literal value. Then it's just a matter of converting :wilds into SQL LIKE's wildcard, the percent sign (%).

Regrettably, this is pretty fragile. It only works if the wild pathname is passed in as a literal value; neither let-bound variables, special variables, nor #'make-pathname will work. (Not to mention the flaws in the matching itself!) Let's see what we can do about that.

(cl-def:def (cl-perec::query-function :lisp-args t) pathname-match-p (path wild) "Convert a comparison to a :wild pathname into SQL. Does not support all possible :wild pathnames." (declare (cl-perec::persistent-type (cl-perec::forall (a) (function (a a) boolean)))) (cl-perec::sql-like :string (cl-perec::syntax-to-sql path) :pattern (cl-perec::syntax-to-sql (cl-perec::make-special-form :operator 'let* :operands `(((wild ,wild) (root (make-pathname :name nil :type nil :defaults wild)) (name (if (wild-pathname-p wild :name) "%" (pathname-name wild))) (type (if (wild-pathname-p wild :type) "%" (pathname-type wild)))) (format nil "~a~a~@[.~a~]" root name type)))) :case-sensitive-p t))

This is a bit messy—because the arguments we're given have already been converted into an abstract syntax tree, what would otherwise be fairly straightforward code must be converted into an AST as well⁴—but it works. make-special-form creates an object that eventually gets evaluated, and it somehow all magically works out. The in-sql matching still isn't perfect, but it's pretty useable. Give it a try!

(cl-perec:with-transaction (cl-perec:select-instances (r tps-report) (cl-perec:where (pathname-match-p (report-path-of r) #p"/share/tps-reports/*"))))

(cl-perec:with-transaction (cl-perec:select-instances (r tps-report) (cl-perec:where (pathname-match-p (report-path-of r) (make-pathname :directory '(:absolute "share" "tps-reports") :name :wild)))))

(let ((w (make-pathname :directory '(:absolute "share" "tps-reports") :name :wild))) (cl-perec:with-transaction (cl-perec:select-instances (r tps-report) (cl-perec:where (pathname-match-p (report-path-of r) w)))))

(defvar *wild* (make-pathname :directory '(:absolute "share" "tps-reports") :name :wild)) (cl-perec:select-instances (r tps-report) (cl-perec:where (pathname-match-p (report-path-of r) *wild*)))

All should produce SQL that looks something like this:

BEGIN $1 = /share/tps-reports/% as TEXT SELECT r._oid, r.report_title, r.report_text, r.report_path FROM tps_report_ap r WHERE ((r.report_path LIKE $1::TEXT)) COMMIT

Pathname matching that handles directories with :wild and :wild-inferiors properly is left as an exercise to the reader, but I'll leave you with a few hints: look into the re-like query-function, which provides you with access to the database server's regular expression engine; and ignore the fact you can't portably rely on the format of namestrings.

Footnotes