While building the development environments for a code base that would eventually be deployed to an auto-scaling AWS environment, a client request pivoted the database tier architecture away from MySQL to PostgreSQL. This pivot was timely given Amazon’s most recent RDS offering, announced as recently as re:Invent. Awesome! Now to integrate PostgreSQL with the existing Vagrant/Chef development environment deployments; not as awesome! What follows is a summary of and solution to several common pitfalls experienced by those deploying PostgreSQL with Chef (omnibus installer). Note: this post assumes some familiarity with Chef. A quick overview of how our deployment gets PostgreSQL … Read More
Oracle XE Limitations (CPU, RAM, Data Limits)
I was trying to make a choice today between selecting MySQL and Oracle XE for a BI project. I didn’t know the limitations off-hand and was surprised I had to start digging through Oracle docs to find it. Here they are for your convenience.
MySQL and SQL Server – Oracle CONNECT BY PRIOR for Recursive, Hierarchical Data
Recursive queries are something I have used many, many times over the years to build dynamic, n-tiered hierarchies. Oracle’s CONNECT BY PRIOR is awesome. Does anyone know if this is possible in SQL Server or MySQL? From what I understand, stored procedures would need to be used in MySQL and SQL Server to accomplish what Oracle does in one SQL statement with CONNECT BY PRIOR. I need to dig into the other databases a bit further to verify, but I am interested in any elegant solutions people have come up with if you would like to share.
Convert INT to BOOLEAN in a SQL SELECT for Multi-Tiered Sorting
We have a family tree. Some people have children, while others do not. We want to sort results by alphabetically listing individuals with children in an initial group, and then those without children in a second group — also alphabetically. Let’s assume there is not an isParent column (nor the equivalent) on the table, but there is a way to determine how many children each individual has.
Data Validation and Cleansing – Technology, a New “Mouse Trap”
You lie in wait, looking for your prey, it approaches, and looks around. You spring the trap and
find the spoils of war…the dreaded %20
Ok, maybe not all that exciting, but it brings up an important problem in data conversion and in
application development projects. Namely the importance of input data trapping and input
validation.
Staging Data with MySQL, CSVed, and XAMPP
The Task: Take and load, using MySQL, a flatfile of over 450 colums and over 145,000 rows of data and put it into a staging table.
The Conditions: The data may not be altered prior to the staging load, or in the staging load. Only when the data is loaded from staging to the final tables may it be altered.