swint-query

swint-query

MySQL query generator for Swint

Warning: This is not the final draft yet, so do not use this until its official version is launched

Installation

$ npm install --save swint-query

Manager

• Overall manager of ORM's
• Usage

var manager = swintQuery.Manager({
		dir: path.join(__dirname, 'models'),
		mysql: {
			host: 'myhost.example.com',
			database: 'mydb',
			user: 'username',
			password: 'mypasswd'
		}
	}, function(err) {
		print(manager.models); // Models fetched from DB server
		done();
	});

Structure

• Defining the model's structure
• Usage

var defs = {
		name: 'foo',
		engine: 'MySQL',
		table: 'foos'
	},
	schema = [
		{
			field: 'bars',
			type: 'RelN:M',
			related: 'bar',
			joinTable: 'foos_bars',
			myKey: 'foo_id',
			relKey: 'bar_id'
		},
		{
			field: 'bazs',
			type: 'Rel1:N',
			related: 'baz',
			joinTable: 'bazs',
			myKey: 'foo_id'
		},
		{
			field: 'qux',
			type: 'RelN:1',
			related: 'qux',
			relKey: 'qux_id'
		}
	];

module.exports = function(manager) {
	defs.db = manager.mySqlConn;
	return swintQuery.Structure(manager, defs, schema);
};

Model

• Methods

  • .query(query, callback)

    • Executes query
    • query: String, the query to be executed
    • callback: Function

  • .query(preparedQuery, params, callback)

    • Executes prepared query
    • preparedQuery: String, the query to be executed
    • params: Array
    • callback: Function

  • .save(data, callback)

    • Insert data if the data doesn't have id, or update data if it has.
    • data: Object or Array, If array, it must be the array of objects to be saved
    • callback: Function

  • .fetch(condition, option, callback)

    • condition: Object, the key is the name of field
    • option: Object
      • target: Array, the list of fields to be fetched, can be * or joinedModel.*.
      • join: Array, the list of the names of models to be joined
      • order: String or Array, the name of the field to be sorted, can be Array.
      • orderFlag: Boolean or Array, the sorting order
      • limit: Number, the number of rows to fetch

  • .queryPromise(query)

    • Executes query
    • query: String, the query to be executed

  • .queryPromise(preparedQuery, params)

    • Executes prepared query
    • preparedQuery: String, the query to be executed
    • params: Array

  • .savePromise(data)

    • Insert data if the data doesn't have id, or update data if it has.
    • data: Object or Array, If array, it must be the array of objects to be saved

  • .fetchPromise(condition, option)

    • condition: Object, the key is the name of field
    • option: Object
      • target: Array, the list of fields to be fetched, can be * or joinedModel.*.
      • join: Array, the list of the names of models to be joined
      • order: String or Array, the name of the field to be sorted, can be Array.
      • orderFlag: Boolean or Array, the sorting order
      • limit: Number, the number of rows to fetch

• Usage

models.foo.fetch({
	myColumn: operator.ne('foo')
}, {
	target: ['*', 'bar.*'],
	join: ['bar'],
	order: 'myColumn',
	orderFlag: true,
	limit: 42
}, function(err, res) {
	// ...
});

Operator

• Various operators
• eq: Equal, { fieldName: eq(42) }
• ne: Not equal, { fieldName: ne(42) }
• gt: Greater than, { fieldName: gt(42) }
• gte: Greater than or equal, { fieldName: gte(42) }
• lt: Less than, { fieldName: lt(42) }
• lte: Less than or equal, { fieldName: lte(42) }
• btn: Between, { fieldName: btn(42, 84) }
• nbtn: Not between, { fieldName: nbtn(42, 84) }
• isNull: Is null, { fieldName: isNull() }
• notNull: Not null, { fieldName: notNull() }