Archive for the ‘Programming’ Category

h1

Getting PHP and MSSQL talking on Mac OSX

April 5, 2012

Here is what I had to do to get PHP talking to an MSSQL database on MAC OSX (Lion). Note that I am running Acquia dev desktop for PHP, you’ll have to change the paths to PHP if your setup is different.

Download Free TDS stable source from http://www.freetds.org/software.html and extract from the tarball.

Login as root with

sudo /usr/bin/bash

Go into freetds sources folder and configure freetds

CFLAGS="-arch i386" LDFLAGS="-arch i386" ./configure --prefix=/usr/local/freetds --enable-msdblib --with-tdsver=8.0

Compile freetds with

make && make install

Download and extract PHP sources for your version of PHP from http://php.net/downloads.php

Go into the php sources folder ext/mssql directory and configure

CFLAGS='-arch i386 -isysroot /Developer/SDKs/MacOSX10.7.sdk -mmacosx-version-min=10.7' ./configure --with-php-config=/Applications/acquia-drupal/php/bin/php-config --with-mssql=/usr/local/freetds

This will create a file called libtool

Go back to the freetds sources directory and run

make clean
CFLAGS="-arch i386" LDFLAGS="-arch i386" ./configure --prefix=/usr/local/freetds --enable-msdblib --with-tdsver=8.0

delete the freetds install
rm -rf /usr/local/freetds

copy the libtool file from the php sources ext/mssql directory to the freetds directory
cp /path/to/php-source/ext/mssql/libtool /path/to/freetds-source

Compile and install freetds again
make && make install

Go to the php-sources/ext/mssql directory and compile mssql
make

cp the so file to your php extensions dir
cp modules/mssql.so /Applications/acquia-drupal/php/ext

Edit your php.ini to include the mssql.so extension (e.g. with vim or nano)
vim /Applications/acquia-drupal/php/bin/php.ini

Add the following line in the Dynamic extensions section
extension=mssql.so

Tell the system where the FreeTDS config file is
echo "export FREETDSCONF=/usr/local/freetds/etc/freetds.conf" >> /etc/profile
echo "/usr/local/freetds/lib" >> /etc/ld.so.conf
ln -s /usr/local/freetds/bin/tds /usr/bin

and also run the followig (to save restarting your MAC)

export FREETDSCONF=/usr/local/freetds/etc/freetds.conf

Edit the freetds config file and set the freetds version to 8.0 (others dont seem to work with SQL server properly)
vim /usr/local/freetds/etc/freetds.conf
tds version = 8.0

Restart Web server

If you need help setting up SQLEXPRESS…

The basics…
http://codebetter.com/jefferypalermo/2005/11/04/how-to-use-sql-server-express-wheres-the-ui-level-200/

Setting up SQLEXPRESS for mixed login (windows authentication didn’t work for me from my remote PHP server)
http://eduzine.edujini-labs.com/archives/24-HOWTO-Configure-Microsoft-SQL-Server-for-Mixed-Mode-Authentication.html

Adding a new user account to sql express:
http://msdn.microsoft.com/en-us/library/aa337562.aspx

Let this new account access a db:
http://msdn.microsoft.com/en-us/library/aa337545.aspx

Advertisements
h1

MySQL Create new user and grant commands

January 19, 2012

Make a new database (schema) …


mysql> create schema myschema;
Query OK, 0 rows affected (0.00 sec)

Add a new user …


mysql> create user 'fred'@'localhost' identified by 'password-secret-string';
Query OK, 0 rows affected (0.00 sec)

Let the user have access to the new schema …


mysql> grant all privileges on myschema.* to 'fred'@'localhost';
Query OK, 0 rows affected (0.01 sec)

h1

Calculating permutations of a list

January 11, 2012

A recent project found me with the need to calculate all the possible permutations of a list of values. This is the possible order of all the values in the list.

For example, the list

1, 2, 3

Has 6 possible permutations:

1, 2, 3
1, 3, 2
2, 1, 3
2, 3, 1
3, 1, 2
3, 2, 1

My first attempt tried to calculate all the permutations in one go using a recursive function. In PHP, this looks like this:

/**
* Recursive permutations function for calculating the
* possible permutations of an array.
*
* @param array $list
* The list to calculate the pemutations of.
*
* @return array
* An array of arrays. Each sub array is a permutation
* of the originial array.
*/
function permutations(array $list) {

if (count($list) == 1) {
return array($list);
}

$perms = array();

foreach ($list as $key => $value) {
$sublist = $list;
unset($sublist[$key]);
foreach (permutations($sublist) as $subperm) {
array_unshift($subperm, $value);
$perms[] = $subperm;
}
}

return $perms;
}

This worked great for small lists of up to about 8 elements but past 8 the number of permutations grows great and you start to run out of memory in the recursive calculations. Incidentally, the total number of permutations is always given as the factorial of the number of elements. So, for 3 elements the total number is 3! or 3 x 2 x 1 = 6.

Here is a short piece of code for calculating the factorial of a positive integer.

/**
* Factorial function. For calculating some of the numbers
* of possible combinations.
*/
function nfact($n) {
if ($n == 0) {
return 1;
}
else {
return $n * nfact($n - 1);
}
}

So, my second attempt was to come up with a relationship between the permutation number and the order of the elements in a list given the starting order. I came up with this (in PHP again)

/**
* Given a list in it's initial state workout the
* i'th permutation.
*
* @param array $initial_state
* The initial state of the array.
* @param int $i
* The permutation of the initial state to find. Initial state is 0.
*
* @return array
* The i'th permutation of the initial state.
*/
function permutation(array $initial_state, $i) {

$n = count($initial_state);

// Requires all keys to be numeric in ascending order.
$initial_state = array_values($initial_state);

if ($n == 1) {
return $initial_state;
}

$t = nfact($n);

$first_value_position = floor($i / ($t / $n));
$first_value = $initial_state[$first_value_position];

// Setup the args of for the next value in the permutation.
unset($initial_state[$first_value_position]);
$i = $i % ($t / $n);

return array_merge(array($first_value), permutation($initial_state, $i));
}

To get all permutations you can then loop $i from 0 to the total number of permutations for the list.

$initial_state = array(1, 2, 3, 4, 5, 6, 7, 8);
$permutations = array();

for ($i = 0; $i < nfact(count($initial_state)); $i++) {
$permutations[] = permutation($initial_state, $i);
}

h1

replace syntax in mysql

November 3, 2011

The mysql REPLACE syntax allows you to do string replacements within the content of mysql table text fields. One application I used recently was to rewrite links in blocks of text entered into the Druapl CMS.

UPDATE table SET col = REPLACE(col, '/some/path', '/some/other/path');

This will replace all instances of ‘/some/path’ with ‘/some/other/path’ within the col column of the table table.

h1

SVN add all waiting files

September 7, 2011

If you would like to add all pending files to svn via an svn add command, the following works quite nicely:


svn status | grep ? | sed 's/^? \(.*\)/\1/g' | xargs svn add

And the same for removing missing files:


svn status | grep ! | sed 's/^! \(.*\)/\1/g' | xargs svn rm

h1

The Clean Coder

July 15, 2011

Just finished Bob Martin’s book, The Clean Coder. Excellent guide to professionalism in the Software Engineering industry. I highly recommend it.

http://www.amazon.co.uk/Clean-Coder-Conduct-Professional-Programmers/dp/0137081073

h1

Timing out PHP SOAP Calls

June 22, 2011

PHP has a bug when making SOAP calls over HTTPS. If the other end picks up but then does not send any data (as can happen if the SOAP server is behind a proxy or load balancer or the like) then its supposed to timeout after a specified amount of time. However, all current versions of PHP do not seem to allow this.

Thanks to Rob Ludwick for providing a workaround using CURL as the transport mechanism instead until PHP fix this. You can read about it on his blog here:
http://www.darqbyte.com/2009/10/21/timing-out-php-soap-calls

There is a PHP bug reported here for reference: http://bugs.php.net/bug.php?id=48524