enode/enode-lisp/enode.el

;; ENODE -- ENODE is Not an Oracle Development Environment
;; A package and 'mode' for providing an interface for examining and
;; developing for relational databases.

;; Copyright 2013 Éibhear Ó hAnluain

;; This file is part of ENODE.
;;
;; ENODE is free software: you can redistribute it and/or modify
;; it under the terms of the GNU General Public License as published by
;; the Free Software Foundation, either version 3 of the License, or
;; (at your option) any later version.
;;
;; ENODE is distributed in the hope that it will be useful,
;; but WITHOUT ANY WARRANTY; without even the implied warranty of
;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
;; GNU General Public License for more details.
;; 
;; You should have received a copy of the GNU General Public License
;; along with ENODE.  If not, see <http://www.gnu.org/licenses/>.

;; Drawing on TOAD by Quest Software as inspiration, this is provides an emacs
;; based interface to examine and develop for a relational database. It's
;; hoped that the following databases will ultimately supported:
;;   mysql
;;   postgres
;;   oracle
;; In fact, the proof-of-concept will be developed for an oracle database,
;; and as I will have reasonably easy access to mysql and postgres databases,
;; They will follow suit. I indend to provide high-level interfaces to
;; databases, so it will be easy to develop a layer for interacting with
;; databases from other vendors.
;;
;; Initially, the following functionality will be provided:
;;   Interface -- look and feel.
;;   Connect and disconnect, plus management of saved connection information.
;;   Database object listing and examination.
;;   Area for typing of ad hoc SQL statements.
;;   Presentation of SQL query output.
;;     - There'll be no facility to change data as presented from a query.
;;       However, update, delete and insert commands will be facilitated
;;       through the SQL area.
;;
;; Interface -- look and feel.
;;   The emacs frame will be divided into three windows:
;;     - The 'object list' or 'navigator' window.
;;         + Here will be listed in tree format the various objects that a
;;           schema has. Hierarchy will be something like: Schema, objects
;;           (tables, views, packages, etc.), columns/indexes (for tables and
;;           views) or procedures/functions (for packages), parameters, etc.
;;         + The user will enter and leave this window by key strokes or
;;           mouse clicks.
;;         + An object is selected by clicking with the middle button or
;;           hitting return when point is somewhere on its name. Once
;;           selected, information will be presented in the information frame
;;           (see below).
;;         + As a tree structure will be used, opening and closing of nodes
;;           will result from selection of the node as described. Selecting an
;;           'open' node will 'close' it and vice versa.
;;         + A node can be closed or opened using the left and right arrow
;;           keys. This will not result in selecting the node, therefore
;;           preserving the information presented.
;;         + This window can present any of a number of buffers. However, each
;;           of these buffers must be of a perticular type (e.g. navigator),
;;           and will have a local key map specific to its use. Separation of
;;           the buffers will facilitate management -- one buffer for tables,
;;           another for views, etc.
;;         + The top node for each buffer will be the name of the schema owner.
;;         + The user can toggle between showing only one schema owner in the
;;           list or all of them
;;     - The information window.
;;         + This will present information on the item from the navigation
;;           window that has most recently been selected.
;;         + The user can navigate to this window by way of key stroke or
;;           mouse click.
;;         + The window will provide a subset of all the information on the
;;           object, and a menu to facilitate showing other information.
;;         + This window can present any of a number of buffers. There will be
;;           a separate buffer type for each type of information being
;;           displayed (i.e. table columns are presented differently from a
;;           table's indexes, which is also different from the list of
;;           triggers on the table, etc.)
;;     - The SQL interaction window.
;;         + This window will present one of two buffers -- the SQL input
;;           buffer and the SQL result buffer.
;;         + They can both be presented, but at the expense of the other
;;           windows.
;;         + In the SQL input buffer, entering commands will be as simple as
;;           entering SQL commands in any sql-mode buffer.
;;         + Indentation will eventually be based on my preferred indentation
;;           scheme, as I am the dictator, and not necessarily benevolent.
;;         + Execution of the command will involve typing a key stroke rather
;;           than the RET key as we will want to format the command nicely.
;;         + The output buffer will present data in one of two formats:
;;             > Select commands will present the selected data in grid format.
;;             > Other commands will generate output in simply sequential
;;               output format.
;;     - Possible arrangements can include:
;;         + Three windows in two rows. The navigator and information windows
;;           in the upper row, the latter being the wider. The SQL interaction
;;           window being in the lower. The upper row would be the higher.
;;         + Three windows in two columns. The navigator in the first column,
;;           and the information and SQL interaction windows in the second.
;;           The latter column will be the wider and the information window
;;           will be higher than the SQL interaction window.
;;         + Two windows in either columnar or tiered format. The user decides.
;;           The windows will be related by function: The navigator and
;;           information windows together with the latter getting more space;
;;           the SQL input and output windows together, each getting equal or
;;           similar space. The SQL window can be displayed in one of the
;;           first two configurations if a function is called from the
;;           information window that warrants it.
;;     - Help information.
;;         + Help can be brought up by typing the '?' key. This will present
;;           the list of key strokes that perform tasks in the window which
;;           has focus.
;;         + The help display will be presented in the SQL interaction window,
;;           which will be presented if it isn't already.
;;         + If the focus is already in a buffer in the SQL interaction window,
;;           the help screen will be presented in the largest visible other
;;           window.
;;         + Typing the '?' key in the help buffer will replace its contents
;;           with all the keystrokes possible based on the type of buffer
;;           supported and listing key strokes that work all over ENODE.
;;         + The user can return to the buffer of most recent focus using
;;           a single key stroke.
;;         + The user can dismiss the help screen and replace the windows to
;;           their previous configuration by typing the 'q' key while in the
;;           help buffer.
;;           
;;
;; Connect and disconnect.
;;   - Upon startup, ENODE will ask for connection information in almost
;;     precisely the manner in which sql-.+ asks for it -- using the
;;     minibuffer to get the username, password and database information.
;;   - ENODE will save each connection information in a history file,
;;     and will maintain a completion list or lists to facilitate quick
;;     connection. For connections to new databases, ENODE will ask for
;;     the type of database (mysql, oracle, etc). This will be stored with
;;     the connection information.
;;   - The actual commands that will be executed against the database will
;;     be based on the type of database being used. However, this will
;;     mainly be hidden from the user.
;;   - ENODE will facilitate concurrent connections.
;;   - A list of possible connections can be presented in the navigation
;;     screen. Open connections will be marked. Opening a closed connection
;;     involved 'selecting' it. Closing an open connection should not be
;;     that easy, and will involve a key stroke followed by an 'Are you
;;     sure?' question. Selecting an open connection which is not the
;;     current connection makes that connection current.
;;     Each connection can be represented in this list either by an alias
;;     given to it explicitly by the user or by a connection string in the
;;     format of something like <user>/<database>@<host>
;;   - Switching between connections will be at the drop of key stroke.
;;       + It will be wise to figure out from the start how new connections
;;         effect the buffers being displayed at the time.
;;       + See above regarding switching between connections using the
;;         navigator window.
;;   - Closing connections can be done by one of two means:
;;       + Close the current connection. Done with a key stroke and a
;;         response to an 'Are you sure?' question, the next connection in
;;         the list of open connections will be activated. If we are closing
;;         the final connection ENODE will report this but not close the
;;         application.
;;       + Place the point in the connection in the navigator and execute a
;;         key stroke.
;;
;; Database object listing and examination.
;;   - The most useful window here will be the navigator. It will list the
;;     objects of interest in a tree structure. There will be separate lists
;;     for tables, views, indexes and stored procedure/functions/packages.
;;     tables will drill down to triggers, columns, indexes and constraints.
;;     Columns will drill down to triggers, indexes and constraints.
;;     Views will drill down similarly. Packages will drill down to specs
;;     and bodies. Specs will drill down to types/procedures/functions/etc.
;;     Bodies will drill down to functions/procedures. Functions/procedures
;;     will drill down to parameter lists and return types (where
;;     appropriate).
;;   - The types of information displayed and the information itself will
;;     depend on the selected item, examples of which are:
;;       + Tables
;;           > Data
;;           > Columns
;;           > Constraints
;;           > Indexes
;;           > Triggers
;;       + Views
;;           > Data
;;           > Columns
;;           > Source
;;       + Constraints
;;           > Tables/Columns
;;       + Packages/procedures/functions
;;           > Dependancies
;;           > Source
;;       + Triggers
;;           > Tables
;;           > Source
;;     In the case of views and tables, if we want to see data, it is to be
;;     displayed in the SQL interaction window.
;;
;; Area for typing of ad hoc SQL statements.
;;   - This will display the SQL input buffer.
;;   - SQL commands can be typed as free text into the buffer.
;;   - Using key strokes, certain actions can then be run on the command in
;;     the buffer: execute, parse/compile, explain execution plan, etc.
;;   - Depending on a prefix argument to each of the key strokes commands,
;;     they will be executed on the contents of the buffer, the SQL command
;;     the point is currently in or on the commands that are in the region.
;;   - It will be possible to save the contents to a file.
;;   - It will be possible to clear the contents in one go.
;;   - It will be possible to insert the contents of a file, either after
;;     point or by first clearing the buffer.
;;   - Inserting the contents of the file into the buffer will not mean
;;     visiting the file. That functionality will come later.
;;
;; Presentation of SQL (query) output.
;;   - For commands other than select statements, the output presented will
;;     be as if the commands had been run on the command line.
;;   - Output from queries will be presented in a grid manner, the
;;     configuration of which will be decided after some initial testing.
;;
;; Internals
;;   - ENODE will maintain many lists which will be used extensively. These
;;     will all be association lists. All the elements of these lists will be
;;     string values, not symbols. Depending on the case sensitivity of the
;;     database system, these will be case sensitive or not. The following
;;     are some of these lists:
;;       + Databases. This list will be populated with the first database we
;;         connect to. The variable describing the current database will
;;         contain a string value from this list.
;;       + Schema owners. There will be a separate list of schema owners
;;         per database. As we connect to databases afresh, the first two
;;         elements of this list will be the user we connect as and the
;;         system/root schema. The variable describing the current schema
;;         owner we're connected as will contain an element from this list.
;;         If the user requests to see any information pertaining to a schema
;;         owner (s)he is not connected as, this list is populated fully.
;;         This list can be refreshed by typing the refresh key stroke while
;;         a schema owner has been selected in the navigation window.
;;         Refreshing the list also refreshes its presentation in the
;;         navigation window.
;;       + Tables. There will be a separate list for each owner. This list
;;         will be populated for the current schema owner as we connect
;;         for the first time. It will be populated for other schema owners
;;         as we request information on a table owned by that schema owner.
;;         This list can be refreshed by typing the refresh key stroke while
;;         a table is selected in the navigation window.
;;       + Views. There will be a separate list for each owner. This list
;;         will be populated for the current schema owner as we connect for the
;;         first time. It will be populated for other schema owners as we
;;         request information on a view owned by that schema owner. This list
;;         can be refreshed by typing the refresh key stroke while a view is
;;         selected in the navigation window.
;;       + Constraints.
;;       + Columns. A list per table or view.
;;       + Indexes. A list per table.
;;       + Packages. A list per schema owner.
;;       + Procedures. A list per schema owner for non packaged procedures, a
;;         list per package for packaged.
;;       + Functions. A list per schema owner for non packaged functions, a
;;         list per package for packaged.
;;         
;;   - Refreshing a list.
;;     The following will happen when a command to refresh a list is called.
;;       1.  An empty list will be created.
;;       2.  The command to populate this new list will be executed.
;;       3.  The contents of the new list will be compared with the existing
;;           list and newer elements will be added to it. Elements that are
;;           in the old list and are missing from the new will be removed from
;;           the old.
;;       4.  If the eode-refresh-recursively variable is non-nil, then
;;           any sublists will also be refreshed. in this manner.
;;       5.  Elements of a list that can have a sublist but that sublist
;;           is nil at the time of the refresh will not have that list
;;           populated. I.e. we don't refresh a list that hasn't been populated
;;           yet.
;;     The following will be applied during a list refresh:
;;       1.  The node in the navigation list will be 'closed' before the
;;           refresh begins.
;;       2.  The node's parent node will be 'closed'.
;;       3.  After the refresh, the parent's node will be opened again.
;;       4.  If the node that had been selected at the time of the call to
;;           refresh exists after the refresh, the point is sent to it and
;;           it is explicitly 'selected'. If it doesn't, the node's parent
;;           node is 'selected'.
;;
;;   - Interacting with the database.
;;       + The main engine will be the sql-<engine> functionality that is
;;         provided as standard with GNU/Emacs distributions.
;;       + All commands will be run in the background and will use the
;;         comint-redirect-send-command* functionality.
;;       + Lists will be read from temporary buffers.
;;       + Presented SQL output will probably have outputting formatting
;;         specified for the actual SQL interpreter being used and send to
;;         the SQL output buffer.
;;
;;   - Context.
;;     There will be variables that will maintain:
;;       + The current database and the most recent one.
;;       + The current schema owner and the most recent one.
;;       + The current table/view/package/etc. and the most recent one.
;;         There will be a separate pair of variables for each type of
;;         object ENODE supports.
;;       + The current selected item. I.e. There is a table named "FOO" and
;;         an index named "BAR". Both the enode-current-table,
;;         enode-current-index and enode-selected-item are nil. The
;;         user navigates to "FOO" in the navigation window and selects it.
;;         enode-current-table and enode-selected-item are set to "FOO".
;;         enode-current-index is still nil. The user then navigates to the
;;         "BAR" index and selects it. enode-selected-item and
;;         enode-current-index are set to "BAR", but enode-current-table
;;         remains set to "FOO".
;;       + The previous selected item.
;;       + The current buffer and the previous one.
;;       + The current window and the previous one.
;;
;; A typical session might be:
;;     1. A user calls the command M-x enode.
;;     2. The user is asked to enter a connection string, and is presented with
;;        a default which is the most recently used connection.
;;     3. The user can accept the default, use M-p and M-n to scroll through
;;        a list of saved connections, type in a connection (using completion
;;        to assist) or type in a new connection name or type in '+'.
;;     4. If the default is accepted that connection string is used to connect.
;;        If a connection from the list is entered, it's used. If a connection
;;        name that isn't on the list is entered, the user wants to create a
;;        new connection with that name. If '+' is entered, the user wants
;;        to create a new connection but doesn't know what to call it.
;;     5. If one of the last two, the user is asked for the type of database
;;        we want to connect to.
;;     6. Based on that, the sql-<vendor> function is called and the user is
;;        then prompted to enter the appropriate information.
;;     7. Once connected, the windows are initialised based on the user's
;;        customisation.
;;     8. The list of databases is initialised. The current database is set.
;;     9. The list of schema owners is initialised. If the user prefers to see
;;        all the schema owners in the navigation window at once, this list is
;;        populated with all the schema owners in the database. If not, this
;;        list is initialised to the requested schema owner and the
;;        system/root user. The current schema owner is set.
;;    10. The point is brought to the schema owner and the information for
;;        that user is presented in the information window. enode-selected-item
;;        is set.

;; If we want oracle shtuff.
(require 'enode-oracle) ;; Oracle only
(require 'enode-mysql) ;; MySQL/MariaDB only

;; Some handy stuff that really should be part of ENODE but isn't
(require 'sql-extras)
(require 'pls-extras) ;; Oracle only

;;
;; Customisable stuff
;;

;; The customisation group. Should be in the SQL group, but will also
;; put it into the local group.
(defgroup enode nil
  "A group for customising ENODE:
The bestest database development environment ever developed for emacs."
  :group 'local
  :group 'SQL
  :version "21.2"
  )

(defcustom enode-projects-dir nil
  "The location down which the SQL files for the projects will be found.
For each project, there will be a directory in this directory named for the project, a subdirectory named \"scripts/sql\" and in it will be a file named \"<project>_ENODE.sql\". This directory is used to determine the projects being used."
  :type 'directory
  :group 'enode
  :version "21.2"
)

(defcustom enode-project-sql-file-dir nil
  "The directory beneath the specific project directory, which in turn is a
subdirectory of ENODE-PROJECTS-DIR, in which the SQL \"seed\" file for the
project can be found."
  :type 'directory
  :group 'enode
  :version "21.2"
)

;; The preferred window configuration:
(defcustom enode-initial-window-arrangement "3r"
  "How do you prefer the windows to be arrayed.
Current options are:
3r: 3 windows arrayed in rows -- The navigator and the information windows
    in the first row, the SQL interaction window in the second.
3c: 3 windows arrayed in columns -- The navigator in the first column, the
    information and SQL interaction windows in the second.
2c: 2 windows arrayed in columns. These are the navigator and the information
    windows.
2r: 2 windows arrayed in rows. These are the SQL input and SQL output windows."
  :type '(choice (const :tag "Three windows, two above, one below" "3r")
                 (const
                  :tag "Three windows, one to the left and two to the right"
                  "3c")
                 (const
                  :tag "Two windows, navigator to the left, information to the right"
                  "2c")
                 (const
                  :tag "Two windows, SQL input above, SQL output below"
                  "2r")
                 )
  :group 'enode
  :version "21.1"
  )

;; Show the schema owner alone in the navigation window, or all the
;; schema owners.
(defcustom enode-show-all-schema-owners nil
  "Whether to show all the schema owners in the navigation window.
If non-nil, all the schema owners will be listed. If nil, only the current
schema owner."
  :type '(choice (const :tag "Only the current schema owner" nil)
                 (const :tag "All schema owners" t)
                 )
  :group 'enode
  :version "21.1"
  )

;; The location of the enode files.
(defcustom enode-directory "~/.enode"
  "The directory where enode's configuration files will be stored"
  :type 'directory
  :group 'enode
  :version "21.2"
  )

;; The file for containing connection information
(defcustom enode-connections-file (concat enode-directory "/enode-connections")
  "The file that contains the list of previous and saved connections.
The file will be an alist. Each element will be the name of the connection
and then a list of the information needed to make the connection: database
vendor, username, server, host. Password will never be stored."
  :type 'file
  :group 'enode
  :version "21.2"
  )

;; The file for containing project information
(defcustom enode-projects-file (concat enode-directory "/enode-projects")
  "The file that contains the list of projects used with ENODE."
  :type 'file
  :group 'enode
  :version "21.2"
  )

;;
;; Non-customisable variables
;;

;; A simple way to change a project in a current ENODE session.
;; Not very robust.
(defalias 'enode-project 'enode)

(defvar enode-progress-message ".")

(defvar enode-selected-item nil
  "The currently selected item from the navigator.
This is the item whose information is displayed in the information window.")

(defvar enode-previous-database nil
  "The most recent database in use")

(defvar enode-previous-schema-owner nil
  "The most recent schema owner in use")

(defvar enode-current-table nil
  "The current table being examined")

(defvar enode-previous-table nil
  "The table examined immediately before enode-current-table")

(defvar enode-projects nil
  "The alist of projects that ENODE handles for the user")

(defvar enode-connections nil
  "The alist of connections that ENODE handles for the user")

(defvar enode-current-connection-type nil
  "The current connection type: 'oracle, 'mysql, etc.")

(defvar enode-supported-connection-types '(oracle mysql)
  "The current connection type: 'oracle, 'mysql, etc.")

(defvar enode-current-connection nil
  "The current connection.")

(defvar enode-passwords-in-use nil
  "Those passwords that have been input in this session")

(defvar enode-up nil
  "Nil if ENODE is not \"running\", non-NIL if it is.")

(defvar enode-connected nil
  "Nil if ENODE is not connected to a database, non-NIL if it is.")

(defvar enode-launched-as-application nil
  "If ENODE has been launched as an application rather than during an otherwise
useful emacs session.")

;;
;; Some constants. Defined here and will be used throughout the code.
;;

;; The buffer that will be used to pick up the output from SQL commands.
(defconst enode-sql-command-output-buffer
  (get-buffer-create " *ENODE-command-output*")
  "This is the buffer that SQL output will be directed to where the information
can be picked up from.")

;;
;; Exposed functions
;;
(defun enode-automatic ()
  "So that enode can be launched from the command-line. It asks for the
project in the body of the function rather than as part of a call to
INTERACTIVE"
  (interactive)
  (let ((the-project (completing-read "Project: "
                                      (enode-available-projects)
                                      nil
                                      t))
        )
    (setq enode-launched-as-application t)
    (enode the-project)
    )
  )

(defun enode (enode-project)
  "Launch the enode system"
  ;; The name of the project we're interested in.
  (interactive (list (completing-read "Project: "
                                      (enode-available-projects)
                                      nil
                                      t))
               )
  ;; We're connecting to an Oracle database
  (setq enode-current-connection-type
        (cadr (assoc enode-project enode-projects)))
  ;; Load functions for that engine
  ;; (load-file (format "enode-%S.el" enode-current-connection-type))
  ;; Let's call
  (enode-start-engine)
  (other-window 1)
  ;; The project's "general purpose" SQL file.
  (let ((sql-file-name (format "%s/%s/%s/%s_ENODE.sql"
                               enode-projects-dir
                               enode-project
                               enode-project-sql-file-dir
                               enode-project)))
    (find-file sql-file-name)
    )
  (setq enode-up t)
  ;; The SQL engine is called without connection information. Log in now.
  (call-interactively 'enode-connection)
  )

(defun enode-start-engine()
  "A function to start the SQL engine for the connection type"
  (cond ((eq 'oracle enode-current-connection-type)
         (enode-oracle-start-sql-engine))
        ((eq 'mysql enode-current-connection-type)
         t)
        )
  )

(defun enode-stop-engine()
  "A function to stop the SQL engine for the connection type"
  (cond ((eq 'oracle enode-current-connection-type)
         (enode-oracle-stop-sql-engine))
        )
  )

(defun enode-connect (connection-description)
  "A function to initiate a connection based on connection type"
  (let ((conn-details (cdr (assoc connection-description
                                  (enode-get-connections))))
        ;; Somewhere to come back to.
        (active-buffer (current-buffer))
        )
    ;; The user to connect as
    (setq conn-user (plist-get conn-details :conn-user))
    ;; The database to connect to
    (setq conn-connection (plist-get conn-details :conn-connection))
    ;; The password to use: if it's not in the saved connection
    ;; details...
    (setq conn-pass (if (not (plist-get conn-details :conn-pass))
                        ;; ... if it's one of the locally saved
                        ;; passwords...
                        (if (assoc connection-description
                                   enode-passwords-in-use)
                            ;; ... use it.
                            (car (cdr (assoc connection-description
                                             enode-passwords-in-use)))
                          ;; otherwise...
                                ;; Read the password from the user
                          (let ((my-conn-pass
                                 (read-passwd
                                  (format "Password for %s on %s: "
                                          conn-user
                                          conn-connection))))
                            ;; add it to the list of those in use.
                            (setq enode-passwords-in-use
                                  (cons (list connection-description
                                              my-conn-pass)
                                        enode-passwords-in-use))
                            my-conn-pass))))
    ;; The SQL prompt for the SQL engine
    (setq conn-prompt (plist-get conn-details :conn-prompt))
    (setq conn-hostname (plist-get conn-details :conn-hostname))
    (setq conn-port (plist-get conn-details :conn-port))
    (cond ((eq 'oracle enode-current-connection-type)
           ;; Connect.
           (enode-oracle-connect conn-user conn-pass conn-connection
                                 conn-prompt)
           )
          ((eq 'mysql enode-current-connection-type)
           (enode-mysql-connect conn-user conn-pass conn-connection
                                conn-hostname conn-prompt conn-port)
           )
          )
    ;; Say who we're now connected to.
    (setq enode-current-connection connection-description)
    (setq enode-connected t)
    (set-buffer active-buffer)
    )
  )

(defun enode-disconnect ()
  "A function to close a connection to the database"
  (interactive)
        ;; Somewhere to come back to.
  (let ((active-buffer (current-buffer)))
    (cond ((eq 'oracle enode-current-connection-type)
           ;; Disconnect.
           (enode-oracle-disconnect)
           )
          )
    ;; Say who we're now connected to.
    (setq enode-current-connection nil)
    (setq enode-connected nil)
    (set-buffer active-buffer)
    )
  )

(defun enode-query-connection ()
  "A function to tell you who you are and what system you're connected to"
  (interactive)
  ;; Print out the information
  (message (cond ((not enode-up) "ENODE not running")
                 ((not enode-connected) "ENODE not connected")
                 (t enode-current-connection))
           )
  )

(defun enode-connection (connection-description)
  "A command to connect to another user"
  ;; Get a connection description from the minibuffer
  (interactive (list (completing-read "Connection: "
                                      (enode-get-connections)
                                      ))
               )
  ;; If the passed description is not a saved description...
  (if (not (assoc connection-description (enode-get-connections)))
      ;; ... get the relevant information and save it.
      (enode-add-new-connection connection-description)
    )
        ;; Details for this connection from the list of available connections
  (enode-connect connection-description)
  )

(defun enode-clear-stored-password (username)
  "A function to clear a password from a list of passwords"
  ; Whose password we want to clear.
  (interactive (list (completing-read "User: "
                                      enode-passwords-in-use
                                      ))
               )
  ;; Create two temporary lists...
        ;; ... the first based on the current password list
  (let ((tmp-password-list enode-passwords-in-use)
        ;; the second a blank list to build a new list.
        tmp-password-list-2)
    ;; For as long as the first list is not empty...
    (while tmp-password-list
      ;; ... if the first entry is not the username we're interested in...
      (if (not (string= (caar tmp-password-list) username))
          ;; ... add the first entry to the new list.
          (setq tmp-password-list-2 (cons (car tmp-password-list)
                                          tmp-password-list-2))
        )
      ;; Remove the first entry from the temporary list
      (setq tmp-password-list (cdr tmp-password-list)))
    ;; Reset the password list with the password information of interest
    ;; removed.
    (setq enode-passwords-in-use tmp-password-list-2)
    )
  )

(defun enode-list-users ()
  "A function to throw up a list of the database users for the current
connection."
  (interactive)
        ;; A buffer to send the information to
  (let ((user-list-buffer (enode-buffer "*ENODE-users*")))
    ;; How are we connected?
           ;; Oracle database...
    (cond ((eq 'oracle enode-current-connection-type)
           (enode-oracle-list-users user-list-buffer)
           )
          )
    ;; Go to the buffer with the information
    (display-buffer user-list-buffer)
    ;; We're done.
    (message "Done.")
    )
  )

(defun enode-desc (object-name)
  "A function to describe an object."
  (interactive (list (read-from-minibuffer "Describe: " )))
  (let ((desc-buffer
         (enode-buffer (format "*ENODE-describe-%s*" object-name)))
        )
    ;; How are we connected?
           ;; Oracle database...
    (cond ((eq 'oracle enode-current-connection-type)
           (enode-oracle-desc object-name desc-buffer)
           )
          )
    (display-buffer desc-buffer)
    (message "Done.")
    )
  )

(defun enode-list-invalid-objects (dbuser &optional object-type)
  ""
  (interactive (list (upcase (enode-connected-user))))
  (enode-list-objects dbuser object-type t)
  )

(defun enode-list-objects (dbuser &optional object-type invalid-flag
                                  filter-string)
  "A function to throw up a list of the database objects owned by the specified
DBUSER.
When called interactively, the current user is used."
  ;; Who we're currently connected as
  (interactive (list (upcase (enode-connected-user))))
        ;; The buffer to send the output to.
  (let ((object-list-buffer
         (enode-buffer
          (format
           "*ENODE-%s-%s*" dbuser
           (if object-type (format "%ss" (downcase object-type)) "objects")
           ))
         )
        )
    ;; How are we connected?
           ;; Oracle database...
    (cond ((eq 'oracle enode-current-connection-type)
           (enode-oracle-list-objects object-list-buffer dbuser object-type
                                      invalid-flag filter-string)
           )
          )
    ;; Go to the buffer (again?)
    (display-buffer object-list-buffer)
    ;; We're Done.
    (message "Done.")
    )
  )

(defun enode-list-object-types (&optional dbuser)
  "A function to throw up a list of the object types."
  ;; Who we're currently connected as
  (interactive)
        ;; The buffer to send the output to.
  (let ((object-type-list-buffer (enode-buffer "*ENODE-object-types*")))
    ;; How are we connected?
           ;; Oracle database...
    (cond ((eq 'oracle enode-current-connection-type)
           (enode-oracle-list-object-types object-type-list-buffer dbuser)
           )
          )
    ;; Show the buffer
    (display-buffer object-type-list-buffer)
    ;; We're Done.
    (message "Done.")
    )
  )

(defun enode-list-tables (dbuser)
  "A function to throw up a list of the database user's tables"
  (interactive (list (upcase (enode-connected-user))))
  (enode-list-objects dbuser "TABLE")
  )

(defun enode-list-tables-filtered (dbuser filter-string)
  "A function to throw up a list of the database user's tables"
  (interactive (list (upcase (enode-connected-user))
                     (upcase (read-from-minibuffer
                              "Case-insensitive name substring: ")
                             )
                     )
               )
  (enode-list-objects dbuser "TABLE" nil filter-string)
  )

(defun enode-list-views (dbuser)
  "A function to throw up a list of the database user's views"
  (interactive (list (upcase (enode-connected-user))))
  (enode-list-objects dbuser "VIEW")
  )

(defun enode-list-materialised-views (dbuser)
  "A function to throw up a list of the database user's views"
  (interactive (list (upcase (enode-connected-user))))
  (enode-list-objects dbuser "MATERIALIZED VIEW")
  )

(defun enode-list-functions (dbuser)
  "A function to throw up a list of the database user's functions"
  (interactive (list (upcase (enode-connected-user))))
  (enode-list-objects dbuser "FUNCTION")
  )

(defun enode-list-procedures (dbuser)
  "A function to throw up a list of the database user's procedures"
  (interactive (list (upcase (enode-connected-user))))
  (enode-list-objects dbuser "PROCEDURE")
  )

(defun enode-list-packages (dbuser)
  "A function to throw up a list of the database user's packages"
  (interactive (list (upcase (enode-connected-user))))
  (enode-list-objects dbuser "PACKAGE")
  )

(defun enode-list-package-bodies (dbuser)
  "A function to throw up a list of the database user's package bodies"
  (interactive (list (upcase (enode-connected-user))))
  (enode-list-objects dbuser "PACKAGE BODY")
  )

;;
;; Internal functions
;;

(defun enode-project-available (enode-project)
  "A function to check whether a project is available. It returns TRUE if
the project's top directory is there AND if the enode file is present."
  (and (file-exists-p (format "%s/%s/%s/%s_ENODE.sql"
                              enode-projects-dir
                              (car enode-project)
                              enode-project-sql-file-dir
                              (car enode-project))))
  )

(defun enode-available-projects ()
  "A function to take the list of ENODE projects and to turn it into a list
of projects that are available."
  (let ((the-project-list (enode-get-projects))
        (available-project-list ()))
    (while the-project-list
      (if (enode-project-available (car the-project-list))
          (setq available-project-list 
                (append available-project-list (list (car the-project-list))))
        )
      (setq the-project-list (cdr the-project-list))
      )
    available-project-list
    )
  )

(defun enode-connected-user ()
  "A function to return the username of the currently connected user"
        ;; Get the current connection description
  (let ((my-conn (enode-query-connection)))
    ;; Locate the username part of it
    (string-match "^\\(.+\\)@" my-conn)
    ;; Return that part
    (substring my-conn (match-beginning 1) (match-end 1))
    )
  )

(defun enode-add-new-connection (conn-desc)
  "A function to add a new connection to the list of current connections"
        ;; Get the list of all the possible connections
  (let ((all-connections (enode-load-connections-list))
        ;; Get the list of the connections of the current type.
        (current-type-connections (enode-get-connections)))
    ;; As long as the description we want to create a connection for doesn't
    ;; exist already...
    (if (not (assoc conn-desc current-type-connections))
        (progn
          ;; Get the username
          (setq conn-user
                (read-from-minibuffer (format "Username for %s: " conn-desc)))
          ;; Get the connection name
          (setq conn-connection
                (read-from-minibuffer (format "Database for %s: " conn-desc)))
          ;; Get the prompt that will be used
          (setq conn-prompt
                (read-from-minibuffer (format "PROMPT for %s: " conn-desc)))
          (setq conn-hostname (if (eq enode-current-connection-type 'mysql)
                                  (read-from-minibuffer (format "HOSTNAME for %s: " conn-desc))
                                nil
                                )
                )

          ;; Add these details to the list of connections for the current type
          (setq current-type-connections
                (cons (list conn-desc
                            conn-user
                            nil
                            conn-connection
                            conn-hostname
                            conn-prompt)
                      current-type-connections))
          ;; Replace the connections for this type in the list of all
          ;; connections with the new list for this type.
          (setq all-connections (cons (cons enode-current-connection-type
                                            current-type-connections)
                                      (assq-delete-all
                                       enode-current-connection-type
                                       all-connections)))
          ;; Open the file that contains the connections
          (find-file enode-connections-file)
          ;; Go to the start of the file.
          (goto-char (point-min))
          ;; Look for where the information is specified
          (re-search-forward "^(setq enode-connections")
          ;; Remove that command.
          (beginning-of-line)
          (kill-sexp)
          ;; (delete-region (point) (save-excursion (end-of-sexp)(point)))

          ;; Insert a replacement command
          (insert (format "(setq enode-connections '%S)" all-connections))

          ;; Write the file and dismiss the buffer
          (save-buffer)
          (kill-buffer (current-buffer))

          ;; Load the new connection information
          (enode-load-connections-list)
          )
      )
    )
  )

(defun enode-get-projects ()
  "A function to build a list of projects"
  ;; If the variable is set already, return it
  (if (not enode-projects)
      (load-file enode-projects-file))
  enode-projects
  )

;; Get the list of connections for the current type of connection
(defun enode-get-connections ()
  "A function to return the list of connections for the current ENODE connection type (as specified in ENODE-CURRENT-CONNECTION-TYPE)."
  (if (not enode-connections)
      (enode-load-connections-list))
  (cdr (assoc enode-current-connection-type enode-connections))
  )

;; Load up the list of connections.
(defun enode-load-connections-list ()
  "Load the connection information from the file."
  (load-file enode-connections-file)
  enode-connections
  )

(defun enode-buffer (buffer-name)
  "A generic function for creating and returning a buffer."
        ;; Get the buffer
  (let ((the-buffer (get-buffer-create buffer-name)))
    ;; Empty it
    (enode-clean-buffer the-buffer)
    ;; Return it
    the-buffer
    )
  )

(defun enode-wait-for-command ()
  "A function to wait for a comint redirect command to complete"
  (save-excursion
    (set-buffer sql-buffer)
    (while (null comint-redirect-completed)
      (message enode-progress-message)
      (setq enode-progress-message
            (concat enode-progress-message "."))
      (accept-process-output nil 0 1000))))

(defun enode-clean-buffer (enode-buffer)
  "A function to empty the ENODE-BUFFER."
  (save-excursion
    (set-buffer enode-buffer)
    (erase-buffer)
    )
  )

(defun enode-code-buffer (code-type &optional code-name code-owner)
  "A generic function for creating and returning a buffer for a piece of code."
        ;; Get the buffer
  (let ((the-buffer
         (get-buffer-create
          (format "*enode-%S-%s%s*"
                  enode-current-connection-type
                  (downcase code-type)
                  (if code-name
                      (format "-%s%s"
                              (if code-owner (format "%s." code-owner) "")
                              code-name)
                    "")
                  )
          )
         )
        )
    ;; Empty it

    (enode-clean-buffer the-buffer)
    ;; Return it
    the-buffer
    )
  )

(defun enode-new-project (proj-name)
  "A function to set up a new project"
  (interactive "sProject name: ")
  ;; Raise an error if the project already exists.
  (if (assoc proj-name enode-projects)
      (error "Project already exists")
    )
        ;; Determine the directory for this new project.
  (let ((proj-dir (format "%s/%s" enode-projects-dir proj-name)))
    ;; If the base SQL directory for the project doesn't exist, create it.
    (if (not (file-exists-p (format "%s/%s" proj-dir
                                    enode-project-sql-file-dir)))
        (make-directory (format "%s/%s" proj-dir
                                enode-project-sql-file-dir) t))
    ;; Get the base SQL file for the project.
    (find-file (format "%s/%s/%s_ENODE.sql" proj-dir
                       enode-project-sql-file-dir proj-name))
    )
  ;; Add the new project to the list of maintained projects.
  (enode-add-project-to-list proj-name)
  )

(defun enode-add-project-to-list (proj-name)
  "A function to add a project to the list of usable projects"

  ;; If it isn't already set, determine the connection type for this
  ;; new project.
  (if (not enode-current-connection-type)
      (setq enode-current-connection-type
            (make-symbol
             (read-from-minibuffer
              (format
               "Please enter a connection type (supported options: %S): "
               enode-supported-connection-types
               ))))
    )

  ;; Add the project to the active list
  (setq enode-projects (cons (list proj-name enode-current-connection-type)
                             enode-projects))
  (save-excursion
    ;; Get the file
    (find-file enode-projects-file)
    ;; Look for where the information is specified
    (re-search-forward "^(setq enode-projects")
    ;; Remove that command.
    (beginning-of-line)
    (kill-sexp)
    ;; (delete-region (point) (save-excursion (end-of-sexp) (point)))
    ;; Insert a replacement command
    (insert (format "(setq enode-projects '%S)" enode-projects))
    ;; Write the file and dismiss the buffer
    (save-buffer)
    (kill-buffer (current-buffer))
    )
  )

(defun enode-rollback ()
  "A command to rollback a change."
  (interactive)
  (enode-commit t)
  )

(defun enode-commit (&optional rollback)
  "A command to commit a change to a database"
  (interactive)
    ;; How are we connected?
           ;; Oracle database...
  (cond ((eq 'oracle enode-current-connection-type)
         (enode-oracle-commit rollback)
         )
        )
  )

(defun enode-quit ()
  "A command to exit ENODE"
  (interactive)
  (let ((my-buffer-list (mapcar 'buffer-name (buffer-list)))
        enode-buffer-list
        (my-case-fold-search case-fold-search)
        user-reply
        )
    (setq case-fold-search t)
    (while my-buffer-list
      (if (or (string-match "enode" (car my-buffer-list))
              (buffer-file-name (get-buffer (car my-buffer-list))))
          (setq enode-buffer-list
                (cons (car my-buffer-list) enode-buffer-list))
        )
      (setq my-buffer-list (cdr my-buffer-list))
      )
    (with-temp-buffer
      (insert (format "\n\nQuitting ENODE!\n\n"))
      (insert (format "%s%s"
                      "You are about to leave ENODE. This will close the SQL "
                      "session and then kill the following buffers:\n\n"))
      (let ((my-enode-buffer-list enode-buffer-list))
        (while my-enode-buffer-list
          (insert (format "  %s %s\n"
                          (if (buffer-modified-p
                               (get-buffer
                                (car my-enode-buffer-list)))
                              "*"
                            "%")
                          (car my-enode-buffer-list))
                  )
          (setq my-enode-buffer-list (cdr my-enode-buffer-list))
          )
        )
      (insert (format "\n"))
      (insert (format "%s%s"
                      "If you want to keep any of these, say \"no\" now and "
                      "rename them."))
      (display-buffer (current-buffer))
      (setq user-reply (yes-or-no-p "Are you sure you want to quit ENODE? "))
      )
    (if user-reply
        (progn
          (enode-stop-engine)
          (kill-buffer sql-buffer)
          (while enode-buffer-list
            (kill-buffer (car enode-buffer-list))
            (setq enode-buffer-list (cdr enode-buffer-list))
            )
          (setq enode-up nil)
          (if enode-launched-as-application
              (save-buffers-kill-emacs)
            )
          )
      )
    )
  )

(provide 'enode)