Fun with DataTypes

Javascript Query Language supports a handful of built-in data types for all sorts of data, strings, numbers, dates, even executable functions. The default dataType, for tables that do not specify datatypes for their columns, is "AMBI", which stores strings, numbers, functions and Date objects, anything else is converted to a string.

jSQL does provide an API for adding your own data type definitions, which is useful for storing instances of your own custom objects.

---

Supported Types

Numeric Types

• NUMERIC(M)
  • Aliases: NUMBER, DECIMAL, FLOAT
  • Stores types as numbers
  • The optional M represents the display width of the number
  • The perens are optional too if you are not providing an argument for M
  • As in MySQL, the display width is for application use only and does not restrict the length of the value stored in the column.
• TINYINT(M)
  • Store an integer between -128 and 127
  • The optional M represents the display width of the number
  • The perens are optional too if you are not providing an argument for M
  • As in MySQL, the display width is for application use only and does not restrict the length of the value
• SMALLINT(M)
  • Store an integer between -32768 and 32767
  • The optional M represents the display width of the number
  • The perens are optional too if you are not providing an argument for M
  • As in MySQL, the display width is for application use only and does not restrict the length of the value
• MEDIUMINT(M)
  • Store an integer between -8388608 and 8388607
  • The optional M represents the display width of the number
  • The perens are optional too if you are not providing an argument for M
  • As in MySQL, the display width is for application use only and does not restrict the length of the value
• INT(M)
  • Store an integer between -2147483648 and 2147483647
  • The optional M represents the display width of the number
  • The perens are optional too if you are not providing an argument for M
  • As in MySQL, the display width is for application use only and does not restrict the length of the value
• BIGINT(M)
  • Store an integer between -9007199254740991 and 9007199254740991
  • The optional M represents the display width of the number
  • The perens are optional too if you are not providing an argument for M
  • As in MySQL, the display width is for application use only and does not restrict the length of the value

String Types

• VARCHAR(M)
  • Store an string of varying length
  • The optional M represents the length of the string
  • The perens are optional too if you are not providing an argument for M
  • As in MySQL, the length is for application use only and does not restrict the length of the value
• CHAR(M)
  • Stores a string of a fixed length
  • The M represents the length of the string
  • If string is shorter than M, string is right padded with spaces, if string is longer, it is truncated.
• ENUM(M, N, ...)
  • Stores any of the pre-specified strings
  • M, N, and any other arguments you pass to it will be available for insert in that column. Anything else will throw an error.

Other Types

• FUNCTION
  • Stores functions.
  • Will throw an error if non functions are stored.
  • Must use a prepared statement to insert a function into a table.
• BOOLEAN
  • Alias: BOOL
  • Store boolean values
• JSON
  • Stores a JSON object
  • Must use a prepared statement to insert a JSON object (or Array) into a table
• DATE
  • Stores a Javascript Date object
  • If using a prepared statement, you can insert an actual JS date object, otherwise any quoted string parsable by Date.parse() will work.
• AMBI
  • Stores functions and JS Date objects, everything else is converted to a string.

---

Creating custom DataTypes

Custom datatypes are useful for storing instances of custom objects, or otherwise filtering your data in your columns.

To create a new data type, use the jSQL.types.add() method to add a new dataType. It takes an object with 4 properties:

• type: The datatype name
• aliases: An array of aliases (alternative names for this datatype)
• serialize: A function that receives the value to be stored, and an array containing any arguments passed into the table definition. This function must return a string serialization of the object.
• unserialize: A function that takes the serialized value to be returned and any arguments passed from the table definition and should return the unserialized version of the stored object.

When using custom data types, it is important to defin them before the database is loaded into memory (before jSQL.load() is called), so that any items stored in memory can be properly interpreted by the API.

Example

jSQL.types.add({
	type: "ENUM",
	serialize: function(value, args){
    	if(args.indexOf(value) < -1) value = args[0];
	    return value;
	},
	unserialize: function(value, args){
		return value;
	}
});

---