PHP Magazine

It all started when I decided to optimize image slicing algorithm for a new feature on UMapper – and since GD is quite RAM-intensive, I needed to check actual memory consumption, and the obvious choice to do so was PHP’s memory_get_usage() function. However, it failed to produce accurate results – it seemed like images loaded into memory weren’t accounted by the function (RAM was still used ).
As it turned out, whoever prepared official php5-gd package, compiles against original GD, and not using PHP5 bundled version of the library. I actually wasn’t aware about the fork, but here is explanation from GD Official Site:

The PHP version of gd offers features similar to and sometimes in addition to those included in the latest version of gd found here as well as many bug fixes not present in the latest GD release. If you are working with PHP, using the built-in gd of PHP 4.3.0 or better is recommended.

We are working to merge the changes done in the PHP GD in the normal GD library.

Well, I was pretty sure that unexpected behavior was caused by using original GD library instead of bundled one. So I decided to remove php5-gd package, recompile php5 from sources, and install updated GD package – which is exactly what gets bundled with PHP5 on other distributions.

Google is my friend, so here is a walkthrough:

# Install build tools, debian helpers and fakeroot
apt-get install build-essential debhelper fakeroot
# Get PHP source (it should go into /usr/src)
cd /usr/src
apt-get source php5
# Install all packages required to build PHP5
apt-get build-dep php5

#Now what we need is to update compile options,
# so we need to edit debian/rules file:
cd php5-5.2.6.dfsg.1
vim debian/rules
# locate the line having "--with-gd=shared,/usr --enable-gd-native-ttf \"
# replace with "--with-gd=shared --enable-gd-native-ttf \"
# that's remove reference to /usr so that bundled library is used

# compile (drink some coffee, walk you dog, see the latest House episode)
dpkg-buildpackage -rfakeroot

# install the new php5-gd package
cd ..
dpkg -i php5-gd_5.2.6.dfsg.1-3ubuntu4.2_i386.deb

# finally restart apache
/etc/init.d/apache2 restart

That’s it – you should be able to see «bundled» near the GD version in the phpinfo() output. Well, that’s not the only gain – it solves problem with memory_get_usage() as well

Now, once I had memory_get_usage() working correctly, back to optimization..

Today, I wanted to find some replacement for BluePrints XML Editor I used on WinXP. That was a general purpose, more specific was the fact that I needed XPath and XQuery supported. It turned out that Editix Lite (free version of commercial Editix) is just what I needed. If you are, like me, looking for checking your XPath queries, this small and simple editor is definitely the tool you should try.

Have just finished reading Ender’s Game – one of the coolest sci-fi books I’ve ever read! Although written in 1985 – it’s still good and pleasant reading. It’s the first book by Orson Scott Card I’ve read, so I already picked all other titles in Ender Wiggin’s series. Anyone who has some spare time, should give this book a try! Would see if the whole saga would be as good as the first book.

Our Flash team has just announced GeoDart launch. If you wander what’s GeoDart – see for yourself (just pick one of user-created Geo games). Personally, I loved European Countries game (though I hope author adds the rest of the Europe to the stock).

If you’ve ever installed some 3rd party software on you website (eg: phpbb forum), you certainly know that database tables, created during that process, generally have a convenient prefix in their names (default one for phpBB is phpbb_). This is done, I believe, to make sure that all of application’s tables get grouped together and do not mix with your previously created ones.

There are situations when you’d want to remove such grouped tables, all of them at once – for example, when, several days ago, I obtained our latest UMapper SQL-dump I, unfortunately, forgot to ignore forum tables, so they got into my localhost box. Today, I spotted those and wondered how can I quickly get rid of them. As I really like to work with MySQL via CLI, the question raised – how to remove multiple tables using single query.

At first, I tried to use LIKE clause the way it is used in SHOW TABLES:

mysql> SHOW TABLES LIKE "phpbb_%"; /* list all phpBB tables */
mysql> DROP TABLES LIKE "phpbb_%"; /* DROP THEM? Quite a naive approach.. */

Well, even before executing it I was 99% sure that this «method» wouldn’t work. Indeed, it didn’t

I certainly knew that you can drop several tables at once if you list all of them separated with commas:

mysql> DROP TABLE phpbb_zebra, phpbb_words; /* DROP several tables*/

So, I decided that it would be good enough if I form a query which would generate yet another query that would drop all similarly prefixed tables. If it’s not already, it would be crystal clear what I mean in a minute.

To get list of tables complying to certain criteria one could use information_schema database. To get list of all phpbb_ prefixed tables we can issue something like this:

mysql> SELECT GROUP_CONCAT(TABLE_NAME) 
           FROM information_schema.TABLES 
           WHERE TABLE_SCHEMA = "your_db_name" AND TABLE_NAME LIKE "phpbb_%";

Result (line-breaks added for readability):

phpbb_acl_groups,phpbb_acl_options,phpbb_acl_roles,phpbb_acl_roles_data,
phpbb_acl_users,phpbb_attachments,phpbb_banlist,phpbb_bbcodes,phpbb_bookmarks,
SKIPPED
phpbb_search_results,phpbb_search_wordlist,phpbb_search_wordmatch,phpbb_sessions,
phpbb_sessions_keys,phpbb_sitelist,phpbb_smilies,phpbb_styles,phpbb_styles_imageset,

Going from here, the only thing we need is to prepend the result with DROP TABLE string:

mysql> SELECT 
           CONCAT(
                  "DROP TABLE ",  
                   GROUP_CONCAT(TABLE_NAME)
           ) AS stmt 
           FROM information_schema.TABLES 
           WHERE TABLE_SCHEMA = "your_db_name" AND TABLE_NAME LIKE "phpbb_%";

Then, actually removing tables is a matter of copy-pasting the generated query.

That’s it! If you know a better way, please, share in comments!

P.S. It only occurred to me that I relied on two queries for doing the job, albeit second one was simply copy-pasted.

I am happy to announce that National Geographic Traveler portal is now relying solely on UMapper for their embedded mappings. That is really nice when application you work on gets broader attention. I hope it would stay this way. The first map NGT created is called the Best Romantic Weekends!

Answer is actually very simple, but several of my friends have missed the point of having size modifier in MySQL column type definitions, so I suppose it’s something non-obvious. Main confusion, I believe, has its roots in string data-type definitions:

VARCHAR(N)
CHAR(N)

where N is the maximum number of characters you want to store into field, i.e. it is field’s length. And depending on your settings you might receive an error (in strict SQL mode) when you are inserting something that exceeds the pre-defined length. Even if you don’t receive error or warning, the field is limited to the length you set, and the exceeding part is truncated. So, VARCHAR(20) and VARCHAR(40) are really different with respect to how big the stored value could be.

Now, let’s get back to numeric types. For numeric types length modifier is actually display width, which has nothing to do with what can be stored in field. Yes, that’s it – both TINYINT(1) and TINYINT(4) can store values in range -128..127 (or for unsigned values 0..255), and are absolutely identical datatypes, with one small twist (which concerns date retrieval – see below).

Here is explanation of what is meant by display width, taken directly from the source:

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

So, if display width doesn’t constrain the range of values our numeric type can hold, why to use it at all? On MySQL forum, there was a post suggesting that by using the display width, we set mental reminder to ourselves on how big the field should be. Me personally, find this quite logical, this is some kind of mental tooltip which pops up when you review your schema.

Of course, there’s a more deliberate functionality for having display width. One obvious example would be ZEROFILL attribute, which when used in datatype definition guarantees that the value returned would always have pre-defined width, and default space padding is replaced with zeroes. For example, when column is defined as INT(5) ZEROFILL, and value to be retrieved is 4, MySQL would return it as 00004.

So, the bottom line: for numeric types, N in TYPE(N) has nothing to do with value range, and refers to display width.

Have a nice weekends!

MySQL command-line client has an option for table names/fields auto-complete. Moreover, it is enabled by default (at least it says so in manual, on my Slackware box it wasn’t). This feature is called auto-rehash – when typing names you simply can hit TAB to see all possible alternatives for auto-completion (if there’s exactly one alternative, then auto-complete would occur without further intervention). Command-line folks would find this quite handy, indeed, if you are using CLI a lot, you just get used to hitting TAB key when in doubt.

So, if it’s not enabled in your client by default, you can enable this by issuing auto-rehash option when invoking the mysql client:

mysql -uroot -p --auto-rehash

Please note that you have to invoke use DBNAME at least once, since that’s the point when MySQL does hashing. Otherwise feature would refuse to do its work.

Of course, you can have auto-rehash (or no-auto-rehash for that matter) option in your ini file if you like:

[mysql]
auto-rehash

Particular drawback of auto-rehashing is slower start up time of client, since data has to be read and hashed for this functionality to work. If that troubles you, I have a good news: you do not have to use –auto-rehash option on start up at all, and you can still get auto-completion, even if –no-auto-rehash option was used. It’s quite simple, just type one of

\#
or
REHASH

in your MySQL client and rehash would occur, allowing you to utilize auto-complete. Personally, I prefer this alternative if my interactions with database are not so often, otherwise I still think that starting with –auto-rehash is easier, and speed penalty is not that big.

Наконец-то появился нормальный способ получить инвайт на хабрахабр. Объяснялка «под катом»..

Read the rest of this entry

Сегодня забрел на http://knol.google.com/k/ сервис позволяющий делиться своими знаниями, записывая их в так называемые «нолы» (knols) – что-то вроде битов знаний Из объяснялки на самом сайте:

A knol is an authoritative article about a specific topic.

то бишь,

«Нол» это авторитетная статья на определенную тему.

Что это нам дает? По-мне, так это что-то вроде википедии, однако контроль над статьей остается за ее автором, а не за комьюнити. В целом, мне идея не очень понравилась. Сама идея-то – ничего, просто мне всегда не по себе когда какая-либо компания начинает заниматься всем и вся. Гугл сделал отличный поисковик, плюс еще 3-4 хороших сервиса, после чего «Остапа понесло» и они уже успели отметиться даже собственным браузером. Кроме того, любителей делиться знаниями, приглашаю на Wikipedia – фактически каждый net-юзер ее использует, если не ежедневно то достаточно часто, написать о предмете, который знаешь хорошо – одна из возможностей поддержать проект.

В общем, стоит понаблюдать за развитием этого сервиса – особенно сейчас в пору кризиса, когда несколько других (нерентабельных) проектов были закрыты (точнее было объявленно об их закрытии, фактически пока все сервисы Гугл пашут).

Удачных выходных!