Skip to content

Latest commit

 

History

History
246 lines (185 loc) · 6.88 KB

tables.md

File metadata and controls

246 lines (185 loc) · 6.88 KB
Raw

Tables configuration

Creating the tables

This is what a table should look like during table creation:

    const User = {
      name : 'string/20',
      age : 'integer',
      mentor : User,
      contacts : [User],
    }

Each column of your table will be represented as a property of an object representing the table.

The properties value can be:

Data types

Inside your table object, to each column name, you must associate a data type. Current valid data types are the following:

  • string
  • integer
  • float
  • double
  • decimal
  • date
  • dateTime
  • time
  • year
  • boolean
  • char
  • text
  • binary
  • varbinary
  • varchar (synonymous: string)
  • json

const acceptedTypes = ['string', 'integer', 'float', 'double', 'decimal', 'date', 'dateTime', 'time', 'year', 'boolean', 'char', 'text', 'binary', 'varbinary', 'varchar', 'json', ];

There are 2 ways to express a data type:

The short string form

You can provide a string containing the data type optionally followed by the size of the column in bytes. / is used to separate 2 arguments.

Example:

    const User = {
      pseudo: 'string/25',
      email: 'string/40',
      password: 'binary/64',
      salt: 'binary/16',
    }

The object form

If you need to provide more details, you can use an object to describe your data type. In this case, you can provide the following parameters:

  • type (one of: string, integer, float, double, decimal, date, dateTime, boolean, text, binary)
  • length (in byte)
  • unsigned (boolean : default false)
  • notNull (boolean : default false)
  • defaultValue (same as type) You can use require('simple-ql').now for dynamic current date, and require('simple-ql').uuid to generate a UUID.
  • autoIncrement (boolean : default false)

Example:

    const Comment = {
      content: 'text',
      title: {
        type : 'string',
        length: 60,
        notNull : true,
        defaultValue : null,
      },
      createdAt: {
        type: 'date',
        defaultValue: now
      }
    }

reservedId

Every table will receive an extra column reservedId of type char/36 with defaultValue set to uuid that will uniquely identify an object within the database.

notNull

You can also require some fields to not accept null as a value by providing a field notNull with an array of columns names:

    const Comment = {
      content: 'text',
      title: 'string/60',
      author: User,
      notNull: ['author', 'title']
    }

Here, the fields author and title cannot be null.

Indexes

To create an index to your table, simply add an index entry to your object. The value should be an array containing the indexes.

There are 2 ways to describe an index:

The short string form

You can provide a string containing the column name of the index, optionally followed by the type of index and/or the size of the index of the index in bytes. / is used to separate 2 arguments.

Valid types are:

  • fulltext
  • unique
  • spatial

Example:

    const User = {
      pseudo: 'string/40',
      description: 'text',
      creation: 'date',
      index : ['creation', 'description/fulltext', 'pseudo/unique/8'],
    }

The object form

If you need to provide more details, you can use an object to describe your data type. In this cas, you can provide the following parameters:

  • column
  • type (one of: unique, fulltext, spatial, undefined. Default: undefined)
  • length (in byte)

You can generate an index over multiple columns. In this case, you must use the object form and provide a table for column and optionally for length.

Notice: The indexed column cannot reference an association field (like mentor or contacts in our example). The only exception is using an array of column, where you can index some Object fields (like mentor), but not an Array field (like contacts).

Example:

    const User = {
      firstname: 'string/40',
      lastname: 'string/40',
      index: [
        {
          column: 'email',
          type: 'unique',
          length: 8,
        },
        {
          column: ['firstname', 'lastname'],
          type: 'unique',
          length: [10,10],
        },
      ]
    }

Index on association tables, making associations unique

Imagine the following table:

    const User = {};
    Object.assign(User, {
        contacts : [User],
    });

Contacts will be converted into an association table, and you can link new contacts with this kind of requests:

{
  User : {
    contacts : {
      add : [
        { name : 'Jane Doe' },
        { name : 'Mummy' },
      ]
    }
  }
}

If you want to ensure that a contact cannot be linked twice to the same User, you can add the table to the index list:

    const User = {};
    Object.assign(User, {
        contacts : [User],
        index: ['contacts/unique']//Or more implicitely : index: ['contacts'] (in this specific case it will be considered the same)
    });

Self-references / Cross references between tables

If you get the following message:

<your column>[] has undefined value in <your table>. It should be a string, an array or an object. If you tried to make a self reference or a cross reference between tables, see the documentation.

This means that you tried to create a reference between tables at a moment when the target table was not yet defined. There are 2 ways to fix this:

1) Linking the tables after creating them with their own properties

    const User = {
      name : 'string/20',
      age : 'integer',
    }
    User.mentor = User;
    User.contacts = [User];

This might make it harder to read the interactions between tables and the complete list of a table's properties. I encourage you using the option below.

2) Creating all the tables and then creating all the properties and links at once with Object.assign

    import { modelFactory } = 'simple-ql'

    const tables = {};
    const { User, Products, Comments, Anything } = modelFactory(tables);

    // Now you can do cross references and self-references
    Object.assign(User, {
        name : 'string/20',
        age : 'integer',
        mentor : User,
        contacts : [User],
    });