MySQL ALTER TABLE ADD COLUMN IF NOT EXISTS

I wrote a MySQL function and procedure that will add a database column unless it exists already. The following code would add 3 fields to the template table, unless they already exist:

-- add fields to template table to support ignoring extra data 
-- at the top/bottom of every page

CALL addFieldIfNotExists ('template', 'firstPageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageFooterBeginY', 'INT NOT NULL DEFAULT 792');

Here is the actual code for the above procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS addFieldIfNotExists 
$$

DROP FUNCTION IF EXISTS isFieldExisting 
$$

CREATE FUNCTION isFieldExisting (table_name_IN VARCHAR(100), field_name_IN VARCHAR(100)) 
RETURNS INT
RETURN (
    SELECT COUNT(COLUMN_NAME) 
    FROM INFORMATION_SCHEMA.columns 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = table_name_IN 
    AND COLUMN_NAME = field_name_IN
)
$$

CREATE PROCEDURE addFieldIfNotExists (
    IN table_name_IN VARCHAR(100)
    , IN field_name_IN VARCHAR(100)
    , IN field_definition_IN VARCHAR(100)
)
BEGIN

    -- http://javajon.blogspot.com/2012/10/mysql-alter-table-add-column-if-not.html

    SET @isFieldThere = isFieldExisting(table_name_IN, field_name_IN);
    IF (@isFieldThere = 0) THEN
    
        SET @ddl = CONCAT('ALTER TABLE ', table_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ;
        SET @ddl = CONCAT(@ddl, ' ', field_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', field_definition_IN);

        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
    END IF;

END;
$$

NetBeans Writes My Code for Me

I'm a big fan of Eclipse.  I use it for Perl, PHP, HTML, CSS, JavaScript, ColdFusion ... but when it comes to hardcore Java development, I prefer NetBeans.  NetBeans refactoring tools are superb, and these are the ones I use the most:

ALT+ENTER for red and yellow squiggly underlines.

NetBeans reports all kinds of hints and errors as you're coding with little yellow and ride lines.  Red lines usually mean it won't compile.  Yellow lines usually indicate a coding style suggestion.

Either way pressing ALT+ENTER will usually provide me with options.

Refactor > Encapsulate Fields

Most classes need getters and setters.  NetBeans writes them for me.

ALT+INSERT for more code writing magic

Alt+Insert gives me a menu of things that the IDE can generate for you.  I use it mostly for constructors and override methods.

Instant JavaDocs

Event though JavaDocs come before the classes and methods they describe, if I define my method first, the JavaDocs will write more of the documentation for me.  I put my cursor on the line above the method...

I type /** and press ENTER.

NetBeans writes as much of the JavaDoc as it can based on what it knows about the method.  Then I fill in the rest...

More!

CTRL+SPACE for autocomplete.  I use it everywhere.  In fact, when I use it in a class but not in a method, it provides me a menu similar to ALT+INSERT where it will actually write constructors and override methods for me.

CTRL+R allows me to refactor and rename anything.  I have to be careful when I use this on a shared code repository.  It's easy to cause conflicts.

CTRL+B takes me to the definition of anything, method, class, enum, etc.

ALT+F7 tells me every place a class, interface or method is used.  This one is my favorite.