verdnatura-mirror
/
loopback-connector-mysql
mirror of https://github.com/loopbackio/loopback-connector-mysql.git
0
0
Fork 0
Code Issues Packages Projects Releases Wiki Activity
loopback-connector-mysql/README.md

370 lines
10 KiB
Markdown
Raw Permalink Blame History

## loopback-connector-mysql
`loopback-connector-mysql` is the MySQL connector module for [loopback-datasource-juggler](http://docs.strongloop.com/loopback-datasource-juggler/).
## Installation
````sh
npm install loopback-connector-mysql --save
````
## Usage
To use it you need `loopback-datasource-juggler`.
1. Setup dependencies in `package.json`:
```json
{
...
"dependencies": {
"loopback-datasource-juggler": "latest",
"loopback-connector-mysql": "latest"
},
...
}
```
2. Use:
```javascript
var DataSource = require('loopback-datasource-juggler').DataSource;
var dataSource = new DataSource('mysql', {
host: 'localhost',
port: 3306,
database: 'mydb',
username: 'myuser',
password: 'mypass'
});
```
You can optionally pass a few additional parameters supported by [`node-mysql`](https://github.com/felixge/node-mysql),
most particularly `password` and `collation`. `Collation` currently defaults
to `utf8_general_ci`. The `collation` value will also be used to derive the
connection charset.
## Data type mappings
`loopback-connector-mysql` uses the following rules to map between JSON types and MySQL data types.
### JSON to MySQL types
- String/JSON: VARCHAR
- Text: TEXT
- Number: INT
- Date: DATETIME
- BOOLEAN: TINYINT(1)
- Point/GeoPoint: POINT
- Enum: ENUM
### MySQL to JSON types
- CHAR: String
- CHAR(1): Boolean
- VARCHAR/TINYTEXT/MEDIUMTEXT/LONGTEXT/TEXT/ENUM/SET: String
- TINYBLOB/MEDIUMBLOB/LONGBLOB/BLOB/BINARY/VARBINARY/BIT: Binary
- TINYINT/SMALLINT/INT/MEDIUMINT/YEAR/FLOAT/DOUBLE/NUMERIC/DECIMAL: Number
- DATE/TIMESTAMP/DATETIME: Date
## Using the `dataType` field/column option with MySQL
`loopback-connector-mysql` allows mapping of LoopBack model properties to MYSQL columns using the 'mysql' property of the
property definition. For example,
"locationId":{
"type":"String",
"required":true,
"length":20,
"mysql":
{
"columnName":"LOCATION_ID",
"dataType":"VARCHAR2",
"dataLength":20,
"nullable":"N"
}
}
`loopback-connector-mysql` also supports using the `dataType` column/property attribute to specify what MySQL column
type is used for many loopback-datasource-juggler types.
The following type-dataType combinations are supported:
- Number
- integer
- tinyint
- smallint
- mediumint
- int
- bigint
Use the `limit` option to alter the display width.
Example:
`{ count : { type: Number, dataType: 'smallInt' }}`
- floating point types
- float
- double
Use the `precision` and `scale` options to specify custom precision. Default is (16,8).
Example:
`{ average : { type: Number, dataType: 'float', precision: 20, scale: 4 }}`
- fixed-point exact value types
- decimal
- numeric
Use the `precision` and `scale` options to specify custom precision. Default is (9,2).
These aren't likely to function as true fixed-point.
Example:
`{ stdDev : { type: Number, dataType: 'decimal', precision: 12, scale: 8 }}`
- String / DataSource.Text / DataSource.JSON
- varchar
- char
- text
- mediumtext
- tinytext
- longtext
Example:
`{ userName : { type: String, dataType: 'char', limit: 24 }}`
Example:
`{ biography : { type: String, dataType: 'longtext' }}`
- Date
- datetime
- timestamp
Example:
`{ startTime : { type: Date, dataType: 'timestamp' }}`
* Enum
Enums are special.
Create an Enum using Enum factory:
```javascript
var MOOD = dataSource.EnumFactory('glad', 'sad', 'mad');
MOOD.SAD; // 'sad'
MOOD(2); // 'sad'
MOOD('SAD'); // 'sad'
MOOD('sad'); // 'sad'
```
- `{ mood: { type: MOOD }}`
- `{ choice: { type: dataSource.EnumFactory('yes', 'no', 'maybe'), null: false }}`
## Discovering Models
MySQL data sources allow you to discover model definition information from existing mysql databases. See the following APIs:
- [dataSource.discoverModelDefinitions([owner], fn)](https://github.com/strongloop/loopback#datasourcediscovermodeldefinitionsusername-fn)
- [dataSource.discoverSchema([owner], name, fn)](https://github.com/strongloop/loopback#datasourcediscoverschemaowner-name-fn)
### Asynchronous APIs for discovery
* MySQL.prototype.discoverModelDefinitions = function (options, cb)
- options:
- all: {Boolean} To include tables/views from all schemas/owners
- owner/schema: {String} The schema/owner name
- views: {Boolean} To include views
- cb:
- Get a list of table/view names, for example:
{type: 'table', name: 'INVENTORY', owner: 'STRONGLOOP' }
{type: 'table', name: 'LOCATION', owner: 'STRONGLOOP' }
{type: 'view', name: 'INVENTORY_VIEW', owner: 'STRONGLOOP' }
* MySQL.prototype.discoverModelProperties = function (table, options, cb)
- table: {String} The name of a table or view
- options:
- owner/schema: {String} The schema/owner name
- cb:
- Get a list of model property definitions, for example:
{ owner: 'STRONGLOOP',
tableName: 'PRODUCT',
columnName: 'ID',
dataType: 'VARCHAR2',
dataLength: 20,
nullable: 'N',
type: 'String' }
{ owner: 'STRONGLOOP',
tableName: 'PRODUCT',
columnName: 'NAME',
dataType: 'VARCHAR2',
dataLength: 64,
nullable: 'Y',
type: 'String' }
* MySQL.prototype.discoverPrimaryKeys= function(table, options, cb)
- table: {String} The name of a table or view
- options:
- owner/schema: {String} The schema/owner name
- cb:
- Get a list of primary key definitions, for example:
{ owner: 'STRONGLOOP',
tableName: 'INVENTORY',
columnName: 'PRODUCT_ID',
keySeq: 1,
pkName: 'ID_PK' }
{ owner: 'STRONGLOOP',
tableName: 'INVENTORY',
columnName: 'LOCATION_ID',
keySeq: 2,
pkName: 'ID_PK' }
* MySQL.prototype.discoverForeignKeys= function(table, options, cb)
- table: {String} The name of a table or view
- options:
- owner/schema: {String} The schema/owner name
- cb:
- Get a list of foreign key definitions, for example:
{ fkOwner: 'STRONGLOOP',
fkName: 'PRODUCT_FK',
fkTableName: 'INVENTORY',
fkColumnName: 'PRODUCT_ID',
keySeq: 1,
pkOwner: 'STRONGLOOP',
pkName: 'PRODUCT_PK',
pkTableName: 'PRODUCT',
pkColumnName: 'ID' }
* MySQL.prototype.discoverExportedForeignKeys= function(table, options, cb)
- table: {String} The name of a table or view
- options:
- owner/schema: {String} The schema/owner name
- cb:
- Get a list of foreign key definitions that reference the primary key of the given table, for example:
{ fkName: 'PRODUCT_FK',
fkOwner: 'STRONGLOOP',
fkTableName: 'INVENTORY',
fkColumnName: 'PRODUCT_ID',
keySeq: 1,
pkName: 'PRODUCT_PK',
pkOwner: 'STRONGLOOP',
pkTableName: 'PRODUCT',
pkColumnName: 'ID' }
### Discover/build/try the models
#### Build a LDL schema by discovery
Data sources backed by the MySQL connector can discover LDL models from the database using the `discoverSchema` API. For
example,
dataSource.discoverSchema('INVENTORY', {owner: 'STRONGLOOP'}, function (err, schema) {
...
}
Here is the sample result. Please note there are 'mysql' properties in addition to the regular LDL model options and
properties. The 'mysql' objects contain the MySQL specific mappings.
{
"name":"Inventory",
"options":{
"idInjection":false,
"mysql":{
"schema":"STRONGLOOP",
"table":"INVENTORY"
}
},
"properties":{
"productId":{
"type":"String",
"required":false,
"length":60,
"precision":null,
"scale":null,
"id":1,
"mysql":{
"columnName":"PRODUCT_ID",
"dataType":"varchar",
"dataLength":60,
"dataPrecision":null,
"dataScale":null,
"nullable":"NO"
}
},
"locationId":{
"type":"String",
"required":false,
"length":60,
"precision":null,
"scale":null,
"id":2,
"mysql":{
"columnName":"LOCATION_ID",
"dataType":"varchar",
"dataLength":60,
"dataPrecision":null,
"dataScale":null,
"nullable":"NO"
}
},
"available":{
"type":"Number",
"required":false,
"length":null,
"precision":10,
"scale":0,
"mysql":{
"columnName":"AVAILABLE",
"dataType":"int",
"dataLength":null,
"dataPrecision":10,
"dataScale":0,
"nullable":"YES"
}
},
"total":{
"type":"Number",
"required":false,
"length":null,
"precision":10,
"scale":0,
"mysql":{
"columnName":"TOTAL",
"dataType":"int",
"dataLength":null,
"dataPrecision":10,
"dataScale":0,
"nullable":"YES"
}
}
}
}
We can also discover and build model classes in one shot. The following example uses `discoverAndBuildModels` to discover,
build and try the models:
dataSource.discoverAndBuildModels('INVENTORY', { owner: 'STRONGLOOP', visited: {}, associations: true},
function (err, models) {
// Show records from the models
for(var m in models) {
models[m].all(show);
};
// Find one record for inventory
models.Inventory.findOne({}, function(err, inv) {
console.log("\nInventory: ", inv);
// Follow the foreign key to navigate to the product
inv.product(function(err, prod) {
console.log("\nProduct: ", prod);
console.log("\n ------------- ");
});
});
}
Reference in New Issue View Git Blame Copy Permalink