Converting TEXT columns to JSONB in PostgreSQL

Recently I had a text field being used to store JSON data from my web application. I hadn’t yet discovered the amazingness of the PostgreSQL JSONB datatype.

JSONB is an amazing datatype because Postgres understands how to query the structure and the keys and values can be indexed.

So if you have a text fields you want to convert to JSONB (provided the existing data is in properly formed JSON already) You can change the datatype and convert the existing data using this simple SQL command:

alter table tbl_name alter column col_name type jsonb using col_name::JSON;

You may be inclined to covert the text using the to_json function like: ‘USING to_json(col_name);’ – BUT DON’T DO THAT! What happens with that is you get a single string encapsulating the valid JSON, containing your JSON. Effectively a double-encoding bug.  The former (casting the column to JSON is the correct way).

How to Factory Restore an iPhone without the Passcode

Recently, I helped a client who owned an iPhone which they needed to restore to factory settings, however they had forgotten the passcode they had originally set the phone up with. Normally, to update an iPhone you are required to ‘authenticate yourself not he device so that the computer being used to do the update knows you own the device.

Under normal conditions this is completely fine; but in this situation it caused a big problem.

Luckily, if you don’t care about the contents of the phone, it is possible to reinstall iOS with out the passcode if you know the trick (be warned, the Phone will be completely erased, but if you cannot get into it and have no backup – you’ve basically lost the datas already, haven’t you?)

  1. Turn off the device by holding down the power button until the slide to turn of message appears and swipe.
  2. Connect your lightning cable to your PC but DO NOT PLUG IN THE PHONE.
  3. Hold the Phone’s “Home” button as you connect the usb cable into the device. This should cause the iPhone to start up automatically.
  4. Continue holding down the “Home” button until the “Connect to iTunes” symbol appears on the screen. When you see this, release the “Home” button.


    A message should appear on the computer from iTunes saying that it has detected an iPhone as a device in Recovery Mode.

  5. Click the “Restore” button and the device will be completely erased and iOS will be reinstalled onto the device.

And walla! One freshly Factory Restored iPhone without knowing the passcode.  This can be done on any computer with iTunes, it does not have to be the computer that normally manages the backups and syncs of the phone.

How The” Penny Auction” Scam Works

Recently there has been a rise and fall of several penny auction sites boasting about selling genuine high-demand items at massive discounts off recommended retail prices.  But all is not what it seems.

The secret to this scam is that you must pay money every time you make a bid!

And every time a bid is made, the timer for the auction increases a little bit, usually by about 10 seconds. So you have a $1000 camera with a bid for $150 and its got 6 seconds left, but nope, someone else makes a bid and extends the timer by 12 seconds or so, and then another, and another and this can go on for hours.

How much each bid costs varies, but usually they will front-bill your credit card (when you signup) for 200 bids for, say for the sake of argument $100. Many people often complain that the charge to their credit card is under-played and not obvious until its to late. Usually, getting refunds from Penny Auction operators is like trying to get blood out of a stone. (Although this practise itself is not a scam; it is definitely misleading).

Now is where the trouble starts.

The thing that most people don’t understand (and where the scam kicks in) is that penny auction operators are allowed to shill their own auction’s bids. They typically do this because;

  1. The price is too low
  2. If not enough people have big on the item

In other words, if the money paid by either the actual bid, or the charges made to the sum of people bidding is LESS than the cost of the item, the site will simply start bidding on its own behalf to push up the price (and extended the duration).

The other dangerous part of the penny auction is that the site operators are allowed to sell your browsing habits and personal information with third-parties, often with those that helps the operator perform “services”.

So with every bid effectively being charged to a credit card; each individual bid (with the obvious exception of the shill bids) subsidises the price of the equipment for the person who eventually wins the item and whom musty pay not only the price they bid, but also for each bid they made.

Sure, some people do actually win very cheap items. But for every individual auction there are several losers making up the difference in price (and who receive nothing). And since the auctioneer can bid against you, no item need actually be sold until the auction site has made a huge profit on that “cheap” item.

Fun Facts from a “Swipe Auctions” example:

 

By the time the auction ended (5 hours later than the first image at the top), the camera in the Swipe Auctions auction pictured had had another 13,105 BIDS!!

At using an EXTREMELY conservative estimate of 5 cents a bid, people still spent $1,283 on bids alone (probably closer to $2,500), and the guy who won had to buy the camera for $256 on top of whatever he spent on bidding.
Only 1 person won that camera auction, and only after spending several hundreds in bids.

Several people lost this auction and threw their money down the drain and wasted several hours of their lives.

There is no way to know if Swipe Auctions extended the auction superficially on their side in order to make more money (is it a coincidence that it ended so abruptly so early (11:00pm), but right after they cleared over $100 in bids (AT LEAST) over the MSRP of the camera?)

For the record, Swipe Auctions no longer exists (and they had actually launched dozens of ‘sister sites’- I have no idea if any of those are still in operation). But this article is written in the hopes that I can protect people from falling into these dodgy sites who aim to take your money and give you very little if nothing in return.

IDisposable StopWatch to Benchmark code blocks (via Lambda or Delegate) in C#

Sometimes you need to time code execution for use *inside* your application. the StopWatch class was designed just for this purpose, but you end up with some pretty ugly code that looks like this:

Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
// Do something.
for (int i = 0; i < 1000; i++) { Thread.Sleep(1); }
// Stop timing.
stopwatch.Stop();
// Write result.
Console.WriteLine("Time elapsed: {0}", stopwatch.Elapsed);

This gets really messy, especially if the code is already pretty complicated. But with very little effort you can make the .Net StopWatch just as beautiful as the Benchmark class in Ruby.

public class DisposableStopwatch: IDisposable {
  private readonly Stopwatch sw;
  private readonly Action f;

  public DisposableStopwatch(Action f) {
    this.f = f;
    sw = Stopwatch.StartNew();
  }

  public void Dispose() {
    sw.Stop();
    f(sw.Elapsed);
  }

}

To use the new stopwatch:

using (new DisposableStopwatch(t => Console.WriteLine("{0} elapsed", t)) {
  // do stuff that I want to measure
}

This is the best solution I’ve ever seen for this problem! No extension (so that it can be used on many classes) and very clean and very simple!

How to Clone or Duplicate a PostgreSQL Database

Sometimes you may find yourself needing to duplicate a postgres database – complete with schema, data; exactly. Sometimes I need to do this because I want to try out some ideas on an existing database but without the hassle of having to backup and restore or write rollbacks for the changes I want to make.

Luckily, it’s super easy to do this.  First ensure that there are not active connections to the source database; and then open the SQL Terminal of your choice and execute:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

This will create a new database, by using the source database as a template.

If you get the message: “ERROR: Database being accessed by other users.” don’t worry; it just means that there are still open database connections, and these will need to be closed before it will work.

SerializationException Running Npgsql Commands on .Net Entity Framework

Recently, while working on a project for a client I was writing a .Net application using the Entity Framework backed onto a PostgreSQL database. All of a sudden I got a System.Runtime.Serialization.SerializationException when I was trying to run my app, and the stack trace (weirdly) didn’t help.

It turns out that while I had added the Npgsql dependency to the project, it wasn’t enough. Entity Framework it seems, needs to have access to Npgsql.dll assembly information.  When EntityFramework tries to use the library, it won’t find it (despite being in the project) unless it is in the Global Assembly Cache (GAC).

The Global Assembly Cache (GAC) is a folder in Windows directory to store the .NET assemblies that are specifically designated to be shared by all applications executed on a system. Assemblies can be shared among multiple applications on the machine by registering them in global Assembly cache(GAC). Source

The best way to resolve this is to use the Npgsql installer that matches the version in your project from: https://github.com/npgsql/npgsql/releases. Those setups take care of registering Npgsql in GAC and set up the machine.config file to include the Npgsql db provider factory.

After I did this, everything worked as it should, and all was right with the world.

97 Things Every Programmer Should Know

PLEASE NOTE: This is a copy of the official list and I only copy it here for my own personal purposes (in case the list is lost), and not because I am trying to circumvent copyright or profiteer from it.

  1. Act with Prudence by Seb Rose
  2. Apply Functional Programming Principles by Edward Garson
  3. Ask “What Would the User Do?” (You Are not the User) by Giles Colborne
  4. Automate Your Coding Standard by Filip van Laenen
  5. Beauty Is in Simplicity by Jørn Ølmheim
  6. Before You Refactor by Rajith Attapattu
  7. Beware the Share by Udi Dahan
  8. The Boy Scout Rule by Uncle Bob
  9. Check Your Code First before Looking to Blame Others by Allan Kelly
  10. Choose Your Tools with Care by Giovanni Asproni
  11. Code in the Language of the Domain by Dan North
  12. Code Is Design by Ryan Brush
  13. Code Layout Matters by Steve Freeman
  14. Code Reviews by Mattias Karlsson
  15. Coding with Reason by Yechiel Kimchi
  16. A Comment on Comments by Cal Evans
  17. Comment Only What the Code Cannot Say by Kevlin Henney
  18. Continuous Learning by Clint Shank
  19. Convenience Is not an -ility by Gregor Hohpe
  20. Deploy Early and Often by Steve Berczuk
  21. Distinguish Business Exceptions from Technical by Dan Bergh Johnsson
  22. Do Lots of Deliberate Practice by Jon Jagger
  23. Domain-Specific Languages by Michael Hunger
  24. Don’t Be Afraid to Break Things by Mike Lewis
  25. Don’t Be Cute with Your Test Data by Rod Begbie
  26. Don’t Ignore that Error! by Pete Goodliffe
  27. Don’t Just Learn the Language, Understand its Culture by Anders Norås
  28. Don’t Nail Your Program into the Upright Position by Verity Stob
  29. Don’t Rely on “Magic Happens Here” by AlanGriffiths
  30. Don’t Repeat Yourself by Steve Smith
  31. Don’t Touch that Code! by Cal Evans
  32. Encapsulate Behavior, not Just State by Einar Landre
  33. Floating-point Numbers Aren’t Real by Chuck Allison
  34. Fulfill Your Ambitions with Open Source by Richard Monson-Haefel
  35. The Golden Rule of API Design by Michael Feathers
  36. The Guru Myth by Ryan Brush
  37. Hard Work Does not Pay Off by Olve Maudal
  38. How to Use a Bug Tracker by Matt Doar
  39. Improve Code by Removing It by Pete Goodliffe
  40. Install Me by Marcus Baker
  41. Inter-Process Communication Affects Application Response Time by Randy Stafford
  42. Keep the Build Clean by Johannes Brodwall
  43. Know How to Use Command-line Tools by Carroll Robinson
  44. Know Well More than Two Programming Languages by Russel Winder
  45. Know Your IDE by Heinz Kabutz
  46. Know Your Limits by Greg Colvin
  47. Know Your Next Commit by Dan Bergh Johnsson
  48. Large Interconnected Data Belongs to a Database by Diomidis Spinellis
  49. Learn Foreign Languages by Klaus Marquardt
  50. Learn to Estimate by Giovanni Asproni
  51. Learn to Say “Hello, World” by Thomas Guest
  52. Let Your Project Speak for Itself by Daniel Lindner
  53. The Linker Is not a Magical Program by Walter Bright
  54. The Longevity of Interim Solutions by Klaus Marquardt
  55. Make Interfaces Easy to Use Correctly and Hard to Use Incorrectly by Scott Meyers
  56. Make the Invisible More Visible by Jon Jagger
  57. Message Passing Leads to Better Scalability in Parallel Systems by Russel Winder
  58. A Message to the Future by Linda Rising
  59. Missing Opportunities for Polymorphism by Kirk Pepperdine
  60. News of the Weird: Testers Are Your Friends by Burk Hufnagel
  61. One Binary by Steve Freeman
  62. Only the Code Tells the Truth by Peter Sommerlad
  63. Own (and Refactor) the Build by Steve Berczuk
  64. Pair Program and Feel the Flow by Gudny Hauknes, Ann Katrin Gagnat, and Kari Røssland
  65. Prefer Domain-Specific Types to Primitive Types by Einar Landre
  66. Prevent Errors by Giles Colborne
  67. The Professional Programmer by Uncle Bob
  68. Put Everything Under Version Control by Diomidis Spinellis
  69. Put the Mouse Down and Step Away from the Keyboard by Burk Hufnagel
  70. Read Code by Karianne Berg
  71. Read the Humanities by Keith Braithwaite
  72. Reinvent the Wheel Often by Jason P Sage
  73. Resist the Temptation of the Singleton Pattern by Sam Saariste
  74. The Road to Performance Is Littered with Dirty Code Bombs by Kirk Pepperdine
  75. Simplicity Comes from Reduction by Paul W. Homer
  76. The Single Responsibility Principle by Uncle Bob
  77. Start from Yes by Alex Miller
  78. Step Back and Automate, Automate, Automate by Cay Horstmann
  79. Take Advantage of Code Analysis Tools by Sarah Mount
  80. Test for Required Behavior, not Incidental Behavior by Kevlin Henney
  81. Test Precisely and Concretely by Kevlin Henney
  82. Test While You Sleep (and over Weekends) by Rajith Attapattu
  83. Testing Is the Engineering Rigor of Software Development by Neal Ford
  84. Thinking in States by Niclas Nilsson
  85. Two Heads Are Often Better than One by Adrian Wible
  86. Two Wrongs Can Make a Right (and Are Difficult to Fix) by Allan Kelly
  87. Ubuntu Coding for Your Friends by Aslam Khan
  88. The Unix Tools Are Your Friends by Diomidis Spinellis
  89. Use the Right Algorithm and Data Structure by JC van Winkel
  90. Verbose Logging Will Disturb Your Sleep by Johannes Brodwall
  91. WET Dilutes Performance Bottlenecks by Kirk Pepperdine
  92. When Programmers and Testers Collaborate by Janet Gregory
  93. Write Code as If You Had to Support It for the Rest of Your Life by Yuriy Zubarev
  94. Write Small Functions Using Examples by Keith Braithwaite
  95. Write Tests for People by Gerard Meszaros
  96. You Gotta Care about the Code by Pete Goodliffe
  97. Your Customers Do not Mean What They Say by Nate Jackson

Tunnelling Your Way Through the Government’s Metadata Retention Laws

If you happen to be fortunate enough to have access to a Unix based web server (such as an EC2 or Linode) machine) you will surprised how easy it is to safely browse the web – circumventing the Australian government’s 2 year mandatory metadata retention laws.

t_29_0You can use an SSH tunnel to use your off-site server as SOCKS Host. A SOCKS Host (or Server) is a general purpose proxy server that establishes a TCP connection to another server on behalf of a client, then routes all the traffic back and forth between the client and the server. It works for any kind of network protocol on any port. Because the connection is secure, only the client and the host can access the the data.

This is how is circumvents Government spying. The only connection your ISP can see is the initial tunnel made to the server. All the delivery of websites etc through that connection are invisible to them (and the government).

It’s tragically easy to setup, simple initiate a SSH connection with dynamic application-level port forwarding, like this:

ssh -D 12345 user@your.server.com

And then, tell your browser that you want to use a HTTP SOCKS 5 Host for proxying (Options > Advanced > Connection Settings for Firefox):

Screen Shot 2015-11-03 at 1.52.46 PMUse manual proxy configuration; set it to SOCKS v5 on the same port you specified as forwarding.

Be warned however, while your Internet traffic will be encrypted and invisible, your DNS lookups will still be public. Firefox has a setting called ‘network.proxy.socks_remote_dns’ which you can enable by browsing to the address ‘about:config’ and searching for the string above.

Lastly, be warned that browsing this way will slightly decrease speed of your browsing – but this may be a small price to pay, and may not even be noticeable.

While this is all trivial for Linux and OSX users; Windows users will need to jump through a few more hoops. This blog post inspired and references an excellent Linode Guide which covers things in more detail, and includes instructions for Windows users using Putty.

How to Install ‘therubyracer’ or ‘libv8’ gem(s) on OSX

Recently, I need to move some Rails projects I was working on to new computer and this needs me to install all the dependencies for these projects.  While using bundler to install the gems; I encountered the following error:

extconf failed, exit code 1
Gem files will remain installed in /Users/ash/.rvm/gems/ruby-2.2.1/gems/libv8-3.16.14.3 for inspection.
Results logged to /Users/ash/.rvm/gems/ruby-2.2.1/extensions/x86_64-darwin-14/2.2.0-static/libv8-3.16.14.3/gem_make.out

An error occurred while installing libv8 (3.16.14.3), and Bundler cannot continue.
Make sure that `gem install libv8 -v '3.16.14.3'` succeeds before bundling.

Fortunately, with homebrew fixing this (on OSX 10.11, El Capitan at least) worked perfectly. Simply execute these commands:

brew install v8
gem install therubyracer
gem install libv8 -v '3.16.14.3' -- --with-system-v8

Enable TRIM Support for 3rd Party SSDs in OSX El Capitan

 

There can be no doubt that the easiest way to increase the performance of an old Mac is to replace it’s hard disk with a shiny new Solid State Drive (SSD). The problem with this is that officially Apple only supports TRIM on Apple’s SSDs, effectively removing TRIM support from 3rd party SSDs. TRIM is a system-level command that allows the operating system and the drive to communicate about which areas of the drive are considered unused and thus ready to be erased and rewritten to. In the absence of TRIM, users can see significantly slower drive writes as the drive begins to fill up.

There are tolls people have written to try and get TRIM working for 3rd Party drives, but luckily, with El Capitan, Apple is relaxing the reins and allowing TRIM to be re-enabled for 3rd party SSDs.

To do this, simply open Terminal and execute the command:

sudo trimforce enable

Apple does give you a warning:

This tool force-enables TRIM for all relevant attached devices, even though they have not been validated for data integrity while using that functionality. By using this tool to enable TRIM, you agree that Apple is not liable for any consequences that may result, including but not limited to data loss or corruption.

Screen Shot 2015-10-31 at 1.21.06 AM

…but after a reboot (the utility will prompt you) TRIM should be enabled and you’ll be good to go.

As always: Proceed at your own risk. Enjoy.