sqlload 0.55.0

SQL data loading tooling.
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
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
//! Splitting list values into child tables' rows, level by level.

use std::sync::Arc;

use arrow_array::cast::AsArray;
use arrow_array::{Array, ArrayRef, Int64Array, RecordBatch, StringArray};
use arrow_cast::display::{ArrayFormatter, FormatOptions};
use arrow_schema::{DataType, Field, Schema};
use serde::de::{Deserialize, Deserializer, MapAccess, Visitor};
use serde_json::value::RawValue;

use crate::column_types::constants::ARROW_TYPE_METADATA_KEY;
use crate::column_types::models::ColumnType;
use crate::load_naming::constants::VALUE_COLUMN;
use crate::load_naming::main::child_table_name::child_table_name;
use crate::load_naming::main::loaded_name::loaded_name;
use crate::load_naming::main::metadata_name::metadata_name;
use crate::load_naming::models::{Compat, MetadataRole, Naming};
use crate::row_batches::_helpers::arrow_json::normalise_column;
use crate::row_batches::_helpers::json_seed::raw_cell;
use crate::row_batches::constants::FLATTEN_SEPARATOR;
use crate::row_batches::main::columns_batch::columns_batch;
use crate::row_batches::models::{ChildBatch, Flatten, RowColumns, Unnest, UnnestRequest};
use crate::row_batches::types::ListChoices;
use crate::row_ids::main::child_row_id::child_row_id;

/// The key dlt gives a list that is itself an item of a list, so its items land a level down (`items__list`).
const NESTED_LIST_KEY: &str = "list";

/// One child table's rows as they are gathered: the items' data cells, and each row's parent, position, own id and root.
pub(crate) struct Level {
    path: String,
    table: String,
    rows: RowColumns,
    parents: Vec<String>,
    positions: Vec<i64>,
    ids: Vec<String>,
    roots: Vec<String>,
    root_rows: Vec<usize>,
}

/// What every level of one split shares: which fields unnest, how deep, and how names are made.
pub(crate) struct Split<'a> {
    pub(crate) unnest: &'a Unnest,
    pub(crate) max_depth: u32,
    pub(crate) naming: Naming,
    /// Whose names the child rows' id, parent and position columns take.
    pub(crate) compat: Compat,
    /// The longest name the destination takes.
    pub(crate) name_limit: Option<usize>,
    /// Which objects inside an item flatten into `<key>__<inner>` fields, by their path from the table's row.
    pub(crate) flatten: &'a Flatten,
    /// Under `"all"`, the child tables the destination already has, whose fields are list fields.
    pub(crate) live: &'a [String],
    /// The paths [`Self::choose_live`] has looked up in `live`.
    pub(crate) looked_up: std::collections::BTreeSet<String>,
    /// Under `"all"`, the fields chosen as list fields or not so far, chosen further as values decide.
    pub(crate) choices: &'a mut ListChoices,
    pub(crate) levels: Vec<Level>,
}

/// What a field's values say about it being a list field: whether some value is a list with items, and whether some value is neither NULL nor a list.
#[derive(Default)]
struct Seen {
    list: bool,
    other: bool,
}

/// Where one list's items go: the list field's dotted path and child table, its depth (1 for a field of the loaded table), and the row it belongs to with that row's root.
struct ListPlace<'a> {
    path: &'a str,
    table: &'a str,
    depth: u32,
    parent: &'a str,
    root: Option<&'a str>,
    /// The root's row in the split batch.
    root_row: usize,
}

/// An object's keys and raw values in the order the JSON writes them.
struct OrderedObject(Vec<(String, Box<RawValue>)>);

impl<'de> Deserialize<'de> for OrderedObject {
    fn deserialize<D: Deserializer<'de>>(deserializer: D) -> Result<Self, D::Error> {
        deserializer.deserialize_map(OrderedVisitor)
    }
}

/// Reads an object's entries in order.
struct OrderedVisitor;

impl<'de> Visitor<'de> for OrderedVisitor {
    type Value = OrderedObject;

    fn expecting(&self, formatter: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        formatter.write_str("a JSON object")
    }

    fn visit_map<A: MapAccess<'de>>(self, mut map: A) -> Result<OrderedObject, A::Error> {
        let mut entries: Vec<(String, Box<RawValue>)> = Vec::new();
        while let Some(entry) = map.next_entry::<String, Box<RawValue>>()? {
            entries.push(entry);
        }
        Ok(OrderedObject(entries))
    }
}

impl Split<'_> {
    /// Move the items of the column `field` to child rows when it is a list field (any column holding a list under `"all"`, a listed one always), each row's items under its id from `request`; whether it moved.
    pub(crate) fn take_lists(
        &mut self,
        (field, column): (&Field, &ArrayRef),
        request: &UnnestRequest<'_>,
    ) -> Result<bool, String> {
        if !self.unnests(field.name(), 1) {
            return Ok(false);
        }
        let rule = (self.naming, self.name_limit);
        self.choose_live(field.name(), || {
            child_table_name(rule, request.table, field.name())
        });
        let Some(texts) = self.choose_column((field, column))? else {
            return Ok(false);
        };
        let table = child_table_name(rule, request.table, field.name());
        for (row, text) in texts.iter().enumerate() {
            let (Some(text), Some(parent)) = (text, request.ids.get(row)) else {
                continue;
            };
            let place = ListPlace {
                path: field.name(),
                table: &table,
                depth: 1,
                parent,
                root: request
                    .roots
                    .and_then(|roots| roots.get(row))
                    .map(String::as_str),
                root_row: row,
            };
            self.add_list(text, &place)?;
        }
        Ok(true)
    }

    /// The JSON texts of the column `field` when it moves to a child table (see [`Self::chooses`]), choosing it from its values when that is still open.
    pub(crate) fn choose_column(
        &mut self,
        (field, column): (&Field, &ArrayRef),
    ) -> Result<Option<Vec<Option<String>>>, String> {
        let texts = json_texts(field, column)?;
        let seen = texts
            .iter()
            .flatten()
            .fold(Seen::default(), |seen, text| seen.and(text));
        Ok(self.chooses(field.name(), &seen)?.then_some(texts))
    }

    /// Whether the field at `path` moves to a child table given what its values in hand show: always for a listed path; under `"all"` when it is chosen as a list field, or this is its first deciding value and that is a list (a NULL or empty list decides nothing). A field chosen as a list field with a value that is not a list fails, naming it, as storing that value or dropping it would both lose it.
    fn chooses(&mut self, path: &str, seen: &Seen) -> Result<bool, String> {
        if matches!(self.unnest, Unnest::Paths(_)) {
            return Ok(true);
        }
        match (self.choices.get(path).copied(), seen) {
            (Some(true), Seen { other: true, .. }) => Err(format!(
                "{path}: unnest = \"all\" took {path} as a list field from its values so far, and a row holds a value that is not a list there; list the paths to unnest without {path}, so it stays a JSON column, or fix the row"
            )),
            (Some(chosen), _) => Ok(chosen),
            (None, Seen { other: true, .. }) => {
                self.choices.insert(path.to_owned(), false);
                Ok(false)
            }
            (None, Seen { list: true, .. }) => {
                self.choices.insert(path.to_owned(), true);
                Ok(true)
            }
            (None, _) => Ok(false),
        }
    }

    /// Under `"all"`, choose the field at `path` as a list field when the destination already has its child table, which `table` names, as an earlier load chose it; each path is looked up once per split.
    fn choose_live(&mut self, path: &str, table: impl FnOnce() -> String) {
        if self.live.is_empty()
            || self.looked_up.contains(path)
            || self.choices.get(path) == Some(&true)
        {
            return;
        }
        self.looked_up.insert(path.to_owned());
        let table = table();
        if self
            .live
            .iter()
            .any(|live| live.eq_ignore_ascii_case(&table))
        {
            self.choices.insert(path.to_owned(), true);
        }
    }

    /// Whether the list field at `path`, `depth` levels down, becomes a child table: listed in `unnest` (or any list under `"all"`), no deeper than `max_depth`.
    fn unnests(&self, path: &str, depth: u32) -> bool {
        depth <= self.max_depth
            && match self.unnest {
                Unnest::None => false,
                Unnest::All => true,
                Unnest::Paths(paths) => paths.iter().any(|listed| listed == path),
            }
    }

    /// Add the items of `text`, the JSON value of a list field (a value that is not a list is a list of itself), as rows of the child table at `place`.
    fn add_list(&mut self, text: &str, place: &ListPlace<'_>) -> Result<(), String> {
        let items: Vec<Box<RawValue>> = match text.trim_start().as_bytes().first() {
            Some(b'n') => return Ok(()),
            Some(b'[') => {
                serde_json::from_str(text).map_err(|error| format!("{}: {error}", place.path))?
            }
            _ => vec![
                RawValue::from_string(text.to_owned())
                    .map_err(|error| format!("{}: {error}", place.path))?,
            ],
        };
        for (position, item) in items.iter().enumerate() {
            self.add_item(item.get(), place, position)?;
        }
        Ok(())
    }

    /// Add `item`, at `position` in its list, as one row of the child table at `place`, then its own list fields as rows a level down.
    fn add_item(
        &mut self,
        item: &str,
        place: &ListPlace<'_>,
        position: usize,
    ) -> Result<(), String> {
        let id = child_row_id(place.parent, place.table, position);
        let entries: Vec<(String, Box<RawValue>)> = match item.trim_start().as_bytes().first() {
            Some(b'{') => {
                let object = serde_json::from_str::<OrderedObject>(item)
                    .map_err(|error| format!("{}: {error}", place.path))?
                    .0;
                let entries = match self.flatten.is_none() {
                    true => object,
                    false => {
                        let path: Vec<&str> = place.path.split('.').collect();
                        flatten_entries(object, (&path, place.path), self.flatten)?
                    }
                };
                self.loaded_keys(entries, place.path)?
            }
            Some(b'[') => vec![(NESTED_LIST_KEY.to_owned(), raw(item, place.path)?)],
            _ => vec![(VALUE_COLUMN.to_owned(), raw(item, place.path)?)],
        };
        let mut lists: Vec<(String, Box<RawValue>)> = Vec::new();
        let mut cells: Vec<(String, Box<RawValue>)> = Vec::new();
        for (key, value) in entries {
            let nested = format!("{}.{key}", place.path);
            let unnests = self.unnests(&nested, place.depth + 1);
            if unnests {
                let rule = (self.naming, self.name_limit);
                self.choose_live(&nested, || child_table_name(rule, place.table, &key));
            }
            let taken = unnests && self.chooses(&nested, &Seen::default().and(value.get()))?;
            match taken {
                true => lists.push((key, value)),
                false => cells.push((key, value)),
            }
        }
        let level = self.level(place);
        for (key, value) in &cells {
            level.rows.push(
                key,
                raw_cell(value.get()).map_err(|error| format!("{}.{key}: {error}", place.path))?,
            );
        }
        level.rows.end_row();
        level.parents.push(place.parent.to_owned());
        level
            .positions
            .push(i64::try_from(position).unwrap_or(i64::MAX));
        level.ids.push(id.clone());
        level.roots.extend(place.root.map(str::to_owned));
        level.root_rows.push(place.root_row);
        for (key, value) in &lists {
            let path = format!("{}.{key}", place.path);
            let table = child_table_name((self.naming, self.name_limit), place.table, key);
            let below = ListPlace {
                path: &path,
                table: &table,
                depth: place.depth + 1,
                parent: &id,
                root: place.root,
                root_row: place.root_row,
            };
            self.add_list(value.get(), &below)?;
        }
        Ok(())
    }

    /// An item's `entries` with each key as it loads under the split's naming (snake_case normalises it); two keys of one item at `path` loading as one name (under `source`, a key spelt as a flattened object's `<object>__<key>`) is an error naming both.
    fn loaded_keys(
        &self,
        entries: Vec<(String, Box<RawValue>)>,
        path: &str,
    ) -> Result<Vec<(String, Box<RawValue>)>, String> {
        let mut loaded: Vec<(String, Box<RawValue>)> = Vec::with_capacity(entries.len());
        let mut spelled: Vec<String> = Vec::with_capacity(entries.len());
        for (key, value) in entries {
            let name = loaded_name(self.naming, &key, self.name_limit);
            if let Some(other) = loaded.iter().position(|(taken, _)| *taken == name) {
                let keys = match spelled[other] == key {
                    true => format!("two keys {key:?} (one may come from flattening an object)"),
                    false => format!("keys {:?} and {key:?} both", spelled[other]),
                };
                return Err(format!(
                    "{path}: {keys} load as {name}; a list's items must not have keys that load under one name"
                ));
            }
            loaded.push((name, value));
            spelled.push(key);
        }
        Ok(loaded)
    }

    /// The level gathering `place`'s rows, made when its first row arrives, after its parent's.
    fn level(&mut self, place: &ListPlace<'_>) -> &mut Level {
        let found = self
            .levels
            .iter()
            .position(|level| level.path == place.path);
        let index = found.unwrap_or_else(|| {
            self.levels.push(Level {
                path: place.path.to_owned(),
                table: place.table.to_owned(),
                rows: RowColumns::new(),
                parents: Vec::new(),
                positions: Vec::new(),
                ids: Vec::new(),
                roots: Vec::new(),
                root_rows: Vec::new(),
            });
            self.levels.len() - 1
        });
        &mut self.levels[index]
    }
}

/// `entries`, at `parts` below the table's row (an item of the list at `parts`, then each object holding them), with each object `flatten` flattens replaced by its keys as `<key>__<inner>`, recursively (an empty object, or a NULL inside a flattened object, leaves nothing); an error names the item's `path`.
fn flatten_entries(
    entries: Vec<(String, Box<RawValue>)>,
    (parts, path): (&[&str], &str),
    flatten: &Flatten,
) -> Result<Vec<(String, Box<RawValue>)>, String> {
    let prefix = &parts[path.split('.').count()..];
    let mut flat: Vec<(String, Box<RawValue>)> = Vec::with_capacity(entries.len());
    for (key, value) in entries {
        let here: Vec<&str> = parts.iter().copied().chain([key.as_str()]).collect();
        let name = prefix
            .iter()
            .copied()
            .chain([key.as_str()])
            .collect::<Vec<&str>>()
            .join(FLATTEN_SEPARATOR);
        match value.get().trim_start().as_bytes().first() {
            Some(b'{') if flatten.flattens(&here)? => {
                let object = serde_json::from_str::<OrderedObject>(value.get())
                    .map_err(|error| format!("{path}.{name}: {error}"))?;
                flat.extend(flatten_entries(object.0, (&here, path), flatten)?);
            }
            Some(b'n') if !prefix.is_empty() => {}
            _ => flat.push((name, value)),
        }
    }
    Ok(flat)
}

/// `text` as a raw JSON value, an error naming `path` when it is not one.
fn raw(text: &str, path: &str) -> Result<Box<RawValue>, String> {
    RawValue::from_string(text.to_owned()).map_err(|error| format!("{path}: {error}"))
}

/// `level`'s rows as a batch: the items' data columns, then, in dlt's order, the root's id when it has them, the parent's id, the position and the row's own id, named under `compat`; a data column taking one of those names is an error.
fn level_batch(level: Level, compat: Compat) -> Result<ChildBatch, String> {
    let data = columns_batch(level.rows, false)?;
    let schema = data.schema();
    let mut fields: Vec<Arc<Field>> = schema.fields().iter().cloned().collect();
    let mut columns: Vec<ArrayRef> = data.columns().to_vec();
    let mut linkage: Vec<(MetadataRole, ArrayRef)> = Vec::with_capacity(4);
    if !level.roots.is_empty() {
        linkage.push((
            MetadataRole::RootId,
            Arc::new(StringArray::from(level.roots)),
        ));
    }
    linkage.push((
        MetadataRole::ParentId,
        Arc::new(StringArray::from(level.parents)),
    ));
    linkage.push((
        MetadataRole::ListIndex,
        Arc::new(Int64Array::from(level.positions)),
    ));
    linkage.push((MetadataRole::RowId, Arc::new(StringArray::from(level.ids))));
    for (role, column) in linkage {
        let name = metadata_name(compat, role);
        if schema.fields().iter().any(|field| field.name() == name) {
            return Err(format!(
                "{}: its items have a field {name}, the name of the column linking {} to its parent; rename it before loading",
                level.path, level.table
            ));
        }
        fields.push(Arc::new(Field::new(name, column.data_type().clone(), true)));
        columns.push(column);
    }
    let batch = RecordBatch::try_new(Arc::new(Schema::new(fields)), columns)
        .map_err(|error| error.to_string())?;
    Ok(ChildBatch {
        path: level.path,
        table: level.table,
        batch,
        root_rows: level.root_rows,
    })
}

/// Column `field` of a batch as each row's JSON text (`None` for NULL): JSON text as it is, a list as its JSON, text as a JSON string, and numbers and booleans as written.
fn json_texts(field: &Field, array: &ArrayRef) -> Result<Vec<Option<String>>, String> {
    let marked = |field: &Field| {
        field
            .metadata()
            .get(ARROW_TYPE_METADATA_KEY)
            .is_some_and(|kind| *kind == ColumnType::Json.to_string())
    };
    let (normal_field, normal) = normalise_column(field.name(), array)?;
    let is_json = marked(field) || marked(&normal_field);
    match (is_json, normal.data_type()) {
        (_, DataType::Utf8) => {
            let mut texts: Vec<Option<String>> = Vec::with_capacity(normal.len());
            for text in normal.as_string::<i32>() {
                texts.push(text.map(|text| quoted(text, is_json)));
            }
            Ok(texts)
        }
        _ => {
            let formatter = ArrayFormatter::try_new(normal.as_ref(), &FormatOptions::default())
                .map_err(|error| error.to_string())?;
            Ok((0..normal.len())
                .map(|row| (!normal.is_null(row)).then(|| formatter.value(row).to_string()))
                .collect())
        }
    }
}

/// `text` as JSON: itself when it already is JSON, otherwise a JSON string.
fn quoted(text: &str, is_json: bool) -> String {
    match is_json {
        true => text.to_owned(),
        false => serde_json::Value::String(text.to_owned()).to_string(),
    }
}

impl Seen {
    /// What is seen once the JSON value `text` is too.
    fn and(self, text: &str) -> Self {
        let text = text.trim_start();
        match text.as_bytes().first() {
            None | Some(b'n') => self,
            Some(b'[') => Self {
                list: self.list || !text[1..].trim_start().starts_with(']'),
                ..self
            },
            Some(_) => Self {
                other: true,
                ..self
            },
        }
    }
}

/// The parent's rows from the `kept` columns, with `schema`'s metadata.
pub(crate) fn parent_batch(
    schema: &Schema,
    kept: Vec<(Arc<Field>, ArrayRef)>,
) -> Result<RecordBatch, String> {
    let (fields, columns): (Vec<Arc<Field>>, Vec<ArrayRef>) = kept.into_iter().unzip();
    RecordBatch::try_new(
        Arc::new(Schema::new_with_metadata(fields, schema.metadata().clone())),
        columns,
    )
    .map_err(|error| error.to_string())
}

/// Every level `split` gathered as its child table's batch, parents before children.
pub(crate) fn child_batches(split: Split<'_>) -> Result<Vec<ChildBatch>, String> {
    let compat = split.compat;
    split
        .levels
        .into_iter()
        .map(|level| level_batch(level, compat))
        .collect()
}