Database meta traversal slots

Database meta traversal

All the following slots are a part of the database meta system, and allow you to retrieve meta data about your databases, such as database names, table names, columns names, and foreign keys for tables. These slots are heavily relied upon by the CRUDifier, as it creates a Hyperlambda backend for you, in order to determine which arguments your HTTP endpoints require, and how to validate these during HTTP invocations towards your backend.

All these have at least 4 versions, one for SQLite, one for MySQL, one for PostgreSQL, and a fourth for SQL Server, and their only difference is that the MySQL versions use “mysql” as its slot name, SQL Server uses “mssql”, PostgreSQL uses “pgsql” and SQLite uses “sqlite”. Besides from this, they’re identical in regards to their APIs, which is the point, since it allows for these slots to be used transparently, or “polymorphistically” towards any underlying database type. In the following section, we therefore document their MySQL version, implying you’ll have to exchange the “mysql” parts to test these towards a different database type.

[magic.db.mysql.databases]

This slot returns all databases the system has access to within the specified connection string. Example usage can be found below.

signal:magic.db.mysql.databases
   connection-string:generic

The above would result in a list of databases resembling the following.

signal
   ""
      db:information_schema
   ""
      db:magic
   ""
      db:mysql
   ""
      db:performance_schema
   ""
      db:sys

[magic.db.mysql.tables]

This slot returns all tables for the specified [connection-string]/[database] combination. Below is example usage.

signal:magic.db.mysql.tables
   connection-string:generic
   database:magic

The above would return all tables in your “magic” database, resulting in something resembling the following.

signal
   ""
      table:crypto_invocations
   ""
      table:crypto_keys
   ""
      table:log_entries
   ""
      table:magic_version
   ""
      table:roles
   ""
      table:task_due
   ""
      table:tasks
   ""
      table:users
   ""
      table:users_crypto_keys
   ""
      table:users_roles

[magic.db.mysql.columns]

This slot returns all columns for the specified [connection-string]/[database]/[table] combination. Below is example usage.

signal:magic.db.mysql.columns
   connection-string:generic
   database:magic
   table:users_roles

The above would return all columns in your “users_roles” table, resulting in something resembling the following.

signal
   ""
      name:role
      db:varchar(45)
      nullable:bool:false
      primary:bool:true
      automatic:bool:false
      hl:string
   ""
      name:user
      db:varchar(256)
      nullable:bool:false
      primary:bool:true
      automatic:bool:false
      hl:string

The fields imply the following.

[name] - Name of column
[db] - Database type for column
[nullable] - If true column accepts null values
[primary] - If true column is a part of the primary key for the table
[automatic] - If true column has a default value
[hl] - Hyperlambda type mapping for the column

[magic.db.mysql.foreign_keys]

This slot returns all foreign keys for the specified [connection-string]/[database]/[table] combination. Below is example usage.

signal:magic.db.mysql.foreign_keys
   connection-string:generic
   database:magic
   table:users_roles

The above would return all foreign keys in your “users_roles” table, resulting in something resembling the following.

signal
   ""
      column:role
      foreign_table:roles
      foreign_column:name
   ""
      column:user
      foreign_table:users
      foreign_column:username

In the above result we can see how the “role” column in the “users_roles” table for instance has a foreign key declaration pointing towards the “name” column in the “roles” table, and similarly for the “user” column pointing towards the “users” table’s “username” column. The resulting values imply the following.

[column] - Name of column in table you’re querying
[foreign_table] - Name of table the column is referencing
[foreign_column] - Name of column in foreign table the column is referencing

[magic.db.mysql.indexes]

This slot returns all indexes for the specified [connection-string]/[database]/[table] combination. Below is example usage.

signal:magic.db.mysql.indexes
   connection-string:generic
   database:magic
   table:users_roles

The above would return all indexes in your “users_roles” table.