How I store Enumerations in the Database

One of the things I come across in databases now and then is a collection of single tables with a name like “MessageType”. You have a look in them and it turns out to have 6 or so rows with no foreign key relationships. Every single time it turns out to be someone had the idea to store an Enumeration (Enum) type in the database. Not a bad idea as it turns out since you can add sort options, soft deletes and the like, but the implementation of a single table for each one is flawed.

The following is how I deal with it (probably not ideal but works well for me). Essentially you define two tables in the database with names like Lookup and Value. Inside lookup you have something similar to the following.

+------+
|id    |
|lookup|
|name  |
+------+

This is basicly a representation of the enum name. Id is usually an autoincrementing id to make joins easy while lookup is the primary key. This is the definition of the enum, IE the name part in the database, or in our example “Message”.

Then you add the enum values to your Values table which looks similar to the below,

+---------+
|id       |
|lookupid |
|name     |
|sortorder|
|deleted  |
+---------+

Then through the power of a simple join you can get your enum values,

SELECT * FROM Value v INNER JOIN Lookup l ON l.id = v.lookupid WHERE l.name = '?';

Adding a simple index on lookupid and id ensures that any lookups are pretty fast (its a very simple join), and you are away. A single place to look for your enum values, the ability to add all sorts of extra metadata to your enums and no more creation of dozens of small tables in your database.

Lesson Learnt – Save Assets

I really should learn from my past mistakes, and usually I do, but this one comes to bite me again and again. I never save assets.

This results in me having to find fonts, recreate images and the like over and over. If you do a hard refresh of this page (CTRL+F5) you will notice the logo looks a little different. This is because I couldn’t find the font I used before and had to find something similar.

Never-mind, it looks alright and I have a copy of the font in source control now so I should be OK going forward. In other news the interface has changed a little. I now have what looks like any other search site’s search box on main page which jumps to the top like Google when you start typing. I am pretty happy with it.

I have a few things in the pipeline too and should start delivering on them soonish. As always I will post details in here.

Lorem Ipsum Word Generation

Something I tend to need from time to time is some generated lorem ipsum text as a placeholder. I would normally search for lorem ipsum, click through to lipsum.com click paragraph etc…

I thought this was too much work, so I quickly added a lipsum generator.

To use it just type the following,

lorem:100

Where you can replace 100 with any number of words up to 1000. Works for the following search texts as well,

loremipsum:100
lorem ipsum:100
lorem
loremipsum
lorem ipsum

List of Most Commonly Used PHP Functions

* Just realised a slight bug in the parser I wrote when I modified it for this article. Will update with the correct counts ASAP

** Correct counts updated. Should be alright now.

One of the things about ranking in Search is that you need to consider all sorts of methods of working out what is relevent. Google broke new ground (although the idea had already existed) with its PageRank algorithm which supplied better search results then all the other search engines. For what I am doing however I need to consider what programmers are looking for. One thing that I considered some time ago was working out which are the most common functions in a language and adding this as an additional signal to ranking.

I couldn’t find anywhere else on the web with this question answered so I took my own approch. The method was to take a collection of large PHP projects, including, WordPress, Mambo, Sphider, Smarty, Drupal, CodeIgniter, dump all their source code into a single file stripped of comments, and then run some simple regex over this file counting the occurance of each function.

Of course the problem with that is for languages like Python with namespaces you need to build the first parts of a compiler/interpreter to work out the most commonly used functions and objects. PHP however traditionally has not had namespaces which makes it rather easy to pull apart and work out the most common functions.

The results of this can be found below.

% of calls Function Name
22.67533433% array
4.518332773% isset
3.313605839% define
3.308496193% empty
2.607069452% assert
2.191271957% file
2.119353681% end
1.893251817% count
1.665872542% date
1.593571043% ord
1.553204834% print
1.436193926% substr
1.419970798% dir
1.32148236% pos
1.229764203% time
1.199234064% exp
1.098063061% key
1.081456709% list
1.047477559% log
1.0454337% com
0.997275281% each
0.972110271% header
0.959719378% is_a
0.959080672% chr
0.935704039% defined
0.904918418% unset
0.869406374% dl
0.845263294% is_array
0.845135553% strlen
0.844113623% tan
0.838109788% link
0.822269884% str_replace
0.787396546% printf
0.769768265% in_array
0.749074196% trim
0.74626389% die
0.725697563% sprintf
0.68264879% strpos
0.660421827% preg_match
0.622610442% pi
0.606259573% delete
0.60268282% explode
0.554268918% min
0.534596779% implode
0.529359391% strtolower
0.498956993% preg_replace
0.458846267% exec
0.450159868% intval
0.440707022% file_exists
0.418480059% dirname
0.397275025% htmlspecialchars
0.384884132% stat
0.373004204% sin
0.354737217% current
0.349244347% mail
0.3484779% is_null
0.329572208% array_merge
0.284351835% trigger_error
0.281286047% pack
0.281158306% eval
0.280008635% function_exists
0.276942847% strtoupper
0.275793177% sizeof
0.257781672% array_keys
0.251522355% is_object
0.251139132% idate
0.24321918% serialize
0.237854051% sort
0.237343086% reset
0.235682451% array_key_exists
0.212178076% is_numeric
0.202342007% abs
0.201064595% exit
0.200298148% extract
0.190206596% is_string
0.185607914% next
0.180370526% max
0.179987303% rand
0.173983468% main
0.159804198% settype
0.159548716% fclose
0.159037751% round
0.151756505% fopen
0.142303659% is_dir
0.142048176% getopt
0.141537212% addslashes
0.140643023% urlencode
0.140515282% fread
0.138982388% md5
0.138982388% unlink
0.130551471% fwrite
0.129785024% copy
0.129657283% get_class
0.122503778% hash
0.121992813% split
0.121481849% array_shift
0.118288319% class_exists
0.116244461% call_user_func
0.115988978% basename
0.111134814% array_push
0.110240626% prev
0.107813544% glob
0.106791615% array_pop
0.104747756% strstr
0.102065191% gettext
0.099127145% gettype
0.093634274% is_file
0.093378792% mktime
0.091718157% join
0.089674298% stripslashes
0.087374957% floor
0.085714322% ini_get
0.084820134% ob_start
0.084564652% flush
0.083925946% unserialize
0.083159499% array_values
0.081626605% file_get_contents
0.080476934% preg_match_all
0.079327264% constant
0.079071782% gmdate
0.075878252% chmod
0.073578911% array_map
0.072684723% strrpos
0.072556982% print_r
0.072556982% strtotime
0.071535053% method_exists
0.070257641% is_readable
0.068852488% filesize
0.068724747% microtime
0.067447336% array_unique
0.067447336% system
0.066680889% is_int
0.066042183% mysql_query
0.065914442% str_repeat
0.065020253% func_get_arg
0.062337689% strip_tags
0.062082207% call_user_func_array
0.061826724% ini_set
0.06131576% array_slice
0.06131576% range
0.060804795% fputs
0.060166089% preg_quote
0.059655124% getdate
0.058633195% mkdir
0.057611266% func_get_args
0.056333854% ucfirst
0.055311925% xml_parse
0.053523548% rename
0.053012584% strtr
0.052373878% preg_split
0.051351949% mt_rand
0.050968725% ceil
0.048924866% version_compare
0.048286161% array_diff
0.047902937% rtrim
0.047775196% curl_setopt
0.047519714% ob_end_clean
0.047519714% strftime
0.045859079% is_writable
0.045603596% base64_encode
0.045603596% urldecode
0.044837149% extension_loaded
0.044709408% ksort
0.044453926% stristr
0.043942961% error_log
0.04381522% realpath
0.043559738% array_search
0.043048773% crypt
0.043048773% substr_count
0.042665549% is_bool
0.041771361% configuration
0.04164362% ftell
0.04164362% readdir
0.041515879% var_export
0.041388138% cos
0.041260397% usage
0.040621691% htmlentities
0.040621691% preg_replace_callback
0.04049395% feof
0.040238467% error_reporting
0.038961056% pow
0.038961056% setcookie
0.037811385% array_reverse
0.037811385% ob_get_contents
0.037555903% get_object_vars
0.037172679% opendir
0.036661715% number_format
0.036661715% stripos
0.035512044% fgets
0.035128821% hexdec
0.034745597% getenv
0.034490115% parse_url
0.033851409% is_resource
0.033468185% compact
0.033468185% strcmp
0.033084962% filemtime
0.033084962% sha1
0.032573997% array_unshift
0.032446256% get_current_user
0.031935291% strrchr
96.22% total 200 total

 

List of all GNU/Linux and Windows Commands

Today I can announce a small but possibly useful feature which is the addition of lists. They are here as way of making it easier to find what you are looking for if the main search lets you down and you want to try a browser inline search. They can also be useful if you just want to scroll through everything in a particular subject. I have the following lists already created,

As always I am open to suggestions of more lists to compile, and will annouce them through this blog as I continue to update things.

BATF – Big Arse Text File

Ever needed the ability to track bugs and features without using a full featured bug/feature tracker? What about storing all your random notes such as server details, blog ideas, books to read, urls etc, without using a full featured CMS or the like. Want to have everything searchable and in the most platform independent format possible?

Enter the BATF. I have always been a fan of the big arse text file (BATF) for keeping track of the above. The catch being I wanted it centralised so I could get at it from any machine I was on (assuming internet access). I also wanted it to provide a simplistic version system. Tags would be useful too.

You can of course do this using SVN, GIT, or any other versioning system. The problem with that is that it brought be close to what I didn’t want to do (setup lots of stuff). So after a few beers I decided that what I really wanted was a BATF that had versioning (simple versioning anyway) built in, was web based so I could access it anywhere and lightweight. Since my 5 minute web search didn’t turn up anything that could do this I thought I would create one.


Behold the online BATF. Everything you add or modify is viewable in a nice timeline of versions. Explore your thought process as you add/modify things. Have something important you want to preserve for some amount of time? Tag it and it will always accessible. You can also explore changes through a simplistic diff viewer that diff’s against the current version.

Since I have been using it for a while and found it useful I thought I would give back to the community which provided the language (PHP), database (MySQL), Javascript framework (JQuery + plugins) and icons (famfamfam icons) by releasing this as free software. You can get a copy at GitHub https://github.com/boyter/BATF. The install instructions are included (pretty simple really). Feel free to fork it and send back patches if you find any bugs etc…