w thep16 - SQL Antipatterns - Avoiding the Pitfalls of Database Programming, Książki IT

[ Pobierz całość w formacie PDF ]
//-->SQL AntipatternsAvoiding the Pitfalls of Database ProgrammingBill KarwinThe Pragmatic BookshelfDallas, Texas • Raleigh, North CarolinaMany of the designations used by manufacturers and sellers to distinguish their productsare claimed as trademarks. Where those designations appear in this book, and The PragmaticProgrammers, LLC was aware of a trademark claim, the designations have been printed ininitial capital letters or in all capitals. The Pragmatic Starter Kit, The Pragmatic Programmer,Pragmatic Programming, Pragmatic Bookshelf, PragProg and the linkinggdevice are trade-marks of The Pragmatic Programmers, LLC.Every precaution was taken in the preparation of this book. However, the publisher assumesno responsibility for errors or omissions, or for damages that may result from the use ofinformation (including program listings) contained herein.Our Pragmatic courses, workshops, and other products can help you and your team createbetter software and have more fun. For more information, as well as the latest Pragmatictitles, please visit us athttp://pragprog.com.Copyright © 2010 Bill Karwin.All rights reserved.No part of this publication may be reproduced, stored in a retrieval system, ortransmitted, in any form, or by any means, electronic, mechanical, photocopying,recording, or otherwise, without the prior consent of the publisher.Printed in the United States of America.ISBN-13: 978-1-934356-55-5Encoded using the finest acid-free high-entropy binary digits.Book version: P3.0—March 2012An expert is a person who has made all themistakes that can be made in a very narrowfield.➤Niels BohrCHAPTER1IntroductionI turned down my first SQL job.Shortly after I finished my college degree in computer and information scienceat the University of California, I was approached by a manager who workedat the university and knew me through campus activities. He had his ownsoftware startup company on the side that was developing a database man-agement system portable between variousUNIXplatforms using shell scriptsand related tools such asawk(at this time, modern dynamic languages likeRuby, Python, PHP, and even Perl weren’t popular yet). The manager ap-proached me because he needed a programmer to write the code to recognizeand execute a limited version of the SQL language.He said, “I don’t need to support the full language—that would be too muchwork. I need only one SQL statement:SELECT.”I hadn’t been taught SQL in school. Databases weren’t as ubiquitous as theyare today, and open source brands like MySQL and PostgreSQL didn’t existyet. But I had developed complete applications in shell, and I knew somethingabout parsers, having done projects in classes like compiler design andcomputational linguistics. So, I thought about taking the job. How hard couldit be to parse a single statement of a specialized language like SQL?I found a reference for SQL and noticed immediately that this was a differentsort of language from those that support statements likeif()andwhile(), variableassignments and expressions, and perhaps functions. To callSELECTonly onestatement in that language is like calling an engine only one part of anautomobile. Both sentences are literally true, but they certainly belie thecomplexity and depth of their subjects. To support execution of that singleSQL statement, I realized I would have to develop all the code for a fullyfunctional relational database management system and query engine.• Click HERE to purchase this book now. discuss2•Chapter 1. IntroductionI declined this opportunity to code an SQL parser and RDBMS engine in shellscript. The manager underrepresented the scope of his project, perhapsbecause he didn’t understand what an RDBMS does.My early experience with SQL seems to be a common one for software devel-opers, even those who have a college degree in computer science. Most peopleare self-taught in SQL, learning it out of self-defense when they find themselvesworking on a project that requires it, instead of studying it explicitly as theywould most programming languages. Regardless of whether the person is ahobbyist or a professional programmer or an accomplished researcher witha PhD, SQL seems to be a software skill that programmers learn withouttraining.Once I learned something about SQL, I was surprised how different it is fromprocedural programming languages such as C, Pascal, and shell, or object-oriented languages like C++, Java, Ruby, or Python. SQL is adeclarativeprogramming languagelike LISP, Haskell, or XSLT. SQL usessetsas afundamental data structure, while object-oriented languages use objects.Traditionally trained software developers are turned off by this so-calledimpedance mismatch,so many programmers are drawn to object-orientedlibraries to avoid learning how to use SQL effectively.Since 1992, I’ve worked with SQL a lot. I’ve used it when developing applica-tions, I’ve developed libraries for SQL programming in Perl and PHP, and I’veprovided technical support and developed training and documentation forthe InterBase RDBMS product. I’ve answered thousands of questions onInternet mailing lists and newsgroups. I see a lot of repeat business—frequent-ly asked questions that show that software developers make the same mistakesover and over again.1.1Who This Book Is ForI’m writingSQL Antipatternsfor software developers who need to use SQL soI can help you use the language more effectively. It doesn’t matter whetheryou’re a beginner or a seasoned professional. I’ve talked to people of all levelsof experience who would benefit from the subjects in this book.You may have read a reference on SQL syntax. Now you know all the clausesof aSELECTstatement, and you can get some work done. Gradually, you mayincrease your SQL skills by inspecting other applications and reading articles.But how can you tell good examples from bad examples? How can you besure you’re learning best practices, instead of yet another way to paint yourselfinto a corner?• Click HERE to purchase this book now. discuss [ Pobierz całość w formacie PDF ]