sqlite_ulid/
lib.rs

1use std::time::{Duration, SystemTime};
2
3use sqlite_loadable::api::ValueType;
4use sqlite_loadable::prelude::*;
5use sqlite_loadable::{api, define_scalar_function, Error, Result};
6use ulid::Ulid;
7
8const DATETIME_FMT: &str = "%Y-%m-%d %H:%M:%S.%3f";
9
10// ulid_version() -> 'v0.1.0'
11pub fn ulid_version(context: *mut sqlite3_context, _values: &[*mut sqlite3_value]) -> Result<()> {
12    api::result_text(context, format!("v{}", env!("CARGO_PKG_VERSION")))?;
13    Ok(())
14}
15
16pub fn ulid_debug(context: *mut sqlite3_context, _values: &[*mut sqlite3_value]) -> Result<()> {
17    api::result_text(
18        context,
19        format!(
20            "Version: v{}
21Source: {}
22",
23            env!("CARGO_PKG_VERSION"),
24            env!("GIT_HASH")
25        ),
26    )?;
27    Ok(())
28}
29
30// ulid() -> '01GMRG9JWFHHCGG5TSXHWYR0CM'
31pub fn ulid(context: *mut sqlite3_context, values: &[*mut sqlite3_value]) -> Result<()> {
32    let ulid = if let Some(input) = values.get(0) {
33        Ulid(u128::from_be_bytes(
34            api::value_blob(input)
35                .try_into()
36                .map_err(|_| Error::new_message("invalid BLOB input to ulid()"))?,
37        ))
38    } else {
39        Ulid::new()
40    };
41    api::result_text(context, ulid.to_string().to_lowercase())?;
42    Ok(())
43}
44
45// ulid_with_prefix('xyz') -> 'xyz_01GMRGH6F01DAKVTG9HJA19MP6'
46pub fn ulid_with_prefix(
47    context: *mut sqlite3_context,
48    values: &[*mut sqlite3_value],
49) -> Result<()> {
50    let prefix = api::value_text(values.get(0).expect("1st argument required"))?;
51    api::result_text(
52        context,
53        format!("{prefix}_{}", Ulid::new().to_string()).to_lowercase(),
54    )?;
55    Ok(())
56}
57
58// ulid_with_datetime('2023-01-26 19:50:09.428') -> '01gqqt2rrmg5p7d2e6dj81sccf'
59pub fn ulid_with_datetime(
60    context: *mut sqlite3_context,
61    values: &[*mut sqlite3_value],
62) -> Result<()> {
63    let string = api::value_text(values.get(0).expect("1st argument required"))?;
64    let datetime = NaiveDateTime::parse_from_str(string, DATETIME_FMT).map_err(|e| {
65        Error::new_message(
66            format!("error parsing date and time using format '{DATETIME_FMT}': {e}").as_str(),
67        )
68    })?;
69
70    let millis = datetime
71        .timestamp_millis()
72        .try_into()
73        .expect("milliseconds to be positive");
74    let duration = Duration::from_millis(millis);
75    let datetime = SystemTime::UNIX_EPOCH
76        .checked_add(duration)
77        .expect("milliseconds to be valid");
78
79    api::result_text(
80        context,
81        Ulid::from_datetime(datetime).to_string().to_lowercase(),
82    )?;
83    Ok(())
84}
85
86// ulid_bytes() -> X'0185310899dd7662b8f1e5adf9a5e7c0'
87// ulid_bytes('') -> X'0185310899dd7662b8f1e5adf9a5e7c0'
88pub fn ulid_bytes(context: *mut sqlite3_context, values: &[*mut sqlite3_value]) -> Result<()> {
89    let ulid = if let Some(input) = values.get(0) {
90        Ulid::from_string(api::value_text(input)?).map_err(|e| {
91            Error::new_message(format!("invalid ULID input to ulid_bytes(): {}", e).as_str())
92        })?
93    } else {
94        Ulid::new()
95    };
96    api::result_blob(context, &ulid.0.to_be_bytes());
97    Ok(())
98}
99
100use chrono::NaiveDateTime;
101// ulid_datetime('01GMP2G8ZG6PMKWYVKS62TTA41') -> 1671483106
102// TODO return millisecond precision
103pub fn ulid_datetime(context: *mut sqlite3_context, values: &[*mut sqlite3_value]) -> Result<()> {
104    let input = values.get(0).expect("1st argument required");
105    let ulid = match api::value_type(input) {
106        ValueType::Text => {
107            let input = api::value_text(input)?;
108            // if input text is longer than a normal ULID, it might be prefixed - so try to strip
109            // a prefix and extract the underlying ULID
110            if input.len() > ulid::ULID_LEN {
111                let id = if let Some((_, ulid)) = input.rsplit_once('_') {
112                    ulid
113                } else {
114                    input
115                };
116                Ulid::from_string(id).map_err(|e| {
117                    Error::new_message(
118                        format!("invalid ULID input to ulid_datetime(): {}", e).as_str(),
119                    )
120                })?
121            } else {
122                Ulid::from_string(input).map_err(|e| {
123                    Error::new_message(
124                        format!("invalid ULID input to ulid_datetime(): {}", e).as_str(),
125                    )
126                })?
127            }
128        }
129        ValueType::Blob => Ulid(u128::from_be_bytes(
130            api::value_blob(input)
131                .try_into()
132                .map_err(|_| Error::new_message("invalid BLOB input to ulid_datetime()"))?,
133        )),
134        _ => return Err(Error::new_message("unsupported input for ulid_datetime")),
135    };
136
137    //api::result_text(context, Ulid::new().to_string())?;
138    let ms = ulid
139        .datetime()
140        .duration_since(SystemTime::UNIX_EPOCH)
141        .map_err(|e| Error::new_message(format!("error calculating duration: {e}").as_str()))?
142        .as_millis()
143        .try_into()
144        .map_err(|e| {
145            Error::new_message(
146                format!("error converting duration to i64 milliseconds: {e}").as_str(),
147            )
148        })?;
149
150    match NaiveDateTime::from_timestamp_millis(ms) {
151        Some(dt) => api::result_text(context, dt.format(DATETIME_FMT).to_string())?,
152        None => return Err(Error::new_message("timestamp overflow")),
153    };
154    Ok(())
155}
156
157// TODO ulid_datetime() to ulid_extract_datetime(), ulid_extract_random()
158
159#[sqlite_entrypoint]
160pub fn sqlite3_ulid_init(db: *mut sqlite3) -> Result<()> {
161    define_scalar_function(
162        db,
163        "ulid_version",
164        0,
165        ulid_version,
166        FunctionFlags::UTF8 | FunctionFlags::DETERMINISTIC,
167    )?;
168    define_scalar_function(
169        db,
170        "ulid_debug",
171        0,
172        ulid_debug,
173        FunctionFlags::UTF8 | FunctionFlags::DETERMINISTIC,
174    )?;
175
176    define_scalar_function(db, "ulid", 0, ulid, FunctionFlags::UTF8)?;
177    define_scalar_function(db, "ulid", 1, ulid, FunctionFlags::UTF8)?;
178    define_scalar_function(db, "ulid_bytes", 0, ulid_bytes, FunctionFlags::UTF8)?;
179    define_scalar_function(db, "ulid_bytes", 1, ulid_bytes, FunctionFlags::UTF8)?;
180    define_scalar_function(
181        db,
182        "ulid_with_prefix",
183        1,
184        ulid_with_prefix,
185        FunctionFlags::UTF8,
186    )?;
187    define_scalar_function(
188        db,
189        "ulid_with_datetime",
190        1,
191        ulid_with_datetime,
192        FunctionFlags::UTF8,
193    )?;
194    define_scalar_function(
195        db,
196        "ulid_datetime",
197        1,
198        ulid_datetime,
199        FunctionFlags::UTF8 | FunctionFlags::DETERMINISTIC,
200    )?;
201    Ok(())
202}
sqlite_ulid/lib.rs

sqlite_ulid/
lib.rs
