Skip to content

Latest commit

 

History

History
230 lines (197 loc) · 5.05 KB

EVENTS.md

File metadata and controls

230 lines (197 loc) · 5.05 KB
Raw

Events

A list of mysql statements and the corresponding events generated by the spout.

###Tables

Database "test" with the following tables:

"ATable" with ( `id` int(11) DEFAULT NULL, `salary` int(11) DEFAULT NULL, `name` varchar(32) DEFAULT NULL, `dept` varchar(32) DEFAULT NULL )

"BTable" with ( `id` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL )

"CTable" with ( `id` int(11) DEFAULT NULL, `manager` varchar(100) DEFAULT NULL )

An Insert

mysql> insert into ATable(id, salary, name, dept) values(100001, 50000, 'Emp001', 'Finance');

Event Generated

{
    "databaseName": "test",
    "serverId": 1,
    "dataEvents": [{
        "tableName": "atable",
        "dataEventType": "INSERT",
        "oldData": null,
        "data": [{
            "name": "Emp001",
            "id": 100001,
            "dept": "Finance",
            "salary": 50000
        }]
    }]
}

An Update

mysql> update ATable set salary=60000, dept='Sales' where id=100001;

Event Generated

{
    "databaseName": "test",
    "serverId": 1,
    "dataEvents": [{
        "tableName": "atable",
        "dataEventType": "UPDATE",
        "oldData": [{
            "name": "Emp001",
            "id": 100001,
            "dept": "Finance",
            "salary": 50000
        }],
        "data": [{
            "name": "Emp001",
            "id": 100001,
            "dept": "Sales",
            "salary": 60000
        }]
    }]
}

A Delete

mysql> delete from ATable where id=100001;

Event Generated

{
     "databaseName": "test",
     "serverId": 1,
     "dataEvents": [{
         "tableName": "atable",
         "dataEventType": "DELETE",
         "oldData": null,
         "data": [{
             "name": "Emp001",
             "id": 100001,
             "dept": "Sales",
             "salary": 60000
         }]
     }]
}

A Transaction involving multiple tables

mysql> start transaction;
mysql> insert into ATable(id, salary, name, dept) values(100005, 50000, 'Emp002', 'HR');
mysql> insert into BTable(id, address) values(100005, 'Mars Street - 0007');
mysql> insert into CTable(id, manager) values(100005, 'Abhi');
mysql> commit;

Event Generated

{
    "databaseName": "test",
    "serverId": 1,
    "dataEvents": [{
        "tableName": "atable",
        "dataEventType": "INSERT",
        "oldData": null,
        "data": [{
            "name": "Emp002",
            "id": 100005,
            "dept": "HR",
            "salary": 50000
        }]
    }, {
        "tableName": "btable",
        "dataEventType": "INSERT",
        "oldData": null,
        "data": [{
            "address": "Mars Street - 0007",
            "id": 100005
        }]
    }, {
        "tableName": "ctable",
        "dataEventType": "INSERT",
        "oldData": null,
        "data": [{
            "manager": "Abhi",
            "id": 100005
        }]
    }]
}

Multi Update Across Tables

mysql> UPDATE ATable, BTable, CTable SET ATable.salary=70000, BTable.address='Jupiter Street - 0002',
        CTable.manager='Box' WHERE ATable.id=100005 AND BTable.id=100005 AND CTable.id=100005;

Event Generated

{
    "databaseName": "test",
    "serverId": 1,
    "dataEvents": [{
        "tableName": "btable",
        "dataEventType": "UPDATE",
        "oldData": [{
            "address": "Mars Street - 0007",
            "id": 100005
        }],
        "data": [{
            "address": "Jupiter Street - 0002",
            "id": 100005
        }]
    }, {
        "tableName": "atable",
        "dataEventType": "UPDATE",
        "oldData": [{
            "name": "Emp002",
            "id": 100005,
            "dept": "HR",
            "salary": 50000
        }],
        "data": [{
            "name": "Emp002",
            "id": 100005,
            "dept": "HR",
            "salary": 70000
        }]
    }, {
        "tableName": "ctable",
        "dataEventType": "UPDATE",
        "oldData": [{
            "manager": "Abhi",
            "id": 100005
        }],
        "data": [{
            "manager": "Box",
            "id": 100005
        }]
    }]
}

####Multi Update in the same table

mysql> update ATable set salary=90000 where name='Emp002';

Event Generated

{
    "databaseName": "test",
    "serverId": 1,
    "dataEvents": [{
        "tableName": "atable",
        "dataEventType": "UPDATE",
        "oldData": [{
            "name": "Emp002",
            "id": 100002,
            "dept": "HR",
            "salary": 50000
        }, {
            "name": "Emp002",
            "id": 100005,
            "dept": "HR",
            "salary": 70000
        }],
        "data": [{
            "name": "Emp002",
            "id": 100002,
            "dept": "HR",
            "salary": 90000
        }, {
            "name": "Emp002",
            "id": 100005,
            "dept": "HR",
            "salary": 90000
        }]
    }]
}
Note that oldData[i] corresponds to data[i].