Extra SQL functions

Utility H2 SQL functions¶

Hawkore Advanced Indexing provides some H2 SQL functions published under PUBLIC schema.

MultiValueIsoText functions for H2¶

Some interesting functions to work with MultiValueIsoText using SQL.

SCHEMA	NAME	DESCRIPTION
PUBLIC	HKMV_TEXT	Returns an iso text from a MultiValueIsoText
PUBLIC	HKMV_UPDATE	Creates/Updates a MultiValueIsoText. Allows to set a N-sequence of 3-tuples (iso, text, isMachine)
PUBLIC	HKMV_DEFAULT_ISO	Returns MultiValueIsoText's default text iso
PUBLIC	HKMV_DEFAULT_TEXT	Returns MultiValueIsoText's default text
PUBLIC	HKMV_REMOVE_ISOS	Remove isos from MultiValueIsoText
PUBLIC	HKMV_META	Retrieve meta from MultiValueIsoText
PUBLIC	HKMV_MERGE	Merge two MultiValueIsoTexts

HKMV_TEXT¶

Returns an iso text from a MultiValueIsoText

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	a MultiValueIsoText
P2	VARCHAR	iso code to extract from P1
P3	VARCHAR	Nullable. Default iso code to extract if P2 not found

Example 1: Search pois within a radius of 20km from point (latitude: 40.416775, longitude: -3.703790) in which its name's es text contains "aeropuerto":

SQL

SELECT 
id,
countryCode,
-- get name's es translation, if not found get en translation
public.hkmv_text(name, 'es', 'en') as name, 
-- get description's es translation, if not found get en translation
public.hkmv_text(description, 'es', 'en') as description,
type,
-- get name's iata alternate name, if not found null
public.hkmv_text(name, 'iata', null) as iata
FROM "pois".poi 
WHERE lucene= '{
   filter: [
      {type: "geo_distance", field: "place", latitude: 40.416775, longitude: -3.703790, max_distance: "20km"}
   ],
   query: {type: "phrase", field: "name.es.text", value: "aeropuerto", slop: 0},
   sort: [
      {field: "id"}
   ]
}' limit 10;

Example 2: Search pois in which its name's meta field has a key=iata.

SQL

SELECT 
id,
countryCode,
-- get name's es translation, if not found get en translation
public.hkmv_text(name, 'es', 'en') as name, 
-- get description's es translation, if not found get en translation
public.hkmv_text(description, 'es', 'en') as description,
type,
-- get name's iata alternate name, if not found null
public.hkmv_text(name, 'iata', null) as iata
FROM "pois".poi 
WHERE lucene = '{ 
       filter: [ 
           {type: "match", field:"name.meta._key", value: "iata"}
       ]
}'
limit 10;

Example 3: Search pois that have on theirs name's meta field a key = iata with prefix "iat" on its value:

SQL

SELECT 
id,
countryCode,
-- get name's es translation, if not found get en translation
public.hkmv_text(name, 'es', 'en') as name, 
-- get description's es translation, if not found get en translation
public.hkmv_text(description, 'es', 'en') as description,
type,
-- get name's iata alternate name, if not found return null
public.hkmv_text(name, 'iata', null) as iata
FROM "pois".poi 
WHERE lucene = '{ 
       refresh:true,
       filter: [ 
           {type: "match", field:"name.meta._key", value: "iata"}
       ],
       query: [ 
           {type: "prefix", field: "name.meta.value$iata", value: "iat"} 
       ]
}'
limit 10;

HKMV_UPDATE¶

Creates/Updates a MultiValueIsoText. Allows to set a N-sequence of 3-tuples (iso, text, isMachine)

iso: an iso/alternate code. String.
text: associated text to iso/alternate code. String.
isMachine: boolean flag as String, to tells if text was generated by a machine (machine translation).

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	MultiValueIsoText to update. Could be null
P2	BOOLEAN	Creates a new MultiValueIsoText if P1 is null
P3	VARCHAR	iso from provided P4 array to set as P1's default iso
P4	ARRAY	('iso','text','isMachine', more 3-tuples.. ) to set on P1

Example 1: Creates or update a MultiValueIsoText adding or replacing 'es' and 'iata' iso/alternate codes on name and adding or replacing 'es' on description of poi with id=57, setting es as default iso code on both name and description.

SQL query current

SELECT 
id,
countryCode,
-- get name's es translation, if not found get en translation
public.hkmv_text(name, 'es', 'en') as name, 
-- get description's es translation, if not found get en translation
public.hkmv_text(description, 'es', 'en') as description,
-- get name's iata alternate name, if not found return null
public.hkmv_text(name, 'iata', null) as iata
FROM "pois".poi 
WHERE id = 57;

SQL update

UPDATE "pois".poi 
SET name = public.hkmv_update(name, true, 'es', ('iata', 'IAT01', 'false', 'es','Aeropuerto Madrid-Barajas Adolfo Suárez', 'false')),
description = public.hkmv_update(description, true, 'es', ('es', 'El aeropuerto principal de Madrid', 'false'))
WHERE id = 57;

SQL query for changes

SELECT 
id,
countryCode,
-- get name's es translation, if not found get en translation
public.hkmv_text(name, 'es', 'en') as name, 
-- get description's es translation, if not found get en translation
public.hkmv_text(description, 'es', 'en') as description,
-- get name's iata alternate name, if not found return null
public.hkmv_text(name, 'iata', null) as iata
FROM "pois".poi 
WHERE id = 57;

Example 2: Add 'otherCode' to poi's name.

SQL update

UPDATE "pois".poi 
SET name = public.hkmv_update(name, true, null, ('otherCode', 'CODEX', 'false'))
WHERE id = 57;

SQL query for changes

SELECT 
id,
countryCode,
-- get name's es translation, if not found get en translation
public.hkmv_text(name, 'es', 'en') as name, 
-- get description's es translation, if not found get en translation
public.hkmv_text(description, 'es', 'en') as description,
-- get name's iata alternate name, if not found return null
public.hkmv_text(name, 'iata', null) as iata,
public.hkmv_text(name, 'otherCode', null) as other_code
FROM "pois".poi 
WHERE id = 57;

HKMV_DEFAULT_ISO¶

Returns MultiValueIsoText's default iso

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	MultiValueIsoText from extract default iso code

SQL

SELECT 
id,
countryCode,
-- get name's en translation, if not found get default translation
public.hkmv_text(name, 'en', public.HKMV_DEFAULT_ISO(name)) as name, 
-- get description's en translation, if not found get default translation
public.hkmv_text(description, 'en', public.HKMV_DEFAULT_ISO(description)) as description,
-- get name's iata alternate name, if not found return null
public.hkmv_text(name, 'iata', null) as iata,
public.HKMV_DEFAULT_ISO(name) as defaultIso
FROM "pois".poi 
WHERE id = 57;

HKMV_DEFAULT_TEXT¶

Returns MultiValueIsoText's default text

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	MultiValueIsoText from extract default iso's text

SQL

SELECT 
id,
countryCode,
-- get name's es translation, if not found get en translation
public.hkmv_text(name, 'es', 'en') as name, 
-- get description's es translation, if not found get en translation
public.hkmv_text(description, 'es', 'en') as description,
-- get name's iata alternate name, if not found return null
public.hkmv_text(name, 'iata', null) as iata,
public.HKMV_DEFAULT_ISO(name) as defaultIso,
public.HKMV_DEFAULT_TEXT(name) as defaultText
FROM "pois".poi 
WHERE id = 57;

HKMV_REMOVE_ISOS¶

Remove isos from a MultiValueIsoText.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	MultiValueIsoText to update. Could be null
P2	BOOLEAN	Creates a new MultiValueIsoText if P1 is null
P3	VARCHAR	iso from provided P4 array to set as P1's default iso
P4	ARRAY	('myIso', 'myIso2', ...) isos to remove from P1

Example: Remove 'otherCode' from poi's name.

SQL update

UPDATE "pois".poi 
SET name = public.hkmv_remove_isos(name, ('otherCode',))
WHERE id = 57;

SQL query for changes

SELECT 
id,
countryCode,
-- get name's es translation, if not found get en translation
public.hkmv_text(name, 'es', 'en') as name, 
-- get description's es translation, if not found get en translation
public.hkmv_text(description, 'es', 'en') as description,
-- get name's iata alternate name, if not found return null
public.hkmv_text(name, 'iata', null) as iata,
public.hkmv_text(name, 'otherCode', null) as other_code
FROM "pois".poi 
WHERE id = 57;

HKMV_META¶

Returns MultiValueIsoText's meta from name.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	MultiValueIsoText from extract meta

Example: Retrieve metas from poi.

SQL

SELECT 
id,
countryCode,
-- get name's es translation, if not found get en translation
public.hkmv_text(name, 'es', 'en') as name, 
-- get description's es translation, if not found get en translation
public.hkmv_text(description, 'es', 'en') as description,
-- get name's iata alternate name, if not found return null
public.hkmv_text(name, 'iata', null) as iata,
public.HKMV_META(name) as metaName
FROM "pois".poi 
WHERE id = 57;

HKMV_MERGE¶

Returns a merged MultiValueIsoText.

If P1 is null, P2 will be returned.
If P2 is null, P1 will be returned.
If P1 and P2 are both null, null will be returned.
If P1 and P2 are both not null, P1 will updated with P2 data and P1 will be returned.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	MultiValueIsoText to update
P2	OTHER	MultiValueIsoText with new data

Example 1: Merge name and description into a MultiValueIsoText.

SQL

SELECT 
id,
countryCode,
-- get name's es translation, if not found get en translation
public.hkmv_text(name, 'es', 'en') as name, 
-- get description's es translation, if not found get en translation
public.hkmv_text(description, 'es', 'en') as description,
-- get merged's 'es' translation, if not found get 'en' translation
public.hkmv_text(public.hkmv_merge(name, description), 'es', 'en') as merged,
-- get name's iata alternate name, if not found return null
public.hkmv_text(name, 'iata', null) as iata
FROM "pois".poi 
WHERE id = 57;

JSON functions for H2¶

Some interesting functions to work with JSON using SQL.

SCHEMA	NAME	DESCRIPTION
PUBLIC	HKJSON_TO_LIST	Creates a List instance from a JSON string
PUBLIC	HKJSON_TO_MAP	Creates a Map instance from a JSON string
PUBLIC	HKJSON_TO_SET	Creates a Set instance from a JSON string

HKJSON_TO_LIST¶

Creates a java.util.ArrayList from a JSON array string. Only works with simple types on entry values.

PARAMETER	TYPE	DESCRIPTION
P1	VARCHAR	JSON array string

SQL

INSERT INTO "test".user 
(name, gender, age, animal, food, number, bool, listz, start_date, stop_date, latitude, longitude) VALUES 
( 'Joana', 'female', -2, 'dog', 'chips', 1, true,  public.HKJSON_TO_LIST('["l1", "l2"]'), '2014/01/01','2014/01/31',-87.8, 0.5);

HKJSON_TO_MAP¶

Creates a java.util.HashMap from a JSON object string. Only works with simple types on map values.

PARAMETER	TYPE	DESCRIPTION
P1	VARCHAR	JSON object string

SQL

INSERT INTO "test".user 
(name, gender, age, animal, food, number, bool, mapz, start_date, stop_date, latitude, longitude) 
VALUES ( 'Javier', 'male', -2, 'monkey', 'chips', 1, true, public.HKJSON_TO_MAP('{"k1":"v1","k2":"v2"}'), '2014/01/01','2014/01/31',-87.8, 0.5);

HKJSON_TO_SET¶

Creates a java.util.HashSet from a JSON array string. Only works with simple types on entry values.

PARAMETER	TYPE	DESCRIPTION
P1	VARCHAR	JSON array string

SQL

INSERT INTO "test".user 
(name, gender, age, animal, food, number, bool, setz, start_date, stop_date, latitude, longitude) 
VALUES ( 'Jorge', 'male', 1, 'rabbit', 'bacon', 1, true, public.HKJSON_TO_SET('["s1", "s2"]'), '2014/01/01','2014/01/31',-87.8, 0.5);

Java Map functions for H2¶

Some interesting functions to work with java Maps using SQL.

SCHEMA	NAME	DESCRIPTION
PUBLIC	HKMAP_CLEAR	Clear a map
PUBLIC	HKMAP_CONTAINSKEY	Whether map contains provided key
PUBLIC	HKMAP_CONTAINSVALUE	Whether map contains provided value
PUBLIC	HKMAP_ISEMPTY	Whether map is empty
PUBLIC	HKMAP_PUTALL	Put all into a map
PUBLIC	HKMAP_PUT	Put an entry into a Map
PUBLIC	HKMAP_REMOVE	Remove entry from a Map
PUBLIC	HKMAP_SIZE	Size of map

HKMAP_CLEAR¶

Clears a map. Returns cleared target map.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target Map. Must not be null

HKMAP_CONTAINSKEY¶

Returns true if the target map contains provided key and false otherwise.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target Map. Must not be null
P2	OTHER	The entry's key to search

HKMAP_CONTAINSVALUE¶

Returns true if the target map contains provided value and false otherwise.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target Map. Must not be null
P2	OTHER	The entry's value to search

HKMAP_ISEMPTY¶

Returns true if the target map is empty and false otherwise.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target Map. Must not be null

HKMAP_PUT¶

Put an entry into a Map. Returns the updated map.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target Map. Must not be null
P2	OTHER	The entry's key
P3	OTHER	The entry's value

HKMAP_PUTALL¶

Put all into a Map. Returns the updated target map or, if target map is null, map on P2.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target Map. Could be not null
P2	OTHER	The map to put into target map. Must not be null

HKMAP_REMOVE¶

Removes an entry from a Map by key. Returns the updated target map.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target Map. Must not be null
P2	OTHER	The entry's key to remove

HKMAP_SIZE¶

Returns the size of the target map.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target Map. Must not be null

Java Collection functions for H2¶

Some interesting functions to work with java Collections using SQL.

SCHEMA	NAME	DESCRIPTION
PUBLIC	HKCOL_ADDALL	Add all items to a collection
PUBLIC	HKCOL_ADD	Add an item to a collection
PUBLIC	HKCOL_CLEAR	Clear a collection
PUBLIC	HKCOL_CONTAINS	Whether collection contains provided item
PUBLIC	HKCOL_ISEMPTY	Whether collection is empty
PUBLIC	HKCOL_REMOVE	Remove an item from a collection
PUBLIC	HKCOL_RETAINALL	Retain all items to a collection
PUBLIC	HKCOL_SIZE	Size of collection

HKCOL_ADD¶

Add an item to a collection. Returns the updated target collection.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target collection. Must not be null
P2	OTHER	The item to add

HKCOL_ADDALL¶

Add all into a Collection. Returns the updated target collection or, if target collection is null, collection on P2.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target collection. Could be not null
P2	OTHER	The collection to add to target collection. Must not be null

HKCOL_CLEAR¶

Clears a collection. Returns the updated target collection.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target collection. Must not be null

HKCOL_CONTAINS¶

Returns true if the target collection contains provided item and false otherwise.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target collection. Must not be null
P2	OTHER	The item to search

HKCOL_ISEMPTY¶

Returns true if the target collection is empty and false otherwise.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target collection. Must not be null

HKCOL_REMOVE¶

Removes an item from a collection. Returns the updated target collection.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target collection. Must not be null
P2	OTHER	The item to remove

HKCOL_RETAINALL¶

Retains all in a collection. Returns the updated target collection or, if target collection is null, collection on P2.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target collection. Could be not null
P2	OTHER	The collection to retain in target collection. Must not be null

HKCOL_SIZE¶

Returns the size of the target collection.

PARAMETER	TYPE	DESCRIPTION
P1	OTHER	Target collection. Must not be null

Keys	Action
`?`	Open this help
`n`	Next page
`p`	Previous page
`s`	Search