SofiaDB · referencia de SQL#

Cada ejemplo de esta página se ejecuta en las pruebas del motor, y cada rechazo se comprueba con su mensaje. Para usar el SQL desde una app, mira SofiaDB desde el lenguaje; para el motor, SofiaDB.

Reglas generales#

• Una sentencia por llamada, con ; final opcional. Si sobra texto: «sobra texto tras la sentencia (…): una sola sentencia por llamada».
• Sentencias: CREATE TABLE, CREATE INDEX, DROP TABLE, DROP INDEX, ALTER TABLE … ADD COLUMN, INSERT, REPLACE, SELECT, UPDATE, DELETE, BEGIN, COMMIT, ROLLBACK y EXPLAIN. Cualquier otra: «sentencia desconocida: …».
• Sin NULL. Toda columna tiene valor. NULL se rechaza donde aparezca: «SofiaDB no usa NULL: toda columna tiene valor (usa '' o 0, o un valor por defecto)». IS NULL e IS NOT NULL: «SofiaDB no usa NULL: IS NULL / IS NOT NULL no tienen sentido».
• Parámetros $1, $2…: los valores viajan aparte del texto. La sentencia debe recibir exactamente tantos como el mayor $n que usa: «la sentencia usa N parámetro(s) y se pasaron M». Hasta 65 535.
• Tipos de resultado: ENTERO, TEXTO, LOGICO, BYTES, DECIMAL e INSTANTE.

Léxico#

Palabras reservadas (no sirven de nombre sin comillas): select from where group order by having limit offset and or not as asc desc in like between is null true false values set insert update delete create drop table into primary key default distinct case when then else end join on union all inner left right full outer cross natural using exists.

Nombres de tablas, columnas e índices: de 1 a 63 bytes, sin caracteres de control («nombre inválido: «n» (1 a 63 bytes, sin caracteres de control)»).

Tipos#

Otro nombre: «tipo desconocido: x».

CREATE TABLE#

CREATE TABLE [IF NOT EXISTS] t (
  columna tipo [restricciones de columna],
  …,
  [restricciones de tabla]
)

Restricciones de columna, en cualquier orden: PRIMARY KEY [AUTOINCREMENT], UNIQUE, NOT NULL, DEFAULT expr, CHECK (expr), REFERENCES t [(col)] y CONSTRAINT nombre delante de cualquiera.

Restricciones de tabla (con CONSTRAINT nombre opcional): UNIQUE (cols), CHECK (expr), FOREIGN KEY (cols) REFERENCES t [(cols)] [ON DELETE|UPDATE RESTRICT|NO ACTION] y PRIMARY KEY (col).

CREATE TABLE cita (
  id INTEGER PRIMARY KEY,
  profesional TEXT NOT NULL,
  inicio TIMESTAMPTZ DEFAULT 0,
  monto NUMERIC(10,2) DEFAULT 0,
  CHECK (monto >= 0)
)

• Clave primaria: una sola columna, de tipo ENTERO, TEXTO, BYTES o INSTANTE. Compuesta: «la clave primaria compuesta no está soportada»; dos: «la tabla declara dos claves primarias»; otro tipo: «la clave primaria c es T: debe ser ENTERO, TEXTO, BYTES o INSTANTE».
• AUTOINCREMENT se acepta. Una clave INTEGER a la que el INSERT no da valor toma la siguiente: el máximo más uno, o 1 si la tabla está vacía. Si se acaban: «se acabaron los números de t».
• DEFAULT admite una expresión sin columnas. CHECK y los índices parciales no admiten parámetros: «CHECK no admite parámetros ($n): usa valores fijos».
• Claves foráneas: se comprueban al final de cada sentencia. Solo RESTRICT o NO ACTION: «ON DELETE|UPDATE solo admite RESTRICT o NO ACTION: CASCADE, SET NULL y SET DEFAULT no están soportados». MATCH, DEFERRABLE e INITIALLY: «…no está soportado: las claves foráneas se comprueban al final de cada sentencia».
• Nombres automáticos de las restricciones: <tabla>_pkey, <tabla>_<col>_key, <tabla>_<col>_check (<tabla>_check si el CHECK es de tabla) y <tabla>_<col>_fkey.
• Límites: de 1 a 1000 columnas, 16 MiB por fila, 1 KiB de clave.
• Otros errores: «la tabla X ya existe», «la columna X está repetida», «la clave primaria c no es una columna», «la columna X no puede admitir NULL: SofiaDB no usa NULL (toda columna tiene valor)», «la columna declara dos REFERENCES».

CREATE INDEX y DROP#

CREATE [UNIQUE] INDEX [IF NOT EXISTS] nombre ON t (col [ASC], …) [WHERE predicado]
DROP TABLE [IF EXISTS] t
DROP INDEX [IF EXISTS] nombre

CREATE UNIQUE INDEX cita_hora ON cita (profesional, inicio)
CREATE INDEX cita_cara ON cita (inicio) WHERE monto > 100

• Un índice se recorre en los dos sentidos: DESC se rechaza («los índices se recorren en los dos sentidos: no hace falta DESC en el índice»).
• El predicado no admite parámetros. Una columna repetida: «la columna c aparece dos veces».
• DROP TABLE de una tabla a la que otra apunta con una clave foránea falla con FORANEA_VIOLADA.

ALTER TABLE#

ALTER TABLE t ADD [COLUMN] columna tipo [DEFAULT expr] [NOT NULL]

Es la única forma de cambiar columnas: «de ALTER TABLE solo hay ADD COLUMN, POLITICA INQUILINO col, SIN POLITICA, AUDITAR y SIN AUDITAR». Las filas que ya existen necesitan un valor, así que DEFAULT es obligatorio: «añadir la columna X necesita DEFAULT: las filas que ya existen deben tener un valor (SofiaDB no usa NULL)».

Tampoco se puede añadir:

INSERT#

INSERT [OR REPLACE | OR IGNORE] INTO t [(col, …)] VALUES (v, …), (v, …)
REPLACE INTO t [(col, …)] VALUES (v, …)

INSERT INTO cita (id, profesional, inicio) VALUES (1, 'ana', 1790586000000), (2, 'luis', 1790589600000)
INSERT OR IGNORE INTO cita (id, profesional) VALUES (1, 'otra')
INSERT OR REPLACE INTO cita (id, profesional) VALUES (1, 'otra')

• Una sentencia con varias filas es atómica: si una falla, ninguna queda.
• Sin lista de columnas, VALUES debe traer todas, en orden. Las columnas omitidas toman su DEFAULT; si no lo tienen: «falta el valor de la columna X (no tiene DEFAULT y SofiaDB no usa NULL)». Si la cantidad no cuadra: «VALUES trae N valor(es) y se esperaban M».
• OR IGNORE salta la fila que choca con una clave o un UNIQUE; OR REPLACE y REPLACE INTO borran la fila con la que chocan y ponen la nueva.
• OR ABORT, OR FAIL y OR ROLLBACK: «INSERT OR «abort» en la posición N no está soportado: usa INSERT, INSERT OR REPLACE o INSERT OR IGNORE».
• Una clave repetida da UNICO_VIOLADO:<tabla>_pkey («clave primaria repetida en t: ya hay una fila con …»).

SELECT#

SELECT { * | alias.* | expr [[AS] alias] }, …
  [FROM fuente [JOIN …]]
  [WHERE predicado]
  [GROUP BY expr, …] [HAVING predicado]
  [ORDER BY expr [ASC|DESC], …]
  [LIMIT expr] [OFFSET expr]

LIMIT y OFFSET pueden ir en cualquier orden. Un SELECT sin FROM vale (SELECT 1 + 1). Una columna sin alias se llama como la columna, o count, sum, min, max si es un agregado.

SELECT DISTINCT se rechaza: «SELECT DISTINCT no está soportado».

FROM y JOIN#

La fuente es t [[AS] alias] o una tabla derivada (SELECT …) [AS] alias (el alias es obligatorio). Un FROM junta hasta 16 fuentes.

SELECT c.id, c.profesional, p.nombre
FROM cita c
INNER JOIN profesional p ON p.id = c.profesional_id
LEFT JOIN sala s ON s.id = c.sala_id

• [INNER] JOIN y LEFT [OUTER] JOIN, siempre con ON. Sin ON: «se esperaba ON tras el JOIN y hay …».
• Un LEFT JOIN sin pareja entrega el valor vacío de cada tipo ('', 0, false), no NULL.
• RIGHT, FULL, CROSS y NATURAL: ««x» en la posición N no está soportado: usa [INNER] JOIN o LEFT JOIN con ON».
• La coma entre tablas: «la coma entre tablas del FROM no está soportada: usa JOIN … ON». USING: «JOIN … USING no está soportado: usa ON».

Subconsultas#

Una subconsulta puede referirse a columnas de la consulta que la contiene (correlada), con hasta 24 niveles. Una escalar sin filas da el valor vacío del tipo.

• «una subconsulta escalar devuelve una sola columna (usa IN o EXISTS para varias)».
• «la subconsulta de IN devuelve una sola columna».
• Una subconsulta correlada no se combina con GROUP BY ni con agregados. Donde no caben: «no se permiten subconsultas en …».

Agregados y agrupación#

count(*), count(e), sum(e), min(e) y max(e), con GROUP BY y HAVING.

SELECT profesional, count(*), sum(id) FROM cita GROUP BY profesional HAVING count(*) > 1 ORDER BY profesional

• SUM de ninguna fila da 0. SUM solo admite números: «SUM no admite un T».
• MIN y MAX de ninguna fila fallan: «MIN/MAX de ninguna fila no tiene valor (SofiaDB no usa NULL): comprueba antes COUNT(*)».
• AVG: «AVG no está soportado: usa SUM y COUNT». Con DISTINCT: «los agregados con DISTINCT no están soportados».
• «no se puede anidar un agregado dentro de otro» y «no se permiten agregados (COUNT, SUM…) en …» (por ejemplo, en un WHERE).
• Agrupar o juntar usa como mucho 64 MiB de memoria: «consulta demasiado grande: necesita más de 64 MiB de memoria para juntar o agrupar (agrega filtros, un índice o LIMIT)».

Expresiones#

De menor a mayor precedencia:

• LIKE usa % (cualquier cosa) y _ (un carácter).
• Comparaciones encadenadas: «no se pueden encadenar comparaciones: usa AND».
• Entero con entero: + - * / %. Fuera de rango: «desbordamiento: el resultado no cabe en el tipo»; «división por cero».
• Decimal: + - * sí; / no («la división de DECIMAL no está soportada»).
• Instante: instante ± entero (milisegundos) da instante; instante − instante da entero.
• Un texto junto a un número se interpreta como número. Tipos incompatibles: «no se puede operar un X con un Y»; menos unario: «no se puede negar un T». Una condición que no es lógica: «… debe ser verdadero o falso y es un T».
• CASE: «CASE no está soportado». ::: «las conversiones con :: no están soportadas».
• Una expresión anida hasta 128 niveles («expresión anidada demasiado profunda») y encadena hasta 256 operandos con AND, OR, +, * o || («expresión encadenada demasiado larga (como mucho 256 operandos seguidos)»).

Funciones#

SELECT upper(profesional), length(profesional), substr(profesional, 1, 2) FROM cita

• coalesce, nullif e ifnull se rechazan: «X no hace falta: SofiaDB no usa NULL».
• Otra función: «función desconocida: x». Cantidad de argumentos distinta: «X recibe [n] argumento(s) y se le pasan N».
• Tipos: «length no admite un T», «abs no admite un T», «encode recibe BYTES», «formato de encode desconocido: x», «decode: el texto no está bien codificado», «substr: la posición debe ser ENTERO», «substr: largo negativo», «substr: el largo debe ser ENTERO».

UPDATE y DELETE#

UPDATE t SET col = expr, … [WHERE predicado]
DELETE FROM t [WHERE predicado]

UPDATE cita SET monto = monto + 1 WHERE id = 2
DELETE FROM cita WHERE inicio < 1790000000000

Ambas devuelven las filas afectadas. Sin WHERE actúan sobre toda la tabla. Una columna asignada dos veces: «la columna c se asigna dos veces». Las restricciones (UNIQUE, CHECK, claves foráneas) se comprueban al final de la sentencia; si fallan, la sentencia no deja nada.

Transacciones#

BEGIN [TRANSACTION | WORK]
START TRANSACTION
COMMIT | END [TRANSACTION | WORK]
ROLLBACK [TRANSACTION | WORK]

Sin BEGIN, cada sentencia es su propia transacción. Lo confirmado sobrevive a un corte de luz. Si otra transacción confirmó antes un cambio en lo que esta leyó, falla con CONFLICTO_SERIALIZACION y hay que repetirla (una sentencia suelta se repite sola). Una transacción acepta hasta 64 MiB de cambios y dura a lo más 30 s; pasado eso falla: «la transacción lleva abierta más de 30 s: confírmala o divídela en partes más cortas».

EXPLAIN#

EXPLAIN SELECT * FROM cita WHERE profesional = 'ana'

Devuelve en texto el plan (qué índice usa, cómo junta). Solo vale para SELECT, UPDATE y DELETE: «EXPLAIN solo vale para SELECT, UPDATE y DELETE».

Códigos de error estables#

Los errores de restricción y de concurrencia empiezan por un código que un programa puede comparar:

Límites#