Sunday 12 September 2010

PHP tricks : generate an MS Excel file

By grapsus on Sunday 12 September 2010, 10:31

Here's the shortest and the fastest way I found to convert a CSV file to MS Excel format. It supports string and numeric fields. I hope this function can avoid you using huge Excel PHP classes which are too complicated and slow (and require reading a lot of documentation) for such a basic task.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

/* Written by Alexis Bezverkhyy <alexis@grapsus.net> in September 2010
 * This is free and unencumbered software released into the public domain.
 * For more information, please refer to <http://unlicense.org/> */
 
/** Convert a CSV file to MS Excel format
 * 
 * @param string $in input file
 * @param string $out output
 * @param string $glue CSV glue
 * @param string $enclosure CSV enclosure character
 */
function csv2xls($in, $out, $glue=";", $enclosure='"')
{
        $fp_in = fopen($in, "r");
        $fp_out = fopen($out, "w");
        
        /* write Excel BOF */
        fputs($fp_out, pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0));
        
        /* Read CSV fields */
        for($row = 0; $fields = fgetcsv($fp_in, 0, $glue, $enclosure); $row++)
        {
                foreach($fields as $col=>$value)
                {
                        $value = trim($value);
                        $value = utf8_decode($value);
                        
                        /* string cell */
                        if(!is_numeric($value))
                        {
                                $l = strlen($value);
                                fputs($fp_out,
                                        pack("ssssss", 0x204, 8 + $l, $row, $col, 0x0, $l).$value);
                        }
                        /* numeric cell */
                        else 
                        {
                                fputs($fp_out,
                                        pack("sssss", 0x203, 14, $row, $col, 0x0).pack("d", $value));
                        }
                }
        }
        
        /* write Excel EOF */
        fputs($fp_out, pack("ss", 0x0A, 0x00));
        
        fclose($fp_out);
        fclose($fp_in);
}

Saturday 28 August 2010

The perfect Eclipse, PDT and Xdebug setup ... on Windows

By grapsus on Saturday 28 August 2010, 17:19

A few days ago I wrote about making a wrapper for Firefox in order to solve Eclipse and PDT bugs when they open an external browser. The script was very simple, written in Bash shell, but not portable on Windows unless you have Cygwin installed. Here's a small C program to achieve the same result on this OS :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

/* Written by Alexis Bezverkhyy <alexis@grapsus.net> in august 2010
 * This is free and unencumbered software released into the public domain.
 * For more information, please refer to <http://unlicense.org/> */
 
#include <stdio.h>
#include <string.h>
#include <windows.h>
 
int WINAPI WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance,
    LPSTR lpCmdLine, int nCmdShow)
{
        int r,i;
        PROCESS_INFORMATION p;
        STARTUPINFO si = { sizeof(si) };
        char url[1000], ffpath[1000], cmdline[1000], *str;
        FILE *fpid, *fpath;
#define PIDFILE "firefox.pid"
#define PATHFILE "firefox.path"
        
        str = strstr(lpCmdLine, "http");
        
        if(!str)
        {
                printf("no URL given\r\n");
                return 1;
        }
 
        for(i=0; str[i] && str[i] != '\'' && str[i] != '('; i++)
        {
                url[i] = str[i];
        }
        url[i] = 0;
        
        if(!strstr(url, "XDEBUG_SESSION_STOP_NO_EXEC"))
        {
                ffpath[0] = 0;
                if(fpath = fopen("firefox.path", "r"))
                {
                        fscanf(fpath, "%s", ffpath);
                        fclose(fpath);
                }
                if(ffpath[0] == 0)
                {
                        sprintf(ffpath, "%s\\Mozilla Firefox", getenv("ProgramFiles"));
                }
                
                sprintf(cmdline, "\"%s\\firefox.exe\" -no-remote -P eclipse %s", ffpath, url);
                r = CreateProcess(
                        NULL,
                        cmdline,
                        NULL,
                        NULL,
                        0,
                        DETACHED_PROCESS | CREATE_BREAKAWAY_FROM_JOB | CREATE_NEW_PROCESS_GROUP,
                        NULL,
                        ffpath,
                        &si,
                        &p);
 
                fpid = fopen(PIDFILE, "w+");
                if(fprintf(fpid, "%d", p.dwProcessId))
                {
                        printf("Pidfile written!\r\n");
                }
                else
                {
                        printf("Cannot write pidfile\r\n");
                        return 1;
                }
                fclose(fpid);
 
                if(r)
                {
                        printf("Firefox launched! PID=%d cmdline=%s\r\n", p.dwProcessId, cmdline);
                }
                else
                {
                        printf("Cannot start Firefox r=%d cmdline=%s\r\n", r, cmdline);
                        return 1;
                }
        }
        else
        {
                DWORD pid;
                fpid = fopen(PIDFILE, "r");
                if(!fpid)
                {
                        printf("Cannot open pid file\r\n");
                        return 1;
                }
                if(fscanf(fpid, "%d", &pid))
                {
                        HANDLE h;
                        if(h = OpenProcess(PROCESS_ALL_ACCESS, 0, pid))
                        {
                                if(TerminateProcess(h,0))
                                {
                                        printf("Firefox process %d terminated!\r\n", pid);
                                }
                                else
                                {
                                        printf("Cannot terminate Firefox process %d\r\n", pid);
                                        return 1;
                                }
                        }
                }
                else
                {
                        printf("Invalid pidfile!\r\n");
                        return 1;
                }
                fclose(fpid);
                if(DeleteFile(PIDFILE))
                {
                        printf("Pidfile removed!\r\n");
                }
        }
 
        return 0;
}

Firefox is supposed to be installed in its default location, unless you create a file named firefox.path in your Eclipse folder with the path to your Firefox installation.

Ffwrap can be built with mingw or Visual C++. Here's the exe file for lazy people.

Thursday 26 August 2010

Allow the insertion of tabulations in a textarea

By grapsus on Thursday 26 August 2010, 13:45

When you press tab in a textarea of a web page, it looses focus and no actual tabulation is inserted. You can bypass this behavior with some Javascript. It is very useful for typing code for instance.

Let's say your textarea is identified with an id :

1
2
3

<form>
<textarea id="textarea"></textarea>
</form>

I took the following script from Wikini, hence it is licensed under GNU GPL. The original script had a very annoying bug : when you typed a tab, it was inserted, but the textarea scrolled the text to its top. I corrected it by storing the scroll position before the insertion and by restoring it after.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

/* Original code taken from Wikini <http://www.wikini.net/>, licensed under GNU GPL */
 
document.getElementById("textarea").onkeydown=textareaKeyDown;
 
function textareaKeyDown(e) {
  if (e == null) e = event;
    if (e.keyCode == 9) {
 
      var scrollTop = this.scrollTop;
      var scrollLeft = this.scrollLeft;
 
      if (typeof(document["selection"]) != "undefined") { // ie
        e.returnValue = false;
        document.selection.createRange().text = String.fromCharCode(9);
      } else if (typeof(this["setSelectionRange"]) != "undefined") {  // other
        var start = this.selectionStart;
        this.value = this.value.substring(0, start) + String.fromCharCode(9)
         + this.value.substring(this.selectionEnd);
        this.setSelectionRange(start + 1, start + 1);
      }
 
      this.scrollTop = scrollTop;
      this.scrollLeft = scrollLeft;
 
      return false;
    }
    return true;
  }
}

Wednesday 25 August 2010

The perfect Eclipse, PDT (PHP developpement tools) and Xdebug setup !

By grapsus on Wednesday 25 August 2010, 22:56

Eclipse is a great IDE and Xdebug adds a lot of usefull features for PHP debugging. With PDT plugin for Eclipse, you can use Eclipse as a debug client for Xdebug and debug live PHP code !

I won't describe here how to setup each of these tools. You may find many better written articles about that (with lots of screenshots and all other fancy stuff). I will simply expose how to solve a few very annoying bugs in this configuration which can drive you crazy.

No output when debugging in browser

When you choose to debug a PHP script in an external browser, the output of your script isn't sent to the browser until the debug session is terminated ! I verified it with Wireshark, the browser gets absolutely nothing (not even the HTTP headers) and keeps waiting. None of the *ob_* or *flush* functions seem to help it. I kept trying different PHP options and even editing Xdebug source code until I found the implicit_flush setting which makes it work !!! Just add

implicit_flush = On

to your php.ini and you'll be able to see your output in live !

I really think it's a bug because when you normally run a PHP script without output buffering, the headers and the content are sent to the client before the end of execution. Maybe Xdebug messes up some internal PHP configuration when it sets up a debugging session.

Very annoying and useless DEBUG SESSION ENDED new page

As you terminate a browser debug session, a new browser window pops up saying DEBUG SESSION ENDED. WTF ?! Why do we need to make a HTTP query to stop the debug session ?! The DBGP protocol used by PDT has a stop command. One more time with Wireshark I saw that this command is issued by PDT and Xdebug answers ok to it !

The only solution I found was to make a wrapper for Firefox to ignore these queries. It works very well. Even better, I found a way to close the Firefox window with your application when you terminate the debug session !

Eclipse opens web browsers with additionnal parameters you cannot disable !

Here comes another Eclipse bug, when you set up an external browser some implicit parameters are passed to it and there is no way to disable it. For example, it doesn't execute

firefox %URL%

but

firefox -remote openURL(%URL%)

WFT ?! I don't want it to grab my existing Firefox instance and mess around with it ! So here's the wrapper to solve the two problems above. Create a new Firefox profile named eclipse by running

firefox -profile-manager -no-remote

Now set up Eclipse to use the following script as web browser. Debug sessions will be opened in a new window and when you terminate it, this window will automatically close !

1
2
3
4
5
6
7
8
9
10
11

#!/bin/bash
 
URL=$(grep -o 'http://[^)]*' <<< "$@")
echo "$URL" >> /tmp/url
if !(grep -q 'XDEBUG_SESSION_STOP_NO_EXEC' <<< "$URL") ; then
        firefox -no-remote -P eclipse "$URL" &
        echo "$!" > /tmp/eclipse-firefox.pid
else
        kill $(cat /tmp/eclipse-firefox.pid)
        rm -f /tmp/eclipse-firefox.pid
fi

Notes

I made my tests on PHP 5.3, Xdebug 2.1, Xdebug 2.2-dev, Eclipse Ganymede, Eclipse Galileo, Eclipse Helios and PDT 2.1 and PDT 2.2 on Debian SID (amd64).

Let me know if these workarounds did it for you or if you found more elegant solutions to those problems.

Edit : here's a port of this wrapper on Windows.

Thursday 5 August 2010

Nagios plugin to check backup folders

By grapsus on Thursday 5 August 2010, 10:00

Here's a plugin I wrote for Nagios to check directories where some scripts regularly store backup files. You may specify a directory to scan, an optional pattern for the filename and the minimal age and size you expect for the latest file found in that directory and matching the pattern. The script can output warnings and critical alerts with different thresholds.

/root/bin//nagios-check-backup.sh :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79

#!/bin/bash
 
# Written by Alexis Bezverkhyy <alexis@grapsus.net> in july 2010
# This is free and unencumbered software released into the public domain.
# For more information, please refer to <http://unlicense.org/>
 
 
function PRINT_USAGE(){
  echo "This Nagios plugin checks backup folders :
  -d DIRECTORY  the directory to search for backup files
  -p PATTERN  an optionnal pattern for backup files
  -t HOURS  maximal age in hours for the latest backup before a warning is issued
  -T HOURS  maximal age in hours for the latest backup before a critical alert is issued
  -s KBYTES maximal size in kilo bytes for the latest backup before a warning is issued
  -S KBYTES maximal size in kilo bytes for the latest backup before a critical alert is issued
  -h    prints out this help
You must at least specify a directory and a minimal size or a minimal age."
  exit 0
}
 
WTIME=0;CTIME=0;WSIZE=0;CSIZE=0;DIR='';PATTERN=''
declare -i CTIME 
declare -i WTIME
declare -i CSIZE
declare -i WSIZE
while true ; do
  getopts 't:T:s:S:d:p:h' OPT 
  if [ "$OPT" = '?' ] ; then break; fi; 
  case "$OPT" in
    "t") WTIME="$OPTARG";;
    "T") CTIME="$OPTARG";;
    "s") WSIZE="$OPTARG";;
    "S") CSIZE="$OPTARG";;
    "d") DIR="$OPTARG";;
    "p") PATTERN="$OPTARG";;
    "h") PRINT_USAGE;;
  esac
done
 
if [ -z "$DIR" -o '(' "$WTIME" = '0' -a "$CTIME" = '0'\
 -a "$WSIZE" = '0' -a "$CSIZE" = '0' ')' ] ; then
  PRINT_USAGE
fi
 
LASTFILE=$(ls -lt --time-style=+%s "$DIR" | grep -v "^total " | grep "$PATTERN"\
 | head -n 1 | sed 's/\s\+/ /g')
if [ -z "$LASTFILE" ] ; then
  echo "CRITICAL - no backup found in $DIR" 
  exit 2
fi
 
TIMESTAMP=$(cut -d ' ' -f 6 <<< "$LASTFILE")
BYTES=$(cut -d ' ' -f 5 <<< "$LASTFILE")
let "SIZE = $BYTES / 1024"
FILENAME=$(cut -d ' ' -f 7 <<< "$LASTFILE")
let "AGE = ( $(date +%s) - $TIMESTAMP ) / 3600"
 
if [ "$CTIME" -gt 0 -a "$AGE" -gt "$CTIME" ] ; then
  echo "CRITICAL - $FILENAME is out of date ($AGE hours old)" 
  exit 2
fi
 
if [ "$WTIME" -gt 0 -a "$AGE" -gt "$WTIME" ] ; then
  echo "WARNING - $FILENAME is out of date ($AGE hours old)"  
  exit 1
fi
 
if [ "$CSIZE" -gt 0 -a "$SIZE" -lt "$CSIZE" ] ; then
  echo "CRITICAL - $FILENAME is too small ($SIZE kb)" 
  exit 2
fi
 
if [ "$WSIZE" -gt 0 -a "$SIZE" -lt "$WSIZE" ] ; then
  echo "WARNING - $FILENAME is too small ($SIZE kb)"  
  exit 1
fi
 
echo "OK - $FILENAME ($AGE hours old, $SIZE kb)"
exit 0

Here is a sample configuration for Nagios to use my script. check_backup checks a regular folder with compressed backups and check_sync checks a directory that is ought to be synchronized, therefore only age is checked and not the size.

commands.cfg :

1
2
3
4
5
6
7
8
9
10

define command {
  command_name check_backup
  command_line /root/bin/nagios-check-backup.sh -d $ARG1$ -p $ARG2$ -t $ARG3$ \
   -T $ARG4$ -s $ARG5$ -S $ARG6$
}
 
define command {
  command_name check_sync
  command_line /root/bin/nagios-check-backup.sh -d $ARG1$ -t $ARG3$ -T $ARG4$
}

foo.cfg :

1
2
3
4
5
6
7
8
9
10
11
12
13

define service{
        use                             generic-service
        host_name                       bar
        service_description             BAK-FOO-CONF
        check_command                   check_backup!/media/backup/foo/conf/!conf!30!60!2000!1000
}
 
define service{
        use                             generic-service
        host_name                       bar
        service_description             BAK-FOO-WWW
        check_command                   check_sync!/media/backup/foo/www/!30!60
}

2 comments

- page 3 of 6 -