Saturday, August 14, 2010

Wildcard on Postgresql Full Text Search

Note these two characters: :*

select x.y, to_tsvector('english',x.y) @@ to_tsquery('english','quic:*') as match
from (values
('The quick brown fox jumps over the lazy dog')) as x(y)

Output:
y                                            | match
---------------------------------------------+-------
 The quick brown fox jumps over the lazy dog | t
(1 row)

Setting RadGrid's ConfirmText programatically

var gbc = grdCategory.MasterTableView.GetColumn("uxDelete") as GridButtonColumn;
bc.ConfirmText = "Are you sure you want to delete??";

Tuesday, August 10, 2010

Cookie to the rescue

Turns out that the solution for preventing the PanelItem from collapsing as suggested from Telerik Knowledge Base is not working


The following works:
protected void Page_Load(object sender, EventArgs e)
{
    RadPanelBar1.PersistStateInCookie = true;
}

Monday, August 9, 2010

Simpler way to get the selected RadGrid row's primary key and description(or other fields)

Much simpler than SelectedIndexChanged

protected void grdCategory_ItemCommand(object source, Telerik.Web.UI.GridCommandEventArgs e)
{
    if (e.Item is GridDataItem)
    {                
        var item = (GridDataItem)e.Item;
        TextBox1.Text = item["category_description"].Text;         
        TextBox2.Text = item.GetDataKeyValue("category_id").ToString();
    }
}

Attaching javascript to a TextBox in ASP.NET

TextBox2.Attributes.Add("onclick","alert('hello')");

Retrieving selected row's key from Telerik RadGrid

This approach easily gets old:

TextBox1.Text = grdCategory.SelectedItems[0].OwnerTableView.DataKeyValues[grdCategory.SelectedItems[0].ItemIndex]["category_id"].ToString();

To make retrieving of key simpler for single select and single key on RadGrid, make an extension method for it:

public static class Helper
{
    public static object SingleSelectKeyValue(this Telerik.Web.UI.RadGrid rg)
    {
        return rg.SelectedItems[0].OwnerTableView.DataKeyValues[rg.SelectedItems[0].ItemIndex][rg.MasterTableView.DataKeyNames[0]];
    }
}

To use:

protected void grdCategory_SelectedIndexChanged(object sender, EventArgs e)
{
    TextBox1.Text = grdCategory.SingleSelectKeyValue().ToString();
}

Postgresql recognizing functional dependency on primary keys for GROUP BY is plain awesome!

This is how I write a query like this...

SELECT
    p.id,
    p.firstname, p.lastname, p.address,
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id, p.firstname, p.lastname, p.address;

...when it involves a GROUP BY:

SELECT
    p.id,
    p.firstname, p.lastname, p.address, -- these auxiliary fields is helpful to program users
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id
    ,p.firstname, p.lastname, p.address; -- these ancillary fields aids the RDBMS on preventing
    -- programmers from accidentally committing the same mistake as MySQL programmers.
    -- see the Mysql code near the bottom of this post.


Notice the p.firstname, p.lastname and p.address are wrapped to new line. I do that to emphasize that the system is semantically grouping person's record(s) based on its ID, not on its firstname, lastname nor address.

Starting from Postgres 9.1, we can now do this...

SELECT
    p.id,
    p.firstname, p.lastname, p.address, -- these auxiliary fields is helpful to users
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.id


-- the ancillary fields(i.e. fields from the same table of primary key) are not needed anymore 
-- when you group on a primary key field with Postgres 9.1. Primary key field is sufficient enough 
-- for Postgres 9.1 that you are grouping the entity on its internal representation(i.e. primary key) 

Notice that we can omit the additional fields p.lastname, p.firstname, p.address on GROUP BY clause. Postgres 9.1 can now infer that we are grouping by primary key. That's a nice capability added to Postgres, we can now avoid SQL design pattern for GROUP BY, i.e. wrapping other fields on new line when we are not semantically grouping on those ancillary fields. Though you can still add the p.lastname, p.firstname, p.address fields on Postgres 9.1's GROUP BY primary key, you can hardly find a need for doing so, especially if the grouped entity has a gazillion fields to be presented to program users.

And since it only recognize primary key on GROUP BY, this will not work on Postgres and any sane RDBMS

SELECT
    p.firstname,
    p.lastname,
    count(*)
FROM
    people p
    JOIN visits v ON p.id = v.person_id
GROUP BY p.firstname

That will not work, as people can have multiple lastname sharing a given firstname. But that will work on MySQL, which is really bad, as it will just randomly select any lastname from the grouped firstname.

To prove how absurd MySQL design choice(or lack thereof) is, this query...

SELECT region, name, COUNT(*) 
FROM bbc 
GROUP BY region



...produces:


regionnameCOUNT(*)
AfricaAngola47
AmericasAntigua and Barbuda20
Asia-PacificAustralia36
EuropeAlbania48
Middle EastAlgeria19
North AmericaCanada3
South AmericaArgentina12
South AsiaAfghanistan8


MySQL will allow that absurd query, and produce results. While other sane RDBMS like Postgresql, Sql Server, Oracle, etc will prevent that absurd scenario.

If you want to COUNT how many people has similar firstname, you must put firstname only on GROUP BY's clause and firstname only on SELECT clause too; if you really want to COUNT people with similar firstname+lastname, you must group on both firstname+lastname.

To test that query on other database visit sqlzoo.net

Postgres really has saner design choices than other RDBMSes. It's really awesome!


Full article here: http://www.depesz.com/index.php/2010/08/08/waiting-for-9-1-recognize-functional-dependency-on-primary-keys/

Interesting: Auxiliary vs Ancillary

WCF Oddities

when returning DataTable() from WCF, using this...


var dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
return dt;

...will result to:

Saturday, August 7, 2010

Postgresql dblink

How to use dblink in Postgres on Windows.

First, get the dblink.sql from here...

C:\Program Files (x86)\PostgreSQL\8.4\share\contrib

...then execute it in pgAdmin.


Sample query:

select * 
from 
   dblink('dbname=test user=postgres password=opensesame',
      'select lname, fname, addressline from customer') 
      as customer(lname text, fname text, addressline text)

Friday, August 6, 2010

Fairly complex query for running balance

with tx as
(
 select null as transaction_date, null as date_time_encoded, 1 as balance_effect, 'Opening Balance' as category, '' as memo, opening_balance as amount
 from account_with_type_opening_balance
 where (account_id,account_type_id) = ('{3F52FED8-90D1-4249-A04E-42AB49D118F0}', '{960B8DAE-1C4C-432C-96E6-33974B6B2B19}')

 union all

 select ws.date_withdrawn_spent, ws.date_time_encoded, -1, ac.account_category_full_description, ws.memo, ws.amount
 from withdrawn_spent ws
 join account_category ac on ac.account_category_id = ws.account_category_id
 where (drawn_from_account_id,drawn_from_account_type_id) = ('{3F52FED8-90D1-4249-A04E-42AB49D118F0}', '{960B8DAE-1C4C-432C-96E6-33974B6B2B19}')

 union all

 select ws.date_withdrawn_spent, ws.date_time_encoded, 1, 
  ac.account_category_full_description 
  || ' FX SOURCE-->' || a.account || ':' || c.currency || '(' || atx.account_type ||  ') ' 
  || to_char(ws.amount, 'FM9,999,999.90') || ' ' || c.currency_symbol 
  || ' #' || to_char(ws.exchange_rate, 'FM9,999,999.90'), 
  ws.memo, ws.transferred_to_amount
 from withdrawn_spent ws
 join account_category ac on ac.account_category_id = ws.account_category_id
 join account a on a.account_id = ws.drawn_from_account_id
 join currency c on c.currency_id = a.currency_id
 join account_type atx on atx.account_type_id = ws.drawn_from_account_type_id
 where (transferred_to_account_id,transferred_to_account_type_id) = ('{3F52FED8-90D1-4249-A04E-42AB49D118F0}', '{960B8DAE-1C4C-432C-96E6-33974B6B2B19}')


 union all

 select dr.date_deposited_received, dr.date_time_encoded, 1, ac.account_category_full_description, dr.memo, dr.amount
 from deposited_received dr
 join account_category ac on ac.account_category_id = dr.account_category_id
 where (received_in_account_id,received_in_account_type_id) = ('{3F52FED8-90D1-4249-A04E-42AB49D118F0}', '{960B8DAE-1C4C-432C-96E6-33974B6B2B19}')
 
 union all

 select fx.date_transferred, fx.date_time_encoded, 1, 
  'FX SOURCE-->' || a.account || ':' || c.currency || '(' || atx.account_type || ') ' 
  || to_char(fx.from_amount,'FM9,999,999.90') || ' ' || c.currency_symbol 
  || ' #' || to_char(fx.exchange_rate, 'FM9,999,999.90'), 
  fx.memo, fx.to_amount
 from fund_transfer fx
 join account a on a.account_id = fx.from_account_id
 join currency c on c.currency_id = a.currency_id
 join account_type atx on atx.account_type_id = fx.from_account_type_id
 where (fx.to_account_id,fx.to_account_type_id) = ('{3F52FED8-90D1-4249-A04E-42AB49D118F0}', '{960B8DAE-1C4C-432C-96E6-33974B6B2B19}')


 union all

 
 select fx.date_transferred, fx.date_time_encoded, -1, 
  'FX DEST-->' || a.account || ':' || c.currency || '(' || atx.account_type || ') ' 
  || to_char(fx.to_amount,'FM9,999,999.90') || ' ' || c.currency_symbol 
  || '#' || fx.exchange_rate, 
  fx.memo, fx.from_amount
 from fund_transfer fx
 join account a on a.account_id = fx.to_account_id
 join currency c on c.currency_id = a.currency_id
 join account_type atx on atx.account_type_id = fx.to_account_type_id
 where (fx.from_account_id,fx.from_account_type_id) = ('{3F52FED8-90D1-4249-A04E-42AB49D118F0}', '{960B8DAE-1C4C-432C-96E6-33974B6B2B19}')

),
sequenced as
(
 select 
  transaction_date, date_time_encoded, category, memo,
  amount,
  balance_effect,
  row_number() over(order by transaction_date nulls first, date_time_encoded) as seq
 from tx
)
select  
 transaction_date, date_time_encoded, category, memo,
 case when balance_effect = -1 then '-' else '+' end as effect, 
 amount, 
 sum(amount * balance_effect) over(order by seq) as balance
from sequenced
-- order by seq desc -- uncomment this if you want to see it from most recent to least recent
limit 30 offset 0