Recently online shopping has become especially popular, superseding and leaving far behind its competitors who work in usual shops. It’s quite convenient, online shops are open 24h without breaks and days-off!
Sometimes I buy goods from StrawberryNET.Com for my wife and myself. Unfortunately, there is a huge problem with the seller’s website. Its interface doesn’t provide customers the ability to sort goods by price, discounts, etc. I don’t know if it is made for special reason or it’s just some human laziness, but it’s none of my business. The geek can get everything he wants from the site.
This year I am taking Stanford’s online class launched this fall by Jennifer Widom: Introduction to Databases. I like the course very much and the author of the course as well! Inspired by db-class I implemented the website’s missing sort feature and was able to sort goods as much as I wished.
To implement the feature I used SQL and sed on my Ubuntu Linux 11.10 oneiric ocelot. First of all I parsed the site’s data and received it in this form (data.txt file):
Ermenegildo Zegna - Z Zegna
Z Zegna Deodorant Stick 75g/2.5oz
Price: US$ 18.50 - (Save 8%)
Giorgio Armani - Acqua Di Gio
Acqua Di Gio Deodorant Stick 75g
Price: US$ 29.00
Joop - Joop Thrill
Joop Thrill For Him Deodorant Stick 70g
Price: US$ 13.00 - (Save 50%)
Paco Rabanne - Paco Rabanne Pour Homme
Pour Homme Deodorant Stick 75ml/2.2oz
Price: US$ 21.50 - (Save 2%)
Bvlgari - Aqva Pour Homme Marine
Aqva Pour Homme Marine Deo Stick 75g/2.7oz
Price: US$ 16.50 - (Save 41%)
Ralph Lauren - Polo Blue
Polo Blue Deodorant Stick 75g/2.5oz
Price: US$ 29.00
Hugo Boss - Hugo
Hugo Deodorant Stick 70g/2.4oz
Price: US$ 26.50
Loris Azzaro - Azzaro Elixir
Azzaro Elixir Coffret: Eau De Toilette Spray 100ml/3.3oz + Deodorant Stick 75g/2.5oz 2pcs
Price: US$ 42.50 - (Save 50%)
Then I designed my SQL database:
drop table if exists Strawberry;
create table Strawberry(brand text, series text, title text, price real, save int);
select * from Strawberry;
Using sed I converted the parsed data to SQL insert queries. sed script (sed.sed file):
1~3 s/ - /","/
1~3 s/^/INSERT INTO Strawberry VALUES ("/
1~3 s/$/"/
2~3 s/^/"/
2~3 s/$/"/
3~3 s/Price: US$ //
3~3 s/$/,0);/
3~3 s/ - (Save /,/
3~3 s/%),0//
3~3 s/%)\ ,0//
The script is ready to be executed by this bash command:
sed -f sed.sed data.txt | sed -e ':a;N;$!ba;s/"\n/",/g' | xclip
xclip is a nice tool to output data to directly to OS clipboard.
The data I got using sed script:
INSERT INTO Strawberry VALUES ("Ermenegildo Zegna","Z Zegna","Z Zegna Deodorant Stick 75g/2.5oz",18.50,8);
INSERT INTO Strawberry VALUES ("Giorgio Armani","Acqua Di Gio","Acqua Di Gio Deodorant Stick 75g",29.00,0);
INSERT INTO Strawberry VALUES ("Joop","Joop Thrill","Joop Thrill For Him Deodorant Stick 70g",13.00,50);
INSERT INTO Strawberry VALUES ("Paco Rabanne","Paco Rabanne Pour Homme","Pour Homme Deodorant Stick 75ml/2.2oz",21.50,2);
INSERT INTO Strawberry VALUES ("Bvlgari","Aqva Pour Homme Marine","Aqva Pour Homme Marine Deo Stick 75g/2.7oz",16.50,41);
INSERT INTO Strawberry VALUES ("Ralph Lauren","Polo Blue","Polo Blue Deodorant Stick 75g/2.5oz",29.00,0);
INSERT INTO Strawberry VALUES ("Hugo Boss","Hugo","Hugo Deodorant Stick 70g/2.4oz",26.50,0);
INSERT INTO Strawberry VALUES ("Loris Azzaro","Azzaro Elixir","Azzaro Elixir Coffret: Eau De Toilette Spray 100ml/3.3oz + Deodorant Stick 75g/2.5oz 2pcs",42.50,50);
Using the database I was able to construct as much SQL-queries as I wished, to sort goods of my favorite brands using SELECT, SORT, ORDER and other SQL structures.
The profit I received:
- saved some money buying the goods
- saved some time (it’s easier to manage DB than to use that site)
- the most important: put into practice the knowledge I gained after listening the db-class Stanford course!
Many thanks to Stanford Engineering and Prof. Jennifer Widom.
