The recent Emacs post made me think of a useful SQL trick I recently learned that I wanted to share. Emacs refers to different SQL engines as ‘products’, e.g. MySQL, SQLite, and so on. You can connect to an SQL database through Emacs but you have a call a different command based on the product. An easier [...]
23
Dec
PHP + PostgreSQL App Performance Example
One of our projects this week was to improve the performance in a payment processing system for a client. The system performed well under limited data sets, but as the data set grew larger the performance response time increased exponentially. In reviewing the application we found several design flaws that were significantly impacting performance. This [...]
14
Oct
PostgreSQL – Exclusive Locking On Triggers
We have been working on a client’s project and dealing with an especially complex view in a PostgreSQL database. The view has a few tables linked together as well as several sub queries, a case statement, and a number of elements that do aggregate functions to build a single record of the view. After updating [...]
25
May
SQL: Using IN / NOT IN Operators
Here is an explanation I recently shared with someone about how to use NOT IN to select a subset of data from a one-to-many relationship. It is a simple example that covers the basic use of NOT IN (and IN) operators in SQL. These operators can be fairly powerful for performing quick queries on complex [...]
16
Nov
Restaurant Apps with The Table Tap
Cyber Sprocket Labs started a fun and exciting relationship that is the perfect cross section of two of our favorite things, programming and beer. While we’ve worked on many exciting projects over the years, this is definitely in the top two. None of our esteemed programmers ever thought they’d be working on an application that [...]
04
Jul
PostgreSQL Cheat Sheet
PostgreSQL is one of our favorite database engines for a variety of reasons. Here is our cheat sheet to help you get online and get around Postgres with minimal effort. Database Access Security Database security is handled primarily in two place, from the system service level via a file called pg_hba.conf and within the database [...]
22
Jan
Analyzing Queries in Postgres
Yesterday I was curious if there was a good way to write my IDS query using a single ‘select’. All of us had the right idea of using a sub-query to limit the results from the join, although there were different ideas about how to use a sub-query. Richard’s solution was the most succinct. However, [...]
05
Jan
Data Translation Service with IDS
Cyber Sprocket Labs first came across Inlet Data Systems (IDS) in January, 2005 via a simple Guru posting asking for assistance “converting data from one proprietary format to another”. The objective was to created an automated process by which a client could upload a file which would be automatically processed by the system & put [...]
10
Nov
PostgreSQL Stored Procedures Part 1
Stored procedures are a great way to enforce business and data access logic in a complex environment. Often with larger projects you will have multiple applications architected by a varied set of professionals all interacting with the same data sets. Stored procedures are a tried and true way to regain some control over those data [...]
08
May
PostgreSQL Introduction
Intro Possibly the most robust freeware DB on earth. Some argue better than MySQL even though MySQL has a much larger following. From the makers PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for [...]
08
May
Postgres::Handler
Our local updated perl docs. These enhanced notes will be published on our next push to CPAN. Name Postgres::Handler – Builds upon DBD::Pg for advanced CGI web apps Description Postgres::Handler builds upon the foundation set by DBI and DBD::Pg to create a superset of methods for tying together some of the basic interface concepts of [...]
Talk To Me...
Enter your email below to sign up for the monthly Store Locator Plus newsletter. Click the Facebook icon to get almost-daily updates on what I'm working on now. The RSS feed icon will bring my bi-weekly blog posts to your feed reader.
