The CREATE TABLE command gives the name of the table, the name of each
column in the table, and the type of data placed in each column. It
can also indicate whether null values are permitted in columns.
- Table Type:
- When creating a table you can optionally specify a
table type. A standard database table will be created if no type is
RAM table will create a table that only exists in
memory for the current database connection. The table is not added
to the system catalog, and is not visible to other database connections.
It can be used as a temporary working table in an application. Within
<sqlcp cache close> or switching databases may remove
the temporary table.
BTREE table creates a table that is inherently indexed by
the fields in the order listed. You can not create other indexes on
BTREE table. This can be useful for key-lookup tables that
have a lot of small rows.
- Table Names:
- Each table in Texis is assigned a name. A table
name can have up to 18 characters (case is significant). The first
character must be a letter, but the remaining characters can include
numbers, letters, and the underscore (
_) character. Table names
may not be the same as SQL keywords or data types. For example,
PROD_TEST are all valid table
names. On MSDOS based systems table names must be unique regardless of
case in the first 8 characters.
- Column Names:
- A column stores data on one attribute. In our
example, we have attributes such as Resume ID, job sought, education,
and experience. Each column within a table has a unique name and may
consist of up to 18 characters (case is significant). The first
character must be a letter and the remaining characters may consist of
letters, numbers, and the underscore (
_) character. No blank
spaces are allowed in the column name. Table names may not be the same
as SQL keywords or data types. Table here shows examples of
valid and invalid column names.
|Valid Column Names || Invalid Column Names || Reason Invalid |
| Hyphen is not allowed. |
| Period is not allowed. |
$ is not allowed.
COST PER MILE
| Spaces are not allowed. |
| Name cannot start with a number. |
| Can not be SQL keyword. |
| Can not be SQL data type. |
Table: Valid and Invalid Column Names
- Data Types:
- Each column within a table can store only one type
of data. For example, a column of names represents character
data, a column storing units sold represents integer data, and a
column of file dates represents time data. In Texis, each
column name defined in the CREATE TABLE statement has a data type
declared with it. These data types include character,
byte, integer, smallint, float, double,
date, varchar, counter, strlst, and indirect.
Table here illustrates the general format for each data
type. A description of each of the Data Types listed in
Table here follows.
|Type of Data || Texis Syntax || Example || Data Value |
|Character || CHAR(length) || CHAR(10) || SMITH |
|Character || CHARACTER(length) || CHAR(25) || 10 Newman Rd |
|Byte || BYTE(length) || BYTE(2) || DE23 |
|Numeric || LONG || LONG || 657899932 |
|Numeric || INTEGER || INTEGER || 657899932 |
|Numeric || SMALLINT || SMALLINT || -432 |
|Numeric || FLOAT || FLOAT || 8.413E-04 |
|Numeric || DOUBLE || DOUBLE || 2.873654219543E+100 |
|Numeric || UNSIGNED INTEGER || UNSIGNED INTEGER || 4000000000 |
|Numeric || UNSIGNED SMALLINT || UNSIGNED SMALLINT || 60000 |
|Date/Time || DATE || DATE || 719283474 |
|Text || VARCHAR(length) || VARCHAR(200) || "The subject of ..." |
|Text || INDIRECT || INDIRECT || Filename |
|Counter || COUNTER || COUNTER || 2e6cb55800000019 |
|String list || STRLST || STRLST || apple,orange,peach, |
Table: Data Types Used in Texis
- Used to store character data, such as names, job
titles, addresses, etc. Length represents the maximum number of
characters that can be stored in this column. CHAR can hold the value
of any ASCII characters 1-127. Unless you want to limit the size of
the field absolutely you should in general use VARCHAR instead as it
is more flexible.
- Same as CHAR, used to store character data,
an alternate supported syntax. As with CHAR, length represents the
maximum number of characters that can be stored in this column.
- Similar to CHAR but with significant differences, BYTE is
used to store any unsigned (non-negative) ASCII values from 0-255.
Specifying BYTE indicates each is a one byte quantity. A byte would
be used where you want to store a small number less than 255 such as
age, or perhaps a flag. A VARBYTE can also be used where the length
of specified characters is variable rather than fixed, where you are
storing arbitrary binary data.
- Used to store large whole numbers; i.e., those without
a fractional part, such as population, units sold, sales in dollars.
The range of long values will depend on the platform you are using.
For most platforms it is identical to INTEGER.
- Used to store large whole numbers where you want to
ensure a 32-bit storage unit.
The largest integer value is +2147483647. The smallest integer value
- UNSIGNED INTEGER:
- Used for similar purposes as INTEGER when
you know the number will never be less than zero. It also extends
the maximum value from 2,147,483,647 to 4,294,967,295. This is
synonymous with DWORD.
- Used to store small whole numbers that require few
digits; for example, age, weight, temperature. The largest value is
+32,767. The smallest value is -32,768.
- UNSIGNED SMALLINT:
- Can store positive numbers in the range from
0 to 65,535. Can be used in many of the same places as SMALLINT.
- Used to store large whole numbers when a 64-bit quantity
must be assured (LONG size varies by platform). Value range is
-9,223,372,036,854,775,808 through +9,223,372,036,854,775,807.
Added in version 6.
- Similar to INT64, but unsigned. Value range is
0 through 18,446,744,073,709,551,616. Added in version 6.
- Used to store real numbers where numerical precision is
important. Very large or very small numbers expressed in scientific
notation (E notation).
- Used to hold large floating point numbers. Having the
characteristics of a FLOAT, its precision is greater and would be used
where numerical precision is the most important requirement.
- Used to store time measured in integer seconds since 00:00:00 Jan.
1 1970, GMT (Greenwich mean time). When entered in this fashion the
format is an integer representing an absolute number of seconds; e.g.,
719283474. The DATE data type is used to avoid confusions
stemming from multi-sourced information originating from different time
zone notations. This data type is entered by a program function rather
than manually, and would generally be converted to calendar time before
being shown to the user. DATEs may also be entered as strings
representing a date/time format such as
- Used to store text field information entirely
in Texis. The specified length is offered as a suggestion only, as
this data type can hold an unlimited number of characters. In the
example in Table here, there may be a short description of
the text, or a relatively small abstract which is stored in the field
of the column itself.
- Used to store text, graphic images, audio,
and so on, where the object is not stored in the table itself, but is
indirectly held in a BLOB field. BLOB stands for
Binary Large Object, and can be used to store the content of many fields
or small files at once, eliminating the need for opening and closing
many files while performing a search. BLOB is used when having a specific
filename is not desired. The BLOB is created and managed at a system
level. The total data held for all BLOBs in a table is limited by the
filesystem. The BLOB file is not accessed unless the data in it is needed.
This will improve the performance of queries that do not need to access
the data. This can also be useful if you are creating a METAMORPH
INVERTED index, and do not allow post processing, and do not display the
actual contents of the record, as the data will not be accessed at all,
and can be removed. This should only be done with extreme caution.
- Similar to BLOB fields, except that each BLOBZ's data is
compressed before storing on disk, and is decompressed upon reading
from disk. The compression/decompression is done internally.
Alternatively, it can be handled externally via the executables
specified by the Blob Compress EXE and Blob Uncompress EXE
commands in the
[Texis] section of the
texis.ini configuration file. External compression allows
custom compression types to be deployed - perhaps better than the
gzip format supported internally by Texis - but at a speed
penalty due to the overhead of running the executables. The BLOBZ
type is only supported in Texis Version 8 and later.
- Used to store filenames which point to data stored in
some other location. Most frequently an INDIRECT column would point
to files containing quantities of full text.
Only one filename may be stored in an INDIRECT field. The filenames
can be inserted with SQL by specifying the filename as a string, or
through a program, which might generate the files to store the data.
The choice of storing
text or filenames only in Texis will depend on what you plan to do
with the files, and also how big they are. INDIRECT can be used to
point to images or other objects as well as text, although currently
only text files can be meaniningfully indexed.
- This field holds an 8 byte value, which can be made
unique across all tables in the database. To insert a counter
value in SQL you can use the
COUNTER keyword in the insert clause.
A counter is made up of two fields, a time, and a sequence number.
This allows the field to be compared with times, to find all records
inserted before a particular time for example.
- A string list is used to hold a number of different
strings. The strings are delimited by a user defined character in the
input string. The delimiter character is printed as the last
character in the result string when a
strlst value is converted
varchar result string (this aids conversion back to
strlst when the
here, is "
lastchar"). This type
is most useful when combined with an application which needs lists of
strings, and set-like operators such as IN, SUBSET or INTERSECT. Other operators are generally undefined for strlst,
though in Texis version 7 and later equality ("
etc.) is defined to be monolithic string-compare of the entire list;
equality of strlst and varchar is the same, treating the
varchar as a one-item strlst (if non-empty) or empty strlst (if
One large difference in Texis over other database management systems
is in the range of data types it supports. While the traditional
fixed length forms of CHAR, INTEGER, FLOAT and so on are used, there
is a corresponding variable length data type which can be used when
appropriate, such as is represented in VARCHAR.
The length following CHAR, as in
CHAR(100), indicates that 100
is the maximum number of allowed characters. Each record with such a
data type defined will have a size of 100 characters, regardless of
whether 3 characters, 57 characters, or even a NULL value is entered.
The length following VARCHAR, as in
that 100 characters is a suggested length. If an entry of 350
characters is required in this field, VARCHAR can make allowances to
The 100 character suggestion in this case is used for memory
allocation, rather than field length limitation. Therefore a VARCHAR
length should be entered as the average, rather than the largest size
for that field. Entering an extremely large length to accommodate one
or two unusual entries would impair the handling of memory for normal
The sophisticated aspects of database design involving choice and use
of data types towards performance and optimization of table
manipulation are addressed in more depth in Chapter here,
Administration of the Database.
The order in which the columns are listed in the CREATE TABLE command
is the order in which the column names will appear in the table.
Copyright © Thunderstone Software Last updated: Aug 4 2020