Vagrant, Chef (installed via omnibus), and the PostgreSQL and Database cookbooks (re: COOK-1406)

blake.willardTechnical Tips2 Comments

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

MySQL and SQL Server – Oracle CONNECT BY PRIOR for Recursive, Hierarchical Data

AshokTechnical TipsLeave a Comment

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

john.kingTechnical Tips1 Comment

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.

Staging Data with MySQL, CSVed, and XAMPP

kevin.landonTechnical TipsLeave a Comment

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.