dba4life

Put a Diaper On It

noreply@blogger.com (Benjamin) — Wed, 10 Aug 2011 16:22:00 +0000

Inspired by Adam Machanic's T-SQL Tuesday topic of 'Crap Code' I decided to take a bit of a different bent--preventing crap code. Instead of coming off as incredibly arrogant by admitting that I cannot recall code that I have written that I am embarrassed of, I thought to answer why that is.

There is definitely code that I revisit that I would like to rewrite bits of. Generally, this is because I am better today than when I wrote the code; learned a new trick, new version has a better function, got a bit more sleep. So what is it that prevents, or at least corrals, crap code? Defined purpose and consistent code. If your code explains why it is doing what it does and is consistently formatted, it is hard to argue with your original reasoning.

So go easy on yourself, jamiet--that is not crap code, that is good code dealing with crap.

Quote

noreply@blogger.com (Benjamin) — Wed, 27 Apr 2011 23:47:00 +0000

"Good design is anticipating the stupidity of users."

Discuss.

Documentation

noreply@blogger.com (Benjamin) — Mon, 07 Mar 2011 22:26:00 +0000

If your work isn't documented, it is worthless. This is my new modus operandi. A couple years of silence is largely due to working projects with failed documentation. It is incredibly draining.

I spent my 20's shirking documentation; excuses like, 'the code is self-documenting (I am decent at effective minimal commenting),' 'this is a one time deal.' There were always reasons not to document.

I am paying for that now.

So do yourself a favor and document. It is a little something to make certain that two parties have reached an understanding. It is a little something to help those that follow you. Sure, it is mundane, but documentation is for heroes.

SSRS: Current Month Date Parameter Defaults

noreply@blogger.com (Benjamin) — Thu, 28 May 2009 02:39:00 +0000

Now and again, I have to pretend that I am a report monkey. SQL Server Reporting Services makes that easy, since most of things are relatively intuitive. Today, I got to figure out (and by 'figure out,' I mean 'Google') the easiest way to create report parameters that default to the first and last days of the current month. The following is what I came up... Surprised that there is not a function to readily do this. So, without further ado, for my future reference, I present the following expressions.

The first of the current month:

=FormatDateTime(

    dateserial(year(Today), month(Today), 1),

    DateFormat.ShortDate                                             -- Format as mm/dd/yyyy

)

The last day of the current month:

=FormatDateTime(

     DateAdd(

        "d", 

-1,

        DateAdd(

            "m", 

1,

            dateserial(year(Today), month(Today), 1)

)

),

    DateFormat.ShortDate

)

And free of formatting and comments, for your copy-and-paste convenience:

=FormatDateTime(dateserial(year(Today),month(Today),1), DateFormat.ShortDate)

=FormatDateTime(DateAdd("d", -1, DateAdd("m", 1, dateserial(year(Today),month(Today),1))), DateFormat.ShortDate)

Wolfram Fail

noreply@blogger.com (Benjamin) — Sat, 16 May 2009 23:44:00 +0000

I have been looking forward to Wolfram Alpha going live. I am no longer so impressed. After trying to find how many state employees California has, to no avail, I asked a simpler question. It looks like, contrary to the hype, Google is safe for a while...

Perl: Convert PDF Files to Text

noreply@blogger.com (Benjamin) — Sat, 07 Feb 2009 02:19:00 +0000

Using the CAM::PDF package, it is easy to extract the text from PDF files. The following script takes all PDF files from a directory and extracts the text of the entire file and writes it to a text file.

This script is also available at dba4Life.


use IO::Handle;

use strict;
use warnings;

use CAM::PDF;
use CAM::PDF::PageText;

my $PDFDIR = "./SomeSubDirectory";
my $pdf;
my %ddl;

opendir DDL, $PDFDIR || die "Error in opening PDF directory $PDFDIR\n";

while((my $filename = readdir(DDL)))
{
# Skip non-PDF files
next if ($filename !~ /\.pdf$/);

$filename = $PDFDIR . '/' . $filename;

if(!-f $filename) { print "\nCould not load $filename";}

# Name output file same as the PDF
my $output = $filename;
$output =~ s/\.pdf/\.txt/;

print "Creating $output...\n";
open(TXTFILE, '>' . $output);

# Load the PDF
$pdf = CAM::PDF->new($filename);

# Total number of pages within the PDF
my $pages = $pdf->numPages;

# Get the text for each page
for(my $x = 1; $x <= $pages; $x++)    {    print TXTFILE text_from_page($x);     }     close(TXTFILE); }  closedir DDL;     sub text_from_page  {   my $pg_num = shift;   return CAM::PDF::PageText->render($pdf->getPageContentTree($pg_num));
}

Simple SQL: Optional Parameters, Static Statements

noreply@blogger.com (Benjamin) — Thu, 16 Oct 2008 23:42:00 +0000

Dynamic SQL, as wonderful a thing as it is, is horrible for security and maintenance. Your best bet is to avoid it. People often whine about making parameters optional. If you set the parameters that you are not using to null, this is a simple thing to do:

declare
@state char(02),
@zip char(05)

set @state = 'CA'
set @zip = null

select
Address
from
CustomerAddress
where
State = coalesce(@state, State)
and Zip = coalesce(@zip, Zip)

Simple and elegant.

Tags: sql

EC2-SQL: Competition in the Cloud

noreply@blogger.com (Benjamin) — Thu, 02 Oct 2008 00:23:00 +0000

Not to be outdone by Oracle, Microsoft SQL Server will be available on Amazon's EC2 come late autumn. Read about it here... And you may want to read up on Microsoft's Windows Cloud operating system for developers at Slashdot. Is Microsoft adapting? Come on, now... It happens... Sometimes... Seldom.

And so goes another step for cloud computing...

Tags: amazon, mssql, MicroSoft

Sulphur Clouds! Oracle on Amazon's EC2

noreply@blogger.com (Benjamin) — Thu, 25 Sep 2008 15:46:00 +0000

Oracle puts its 11g database in Amazon's cloud. With a title like that, what can I add? This is a well written article about Oracle on EC2. They are also supporting backups being shipped to Amazon. Anything else and I will be reiterating the article.

Tags: amazon, oracle

Oklahoma Finds Next Level of Stupidity

noreply@blogger.com (Benjamin) — Fri, 19 Sep 2008 19:00:00 +0000

Wow. That is all that I have to say after reading this article:

Oklahoma Leaks Tens of Thousands of Social Security Numbers, Other Sensitive Data.

Technically, State information is public... But this goes beyond overshare. You just know that the application account has escalated privileges... I have to go back to my opening statement; wow. What a kick in the head...

Tags: security, sql injection

Bad Design: A Reminder to Document

noreply@blogger.com (Benjamin) — Wed, 17 Sep 2008 17:36:00 +0000

Brian Walker writes about SQL Server database design disasters: What not to do. Unfortunately, these design failings are all too common. We all take different things from articles; my tangent is about a lack of documentation.

As a nerd, I would much rather do the fun part--creating, troubleshooting or anything else that requires thought. As a consultant, time and time again I have seen poor designs that are compounded by a lack of documentation. Not determining standards and and conventions, you facilitate others mutating a design with their own habits. Even if you have a poor system, the maintenance of it can be helped by documentation. Poor and consistent is a far cry better than poor and inconsistent. That makes it generally referred to as 'awful.'

It really does not take much time to document your approach. Simple documentation to outline the what and the why--even just a paragraph or two--might save countless hours down the line. Bad design is often perpetuated by lack of formalizing the business process. The least that a good database designer (or architect, if you prefer (I do--who doesn't want to be an architect?)) can do is document their approach; a naming convention document and a data dictionary. It is also important to do this before, during and after the design/implementation. Saying you will do it later typically means a rushed deadline is justification for not documenting (yeah, I have used that excuse too). Perhaps writing it out will key you into a couple weaknesses of your design... And wouldn't that make that horrible documentation process worthwhile?

Tags: design, documentation

Random: Data Presentation of Discretionary Spending

noreply@blogger.com (Benjamin) — Wed, 10 Sep 2008 17:08:00 +0000

The New York Times has an interesting article about discretionary incoming spending, broken into a few categories. Numbers are always interesting, but what really got me was the presentation of the data--very cool way of showing the world.

More on Naming Standards

noreply@blogger.com (Benjamin) — Tue, 02 Sep 2008 17:59:00 +0000

Ronald Bradford has a very nice article about naming conventions--and not just because we generally line up on our opinions.

Tags: design,
naming convention

Singular Table Name

noreply@blogger.com (Benjamin) — Thu, 21 Aug 2008 21:21:00 +0000

Almost as bad as prefixing tables with a table-indicator is using plural names. Even Oracle tools default to this... It is just wrong.

A relation constitutes a header and a body. The header (or relational schema) is a set of attribute (column) and domain (value) pairs. The body is the set of tuples (rows). A tuple is a valued occurrence of the header.

Consequently, if you make your table names plural, make your column names plural for consistency.

Less mathematical justifications for using singular tables names include:

Consistency - Because sometimes you need 'es' to pluralize a word.
Consistency - Shouldn't 'ProductOrders' be 'ProductsOrders?'

Just like your mother taught you, just because everyone else is doing it, doesn't make it right.

Tags: design,
naming convention

Newish DBA Comic Strip, World Rejoices

noreply@blogger.com (Benjamin) — Fri, 15 Aug 2008 15:39:00 +0000

For a few weeks I have been following The Adventures of Ace, DBA. There have been a few funnies. And today's is rather good (the previous Error Code strip was good too). It is a new database-centric stick figure comic strip. There is no more to read here; go, be amused.

SQL Server 2008 Released

noreply@blogger.com (Benjamin) — Thu, 07 Aug 2008 16:48:00 +0000

SQL Server 2008 is now available. Read about what's new and the return of the debugger.

Tags: mssql

Fiction For A Better Reality... Please

noreply@blogger.com (Benjamin) — Wed, 06 Aug 2008 22:05:00 +0000

Cory Doctorow plugged his new story, The Things that Make Me Weak and Strange Get Engineered Away 'about geek monasteries that house smart people who can't get along in the world and put them to work as coders.' It is hosted by Tor--great SF publisher that gives out free books! Well, they used to... Have not received one for a while. In any event, check out Tor.

Make it real! Great premise... Any interested investors out there?

Tags: fiction, Creative Commons

Microsoft Leads Data Warehousing to Happiness

noreply@blogger.com (Benjamin) — Thu, 24 Jul 2008 21:14:00 +0000

This headline covers it all:

Microsoft Acquiring DATAllegro, Rebooting Data Warehousing Appliance Strategy, Triggering Industry Consolidation

Or you can read the official press release. Hopefully they do better with data than comedy.

Tags: data warehouse, dbms, Microsoft

Database Best Practices

noreply@blogger.com (Benjamin) — Thu, 24 Jul 2008 16:48:00 +0000

Preamble

Like all things, we tend to speak in black and white while we act in grays. This implies that we should be wary the individual that speaks in grays because they probably act in black and whites. Documentation should always be black and white; we want to provide a body of work that elicits understanding.

Best practices define the tried and true methodologies. The vast majority of the time, we can and should abide by the conventional wisdom. All too frequently, people justify a poor approach by citing complexities. It has been my experience that if you cannot make something sound simple, it is not well understood. Complexity is just a sequence of simple steps. Just like designing a database, break down the concepts and strive for the beauty of simplicity. If you arrive at something that cannot be fitted into the mold, accept that you may have to step outside of best practice; just make certain you document why.

Key Best Practice Points

Backup and Maintenance Plans – The first tenet of being a DBA; always have a backup plan. The backup plan should accommodate the business needs by guaranteeing minimal downtime and optimal data recovery.
Minimum Permissions –All database users should operate with only the permissions required to complete assigned tasks.
Change control – Change control ensures minimal downtime and efficient and effective updates. All proposed changes to production should be documented and provided in a manner that can be tested and rollback if necessary.
Separate Environments –Changes should be migrated across physically distinct environments; development, test and production. Developers should not have access to production environments. Test should be treated as production; changes should be tested against a production backup.
Documentation – Complete documentation serves to illustrate that thought went into the design, that maintenance is not an afterthought and provides a forum to justify design decisions and points of contention.
Comments – While code is largely self-documenting for those that know the language, clarifying the methodology with logic/process comments is tremendously useful.
Naming convention – Determine and adhering to a naming convention promotes consistent coding and documentation.
Always define primary keys – Candidate keys should be documented and a primary key should be physically enforced. If using surrogate keys, logical keys should still be physically enforced.
Constraints – Referential integrity constraints prevents orphaned data. Check constraints are the physical enforcement of business rules.
Normalization – Third normal form is the default level to design to.Avoid NULL – Understand what null values are. Minimize null values by properly normalizing.
Stored Procedures – Promotes maintainability via a separate and complete body of database code. Security is easier to implement by function. Improves performance.
Avoid wildcard selects – Explicit column lists reduce unnecessary I/O and processing. Additionally, they promote better change control.
Avoid Distinct – Adds I/O and processing. If data is not being uniquely selected, re-evaluate the query. Distinct is often a shortcut for lazy querying, poor design or both.
Derived statements – Derived statements can often drastically improve performance.
Explicit joins –Qualify intent by using explicit joins.
Minimize Cursor Utilization – Cursors are expensive operavtions; by thinking in sets, cursors are often rendered unnecessary.
Minimize triggers—Triggers are often misused to accommodate poor process and/or poor design. Triggers should not perform any complex actions, nor perform non-deterministic actions that may affect other data.
Avoid dynamic SQL – Dynamic SQL complicates maintenance, requires escalated privileges (explicit table grants) and typically negates performance benefits of stored execution plans.
Error/exception handling – After modifying operations, check the success. As well, general exceptions that are likely to arise should be handled.
Avoid wildcards at the start of strings – Wildcards reduce index efficiency; when used at the beginning of strings, indexes are largely disregarded.
Do not store derived columns – If you can calculate the values from existing fields, it should not be stored; use views to present derived values.
Minimize text/blobs – Fundamentally, compound objects do not belong within the relational model.

Tags: best practice, dba

Flipping Awesome

noreply@blogger.com (Benjamin) — Wed, 23 Jul 2008 18:03:00 +0000

PressFlip is a learning recommendation service... It is smart. And cool--so cool it destroyed my thesaurus. Text analytics is heading for huge, so check out a very slick implementation. Read Ted's unofficial press release. That guy can write... I missed his style.

Tags: text analytics

MySQL: No Interactive Cursors? Curses!

noreply@blogger.com (Benjamin) — Fri, 18 Jul 2008 21:18:00 +0000

MySQL, the billion dollar open source database management system that runs some pretty serious websites. A database engine that does not allow you to run interactive blocks nor cursors. That you cannot even interactively reference a variable?! No wonder more people do not use stored procedures with MySQL...

Will people please tell me that I am wrong, that I have missed something?

Tags: mysql

Null Understanding

noreply@blogger.com (Benjamin) — Tue, 15 Jul 2008 00:32:00 +0000

Defining Null

Contrary to the first sentence of the Null SQL entry on Wikipedia, null does not mean 'no value' (the page on Three Valued Logic (3VL) gets it right). If it it did, 'select * from Address where Zip = NULL' would return the same results as 'select * from Address where Zip is null'. Strictly speaking, when a value is null it means that there may be a value or there may not be a value; we do not know the value. Null is the unknown-value-placeholder.

To Null, or Not to Null

Jihad! Nulls create a nice bifurcation in the database world. Basically, if you think nulls are bad, you get lumped in with the theorists. If you think nulls are good, well, a programmer or a practitioner (it gets a little muddy--null?). The truth is, null values and logic do have their place. It may not be a comfortable place (and not somewhere I want to go), but they have come to be a necessity.

Nulls are handled differently than known values. Consequently, the presence of null values often creates complexity. If the value is missing, we sometimes want to include it in our set, and sometimes not... Isn't that a key point to 3VL?

The important point is to use nulls as they were intended; to reflect a value in an unknown state. Do not use the null for non-values or missing values--that serves to perpetuate the misunderstanding.

Personally, I am on C.J. Date's side--don't use them. Even so, there are times when a design is conducive to null logic.

Codd later proposed a Four Value Logic for the Relational Model. By that time (and in part because), nulls were being misused by the practitioners. I wonder if the 4VL had survived if we would still misuse nulls or if the whole debate would be null and void...

Tags: null, sql

Deifying Logic

noreply@blogger.com (Benjamin) — Thu, 10 Jul 2008 16:54:00 +0000

I stumbled upon a very cool diagram. It illustrates, literally (err... figuratively? (seriously, why is 'literally' so often misused?)) "16 permutations formed by affirming or denying two propositions, A and B." A strong understand of basic logic helps us with all facets of life, not just optimizing our query skills.

Relational vs. Cloud

noreply@blogger.com (Benjamin) — Thu, 03 Jul 2008 17:55:00 +0000

The Scale Out Blog has an interesting article about the relational model and cloud databases.

SQL Injection: Not an Issue

noreply@blogger.com (Benjamin) — Wed, 25 Jun 2008 16:32:00 +0000

Denis Gobo has a post about Microsoft addressing SQL injection. Microsoft is being more proactive in preventing SQL injection by providing tools to scan sites and code. Here is a thought; stop publishing example code with inline SQL. Best practices are 'best' for a reason. Denis closes by stating that the 'problem is of course that you should never ever run as dbo or even worse sa.' And it really is almost that simple. An application account should have the minimum permissions needed for execution. If you use stored procedures, you do not even need to grant select permissions on tables (you aren’t using dynamic SQL, are you?). What is better than that?

Best practices tell you to check your inputs but really you can be rather lazy about this if you are parameterizing (it’s a word now) your stored procedure calls and are not using dynamic SQL; pretty hard to inject a string into a number field. That means that all you need to do is validate strings. Even if you do not do that, the account can only connect and run procedures. Failing this (because you really absolutely cannot see how you cannot use dynamic SQL (think harder)), use parameterized queries. Make certain that the application limits inputs—why is a name field large enough to contain an entire SQL block?

So what have we learned?

Minimum permissions
Use stored procedures
Check your inputs

I do not mean to oversimplify, but I cannot make it sound complicated. If you code right, your work is done. SQL injection is an issue for half-assed developers. The end.

Tags: sql injection, security