Database Archive

Couple of things to note today

Posted September 25, 2013 By Landis V

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/

Be the first to comment

Updates to grocery price application

Posted February 23, 2013 By Landis V

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&nbsp;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 🙂

Be the first to comment

Grocery price tracking

Posted January 6, 2013 By Landis V

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.

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 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 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.

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.

1 Comment. Join the Conversation

(Home) Inventory Software

Posted January 6, 2013 By Landis V

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.

Be the first to comment