Jul 25

We have been using  PostgresSQL  in our new product Quiz Stack.

Quiz Stack is a SAAS based  product. Which can  conduct,manage,analysis quizzes.We are using rails and postgresql as our backend.It’s subdomain app and hence we decide to use postgresql schema based approach for storing/querying data.

While working on one of the feature It was needed to draw some random fixed questions for each user.So each user has different order of questions which  is maintain for each specific user.

In Mysql it’s called field function to achieve this ,unfortunately there isn’t  anything as of I know in PostgresSql or is it ? :)

Well we can obviously write a function which can help you do the same.Let’s the function name be sortbyid. 

Following code snippet is a function definition

CREATE OR REPLACE FUNCTION sortByID(anyelement, VARIADIC anyarray) RETURNS BIGINT AS $$
				SELECT n FROM (
   			 	SELECT ROW_NUMBER() OVER () AS n, x FROM unnest($2) x)
   			 	 numbered WHERE numbered.x = $1;
			$$ LANGUAGE 'SQL' IMMUTABLE STRICT;

So here how I get the result from Question table  for 3 record with order or id as 2,1,3

Continue reading »

Tagged with:
Nov 18

In my recent work i have face some difficulties for generating different report for different organization or client using same application,because every client have their own reporting format.

Like SAAS based application if it is generate some MIS report and if client require some more or less information depending upon his requirement we can give some editable facilities to edit and generate report.

So to fulfill this kind of requirement user editable report which contains Markup + ruby code for reporting purpose.
I am using liquid ,acts_as_flying_saucer,rchart,textile/Html for generating report.

Continue reading »

Tagged with:
May 01

In this article i have just taken a short example describing vertical table,and its implementation using rails.

When table has no fixed column it has dynamic fields .data is not stored on separate column but data stores in vertical fashion.

For implementing vertical table table has mainly 2 columns key and value pair.key is like database column name and value contains actual data.

Lets take an e.g

Consider website in which it contains various products and its comparison ,searching and many more features.product can be mobile,car etc.Product has many items.item may be nokia 6600,nokia n72 etc.

Here our aim is not compare aur search items.Here our main aim is how vertical table concept is applied.Suppose we have to build only single product comparison site then each product has it’s own attributes that can be easily build with adding column for each product.

we are developing generic site(multiple product).so there is no fixed attributes,each product has it’s own attributes either we create different model for each product or we can proceed with vertical table concept.

Vertical table

  • Product has many Product Attributes.
  • Product has many Items.
  • Item has many Item Attributes.
  • Item Attribute belongs to Product Attribute.

Product Attribute mainly requires 3 fields

name : which is used for key in vertical table(i.e column name)

option_type : which is used for viewing different form of data when item is created .(textbox,dropdown,checkbox,multi option)

option : which contain option if its drop down or multi option(serialize)

Item has only name field which is optional.

Item attribute mainly required 3 fields

name : key name or column name

value : actual value

item_id : foreign key of item

It is used where table has dynamic fields,and it only concern with data.

Here data grows vertically.

It has mainly key ,value pair.

Source code of above example is hosted on github http://github.com/amardaxini/vertical_table_demo

For More info about vertical table you can visit http://weblogs.foxite.com/andykramek/archive/2009/05/03/8369.aspx

Tagged with:
preload preload preload