transaction logging in JSON, within the MySQL database
Perl Perl 6
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
.gitignore
MANIFEST
MANIFEST.SKIP
Makefile.PL
README
daifuku

README

NAME
    daifuku - transaction logging in JSON, within the MySQL database

SYNOPSIS
        # print stored procedures / triggers for the tables
        daifuku dbname tblname1 tblname2 ... > setup.sql

        # install the procedures and triggers
        mysql -u root dbname < setup.sql

        # to record the log, issue the following sequence of SQL
        BEGIN;                          -- start a transaction
        CALL daifuku_begin('any text'); -- activate logging of the transaction
        ...                             -- issue statements
        COMMIT;                         -- commit the transaction

        # log is stored in the `daifuku_log` table
        SELECT * FROM daifuku_log;

DESCRIPTION
    Daifuku generates stored procedures / triggers for logging the changes
    made through the transactions.

    The log is collected in the daifuku_log table in JSON format, so that it
    would be easy to use the log for whatever purpose (e.g. for audit, undo,
    display, etc.).

INSTALLATION
    The software is packaged as an ordinary Perl module. It can be installed
    by running the following commands.

        % perl Makefile.PL
        % make
        % make install

COMMAND OPTIONS
  --host=host
  --port=port
  --socket=socket-file
    override the default server location specified in "my.cnf"

  --user=username
    username to be used for connecting to the database (default: `root`)

  --pass=password
    password to be used for connecting to the database (default: none)

  --help
    prints help

  --version
    prints the version number

STORED PROCEDURES
  daifuku_begin(info)
    The function instructs the triggers to log the current transaction. Upon
    return, the id of the row within the daifuku_log table in which the
    transaction will be logged is stored in the @daifuku_id user variable.
    Arbitrary text can be passed as an argument, which would be stored in
    the `info` column of the daifuku_log table.

    The function cannot be used outside of a transaction.

  daifuku_is_active()
    Returns 1 if daifuku_begin() has been called for the current
    transaction, or 0 if otherwise.

LOG FORMAT
    The transaction log is stored in `daifuku_log` table which is
    automatically created when installing the triggers. The schema of the
    table looks like:

        CREATE TABLE `daifuku_log` (
          `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
          `info` longtext NOT NULL,
          `action` longtext NOT NULL,
          PRIMARY KEY (`id`)
        ) DEFAULT CHARSET=utf8

    Within the `action` column, log of each transaction is stored as an JSON
    array of an array consisting of four-elements.

    operation
        the first element stores the type of the operation. It is one of:
        "insert", "update", "delete"

    table_name
        the second element stores the name of the modified table

    old_columns
        For "update" and "delete" operations, the third element stores a
        JSON object listing the values of the columns before the operation
        was performed. For "insert" operations, the element is "null".

    new_columns
        For "insert" and "update" operations, the fourth element stores a
        JSON object listing the values of the columns after the operation
        was performed. For "delete" operations, the element is "null".

    In the value-parts of the third and fourth elements, column data are
    stored either as JSON strings or as base64-encoded strings encapsulated
    in single-element JSON arrays.

    For example, the transaction log shown below indicates that a message
    (body is `hello`) is sent from user 33 to user 89, and that the the
    entry in `last_action` table for user 33 has been updated to reflect the
    time of the action.

        [
            [
                "insert",
                "messages",
                {},
                {
                    "id":"199",
                    "from":"33","to":"89",
                    "body":["aGVsbG8="]
                }
            ],
            [
                "update",
                "last_action",
                {
                    "user_id":"33",
                    "at":"2015-03-29 12:35:56"
                },
                {
                    "user_id":"33",
                    "at":"2015-03-31 01:23:45"
                }
            ]
        ]

AUTHOR
    Kazuho Oku

LICENSE
    The software is licensed under the MIT license.