Menu
▾
▴
Home
DAQY - a structured data tool
What is this?
This is a command-line utility program for manipulation of structured data,
such as represented in a spreadsheet, database, or graph data.
What can it do?
It can convert data between different formats, including CSV, TSV, JSON,
RDF, and XML. It can perform SQL queries to filter, rearrange, and sort
the data and then write the results to a new file. Query results can also
be written as a table in HTML or Markup format for inclusion in a document,
or as SQL CREATE and INSERT commands for importing into a database.
It can be thought of as an in-memory database. By taking advantage of a
64-bit architecture and using an efficient internal representation, it can
handle multi-gigabyte data sets. Schema is inferred instead of needing to
be supplied by the user.
Why?
I (David N. Gray <DGray@acm.org>) created it as a retirement hobby to occupy
my mind. I offer it here because it has a lot of functionality that I
think other people will find useful.
What does the name mean?
DAQY could be thought of as suggestive of "David's Query", but it's really
just a short sequence of arbitrary letters which didn't already mean something.
Typical usage
A typical usage scenario might go something like the following. The tool
is invoked with one or more data file pathnames on the command line
(possibly using wild cards). The program attempts to load all of the data
into memory, with messages to the console summarizing what it is doing.
After processing all of the command-line arguments, the program begins
accepting interactive commands, prompting with "?". A HELP command is
available at any time for more information. If any files failed to load
because the data format couldn't be determined, a LOAD AS command can be
used. Once the data is loaded, the SHOW TABLES and SHOW CLASSES commands
can be used to get an overview of what the data looks like. The command
SHOW CLASS will give more detail about an individual class. The table and
class names are usually assigned automatically, so you may want to do
RENAME TABLE or RENAME CLASS to give a better name. Now you can start
entering query commands to find and arrange the data that you want to see.
The first 20 resulting rows will be displayed on the console before asking
if you want to see more. By default, this preview uses a human-readable
textual table, but the FORMAT command may be used to choose a different
representation. When you are satisfied that you are seeing the results
that you want, use the WRITE command to save them to a file in whatever
format you choose. When finished, use the QUIT command to end the program.
Note that if all you want to do is to convert all of the data to a
different format without any changes, after the data is loaded, you would
only need to say:
WRITE FROM ALL TO <file> AS <format>;
This command could be given in quotes as the last command-line argument, in
which case the program would terminate after the write without entering
interactive mode.
This document is just an overview; more detailed documentation is provided
by the HELP command within the program.
Design philosophy
My approach to the query implementation here is that the purpose of the
program is to answer your questions, not to force you to follow a bunch of
arbitrary rules. So there is no complaint if a query command is not
strictly correct syntax as long as the intended meaning is clear enough.
This includes treating keywords as case-insensitive and context dependent,
allowing omitting semantically redundant keywords, allowing clauses in
non-standard order, and allowing multiple ways of doing things for
compatibility with various other SQL implementations. Aliases are
recognized for many of the keywords and operators.
Another principle is that the user shouldn't need to tell the computer
something which it can figure out for itself. So, for example, the default
is to automatically determine the encoding and format of input files.
Development status
This can be considered a beta test of a first release. It should have more
testing to be fully robust, and there are lots of features which could be
added in the future. CSV and tab-separated formats have been used the most
by myself, so other formats are more likely to have bugs. The Turtle
support has only been tested on trivial examples, so probably needs a lot
more work. Since this is an early preview, I'm not promising compatibility
with future releases.
Bug reports and code contributions are welcome. Let me know what
additional features you would like to see.
SQL details
The query commands are intended to support a subset of standard SQL SELECT
statements.
Limitations of SQL support
Following is a list of the most notable SQL features which are
not supported here:
-
No joins. (Note however that the field path extension described below
provides a different way for a query to use data from multiple related
tables.) -
Doesn't support query keywords GROUP BY or HAVING.
-
No WITH clause for Common Table Expressions.
-
Types TIME, DATETIME, and TIMESTAMP don't support fractional seconds.
-
Does not implement proper comparisons for numbers with an exponent.
-
Does not support character sets in LIKE. (But does support regular
expressions with the LIKE_REGEX operator.) -
WHERE clause doesn't support sub-queries EXISTS or UNIQUE.
-
Columns in an ORDER BY clause must be designated by name, not by number.
-
Does not support data modification commands such as SET, UPDATE, DELETE,
MERGE, ALTER, TRUNCATE, or DROP. (However, data creation commands CREATE
TABLE and INSERT INTO are supported.) -
No transaction controls or data control, since the data is assumed to be
read-only, single user. -
Schema (AKA meta-data) is not available for querying. CREATE SCHEMA is
not supported, although CREATE TABLE is. -
Some functions which are not (yet?) supported: UPPER, LOWER,
EXTRACT, CURRENT_DATE, CURRENT_TIMESTAMP,
CONVERT, POSITION, LEAST, GREATEST, LAG, LEAD,
TO_DATE, DATEDIFF, SQRT, LOG, ROUND
(Note if you wanted to use UPPER or LOWER to implement case-insensitive
comparison, you can use operator ILIKE, which is more efficient anyway.) -
No "window functions" such as ROW_NUMBER() ... OVER ... etc.
Implementation dependencies
This section lists how this program handles features whose details differ
in various SQL implementations.
-
Keywords are treated as case-insensitive.
-
Limiting the number of values may be done by either LIMIT <n> or the more
verbose FETCH FIRST <n> ROWS ONLY. -
LIKE patterns are case sensitive. Use ILIKE for case-insensitive matching.
-
Regular expressions are supported by the following WHERE condition:
string [ NOT ] LIKE_REGEX pattern
which tests whether the right-hand argument (which should be a string
constant), taken as a POSIX regular expression, matches any part of the
left-hand argument, case sensitive. A ~ may be used as an alias for
LIKE_REGEX, and ~* means a case-insensitive comparison. -
In a SELECT clause with a column alias name, the keyword AS is not optional.
-
In a character string literal delimited by single quotes (apostrophe),
an apostrophe within the string is denoted by two apostrophes. Use of
backslash as an escape character is not supported. A prefix of N for
Unicode is allowed but not needed. -
Integer constants may be represented in hexadecimal form like: X'1F'
-
An empty spreadsheet cell or an omitted attribute in JSON or XML is taken
to be a NULL value, which a WHERE clause will handle in accordance with
SQL's three valued logic. If the two operands in a comparison are of
incompatible types, such that the comparison is not meaningful, then the
result is UNKNOWN. Similarly, arithmetic operators return a NULL result
unless both operands are numbers.
Extensions to SQL
This section lists query features which are extensions to SQL.
-
The clauses within a query statement are not required to be in a particular
order, as long as one of SELECT, FROM, or WHERE is first. -
Keywords are recognized as such only in the appropriate context, so there
are no reserved words. -
Copying the Objectivity Do language, RETURN may be used instead of SELECT,
SKIP may be used instead of OFFSET, and TAKE may be used instead of LIMIT. -
C syntax is allowed for conditional expression operators.
For example: a!=b means the same as a<>b . -
If you just want a substring test without needing any other regular
expression features, you can do:
string CONTAINS string -
A single cell may contain multiple values, implemented as a list or
one-dimensional array. (In spreadsheet format, the values are separated by
a comma with no space.) These can be treated as an unordered set. For the
purpose of set operations, NULL is considered an empty set, and a scalar
value is treated as a set with a single element. A set constant uses the
customary SQL notation of a comma-separated list of values within parentheses.
Set comparisons:
x IN y -- x is a subset of y
x CONTAINS y -- y is a subset of x
ANY x <relop> c -- some element of set x satisfies the comparison with c
ALL x <relop> c -- every element of set x satisfies the comparison with c
ANY x IN y -- sets x and y intersect (have at least one common element) -
Aggregate functions COUNT, MAX, MIN, AVG, and SUM used in a WHERE clause
apply to the elements of a set. Thus function COUNT will give the number
of elements in a set; for example:
WHERE COUNT(children) > 3 -- if children has more than three elements
(Note that this is different from their meaning in a SELECT clause, where they
apply to all the values in a column.) -
Date literals may use other common forms such as mm/dd/yyyy as well as the
SQL standard yyyy-mm-dd. Time literals may use forms such as 14:00 or
2:00pm as well as the SQL standard 14:00:00. For both dates and times,
quotes are optional, and you don't need to use a DATE or TIME function to
convert the type of the literal. -
In a FROM clause, the keyword RESULT may be used to apply the query to the
results from the previous query. -
If the value of a field is a reference to another object, or the unique
name or identifier of another object, then a field in the other object can
be designated by a path written as <field1>.<field2> where <field1> names a
field in the current object and <field2> names a field in the referenced
object. If the first field is NULL or does not designate an object which
has a field with the second name, the value of the combination is NULL.
This notation may be used in a WHERE clause, SELECT clause, or ORDER BY
clause.
For example:
SELECT name, spouse.name AS spouse WHERE spouse.employer IS NULL
Programmer notes
This program is written in C++11. It has been built and tested on
Microsoft Windows 10 with Visual Studio 2019, and on Ubuntu Linux with
gcc 11.4.0. In order to support large data sets, it should be built
for a 64-bit architecture wherever that is available. There has been no
attempt to make the code thread-safe.
The code for data manipulation is also packaged as a static link library
which could be used from an application program, but there isn't yet any
documentation for that usage, and it should probably have some cleanup of
the design first.
The source includes a solution file for Visual Studio and a Unix make file.
Besides building the tool itself, they also build a unit test program which
can be run to verify the build.
Since this code was written as a hobby, the style is not as clean as I
would have used in a commercial product. Implementation techniques tended
to be determined more by what was easy and fun than by finding the most
optimal way.
Project Members:
- David N. Gray (admin)
