Download the PHP package survos/pixie-bundle without Composer

Pixie Bundle

A Symfony bundle that leverages Sqlite to create an indexed no-sql datastore.

Inspired by https://gist.github.com/sbrl/c3bfbbbb3d1419332e9ece1bac8bb71c and https://dexie.org/ and the Symfony PDO Cache component.

At its core, the idea is to store strings or unstructured data accessible by a key or a filter.

Initially, it was just a string lookup, which could be a JSON string, for example looking up a wikidata object by its QID, or a movie from a csv file by its imdb_id.

You can customize the import process by adding a .conf file with directives that facilitate renaming fields and munging data. You can also listen for events during the import and export process.

Pixie can (will, eventually) use json schema files to defining the fields. https://json-schema.org/ using LiFormBundle

The indexes can be defined via the command line, a listener, or a .conf file. There's a succinct format that is a comma-separated string, and a detailed format with is a hash with the index details as keys.

There is (will be) an API endpoint if api-platform is installed.

Integration with survos/translation-bundle

To import a collection of csv files in a directory to a Pixie database, we'll need to do the following

• each csv file becomes a table, which we often rename
• rename the columns
• map data to specific columns, by default simply extract the json
• allow arrays and objects to be property encoded, e.g. genre| or tags,
• add columns that aren't present in the csv, e.g. status or createdAt
• tweak the data, including casting to type and applying regex rules
• @todo: map related tables, e.g. artwork.artist -> artist table
• create indexes for filter queries
• meili index the pixie file

Pixie-specific naming

When creating the table, we add a json snippet after the property creation to indicate some attributes. To support related tables and translation, there are some specific columns names we use that are (maybe?) prefixed by _ or __:

Or should we do this?

• _key: the primary key of a table that can be used by a related table
• _attributes: the JSON blob of everything that's not a property
• _label: the label that is used when a relation is displayed in another table. Translatable. Definable
• _description: eventually we'll support defining translatable properties but for now we just support _description

Events / Data Adjustments

During the import, columns can be renamed with the rules key, and data can be tweaked with the formatters key.

Additionally, a RowEvent is called for before and after every row is inserted into the StorageBox, as well as before and after the import process. This allows for creating new fields or tables or cleaning up data that needs services.

Workflows

Manipulating data that involves web services can be done with workflows, so events can be dispatched async. For example, an 'enhance' event can use the facebook id above to get the latest events, or the latest instagram post.

and then

Setup

All pixie db files have an associated configuration file that describes the mapping and underlying data structure. Generally it shares the same base filename.

Loading a Pixie

As the data directories (where the csv/json files are located) can be anywhere, there are a few way to indicate them.

There are 3 way to load data into pixie.

"Special" Tables

Since pixie databases offer a convenient way to work with Excel data, there is a special table for handling drawings (embedded images). It is created with bin/console grid:excel-to-csv (in the grid-group bundle? In museado?)

Excel stores embedded images as "Drawings".

Translations are also stored as pixie tables, and have their own section.

Examples

• Movies (imdb)
• Schools

CSV Datasets

• https://www.stats.govt.nz/large-datasets/csv-files-for-download/
• https://www.stats.govt.nz/assets/Uploads/Research-and-development-survey/Research-and-development-survey-2021/Download-data/Research-and-development-survey-2021-CSV-notes.csv

Reading an Existing Pixie

The conf file simplifies some of the php calls, but isn't 100% necessary

Suppose we want to filter by category. First, we need to add an index, dexie-style, to the table.

Often CSV files and other data sources have key names that aren't really compatible with sqlite column names. To map the old names to the new ones, you can create a sequence of regex rules for the column names.

From MOMA Artists.json

All fields will be converted to camel_case, even without regex rules.

Note that the JSON is NOT compressed, so the keys are duplicated. So the sqlite file is larger than the CSV by (rowCount * headerRowSize).

Usage

Initialize StorageBox with the sqlite database name plus any new tables to be created.

Existing tables can be used, but automatic table creation is disabled.

Multi-lingual StorageBox

Specifically for kv lookups of text fields that may have translations

Credits

Debug icon from https://www.svgrepo.com/svg/11690/database

Backups

For some reason, the .wal file stays open. We need a utility that does the following

• execute the query "vacuum into 'xx.backup.db'"
• zip the backup
• move the backup to s3 or bunny

To restore, do the opposite

• Fetch the zipped backup
• optionally rename the existing database
• remove .wal, etc.
• unzip

S3 example

aws s3 sync . s3://voxitour/data/pixie --acl public-read --exclude="" --include="met.zip" aws s3 sync s3://voxitour/data/pixie . --acl public-read --exclude="" --include="met.zip"

aws s3 sync . s3://voxitour/data/pixie --acl public-read --exclude="" --include="x.pixie.db" aws s3 sync s3://voxitour/data/pixie . --acl public-read --exclude="" --include="x.pixie.db"

aws s3 sync s3://voxitour/data/pixie . --acl public-read --exclude="" --include=".zip"