QuicksearchShow tagged entriesCategories |
Tuesday, August 17. 2010
Drop empty columns with dynamically ... Posted by Mark van der Velden
in MySQL at
13:54
Comments (0) Trackbacks (0) Drop empty columns with dynamically defined columns and tablesFor a migration process I wanted to build in extra validation in some destructive MySQL queries, to eliminate the risk that data might be lost. Of course all data is back-upped, but I rather be safe then sorry. This is fairly straight forward, create a stored procedure and perform an ALTER statement whenever the previously defined criteria have been met. But I wanted to define a single procedure rather then create one for every table I had to drop columns from. And since you can't simply use variables for column/table names, you have to improvise a little. I came up with the following: -- Defining the "drop empty column" SP DELIMITER @@ DROP PROCEDURE IF EXISTS drop_empty_column @@ CREATE PROCEDURE drop_empty_column( IN itable VARCHAR(50), IN icolumn VARCHAR(50), OUT succeeded INT ) BEGIN SET @amount = -1; SET @itable = itable; SET @icolumn = icolumn; -- Build the query, with dynamic table and column. Store the result in @amount SELECT CONCAT('SELECT COUNT(', @icolumn ,') INTO @amount FROM ', @itable ,' WHERE(', @icolumn ,' IS NOT NULL OR ', @icolumn ,' != "");') INTO @testSql; PREPARE testSqlStmt FROM @testSql; -- Execute EXECUTE testSqlStmt; -- Check if we have 0 rows, else we still have data and we can't drop. IF @amount = 0 THEN -- Build the query SELECT CONCAT('ALTER TABLE ', @itable ,' DROP COLUMN ', @icolumn ,';') INTO @alterSql; -- Execute PREPARE alterSqlStmt FROM @alterSql; EXECUTE alterSqlStmt; -- Update the status SELECT 1 INTO succeeded; ELSE SELECT 0 INTO succeeded; END IF; END@@ DELIMITER ; -- Conditionally drop the column "deprecated_column" from table "some_table" CALL drop_empty_column('some_table', 'deprecated_column', @succeeded); -- Show the status SELECT @succeeded; -- Cleanup DROP PROCEDURE IF EXISTS drop_empty_column;
I'm fairly positive that this could be done in a much better way then that I'm doing here, but this works too. Simply repeat the CALL drop_empty_column(..) for every table/column combination you want to DROP and update the definition of an "empty column" to what you want. Currently it drops the column only if the values contain nothing other then NULL or "" (empty string) values. Further reading: Tuesday, June 8. 2010
What do you get when you mix; MSSQL, ... Posted by Mark van der Velden
in PHP at
21:58
Comment (1) Trackbacks (0) What do you get when you mix; MSSQL, PDO and uniqueidentifier?You get NULL! Well you get NULL when you don't cast. Say for example you do the following: $dbh = new PDO([..]);
$stmt = $dbh->prepare('SELECT accountid FROM dbo.Account'); $stmt->execute(); echo $stmt->fetchColumn(); // NULL But when you do the following: $stmt = $dbh->prepare('SELECT CAST(accountid AS varchar(36)) accountid FROM dbo.Account'); $stmt->execute(); echo $stmt->fetchColumn(); // "F05C92A1-3119-4206-A123-49A759AC99FB" I didn't think the casting would be necessary, since according to the manual: http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx the datatype 'uniqueidentifier' has implicit casts with multiple data-types. But I guess it's just one of those things... Friday, June 4. 2010
Connecting from PHP on a non ... Posted by Mark van der Velden
in PHP, PlanetPHP at
07:24
Comments (3) Trackbacks (0) Connecting from PHP on a non Microsoft OS to MSSQL with a domain accountI was asked to create a web interface front-end with Microsoft Dynamics CRM as back-end. But I had some troubles setting up the connection, since it has to be done using a domain logon. This doesn't have to be a problem at all, unless your configuration is wrong! In this article I'll explain a few things and point you in the right direction when you have login problems. As stated earlier, the server running the PHP installation is not Microsoft. In this case a AS400 installation, but it could've been a Linux installation also. I'm using PDO for this article and PHP version 5.2.11. Even if you don't want to use PDO, I recommend using it only for debugging (if possible) since that will give you *most likely* more debug information then the mssql_* family. When using PDO with a MS-SQL database, you'll need to supply "dblib" as driver and DBLib uses FreeTDS as underlaying library. FreeTDS can be a source of troubles when you're trying to connect, if not configured properly. So I'll kick-off with a little information about it. Don't skip it if you have login problems!
Continue reading "Connecting from PHP on a non Microsoft OS to MSSQL with a domain account"Monday, April 19. 2010
PHPUnit conditional test based on a ... Posted by Mark van der Velden
in PHP, PlanetPHP at
16:47
Comments (2) Trackbacks (0) PHPUnit conditional test based on a PHP versionI had a problem with running test cases on multiple CI environments, where one of the two runs on PHP 5.2 and the other on PHP 5.3. This basically meant that all our pretty PHP 5.3 code caused the builds to fail on the 5.2 only machine. To solve this problem I needed a way to skip tests when the PHP version was less then 5.3.0. Besides the reason I needed this for a -less then ideal- setup. This can also be a generic way to skip certain tests, based on a PHP version. class someTest extends PHPUnit_Framework_TestCase { public function setUp() { // Testing if we are dealing with version 5.3.0 or higher if (!version_compare(PHP_VERSION, '5.3.0', '>=')) { $this->markTestSkipped('Invalid PHP version, unable to run tests.'); } } public function test_testFoo() { // .. some awesum test case .. \\ } } You can also use the cool @depends annotation of PHPUnit and put the version logic in a test. This has my preference, but it's not always possible. In case you have some code that simply can't be parsed by the older PHP engines. If you know a better way to do this, please share! Tuesday, November 3. 2009
Multiple PHP versions on one webserver Posted by Mark van der Velden
in Apache, PHP, PlanetPHP at
08:39
Comments (7) Trackbacks (0) Multiple PHP versions on one webserverIntroductionThis is a blog about running two PHP versions on one webserver and using multiple php.ini files, this combination can be a tricky one to tackle. But luckily one we can tackle quite easily as long as one of the PHP versions is >= 5.2.7. For this example I'll be using Apache, but the webserver flavor doesn't really matter. The most important part is the "PHP_INI_SCAN_DIR" environment variable. The whyThere could be a number of reasons to want what I'm about to talk about. In my case I have a project where I have a legacy code-base, running on a specific PHP version, and a new code-base which will be run on 5.3. Because the new code-base will be a ongoing progress of replacing the old, it first has to run side by side with the legacy code-base. So I wanted my development image to run two PHP versions. The old code-base used php.ini settings such as a include-path, error reporting, etc. Which will be different from the new code-bas, and those can no-longer be set with the 'php_value' feature of Apaches since the PHP version we'll be using for that runs as (f)CGI rather then as module. Thursday, October 29. 2009
Javascript printing a popup window Posted by Mark van der Velden
in Javascript at
15:25
Comments (3) Trackbacks (0) Javascript printing a popup windowFor the impatient, a working example:http://dynom.nl/jquery/print_popup.html
It seems so easy, but I had some trouble printing a popup window containing an image. Whenever I printed the page using the following code it failed. /** * FAIL */ function printIt() { var win = window.open('/path/to/image.jpg', 'Image', 'resizable=yes,...'); if (win) { win.focus(); win.print(); } return false; }
So I changed from opening a URL to writing a IMG tag to the opened window, which works like a charm. /** * Works like a charm. */ function printIt() { var win = window.open('', 'Image', 'resizable=yes,...'); if (win) { win.document.writeln('<img src="/path/to/image" alt="image">'); win.document.close(); win.focus(); win.print(); } return false; }
And to put it in jQuery terms: /**
* To put it in jQuery terms: */ Popup = { init : function () { $('a#action_print').bind('click', Popup.printIt); }, printIt : function () { var win = window.open('', 'Image', 'resizable=yes,...'); if (win.document) { win.document.writeln('<img src="'+ $(this).attr('href') +'" alt="image" />'); win.document.close(); win.focus(); win.print(); } return false; } } $(document).ready(function () { Popup.init(); }); Sunday, October 11. 2009
Multiple backend session storage handler Posted by Mark van der Velden
in PHP at
11:17
Comments (0) Trackbacks (0) Defined tags for this entry: backend, driver, loadbalancing, php, session handler, session_set_save_handler, state, write trough
Multiple backend session storage handlerRecently I got asked if I knew about a system that supports multiple session back-ends at once. I didn't know about one and since it's not rocket-science I decided to spent a few hours and whoop something up. For the impatient, checkout: http://github.com/Dynom/SessionHandler What does it do?It is a drop-in high-availability storage back-end for PHP sessions by offering a redundant session storage system. It's as easy as including the lib, define the drivers you want to use (e.g. Memcache and MySQL) prepare their configuration/installation and done. If you already have a MySQL server and a Memcache instance running you can set it up in about 5 minutes. It's also easy to extend and write new drivers, just extend the template class and fill in the blanks. Continue reading "Multiple backend session storage handler"Tuesday, October 6. 2009
Sorting of lists in jQuery Posted by Mark van der Velden
in Javascript at
13:00
Comments (0) Trackbacks (0) Sorting of lists in jQueryA while back I posted a simple sorting algorithm for number sorting, but it doesn't really do the trick for regular strings (e.g. anchors). But since I needed one recently I wrote another small and very simple sorting solution. For a working example, see: http://dynom.nl/jquery/listsort.php My.List = { Sort : function (listSelector) { var sortedElements, left, right; // If we have any list items to sort on if ($(listSelector + ' li').length > 0) { // Remove the items from the DOM sortedElements = $(listSelector + ' li').remove(); // Sort, using a custom sorting and compare by anchor contents sortedElements.sort(function (a, b) { left = $(a).children('a').html(); right = $(b).children('a').html(); if (left > right) { return 1; } else if (right < left) { return -1; } else { return 0; } }); // Place the items back on the DOM $(listSelector).append(sortedElements); } } }; Again, the source should be self explanatory
Tuesday, October 6. 2009
Click many, call once with jQuery Posted by Mark van der Velden
in Javascript at
12:28
Comments (0) Trackbacks (0) Click many, call once with jQueryRecently I wanted to improve they way some anchors where handled in my recent project, it works a bit like adding and removing items to/from a list. But it was possible to trick the interface by quickly clicking multiple times on a link before the AJAX request was finished, while the back-end handled this without any problems, the interface for the visitor couldn't. And since I'm a neat freak and I had a few minutes to spare, I came up with the following. A working example: http://dynom.nl/jquery/clickmanycallonce.html.
My.items = {
init : function () { // Bind the real item only once, and the dummy item permanently // This way the *real* item is only called once, regardless of the amount of clicks // And the link remains clickable but doesn't trigger the browser to follow it. $('ul>li>a.item').one('click', My.items.real_action).bind('click', My.items.dummy_action); }, dummy_action : function () { $(this).html( $(this).html() + '.' ); return false; }, real_action : function () { /* Fancy AJAX call, that shouldn't be executed more then once for every click */ $('div#frame').html($('div#frame').html() + "<br>\nCalling 'real_action' for anchor: " + $(this).html()); return false; } }; // When the DOM is ready $(document).ready(function () { // Attach the filter to our input and list My.items.init(); }); While the code is fairly self explanatory and quite simple here the steps in a bit more detail:
This situation is probably only favorable if the item you click is removed from the DOM (or has actions re-bound after the call) else it might not be what you want. Friday, September 25. 2009The PHP Quiz seriesI like solving puzzles, probably one of the reason why I like programming as much as I do. I also like finding challenges and experimenting, as such I came to the idea to start the PHP Quiz series. They contain typical combination of PHP quirks and lesser known features of PHP. The reason behind the quizzes is not to advocate bad or good coding practices, but it's intended to let you find out the why in all of it. I believe that by understanding what happens it can make you a better programmer and you might spot bugs easier then without knowing what happens. The series are not ordered in level of difficulty but merely in the order that I found out about them, thought about them or where simply sent in. But in general I think it's safe to say that your knowledge of PHP should be quite a leap forward from novice before you can answer most questions. This however doesn't mean that, once you can do the quizzes flawlessly, that you are a superior programmer. There is a big different between knowing how to design an application and knowing why $array = array(1,1) + array(2,2) only results in an array with two elements. An overview of all the PHP Quizzes: http://blog.dynom.nl/categories/PHPQuiz_12
-D |
Calendar
|
|||||||||||||||||||||||||||||||||||||||||||||||||
