File Coverage

blib/lib/DBD/SQLite/GetInfo.pm
Criterion Covered Total %
statement 24 24 100.0
branch 1 2 50.0
condition 2 5 40.0
subroutine 10 10 100.0
pod 0 6 0.0
total 37 47 78.7


line stmt bran cond sub pod time code
1             package DBD::SQLite::GetInfo;
2              
3 9     9   302 use 5.006;
  9         37  
4 9     9   50 use strict;
  9         19  
  9         296  
5 9     9   58 use warnings;
  9         18  
  9         366  
6              
7 9     9   69 use DBD::SQLite;
  9         19  
  9         8212  
8              
9             # SQL_DRIVER_VER should be formatted as dd.dd.dddd
10             my $dbdversion = $DBD::SQLite::VERSION;
11             $dbdversion .= '_00' if $dbdversion =~ /^\d+\.\d+$/;
12             my $sql_driver_ver = sprintf("%02d.%02d.%04d", split(/[\._]/, $dbdversion));
13              
14             # Full list of keys and their return types: DBI::Const::GetInfo::ODBC
15              
16             # Most of the key definitions can be gleaned from:
17             #
18             # https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetinfo-function
19              
20             our %info = (
21             20 => 'N', # SQL_ACCESSIBLE_PROCEDURES - No stored procedures to access
22             19 => 'Y', # SQL_ACCESSIBLE_TABLES - SELECT access to all tables in table_info
23             0 => 0, # SQL_ACTIVE_CONNECTIONS - No maximum connection limit
24             116 => 0, # SQL_ACTIVE_ENVIRONMENTS - No "active environment" limit
25             1 => 0, # SQL_ACTIVE_STATEMENTS - No concurrent activity limit
26             169 => 127, # SQL_AGGREGATE_FUNCTIONS - Supports all SQL-92 aggregrate functions
27             117 => 0, # SQL_ALTER_DOMAIN - No ALTER DOMAIN support
28             86 => 1, # SQL_ALTER_TABLE - Only supports ADD COLUMN and table rename (not listed in enum) in ALTER TABLE statements
29             10021 => 0, # SQL_ASYNC_MODE - No asynchronous support (in vanilla SQLite)
30             120 => 0, # SQL_BATCH_ROW_COUNT - No special row counting access
31             121 => 0, # SQL_BATCH_SUPPORT - No batches
32             82 => 0, # SQL_BOOKMARK_PERSISTENCE - No bookmark support
33             114 => 1, # SQL_CATALOG_LOCATION - Database comes first in identifiers
34             10003 => 'Y', # SQL_CATALOG_NAME - Supports database names
35             41 => '.', # SQL_CATALOG_NAME_SEPARATOR - Separated by dot
36             42 => 'database', # SQL_CATALOG_TERM - SQLite calls catalogs databases
37             92 => 1+4+8, # SQL_CATALOG_USAGE - Supported in calls to DML & table/index definiton (no procedures or permissions)
38             10004 => 'UTF-8', # SQL_COLLATION_SEQ - SQLite 3 uses UTF-8 by default
39             87 => 'Y', # SQL_COLUMN_ALIAS - Supports column aliases
40             22 => 0, # SQL_CONCAT_NULL_BEHAVIOR - 'a'||NULL = NULL
41              
42             # SQLite has no CONVERT function, only CAST. However, it converts to every "affinity" it supports.
43             #
44             # The only SQL_CVT_* types it doesn't support are date/time types, as it has no concept of
45             # date/time values once inserted. These are only convertable to text-like types. GUIDs are in
46             # the same boat, having no real means of switching to a numeric format.
47             #
48             # text/binary types = 31723265
49             # numeric types = 28926
50             # date/time types = 1802240
51             # total = 33554431
52              
53             48 => 1, # SQL_CONVERT_FUNCTIONS - CAST only
54              
55             53 => 31723265+28926, # SQL_CONVERT_BIGINT
56             54 => 31723265+28926, # SQL_CONVERT_BINARY
57             55 => 31723265+28926, # SQL_CONVERT_BIT
58             56 => 33554431, # SQL_CONVERT_CHAR
59             57 => 31723265+1802240, # SQL_CONVERT_DATE
60             58 => 31723265+28926, # SQL_CONVERT_DECIMAL
61             59 => 31723265+28926, # SQL_CONVERT_DOUBLE
62             60 => 31723265+28926, # SQL_CONVERT_FLOAT
63             173 => 31723265, # SQL_CONVERT_GUID
64             61 => 31723265+28926, # SQL_CONVERT_INTEGER
65             123 => 31723265+1802240, # SQL_CONVERT_INTERVAL_DAY_TIME
66             124 => 31723265+1802240, # SQL_CONVERT_INTERVAL_YEAR_MONTH
67             71 => 31723265+28926, # SQL_CONVERT_LONGVARBINARY
68             62 => 31723265+28926, # SQL_CONVERT_LONGVARCHAR
69             63 => 31723265+28926, # SQL_CONVERT_NUMERIC
70             64 => 31723265+28926, # SQL_CONVERT_REAL
71             65 => 31723265+28926, # SQL_CONVERT_SMALLINT
72             66 => 31723265+1802240, # SQL_CONVERT_TIME
73             67 => 31723265+1802240, # SQL_CONVERT_TIMESTAMP
74             68 => 31723265+28926, # SQL_CONVERT_TINYINT
75             69 => 33554431, # SQL_CONVERT_VARBINARY
76             70 => 33554431, # SQL_CONVERT_VARCHAR
77             122 => 33554431, # SQL_CONVERT_WCHAR
78             125 => 33554431, # SQL_CONVERT_WLONGVARCHAR
79             126 => 33554431, # SQL_CONVERT_WVARCHAR
80              
81             74 => 1, # SQL_CORRELATION_NAME - Table aliases are supported, but must be named differently
82             127 => 0, # SQL_CREATE_ASSERTION - No CREATE ASSERTION support
83             128 => 0, # SQL_CREATE_CHARACTER_SET - No CREATE CHARACTER SET support
84             129 => 0, # SQL_CREATE_COLLATION - No CREATE COLLATION support
85             130 => 0, # SQL_CREATE_DOMAIN - No CREATE DOMAIN support
86             131 => 0, # SQL_CREATE_SCHEMA - No CREATE SCHEMA support
87             132 => 16383-2-8-4096, # SQL_CREATE_TABLE - Most of the functionality of CREATE TABLE support
88             133 => 0, # SQL_CREATE_TRANSLATION - No CREATE TRANSLATION support
89             134 => 1, # SQL_CREATE_VIEW - CREATE VIEW, no WITH CHECK OPTION support
90              
91             23 => 2, # SQL_CURSOR_COMMIT_BEHAVIOR - Cursors are preserved
92             24 => 2, # SQL_CURSOR_ROLLBACK_BEHAVIOR - Cursors are preserved
93             10001 => 0, # SQL_CURSOR_SENSITIVITY - Cursors have a concept of snapshots, though this depends on the transaction type
94              
95             2 => \&sql_data_source_name, # SQL_DATA_SOURCE_NAME - The DSN
96             25 => \&sql_data_source_read_only, # SQL_DATA_SOURCE_READ_ONLY - Might have a SQLITE_OPEN_READONLY flag
97             16 => \&sql_database_name, # SQL_DATABASE_NAME - Self-explanatory
98             119 => 0, # SQL_DATETIME_LITERALS - No support for SQL-92's super weird date/time literal format (ie: {d '2999-12-12'})
99             17 => 'SQLite', # SQL_DBMS_NAME - You are here
100             18 => \&sql_dbms_ver, # SQL_DBMS_VER - This driver version
101             170 => 1+2, # SQL_DDL_INDEX - Supports CREATE/DROP INDEX
102             26 => 8, # SQL_DEFAULT_TXN_ISOLATION - Default is SERIALIZABLE (See "PRAGMA read_uncommitted")
103             10002 => 'N', # SQL_DESCRIBE_PARAMETER - No DESCRIBE INPUT support
104              
105             # XXX: MySQL/Oracle fills in HDBC and HENV, but information on what should actually go there is
106             # hard to acquire.
107              
108             # 171 => undef, # SQL_DM_VER - Not a Driver Manager
109             # 3 => undef, # SQL_DRIVER_HDBC - Not a Driver Manager
110             # 135 => undef, # SQL_DRIVER_HDESC - Not a Driver Manager
111             # 4 => undef, # SQL_DRIVER_HENV - Not a Driver Manager
112             # 76 => undef, # SQL_DRIVER_HLIB - Not a Driver Manager
113             # 5 => undef, # SQL_DRIVER_HSTMT - Not a Driver Manager
114             6 => 'libsqlite3odbc.so', # SQL_DRIVER_NAME - SQLite3 ODBC driver (if installed)
115             77 => '03.00', # SQL_DRIVER_ODBC_VER - Same as sqlite3odbc.c
116             7 => $sql_driver_ver, # SQL_DRIVER_VER - Self-explanatory
117              
118             136 => 0, # SQL_DROP_ASSERTION - No DROP ASSERTION support
119             137 => 0, # SQL_DROP_CHARACTER_SET - No DROP CHARACTER SET support
120             138 => 0, # SQL_DROP_COLLATION - No DROP COLLATION support
121             139 => 0, # SQL_DROP_DOMAIN - No DROP DOMAIN support
122             140 => 0, # SQL_DROP_SCHEMA - No DROP SCHEMA support
123             141 => 1, # SQL_DROP_TABLE - DROP TABLE support, no RESTRICT/CASCADE
124             142 => 0, # SQL_DROP_TRANSLATION - No DROP TRANSLATION support
125             143 => 1, # SQL_DROP_VIEW - DROP VIEW support, no RESTRICT/CASCADE
126              
127             # NOTE: This is based purely on what sqlite3odbc supports.
128             #
129             # Static CA1: NEXT, ABSOLUTE, RELATIVE, BOOKMARK, LOCK_NO_CHANGE, POSITION, UPDATE, DELETE, REFRESH,
130             # BULK_ADD, BULK_UPDATE_BY_BOOKMARK, BULK_DELETE_BY_BOOKMARK = 466511
131             #
132             # Forward-only CA1: NEXT, BOOKMARK
133             #
134             # CA2: READ_ONLY_CONCURRENCY, LOCK_CONCURRENCY
135              
136             144 => 0, # SQL_DYNAMIC_CURSOR_ATTRIBUTES1 - No dynamic cursor support
137             145 => 0, # SQL_DYNAMIC_CURSOR_ATTRIBUTES2 - No dynamic cursor support
138             146 => 1+8, # SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1
139             147 => 1+2, # SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2
140             150 => 0, # SQL_KEYSET_CURSOR_ATTRIBUTES1 - No keyset cursor support
141             151 => 0, # SQL_KEYSET_CURSOR_ATTRIBUTES2 - No keyset cursor support
142             167 => 466511, # SQL_STATIC_CURSOR_ATTRIBUTES1
143             168 => 1+2, # SQL_STATIC_CURSOR_ATTRIBUTES2
144              
145             27 => 'Y', # SQL_EXPRESSIONS_IN_ORDERBY - ORDER BY allows expressions
146             8 => 63, # SQL_FETCH_DIRECTION - Cursors support next, first, last, prior, absolute, relative
147             84 => 2, # SQL_FILE_USAGE - Single-tier driver, treats files as databases
148             81 => 1+2+8, # SQL_GETDATA_EXTENSIONS - Same as sqlite3odbc.c
149             88 => 3, # SQL_GROUP_BY - SELECT columns are independent of GROUP BY columns
150             28 => 4, # SQL_IDENTIFIER_CASE - Not case-sensitive, stored in mixed case
151             29 => '"', # SQL_IDENTIFIER_QUOTE_CHAR - Uses " for identifiers, though supports [] and ` as well
152             148 => 0, # SQL_INDEX_KEYWORDS - No support for ASC/DESC/ALL for CREATE INDEX
153             149 => 0, # SQL_INFO_SCHEMA_VIEWS - No support for INFORMATION_SCHEMA
154             172 => 1+2, # SQL_INSERT_STATEMENT - INSERT...VALUES & INSERT...SELECT
155             73 => 'N', # SQL_INTEGRITY - No support for "Integrity Enhancement Facility"
156             89 => \&sql_keywords, # SQL_KEYWORDS - List of non-ODBC keywords
157             113 => 'Y', # SQL_LIKE_ESCAPE_CLAUSE - Supports LIKE...ESCAPE
158             78 => 1, # SQL_LOCK_TYPES - Only NO_CHANGE
159              
160             10022 => 0, # SQL_MAX_ASYNC_CONCURRENT_STATEMENTS - No async mode
161             112 => 1_000_000, # SQL_MAX_BINARY_LITERAL_LEN - SQLITE_MAX_SQL_LENGTH
162             34 => 1_000_000, # SQL_MAX_CATALOG_NAME_LEN - SQLITE_MAX_SQL_LENGTH
163             108 => 1_000_000, # SQL_MAX_CHAR_LITERAL_LEN - SQLITE_MAX_SQL_LENGTH
164             97 => 2000, # SQL_MAX_COLUMNS_IN_GROUP_BY - SQLITE_MAX_COLUMN
165             98 => 2000, # SQL_MAX_COLUMNS_IN_INDEX - SQLITE_MAX_COLUMN
166             99 => 2000, # SQL_MAX_COLUMNS_IN_ORDER_BY - SQLITE_MAX_COLUMN
167             100 => 2000, # SQL_MAX_COLUMNS_IN_SELECT - SQLITE_MAX_COLUMN
168             101 => 2000, # SQL_MAX_COLUMNS_IN_TABLE - SQLITE_MAX_COLUMN
169             30 => 1_000_000, # SQL_MAX_COLUMN_NAME_LEN - SQLITE_MAX_SQL_LENGTH
170             1 => 1021, # SQL_MAX_CONCURRENT_ACTIVITIES - Typical filehandle limits
171             31 => 1_000_000, # SQL_MAX_CURSOR_NAME_LEN - SQLITE_MAX_SQL_LENGTH
172             0 => 1021, # SQL_MAX_DRIVER_CONNECTIONS - Typical filehandle limits
173             10005 => 1_000_000, # SQL_MAX_IDENTIFIER_LEN - SQLITE_MAX_SQL_LENGTH
174             102 => 2147483646*65536, # SQL_MAX_INDEX_SIZE - Tied to DB size, which is theortically 140TB
175             32 => 1_000_000, # SQL_MAX_OWNER_NAME_LEN - SQLITE_MAX_SQL_LENGTH
176             33 => 1_000_000, # SQL_MAX_PROCEDURE_NAME_LEN - SQLITE_MAX_SQL_LENGTH
177             34 => 1_000_000, # SQL_MAX_QUALIFIER_NAME_LEN - SQLITE_MAX_SQL_LENGTH
178             104 => 1_000_000, # SQL_MAX_ROW_SIZE - SQLITE_MAX_SQL_LENGTH (since INSERT has to be used)
179             103 => 'Y', # SQL_MAX_ROW_SIZE_INCLUDES_LONG
180             32 => 1_000_000, # SQL_MAX_SCHEMA_NAME_LEN - SQLITE_MAX_SQL_LENGTH
181             105 => 1_000_000, # SQL_MAX_STATEMENT_LEN - SQLITE_MAX_SQL_LENGTH
182             106 => 64, # SQL_MAX_TABLES_IN_SELECT - 64 tables, because of the bitmap in the query optimizer
183             35 => 1_000_000, # SQL_MAX_TABLE_NAME_LEN - SQLITE_MAX_SQL_LENGTH
184             107 => 0, # SQL_MAX_USER_NAME_LEN - No user support
185              
186             37 => 'Y', # SQL_MULTIPLE_ACTIVE_TXN - Supports mulitple txns, though not nested
187             36 => 'N', # SQL_MULT_RESULT_SETS - No batches
188             111 => 'N', # SQL_NEED_LONG_DATA_LEN - Doesn't care about LONG
189             75 => 1, # SQL_NON_NULLABLE_COLUMNS - Supports NOT NULL
190             85 => 1, # SQL_NULL_COLLATION - NULLs first on ASC (low end)
191             49 => 4194304+1, # SQL_NUMERIC_FUNCTIONS - Just ABS & ROUND (has RANDOM, but not RAND)
192              
193             9 => 1, # SQL_ODBC_API_CONFORMANCE - Same as sqlite3odbc.c
194             152 => 1, # SQL_ODBC_INTERFACE_CONFORMANCE - Same as sqlite3odbc.c
195             12 => 0, # SQL_ODBC_SAG_CLI_CONFORMANCE - Same as sqlite3odbc.c
196             15 => 0, # SQL_ODBC_SQL_CONFORMANCE - Same as sqlite3odbc.c
197             10 => '03.00', # SQL_ODBC_VER - Same as sqlite3odbc.c
198              
199             115 => 1+8+16+32+64, # SQL_OJ_CAPABILITIES - Supports all OUTER JOINs except RIGHT & FULL
200             90 => 'N', # SQL_ORDER_BY_COLUMNS_IN_SELECT - ORDER BY columns don't have to be in the SELECT list
201             38 => 'Y', # SQL_OUTER_JOINS - Supports OUTER JOINs
202             153 => 2, # SQL_PARAM_ARRAY_ROW_COUNTS - Only has row counts for executed statements
203             154 => 3, # SQL_PARAM_ARRAY_SELECTS - No support for arrays of parameters
204             80 => 0, # SQL_POSITIONED_STATEMENTS - No support for positioned statements (WHERE CURRENT OF or SELECT FOR UPDATE)
205             79 => 31, # SQL_POS_OPERATIONS - Supports all SQLSetPos operations
206             21 => 'N', # SQL_PROCEDURES - No procedures
207             40 => '', # SQL_PROCEDURE_TERM - No procedures
208             93 => 4, # SQL_QUOTED_IDENTIFIER_CASE - Even quoted identifiers are case-insensitive
209             11 => 'N', # SQL_ROW_UPDATES - No fancy cursor update support
210             39 => '', # SQL_SCHEMA_TERM - No schemas
211             91 => 0, # SQL_SCHEMA_USAGE - No schemas
212             43 => 2, # SQL_SCROLL_CONCURRENCY - Updates/deletes on cursors lock the database
213             44 => 1+16, # SQL_SCROLL_OPTIONS - Only supports static & forward-only cursors
214             14 => '\\', # SQL_SEARCH_PATTERN_ESCAPE - Default escape character for LIKE is \
215             13 => \&sql_server_name, # SQL_SERVER_NAME - Just $dbh->{Name}
216             94 => '', # SQL_SPECIAL_CHARACTERS - Other drivers tend to stick to the ASCII/Latin-1 range, and SQLite uses all of
217             # the lower 7-bit punctuation for other things
218              
219             155 => 7, # SQL_SQL92_DATETIME_FUNCTIONS - Supports CURRENT_(DATE|TIME|TIMESTAMP)
220             156 => 1+2+4+8, # SQL_SQL92_FOREIGN_KEY_DELETE_RULE - Support all ON DELETE options
221             157 => 1+2+4+8, # SQL_SQL92_FOREIGN_KEY_UPDATE_RULE - Support all ON UPDATE options
222             158 => 0, # SQL_SQL92_GRANT - No users; no support for GRANT
223             159 => 0, # SQL_SQL92_NUMERIC_VALUE_FUNCTIONS - No support for any of the listed functions
224             160 => 1+2+4+512+1024+2048+4096+8192, # SQL_SQL92_PREDICATES - Supports the important comparison operators
225             161 => 2+16+64+128, # SQL_SQL92_RELATIONAL_JOIN_OPERATORS - Supports the important ones except RIGHT/FULL OUTER JOINs
226             162 => 0, # SQL_SQL92_REVOKE - No users; no support for REVOKE
227             163 => 1+2+8, # SQL_SQL92_ROW_VALUE_CONSTRUCTOR - Supports most row value constructors
228             164 => 2+4, # SQL_SQL92_STRING_FUNCTIONS - Just UPPER & LOWER (has SUBSTR, but not SUBSTRING and SQL-92's weird TRIM syntax)
229             165 => 1+2+4+8, # SQL_SQL92_VALUE_EXPRESSIONS - Supports all SQL-92 value expressions
230              
231             118 => 1, # SQL_SQL_CONFORMANCE - SQL-92 Entry level
232             83 => 0, # SQL_STATIC_SENSITIVITY - Cursors would lock the DB, so only old data is visible
233             50 => 8+16+256+1024+16384+131072, # SQL_STRING_FUNCTIONS - LTRIM, LENGTH, REPLACE, RTRIM, CHAR, SOUNDEX
234             95 => 1+2+4+8+16, # SQL_SUBQUERIES - Supports all of the subquery types
235             51 => 4, # SQL_SYSTEM_FUNCTIONS - Only IFNULL
236             45 => 'table', # SQL_TABLE_TERM - Tables are called tables
237             109 => 0, # SQL_TIMEDATE_ADD_INTERVALS - No support for INTERVAL
238             110 => 0, # SQL_TIMEDATE_DIFF_INTERVALS - No support for INTERVAL
239             52 => 0x20000+0x40000+0x80000, # SQL_TIMEDATE_FUNCTIONS - Only supports CURRENT_(DATE|TIME|TIMESTAMP)
240             46 => 2, # SQL_TXN_CAPABLE - Full transaction support for both DML & DDL
241             72 => 1+8, # SQL_TXN_ISOLATION_OPTION - Supports read uncommitted and serializable
242             96 => 1+2, # SQL_UNION - Supports UNION and UNION ALL
243             47 => '', # SQL_USER_NAME - No users
244              
245             166 => 1, # SQL_STANDARD_CLI_CONFORMANCE - X/Open CLI Version 1.0
246             10000 => 1992, # SQL_XOPEN_CLI_YEAR - Year for V1.0
247             );
248              
249             sub sql_dbms_ver {
250 1     1 0 3 my $dbh = shift;
251 1         7 return $dbh->FETCH('sqlite_version');
252             }
253              
254             sub sql_data_source_name {
255 1     1 0 3 my $dbh = shift;
256 1         4 return "dbi:SQLite:".$dbh->{Name};
257             }
258              
259             sub sql_data_source_read_only {
260 1     1 0 3 my $dbh = shift;
261 1   50     15 my $flags = $dbh->FETCH('sqlite_open_flags') || 0;
262 1 50 33     10 return $dbh->{ReadOnly} || ($flags & DBD::SQLite::OPEN_READONLY()) ? 'Y' : 'N';
263             }
264              
265             sub sql_database_name {
266 1     1 0 3 my $dbh = shift;
267 1         11 my $databases = $dbh->selectall_hashref('PRAGMA database_list', 'seq');
268 1         243 return $databases->{0}{name};
269             }
270              
271             sub sql_keywords {
272             # SQLite keywords minus ODBC keywords
273 1     1 0 3 return join ',', (qw<
274             ABORT AFTER ANALYZE ATTACH AUTOINCREMENT BEFORE CONFLICT DATABASE DETACH EACH EXCLUSIVE
275             EXPLAIN FAIL GLOB IF IGNORE INDEXED INSTEAD ISNULL LIMIT NOTNULL OFFSET
276             PLAN PRAGMA QUERY RAISE RECURSIVE REGEXP REINDEX RELEASE RENAME REPLACE ROW
277             SAVEPOINT TEMP TRIGGER VACUUM VIRTUAL WITHOUT
278             >);
279             }
280              
281             sub sql_server_name {
282 1     1 0 3 my $dbh = shift;
283 1         4 return $dbh->{Name};
284             }
285              
286             1;
287              
288             __END__