c2fo / patio Goto Github PK
View Code? Open in Web Editor NEWIdiomatic database toolkit
Home Page: http://c2fo.github.io/patio
License: MIT License
Idiomatic database toolkit
Home Page: http://c2fo.github.io/patio
License: MIT License
Hi,
I'm a new user but I'm already loved your module !! I don't know how to read the object that I received when I'm doing this request
var Product = db.from('Product');
Product.first({name: appName}).then(function(product){
// do something here
});
also I've an error when I want to show the object on the output.
util.log(product);
node.js:201
throw e; // process.nextTick error, or 'error' event on first tick
^
TypeError: Cannot call method 'toString' of null
at Object.log (util.js:443:42)
at [object Object].<anonymous> (/Users/xxxx/Desktop/ProjectTest/libs/database.js:52:18)
at [object Object].<anonymous> (/Users/xxxx/node_modules/comb/lib/promise.js:68:20)
at Array.0 (/Users/xxxx/node_modules/comb/lib/base/functions.js:30:27)
at EventEmitter._tickCallback (node.js:192:40)
I request support for SQLite3 databases.
we can define a filter witin an association at definition time like :
patio.addModel("father").oneToMany("children", {query : {age : {gt : 10}}})
but how can we do it when we use it, ex:
var Father = patio.addModel("father").oneToMany("children")
Father.first().then(function(f){
f.children.filter( {age : {gt : 10} }).then ....
});
this means association call should be able to return a query rather than a promise
Found using nsp.
The transaction() method returns a promise that is only resolved in some error conditions. When the transaction is successfully completed or rolled back )due to a promise error within the callback), this promise is not resolved. (See __transaction, currently at line 592 of lib/database/query.js.)
Hello, I ask myself a question. I do this to get an item from a table and delete it if I must. But I wonder if it removes the only row I select from the table, or rather all the rows in the table?
Thank you
KeywordsList.first({kliDvcToken: DEVICE_ID, kliKeyDesc: key}).then(function(keyword){
if(keyword == null){
if(data[key] == 'on'){
KeywordsList.insert([key, DEVICE_ID]);
util.log(key + ' has been added');
}
} else {
if(data[key] == 'off'){
KeywordsList.remove().then(function(numRowsDeleted){
if(numRowsDeleted > 0){
util.log(key + ' has been deleted');
}
}, function(error){
console.log('ERROR : ' + error);
});
}
}
}, function(error){
console.log('ERROR : ' + error);
});
Hi,
I get an error that bothers me. Here is my code
DB.from('SplashAd')
.filter(
'sport={sport} AND country={country} AND language={language} AND platform_id={platform_id} AND debug={debug} AND expiration_timestamp>{now}',
filter
)
.all()
.then(function (splash_ads) {
done(null, splash_ads);
}, function (err) {
done(err);
});
I often use this code and it still works, but not here to make it work. When I remove the filter function I get all the data. Using the where function or first still get the same problem.
Any idea ?
for example, I have two objects: User and device. device belongs to user:
var User = patio.addModel("user");
var Device = patio.addModel('device');
User.oneToMany('devices', {model:'device', key:'user_id'});
Device.manyToOne('user', {model:'user', key:'user_id'});
now to find devices belonging to user I should do (I have object _user loaded from DB):
Device.filter({user_id : _user.id}.all()....
but that is not right way. at this point I should not care about field names in DB. I should care about values in Object Model. and there I have field user.
so, the right way here should be:
device.filter({user: _user} );
Just attempted a quick cockroach db connect and query. Initially it failed on the Postgres version check. I tweaked that function and queries started working.
Pushed to the cockroach
branch. https://github.com/C2FO/patio/compare/cockroach?expand=1
var patio = require("../patio");
//set camelize = true if you want snakecase database columns as camelcase
patio.camelize = true;
// insecure mode password doesnt matter
const DB = patio.connect("cockroach://root:[email protected]:26257/bank");
// console.log(DB);
const Accounts = patio.addModel("accounts");
// This works...
return DB.fetch("SELECT * FROM accounts").all().chain((data) => {
console.log(data);
}).chain(() => {
console.log("go sync")
return Accounts.sync().chain(() => {
console.log('synced...')
return Accounts.naked().all().chain((state) => {
console.log(state);
})
.chain(process.exit, function (err) {
console.log(err)
process.exit(1);
});
}, function(err) {
console.log(err);
process.exit();
});
});
โ node test.js
[ { id: '1', balance: 1000.5 },
{ id: '2', balance: 2000.5 },
{ id: '3', balance: 222.5 },
{ id: '4', balance: 222.56 } ]
go sync
19.2.2
synced...
[ { id: '1', balance: 1000.5 },
{ id: '2', balance: 2000.5 },
{ id: '3', balance: 222.5 },
{ id: '4', balance: 222.56 } ]
Hello, I've a issue with the having clause. I want to make a sql request like
SELECT * FROM User JOIN KeywordList ON userid=keyuserid GROUP BY userid HAVING COUNT(userid)
How I can do that with your patio module !
Hello,
I can't drop foreign keys previously created with addForeignKey in my schema migration.
Currently toHash()
assumes the column being indexed to be unique.
myModel.toHash("some_unique_key");
However, if the column being indexed is not actually unique you will have an undesired effect of the method overriding your data output
Take this data set for example.
name | gender |
---|---|
Bob | M |
Stan | M |
Rachel | F |
Julia | F |
Fred | M |
Now when you run your data request
person.toHash("gender");
You will get back an object looking something like the following
{
"M": { "name": "Fred", "gender": "M" },
"F": { "name": "Julia", "gender": "F" }
}
This happens because the toHash
method is overriding the index key in the return type after each iteration. I would propose that we make this consistent and instead return a grouped data set result.
{
"M": [
{ "name": "Bob", "gender": "M" }
{ "name": "Stan", "gender": "M" }
{ "name": "Fred", "gender": "M" }
],
"F": [
{ "name": "Rachel", "gender": "F" }
{ "name": "Julia", "gender": "F" }
]
}
Hi I have a table with quite a few rows(14,000,000). I recently found patio and it looks very promising. I did some basic queries on our user database and it seemed fine. When I started doing queries on a very large table something seemed very wrong.
PkgLoad.filter({
time_stamp: {
between: ['2011-01-01 00:00:00', '2011-01-01 0:3:59']
}
}).all(function(row) {
});
This is the query I'm doing. I've checked what is being sent to mysql, and I'm logging all queries. This query only returns 6 rows from this table. If I copy the query that this function is creating, and run it, it completes instantly. It seems to take around 15 seconds to do this query through patio.
I've also tried using first where it uses the LIMIT 1 statement. It still takes forever.
This is the query it produces
SELECT * FROM `pkg_load` WHERE ((`time_stamp` >= '2011-01-01 00:00:00') AND (`time_stamp` <= '2011-01-01 0:4:59'))
If I do a more simple query such as a findById(where I'm returning one result) it works great. I just wanted to make sure I'm not doing anything wrong. Thanks.
The patio examples need to be changed to use the inter-model dependency pattern found at http://blog.dougamartin.com
I've had some troubles trying to write in a nice and readable way whis query:
UPDATE myTable SET myField=1 WHERE foreignId = 123 AND userId NOT IN (1,2,3)
so may I ask a new feature?
as you say { foo: [1,2,3] }
translates to foo IN (1,2,3)
but it seems that { foo: { eq: [1,2,3] } }
does NOT translate to the same code. it also seems that { foo: { in: [1,2,3] } }
does not work, so would it be possible to make eq/neq work with arrays? that would make me happy as I could write
conditions = {
foreingId: 123,
userId: { neq: [1,2,3] }
}
;)
Is it possible to specify what associations to eager load at the query level, as opposed to in the model association definition?
Thanks!
I can see a lot of work is going into the v0.0.8 branch and that postgres support is imminent. Are things stable enough to test? How can we help?
Hi, I want to do a select on a column 'idMessage' which is in 2 tables that have joined, how can I do that ?
var Device = db.from('Device');
var DevicePushIDStateMessage = Device.join('PushID', {idDevice: sql.macAddress}).join('State', {idPush: sql.deviceToken}).join('Message', {idMessage: sql.idMessage});
DevicePushIDStateMessage.first({macAddress: macAddress, idMessage: idMessage}).then(function(object){
if(object){
util.log('not null');
} else {
util.log('null');
}
});
when I made the test, the message 'null' or 'not null' doesn't show.
thanks
Hi,
I want to make this request :
SELECT * FROM Table WHERE identifier='xxxxxxxxxx'
It's possible to make this for my request ?
db.from('Table').all().where('identifier=?', 'xxxxxxxxxx').then(function(doSomething){...}, function(error){...});
Hi, I've got an issue with an asynchronous function where i'm using sql requests.
function get_user_message_list(parameters, array, response)
{
var new_array = [];
for (var i in array) {
//console.log(i);
//console.log(array[i]);
var json = array[i];
json = get_json_message(parameters, json);
console.log(json);
new_array.push(json);
console.log(new_array);
}
console.log("NEW ARRAY :");
console.log(new_array);
response.writeHeader(200, {'Content-Type':'application/json'});
response.end(JSON.stringify(new_array));
}
function get_json_message(parameters, json)
{
console.log("JSON OBJECT :");
console.log(json);
var dataset = db.from(TABLES.USER).join(TABLES.MOVIE_LIST, {MLUserId: sql.URId}).join(TABLES.MOVIE, {MVId: sql.MLMovieId});
dataset.where('MLSeen={seen} AND MVSourceId={movie} AND MVSource={source} AND URId!={user}', {seen: 1, movie: json['content']['movie_id'], source: json['content']['movie_source'], user:parameters.FACEBOOK_ID}).all().then(function(users){
if (users) {
for (var j in users) {
json['content']['users_seen'].push(users[j].URId);
}
}
//console.log(json['content']['users_seen']);
dataset.where('MLSeen={seen} AND MVSourceId={movie} AND MVSource={source} AND URId!={user}', {seen: 0, movie: json['content']['movie_id'], source: json['content']['movie_source'], user:parameters.FACEBOOK_ID}).all().then(function(users){
if (users) {
for (var j in users) {
json['content']['users_not_seen'].push(users[j].URId);
}
}
console.log(json);
}, errorHandler);
}, errorHandler);
}
With this code, I want to wait the sql requests and then push the json to my array and after send the response to the client!
How can I achieve this ?
Patio adds a lot of power via its model layer and the associations.
Currently I do not think there are any docs for the automatically added methods.
The
As well as other magic methods are not documented. It'd be great to add it to the documentation so people browsing the docs knew they were there. They are tested, which is awesome, so it should be a fairly easy add.
Hi,
After few hours of use, I receive this error and need to restart the application.
events.js:71
throw arguments[1]; // Unhandled 'error' event
^
Error: Connection lost: The server closed the connection.
at Protocol.end (/home/ubuntu/servers/pinchmanager-backend/node_modules/patio/node_modules/mysql/lib/protocol/Protocol.js:63:13)
at Socket.onend (stream.js:66:10)
at Socket.EventEmitter.emit (events.js:126:20)
at TCP.onread (net.js:418:51)
What's the problem ?
Does this exist? Can't seem to find it in the source code and seems my models don't get generated correctly when I have a table with these column types(any query hangs). Thanks
I'm having some troubles with multi database support. If I do
model = patio.addModel "foobar"
model.sync().classic etc..
it works without problem, even in the coffee repl:
coffee> model.first().classic (e,r) -> console.log r.id
{ promise: [Function],
chain: [Function],
chainBoth: [Function],
addCallback: [Function],
addErrback: [Function],
then: [Function],
both: [Function],
classic: [Function] }
coffee> 1801
but if I do:
conn = patio.createConnection uri
model = patio.addModel conn.from("foobar")
model.sync().classic etc..
nothing works anymore:
offee> model.first().classic (e,r) -> console.log r.id
{ promise: [Function],
chain: [Function],
chainBoth: [Function],
addCallback: [Function],
addErrback: [Function],
then: [Function],
both: [Function],
classic: [Function] }
coffee>
repl:2
return console.log(r.id);
^
TypeError: Cannot read property 'id' of undefined
at repl:2:23
at spreadArgs (/Users/matteo/.nvf/installed/0.10.29/lib/node_modules/patio/node_modules/
comb/lib/base/functions.js:26:17)
at Object._onImmediate (/Users/matteo/.nvf/installed/0.10.29/lib/node_modules/patio/node
_modules/comb/lib/promise.js:100:25)
at processImmediate [as _immediateCallback] (timers.js:336:15)
I've re-read the doc and I'm pretty sure I'm doing nothing wrong...
Not sure there's a real ask here but maybe just some doc clean up to fix confusion.
The class definition for patio.sql.Json and patio.sql.JsonArray seem to be broken:
However there is a pretty thorough explanation of the json class under patio.sql class definition:
How to mixin an object with a Model
I would like to do :
var MixIn = { method: function(){}}; // or with comb.define
var M = patio.addModel('model', Mixin, {...});
(new M()).method()
But it doesn't work because M must be a patio.Model or here Mixin is just a way to share code
Thanks;
It creates an sql clause like the following
"1" = 1
The column was a text data type.
i.e.
SomeModel
.filter({someColumn: {notIn: []}})
.all();
//also
SomeModel
.exclude({someColumn: []})
.all();
I'm trying to use the associations system to insert data into 3 tables...
projects -----< jobs -----< stages
fk_cols: project job
Here's the code I'm running (I'm generalizing the columns where possible):
const db = patio.createConnection('mysql://site:pwdhash@localhost:3306/my_db');
const Project = patio.addModel('projects').oneToMany('jobs');
const Job = patio.addModel('jobs').manyToOne('projects').oneToMany('stages');
const Stage = patio.addModel('stages').manyToOne('jobs');
Project.save({
name: 'test',
jobs: [{
blockNumber: 'P1300',
quantity: 1,
dimensions: '3x2x1',
material: '1730',
grind: 'FG',
stages: [{
name: 'design',
complete: false
}]
}]
}).chain(console.log, console.log);
The following error is produced:
QueryError : ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails ("my_db"."jobs", CONSTRAINT "jobs_ibfk_1" FOREIGN KEY ("project") REFERENCES "projects" ("id") ON DELETE CASCADE ON UPDATE CASCADE): INSERT INTO "jobs" ("blockNumber", "quantity", "dimensions", "material", "grind") VALUES ('P1300', 1, '3x2x1', '1730', 'FG')
Any ideas on a cause & solution?
Hello:
I had a problem with patio detecting a column's default value, specifically with timestamp type and CURRENT_TIMESTAMP default value.
This is my table definition:
mysql> desc clients;
+----------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | John | |
| lastName | varchar(255) | NO | | NULL | |
| created | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| updated | datetime | YES | | NULL | |
+----------+--------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
And this is the error I was getting
TypeError: Object CURRENT_TIMESTAMP has no method 'match'
[2012-08-01T14:02:07:641 (ART)] ERROR - TypeError: Object CURRENT_TIMESTAMP has no method 'match'
at define.instance.__columnSchemaToJsDefault (/home/mariano/work/dingo/node_modules/patio/lib/database/query.js:752:95)
at defaultFunction (/home/mariano/work/dingo/node_modules/comb/lib/define.js:36:26)
at wrapper [as __columnSchemaToJsDefault] (/home/mariano/work/dingo/node_modules/comb/lib/define.js:49:17)
at define.instance.schema.schemaParseTable.then.hitch.schemas.(anonymous function) (/home/mariano/work/dingo/node_modules/patio/lib/database/query.js:304:48)
at Array.forEach (native)
at define.instance.schema (/home/mariano/work/dingo/node_modules/patio/lib/database/query.js:301:30)
[...]
First of all, I'm quite new to patio so I'm not sure if I'm doing something wrong. However, I could track the problem down to Database.__columnSchemaToJsDefault(def, type). The argument def (column's default value) is supposed to be a string but instead a buffer was being passed.
So I wrote a small patch to treat the column's default value in a similar way to the column's type: https://gist.github.com/3228630
I repeat: I'm new to patio, so any advice is appreciated.
Thanks,
Mariano.
Is there a solution to include associations in a query on a model , like with :include within Activerecord in rails ?
Ex :
var A = patio.addModel('a');
var B = patio.addModel(''b').oneToOne(''a');
B.filter({id: [1,2,3,4,5]}).include(['a']).all(function(b){b.a)})
should produce :
select * from b where id in (1,2,3,4,5)
select * from a where b_id in (b_id1, b_id2, b_id3, ...)
eager load is close to the point but will produce many requests (5 here)
Any existing solution or help to dev it ?
thanks for your very good job
It would be great to have a synchronous add model function. This is something I do on startup(not sure what everyone else does) one time. I can't configure the rest of my application until the add models are done. As of now I'm chaining a couple of callbacks. I could use the sync module for nodejs but I think it makes more sense if it's built in. Thanks!
As the title says. Here's my test code (generalized):
const patio = require('patio');
const db = patio.connect('mysql://lol:no@localhost:3306/my_db?minConnections=1&maxConnections=5');
const car = patio.addModel('cars').oneToMany('wheels');
const wheel = patio.addModel('wheels').manyToOne('cars').oneToMany('tyres');
const tyre = patio.addModel('tyres').manyToOne('wheels');
patio.syncModels().chain(_ => wheel.remove({id: 0}, {load: true}).chain(console.log), console.log);
And the error:
Error: Model error : null is not allowed for the wheelsId column on model tyres
at new patio.ModelError (/my/root/node_modules/patio/lib/errors.js:63:12)
at define.instance._typeCastValue (/my/root/node_modules/patio/lib/model.js:327:27)
at define.instance._setColumnValue (/my/root/node_modules/patio/lib/model.js:304:24)
at .job (/my/root/node_modules/patio/lib/model.js:784:22)
at .<anonymous> (/my/root/node_modules/patio/lib/model.js:219:33)
at Array.forEach (native)
at define.instance.__set (/my/root/node_modules/patio/lib/model.js:215:33)
at /my/root/node_modules/patio/lib/plugins/query.js:226:34
at .<anonymous> (/my/root/node_modules/patio/lib/model.js:698:37)
at resolveOrPromisfyFunction (/my/root/node_modules/patio/lib/utils.js:17:20)
I know it's not necessary to explicitly set load: true
, it's just so you can change it to false
quickly and see that the error goes away.
Trying to use the onUpdate: "cascade" in my Schema CRUD but get the following error:
TypeError: Object [object Object] has no method '__onUpdateClause'
Hi,
var patio = require('patio'),
comb = require('comb');
var dbOptions = {
host:'localhost',
port:'/Applications/MAMP/tmp/mysql/mysql.sock',
type:'mysql',
maxConnections:20,
minConnections:1,
user:'user',
password:'user',
database:'User'
};
var DB = patio.createConnection(dbOptions);
DB.from('table').all().then(function(rows){
// some code here
}, errorHandler);
DB.disconnect().then(function(){
// some code here
}, errorHandler);
Questions :
DB.disconnect()
is executed, we will have now 4 connections ?Thank you for the answers.
It would be helpful for scripting database migrations if 'patio migrate' would return a non-zero value for error conditions. Two error cases that returned a 0 code for me recently:
Some other cases (such as missing some required arguments) are already returning error codes.
model.js -> _typeCastValue
logs a null value for the column name. It does not provide the name of the table where the deletion cascade failed.
I've this (pseudo) code in my model which is called by many concurrent requests to my server:
@filter({ code: json.code }).isEmpty().chain (isEmpty) =>
unless isEmpty
callback "meaningful error message"
return
json = sanitizeData json
create = (db, done) =>
@save(json).classic (err, result) ->
return done err if err
async.each someOtherDataToSave, iterator, done
return
@db.transaction({ isolated: true }, create).classic callback
every time I run the unit test, the first transaction is ok, while the second one seems to end too early, so I've an error because my code tries to call a method on null instead of the saved model. with my fork this doesn't happen.
return xxx.getSomething(lang, function(attrs) {
^
TypeError: Cannot call method 'getSomething' of null
btw looking at the mysql log I've found a (probable) error.
349 Query SELECT 1 FROM `xxx` WHERE (`code` = 'e2317300') LIMIT 1
350 Query SELECT 1 FROM `xxx` WHERE (`code` = 'efcbdcbd') LIMIT 1
351 Query SELECT 1 FROM `xxx` WHERE (`code` = '754e5389') LIMIT 1
352 Query SELECT 1 FROM `xxx` WHERE (`code` = 'bb680f88') LIMIT 1
353 Query SELECT 1 FROM `xxx` WHERE (`code` = '2c71a0d6') LIMIT 1
354 Query BEGIN
354 Query INSERT INTO `xxx` (`createdAt`, `updatedAt`, `code`, etc...
354 Query INSERT INTO `yyy` (`createdAt`, `updatedAt`, etc...
354 Query COMMIT
348 Query BEGIN
355 Query SELECT * FROM `zzz` WHERE...
349 Query SELECT * FROM `yyy` WHERE...
348 Query INSERT INTO `xxx` (`createdAt`, `updatedAt`, `code`, etc...
348 Query SELECT 1 FROM `xxx` WHERE (`code` = 'e85854ec') LIMIT 1
logs ends here ^
As you can see the last select is run inside my transaction (connection id 348), and I suppose this is wrong. this happens with my fork too, so my patch was incomplete too :) but I suppose I didn't catch it because my code ran without errors. Anyway, this should not be the cause of my errors with v0.5.0, but it's still something else that might be needed to look upon.
If I'll be able to understand what goes wrong with v0.5.0 I'll add my info here.
Hi, is not an issue, is much more a request for assistance. I've 8 queries, but they can't be done async. I want to execute them in series and after use all dataset (8) results, how can I achieve that ?
I've also this query, and I want to translate it in patio
select `SADate`, sum(`SAUnits`) as total from Sales WHERE `SAApplicationSku`='210886' and `SATypeIdentifier` in ('1','1T','1F','F1','IA1') and `SAUnits`<0 group by `SADate` order by `SADate` desc
Thank you for your help.
Hi, I've a problem when my server receives multiple requests which leads to a certain transaction executed in parallel (according to the async module definition of "parallel", i.e. a transaction is started before the previous one is completed).
my transaction is done this way (with pseudo-coffeescript):
doStuff = (db, done) =>
@readFromDb userId, =>
@doSomethingElse userId, =>
child = Child.create
userId: userId
@addChild(child).classic done
return
@db.transaction(doStuff).classic callback
the problem is that sometimes the done callback is undefined. moreover looking at mysql query log it seems that the connection pool is used in some strange way, because e.g. I see two insert which should belong to two different transaction coming from the same connection id.
when in my unit test I send the requests to the server with "async.eachSeries" everything works, but if I use "async.each" then the problem arises.
I'm not sure if this is a bug or I simply have dome something wrong in my code, but I can't spot any obvious error in my transaction function...
thanks in advance for your help.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.