Forum: Building VoltDB Applications

Post: Export update/delete

Export update/delete
alexlzl
Mar 9, 2011
Hi,
Would you please explain how update and delete are communicated to the export client for a normal export table? I understand that export-only table only supports inserts.
By looking at the ExportClientBase, it seems only handles row data without any knowledge about the operation (insert/delete/update). I am trying to use Export to act like some kind of "replication" mechanism for our app.
Thank you.
alex
re: Export update/delete
tcallaghan
Mar 11, 2011
Alex,
The export table is a mechanism to make VoltDB data available to external systems. Within your stored procedures you are allowed to insert into them (you can't select/update/delete). When the stored procedure commits the rows in export tables are made available to export clients.
The best way to accomplish what you are describing would be as follows:
1. Create your export table with an additional column, lets call it "sql_operation". So if I had a VoltDB table named "customer" that I wanted to replicated to an external system, I do something like this:
create table customer
(customer_id bigint not null,
customer_name varchar(50),
primary key (customer_id));

create table customer_export
(customer_id bigint not null,
customer_name varchar(50) not null,
sql_operation varchar(10) not null);
2. In your stored procedure, when you are performing operations on customer do a corresponding insert into customer_export:
if you inserted a new customer, "insert into customer_export (customer_id, customer_name, sql_operation) values (<customer_id>, <customer_name>, 'insert');"
if you updated an existing customer, "insert into customer_export (customer_id, customer_name, sql_operation) values (<customer_id>, <new_customer_name>, 'update');"
if you deleted an existing customer, "insert into customer_export (customer_id, customer_name, sql_operation) values (<customer_id>, <customer_name>, 'delete');"
-Tim
export vs export-only?
alexlzl
Mar 14, 2011
Alex,
The export table is a mechanism to make VoltDB data available to external systems. Within your stored procedures you are allowed to insert into them (you can't select/update/delete). When the stored procedure commits the rows in export tables are made available to export clients.
The best way to accomplish what you are describing would be as follows:
1. Create your export table with an additional column, lets call it "sql_operation". So if I had a VoltDB table named "customer" that I wanted to replicated to an external system, I do something like this:
create table customer
(customer_id bigint not null,
customer_name varchar(50),
primary key (customer_id));

create table customer_export
(customer_id bigint not null,
customer_name varchar(50) not null,
sql_operation varchar(10) not null);
2. In your stored procedure, when you are performing operations on customer do a corresponding insert into customer_export:
if you inserted a new customer, "insert into customer_export (customer_id, customer_name, sql_operation) values (<customer_id>, <customer_name>, 'insert');"
if you updated an existing customer, "insert into customer_export (customer_id, customer_name, sql_operation) values (<customer_id>, <new_customer_name>, 'update');"
if you deleted an existing customer, "insert into customer_export (customer_id, customer_name, sql_operation) values (<customer_id>, <customer_name>, 'delete');"
-Tim


Hi Tim,
Seems you answer is specific to "export-only" tables, since you mentioned you can't "select/update/delete" them. It sure works. However, based on your documentation, seems you can also "export" a normal table. Identifying Export Tables ("customer" and "flight" are normal tables, only "reservations_final" is export-only)
What would be the scenarios for normal table used for export? How should I handle update/delete?
Thanks
alex
re: Export update/delete
tcallaghan
Mar 22, 2011
Hi Tim,
Seems you answer is specific to "export-only" tables, since you mentioned you can't "select/update/delete" them. It sure works. However, based on your documentation, seems you can also "export" a normal table.
("customer" and "flight" are normal tables, only "reservations_final" is export-only)
What would be the scenarios for normal table used for export? How should I handle update/delete?
Thanks
alex


Alex,
In release 1.3 we are removing support for "export" tables. This is why my response ignored them (I should have mentioned that in my post).
You can accomplish the same functionality using export only tables, it just requires a little more coding.
-Tim