MariaDB SQL Grammar [Part 3]

Identifier Qualifiers:

How to reference an object and its context in SQL statement ?

Blog Post: Understanding Identifier Qualifiers in SQL

In SQL, identifiers are used to reference data structures such as databases, tables, or columns. Qualifiers are used to specify the context within which the final identifier is interpreted. Let's take a closer look at how identifier qualifiers work in SQL.

1. Qualifiers for Databases When referencing a database, only the database identifier needs to be specified. For example, "db_name" is a valid qualifier for a database. If no database is specified, the current database is assumed. However, if there is no default database and no database is specified, an error is issued.

2. Qualifiers for Objects within Databases For objects such as tables, views, functions, etc. that are contained within a database, the database identifier can be specified. If no database is specified, the current database is assumed. It's important to note that the database identifier is optional for objects within the current database.

3. Qualifiers for Column Names For column names, the table and the database are generally obvious from the context of the statement. However, it is possible to specify the table identifier or the database identifier plus the table identifier. For example, "db_name.tbl_name.col_name" is a fully-qualified identifier that specifies the database, table, and column names.

4. Quoting and Spacing All identifiers can be quoted individually using backticks (`) or other quote characters, if needed. Extra spacing, including new lines and tabs, is allowed between identifiers and qualifiers for readability.

5. Dot (.) as Separator If a qualifier is composed of more than one identifier, a dot (.) must be used as a separator. For example, "db_name.tbl_name" uses a dot as a separator between the database and table identifiers. It's important to use the dot as a separator for correct interpretation of qualifiers.

6. Dot (.) for Default Database In some cases, a table identifier may be prefixed with a dot (.) for ODBC compliance, but this has no practical effect on MariaDB. It is equivalent to just specifying the table name without the dot. For example, "tbl_name" is equivalent to ".tbl_name" or ".tbl_name".

In conclusion, understanding how identifier qualifiers work in SQL is crucial for correctly referencing databases, tables, and columns in SQL statements. By using the appropriate qualifiers and following the correct syntax, you can ensure accurate interpretation of identifiers in your SQL queries.

Identifier to File Name Mapping

In modern databases, each identifier, such as table and index names, maps to a file name on the filesystem. However, not all characters that are allowed in table names can be used in file names. To overcome this limitation, MariaDB uses a special character set to encode potentially unsafe characters in the table name to derive the corresponding file name. This ensures that the user can create tables using all characters allowed in the SQL Standard and that the database is not dependent on any particular filesystem.

The encoding rules for potentially unsafe characters are as follows. If the identifier is made up only of basic Latin numbers, letters, and/or the underscore character, the encoding matches the name. Otherwise, the identifier is encoded according to a table of code ranges. Each code range corresponds to a pattern of characters used to encode the identifier. For example, the code range 00C0..017F maps to the pattern [@][0..4][g..z], which means that the first character of the encoded identifier is '@', the second character can be any digit from 0 to 4, and the third character can be any letter from 'g' to 'z'.

The encoding happens transparently at the filesystem level, except for identifiers created before MySQL 5.1.6. For these identifiers, the server prefixes 'mysql50' to their name, and the prefix needs to be supplied to reference the table.

To find the file name for a table with a non-Latin1 name, you can use the following query:

SELECT CAST(CONVERT("this_is_таблица" USING filename) AS binary);

This will return the corresponding file name, which is "this_is_@y0@g0@h0@r0@o0@i1@g0".

To find the table name for a file name, you can use the following query:

SELECT CONVERT(_filename "this_is_@y0@g0@h0@r0@o0@i1@g0" USING utf8);

This will return the corresponding table name, which is "this_is_таблица".

In summary, identifier to file name mapping is an essential feature of modern databases. MariaDB uses a special character set to encode potentially unsafe characters in table names to derive the corresponding file name. This ensures that the user can create tables using all characters allowed in the SQL Standard and that the database is not dependent on any particular filesystem.

More Details:

https://github.com/AgeDB-Enterprise/maria-server

https://github.com/apache/age