PDODocument
Getting Started

Namespace

The base namespace for this library is BitBadger\PDODocument. There are a couple of supporting objects and enumerations, but the vast majority of its functionality is implemented as static objects and functions in this namespace.

Configuring the Connection

As this is based on PDO, the data source names (DSNs) follow the PostgreSQL or SQLite standard formats. When you call Configuration::useDSN() to set this connection, PDODocument will also configure itself to use the SQL syntax required by the driver selected.

Configuration also has static properties $username, $password, and $options, which correspond to the other three parameters to the PDO constructor. The username and password can also be set via the PDO_DOC_USERNAME and PDO_DOC_PASSWORD environment variables - which, if they are present, override anything specified in code.

Configuring ID Fields

Every document must have a unique identifier; by default, the property or array key id will be used for this purpose. To override this, set Configuration::$idField to the desired name.

The library can also generate IDs if they are missing. There are three different types of IDs, specified in the AutoId enumeration:

AutoId::Number generates a “max ID plus 1” query based on the current values of the table.
AutoId::UUID generates a v4 Universally Unique Identifier (UUID) for each document.
AutoId::RandomString uses PHP's random_bytes function and converts them to lowercase hexadecimal. The length of the string defaults to 16 characters, which can be changed by setting the Configuration::$idStringLength property. It will only generate even counts of characters; setting this to 15 would result in a string length of 14.

Ensuring Tables and Indexes Exist

Both PostgreSQL and SQLite store data in tables and can utilize indexes to retrieve that data efficiently. Each application will need to determine the tables and indexes it expects.

Let's use a naive example of a hotel chain to help us think through these concepts. This chain has several hotels, and each hotel has several rooms. While each hotel could have its rooms as part of a Hotel document, there would likely be a lot of contention when concurrent updates for rooms, so we will put rooms in their own table. The hotel will store attributes like name, address, etc.; while each room will have the hotel's ID (named id), along with things like room number, floor, and a list of date ranges where the room is not available. (This could be for customer reservation, maintenance, etc.)

(Note that all of the “ensure” methods/functions below use the IF NOT EXISTS clause; they are safe to run even if the table and indexes already exist.)

PostgreSQL

We have a few options when it comes to indexing our documents. We can index a specific JSON field; each table's primary key is implemented as a unique index on the configured ID field. We can also use a GIN index to index the entire document, and that index can even be optimized for a subset of JSON Path operators.

Let's create a general-purpose index on hotels, a “HotelId” index on rooms, and an optimized document index on rooms.

    Definition::ensureTable('hotel');
    Definition::ensureDocumentIndex('hotel', DocumentIndex::Full);
    Definition::ensureTable('room');
    // parameters are table name, index name, and fields to be indexed    
    Definition::ensureFieldIndex('room', 'hotel_id', ['hotelId']);
    Definition::ensureDocumentIndex('room', DocumentIndex::Optimized);

SQLite

For SQLite, the only option (outside of some quite complex techniques) for JSON indexes are indexes on fields. Just as traditional relational indexes, the order of these fields is significant. In our example, if we indexed our rooms on hotel ID and room number, it could also be used for efficient retrieval just by hotel ID.

Let's create hotel and room tables, then index rooms by hotel ID and room number.

    Definition::ensureTable('hotel');
    Definition::ensureTable('room');
    Definition::ensureFieldIndex('room', 'hotel_and_nbr', ['hotelId', 'roomNumber']);

Now that we have tables, let's use them!

Loading…