Database
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 metadata files themselves. The pg_hba.conf file controls what level of credentials are needed based on what IP address the requesting connection is coming from. The metadata within the engine itself generally controls user level access once they are connected and approved at the system level. Systemwide Configuration via pg_hba.conf This file matches IP address with a set of rules to determine how much data you need to provide in the first place before getting access to the database engine. It includes the IP address, the username ...
0 Comment | Posted in Database,PostgreSQL,blog
July 04, 2010
Logistics & Inventory Management with Alutiiq
Alutiiq recently award Cyber Sprocket a 1-year teaming agreement, making this our 4th year of working with Alutiiq on developing, supporting, & maintaining their military logistics & inventory management application. We are very excited to be working with Alutiiq for another year. The upcoming year will bring some exciting new possibilities for follow on projects that augment the system already in place. We're looking forward to being part of the design & development team and supporting our clients and our country both at home and abroad. Technical Overview Services Provided Web Application Programming Database Design Database Maintenance System Architecture Network Support Platform Details ASP.Net / VB.net IIS MS-SQL HTML Javascript CSS
Comments Off | Posted in ASP.Net,Charleston SC,Javascript,MS-SQL,VB.Net,portfolio
April 15, 2010
Consumer Web Apps with Abundatrade
When Abundatrade decided to take their project to the next level they chose Cyber Sprocket Labs to help them get there. They brought their existing website to us and asked us to help. They were looking for a more fluid, more enjoyable web experience for their users. They needed an updated easy-to-use web calculator & they needed it fast. Cyber Sprocket did the job quickly & did it right. More than a year after that first quick "help us fix our site" project we are still helping them push their technology further. We are now not only helping them slowly morph their consumer services into something bigger & better, we are also helping them run their operations more efficiently by integrating the web technologies with their daily operations & services that run the business. The main part of the consumer interface is a product valuation calculator. Using ...
0 Comment | Posted in Javascript,MySQL,PHP,portfolio
April 05, 2010
Data Schema Documentation Tool : SchemaSpy
A topic that came up today is that the 3DChat game database does not really have a reference schema. It used to, in the early stages of the Admin Panel repository, but was eventually phased out and replaced with a combination of PDFs and PNGs generated from a Visio document. There is an issue of usefulness here, with two sides to consider. 1. As a developer, I prefer to have a text, written, SQL schema to work with and refer to. Something I can feed directly into my local database engine. When I have questions about what a column is used for, or how tables relate, this is where I look first and where I expect to see the explanations. 2. However, I may be a developer, but not a developer who deals directly with the schema above. Consider 3DChat in particular. Our game database schema was in a Git repository that Golem ...
0 Comment | Posted in Data Management,Database,blog
February 25, 2010
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, there is an important follow-up lesson that I want to talk about. What I asked yesterday is representative of something we all seem to ask ourselves consistently: "What is the most clever way I can do this?" When it comes to database queries I believe we have a tendency to see how much we can accomplish in one swoop. At the heart of this tendency is the assumption that more queries equals less efficiency. Please strive to constantly challenge this assertion. Our thoughts about software should be continuously reinforced by demonstrable ...
0 Comment | Posted in PostgreSQL,SQL,blog
January 22, 2010
SQL Movement Commands
Most programming modes in Emacs support two commands, beginning-of-defun and end-of-defun. These move you to the beginning and end of the nearest function, and by default are bound to C-M-a and C-M-e, respectively. However, sql-mode does not have anything similar. I have the above two commands bound to C-up and C-down for quickly moving around functions, but I find myself often hitting them when viewing an SQL file to move through tables---which doesn't work. Fortunately that functionality is pretty easy to add. For my fellow Emacs users, here is some code I whipped up that you can use to easily move between tables and views. (defconst sql-beginning-of-table-or-view-regexp "^\\s-*create\\s-+\\(?:table\\|view\\)" "Regular expression for matching the start of an SQL table or view definition.") (defun sql-prev-table-or-view () "Move backwards to the beginning of the nearest table or view from point." (interactive) (re-search-backward sql-beginning-of-table-or-view-regexp nil 'noerror)) (defun sql-next-table-or-view () "Move forwards to the beginning of the nearest table or view from point." (interactive) (re-search-forward sql-beginning-of-table-or-view-regexp nil 'noerror)) Then you ...
Powerful Data Insertion Features in MySQL
There are several powerful techniques for getting your data into a MySQL database. MySQL supports non-standard extensions to SQL, giving you more flexibility in certain situations. If you want to take full advantage of MySQL, keep these tips in mind. The Problem Say you need to shove some new data into a table. Naturally, you want to INSERT. But it so happens that if the data is already there, you want to UPDATE the existing data. You don't care if you have to INSERT or UPDATE - you just want the new data in the table. You decide do one of two things. You might try DELETE FROM yourtable WHERE key='yourkey'; INSERT INTO yourtable (key, data1, data2) VALUES ('yourkey', 1, 2); This is simple and effective. You could also check to see if the record already exists, and if so, UPDATE. What you really wish you had is an "INSERT OR UPDATE" command. Allow me ...
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 it into a customer inbox for later retrieval in their required format. The System Today Over the years this system has grown into a fully automated, fully customizable web portal for sharing and translating data between clients. The system now supports over two dozen input & output file formats from a variety of sources. New file layouts can be created and managed via a 100% web driven interface. The administrator can select from several base files types such as CSV, XML, printed report layouts, fixed length, and multi-record fixed length formats. A simple data mapping application allows ...
Comments Off | Posted in Perl,PostgreSQL,System Administration,portfolio
January 05, 2010
Issues With MySQL
If I had my choice I'd ALWAYS choose PostgreSQL over MySQL every time. It is a stronger, more reliable, more robust database engine without question. So why do I even think about MySQL, ever? Because I have to. It is my job to know technology and, unfortunately for the industry at large, MySQL is the defacto standard for nearly every open source web application in existence. The very predominance of MySQL does not make it better, just a necessary evil that we have to deal with every day. Unfortunately the pure power of numbers often forces us to create new systems in MySQL just for the sake of simplicity on our client systems. The Complaints List So why do I dislike MySQL so much? There are a number of reasons. Granted, MySQL has done a good job in the ...
Custom Site & Store Builder with Energy Inc.
The Energy Detective (TED) is a consumer based product that helps home users track their energy usage on a per-device or cross-household level. When Energy Inc, the makers or TED needed to upgrade their site with an easy-to update content management system (CMS) and the addition of a custom storefront, they came to Cyber Sprocket Labs. Within months we had ported their old static-page driven site to our new custom site builder. They could now easily update their own content without getting developers involved, and better yet - the system protected them from inadvertently breaking their site design. The staff at Energy Inc. soon became experts at the system and added new content as well as new product models to the site. The site also started with a simple storefront module. It allowed Energy Inc. to upload new products and track inventory levels to ensure ...
0 Comment | Posted in Cloud Computing,MySQL,PHP,portfolio
December 17, 2009
PostgreSQL Stored Procedures – Part 2
In a follow up to our initial Stored Procedures article, here we present some more complex examples based on real-world work we've completed on a PostgreSQL 8.3 database. Fetching A Newly Inserted ID Often times you will want to insert a record into a table that has a default ID string. There is any easy method for doing this with the INSERT...RETURNING syntax. In this stored procedure example below we add a new group name to our groups table and get back the auto-generated ID (primary key) in the newid variable. We then use that to insert a record in our group members table AND return the newly created ID back from the stored procedure, making it easy for the calling program to catch the new ID: DECLARE newid BIGINT; BEGIN INSERT INTO groups (group_name) values ($1) RETURNING group_id INTO newid; INSERT INTO group_members (group_id, ...
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 sets and ensure standards compliance. The following paragraphs will show the basic syntax and some simple examples for using stored procedures in PostgreSQL. Syntax CREATE [ OR REPLACE ] FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [ RETURNS rettype ] { LANGUAGE langname | IMMUTABLE | STABLE | VOLATILE | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | COST execution_cost | ROWS result_rows | SET configuration_parameter { TO value | = value | FROM ...
0 Comment | Posted in Database,PostgreSQL,SQL,blog
November 10, 2009
Database Definition Languages
Intro Keeping your application code updated with the latest version is fairly easy these days. Version control systems such as CSV, Subversion, and Git are everywhere and are very efficient at keeping all your code files inline. However, keeping your database changes in sync with your code is more of a challenge. The following document is a work in progress, outlining the challenges and solutions we've come up with in keeping our distributed applications well managed in a source control system. That means the ENTIRE application, the code and data all living together in perfect harmony. Current Direction Our current train of thought is to pursue a standard database definition language (DDL) that keeps our core data structures in a standard ASCII format that can be easily managed with code-centric version control systems. We'll graft on a definition for version information to be attached to tables, indexes, ...
MySQL Introduction
MySQL is a common database for web based applications, primarily because it is free and there is a lot of community support for the database. The most recent version of MySQL (v5 as of this writing) has even added some "big boy" features to help create robust database applications. MySQL Versions The current release of MySQL is 5. Notice from MySQL AB... End of Product Lifecycle Active development and support for MySQL database server versions 3.23, 4.0, and 4.1 has ended. However, for MySQL 4.0 and 4.1, there is still extended support available. For details, see http://www.mysql.com/company/legal/lifecycle/#calendar. According to the MySQL Lifecycle Policy (see http://www.mysql.com/company/legal/lifecycle/#policy), only Security and Severity Level 1 issues will still be fixed for MySQL 4.0 and 4.1. Please consider upgrading to a recent version (MySQL 5.0 or 5.1). Linux Command Line Commands Some basic MySQL commands you can execute from the Linux command line are shown ...
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 reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among ...
0 Comment | Posted in Database,PostgreSQL,blog
May 08, 2009
