Example #1
0
  removeColumn: function (collectionName, attrName) {
    // Escape table name and attribute name
    var tableName = mysql.escapeId(collectionName);
    attrName = mysql.escapeId(attrName);

    return 'ALTER TABLE ' + tableName + ' DROP COLUMN ' + attrName;
  },
Example #2
0
    joins.forEach(function(join) {
      var tableName = join.child.toLowerCase(),
          alias,
          foreignKey,
          parentKey;

      // Build up an escaped alias
      alias = mysql.escapeId(join.alias.toLowerCase() + '_' + tableName);

      // Escape Table Name
      tableName = mysql.escapeId(tableName);

      // Build Full Key Names
      foreignKey = alias + '.' + mysql.escapeId(join.childKey);
      parentKey = mysql.escapeId(join.parent) + '.' + mysql.escapeId(join.parentKey);

      // Check if this is a junctionTable that is being used as the child of another join
      joins.forEach(function(otherJoin) {
        if(otherJoin.child === join.parent) {
          parentKey = mysql.escapeId(otherJoin.alias.toLowerCase() + '_' + otherJoin.child.toLowerCase());
          parentKey += '.' + mysql.escapeId(join.parentKey);
        }
      });

      // Build Join Clause
      queryPart += 'LEFT JOIN ' + tableName + ' AS ' + alias + ' ON ' +
        parentKey + ' = ' + foreignKey + ' ';
    });
Example #3
0
     + p.joins.map(function (v) {
     v[1] = escapeId(v[1]);
     v[3] = escapeId(v[3]);
     v[5] = arEscapeId(v[5]);
     v[7] = arEscapeId(v[7]);
     return v.join(" ");
 }).join((debug ? " \n" : " "))
        }, function(err) {
          if(err) return cb(err);

          var pk = 'id';

          Object.keys(collection.definition).forEach(function(key) {
            if(!collection.definition[key].hasOwnProperty('primaryKey')) return;
            pk = key;
          });

          // If there are no records (`!records.length`)
          // then skip the query altogether- we don't need to look anything up
          if (!records.length){
            return cb(null, []);
          }

          // Build a Query to get newly inserted records
          var query = 'SELECT * FROM ' + mysql.escapeId(tableName) + ' WHERE ' + mysql.escapeId(pk) + ' IN (' + records + ');';

          // Run Query returing results
          if (LOG_QUERIES) {
            console.log('\ncreateEach() :: Executing MySQL query: ',query);
          }
          connection.query(query, function(err, results) {
            if(err) return cb(err);
            cb(null, results);
          });
        });
Example #5
0
        function(callback) {
            // Create the table.
            var query = 'CREATE TABLE IF NOT EXISTS ' + table + ' (\n';
            Object.keys(schema).forEach(function(column, i) {
                if (i > 0) {
                    query += ',\n';
                }
                query += mysql.escapeId(column);
                query += ' ' + typeMap[schema[column]];
            });

            // Define the primary key to prevent duplicates.
            if (primaryKey) {
                if (schema[primaryKey] !== 'number' && schema[primaryKey] !== 'date') {
                    query += ', PRIMARY KEY (' + mysql.escapeId(primaryKey) + ' (100))';
                } else {
                    query += ', PRIMARY KEY (' + mysql.escapeId(primaryKey) + ')';
                }
            }

            query += ')';

            that.job.log.debug(query);
            connection.query(query, function(err, results) {
                callback(err);
            });
        },
Example #6
0
 var _construct_link = function(entity_id, linker, column_name, reason) {
     return "(SELECT t2.ad_id as id, json, "+mysql.escape(reason)+" as reason " +
       "FROM entities as e "+
       "JOIN "+mysql.escapeId(linker)+" as t1 ON e.ad_id = t1.ad_id " +
       "JOIN "+mysql.escapeId(linker)+" as t2 ON t2."+mysql.escapeId(column_name)+"=t1."+mysql.escapeId(column_name)+" AND NOT t2.ad_id IN (SELECT ad_id from entities where entity_id="+mysql.escape(entity_id)+") " +
       "JOIN ads ON t2.ad_id=ads.id " +
       "WHERE e.entity_id="+mysql.escape(entity_id)+")"
 }
Example #7
0
 function arEscapeId(combo) {
     if (Array.isArray(combo)) {
         if (combo.length !== 2) {
             throw new Error("wtf!?");
         }
         return escapeId(combo[0]) + "." + escapeId(combo[1]);
     }
     return escapeId(combo);
 }
Example #8
0
File: base.js Project: maq128/temp
	Pump.dbconn.inertOrDiscard = function(tbl, row) {
console.log('insert', row.product_id, row.status);
		var fields = [];
		var values = [];
		for (var field in row) {
			fields.push(mysql.escapeId(field));
			values.push(':' + field);
		}
		Pump.dbconn.query('INSERT IGNORE INTO ' + mysql.escapeId(tbl) + ' (' + fields.join(',') + ') VALUES(' + values.join(',') + ')', row);
	};
Example #9
0
	stmtCreateJoinTable(p) {
		return `CREATE TABLE IF NOT EXISTS ${mysql.escapeId(`${p.master}_${p.field}`)} (`
			+ ` ${mysql.escapeId(ID)} INT,`
			+ ` FOREIGN KEY (${mysql.escapeId(ID)}) `
			+ ` REFERENCES ${mysql.escapeId(p.master)}(${mysql.escapeId(ID)}),`
			+ ` ${mysql.escapeId(p.field)} INT,`
			+ ` FOREIGN KEY (${mysql.escapeId(p.field)})`
			+ ` REFERENCES ${mysql.escapeId(p.slave)}(${mysql.escapeId(ID)}));`;
	}
Example #10
0
app.post('/update_table/:current_table', function(req, res){
    thisConnection = connection_obj[req.body.socket_id];
    var query = "UPDATE " + mysql.escapeId(req.params.current_table) + "\nSET ";
    query += mysql.escapeId(req.body.columns[parseInt(req.body.column)]) + "=" + thisConnection.escape(req.body.value)
    query += "\nWHERE "
    for(var i in req.body.columns){
        query += mysql.escapeId(req.body.columns[i]) + "=" + thisConnection.escape(req.body.row_vals[i]) + " AND "
    }
    query = query.slice(0, -5);
    thisConnection.query(query, function(err, rows){
        if(err) res.send(err);

        res.send(req.body.value);
    });
});
Example #11
0
File: base.js Project: maq128/temp
	Pump.dbconn._update = function(tbl, upd, criteria) {
console.log('update', JSON.stringify(upd), JSON.stringify(criteria));
		var params = {};
		var sets = [];
		var conds = [];
		for (var field in upd) {
			sets.push(mysql.escapeId(field) + '=:' + field);
			params[field] = upd[field];
		}
		for (var field in criteria) {
			conds.push(mysql.escapeId(field) + '=:' + field);
			params[field] = criteria[field];
		}
		Pump.dbconn.query('UPDATE ' + mysql.escapeId(tbl) + ' SET ' + sets.join(',') + ' WHERE ' + conds.join(' AND '), params);
	};
Example #12
0
  insertQuery: function (collectionName, data) {
    // Escape table name
    var tableName = mysql.escapeId(collectionName);

    // Build query
    return 'INSERT INTO ' + tableName + ' ' + '(' + sql.attributes(collectionName, data) + ')' + ' VALUES (' + sql.values(collectionName, data) + ')';
  },
Example #13
0
      spawnConnection(function(connection, cb) {

        // Escape table name
        var tableName = mysql.escapeId(dbs[collectionName].identity);

        // Build query
        var query = 'DELETE FROM ' + tableName + ' ';

        query += sql.serializeOptions(dbs[collectionName].identity, options);

        // Run query
        connection.query(query, function(err, result) {

          var resultArray = [];

          // Normalize Result Array
          if(Array.isArray(result)) {
            result.forEach(function(value) {
              resultArray.push(value.insertId);
            });

            return cb(null, resultArray);
          }

          resultArray.push(result.insertId);
          cb(err, resultArray);
        });
      }, dbs[collectionName].config, cb);
Example #14
0
      spawnConnection(function(connection, cb) {

        // Escape table name
        var tableName = mysql.escapeId(dbs[collectionName].identity);

        // Find the record before updating it
        var criteria = sql.serializeOptions(dbs[collectionName].identity, options);

        var query = 'SELECT id FROM ' + tableName + ' ' + criteria;

        connection.query(query, function(err, results) {
          if(err) return cb(err);

          var ids = [];

          results.forEach(function(result) {
            ids.push(result.id);
          });

          // Prepare values
          Object.keys(values).forEach(function(value) {
            values[value] = utils.prepareValue(values[value]);
          });

          // Build query
          var query = 'UPDATE ' + tableName + ' SET ' + sql.updateCriteria(dbs[collectionName].identity, values) + ' ';

          query += sql.serializeOptions(dbs[collectionName].identity, options);

          // Run query
          connection.query(query, function(err, result) {
            if (err) return cb(err);

            var criteria;

            if(ids.length === 1) {
              criteria = { where: { id: ids[0] }, limit: 1 };
            } else {
              criteria = { where: { id: ids }};
            }

            // the update was successful, select the updated records
            adapter.find(collectionName, criteria, function(err, models) {
              if (err) return cb(err);

              var values = [];

              // Build a Query Object
              var _query = new Query(dbs[collectionName].definition);

              models.forEach(function(item) {
                values.push(_query.cast(item));
              });

              cb(err, values);
            });
          });

        });
      }, dbs[collectionName].config, cb);
	selectQuery: function (collectionName, options) {
		// Escape table name
		var tableName = mysql.escapeId(collectionName);

		// Build query
		return 'SELECT * FROM ' + tableName + ' ' + sql.serializeOptions(collectionName, options);
	},
Example #16
0
  _schema: function(collectionName, attribute, attrName) {
    attrName = mysql.escapeId(attrName);
    var type = sqlTypeCast(attribute);

    // Process PK field
    if(attribute.primaryKey) {

      // If type is an integer, set auto increment
      if(type === 'INT') {
        return attrName + ' INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY';
      }

      // Just set NOT NULL on other types
      return attrName + ' VARCHAR(255) NOT NULL PRIMARY KEY';
    }

    // Process NOT NULL field.
    // if notNull is true, set NOT NULL constraint
    var nullPart = '';
    if (attribute.notNull) {
      nullPart = ' NOT NULL ';
    }

    // Process UNIQUE field
    if(attribute.unique) {
      return attrName + ' ' + type + nullPart + ' UNIQUE KEY';
    }

    // Process INDEX field (NON-UNIQUE KEY)
    if(attribute.index) {
      return attrName + ' ' + type + nullPart + ', INDEX(' + attrName + ')';
    }

    return attrName + ' ' + type + ' ' + nullPart;
  },
Example #17
0
 Object.keys(schema).forEach(function(column, i) {
     if (i > 0) {
         query += ',\n';
     }
     query += mysql.escapeId(column);
     query += ' ' + typeMap[schema[column]];
 });
Example #18
0
 con.getConnection(function (conErr, connection) {
     if (conErr) {
         return next(conErr);
     }
     var query, columns = '*';
     if (opts && opts.columns) {
         columns = mysql.escapeId(opts.columns);
     }
     try {
         query = andEscape("SELECT " + columns + " FROM " + table + " WHERE ??", attrs);
     } catch (e) {
         return next(e);
     }
     if (opts && opts.order) {
         query += ' ORDER BY ' + mysql.escape(opts.order);
     }
     if (opts && opts.order && opts.desc) {
         query += ' DESC ';
     }
     if (opts && opts.limit) {
         query += ' LIMIT ' + mysql.escape(opts.limit);
     }
     if (opts && opts.limit && opts.offset) {
         query += ' OFFSET ' + mysql.escape(opts.offset);
     }
     connection.query(query, function (err, rows) {
         connection.release();
         next(err, rows);
     });
 });
Example #19
0
 db.dbConnection.query(useridQuery, function(err, results) {
   if (results.length === 0) {
     var queryString = 'INSERT INTO usernames (username) VALUES (?)';
     var queryArgs = [mysql.escapeId(user)];
     postHandler(res, queryString, queryArgs);
   }
 });
Example #20
0
	escapeKey : function(key, options) {
		var rtn = '';

		if (options.useName) {
			if (options.alias && options.alias.length > 0)
				rtn = mysql.escapeId(options.alias) + '.';
			else
				rtn = mysql.escapeId(this.table) + '.';
		}

		if (key instanceof op.Operator)
			return key.getField(this, options);
		else if (key == '*')
			return rtn + key;
		else
			return rtn + mysql.escapeId(key);
	},
Example #21
0
  selectQuery: function (collectionName, options) {
    // Escape table name
    var tableName = mysql.escapeId(collectionName);

    // Build query
    var query = utils.buildSelectStatement(options, collectionName);
    return query += sql.serializeOptions(collectionName, options);
  },
Example #22
0
		sorts.forEach(function(sort){
			var field_name_ = self.getFieldName(sort.orderby);
			if (!field_name_)
				return;
			order_statement_.push(
				// 记得从alias转换为字段名
				mysql.escapeId(field_name_) + " " + (sort.order === "desc" ? "desc" : "asc")
			);
		});
Example #23
0
        spawnConnection(function __DESCRIBE__(connection, cb) {

            var tableName = mysql.escapeId(dbs[collectionName].identity);

            var query = 'DESCRIBE ' + tableName;
            var pkQuery = "SHOW INDEX FROM " + tableName + ";";

            connection.query(query, function __DESCRIBE__(err, schema) {
                if (err) {
                    if (err.code === 'ER_NO_SUCH_TABLE') {
                        return cb();
                    } else return cb(err);
                }

                connection.query(pkQuery, function(err, pkResult) {
                    if (err) return cb(err);

                    // Loop through Schema and attach extra attributes
                    schema.forEach(function(attr) {

                        // Set Primary Key Attribute
                        if (attr.Key === 'PRI') {
                            attr.primaryKey = true;

                            // If also an integer set auto increment attribute
                            if (attr.Type === 'int(11)') {
                                attr.autoIncrement = true;
                            }
                        }

                        // Set Unique Attribute
                        if (attr.Key === 'UNI') {
                            attr.unique = true;
                        }
                    });

                    // Loop Through Indexes and Add Properties
                    pkResult.forEach(function(result) {
                        schema.forEach(function(attr) {
                            if (attr.Field !== result.Column_name) return;
                            attr.indexed = true;
                        });
                    });

                    // Convert mysql format to standard javascript object
                    var normalizedSchema = sql.normalizeSchema(schema);

                    // Set Internal Schema Mapping
                    dbs[collectionName].schema = normalizedSchema;


                    // TODO: check that what was returned actually matches the cache
                    cb(null, normalizedSchema);
                });

            });
        }, dbs[collectionName].config, cb);
Example #24
0
			options.order.forEach(function(element) {
				var str = '';
				var isExpression = element.hasOwnProperty('exp');
				if(isExpression) str += element.exp + '(';
				str += element.hasOwnProperty('col') ? mysql.escapeId(element.col) : '';
				if(isExpression) str += ')';
				str += element.desc ? ' DESC' : ' ASC';
				order.push(str);
			}, this);
Example #25
0
	async put(table, entry, query) {
		const connection = this._connect();
		try {
			await connection.exec(
				`UPDATE ${escapeId(table)} SET ${formSetStmt(entry)}${whereStmt(query)};`
			);
		} finally {
			await connection.end();
		}
	}
Example #26
0
			options.where.forEach(function(element) {
				var str = '';
				if(typeof element == 'string')
					str += this._validateOperator(element);
				else {
					str += mysql.escapeId(element.col);
					str += element.op ? this._validateOperator(element.op) : '=';
					str += mysql.escape(element.val);
				}
				where.push(str);
			}, this);
Example #27
0
	async post(table, entries) {
		const connection = this._connect();
		try {
			const [fields, values] = formFieldsAndValuesStmt(entries);
			await connection.exec(
				`INSERT INTO ${escapeId(table)}(${fields}) VALUES ${values};`
			);
		} finally {
			await connection.end();
		}
	}
Example #28
0
  addColumn: function (collectionName, attrName, attrDef) {
    // Escape table name and attribute name
    var tableName = mysql.escapeId(collectionName);

    // sails.log.verbose("ADDING ",attrName, "with",attrDef);

    // Build column definition
    var columnDefinition = sql._schema(collectionName, attrDef, attrName);

    return 'ALTER TABLE ' + tableName + ' ADD ' + columnDefinition;
  },
Example #29
0
	async delete(table, query) {
		const connection = this._connect();
		try {
			const result = await connection.exec(
				`DELETE FROM ${escapeId(table)}${whereStmt(query)};`
			);
			return result;
		} finally {
			await connection.end();
		}
	}
        connection.connect(function(err) {
            if (err) return ee.emit('error', err);

            connection.query('CREATE DATABASE IF NOT EXISTS ' + mysql.escapeId(db.name), function(err, rows, fields) {
                if (err) return ee.emit('error', err);
                connection.end(function() {
                    ee.emit('done');
                });
            });

        });