OVERVIEW
This is an attempt to bring some added power to EDS by turning EDS files into Relational Databases. EuSQL implements a fairly large subset of standard SQL. Ultimately, I'd like to build EuSQL into a full fledged RDBMS (Relational Database Management System), including transactions, record locks, stored procedures and triggers, and write an ODBC driver for it. For now, though, this is what you've got. :)
EuSQL requires the version of database.e that comes with Euphoria 2.4.
EuSQL will run on any platform that runs Euphoria. There is no platform specific code in EuSQL.
LICENSE AND DISCLAIMER
This software is freeware, but you must give me credit for any applications developed with EuSQL. You can modify any of the code, so long as you do not take credit for any of the original source, and any modifications must be marked as such.
THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFIT; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
FILES INCLUDEDMain Library files: eusql.e : Main include file eusql.htm : You're reading it. hlist.e : Hybrid associative list routines by Jiri Babor. Used by EuSQL. sprint.e : Adopted from Gabriel Boehme's print.e
Matheval files (used for comparisons and calculations by EuSQL): matheval.e : Main matheval lib file. booleval.e : Evaluates boolean expressions. ppeval.e : Formats matheval sequences for display. misceval.e : Used by matheval.e symeval.e : Symbolic and algebraic simplification parseval.e : Parses mathematical and logical expressions scripteval.e : More complex operators texteval.e : Text manipulation
Demo Files: archive.edb : RDS's archive of user contributions sql.ex : Demo that creates sql.edb from archive.edb sqldemo.ex : Demo that runs several queries on sql.edb print.e : Gabriel Boehme's smart print routines (used in the demo)
Topic | Description |
Index | Alphabetical list of all items. |
API | Important notes on using the EuSQL API |
Change History | Summary of changes made to the library |
Data | Functions for manipulating records. |
DB Management | Navigation, creation and deletion of databases and tables |
Errors | Trapping and using EuSQL error information |
Known Bugs | Still gotta squash these... |
Miscellaneous Notes | How it works, helpful hints |
Queries | These functions are used for executing SQL statements. |
SQL | Summary of supported SQL syntax |
TODO | What's next? |
Utilities | Helper routines used by the library that may be useful |
All functions return sequences when no errors occur. They will return the integer EUSQL_ERR code on errors (see Errors for details). The only exception to this is for action queries (UPDATE/INSERT/DELETE) when the action could not be performed. Error status is returned on a record by record basis as described in the SQL syntax entry for each type of query. Also note that you may pass empty sequences for db_name and table_name, and EuSQL will use the current database and table.
It is important to distinguish between a field index and an index created on a table for query purposes. When a function requires a field index, it is not asking for the name of an index created for that field. The required parameter is actually the necessary subscript that would be used to refer to the field as a part of the record if the record were stored in a sequence. If the structure of the table were:
Then the record as a sequence would be:
{ ID, { NAME.FIRST, NAME.LAST }, EMAIL }
So the corresponding indices would be:
Field | Index |
ID | {1} |
NAME | {2} |
NAME.FIRST | {2,1} |
NAME.LAST | {2,2} |
{3} |
v0.76.8 8/03/04 * Fixed bug with update queries where only one field would be updated * Fixed bug where duplicate literals were not parsed correctly * Fixed bug in insert_record2() (did the earlier bug creep back in?) * Fixed bug in update_record2() for updating an index * Fixed parsing bug for SELECT COUNT(*) queries v0.76.7 6/1/04 * Properly handles semicolons at the end of a SQL statement * Bug fixes v0.76.6 3/16/04 * Fixed bug in insert_record2() v0.76.5 1/30/04 * Fixed bug with UPDATE queries v0.76.4 1/27/04 * insert_record2() optimized to update indices at the end, rather than for each individual record * Bug fixes: + Updated parse_delete() to add the correct number of elements to query[QN_VALUES] (4) + Incorrect definition of EUSQL_CONCAT + EUSQL_MULTIPLY was conflicting with EUSQL_ASTERISK, and broke 'SELECT * FROM TABLE'-type functionality + Fixed indexing errors when deleting records + Fixed some parsing errors introduced into other query types by modifications to SELECT parsing v0.76.3 12/04/03 * Bug fixes: + Parameter and matheval variable handling + Parser was missing <=, >= and incorrectly parsing quoted literals + ORDER BY didn't properly take into account non-displayed fields + UPDATE query wouldn't update with an atom value + select_current() didn't always change the current table * Uses matheval v1.61 + Added scripteval.e to use matheval's IF() operator + Added texteval.e for string manipulation * Calculated fields for queries (arithmetic, IF, text operations)v0.76.2 11/17/03 * Optimized and fixed bugs in select queries, parameter handling
v0.76.1 10/27/03 * insert_record() and insert_record2() will call blank_record() if an atom is passed for the blank record. * Better parsing of INSERT queries. No longer have to prepend the table name to each field to be inserted. * Added validate_field2() for easier field validation. * Can change the way EuSQL opens databases using eusql_exclusive().
v0.76 10/08/03 * insert_record2() returns sequence with error codes for records * Fixed insert bug when a parameter's value had not been set.
v0.75 8/4/03 * Fixed delete_record() bugs * Added AUTONUMBER datatype
v0.74 4/21/03 * Major optimization for SELECT queries * Changed order of precedence for boolean logic so that it makes more sense (fewer parenthesis are needed) * IN() keyword now works in where clauses * create_db() didn't update db_list * Bug fixes to DELETE, UPDATE parsing and execution * rename_table() added v0.73 4/10/03 * Improved error checking and reporting in several places * Fixed bug where eusql missed fields with the same name as a table * update and delete functions now update indices * Fixes to boolean evaluation * Can now use IS [NOT] NULL in WHERE clauses * More parameter bug fixes--/UPDATE queries * Other UPDATE query bug fixes * Fixed bugs regarding parameters--values not being set and read properly v0.72 1/16/03 * Fixed several bugs regarding outer joins v0.71 11/22/02 * Select now uses indices, dramatic speed increase * Outer joins working * Can alias an aggregate function: SUM(TABLE1.FIELD1) AS FIELD1 v0.7 4/19/02 * Literals now case sensitive. * Condition and single, multiple join work now. * Need to test multiple conditionals with joins. * Indices are being created, but not sure if correct, since they're not used anywhere else. v0.62 6/14/01 * added matheval:booleval for comparison and calculations v0.61 5/2/01 * create_index(): need to allow queries to do this, too * get_record2(): Retrieve a record based on non-key values. Also had to add index_wild() to hlist.e to search on wildcard values. v0.6 3/6/01 * Added CREATE, DROP TABLE queries. * Modularized parsing routines. v0.5 3/2/01 * Added API commands to update, insert, retrieve records, get the structure of a table's records, create tables and fields, select current database, table * When create_table and create_field are used, TABLEDEF is automatically updated. * "SELECT *" now returns 'flat' records * Fixed bug regarding condition handling. Can now use conditions with "SELECT *" * Initial support for datatypes -- they're stored, but not actually used for verification yet * Fixed bug with UPDATE queries v0.4 2/8/01: * Better error handling. Added get_sql_err() for more verbose error reporting. * Many bug fixes. * DELETE queries supported. Returns keys of deleted records. * UNION queries supported. * "SELECT *" supported. * INSERT queries supported. * UPDATE queries supported. v0.3a 11/2/00: * Column functions COUNT, SUM, AVG, GROUP BY * Added support for SELECT DISTINCT to get unique values * field struct converted to uppercase in table_list() * Changed from absolute referencing of compiled query to using constants to allow flexibility in sequence structure.
All fields must have a specified data type. Any writes to the field will be type checked
based on the data type. A non-conforming value will cause an error to be returned.
It is possible, however, that a field of type INTEGER or ATOM will return an empty sequence.
This actually means that the field is NULL--no value has yet to be assigned to it.
The best method for retrieving error information is through the function get_sql_err(), which returns extended error information, as well as the standard error message (see description in API). It is recommended that you call get_sql_err() rather than accessing EUSQL_ERR_MSG (see below), since you'll get a more detailed description of the error.
EuSQL has predefined error strings, which can be accessed through EUSQL_ERR_MSG:
global constant EUSQL_ERR_MSG = { "EuSQL Error: OPERATION FAILED", "EuSQL Error: ERROR IN 'WHERE' CLAUSE", "EuSQL Error: INVALID FIELD", "EuSQL Error: JOIN ERROR", "EuSQL Error: INVALID TABLE", "EuSQL Error: SYNTAX ERROR" }ex:
object sql sql = "SELECT ID FROM EMPLOYEES;" sql = parse_sql( sql ) if atom(sql) then puts(1, get_sql_err( sql ) ) abort(1) end if
Tools you can use
If you haven't already, I recommend using my Euphoria Database Browser (EDB). It runs
on Windows. Check out my web page or the
RDS archives for downloads and updates.
Speed
EuSQL is not extremely fast relative to commercial databases, however, there are some things
you can do to speed up operations (meanwhile, I'll work on optimizing everything).
How it works
EuSQL needs to have an extra table, named "TABLEDEF" created in the database it is to use.
This stores the structures of records in your tables. This is automatically created when you
use create_db. It will also create the table "INDEXDEF", which is used to store indices created
on tables.
The keys [of TABLEDEF] are the names of the tables, and the first field contains a sequence which describes the structure and field names for each table. Each field is described a sequence with two elements:
The contents of the field could be described by either an empty sequence, or by one or more field names. This way, we preserve the flexibility of deeply nested sequences, while making it easy to use the data.
Other fields [in TABLEDEF] keep track of field datatypes and indices for each table.
Example: Suppose table MAIL contains information about people on a mailing list, where the records have the structure (the first field is actually the table/EDS key, which can also be composed of multiple subfields):
{ { "ID", {} } { "Name", { {"First", {}}, {"Last", {}}, } }, { "Address", { {"Street", {}}, {"City", {}}, {"State", {}}, {"ZIP", {}} } } }Then to refer to a field in a SQL statement (EuSQL is case insensitive):
First Name : "name.first" Street : "address.street" Address : "address" -- actually a sequence: -- { Address.Street, Address.City, Address.State, Address.ZIP } etc.Using this method, it is possible to grab a single field, or a more complex, nested sequence of fields.
http://w3.one.net/~jhoffman/sqltut.htm
http://msdn.microsoft.com/library/wcedoc/vbce/sql.htm
http://www.spnc.demon.co.uk/ora_sql/sqlmain.htm
There are several strategies for optimizing queries using EuSQL. First, try to focus you queries using a WHERE clause, especially in queries with a join, as this allows EuSQL to eliminate many records from its search. Another way to improve performance is to create indices on fields in your tables. You should try to create an index on a field that is likely to be part of a join, or that will likely be included in a WHERE clause. An index on a field will slow down INSERT, DELETE and UPDATE operations, but can dramatically improve SELECT operations. Since SELECT is usually done on records more often than operations that change the data, this is usually a very profitable trade off.
Please let me know if any of these are important to you, and they'll probably be implemented sooner than later.
Concatenates two strings into one string.
SELECT THE.FIELD, 'FIELD: ' & THE.FIELD AS FIELD FROM MY_TABLETHE.FIELD FIELD --------- ----------- ID FIELD: ID NAME FIELD: NAME
See Also: AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Used to create an alias for a field.
ex:
SELECT FNAME AS NAME FROM EMPLOYEES WHERE NAME LIKE "A*";
See Also: &, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Returns the average of records selected.
ex:
SELECT AVG(SALARY) FROM EMPLOYEES;
See Also: &, AS, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Pass the result of get_record_struct() to blank_field, and it will return a sequence with the correct structure, but with all empty sequences.
See Also: blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2
Returns an empty record for specified table where all fields are empty sequences. This wraps blank_field() and get_record_struct() into one call.
See Also: blank_field, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2
Deletes all parameters previously set.
See Also: get_parameter, init_params, parse_sql, run_query, run_sql, set_parameter
Closes the specified database.
See Also: create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table
Returns a count of the number of records selected.
ex:
SELECT COUNT(ID) FROM EMPLOYEES;
See Also: &, AS, AVG, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Create a table:
CREATE TABLE EMPLOYEE, NAME AS TEXT, SSN AS TEXT
See Also: &, AS, AVG, COUNT, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Creates a EuSQL database, including TABLEDEF and INDEXDEF tables.
See Also: close_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table
You do not have to create a field before you can create a subfield. EuSQL will automatically create a parent field for a subfield if it doesn't exist.
Valid datatypes:
Atoms, integers objects and sequences are the same as the standard Eu definitions. Text is just a one dimensional sequence filled with integers in range 0-255. The binary datatype is a one dimensional sequence filled with bytes.
build_index should be 1 if you wish to create an index (you cannot create an index for the primary key, although you can for subfields of a primary key), or 0 if not.
See Also: close_db, create_db, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table
Creates an index (stored in INDEXDEF) on a field in a table. Each index must have a unique name (ix_name). It is recommended that you create an index on any field that is likely to be used as a foreign key, as this will speed up queries. The primary key itself may not be indexed, however, subfields of the primary key may be indexed.
See Also: close_db, create_db, create_field, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table
Creates a table and generates an entry in TABLEDEF.
See Also: close_db, create_db, create_field, create_index, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table
Delete records in one or more tables. Syntax is similar to to SELECT statement. Any table with a field mentioned between DELETE and FROM will have selected records deleted. Use the WHERE clause to specify records to delete.
See Also: &, AS, AVG, COUNT, CREATE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Delete record with primary key 'key' from specified db/table.
See Also: get_record, get_record2, get_record_mult_fields, insert_record, insert_record2, update_record, update_record2
Query will not return duplicate values.
ex:
SELECT DISTINCT LNAME FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Drop a table from the database:
DROP TABLE EMPLOYEE
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Deletes a field and any index associated with the field. field_name can be one field, or a sequence of field names to be dropped.
See Also: close_db, create_db, create_field, create_index, create_table, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table
Deletes a table and cleans up system tables to remove dropped table.
See Also: close_db, create_db, create_field, create_index, create_table, drop_field, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table
Variables of type EUSQLRESULT are sequences, unless there is an error, at which point the variable will contain a positive integer. If 'with type' is specified, typechecking will fail, and the error will be output to either a user specified routine or to the output stream specified by eusql_err_out(). If you call eusql_true_typecheck( 0 ), then your program will not crash when a EUSQLRESULT is an integer. EuSQL will pass control back to you. If 'without type' is specified, EuSQL will not automatically catch errors.
See Also: eusql_err_out, eusql_true_typecheck, get_sql_err
Redirects EuSQL typecheck errors. If fn is positive, then errors are output to file number fn. If fn is negative, then -fn is the routine id of a user specified callback procedure. EuSQL will call this procedure if a typecheck fails. The parameter will be the integer EUSQL_ERR_ code. When the callback procedure returns, the typecheck will fail unless typechecking was previously turned off using eusql_true_typecheck(). The default value is 1, which ouputs error messages to the console.
See Also: EUSQLRESULT, eusql_true_typecheck, get_sql_err
If flag is zero, subsequenct calls to open_db() will open databases with the EDS flag DB_LOCK_NO. If flag is a non-zero value, subsequent calls to open_db() will use the EDS flag DB_LOCK_EXCLUSIVE. The default is to use DB_LOCK_EXCLUSIVE.
See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, open_db, reindex, rename_field, rename_table, select_current, select_db, select_table
Indicates whether typechecks for variables of type EUSQLRESULT will return a value of zero. This is independent of any calls to 'with type' or 'without type'. new_setting should be a boolean value. If set to 0, EUSQLRESULT typechecks that fail will not crash if typechecking is on.
See Also: EUSQLRESULT, eusql_err_out, get_sql_err
Names for fields with subfields are not returned.
See Also: blank_field, blank_record, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2
Returns a flattened version of indices for a record. Indices for fields with subfields are not returned. 'index' should be an empty sequence.
See Also: blank_field, blank_record, flat_field_names, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2
Returns the flattened version of the data of a record. Data for fields with subfields are not returned.
See Also: blank_field, blank_record, flat_field_names, flat_index, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2
Specifies table from which fields are selected.
ex:
SELECT NAME FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Returns the datatype for the field. field can be either tokenized or untokenized.
See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2
Returns the value of the next AUTONUMBER that will be assigned for the specified field.
See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field, validate_field2
Returns the value of a parameter.
See Also: clear_params, init_params, parse_sql, run_query, run_sql, set_parameter
Returns a field (or the whole field as a flat record) with primary key = key from table table_name. You may specify either the field name or the index (as returned by validate_field()). Data is returned as a sequence. To get the actual value, take the first element of the data returned. This is to allow error processing.
See Also: delete_record, get_record2, get_record_mult_fields, insert_record, insert_record2, update_record, update_record2
Allows record retrieval based on non-key field values. Returns only the first record found with the correct field value.
See Also: delete_record, get_record, get_record_mult_fields, insert_record, insert_record2, update_record, update_record2
Retrieves multiple fields from a record.
See Also: delete_record, get_record, get_record2, insert_record, insert_record2, update_record, update_record2
Returns the TABLEDEF definition of the structure of the tables records.
See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, map_field, seq_store, tokenize, type_check, validate_field, validate_field2
Use this to get a text representation of the error returned. It also includes any extended error information beyond the standard error.
See Also: EUSQLRESULT, eusql_err_out, eusql_true_typecheck
BYIf other aggregate functions are used (SUM, COUNT, AVG, etc), any field not within a function must be specified by GROUP BY.
ex:
SELECT JOB_DESC, AVG(SALARY) FROM EMPLOYEES GROUP BY JOB_DESC;
This returns the average salary for each JOB_DESC:
JOB_DESC AVG OF SALARY SECRETARY 25000 MANAGER 60000 ...etc
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
IF can be used in a calculated field to do conditional calculations:
SELECT NUM, NUM + IF( NUM > 10, 0, 10 ) AS TEST FROM NUMBERSNUM TEST --- ---- 1 10 10 10 11 0
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
IN is used in a WHERE clause to select from a list of values:
SELECT NAME, DEPARTMENT FROM EMPLOYEES WHERE DEPARTMENT IN(1,3,5)This would select the names of all employees in departments 1, 3, and 5.
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Initialize EuSQL parameters and store as Matheval variables.
See Also: clear_params, get_parameter, parse_sql, run_query, run_sql, set_parameter
Insert records into a table. Values to be inserted can be specified either with a SELECT statement or explicity using VALUES( f1, f2, f3, ... ).
The order in which fields are specified must match the order in which the values are given. If the number fields and values (supplied explicitly or implicitly) do not match, an error will be returned.
INSERT INTO TABLE1 FIELD1, FIELD3, FIELD4, FIELD2 VALUES( VAL1, VAL3, VAL4, VAL2)Inserts resulting records of SELECT into TABLE1.FIELD1 <- VAL1 FIELD3 <- VAL3 FIELD4 <- VAL4 FIELD2 <- VAL2
INSERT INTO TABLE1 FIELD1, FIELD3, FIELD4, FIELD2 SELECT FIELD1, FIELD3, FIELD4, FIELD2 FROM TABLE2
The inserted records will be returned with a status field appended to each record, indicating either "OK" or "ERROR". The most common error is duplication of primary keys.
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Inserts a record into specified table.
{ ID, { NAME.FIRST, NAME.LAST }, EMAIL }
So the corresponding indices would be:
Field | Index |
ID | {1} |
NAME | {2} |
NAME.FIRST | {2,1} |
NAME.LAST | {2,2} |
{3} |
See Also: delete_record, get_record, get_record2, get_record_mult_fields, insert_record2, update_record, update_record2
insert_record2() is for inserting multiple records at a time. It is much faster to do bulk inserts this way than using insert_record() or multiple INSERT statements.
data = { { "Joe", 1, 6 }, { "Bob", 4, 2 }, ...etc... }
{ ID, { NAME.FIRST, NAME.LAST }, EMAIL }
So the corresponding indices would be:
Field | Index |
ID | {1} |
NAME | {2} |
NAME.FIRST | {2,1} |
NAME.LAST | {2,2} |
{3} |
ex: EUSQLRESULT ok, void -- suppose that MY_TABLE and YOUR_TABLE are in myDB.edb and yourDB.edb -- both contain 2 fields: ID and NAME -- and we'd like to copy all the records from MY_TABLE to YOUR_TABLEinsert_record2() now always returs a sequence containing the status for each of the records to be inserted. Each status indicator will be a two-element sequence. The first element will be a sequence if insertion worked, or an atom if insertion failed. If it failed, the second element will be a full error description as returned by get_sql_err().-- get all the records from MY_TABLE void = open_db("myDB.edb") ok = run_sql("SELECT * FROM MY_TABLE")
-- now insert them into YOUR_TABLE void = open_db("yourDB.edb") ok = insert_record2( "myDB.edb", "YOUR_TABLE", blank_record("myDB.edb","YOUR_TABLE"), ok[2], { {1}, {2} })
See Also: delete_record, get_record, get_record2, get_record_mult_fields, insert_record, update_record, update_record2
LEFT/RIGHT/INNER JOIN ON Use relationship between two tables to 'join' into one table for querying purposes. You must specify either LEFT, RIGHT OR INNER. An INNER join requires that both fields be non-null, while a RIGHT or LEFT join can have either the 'left' or 'right' field, respectively, be null. Field names must have the table name preceding the field name (EMPLOYEES.NAME).
ex:
SELECT EMPLOYEE.ID, SCHEDULE.DATE FROM EMPLOYEE INNER JOIN SCHEDULE ON EMPLOYEE.ID = SCHEDULE.ID;SCHEDULE.ID is a foreign key to EMPLOYEE.ID, and both fields must be non-null
SELECT EMPLOYEE.ID, MGR.NAME FROM EMPLOYEE LEFT JOIN MGR ON EMPLOYEE.MGR_ID = MGR.ID;EMPLOYEE.MGR_ID is the foreign key to MGR.ID. MGR_ID can be null (not all employees have managers)
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Returns the first chars characters of string.
SELECT THE.FIELD, LEFT(THE.FIELD, 2) AS LEFT_2 FROM MY_TABLETHE.FIELD LEFT_2 --------- ------ JOHN JO MARY MA
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Returns the length of string.
SELECT THE.FIELD, LEN(THE.FIELD) AS LENGTH FROM MY_TABLETHE.FIELD LENGTH --------- ------ ABCDEFG 7 XYZ 3
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Used for wildcard string comparisons.
ex:
SELECT NAME FROM EMPLOYEES WHERE NAME LIKE "A*";
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
This is used by EuSQL to put field values into a structured EuSQL record. For instance, if your table had the fields:
ok = map_field( {{ {}, {} }, {}, { {}, {}}}, -- the record structure without any data { "John", "Smith", "555-1234", -- the data to be stored "123 Main St", "USA" }, {{1,1},{1,2},{2},{3,1},{3,2}}) -- the respective field indices for each -- fieldok = { {"John", "Smith"},"555-1234",{"123 Main St","USA"}}
See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, seq_store, tokenize, type_check, validate_field, validate_field2
Returns the largest value from the records selected
ex:
SELECT MAX(SALARY) FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Returns the chars characters of string, starting with character number start.
SELECT THE.FIELD, MID(THE.FIELD, 2, 2) AS MID_2 FROM MY_TABLETHE.FIELD MID_2 --------- ----- JOHN OH MARY AR
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Returns the smallest value from the records selected
ex:
SELECT MIN(SALARY) FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Opens a EuSQL database. The default is to open the database with the EDS flag DB_LOCK_EXCLUSIVE. To open databases with DB_LOCK_NO, use eusql_exclusive().
See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, reindex, rename_field, rename_table, select_current, select_db, select_table
BY [DESC]Set a sort order for the returned records. The default is to order in ascending order. Use DESC to order by descending order.
ex:
SELECT NAME, AGE FROM EMPLOYEES ORDER BY AGE DESC, NAME;
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
sql must be a string. This function will return a 'compiled' query, which can be passed to run_query. If there is an error, parse_sql will return an atom. If a query will be run multiple times (perhaps using different parameters each time), it is faster to parse the statement once, and use run_query() than to use run_sql() each time.
See Also: clear_params, get_parameter, init_params, run_query, run_sql, set_parameter
Rebuilds the specified index.
See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, rename_field, rename_table, select_current, select_db, select_table
Rename a field.
See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_table, select_current, select_db, select_table
Renames the table table_name to new_name and updates TABLEDEF and INDEXDEF with the new name.
See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, select_current, select_db, select_table
Returns the last chars characters of string.
SELECT THE.FIELD, RIGHT(THE.FIELD, 2) AS RIGHT_2 FROM MY_TABLETHE.FIELD RIGHT_2 --------- ------- JOHN HN MARY RY
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
query must be compiled SQL returned from parse_sql(). This function returns a 3 element sequence:
See Also: clear_params, get_parameter, init_params, parse_sql, run_sql, set_parameter
sql must be a SQL statement in a string. This function calls parse_sql and run_query for you, returning the result. If there is an error in the statement, an atom will be returned, otherwise, the result is a three element sequence:
See Also: clear_params, get_parameter, init_params, parse_sql, run_query, set_parameter
Used to query a database to return values
ex:
SELECT NAME, AGE FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , STR, SUM, UNION, UPDATE, VAL, WHERE, [...]
Change the current database and table. An empty sequence for either parameter will use the current setting.
See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_db, select_table
Change the current database.
See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_table
Change the current table.
See Also: close_db, create_db, create_field, create_index, create_table, drop_field, drop_table, eusql_exclusive, open_db, reindex, rename_field, rename_table, select_current, select_db
Store a in b at subcript c
See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, tokenize, type_check, validate_field, validate_field2
Sets the value of a parameter to be used by a query. Parameters must be enclosed in square brackets ( [param1] ) when declared in a SQL statement, but the brackets must be omitted when calling set_parameter.
See Also: clear_params, get_parameter, init_params, parse_sql, run_query, run_sql
Converts an atom (number) into a string.
SELECT THE.FIELD, STR(THE.FIELD) AS STRING FROM MY_TABLETHE.FIELD STRING --------- ------ 1 "1" 6.7 "6.7"
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, SUM, UNION, UPDATE, VAL, WHERE, [...]
Returns the sum of records selected.
ex:
SELECT SUM(SALARY) FROM EMPLOYEES;
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, UNION, UPDATE, VAL, WHERE, [...]
Tokenizes a sequence based on a separator. Used to tokenize a field to be passed to validate_field(). tokenize function by F Dowling (check the archives at www.rapideuphoria.com)
See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, type_check, validate_field, validate_field2
See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, validate_field, validate_field2
Allows you to combine two SELECT statements into one query:
SELECT NAME, ADDRESS FROM EMPLOYEES UNION SELECT NAME, ADDRESS FROM DEPENDENTSThe resulting dataset would contain two fields (name and address) with the combined data from each SELECT statement.
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UPDATE, VAL, WHERE, [...]
Update records in a table. The format for the UPDATE command is:
UPDATEFor example, if you wanted to change all employees in department 3 to department 2:SET = , [ = ...] [WHERE ]
UPDATE EMPLOYEES SET DEPARTMENT = 2 WHERE DEPARTMENT = 3
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, VAL, WHERE, [...]
Update a record. 'key' specifies which record is to be updated. 'index' specifies the field indices to update, and data is a sequence of field values to update.
See Also: delete_record, get_record, get_record2, get_record_mult_fields, insert_record, insert_record2, update_record2
Update a record.
See Also: delete_record, get_record, get_record2, get_record_mult_fields, insert_record, insert_record2, update_record
Converts a string to an atom (number), or leaves an atom value alone.
SELECT THE.FIELD, VAL( THE.FIELD ) AS NUM FROM MY_TABLETHE.FIELD NUM --------- --- "1" 1 "6.7" 6.7
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, WHERE, [...]
ex: -- Assume a table "PERSON": -- NAME.FIRST -- NAME.LAST -- EMAILfield = tokenize( "NAME.FIRST", '.' ) struct = get_record_struct( my_db, "PERSON" ) field_index = validate_field( field, struct )
-- field_index = {1,1}
See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field2
Returns the field index the field for field_name in table table_name in the open database db_name. This function calls get_record_struct(), tokenize() and validate_field() for you. If you need to call this multiple times, it will be faster to store a local copy of the results from get_record_struct() and tokenize() and pass them directly to validate_field().
ex: -- Assume a table "PERSON": -- NAME.FIRST -- NAME.LAST -- EMAILfield_index = validate_field( my_db, "PERSON", "NAME.FIRST" )
-- field_index = {1,1}
See Also: blank_field, blank_record, flat_field_names, flat_index, flat_record, get_field_datatype, get_next_autonumber, get_record_struct, map_field, seq_store, tokenize, type_check, validate_field
Set constraints on records selected. You can use =, >, < or LIKE for comparisons. String arguments must be enclosed by single quotes ' '. If you have multiple conditions (using AND and OR to tie them together, you should put the individual conditions in parentheses, since the precedence order may not be what you believe it to be.
ex:
SELECT NAME, AGE FROM EMPLOYEES WHERE (AGE > 40) AND (NAME LIKE 'A*');
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, [...]
Used to specify a parameter that can be set independently of parsing/compiling the query. Use set_parameter to set the value of a parameter. Parameters can be used as values for fields or for comparison purposes. This can be used for queries that are run often, but which need different values. Rather than building/parsing a new SQL command, the parameters in a compiled query can be modified.
ex:
SELECT NAME FROM EMPLOYEES WHERE NAME LIKE [NAME];set_parameter( "name", "A*" )
See Also: &, AS, AVG, COUNT, CREATE, DELETE, DISTINCT, DROP, FROM, GROUP, IF, IN, INSERT, JOIN:, LEFT, LEN, LIKE, MAX, MID, MIN, ORDER, RIGHT , SELECT, STR, SUM, UNION, UPDATE, VAL, WHERE
punctuation
] keyword
] aggregate
] func
] func
] proc
] proc
] aggregate
] command
] func
] func
] func
] func
] command
] func
] keyword
] command
] func
] func
] type
] User defined typeproc
] proc
] proc
] func
] func
] func
] keyword
] func
] func
] func
] func
] func
] func
] func
] func
] aggregate
] keyword
] keyword
] proc
] command
] func
] func
] keyword
] keyword
] keyword
] keyword
] func
] aggregate
] keyword
] aggregate
]
] EuSQL.e v0.76.8func
] keyword
] func
] func
] func
] func
] keyword
] func
] func
] command
] func
] func
] func
] func
] proc
] keyword
] aggregate
] func
] func
] keyword
] command
] func
] func
] keyword
] func
] func
] keyword
] punctuation
]