Kali Linux – successor to BackTrack – http://www.kali.org/
Should probably spend some time playing around with Ruby a little bit.
DbVisualizer – looks very handy for quick analysis of databases – http://www.dbvis.com/
I recently posted about a grocery tracking database/application I had built using Xataface. Today I circled back to it for a bit and made a couple of updates to provide some additional functionality and to fix a couple of behaviors I found marginally annoying.
I started out with some reading about delegate classes and how to create them, which was instrumental in creating the changes described below. Essentially in my case, the initial creation of the delegate class was fairly simple. I created a new file owned by the HTTP server user at grocery/tables/entry/entry.php. Initially, the contents of this file were pretty basic:
<?
class tables_entry {
}
?>
One of my goals was to improve the speed of entry from my receipts, and I saw a couple of potential methods for this. I ran across an article about adding a CSV import and added that code into my delegate class (inside the {}’s for the class) and thought it might simplify additions with a lot of common fields – such as the date, store, and location for a particular receipt. Please note that I have not given any thought to the security or sanity checks on this code, and it may be unsafe against injection attacks.
function __import__csv(&$data, $defaultValues=array()){
// build an array of Dataface_Record objects that are to be inserted based
// on the CSV file data.
$records = array();
// first split the CSV file into an array of rows.
$rows = explode("\n", $data);
foreach ( $rows as $row ){
// We iterate through the rows and parse the name, phone number, and email
// addresses to that they can be stored in a Dataface_Record object.
list($brand, $type, $subtype, $description, $count, $size, $unit, $price, $priceisperitem, $regularprice, $store, $rcptdesc, $sku, $pricedate, $location, $note) = explode(',', $row);
$record = new Dataface_Record('entry', array());
// We insert the default values for the record.
$record->setValues($defaultValues);
// Now we add the values from the CSV file.
$record->setValues(
array(
'Brand'=>$brand,
'Type'=>$type,
'Subtype'=>$subtype,
'Description'=>$description,
'Count'=>$count,
'Size'=>$size,
'Unit'=>$unit,
'Price'=>$price,
'Priceisperitem'=>$priceisperitem,
'Regularprice'=>$regularprice,
'Store'=>$store,
'Rcptdesc'=>$rcptdesc,
'Sku'=>$sku,
'Pricedate'=>$pricedate,
'Location'=>$location,
'Note'=>$note
)
);
// Now add the record to the output array.
$records[] = $record;
}
// Now we return the array of records to be imported.
return $records;
}
My thought was that I could pre-fill common fields for a particular shopping trip and save some time. Unfortunately, after trying it I was somewhat disappointed at the amount of time it took me to enter things as comma separated lines, and the code isn’t working quite right as the pre-filled values are the only things that come through. So instead I turned my focus towards improving the process I already use.
Like most people, we make quite a few repeat purchases and generally shop the same few stores. As such, I already have many items in the database and only need to search, copy, and change a field or two (pricedate, perhaps the price or a note) for the new record. This works pretty well, but the field modification when copying a record from a search with multiple items returned bugs me just a little bit – the pre-fill in the field modifications might not match the data from the record you copied, but from another record in the search instead. This doesn’t happen when only one record is returned. So, I added another function to my delegate class to allow me to focus on just one row of a search. Now I just have to click a “Go To” link, and I have a single record that I can copy and know my pre-fill boxes will be right if I accidentally hit a field I don’t need to modify! Here’s the code (this also goes inside the {}’s for the class, but NOT inside the ones for the function I previously defined).
function entryID__renderCell ( &$record ) {
//LV 2013-02-23
//Wrote based on variable info at http://xataface.com/wiki/Creating_a_Dashboard
//and renderCell info at http://xataface.com/documentation/how-to/list_tab
return $record->strval('entryID').'<br>(<a href="'.$ENV.DATAFACE_SITE_HREF.'?-action=list&-table=entry&entryID='.$record->strval('entryID').'">Go To</a>)';
}
This worked slick, and with an initial foray into renderCell under my belt, I was comfortable tackling another one to allow me to quickly display all the records of a particular brand – which I may later extend to a couple of other fields as well. That code is as follows, with the same stipulations on placement as noted above.
function brand__renderCell ( &$record ) {
//LV 2013-02-23
//Wrote based on variable info at http://xataface.com/wiki/Creating_a_Dashboard
//and renderCell info at http://xataface.com/documentation/how-to/list_tab
return $record->strval('brand').'<br>(<a href="'.$ENV.DATAFACE_SITE_HREF.'?-action=list&-table=entry&brand='.$record->strval('brand').'">View All</a>)';
}
I also fixed a couple of fields that were not hidden in my list view previously based upon the information here. In my fields.ini file, I changed “widget:type” to “visibility:list”, and the unnecessary fields disappeared from view, just as I had desired.
Now I just need to find a decent code-formatting/including plugin for WordPress, as the “code” syntax leaves a little to be desired and it would be fantastic if it displayed with the same syntax highlighting I get in Vi 🙂
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.
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));
DELIMITER $$CREATE TRIGGER `grocery_price`.`entry_setupc`BEFORE INSERT ON `grocery_price`.`entry`
FOR EACH ROWBEGIN
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 ROWBEGIN
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.
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.”
cd /var/www/grocerymkdir templates_cchown 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.
DROP TABLE entry;
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 ROWBEGINIF 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 ROWBEGINSET 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 ROWBEGIN
SET NEW.createtime = CURRENT_TIMESTAMP();
IF NEW.skuisupc = 1 THEN SET NEW.upc = NEW.sku;
END IF;
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.
[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 ROWBEGINSET 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).
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.
Something I’ve thought about off and on for some time. Looking primarily at open source software. This post may be updated as I continue to look.
http://sourceforge.net/projects/asset-tracker/ – last updated 5-Apr-2012. Fairly rudimentary interface. Simple.
http://sourceforge.net/projects/homeinv/ – last updated 22-Feb-2011. Interface looks nice. Vulnerable to SQL injection (here) and not being updated any longer (here). Also need to make sure to install php-mysql, php-pear, and php-pager to get it operational.
http://xataface.com – Pretty simple and fairly customisable, just need to build the backend database properly. Not sure about handling of images, but does appear to support BLOB fields, might be able to use with an inline image (stored in the database) or link to an images database in that case. This also looks like a fantastic frontend for the grocery price and location database with a small amount of tweaking.
http://www.vfront.org – similar to above. Doesn’t appear to do pictures.
You are currently browsing the archives for the Database category.
