1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
//! Conversions between Rust and **SQLite** types.
//!
//! # Types
//!
//! | Rust type | SQLite type(s) |
//! |---------------------------------------|------------------------------------------------------|
//! | `bool` | BOOLEAN |
//! | `i8` | INTEGER |
//! | `i16` | INTEGER |
//! | `i32` | INTEGER, INT4 |
//! | `i64` | BIGINT, INT8 |
//! | `u8` | INTEGER |
//! | `u16` | INTEGER |
//! | `u32` | INTEGER |
//! | `u64` | INTEGER (Decode only; see note) |
//! | `f32` | REAL |
//! | `f64` | REAL |
//! | `&str`, [`String`] | TEXT |
//! | `&[u8]`, `Vec<u8>` | BLOB |
//!
//! #### Note: Unsigned Integers
//! Decoding of unsigned integer types simply performs a checked conversion
//! to ensure that overflow does not occur.
//!
//! Encoding of the unsigned integer types `u8`, `u16` and `u32` is implemented by zero-extending to
//! the next-larger signed type. So `u8` becomes `i16`, `u16` becomes `i32`, and `u32` becomes `i64`
//! while still retaining their semantic values.
//!
//! SQLite stores integers in a variable-width encoding and always handles them in memory as 64-bit
//! signed values, so no space is wasted by this implicit widening.
//!
//! However, there is no corresponding larger type for `u64` in SQLite
//! (it would require a native 16-byte integer, i.e. the equivalent of `i128`),
//! and so encoding is not supported for this type.
//!
//! Bit-casting `u64` to `i64`, or storing it as `REAL`, `BLOB` or `TEXT`,
//! would change the semantics of the value in SQL and so violates the principle of least surprise.
//!
//! ### [`chrono`](https://crates.io/crates/chrono)
//!
//! Requires the `chrono` Cargo feature flag.
//!
//! | Rust type | Sqlite type(s) |
//! |---------------------------------------|------------------------------------------------------|
//! | `chrono::NaiveDateTime` | DATETIME (TEXT, INTEGER, REAL) |
//! | `chrono::DateTime<Utc>` | DATETIME (TEXT, INTEGER, REAL) |
//! | `chrono::DateTime<Local>` | DATETIME (TEXT, INTEGER, REAL) |
//! | `chrono::DateTime<FixedOffset>` | DATETIME (TEXT, INTEGER, REAL) |
//! | `chrono::NaiveDate` | DATE (TEXT only) |
//! | `chrono::NaiveTime` | TIME (TEXT only) |
//!
//! ##### NOTE: `DATETIME` conversions
//! SQLite may represent `DATETIME` values as one of three types: `TEXT`, `REAL`, or `INTEGER`.
//! Which one is used is entirely up to you and how you store timestamps in your database.
//!
//! The deserialization for `NaiveDateTime`, `DateTime<Utc>` and `DateTime<Local>` infer the date
//! format from the type of the value they're being decoded from:
//!
//! * If `TEXT`, the format is assumed to be an ISO-8601 compatible datetime string.
//! A number of possible formats are tried; see `sqlx-sqlite/src/types/chrono.rs` for the current
//! set of formats.
//! * If `INTEGER`, it is expected to be the number of seconds since January 1, 1970 00:00 UTC,
//! as if returned from the `unixepoch()` function (without the `subsec` modifier).
//! * If `REAL`, it is expected to be the (possibly fractional) number of days since the Julian epoch,
//! November 24, 4714 BCE 12:00 UTC, as if returned from the `julianday()` function.
//!
//! These types will always encode to a datetime string, either
//! with a timezone offset (`DateTime<Tz>` for any `Tz: TimeZone`) or without (`NaiveDateTime`).
//!
//! ##### NOTE: `CURRENT_TIMESTAMP` and comparison/interoperability of `DATETIME` values
//! As stated previously, `DateTime<Tz>` always encodes to a date-time string
//! _with_ a timezone offset,
//! in [RFC 3339 format][::chrono::DateTime::to_rfc3339_opts] (with `use_z: false`).
//!
//! However, most of SQLite's datetime functions
//! (including `datetime()` and `DEFAULT CURRENT_TIMESTAMP`)
//! do not use this format. They instead use `YYYY-MM-DD HH:MM:SS.SSSS` without a timezone offset.
//!
//! This may cause problems with interoperability with other applications, and especially
//! when comparing datetime values, which compares the actual string values lexicographically.
//!
//! Date-time strings in the SQLite format will generally _not_ compare consistently
//! with date-time strings in the RFC 3339 format.
//!
//! We recommend that you decide up-front whether `DATETIME` values should be stored
//! with explicit time zones or not, and use the corresponding type
//! (and its corresponding offset, if applicable) _consistently_ throughout your
//! application:
//!
//! * RFC 3339 format: `DateTime<Tz>` (e.g. `DateTime<Utc>`, `DateTime<Local>`, `DateTime<FixedOffset>`)
//! * Changing or mixing and matching offsets may break comparisons with existing timestamps.
//! * `DateTime<Local>` is **not recommended** for portable applications.
//! * `DateTime<FixedOffset>` is only recommended if the offset is **constant**.
//! * SQLite format: `NaiveDateTime`
//!
//! Note that non-constant offsets may still cause issues when comparing timestamps,
//! as the comparison operators are not timezone-aware.
//!
//! ### [`time`](https://crates.io/crates/time)
//!
//! Requires the `time` Cargo feature flag.
//!
//! | Rust type | Sqlite type(s) |
//! |---------------------------------------|------------------------------------------------------|
//! | `time::PrimitiveDateTime` | DATETIME (TEXT, INTEGER) |
//! | `time::OffsetDateTime` | DATETIME (TEXT, INTEGER) |
//! | `time::Date` | DATE (TEXT only) |
//! | `time::Time` | TIME (TEXT only) |
//!
//! ##### NOTE: `DATETIME` conversions
//! The behavior here is identical to the corresponding `chrono` types, minus the support for `REAL`
//! values as Julian days (it's just not implemented).
//!
//! `PrimitiveDateTime` and `OffsetDateTime` will always encode to a datetime string, either
//! with a timezone offset (`OffsetDateTime`) or without (`PrimitiveDateTime`).
//!
//! ##### NOTE: `CURRENT_TIMESTAMP` and comparison/interoperability of `DATETIME` values
//! As stated previously, `OffsetDateTime` always encodes to a datetime string _with_ a timezone offset,
//! in [RFC 3339 format][::time::format_description::well_known::Rfc3339] (using `Z` for UTC offsets).
//!
//! However, most of SQLite's datetime functions
//! (including `datetime()` and `DEFAULT CURRENT_TIMESTAMP`)
//! do not use this format. They instead use `YYYY-MM-DD HH:MM:SS.SSSS` without a timezone offset.
//!
//! This may cause problems with interoperability with other applications, and especially
//! when comparing datetime values, which compares the actual string values lexicographically.
//!
//! Date-time strings in the SQLite format will generally _not_ compare consistently
//! with date-time strings in the RFC 3339 format.
//!
//! We recommend that you decide up-front whether `DATETIME` values should be stored
//! with explicit time zones or not, and use the corresponding type
//! (and its corresponding offset, if applicable) _consistently_ throughout your
//! application:
//!
//! * RFC 3339 format: `OffsetDateTime` with a **constant** offset.
//! * Changing or mixing and matching offsets may break comparisons with existing timestamps.
//! * `OffsetDateTime::now_local()` is **not recommended** for portable applications.
//! * Non-UTC offsets are only recommended if the offset is **constant**.
//! * SQLite format: `PrimitiveDateTime`
//!
//! Note that non-constant offsets may still cause issues when comparing timestamps,
//! as the comparison operators are not timezone-aware.
//!
//! ### [`uuid`](https://crates.io/crates/uuid)
//!
//! Requires the `uuid` Cargo feature flag.
//!
//! | Rust type | Sqlite type(s) |
//! |---------------------------------------|------------------------------------------------------|
//! | `uuid::Uuid` | BLOB, TEXT |
//! | `uuid::fmt::Hyphenated` | TEXT |
//! | `uuid::fmt::Simple` | TEXT |
//!
//! ### [`json`](https://crates.io/crates/serde_json)
//!
//! Requires the `json` Cargo feature flag.
//!
//! | Rust type | Sqlite type(s) |
//! |---------------------------------------|------------------------------------------------------|
//! | [`Json<T>`] | TEXT |
//! | `serde_json::JsonValue` | TEXT |
//! | `&serde_json::value::RawValue` | TEXT |
//!
//! # Nullable
//!
//! In addition, `Option<T>` is supported where `T` implements `Type`. An `Option<T>` represents
//! a potentially `NULL` value from SQLite.
//!
//! # Non-feature: `NUMERIC` / `rust_decimal` / `bigdecimal` Support
//! Support for mapping `rust_decimal::Decimal` and `bigdecimal::BigDecimal` to SQLite has been
//! deliberately omitted because SQLite does not have native support for high-
//! or arbitrary-precision decimal arithmetic, and to pretend so otherwise would be a
//! significant misstep in API design.
//!
//! The in-tree [`decimal.c`] extension is unfortunately not included in the [amalgamation],
//! which is used to build the bundled version of SQLite3 for `libsqlite3-sys` (which we have
//! enabled by default for the simpler setup experience), otherwise we could support that.
//!
//! The `NUMERIC` type affinity, while seemingly designed for storing decimal values,
//! stores non-integer real numbers as double-precision IEEE-754 floating point,
//! i.e. `REAL` in SQLite, `f64` in Rust, `double` in C/C++, etc.
//!
//! [Datatypes in SQLite: Type Affinity][type-affinity] (accessed 2023/11/20):
//!
//! > A column with NUMERIC affinity may contain values using all five storage classes.
//! > When text data is inserted into a NUMERIC column, the storage class of the text is converted to
//! > INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal,
//! > respectively. If the TEXT value is a well-formed integer literal that is too large to fit in a
//! > 64-bit signed integer, it is converted to REAL. For conversions between TEXT and REAL storage
//! > classes, only the first 15 significant decimal digits of the number are preserved.
//!
//! With the SQLite3 interactive CLI, we can see that a higher-precision value
//! (20 digits in this case) is rounded off:
//!
//! ```text
//! sqlite> CREATE TABLE foo(bar NUMERIC);
//! sqlite> INSERT INTO foo(bar) VALUES('1.2345678901234567890');
//! sqlite> SELECT * FROM foo;
//! 1.23456789012346
//! ```
//!
//! It appears the `TEXT` storage class is only used if the value contains invalid characters
//! or extra whitespace.
//!
//! Thus, the `NUMERIC` type affinity is **unsuitable** for storage of high-precision decimal values
//! and should be **avoided at all costs**.
//!
//! Support for `rust_decimal` and `bigdecimal` would only be a trap because users will naturally
//! want to use the `NUMERIC` type affinity, and might otherwise encounter serious bugs caused by
//! rounding errors that they were deliberately avoiding when they chose an arbitrary-precision type
//! over a floating-point type in the first place.
//!
//! Instead, you should only use a type affinity that SQLite will not attempt to convert implicitly,
//! such as `TEXT` or `BLOB`, and map values to/from SQLite as strings. You can do this easily
//! using [the `Text` adapter].
//!
//!
//! [`decimal.c`]: https://www.sqlite.org/floatingpoint.html#the_decimal_c_extension
//! [amalgamation]: https://www.sqlite.org/amalgamation.html
//! [type-affinity]: https://www.sqlite.org/datatype3.html#type_affinity
//! [the `Text` adapter]: Text
pub use *;