vsql_cube extension for MySQL

Use vsql_cube when several numeric columns belong together and you want to query them as a single value. A chain of AND conditions can test whether a row falls inside a range, but it cannot rank rows by distance. The cube type does both. It is a port of PostgreSQL’s cube extension. Where PostgreSQL has a named function the name matches, and PostgreSQL’s operators and array constructors become the named functions below.


Maintainer	VillageSQL
Source and full documentation	villagesql/vsql-cube
License	GPL-2.0

Install

vsql_cube.veb is already in the server’s lib/veb/ directory if you installed VillageSQL with the install script, the Docker image, or a release tarball. Install it into the server with one statement:

INSTALL EXTENSION vsql_cube;

Confirm it is there:

SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_cube';

+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_cube      |
+----------------+

To build it yourself, follow the build instructions in the repository.

What it adds

The cube type

Declare the column as `cube`(N), where N is the number of dimensions. cube is a MySQL reserved word, so it needs backticks, and the dimension count is required.

CREATE TABLE sensors (
    id      INT PRIMARY KEY,
    name    VARCHAR(32),
    reading `cube`(3)
);

Write values as a coordinate string. The column parses it:

INSERT INTO sensors VALUES
    (1, 'north', '12.5,40.1,3.0'),
    (2, 'south', '48.0,12.7,9.5'),
    (3, 'east',  '30.2,25.0,6.1');

Functions

Function	Returns	What it does
`cube_point_nd(coords)`	`cube`	Builds a point from a comma-separated coordinate string
`cube_box_nd(lower, upper)`	`cube`	Builds a box from two corner strings
`cube_point(x)`, `cube_box(x, y)`	`cube`	Builds a one-dimensional point or box
`cube_from_string(s)`, `cube_to_string(c)`	`cube`, string	Converts between text and `cube`
`cube_dim(c)`	int	Number of dimensions
`cube_coord(c, n)`	real	Coordinate `n`, counting through the lower-left corner and then the upper-right. Out of range gives NULL
`cube_ll_coord(c, n)`, `cube_ur_coord(c, n)`	real	Coordinate `n` of the lower-left or upper-right corner
`cube_is_point(c)`	int	1 when the value is a point, 0 when it is a box
`cube_contains(a, b)`, `cube_contained_by(a, b)`	int	Whether one value encloses the other
`cube_overlaps(a, b)`	int	Whether two values share any space
`cube_distance(a, b)`	real	Euclidean distance
`cube_taxicab_distance(a, b)`	real	Manhattan distance
`cube_chebyshev_distance(a, b)`	real	Chebyshev distance
`cube_union(a, b)`, `cube_inter(a, b)`	`cube`	Smallest box holding both; their intersection
`cube_enlarge(c, r, n)`	`cube`	Grows a value by `r` in the first `n` dimensions
`cube_subset(c, dims)`	`cube`	A new value from the listed dimensions
`cube_add_dim(c, lower, upper)`	`cube`	Appends a dimension
`cube_agg(c)`	`cube`	Aggregate: the bounding box of every value in the group
`cube_scalar_agg(x)`	`cube`	Aggregate: the bounding range of a numeric column

Example

Find the two sensors nearest a target reading, then the sensors inside a region:

SELECT name, ROUND(cube_distance(reading, cube_point_nd('30,25,6')), 2) AS dist
FROM sensors
ORDER BY dist
LIMIT 2;

+-------+-------+
| name  | dist  |
+-------+-------+
| east  |  0.22 |
| south | 22.08 |
+-------+-------+

SELECT name FROM sensors
WHERE cube_contains(cube_box_nd('10,10,0', '35,45,7'), reading) = 1;

+-------+
| name  |
+-------+
| north |
| east  |
+-------+

A cube argument gets its dimension count from the column it came from, so every call above passes a column value. Feeding one function’s result straight into another, as in cube_dim(cube_point_nd('1,2')), fails with

ERROR 3219 (HY000): Cannot initialize function 'cube_dim': cannot determine     type parameters for vsql_cube.cube in argument 1

. For the same reason, insert a plain coordinate string rather than wrapping it in cube_point_nd().

Getting Started

Server Development

Using Extensions

Creating Extensions

Install

What it adds

The cube type

Functions

Example

See also

​Install

​What it adds

​The cube type

​Functions

​Example

​See also

Install

What it adds

The cube type

Functions

Example

See also