data types and constraints

Creating a Table

In JSQL-SX a table is created in the general form by:

// Get a handle on a database //
var db = jsql.DB.open(db_name);
// Create the table //
db.create(table_name,
  [[column_name_1, data_type, constraint],
   [column_name_2, data_type, constraint],
   ...
   [column_name_n, data_type, constraint]]
);

The general form for a column definition is an array containing 3 elements:

1. Column Name
2. Data Type
3. Constraint

Example:

// Get a handle on a database named 'mail' //
var db = jsql.DB.open("mail");
// Create a table named 'inbox' //
db.create("inbox",
  [["id",   "INTEGER", "PRIMARY KEY"],
   ["date", "TEXT",    "NOT NULL"],
   ["from", "TEXT",    "NOT NULL"],
   ["to",   "TEXT",    "NOT NULL"],
   ["msg",  "TEXT",    "NULL"],
   ["new",  "BOOLEAN", "NOT NULL"]]
);

Data Types

A data type is an attribute that specifies the type of data the values in a column are allowed to be.

In JSQL-SX, data types are much simpler than the ones specified by SQL.

Data Type	Comments
INTEGER or INT	In general, any value considered to be an integer by Javascript. Accepts integers as a string (for example, "20"). Accepts whole numbers (for example, 20.0 is accepted but 20.01 is not).
FLOAT	In general, any value considered to be a float by Javascript. Accepts floats as a string (for example, "20.01"). Accepts integer numbers.
BOOLEAN or BOOL	The Javascript boolean data type plus a couple of tweaks. Accepts booleans as a string (for example, "true"). Accepts the numbers 0 (false) or 1 (true).
TEXT	Columns of this data type can contain string values.

Constraints

Constraints are the rules enforced on data columns.

In JSQL-SX, only column-level constraints are available and, currently, only the Primary Key constraint has real effects.

Constraint	Comments
NULL	Allows for null or undefined values. Currently not checked.
NOT NULL	Makes sure no null or undefined values are addmitted when the insert() method is invoked. Currently not checked.
PRIMARY KEY	Makes sure every value in the column is unique. Auto-incrementing is used. An integer data type is assumed. There can only be 1 primary key column per table.
TEXT	Basically, column of this data type can contain any value (even arrays). Supposed to be the most flexible data type.