Below is the actual implementation of CsvSQL's SQL queries. These queries were written based on the EBNF documented earlier. Due to the precise nature of EBNF it enabled me to completely specify every facet of the syntactical grammar that would make up CsvSQL's commands.
The examples in this section are based on the below CSV file which we will name data.csv
id,firstname,lastname,email 12345,John,Smith,john.smith@example.com 23456,Patrick,Murphy,patrick.murphy@example.com 34567,Sarah,Jones,sarah.jones@example.com
SELECT [ * | select_expr ] [ FROM file_name [ WHERE where_condition ] [ LIMIT row_count ] ]
SELECT is used to retrieve one or more rows from a file, and can include the WHERE clause along with a LIMIT.
The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.
The WHERE clause can be used for selections that include an AND or an OR operator, where AND requires for both conditions to be true, and OR requires either one or the other.
A LIMIT is used to limit the number of lines to be selected to a defined row_count
Example 4-1. Sample SELECT
SELECT * FROM file_name
csvsql> SELECT * FROM data.csv id | firstname| lastname| email | 12345| John | Smith | john.smith@example.com | 23456| Patrick | Murphy | patrick.murphy@example.com| 34567| Sarah | Jones | sarah.jones@example.com |
SELECT * FROM file_name LIMIT row_count
csvsql> SELECT * FROM data.csv LIMIT 2 id | firstname| lastname| email | 12345| John | Smith | john.smith@example.com | 23456| Patrick | Murphy | patrick.murphy@example.com|
SELECT * FROM file_name WHERE where_condition
csvsql> SELECT * FROM data.csv WHERE firstname = Sarah id | firstname| lastname| email | 34567| Sarah | Jones | sarah.jones@example.com |
SELECT col_name FROM file_name WHERE where_condition
csvsql> SELECT firstname,email FROM data.csv WHERE id > 20000 firstname| email | Patrick | patrick.murphy@example.com| Sarah | sarah.jones@example.com |
csvsql> SELECT firstname,email FROM data.csv WHERE firstname = John OR lastname = Jones firstname| email | John | john.smith@example.com | Sarah | sarah.jones@example.com |
INSERT INTO file_name [ (col_name,col_name,...) ] VALUES (expr,expr,...)
Insert is used to append a new row to a CSV file. Insert can be called in two ways. These are
INSERT INTO file_name VALUES (expr,expr,...)
This method can be used only if you are inserting a value for each field in the CSV. If a user tried to enter either more or less values than the CSV requires for a "full" row, the insert will fail and inform the user.
INSERT INTO file_name (col_name,col_name,...) VALUES (expr,expr,...)
This method can be used to insert values directly into their corresponding fields. For example, if a user wanted to insert an item into the 1st, 5th, and 7th fields of a CSV only they would use this method with the relevant column names.It cannot be used to insert more elements than there are fields.
Example 4-2. Sample INSERT
INSERT INTO file_name (col_name,col_name,...) VALUES (expr,expr,...)
csvsql> INSERT INTO data.csv (id,firstname,lastname,email) VALUES (45678,Jim,Gleeson,jim.gleeson@example.com) csvsql> SELECT * FROM data.csv id | firstname| lastname| email | 12345| John | Smith | john.smith@example.com | 23456| Patrick | Murphy | patrick.murphy@example.com| 34567| Sarah | Jones | sarah.jones@example.com | 45678| Jim | Gleeson | jim.gleeson@example.com |
UPDATE file_name SET col_name=expr [ WHERE where_condition ]
Update is used to update a field, or fields in a CSV. The user can select a field based on restrictions in a where statement ad update the corresponding field(s). Update must be supplied with a valid where clause.
The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be updated. where_condition is an expression that evaluates to true for each row to be updated.
The WHERE clause can be used for updates that include an AND or an OR operator, where AND requires for both conditions to be true, and OR requires either one or the other.
Example 4-3. Sample UPDATE
UPDATE file_name SET col_name=expr WHERE where_condition
csvsql> UPDATE data.csv SET firstname = James WHERE id = 45678 csvsql> SELECT * FROM data.csv id | firstname| lastname| email | 12345| John | Smith | john.smith@example.com | 23456| Patrick | Murphy | patrick.murphy@example.com| 34567| Sarah | Jones | sarah.jones@example.com | 45678| James | Gleeson | jim.gleeson@example.com |
DELETE FROM file_name
WHERE where_condition
Delete will remove a row from a CSV. The user must supply a where clause which will restrict the delete to deleting any rows which match the criteria.
The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause.
The WHERE clause can be used for selections that include an AND or an OR operator, where AND requires for both conditions to be true, and OR requires either one or the other.
Example 4-4. Sample UPDATE
DELETE FROM file_name WHERE where_condition
csvql> DELETE FROM data.csv WHERE email = john.smith@example.com csvsql> SELECT * FROM data.csv id | firstname| lastname| email | 23456| Patrick | Murphy | patrick.murphy@example.com| 34567| Sarah | Jones | sarah.jones@example.com | 45678| James | Gleeson | jim.gleeson@example.com |
CREATE file_name (col_name,col_name,...) WITH VALUES (expr,expr...)
Create can be used to create a new CSV formatted file. CsvSQL's create format differs slightly from standard SQL. This is because as we use text files we do not need to define field types. As far as text files are concerned a string and an interger are the same thing. You do however have to specify not only the column names to be used, but also the first row of data. This is due to a limitation in the method used to process the data. If we create a file that has no data then the arrays used to hold the data are inititialised, and as such cannot be added to.