SQLite Rights Management
SQLite does not include management rights to access and modify data. The management is done by the file system of the operating system: if the file containing the database is not writable by a user, it also can not edit records and structure of the database data.
Rights management with GRANT and REVOKE is nonexistent, although they are part of the SQL-92 specification.
Using a SQLite database requires no installation or configuration.
SQLite Portability
The library is entirely written in ANSI-C, the standard version of the C programming language, and uses no external libraries other than the standard library of language. This makes SQLite compile without modification on all major computer architectures by providing a compiler complying with ANSI C.
The file database SQLite is entirely independent of the operating system and architecture on which they are used. The same database file can be used on two architectures with radically different operation, SQLite, providing an abstraction layer totally transparent to the developer. The files are compatible with each other for each major version of the library since version 3.0.0 of SQLite, so a file created with version 3.0.0 will be available with version 3.6.19 and vice versa, the files created between two different major versions (2.0.0 and 3.0.0 for example) may be compatible (especially in backward compatibility), but this is not always the case.
SQLite Data Types
SQLite uses dynamic typing for the content of cells, unlike almost all DBMS that use static typing: when creating a new table in the database, it is a recommended standard or affinity, not forced to store the data in the column is filled, not a type that defines how it will be represented in memory, this being reserved for the cell itself. When data are entered into the database, SQLite will attempt to convert the new data to the type recommended but will not do so if this is impossible.
There are several types of affinity in SQLite, the latter defining how SQLite will work at the entry of new data:
- TEXT: Saves the data as a string of unlimited size. If a number is entered in a column of this type, it will automatically be converted into a string;
- NUMERIC: attempts to save the data as a whole or as a real, but if this proves impossible, the data will be recorded as a string;
- INTEGER: Saves the data as a whole if it can be encoded without loss, but can use REAL or TEXT types if it can be done;
- REAL: saves the data as a real, even if it is an integer. If the value is too large, the data will be converted to string;
- NONE the data is stored as is, without conversion.
Thus, each type of affinity can accept any data type, the only exception is the particular type INTEGER PRIMARY KEY, when applied to a single column because it is not a usual type but an alias for the ROWID column inside the motor that matches the address of record, unique across the table.
The use of dynamic typing improves consistency between data from the database and the types of language used to query if it is also a dynamically typed language to (like Python, PHP, Perl or Ruby ) pose no real problems with using static typing languages (like C / C + + or Java).
SQLite – Determining the type of affinity
To maintain compatibility with other platforms, SQLite automatically converts the type names declared in the type affinity that best, thus:
- All type names containing the keyword will be recognized as INT INTEGER fields. However, only the declaration INTEGER PRIMARY KEY will be recognized as an alias for the ROWID;
- All type names containing the following keywords: CHAR (this includes VARCHAR), CLOB or TEXT fields will be recognized as TEXT;
- All type names containing the keyword will be recognized as BLOB fields affinity NONE;
- All type names containing the following keywords: REAL, FLOAT or DOUBLE field will be recognized as REAL;
- In all other cases, or if the type is not specified, the affinity is NUMERIC will be used.
SQLite Self Storage
Although SQLite uses dynamic typing, the memory representation and processing performed on the data requires the use of different classes of storage. This is only valid for version 3 and later versions, because the data were stored as strings in the previous versions.
All the data handled by the engine of database use the following types:
- NULL: data is the special type NULL, which indicates the lack of information or an undefined value;
- INTEGER *: the data is a signed integer and it is registered, according to the order of magnitude of 1, 2, 3, 4, 6 or 8 bytes;
- REAL: the data is a floating point number recorded by an 8-byte IEEE;
- TEXT: the data is a string, encoded in UTF-8 (default), UTF-16 or UTF-BE-16-LE;
- BLOB: the data is recorded as it was given.
NULL
The standard does not define exactly how the treaty should be null.
Like most RDBMSs, NULL all records are considered distinct from the UNIQUE constraint, but are considered identical by the UNION operator and the keyword DISTINCT.
Arithmetic operations including a NULL in their expression return the value UNKNOWN (indeterminate value). In the Boolean operations, the return value could be NULL if UNKNOWN occurs and the result can be determined with certainty: NULL OR gives a value of 1, but NULL OR 0 gives the value UNKNOWN because the operation can be resolved with certainty.
Dates
SQLite does not have a type to represent dates. However, there is a set of functions for manipulating them. Storing a date can be a string in its ISO 8601 or an integer in the form of a UNIX timestamp.
Constraints
SQLite manages constraints on one or more columns. The constraint NOT NULL, CHECK, DEFAULT and COLLATE are declared on the column while PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY can be reported on one or more columns.
The UNIQUE constraint automatically creates an index on the column or columns on which it is applied.
Continued.
