enode/enode-lisp/planner.el

;; (load-file "enode-experiments.el")

;; The customisation group. Will in the ENODE group.
(defgroup enode-planner nil
  "A group for customising the ENODE-PLANNER functionality."
  :group 'ENODE
  :version "21.2"
  )

;; What table the execution plan should be made from
(defcustom enode-planner-plan-table "PLAN_TABLE"
  "The table that the will be used for generating an execution plan for
queries. Defaults to PLAN_TABLE, but can, of course, be anything else."
  :type 'string
  :group 'enode-planner
  :version "21.2"
  )

(defun enode-oracle-remove-prompt-from-plan-output (plan-buffer)
  "A function to clean the explain plan output of the sqlprompt string"
  (let ((sql-prompt (enode-oracle-get-sql-prompt)))
    (save-excursion
      (set-buffer plan-buffer)
      (goto-char (point-min))
      (while (search-forward sql-prompt nil t)
        (replace-match "" nil nil)
        )
      )
    )
  )

(defun enode-plan-buffer ()
  "A function to return the buffer assigned for the explain plan output"
  (get-buffer-create "*enode-explain-plan*"))

(defun get-statement-id ()
  "A function to generate a unique statement id for the explain plan"
  (format "ENODE_%s_%s"
          (format-time-string "%Y%m%d")
          (format-time-string "%H%M%S"))
  )

(defun prepare-sql-buffer-for-command ()
  "A function to prepare the SQLi buffer for an outputting command. It sets
properties of the SQL*Plus process to value that make an explain plan output
somewhat pretty."
  (enode-oracle-sqlplus-set-param "heading" "off")
  (enode-oracle-sqlplus-set-param "linesize" "1024")
  (enode-oracle-sqlplus-set-param "pagesize" "0")
  )

(defun reset-sql-buffer-after-command (page-size line-size)
  "A function to return the SQL*Plus process' setting to 'original' values. As
well as setting PAGE-SIZE and LINE-SIZE, it also turns 'heading'
on and 'sqlnumber' on."
  (enode-oracle-sqlplus-set-param "heading" "on")
  (enode-oracle-sqlplus-set-param "pagesize" page-size)
  (enode-oracle-sqlplus-set-param "linesize" line-size)
  )

(defun get-sql-explain-plan-command (enode-plan-statement-id)
  "A function to return the 'explain plan...' statement. It sets the
statement_id to ENODE-PLAN-STATEMENT-ID. It also includes the sql command from
the current buffer -- i.e. the command you want to analyse."
  (format
   "explain plan\nset     statement_id = '%s'\ninto    %s\nfor\n%s;"
   enode-plan-statement-id
   enode-planner-plan-table
   (get-sql-command-to-explain)
   )
  )

(defun get-sql-command-to-explain ()
  "A function to return as a string the SQL command in the current buffer.
It assumes there is only one command. It replaces empty lines with a space and
the ';' character with nothing."
  (replace-regexp-in-string "^[$\n]" " "
                            (replace-regexp-in-string ";" "" (buffer-string)))
  )

(defun get-explain-plan-output-command (enode-plan-statement-id)
  "A function to return the command to generate the nice explain plan output.
It uses ENODE-PLAN-STATEMENT-ID to determine which explain plan we want to
look at."
  (format "SELECT LPAD ( ' ', LEVEL - 1 ) || decode ( position, null, '', position || '-' ) || operation || decode ( options, null, '', ' ' || options ) || decode ( object_name, null, '', ' ' || object_name ) || decode ( optimizer, null, '', ' Optimiser=''' || optimizer || '''' ) || decode ( cost, null, '', ' Cost=' || cost ) || decode ( cardinality, null, '', ' Cardinality=' || cardinality ) FROM %s START WITH id = 0 AND statement_id = '%s' CONNECT BY PRIOR id = parent_id AND statement_id = '%s';"
          enode-planner-plan-table
          enode-plan-statement-id
          enode-plan-statement-id)
  )

;;;; Change this to use enode-oracle-run-sql-command
(defun enode-prepare-explain-plan (enode-plan-statement-id)
  "A function to prepare an explain plan. That is, it takes the SQL command
in the current buffer and generates an explain plan in the PLAN_TABLE in it,
using ENODE-PLAN-STATEMENT-ID as the statement_id value."
  (save-excursion
    ;; Get the full explain plan command and the SQLi buffer to run it in.
    (let ((enode-sql-command
           (get-sql-explain-plan-command enode-plan-statement-id))
          (enode-sql-buffer sql-buffer)
          )
      ;; Run the command.
      (comint-redirect-send-command-to-process
       enode-sql-command
       (enode-temp-output-buffer)
       enode-sql-buffer
       nil
       t
       )
      ;; Allow the command to complete.
      (enode-wait-for-command)
      )
    )
  )

;;;; Change this to use enode-oracle-run-sql-command
(defun enode-output-explain-plan (enode-plan-statement-id)
  "A function to display an explain plan that is already in the PLAN_TABLE. It
identifies the plan with ENODE-PLAN-STATEMENT-ID."
  (save-excursion
    ;; Get the linesize and pagesize values the command to run
    ;; to generate the output and the SQLi buffer in which to run it.
    (let ((enode-line-size (enode-oracle-sqlplus-get-linesize))
          (enode-page-size (enode-oracle-sqlplus-get-pagesize))
          (enode-plan-output-command
           (get-explain-plan-output-command enode-plan-statement-id))
          (enode-sql-buffer sql-buffer)
          )
      ;; Empty the explain plan output buffer.
      (enode-clean-buffer (enode-plan-buffer))
      ;; Tell SQL*Plus we want pretty output for this command.
      (prepare-sql-buffer-for-command)
      ;; Run the command.
      (comint-redirect-send-command-to-process
       enode-plan-output-command
       (enode-plan-buffer)
       enode-sql-buffer
       nil
       t)
      ;; Allow the command to complete.
      (enode-wait-for-command)
      ;; We're done with the nice output. Return SQL*Plus to it regular ugly
      ;; stuff.
      (reset-sql-buffer-after-command
       enode-page-size enode-line-size)
      )
    )
  )

(defun get-explain-plan (&optional present-plan-command-output-p)
  "A function to display an explain plan for an SQL command in the current
buffer."
  ;; We want to call this as a command
  (interactive "P")
  ;; For feedback purposes
  (setq enode-oracle-progress-message ".")
  ;; Generate a statement_id value for this plan.
  (let ((plan-statement-id (get-statement-id)))
    ;; Generate the plan in the PLAN_TABLE
    (enode-prepare-explain-plan plan-statement-id)
    (if present-plan-command-output-p
        (save-excursion
          (display-buffer (get-buffer " *enode-comint-sql-output*"))
          (read-from-minibuffer "Hit return to continue...")))
    ;; Send a nice representation of the plan to the plan buffer.
    (enode-output-explain-plan plan-statement-id)
    ;; Clean up the buffer
    (enode-oracle-remove-prompt-from-plan-output (enode-plan-buffer))
    ;; Show us the plan in another window.
    (pop-to-buffer (enode-plan-buffer))
    )
  )

(defun get-explain-plan-for-region (beg end &optional present-plan-command-output-p)
  "A function to take an SQL statement from the region and to
get an explain plan for it."
  (interactive "r\nP")
  (let ((plan-command-buffer (enode-stage-buffer))
        (plan-command (buffer-substring beg end)))
    (set-buffer plan-command-buffer)
    (insert plan-command)
    (get-explain-plan present-plan-command-output-p)
    )
  )