The table keyword is used to create a table and optionally add rows of data to it. Tables can be created in several ways:
by manually specifying all values,
by taking values from data structures such as lists,
by takich values from the results of an sql query,
Syntax:
table table_name:
format_declaration
creating_index
column_name, column_name ...
column_value, column_value ...
column_value, column_value ...
...
format_declaration is described in the format section.
String values do not have to be enclosed by apostrophe (but enclosing may be necessary in some cases, for example if the first string value contains only digits). The only exceptions are declarations of DATE, TIME and TIMESTAMP:
DATE format_string [ language_code [country_code] ]
TIME format_string [ language_code [country_code] ]
TIMESTAMP format_string [ language_code [country_code] ]
format_string is in the Java LST clock format
the list of possible values for language_code is described by the ISO 639 specification (see Appendix)
the list of possible values for country_code is described by the ISO 3166 specification (see Appendix)
The following syntax is used to create indexes:
[unique] index on col1[(n)] [,col2[(n2)]...]["index_name"]
Each line describes one (single or multi-column) index. In order to create a number of multi-column indices, the instruction above should be repeated the required number of times. If index_name is omitted, it will be generated automatically in the form "idx_LINENUMBER"
The number of column_value entries in one row must be the same as the number of column_name entries.
Example 16.11. Format declaration:
table '_table_':
format a VARCHAR(5)
format b INTEGER
format c DATE 'yyyy MM dd'
format d TIMESTAMP 'yyyy MMMM dd - HH:mm:ss' "en" "en"
format e TIMESTAMP 'yyyy MMM dd - HH:mm:ss' "pl" "pl"
a b c d e
str 2 '2000 11 4' '2000 JULY 4 - 12:12:12' None
2 None None None '2000 STY 02 - 3:45:5'
'str' -3 '2001 05 13' None '1999 LUT 10 - 16:12:00'
sql a:
select * from _table_
for c in a.header: # see the chapter Data Access and Data Processing: Using SQL statement
print c
Output:
{'columnTypeName': 'VARCHAR', 'precision': None, 'displaySize': 2147483647, 'columnName': 'a', 'columnType': 12, 'isNullable': None, 'scale': None}
{'columnTypeName': 'INTEGER', 'precision': None, 'displaySize': 2147483647, 'columnName': 'b', 'columnType': 4, 'isNullable': 0, 'scale': 0}
{'columnTypeName': 'DATE', 'precision': None, 'displaySize': 2147483647, 'columnName': 'c', 'columnType': 91, 'isNullable': None, 'scale': None}
{'columnTypeName': 'TIMESTAMP', 'precision': None, 'displaySize': 2147483647, 'columnName': 'd', 'columnType': 93, 'isNullable': None, 'scale': None}
{'columnTypeName': 'TIMESTAMP', 'precision': None, 'displaySize': 2147483647, 'columnName': 'e', 'columnType': 93, 'isNullable': None, 'scale': None}
To quickly check the content of the created table the following command can be used:
Syntax:
table table_name <- sequence:
format_declaration
creating_index
column_name , column_name ...
...
Example 16.14. Sequences as source of data
ll = [[0, 1, 2], [3, 4, 5]]
table 'TAB1' <- ll:
a b c
lt = [(0, 1, 2),(3, 4, 5)]
table 'TAB2' <- lt:
a b c
tt = ((0, 1, 2),(3, 4, 5))
table 'TAB3' <- tt:
a b c
tl = ([0, 1, 2],[3, 4, 5])
table 'TAB4' <- tl:
a b c
tm = ([0, 1, 2],(3, 4, 5))
table 'TAB5' <- tm:
a b c
trans None <- 'TAB5':
print a, b, c
Output
0 1 2 3 4 5
Syntax:
table table_name <- sql_result:
format_declaration
creating_index
column_name, column_name ...
...
Example 16.15. SQL-query result as source of data
lista = [['2005-04-12', 12, 'aaa'], ['2007-08-16', None, 'bbb'], [None, 10, None]]
table 'TAB1' <- lista:
format a DATE 'yyyy-MM-dd'
format b INTEGER
format c VARCHAR(10)
a b c
sql res:
select * from TAB1
print "SQL-query result:"
print res
table 'TAB2' <- res:
a b c
trans None <- 'TAB2':
print a, b, c
Output
SQL-query result:
a | b | c |
+----------+------+------+--
2005-04-12 | 12 | aaa |
2007-08-16 | None | bbb |
None | 10 | None |
2005-04-12 12 aaa
2007-08-16 None bbb
None 10 None
Regardless of the method used to create a table it is possible to define column names or formats outside the 'table' part (as a list). To use such definition it is neceassary to prefix the name of the lists(s) inside the table procedure with the '$' character:
Example 16.16. Defining column names and column formats as a list.
tm = ([0, 1, 2],(3, 4, 5))
col_formats = ["a DOUBLE", "b INTEGER", "c VARCHAR"]
col_names = ['a', 'b', 'c']
table 'TAB1' <- tm:
format $col_formats
$col_names
trans None <- 'TAB1':
print a, b, c
print tableColumns('TAB1')
Output
0.0 1 2 3.0 4 5 ['a', 'b', 'c']
These methods of creating tables are recommended only for small amounts of data. In other cases data may be imported using:
the csvImport command for data stored in csv files,
GUI based CSV import option,
the xlsImport command for data stored in Excel spreadsheets,
the IMPORT TABLE GDBase command, for data stored in other databases,
the import feature of any other database used.