NAME
DBIO::SQLite::Util - Shared SQLite-specific helper functions for DBIO
VERSION
version 0.900007
DESCRIPTION
Small helpers that capture SQLite-specific truths shared across the driver, so the same rule is not re-encoded in several places.
METHODS
libsqlite_version
my ($string, $number) = libsqlite_version($dbh);
The version of the linked libsqlite, as the string SELECT sqlite_version() gives and as an integer to compare against (3.27.0 is 3_027_000). The components are compared as numbers, never as text: "3.9" sorts before "3.37" as a string and after it as a version.
foreign_key_action
my $action = foreign_key_action($value, $context);
The one check of the action of a foreign key (on_delete, on_update), shared by DBIO::SQLite::DDL and DBIO::SQLite::Diff. The value is read the way SQLite reads it -- runs of white space count as one space, letter case does not matter -- and has to be one of the five actions: NO ACTION, RESTRICT, CASCADE, SET NULL or SET DEFAULT. It is returned as declared, with the white space collapsed and trimmed; the letter case is not changed.
A value that is not declared, undef or '', returns undef. Any other word throws a DBIO::Exception that names the value, the five allowed actions and $context (the relationship or the table the caller is looking at), so a typo in a schema is refused when it is read and not left to SQLite as a syntax error.
column_is_nullable
my $is_nullable = column_is_nullable($not_null, $is_pk, $pk_is_rowid_alias);
The single source of truth for SQLite column nullability, shared by the introspect path ("table_columns_info" in DBIO::SQLite::Introspect) and the live PRAGMA path ("_dbh_columns_info_for" in DBIO::SQLite::Storage) so the driver cannot answer two different things about the same column. Returns 1 when the column may hold NULL, 0 otherwise.
A column declared NOT NULL is never nullable, and a column that is not part of the primary key is nullable exactly when PRAGMA table_info says so. The third argument decides the remaining case -- a key column that carries no NOT NULL attribute -- and it has to, because SQLite really does store NULL in most such columns:
CREATE TABLE t (name TEXT PRIMARY KEY); -- a rowid table
INSERT INTO t (name) VALUES (NULL); -- accepted
INSERT INTO t (name) VALUES (NULL); -- accepted again
Both rows are there afterwards; the two NULLs do not even collide with one another. This is SQLite's documented, deliberately-kept bug (see "The PRIMARY KEY" in https://sqlite.org/lang_createtable.html) and it is why this driver does not take DBIO::Introspect::Base's is_pk => not nullable fold at face value (core ADR 0038): reporting is_nullable => 0 for that column makes DBIO::Generate bake NOT NULL into the regenerated result class, and the redeployed table then refuses the very rows it was generated from.
The one key column that genuinely cannot hold NULL is the rowid alias -- INTEGER PRIMARY KEY on a rowid table, where NULL is an input token meaning "assign the rowid" and the stored value is an integer. Pass $pk_is_rowid_alias true for it; "pk_is_rowid_alias" answers that question from the database. WITHOUT ROWID tables need no special case: SQLite reports notnull = 1 for their key columns itself.
Omitting the third argument answers "not the alias", i.e. nullable. That is the safe direction of the two: guessing 1 leaves the engine's own constraint to reject a bad INSERT with a real error, while guessing 0 produces a schema that rejects data the database accepts.
quote_identifier
my $sql = sprintf 'DROP TABLE %s;', quote_identifier($table_name);
A name as SQLite takes it for an identifier in a statement: between double quotes, with every double quote inside it doubled. item becomes "item", order becomes "order", my table becomes "my table" and a"b becomes "a""b". It needs no database handle.
The name is always quoted, whatever it looks like. A name cannot be told from a reserved word by its shape -- order, group and index are made of the same letters as item -- and written bare such a word is a syntax error. Quoting changes nothing about which object is meant: SQLite does not fold the letter case of a name, quoted or not, so "Name" and Name are one table.
This is the one quoter of the driver. Every identifier it writes into a statement of its own goes through here: the DDL of DBIO::SQLite::DDL and of the DBIO::SQLite::Diff ops, and the PRAGMA calls of DBIO::SQLite::Introspect. The queries of the ORM do not -- how those are quoted is the SQL maker's business and that of the quote_names connect attribute.
pk_is_rowid_alias
my $alias = pk_is_rowid_alias($dbh, $table);
True when $table's primary key is the rowid alias -- the one key shape whose column cannot hold NULL. $table is a bare table name, without a schema prefix.
The test is PRAGMA index_list: SQLite materialises every primary key as an origin = 'pk' automatic index except the rowid alias, which is the rowid itself and needs no index. So "the table declares a key but has no pk-origin index" identifies the alias exactly, with no parsing of the stored CREATE TABLE text.
Nothing cheaper is correct here. PRAGMA table_info alone cannot tell the alias apart from a key column that holds NULL -- it reports type=INTEGER notnull=0 pk=1 for both id INTEGER PRIMARY KEY (the alias) and id INTEGER PRIMARY KEY DESC (not an alias, stores NULL). Type affinity is no better: INT has INTEGER affinity but only the exact declared type INTEGER makes an alias, so id INT PRIMARY KEY also stores NULL.
A table with no primary key at all has no pk-origin index either and so reads as "alias" here; that is harmless, because the answer is only ever consulted for a column PRAGMA table_info already marked pk. If the PRAGMA cannot be run the function reports true, which falls back to the DBIO::Introspect::Base answer (a key column is not nullable) rather than inventing a new one.
selectall_hashrefs
my $rows = selectall_hashrefs($dbh, 'PRAGMA index_list("item")');
Runs $sql and returns every row as a hash reference, like $dbh->selectall_arrayref($sql, { Slice => {} }), except that a statement without result columns yields [].
That is the case for a PRAGMA that has nothing to report -- the foreign_key_list of a table without keys -- or that the linked SQLite library does not know. DBI before 1.635 dies in fetchall_arrayref on such a statement ("invalid number of arguments" from bind_columns); later DBI returns an empty list. The helper gives the older DBI the later answer without looking at the DBI version: it asks the statement handle for NUM_OF_FIELDS and does not fetch when there are none. Every PRAGMA read of DBIO::SQLite::Introspect goes through here.
default_value_sql
my $sql = 'DEFAULT ' . default_value_sql($column->{default_value});
Renders a column default of the introspected model -- the dflt_value of PRAGMA table_xinfo -- as what has to follow the DEFAULT keyword. Shared by DBIO::SQLite::Diff::Table and DBIO::SQLite::Diff::Column, which both write that clause.
The value cannot be written back as it is read. SQLite's grammar takes a literal after DEFAULT or an expression in parentheses, and the PRAGMA reports the expression without them: DEFAULT (datetime('now')) reads as datetime('now'), and DEFAULT datetime('now') is a syntax error. Nor can everything be wrapped:
a number, a string or blob literal, and a keyword (
NULL,TRUE,CURRENT_TIMESTAMP, ...) are valid either way and are left as they are;an identifier --
DEFAULT abc,DEFAULT "abc", which SQLite reads as the string'abc'-- must not be wrapped:(abc)is a column reference, refused as "default value of column is not constant";everything else is an expression and gets the parentheses, including one that already begins and ends with one:
DEFAULT ((1)+(2))reads as(1)+(2).
The parentheses are harmless where they were not needed, so the rule errs towards them: only what is recognisably a single literal or identifier token goes without. An expression ending in a -- comment gets its closing parenthesis on a new line, where the comment cannot swallow it.
refuse_qualified_name
refuse_qualified_name(table => $name) if defined $name and !ref $name;
Throws a DBIO::Exception when $name, a table or view name a schema gave as a string, has a dot in it. SQLite has no schemas to qualify a name with: other.t would be read as the one table whose name is literally that, and nobody who writes it means that. The message names the kind, table or view, and the name as declared, refuses, and points at the declaration that is literal SQL, \'other.t', which is not touched by this.
split_type_size
my ($type, $size) = split_type_size('CHARACTER(10)'); # 'CHARACTER', 10
my ($type, $size) = split_type_size('NUMERIC(10, 2)'); # 'NUMERIC', [10, 2]
my ($type, $size) = split_type_size('TEXT'); # 'TEXT', undef
Takes a declared column type apart into the type and its size. SQLite keeps a column's length or precision inside the declared type text, and this is the one place that reads it back out: a number, or two numbers separated by a comma, in the parentheses at the end of the type. The size is the number, or [ $precision, $scale ] for two.
A type that does not end like that comes back whole, with undef for the size -- VARCHAR(max), TEXT, DOUBLE PRECISION. The introspected model (DBIO::SQLite::Introspect::Columns) and the compiled one ("target_from_compiled" in DBIO::SQLite::Diff) both go through it, so the two sides of a diff are always split the same way.
type_with_size
my $type = type_with_size('VARCHAR', 20); # 'VARCHAR(20)'
my $type = type_with_size('NUMERIC', [10, 2]); # 'NUMERIC(10,2)'
my $type = type_with_size('TEXT', undef); # 'TEXT'
The reverse of "split_type_size", for a statement written from the fields of a model column.
type_affinity
my $affinity = type_affinity($declared_type); # 'TEXT', 'INTEGER', ...
The affinity SQLite gives a column of that declared type: INTEGER, TEXT, BLOB, REAL or NUMERIC. The type is the text that follows the column's name in the CREATE TABLE -- what PRAGMA table_xinfo reports as type, what "install_ddl" in DBIO::SQLite::DDL writes there, the native_type of a compiled model.
It is SQLite's own rule (https://sqlite.org/datatype3.html, section 3.1): five questions about the letters of the type, in any letter case, asked in this order, and the first that is answered with yes decides:
- 1. it contains
INT--INTEGER; - 2. it contains
CHAR,CLOBorTEXT--TEXT; - 3. it contains
BLOB, or there is no type --BLOB; - 4. it contains
REAL,FLOAorDOUB--REAL; - 5. anything else --
NUMERIC.
The order is part of the rule: CHARINT holds CHAR and has INTEGER affinity. And the rule reads letters, not type names: DATE, BOOLEAN and STRING are NUMERIC, POINT is INTEGER. An undefined type is no type.
default_literal_sql
my $sql = 'DEFAULT ' . default_literal_sql($info->{default_value}, $type);
Turns a default_value that is a plain Perl value -- not a scalar ref -- into what follows the DEFAULT keyword. This is the one place where such a value becomes SQL. $type is the column's declared type, as "type_affinity" takes it.
Whether the value is a number or a string cannot be seen from the Perl scalar: '007' and 7 are both digits. The column decides it.
In a column with TEXT affinity the value is a string, and is always written as an SQL string literal: in apostrophes, with every apostrophe inside it doubled.
007becomes'007',10becomes'10',it'sbecomes'it''s'. Written bare,DEFAULT 007is the number 7, which the column's affinity stores as the text7, andDEFAULT 1.50arrives as1.5: the declared string never reaches a row.In any other column, and when no type is given, a number goes bare -- the canonical form of a number: no leading zeros, an optional leading minus, an optional fraction without trailing zeros (
5,-1,1.5) -- so that the column's default is a number and not a string holding one. Anything else is a string and is written as the literal described above. That includes a string of digits that is not the canonical number (007,1.50), which keeps its quotes and its value, and what SQLite would read as a number if it stood there bare but this rule does not name, such as1e3,.5or0x10.
"install_ddl" in DBIO::SQLite::DDL writes the result into the CREATE TABLE, and PRAGMA table_xinfo reports a default as the text it was declared with, so the result is also how DBIO::SQLite::Introspect reads that column back. "target_from_compiled" in DBIO::SQLite::Diff uses the same function for that reason: its model has to hold the default the way the introspector reports the installed one, or the two differ on a column nobody changed. Each of the two passes the type it gives the column.
A default given as a scalar ref is literal SQL and does not come through here. Not to be confused with "default_value_sql", which takes a default that is already SQL text.
sql_tokens
my @tokens = sql_tokens($statement); # ([ $kind, $text ], ...)
Splits the text of a statement into its lexical units, as far as the driver has to tell them apart to read a statement SQLite stored -- the sql of sqlite_master. This is the one lexer for that; DBIO::SQLite::Diff::Column reads a column's declaration with it, DBIO::SQLite::Diff::Rebuild finds the table's name, DBIO::SQLite::Diff::Index compares two CREATE INDEX statements, "comparable_statement" two views or two virtual tables, and DBIO::SQLite::Introspect::Indexes looks for a WHERE.
Each token is a [ $kind, $text ] pair, and the texts joined are the statement again, character for character. The kinds:
skip-- whitespace and comments (-- ...to the end of the line,/* ... */), any run of them as one token;quoted-- anything in quotes, in the four forms SQLite knows:'apostrophes',"double quotes",`backticks`(each with its own quote character doubled inside) and[brackets]. What is inside is never looked at, so a comma, a parenthesis or a keyword in a name or a string separates nothing;punct-- one of(,)and,;word-- everything else: a run of characters up to the next whitespace, quote, parenthesis, comma,/or-, or a single character that starts none of the above. A bare name or keyword is one word, and so ismain.item.
It is not SQLite's tokenizer and does not validate anything: operators and numbers are just words.
token_identifier
my $name = token_identifier($kind, $text);
my $name = token_identifier(@$token);
The name a token of "sql_tokens" stands for where SQLite's grammar has an identifier: the text of a word, and what is between the quotes of a quoted token, with a doubled quote character made one again. item, "item", `item` and [item] are all item, and "a""b" is a"b.
SQLite takes a name in any of the four quotings, apostrophes included, so 'item' is item here as well. Whether a token in apostrophes is a name or a string is a matter of where it stands, and the caller's to know.
create_name_index
my @tokens = sql_tokens($statement);
my $at = create_name_index(\@tokens, 'VIEW');
my $at = create_name_index(\@tokens, 'VIRTUAL', 'TABLE');
The index of the token that holds the object's name in a tokenised CREATE statement of the kind the words name: the first word or quoted token behind CREATE and those words, and behind IF NOT EXISTS when that is there. Whitespace and comments between them do not matter, nor does the letter case of the keywords. Returns undef for a statement that does not begin that way -- asked for a VIEW, a CREATE TABLE has no name.
SQLite stores a statement from the name on behind a CREATE TABLE, CREATE VIEW or CREATE VIRTUAL TABLE of its own, so TEMP, IF NOT EXISTS and a schema prefix never come back from sqlite_master. A statement that was not read from there and names its object as main.item has that whole word as its name token.
create_table_name_index
my @tokens = sql_tokens($create_table);
my $at = create_table_name_index(\@tokens);
my $name = token_identifier(@{ $tokens[$at] }) if defined $at;
"create_name_index" for a CREATE TABLE: the index of the token that holds the table's name. Returns undef for anything that does not begin that way -- a CREATE VIEW, a CREATE VIRTUAL TABLE.
comparable_statement
my $same = comparable_statement($old_sql, 'VIEW')
eq comparable_statement($new_sql, 'VIEW');
A stored CREATE statement in the form two of them are compared in, for the kind of statement the words name (as "create_name_index" takes them): its tokens without whitespace and comments, joined by one space, with the object's name written in the driver's own quoting, whichever quoting it came in. CREATE VIEW v AS SELECT 1, CREATE VIEW "v" AS SELECT 1 and
CREATE VIEW [v] -- the first
AS SELECT 1
are one statement that way. A database deployed before DBIO::SQLite::DDL quoted every name holds the first, the target of an upgrade the second.
Only the name is read. Everything behind it has to be the same token for token, in the letter case it is written in: it is SQL the schema gave -- a view's SELECT, the arguments of a virtual table's module -- and comes back the same from an unchanged schema. A statement that is not of that kind is its tokens joined and nothing else.
DBIO::SQLite::Diff compares views and virtual tables with it. DBIO::SQLite::Diff::Index reads more names than the first out of a CREATE INDEX and has a reader of its own.
AUTHOR
DBIO & DBIx::Class Authors
COPYRIGHT AND LICENSE
Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.