Index :: FAQ :: Contact

Featured Media: Basenet Radio

The show has been dormant for some time (years) but its back.

Latest HackerMedia:

August 21, 2014

Hacker Public Radio
HPR1580: The fast and ntfs File System
The fast and ntfs File System

August 20, 2014

Hacker Public Radio
HPR1579: Crowd Sourced Air Quality Monitoring
Klaatu interviews a programmer about new crowd-sourced air quality detection systems. Big crowds at this Carnegie Melon event, so the sound quality is not great. Links CMU CREATE Lab

Off the Hook
Off The Hook - Aug 20, 2014


The Linux Link Tech Show
The Linux Link Tech Show Episode 571
Russ Pavlicek returns to the show

August 19, 2014

Hacker Public Radio
HPR1578: AudioBookClub-08-How to Succeed in Evil:The Novel
In this episode, the hackerpublicradio.org Audiobook Club reviews How to Succeed in Evil: The Novel by Patrick E. McLean. You can download this audiobook for free (or voluntary donation) from http://podiobooks.com/title/how-to-succeed-in-evil-the-novel/ It's also available as an ebook on Amazon: http://www.amazon.com/How-Succeed-Evil-Patrick-McLean-ebook/dp/B00589W1DM You can find more content (including podcasts) from Patrick E. Mclean on his websites -- http://succeedinevil.com/ -- http://www.patrickemclean.com/ -There is even a promotional comic book http://www.succeedinevil.com/evilfinalsmall.pdf Three out of four of us liked this book, and we all had some good things to say about it. While it's true that this is an entertaining story set in a super hero world, we found it it more amusing and more thought provoking than your average super hero story. As usual, during this episode of the AudioBookClub the hosts have each reviewed a beverage of their choice. pokey drank a Sam Adams Summer Ale http://www.samueladams.com/craft-beers/summer-ale. Thaj drank a home brewed Iced Tea https://en.wikipedia.org/wiki/Iced_tea . x1101 was drinking 32oz of water from the great tasting state of Maine out of a Nalgene water bottle http://nalgene.com/ . FiftyOneFifty was drinking Jim Beam Rye Whisky http://www.jimbeam.com/other-bourbons . Our next audiobook will be Down and Out in the Magic Kingdom by Corey Doctorow http://podiobooks.com/title/down-and-out-in-the-magic-kingdom/ Our next book club recording will be 2014/07/15T23:00:00+00:00 (https://en.wikipedia.org/wiki/ISO_8601#Times) If you'd like a Google calendar invite, or if you'd like to be on the HPR_AudioBookClub mailing list, please get in contact with us on the HPR mailing list 'hpr at hackerpublicradio dot org' There are several ways to submit feedback for this episode including the HPR mail list hpr@hackerpublicradio.org, and the episode's comment section pokey prefers his feedback to come via the HackerPublicRadio comment system, but is also usually available on StatusNet @pokey/micro.fragdev.com Please remember to visit the HPR contribution page. We could really use your help right now. http://hackerpublicradio.org/contribute.php We had a great time recording this show, and we hope you enjoyed it as well. We hope you'll consider joining us next time. Thank you very much for listening. Sincerely, The HPR_AudioBookClub P.S. Some people enjoy finding mistakes. For their enjoyment, we have included a few.

Phone Losers
Snow Plow Show – August 19th, 2014 – Word Crimes
This show is sponsored by Rick from Ohio.  Thanks for only giving us a short show, Rick! In this show we revisit the Ghost Tower guy, fail to find Nazi gold in our apartment, and notify some people of their … Continue reading

Phone Losers
Snow Plow Show – August 19th, 2014 – Word Crimes
This show is sponsored by Rick from Ohio.  Thanks for only giving us a short show, Rick! In this show we revisit the Ghost Tower guy, fail to find Nazi gold in our apartment, and notify some people of their crimes against grammar.

August 18, 2014

Hacker Public Radio
HPR1577: Introducing Nikola the Static Web Site and Blog Generator
Nikola - The Static Web Site and Blog Generator - http://getnikola.com Note: Please see developer notes below What is it? A Static Website and Blog Generator based on Python. What is a Static Website Generator? It generates posts and pages via commands. You edit those posts and pages in a text editor, then run a command to build the site, and finally, deploy/upload the generated html etc files to your webhost. That sounds kinda old school are you sure thats web 3.0? Its old and new school. Nikola gives you CMS like features without the overhead of the database server and page rendering engine. How can I install it? Use PIP and follow the handbook on the getnikola.com website. NOTE: Python 2.6 or newer or Python 3.3 or newer is required sudo pip install nikola sudo pip install nikola[extras] You should be good to go if you can enter nikola help in a terminal and get a list of nikola commands. Lets create our skeleton website: nikola init mysite You will need to answer some questions now (NOTE a directory to cd into called mysite will be created if you issue mysite.. You should enter your domain name instead - mysite is just an example). The questions it asks will help populate the conf.py file in the mysite directory. Site Title: Site Author: Site Author Email: Site Description: Site URL: Languages to support: (default en) Time zone: Which comments system to use: Once complete your site will be created and in the directory you named the site as - in my case, mysite. cd into that and take a look at the files with ls. you will have: conf.py - your configuration file files - where you will place images etc and reference them in blog posts and pages galleries - where you can serve up images in a gallery posts - where your blog posts go stories - where your pages go Lets create a blog post. nikola new_post Type in the title of your blog post and hit enter. I will use foobar in this example It will report the new post is in posts/foobar.rst fire up your text editor and edit that file. There is a header area at the top of the file - most of it is already filled in and you wont need to change it but you should add a Tag because you can see posts by Tag once the site is generated and it gives your readers a way to find all items on that subject. These are separated by commas so enter as many or few as you like. Enter a Description in the Description area. Now move into the Write your post here area and go to town - erase that or it shows up in your post. You should read the page on ReStructuredText here: http://getnikola.com/quickstart.html but also just look at the Source by clicking Source on the getnikola website and you can see the markup they used. Some basics are *word* for italics, word for bold, a single * space item for bullet points and for hyperlinks `Tree Brewing Co: `_. a Tree Brewing Co hyperlink which will bring you when clicked to treebere.com. Lastly issue: .. image:: /files/imagefilename.jpg to point to an image file that you have placed into the files directory. Ok lets say you are done your post, save it and exit. Lets now build your site and fire up the built in webserver to display it. nikola build nikola serve -b Your default web browser will launch and you will see your site with blog post. Savour the moment - you have just created your first blog post. Note all the generated files you would upload to your webhost are in the output folder. Ok so thats great but I want to add pages and have it in my navigation window Ok lets do that. nikola new_post -p Enter a name for it and press Enter. In my case I created MyPage It tells you your page is in the stories directory and shows you how it named the file. In my case its mypage.rst Open that in a text editor and compose the page - save it when complete. So that would be great but its not showing up in your navigation yet. You need to put that in your conf.py file. Open conf.py in a text editor, look for NAVIGATION_LINKS. Observe how the existing pages are linked and follow that format. Here is how I would add mypage: (/stories/mypage.html, MyPage), any page you create will show up in stories so dont forget to put that in the path. NAVIGATION_LINKS = { DEFAULT_LANG: ( ("/archive.html", "Archive"), ("/categories/index.html", "Tags"), ("/rss.xml", "RSS feed"), ("/stories/mypage.html", "MyPage"), ), } Save that and rebuild your site. NOTE:: As of Today Nikola v7.0.1 requires a special command to include the new pages in navigation. This has been fixed in git but currently you must issue: nikola build -a nikola serve -b Now you are viewing it - nice work - you have a page now. This site seems a bit plain, how can I theme it? Glad that you asked - issue this command. nikola bootswatch_theme -n custom_theme -s slate -p bootstrap3 Now you have set it to use the slate bootswatch theme. Review the bootswatch themes on: http://bootswatch.com/ In order to let Nikola know to use this new theme you need to edit the conf.py file and look for THEME and change the value from bootstrap3 to custom_theme. Now issue these commands at the command line to view the changes: nikola build nikola serve -b You can modify the themes to your liking and there is guidance on changing the theme on the nikola website. There are ways to depoly your site via rsync or ftp commands in the conf.py file. There are also other things you can set in the conf file such as google analytics, add an embeded duckduckgo or google search engine, specify options for the image galleries etc. More things you can do to spify up your posts / pages are to do with using shortcode like sytax for ReStructuredText. You can embed soundcloud, youtube videos etc - here is a list of these: http://getnikola.com/handbook.html#restructuredtext-extensions I hope this helps you get started on using Nikola and hope you enjoy using it as much as I do. If you have questions or comments, find me in the oggcastplanet.net irc chat room on freenode, or go to http://stevebaer.com click Tags and click HPR and leave a comment on this episodes blog post. Until next time, Cheers! Corrections to this episode provided by Chris Warrick http://stevebaer.com/posts/hpr-episode-on-using-the-static-web-site-and-blog-generator-called-nikola.html Some small corrections: it is recommended to use a virtualenv, `sudo pip` can be dangerous `pip install nikola[extras]` is enough, no need to do both steps new pages can be created with `nikola new_page`, too (both ways are equally supported) missing quotes around "MyPage" in example navbar code Fixed you can get rid of /stories/ if you change PAGES[*][1] from "stories" to an empty string. bootswatch themes are not everything, there is also install_theme that uses a more varied collection from http://themes.getnikola.com/

August 17, 2014

Hacker Public Radio
HPR1576: How I got into Linux
Short Summary: This is my story about how I got into computers, computing and GNU/Linux. Links: Personal identity number in Sweden https://en.wikipedia.org/wiki/Personal_identity_number_%28Sweden%29 Apple II: https://en.wikipedia.org/wiki/Apple_2 ABC80: https://en.wikipedia.org/wiki/ABC_80 Tipp-Ex: https://en.wikipedia.org/wiki/Tippex IBM Portable PC: https://en.wikipedia.org/wiki/IBM_Portable MacIntosh Plus: https://en.wikipedia.org/wiki/Macintosh_Plus Imagewriter II: https://en.wikipedia.org/wiki/ImageWriter_II Hypercard: https://en.wikipedia.org/wiki/Hypercard Netscape Navigator: https://en.wikipedia.org/wiki/Netscape_Navigator Debian Social Contract: https://www.debian.org/social_contract

August 15, 2014

Phone Losers
The Snow Plow Hobo Sound Quality Show – August 15th, 2014
Today’s show was done while on the road and its hobo sound quality is fully funded by UtahKurt.  Thanks a lot, UtahKurt.  In this show I collapse an apartment floor, tear down a lady’s fence, and yell at a guy … Continue reading

Phone Losers
Snow Plow Show – August 15th, 2014 – Hobo Sound Quality
Today’s show was done while on the road and its hobo sound quality is fully funded by UtahKurt. Thanks a lot, UtahKurt. In this show I collapse an apartment floor, tear down a lady’s fence, and yell at a guy about his hobo cat food. And, of course, play the hits of Rappy McRapperson.

August 14, 2014

Hacker Public Radio
HPR1575: 35 - LibreOffice Calc - Introduction to Functions
In this episode we review what a function is, discuss the different types of functions available in LibreOffice, discuss the concept of arguments in mathematics, and present a general process for using functions in Calc. Links: http://www.merriam-webster.com/dictionary/function http://en.wikipedia.org/wiki/Argument_of_a_function http://www.ahuka.com/?page_id=780

August 13, 2014

Hacker Public Radio
HPR1574: Arts and Bots
Klaatu interviews a teacher about the use of robots and programming in liberal arts classes. Big crowds at this Carnegie Melon event, so the sound quality is not great. Links CMU CREATE Lab

Off the Hook
Off The Hook - Aug 13, 2014


Phone Losers
Snow Plow Show – August 10th, 2014 – Termite Sunday
Today’s blasphemous Sunday show is sponsored by D. Glad!

Phone Losers
Snow Plow Show – August 8th, 2014 – Lawn Killer
Are you in the mood to hear the same phone call over and over for an entire show? Then this show, sponsored by Michael from Australia, is the show for you! The editing of approximately 3 hours out of this show is all thanks to Michael. Be thankful that you missed the live show.

The Linux Link Tech Show
The Linux Link Tech Show Episode 570
netflix streaming on linux, finally, solr, ant, life in the dreamhouse

August 12, 2014

Hacker Public Radio
HPR1573: Make your own t-shirt with bleach
Making T-shirts with bleach and freezer paper links Tutorial in pictures: http://imgur.com/a/ELB7g bleachshirts: http://www.reddit.com/r/bleachshirts/ be sure to check out side bar at /r/bleachshirts for more tutorials

August 11, 2014

Hacker Public Radio
HPR1572: An Open Source News Break from Opensource.com
In this episode: The new Fedora Scientific Spin, open source approaches to pharmaceutical research, and the Apache Open Climate workbench. Links: https://opensource.com/life/14/6/linux-distribution-science-geeks http://opensource.com/health/14/6/can-open-science-help-patients-and-save-pharma http://opensource.com/life/14/6/NASA-Earth-science-open-source

August 10, 2014

Phone Losers
Snow Plow Show – August 8th, 2014 – Termite Sunday
Today’s blasphemous Sunday show is sponsored by the artist formally known as D. Glad!   Creep by those one guys that do weird covers of songs Thanks, everyone, for sending your “cactus” clips to me! People mail me stuff a … Continue reading

Hacker Public Radio
HPR1571: Yahoo Mail Forwarder
Build, configure and deploy a self maintaining Yahoo mail forwarding virtual client. Needed VirtualBox Fedora 20 LXDE/32Bit iso file. Virtual Hosting Server (currently using VirtualBox, phpVirtualBox with a Centos6 host). Yahoo Account IMAP capable email account for delivery. Since it will be virtual, isolated, single purpose machine, Security is minimal. Step by step instuctions at http://james.toebesacademy.com/YahooMailForwarder.html Build VM Configure Applications and AutoStart Configure Mail Forwarding Configure Automatic Maintenance Test Deploy to Virtual Server. Known Issues: Occasionally bulk forwards spam folder.... Let me know your thoughts and if you want to hear more about my home server configuration.

August 09, 2014

Phone Losers
Snow Plow Show – August 8th, 2014 – Lawn Killer
  Are you in the mood to hear the same phone call over and over for an entire show?  Then this show, sponsored by Michael from Australia, is the show for you!  The editing of approximately 3 hours out of … Continue reading

August 07, 2014

Hacker Public Radio
HPR1570: The JFS File System
The JFS File System

August 06, 2014

Hacker Public Radio
HPR1569: Many-to-many data relationship howto
How to implement a many-to-many join in a relational database The purpose of this HPR show is to demonstrate the best, and really the only way to define a many-to-many relationship between two entities in a database. What triggered it? There has been some discussion between Ken and Dave on the community news podcasts, presumably relating to some work which is being done on the HPR web site. I sent Ken an email explaining how to implement a many-to-many relationship and got a predictable response; do a show :) So here it is. What do I mean by database entity? In analysing the structure of the data which is to be stored by a database, one of the most important things to do is to identify what entities are to be stored and manipulated. What constitutes an entity is often quite simple; some examples might be 'customer', 'billing address', 'shipping address', 'invoice', 'invoice item' etc. In fact it's also true to say that more often than not entities and tables have a one-to-one relationship. If the analysis of your data reveals that there is a 'customer' entity, then there will probably be a 'customer' table. One area where this might not be quite true is where the mechanism used to implement the whole software system demands a greater level of granularity. There are some e-commerce systems which are written with object-oriented technology and which demand that the data model matches the objects in the system. Typically this results in a data model that might look like it is over-normalised. But for the sake of this example, we will assume that one entity occupies one table. In fact if you find your analysis has any two tables that appear to have a one-to-one relationship, there is probably something wrong with your analysis because these two tables could be merged into one. Entity Relationships In a database system comprised of a number of tables, one table per entity, there will be complex relationships between the entities. This is the reason we talk about 'relational' databases. Or perhaps it is because all the columns in a table are supposed to be related to the unique identifier in that table, not sure, and neither was Mr. Codd. Types of relationship One-to-many or many-to-one, depending on which end of the telescope you are looking through. For example a customer might make one, or many purchases from your company e-commerce system. In which case there will be a one-to-many relationship between the 'customer' table and the 'invoice' table in the sales ledger. Note that if the customer has only ever made one purchase there will only be one row in the invoice table, but it COULD contain more. The more end is really one-or-more. Because one visit to the web site might result in the customer dropping more than one item into his shopping cart, there will also be an 'invoice lines' or 'invoice items' table. How these relationships are represented on diagrams Classically an entity relationship diagram consists of a series of rectangles, one for each table with the name of the entity written in the box. The entity rectangles are joined together by lines. These lines have what are usually called 'crows feet' at the 'many' end. A crows foot looks just like what it says, where the line joins the many end of the join, the line splits into three prongs before it hits the side of the many entity rectangle. Depending on what mechanism has been used to create this diagram, the crows foot can also look more like a fork than a crows foot, but there are still three prongs. In this text I will use a line consisting of dashes to join entities, and a backwards left or right arrow to represent the many end of a relationship. So, using the example above, the one-to-many relationship between customer and invoice looks like this: customer-----<invoice Here the less-than sign is used at the many end and should be thought of as a crows foot with the middle toe missing. How to join two tables in a many-to-many relationship You will not come across this relationship very often. It is far less common than a simple one-to-many or many-to-one. One example where this might be useful, and the example I use in this text, is a music database where two of the entities are: Artist Genre Clearly there will be multiple artists, and multiple genres. And it is not inconceivable that an artist might appear in more than one genre. And a given genre will clearly contain more than one artist. So this gives rise to a many-to-many relationship, which in pure analysis terms could be diagrammed like this: artist>-----<genre But this is NOT the way to define it in actual physical database tables. Observing the rules of normalisation, an artist should be identified by one property, the artist name, and a genre should be identified by one single property, the genre name. More often, and in our example below, each entity is given a unique identifier which is in addition to it's actual name. This is how changing a simple name in one table can result in the change being seen globally over the entire database system. For example in a customer table, the row: id name -- ---- 1 Mickey Mouse Will cause 'Mickey Mouse' to be shown as the customer name wherever the identifier '1' is used to retrieve records or to join tables in a complex SQL query. Change 'Mickey Mouse' to 'Donald Duck' in this table, and 'Donald Duck' will appear everywhere 'Mickey Mouse' was seen before. Foreign Keys When two tables are to be joined to make a query, columns, or multiple columns are given indexes. A column which contains the key from another table is called a 'foreign key'. Going back to our customer and invoice example from above, the invoice table will contain the customer identifier from the customer table. And because a single customer can make more than one visit to our web site to buy stuff, the column containing the customer identifier in the invoice table is not given a unique index. If the customer with the identifier '12345' has made five different shopping excursions to our site, there will be five rows in the 'invoice' table containing the customer identifier '12345'. Armed with this information, how will we represent this: artist>-----<genre Clearly the artist table should contain a foreign key from the genre table, and the genre table should contain a foreign key from the artist table. The rules of normalisation say that all the attributes in a table (columns) should relate to the primary key of the table. Clearly putting a genre foreign key into the artist table, or an artist foreign key into the genre table busts this rule wide open. Don't do it The solution To solve this problem we introduce another table between the artist table and the genre table, which I always suffix with '_xref', short for cross-reference. Now our entity relationship diagram will look like this: artist-----<artist_genre_xref>-----genre What does the artistgenrexref table contain? Simple, it contains the bare minimum to define a unique row which joins an artist and a genre. If the artist identifier is called artistid and the genre identifier is called genreid, then the xref table contains two columns: artist_id genre_id What kind of index do we need on this table to make a unique relationship between an artist and a genre? We need a unique compound index which uses both columns. This will ensure that one and only one row can appear in the table joining one artist to one genre. But because an artist can belong to more than one genre, both columns in the index mean this is possible. A worked example In the example code and data below, I have used SQLite3. SQLite is the world's most used Relational Database System (RDBMS). How can this claim be made? Well if you have a smart-phone in your pocket, it probably uses SQLite. If you have a satellite TV receiver, a Tivo or some other kind of home media device, it probably contains SQLite. And if you are really strange and have a fridge which will tell you what's inside, it probably uses SQLite. And there will be one row which says 'half an onion wrapped in foil which has been in here for six months'. Several times I have been brought Blackberry hand-sets and asked to retrieve important documents or texts from it when the user interface mechanism has failed. Something that often happens with that flavour of soft fruit. SQLite is easy to install on your Linux machine. In fact it is used by so many other packages that it may well be on there already. But you may have to install the interactive SQLite3 program. On Arch Linux: $ sudo pacman -S sqlite3 On Debian or Ubuntu: $ sudo apt-get install sqlite3 Below I have inserted the contents of all the files which I created to demo this many-to-many relationship strategy. Each file is topped and tailed by the string '--snip--'. In the SQLite3 interactive program, a double dash ('--') starts a line comment. Each file also contains the name of the file and a description of what it does. An exception is the .csv files I have used to load data into my little test database. These have the '--snip--' tops and tails in this text but they do not exist in the actual data, for obvious reasons. If you are snipping out the csv data to try this at home, don't include anything but the data lines in the csv data. To start an interactive SQLite 3 prompt and create your test database, do this at a Linux or Windows command prompt. Here the prompt is represented by a dollar sign: $ sqlite3 music.db You will get this prompt: sqlite> Because the first thing I alwys want to know about apiece of software I haven't used before is how to get out, to get out of the interactive SQLite3 session, type this, of course 'sqlite>' is the prompt: sqlite>.quit To run a file of commands you have defined in an external file, do this: sqlite>.read filename Where 'filename' is a file containing dot prefixed commands and/or SQL. The files from my working example: The following file contains commands to create tables and indexes in the database named on the command-line when you called sqlite3. --snip-- -- -- file name: ddl.sql -- -- ddl = 'data definition language' -- -- This SQL creates three tables, the artists table tbl_artist, -- the genre table tbl_genre, and the cross-reference -- ttable tbl_artist_genre_xref -- -- The xref table is what provides the many-to-many relationship -- between artist and genre by virtue of it's -- compund index; idx_artist_genre_xref, -- which has two columns included in it -- -- I use the tbl_ prefix for tables and the idx_ prefix for indexes. -- These might seem redundant but they are useful for preventing -- collisions between database component names and reserved words. -- -- Create the artist table create table tbl_artist ( artist_id integer not null primary key, artist_name text not null ); -- Create the genre table create table tbl_genre ( genre_id integer not null primary key, genre_name text not null ); -- Create the artist_genre_xref table -- -- I use number for both columns instead of integer because SQLite -- does something funky with auto-incrementing integer -- columns which have a 'not null' constraint, I think create table tbl_artist_genre_xref ( artist_id number not null, genre_id number not null ); create unique index idx_artist_genre_xref on tbl_artist_genre_xref ( artist_id, genre_id ); --snip-- There follow three files which contain comma-separated values (.csv) records for loading into each table. The first is artist data: --snip-- 1,"Horslips" 2,"Runrig" 3,"The Pogues" 4,"Led Zeppelin" 5,"Disturbed" 6,"Martin Carthy" 7,"Steeleye Span" 8,"Schubert" 9,"Mozart" --snip-- The first three artists fall into both the folk and rock genres. Led Zeppelin and Disturbed will be in rock only. Martin Carthy and Steeleye Span are folk only. Shubert and Mozart need no further explanation. The next is data to load into the genre table: --snip-- 1,"Folk" 2,"Rock" 3,"Classical" 4,"Scottish" 5,"Irish" --snip-- The last data file is the data which will be loaded into the artistgenrexref table. It contains only numerical data: --snip-- 1,1 2,1 3,1 1,2 2,2 3,2 4,2 5,2 6,1 7,1 8,3 9,3 1,5 3,5 2,4 --snip-- What is this data doing? Well there are some artists there which will appear in both the 'folk and the 'rock' genres. Horslips are a seventies Irish folk-rock band. I still play their 'The Book of Invasions' album at least once a week. This is either because it is a seminal album or because I am a dinosaur who refuses to be dragged kicking and screaming into the 21st century. Runrig are a band from the Western Isles of Scotland who cross over the folk/rock boundary also. Check links at the end of this text. The next file loads the data from these .csv files into the three database tables. --snip-- -- -- file name: load.sql -- -- Load some data into the tables from three CSV files -- .separator "," .import artist.csv tbl_artist .import genre.csv tbl_genre .import xref.csv tbl_artist_genre_xref --snip-- There now follow some SQL queries which retrieve data-sets which should be obvious from the file names: --snip-- -- -- file name: select-folk-only.sql -- -- This is where the SQL gets a bit hairy, using a sub-query to exclude -- everything except the category we want from the record-set. -- -- This is the kind of situation where a view -- might be useful to hide some of the SQL complexity from -- the user. -- select a.artist_id, a.artist_name, x.genre_id from tbl_artist as a, tbl_artist_genre_xref as x where a.artist_id = x.artist_id and x.genre_id = 1 and a.artist_id not in ( select a.artist_id from tbl_artist as a, tbl_artist_genre_xref as x where a.artist_id = x.artist_id and x.genre_id != 1 ); --snip-- --snip-- -- -- file name: select-folk.sql -- -- Straight-forward query to select artists that appear in the folk -- category. -- -- This will return artists that appear in the folk category, which -- includes artists that appear either exclusively in folk or in BOTH -- folk and any other category also. -- -- A little hard to get your head around. Remember the artists returned -- by this set are in both folk and any other category. -- select a.artist_id, a.artist_name, x.genre_id from tbl_artist as a, tbl_artist_genre_xref as x where a.artist_id = x.artist_id and x.genre_id = 1; --snip-- --snip-- -- -- file name: select-rock-only.sql -- -- This is where the SQL gets a bit hairy. It uses a sub-query to -- exclude everything except the category we want from the record-set. -- -- This is the type of query where a view might be useful to hide some -- of the SQL complexity from the user -- select a.artist_id, a.artist_name, x.genre_id from tbl_artist as a, tbl_artist_genre_xref as x where a.artist_id = x.artist_id and x.genre_id =2 and a.artist_id not in ( select a.artist_id from tbl_artist as a, tbl_artist_genre_xref as x where a.artist_id = x.artist_id and x.genre_id != 2 ); --snip-- --snip-- -- -- file name: select-rock.sql -- -- select all artists which appear in the rock category -- select a.artist_id, a.artist_name from tbl_artist as a, tbl_artist_genre_xref as x where a.artist_id = x.artist_id and x.genre_id = 2; --snip-- --snip-- -- -- file name: select-scottish.sql -- -- Straight-forward query to select artists that appear in the scottish -- category. -- -- This will return artists that appear in the scottish category, which -- includes artists that appear either exclusively in scottish or in -- BOTH scottish and any other category also. -- select a.artist_id, a.artist_name, x.genre_id from tbl_artist as a, tbl_artist_genre_xref as x where a.artist_id = x.artist_id and x.genre_id = 4; --snip-- --snip-- -- -- file name: select-irish.sql -- -- Straight-forward query to select artists that appear in the irish -- category. -- -- This will return artists that appear in the irish category, which -- includes artists that appear either exclusively in irish or in BOTH -- irish and any other category also. -- select a.artist_id, a.artist_name, x.genre_id from tbl_artist as a, tbl_artist_genre_xref as x where a.artist_id = x.artist_id and x.genre_id = 5; --snip-- The next files, all prefixed 'dump' will dump the record-sets returned by the above SQL queries into corresponding .csv files: --snip-- -- -- file name: dump-folk-only.sql -- -- Dump all artists that appear ONLY in the folk category -- into folk-only.csv -- .mode csv .output folk-only.csv .read select-folk-only.sql --snip-- --snip-- -- -- file name: dump-folk.sql -- -- Dump all artists that appear in the folk category -- into folk.csv -- .mode csv .output folk.csv .read select-folk.sql --snip-- --snip-- -- -- file name: dump-rock-only.sql -- -- Dump all artists that appear ONLY in the rock category -- into rock-only.csv -- .mode csv .output rock-only.csv .read select-rock-only.sql --snip-- --snip-- -- -- file name: dump-rock.sql -- -- Dump all artists that appear in the rock category -- into rock.csv -- .mode csv .output rock.csv .read select-rock.sql --snip-- --snip-- -- -- file name: dump-scottish.sql -- -- Dump all artists that appear in the scottish category -- into scottish.csv -- .mode csv .output scottish.csv .read select-scottish.sql --snip-- --snip-- -- Dump all artists that appear in the irish category -- into irish.csv -- .mode csv .output irish.csv .read select-irish.sql --snip-- In a real-world example, the hard-coded numeric genre identifiers in the above queries would be replaced by placeholders like '?' which would be replaced by actual values at run-time. Conclusion In conclusion, in my experience programming all manner of systems using Oracle, MySQL and SQLite databases, this method is the only one which doesn't take diabolical liberties with the rules of database normalisation. It may result in SQL queries which will make you scratch your head, but that is far more acceptable than doing stuff like trying to shoe-horn a many-to-many relationship into your database structure by other means. One of the most crucial aspects of using a high-end RDBMS like Oracle, MySQL or SQLServer, is the need to get as much data selection done by the server as possible. This is because the server is a big fat box in a cupboard connected to your machine by a network infrastructure. It might be next door or on the other side of the world. It makes far more sense for the server to return to you those records, and ONLY those records you want, over the network. Any other strategy for implementing a many-to-many relationship is likely to result in you pulling stuff back to your machine which you are ultimately going to drop in some kind of loop. Slow and wasteful of bandwidth. In the example I have used, the cross-reference table was populated manually. In most real-world implementations the cross-reference table will be populated in response to records being added to the two outer tables, or in response to user-intervention using a client application. Often triggers are used to create the cross-reference rows. If you were authoring a music database application in which such a relationship exists between artist and genre, the user interface would probably provide a means for the user to decide which genres to plop artists into. I have used SQLite to demo this strategy. While SQLite is a great tool, it is a 'lite' tool that is designed for single-user applications, in particular embedded systems. If you are thinking of starting an airline and want to implement a world-wide seat booking application which will serve many concurrent users, needing complex transactional operations, don't use SQLite or your customer complaints are likely to exceed bookings. My example data definitions also contain no constraints for preventing orphaned rows. These are rows in a table containing a foreign key where no record exists in the table identified by the foreign key. Because in my example I load the 'parent' tables before I load the table which contains foreign keys to both of those tables, there is no risk of creating orphaned rows. Most RDBMS systems include a mechanism for what is called 'cascaded deletes', that is, when deleting a row from a parent table, any row in a table containing a foreign key for that ro, a 'child' row, will also be deleted, preventing 'orphaned' records. Applying this to the above example, deleting 'Runrig' from the artists table would also delete all rows from the artistgenrexref table with the identifier for 'Runrig'. Links Database normalisation according to Mr Codd: http://en.wikipedia.org/wiki/Database_normalization SQLite: http://www.sqlite.org/ Horslips are/were an Irish folk-rock band. The first two tracks from 'The Book of Invasions; a Celtic Symphony' live: http://www.youtube.com/watch?v=rqk3U0nFEI8 Runrig are a Scottish folk-rock band who have been around for years. Loch Lomond live: http://www.youtube.com/watch?v=CHu0h9XaNcg If any queries result from this show about any of the terms I might have very casually scattered about relating to database theory, assuming I know the answers, I can do more shows about those. Maybe one about SQLite in particular. Mike

Phone Losers
Snow Plow Show – August 6th, 2014 – Ghost Tower
Here’s a brand new show that has everything! Blue cacti! Ghost towers! Squatters rights! Extortion of the smoothie store! All thanks to today’s sponsor – neonlikebyork. Thanks, neonlikebyork! Break My Stride by Matthew Wilder Katie’s annoying kid stole Carlito’s train … Continue reading


[more]
Link to us!

Hackermedia.org is provided by:
Binary Revolution | Infonomicon Computer Club