Use appropriate data types in your database tables
Just this past week I was called upon to troubleshoot some code executed various sql queries on a database. Though not important for the task at hand, I couldn't help but notice how little care was taken in modeling the database tables. Almost every column, whether it be a number, date, or text, was given a data type of either text or varchar (100).
Let's take a look at what you lose when you don't assign the proper data type to a column:
- Date / Time formatting, comparison and arithmetic functions provided by the DBMS.
- Storage optimization provided by numeric fields.
- The ability for other applications to make sense of your data.
- The value of optimizations such as indexes.
And let's not forget what you gain by using correct data types:
- Data integrity constraints.
- Validity checks for proper data type on data inserted or updated.
- More optimized querying.
- Data compatibility with the scripting / programming language you are connecting with.
When using VARCHAR or CHAR data types, try to think like a computer when deciding the length of the field. While we like to think in 10's, computer's, as you know, like binary. So instead of a VARCHAR(30), try a VARCHAR(32).
PHP text field length issues with ODBC connections
This is one that you'll definitely want to archive as a reference because you're bound to run into this issue sooner or later if you ever need to develop a database-driven site on PHP/Windows.
I was recently contracted to troubleshoot a website migration to a Windows / IIS / PHP server. The migration was smooth but most of the website content appeared truncated, as if the web browser just didn't want to display more than 2 pages worth of information.
It ends up that the MySQL ODBC driver was truncating text fields at 4096 bytes when queried. A little research told me that this is a php.ini configuration issue where the maximum size of a return field is set at 4096 bytes. The php.ini directive I'm speaking of is odbc.defaultlrl. I call it a configuration issue simply because the default value of 4096 bytes is ridiculous and is bound to cause headaches for most webmasters.
Set odbc.defaultlrl to a higher power of 2 to maintain your sanity. I like odbc.defaultlrl = 32768.
Postgresql Encryption
Today I was given the task of encrypting certain data fields in a PostGreSQL database. My first reaction was to immediately Google the topic. I learned about the encrypt and decrypt functions which allow you to specify a piece of data, encryption key, and an encryption method. So I begin to test these functions, but the server responds that the function does not exist.
Some more research tells me that I need to install a module called pgcrypto. Although, I believe the latest PostGreSQL releases come with pgcrypto built in, earlier versions require the add-on.
So, pgcrypto is installed. I test the encrypt function again, same error! The problem is, although I installed pgcrypto, I need to make the pgcrypto functions available to my database.
Simply use your favorite tool, log in to your PostGreSQL database and issue the following sequence:
SET search_path = public;
CREATE OR REPLACE FUNCTION encrypt(bytea, bytea, text)
RETURNS bytea
AS '$libdir/pgcrypto', 'pg_encrypt'
LANGUAGE 'C';
CREATE OR REPLACE FUNCTION decrypt(bytea, bytea, text)
RETURNS bytea
AS '$libdir/pgcrypto', 'pg_decrypt'
LANGUAGE 'C';