Mailing-List: contact commits-help@couchdb.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@couchdb.apache.org
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
From: Apache Wiki <wikidiffs@apache.org>
To: Apache Wiki <wikidiffs@apache.org>
Date: Tue, 24 Jan 2012 18:59:51 -0000
Message-ID: <20120124185951.80708.45109@eos.apache.org>
Subject: 
 =?utf-8?q?=5BCouchdb_Wiki=5D_Update_of_=22Views=5Ffor=5FSQL=5FJockeys=22_?=
 =?utf-8?q?by_JoanTouzet?=
Auto-Submitted: auto-generated

Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Couchdb Wiki" for c=
hange notification.

The "Views_for_SQL_Jockeys" page has been changed by JoanTouzet:
http://wiki.apache.org/couchdb/Views_for_SQL_Jockeys?action=3Ddiff&rev1=3D4=
&rev2=3D5

Comment:
Removing outdated incomplete content in favour of the superior reference

  <<Include(EditTheWiki)>>
  =

- The latest version is maintained at http://books.couchdb.org/relax/refere=
nce/views-for-sql-jockeys
+ This page now resides athttp://books.couchdb.org/relax/reference/views-fo=
r-sql-jockeys instead.
  =

- =

- =3D View Cookbook for SQL Jockeys =3D
- =

- This is a collection of some common SQL queries and how to get the same r=
esult in CouchDB. The key to remember here is that CouchDB does not work li=
ke an SQL database at all and that best practices from the SQL world do not=
 translate well or at all to CouchDB. This cookbook assumes that you are fa=
miliar with the CouchDB basics like creating and updating databases and doc=
uments.
- =

- =3D=3D Using Views (CREATE / ALTER TABLE) =3D=3D
- =

- Using views is a two step process. First you ''define'' a view, then you =
''query'' it. This is analogous to defining a table structure (with indexes=
) using `CREATE TABLE` or `ALTER TABLE` and querying it using an SQL query.
- =

- =3D=3D=3D Defining a View =3D=3D=3D
- =

- Defining a view is done by creating a special document in a CouchDB datab=
ase. The only actual speciality is the `_id` of the document: it starts wit=
h `_design/`, for example `_design/application`. Other than that, it is jus=
t a regular CouchDB document. To make sure CouchDB understands that you are=
 defining a view, you need to prepare the contents of that design document =
in a special format. Here is an example:
- =

- {{{
- {
-   "_id": "_design/application",
-   "_rev": "1-C1687D17",
-   "views": {
-     "viewname": {
-       "map": "function(doc) { ... }",
-       "reduce": "function(keys, values) { ... }"
-     }
-   }
- }
- }}}
- =

- We are defining a view `viewname`. The definition of the view consists of=
 two functions. The ''map function'' and the ''reduce function''. Specifyin=
g a reduce function is optional. We'll look at the nature of the functions =
later. Note that `viewname` can be whatever you like; `users`, `by-name`, o=
r `by date` are just some examples.
- =

- A single design document can also include multiple view definitions, each=
 identified by a unique name:
- =

- {{{
- {
-   "_id": "_design/application",
-   "_rev": "1-C1687D17",
-   "views": {
-     "viewname": {
-       "map": "function(doc) { ... }",
-       "reduce": "function(keys, values) { ... }"
-     },
-     "anotherview": {
-       "map": "function(doc) { ... }",
-       "reduce": "function(keys, values) { ... }"
-     }
-   }
- }
- }}}
- =

- =3D=3D=3D Querying a View =3D=3D=3D
- =

- The name of the design document and the name of the view are significant =
for querying the view. To query the view `viewname` you perform a HTTP `GET=
` request to the following URI:
- =

- {{{
- /database/_design/application/_view/viewname
- }}}
- =

- `database` is the name of the database you created your design document i=
n. Next up is the design document name and then the view name prefixed with=
 `_view/`. To query `anotherview` replace `viewname` in that URI with `anot=
herview`. If you want to query a view in a different design document adjust=
 the design document name.
- =

- =

- =3D=3D=3D Map & Reduce Functions =3D=3D=3D
- =

- Map/Reduce is a concept that solves problems by applying a two-step proce=
ss; aptly named the ''map'' phase and the 'reduce' phase. The map phase loo=
ks at all documents in CouchDB separately one after the other and creates a=
 ''map result''. The map result is an ordered list of key-value pairs. Both=
 key and value can be specified by the user writing the map function. A map=
 function may call the built-in `emit(key, value)` function 0 to N times pe=
r document, creating a row in the map result per invocation.
- =

- CouchDB is smart enough to only run a map function once for every documen=
t, even on subsequent queries on a view. Only changes to documents, or new =
documents need to be processed anew.
- =

- =3D=3D=3D=3D Map Functions =3D=3D=3D=3D
- =

- Map functions run in isolation for every document. They can't modify the =
document and they can't talk to the outside world; they can't have ''side-e=
ffects''. This is required so CouchDB can guarantee correct results without=
 having to recalculate a complete result when only one document gets change=
d.
- =

- The map result looks like this:
- =

- {{{
- {"total_rows":3,"offset":0,"rows":[
-   {"id":"fc2636bf50556346f1ce46b4bc01fe30","key":"Lena","value":5},
-   {"id":"1fb2449f9b9d4e466dbfa47ebe675063","key":"Lisa","value":4},
-   {"id":"8ede09f6f6aeb35d948485624b28f149","key":"Sarah","value":6}
- }
- }}}
- =

- It is a list of rows sorted by the value of `key`. The `id` is added auto=
matically and refers back to the document that created this row. The `value=
` is the data you're looking for. For example purposes, it's the girl's age.
- =

- The map function that produces this result is:
- =

- {{{
- function(doc) {
-   if(doc.name && doc.age) {
-     emit(doc.name, doc.age);
-   }
- }
- }}}
- =

- It includes a sanity check to see we're operating on the right fields and=
 calls the emit function with the name and age as key and value.
- =

- =3D=3D=3D=3D Reduce Functions =3D=3D=3D=3D
- =

- The reduce functions are explained later.
- =

- =

- =3D=3D Lookup by Key (SELECT field FROM table WHERE value=3D"key") =3D=3D
- =

- Use case: Get a ''result'' (that can be a record or set of records) assoc=
iated with a ''key''.
- =

- To look something up quickly, regardless of the storage mechanism, an ind=
ex is needed. An index is a data structure optimized for quick search and r=
etrieval. CouchDB's map result is stored in such an index, which happens to=
 be a b+-tree.
- =

- To look up a value by `"key"` we need to put all values into the key of a=
 view. All we need is a simple map function:
- =

- {{{
- function(doc) {
-   if(doc.value) {
-     emit(doc.value, null);
-   }
- }
- }}}
- =

- This creates a list of documents that have a `value` field sorted by the =
data in the `value` field. We don't emit a value. The view result will give=
 us a list of document ids that we can then query individually, or we can u=
se the `?include_docs=3Dtrue` view query option to have CouchDB retrieve th=
e document data for us.
- =

- =

- =3D=3D Aggregate Functions (SELECT COUNT(field) FROM table) =3D=3D
- =

- =

- =3D=3D Get Unique Values (SELECT DISTINCT field FROM table) =3D=3D
-=20