S-SQL Reference Manual
This is the reference manual for the S-SQL component of the postmodern library.
S-SQL provides a lispy syntax for SQL queries, and knows how to convert various lisp types to their textual SQL representation. It takes care to do as much of the work as possible at compile-time, so that at runtime a string concatenation is all that is needed to produce the final SQL query.
Interface
macro sql (form)
→ string
Convert the given form (a list starting with a keyword) to an SQL query string at compile time, according to the rules described here. For example:
(sql (:select '* :from 'country :where (:= 'a 1))) "(SELECT * FROM country WHERE (a = 1))"
but
(sql '(:select '* :from 'country :where (:= 'a 1)))
would throw an error. For the later case you need to use sql-compile.
function sql-compile (form)
→ string
This is the run-time variant of the sql macro. It converts the given list to an SQL query, with the same rules except that symbols in this list do not have to be quoted to be interpreted as identifiers. For example:
(sql-compile '(:select '* :from 'country :where (:= 'a 1))) \"(SELECT * FROM country WHERE (a = 1))\"
but
(sql (:select '* :from 'country :where (:= 'a 1)))
would throw an error. For the later case you need to use sql.
function sql-template (form)
In cases where you do need to build the query at run time, yet you do not want to re-compile it all the time, this function can be used to compile it once and store the result. It takes an S-SQL form, which may contain \[ placeholder symbols, and returns a function that takes one argument for every \]. When called, this returned function produces an SQL string in which the placeholders have been replaced by the values of the arguments.
function enable-s-sql-syntax (&optional (char #\Q))
Modifies the current readtable to add a #Q syntax that is read as (sql …). The character to use can be overridden by passing an argument.
function sql-escape-string (string)
→ string
Escapes a string for inclusion in a PostgreSQL query. A quoted symbol will generate an error. Example:
(sql-escape-string \"Puss in 'Boots'\") \"E'Puss in ''Boots'''\"
method sql-escape (value)
→ string
A generalisation of sql-escape-string looks at the type of the value passed, and properly writes it out it for inclusion in an SQL query. Symbols will be converted to SQL names. Examples:
(sql-escape "tr'-x") "E'tr''-x'" (sql-escape (/ 1 13)) "0.0769230769230769230769230769230769230" (sql-escape #("Baden-Wurttemberg" "Bavaria" "Berlin" "Brandenburg")) "ARRAY[E'Baden-Wurttemberg', E'Bavaria', E'Berlin', E'Brandenburg']"
variable downcase-symbols
When converting symbols to strings, whether to downcase the symbols is set here. The default is to downcase symbols.
variable standard-sql-strings
Used to configure whether S-SQL will use standard SQL strings (just replace #\' with ''), or backslash-style escaping. Setting this to NIL is always safe, but when the server is configured to allow standard strings (compile-time parameter 'standard_conforming_strings' is 'on', which will become the default in future versions of PostgreSQL), the noise in queries can be reduced by setting this to T.
variable postgres-reserved-words hashtable
A set of all Postgresql's reserved words, for automatic escaping. Probably not a good idea to use these words as identifiers anyway. '("all" "analyse" "analyze" "and" "any" "array" "as" "asc" "asymmetric" "authorization" "between" "binary" "both" "case" "cast" "check" "collate" "column" "concurrently" "constraint" "create" "cross" "current-catalog" "current-date" "current-role" "current-schema" "current-time" "current-timestamp" "current-user" "default" "deferrable" "desc" "distinct" "do" "else" "end" "except" "false" "fetch" "filter" "for" "foreign" "freeze" "from" "full" "grant" "group" "having" "ilike" "in" "initially" "inner" "intersect" "into" "is" "isnull" "join" "lateral" "leading" "left" "like" "limit" "localtime" "localtimestamp" "natural" "new" "not" "notnull" "nowait" "null" "off" "offset" "old" "on" "only" "or" "order" "outer" "overlaps" "placing" "primary" "references" "returning" "right" "select" "session-user" "Share" "similar" "some" "symmetric" "table" "then" "to" "trailing" "true" "union" "unique" "user" "using" "variadic" "verbose" "when" "where" "window" "with"))
variable escape-sql-names-p
Determines whether double quotes are added around column, table, and ** function names in queries. Valid values:
- T, in which case every name is escaped,
- NIL, in which case no name is escape,
- :auto, which causes only reserved words to be escaped, or.
- :literal which is the same as :auto except it has added consequence in to-sql-name (see below).
The default value is :auto.
Be careful when binding this with let and such ― since a lot of SQL compilation tends to happen at compile-time, the result might not be what you expect. Mixed case sensitivity is not currently well supported. Postgresql itself will downcase unquoted identifiers. This will be revisited in the future if requested.
function sql-type-name (type)
→ string
Transform a lisp type into a string containing something SQL understands. Default is to just use the type symbol's name.
function to-sql-name (name &optional (escape-p escape-sql-names-p)(ignore-reserved-words nil)
→ string
Convert a symbol or string into a name that can be a sql table, column, or operation name. Add quotes when escape-p is true, or escape-p is :auto and the name contains reserved words. Quoted or delimited identifiers can be used by passing :literal as the value of escape-p. If escape-p is :literal, and the name is a string then the string is still escaped but the symbol or string is not downcased, regardless of the setting for downcase-symbols and the hyphen and forward slash characters are not replaced with underscores.
Ignore-reserved-words is only used internally for column names which are allowed to be reserved words, but it is not recommended.
function from-sql-name (string)
→ keyword
Convert a string that represents an SQL identifier to a keyword by uppercasing it and converting the underscores to dashes.
macro register-sql-operators (arity &rest names)
Define simple SQL operators. Arity is one of :unary (like 'not'), :unary-postfix (the operator comes after the operand), :n-ary (like '\+': the operator falls away when there is only one operand), :2+-ary (like '=', which is meaningless for one operand), or :n-or-unary (like '-', where the operator is kept in the unary case). After the arity may follow any number of operators, either just a keyword, in which case the downcased symbol name is used as the SQL operator, or a two-element list containing a keyword and a name string.
SQL Types
S-SQL knows the SQL equivalents to a number of Lisp types, and defines some extra types that can be used to denote other SQL types. The following table (yes, I know this table is duplicated on other pages) shows the correspondence:
| Lisp type | SQL type | Description |
|---|---|---|
| integer | smallint | -32,768 to +32,768 2-byte storage |
| integer | integer | -2147483648 to +2147483647 integer, 4-byte storage |
| integer | bigint | -9223372036854775808 to<PHONE_NUMBER>854775807 8-byte storage |
| (numeric X Y) | numeric(X, Y) | see discussion below |
| float, real | real | float, 6 decimal digit precision 4-byte storage |
| double-float | double-precision | float, 15 decimal digit precision 8-byte storage |
| string, text | text | variable length string, no limit specified |
| string | char(X) | char(length), blank-padded string, fixed storage length |
| string | varchar(X) | varchar(length), non-blank-padded string, variable storage |
| boolean | boolean | boolean, 'true'/'false', 1 byte |
| bytea | bytea | binary string which allows non-printable octets |
| date | date | date range: 4713 BC to 5874897 AD |
| interval | interval | time intervals |
| array | array | See Array-Notes |
Numeric and decimal are variable storage size numbers with user specified precision. Up to 131072 digits before the decimal point; up to 16383 digits after the decimal point. The syntax is numeric(precision, scale). Numeric columns with a specified scale will coerce input values to that scale. For more detail, see https://www.postgresql.org/docs/current/datatype-numeric.html
SQL Syntax
An S-SQL form is converted to a query through the following rules:
- Lists starting with a keyword are operators. They are expanded as described below if they are known, otherwise they are expanded in the standard way: operator(arguments, …)
- Quoted symbols or keywords are interpreted as names of columns or tables, and converted to strings with to-sql-name.
- Anything else is evaluated and the resulting Lisp value is converted to its textual SQL representation (or an error is raised when there is no rule for converting objects of this type). Self-quoting atoms may be converted to strings at compile-time.
sql-op :select (&rest args)
Creates a select query. The arguments are split on the keywords found among them. The group of arguments immediately after :select is interpreted as the expressions that should be selected. After this, an optional :distinct may follow, which will cause the query to only select distinct rows, or alternatively :distinct-on followed by a group of row names. Next comes the optional keyword :from, followed by at least one table name and then any number of join statements. Join statements start with one of :left-join, :right-join, :inner-join, :outer-join or :cross-join, then a table name or subquery, then the keyword :on or :using, if applicable, and then a form. A join can be preceded by :natural (leaving off the :on clause) to use a natural join. After the joins an optional :where followed by a single form may occur. And finally :group-by and :having can optionally be specified. The first takes any number of arguments, and the second only one. A couple of examples:
(query (:select 'item :distinct :from 'item-table :where (:= 'col1 "Albania"))) (query (:select (:+ 'field-1 100) 'field-5 :from (:as 'my-table 'x) :left-join 'your-table :on (:= 'x.field-2 'your-table.field-1) :where (:not-null 'a.field-3))) (query (:order-by (:select 'regions.name (:count 'regions.name) :from 'countries 'regions :where (:= 'regions.id 'countries.region-id) :group-by 'regions.name) 'regions.name)) (query (:select (:count 'c.id) 'r.name :from (:as 'countries 'c) :inner-join (:as 'regions 'r) :on (:= 'c.region-id 'r.id) :group-by 'r.name :having (:< (:count 'c.id) 10)))
Joins
Cross Join
From the postgresql documentation: "For every possible combination of rows from T1 and T2 (i.e., a Cartesian product), the joined table will contain a row consisting of all columns in T1 followed by all columns in T2. If the tables have N and M rows respectively, the joined table will have N * M rows."
(query (:select '* from 'employee :cross-join 'compensation))
Inner Join
An inner join looks at two tables and creates a new result consisting of the selected elements in the rows from the two tables that match the specified conditions. You can simplistically think of it as the intersection of the two sets. In reality, it is creating a new set consisting of certain elements of the intersecting rows. An inner join is the default and need not be specified.
A sample of standard sql using two inner joins to collect information from three tables could look like this:
(SELECT foo, bar, baz FROM (SELECT foo FROM x WHERE some-condition-here) AS tmp1 INNER JOIN (SELECT bar FROM x WHERE some-condition-here) AS tmp2 ON (tmp1.id = tmp2.id) INNER JOIN (SELECT baz FROM x WHERE some-condition-here) AS tmp3 ON (tmp2.id = tmp3.id))
The same query could be expressed in s-sql as:
(query (:select 'foo 'bar 'baz :from (:as (:select 'foo :from 'x :where 'x) 'tmp1) :inner-join (:as (:select 'bar :from 'x :where 'x) 'tmp2) :on (:= 'tmp1.id 'tmp2.id) :inner-join (:as (:select 'baz :from 'x :where 'x) 'tmp3) :on (:= 'tmp2.id 'tmp3.id)))
The pre-ansi shorthand example, using a countries and regions tables would look like this:
(query (:select 'countries.name :from 'countries 'regions :where (:and (:= 'countries.region-id 'regions.id) (:= 'regions.name "North America"))))
The full portable ansi version, using inner join would look like this.
(query (:select 'tmp1.name :from (:as (:select 'name 'region-id :from 'countries) 'tmp1) :inner-join (:as (:select 'id :from 'regions :where (:= 'name "North America")) 'tmp2) :on (:= 'tmp1.region-id 'tmp2.id)))
Outer Join
An outer join not only generates an inner join, it also joins the rows from one table that matches the conditions and adds null values for the joined columns from the second table (which obviously did not match the condition.) Under Postgresql, a "left join", "right join" or "full join" all imply an outer join.
A left join (or left outer join) looks at two tables, keeps the matched rows from both and the unmatched rows from the left table and drops the unmatched rows from the right table. A right outer join keeps the matched rows, the unmatched rows from the right table and drops the unmatched rows from the left table. A full outer join includes the rows that match from each table individually, with null values for the missing matching columns.
Left Join
Example: Here we assume two tables. A countries table and a many-to-many linking table named countries-topics. (There is an implicit third table named topics.) We are looking for records from the countries table which do not have a match in the countries-topics table. In other words, where do we have a note, but not matched it to a topic?
(query (:order-by (:select 'countries.id 'countries.name :distinct :from 'countries :left-join 'countries-topics :on (:= 'countries.id 'countries-topics.country-id) :where (:is-null 'countries-topics.country-id)) 'countries.id))
Here is a somewhat contrived example using a countries and regions table. We want to get the names of all the regions and also return the country names in one specified region. Assume that we only want the names of the countries in Central America, which happens to have a region-id of 3.
(query (:select 'tmp2.name 'tmp1.name :from (:as (:select 'id 'name :from 'regions) 'tmp2) :left-join (:as (:select 'name 'region-id :from 'countries :where (:= 'region-id 3)) 'tmp1) :on (:= 'tmp1.region-id 'tmp2.id)))
Defined Operators
The following operators are defined:
sql-op :+, :*, :%, :&, :|, :||, :and, :or, :=, :/, :!=, :<, :>, :<=, :>=, :^, :union, :union-all, :intersect, :intersect-all, :except, :except-all (&rest args)
These are expanded as infix operators. When meaningful, they allow more than two arguments. :- can also be used as a unary operator to negate a value. Note that the arguments to :union, :union-all, :intersect, and :except should be queries (:select forms).
Note that you'll have to escape pipe characters to enter them as keywords. S-SQL handles the empty keyword symbol (written :||) specially, and treats it like :\|\|, so that it can be written without escapes. With :\|, this doesn't work.
sql-op :or
(query (:select 'countries.name :from 'countries 'regions :where (:and (:or (:= 'regions.name "North America") (:= 'regions.name "Central America")) (:= 'regions.id 'countries.region-id))))
or using parameterized queries
(query (:select 'countries.name :from 'countries 'regions :where (:and (:or (:= 'regions.name '$1) (:= 'regions.name '$2)) (:= 'regions.id 'countries.region-id))) "North America" "Central America")
sql-op :intersect
Intersect produces a result contain rows that appear on all the sub-selects.
(query (:intersect (:select 'countries.name :from 'countries :where (:< 'latitude 16.44)) (:select 'countries.name :from 'countries 'regions :where (:and (:= 'region-id 'regions.id) (:= 'regions.name "Caribbean")))))
sql-op :union, :union-all
The union operation generally eliminates what it thinks are duplicate rows. The union-all operation preserves duplicate rows. The examples below use the union-all operator, but the syntax would be the same with union.
(query (:select 'id 'name :from (:as (:union-all (:select 'id 'name :from 'countries :where (:<= 'name "B" )) (:select 'id 'name :from 'countries :where (:>= 'name "V" ))) 'a))) (query (:select 'a.id 'a.name 'a.region :from (:as (:union-all (:select 'countries.id 'countries.name (:as 'regions.name 'region) :from 'countries 'regions :where (:and (:<= 'countries.name "B" ) (:= 'regions.id 'countries.region-id ))) (:select 'countries.id 'countries.name (:as 'regions.name 'region) :from 'countries 'regions :where (:and (:>= 'countries.name "V" ) (:= 'regions.id 'countries.region-id )))) 'a) :group-by 'a.id 'a.region 'a.name))
sql-op :except, :except-all
:except removes all matches. :except-all is slightly different. If the first select statement has two rows that match a single row in the second select statement, only one is removed.
(query (:except (:select 'id 'name :from 'countries :where (:like 'name "%New%")) (:select 'id 'name :from 'countries :where (:like 'name "%Zealand%")))) (query (:except-all (:select '* :from 'clients) (:select '* :from 'vips)))
sql-op :~, :not (arg)
Unary operators for bitwise and logical negation.
(query (:order-by (:select 'recommendedby (:count '*) :from 'cd.members :where (:not (:is-null 'recommendedby)) :group-by 'recommendedby) 'recommendedby))
sql-op :any, :any*
Any needs to be considered as a special case. Quoting Marijn Haverbeke here,"Postgres has both a function-call-style any and an infix any, and S-SQL's syntax doesn't allow them to be distinguished." As a result, postmodern has a regular :any sql-op and a :any* sql-op, which expand slightly differently.
In general, the any qualifier in an sql statement looks at a subquery and does a comparison against that subquery. Sticking with our countries table, we have latitude, longitude data for every country (I'm not sure whether my table pulled the capital cities or the geographic center) and some designated a region for each country, so we have a region-id that matches the primary key 'id' in a regions table.
Out of curiosity, let's determine which countries in "North America" have a longitude less than any country in "South America". The standard sql could look like this:
(query "select countries.name from countries,regions where regions.id=region_id and regions.name='North America' and longitude > any(select longitude from countries, regions where region_id = regions.id and regions.name='South America')") (("Bermuda") ("Greenland"))
This can be re-phrased in s-sql as
(query (:select 'countries.name :from 'countries 'regions :where (:and (:= 'regions.id 'region-id) (:= 'regions.name "North America") (:> 'longitude (:any (:select 'longitude :from 'countries 'regions :where (:and (:= 'regions.id 'region-id) (:= 'regions.name "South America")))))))) (("Bermuda") ("Greenland"))
Subselects work fine in both regular sql and s-sql. If you have already calculated your subselect and put it in a variable, that variable needs to be a vector and whether you should use the :any sql-op or the :any* sql-op depends on your phrasing. (Notice that the second variation has an asterisk). (If you try to use a list, you will trigger an error message that you cannot convert that into an sql literal.)
The SQL keyword ANY can be used in a parameterized sql statement if you provide it with a vector. The following two toy examples work in raw sql.
(query "select name from countries where id=any($1)" (vector 21 22)) (("Iceland") ("US")) (let ((toy-query (vector 21 22))) (query "select name from countries where id=any($1)" toy-query)) (("Iceland") ("US"))
Now using s-sql and keeping with the toy example, notice that using :any does not work, but using :any* does work.
(let ((toy-query (vector 21 22))) (query (:select 'name :from 'countries :where (:= 'id (:any '$1))) toy-query)) ; Evaluation aborted on #<CL-POSTGRES-ERROR:SYNTAX-ERROR-OR-ACCESS-VIOLATION {10030AF6A1}>. (let ((toy-query (vector 21 22))) (query (:select 'name :from 'countries :where (:= 'id (:any* '$1))) toy-query)) (("Iceland") ("US"))
Going back to our earlier example, remember that I said that unless you use a subselect, you need to provide a vector to :any or :any*. A standard query returns a list, not a vector. So you would need to coerce the variable into a vector before you pass it to :any*. See below as an example.
(let ((South-America (coerce (query (:select 'longitude :from 'countries 'regions :where (:and (:= 'regions.id 'region-id) (:= 'regions.name "South America"))) :column)) 'vector)) (query (:select 'countries.name :from 'countries 'regions :where (:and (:= 'regions.id 'region-id) (:= 'regions.name "North America") (:> 'longitude (:any* South-America)))))) (("Bermuda") ("Greenland"))
sql-op :function (name (&rest arg-types) return-type stability body)
Create a stored procedure. The argument and return types are interpreted as type names and not evaluated. Stability should be one of :immutable, :stable, or :volatile (see the PostgreSQL documentation). For example, a function that gets foobars by id:
(:function 'get-foobar (integer) foobar :stable (:select '* :from 'foobar :where (:= 'id '$1)))
sql-op :~, :~*, :!~, :!~* (string pattern)
Regular expression matching operators. The exclamation mark means 'does not match', the asterisk makes the match case-insensitive.
(query (:select (:regexp_match "foobarbequebaz" "bar.*que")) :single) #("barbeque") (query (:select (:regexp_match "foobarbequebaz" "bar.~que")) :single) :NULL (query (:select (:~ "foobarbequebaz" "bar.*que") ) :single) t (query (:select (:!~ "foobarbequebaz" "bar.*que") ) :single) nil (query (:select (:~ "foobarbequebaz" "barque") ) :single) nil (query (:select (:~ "foobarbequebaz" "barbeque") ) :single) t (query (:select (:~ "foobarBequebaz" "barbeque") ) :single) nil (query (:select (:~* "foobarBequebaz" "barbeque") ) :single) t (query (:select 'id 'text :from 'text-search :where (:~ 'text "sushi")))
sql-op :like, :ilike (string pattern)
Simple SQL string matching operators (:ilike is case-insensitive).
(query (:select 'id 'name :from 'countries :where (:like 'name "%New%")))
sql-op :desc (column)
Used to invert the meaning of an operator in an :order-by clause.
(query (:order-by (:select 'location 'time 'report :distinct-on 'location :from 'weather-reports) 'location (:desc 'time)))
sql-op :nulls-first, :nulls-last (column)
Used to determine where :null values appear in an :order-by clause.
sql-op :as (form name &rest fields)
Also known in some explanations as "alias". This assigns a name to a column or table in a :select form. When fields are given, they are added after the name, in parentheses. For example, (:as 'table1 't1 'foo 'bar) becomes table1 AS t1(foo, bar). When you need to specify types for the fields, you can do something like (:as 'table2 't2 ('foo integer)). Note that names are quoted, types are not (when using sql-compile or sql-template, you can leave out the quotes entirely).
(query (:select (:as 'countries.name 'country) (:as 'regions.name 'region) :from 'countries 'regions :where (:and (:= 'regions.id 'region-id) (:= 'regions.name "Central America"))) :alists) (((:COUNTRY . "Belize") (:REGION . "Central America")) ((:COUNTRY . "Costa Rica") (:REGION . "Central America")) ((:COUNTRY . "El Salvador") (:REGION . "Central America")) ((:COUNTRY . "Guatemala") (:REGION . "Central America")) ((:COUNTRY . "Panama") (:REGION . "Central America")) ((:COUNTRY . "Nicaragua") (:REGION . "Central America")))
The following uses aliases for both columns and tables in the from and inner-join clauses:
(query (:order-by (:select (:as 'recs.firstname 'firstname) (:as 'recs.surname 'surname) :distinct :from (:as 'cd.members 'mems) :inner-join (:as 'cd.members 'recs) :on (:= 'recs.memid 'mems.recommendedby)) 'surname 'firstname))
Note: Postmodern does not allow you to create an unescaped string alias. In other words, you cannot generate this:
"select sum(slots as "Total Slots" from cd.bookings"
without using :raw
sql-op :cast (query)
The CAST operator. Takes a query as an argument, and returns the result explicitly cast by postgresql to a specific type. Unlike :type, :cast can pass the type as a variable.
(query (:select (:cast (:as "20" 'integer))) :single) 20 (let ((type 'text)) (query (:select (:cast (:as "20" type))) :single)) "20" (let ((type 'integer)) (query (:select (:cast (:as "20" type))) :single)) 20 (query (:union (:select (:as 1 'real)) (:select (:cast (:as "2.2" 'real))))) ((1.0) (2.2))
sql-op :type (query)
Is similar to cast but uses the postgresql :: formating. Unlike cast it will not accept a variable as the type.
E.g.
(sql (:select (:as (:- (:type (:now) 'date) 'x) 'some-date) :from (:as (:generate-series 1 10) 'x))) "(SELECT (now()::DATE - x) AS some_date FROM generate_series(1, 10) AS x)"
sql-op :create-composite-type (type-name &rest args)
Creates a composite type with a type-name and two or more columns. E.g.
(query (:create-composite-type 'fullname (first-name text) (last-name text)))
sql-op :exists (query)
The EXISTS operator. Takes a query as an argument, and returns true or false depending on whether that query returns any rows. In the example below, it is applied to a subquery.
(query (:select 'id 'name :from 'regions :where (:exists (:select 'region-id :from 'countries :where (:and (:= 'countries.name "Costa Rica") (:= 'regions.id 'countries.region-id))))))
sql-op :is-null (arg)
Test whether a value is null.
(query (:select 'ta :from 'a :where (:not (:is-null 'ta))))
sql-op :not-null (arg)
Test whether a value is not null.
(query (:select 'ta :from 'a :where (:not-null 'ta)))
sql-op :in (value set)
Test whether a value is in a set of values.
(query (:select 'name :from 'countries :where (:in 'id (:set 20 21 23)))) (query (:select 'region 'product (:as (:sum 'quantity) 'product-units) (:as (:sum 'amount) 'product-sales) :from 'orders :where (:in 'region (:select 'region :from 'top-regions)) :group-by 'region 'product))
sql-op :set (&rest elements)
Denote a set of values. This operator has two interfaces. When the elements are known at compile-time, they can be given as multiple arguments to the operator. When they are not, a single argument that evaluates to a list should be used.
The following would be the syntax in postmodern sql where the set is a list. If you want to use a vector, then you need to use Any:
The following are equivalent
(query (:select 'name :from 'countries :where (:in 'id (:set 20 21 23)))) (let ((x (list 20 21 23))) (query (:select 'name :from 'countries :where (:in 'id (:set x))))) (query (:select 'name :from 'countries :where (:in 'id (:set (list 20 21 23)))))
However, the following will generate an error about inability to convert to an sql literal
(query (:select 'name :from 'countries :where (:in 'id (:set '(20 21 23)))))
Now with selecting a dao
(select-dao 'countries (:in 'id (:set (list 20 21 23
Now with selecting from a vector. Note both the use of any* and := instead of :in.
(let ((x (vector 20 21 23))) (query (:select 'name :from 'countries :where (:= 'id (:any* x)))))
Note that the responses will still come back in a list of lists
IMPORTANT: 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."
sql-op :array (query)
This is used when calling a select query into an array. See array-notes.html for more detailed notes on the use of arrays.
(query (:order-by (:select 'r.rolename (:as (:array (:select 'b.rolename :from (:as 'pg_catalog.pg-auth-members 'm) :inner-join (:as 'pg-catalog.pg-roles 'b) :on (:= 'm.roleid 'b.oid) :where (:= 'm.member 'r.oid ))) 'memberof) :from (:as 'pg-catalog.pg-roles 'r)) 1))
sql-op :array[] (&rest args)
This is the general operator for arrays. It also handles statements that include functions in the query such as (:+ 1 2), (:pi) in the array. See array-notes.html for more detailed notes on the use of arrays.
(query (:select (:array-prepend 1 (:array[] 2 3)))) ((#(1 2 3))) (query (:select (:array-prepend 1 (:array[] 2 3))) :single) #(1 2 3)
sql-op :[] (form start &optional end)
Dereference an array value. If end is provided, extract a slice of the array. Sample usage below, but also see array-notes.html for more detailed notes on the use of arrays.
(query (:select 'receipe-id (:[] 'tags 2 3) :from 'receipe-tags-array :where (:= 'receipe-id 3)))
sql-op :extract (unit form)
Extract a field from a date/time value. For example, (:extract :month (:now)).
(query (:order-by (:select 'facid (:as (:extract 'month 'starttime) 'month) (:as (:sum 'slots) 'total-slots) :from 'cd.bookings :where (:and (:>= 'starttime "2012-01-01") (:< 'starttime "2013-01-01")) :group-by 'facid 'month) 'facid 'month))
sql-op :case (&rest clauses)
A conditional expression. Clauses should take the form (test value). If test is :else, an ELSE clause will be generated.
(query (:select 'name (:as (:case ((:> 'monthlymaintenance 100) "expensive") (:else "cheap")) 'cost) :from 'cd.facilities))
sql-op :between (n start end)
Test whether a value lies between two other values.
(query (:select 'name :from 'countries :where (:between 'latitude -10 10)) :column)
sql-op :between-symmetric (n start end)
Works like :between, except that the start value is not required to be less than the end value.
sql-op :dot (&rest names)
Can be used to combine multiple names into a name of the form A.B to refer to a column in a table, or a table in a schema. Note that you can also just use a symbol with a dot in it.
sql-op :type (form type)
Add a type declaration to a value, as in in "4.3::real". The second argument is not evaluated normally, but put through sql-type-name to get a type identifier.
(query (:select (:type 1.0 int)))
sql-op :raw (string)
Insert a string as-is into the query. This can be useful for doing things that the syntax does not support, or to re-use parts of a query across multiple queries:
(let* ((test (sql (:and (:= 'foo 22) (:not-null 'bar)))) (rows (query (:select '* :from 'baz :where (:raw test))))) (query (:delete-from 'baz :where (:raw test))) (do-stuff rows))
sql-op :fetch (form amount &optional offset)
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))
sql-op :limit (query amount &optional offset)
In S-SQL limit is not part of the select operator, but an extra operator that is applied to a query (this works out better when limiting the union or intersection of multiple queries, same for sorting). It limits the number of results to the amount given as the second argument, and optionally offsets the result by the amount given as the third argument.
(query (:limit (:order-by (:select 'surname :distinct :from 'cd.members) 'surname) 10))
sql-op :order-by (query &rest exprs)
Order the results of a query by the given expressions. See :desc for when you want to invert an ordering. Note: This is not the same as passing an :order-by parameter to an aggregation operator. For that see Aggregation Operators.
(query (:order-by (:select 'id 'name 'city 'salary (:every (:like 'name "J%")) :from 'employee :group-by 'name 'id 'salary 'city) 'name))
sql-op :values
Values computes a row value or set of row values for use in a specific query. See the postgresql docs at: https://www.postgresql.org/docs/current/static/queries-values.html and https://www.postgresql.org/docs/current/static/sql-values.html Example:
(query (:select '* :from (:as (:values (:set 1 "one") (:set 2 "two") (:set 3 "three")) (:t1 'num 'letter)))) (query (:select 'a 'b 'c (:cast (:as (:* 50 (:random)) 'int)) :from (:as (:values (:set "a") (:set "b")) (:d1 'a)) (:as (:values (:set "c") (:set "d")) (:d2 'b)) (:as (:values (:set "e") (:set "f")) (:d3 'c)))) (query (:with-recursive (:as (:t1 'n) (:union-all (:values (:set 1)) (:select (:+ 'n 1) :from 't1 :where (:< 'n 100)))) (:select (:sum 'n) :from 't1)) :single)
sql-op :empty-set
This is a fudge. It returns a string "()" where something like '() would return "false" or :() would throw an error. Example:
(query (:select 'appnumber 'day (:sum 'inserts) (:sum 'updates) (:sum 'deletes) (:sum 'transactions) :from 'db-details :group-by (:grouping-sets (:set 'appnumber 'day (:empty-set)))))
sql-op :group-by
https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-GROUPING-SETS The GROUP BY Clause is used to group together those rows in a table that have the same values in all the columns listed. The order in which the columns are listed does not matter. The effect is to combine each set of rows having common values into one group row that represents all rows in the group. This is done to eliminate redundancy in the output and/or compute aggregates that apply to these groups. Example:
(query (:order-by (:select 'mems.surname 'mems.firstname 'mems.memid (:as (:min 'bks.starttime) 'starttime) :from (:as 'cd.bookings 'bks) :inner-join (:as 'cd.members 'mems) :on (:= 'mems.memid 'bks.memid) :where (:>= 'starttime "2012-09-01") :group-by 'mems.surname 'mems.firstname 'mems.memid) 'mems.memid))
sql-op :grouping-sets
https://www.postgresql.org/docs/current/static/queries-table-expressions.html#QUERIES-GROUPING-SETS More complex grouping operations are possible using the concept of grouping sets. The data selected by the FROM and WHERE clauses is grouped separately by each specified grouping set, aggregates computed for each group just as for simple GROUP BY clauses, and then the results returned. This operator requires postgresql 9.5 or later. For example:
(query (:select 'city (:as (:extract 'year 'start-date) 'joining-year) (:as (:count 1) 'employee_count) :from 'employee :group-by (:grouping-sets (:set 'city (:extract 'year 'start-date)))))
Time, Date and Interval Operators
sql-op :interval (arg)
Creates an interval data type, generally represented in postmodern as an alist
sql-op :make-interval (&rest args)
Takes lists of (time-unit value) and returns a timestamp type. Example:
(query (:select (:make-interval ("days" 4) ("hours" 10) ("secs" 1.2))) :single)
Aggregation Operators
sql-op :count (&rest args)
Count returns the number of rows for which the expression is not null. It can be the number of rows collected by the select statement as in:
(query (:select (:count '*) :from 'table1 :where (:= 'price 100)))
or it can be a smaller number of rows based on the allowed keyword parameters :distinct and :filter or some other type of condition as in:
(query (:select (:count 'memid :distinct) :from 'cd.bookings))
or
(query (:select (:as (:count '* :distinct) 'unfiltered) (:as (:count '* :filter (:= 1 'bid)) 'filtered) :from 'testtable))
Note that if used, the filter must be last in the count args. If distinct is used, it must come before filter. Unlike standard sql, the word 'where' is not used inside the filter clause. E.g.
(query (:select (:count '*) (:count '* :filter (:= 1 'bid)) 'id :from 'pbbench-history))
See tests.lisp for examples.
sql-op :avg (&rest rest args)
Avg calculates the average value of a list of values. Note that if the filter keyword is used, the filter must be last in the avg args. If distinct is used, it must come before filter. E.g. See tests.lisp for more examples.
(query (:select (:avg '*) (:avg '* :filter (:= 1 'bid)) 'id :from 'pbbench-history))
sql-op :sum (&rest rest args)
Sum calculates the total of a list of values. Note that if the keyword filter is used, the filter must be last in the sum args. If distinct is used, it must come before filter. Unlike standard sql, the word 'where' is not used inside the filter clause (s-sql will properly expand it). See tests.lisp for more examples.
(query (:select (:sum '*) (:sum '* :filter (:= 1 'bid)) 'id :from 'pbbench-history))
sql-op ::max (&rest args)
max returns the maximum value of a set of values. Note that if the filter keyword is used, the filter must be last in the max args. If distinct is used, it must come before filter. Unlike standard sql, the word 'where' is not used inside the filter clause (s-sql will properly expand it). See tests.lisp for more examples.
(query (:select (:max '*) (:max '* :filter (:= 1 'bid)) 'id :from 'pbbench-history))
sql-op ::min (&rest args)
min returns the minimum value of a set of values. Note that if the filter keyword is used, the filter must be last in the min args. If distinct is used, it must come before filter. Unlike standard sql, the word 'where' is not used inside the filter clause (s-sql will properly expand it). See tests.lisp for more examples.
(query (:select (:min '*) (:min '* :filter (:= 1 'bid)) 'id :from 'pbbench-history))
sql-op ::every (&rest args)
Every returns true if all input values are true, otherwise false. Note that if the filter keyword is used, the filter must be last in the every args. If distinct is used, it must come before filter. Unlike standard sql, the word 'where' is not used inside the filter clause (s-sql will properly expand it). See tests.lisp for more examples.
(query (:select '* (:every (:like 'studname "%h")) :from 'tbl-students :group-by 'studname 'studid 'studgrades))
sql-op :percentile-cont (&rest args)
Requires Postgresql 9.4 or higher. Percentile-cont returns a value corresponding to the specified fraction in the ordering, interpolating between adjacent input items if needed. There are two required keyword parameters :fraction and :order-by. If the fraction value is an array, then it returns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the value corresponding to that percentile. Examples:
(query (:select (:percentile-cont :fraction 0.5 :order-by 'number-of-staff) :from 'schools)) (query (:select (:percentile-cont :fraction array[0.25 0.5 0.75 1] :order-by 'number-of-staff) :from 'schools))
sql-op :percentile-dist (&rest args)
Requires Postgresql 9.4 or higher. There are two required keyword parameters :fraction and :order-by. Percentile-dist returns the first input value whose position in the ordering equals or exceeds the specified fraction. If the fraction parameter is an array eturns an array of results matching the shape of the fractions parameter, with each non-null element replaced by the input value corresponding to that percentile. Examples:
(query (:select (:percentile-dist :fraction 0.5 :order-by 'number-of-staff) :from 'schools)) (query (:select (:percentile-dist :fraction array[0.25 0.5 0.75 1] :order-by 'number-of-staff) :from 'schools))
sql-op :corr (y x)
The corr function returns the correlation coefficient between a set of dependent and independent variables. Example:
(query (:select (:corr 'height 'weight) :from 'people))
sql-op :covar-pop (y x)
The covar-pop function returns the population covariance between a set of dependent and independent variables. Example:
(query (:select (:covar-pop 'height 'weight) :from 'people))
sql-op :covar-samp (y x)
(query (:select (:covar-samp 'height 'weight) :from 'people))
The covar-samp function returns the sample covariance between a set of dependent and independent variables. Example:
sql-op :string-agg (&rest args)
String-agg allows you to concatenate strings using different types of delimiter symbols. Allowable optional keyword parameters are :distinct, :order-by and :filter Note that order-by in string-agg requires postgresql 9.0 or later. Filter requires postgresql 9.4 or later. See tests.lisp for more examples.
(query (:select (:as (:string-agg 'bp.step-type \",\" ) 'step-summary) :from 'business-process)) (query (:select 'mid (:as (:string-agg 'y \",\" :distinct :order-by (:desc 'y)) 'words) :from 'moves)) (query (:select (:string-agg 'name "," :order-by (:desc 'name) :filter (:< 'id 4)) :from 'employee))
sql-op :array-agg (&rest args)
Array-agg returns a list of values concatenated into an arrays. Allowable optional keyword parameters are :distinct, :order-by and :filter.
Note that order-by in array-agg requires postgresql 9.0 or later. Filter requires postgresql 9.4 or later. See array-notes.html for more detailed notes on the use of arrays.
Example with Filter:
(query (:select 'g.id (:as (:array-agg 'g.users :filter (:= 'g.canonical \"Y\")) 'canonical-users) (:as (:array-agg 'g.users :filter (:= 'g.canonical \"N\")) 'non-canonical-users) :from (:as 'groups 'g) :group-by 'g.id))
sql-op :mode (&rest args)
Mode is used to find the most frequent input value in a group. See e.g. https://www.postgresql.org/docs/10/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE and article at https://tapoueh.org/blog/2017/11/the-mode-ordered-set-aggregate-function
(query (:select (:mode 'items) :from 'item-table))
sql-op :regr_avgx (y x)
The regr_avgx function returns the average of the independent variable (sum(X)/N) Example:
(query (:select (:regr_avgx 'height 'weight) :from 'people))
sql-op :regr_avgy (y x)
The regr_avgy function returns the average of the dependent variable (sum(Y)/N). Example:
(query (:select (:regr_avgy 'height 'weight) :from 'people))
sql-op :regr_count (y x)
The regr_count function returns the number of input rows in which both expressions are nonnull. Example:
(query (:select (:regr_count 'height 'weight) :from 'people))
sql-op :regr_intercept (y x)
The regr_intercept function returns the y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs. Example:
(query (:select (:regr_intercept 'height 'weight) :from 'people))
sql-op :regr_r2 (y x)
The regr_r2 function returns the square of the correlation coefficient. Example:
(query (:select (:regr_r2 'height 'weight) :from 'people))
sql-op :regr_slope (y x)
The regr_slope function returns the slope of the least-squares-fit linear equation determined by the (X, Y) pairs. Example:
(query (:select (:regr_slope 'height 'weight) :from 'people))
sql-op :regr_sxx (y x)
The regr_sxx function returns the sum(X^2) - sum(X)^2/N (“sum of squares” of the independent variable). Example:
(query (:select (:regr_sxx 'height 'weight) :from 'people))
sql-op :regr_sxy (y x)
The regr_sxy function returns the sum(X*Y) - sum(X) * sum(Y)/N (“sum of products” of independent times dependent variable). Example:
(query (:select (:regr_sxy 'height 'weight) :from 'people))
sql-op :regr_syy (y x)
The regr_syy function returns the sum(Y^2) - sum(Y)^2/N (“sum of squares” of the dependent variable). Example:
(query (:select (:regr_syy 'salary 'age) :from 'employee))
sql-op :stddev (&rest args)
The stddev function returns the the sample standard deviation of the input values. It is a historical alias for stddev-samp. Example:
(query (:select (:stddev 'salary) :from 'employee))
sql-op :stddev-pop (&rest args)
The stddev-pop function returns the population standard deviation of the input values. Example:
(query (:select (:stddev-pop 'salary) :from 'employee))
sql-op :stddev-samp (&rest args)
The stddev-samp function returns the sample standard deviation of the input values. Example:
(query (:select (:stddev-samp 'salary) :from 'employee))
sql-op :variance (&rest args)
Variance is a historical alias for var_samp. The variance function returns the sample variance of the input values (square of the sample standard deviation). Example:
(query (:select (:variance 'salary) :from 'employee))
sql-op :var-pop (&rest args)
The var-pop function returns the population variance of the input values (square of the population standard deviation). Example:
(query (:select (:var-pop 'salary) :from 'employee) :single)
sql-op :var-samp (&rest args)
The var-samp function returns the sample variance of the input values (square of the sample standard deviation). Example:
(query (:select (:var-samp 'salary) :from 'employee) :single)
Window Functions
sql-op :over (form &rest args)
Over, partition-by and window are so-called window functions. A window function performs a calculation across a set of table rows that are somehow related to the current row.
(query (:select 'salary (:over (:sum 'salary)) :from 'empsalary))
sql-op :partition-by (&rest args)
Args is a list of one or more columns to partition by, optionally followed by an :order-by clause.
(query (:select 'depname 'subdepname 'empno 'salary (:over (:avg 'salary) (:partition-by 'depname 'subdepname)) :from 'empsalary))
Note the use of :order-by without parens:
(query (:select 'depname 'empno 'salary (:over (:rank) (:partition-by 'depname :order-by (:desc 'salary))) :from 'empsalary))
sql-op :window (form)
(query (:select (:over (:sum 'salary) 'w) (:over (:avg 'salary) 'w) :from 'empsalary :window (:as 'w (:partition-by 'depname :order-by (:desc 'salary)))))
sql-op :with (&rest args)
With provides a way to write auxillary statements for use in a larger query, often referred to as Common Table Expressions or CTEs.
(query (:with (:as 'upd (:parens (:update 'employees :set 'sales-count (:+ 'sales-count 1) :where (:= 'id (:select 'sales-person :from 'accounts :where (:= 'name "Acme Corporation"))) :returning '*))) (:insert-into 'employees-log (:select '* (:current-timestamp) :from 'upd))))
sql-op :with-recursive (&rest args)
Recursive modifier to a WITH statement, allowing the query to refer to its own output.
(query (:with-recursive (:as (:t1 'n) (:union-all (:values (:set 1)) (:select (:+ 'n 1) :from 't1 :where (:< 'n 100)))) (:select (:sum 'n) :from 't1))) (query (:with-recursive (:as (:included_parts 'sub-part 'part 'quantity) (:union-all (:select 'sub-part 'part 'quantity :from 'parts :where (:= 'part "our-product")) (:select 'p.sub-part 'p.part 'p.quantity :from (:as 'included-parts 'pr) (:as 'parts 'p) :where (:= 'p.part 'pr.sub-part) ))) (:select 'sub-part (:as (:sum 'quantity) 'total-quantity) :from 'included-parts :group-by 'sub-part))) (query (:with-recursive (:as (:search-graph 'id 'link 'data 'depth) (:union-all (:select 'g.id 'g.link 'g.data 1 :from (:as 'graph 'g)) (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1) :from (:as 'graph 'g) (:as 'search-graph 'sg) :where (:= 'g.id 'sg.link)))) (:select '* :from 'search-graph))) (query (:with-recursive (:as (:search-graph 'id 'link 'data'depth 'path 'cycle) (:union-all (:select 'g.id 'g.link 'g.data 1 (:[] 'g.f1 'g.f2) nil :from (:as 'graph 'g)) (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1) (:|| 'path (:row 'g.f1 'g.f2)) (:= (:row 'g.f1 'g.f2) (:any* 'path)) :from (:as 'graph 'g) (:as 'search-graph 'sg) :where (:and (:= 'g.id 'sg.link) (:not 'cycle))))) (:select '* :from 'search-graph)))
Table Functions
sql-op :for-update (query &key of nowait)
Locks the selected rows against concurrent updates. This will prevent the rows from being modified or deleted by other transactions until the current transaction ends. The :of keyword should be followed by one or more table names. If provided, PostgreSQL will lock these tables instead of the ones detected in the select statement. The :nowait keyword should be provided by itself (with no argument attached to it), after all the :of arguments. If :nowait is provided, PostgreSQL will throw an error if a table cannot be locked immediately, instead of pausing until it's possible.
(query (:for-update (:select :* :from 'foo 'bar 'baz) :of 'bar 'baz :nowait))
sql-op :for-share (query &key of nowait)
Similar to :for-update, except it acquires a shared lock on the table, allowing other transactions to perform :for-share selects on the locked tables.
sql-op :insert-into (table &rest rest)
Use insert-into when you are either inserting from a select clause and you do not need to specify specific columns:
(query (:insert-into 'table1 (:select 'c1 'c2 :from 'table2)))
or you are alternating specific columns and values for a single row:
(query (:insert-into 'my-table :set 'field-1 42 'field-2 "foobar"))
You can use parameterized variables in the insert statement.
(let ((name "test-cat4")) (query (:insert-into 'categories :set 'name '$1) name))
It is possible to add :returning, followed by a list of field names or expressions, at the end of the :insert-into form. This will cause the query to return the values of these expressions as a single row.
(query (:insert-into 'my-table :set 'field-1 42 'field-2 "foobar" :returning '*)) (query (:insert-into 'my-table :set 'field-1 42 'field-2 "foobar" :returning 'id))
In Postgresql versions 9.5 and above, it is possible to add :on-conflict-do-nothing (if the item already exists, do nothing). If you want to specify the unique column to be checked for conflict, use :on-conflict 'column-name :do-nothing. If you do not want to specify the unique column name, use :on-conflict-do-nothing.
(query (:insert-into 'test-table :set 'column-A '$1 'column-B '$2 :on-conflict 'column-A :do-nothing :where (:= 'test-table.column-A '$1) :returning '*) "c" 37) (query (:insert-into 'test-table :set 'column-A '$1 'column-B '$2 :on-conflict-do-nothing 'column-A :where (:= 'test-table.column-A '$1) :returning '*) "c" 37)
If your insertion is setting a column that is an identity column with a value normally created by the system and you want to override that, you can use the :overriding-system-value keyword:
(query (:insert-into 'table1 :set 'c1 "A" 'c2 "B" :overriding-system-value)) (query (:insert-rows-into 'table1 :overriding-user-value :values '(((:select 'c1 'c2 :from 'table2)))))
To create what is commonly known as an upsert, use :on-conflict-update (if the item already exists, update the values) followed by a list of field names which are checked for the conflict then using :update-set followed by a list of field names or expressions following the syntax for updating a table. This is sometimes called an "upsert". Note that as per the postgresql sql documentation you must prepend the table name to the column in the where statement if you are updating.
(query (:insert-into 'test-table :set 'column-A '$1 'column-B '$2 :on-conflict-update 'column-A :update-set 'column-B '$2 :where (:= 'test-table.column-A '$1) :returning '*) "c" 37)
If the destination table has identity columns and you want to override those identity columns with specific values, you should specify :overriding-system-value.
(query (:insert-into 'test-table :set 'column-A '$1 'column-B '$2 :overriding-system-value :on-conflict-update 'column-A :update-set 'column-B '$2 :where (:= 'test-table.column-A '$1) :returning '*) "c" 37)
If you are selecting from another table which has column names the same as your destination table and you want to keep the destination table's identity column values, then you can use :overriding-user-value. E.g.
(query (:insert-into 'table1 :overriding-user-value (:select 'c1 'c2 :from 'table2)))
sql-op :insert-rows-into (table &rest rest)
Insert-rows-into provides the ability to insert multiple rows into a table without using a select statement. (Insert-rows-into keeps the VALUES key word in the resulting sql. If you do use a select statement, Postgresql requires that it only return one row.)
Specify the columns first with the keyword :columns then provide a list of lists of the values as a parameter to the keyword :values. Example:
(query (:insert-rows-into 'my-table :columns 'field-1 'field-2 :values '((42 "foobar") (23 "foobaz"))))
An example using a select statement returning one row:
(squery (:insert-rows-into 't6 :columns 'tags :values '(((:select 'id :from 't5)))))
If you will use the default columns, this can be simplified and the :columns parameters can be dropped. Example:
(query (:insert-rows-into 'my-table :values '((42 "foobar") (23 "foobaz"))))
If your insertion is setting a column that is an identity column with a value normally created by the system and you want to override that, you can use the :overriding-system-value keyword:
(query (:insert-rows-into 'table1 :columns 'c1 'c2 :overriding-system-value :values '((1 "a") (2 "b")))) (query (:insert-rows-into 'table1 :overriding-system-value :values '(((:select 'c1 'c2 :from 'table2)))))
Similarly to :insert-into, :insert-rows-into allows the "upsert" use of :on-conflict. Again, if you want to specify the unique column to be checked for conflict, use :on-conflict 'column-name :do-nothing. If you do not want to specify the unique column name, use :on-conflict-do-nothing. The following example uses :on-conflict-do-nothing
(query (:insert-rows-into 'distributors :columns 'did 'dname :values '((10 "Conrad International")) :on-conflict-do-nothing :where 'is-active)) (query (:insert-rows-into 'distributors :columns 'did 'dname :values '((10 "Conrad International")) :on-conflict 'did :do-nothing :where 'is-active))
or :on-conflict-update
(query (:insert-rows-into 'distributors :columns 'did 'dname :values '((5 "Gizmo Transglobal") (6 "Associated Computing Inc.")) :on-conflict-update 'did :update-set 'dname 'excluded.dname))
You can use :on-conflict-on-constraint to check for conflicts on constraints.
(query (:insert-rows-into 'test :columns 'some-key 'some-val :values '(("a" 3) ("b" 6) ("c" 7)) :on-conflict-on-constraint 'somekey :do-nothing :returning '*)) (query (:insert-rows-into 'test :columns 'some-key 'some-val :values '(("a" 2) ("b" 6) ("c" 7)) :on-conflict-on-constraint 'somekey :update-set 'some-val 'excluded.some-val :returning '*))
sql-op :update (table &rest rest)
Update values in a table. After the table name there should follow the keyword :set and any number of alternating field names and values, like for :insert-into. Next comes the optional keyword :from, followed by at least one table name and then any number of join statements, like for :select. After the joins, an optional :where keyword followed by the condition, and :returning keyword followed by a list of field names or expressions indicating values to be returned as query result.
sql-op :delete-from (table &rest rest)
Delete rows from the named table. Can be given a :where argument followed by a condition, and a :returning argument, followed by one or more expressions that should be returned for every deleted row.
(query (:delete-from 'cd.bookings :where (:= 'id 5)))
sql-op :create-table (name (&rest columns) &rest options)
Create a new table. The simplest example would pass two parameters, the table name and a list of lists providing information for each column. For example:
(query (:create-table 'george ((id :type integer))))
where 'george is the name of the table, it has 1 column named id which is limited to integers. There are no indexes or keys in this example.
See create-tables.html for more detailed examples.
Column Definition parameters
After the table name a list of column definitions follows, which are lists that start with a name, followed by one or more of the following keyword arguments:
- :type
This one is required. It specifies the type of the column. Use a type like (or db-null integer) to specify a column that may have NULL values.
- :default
Provides a default value for the field.
- :unique
If this argument is non-nil, the values of the column must be unique.
- :primary-key
When non-nil, the column is a primary key of the table.
- :check
Adds a constraint to this column. The value provided for this argument must be an S-SQL expression that returns a boolean value. It can refer to other columns in the table if needed.
- :references
Adds a foreign key constraint to this table. The argument provided must be a list of the form (target &optional on-delete on-update). When target is a symbol, it names the table to whose primary key this constraint refers. When it is a list, its first element is the table, and its second element the column within that table that the key refers to. on-delete and on-update can be used to specify the actions that must be taken when the row that this key refers to is deleted or changed. Allowed values are :restrict, :set-null,
- :set-default, :cascade, and :no-action.
Table Constraints
After the list of columns, zero or more extra options (table constraints) can be specified. These are lists starting with one of the following keywords:
- :check
Adds a constraint to the table. Takes a single S-SQL expression that produces a boolean as its argument.
- :primary-key
Specifies a primary key for the table. The arguments to this option are the names of the columns that this key consists of.
- :unique
Adds a unique constraint to a group of columns. Again, the arguments are a list of symbols that indicate the relevant columns.
- :foreign-key
Create a foreign key. The arguments should have the form (columns target &optional on-delete on-update), where columns is a list of columns that are used by this key, while the rest of the arguments have the same meaning as they have in the :references option for columns. Every list can start with :constraint name to create a specifically named constraint.
Note that, unlike most other operators, :create-table expects most of its arguments to be unquoted symbols. The exception to this is the value of :check constraints: These must be normal S-SQL expressions, which means that any column names they contain should be quoted. When programmatically generating table definitions, sql-compile is usually more practical than the sql macro.
Here is an example of a :create-table form:
(:create-table enemy ((name :type string :primary-key t) (age :type integer) (address :type (or db-null string) :references (important-addresses :cascade :cascade)) (fatal-weakness :type text :default "None") (identifying-color :type (string 20) :unique t)) (:foreign-key (identifying-color) (colors name)) (:constraint enemy-age-check :check (:> 'age 12)))
For more detail and examples on building tables using the s-sql approach, see create-tables.html
sql-op :alter-table (name action &rest args)
Alters named table. Currently changing a column's data type is not supported. The meaning of args depends on action:
- :add-column
Adds column to table. args should be a column in the same form as for :create-table.
(query (:alter-table "packages" :add-column 'system-data-p :type (or boolean db-null)))
- :set-default
Adds or changes a default value for a column
(query (:alter-table 'countries :alter-column 'updated-at :set-default (:now)))
- :drop-column
Drops a column from the table.
(query (:alter-table "test-uniq" :drop-column 'address))
- :add-constraint
Adds a named constraint to the table.
(query (:alter-table "test-uniq" :add-constraint silly-key :primary-key 'code 'title)) (query (:alter-table enemy :add-constraint enemy-age-check :check (:> 'age 21)))
- :drop-constraint
Drops constraint. First of args should name a constraint to be dropped; second, optional argument specifies behaviour regarding objects dependent on the constraint and it may equal :cascade or :restrict.
(query (alter-table enemy :drop-constraint enemy-age-check))
- :add
Adds an unnamed constraint to table. args should be a constraint in the same form as for :create-table. (This is for backwards-compatibility, you should use named constraints.)
- :rename
Adds the ability to rename a table.
- :rename-column
Adds the ability to rename a column of a table.
(query (:alter-table "test-uniq" :rename-column 'address 'city))
sql-op :drop-table (name)
Drops the named table. You may optionally pass :if-exists before the name to suppress the error message if the table does not exist. You can also optionally pass :cascade after the name to indicate that it should also drop any other tables, indices, etc which depend on that table. Accepts strings, variable or symbol as the identifier.
(query (:drop-table 'table1)) (query (:drop-table :if-exists 'table1)) (query (:drop-table :if-exists 'table1 :cascade)) (query (:drop-table (:if-exists 'table1-with-longer-name) :cascade)) (let ((table-var1 "table1")) (is (equal (sql (:drop-table :if-exists table-var1 :cascade)) "DROP TABLE IF EXISTS table1 CASCADE")) (let ((table-var1 'table-1)) (is (equal (sql (:drop-table :if-exists table-var1 :cascade)) "DROP TABLE IF EXISTS table_1 CASCADE"))
sql-op :truncate (&rest args)
Truncates one or more tables, deleting all the rows. Optional keyword arguments are allowed in the following order. Note that :continue-identity and :restart-identity make no sense if both are included.
- :only (if not specified, the table and its descendants are truncated).
- :continue-identity (the values of sequences will not be changed. This is the default)
- :restart-identity (the values of sequences owned by the table(s) will be restarted)
- :cascade (will cascade the truncation through tables using foreign keys.)
Example calls would be:
(query (:truncate 'bigtable 'fattable)) (query (:truncate 'bigtable 'fattable :only)) (query (:truncate 'bigtable 'fattable :only :continue-identity)) (query (:truncate 'bigtable 'fattable :restart-identity)) (query (:truncate 'bigtable 'fattable :only :restart-identity :cascade ))
sql-op :create-index (name &rest args)
Create an index on a table. After the name of the index the keyword :on should follow, with the table name after it. Then the keyword :fields, followed by one or more column names. Optionally, a :where clause with a condition can be added at the end to make a partial index.
(sql (:create-index 'gin-idx :on "historical-events" :using gin :fields 'data)) "CREATE INDEX gin_idx ON historical_events USING GIN (data)"
sql-op :create-unique-index (name &rest args)
Works like :create-index, except that the index created is unique.
sql-op :drop-index (name)
Drop an index. Takes :if-exists and/or :cascade arguments like :drop-table. Accepts strings, variable or symbol as the identifier.
(query (:drop-index 'index1)) (query (:drop-index :if-exists 'index1)) (query (:drop-index :if-exists 'index1 :cascade)) (let ((table-var1 "table1")) (is (equal (sql (:drop-index :if-exists table-var1 :cascade)) "DROP INDEX IF EXISTS table1 CASCADE"))
sql-op :create-sequence (name &key increment min-value max-value start cache cycle)
Create a sequence with the given name. The rest of the arguments control the way the sequence selects values.
sql-op :alter-sequence (name)
Alters a sequence. See Postgresql documentation for parameters.
- :increment
Sets the amount by which each subsequent increment will be increased.
- :min-value
- :max-value
- :no-min
- :no-max
- :start
- :restart
- :cache
- :cycle
- :no-cycle
- :owned-by
- :if-exists before the name to suppress the error message.
sql-op :drop-sequence (name)
Drop a sequence. Takes :if-exists and/or :cascade arguments like :drop-table. Accepts strings, variable or symbol as the identifier.
(query (:drop-sequence 'sequence1)) (query (:drop-sequence :if-exists 'sequence1)) (query (:drop-sequence :if-exists 'sequence1 :cascade))
sql-op :drop-view (name)
Drop a view. Takes optional :if-exists argument. Accepts strings, variable or symbol as the identifier.
sql-op :set-constraints (state &rest constraints)
Configure whether deferrable constraints should be checked when a statement is executed, or when the transaction containing that statement is completed. The provided state must be either :immediate, indicating the former, or :deferred, indicating the latter. The constraints must be either the names of the constraints to be configured, or unspecified, indicating that all deferrable constraints should be thus configured.
sql-op :listen (channel)
Tell the server to listen for notification events on channel channel, a string, on the current connection.
sql-op :notify (channel &optional payload)
Signal a notification event on channel channel, a string. The optional payload string can be used to send additional event information to the listeners.
sql-op :create-role (role &rest args)
Create a new role (user). Following the role name are optional keywords arguments:
- :options
One or more of the no-parameter options to PostgreSQL's CREATE ROLE SQL command.
- :password
Sets the role's password. (A password is only of use for roles having the LOGIN attribute, but you can nonetheless define one for roles without it.) If you do not plan to use password authentication you can omit this option. If no password is specified, the password will be set to null and password authentication will always fail for that user.
- :connection-limit
If role can log in, this specifies how many concurrent connections the role can make. -1 (the default) means no limit.
- :valid-until
The :valid-until clause sets a date and time after which the role's password is no longer valid. If this clause is omitted the password will be valid for all time.
- :role
Lists one or more existing roles which are automatically added as members of the new role. (This in effect makes the new role a “group”.)
- :in-role
Lists one or more existing roles to which the new role will be immediately added as a new member.
Here is an example of a :create-role form:
(query (:create-role 'user23 :options 'SUPERUSER 'NOINHERIT 'LOGIN :password "mypassword" :connection-limit 100 :role 'users))
sql-op :drop-database (name)
Drops the named database. You may optionally pass :if-exists before the name to suppress the error message. Examples:
(query (:drop-database 'database-name)) (query (:drop-database :if-exists 'database-name)) (let ((var 'my-database)) (query (:drop-database var)))
sql-op :copy (table &rest args)
Move data between Postgres tables and filesystem files. Table name is required followed by one or more of the following keyword arguments. Documentation for the copy command provides a full reference. An example from the Greenplum tutorial:
(query (:copy 'faa.d_airlines :columns 'airlineid 'airline_desc :from "/home/gpadmin/gpdb-sandbox-tutorials/faa/L_AIRLINE_ID.csv" :on-segment t :binary t :oids t :header t :delimiter "," :null "NULL" :escape "my-escape-string" :newline "CR" :csv t :log-errors t :segment-reject-limit 100 'ROWS))
Dynamic Queries, Composition and Parameterized Queries
Overview
The question gets asked how to build dynamic or composable queries in postmodern. First we need to understand the context - is the programmer building the query or are you taking data from a user and using that to build a query?
Programmer Built Queries
The question gets asked how to build dynamic or composable queries in postmodern. First we need to understand the context - is the programmer building the query or are you taking data from a user and using that to build a query? We need to remember that the query macro assumes that everything that is not a list starting with a keyword will evaluate to a string.
In any case you will need to ensure that either you have control over the inputs or they still result in parameterized queries. If not you have opened yourself up to an sql injection attack.
If you are not using s-sql, then it becomes easy. The query macro assumes that everything that is not a list starting with a keyword will evaluate to a string. That means you can build it with a simple format string
(query (format nil "select ~a from ~a where ~a" "carrots" "garden" "length > 3"))
With s-sql, there are generally three approaches to building dynamic or composible queries: pass symbols and values as variables, use sql-compile or use :raw.
For purposes of this example, we will use the following employee table:
(query (:create-table employee ((id :type int) (name :type text) (salary :type numeric) (start_date :type date) (city :type text) (region :type char) (age :type int)))) (query (:insert-rows-into 'employee :columns 'id 'name 'salary 'start-date 'city 'region 'age :values '((1 "Jason" 40420 "02/01/94" "New York" "W" 29) (2 "Robert" 14420 "01/02/95" "Vancouver" "N" 21) (3 "Celia" 24020 "12/03/96" "Toronto" "W" 24) (4 "Linda" 40620 "11/04/97" "New York" "N" 28) (5 "David" 80026 "10/05/98" "Vancouver" "W" 31) (6 "James" 70060 "09/06/99" "Toronto" "N" 26) (7 "Alison" 90620 "08/07/00" "New York" "W" 38) (8 "Chris" 26020 "07/08/01" "Vancouver" "N" 22) (9 "Mary" 60020 "06/08/02" "Toronto" "W" 34))))
- Approach #1 Using symbols in variables
- Select Statements
Consider the following two toy examples where we determine the table and columns to be selected using symbols (either keyword or quoted) inside variables.
(let ((table 'employee) (col1 :id) (col2 :name) (id 3)) (query (:select col1 col2 :from table :where (:= 'id '$1)) id)) ((3 "Celia")) (let ((table 'employee) (col1 'name) (col2 'salary) (id 3)) (query (:select col1 col2 :from table :where (:= 'id '$1)) id)) (("Celia" 24020))
This will not work if you use strings instead of symbols because sql-expand will wrap the strings in the variables in escape format as if they were string constants and Postgresql will throw an error because it is not expecting string constants in the middle of a select statement.
- Update Statements
This works with update statements as well
(let ((table 'employee) (col1 :id) (col2 :name) (new-name "Celeste") (id 3)) (query (:update table :set col2 new-name :where (:= col1 '$1)) id) (query (:select col1 col2 :from table :where (:= 'id '$1)) id)) ((3 "Celeste"))
- Insert Statements
This works with insert-into statements as well
(let ((table 'employee) (col1 'id) (col2 'name) (new-name "Rochelle") (id 10) (col3 'salary) (col3-value 3452) (col4 'start-date) (col4-value "02/01/03") (col5 'city) (col5-value "Victoria") (col6 'region) (col6-value "N") (col7 'age) (col7-value 32)) (query (:insert-into table :set col1 id col2 new-name col3 col3-value col4 col4-value col5 col5-value col6 col6-value col7 col7-value))) (query (:select 'id 'name 'salary :from 'employee :where (:= 'id 10 ))) ((10 "Rochelle" 3452))
- Select Statements
- Delete Statements
This works with delete statements as well
(let ((table 'employee) (col1 :id) (col1-value 10)) (query (:delete-from table :where (:= col1 col1-value))))
Approach #2 Use sql-compile
Sql-compile does a run-time compilation of an s-sql expression. In the following example, we create a function that accepts a where-clause, a table-name, 3 columns to select and two parameters to go into the where clause.
(defun toy-example (where-clause table-name col1 col2 col3 arg1 arg2) (with-test-connection (query (sql-compile (append `(:select ,col1 ,col2 ,col3 :from ,table-name :where) where-clause)) arg1 arg2))) (toy-example '((:and (:= 'city '$1) (:> 'salary '$2))) 'employee 'id 'name 'city "Toronto" 45000) ((6 "James" "Toronto") (9 "Mary" "Toronto"))
If we just look at what this call to sql-compile in toy-example generates, it would look like:
"(SELECT id, name, city FROM employee WHERE ((city = $1) and (salary > $2)))"
This example is still a parameterized query but for security reasons you will need to be very careful how you generate the where clause.
Another example with sql-compile and append, in this case updating a table and setting two columns to NULL.
(sql-compile (append '(:update :table1 :set) (loop for a in '("col1" "col2") collect a collect :NULL))) "UPDATE table1 SET E'col1' = NULL, E'col2' = NULL"
Lets think about it differently. What if we know the universe of columns we want to select, but want to conditionally select some of them. Suppose we know our targetted table has columns:
'id 'name 'salary 'start-date 'city 'region 'age.
We may decide we always want name, city and age, but salary and start-date are conditional.
(defun toy-example-2 (salaryp start-date-p) (sql-compile (remove nil `(:select 'name 'city 'age ,(if salaryp 'salary nil) ,(if start-date-p 'start-date nil) :from 'employee)))) (query (toy-example-2 t t)) (("Jason" "New York" 29 40420 #<SIMPLE-DATE:DATE 01-02-1994>) ("Robert" "Vancouver" 21 14420 #<SIMPLE-DATE:DATE 02-01-1995>) ("Celia" "Toronto" 24 24020 #<SIMPLE-DATE:DATE 03-12-1996>) ("Linda" "New York" 28 40620 #<SIMPLE-DATE:DATE 04-11-1997>) ("David" "Vancouver" 31 80026 #<SIMPLE-DATE:DATE 05-10-1998>) ("James" "Toronto" 26 70060 #<SIMPLE-DATE:DATE 06-09-1999>) ("Alison" "New York" 38 90620 #<SIMPLE-DATE:DATE 07-08-2000>) ("Chris" "Vancouver" 22 26020 #<SIMPLE-DATE:DATE 08-07-2001>) ("Mary" "Toronto" 34 60020 #<SIMPLE-DATE:DATE 08-06-2002>)) (query (toy-example-2 t nil)) (("Jason" "New York" 29 40420) ("Robert" "Vancouver" 21 14420) ("Celia" "Toronto" 24 24020) ("Linda" "New York" 28 40620) ("David" "Vancouver" 31 80026) ("James" "Toronto" 26 70060) ("Alison" "New York" 38 90620) ("Chris" "Vancouver" 22 26020) ("Mary" "Toronto" 34 60020))
You could skip the (remove nil… portion and substitute t for nil. E.g.
(defun toy-example-2 (salaryp start-date-p) (sql-compile `(:select 'name 'city 'age ,(if salaryp 'salary t) ,(if start-date-p 'start-date t) :from 'employee)))
But I prefer to remove those segments completely from the query.
Following on this same thread of thought, you can define a portion of the sql in a let clause:
(let ((sql1 '(:= name "Jason"))) (query (sql-compile `(:select 'name 'city 'age :from 'employee :where ,sql1)))) (("Jason" "New York" 29))
An example of this would be getting more columns depending on the postgresql server versionr:
(defun more-table-info (table-name) "Returns variable amounts of information depending on the postgresql server version" (let* ((version>11 (postgresql-version-at-least "12.0" *database*)) (version>10 (postgresql-version-at-least "11.0" *database*)) (select-query (sql-compile `(:order-by (:select (:as 'a.attnum 'ordinal-position) (:as 'a.attname 'column-name) (:as 'tn.typname 'data-type) ,(if version>10 'a.attidentity t) ,(if version>11 'a.attgenerated t) :from (:as 'pg_class 'c) (:as 'pg_attribute 'a) (:as 'pg_type 'tn) :where (:and (:= 'c.relname '$1) (:> 'a.attnum 0) (:= 'a.attrelid 'c.oid) (:= 'a.atttypid 'tn.oid))) 'a.attnum)))) (query select-query (to-sql-name table-name))))


|
BitShares-Core
6.1.0
BitShares blockchain implementation and command-line interface software
|
an elliptic curve private key. More...
#include <elliptic.hpp>
Public Member Functions | |
| private_key () | |
| private_key (private_key &&pk) | |
| private_key (const private_key &pk) | |
| ~private_key () | |
| private_key & | operator= (private_key &&pk) |
| private_key & | operator= (const private_key &pk) |
| private_key | child (const fc::sha256 &offset) const |
| private_key_secret | get_secret () const |
| operator private_key_secret () const | |
| fc::sha512 | get_shared_secret (const public_key &pub) const |
| compact_signature | sign_compact (const fc::sha256 &digest, bool require_canonical=true) const |
| public_key | get_public_key () const |
| unsigned int | fingerprint () const |
Static Public Member Functions | |
| static private_key | generate () |
| static private_key | regenerate (const fc::sha256 &secret) |
| static private_key | generate_from_seed (const fc::sha256 &seed, const fc::sha256 &offset=fc::sha256()) |
Friends | |
| bool | operator== (const private_key &a, const private_key &b) |
| bool | operator!= (const private_key &a, const private_key &b) |
| bool | operator< (const private_key &a, const private_key &b) |
an elliptic curve private key.
Definition at line 89 of file elliptic.hpp.
| fc::ecc::private_key::private_key | ( | ) |
Definition at line 32 of file elliptic_impl_priv.cpp.
| fc::ecc::private_key::private_key | ( | private_key && | pk | ) |
Definition at line 36 of file elliptic_impl_priv.cpp.
| fc::ecc::private_key::private_key | ( | const private_key & | pk | ) |
Definition at line 34 of file elliptic_impl_priv.cpp.
| fc::ecc::private_key::~private_key | ( | ) |
Definition at line 38 of file elliptic_impl_priv.cpp.
| private_key fc::ecc::private_key::child | ( | const fc::sha256 & | offset | ) | const |
Definition at line 130 of file elliptic_common.cpp.
|
inline |
Definition at line 139 of file elliptic.hpp.
|
static |
Definition at line 217 of file elliptic_common.cpp.
|
static |
This method of generation enables creating a new private key in a deterministic manner relative to an initial seed. A public_key created from the seed can be multiplied by the offset to calculate the new public key without having to know the private key.
Definition at line 176 of file elliptic_common.cpp.
| public_key fc::ecc::private_key::get_public_key | ( | ) | const |
Definition at line 70 of file elliptic_impl_priv.cpp.
| fc::sha256 fc::ecc::private_key::get_secret | ( | ) | const |
Definition at line 59 of file elliptic_impl_priv.cpp.
| fc::sha512 fc::ecc::private_key::get_shared_secret | ( | const public_key & | pub | ) | const |
Given a public key, calculatse a 512 bit shared secret between that key and this private key.
Definition at line 66 of file elliptic_secp256k1.cpp.
|
inline |
Definition at line 114 of file elliptic.hpp.
| private_key & fc::ecc::private_key::operator= | ( | private_key && | pk | ) |
Definition at line 40 of file elliptic_impl_priv.cpp.
| private_key & fc::ecc::private_key::operator= | ( | const private_key & | pk | ) |
Definition at line 46 of file elliptic_impl_priv.cpp.
|
static |
Definition at line 52 of file elliptic_impl_priv.cpp.
| compact_signature fc::ecc::private_key::sign_compact | ( | const fc::sha256 & | digest, |
| bool | require_canonical = true |
||
| ) | const |
Definition at line 89 of file elliptic_impl_priv.cpp.
|
friend |
Definition at line 130 of file elliptic.hpp.
|
friend |
Definition at line 134 of file elliptic.hpp.
|
friend |
Definition at line 126 of file elliptic.hpp.


Manual Input of Displacement Data
The form for manual input of slickenside
planes and striations is similar to
the
previous programs. After clicking on the "Start" button, you put in the
azimuth and dip
of the slickenside plane, followed by
azimuth and dip of the striation. The dip
angle is positive if the plane is a reverse
fault, and negative if the plane is a normal
fault. For positive dip angles you may drop the '+' sign.
The input of a fabric element is terminated by "Enter". The
input is shown in the list field. Measurement errors which produce an
offset between the striation and the slickenside plane, are corrected
automatically.
Input is terminated by "Terminate data input". This activates the menus
"Save data", "Printer", and the button "Plot". The "Plot"' button opens
the plot form. Maximum number of data
shows
the value which is written in Fabric.ini.


Hope: make it easy for leetcode to study & easy
Hope: can collect & record the video explanation of leetcode
Hope: users can contribute their own code & video explanation.
This package: easyleetcode
It is based on Python fastapi as server、Vue.js as client
For Python programmers to learn from leetcode fast
You can run and write your code online on the web
Support markdown notes
Python Code location: easyleetcode / leetcodes
Markdown Notes location: easyleetcode / leetcodes_ m
Our title : 新蕾Python
Because of my girlfriend's support, let I insisted on doing this event for Python education
So 新and 蕾 it's short for our name
< img src="/static/logo.jpg" align="center" style="width:520px;height:520px" >


Linked Pipes @LinkedPasts: Linked Data Tools and Workflows
Workshop at #LinkedPasts7
At Linked Pasts IV in Mainz 2018, the Linked Pasts Network had a session on "Linked Data Tools and Pipelines". As a result, the LinkedPipes Working Group was born, further developed during Linked Pasts V in Bordeaux. The main goal of Linked Pipes is to semantically model pipelines for LOD related workflows. The Linked Pipes is based on a specific ontology which assumes that a tool is linked into the Linked Open Data Cloud via Wikidata. This hackathon will be a virtual discussion room for further development of the data structure and goal, and discussion of ways to participate. It is splitted into two online sessions where the community may get an introduction, try out modelling linked pipes, report challenges and problems, discuss solutions and plan the next steps. A particular emphasis should be given to the incorporation and reuse of related Linked Data vocabularies for software workflow descriptions, a connection of linked pipes to traditional modes of publication (publication workflow identifiers) and the description of provenance of data and an adequate representation of such pipes to all audiences. The results should be implemented, sketched, discussed and finally published as a working paper for documenting data workflows.
Links
- Introduction Slides @ LinkedPasts7
- Summary Slides @ LinkedPasts7
- Linked Pipes Viewer
- Linked Pipes Mailing List (Google Group)
The Idea - Examples (more in the online viewer)
Example: Text -> Recogito Geo Annotation -> QGIS Map
Credits
Florian Thiery, Linked Pasts Network, mainzed, RGZM, Mainz, Germany
Timo Homburg, Linked Pasts Network, mainzed, i3mainz, Mainz, Germany


| AAC |
MARUTI SUZUKI |
1038 |
+2.01 |
Received by company |
| AAD |
GREY ORANGE |
2338 |
-0.01 |
Currently in production |
| AAX |
MINDA |
3222 |
+0.01 |
Finished, To be delivered |
| XXD |
HONDA MOTORS |
1402 |
-1.01 |
Awaiting reply from company |
| AAC |
MARUTI SUZUKI |
1538 |
+2.01 |
Planning work going on |
| AAD |
GREY ORANGE |
2738 |
-0.01 |
Received by company |
| AAX |
MINDA |
3822 |
+0.01 |
Currently in production |
| XXD |
HONDA MOTORS |
1802 |
-1.01 |
Awaiting reply from company |
| AAC |
MARUTI SUZUKI |
1938 |
+2.01 |
Currently in production |
| AAD |
GREY ORANGE |
2438 |
-0.01 |
Received by company |
| AAX |
MINDA |
3222 |
+0.01 |
Planning work going on |
| XXD |
HONDA MOTORS |
1102 |
-1.01 |
Finished, To be delivered |
| AAC |
MARUTI SUZUKI |
1238 |
+2.01 |
Received by company |
| AAD |
GREY ORANGE |
2638 |
-0.01 |
Currently in production |
| AAX |
MINDA |
3522 |
+0.01 |
Finished, To be delivered |
| XXD |
HONDA MOTORS |
1702 |
-1.01 |
Awaiting reply from company |
| AAC |
MARUTI SUZUKI |
1238 |
+2.01 |
Planning work going on |
| AAD |
GREY ORANGE |
2638 |
-0.01 |
Finished, To be delivered |
| AAX |
MINDA |
3722 |
+0.01 |
Currently in production |
| XXD |
HONDA MOTORS |
1802 |
-1.01 |
Planning work going on |
| AAC |
MARUTI SUZUKI |
1338 |
+2.01 |
Awaiting reply from company |
| AAD |
GREY ORANGE |
2238 |
-0.01 |
Finished, To be delivered |
| AAX |
MINDA |
3722 |
+0.01 |
Planning work going on |
| XXD |
HONDA MOTORS |
1402 |
-1.01 |
Received by company |
| AAC |
MARUTI SUZUKI |
1338 |
+2.01 |
Received by company |
| AAD |
GREY ORANGE |
2738 |
-0.01 |
Currently in production |
| AAX |
MINDA |
3822 |
+0.01 |
Awaiting reply from company |
| XXD |
HONDA MOTORS |
1702 |
-1.01 |
Planning work going on |
| AAC |
MARUTI SUZUKI |
1838 |
+2.01 |
Received by company |
| AAD |
GREY ORANGE |
2338 |
-0.01 |
Planning work going on |
| AAX |
MINDA |
3222 |
+0.01 |
Currently in production |
| XXD |
HONDA MOTORS |
1202 |
-1.01 |
Awaiting reply from company |


At least 3 dead in Bolivia as protesters clash with security forces amid food, fuel crisis
On Tuesday, troops and police lifted the blockade on a fuel plant in El Alto.
At least three people were killed and several wounded as protesters clashed with security forces in La Paz as the Bolivian military lifted the blockade of fuel supplies.
The protests which have gathered strength after ex-president Morales left for exile to Mexico last week amid vote-rigging allegation in a controversial election last month has led to renewed fighting between Morales supporters and police.
On Tuesday, troops and police lifted the blockade on a fuel plant in El Alto, operated by state-run YPFB even as Morales supporters demanded his return. The authorities deployed armoured vehicles and helicopters in a bid to break the blockade which has severely hit Bolivia's economy.
Bolivia has been hit by food and fuel shortages amid the protests as the government led by interim president Jeanine Anez, 52, has insisted that free and fair election would be held shortly. However, Anez hasn't announced the date of the elections.
Morales supporters have been demanding interim President Jeanine Anez's resignation and the return of their leader.
The death toll as a result of the clashes on Tuesday climbed to 27 since the elections on October 20. Last Friday, nine people were killed as police clashed with protesters in the central city of Cochabamba, a coca-growing highland. On Monday, in renewed clashes again near Cochabamba police fired tear gas at Morales supporters.
Carlos Mesa an influential politician who came second in the presidential polls in October urged Anez to hold elections as soon as possible along with other ministers who want the polls since Morales' Movement to Socialism party (MAS) has a majority in the Congress.


Automate the identification of date picker trigger elements & container elements
Pre-train a BERT model network with HTML represented in Emmet syntax, and leverage the resultant network to fine tune a model to identify HTML as a datepicker trigger or container element as a binary classification task
syntax used to represent HTML based on CSS selectors.
div + div > p > span + em
div#page > div.logo + ul.navigation > li*5 > a{Item $}
Offers a representation of HTML that is more concise and similar in structure to human language.


{% extends "base.html" %} {% block title %}The Blue Alliance - Verify Webhook{% endblock %} {% block meta_description %}Verify your ownership of a webhook you've created.{% endblock %} {% block content %}
Please double check that the verification code you entered is correct.


Definition at line 8371 of file compiled.php.
Create a new controller dispatcher instance.
| \Illuminate\Routing\Router | $router | |
| \Illuminate\Container\Container | $container |
Definition at line 33 of file ControllerDispatcher.php.
|
protected |
Apply the applicable after filters to the route.
| \Illuminate\Routing\Controller | $instance | |
| \Illuminate\Routing\Route | $route | |
| \Illuminate\Http\Request | $request | |
| string | $method |
Definition at line 199 of file ControllerDispatcher.php.
|
protected |
Definition at line 8429 of file compiled.php.
|
protected |
Call the "before" filters for the controller.
| \Illuminate\Routing\Controller | $instance | |
| \Illuminate\Routing\Route | $route | |
| \Illuminate\Http\Request | $request | |
| string | $method |
Definition at line 174 of file ControllerDispatcher.php.
|
protected |
Definition at line 8418 of file compiled.php.
|
protected |
Call the given controller instance method.
| \Illuminate\Routing\Controller | $instance | |
| \Illuminate\Routing\Route | $route | |
| string | $method |
Definition at line 156 of file ControllerDispatcher.php.
|
protected |
Definition at line 8413 of file compiled.php.
|
protected |
Call the given controller filter method.
| array | $filter | |
| \Illuminate\Routing\Route | $route | |
| \Illuminate\Http\Request | $request |
Definition at line 290 of file ControllerDispatcher.php.
|
protected |
Definition at line 8468 of file compiled.php.
|
protected |
Call the given controller instance method.
| \Illuminate\Routing\Controller | $instance | |
| \Illuminate\Routing\Route | $route | |
| \Illuminate\Http\Request | $request | |
| string | $method |
Definition at line 95 of file ControllerDispatcher.php.
|
protected |
Definition at line 8391 of file compiled.php.
Dispatch a request to a given controller and method.
| \Illuminate\Routing\Route | $route | |
| \Illuminate\Http\Request | $request | |
| string | $controller | |
| string | $method |
Definition at line 49 of file ControllerDispatcher.php.
Definition at line 8376 of file compiled.php.
|
protected |
Determine if the given filter applies to the request.
| array | $filter | |
| \Illuminate\Http\Request | $request | |
| string | $method |
Definition at line 234 of file ControllerDispatcher.php.
|
protected |
Definition at line 8444 of file compiled.php.
|
protected |
Determine if the filter fails the method constraints.
| array | $filter | |
| \Illuminate\Http\Request | $request | |
| string | $method |
Definition at line 255 of file ControllerDispatcher.php.
|
protected |
Definition at line 8453 of file compiled.php.
|
protected |
Determine if the filter fails the "on" constraint.
| array | $filter | |
| \Illuminate\Http\Request | $request | |
| string | $method |
Definition at line 268 of file ControllerDispatcher.php.
|
protected |
Definition at line 8457 of file compiled.php.
|
protected |
Get the assignable after filter for the route.
| \Closure | string | $filter |
Definition at line 219 of file ControllerDispatcher.php.
|
protected |
Definition at line 8437 of file compiled.php.
|
protected |
Get the middleware for the controller instance.
| \Illuminate\Routing\Controller | $instance | |
| string | $method |
Definition at line 118 of file ControllerDispatcher.php.
|
protected |
Definition at line 8398 of file compiled.php.
|
protected |
Make a controller instance via the IoC container.
| string | $controller |
Definition at line 79 of file ControllerDispatcher.php.
|
protected |
Definition at line 8386 of file compiled.php.
| methodExcludedByOptions | ( | $method, | |
| array | $options | ||
| ) |
Determine if the given options exclude a particular method.
| string | $method | |
| array | $options |
Definition at line 142 of file ControllerDispatcher.php.
| methodExcludedByOptions | ( | $method, | |
| array | $options | ||
| ) |
Definition at line 8409 of file compiled.php.
|
protected |
Definition at line 8370 of file compiled.php.
|
protected |
Definition at line 8369 of file compiled.php.


{{formErrors.name || "The username is required."}}
{{formErrors.name || "The custom id is required."}}


Accepts: glTF and FBX
.gltf .glb .fbx
The skinned mesh may not be handeld properly by the application.


This is the admin page
Use Cases:
- /newPlace: Opens Form where information can be filed and submitted to insert a new place in current DB
- /place/placeid: Shows the information of place which can be edited
- /placesOfCity/cityid: Not implemented
- /insertCrawledPlace/cityid: Given the city id of a crawled place in old DB, will show the details here. Inserting will insert into the new DB


Walter, I'm your lawyer. Anything you say to me is totally privileged. I'm not in the shakedown racket. I'm a lawyer. Even drug dealers need lawyers, right? Especially drug dealers.
No shit! Right now you're Fredo. But, y'know, with some sound advice and proper introductions, who knows? I'll tell you one thing: you've got the right product. Anything that gets the DEA's panties in this big a bunch, you're onto something special. And I would like to be a small and silent part of it. Food for thought, yeah? So if you want to make more money and keep the money that you make, better call Saul!
Better safe than sorry. That's my motto. Yeah, you do seem to have a little 'shit creek' action going. You know, FYI, you can buy a paddle. Did you not plan for this contingency? I mean the Starship Enterprise had a self-destruct button. I'm just saying.
I didn't say I killed him.  Crushed his head... with an ATM machine... right in front of me. I mean, crushed it like...  You know and the the blood, like everywhere. Like there was so much you would not believe. Man will you just please give me... just give me my weed alright? It helps with my nausea.
Four pounds... foooour pounds as if two pounds wasn't bad enough. we're talking two three hundred boxes of sinus pills there ain't that many Smurfs in the world. So no sudo? So you do have a plan! Yeah, Mr. White! Yeah, science!
Yo... it's appointment only! Jeez, you look like... Lex Luthor. I got two dudes turned into raspberry slushy and flushed down my toilet. I can't even take a proper dump in there. I mean the whole damn house has gotta be haunted by now.


This chapter discusses various new API features that have been added in recent versions of the Nokia 9000i Communicator.
1 Software Version Number
2 User's Name
3 IMEI Code Number
4 New SMS Header
5 New Standard Monikers, Bitmaps
6 Non-exclusive Lists
7 Incremental Searches
8 Contact's URL and Password
9 ContactList Searches
10 Unknown Phone Numbers
The INI file contains the version number. The [ui] category's productName field will be one of the following strings:
There is also a software serial number, in the swSerialNumber field. This field has the format: 000000/00/AAABBCCDDLLLFKK/0
Use one of the the
IniFileReadString...()
routines to find out the value of these INI file fields.
Code Display 13-1 Getting the Software Version Number
@start StringRes; @chunk TCHAR UICategory[] = "ui"; @chunk TCHAR SWSerialNumber[] = "swSerialNumber"; @end StringRes;
...
@method TestProcessClass, MSG_TEST_PROCESS_GET_VERSION { TCHAR version[28] = ""; TCHAR *category; TCHAR *key; word value;
MemLock( OptrToHandle( @UICategory ) ); category = (TCHAR *) LMemDeref( @UICategory ); key = (TCHAR *) LMemDeref( @SWSerialNumber );
/* * Extract the information you want from version string. */
MemUnlock( OptrToHandle( @UICategory ) ); }
The ContDB library has a new function,
ContactGetUserName()
, which returns the user's name.
To read the phone's IMEI number, you will need to use the
EciImeiGet()
function, provided by the eci library. This function takes one parameter, a pointer to a buffer to fill with the code number string. The buffer must be at least 17 bytes long, to hold the null-terminated 16-character code string. The function returns zero if the IMEI code was read succesfully, non-zero otherwise.
Code Display 13-2 Reading the IMEI Code
@include <eci.goh>
...
@method TestProcessClass, MSG_TEST_PROCESS_GET_IMEI { TCHAR imei[17] = "";
if ( EciGetImei( imei) == 0) { /* * We got IMEI code, do any check you want. */ }
else { /* * For some reason query failed. */ } }
In addition to the old way to send messages to application using their
GeodeToken
in front of the actual data, a new user data header is now introduced. Otherwise sending and receiving messages has not changed. The new header adopts the concept from Narrow Band Socket specification.
The new header format is //SCKddoo or //SCKLddddoooo where dd (and dddd) represent "destination port" and oo (and oooo) represent "originator port". Both these headers are terminated with space character.These ports are ascii coded hexadecimal numbers.
Short header covers ports from 0 to 255 (00 to FF in hex) and the longer form from 0 to 65535 (0000 to FFFF in hex).
The range of port numbers is divided to reserved port address range and to dynamic and/or private port address range. Usage of port address range 0 to 49151 is restricted and assignment of a port in this range requires contacting port address assignment authority. The reserved port address range is further divided to ports for "well-known protocols" (range 0 to 239 and 256 to 1024) and to registered ports (range 1025 to 49151). Nokia has reserved ports 5500 to 5599 for company-specific protocols. Port addresses in the dynamic address range 240 to 255 or in the dynamic and/or private address range 49152 to 65535 may be used freely by any vendor.
In order to receive these messages application must register themselves to SMS receive application. This can be done using smreg library. This library offers functions for registering and unregistering.
SMRegRegisterApplication()
takes there parameters:
GeodeToken
of your application, a pointer to buffer containing "port numbers" you register for and number of messages you are registering. Port numbers must be ASCII coded hexadecimal numbers.
Code Display 13-3 Registering to receive "destination port" SMS Messages
@method TestProcessClass, MSG_TEST_PROCESS_REGISTER { GeodeToken appl; TCHAR messageProtocol[5]; word protocol;
/* We register ourselves to listen port 43962 that is ABBA in hex. */ protocol = 43962; appl.GT_chars[0] = `T'; appl.GT_chars[1] = `E'; appl.GT_chars[2] = `S'; appl.GT_chars[3] = `T'; appl.GT_manufID = 11;
/* Let's ask smreg library to convert 43962 to ascii coded hex for us. */ SMRegIntegerToHex( protocol, messageProtocol, SMRITH_NULL_TERMINATE |SMRITH_WORD_LENGTH);
/* Now we have messageProtocol = "ABBA" */ SMRegRegisterApplication( appl, messageProtocol, 1); }
You can register more than one port by calling the function:
SMRegRegisterApplication( appl, "ABBADADA0055", 3);
Then you will receive messages ABBA, DADA and 55.
The function returns a
SMRegResult
value: SMRR_OK if your registration was succesful; otherwise it will be SMRR_FAIL.
SMRegIntegerToHex()
function converts passed decimal integers to ascii coded hexadecimal strings. You pass a word containing integer to be converted, a character pointer where the result is stored and flags how the conversion is done. You can convert either byte length integers i.e. 0 to 255 and the result is two characters to word length i.e. 0 to 65535 and the result is four characters. By default the destination string is not null terminated, but if you pass flag SMRITH_NULL_TERMINATE null is added at the end of the string.
If you want to stop receiving messages you can unregister yourself. This can be done using
SMRegUnregisterApplication()
function. The parameters are identical to SMRegRegisterApplication. In addition SMMR_OK and SMRR_FAIL, this unregister function may return SMRR_APPLICATION_NOT_REGISTERED to indicate that you were not registered to listen this message.
The message routing table is stored to .INI file. You may freely modify it yourself, but it is not recommended. More than one application may want to listen same messages, so writing .INI file direct may cause problems to other applications. The category is SMSRouteTable and port numbers represent keys underneath.
Code Display 13-4 INI file: SMSRouteTable example
Here TEST application wants to receive messages 00F0 and ABBA and TES2 wants to receive message ABBA.
[SMSRouteTable] 00F0 = "TEST",11 ABBA = { "TEST",11 "TES2",11 }
There are new ComplexMonikerType values and a new ComplexMonikerBitmap:
GenItemGroups may now be of type GIGBT_NON_EXCLUSIVE, allowing the user to select more than one item at a time.
GenItemGroupClass
now has two new messages which allow you to get and set which item has the "focus"--which single item the user currently has highlighted, as opposed to the selected items:
If you are using a ContactList (from the Contact DB library), youmay set it up for multiple selections by giving it a ATTR_CONTACT_LIST_NON_EXCLUSIVE_LIST variable data field. To find out which records are selected in such a list, use
MSG_CONTACT_LIST_GET_IDS_OF_SELECTED_RECORDS.
To allow the user to narrow the choices shown in a list when they type into a text entry, use two new classes in the Foam library: FilterListClass and FilterTextClass. Their API may be found in the include file foam.goh.
These classes are only available after Build 4. Check the "
F
" field of the version number, as described above.
The Contact DB Library has four new ContactFieldType values: CFT_TEL_FAX, CFT_TEL_DATA, CFT_URL and CFT_PASSWORD.
These values are only available after Build 4. Check the "
F
" field of the version number, as described above.
Normally, a ContactList will lose track of the user's current search if the focus moves to another object. To force the list to remember the current search, send it a MSG_CONTACT_LIST_PRESERVE_SEARCH_ON_NEXT_LOST_FOCUS before it loses the focus.
This message is only available after Build 4. Check the "
F
" field of the version number, as described above.
If the user enters a valid phone number in the search box of aContactList, but that number is not found, an application mayrequest that the ContactList send out a notification using a new variable data field.
Give the ContactList a ATTR_CONTACT_LIST_NOTIFY_NUMBER_ENTERED_MSG variable data field. This field's data is a message.
The message should have parameters:
void MSG_MY_CL_NOTIFY( MemHandle phNumber = cx);
The passed block contains the phone number string. The message's recipient is responsible for freeing the passed block.
When using the Contact Log library to log a contact, if you don't know the phone number, you can pass LEF_WILDCARD_NUMBER to
LogAddEntry()
to specify that this call's number should match any phone number.