XLDR schemas

Schemas and adapter settings for the mapping specification of XLDR, a modular toolkit that loads CSV, XML, JSON, fixed-length and Excel files into database tables - and, the case it was written for, flat files with no header row and several kinds of record interleaved in one file, told apart by a column rather than by a name.

A spec that points at one of these is validated and autocompleted as you write it, in IntelliJ, VS Code and anything else that understands JSON Schema or XSD. Both references are ignored by XLDR itself, so a spec that carries one loads exactly as it would without.

New to writing one? The tutorial is a path through the format rather than a reference: thirteen short pages, each adding one thing to the spec built by the page before. A first CSV, the same spec in XML, then pages 4 and 5 on the headerless file and the one interleaving two record types, which come early because that is the case this was written for; then constants, variables, lookups, expressions and types - and a last page on having a language model draft a spec, and what to check in what it hands back.

To run it: the latest release carries the server, the adapters and JDBC drivers for H2 and PostgreSQL. Unpack it and run bin/xldr; Java 25 or later is the only requirement, and any other driver is a jar dropped into drivers/. The library modules are on Maven Central under io.github.ralfspoeth.xldr.

Schemas

A schema is published whenever the format changes, and named after the release that changed it: the format is still settling before 1.0, and a spec should be validated against the schema its release reads. A release that changes nothing about the format - 0.11, 0.12, 0.14 to 0.20, 0.22, 0.24 to 0.31, 0.33, 0.34, 0.36 to 0.39, 0.41, 0.45, 0.48, 0.49, 0.51, 0.52 - keeps the previous schema rather than publishing a copy of it. An older one stays where it is, so a spec pinned to it keeps validating.

0.53 is the exception that proves the rule. It publishes a pair without changing the format, because a published schema is never edited in place and one of its descriptions was wrong: it had named a subset of the expression functions since 0.10 while the set grew without it. The new pair says nothing about the functions and points here instead, so the list cannot drift again. Every constraint is identical - a spec valid under mapping-spec-0.50 is valid under mapping-spec-0.53, and the only differences between the two files are that sentence and the schema's own name.

In 0.50 a table or column name is held to being a name. Unquoted it is a letter or underscore followed by letters, digits, underscore, $ or #; anything else goes in double quotes, with an interior quote doubled, so the column a"b is written "a""b". A qualified name is refused - a deployment says its catalog and schema in target.properties.
The reason is that a name is the one thing a spec contributes to a statement that is not bound as a parameter: it is concatenated into the insert and into every lookup subquery. A routine name had been held to a shape since 0.40 on exactly that argument, and the argument was true of tables and columns too the whole time.

In 0.47 the field type DATE is called TEMPORAL. Only the name changed: it has always been delivered as a LocalDateTime and bound as SQL TIMESTAMP, and has always read a plain date as the timestamp at the start of that day - so DATE named the one SQL type it is not, where every other name here (TEXT, INTEGRAL, FP, DECIMAL) says a kind rather than a type of some particular system. A spec still saying DATE is refused when it is read, and told what to write instead.

In 0.46 a value may be part of another one, picked out by a regex: the currency in prices_EUR_20260101.csv, the year inside a product code. It carries the pattern to match with, the capturing group to take - 0, the whole match, by default - and the source it reads, written on the regex itself exactly as it would be written on the field mapping. So the subject is any source there is: a field, a var, an expr, or in a var a lookup or an fn. A pattern that does not match yields NULL, which is what a lookup already does for a key matching no row; and the pattern is compiled when the spec is read, so a feed is activated only if its patterns compile.

In 0.44 a lookup may match on no column at all, which reads a single-row view or Oracle's dual. It is said as an empty conditions rather than by leaving the conditions out, so that a forgotten key stays an error rather than quietly becoming an unconditional read. In XML the conditions moved into a <conditions> wrapper, which is what makes an empty one sayable at all - so an XML spec written against 0.43 needs its <condition> children wrapped.

In 0.43 a lookup may match on more than one column, written as a conditions array where a single key is written as keyColumn beside its source - a rate by currency and date, a price by article and price list. The conditions are anded in the order given, which is the order of the where clause and so of the bound parameters. The single-key spelling is unchanged and still the way to write a lookup on one column.

Also in 0.43, and a correction rather than a change: a var's lookup may be keyed by an fn. Both schemas have said so since 0.40 while the reader refused it, so an editor passed such a spec and the server would not load it. The reader now does what the schemas describe.

In 0.42 a spec may end with transform, a list of procedures the database runs once the input has been loaded and before the load is committed - closing a batch, reconciling what arrived. A procedure returns nothing, which is what separates it from the fn below: one is a value and belongs to a var, the other is work and belongs to the spec. Running before the commit means a procedure sees the new rows while nobody else can, and that one which fails rolls the whole file back.

In 0.40 a var may call a function in the target database, written as fn with a name, the type it returns and its args, each of which is a value source of its own. It is a var source and not a column one: a var is evaluated once per load and a column bound once per record, so the same call in a field mapping would be a round trip a row. Saying which sources belong where is what the schemas now do - a column's sources and a var's are two separate definitions - and that closes something 0.35 had wrong by omission: one lookup definition served both places, so a var keyed by a fieldSelector validated in an editor and then threw at load, having no record to read the field from.

One consequence worth knowing. The JSON schema lists the known mimeType values, but as an anyOf beside a plain string, so the list is what an editor offers rather than what the schema permits - any type validates, and must, since an adapter may come from anywhere. The list therefore names the adapters that existed when the schema was published and lags behind ones added since. That is how text/tab-separated-values spent four releases missing from the list while validating perfectly well: added to the adapter in 0.28, it reached the schema only in 0.32, the first release since to change the format at all. The list has not moved since; no adapter has been added since 0.32.

In 0.37 a JSON selector may no longer begin with a slash, and this is one the schema cannot catch: the same field carries an XPath for the XML adapter, where a leading slash is ordinary. A leading slash is RFC 6901's - the syntax of JSON Schema $ref, JSON Patch and OpenAPI - and the two differ where it costs most, an array step there being a bare number and a bare number here a member name. It used to be stripped, so /orders/0/id resolved to nothing and loaded a column of NULLs without a word. It is now refused when the adapter is built. A bare number without a slash still means a member of that name.

In 0.35 a selector may no longer be blank, wherever one appears. It used to mean two things: the XML and Excel adapters refused it while the JSON one resolved it to the whole document, so the same spelling read two ways depending on who read it. There is now one way to say "every record", which is to leave the attribute or member out. Nothing else about the format changed, and a spec that does not write a blank selector - which is every spec anyone has written on purpose - validates against 0.32 and 0.35 alike.

In 0.32 a field selector gained nth, a record selector gained discriminator, and the two ways of saying where a value sits became two names rather than one attribute meaning different things in different files. See the README for what each adapter counts.

In 0.23 accepts and sentinel left the spec. How a feed's files arrive is a property of the deployment rather than of the mapping - the names a producer uses differ between test and production while the mapping does not - so they moved to a delivery.properties beside the spec, which the server reads and no schema describes. A spec still carrying either is refused rather than ignored, so that it is moved and not merely dropped. Nothing else about the format changed.

In 0.21 the field types STRING, INTEGER and FLOAT became TEXT, INTEGRAL and FP; DECIMAL and TEMPORAL are unchanged, as is leaving the type out, which still means text. The names are deliberately none of Java's, so that nobody reads FP as a float or INTEGRAL as an int and infers a width from it. An old name is refused when the spec is read rather than defaulting quietly, so a spec that has not been edited fails by name.

In 0.13 every element takes an optional comment, for a note to whoever reads the spec next. The readers have always ignored what they do not know; naming the annotation lets the schema go on refusing everything else, which is how a misspelling like fieldSelector for fieldSelectors gets caught.

In 0.10 databaseTable became table and databaseColumn became column, the names a lookup had used all along; a spec from an earlier release has to be renamed, and is refused with a message saying so rather than silently. Also in 0.10, a constant may be null, which loads a SQL NULL into the column. In 0.9 a record selector's selector became optional: where a whole file holds one kind of record - a CSV with a header, a fixed-length file - there is nothing to locate.

Using them

In a JSON spec, as a member the reader does not recognise and therefore ignores:

{
  "$schema": "https://ralfspoeth.github.io/xldr/schema/mapping-spec-0.53.json",
  "input": {
    "mimeType": "text/csv",
    "properties": { "fieldSeparator": "," },
    "recordSelectors": [ ... ]
  },
  "mapping": [ ... ]
}

In an XML spec, as the schema location of a document that has no namespace of its own:

<mappingSpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:noNamespaceSchemaLocation="https://ralfspoeth.github.io/xldr/schema/mapping-spec-0.53.xsd">
    <input mimeType="text/xml">
        ...
    </input>
</mappingSpec>

Adapter settings

The settings of an adapter sit in the input's properties, beside the mimeType that chooses it - grouped rather than spread out, because which of them mean anything depends on that MIME type. A value is taken as its text, so false and 2 may be written as themselves. An adapter ignores any key it does not recognise, so what follows is the whole of what each one reads; the schema does not check these, since it cannot know which adapter will be asked.

The same goes for the two kinds of selector. A record selector says where the records are, a field selector says where a value sits within one, and both are written in a language the adapter chooses - XPath for XML, a character range for fixed length, a range of cells for Excel. The schema checks that a selector is a string and nothing more, so a selector that will not compile is found when the feed comes up - or, in a servlet container, when the application is deployed, which refuses to start with a spec it cannot load.

"input": {
  "mimeType": "text/csv",
  "properties": { "fieldSeparator": ";", "header": false, "dateFormat": "dd.MM.yyyy" },
  "recordSelectors": [ ... ]
}

In XML the same settings are the attributes of a <properties> child of <input>:

<properties fieldSeparator=";" header="false" dateFormat="dd.MM.yyyy"/>

Every text adapter

CSV, XML, fixed length and JSON share the conversion settings. They say how the input writes its values; the field type says what the value is. Without them values are read in their canonical form - an ungrouped literal, ISO-8601 for a date.

Excel needs none of these: a spreadsheet carries typed cells, so a date or a number arrives as one already.

CSV text/csv, text/tab-separated-values

The defaults are RFC 4180's, so a spec that says nothing beyond text/csv reads the format the MIME type is registered for. Two of them the RFC does not decide. It registers header as a MIME parameter and then says in as many words that an implementation choosing not to use it must decide for itself; present is xldr's answer, because a selector names a column and a headerless file has no names to offer. And by the RFC's own grammar a blank line is a record of one empty field, which no implementation reads it as and nobody writing a file by hand means - hence emptyLine = skip.

text/tab-separated-values settles three of those by itself. Its IANA registration is shorter than RFC 4180 and stricter: a tab separates the fields, a field cannot contain a tab and so needs no quoting mechanism at all, and the first line is the field names rather than optionally so. A spec naming that type therefore carries no properties at all. It may repeat what the type already says - a tab separator for a TSV file is redundant, not wrong - but one that contradicts it is refused, the type being a claim about what the file is. A file that is tab-separated without being TSV - quoted fields, or no header - is text/csv with "fieldSeparator": "\t". Everything the registration does not mention stays open: a comment character, emptyLine, charset and the conversion settings.

A selector that names no column of the file is refused, rather than read as null for every row. A tab-separated file read with commas has exactly one column, called the whole header line, so every selector misses and the load would otherwise report success over a table of nulls. The message names the selector, lists the columns the header actually carried and says which separator they were split on. A column merely missing from some line is still null: that is a short line, not a spec that does not fit its file.

A record is a line, and there is nothing to configure about that: \n, \r\n and \r all end one - more liberal than the RFC, which says CRLF. Inside a quoted field the separator and the line break are ordinary characters and a doubled quote is one literal quote, so a record runs over as many lines as a quoted field needs.

XML text/xml, application/xml

XML differs from the other adapters in two deliberate ways: a TEXT field keeps an empty string rather than becoming null, because XPath cannot tell "no such element" from "an element that is empty"; and an FP is taken through XPath's own numeric evaluation rather than from its text, which is why INTEGRAL and DECIMAL are not - XPath 1.0 knows only doubles.

A prefix used in either must be bound with an ns. property first; XPath 1.0 has no notion of a default namespace, so an unprefixed name never matches a namespaced element.

Fixed length text/plain

A line that stops short of a field's bounds is not an error - the value is whatever the line still holds, and a field beyond the end of the line is null - so a producer's trailing padding is irrelevant.

JSON application/json, text/json

No settings of its own, and deliberately no charset - JSON exchanged between systems is UTF-8 by definition (RFC 8259). JSON carries its own types, so a number arrives exactly, never rounded through a double, and dateFormat and numberFormat apply only to values written as strings.

Both selectors are pointers in Greyson's syntax: slash separated steps, where a step is a member name, [n] for the n-th element of an array ([-1] counting from the end), or #regex to match a member by pattern. Leading slashes are ignored, so /data/orders and data/orders are the same pointer.

Excel application/vnd.ms-excel, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

No properties. One adapter serves both .xls and .xlsx, the format being detected from the file itself.

What they catch, and what they do not

A schema checks the shape of a spec: names that are missing or misspelled, a field type that is not one of the five, a delivery pattern without its glob: or regex: prefix. The JSON schema also states the rules that XSD 1.0 cannot - that a field mapping carries exactly one value source, and that a variable reads no field - so it is the stricter of the two.

What no schema can see is whether the spec makes sense as a whole. There was a bin/xldr validate for that, removed in 0.30: the checks worth having had moved one at a time into the places that know. A MIME type no adapter reads stops a feed activating and stops a servlet deploying; a selector the adapter cannot compile does the same; a selector naming no column of the file it is reading is refused by the adapter as it reads. Each of those is earlier than a command, or better informed, and none of them can be forgotten to be run.