Command Discussion

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 specified.

Specifying a 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 Vortex a <sqlcp cache close> or switching databases may remove the temporary table.

A BTREE table creates a table that is inherently indexed by the fields in the order listed. You can not create other indexes on a 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, RESUME, BUDGET93, and 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
EMPNBR	EMP-NBR	Hyphen is not allowed.
EMP_NBR	EMP.NBR	Period is not allowed.
COST1	COST_IN_$	$ is not allowed.
COST_PER_MILE	COST PER MILE	Spaces are not allowed.
SALES1991	1991SALES	Name cannot start with a number.
Where	WHERE	Can not be SQL keyword.
Date	DATE	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

CHAR(length):

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.

CHARACTER(length):

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.

BYTE:

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.

LONG:

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.

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 is -2147483648.

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.

SMALLINT:

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.

INT64:

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.

UINT64:

Similar to INT64, but unsigned. Value range is 0 through 18,446,744,073,709,551,616. Added in version 6.

FLOAT:

Used to store real numbers where numerical precision is important. Very large or very small numbers expressed in scientific notation (E notation).

DOUBLE:

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.

DATE:

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 '1994-03-05 3:00pm'

VARCHAR(length):

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.

BLOB:

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.

BLOBZ:

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 in some cases, via the executables specified by the Blobz External Compress EXE (here) and Blobz External Uncompress EXE (here) commands in the [Texis] section of the conf/texis.ini configuration file; see those settings for more caveats. 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.

INDIRECT:

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.

COUNTER:

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.

STRLST:

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 to a varchar result string (this aids conversion back to strlst when the varchartostrlstsep setting, 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 ("=" comparison 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 empty).

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 VARCHAR(100), indicates that 100 characters is a suggested length. If an entry of 350 characters is required in this field, VARCHAR can make allowances to handle it.

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 operations.

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.