Loading TOC...
SQL Data Modeling Guide (PDF)

SQL Data Modeling Guide — Chapter 7

SQL Syntax

This chapter describes some of the SQL syntax that are unique to MarkLogic Server.

DESCRIBE Statement

The DESCRIBE statement can be used to return the contents of a schema or view.

The syntax is:

DESCRIBE [SCHEMA] name

For example, to return a list of views in the ‘main' schema, enter:

DESCRIBE SCHEMA main

To return a list of the columns in the ‘customers' view, enter:

DESCRIBE customers 

Using DESCRIBE to List Supported Functions

Many MarkLogic functions, such as xdmp:database-name, fn:collection, cts:similar-query, math:exp, can be executed in SQL statements. The SQL names of these functions take to form of:

namespace_function_name

For example, the xdmp:database-name function is expressed in SQL as xdmp_database_name.

The supported functions are too numerous to list here, but you can obtain a list of all supported functions by entering:

DESCRIBE all functions

You can narrow the list to scalar or aggregate functions by entering either:

DESCRIBE scalar functions
DESCRIBE aggregate functions

The following are some examples of the use of MarkLogic functions in SQl statements:

Provide the version of MarkLogic Server and hardware information:

select xdmp_version(), xdmp_platform(), xdmp_architecture()

Trace the performance of a query:

select xdmp_elapsed_time, t1.this, t2.that from t1, t2
    where t1.key=t2.ref group by t1.this

Do some trigonometry:

select math_cos(radian) from angles

Do some geospatial:

select cts_distance(town.center, building.location) from town, building

Get the MarkLogic home page:

select xdmp_http_get("http://www.marklogic.com/")

MATCH Operator

The MarkLogic SQL interface includes a MATCH operator that can be used to perform full-text queries against either a column or a field that is bound to a view, as described in Creating a View.

When the MATCH operator is applied to the whole content, column names are bound to their corresponding range index references and searchable fields are bound to their field names. When the MATCH operator is applied to individual columns, all names are unbound, as it doesn't make sense to constrain searches against one range index to the values of another. These queries are executed in unfiltered mode.

The search expression following the MATCH operator must be contained inside quotes.

Field names, like view and schema names, are treated as case-insensitive for the purposes of duplicate detection and lookup.

Search Grammar

The following table lists the search grammar that can be used by the MATCH operator.

Type Token
Wildcards*? % *
Boolean OperatorsAND, OR, NOT, NOT_IN, NEAR/integer)
Comparison OperatorsEQ, NE, LT, LE, GT, GE
Name Binding**<field_name>:<value>, <column_name>:<value>

* To use wildcards in a search expression, you must enable trailing wildcard searches and word lexicons (codepoint collation) on your database.

** Searches are constrained to the named field or column values.

Examples

SELECT * FROM employees WHERE employees MATCH "Manager"
SELECT * FROM employees WHERE employees MATCH "position:Manager"
SELECT firstname, lastname FROM employees WHERE employees 
    MATCH "employeeid LE 3"
SELECT * FROM messages WHERE messages 
    MATCH "cause:numeric AND text:expression"
SELECT * FROM messages WHERE text MATCH "invalid OR wrong OR incorrect"
SELECT * FROM messages WHERE messages
    MATCH "(cause:operand NEAR/10 cause:incompatible) 
    AND (correct AND expect)"
SELECT * FROM resumes WHERE resumes 
    MATCH 'firstname:M* and (BA or BS) 
    NEAR/15 (Pomona NOT_IN "Cal Poly Pomona")'
SELECT * FROM employees WHERE employees MATCH 'firstname:J*'
SELECT * FROM employees WHERE firstname MATCH "J*"

SET/SHOW Statements

MarkLogic Server supports Postgres SET and SHOW run-time configuration parameters, as well as some parameters that are specific to MarkLogic Server. For details on the Postgres parameters, see:

All SET parameters are good for the duration of the SQL session in which they are set. Some parameters are read-only and can only be specified by the SHOW statement. These are described in Read-only SHOW Parameters.

All SET string values must be specified in single quotes (SET parameter 'value').

timezone or time zone

Sets the timezone offset to that for the given timezone name. The standard permitted formats and keywords can be used.

For example, to set the timezone to UTC, enter:

SET timezone 'UTC'

statement_timeout

Sets the timeout for statement execution (milliseconds).

For example:

SET statement_timeout 5000

lc_messages

Sets the locale for error messages.

For example:

SET lc_messages 'en_US'

lc_collate

Sets the default collation in the dynamic environment.

The form we will see from the Postgres client is:

SET lc_collate 'en_US.utf8'

This maps to the collation: http://marklogic.com/collation/en_US

You can also specify a full collation string:

SET lc_collation 'http://marklogic.com/collation/en_US/S1/MO'

lc_numeric

Sets the locale for formatting numeric values.

For example:

set lc_numeric 'de_DE'

lc_time

Sets the locale for formatting date/time values.

For example:

set lc_time 'en_US.UTF-8'

DateType

Sets the output format for dates.

For example:

SET DateType 'ISO'

extra_float_digits

Sets the number of digits displayed for floating point types.

For example:

SET extra_float_digits 2

client_encoding or NAMES

Declares the encoding of data coming from the client.

For example:

SET client_encoding 'UTF8'

SET NAMES is the standard syntax for the same thing.

SET NAMES 'UTF8'

SCHEMA or search_path

Sets the default schema referenced by names in SQL statements.

For example:

SET search_path 'main'

mls_default_xquery

Set the default XQuery version.

For example:

SET mls_default_xquery '1.0-ml'

mls_redundant_check

Enable or disable the SQLITE redundant check on normal (on full-text) query constraints on rows. Value is 1 (enable) or 0 (disable). The default is 0.

For example:

SET mls_redundant_check 1; 
SELECT title, year FROM songs WHERE year=1991

Read-only SHOW Parameters

The following parameters can be obtained via the SHOW statement but they are read-only and cannot be set via the SET statement.

ParameterDescription
ALLReturn values for all the variables with descriptions (columns=name, setting, description).
lc_ctypeReturn the locale for character classifications. For us this is fixed at zxx.utf8.
max_function_argsThe limit on the number of function arguments. This will be the value of SQLITE_MAX_FUNCTION_ARG, by default 127.
max_identifier_lengthThe limit on the length of a name. This will be fixed at 64.
max_index_keysThe limit on the number of keys in an index. This will be the value of SQLITE_MAX_COLUMN, by default 2000.
integer_datetimesWhether the server supports 64-bit date/time values. Fixed at 1.
server_encodingThe encoding the server uses. Fixed at UTF-8.
server_versionThe version of MarkLogic Server.
server_version_numThe version of the server expressed as a single integer.

« Previous chapter
Powered by MarkLogic Server 7.0-4.1 and rundmc | Terms of Use | Privacy Policy