Multiple forms for sql operator

[Symbolics]

I am trying to create a function for both GreenPlum and Postgres. Greenplum has an extended set of role attributes that I wish to list if the DB is GP. Ideally, the function would look like this:

(defun list-database-roles (&key (gp nil))
  "Returns list of roles and attributes."
  (query (:order-by
    (:select (:as 'r.rolname 'role-name)
           (:as 'r.rolsuper 'super-user-p)
           (:as 'r.rolinherit 'role-inherit-p)
           (:as 'r.rolcreaterole 'create-role-p)
           (:as 'r.rolcreatedb 'create-db-p)
           (:as 'r.rolcanlogin 'loginp)
           ; (:as 'r.rolreplication 'replicationp) ;postgresql 9.3+
           ; (:as 'r.rolbypassrls 'bypass-row-level-security-p) ;postgresql 9.5+
           (:as 'r.rolconnlimit 'connection-limit)
           (:raw (if gp
                 (sql
                   (:as 'r.rolcreaterextgpfd 'create-readable-external-gpfd-table-p)
                   (:as 'r.rolcreatewextgpfd 'create-writable-external-gpfd-table-p)
                   (:as 'r.rolcreaterexthttp 'create-readable-external-http-table-p)
                   (:as 'r.rolcreaterexthdfs 'create-readable-external-hdfs-table-p)
                   (:as 'r.rolcreatewexthdfs 'create-writable-external-hdfs-table-p))))
           :from (:as 'pg_catalog.pg_roles 'r))
      1)))

However the sql operator takes only a single form. I could use a pattern like:

(:raw (if gp (sql (:as role-attribute-1...
(:raw (if gp (sql (:as role-attribute-2...

but that is a bit clunky. Does anyone know a better way?

Just wondering too: what happened to the mailing list? Seem this question would be a good candidate for a mailing list if it were still around.

[sabracrolleton]

Will think about this. The mailing list still exists at https://mailman.common-lisp.net/listinfo/postmodern-devel

[sabracrolleton]

The problem is that sql is a macro, not a function. We could edit sql-expand and sql-expand-list to not insert a comma when provided an empty string. In the absence of that, I think the function would look like:

(defun list-database-roles (&key (gp nil))
  "Returns list of roles and attributes."
  (if gp
      (query (:order-by
           (:select (:as 'r.rolname 'role-name)
                    (:as 'r.rolsuper 'super-user-p)
                    (:as 'r.rolinherit 'role-inherit-p)
                    (:as 'r.rolcreaterole 'create-role-p)
                    (:as 'r.rolcreatedb 'create-db-p)
                    (:as 'r.rolcanlogin 'loginp)
                                        ; (:as 'r.rolreplication 'replicationp) ;postgresql 9.3+
                                        ; (:as 'r.rolbypassrls 'bypass-row-level-security-p) ;postgresql 9.5+
                    (:as 'r.rolconnlimit 'connection-limit)
                    (:raw (s-sql::sql-expand-list
                           (list (sql (:as 'r.rolcreaterextgpfd 'create-readable-external-gpfd-table-p))
                                 (sql (:as 'r.rolcreatewextgpfd 'create-writable-external-gpfd-table-p))
                                 (sql (:as 'r.rolcreaterexthttp 'create-readable-external-http-table-p))
                                 (sql (:as 'r.rolcreaterexthdfs 'create-readable-external-hdfs-table-p))
                                 (sql (:as 'r.rolcreatewexthdfs 'create-writable-external-hdfs-table-p)))))
                    :from (:as 'pg_catalog.pg_roles 'r))
           1))
    (query (:order-by
           (:select (:as 'r.rolname 'role-name)
                    (:as 'r.rolsuper 'super-user-p)
                    (:as 'r.rolinherit 'role-inherit-p)
                    (:as 'r.rolcreaterole 'create-role-p)
                    (:as 'r.rolcreatedb 'create-db-p)
                    (:as 'r.rolcanlogin 'loginp)
                                        ; (:as 'r.rolreplication 'replicationp) ;postgresql 9.3+
                                        ; (:as 'r.rolbypassrls 'bypass-row-level-security-p) ;postgresql 9.5+
                    (:as 'r.rolconnlimit 'connection-limit)
                    :from (:as 'pg_catalog.pg_roles 'r))
           1))))

[Symbolics]

I see. I was hoping for a way to conditionalise particular lines of code. Otherwise, even with this example, there are many combinations of implementations and version. The function would likely be several pages long if all were catered for.

[sabracrolleton]

That would be a good enhancement. I have something in testing that would get you to: either

(defun list-database-roles (&key (gp nil))
  "Returns list of roles and attributes."
  (query (:order-by
          (:select (:as 'r.rolname 'role-name)
                   (:as 'r.rolsuper 'super-user-p)
                   (:as 'r.rolinherit 'role-inherit-p)
                   (:as 'r.rolcreaterole 'create-role-p)
                   (:as 'r.rolcreatedb 'create-db-p)
                   (:as 'r.rolcanlogin 'loginp)
                                        ; (:as 'r.rolreplication 'replicationp) ;postgresql 9.3+
                                        ; (:as 'r.rolbypassrls 'bypass-row-level-security-p) ;postgresql 9.5+
                   (:as 'r.rolconnlimit 'connection-limit)
                   (when gp (s-sql::sql-expand-list
                          (list (sql (:as 'r.rolcreaterextgpfd 'create-readable-external-gpfd-table-p))
                                (sql (:as 'r.rolcreatewextgpfd 'create-writable-external-gpfd-table-p))
                                (sql (:as 'r.rolcreaterexthttp 'create-readable-external-http-table-p))
                                (sql (:as 'r.rolcreaterexthdfs 'create-readable-external-hdfs-table-p))
                                (sql (:as 'r.rolcreatewexthdfs 'create-writable-external-hdfs-table-p)))))
                   :from (:as 'pg_catalog.pg_roles 'r))
          1)))

or

(defun list-database-roles (&key (gp nil))
  "Returns list of roles and attributes."
  (query (:order-by
          (:select (:as 'r.rolname 'role-name)
                   (:as 'r.rolsuper 'super-user-p)
                   (:as 'r.rolinherit 'role-inherit-p)
                   (:as 'r.rolcreaterole 'create-role-p)
                   (:as 'r.rolcreatedb 'create-db-p)
                   (:as 'r.rolcanlogin 'loginp)
                                        ; (:as 'r.rolreplication 'replicationp) ;postgresql 9.3+
                                        ; (:as 'r.rolbypassrls 'bypass-row-level-security-p) ;postgresql 9.5+
                   (:as 'r.rolconnlimit 'connection-limit)
                   (when gp (sql (:as 'r.rolcreaterextgpfd 'create-readable-external-gpfd-table-p)))
                   (when gp (sql (:as 'r.rolcreatewextgpfd 'create-writable-external-gpfd-table-p)))
                   (when gp (sql (:as 'r.rolcreaterexthttp 'create-readable-external-http-table-p)))
                   (when gp (sql (:as 'r.rolcreaterexthdfs 'create-readable-external-hdfs-table-p)))
                   (when gp (sql (:as 'r.rolcreatewexthdfs 'create-writable-external-hdfs-table-p)))
                   :from (:as 'pg_catalog.pg_roles 'r))
          1)))

Would that work?

[Symbolics]

The latter is probably slightly better because it is line by line. Both would work.

[sabracrolleton]

(defun list-database-roles (&key (gp nil))
  "Returns list of roles and attributes."
  (sql-compile
          (remove nil `(:select (:as 'r.rolname 'role-name)
                             (:as 'r.rolsuper 'super-user-p)
                             (:as 'r.rolinherit 'role-inherit-p)
                             (:as 'r.rolcreaterole 'create-role-p)
                             (:as 'r.rolcreatedb 'create-db-p)
                             (:as 'r.rolcanlogin 'loginp)
                             (:as 'r.rolconnlimit 'connection-limit)
                             ,(if gp '(:as 'r.rolcreaterextgpfd 'create-readable-external-gpfd-table-p) nil)
                             ,(if gp '(:as 'r.rolcreatewextgpfd 'create-writable-external-gpfd-table-p) nil)
                             ,(if gp '(:as 'r.rolcreaterexthttp 'create-readable-external-http-table-p) nil)
                             ,(if gp '(:as 'r.rolcreaterexthdfs 'create-readable-external-hdfs-table-p) nil)
                             ,(if gp '(:as 'r.rolcreatewexthdfs 'create-writable-external-hdfs-table-p) nil)
                             :from (:as 'pg_catalog.pg_roles 'r)))))

[sabracrolleton]

Different ways of generating dynamic queries are now documented in doc/dynamic-queries.html