1 | ## JugglingDB-MySQL [![Build Status](https://travis-ci.org/jugglingdb/mysql-adapter.png)](https://travis-ci.org/jugglingdb/mysql-adapter)
|
2 |
|
3 | MySQL adapter for JugglingDB.
|
4 |
|
5 | ## Usage
|
6 |
|
7 | To use it you need `jugglingdb@0.2.x`.
|
8 |
|
9 | 1. Setup dependencies in `package.json`:
|
10 |
|
11 | ```json
|
12 | {
|
13 | ...
|
14 | "dependencies": {
|
15 | "jugglingdb": "0.2.x",
|
16 | "jugglingdb-mysql": "latest"
|
17 | },
|
18 | ...
|
19 | }
|
20 | ```
|
21 |
|
22 | 2. Use:
|
23 |
|
24 | ```javascript
|
25 | var Schema = require('jugglingdb').Schema;
|
26 | var schema = new Schema('mysql', {
|
27 | database: 'myapp_test',
|
28 | username: 'root'
|
29 | });
|
30 | ```
|
31 | You can optionally pass a few additional parameters supported by `node-mysql`, most particularly `password` and `collation`. `Collation` currently defaults to `utf8mb4_general_ci`. The `collation` value will also be used to derive the connection charset.
|
32 |
|
33 | ## Running tests
|
34 |
|
35 | npm test
|
36 |
|
37 | ## Using the `dataType` field/column option with MySQL
|
38 |
|
39 | The jugglingdb MySQL adapter now supports using the `dataType` column/property attribute to specify what MySQL column type is used for many jugglingdb types.
|
40 |
|
41 | The following type-dataType combinations are supported:
|
42 | * <h4> Number </h4>
|
43 | * <h5> integer </h5>
|
44 | * tinyint
|
45 | * smallint
|
46 | * mediumint
|
47 | * int
|
48 | * bigint
|
49 |
|
50 | Use the `limit` option to alter the display width.
|
51 |
|
52 | Example:
|
53 | `{ count : { type: Number, dataType: 'smallInt' }}`
|
54 |
|
55 | * <h5> floating point types </h5>
|
56 | * float
|
57 | * double
|
58 |
|
59 | Use the `precision` and `scale` options to specify custom precision. Default is (16,8).
|
60 |
|
61 | Example:
|
62 | `{ average : { type: Number, dataType: 'float', precision: 20, scale: 4 }}`
|
63 |
|
64 | * <h5> fixed-point exact value types </h5>
|
65 | * decimal
|
66 | * numeric
|
67 |
|
68 | Use the `precision` and `scale` options to specify custom precision. Default is (9,2).
|
69 |
|
70 | These aren't likely to function as true fixed-point.
|
71 |
|
72 | Example:
|
73 | `{ stdDev : { type: Number, dataType: 'decimal', precision: 12, scale: 8 }}`
|
74 |
|
75 | * <h4> String / Schema.Text / Schema.JSON </h4>
|
76 | * varchar
|
77 | * char
|
78 | * text
|
79 | * mediumtext
|
80 | * tinytext
|
81 | * longtext
|
82 |
|
83 | Example:
|
84 | `{ userName : { type: String, dataType: 'char', limit: 24 }}`
|
85 |
|
86 | Example:
|
87 | `{ biography : { type: String, dataType: 'longtext' }}`
|
88 |
|
89 | * <h4> Date </h4>
|
90 | * datetime
|
91 | * timestamp
|
92 |
|
93 | Example:
|
94 | `{ startTime : { type: Date, dataType: 'timestamp' }}`
|
95 |
|
96 | * <h4> Enum </h4>
|
97 | Enums are special.
|
98 | Create an Enum using Enum factory:
|
99 |
|
100 | ```javascript
|
101 | var MOOD = schema.EnumFactory('glad', 'sad', 'mad');
|
102 | MOOD.SAD; // 'sad'
|
103 | MOOD(2); // 'sad'
|
104 | MOOD('SAD'); // 'sad'
|
105 | MOOD('sad'); // 'sad'
|
106 | ```
|
107 |
|
108 | * `{ mood: { type: MOOD }}`
|
109 | * `{ choice: { type: schema.EnumFactory('yes', 'no', 'maybe'), null: false }}`
|
110 |
|
111 | ## Using OR and IN operator
|
112 |
|
113 | ### OR
|
114 | Mysql adapter now supports the or functionality. You can add an `or` array object to the where clause to join the arguments in the `or` array with an OR.
|
115 |
|
116 | Example:
|
117 | This example selects all the animals whose name are Penny AND type is either cat OR size is medium
|
118 |
|
119 | ```javascript
|
120 | where : {
|
121 | name : 'Penny',
|
122 | or : [ { type : 'cat'},
|
123 | { size : 'medium'}
|
124 | ]
|
125 | }
|
126 | ```
|
127 |
|
128 | It's important to note that each object in the `or` array is treat as if it was in the "where" clause, thus you can create complex queries like this;
|
129 |
|
130 | Example:
|
131 | The example below selects all large white dogs OR all cats who are either small or black color
|
132 |
|
133 | ```javascript
|
134 | where : {
|
135 | or : [ { type : 'dog', color : 'white', size : 'large'},
|
136 | { type : 'cat', or : [ { size : 'small'},
|
137 | { color : 'black'}
|
138 | ]
|
139 | }
|
140 | ]
|
141 | }
|
142 | ```
|
143 |
|
144 | SQL translation for the above would be:
|
145 |
|
146 | ```sql
|
147 | WHERE (type = 'dog' AND color = 'white' AND size = 'large')
|
148 | OR (type = 'cat' AND (size = 'small' OR color = 'black'))
|
149 | ```
|
150 |
|
151 | ### IN
|
152 |
|
153 | IN operator is pretty straight forward. If you give any columns in the where clause an array, they will be interpreted to be an IN object
|
154 |
|
155 | Example:
|
156 | The example below will look for items that have id 1, 4 or 6
|
157 |
|
158 | ```javascript
|
159 | where : {
|
160 | id : [1,4,6]
|
161 | }
|
162 | ```
|
163 |
|
164 | ## Connection Pooling
|
165 | Mysql adapter uses the pooling provided by the node-mysql module. Simply set `pool` option to true in the connection settings.
|
166 |
|
167 | ### Pool Options
|
168 | Taken from node-mysql module
|
169 |
|
170 | * `waitForConnections`: Determines the pool's action when no connections are available and the limit has been reached. If `true`, the pool will queue the connection request and call it when one becomes available. If `false`, the pool will immediately call back with an error. (Default: `true`)
|
171 | * `connectionLimit`: The maximum number of connections to create at once.(Default: `10`)
|
172 | * `queueLimit`: The maximum number of connection requests the pool will queue before returning an error from `getConnection`. If set to `0`, there is no limit to the number of queued connection requests. (Default: `0`)
|
173 |
|
174 | ## Creating Multi-Column Indexes
|
175 | The mysql adapter supports the declaration of multi-column indexes on models via the the `indexes` option in the 3rd argument to `define`.
|
176 |
|
177 | ```javascript
|
178 | UserData = db.define('UserData', {
|
179 | email: { type: String, null: false, index: true },
|
180 | name: String,
|
181 | bio: Schema.Text,
|
182 | birthDate: Date,
|
183 | pendingPeriod: Number,
|
184 | createdByAdmin: Boolean,
|
185 | } , { indexes: {
|
186 | index0: {
|
187 | columns: 'email, createdByAdmin'
|
188 | }
|
189 | }
|
190 | });
|
191 | ```
|
192 |
|
193 | ## MIT License
|
194 |
|
195 | ```text
|
196 | Copyright (C) 2012 by Anatoliy Chakkaev
|
197 |
|
198 | Permission is hereby granted, free of charge, to any person obtaining a copy
|
199 | of this software and associated documentation files (the "Software"), to deal
|
200 | in the Software without restriction, including without limitation the rights
|
201 | to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
|
202 | copies of the Software, and to permit persons to whom the Software is
|
203 | furnished to do so, subject to the following conditions:
|
204 |
|
205 | The above copyright notice and this permission notice shall be included in
|
206 | all copies or substantial portions of the Software.
|
207 |
|
208 | THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
|
209 | IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
|
210 | FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
|
211 | AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
|
212 | LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
|
213 | OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
|
214 | THE SOFTWARE.
|
215 | ```
|