Gists for transactions post

insert_po.sql

START TRANSACTION;

/* Create a purchase order row */
INSERT INTO purchase_orders (id,title,total) VALUES (1, ‘purchase order 1’, 10.50);

/* Create a line item, including the foreign key of the purchase order we just created */
INSERT INTO line_items(id,sku,quantity,price,purchase_order_id) VALUES (2, ‘a’, 1, 10.50 1);

COMMIT;

po_document.js

var purchase_order = { 
  _id: 1
  title: ‘Purchase order 1’,
  total: 10.50,
  line_items: [ 
    { sku: ‘a’, quantity: 1, price: 10.50 }
  ]
} 

po_insert.js

db.purchase_orders.save( purchase_order )

po_schema.sql

CREATE TABLE purchase_orders ( 
	id INT NOT NULL,
	title VARCHAR(100),
	total DECIMAL(10,2)
);

CREATE TABLE line_items (
	id INT NOT NULL,
	sku VARCHAR(100),
	quantity INT,
	price DECIMAL(10,2),
	purchase_order_id INT,
	Foreign Key (purchase_order_id) references purchase_orders(id)
);

po_update.js

db.purchase_orders.update( { _id: 1234 }, { 
  $pushAll: { line_items: [
      { sku: ‘c’, quantity: 1, price: 12.34 },
      { sku: ‘d’, quantity: 1, price: 15.25 } 
    ],
  $inc: { total: 27.59 } }
});

update_po.sql

START TRANSACTION;

/* Add some new line item to the PO */
INSERT INTO line_items(id,sku,quantity,purchase_order_id) VALUES (3, ‘b’, 1, 12.34, 1);
INSERT INTO line_items(id,sku,quantity,purchase_order_id) VALUES (4, ‘c’, 1, 15.25, 1);

/* Update the “total” field of the purchase order to reflect the added line items */ 
UPDATE purchase_orders SET total = (total + 12.34 + 15.25) WHERE id = 1;

COMMIT;