Connect to remote postgreSQL database using Ruby

Ever wrote a script, meaning you needed to connect to a remote PG database using PORO (plain old ruby object)?

This can be quite tricky as I found out. Here is a how to.

Let’s say you are parsing some page on the interwebs.

require 'nokogiri'
require 'open-uri'
require 'pg'

@doc = Nokogiri::HTML(open("http://www.some-url.com/products"))

attributes = @doc.css("td[class=some class] a @href")

attrArray = attributes.map { |attr| attr.value}


@newDocArray = Array.new
@nameArray = Array.new

attrArray.each_with_index do |link, index|
 newDoc = Nokogiri::HTML(open("http://www.some-url.com/products/#{link}"))
 
 nameDoc = newDoc.css("div h1")
 newDoc = newDoc.xpath("//div[@class='cardpage']")
 
 newDoc.search("form").remove
 newDoc.search("a").remove

 @nameArray << nameDoc.map { |attr| attr.inner_text}[0]
 @newDocArray << newDoc.map { |attr| attr.inner_text}[0]

end

Now you need to prepare you postgres config on the remote machine to allow connections from your current local machine (per default postgres allows only localhost connections, meaning from remote machine to itself).

There is an amazing tutorial on this page – http://www.cyberciti.biz/faq/postgresql-remote-access-or-connection/

I will sum it up:

Edit $ nano /var/lib/pgsql/data/pg_hba.conf It might be in some other different folder. If that is the case, just search for “pg_hba.conf” – find / -name pg_hba.conf

Now search for your own ip and paste it in pg_nba.conf

host all all 192.168.1.0 255.255.255.0 trust

(Replace 192.168.1.0 with your obtained ip. I left DNS mask unchanged.

Now find and edit $ nano /var/lib/pgsql/data/postgresql.conf

Changing this value – tcpip_socket = true . Now comes the important part – if you don’t find any tcpip_socket value, then DON’T add it! Instead search for

# – Connection Settings –

listen_addresses = ‘*’
port = 5432

And change listen_addresses = ‘localhost’  to listen_addresses = ‘*’ .

That’s it! Restart # /etc/init.d/postgresql restart postgreSQL and try connecting $ psql -h 82.102.42.56 -U username -d database where:

-h 82.102.42.56 is the remote server ip where PG db is running
-U username is the username of the PG user
-d database is the database name

Now we’re all ready to rock and roll!

@conn = PGconn.connect("82.102.42.56", 5432, '', '', "my_database", "my_user", "my_topsecret_password")

def prepareInsertProduct
    	@conn.prepare("insert_product", "insert into products (name, description,) values ($1, $2)")
	end

def addProduct(name, description)
    @conn.exec_prepared("insert_product", [name, description])
end

def connect(arrayContent, arrayNames)

	prepareInsertProduct

	length = arrayContent.length

	arrayContent.each_with_index do |contentValue, index|		
		name = arrayNames[index]
		description = contentValue

		addProduct(name, description)

		print "Processing entry - #{index}"
		print "\r"
	end

end

connect(@newDocArray, @nameArray)

 

 

Eve-trader.net – the new source for EVE Online productioneers

Greetings, capsuleers!

Few days ago I launched my long time dreamed project – eve-trader.net (http://eve-trader.net). This services is intended to be a lightweight alternative to updating your excel sheets daily with current prices (most likely you will get them from eve-central.com). Via the eve-central.com API I fetch the prices on requested blueprints, calculate for you the cheapest material prices and suggest where you can sell the end product most expensive.

Settings and assumptions as of today:

  • Areas considered for calculation are all solar systems within 5 jumps around the five main trade hubs (which currently are – Jita, Amarr, Rens, Dodixie, Hek) and which are high sec
  • All skills are lvl 5 (which you probably should have if you considering make money with industry)
  • No fees and taxes are calculated (if they are hindering you to make profit you are producing the wrong thing)

The service is in it’s early stage, so be prepared to encounter some changes in near future. What is planned:

  • Having at least a few options available in an “expert” menu
  • News section
  • Implement user accounts where user can save their blueprints, thus, having a faster access to their favourite blueprints
  • Having a history saved for each blueprint.
  • A suggestions feature, where users can suggest new things and other users can vote on them.
  • Making market predictions based on Monte-Carlo-Simulation or Expected Shortfall methods.

Properly create and restore your development postgreSQL on production

So maybe you are like me, who is parsing a lot of static data into a development database, make an app around this data and then you don’t want to have an empty database on production, but instead you want to have a full copy of your development database.

After spending a few hours trying to make it work, there is one solution, that yielded exactly 0 errors (yay!)

On your local machine (in the shell, wherever you currently are) :

pg_dump --no-owner your_db_develoment > backup.bak

Then put it in some safe place on your remote machine:

scp backup.bak deploy@192.168.111.222:/home/restore/backup.bak

Make a backup of your production database for safety sake (on your production server, of course)

pg_dump --no-owner your_db_production > /home/backups/04-03-2016/backup.bak

If there is no production database yet on the server, proceed with creating it, elsewise you might need (if the app has users) to cut the current sessions, so check these out:

http://stackoverflow.com/questions/12924466/capistrano-with-postgresql-error-database-is-being-accessed-by-other-users

or

http://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server?rq=1

Ok, now we have no database on production machine at all. Let’s create new production database:

postgres=# CREATE DATABASE database_production;
CREATE DATABASE

postgres=# CREATE USER production_db_user WITH password 'qwerty';
CREATE ROLE

postgres=# GRANT ALL privileges ON DATABASE database_production TO production_db_user;
GRANT

Make sure to populate the password with the same password, as set up in database.yml for production user.

That’s it! No need to change the owner. Now let’s restore the backup file:

sudo psql -d mydb_production -U production_user < /home/deploy/restore/backup.bak;

Password for user production_user: 
SET
SET
SET
SET
SET
SET
CREATE EXTENSION
ERROR:  must be owner of extension plpgsql
SET
SET
SET
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
CREATE SEQUENCE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 14964
 setval 
--------
 114041
(1 row)

COPY 3151
 setval 
--------
   3236
(1 row)

COPY 28011
 setval 
--------
   7081
(1 row)

COPY 8
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
ALTER TABLE
ALTER TABLE

As of now I have no issues regarding the one error above.

All hail Gaben… ehm optimization!

Before:

Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.534104)
Benchmarking parseQuickLook:
       user     system      total        real
   0.020000   0.000000   0.020000 (  0.610587)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.422812)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.405687)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.396164)
Benchmarking parseQuickLook:
       user     system      total        real
   0.010000   0.000000   0.010000 (  0.371604)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.411998)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.010000   0.010000 (  0.387384)
Benchmarking parseQuickLook:
       user     system      total        real
   0.020000   0.000000   0.020000 (  0.673446)
Recommendations report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000076)
Production cost report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.007484)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.418086)
Benchmarking parseQuickLook:
       user     system      total        real
   0.020000   0.000000   0.020000 (  0.446162)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.392303)
Benchmarking parseQuickLook:
       user     system      total        real
   0.010000   0.000000   0.010000 (  0.390188)
Benchmarking parseQuickLook:
       user     system      total        real
   0.010000   0.000000   0.010000 (  0.412362)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.427071)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.627583)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.402546)
Benchmarking parseQuickLook:
       user     system      total        real
   0.010000   0.000000   0.010000 (  0.434892)
Recommendations report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000102)
Production cost report: 
       user     system      total        real
   0.010000   0.000000   0.010000 (  0.001292)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.418595)
Benchmarking parseQuickLook:
       user     system      total        real
   0.020000   0.000000   0.020000 (  0.413026)
Benchmarking parseQuickLook:
       user     system      total        real
   0.010000   0.010000   0.020000 (  0.400815)
Benchmarking parseQuickLook:
       user     system      total        real
   0.010000   0.000000   0.010000 (  0.409537)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.464621)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.410090)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.393017)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.442389)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.412018)
Recommendations report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000064)
Production cost report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.004622)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.408888)
Benchmarking parseQuickLook:
       user     system      total        real
   0.010000   0.010000   0.020000 (  0.417567)
Benchmarking parseQuickLook:
       user     system      total        real
   0.010000   0.000000   0.010000 (  0.461256)
Benchmarking parseQuickLook:
       user     system      total        real
   0.010000   0.000000   0.010000 (  0.410171)
Benchmarking parseQuickLook:
       user     system      total        real
   0.010000   0.000000   0.010000 (  0.405976)
Benchmarking parseQuickLook:
       user     system      total        real
   0.010000   0.000000   0.010000 (  0.396160)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.436136)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.400430)
Benchmarking parseQuickLook:
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.394962)
Recommendations report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000034)
Production cost report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000094)

After optimization:

Recommendations report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000046)
Production cost report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.010514)
Recommendations report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000043)
Production cost report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000584)
Recommendations report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000030)
Production cost report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000774)
Recommendations report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000064)
Production cost report: 
       user     system      total        real
   0.000000   0.000000   0.000000 (  0.000251)

Hashtag “inlovewithruby” 🙂

From 14.727 seconds to 0.012306 seconds, that’s 1196 times increase.

Rails eShop – my first big project using Ruby on Rails

[readolog_dropcap ]H[/readolog_dropcap]appy new Rails year, fellow riders!

 

I finished my very first “serious” project – yay! It looks and behaves like a rails eShop (you can find link further down below). Before I go into technical details some intro – this website was created for friends of mine and although it was kind of experimental for me (first real app), it turned out to be quite decent as one might say so.

As with every customer-oriented project, there was a lot of JavaScript involved, which is by far not my strongest language. It started calm and nicely with pure ruby on rails, I could use a lot of stuff I learned from this book – Agile Web Development With Rails. But then… as more and more wished appeared I realized that there is no way around some front-end development.

Enough smalltalk, let’s get straight to the data:

  • Bootstrap Theme used – “Minimal
  • Notable gems: figaro (managing global variables), devise (users), carrierwave (images uploads), sendgrid (for sending any kinds of mails)
  • Database: postgresql (I think it’s pretty a must-have for shops because of hstore – which, as far as I understand makes postgres almost non-relational, because hstore columns allow to store hash/value pairs)
  • Deployment: capistrano, puma, nginx (you can check out my article about deployment here)
  • Other features: protected admin namespace

[readolog_dropcap ]M[/readolog_dropcap]ost notable code

 

JavaScript – a calculator for end value of a product based on amount, delivery method and size (plus additional services, which are to be differentiated between one for each product and one for a full order). There is some hard coded stuff and of course this piece of code might not be the state of the art, however it works pretty well.

$(document).ready ->

    if $('a.size_input').length != 0
        $('a.size_input')[0].click()

jQuery ->
    $('li a').click (event) ->
        # event.preventDefault()
        $('a').removeClass('active')
        $(this).addClass('active')

    $('form').on 'click', '.add_fields', (event) ->
    	time = new Date().getTime()
    	regexp = new RegExp($(this).data('id'), 'g')
    	$(this).before($(this).data('fields').replace(regexp, time))
    	event.preventDefault()

    get_multiplier = ->
        amount = $('#amount').val()
        multiplier = 1.5
        multiplier = switch
            when amount <= 99 then multiplier = 1.5
            when amount >= 100 and amount < 300 then multiplier = 1.4
            when amount >= 300 and amount < 500 then multiplier = 1.3
            when amount >= 500 and amount < 1000 then multiplier = 1.25
            when amount >= 1000 and amount < 3000 then multiplier = 1.23
            when amount >= 3000 and amount < 7000 then multiplier = 1.2
        return multiplier

    get_delivery = ->
        delivery = $('#delivery').val()
        return parseFloat(delivery).toFixed(2)

    get_size = ->
        # in this method we either select the price value based on the flash
        # drive size, or, if it has a basic price, just simply the basic price
        # from the hidden #basicprice id

        selected_size = $('input[name=volume]:checked').val()
        if (selected_size == undefined)
            return parseFloat($('#basicprice')[0].innerText)
        else
            array = selected_size.split(",")[1].replace(']','')
            return parseFloat(array).toFixed(2)

    get_all_addservices = ->
        # This method delivers all checked addservice price
        # so we can first substract it before applying any multiplications
        addservices = new Array()
        addservicesParty = new Array()

        console.log('inside get all addservices:')
        checkboxes = document.getElementsByName("addservices[]")
        
        for checkbox in checkboxes

            addserviceprice_splitted = (checkbox.value).split(" ")
            # console.log(addserviceprice_splitted[2])
            
            # Here we need to check if the 'apply to whole party is true or false'
            party = addserviceprice_splitted[2]
                

            addserviceprice = parseFloat(addserviceprice_splitted[1]).toFixed(2)
            if checkbox.checked
                parsedPrice = parseFloat(addserviceprice)

                if party == 'true'
                    addservicesParty.push(parsedPrice)
                else
                    addservices.push(parsedPrice)

        if addservices.length > 0
            sum = addservices.reduce((a,b) => a+b)
        else
            sum = 0

        if addservicesParty.length > 0
            sumParty = addservicesParty.reduce((a,b) => a+b)
        else
            sumParty = 0

        console.log('Addservices combined: ' + addservices + '| Addservice to whole party: '+ addservicesParty)
        return [parseFloat(sum), parseFloat(sumParty)]

    recalculate = ->
        # This function recalculates the price
        base = get_size()
        deliveryCoefficient = get_delivery()
        amountCoefficient = get_multiplier()
        addservices = get_all_addservices()

        addserviceOne = addservices[0]
        addserviceParty = addservices[1]

        amount = $('#amount').val()

        console.log('base - ' + base + '| delivery - ' + deliveryCoefficient + ' | add service sum - ' + addservices + ' | amount coefficient - ' + amountCoefficient)

        endPriceOne = (base*deliveryCoefficient*amountCoefficient) + addserviceOne
        endPriceParty = (base * deliveryCoefficient * amountCoefficient) * amount
        endPriceAll = endPriceOne * amount + addserviceParty
        endPriceServices = (addserviceOne * amount) + addserviceParty

        $('#priceValue').text(endPriceAll.toFixed(2))
        $('#priceForOne').text(endPriceOne.toFixed(2))
        $('#priceForAll').text(endPriceParty.toFixed(2))
        $('#priceForAddservices').text(endPriceServices.toFixed(2))

    $('.size_input').click (event) ->
        recalculate()
    
    $('.addservice_checkbox').change (event) ->
        recalculate()
        

    $('#amount').on('input', (event) ->
        recalculate()
        )

    $('#delivery').change (event) ->
        recalculate()

    $("#thediv").click (event) ->
        $("#addservices").toggleClass("reveal-closed").toggleClass("reveal-open")

    $('#load_more_btn').click (event) ->
        event.preventDefault()

Here we have some fancy helper methods which I grabbed from Ryan Bates screencasts. They allow you to add fields on the go:

module ApplicationHelper


	def hidden_div_if(condition, attributes = {}, &block)
		if condition
			attributes["style"] = "display: none"
		end
		content_tag('div', attributes, &block)
	end

	def link_to_add_fields(name, f, association)
		new_object = f.object.send(association).klass.new
		id = new_object.object_id
		fields = f.fields_for(association, new_object, child_index: id) do |builder|
			render(association.to_s.singularize + "_fields", f: builder)
		end
		link_to(name, '#', class: 'add_fields', data: {id: id, fields: fields.gsub("\n", "")})
	end

[readolog_dropcap ]L[/readolog_dropcap]ast, but not least

 

we have of course a fully functional rails eShop with a working cart, order placement, sending confirmation letters to customers and admins, an administration panel for managing products etc. etc.

Unfortunately the owners decided to put this page on ice, therefore it’s unlikely that you will receive some flash drives. However you can check the page out all by yourself – http://souvenirnya-produktsiya.ru/

 

Deploying your Rails app on a VDS

Recently I finally finished development of my very first serious app, which was ready to be deployed on a production machine. This, however, turned out to be trickier than anticipated. I’ve read myself into various tutorials, decided to spin up a droplet on digitalocean with preinstalled Unicorn + Nginx bundle. While this droplet works well with the initial app I had a hard time trying to make

a) multiple sites work

b) any site but the default work

I surely don’t want to assume that unicorn is somewhat hard to handle, it may have been just me, who is unable to accomplish such a task. On the other hand I found that using puma instead of unicorn is far more profitable.

I grabbed this outstanding tutorial – https://www.digitalocean.com/community/tutorials/deploying-a-rails-app-on-ubuntu-14-04-with-capistrano-nginx-and-puma and was able to get the whole app running on production within few hours.

Don’t forget to install ImageMagick sudo apt-get install imagemagick if you suddenly discover, that there are no image uploads on production. Check out this article too – https://chuanhesmile.wordpress.com/2014/12/13/issue-fixed-rollback-transaction-when-uploading-images-using-carrierwave/

This is important because there might be no errors at all and it did cost me a lot of time to figure that out on my own.

My last advise – check out how to create a memory swap because that’s one of the first issues I ran into while deploying my application (I’m using the 5$ droplets with 512 MB RAM for all my projects).

The beautiful world of hstore (PostgreSQL)

In my recent project I stumbled across the need to store, let’s say an array of elements (in my app a user can add to this to-be-purchased item additional services. Therefore I need to somewhere store those additional services). One idea might be to create an additional model and connect cart_items with additional services through some “additionalservization” join table.

However this is the man’s rails world and things are meant to be easy. And here comes the hstore – awesome extension for your PostgreSQL which allows you to store hash values.

Let’s start simple

Log in into your postgreSQL:

psql
\list
\connect app_database_development

this leads to:
=> psql (9.4.1, server 9.4.2)
You are now connected to database "app_development" as user "user".

app_development-# CREATE extension hstore;

That’s it, now your database supports this extension.

Now it’s time to generate the migration:

rails g migration AddDataToLineItems addservices:hstore
rake db:migrate

Now jump right into rails console:

2.2.0 :004 > @li = LineItem.last
2.2.0 :005 > @li.addservices = {first: 4, second: 'one', third: 'some more text'}
 => {:first=>4, :second=>"one", :third=>"some more text"}

Wasn’t that easy and awesome?

Beautiful strings

Today I found an interesting challenge on CodeEval – it’s from Facebook Hacker Cup 2013 Hackathon. At first I had my issues with understanding the task. Here’s the description:

[readolog_blockquote ]Given a string s, little Johnny defined the beauty of the string as the sum of the beauty of the letters in it. The beauty of each letter is an integer between 1 and 26, inclusive, and no two letters have the same beauty. Johnny doesn’t care about whether letters are uppercase or lowercase, so that doesn’t affect the beauty of a letter. (Uppercase ‘F’ is exactly as beautiful as lowercase ‘f’, for example.)

You’re a student writing a report on the youth of this famous hacker. You found the string that Johnny considered most beautiful. What is the maximum possible beauty of this string?[/readolog_blockquote]

Your input are few lines of senseless strings. Output should be a number, a sum of values for every letter. In other words this challenge can be described as following:

[readolog_blockquote ]Count encounters of every letter. Since you can use every number only once and your goal is to produce the biggest possible sum you should assign the bigger numbers to letters, which occur most often.[/readolog_blockquote]

If there are letters with same presence amount it makes no difference to which you assign the exact number. Let’s say you have three ‘a’, three ‘b’ and three ‘c’. If you decrease your assignment by one each time it won’t matter to which exact letter you’ll assign exact number, since the math is 3*26 + 3*25 + 3*24 and it doesn’t care to which letter ‘belongs’ the number ‘3’.

So here is my code. I’m sure it can be done in a one-liner, however I wouldn’t consider this challenge as trivial, therefore I’m a bit proud of my solution.

def largestHashKey(hash)
  hash.max_by{|k,v| v}
end

def letter?(lookAhead)
  lookAhead =~ /[[:alpha:]]/
end

File.open("beautifulStrings.txt").each_line do |line|
	charFrequency = Hash.new {0}
	line.split("").each do |char|
		if letter?char then charFrequency[char.downcase] +=1 end
	end
	# Now iterate through all hash values, find maximum, assign 26 to it, then maximum-1, assign 25 to it and so on

	i = largestHashKey(charFrequency)[1]
	sum = 0
	stringValue = 26

	while i>0
		if charFrequency.key(i)
			charFrequency.each_value do |value|
				if value == i
					sum += i*stringValue
					stringValue -= 1
				end
			end
		end
		i -=1
	end
	puts sum
end