Forum OpenACS Development: duplicate queries in .tcl, .xql, -oracle.xql, -postgresql.xql

Just from experience (not having looked at the query parser code), the precedence seems to be:

-postgres.xql/-oracle.xql

.xql

.tcl

The queries in the .xql file are common to all databases, but can be overridden in a db-specific xql file.  For instance, the .xql file might have a query with a SQL92 outer join.  This would work fine with postgres but not with Oracle 8x, so it will appear in the oracle xql file, but not the postgres xql file.  As for queries in the tcl files, yes, they could be eliminated.  They're only used when no corresponding query exists in a .xql file.  I personally like having them inline, as it's easy to see what the query is supposed to do.  It also speeds up development greatly to keep everything in the tcl file, then split it out later with automated scripts.

I can only offer this kludge below, which binds C-c a to opening the corresponding xql file, C-c a the adp file and so on. Additions highly welcome.

;; define shortcuts to open the corresponding xql/tcl/whatever file to
;; the current open buffer.
(defun acs-find-file (fileext) "open acs buffer" (interactive "s")
  (find-file (concat (substring (buffer-file-name) 0 (string-match "(.tcl|.adp|-postgresql.xql|-oracle.xql|.xql)$" (buffer-file-name))) fileext)))


;; don't know how to bind functions with parameters to keystrokes, so
;; i have to define one function for each possible parameter value
(defun acs-find-file-adp () "" (interactive) (acs-find-file ".adp"))
(defun acs-find-file-tcl () "" (interactive) (acs-find-file ".tcl"))
(defun acs-find-file-postgresql () "" (interactive) (acs-find-file "-postgresql.xql"))
(defun acs-find-file-oracle () "" (interactive) (acs-find-file "-oracle.xql"))
(defun acs-find-file-xql () "" (interactive) (acs-find-file ".xql"))

(global-set-key "C-ca" 'acs-find-file-adp)
(global-set-key "C-ct" 'acs-find-file-tcl)
(global-set-key "C-cp" 'acs-find-file-postgresql)
(global-set-key "C-co" 'acs-find-file-oracle)
(global-set-key "C-cx" 'acs-find-file-xql)

I agree with both... It's much easier to write code using SQL right in the Tcl.  The second you extract those queries, though, the queries should be removed from the Tcl file.  I find that the general niceness of having a little bit of reminder code in the Tcl file to give me the gist of what's going on is totally outweighed by my propensity to start editing that query and then scratch my head for a few minutes (or more) when it does nothing to the app.  Luckily now conditioning has kicked in and I figure out the problem pretty quickly or manage to avoid it altogether, but it was a painful pavlovian experience that I wouldn't wish on anybody else.

Something like the following works reasonably well in xemacs (probably it also works in regular emacs):

(defun dcw-find-query ()
  "run grep on selection 
   Uses x-get-selection to get the grep string"
  (interactive "")
  (let* ((file-name 
          (concat (substring (buffer-file-name) 0 
                             (string-match ".tcl$" 
                                           (buffer-file-name))) "*.xql")))
    (grep (concat "grep -n "" (x-get-selection) "" " file-name))))

Then bind this function to a function key. To use just highlight the query name in the .tcl file and hit the function key.

I use ctags/etags to index the .xql and -postgresql.xql files so that in Emacs M-. can find a named query in the XQL files. This approach is not 100% fool proof as M-. finds the FIRST query by that name. This could be in a file that doesn't correspond to the .tcl file containing the named query.

Nevertheless, I find it useful in most cases. Here's the command line to add queries to the Emacs TAGS file:

find . -type f -name "*.xql" -and ! -name "*-oracle.xql" -exec etags --append -o TAGS --lang=none
      --regex='/[     ]*<(partial)?(full)?query name="([^     ]+)">//' {} ; -print

Please note that this command does NOT index the oracle files. This is because I work predominantly with PostgreSQL.

Since, the bboard ate some backslashes, I'm reposting:

(defun dcw-find-tag-default ()
  "return a string near the point"
  (interactive "")
  (save-excursion
    (while (looking-at "\sw\|\s_")
      (forward-char 1))
    (if (or (re-search-backward "\sw\|\s_"
				(save-excursion (beginning-of-line) (point))
				t)
	    (re-search-forward "\(\sw\|\s_\)+"
			       (save-excursion (end-of-line) (point))
			       t))
	(progn (goto-char (match-end 0))
	       (buffer-substring (point)
				 (progn (forward-sexp -1)
					(while (looking-at "\s'")
					  (forward-char 1))
					(point)))) nil)))



(defun dcw-find-query ()
  "run grep on selection 
   Uses x-get-selection to get the grep string"
  (interactive "")
  (let* ((file-name 
          (concat (substring 
                   (buffer-file-name) 0 
                   (string-match ".tcl$" (buffer-file-name))) "*.xql")))
    (grep (concat "grep -n "query  *name=.*" 
                  (dcw-find-tag-default) "" " file-name))))

Hope this works.