{"id":1043,"date":"2011-11-26T02:16:01","date_gmt":"2011-11-25T22:16:01","guid":{"rendered":"http:\/\/sandalov.org\/blog\/?p=1043"},"modified":"2012-01-18T10:30:42","modified_gmt":"2012-01-18T06:30:42","slug":"how-do-geeks-shop-inspired-by-stanford-db-class","status":"publish","type":"post","link":"https:\/\/sandalov.org\/blog\/1043\/","title":{"rendered":"How do geeks shop? (Inspired by Stanford DB-Class)"},"content":{"rendered":"<div class=\"2d836d85a73757df15d0de19d7bef594\" data-index=\"1\" style=\"float: none; margin:10px 0 10px 0; text-align:center;\">\n<script type=\"text\/javascript\"><!--\r\ngoogle_ad_client = \"ca-pub-2287684400845931\";\r\n\/* valerymorozov *\/\r\ngoogle_ad_slot = \"5606708872\";\r\ngoogle_ad_width = 728;\r\ngoogle_ad_height = 90;\r\n\/\/-->\r\n<\/script>\r\n<script type=\"text\/javascript\"\r\nsrc=\"http:\/\/pagead2.googlesyndication.com\/pagead\/show_ads.js\">\r\n<\/script>\n<\/div>\n<p>Recently online shopping has become especially popular, superseding and leaving far behind its competitors who work in usual shops. It\u2019s quite convenient, online shops are open 24h without breaks and days-off!<\/p>\n<p>Sometimes I buy goods from <a href=\"http:\/\/strawberrynet.com\/\" target=\"_blank\">StrawberryNET.Com<\/a> for my wife and myself. Unfortunately, there is a huge problem with the seller\u2019s website. Its interface doesn\u2019t provide customers the ability to sort goods by price, discounts, etc. I don\u2019t know if it is made for special reason or it\u2019s just some human laziness, but it\u2019s none of my business. The geek can get everything he wants from the site.<\/p>\n<p><a href=\"http:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003154.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003154-300x168.png\" alt=\"\" title=\"Screenshot-at-2011-11-26-003154\" width=\"300\" height=\"168\" class=\"aligncenter size-medium wp-image-1050\" srcset=\"https:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003154-300x168.png 300w, https:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003154-1024x575.png 1024w, https:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003154.png 1067w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>This year I am taking <a href=\"http:\/\/soe.stanford.edu\/\" target=\"_blank\">Stanford\u2019s<\/a> online class launched this fall by Jennifer Widom: <a href=\"http:\/\/www.db-class.org\/\" target=\"_blank\">Introduction to Databases<\/a>. I like the course very much and the author of the course as well! Inspired by db-class I implemented the website\u2019s missing sort feature and was able to sort goods as much as I wished.<\/p>\n<p><a href=\"http:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003218.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003218-300x168.png\" alt=\"\" title=\"Screenshot-at-2011-11-26-003218\" width=\"300\" height=\"168\" class=\"aligncenter size-medium wp-image-1051\" srcset=\"https:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003218-300x168.png 300w, https:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003218-1024x575.png 1024w, https:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003218.png 1067w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>To implement the feature I used SQL and sed on my Ubuntu Linux 11.10 oneiric ocelot. First of all I parsed the site\u2019s data and received it in this form (data.txt file):<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\nErmenegildo Zegna - Z Zegna\r\nZ Zegna Deodorant Stick 75g\/2.5oz\r\nPrice: US$ 18.50 - (Save 8%)\r\nGiorgio Armani - Acqua Di Gio\r\nAcqua Di Gio Deodorant Stick 75g\r\nPrice: US$ 29.00\r\nJoop - Joop Thrill\r\nJoop Thrill For Him Deodorant Stick 70g\r\nPrice: US$ 13.00 - (Save 50%)\r\nPaco Rabanne - Paco Rabanne Pour Homme\r\nPour Homme Deodorant Stick 75ml\/2.2oz\r\nPrice: US$ 21.50 - (Save 2%)\r\nBvlgari - Aqva Pour Homme Marine\r\nAqva Pour Homme Marine Deo Stick 75g\/2.7oz\r\nPrice: US$ 16.50 - (Save 41%)\r\nRalph Lauren - Polo Blue\r\nPolo Blue Deodorant Stick 75g\/2.5oz\r\nPrice: US$ 29.00\r\nHugo Boss - Hugo\r\nHugo Deodorant Stick 70g\/2.4oz\r\nPrice: US$ 26.50\r\nLoris Azzaro - Azzaro Elixir\r\nAzzaro Elixir Coffret: Eau De Toilette Spray 100ml\/3.3oz + Deodorant Stick 75g\/2.5oz 2pcs\r\nPrice: US$ 42.50 - (Save 50%)\r\n<\/pre>\n<p>Then I designed my SQL database:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\ndrop table if exists Strawberry;\r\ncreate table Strawberry(brand text, series text, title text, price real, save int);\r\nselect * from Strawberry;\r\n<\/pre>\n<p>Using sed I converted the parsed data to SQL insert queries. sed script (sed.sed file):<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\n1~3 s\/ - \/&amp;amp;amp;quot;,&amp;amp;amp;quot;\/\r\n1~3 s\/^\/INSERT INTO Strawberry VALUES (&amp;amp;amp;quot;\/\r\n1~3 s\/$\/&amp;amp;amp;quot;\/\r\n2~3 s\/^\/&amp;amp;amp;quot;\/\r\n2~3 s\/$\/&amp;amp;amp;quot;\/\r\n3~3 s\/Price: US$ \/\/\r\n3~3 s\/$\/,0);\/\r\n3~3 s\/ - (Save \/,\/\r\n3~3 s\/%),0\/\/\r\n3~3 s\/%)\\ ,0\/\/\r\n<\/pre>\n<p>The script is ready to be executed by this bash command:<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">\r\nsed -f sed.sed data.txt | sed -e &#039;:a;N;$!ba;s\/&amp;amp;amp;quot;\\n\/&amp;amp;amp;quot;,\/g&#039; | xclip\r\n<\/pre>\n<p>xclip is a nice tool to output data to directly to OS clipboard.<\/p>\n<p>The data I got using sed script:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nINSERT INTO Strawberry VALUES (&amp;amp;amp;quot;Ermenegildo Zegna&amp;amp;amp;quot;,&amp;amp;amp;quot;Z Zegna&amp;amp;amp;quot;,&amp;amp;amp;quot;Z Zegna Deodorant Stick 75g\/2.5oz&amp;amp;amp;quot;,18.50,8);\r\nINSERT INTO Strawberry VALUES (&amp;amp;amp;quot;Giorgio Armani&amp;amp;amp;quot;,&amp;amp;amp;quot;Acqua Di Gio&amp;amp;amp;quot;,&amp;amp;amp;quot;Acqua Di Gio Deodorant Stick 75g&amp;amp;amp;quot;,29.00,0);\r\nINSERT INTO Strawberry VALUES (&amp;amp;amp;quot;Joop&amp;amp;amp;quot;,&amp;amp;amp;quot;Joop Thrill&amp;amp;amp;quot;,&amp;amp;amp;quot;Joop Thrill For Him Deodorant Stick 70g&amp;amp;amp;quot;,13.00,50);\r\nINSERT INTO Strawberry VALUES (&amp;amp;amp;quot;Paco Rabanne&amp;amp;amp;quot;,&amp;amp;amp;quot;Paco Rabanne Pour Homme&amp;amp;amp;quot;,&amp;amp;amp;quot;Pour Homme Deodorant Stick 75ml\/2.2oz&amp;amp;amp;quot;,21.50,2);\r\nINSERT INTO Strawberry VALUES (&amp;amp;amp;quot;Bvlgari&amp;amp;amp;quot;,&amp;amp;amp;quot;Aqva Pour Homme Marine&amp;amp;amp;quot;,&amp;amp;amp;quot;Aqva Pour Homme Marine Deo Stick 75g\/2.7oz&amp;amp;amp;quot;,16.50,41);\r\nINSERT INTO Strawberry VALUES (&amp;amp;amp;quot;Ralph Lauren&amp;amp;amp;quot;,&amp;amp;amp;quot;Polo Blue&amp;amp;amp;quot;,&amp;amp;amp;quot;Polo Blue Deodorant Stick 75g\/2.5oz&amp;amp;amp;quot;,29.00,0);\r\nINSERT INTO Strawberry VALUES (&amp;amp;amp;quot;Hugo Boss&amp;amp;amp;quot;,&amp;amp;amp;quot;Hugo&amp;amp;amp;quot;,&amp;amp;amp;quot;Hugo Deodorant Stick 70g\/2.4oz&amp;amp;amp;quot;,26.50,0);\r\nINSERT INTO Strawberry VALUES (&amp;amp;amp;quot;Loris Azzaro&amp;amp;amp;quot;,&amp;amp;amp;quot;Azzaro Elixir&amp;amp;amp;quot;,&amp;amp;amp;quot;Azzaro Elixir Coffret: Eau De Toilette Spray 100ml\/3.3oz + Deodorant Stick 75g\/2.5oz 2pcs&amp;amp;amp;quot;,42.50,50);\r\n<\/pre>\n<p><a href=\"http:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003301.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003301-300x168.png\" alt=\"\" title=\"Screenshot-at-2011-11-26-003301\" width=\"300\" height=\"168\" class=\"aligncenter size-medium wp-image-1052\" srcset=\"https:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003301-300x168.png 300w, https:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003301-1024x575.png 1024w, https:\/\/sandalov.org\/blog\/wp-content\/uploads\/2011\/11\/Screenshot-at-2011-11-26-003301.png 1067w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><\/p>\n<p>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.<\/p>\n<p>The profit I received:<\/p>\n<ul>\n<li>saved some money buying the goods<\/li>\n<li>saved some time (it\u2019s easier to manage DB than to use that site)<\/li>\n<li>the most important: put into practice the knowledge I gained after listening the db-class Stanford course!<\/li>\n<\/ul>\n<p>Many thanks to Stanford Engineering and Prof. Jennifer Widom.<\/p>\n<div class=\"2d836d85a73757df15d0de19d7bef594\" data-index=\"1\" style=\"float: none; margin:10px 0 10px 0; text-align:center;\">\n<script type=\"text\/javascript\"><!--\r\ngoogle_ad_client = \"ca-pub-2287684400845931\";\r\n\/* valerymorozov *\/\r\ngoogle_ad_slot = \"5606708872\";\r\ngoogle_ad_width = 728;\r\ngoogle_ad_height = 90;\r\n\/\/-->\r\n<\/script>\r\n<script type=\"text\/javascript\"\r\nsrc=\"http:\/\/pagead2.googlesyndication.com\/pagead\/show_ads.js\">\r\n<\/script>\n<\/div>\n\n<div style=\"font-size: 0px; height: 0px; line-height: 0px; margin: 0; padding: 0; clear: both;\"><\/div>","protected":false},"excerpt":{"rendered":"<p>Recently online shopping has become especially popular, superseding and leaving far behind its competitors who work in usual shops. It\u2019s 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 &hellip; <a class=\"continue-reading-link\" href=\"https:\/\/sandalov.org\/blog\/1043\/\"> Continue reading <span class=\"meta-nav\">&rarr; <\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[222,219,220,224,221,223],"class_list":["post-1043","post","type-post","status-publish","format-standard","hentry","category-unsorted","tag-databases","tag-db-class","tag-dbclass","tag-shop","tag-stanford","tag-strawberrynet"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/sandalov.org\/blog\/wp-json\/wp\/v2\/posts\/1043","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sandalov.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sandalov.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sandalov.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sandalov.org\/blog\/wp-json\/wp\/v2\/comments?post=1043"}],"version-history":[{"count":9,"href":"https:\/\/sandalov.org\/blog\/wp-json\/wp\/v2\/posts\/1043\/revisions"}],"predecessor-version":[{"id":1110,"href":"https:\/\/sandalov.org\/blog\/wp-json\/wp\/v2\/posts\/1043\/revisions\/1110"}],"wp:attachment":[{"href":"https:\/\/sandalov.org\/blog\/wp-json\/wp\/v2\/media?parent=1043"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sandalov.org\/blog\/wp-json\/wp\/v2\/categories?post=1043"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sandalov.org\/blog\/wp-json\/wp\/v2\/tags?post=1043"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}