Basic Data Navigation
SlashDB is a Resource Oriented Architecture. It means that each URL strongly references to certain document with data. The most important concept is to think of URLs like paths to specific documents in your file system. For example, let's reach out for an XML document of Customer "ALFKI" in our Northwind database:
From this point you may filter the data, drill down using mechanism of following relations, choose needed data structure and finally preferred output document format.
While "discovering" the data using GUI, cURL, programming libraries or a REST client at any moment you may change extension of current URL to receive the same data in more suitable document format.
There are two ways to navigate the databases setup in SlashDB. Users can navigate via the SlashDB online interface or by typing an URL in an internet browser.
Navigation by SlashDB interface
Selecting a database by selecting the HTML button will open up to a list of tables in the selected database.
For example, clicking on the HTML button for Chinook (Above) will open up the Chinook database. (Below)
As described in Data Discovery, the database sub-folders allow users to view folders and files in the HTML, JSON, CSV, XML and XSD formats.
Clicking on HTML again will reveal the content of the selected table. For example, selecting Invoice in the Chinook database example will reveal a list of files with an invoice ID. (Below)
Navigation by URL
Alternatively users can just type in the address bar of an internet browser the URL of the database, table or file they would like to access.
For example, to access the Chinook database via URL, type the following URL in the internet browser's address bar:
The internet browser will open up the list of tables in the Chinook database.
To open the Invoice table type the following:
The internet browser will open up the list of files with an invoice ID.
Navigating Tables
The tables view allows users to browse table rows freely, but more importantly, it allows users to transverse relations.
As an example, in the Chinook database, we pick the Playlist table and see records in it.
To find out what is in the PlaylistID 1 (Music) playlist, click on the button in the column PlaylistTrack.
The view is changed and we can see now the table PlaylistTrack with records for PlaylistID 1. It's an association table for many-to-many relation between Playlist and Track. We can follow next relation to the Track table.
Clicking on the header of the table will take us to a page with all tracks related to PlaylistID 1.
On the other hand clicking on the HTML button in row for TrackId 1 will take us to details of that track.
Users can keep going further, by finding out which Album, Genre, etc. the song is ID'd under.
PlaylistTrack, Album and Genre are all tables that are searchable in the top level Chinook database. So instead of viewing which songs are on Playlist 1, users can find out which Playlists have Jazz songs by selecting the Genre table.
Navigating Tables by URL
Alternatively, users can skip all the above and go directly to any table by typing a URL into a internet browser.
https://demo.slashdb.com/db/Chinook/Invoice/InvoiceId/1/Customer.html
The above URL directed the user to the customer with InvoiceId 1. SlashDB follows a consistent format of URL creation. A detailed explanation can be found below:
The URL method is very powerful and allows users to narrow down searches in a variety of ways.
Resource Types
Each URL in SlashDB Data Discovery references to some resources which represent data in specific data structure: list of objects, single object, arrays, vector or scalar.
List of Objects
Returned data contains all available columns and relationships in a form of list.
Examples:
https://demo.slashdb.com/db/Chinook/Invoice.html - all data about Invoices
Single object
When using canonical URL of a resource (filtering only on Primary Key columns) the resource is presented as single entity (different than list output). This output can be forced into list with wantarray query parameter.
Usage:
/[table_name]
Examples:
/Invoice/InvoiceId/1.html - Invoice filtered on Primary Key column (InvoiceId = 1)
Arrays
Show only certain columns of objects. Allows to present important data instead of all columns and relations. Reduces amount of sent data and makes output clearer. Add comma separated names of columns you want to get in the output.
Usage:
/[table_name]/[column_A],[column_B]
Examples:
https://demo.slashdb.com/db/Chinook/Invoice/InvoiceDate,BillingCountry.html - get only two columns of Invoices - InvoiceDate and BillingCountry
Vector
Vector is an output of single column among multiple rows.
Getting a vector from a single column is as simple as not including a value range in the URL.
Usage:
/[table_name]/[column_name]
Examples:
https://demo.slashdb.com/db/Chinook/Genre/Name.html - get only genre names
Scalar
Getting a single column value (or scalar) is also possible by including the attribute name after the value range.
Usage:
/[table_name]/[pk_column]/[pk_value]/[column_name]
Examples:
/Invoice/InvoiceId/1/BillingCity.html
Filtering
Searching resources is performed in URL. The filtering is always composed by URL segment that defines column to filter on and next URL segment that is value. Here's an example of a URL that uses all filtering options
See proper section for more details: Exact Value, Or, And, Search by Substring, Search by Ranges and Advanced Data Navigation
Data Types
- String use urlencoding in case of non-ascii characters
- Number use integers or float with "." (dot) for decimal separator
- Date use ISO format (YYYY-MM-DD)
Datetime use ISO formats
- YYYY-MM-DD hh:mm:ss
- YYYY-MM-DD hh:mm:ss.uuuuuu
- YYYY-MM-DDThh:mm:ss
- YYYY-MM-DDThh:mm:ss.uuuuuu
where:
YYYY - four digit year e.g. 2018
MM - two digit month e.g. 12
DD - two digit day e.g. 31
hh - two digit hour e.g. 23
mm - two digit minute e.g. 59
ss - two digit second e.g. 59
uuuuuu - six digit microsecond e.g. 999999
Exact Value
The simplest filtering is by exact comparison.
Usage:
/[column_name]/[value]
Examples:
https://demo.slashdb.com/db/Chinook/Customer/Country/USA - get Customers from USA
OR
You can separate values with "," (comma) to compare with multiple values. This effectively works like an "or".
Usage:
/[column_name]/[value1],[value2]
Examples:
https://demo.slashdb.com/db/Chinook/Customer/Country/USA,Canada - get Customers from USA or Canada
AND
You can combine filtering on multiple columns which effectively works like and "and". You can combine as many, as you need.
Usage:
/[column_A]/[value_for_A]/[column_B]/[value_for_B]
Examples:
https://demo.slashdb.com/db/Chinook/Customer/Country/USA/City/New York - get Customers from New York, USA.
NOT (negation)
Negation can be applied to any filter (i.e. exact value, substring, etc.) to obtain records other than the ones matching the given filter. Prefix the filtered field with the ~ (tilde) character to invert the filtering condition for that field.
Usage:
/~[column_name]/[filter]
Examples:
https://demo.slashdb.com/db/Chinook/Customer/~Country/USA
- get Customers from anywhere except the USA.
https://demo.slashdb.com/db/Chinook/Customer/~Country/USA,Canada
- get Customers from anywhere except the USA or Canada.
https://demo.slashdb.com/db/Chinook/Track/~TrackId/1..3495
- get Tracks with TrackId outside of the range of 1 to 3495.
Search by Substring
It's also possible to search by matching substring. In general "*" (asterisk) means any characters. It's also possible to search asterisk literally but then you need to override default wildcard character.
Usage:
/[column_name]/[starts_with_string]*
/[column_name]/*[ends_with_string]
/[column_name]/[starts_with_string]*[ends_with_string]
/[column_name]/*[contains_string]*
Examples:
https://demo.slashdb.com/db/Chinook/Customer/LastName/M* - get only Customer with LastName starting with letter M
https://demo.slashdb.com/db/Chinook/Customer/LastName/M*r - get only Customer with LastName starting with letter "M" and ending with letter "r".
https://demo.slashdb.com/db/Chinook/Customer/City/*ar* - get only Customers from Cities that have "ar" somewhere in their name.
Search by Ranges
For numeric values (integer, float, date) can filter by a range.
- Greater or equal
- Less or equal
- Between
Usage:
/[column_name]/[value]..
/[column_name]/..[value_to]
/[column_name]/[value_from]..[value_to]
Examples:
https://demo.slashdb.com/db/Chinook/Invoice/Total/9.91.. - get Invoices of Total value ≥ 9.91
https://demo.slashdb.com/db/Chinook/Invoice/Total/..1.98 - get Invoices of Total value ≤ 1.98
https://demo.slashdb.com/db/Chinook/Invoice/CustomerId/1..5.html - get Invoices of Customer with CustomerId between 1 and 5.
Column filter
It's possible to select only certain columns. See section about selecting single column or multiple columns.
Following Relations
SlashDB allows to easily follow relations between tables by adding to the URL segment a segment with a name of the relation. You can follow as many relations as you need and within each context (table) you may perform additional filtering
Usage:
/[table_name]/[relation_name]
Examples:
https://demo.slashdb.com/db/Chinook/Customer/CustomerId/14/Invoice.html - first find certain Customer (CustomerId = 14) then follow relation "Invoice" to find his inovoices.
https://demo.slashdb.com/db/Chinook/Customer/CustomerId/14/Invoice/InvoiceLine/Track/Album/Artist.html - get list of artists whose music the Customer ever bought
https://demo.slashdb.com/db/Chinook/Customer/CustomerId/14/Invoice/InvoiceDate/2013-03-01../InvoiceLine/Track/Album - get albums bought by Customer 14 after 2013-10-08
Output Document Formats
Each URL represents specific resource and also a format of acquired or sent data. The output format can be changed by simply adding or replacing extension
Usage:
/some/data-discovery/url.csv
/some/data-discovery/url.json
/some/data-discovery/url.xml
/some/data-discovery/url.xsd
/some/data-discovery/url.html
/some/data-discovery/url
Default: .html (when no extension)
Examples:
https://demo.slashdb.com/db/Chinook/Customer/Country/USA - customers from USA in default format (HTML)
https://demo.slashdb.com/db/Chinook/Customer/Country/USA.html - customers from USA in HTML
https://demo.slashdb.com/db/Chinook/Customer/Country/USA.csv - customers from USA in CSV
https://demo.slashdb.com/db/Chinook/Customer/Country/USA.json - customers from USA in JSON
https://demo.slashdb.com/db/Chinook/Customer/Country/USA.xml - customers from USA in XML
https://demo.slashdb.com/db/Chinook/Customer/Country/USA.xsd - XML Schema for customers
Modifying Resources
Even though SlashDB is REST centric, the Data Discovery GUI allows performing simple modifications of single resources (of course modifying multiple resources is also possible, but only via the REST API).
Editing Resource Using GUI
For instance, by going to:
https://demo.slashdb.com/db/Chinook/Invoice/InvoiceId/1.html
you can click the Edit button and adjust the selected resource.
At any given time, if you change your mind, you can click the Close button to safely revert all the changes. When the you click the Save button (or press Enter key, while editing), your changes will be saved.
When a change in the Primary Key (PK) occurs, after a successful save, you will be prompted about a redirect to the resources new address. The redirect will happen automatically.
During a save, error might occur - so watch out for them. For example when trying to modify the AlbumId to a value that is already taken i.e. 2.
As can be seen above, SQLite returned an "UNIQUE constraint" error for Album.AlbumId.
Deleting Resource Using GUI
For instance, by going to:
https://demo.slashdb.com/db/Chinook/PlaylistTrack/PlaylistId/1/TrackId/3402.html
you can click the Delete. A confirmation prompt will show.
Upon successful delete, the resource identified by the URL no longer exists. You will be asked if you want to be redirected to the table to which the resource belonged to i.e. https://demo.slashdb.com/db/Chinook/PlaylistTrack.html?limit=29.
Clicking No will just close the prompt.
As with editing and error might occur during the delete process.
For:
https://demo.slashdb.com/db/Chinook/Invoice/InvoiceId/1.html
delete, will result in code 409:
This is normal and indicates violation of database referential integrity, which prevented deleting a record referenced by other records.