For some time (at least two years now) I’ve been tracking grocery prices on a few items that I buy regularly. I’ve handled this through a spreadsheet in Google Docs, but more and more I’ve been interested in getting this into a database. In conjunction with some things I’ve been looking at for home inventory, I decided to give xataface a try in this category as well. I’ve spent some time with it this weekend, and while I’m a long way from finished, I’ve at least got something that should be simple to enter data from across the board. Additionally, I’m looking at acquiring a couple of barcode scanners next week to further the project 🙂 Here’s what most of my weekend outside of family activities has consisted of.
Install (Linux – already done,) Apache, MySQL, and PHP.
Create a database for the application
create database grocery_price;
Add database user and grant privileges
CREATE USER ‘gprice’@’localhost’ IDENTIFIED BY ‘secretpassword’;
GRANT INSERT, SELECT, DELETE, UPDATE ON grocery_price.* TO ‘gprice’@’localhost’;
Create the tables for storing grocery items and purchases. Each grocery will need to have an ID; it would be ideal to have it be the UPC/EIN(?) code, but not all receipts have that (indeed, not even all products have it… think vegetables), and if items are not scanned as they come in it simply wouldn’t work. Right now about 25% of my spreadsheet entries (103 out of 397) have blank SKU fields, others have store SKUs, and probably the majority have a UPC or partial UPC. Initially creating a table that matches up to entry lines in existing database, maybe with a true/false marker for “SKU is UPC”.
CREATE TABLE grocery_price.entry (
entryID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
createtime DATETIME DEFAULT NULL,
updatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
brand VARCHAR(55),
type VARCHAR(40) COMMENT ‘generic classification for product i.e. juice, cheese, wine’,
subtype VARCHAR(55) COMMENT ‘more descr clsfcation for product i.e. apple, colby, shiraz’,
description VARCHAR(255) COMMENT ‘freeform name of product with more description/keywords’,
count TINYINT UNSIGNED DEFAULT 1 COMMENT ‘count of item purchased; 0 if observed only’,
size FLOAT COMMENT ‘number of ounces, mL, units, count, etc’,
unit VARCHAR(20) COMMENT ‘ounces, mL, lbs, each, etc.’,
price DECIMAL(6,2) COMMENT ‘price per item unless next field – priceisperitem – is 0’,
priceisperitem BIT(1) DEFAULT 1 COMMENT ‘true/set unless the price in the row is for count’,
regularprice DECIMAL(6,2) NULL COMMENT ‘if onsale and regular price recorded’,
store VARCHAR(80),
rcptdesc VARCHAR(80) COMMENT ‘description of product as displayed on receipt’,
sku VARCHAR(40) COMMENT ‘SKU from receipt or preferably UPC w or w/o check digit’,
skuisupc BIT(1) DEFAULT 1 COMMENT ‘1/true if the SKU field is a UPC w or w/o a check digit’,
upc BIGINT,
pricedate DATE COMMENT ‘date on receipt if a purchase, otherwise date price seen’,
location VARCHAR(80),
note VARCHAR(255));
* If the “skuisupc” field is set to 1, should have a stored procedure/function automatically update/insert the “upc” field when the record is first entered – and preferably calculate and add the check digit if it is not present. UPC has 11 digits + check, EAN has 12+1 check, can convert UPC to EAN by adding a 0 at the beginning. This is not finished yet, what is below is just a start on an outline for it. Might just accept what it has if I have marked it as valid for the time being.
1. Check length. If 11 or less, assume UPC without check digit and left pad zeros to a length of 12, run EAN check calculation.
2. If length is 12
DELIMITER $$
CREATE TRIGGER `grocery_price`.`entry_setupc`
BEFORE INSERT ON `grocery_price`.`entry`
FOR EACH ROW
BEGIN
IF NEW.skuisupc = 1 THEN SET NEW.upc = NEW.sku;
END IF;
END $$
DELIMITER ;
* Also need to have a stored procedure/function/trigger automatically set the updatetime field on an ‘UPDATE’ action
DELIMITER $$
CREATE TRIGGER `grocery_price`.`entry_createtime`
BEFORE INSERT ON `grocery_price`.`entry`
FOR EACH ROW
BEGIN
SET NEW.createtime = CURRENT_TIMESTAMP();
END $$
DELIMITER ;
Load CSV data into the file. CSV contains columns for brand, type, subtype, description, size, unit, price, regularprice, store, rcptdesc, sku, skuisupc, pricedate, location, notes.
LOAD DATA INFILE ‘/home/landisv/Documents/grocery.csv’ INTO TABLE `grocery_price`.`entry` FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ (brand, type, subtype, description, size, unit, price, regularprice, store, rcptdesc, sku, skuisupc, pricedate, location, note);
Permitted mysqld access through apparmor as per http://stackoverflow.com/questions/4215231/mysql-load-data-infile-error-code-13.
Installed php5-mysql
apt-get install php5-mysql
Attempted setup for initial application, which failed.
php makesite ../grocery gprice:w1WnmAV4P9QdTT93Gbhk@localhost/grocery_price /xataface
Granted gprice user proper permissions
SHOW GRANTS FOR `gprice`@`localhost`;
GRANT INSERT, SELECT, DELETE, UPDATE ON `grocery_price`.* TO `gprice`@`localhost`;
Reran creation command successfully. Attempted to access the web application and received the following message: “As of Xataface 1.3 all applications are now required to have its own templates_c directory to house its compiled templates. Please create the directory “/var/www/grocery/templates_c” and ensure that it is writable by the web server.”
Created the templates_c directory and changed its ownership to the webserver.
cd /var/www/grocery
mkdir templates_c
chown www-data:www-data templates_c/
Getting HTTP 500 errors. Did a little reading, installed php-pear and smarty engine.
apt-get install php-pear smarty
Still returning errors. Viewed apache logs, noted that the database user I was working with (which I had only granted insert, select, update, and delete privileges to) needed create permissions (on a couple of tables as I discovered working through the issue). Granted create permissions to the gprice user as follows.
GRANT CREATE ON `grocery_price`.`dataface__version` TO `gprice`@`localhost`;
GRANT CREATE ON `grocery_price`.`dataface__mtimes` TO `gprice`@`localhost`;
GRANT CREATE ON `grocery_price`.`dataface__preferences` TO `gprice`@`localhost`;
Once the above permissions were granted, I was able to access the database from the web interface. Pretty ugly right now, as there are a couple of fields there’s really no need to see from the user interface (timestamps specifically), but it does work. Also just noted that my rcptdate/pricedate stamps are ugly as sin and broken, so will probably drop the ‘entry’ table and recreate it, fix the CSV in the format expected by MySQL, and re-import it. Sure enough, checking CSV indicates date format is MM/DD/YY, and for the DATE datatype MySQL will be expecting it as YYYY-MM-DD. Ended up being easier to fix the date format in Google Docs and redownload, so I did that. Ran the following to drop and recreate the ‘entry’ database table.
CREATE TABLE grocery_price.entry (
entryID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
createtime DATETIME DEFAULT NULL,
updatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
brand VARCHAR(55),
type VARCHAR(40) COMMENT ‘generic classification for product i.e. juice, cheese, wine’,
subtype VARCHAR(55) COMMENT ‘more descr clsfcation for product i.e. apple, colby, shiraz’,
description VARCHAR(255) COMMENT ‘freeform name of product with more description/keywords’,
count TINYINT UNSIGNED DEFAULT 1 COMMENT ‘count of item purchased; 0 if observed only’,
size FLOAT COMMENT ‘number of ounces, mL, units, count, etc’,
unit VARCHAR(20) COMMENT ‘ounces, mL, lbs, each, etc.’,
price DECIMAL(6,2) COMMENT ‘price per item unless next field – priceisperitem – is 0’,
priceisperitem BIT(1) DEFAULT 1 COMMENT ‘true/set unless the price in the row is for count’,
regularprice DECIMAL(6,2) NULL COMMENT ‘if onsale and regular price recorded’,
store VARCHAR(80),
rcptdesc VARCHAR(80) COMMENT ‘description of product as displayed on receipt’,
sku VARCHAR(40) COMMENT ‘SKU from receipt or preferably UPC w or w/o check digit’,
skuisupc BIT(1) DEFAULT 1 COMMENT ‘1/true if the SKU field is a UPC w or w/o a check digit’,
upc BIGINT,
pricedate DATE COMMENT ‘date on receipt if a purchase, otherwise date price seen’,
location VARCHAR(80),
note VARCHAR(255));
DELIMITER $$
CREATE TRIGGER `grocery_price`.`entry_setupc`
BEFORE INSERT ON `grocery_price`.`entry`
FOR EACH ROW
BEGIN
IF NEW.skuisupc = 1 THEN SET NEW.upc = NEW.sku;
END IF;
END $$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER `grocery_price`.`entry_createtime`
BEFORE INSERT ON `grocery_price`.`entry`
FOR EACH ROW
BEGIN
SET NEW.createtime = CURRENT_TIMESTAMP();
END $$
DELIMITER ;
Received a message at this point that current version of MySQL doesn’t support multiple triggers with the same action. Makes sense, so I revamped those a little and combined them into one, dropped the table, and re-added as follows.
CREATE TABLE grocery_price.entry (
entryID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
createtime DATETIME DEFAULT NULL,
updatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
brand VARCHAR(55),
type VARCHAR(40) COMMENT ‘generic classification for product i.e. juice, cheese, wine’,
subtype VARCHAR(55) COMMENT ‘more descr clsfcation for product i.e. apple, colby, shiraz’,
description VARCHAR(255) COMMENT ‘freeform name of product with more description/keywords’,
count TINYINT UNSIGNED DEFAULT 1 COMMENT ‘count of item purchased; 0 if observed only’,
size FLOAT COMMENT ‘number of ounces, mL, units, count, etc’,
unit VARCHAR(20) COMMENT ‘ounces, mL, lbs, each, etc.’,
price DECIMAL(6,2) COMMENT ‘price per item unless next field – priceisperitem – is 0’,
priceisperitem BIT(1) DEFAULT 1 COMMENT ‘true/set unless the price in the row is for count’,
regularprice DECIMAL(6,2) NULL COMMENT ‘if onsale and regular price recorded’,
store VARCHAR(80),
rcptdesc VARCHAR(80) COMMENT ‘description of product as displayed on receipt’,
sku VARCHAR(40) COMMENT ‘SKU from receipt or preferably UPC w or w/o check digit’,
skuisupc BIT(1) DEFAULT 1 COMMENT ‘1/true if the SKU field is a UPC w or w/o a check digit’,
upc BIGINT,
pricedate DATE COMMENT ‘date on receipt if a purchase, otherwise date price seen’,
location VARCHAR(80),
note VARCHAR(255));
DELIMITER $$
CREATE TRIGGER `grocery_price`.`entry_insert`
BEFORE INSERT ON `grocery_price`.`entry`
FOR EACH ROW
BEGIN
SET NEW.createtime = CURRENT_TIMESTAMP();
IF NEW.skuisupc = 1 THEN SET NEW.upc = NEW.sku;
END $$
DELIMITER ;
LOAD DATA INFILE ‘/home/landisv/Documents/grocery.csv’ INTO TABLE `grocery_price`.`entry` FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ (brand, type, subtype, description, size, unit, price, regularprice, store, rcptdesc, sku, skuisupc, pricedate, location, note);
This worked great. The downside is that the UPC import sucked, and I will probably drop those columns (upc and skuisupc) for the time being until I get around to adding them and the associated triggers as bitmaps. For now I will just hide them.
Created a fields.ini folder (readable/writable by the webserver) in the /var/www/grocery/tables/entry directory with the following contents.
[entryID]
widget:type = “hidden”
[createtime]
widget:type = “hidden”
[updatetime]
widget:type = “hidden”
[skuisupc]
widget:type = “hidden”
[upc]
widget:type = “hidden”
This eliminated the fields in the detail edit view, but they still appear in the detail view itself as well as the list view. Something else to probably work on over time. At this point I tried adding a record by copying, which works fairly well (my test was primarily to check on the create/update time and ID fields to make sure they were unique with the copy). Unfortunately I noticed that my prices all failed the import. I suspect this is due to having a dollar sign in the CSV fields. Since I’ll be redoing the table anyway, will go ahead and drop the ‘skuisupc’ and ‘upc’ fields out of it – and also see how xataface handles still having these fields in the fields.ini file 🙂 Ran the following.
CREATE TABLE grocery_price.entry (
entryID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
createtime DATETIME DEFAULT NULL,
updatetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
brand VARCHAR(55),
type VARCHAR(40) COMMENT ‘generic classification for product i.e. juice, cheese, wine’,
subtype VARCHAR(55) COMMENT ‘more descr clsfcation for product i.e. apple, colby, shiraz’,
description VARCHAR(255) COMMENT ‘freeform name of product with more description/keywords’,
count TINYINT UNSIGNED DEFAULT 1 COMMENT ‘count of item purchased; 0 if observed only’,
size FLOAT COMMENT ‘number of ounces, mL, units, count, etc’,
unit VARCHAR(20) COMMENT ‘ounces, mL, lbs, each, etc.’,
price DECIMAL(6,2) COMMENT ‘price per item unless next field – priceisperitem – is 0’,
priceisperitem BIT(1) DEFAULT 1 COMMENT ‘true/set unless the price in the row is for count’,
regularprice DECIMAL(6,2) NULL COMMENT ‘if onsale and regular price recorded’,
store VARCHAR(80),
rcptdesc VARCHAR(80) COMMENT ‘description of product as displayed on receipt’,
sku VARCHAR(40) COMMENT ‘SKU from receipt or preferably UPC w or w/o check digit’,
pricedate DATE COMMENT ‘date on receipt if a purchase, otherwise date price seen’,
location VARCHAR(80),
note VARCHAR(255));
DELIMITER $$
CREATE TRIGGER `grocery_price`.`entry_insert`
BEFORE INSERT ON `grocery_price`.`entry`
FOR EACH ROW
BEGIN
SET NEW.createtime = CURRENT_TIMESTAMP();
END $$
DELIMITER ;
LOAD DATA INFILE ‘/home/landisv/Documents/grocery.csv’ INTO TABLE `grocery_price`.`entry` FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ (brand, type, subtype, description, size, unit, price, regularprice, store, rcptdesc, sku, pricedate, location, note);
Didn’t observe any fits from the fields.ini file for the now missing fields, and my price information appears to have imported as expected. All 397 of my records appear to have imported properly. I also ran a really quick and dirty SQL injection attack which did not appear successful, so that’s a good indication as well (though a long ways from my forte, so I may not have done it correctly).
Attempted to add a record again and received another HTTP 500, though the record does appear to have been added. Checked apache logs again and noted another table that needed CREATE permissions for the database user, added as follows.
GRANT CREATE ON `grocery_price`.`dataface__record_mtimes` TO `gprice`@`localhost`;
My next attempt to add a record appeared to work without error. There’s still some tuning and visual improvement, as well as perhaps some selection list tie-ins to be added, but I’m pleased with the results for now.
