Tuesday, September 14, 2010 

Find the unused selectors in a webpage.

Often was facing a problem with slow page loading and CSS contributed to it. Found this incredible tool from site point. http://www.sitepoint.com/dustmeselectors/ui/ now you can find the unused selectors in your site, the tool spits out the same in csv format. So cool.

Monday, September 06, 2010 

Installing missing dependencies redhat / fedora / centos

When you try to install a rpm on the fedora/centos. It sometimes says
missing dependencies. Earlier I used to try "Yum install
<dependencyname>" which most of the time does not used to work.
Today I discovered that it is not because they are not available but
they are provided as a bundle with some other package. Hence the
correct way is to do "yum provides <dependencyname>" which would then
list the actual package that you need to install to get this

Edit: Even simpler is to do "yum localinstall  " the dependencies are auto installed by yum 

Thursday, September 02, 2010 

Query to delete rows with duplicate columns.

Say you have table1 with field_name which is having duplicate records, and if you want to delete rows with duplicate columns, you can use the below query.

Assumes that you have a unique id, ID and the duplicate row is field_name.

delete from table1
USING table1, table1 as vtable
WHERE (table1.ID > vtable.ID)
AND (table1.field_name=vtable.field_name)


Mysql do bulk operations on tables, columns, information schema & Export to CSV

Bulk operations:
I had used the below script to bulk rename mysql tables from lowercase to all upper case. And it worked like charm.

The same concept can be used to do any other like column renaming, table renaming any of the alter table commands.

SELECT 'USE yourdbname;'
SELECT CONCAT("ALTER TABLE ",`table_name`," RENAME AS ",upper(`table_name`),";") from information_schema.`TABLES` where `table_schema` = 'yourdbname' INTO OUTFILE '/tmp/result.txt';

SOURCE  '/tmp/result.txt';

If you are using query browser, you might try using "LOAD DATA LOCAL INFILE"

CSV Export:
Given a query such as
SELECT order_id,product_name,qty FROM orders

The below query would create a csv file with the order_id, product_name, qty.
SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'

Further to add a heading row in the csv
SELECT 'order_id','product_name','qty'
SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'

Out put format.
"1","Tech-Recipes sock puppet","14.95"
"2","Tech-Recipes chef's hat","18.95"

Keep in mind that the output file must not already exist and that the user MySQL is running as has write permissions to the directory MySQL is attempting to write the file to.

Found part of this interesting code to export data as csv here.