csv export with php

Posted by sergey - 05/03/11 at 01:03:27 pm

headers for exporting data as csv file download.

header(”Content-type: application/x-msdownload;charset=utf-8″);
header(”Content-Disposition: attachment; filename=name_”.date(’d.m.Y.G.i’).’.’.$type);
header(”Pragma: no-cache”);
header(”Expires: 0″);
echo pack(”CCC”,0xef,0xbb,0xbf); // bom to tell fucking Excel that its utf8

sshfs

Posted by sergey - 30/09/10 at 09:09:35 pm

Installation:

aptitude install sshfs

chmod +x /dev/fuse

mount:

sshfs -o allow_root -o cache=no root@host:/ /mnt/dir/

Cybersecurity

Posted by sergey - 02/03/10 at 09:03:03 pm

Get list of all foreign key constraints.

Posted by sergey - 05/02/10 at 02:02:47 pm

Here is the way i do this -

SELECT conname,
       t.relname AS TABLE,
       tf.relname AS ftable,
       a.attname AS field,
       af.attname AS ffield
FROM pg_constraint c,
     pg_attribute a,
                   pg_attribute af,
                   pg_stat_user_tables t,
                   pg_stat_user_tables tf
WHERE c.contype='f'
  AND c.conkey[1]=a.attnum
  AND c.conrelid=a.attrelid
  AND c.confkey[1]=af.attnum
  AND c.confrelid=af.attrelid
  AND t.relid=c.conrelid
  AND tf.relid=c.confrelid

Multithread url fetch using PHP.

Posted by sergey - 04/01/10 at 09:01:49 pm

To be honest, that’s not a good idea to use fork in php since it eats memory very aggressively. However, in some situations this can be really useful or can be a temporary solution.

Here is an example of using php function pcntl_fork:


$urls = file('urls.txt');
foreach ($urls as &$url) {

$pid=pcntl_fork();
if ($pid == -1) {
die('could not fork');
}
elseif ($pid)
{
}
else
{
// child
file_put_contents(mt_rand(1, 999999), file_get_contents($url));
exit;
}
}

//parent
while (pcntl_wait($status)>0) {};

?>

Another useful function that can be used for downloading from several urls at the same time:


function multi_request($urls)
{
$curly = array();
$result = array();
$mh = curl_multi_init();

foreach ($urls as $id => $url) {
$curly[$id] = curl_init();
curl_setopt($curly[$id], CURLOPT_URL, $url);
curl_setopt($curly[$id], CURLOPT_HEADER, 0);
curl_setopt($curly[$id], CURLOPT_RETURNTRANSFER, 1);
curl_setopt($curly[$id], CURLOPT_TIMEOUT, 30);
curl_setopt($curly[$id], CURLOPT_FOLLOWLOCATION, true);
curl_setopt($curly[$id], CURLOPT_SSL_VERIFYPEER, 0);
curl_setopt($curly[$id], CURLOPT_SSL_VERIFYHOST, 0);
curl_multi_add_handle($mh, $curly[$id]);
}

$running = null;
do {
curl_multi_exec($mh, $running);
} while($running > 0);

foreach($curly as $id => $c) {
$result[$id] = curl_multi_getcontent($c);
curl_multi_remove_handle($mh, $c);
}
curl_multi_close($mh);
return $result;
}
?>

A kind of multithreading can be also achieved with popen function and curl or wget:

foreach($urls as $url)
{
$pps[] = popen(”wget -O - “.escapeshellarg($url) , ‘r’);
}
foreach($pps as $pp)
{
$out = fread($pp,1024*1024);
// do something with the output
}

Of course the last example is not a real multithreading since have to process the output in a linear manner, but the downloading occurs in parallel.
In addition, this allows using external commands like wget and all their features.

row_number() for Postgres before 8.4

Posted by sergey - 10/07/09 at 04:07:31 pm

Unfortunately i found a solution for this problem only this week, after the new postgres version 8.4 was already released. In the new version this function is already implemented along with many other functions called “window functions“.
In any way, for everyone that still uses Postgress version less then 8.4, here is my solution for this -


CREATE OR REPLACE FUNCTION counter(int) RETURNS INT AS $$
return $_[0] + $_SHARED{counter}++; $$
LANGUAGE plperl;

CREATE OR REPLACE FUNCTION get_counter() RETURNS INT AS $$
return $_SHARED{counter}++;
$$
immutable
LANGUAGE plperl;

create or replace function get_row_number() returns integer as
’select counter(get_counter()*-1);’
language sql

After declaring this set of functions you will be able to run queries like this -

select get_row_number(),* from table...

I think this solution is better then creating temporary sequences and using nextval(’rownum’).

Your comments are appreciated

Posted by sergey - 31/05/09 at 07:05:23 pm

“Where mysql is good, grep is good too”

from russian forums…

inet_ntoa and inet_aton functions for Postgresql

Posted by sergey - 31/03/09 at 02:03:43 pm

These functions can be used to convert integer to ip and visa verse. This sometimes also called inet_itoa and inet_atoi.


create or replace function inet_aton(inet) returns bigint as '
select inetmi($1,''0.0.0.0'');'
language sql immutable;
create or replace function inet_ntoa(int) returns inet as '
select ''0.0.0.0''::inet+$1;'
language sql immutable;

=> select inet_ntoa(16843265);
inet_itoa
———–
1.1.2.1
(1 row)

=> select inet_aton(’1.1.2.1′);
inet_atoi
———–
16843265
(1 row)

Using Eclipse for editing remote files with ssh/sftp.

Posted by sergey - 22/08/08 at 08:08:36 pm

It took me a while to figure out how to work with remote files on server using Eclipse. It seems most of Eclipse users are developing locally and upload already tested code to the server (maybe i’am wrong). But for me it is important to work directly with remote files.
While on linux you can open and edit any file on the server using fish:// (or something like this), on windows there is no such option… On windows there is an option to install some software like sftpDrive, but it costs some 40$…

So I first tried to use winscp and configure Eclipse as an editor, but it didn’t worked because every time i clicked a file it was opening another copy of Eclipse despite any configurations i tried.

Then i played with several Eclipse plugins like Eclipse SSH Console, SFTP Plug-in for Eclipse, and aptana’s “Syncronize feature”, but no of them gave me the result i expected. None of the plugins give the ability to browse/edit/save remote file.

And FINALLY i found Remote System Explorer that solves everything. You download the plugin an intall it, and then you get a small button “Remote Systems” at the bottom of your eclipse that allow you to connect to you servers directly from eclipse. Great success!!

Get list of databases/tables/views in Postgresql

Posted by sergey - 21/08/08 at 09:08:59 pm

Get list of databases -
select datname FROM pg_catalog.pg_database;

This displays tables, views, indexes and sequences except postgres internal tables/views
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

Same, but only for tables
select c.relname FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid);

This shows table columns of a given table and their types -
SELECT
attname as Field,
(select typname from pg_type where oid=pga.atttypid) as Type
FROM pg_attribute pga
WHERE
attrelid=(select oid from pg_class where relname=$table) and
attisdropped=false and
attnum>0