Schemas

SlashDB supports retrieval of database schema documents in JSON, XML, and CSV formats. These documents contain metadata for a resource, such as supported data types and maximum lengths of string columns. The schema document is customized according to the format requested:

• JSON schema documents use the JSON Schema standard
• XML schema documents use the XML Schema Definition
• CSV schema documents implement a custom schema using Python data types

Schema documents can be retrieved by appending the schema query string parameter to a URL, e.g to return a JSON formatted schema for the Album table in the Chinook database:

https://demo.slashdb.com/db/Chinook/Album.json?schema

N.B. - SlashDB previously supported only XML schemas, using the xsd extension. Support for this extension is deprecated and the extension will be dropped in future releases. To retrieve an XML schema, use the schema query string parameter.

Schema documents have a number of uses:

• Validation of a document against a schema – data can be checked to ensure it conforms to a format as specified in the schema before uploading to SlashDB or using the data in other workflows
• Documentation of resource elements – provides information about:
  • nesting of elements
  • order and cardinality of elements (JSON and XML only)
  • element attributes like href (JSON and XML only)
• Documentation of resource properties – provides information about:
  • names of resources
  • names of resource attributes
  • data types of resource attributes
  • whether resource attributes can be null
• Querying Support for XPath, XSLT and XQuery
• Data Binding for programmatic purposes by converting XML documents to in-memory objects that are more accessible by applications
• It's even possible to map XML elements to cells in Excel

CSV Schema Notes

Since there is no widely accepted CSV schema standard, SlashDB implements a custom CSV schema that represents column data types using Python data types. The following types are used in schema documents:

• bool
• str
• int
• float
• decimal.Decimal
• datetime.datetime
• datetime.date
• datetime.time
• bytearray

The CSV schema generator supports a wide range of column types, and tries to map the column type to its closest Python equivalent. If the generator cannot identify the column type, it will display its type as str, along with logging a warning to the server log.

Schemas for Data Discovery

Using Data Discovery, a resource can be retrieved in JSON/XML/CSV format with a GET request. A corresponding schema can be retrieved by making a GET request to the same URL by using the schema query string parameter.

# JSON
curl "https://demo.slashdb.com/db/Chinook/Customer.json"

# JSON Schema
curl "https://demo.slashdb.com/db/Chinook/Customer.json?schema"

# XML
curl "https://demo.slashdb.com/db/Chinook/Customer.xml"

# XML Schema
curl "https://demo.slashdb.com/db/Chinook/Customer.xml?schema"

# CSV
curl "https://demo.slashdb.com/db/Chinook/Customer.csv"

# CSV Schema
curl "https://demo.slashdb.com/db/Chinook/Customer.csv?schema"

It is also possible to retrieve schemas using more complex URLs that include SlashDB features like filtering, relationship traversal, column selection, depth, etc.

For example:

# JSON
curl -L -H "Accept: application/json" "https://demo.slashdb.com/db/Chinook/Customer/City/Prague/Invoice?offset=2&limit=1&sort=-Total&depth=1"

# JSON Schema
curl -L -H "Accept: application/json" "https://demo.slashdb.com/db/Chinook/Customer/City/Prague/Invoice?schema&depth=1"

# XML
curl -L -H "Accept: text/xml" "https://demo.slashdb.com/db/Chinook/Customer/City/Prague/Invoice?offset=2&limit=1&sort=-Total&depth=1"

# XML Schema
curl -L -H "Accept: text/xml" "https://demo.slashdb.com/db/Chinook/Customer/City/Prague/Invoice?schema&depth=1"

# CSV
curl -L -H "Accept: text/csv" "https://demo.slashdb.com/db/Chinook/Customer/City/Prague/Invoice?offset=2&limit=1&sort=-Total&depth=1"

# CSV Schema
curl -L -H "Accept: text/csv" "https://demo.slashdb.com/db/Chinook/Customer/City/Prague/Invoice?schema&depth=1"

Requests for JSON & XML schemas accept additional modifiers e.g. cardinality that change the returned schema.

JSON Example

Let's retrieve a single Customer by making an HTTP request:

curl https://demo.slashdb.com/db/Chinook/Customer/CustomerId/10.json

The response body contains data from a single row – i.e. a single resource:

{
    "__href": "/db/Chinook/Customer/CustomerId/10.json",
    "Employee": {
        "__href": "/db/Chinook/Customer/CustomerId/10/Employee.json"
    },
    "CustomerId": 10,
    "FirstName": "Eduardo",
    "LastName": "Martins",
    "Company": "Woodstock Discos",
    "Address": "Rua Dr. Falc\u00e3o Filho, 155",
    "City": "S\u00e3o Paulo",
    "State": "SP",
    "Country": "Brazil",
    "PostalCode": "01007-010",
    "Phone": "+55 (11) 3033-5446",
    "Fax": "+55 (11) 3033-4564",
    "Email": "eduardo@woodstock.com.br",
    "SupportRepId": 4,
    "Invoice": {
        "__href": "/db/Chinook/Customer/CustomerId/10/Invoice.json"
    }
}

In the above output:

• A JSON object is returned; it has an __href property that points to the resource
• CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId are properties whose names correspond to columns in the table and contain the values for the resource
• Invoice and Employee are objects that represent relationships to other tables; they have __href properties that point to resources related to that Customer , i.e. invoices issued for the customer and employee

Now we can retrieve the corresponding schema by using the schema query string parameter:

curl -L https://demo.slashdb.com/db/Chinook/Customer/CustomerId/10.json?schema

This JSON document is returned:

{
    "title": "Customer",
    "type": "object",
    "required": [
        "CustomerId",
        "FirstName",
        "LastName",
        "Email"
    ],
    "properties": {
        "CustomerId": {
            "type": "integer"
        },
        "FirstName": {
            "type": "string",
            "maxLength": 40
        },
        "LastName": {
            "type": "string",
            "maxLength": 20
        },
        "Company": {
            "type": [
                "string",
                "null"
            ],
            "maxLength": 80
        },
        "Address": {
            "type": [
                "string",
                "null"
            ],
            "maxLength": 70
        },
        "City": {
            "type": [
                "string",
                "null"
            ],
            "maxLength": 40
        },
        "State": {
            "type": [
                "string",
                "null"
            ],
            "maxLength": 40
        },
        "Country": {
            "type": [
                "string",
                "null"
            ],
            "maxLength": 40
        },
        "PostalCode": {
            "type": [
                "string",
                "null"
            ],
            "maxLength": 10
        },
        "Phone": {
            "type": [
                "string",
                "null"
            ],
            "maxLength": 24
        },
        "Fax": {
            "type": [
                "string",
                "null"
            ],
            "maxLength": 24
        },
        "Email": {
            "type": "string",
            "maxLength": 60
        },
        "SupportRepId": {
            "type": [
                "integer",
                "null"
            ]
        },
        "Employee": {
            "type": [
                "object",
                "null"
            ],
            "properties": {
                "__href": {
                    "type": [
                        "string",
                        "null"
                    ]
                }
            }
        },
        "Invoice": {
            "type": [
                "object",
                "null"
            ],
            "properties": {
                "__href": {
                    "type": [
                        "string",
                        "null"
                    ]
                }
            }
        },
        "__href": {
            "type": [
                "string",
                "null"
            ]
        }
    }
}

The document contains information about the Customer resource and its corresponding JSON document:

• it contains an object with title Customer
  • there are required fields when creating a new Customer resource - CustomerId, FirstName, LastName, Email
• Nested inside are:
  • simple elements like: CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId. Each of them have attributes:
    • Javascript data types (e.g. integer, string)
    • if the value is nullable, the type will be a Javascript array with the native type and null, e.g. ["string", "null"]
    • if the type is a string, and has a maximum length, a maxLength property with an integer value denoting the maximum character length of the column
    • certain columns that don't have a native Javascript type like date, time, or datetime will be represented as Javascript string types with a format property, e.g. "format": "date-time"
  • objects like: Invoice or Employee with property __href indicate that they are related resources
  • a property __href, of Javascript type string

XML Example

Let's retrieve a single Customer by making an HTTP request:

curl https://demo.slashdb.com/db/Chinook/Customer/CustomerId/10.xml

The response body contains data from a single row – i.e. a single resource:

<?xml version="1.0" encoding="utf-8"?>
<SlashDB xmlns="http://www.vtenterprise.com/slashdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:schemaLocation="http://www.vtenterprise.com/slashdb https://demo.slashdb.com/db/Chinook/Customer.xsd?cardinality=1">
    <Customer href="/db/Chinook/Customer/CustomerId/10.xml">
        <CustomerId>10</CustomerId>
        <Invoice href="/db/Chinook/Customer/CustomerId/10/Invoice.xml" />
        <FirstName>Eduardo</FirstName>
        <LastName>Martins</LastName>
        <Company>Woodstock Discos</Company>
        <Address>Rua Dr. Falcão Filho, 155</Address>
        <City>São Paulo</City>
        <State>SP</State>
        <Country>Brazil</Country>
        <PostalCode>01007-010</PostalCode>
        <Phone>+55 (11) 3033-5446</Phone>
        <Fax>+55 (11) 3033-4564</Fax>
        <Email>eduardo@woodstock.com.br</Email>
        <SupportRepId>4</SupportRepId>
        <Employee href="/db/Chinook/Customer/CustomerId/10/Employee.xml" />
    </Customer>
</SlashDB>

In the above output:

• SlashDB is the root element in the XML document that wraps all other elements
• Customer is an element representing a single resource; it has an href attribute that points to the resource
• CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId are elements whose names correspond to columns in the table and contain the values for the resource
• Invoice and Employee are elements that represent relationships to other tables; they have href attributes that point to resources related to that Customer , i.e. invoices issued for the customer and employee

Now we can retrieve the corresponding schema by using the schema query string parameter:

curl -L https://demo.slashdb.com/db/Chinook/Customer/CustomerId/10.xml?schema

This will redirect to the canonical location:

curl https://demo.slashdb.com/db/Chinook/Customer.xml?cardinality=1&schema

The final response contains an XSD document that describes the Customer resource:

<?xml version='1.0' encoding='utf-8'?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.vtenterprise.com/slashdb" elementFormDefault="qualified" targetNamespace="http://www.vtenterprise.com/slashdb">
  <xsd:element name="SlashDB">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Customer">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="CustomerId" nillable="false" type="xsd:integer" minOccurs="0"/>
              <xsd:element name="Invoice" minOccurs="0">
                <xsd:complexType>
                  <xsd:attribute name="href"/>
                </xsd:complexType>
              </xsd:element>
              <xsd:element name="FirstName" nillable="false" type="xsd:string" minOccurs="0"/>
              <xsd:element name="LastName" nillable="false" type="xsd:string" minOccurs="0"/>
              <xsd:element name="Company" nillable="true" type="xsd:string" minOccurs="0"/>
              <xsd:element name="Address" nillable="true" type="xsd:string" minOccurs="0"/>
              <xsd:element name="City" nillable="true" type="xsd:string" minOccurs="0"/>
              <xsd:element name="State" nillable="true" type="xsd:string" minOccurs="0"/>
              <xsd:element name="Country" nillable="true" type="xsd:string" minOccurs="0"/>
              <xsd:element name="PostalCode" nillable="true" type="xsd:string" minOccurs="0"/>
              <xsd:element name="Phone" nillable="true" type="xsd:string" minOccurs="0"/>
              <xsd:element name="Fax" nillable="true" type="xsd:string" minOccurs="0"/>
              <xsd:element name="Email" nillable="false" type="xsd:string" minOccurs="0"/>
              <xsd:element name="SupportRepId" nillable="true" type="xsd:integer" minOccurs="0"/>
              <xsd:element name="Employee" minOccurs="0">
                <xsd:complexType>
                  <xsd:sequence/>
                  <xsd:attribute name="href"/>
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
            <xsd:attribute name="href"/>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

The XSD contains information about the Customer resource and its corresponding XML document:

• the root element is named SlashDB
• it contains a complex type named Customer with attribute href which is required because minOccurs and maxOccurs are by default 1
• Nested inside are:
  • simple elements like: CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId. Each of them have attributes:
    • nillable defines if empty values are allowed,
    • data types (e.g. xsd:string)
    • cardinality, whether the element must appear in the XML document:
    • minOccurs="0" means an element can be omitted
    • maxOccurs is not present, so defaults to value 1 which means that an element cannot appear more than once
  • complex elements like: Invoice or Employee with attribute href indicate that they are related resources

CSV Example

Let's retrieve a single Customer by making an HTTP request:

curl https://demo.slashdb.com/db/Chinook/Customer/CustomerId/10.csv

The response body contains data from a single row – i.e. a single resource:

CustomerId,FirstName,LastName,Company,Address,City,State,Country,PostalCode,Phone,Fax,Email,SupportRepId
10,Eduardo,Martins,Woodstock Discos,"Rua Dr. Falcão Filho, 155",São Paulo,SP,Brazil,01007-010,+55 (11) 3033-5446,+55 (11) 3033-4564,eduardo@woodstock.com.br,4

In the above output:

• The first row contains the column names from the resource
• The second row contains the corresponding values in each column for the resource

Now we can retrieve the corresponding schema by using the schema query string parameter:

curl -L https://demo.slashdb.com/db/Chinook/Customer/CustomerId/10.csv?schema

This will redirect to the canonical location:

curl https://demo.slashdb.com/db/Chinook/Customer.csv?schema

The final response contains a CSV document that describes the Customer resource:

Name,Type,Nullable,Length
CustomerId,int,False,
FirstName,str,False,40
LastName,str,False,20
Company,str,True,80
Address,str,True,70
City,str,True,40
State,str,True,40
Country,str,True,40
PostalCode,str,True,10
Phone,str,True,24
Fax,str,True,24
Email,str,False,60
SupportRepId,int,True,

The CSV contains information about the Customer resource and its corresponding CSV document:

• the first row is a list of metadata attributes - the column name, its Python type, whether it is nullable, and its max character length, if applicable
• the remaining rows contain the column metadata values:
  • the Nullable column may be True or False
  • Python data type (e.g. str)
  • the Length column will contain an integer value if applicable, otherwise it will be empty

Schemas for SQL Pass-thru

With SQL Pass-thru, a pre-defined query can be executed and retrieve data in JSON/XML/CSV format with a GET request. A corresponding schema can be retrieved by making a GET request to the same URL by using theschema query string parameter.

Important Note: schemas are only available for queries that return data.

When a schema is requested, SlashDB executes the query and fetches one row to discover the returned data types, then creates the schema based on that response.

JSON Example

Let's execute a SQL Pass-thru query by making an HTTP request:

curl https://demo.slashdb.com/query/sales-by-year.json

The returned response contains JSON data:

[
    {
        "Year": "2009",
        "TotalSales": 449.4600000000003
    },
    {
        "Year": "2010",
        "TotalSales": 481.45000000000033
    },
    {
        "Year": "2011",
        "TotalSales": 469.5800000000003
    },
    {
        "Year": "2012",
        "TotalSales": 477.53000000000026
    },
    {
        "Year": "2013",
        "TotalSales": 450.58000000000027
    }
]

In the above output:

• The data is returned as a JSON array of objects, with each object representing a row
• Each object contains a property for each column in the row: Year and Total

Now let's retrieve the schema:

curl https://demo.slashdb.com/query/sales-by-year.json?schema

The response contains a JSON document that describes the data structure that the SQL query previously returned:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Year": {
                "type": [
                    "string",
                    "null"
                ]
            },
            "TotalSales": {
                "type": [
                    "number",
                    "null"
                ]
            }
        }
    },
    "minItems": 1
}

from the schema, it can be seen that:

• the type of data returned is a JSON array
• nested inside are an unlimited number of objects
• each object has two properties:
  • Year with data type string
  • Total with data type number
• the null value in the type property indicates that the values may be empty

XML Example

Let's execute a SQL Pass-thru query by making an HTTP request:

curl https://demo.slashdb.com/query/sales-by-year.xml

The returned response contains XML data:

<?xml version="1.0" encoding="utf-8"?>
<SlashDB xmlns="http://www.vtenterprise.com/slashdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:schemaLocation="http://www.vtenterprise.com/slashdb https://demo.slashdb.com/query/sales-by-year.xsd">
    <row>
        <Year>2009</Year>
        <Total>451.44000000000034</Total>
    </row>
    <row>
        <Year>2010</Year>
        <Total>481.45000000000033</Total>
    </row>
    <row>
        <Year>2011</Year>
        <Total>469.5800000000003</Total>
    </row>
    <row>
        <Year>2012</Year>
        <Total>477.53000000000026</Total>
    </row>
    <row>
        <Year>2013</Year>
        <Total>450.58000000000027</Total>
    </row>
</SlashDB>

In the above output:

• SlashDB is the root element in the XML document that wraps all other elements
• Nested inside are repeated row elements that represent each row returned by the SQL query
• Each row element contains an element for each column returned: Year and Total

Now let's retrieve the schema:

curl https://demo.slashdb.com/query/sales-by-year.xml?schema

The response contains an XSD document that describes the data structure that the SQL query previously returned:

<?xml version='1.0' encoding='utf-8'?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.vtenterprise.com/slashdb" elementFormDefault="qualified" targetNamespace="http://www.vtenterprise.com/slashdb">
  <xsd:element name="SlashDB">
    <xsd:complexType>
      <xsd:sequence maxOccurs="unbounded">
        <xsd:element name="row">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="Year" type="xsd:integer" minOccurs="0" nillable="true"/>
              <xsd:element name="Total" type="xsd:float" minOccurs="0" nillable="true"/>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

from the schema, it can be seen that:

• the root element is named SlashDB
• nested inside are an unlimited number of row elements
• each row has two elements:
  • Year with data type integer
  • Total with data type float
• minOccurs=0 and nillable indicate that the values may be empty

CSV Example

Let's execute a SQL Pass-thru query by making an HTTP request:

curl https://demo.slashdb.com/query/sales-by-year.csv

The returned response contains CSV data:

Year,TotalSales
2009,449.4600000000003
2010,481.45000000000033
2011,469.5800000000003
2012,477.53000000000026
2013,450.58000000000027

In the above output:

• The data is returned as CSV rows, with a header row containing column names
• Each row contains a value for each column in the row: Year and Total

Now let's retrieve the schema:

curl https://demo.slashdb.com/query/sales-by-year.csv?schema

The response contains a CSV document that describes the data structure that the SQL query previously returned:

Name,Type,Nullable,Length
Year,str,True,
TotalSales,float,True,

from the schema, it can be seen that:

• the schema has a header with the column metadata fields
• each row represents a column and its metadata
  • Year with data type string
  • Total with data type float
• the True value in the Nullable column indicates that the values may be empty