github.com/cozy/cozy-stack@v0.0.0-20240603063001-31110fa4cae1/docs/couchdb-quirks.md

# CouchDB Quirks

## Mango indexes

### Exists operator

The
[`$exists` operator](http://docs.couchdb.org/en/stable/api/database/find.html#condition-operators)
can be used with a mango index for the `true` value, but not for the `false`
value. For `false`, a more heavy solution is required:
[a partial index](http://docs.couchdb.org/en/stable/api/database/find.html#find-partial-indexes).

### Index selection

CouchDB may accept or refuse to use a mango index for a query, with obsure
reasons. In general, you can follow these two rules of thumb:

1. An index on the fields `foo, bar, baz` can be used only to fetch documents
   where `foo`, `bar`, and `baz` exist. It means that a query that filters only
   on the value on `foo` won't use the mango index, because it can miss a
   document where `foo` has the expected value but without `bar` or `baz`. If
   you know that all the documents that you want have the `bar` and `baz`
   fields, you can just add two filters `$exists: true` (one for `bar`, the
   other for `baz`).

2. You should use exactly the same sequence of fields for creating the index and
   the `sort` operator of the query. If you have an index on `os, browser, ip`
   for the `io.cozy.sessions.logins`, and you want to have all the documents for
   a login from `windows`, sorted by `browser`, you can use the index, but you
   should use `os, browser, ip` for the sort (or at least `os, browser`, even if
   it is seems to weird to sort on `os` when all the sorted documents will have
   the same value, `windows`). Please note that using `use_index` on a request,
   the results will be sorted by default according to this rule. So, you can
   omit the `sort` operator on the query (except if you want the `descending`
   order).

### Warnings for slow requests

When requesting a mango index, CouchDB can use an index. But there are also
cases where no index can be used, or where the index is not optimal. Let's
see the different scenarios:

- CouchDB doesn't use an index, it will respond with a warning, and cozy-stack
  will transform this warning in an error, as developers should really avoid
  this issue

- CouchDB can use an index for the selector but not for the sort, it will
  respond with an error, and the cozy-stack will just forward the error

- CouchDB can use an index, but will still look at much more documents in
  the index that what will be in the response (it happens with `$or` and `$in`
  operators, which should be avoided), CouchDB 3+ will send a warning and the
  cozy-stack will forward the documents and the warning to the client.

### Comparison of strings

Comparison of strings is done using ICU which implements the Unicode Collation
Algorithm, giving a dictionary sorting of keys. This can give surprising
results if you were expecting ASCII ordering. Note that:

- All symbols sort before numbers and letters (even the “high” symbols like tilde, `0x7e`)
- Differing sequences of letters are compared without regard to case, so `a < aa` but also `A < aa` and `a < AA`
- Identical sequences of letters are compared with regard to case, with lowercase before uppercase, so `a < A`.

## Old revisions

CouchDB keeps for each document a list of its revision (or more exactly a tree
with replication and conflicts).

It's possible to ask the list of the old revisions of a document with
[`GET /db/{docid}?revs_info=true`](http://docs.couchdb.org/en/stable/api/document/common.html#get--db-docid).
It works only if the document has not been deleted. For a deleted document,
[a trick](https://stackoverflow.com/questions/10854883/retrieve-just-deleted-document/10857330#10857330)
is to query the changes feed to know the last revision of the document, and to
recreate the document from this revision.

With an old revision, it's possible to get the content of the document at this
revision with `GET /db/{docid}?rev={rev}` if the database was not compacted. On
CouchDB 2.x, compacts happen automatically on all databases from times to times.

A `purge` operation consists to remove the tombstone for the deleted documents.
It is a manual operation, triggered by a
[`POST /db/_purge`](http://docs.couchdb.org/en/stable/api/database/misc.html).

## Conflicts

It is possible to create a conflict on CouchDB like it does for the replication
by using `new_edits: false`, but it is not well documented to say the least. The
more accurate description was in the old wiki, that [no longer
exists](https://wiki.apache.org/couchdb/HTTP_Bulk_Document_API#Posting_Existing_Revisions).
Here is a copy of what it said:

> The replicator uses a special mode of \_bulk_docs. The documents it writes to
> the destination database already have revision IDs that need to be preserved for
> the two databases to be in sync (otherwise it would not be possible to tell that
> the two represent the same revision.) To prevent the database from assigning
> them new revision IDs, a "new_edits":false property is added to the JSON request
> body.

> Note that this changes the interpretation of the \_rev parameter in each
> document: rather than being the parent revision ID to be matched against, it's
> the existing revision ID that will be saved as-is into the database. And since
> it's important to retain revision history when adding to the database, each
> document body in this mode should have a \_revisions property that lists its
> revision history; the format of this property is described on the HTTP document
> API. For example:

> `curl -X POST -d '{"new_edits":false,"docs":[{"_id":"person","_rev":"2-3595405","_revisions":{"start":2,"ids":["3595405","877727288"]},"name":"jim"}]}' "$OTHER_DB/_bulk_docs"`

> This command will replicate one of the revisions created above, into a
> separate database `OTHER_DB`. It will have the same revision ID as in `DB`,
> `2-3595405`, and it will be known to have a parent revision with ID
> `1-877727288`. (Even though `OTHER_DB` will not have the body of that revision,
> the history will help it detect conflicts in future replications.)

> As with \_all_or_nothing, this mode can create conflicts; in fact, this is
> where the conflicts created by replication come from.
> In short, it's a `PUT /doc/{id}?new_edits=false` with `_rev` the new revision of
> the document, and `_revisions` the parents of this revision in the revisions
> tree of this document.

### Conflict example

Here is an example of a CouchDB conflict.

Let's assume the following document with the revision history `[1-abc, 2-def]`
saved in database:

```
{
  "_id": foo,
  "_rev": 2-def,
  "bar": "tender",
  "_revisions": {
    "ids": [
      "def",
      "abc"
    ]
  }
}
```

The `_revisions` block is returned when passing `revs=true` to the query and
gives all the revision ids, which the revision part after the dash.
For instance, in `2-def`, `2` is called the "generation" and `def` the "id".

We update the document with a `POST /bulk_docs` query, with the following
content:

```
{
	"docs": [
		{
			"_id": "foo",
			"_rev": "3-ghi",
			"_revisions": { "start": 3, "ids": ["ghi", "xyz", "abc"] }
			,
			"bar": "racuda"
		}
	],
	"new_edits": false
}
```

This produces a conflict bewteen `2-def` and `2-xyz`: the former was first saved
in database, but we forced the latter to be a new child of `1-abc`. Hence, this
document will have two revisions branches: `1-abc, 2-def` and `1-abc, 2-xyz, 3-ghi`.

### Sharing

In the [sharing protocol](https://docs.cozy.io/en/cozy-stack/sharing-design/),
we implement this behaviour as we follow the CouchDB replication model. However,
we prevent CouchDB conflicts for files and directories: see [this
explanation](https://docs.cozy.io/en/cozy-stack/sharing-design/#couchdb-conflicts)

## Design docs in \_all_docs

When querying `GET /{db}/_all_docs`, the response include the design docs. It's
quite difficult to filter them, particulary when pagination is involved. We have
added an endpoint `GET /data/:doctype/_normal_docs` to the stack to help client
side applications to deal with this.