Skip to content

Latest commit

 

History

History
816 lines (563 loc) · 33 KB

CHANGELOG.md

File metadata and controls

816 lines (563 loc) · 33 KB
Raw

Changelog 1.33.12

Changes to Postmodern

  • Added :define-dao-class macro (including docs and tests)
  • Added more documentation on connections (use with-connection rather than toplevel-connect if creating an executable.

Minor additions to cl-postgres tests

Changelog 1.33.11

Changes to S-SQL

  • Added :analyze operator
  • Added :using option for :delete-from
  • Additional S-SQL Documentation

Updated version numbers in the asd files

Changelog 1.33.10

Changes to S-SQL

  • Added :call operator to s-sql (so that you can use the s-sql syntax to call a Postgresql procedure) and substantial more s-sql examples in the documentation.

  • Fixed bug in create-composite-type that disallowed certain multiword parameters

  • Fixed bug in to-type-name and sql-type-name that precluded custom types

  • Fixed bug in :create-table ability to create generated columns.

  • Fixed bug in to-tsvector calls

  • Added cube operators

  • Added more documentation examples and tests for s-sql

Changelog 1.33.9

Fixed bug in thread handling when using binary parameters

Changelog 1.33.8

Dollar Quoted tags are allowed in files. Prior to Postmodern version 1.33.8 only alphabetic characters were allowed in tags. Postgresql documentation and industry practice allow any character to be in a dollar quoted tag. Postmodern version 1.33.8 relaxes the alphabetic character requirement. The only limitation now is that digit characters cannot be in the first position in a tag.

This version also fixes a bug in the postmodern function drop-role. Previously if a role owned objects in multiple databases, the drop-role function tried to drop the role before all owned objects were reassigned owners.

Changelog 1.33.7

Changes in cl-postgres and s-sql to allow use of plain proper lists in parameterized queries. Previously only vectors could be used. The following show examples using both vectors and lists in queries using both raw sql and s-sql.

    (query "select name from employee where id = any($1)"
         #(1 3 4))
    (query "select name from employee where id = any($1)"
         '(1 3 4))

    (let ((emp-ids #(1 2)))
       (query "select name from employee where id = any($1)"
               emp-ids))

    (let ((emp-ids '(1 2)))
       (query "select name from employee where id = any($1)"
               emp-ids))

     (query (:select 'name :from 'employee :where (:= 'id (:any* '$1)))
             #(1 3) :column)
      '("Jason" "Celia")

     (query (:select 'name :from 'employee :where (:= 'id (:any* '$1)))
             '(1 3) :column)
      '("Jason" "Celia")

Plain proper lists can also now be used in s-sql queries using :in. Previously you needed to use :set

    (query (:select 'name :from 'employee :where (:in 'id (:set 1 3 4))))
    '(("Jason") ("Celia") ("Linda"))

Now you can also provide a list.

    (let ((emp-ids '(1 2)))
        (query (:select 'name :from 'employee :where (:in 'id emp-ids))))
    (("Jason") ("Robert"))

Changelog 1.33.6

Postmodern/cl-postgres now returns an empty array if Postgresql returns an empty array.

The previous behavior had been to return nil, but I have been convinced by a user to make this change.

Changelog 1.33.5

Discontinue support for versions of sbcl < 1.2.5 which were compiled without support for bsd-sockets.

Drop unwarrented assumption that the role 'postgres' will always exist when dropping a role resulting in ownership changes of postgresql objects.

Add more documentation on the limitation in s-sql on using lists in a parameterized statement. If you are trying to use a list in a parametized statement, you can't. You have to convert the list to a vector and use "any" rather than "in."

Add additional support for ssl connections to allow use of a root certificate for validation. Set *ssl-root-ca-file* to the pathname for the root certificate.

Changelog 1.33.4

Fix bug in a warning in execute-file that referred to the current package rather than Postmodern.

Added retry-transaction restart in the call-with-transaction function

Changelog 1.33.2

Fix bug in export functions when user tries to export nil into a database

Fix bug in import functions when functions are called but the current package has changed. Note to self. Pay attention to needs for fully qualified symbols (including package names) and how to test them for equality.

Changelog 1.33.1

Dao Export and Import Functions (Postmodern v. 1.33.1 and newer)

There may be times when the types of values in a dao slot do not have comparable types in Postgresql. For purposes of the following example, assume you have slots that you want to contain lists. Postgresql does not have a "list" data type. Postgresql arrays must be homogeneous but CL lists do not have that limitation. What to do?

One method would be to use text columns or jsonb columns in Postgresql and have functions that convert as necessary going back and forth. In the following example we will use text columns in Postgresql and write CL list data to string when we "export" the data to Postgresql and then convert from string when we "import" the data from Postgresql into a dao-class instance.

Consider the following dao-class definition. We have added additional column keyword parameters :col-export and :col-import. These parameters refer to functions which will convert the values from that slot to a valid Postgresql type (in our example, a string) on export to the database and from that Postgresql type to the type we want in this slot on import from the database.

    (defclass listy ()
      ((id :col-type integer :col-identity t :accessor id)
       (name :col-type text :col-unique t :col-check (:<> 'name "")
             :initarg :name :accessor name)
       (rlist :col-type (or text db-null) :initarg :rlist :accessor rlist
              :col-export list-to-string :col-import string-to-list)
       (alist :col-type (or text db-null) :initarg :alist :accessor alist
              :col-export list-to-string :col-import string-to-alist)
       (plist :col-type (or text db-null) :initarg :plist :accessor plist
              :col-export list-to-string :col-import string-to-plist))
      (:metaclass dao-class)
      (:table-name listy))

Now we need to define the import functions. When writing your import functions, pay attention to how you want to handle nil or :NULL values as well as how you might want to error check the conversion from a Postgresql datatype to a CL datatype.

    (defun string-to-list (str)
      "Take a string representation of a list and return a lisp list.
    Note that you need to handle :NULLs."
      (cond ((eq str :NULL)
             :NULL)
            (str
             (with-input-from-string (s str) (read s)))
            (t nil)))

And now we need to define the export function. In our example we are just going to be using format to write the CL value to a string. You are responsible for writing an export function that does what you need. This example just tells Postgresql to insert a string "unknown" if the slot value is not a list. You would need more error checking and condition handling.

    (defun list-to-string (val)
      "Simply uses (format ..) to write a list out as a string"
      (if (listp val)
          (format nil "~a" val)
          "unknown"))

Changelog v. 1.33.0

This version of Postmodern now provides the ability to pass parameters to Postgresql in binary format IF that format is available for that datatype. Currently this means int2, int4, int8, float, double-float (except clisp) and boolean. Rational numbers continue to be passed as text.

The flag is set in the database connection object. (Thank you Cyrus Harmon for suggesting that). This means it can be set either in the initial connection to the database or using the use-binary-parameters function to set it after the initial connection has been established. If you are using multiple connections, some can be set to use binary parameters, some not.

If a query to Postgresql does not have a table column which would allow Postgresql to determine the correct datatype and you do not specify differently, Postgresql will treat the parameters passed with the query as text. The default text setting with results:

    (query "select $1" 1 :single)
    "1"
    (query "select $1" 1.5 :single)
    "1.5"
    (query "select $1" T :single)
    "true"
    (query "select $1" nil :single)
    "false"
    (query "select $1" :NULL :single)
    :NULL

You can specify parameter type as so:

    (query "select $1::integer" 1 :single)
    1

Setting the use-binary slot in the database connection object to t has the following results:

    (query "select $1" 1 :single)
    1
    (query "select $1" 1.5 :single)
    1.5
    (query "select $1" T :single)
    T
    (query "select $1" nil :single)
    NIL
    (query "select $1" :NULL :single)
    :NULL

The default for cl-postgres/Postmodern is to continue to pass parameters to Postgresql as text (not in binary format) in order to avoid breaking existing user code. If you want to pass parameters to Postgresql in binary format and want to set that up when you are making the database connection, the following examples may help. We continue the difference in the signatures (cl-postgres using optional parameters and postmodern using keyword parameters) because of the expected downstream breakage if we shifted cl-postgres:open-database to using keyword parameters.

The signature for opening a database in cl-postgres:

    (defun open-database (database user password host
                          &optional (port 5432) (use-ssl :no)
                          (service "postgres") (application-name "")
                          (use-binary nil))
        ...)

or your favorite macro.

In postmodern you have the connect function or the with-connection macro:

    (defun connect (database-name user-name password host
                    &key (port 5432) pooled-p
                    (use-ssl *default-use-ssl*)
                    (use-binary nil)
                    (service "postgres")
                    (application-name ""))
      ...)

    (defmacro with-connection (spec &body body)
      `(let ((*database* (apply #'connect ,spec)))
         (unwind-protect (progn ,@body)
           (disconnect *database*))))

In any case, you can set the flag after the connection is established with the use-binary-parameters function:

    (pomo:use-binary-parameters *database* t)

    (cl-postgres:use-binary-parameters some-database-connection t)

Using binary parameters does tighten type checking when using prepared queries. You will not be able to use prepared queries with varying formats. In other words, if you have a prepared query that you pass an integer as the first parameter and a string as the second parameter the first time it is used, any subsequent uses of that prepared query during that session will also have to pass an integer as the first parameter and a string as the second parameter.

Benchmarking does indicate a slight speed and consing benefit to passing parameters as binary, but your mileage will vary depending on your use case.

In addition, this version also adds the ability to have queries returned as vectors of vectors, using a vectors keyword.

    (query "select id, some_int, some_text from tests_data :where id = 1" :vectors)
;; or
    (query (:select 'id 'some-int 'some-text :from 'test-data)
         :vectors)
    #(#(1 2147483645 "text one")
      #(2 0 "text two")
      #(3 3 "text three"))

Like :array-hash, if there is no result it will return an empty array, not nil.

Changelog v. 1.32.9

Adds new utility functions

  • table-description-menu which allows you to pick and choose what table characteristics you want returned. See giant docstring for details.

  • get-schema-comment which takes a schema name and returns the schema comment as a string

  • list-check-constraints which takes a fully qualified table name and returns a list of lists of check constraints where each sublist has the form of (check-constraint-name check).

Example: (list-check-constraints "s2.employees") (("employees_birth_date_check" "CHECK (birth_date > '1900-01-01'::date)") ("employees_check" "CHECK (start_date > birth_date)") ("employees_salary_check" "CHECK (salary > 0::numeric)"))

Now exports get-column-comments (the parameter string has changed if you were using the internal version) get-all-table-comments

Bug Fixes:

Fixes a bug when trying to connect to a database using ssl. If the keyword :try was used, the connection would not fall back to non-ssl connections.

Changelog v. 1.32.8

S-SQL Enhancements

:Update

without the :columns parameter, :update requires alternating column value like so:

    (query (:update 'weather
            :set 'temp-lo (:+ 'temp-lo 1)
                 'temp-hi (:+ 'temp-lo 15)
                 'prcp :default
            :where (:and (:= 'city "San Francisco")
                         (:= 'date "2003-07-03"))
            :returning 'temp-lo 'temp-hi 'prcp))

:update now accepts a :columns parameter. This allows the use of either :set or :select (both of which need to be enclosed in a form) to provide the values, allowing update queries like:

    (query (:update 'weather
            :columns 'temp-lo 'temp-hi 'prcp
                     (:set (:+ 'temp-lo 1)  (:+ 'temp-lo 15) :DEFAULT)
            :where (:and (:= 'city "San Francisco")
                         (:= 'date "2003-07-03"))))

    (query (:update 't1
            :columns 'database-name 'encoding
                     (:select 'x.datname 'x.encoding
                     :from (:as 'pg-database 'x)
                     :where (:= 'x.oid 't1.oid))))

:Insert-into

Insert-into also now accepts a :columns parameter which allows more precise use of select to insert values into specific row(s). A sample query could look like:

    (query (:insert-into 't11
            :columns 'region 'subregion 'country
            (:select (:as 'region-name 'region)
                     (:as 'sub-region-name 'subregion)
                     'country
             :from 'regions)))

Joins

Lateral Joins

Joins are now expanded to include lateral joins. So addition join types are

  • :join-lateral (best practice is still to be specific on what kind of join you want)
  • :left-join-lateral
  • :right-join-lateral
  • :inner-join-lateral
  • :outer-join-lateral
  • :cross-join-lateral

Ordinality

Selects can now use :with-ordinality or :with-ordinality-as parameters. Postgresql will give the new ordinality column the name of ordinality. :with-ordinality-as allows you to set different names for the columns in the result set.

    (query (:select '*
            :from (:generate-series 4 1 -1)
            :with-ordinality))


    (query (:select 't1.*
            :from (:json-object-keys "{\"a1\":\"1\",\"a2\":\"2\",\"a3\":\"3\"}")
            :with-ordinality-as (:t1 'keys 'n)

New Utility copy-from-csv

Just a convenience function. It runs the psql copy command from inside lisp using uiop:run-program

Changelog v. 1.32.7

Additional capabilities for s-sql functions :insert-into and :insert-rows-into

Specifically, both can now use:

  • overriding-system-value
  • overriding-user-value
  • on-conflict-do-nothing
  • on-conflict
  • on-conflict-on-constraint
  • on-conflict-update
  • do-nothing
  • update-set
  • from
  • where
  • returning

See updated s-sql docs for examples.

Changelog v. 1.32.4

Added the ability to return results as json-encoded results as follows:

  • :Json-strs Return a list of strings where the row returned is a json object expressed as a string

    (query (:select 'id 'int4 'text :from 'short-data-type-tests :where (:< 'id 3)) :json-strs) ("{"id":1,"int4":2147483645,"text":"text one"}" "{"id":2,"int4":0,"text":"text two"}")

  • :Json-str Return a single string where the row returned is a json object expressed as a string

    (query (:select 'id 'int4 'text :from 'short-data-type-tests :where (:= 'id 3)) :json-str) "{"id":3,"int4":3,"text":"text three"}"

  • :Json-array-str Return a string containing a json array, each element in the array is a selected row expressed as a json object

    (query (:select 'id 'int4 'text :from 'short-data-type-tests :where (:< 'id 3)) :json-array-str) "[{"id":1,"int4":2147483645,"text":"text one"}, {"id":2,"int4":0,"text":"text two"}]"

Changelog v. 1.32.3

Added flag to avoid SSL certificate verification if required by user

Fix S-SQL issue 239 (:drop-table ...) expanded incorrectly

(:drop-table ...) can again use variable input Allowable permutations are:

(let ((table-var1 "table-1")
      (table-var2 'table-1))
  (query (:drop-table :if-exists "table_1"))

  (query (:drop-table :if-exists table-var1 :cascade))

  (query (:drop-table :if-exists "table-1" :cascade))

  (query (:drop-table :if-exists 'table-1 :cascade))

  (query (:drop-table :if-exists table-var2 :cascade))

  (query (:drop-table (:if-exists "table-1") :cascade))

  (query (:drop-table :if-exists table-var1))

  (query (:drop-table :if-exists "table-1"))

  (query (:drop-table :if-exists 'table-1))

  (query (:drop-table :if-exists table-var2))

  (query (:drop-table (:if-exists "table-1")))

  (query (:drop-table table-var1 :cascade))

  (query (:drop-table "table-1" :cascade))

  (query (:drop-table 'table-1 :cascade))

  (query (:drop-table table-var2 :cascade))

  (query (:drop-table  "table-1" :cascade))

  (query (:drop-table table-var1))

  (query (:drop-table "table-1"))

  (query (:drop-table 'table-1))

  (query (:drop-table table-var2))

  (query (:drop-table 'table-1))

  (query (:drop-table "table-1")))

Fix S-SQL issue 236 (:create-table ...) error on multiple attributes

The s-sql version of (:create-table ...) will now accept:

Basic table name permutations with :temp, :if-not-exists and :unlogged

(query (:create-table 'distributors-in-hell)

(query (:create-table "distributors-in_hell")

(query (:create-table (:temp 'distributors-in_hell))

(query (:create-table (:temp "distributors-in_hell"))

(query (:create-table (:temp :if-not-exists "distributors-in_hell"))

(query (:create-table (:temp :if-not-exists 'distributors-in-hell))

(query (:create-table (:temporary 'distributors-in-hell))

(query (:create-table (:temporary "distributors-in_hell"))

(query (:create-table (:temporary :if-not-exists 'distributors-in_hell))

(query (:create-table (:temporary :if-not-exists "distributors-in_hell"))

(query (:create-table (:unlogged 'distributors-in_hell))

(query (:create-table (:unlogged "distributors-in_hell"))

(query (:create-table (:unlogged :if-not-exists 'distributors-in_hell))

(query (:create-table (:unlogged :if-not-exists "distributors-in_hell"))

(query (:create-table (:temp :unlogged 'distributors-in_hell))

(query (:create-table (:temp :unlogged "distributors-in_hell"))

(query (:create-table (:temp :unlogged :if-not-exists "distributors-in_hell"))

(query (:create-table (:temp :unlogged :if-not-exists 'distributors-in-hell))

Expanding table names with composite types

(query (:create-table (:of distributors-in-hell 'employee-type))

(query (:create-table (:of distributors-in-hell "employee-type"))

(query (:create-table (:temp :of distributors-in-hell 'employee-type))

(query (:create-table (:temp :of distributors-in-hell "employee-type"))

(query (:create-table (:temp :unlogged :of distributors-in-hell 'employee-type))

(query (:create-table (:temp :unlogged :of distributors-in-hell "employee_type"))

(query (:create-table (:temp :if-not-exists :of distributors-in-hell 'employee-type))

(query (:create-table (:temp :if-not-exists :of distributors-in-hell "employee-type"))

(query (:create-table (:temp :unlogged :if-not-exists :of distributors-in-hell 'employee-type))

(query (:create-table (:temp :unlogged :if-not-exists :of distributors-in-hell "employee_type"))

(query (:create-table (:unlogged :of distributors-in-hell 'employee-type))

(query (:create-table (:unlogged :of distributors-in-hell "employee_type"))

Expanding table names with wrapping the table name in a sublist with :if-not-exists

(query (:create-table (:temp (:if-not-exists "distributors-in_hell")))

(query (:create-table (:temp (:if-not-exists 'distributors-in-hell)))

(query (:create-table (:temporary (:if-not-exists 'distributors-in_hell)))

(query (:create-table (:temporary (:if-not-exists "distributors-in_hell")))

(query (:create-table (:unlogged (:if-not-exists 'distributors-in_hell)))

(query (:create-table (:unlogged (:if-not-exists "distributors-in_hell")))

(query (:create-table (:temp :unlogged (:if-not-exists "distributors-in_hell")))

(query (:create-table (:temp :unlogged (:if-not-exists 'distributors-in-hell)))

Note: (:create-table ...) does not accept variables as the table name

Some additional tests, and small formatting and documentation changes

Changelog v. 1.32

Highlights

  • Daos now have the ability to specify columns as identity, unique, references, primary key or check.

  • New create-database and create-role functions make it easier to set readonly or edit-only permissions on databases, schemas or tables.

  • More documentation on connections (toplevel v. with-connection), dao-utilitization and more examples for s-sql

New Functionality for DAOs

Daos now have the ability to specify columns as identity, unique, references, primary key or check.

As an example:

 (defclass country ()
   ((id :col-type integer :col-identity t :accessor id)
    (name :col-type string :col-unique t :check (:<> 'name "")
          :initarg :name :reader country-name)
    (inhabitants :col-type integer :initarg :inhabitants
                 :accessor country-inhabitants)
    (sovereign :col-type (or db-null string) :initarg :sovereign
               :accessor country-sovereign)
    (region-id :col-type integer :col-references ((regions id))
               :initarg :region-id :accessor region-id))
   (:metaclass dao-class)
   (:table-name countries))

In this example we have an id column which is specified to be an identity column. Postgresql will automatically generate a sequence of of integers and this will be the primary key.

We have a name column which is specified as unique and is not null and the check will ensure that the database refuses to accept an empty string as the name.

We have a region-id column which references the id column in the regions table. This is a foreign key constraint and Postgresql will not accept inserting a country into the database unless there is an existing region with an id that matches this number. Postgresql will also not allow deleting a region if there are countries that reference that region's id. If we wanted Postgresql to delete countries when regions are deleted, that column would be specified as:

 (region-id :col-type integer :col-references ((regions id) :cascade)
   :initarg :region-id :accessor region-id)

Now you can see why the double parens.

New S-SQL Functionality

Fetch is a more efficient way to do pagination instead of using limit and offset. Fetch allows you to retrieve a limited set of rows, optionally offset by a specified number of rows. In order to ensure this works correctly, you should use the order-by clause. If the amount is not provided, it assumes you only want to return 1 row. https://www.postgresql.org/docs/current/sql-select.html Examples:

 (query (:fetch (:order-by (:select 'id :from 'historical-events) 'id) 5))

 ((1) (2) (3) (4) (5))

 (query (:fetch (:order-by (:select 'id :from 'historical-events) 'id) 5 10))

 ((11) (12) (13) (14) (15))

New Postmodern Functions

  • function get-database-comment (database-name)

    Returns the comment, if any, attached to a database.

  • function change-toplevel-database (new-database user password host)

    Just changes the database assuming you are using a toplevel connection. Recommended only for development work. Returns the name of the newly connected database as a string.

database-management

  • function create-database (database-name &key (encoding "UTF8") (connection-limit -1) owner limit-public-access comment collation template)

    Creates and initializes a database. Besides the obvious database-name parameter, you can also use key parameters to set encoding (defaults to UTF8), owner, connection-limit (defaults to no limit)). If limit-public-access is set to t, then only superuser roles or roles with explicit access to this database will be able to access it. If collation is set, the assumption is that template0 needs to be used as the base of the database rather than template1 which may contain encoding specific or locale specific data.

  • function list-templates ()

    Returns a list of existing database template names.

  • function list-available-collations ()

    Get a list of the collations available from the current database cluster. Collations are a mess as different operating systems provide different collations. We might get some sanity if Postgresql can use ICU as the default. See https://wiki.postgresql.org/wiki/Collations.

  • function list-database-access-rights (&optional database-name)

    If the database parameter is specifed, this returns an list of lists where each sublist is a role name and whether they have access rights (t or nil) to that particular database. If the database-name is not provided, the sublist is a database name, a role name and whether they have access rights (t or nil). This excludes the template databases.

roles

  • function list-role-permissions (&optional role)

    This returns a list of sublists of the permissions granted within the currently connected database. If an optional role is provided, the result is limited to that role. The sublist returned will be in the form of role-name, schema-name, table-name and then a string containing all the rights of that role on that table in that schema.

  • function create-role (name password &key (base-role :readonly) (schema :public) (tables :all) (databases :current) (allow-whitespace nil) (allow-utf8 nil) (allow-disallowed-names nil) (comment nil))

    Keyword parameters: Base-role. Base-role should be one of :readonly, :editor, :admin, :standard or :superuser. A readonly user can only select existing data in the specified tables or databases. An editor has the ability to insert, update, delete or select data. An admin has all privileges on a database, but cannot create new databases, roles, or replicate the system. A standard user has no particular privileges other than connecting to databases.

    :schema defaults to :public but can be a list of schemas. User will not have access to any schemas not in the list.

    :tables defaults to :all but can be a list of tables. User will not have access to any tables not in the list.

    :databases defaults to :current but can be a list of databases. User will not have access to any databases not in the list.

    :allow-whitespace - Whitespace in either the name or password is not allowed by default.

    :allow-utf8 defaults to nil. If t, the name and password will be normalized. If nil, the name and password are limited to printable ascii characters. For fun reading on utf8 user names see https://labs.spotify.com/2013/06/18/creative-usernames. Also interesting reading is https://github.com/flurdy/bad_usernames and https://github.com/dsignr/disallowed-usernames/blob/master/disallowed%20usernames.csv, and https://www.b-list.org/weblog/2018/feb/11/usernames/

    :allow-disallowed-names defaults to nil. If nil, the user name will be checked against *disallowed-role-names*.

    As an aside, if allowing utf8 in names, you might want to think about whether you should create second copy of the username in the original casing and normalized as NFC for display purposes as opposed to normalizing to NFKC. It might be viewed as culturally insensitive to change the display of the name.

  • function alter-role-search-path (role search-path)

    Changes the priority of where a role looks for tables (which schema first, second, etc. Role should be a string or symbol. Search-path could be a list of schema names either as strings or symbols.

  • function change-password (role password &optional expiration-date)

    Alters a role's password. If the optional expiration-date parameter is provided, the password will expire at the stated date. A sample expiration date would be 'December 31, 2020'. If the expiration date is 'infinity', it will never expire. The password will be encrypted in the system catalogs. This is automatic with postgresql versions 10 and above.

  • function grant-role-permissions (role-type name &key (schema :public) (tables :all) (databases :all))

    Grant-role-permissions assumes that a role has already been created, but permissions need to be granted or revoked on a particular database.

    A :superuser can create databases, roles, replication, etc. Returns nil. A :standard user has no particular privileges or restrictions. Returns nil. An :admin user can edit existing data, insert new data and create new tables in the specified databases/schemas/tables. An :editor user can update fields or insert new records but cannot create new tables in the specified tables or databases. A :readonly role can only read existing data in the specified schemas, tables or databases. Schema, tables or databases can be :all or a list of schemas, tables or databases to be granted permission.

    Granting :all provides access to all future items of that type as well.

    Note that the schema and table rights and revocations granted are limited to the connected database at the time of execution of this function.

  • function grant-readonly-permissions (schema-name role-name &optional (table-name nil))

    Grants select privileges to a role for the named schema. If the optional table-name parameter is provided, the privileges are only granted with respect to that table. Note that we are giving some function execute permissions if table-name is nil, but if the table-name is specified, those are not provided. Your mileage may vary on how many privileges you want to provide to a read-only role with access to only a limited number of tables.

  • function grant-editor-permissions (schema-name role-name &optional (table-name nil))

    Grants select, insert, update and delete privileges to a role for the named schema. If the optional table-name parameter is provided, the privileges are only granted with respect to that table. Note that we are giving some function execute permissions if table-name is nil, but if the table-name is specified, those are not provided. Your mileage may vary on how many privileges you want to provide to a editor role with access to only a limited number of tables.

  • function grant-admin-permissions (schema-name role-name &optional (table-name nil))

    Grants all privileges to a role for the named schema. If the optional table-name parameter is provided, the privileges are only granted with respect to that table.

  • function revoke-all-on-table (table-name role-name)

    Takes a table-name which could be a string, symbol or list of strings or symbols of tables names, a role name and revokes all privileges that role-name may have with that/those tables. This is limited to the currently connected database and can only revoke the privileges granted by the caller of the function.

  • function list-role-accessible-databases (role-name)

    Returns a list of the databases to which the specified role can connect.

tables

  • function get-table-comment (table-name &optional schema-name)

    Retrieves the comment, if any attached to the table

general utilities

  • function add-comment (type name comment &optional (second-name ""))

    Attempts to add a comment to a particular database object. The first parameter is a keyword for the type of database object. The second parameter is the name of the object. The third parameter is the comment itself. Some objects require an additional identifier. The names can be strings or symbols.

    Example usage would be:

    (add-comment :database 'my-database-name "Does anyone actually use this database?".)

(add-comment :column 'country-locations.name "Is what it looks like - the name of a country".)

(add-comment :column "country_locations.name" "Is what it looks like - the name of a country".)

    Example usage where two identifiers are required would be constraints:

    (add-comment :constraint 'constraint1  "Some kind of constraint descriptions here".
             'country-locations)

  • function postgres-array-string-to-list (str)

    Takes a postgresql array in the form of a string like "{sabra=CTc/sabra,a=c/sabra,b=c/sabra}"

    and returns a lisp list like

    ("sabra=CTc/sabra" "a=c/sabra" "b=c/sabra")

  • function postgres-array-string-to-array (str)

    Same thing but returns an array