Title: Theory / Unimportant / CS / Programming / Sqlite / general
Tags: hasNotNewEffectReturnsMeaning FromInstruction computer_to_instruction some_instruction fromImplication type FromVariables automatedNewLineAfterCommaSpace examplesSwitchExample1 has_seen_instruction_2 mathToSee récupéré seen_description
On every note of this deck, unless a note says otherwise:
Language: sqlite

Definition: List of sql statements separated by ;
Chapter: http://sqlite.org/lang.html
Notation: sql-stmt-list

Definition: (Explain (query plan)?)? statement
Chapter: http://sqlite.org/lang.html
Notation: sql-stmt

Question: How to alter a table, if the alteration is not supported in sqlite ?
Answer: Create a new table, copy content, drop old table, rename the new table to old's name. Also copy every properties
Chapter: http://sqlite.org/lang_altertable.html

Definition: column-name type-name? colunm-constraint*
Chapter: http://sqlite.org/lang_altertable.html
Notation: column-def
Extra: sql-lite

Name: A constant expression
Definition: An expression which does contains no sub-queries, column or table references, bound parameters, or string literals enclosed in double-quotes instead of single-quotes.
Chapter: http://sqlite.org/lang_createtable.html
Example: sql-lite's default value

Instruction: COLLATE collation-name
Effect: specifies the name of a collating sequence to use as the default collation sequence for the column.
Chapter: http://sqlite.org/lang_createtable.html
Extra: If no COLLATE clause is specified, the default collation sequence is BINARY.
Where to use it: sql-lite's column constraint

Question: Number of primary key
Answer: At most one.
Chapter: http://sqlite.org/lang_createtable.html
Example: sqlite

Name: integer primarey key
Definition: A primary key column, whose type is INTEGER, the table is with ROWID
Chapter: http://sqlite.org/lang_createtable.html
Example: sqlite's constraint

Question: Inserting a null value in rowid
Answer: the system choose a new rowid
Chapter: http://sqlite.org/lang_createtable.html

Typ: null
Meaning: The null value
Language: sqlite
Chapter: http://sqlite.org/datatype3.html

Typ: INTEGER
Meaning: Signed integer, stored in 1, 2, 3, 4, 7 or 8 bytes depending on its magnitude
Language: sqlite
Chapter: http://sqlite.org/datatype3.html

Typ: REAL
Meaning: Floating point value, stored as an 8-byte IEEE floating point number
Language: sqlite
Chapter: http://sqlite.org/datatype3.html

Typ: TEXT
Meaning: String, stored using the database encoding
Language: sqlite
Chapter: http://sqlite.org/datatype3.html

Typ: BLOB
Meaning: Blob of data, stored exactly as it was input
Language: sqlite
Chapter: http://sqlite.org/datatype3.html

Name: storage class
Typ: A set of types
Section: 2
Chapter: http://sqlite.org/datatype3.html
Context: SQLite

Typ: Boolean
Meaning: False is a value whose cast to numeric is 0 or 0.0
Null is a value whose cast to numeric is NULL
True is everything else
Language: sqlite
Chapter: http://sqlite.org/datatype3.html
Section: 2

Name: \(C\)'s affinity
Definition: A prefered type for data in this column
Chapter: http://sqlite.org/datatype3.html
Section: 3
Variables: \(C\) a column

Question: What happens when inserting \(V\) in \(C\)
Answer: Type is converted if the conversion is lossless (15 significative bit preserved for real) and not converted otherwise
Chapter: http://sqlite.org/datatype3.html
Section: 3
Variables: \(C\) a column with type affinity \(A\)
\(V\) a value of type \(T\)

Smaller0: \(t\) contains the subword "int"
Greater0: the type affinity of \(C\) is integer
Chapter: http://sqlite.org/datatype3.html
Section: 3.1
Variables: \(C\) a column with declared type \(t\)
Hide middle: yes

Smaller0: \(t\) contains the subword "char", "clob" or "text"
Greater0: the type affinity of \(C\) is text
Chapter: http://sqlite.org/datatype3.html
Section: 3.1
Variables: \(C\) a column with declared type \(t\)

Smaller0: \(t\) contains the subword "blob" or is not specified
Greater0: the type affinity of \(C\) is blob
Chapter: http://sqlite.org/datatype3.html
Section: 3.1
Variables: \(C\) a column with declared type \(t\)

Smaller0: \(t\) contains the subword "real", "floa" or "doub"
Greater0: the type affinity of \(C\) is real
Chapter: http://sqlite.org/datatype3.html
Section: 3.1
Variables: \(C\) a column with declared type \(t\)

Smaller0: the word \(t\) is not of the form of the 4 other type affinity
Greater0: the type affinity of \(C\) is numeric
Chapter: http://sqlite.org/datatype3.html
Section: 3.1
Variables: \(C\) a column with declared type \(t\)

Name: factored-select-stmt
Definition: simple-select-statement, with potentially many select-core, separated by compound-operator
Chapter: http://sqlite.org/syntax/factored-select-stmt.html
Extra: sqlite

Name: compound operator
Definition: UNION, UNION ALL, INTERSECT and EXCEPT
Chapter: http://sqlite.org/syntax/compound-operator.html
Extra: sqlite

Name: simple-select-stmt
Definition: (WITH (RECURSIVE)? common-table-expression,+)? select-core (ORDER B ordering-term,+)? (LIMIT expr ((OFFSET | ,)expr)?)?
Chapter: http://sqlite.org/lang_select.html
Extra: sqlite

Name: select-core
Definition: Either
Select (distinct|all)? result-column,+ (FROM (table-or-subquery,+|join-clause))? (WHERE expr)? (GROUP BY expr,+ (HAVING expr)?)?
or
VALUE ( \ (expr,+ \ )),+
Chapter: http://sqlite.org/lang_select.html
Extra: sqlite

-From clause
-where clause
-groups by, having and result-column
-DISTINCT/ALL
Chapter: http://sqlite.org/lang_select.html
Name: Evaluation of a simple query
Example: sqlite

Name: An aggregate simple select statement
Definition: It contains a GROUP BY clause or aggregate functions in the result set
Chapter: http://sqlite.org/lang_select.html
Extra: sqlite

Name: A non-aggregate simple select statement
Definition: A simple select statement which is not an aggregate simple select statement
Chapter: http://sqlite.org/lang_select.html
Extra: sqlite

Definition: The function sending \(A\subseteq\mathbb A^n\), to the set of polynomials \(f\in k[x_1,\dots,x_n]\) which vanishes for all \(\vec a\in A\)
Page: 661
Chapter: 15
Section: 1
Variables: \(k\) a field,
Notation: \(\mathcal I\)
Index: 6
Kind: Ex
Typ: Contravariant

Definition: The set of polynomials \(f\in k[x_1,\dots,x_n]\) which vanishes for all \(\vec a\in A\)
Page: 660
Chapter: 15
Section: 1
Variables: \(n\in\mathbb N\),
\(k\) a field,
\(A\subseteq\mathbb A^n\)
Notation: \(\mathcal I(A)\)
Typ: An ideal

Instruction: without rowid
Effect: State that the created table should not have a rowid column
Chapter: http://sqlite.org/lang_createtable.html
Where to use it: end of a CREATE TABLE instruction
Assuming: The table have some primary key

Instruction: IF NOT EXISTS
Effect: The table/view is not created with another one with the same name already exists.
Chapter: http://sqlite.org/lang_createtable.html
Where to use it: after a CREATE table/view instruction

Instruction: TEMP or TEMPORARY
Effect: What is created is deleted when the database is closed
Chapter: http://sqlite.org/lang_createtable.html
Where to use it: after a CREATE instruction

Question: How to store date and time ?
Answer: stored either as text, real or integer, using sqlite conversion function
Chapter: http://sqlite.org/datatype3.html
Section: 2

Instruction: insert into foo DEFAULT VALUES
Page: https://sqlite.org/lang_insert.html
Effect: Insert into the table foo a row populated with the default values

Instruction: insert into foo VALUES (buz)
Page: https://sqlite.org/lang_insert.html
Effect: Insert into the table foo rows populated with the values of buz
Assuming: buz have the same number of columns as the table

Instruction: insert into foo SELECT (buz)
Page: https://sqlite.org/lang_insert.html
Effect: Insert into the table foo the result of the expression buz
Assuming: buz have the same number of columns as the table

Instruction: (list of columns)
Page: https://sqlite.org/lang_insert.html
Effect: The following expression only consider the columns of list of columns, other columns are populated with default values.
Context: After INSERT INTO table

Instruction: REPLACE
Page: https://sqlite.org/lang_insert.html
Effect: Abbreviation for INSERT or REPLACE

Instruction: INSERT or foo
Page: https://sqlite.org/lang_insert.html
Effect: Abbreviation for INSERT ON CONFLICT foo

Instruction: Distinct
Effect: duplicate elements are filtered before being passed into the aggregate function
Context: sql-lite
Chapter: http://sqlite.org/lang_aggfunc.html
Where to use it: agregate function

Instruction: avg(X)
Context: sql-lite's
Chapter: http://sqlite.org/lang_aggfunc.html
Returns: the average value of all non-NULL X within a group. String and BLOB values that do not look like numbers are interpreted as 0.
Typ: float or NULL

Instruction: count(X)
Context: sql-lite
Chapter: http://sqlite.org/lang_aggfunc.html
Returns: a count of the number of times that X is not NULL in a group.
Where to use it: select
Typ: int

Instruction: group_concat(X,Y)
Effect: a string which is the concatenation of all non-NULL values of X. If parameter Y is present then it is used as the separator between instances of X.
Context: sql-lite
Chapter: http://sqlite.org/lang_aggfunc.html
Typ: string

Instruction: min(X)
Context: sql-lite
Chapter: http://sqlite.org/lang_aggfunc.html
Returns: The minimum value of all values in the group. The minimum value is the value that would be returned first in an ORDER BY on the same column
Typ: type of X,
  or null

Instruction: sum(X)
Context: sql-lite
Chapter: http://sqlite.org/lang_aggfunc.html
Returns: sum of all non-NULL values in the group, assuming one such value exists
Typ: Integer if X contains only integer, null if it contains only null values, float otherwise

Instruction: total(X)
Context: sql-lite
Chapter: http://sqlite.org/lang_aggfunc.html
Returns: Sum of all non-NULL values in the group
Typ: Float

Instruction: ALTER TABLE (schema-name.)?table-name RENAME TO new-table-name
Effect: Rename table-name (in schema-name) to new-table-name. Changes accordingly the foreign key of other tables, but no triggers or select statements withing views
Context: sql-lite
Chapter: http://sqlite.org/lang_altertable.html

Instruction: ALTER TABLE table_name ADD (COLUMN)? column-def
Effect: add a new column to an existing table. The new column is always appended to the end of the list of existing columns.
Context: sql-lite
Chapter: http://sqlite.org/lang_altertable.html
Assuming: No primary key, no unique constraint,
default value is not CURRENT_(time, date nor stamp), no expression in parentheses
Not null constraint implies a non-null default value
If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.

Instruction: CREATE TABLE foo AS (bar)
Effect: Execute bar as a select statement, assign its the name foo and make it a new table.
Chapter: http://sqlite.org/lang_createtable.html

Instruction: CREATE TABLE foo (column-def,+ table-constraint,*)
Effect: Create a table named foo.
This table contains columns from column-def, must satisfies constraint from table-constraint.
Chapter: http://sqlite.org/lang_createtable.html

Variable: sqlite_ prefix
Meaning: Tables reserved for sqlite internal use
Context: sqlite
Chapter: http://sqlite.org/lang_createtable.html

Instruction: DEFAULT (
  signed-number,
  literal-value,
  (expr)
)
Effect: Gives the default value of the column
Chapter: http://sqlite.org/lang_createtable.html
Typ: column-constraint

Instruction: CURRENT_TIME
Effect: the current time
Chapter: http://sqlite.org/lang_createtable.html
Extra: «sql-lite's default value» was in module. Why ?

Instruction: CURRENT_DATE
Effect: the current date
Chapter: http://sqlite.org/lang_createtable.html
Extra: «sql-lite's default value» was in module. Why ?

Instruction: CURRENT_TIMESTAMP
Effect: the current timestamp
Chapter: http://sqlite.org/lang_createtable.html
Extra: «sql-lite's default value» was in module. Why ?

Variable: SQLITE_MAX_COLUMN
Meaning: maximal number of column of a table
Context: sqlite
Chapter: http://sqlite.org/lang_createtable.html
Initialization: 2000
Value: Up to 32767

Variable: SQLITE_MAX_LENGTH
Meaning: The maximum number of bytes in a string or BLOB in SQLite
Context: sqlite
Chapter: http://sqlite.org/lang_createtable.html
Initialization: 1 billion

Instruction: UNIQUE
Effect: state that the current columns, or the arguments, must contains distinct values. Null is assumed to be distinct from itself
Chapter: http://sqlite.org/lang_createtable.html
Where to use it: sqlite's constraint

Instruction: CHECK (expr)
Effect: State that, for each new line, it should be checked whether expr returns a value whose cast to NUMERIC is distinct from 0 and from 0.0
Chapter: http://sqlite.org/lang_createtable.html
Where to use it: sqlite's constraint
Assuming: expr does not contains a subquery

Instruction: NOT NULL
Effect: State that the lines added to the table must contains a non null value for this column
Chapter: http://sqlite.org/lang_createtable.html
Where to use it: sqlite's constraint
Assuming: column constraint only

Instruction: rowid, _rowid_, oid
Effect: A 64 bit signed integer key uniquely identifying a row in a table
Chapter: http://sqlite.org/lang_createtable.html
Assuming: table not declared with WITHOUT ROWID

Instruction: Explain (query plan)? before the statement
Effect: Explain the operations mades by the following statement if executed
Context: sql-lite
Chapter: http://sqlite.org/lang_aggfunc.html

Instruction: create view foo (bar) as buz
Page: https://sqlite.org/lang_createview.html
Effect: Create a view, named foo, with columns bar, containing the result of the select statement buz
Assuming: bar and buz has the same number of statements. Or (bar) is omitted

Instruction: max(X)
Context: sql-lite
Chapter: http://sqlite.org/lang_aggfunc.html
Returns: The maximum value of all values in the group. The maximum value is the value that would be returned last in an ORDER BY on the same column
Typ: type of X,
  or null