BitBadger.Documents
Basic Usage

Overview

There are several categories of operations that can be accomplished against documents.

• Count returns the number of documents matching some criteria
• Exists returns true if any documents match the given criteria
• Insert adds a new document, failing if the ID field is not unique
• Save adds a new document, updating an existing one if the ID is already present (“upsert”)
• Update updates an existing document, doing nothing if no documents satisfy the criteria
• Patch updates a portion of an existing document, doing nothing if no documents satisfy the criteria
• Find returns the documents matching some criteria
• RemoveFields removes fields from documents matching some criteria
• Delete removes documents matching some criteria

Insert and Save were the only two that don't mention criteria. For the others, “some criteria” can be defined a few different ways:

• All references all documents in the table; applies to Count and Find
• ById looks for a single document on which to operate; applies to all but Count
• ByFields uses JSON field comparisons to select documents for further processing (PostgreSQL will use a numeric comparison if the field value is numeric, or a string comparison otherwise; SQLite will do its usual best-guess on types); applies to all but Update
• ByContains (PostgreSQL only) uses a JSON containment query (the @> operator) to find documents where the given sub-document occurs (think of this as an = comparison based on one or more properties in the document; looking for hotels with { "Country": "USA", "Rating": 4 } would find all hotels with a rating of 4 in the United States); applies to all but Update
• ByJsonPath (PostgreSQL only) uses a JSON patch match query (the @? operator) to make specific queries against a document's structure (it also supports more operators than a containment query; to find all hotels rated 4 or higher in the United States, we could query for "$ ? (@.Country == \"USA\" && @.Rating > 4)"); applies to all but Update

Finally, Find also has FirstBy* implementations for all supported criteria types, and Find*Ordered implementations to sort the results in the database.

Saving Documents

The library provides three different ways to save data. The first equates to a SQL INSERT statement, and adds a single document to the repository.

// C#, All
    var room = new Room(/* ... */);
    // Parameters are table name and document
    await Document.Insert("room", room);

// F#, All
    let room = { Room.empty with (* ... *) }
    do! insert "room" room

The second is Save; and inserts the data it if does not exist and replaces the document if it does exist (what some call an “upsert”). It utilizes the ON CONFLICT syntax to ensure an atomic statement. Its parameters are the same as those for Insert.

The third equates to a SQL UPDATE statement. Update applies to a full document and is usually used by ID, while Patch is used for partial updates and may be done by field comparison, JSON containment, or JSON Path match. For a few examples, let's begin with a query that may back the “edit hotel” page. This page lets the user update nearly all the details for the hotel, so updating the entire document would be appropriate.

// C#, All
    var hotel = await Document.Find.ById<Hotel>("hotel", hotelId);
    if (!(hotel is null))
    {
        // update hotel properties from the posted form
        await Update.ById("hotel", hotel.Id, hotel);
    }

// F#, All
    match! Find.byId<Hotel> "hotel" hotelId with
    | Some hotel ->
        do! Update.byId "hotel" hotel.Id updated
                { hotel with (* properties from posted form *) }
    | None -> ()

For the next example, suppose we are upgrading our hotel, and need to take rooms 221-240 out of service*. We can utilize a patch via JSON Path** to accomplish this.

// C#, PostgreSQL
    await Patch.ByJsonPath("room",
        "$ ? (@.HotelId == \"abc\" && (@.RoomNumber >= 221 && @.RoomNumber <= 240)",
        new { InService = false });

// F#, PostgreSQL
    do! Patch.byJsonPath "room"
            "$ ? (@.HotelId == \"abc\" && (@.RoomNumber >= 221 && @.RoomNumber <= 240)"
            {| InService = false |};

* - we are ignoring the current reservations, end date, etc. This is very naïve example!

** - Both PostgreSQL and SQLite can also accomplish this using the Between comparison and a ByFields query:

// C#, Both
    await Patch.ByFields("room", FieldMatch.Any, [Field.Between("RoomNumber", 221, 240)],
        new { InService = false });

// F#, Both
    do! Patch.byFields "room" Any [ Field.Between "RoomNumber" 221 240 ] {| InService = false |}

This could also be done with All/FieldMatch.All and GreaterOrEqual and LessOrEqual field comparisons, or even a custom query; these are fully explained in the Advanced Usage section.

There is an Update.ByFunc variant that takes an ID extraction function run against the document instead of its ID. This is detailed in the Advanced Usage section.

Finding Documents

Functions to find documents start with Find.. There are variants to find all documents in a table, find by ID, find by JSON field comparisons, find by JSON containment, or find by JSON Path. The hotel update example above utilizes an ID lookup; the descriptions of JSON containment and JSON Path show examples of the criteria used to retrieve using those techniques.

Find methods and functions are generic; specifying the return type is crucial. Additionally, ById will need the type of the key being passed. In C#, ById and the FirstBy* methods will return TDoc?, with the value if it was found or null if it was not; All and other By* methods return List<TDoc> (from System.Collections.Generic). In F#, byId and the firstBy* functions will return 'TDoc option; all and other by* functions return 'TDoc list.

Find*Ordered methods and function append an ORDER BY clause to the query that will sort the results in the database. These take, as their last parameter, a sequence of Field items; a .Named method allows for field creation for these names. Within these names, prefixing the name with n: will tell PostgreSQL to sort this field numerically rather than alphabetically; it has no effect in SQLite (it does its own type coercion). Adding " DESC" at the end will sort high-to-low instead of low-to-high.

Deleting Documents

Functions to delete documents start with Delete.. Document deletion is supported by ID, JSON field comparison, JSON containment, or JSON Path match. The pattern is the same as for finding or partially updating. (There is no library method provided to delete all documents, though deleting by JSON field comparison where a non-existent field is null would accomplish this.)

Counting Documents

Functions to count documents start with Count.. Documents may be counted by a table in its entirety, by JSON field comparison, by JSON containment, or by JSON Path match. (Counting by ID is an existence check!)

Document Existence

Functions to check for existence start with Exists.. Documents may be checked for existence by ID, JSON field comparison, JSON containment, or JSON Path match.

What / How Cross-Reference

The table below shows which commands are available for each access method. (X = supported for both, P = PostgreSQL only)

Insert, Save, and Update.* operate on single documents.

« BitBadger.Documents Home View Source Repo