|
DB create Making a new DB
Parameters:
[with properties record]: the properties of the database.
- The record format is {DBName: string, DBFields: list of field-descriptions
(see: field description}
- The default for DBName is "ScriptDB".
- The default for DBFields is one field with class string and name field1
Returns:
The database.
Example:
set db to DB create with properties {DBName:"my_temp_db", DBFields:{{"num",
integer}, {}}}
DB add record Adding a record
to the DB
Parameters:
Script DB Record: a list of values, one per database field
- Any field 's value missing completed with default (see: Field classes and
defaults).
- Any additional field is ignored.
- All fields are converted to field class defined in the database.
If the conversion failed, -6802 error is returned.
to db Script DB: the script db
[at integer]: Position to insert the record.
- Default is at the end of the database.
- 0 or 1 or any negative number means at the beginning of the database.
- The parameter works in insert mode so "at 3" means before current
record 3.
- A number larger than records number refers to end of database.
Returns:
The database with the new record.
Example:
set my_db to DB add record {1, "my name"} to db my_db Notes:
- For adding more than one record it is advised to use DB add records and not
call DB add record many times.
- The operation fails if the database reached the maximal records number (after
the addition, error -6847).
DB add records Adding many
records to the DB
Parameters:
list of ScriptDB Records: a list of lists. In every inside list there
are values for one database record.
- Any field's value missing completed is with default (see: Field classes
and default values).
- Any additional field is ignored.
- All fields are converted to field class defined in the database.
If the conversion failed, error -6802 is returned.
to db Script DB: the script db
[at integer]: Position to insert the
record.
- Default is at the end of the database.
- 0 or 1 or any negative number means at the beginning of the database.
- The parameter works insert mode so "at 3" means before current
record 3.
- A number larger than records number refers to end of database. Returns:
The database with the new record.
Example:
set my_db to DB add records {{5, "my name"}, {10, "your name"}}
to db my_db Note:
The operation fails if the database reached the maximal records number (after
the addition, error -6847).
DB update record Updating a
record
Parameters:
integer: The number of record to update.
- The number must be between 1 and the number of records in the database.
in db Script DB: the script db
[with data ScriptDB record]: a list of values, one per database field
- Any field 's value missing completed with default (see: Field classes and
defaults).
- Any additional field is ignored.
- All fields are converted to field class defined in the database.
If the conversion failed, error -6802 is returned.
Returns:
The database with the new record.
Example:
set my_db to DB update record 5 in db my_db with data
{"abc", 5} Notes:
For adding more than 1 record it's advised to use DB add records.
DB load file Add records from
a file
Parameters:
string: a string with the filename
[with properties record]: Database fields description. (see: Fields
description)
- Ignored in XML mode.
- Required in text mode.
[field delimiters list]: A list of 1-character strings each item is
a separator.
Or one 1-character string as the only separator.
- Default is ",".
[record delimiters list]: A list of 1-character strings each item is
a separator.
Or one 1-character string as the only separator.
- Default is Return and LF (ASCII 10 and 13 - any of them is taken as separator).
[format XML/txt/txt UTF16]: The format of the file - text with delimited
data as ASCII or UTF16, or XML.
- Default is XML.
Returns:
The database loaded.
Example:
set d to DB load file filename with properties {DBName:"Data", DBFields:{{"random",
real}, {"str", string},
{"zone", integer}}} field delimiters " " format txt Notes:
In case of conversion problems for a field, the default value is taken (see:
Default values).
DB save Save file data
Parameters:
Script DB: The database to save.
to file string: The filename.
[field delimiters list]: One 1-character string as the field separator.
- Default is ",".
[record delimiters list]: One 1-character string as the record separator.
- Default is LF (ascii 10).
[format XML/txt/txt UTF16]: The format of the file - text with delimited
data as ASCII or UTF16, or XML.
- Default is XML.
Returns:
Nothing.
Example:
DB save my_db to file filename format txt Error codes:
-6811 Missing parameter.
-6812 Bad parameter.
-6851 Bad field separator.
-6852 Bad record separator.
-6853 Invalid filename.
-6854 Unable to open file.
DB delete records Delete records
Parameters:
from db ScriptDB: The database
[number anything]: Record numbers to delete (see: Record numbers reference).
[where list]: An expression to restrict deleted record (see: Find expression).
Returns:
The database after records deletion.
Example:
set my_db to DB delete records where {"fIeld(age)", ">",
10} number "11-20" from db my_db Notes:
At least one of the number and where parameters should be sent. If both are
used only records which meet both restrictions are deleted.
DB get data Find records according
to given params
Parameters:
from db ScriptDB: The database
[in records anything]: Record numbers to get data from (see: Record
numbers reference). If omitted, all records are taken (if meet other
parameters).
[fields anything]: Fields to get. If omitted, all fields are taken.
(see Fields reference).
[where list]: An expression to restrict the records returned by the
command (see: Find expression).
[as database/data only/row numbers]:
The form in which the data is returned.
as database - the data is returned as a ScriptDB
as data only (default) - the data is returned as a list of lists
as row numbers - the row numbers found returned as a list
[transposed boolean]:
If the data is returned as data only, and transposed is on the lists of lists
is transposed, meaning every internal list is made a column in the so- called
matrix.
For example, {{5,"USA",200},{10,"CANADA",300}} transposed
would be:
{{5,10},{"USA","CANADA"},{200,300}}
The parameter is ignored is used only in as data only mode.
The default if without transposed.
Returns:
The data found as ScriptDB, list of lists or list of row numbers, see as parameter
above.
Example:
set age_limit_db to (DB get data where {"field(age)", "≥",
min_age, "AND", "field(age)", "≤", max_age}
from db my_db as database) Notes:
In data only return mode the list of lists is always simplified, so brackets
are removed from one item lists.
This can be in two situations, as in the examples:
{{1},{2},{3}} => {1,2,3}
{{1,2,3}} => {1,2,3}
DB sort db Sort records
Parameters:
ScriptDB: The database
by anything: A reference to the sort keys (See: fields reference).
At least one key should be mentioned.
The '-' sign before a key means the sort is descending for this key.
If a few keys are mentioned the first is primary, second secondary etc.
Example:
DB sort db y by {"-grade", "last name"} Returns:
The sorted ScriptDB.
Error codes:
-6811 Missing parameter.
-6812 Bad parameter.
DB Add field Add a field to
a db
Parameters:
record: The description of the field to add (see: Field description).
to db ScriptDB: The database to which we add the field.
- The field name must be new. Otherwise, error -6806 is returned.
[with data list]: a list of values, one per database record
- If there are less items in the list than records in database, the last records
get the value defined in using default parameter or if it is omitted, the
default value according to field definition (see: Default values).
- Any additional items in the list is ignored.
- Every field is converted to field class defined in the database.
If the conversion failed, error -6810 is returned.
[using default anything]: Default to use no data is mentioned to some
(or all) or the records.
- If the with data parameter is omitted, all records get the default as the
value of the new field.
- If the with data parameter contains less items than database records, the
records with no data get the default as the value of the new field.
- The parameter is converted to the class of the new field. If the conversion
failed, error -6809 is returned.
Returns:
The database with the new field.
Example:
set my_db to DB add field {"new_field", integer} with data {1, 2}
using default 10 to db my_db Note:
The operation fails if the database had already the maximal fields number (error
-6844).
DB remove field Remove a field
from a db
Parameters:
string: The name of the field to remove.
from db ScriptDB: The database to remove the field from.
Returns:
The database with the new field.
Example:
set my_db to DB remove field {"name"} from db my_db Note:
It is not allowed to remove the only field in a database (error -6808).
DB count records Returns number
of records of the database
Parameters:
of db SctiptDB: The database.
Returns:
The number of records in the database.
Example:
set records_num to DB count records of db my_db
DB count fields Returns number of fields of the database
Parameters:
of db SctiptDB: The database.
Returns:
The number of fields in the database.
Example:
set fields_num to DB count fields of db my_db
DB register Register a serial number
Parameters:
String: The serial number.
Returns:
License status.
Example:
DB register my_serial_number
DB check license Check license status
Parameters:
None.
Returns:
License status.
Example:
DB check license
Field classes and defaults
Here are the classes supported in the database and their defaults:
1. integer, 0
2. short, 0
3. real, 0
4. string, ""
5. unicode string, ""
5. boolean, true
6. Script DB Date, 1/1/1904
7. Script DB Time, 00:00:00
8. Script DB Timestamp, 1/1/1904 00:00:00
Fields description
Format:
Field description can be given as:
1) A record with words:
a) DBFieldName: string
b) DBFieldClass: type class
2) A list with two items with fixed order:
a) string as the field name.
b) type class as field class.
Restrictions:
In both ways,
1) The class must be a supported one (see: Field classes and defaults)
2) Field name is normalized, meaning all leading and trailing blanks
and tabs are removed, and all blank/tabs sequences are converted to 1
blank.
Normalized field name have to:
a) Be unique (name is case insensitive)
b) Start with a letter.
c) All characters are letters, digits, _ (underscore), - (dash) or space.
d) Not more than 20 characters long.
Defaults:
1) The default class is string.
2) The default field name is "Fieldn", where n is the number
of the field. If the name is already taken, will try n+1, n+2 and so
on until finding a free one.
Record numbers reference
Record reference is a way to specify one of more database records. It
is used in DB get data and DB delete records commands.
There are three ways for specify the record/s:
1) Integer - the record number in the database.
The number must be within 1 and the number of fields in the database.
2) String - list of field ranges.
The string is a list of record ranges and numbers, separated by commas.
For example, "2,4-7,20" refers to records 2,4,5,6,7,20
3) A list of Integers specifying record numbers.
Duplicate record numbers count as one, and record numbers out of range
are ignores.
The records are returned sorted by original record number.
For example, in a database with 20 records, the reference "20,10-12,19-22" will
return fields 10,11,12,19,20 in this order.
Fields reference
Field reference is a way to specify one of more database fields. It
is used in DB Sort and DB get data commands.
There are three ways for specify the field/s:
1) Integer - the field number in the database. Note that the number
is the current field number in the DBFields property in the database
and not the number in which the field was originally created.
The number must be within 1 and the number of fields in the database,
otherwise an error is returned.
2) String - the field name (case insensitive).
3) A list of Integers and strings containing references as described
on 1 and 2. It is valid to mix both integers and strings on the same
list.
In the sort command it is valid to mention "-" before
the reference, whether it is a string or a number.
A field reference may not contain more than one reference to the same
field.
Find expression
In several commands there is an option to restrict data affected by
applying
on it a boolean expression. The expression is a list of items forming
a boolean result.
The items may be one of the following:
1. An integer - added to the expression as a numeric value.
2. A real number - added to the expression as a real number value.
3. Boolean - added to the expression as true or false.
4. Date - added to the expression as date value.
5. String - added to the expression as:
a) Operator - if found in operators list.
OR
b) Field value - if the string is of the form "Field(FieldName)"
OR
c) A String value, otherwise.
6. Unicode text.
7. A list - an internal expression. It is added to main expression as
the internal expression enclosed by logical brackets.
8. Any other item class results in error -6858.
For example, the expression: {Field(age), ">",
18}
Returns TRUE for all records with field names "age" greater
than 18.
The supported operators are:
Numeric
*
/
+ (can be used for strings concatenation as well)
-
Comparison
=
≠
>
≥
<
≤
Field notation
Field(FieldName)
Logical
AND
OR
NOT
(
)
Constant operands
True, False
{ } script list markers can be used as logical brackets.
So, {10,"=","(","Field(num)","*","2",")"} is equivalent to:
{10,"=",{"Field(num)","*","2"}}
The operations are calculated according to the following order, if a
few on same level, in order of appearance:
1. Anything within brackets.
2. Field()
3. *, /
4. +, -
5. =,≠,>,≥,<,≤
6. NOT
7. AND
8. OR
Example:
{5,"*",{"field(sum)","+",2},"OR","field(age)",">","2","AND","field(age)","<",10}
With brackets to show order, this expression results in:
(5*(field(sum)+2)) or ((field(age) > 2) and (field(age)<10))
The OR operator calculated last. The + is calculated before the * as
it is bracketed.
- There is no cross-type operations, other than an integer an a real
number or string and unicode text.
- All operators, field names and strings are case insensitive.
Note:
Both operators and string values are sent to the scripting addition within
quotes.
If there is a mistake in the operator form the string value will be taken,
so some error like too many operands may occur.
If a string value similar to an operator form is required, for example a string
with the value "and", a script variable containing this word should
be used.
Hardcoded Limitations
Max field name length - 20
Max fields in a database - 100
Max length of field string reference - 500
Max length of record string reference - 500
Max size of a single field value - 500
Max separators number (in DB load) - 10
Max number of sort keys - 10
|
HOME
Download
PDF version