#__table__
col1 col2
1 9
1 9
1 9
2 8
... ...
#1
select
col1,
col2
From
__table__
group by 1
#2
select
col1,
col2
From
__table__
group by col2
__table__ 에 대해 group by를 했을 때,
group by 1이 어느것을 지칭하는지 헷갈린다.
그래서 확인해보았다.
결론은 동일위치선상에 있는 쿼리문의 select의 대상자를 지칭하는 것이다.
만약 select a, b, c가 있다면, group by 1은 a를 지칭하는 것이다.
이 내용을 정확히 알고 있다면, 상관없지만, 어떤 쿼리는 대상 컬럼이름을 적어주는데 적어놓지 않고 번호로 지칭하는 경우가 종종 있기 때문에 알아두면 유용하다.
아래 처럼 id가 첫번째 컬럼이고, group by 1 로 첫번째이기 때문에 이런 경우에 특히 헷갈린다.
아래와 같이 결과가 나오는 것을 보면 1번째인 id값을 토대로 agg된다.
WITH ex ( id, val) as (
VALUES
(1, 9),
(1, 9),
(1, 9),
(2, 8),
(3, 8),
(4, 8),
(5, 8)
)
select id, count(1) as cnt from ex
group by id
-- group by 1 <<-- id 또는 1을 입력하는 결과는 같다.
-------------
id cnt
3 1
1 3 <<<-----
2 1
4 1
5 1
다른 예제로 두번째 컬럼인 val을 기준으로 하면 아래와 같이 출력된다.
이 내용으로 컬럼의 순서가 아닌 select에 명시된 순서 기준으로 group by 1이 동작하는 것을 알 수 있다.
WITH ex ( id, val) as (
VALUES
(1, 9),
(1, 9),
(1, 9),
(2, 8),
(3, 8),
(4, 8),
(5, 8)
)
select val, count(1) as cnt from ex
group by val
-- group by 1 <<-- val 또는 1을 입력했을 때 동일한 결과가 나온다.
---------------
val cnt
9 3
8 4
이 내용을 통해 group by 1, 2, ... 에 대해 헷갈려서 발생하는 오류는 피하면 좋겠다.
Cradle's API builds right on top of Node's asynch API. Every asynch method takes a callback as its last argument. The return value is an event.EventEmitter, so listeners can also be optionally added.
You can check if a database exists with the exists() method.
db.exists(function(err,exists){if(err){console.log('error',err);}elseif(exists){console.log('the force is with you.');}else{console.log('database does not exists.');db.create();/* populate design documents */}});
If you want to get a specific revision for that document, you can pass it as the 2nd parameter to get().
Cradle is also able to fetch multiple documents if you have a list of ids, just pass an array to get:
db.get(['luke','vader'],function(err,doc){...});
Querying a view
db.view('characters/all',function(err,res){res.forEach(function(row){console.log("%s is on the %s side of the force.",row.name,row.force);});});
You can access the key and value of the response with forEach using two parameters. An optional third parameter will return the id like this example.
db.view('characters/all',function(err,res){res.forEach(function(key,row,id){console.log("%s has view key %s.",row.name,key);});});
To use View Generation Options you can use the view Method with three parameters (viewname, options, callback):
db.view('characters/all',{group:true,reduce:true},function(err,res){res.forEach(function(row){console.log("%s is on the %s side of the force.",row.name,row.force);});});
Querying a row with a specific key
Lets suppose that you have a design document that you've created:
If you want all the cars made by Ford with a model name between Rav4 and later (alphabetically sorted). In CouchDB you could query this view directly by making an HTTP request to:
In the options object you can also optionally specify whether or not to group and reduce the output. In this example reduce must be false since there is no reduce function defined for the cars/byMakeAndModel. With grouping and reducing the options object would look like:
Note that when saving a document this way, CouchDB overwrites the existing document with the new one. If you want to update only certain fields of the document, you have to fetch it first (with get), make your changes, then resave the modified document with the above method.
If you only want to update one or more attributes, and leave the others untouched, you can use the merge()method:
db.merge('luke',{jedi:true},function(err,res){// Luke is now a jedi,// but remains on the dark side of the force.});
Note that we didn't pass a _rev, this only works because we previously saved a full version of 'luke', and thecache option is enabled.
bulk insertion
If you want to insert more than one document at a time, for performance reasons, you can pass an array tosave():
Note: If you must use View Generation Options on your temporary view you can use the three parameter version of the temporaryView() Method - similar to the one described above.
creating validation
when saving a design document, cradle guesses you want to create a view, mention views explicitly to work around this.
db.save('_design/laws',{views:{},validate_doc_update:function(newDoc,oldDoc,usrCtx){if(!/^(light|dark|neutral)$/.test(newDoc.force))throw{error:"invalid value",reason:"force must be dark, light, or neutral"}}}});
removing documents (DELETE)
To remove a document, you call the remove() method, passing the latest document revision.
If remove is called without a revision, and the document was recently fetched from the database, it will attempt to use the cached document's revision, providing caching is enabled.
Or if you want to see changes since a specific sequence number:
db.changes({since:42},function(err,list){...});
The callback will receive the list of changes as an Array. If you want to include the affected documents, simply pass include_docs: true in the options.
Streaming
You can also stream changes, by calling db.changes without the callback. This API uses the excellentfollowlibrary from IrisCouch:
In this case, it returns an instance of follow.Feed, which behaves very similarly to node's EventEmitterAPI. For full documentation on the options available to you when monitoring CouchDB with .changes() see the follow documentation.
Attachments
Cradle supports writing, reading, and removing attachments. The read and write operations can be either buffered or streaming
Writing
You can buffer the entire attachment body and send it all at once as a single request. The callback function will fire after the attachment upload is complete or an error occurs
You can use a read stream to upload the attachment body rather than buffering the entire body first. The callback function will fire after the streaming upload completes or an error occurs
Syntax
vardoc=savedDoc// <some saved couchdb document which has an attachment>varid=doc._idvarrev=doc._revvaridAndRevData={id:id,rev:rev}varattachmentData={name:attachmentName// something like 'foo.txt''Content-Type':attachmentMimeType// something like 'text/plain', 'application/pdf', etc.body:rawAttachmentBody// something like 'foo document body text'}varreadStream=fs.createReadStream('/path/to/file/')varwriteStream=db.saveAttachment(idData,attachmentData,callbackFunction)readStream.pipe(writeStream)
When the streaming upload is complete the callback function will fire
Example Attach a pdf file with the name 'bar.pdf' located at path './data/bar.pdf' to an existing document
varpath=require('path')varfs=require('fs')// this document should already be saved in the couchdb databasevardoc={_id:'fooDocumentID',_rev:'fooDocumentRev'}varidData={id:doc._id,rev:doc._rev}varfilename='bar.pdf'// this is the filename that will be used in couchdb. It can be different from your source filename if desiredvarfilePath=path.join(__dirname,'data','bar.pdf')varreadStream=fs.createReadStream// note that there is no body field here since we are streaming the uploadvarattachmentData={name:'fooAttachment.txt','Content-Type':'text/plain'}db.saveAttachment(idData,attachmentData,function(err,reply){if(err){console.dir(err)return}console.dir(reply)},readStream)
Reading
Buffered
You can buffer the entire attachment and receive it all at once. The callback function will fire after the download is complete or an error occurs. The second parameter in the callback will be the binary data of the attachment
You can stream the attachment as well. If the attachment is large it can be useful to stream it to limit memory consumption. The callback function will fire once the download stream is complete. Note that there is only a single error parameter passed to the callback function. The error is null is no errors occured or an error object if there was an error downloading the attachment. There is no second parameter containing the attachment data like in the buffered read example
Example Say you want to read back an attachment that was saved with the name 'foo.txt'. However the attachment foo.txt is very large so you want to stream it to disk rather than buffer the entire file into memory
vardoc=<somesaveddocumentthathasanattachmentwithname*foo.txt*>varid=doc._idvarattachmentName='foo.txt'vardownloadPath=path.join(__dirname,'foo_download.txt')varwriteStream=fs.createWriteStream(downloadPath)varreadStream=db.getAttachment('piped-attachment','foo.txt',function(err){// note no second reply paramterif(err){console.dir(err)return}console.dir('download completed and written to file on disk at path',downloadPath)})readStream.pipe(writeStream)
Removing
You can remove uploaded attachments with a _id and an attachment name
