Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

We are sorry to see you leave - Beta is different and we value the time you took to try it out. Before you decide to go, please take a look at some value-adds for Beta and learn more about it. Thank you for reading Slashdot, and for making the site better!

SQL Hacks

samzenpus posted more than 7 years ago | from the hack-away dept.

72

Scott Walters writes "Many of the recipes in SQL Hacks will improve the SQL you write day to day, and many will give you the confidence to attempt much more involved tasks with SQL. Other recipes will rarely if ever be needed, but make for a entertaining and education reading in a similar way that "worse case survival scenario" books do — SQL is pitted against the most difficult analysis tasks just as survival scenario books pit humans against pavement and lions. SQL Hacks fits well in the Hacks series, which raises the bar on advanced books by offering large, eclectic sets of tricks for problems that an unambitious person (a non-hacker) wouldn't ever push technology hard enough to run into. Put another way, the questions answered in a good Hacks book are ones that would get a "good question" comment rather than a an "RTFM!" response. It does a good job continuing where O'Reilly's SQL Cookbook left off, which is always difficult with two books written at slightly different times by different authors. Still, it's harder to review a Hacks book than a Learning book as, with hacks, the sky is the limit, and the reader will always find herself wishing for more. To this end, I hope O'Reilly continues to publish newer editions of their various Hacks books, drawing in more and more content in each edition, and identifying recipes that might better serve in the Cookbook counterpart." Read the rest of Scott's review.

SQL Hacks skips most of the tutelage and shows you very specific ways for doing specific chores, with more explanation of how to adapt it than theory behind it. Most hacks have database specific information for the five databases the book tackles, and many hacks are inherently different on each system, making them completely different solutions to the same problem. Those five databases are Microsoft Access, Microsoft SQL Server, MySQL, Oracle, and PostgresSQL; most of the ideas require work to adapt or are completely specific to the database system, so I wouldn't suggest straying from this supported set. The authors did their homework, and SQL Hack's strengths are the depth, detail, and level of knowledge with which each database system is covered, and the book's willingness to get down and gritty. There's never an impression that juicy details were omitted because the authors didn't want to expend the effort to pick a colleague's brain or hunt down a factoid that never got documented elsewhere. Learning how to create indices on functions with multiple arguments in Postgres was worth more than the "hack" it was a footnote in. This dedication carriers over to screen shots showing how something is done in Microsoft Access directly opposite Unix shell pipelines between grep, perl, and the SQL command shell. Most books, including mine, are a bit awkward or vague on either Unix or Microsoft Windows, but the author's and contributor's experience on this one expertly covered platforms specific database topics. Besides just database systems and platforms, the authors challenged themselves to show how to securely and efficiently use the database interfaces of a set of languages: C#, Java, Perl, Python, and PHP. The polish shows, and you'll have absolute confidence that all of the tricks really are at your fingertips, regardless of your choice of operating system, database system, or programming language.

It gets bonus points for mentioning non-obvious types of input, such as cookies, that must be sanitized or sent through bound parameters, in its discussion of SQL injections. In the security department, it looks at SQL injections from three points of view: early on in the book, correct code is shown; later, SQL injections are shown from the point of view of the attacker, with several pages of strategies and scenarios for formulating attacks; and then from the point of view of the defender, who has to defang and avoid these scenarios — extra bonus points for this comprehensive treatment.

If you're looking for a quick buy/don't buy indication, then, by all means, buy it. That is, assuming that it's not intended to be your first or only SQL book. By it's own indication, it won't teach you the basics of database normalization, installation, and so forth. I would buy it as a second SQL book, though, after the fantastic 'The Practical SQL Handbook', as it's written to a much higher standard than most books, and gets things right, such as security, the intricacies of using a database to handle accounts, and transactions and shopping carts. The cover text promises lots of advanced hackery, but that's vague. "Pushing the limits of SQL"... "Solve puzzles using SQL"... "Manage users and audit the changes they make to the database".

Here are the major sections: SQL Fundamental; Joins, Unions, and Views; Text Handling; Date Handling; Number Crunching; Online Applications; Organizing Data; Storing Small Amounts of Data; Locking and Performance; Reporting; Users and Administration; and Wider Access.

Wider Access requires some explanation. It deals with locking down the various database systems to securely providing guest accounts, or, more generally, to limit damage in the case of an SQL injection attack or similar compromise.

With some well designed tables, SQL Hacks will show you quite a few tricks, some of them quote involved, quite non-obvious, and quite clever, to extract meaning from the data. You'll probably learn quite a few new types of reports you can do — intersecting ranges from different sets of data, outputting SVG pie charts, swapping rows and columns, finding medians, computing running totals, and computing running functions such as compound interest struck me as the most useful and got mental bookmarks.

I have two metrics for this book. The first metric is whether I'd buy it if I came across it in a book store, and that's a function of whether I'd have exhausted what it had to offer after an hour or so of furious skimming and intentionally picking out the best parts from the table of contents. Very few books make this cut for me.

The other metric is whether the authors did at least what I imagine I would have done were I writing it. This test is also a difficult one but builds in a great deal of forgiveness as my ideas are quite likely dumb ones.

I totally dig the cut-and-paste ASCII query results. The authors could have easily marked all of those up in DocBook and made it prettier but also alien compared to what you'll see at the computer. They're not ashamed of the SQL command shell, and they're not ashamed of SQL.

Many hacks have several examples, covering the problem with different constraints and end goals in mind.

Multi-platform, and thoroughly so. One moment, it's showing how to use XSLT tools from the command line on Microsoft Windows, and on the next page, there's a Unix shell pipeline with wget, xsltproc, and grep. Perl one-liners abound, and there are screen shots from Windows applications with instructions for navigating the menus and setting the needed options. You won't feel shortchanged for running the "wrong" platform.

When a powerful, modern SQL extension, such as replace, gets ratified by the standards committees, the authors let you know. Sidebars are spread around sharing the good news that sometime you might not have heard of before is portable. At the same time, some features are just fluff, and you're warned off of operations intentionally left out of the SQL92 standard.

Sometimes database systems have non-portable local extensions, such as MySQL's full-text indexing and SQLServer's XML handling features, and lots of these get motioned too, usually as variations on examples demonstrating the feature as a short-cut or simplification.

The treatment of security is first rate. The polish is top notch. Writing a book is a huge undertaking, and the economics of book publishing gives publishers little margin for advances. A book that reads like it's third release but is actually in its first can only be the product of an exceptional level of dedication by the authors.

Rarely, the authors do get tutorial-ish, but only a little, and I think it works: "Choose the right join style for your relationship" deals the difference between inner and outer joins, and whether records should be partially populated with nulls or omitted entirely when relations between tables can't be made for a record. Another section shows how to convert between subqueries and outer joins, and talks about when it's possible, and this serves as a sort of lesson in demonstrating the equivalencies between the two.

The "Hacks" format is similar to the "Cookbook" format. Both offer small, randomly-accessible (flip to it when you need it) examples of how to accomplish various tasks. In the traditional, MIT circles, a hack is piece of work that's either brilliant in its simple elegance or else brilliant in its expediency and simple effectiveness, and as such, is worthy of some esteem. It's also work that's custom for a particular scenario and has limited domain — in other words, it's a highly specialized fix or improvement. If a stock fix is applied systematically, that's mechanical, not clever. By this definition, showing users how to invoke their SQL monitor, or showing users how to decide whether to use an outer or inner join, are not hacks. Few of the recipes triggered this peeve, and they were early in the book, but including those few muddles the question of who the audience is, and lowers the standard for the Hacks series, endangering its basic premise. 'SQL Hacks' isn't alone in this sin; most of the Hacks books do it to some degree.

It was written by two professors at Napier University in Edinburgh, Scotland. The style, grammar, and presentation are perfectly fine — but only that. It's not a bone dry college text book, but it was written with a dedication to professionalism that can make a technical book tedious and will certainly keep it from becoming a classic. The literary power of Brooks, Hoare, or Wall is conspicuously absent.

Authors of Hacks books are at liberty to tap the experiences of the best and brightest of the field, and the best and brightest often have tricks just too strange, clever, or specialized to fit into any ordinary sort of text. I'd like to imagine that if I were charged with writing one of these, I'd have hundreds of contributors (I'm not likable, but I am persistent). Nothing against the contributors (two of them more than 20 years experience each), but why stop at three?

I said I had two benchmarks: whether I'd be likely to walk out of a bookstore with it if I had an hour alone with it to try to get my fill, and whether it touched on the subject that I thought it should.

Before cracking the cover, I stopped to ponder what would really impress me, and what I'd like to see. The Internal Functional Programming Competition had a puzzle solved by the contest winner using SQL. I'd like to see similar combinatronics and optimization problems solved using SQL. I'd like to see a good implementations of semi-infinite-strings, the text indexing data structure and algorithm that Google uses. I've done a version of this, but my implementation leaves something wanting. When reforming badly non-normalized databases, I've had to build a normalized database in parallel and populate it from queries on the non-normalized one. It would be interesting to hear how other people approach that problem, and what I can learn from them. There are other jobs that I've tackled and managed despite never having been prepared for. Renumber a display_order priority on records in response to the user adjusting or reassigning priorities. Trees using self-joining tables is something more people should be exposed to, especially when presented with non-normalized data.

There was no semi-infinite-string implementation, but the book showed how to build full-text indexes the optimal way for each database, using built-in full-text indices and optional add-on modules offering full-text indexing. The renumbering example took the more general form of running-totals computations. There were a few examples of self-joining data, and one tree example visualized the structure. Normalizing data had tricks, including some with views, and it showed how to use Cartesian joins to do combinatronics problems. So, aside form one sort-of, the authors nailed my entire wish list. That's amazing — I've never had that happen before, actually.

The highest endorsement a book can earn from me (a cheapskate, who already has a good deal of knowledge from working the industry for ten years) is getting bought on a random trip to the bookstore where I hadn't been looking for or intending to buy anything, and paying full price on top of that. Books that are surprising, riveting, and so packed with information that I couldn't possibly copy all of the best parts down and exhaust it in an hour or two are the ones that get purchased in this manner. I have 'SQL Hacks' in my hot little hands here at home, so this benchmark is now synthetic, but... I'm somewhat undecided, and not sure whether I would or wouldn't walk out with it. More likely, I'd just put it on my wishlist and pick it up later, for a discount (I'm a cheapskate, remember). If you don't know how to do more than half of the things listed in the table of contents, most certainly buy it. If you find yourself frequently working with SQL and constantly face new problems, buy it. If you find yourself still learning SQL and wanting a variety of examples, buy it. If you're shopping for a handful of good SQL books, buy it.

On a scale of stuff laying around the house, I give it 7 gold stars, half a box of binder clips, some AA batteries, and a bottle of really good soy sauce.


You can purchase SQL Hacks from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.

Sorry! There are no comments related to the filter you selected.

i tried to do "hack" sql stuff once (0, Offtopic)

east coast (590680) | more than 7 years ago | (#17512348)

I tried to do "hack" sql stuff once but it kept throwing an error on "$313C7"

Error (0)

jrwr00 (1035020) | more than 7 years ago | (#17512390)

I tried messing around with SQL (but as you can see what happened to slashdot a while back) SQL can mess with your mind

SQL sux (0, Funny)

Anonymous Coward | more than 7 years ago | (#17512486)

grep foo database.txt is all anyone would ever need.

Re:SQL sux (0)

Anonymous Coward | more than 7 years ago | (#17524126)

MySQL is the database for you!

what kind of survival book is that? (2, Insightful)

macadamia_harold (947445) | more than 7 years ago | (#17512512)

SQL is pitted against the most difficult analysis tasks just as survival scenario books pit humans against pavement and lions.

This "survival scenario book" you're referring to sounds like it takes place at the zoo.

Re:what kind of survival book is that? (2, Funny)

Giranan (762783) | more than 7 years ago | (#17512676)

To be fair, it requires a good deal of training to weather the random assaults of monkey poo.

Re:what kind of survival book is that? (3, Funny)

truthsearch (249536) | more than 7 years ago | (#17512974)

I think he's saying programming complex SQL is like being thrown in a pit of lions, then covered in asphalt. I'd say that's about right.

PhD on the Cover? (4, Funny)

Aqua_boy17 (962670) | more than 7 years ago | (#17514438)

I personally like the picture of the post-hole digger on the cover. I don't know if that's supposed to represent data mining tools (a pick and shovel would have been more appropos in that case) or if it's just a tool to help dig yourself out of the hole you find yourself in after 'inheriting' a DBA position.

Re:PhD on the Cover? (0)

Anonymous Coward | more than 7 years ago | (#17527110)

Posthole diggers (from a childhood filled with similarily unfortunate experiences) are remarkably ineffective for digging any kind of holes other than the straight down variety... So perhaps this book will only allow you to dig yourself deeper in the hole?

Update on the link (-1)

Anonymous Coward | more than 7 years ago | (#17512558)

While for some reason the review links to B & N, it seems Amazon has it significantly cheaper [amazon.com] (look at the "Used and new..." listings).

MOD PARENT DOWN (-1, Redundant)

Anonymous Coward | more than 7 years ago | (#17513556)

Parent is a referral spammer, this is the link you are looking for! [amazon.com]

Re:MOD PARENT DOWN (0)

Anonymous Coward | more than 7 years ago | (#17514000)

Where's the referral link in the parent? One can clearly see one in the next comment down, though.

Re:Update on the link (0)

Anonymous Coward | more than 7 years ago | (#17513662)

Yes, let's all whine about software patents and then support Amazon, the leading proponent of software patents on the Web.

Amazon.com is selling it cheap! (-1, Troll)

Anonymous Coward | more than 7 years ago | (#17512602)

B&N has it for $23.99, but Amazon.com is selling it for only $20.69! Save yourself some money by buying it here [amazon.com] .

MOD PARENT DOWN, REFERRAL SPAMMER (0)

Anonymous Coward | more than 7 years ago | (#17512888)

Take a look at the parent's link, and you'll see the referral tag "kaleidojewel1-20".

Good Article (2, Informative)

b1ad3runn3r (896115) | more than 7 years ago | (#17512834)

Yes, offtopic, but I think it deserves to be noted that its a pretty well written review.

Re:Good Article (1)

Osty (16825) | more than 7 years ago | (#17513544)

Yes, offtopic, but I think it deserves to be noted that its a pretty well written review.

I must not have read the same review as you. The one I read was full of sentence fragments, run-on sentences, comma and semi-colon misuse and overuse, poor organization, minor typos, and unnecessary comparisons to the reviewer's own authoring abilities. The content of the review was decent, though I didn't get enough from the review to know whether or not the book would be useful for me. The presentation of the review left a lot to be desired. Assuming the reviewer really is a techinical author as he implies, he must have one heck of a good editor for his books. He should've asked his editor to proof this review before posting it up to Slashdot, because as it is right now it's only semi-readable.

Re:Good Article (1)

fm6 (162816) | more than 7 years ago | (#17514742)

Why is it offtopic? Whenever we get poorly written reviews (and that's most of the time) everybody bitches and flames at length. It's only fair to note when the reviewer actually knows what they're doing.

Preventing this kind of thing... (4, Funny)

Bazman (4849) | more than 7 years ago | (#17512928)

From: http://thedailywtf.com/Articles/The_Data_Cleanup.a spx [thedailywtf.com]

UPDATE OWNER_USER.all_candidates SET name = filter(translate(REPLACE(REPLACE
(replace(replace(replace(replace(replace(replace(r eplace(replace(replace(replace
(REPLACE(replace(replace(replace(replace(replace(r eplace(replace(replace(replace
(replace(replace(replace(replace(replace(replace(r eplace(REPLACE(REPLACE(
        name,
CHR(1),null),CHR(2),null),CHR(3),null),CHR(4),null ),CHR(5),null),CHR(6),null),
CHR(7),null),CHR(8),null),CHR(9),null),CHR(10),nul l),CHR(11),null),CHR(12),
null),CHR(13),null),CHR(14),null),CHR(15),null),CH R(16),null),CHR(17),null),
CHR(18),null),CHR(19),null),CHR(20),null),CHR(21), null),CHR(22),null),CHR(23),
null),CHR(24),null),CHR(25),null),CHR(26),null),CH R(27),null),CHR(28),null),
CHR(29),null),CHR(30),NULL),CHR(31),NULL))), ...I hope.

Re:Preventing this kind of thing... (3, Funny)

Otter (3800) | more than 7 years ago | (#17513234)

Being accustomed to human genome data -- my first thought was that whoever has 31 chromosomes has more pressing concerns than some ugly SQL.

Re:Preventing this kind of thing... (1)

vivian (156520) | more than 7 years ago | (#17519552)

It's obviously replacing non-pritning chars with null then doing something else with that in the translate and filter functions. The replace's are nested because there's no function to simply giv a range of values or array of values in the replace function.

If they had used Postgres or Oracle, you could have used a regular expression to replace all those posky non-printing characters with null.

Ok, I have had enough of this "HER" Stuff (2, Interesting)

Anonymous Coward | more than 7 years ago | (#17513182)

why is it in every book i read, and in also reviews, the author and reviewer refer to the read or user as "her" all the time. Do they actually thing the main demographic that is reading their tech books are women? I am not a sexist here but it is a fact that IT,coding,networking is a male dominiated field.. hacking linux exposed, and hacking web apps exposed also refered to me as a woman.. ??

Re:Ok, I have had enough of this "HER" Stuff (3, Funny)

theskipper (461997) | more than 7 years ago | (#17513694)

Well, when I code, I sprinkle niceties like "you look really nice today" and "have you lost weight?" among my comments.

Just in case it's a buxom blonde that peruses my c++ in the future.

Re:Ok, I have had enough of this "HER" Stuff (1)

Mike89 (1006497) | more than 7 years ago | (#17519154)

I do similiar things. Sometimes without remembering doing it. I found in an older project the other day that the top of the file was marked simply "Still 12 days till the Wii :-(".

I've found other strange stuff too. In a project from a couple of years ago I'd used a close friends birthday as the read handle for several files. It's interesting, because anyone else who goes through it will be at a lose as to what I was talking about (for the most part - they'd understand the Wii business). I also get to see what I was like in the past, without becoming a whiney blogger who talks about every single detail of my life in excruiatingly boring detail.

Re:Ok, I have had enough of this "HER" Stuff (1)

Keyslapper (852034) | more than 7 years ago | (#17513780)

No, they're just continuing to labor under the assumption that a lot of technical males get overly excited that there might be real girls out there that actually know what things like "grep", "floppy", and "hard drive" really mean.

Either that or they think writing "her" will eventually lead to more geeks encouraging their daughters to take up technical careers.

Of course, that's not to say there aren't already some very geeky girls out there ...

Re:Ok, I have had enough of this "HER" Stuff (3, Insightful)

Ardeaem (625311) | more than 7 years ago | (#17513948)

If this annoys you, do you think it annoys many women that the generic pronoun for animate things in English is "he"?

Re:Ok, I have had enough of this "HER" Stuff (0)

Anonymous Coward | more than 7 years ago | (#17516810)

Male is also neuter in English in many cases, so they should be tickled that 'he' is either male or neutered :D In all seriousness, though, the proper way to write a book to a mixed audience is to use the male pronouns. Regardless of whether that's politically correct, it IS grammatically correct. Didn't the editor catch this?

Re:Ok, I have had enough of this "HER" Stuff (1)

Ardeaem (625311) | more than 7 years ago | (#17518220)

Actually, it doesn't matter. Either is grammatically correct. Perhaps by grammatically correct you mean standard style, but even this isn't so much true any more. There are several alternatives being used, and all of them get the point across. These conventions change all the time and they are completely arbitrary.

Re:Ok, I have had enough of this "HER" Stuff (0)

Anonymous Coward | more than 7 years ago | (#17514362)

The reason is, a lot men in the computer industry are wimps, and they are afraid they might offend a woman someday, and so they act like they are "feminist" or "sensitive" or whatever. It's just the pendulum of sexism swinging the other way.

See it's like this:

Women read "he" in a book and they get offended and start organizing protest groups and whatnot. Then the men who aren't getting laid freak out and say "whoa, we better do what these women say, or we won't get laid."

Men read "she" in a book and they just don't give a shit either way, they just assume a wimp wrote the book and they chuckle and do their job anyway.

Why do you care, dude? Be man. He, She, It, whatever. Though I personally do get a little riled up when people MAKE UP words like "sie" or "hier" or crazy shit like that.

Re:Ok, I have had enough of this "HER" Stuff (1, Informative)

Anonymous Coward | more than 7 years ago | (#17514400)

The author is illiterate.
http://www.theregister.co.uk/2002/04/08/new_winnt_ 2k_xp_security/ [theregister.co.uk]

All right, I'm only going to say this once: 'He' is the singular indefinite pronoun in English ("if a person drinks too much, he will likely experience a hangover"). 'He' also happens to be the masculine personal pronoun.

'She' is the singular pronoun of personification in English ("if England fails to advance America's foreign-policy ambitions, she will suffer terrible consequences"). 'She' also happens to be the feminine personal pronoun.

Confusing the two exhibits not a warm-and-fuzzy concern for the inclusion of women so much as a writer's or speaker's ignorance. Using the feminine personal pronoun as an indefinite article is as moronic as using the masculine personal pronoun for personification. Thus the captain greets us: "Welcome to my ship. Isn't he splendid?"

Give it up, people. It's not thoughtful; it's just illiterate.

Re:Ok, I have had enough of this "HER" Stuff (1)

reanjr (588767) | more than 7 years ago | (#17518812)

Male = he
Female = she
Neuter = one

If one drinks too much, one will likely experience a hangover.

To avoid using "one", one should utilize alternating gender-based pronouns (which is usually the case, but men - predominantly - will only notice those pronouns that are feminine and not those that are masculine). The alternation frequency should be regular, but ultimately depends upon textual context.

One could alternate gender every paragraph (excepting those cases where a pronoun is in reference to a predetermined gender in extended scenarios). Other times it may be appropriate to alternate whenever an individual is introduced (e.g., a manual that rarely uses personifications). Another method is to switch off every chapter.

There are numerous methodologies, the absolute worst being masculine pronouns throughout.

Re:Ok, I have had enough of this "HER" Stuff (1)

Falesh (1000255) | more than 7 years ago | (#17516506)

Personally I like the convention that some authors use where they use "he" if the author is male or "she" if the author is female.

Re:Ok, I have had enough of this "HER" Stuff (0)

Anonymous Coward | more than 7 years ago | (#17520500)

Because it's a really good idea - I first saw it in the middle 80's when authors stopped writing "his/her" in their hypotheticals and anecdotes and instead just chopped and changed to spread the pronouns around. Much more natural and obliterates the clumsiness of po-faced attempts to avoid any and all hint of sexist assumptions in every sentence, and achieves the objective.

You might have heard the argument that "his" encompasses "her" and man covers both male and female, and that we shouldn't bother.

That idea is rubbish. Going back to the hard-wired mode of the 50's would mean all nurses become defacto women and all doctors defacto men.

Did you know that many languages have gender free terms for people? Even ancient ones:- in Greek male is 'andro' and female is 'gyno', but the generic is 'homo' and is used only when referring to people of both or potentially either sex. Latin has 'vir' - a male, 'fem' or 'feminos' - female and and several words for person which depend on context. Quite a few asian languages are similar (granted, quite a few others are more 'gendered' than French, Japanese for example sees men and women speaking slightly different languages).

If people 2,000 years ago could do this so can we.

Better to rid our language of these demeaning assumptions, particularly when our language actually has usable gender-free terms, and careful usage and practice can keep the writing free of artiface.

I started this practice in my own (mostly technical) writing during the '80's. For a few years I had to explain to my reviewers
that I didn't want to accept correction back to the old terms. They always agreed, and after a few years no-one noticed or cared anymore.

Not hard to do, and it's only laziness that prevents you from learning how.

The biggest SQL hack of all... (1, Interesting)

Anonymous Coward | more than 7 years ago | (#17513204)

is to not use SQL. Seriously, at this point, with persistence systems like Hibernate and EJB3, most of us should no longer be using SQL for most database work.

Re:The biggest SQL hack of all... (5, Insightful)

Anonymous Coward | more than 7 years ago | (#17514150)

is to not use SQL. Seriously, at this point, with persistence systems like Hibernate and EJB3, most of us should no longer be using SQL for most database work.

Congratulations, you get the "Don't Hire This Guy" award for this particular database-related slashdot post.

In two simple sentences, you've managed to display most of what's wrong with the field of data management today. Let's review:

* You use an invented, ad-hoc terminology instead of an accepted term: "persistence system", instead of "database management system" or "DBMS". I assume you call it a "persistance system" to reinforce the fact that it doesn't do much for you besides remember some bits on disk.

* You support the COBOL-era practice of application-centric databases. Tell me, how do I access your Hibernate-controlled data from my Ruby script?

* You support COBOL-era network databases, since I assume you're using Java, which is object-oriented, and object databases are basically network databases under a different name (more ad-hoc terminology).

* You commited one or both of Date's Great Blunders: you equate entire SQL tables with types, rather then demanding that your non-scalar types be stored directly in the database without a "mapping" step, and you believe all values should have unique simple IDs, even though that data has nothing to do with what you're modeling.

* You believe that database queries should be procedural and navigational, rather than algebraic and declarative. Tell me, if you have a sequence of Java statements in your code that perform a complex data management task, are they optimized as a unit into simpler, equivalent statements? Is it even possible? No, didn't think so.

* You (likely) believe that business logic belongs in the application, rather than the underlying DBMS, even though you also have business logic in the DBMS already, such as different column types.

* You're generally very confused, since Hibernate actually uses SQL itself.

Basically, you'd rather avoid the complexity of learning about what a DBMS is, what the underlying model is, how SQL approximates the model, and how to design a DB that can outlive it's various applications and "frameworks of the month".

Congratulations! You win a swift kick in the nuts!

You have no idea what you're talking about (0)

Anonymous Coward | more than 7 years ago | (#17515370)

You have absolutely no idea what you're talking about here. I'm glad you wouldn't hire me because I wouldn't want to take orders from someone who is so ill-informed about modern object persistence systems.

Hibernate etc are NOT new storage systems. They are layers on top of good old fashioned SQL databases. What happens if a Ruby script needs to access data that have been stored by Hibernate? The Ruby script, or perl script, or whatever, can use plain old SQL statements just like they always have. Hibernate uses plain old SQL databases just like everything. All it does is it saves the application developer from having to write SQL code to create, retrieve, update and delete objects.


You believe that database queries should be procedural and navigational, rather than algebraic and declarative. Tell me, if you have a sequence of Java statements in your code that perform a complex data management task, are they optimized as a unit into simpler, equivalent statements? Is it even possible? No, didn't think so. I have no idea what "procedural and navigational" vs "algebraic and declarative" means. Nor should I know. All I want to do as an application developer is do things like say, "persist this object", or "fetch all objects that have these properties". Hibernate and EJB3 can do those things for me, and I don't need to know any of this other stuff.


As for optimizing a sequence of statements into a simpler, equivalent sequence, yes that is EXACTLY what Hibernate and EJB3 do in an automated way, and they usually do a better job of it than programmers typically do, just like a good C compiler normally produces better and faster assembly language than a human can normally produce. How can computers optimize better than humans on these tasks? Well, of course humans CAN do it better just like there are some humans who can play chess better than computers, but in a normal real-world setting, computers win at these tasks.


Basically, you'd rather avoid the complexity of learning about what a DBMS is, what the underlying model is, how SQL approximates the model, and how to design a DB that can outlive it's various applications and "frameworks of the month". Yeah that's right! I certainly would rather avoid the complexity of learning about how DBMS systems work and just get straight to what I want to do, which is dealing with objects. Hey that's the same thing that happens when I program in C! I avoid knowing anything about assembly language, the ELF file format, and all that other stuff because I have some great tools that handle all that for me so I can focus on learning other things and being productive!

You support COBOL-era network databases, since I assume you're using Java, which is object-oriented, and object databases are basically network databases under a different name (more ad-hoc terminology). I have no idea what that means. I've never used COBOL. I know that when I build a EJB3 or Hibernate app, I'm using the same old databases and tables that I have always used, using the same old MySQL / Postgres / SQLServer / etc that I have always used. I just know that EJB3 looks at my classes and automatically creates good, human-friendly table definitions. I can and do open up an SQL CLI and look at what's happening in the tables, and it's just like it always was. Again I think you have no idea how these technologies (object persistence) actually work, or you wouldn't be making comments like that.

Re:You have no idea what you're talking about (0)

Anonymous Coward | more than 7 years ago | (#17516256)

I know this is slashdot, but it's incredible to see two "look what an ignorant dumbass I am" posts like this in a row.

'I have no idea what "procedural and navigational" vs "algebraic and declarative" means. Nor should I know.'

Wow. What a pompous retard. I'll bet you wouldn't know what a normalized entity-relationship was if it bit you in the ass, or why it's important.

Like the other poster said, you really do need a kick in the nuts.

Re:You have no idea what you're talking about (0)

Anonymous Coward | more than 7 years ago | (#17516740)

I know exactly what a normalized entity relationship is, and why it's important, and how EJB3 makes it easy for me to set them up, including in situations with inheritance. Yes all that can be done by hand in SQL but it is so much saner to have a tool help out with it, I would never go back if I didn't have to. As for "procedural and navigational" vs "algebraic and declarative", I really don't know what those are, except I know that EJB3 is big on "declarative" things by using annotations to declare how things are, and then having the container manage how it is accomplished. How that relates to SQL itself, whatever.

And the post I was replying to was completely clueless, in that even though he said, "it's all SQL underneath", he still was asking, "how can you access data stored with Hibernate outside of Java", and said that Hibernate is some kind of COBOL-style network data storage thing. Whatever, that's totally wrong and obviously he has no idea about what Hibernate is or how it works. What I mainly need to know is that EJB3 / Hibernate let me annotate my objects, and if I do it right, they auto-generate correct normalized tables for me, and generate all the SQL for me, and are able to automatically take a series of steps and transform them into a simplified, and usually optimal, step at the end of a transaction. All this saves me from having to try to do these things by hand, and plain old human-friendly SQL underlies the whole thing.

Re:You have no idea what you're talking about (4, Insightful)

feronti (413011) | more than 7 years ago | (#17517810)

What you've just illustrated is one of the biggest problems I see with computer science education today. It seems that the kids coming out of computer science programs have absolutely no idea what has come before. They have no sense of the history behind the technologies they're learning, and so are blind to the fact that they're simply repeating what has already been done. If you don't know where you came from, how can you know where you're going to?

Re:You have no idea what you're talking about (1)

jadavis (473492) | more than 7 years ago | (#17520254)

And the post I was replying to was completely clueless, in that even though he said, "it's all SQL underneath", he still was asking, "how can you access data stored with Hibernate outside of Java", and said that Hibernate is some kind of COBOL-style network data storage thing.

It's not that the data is inaccessible but that the way the data is accessed is tied to the way the data is stored. For instance, let's say you have two versions of the same application, and in the first version a given relationship is one-to-one (let's say spouse) and the second version it's one-to-many (they just legalized polygamy). You'd like both applications to be able to operate on the same database at the same time. You devise a rule that says that version one can only see the first spouse that the person married, and can't add a spouse if one or many already exist. That's good enough for version one of the application. Version 2 can, of course, add and view as many spouses as you want.

With a relational database, you can do that quite easily. Just use an updatable view.

How would you do that if both applications used Hibernate?

Re:You have no idea what you're talking about (2, Insightful)

Anonymous Coward | more than 7 years ago | (#17517264)

I'm glad you wouldn't hire me because I wouldn't want to take orders from someone who is so ill-informed about modern object persistence systems.

"Modern" ?? Read this carefully: in the old days, we had hierarchic databases: entity X is a child or parent of entity Y. People realized that this wasn't general enough (sometimes, you just want a list of all the order items, and you don't want to have to retrieve all the orders first). So they thought up network databases: Entity X is LINKED to entity Y, or collected in a container (for an aribitrary number of X's linked to Y). Then people realized it wasn't general enough. So someone smarter than either of us sat down and said "Let's associate entities ONLY by boolean statements. Entity X is associated with entity Y, only if the following statement about X and Y is true ....)" You can't get any more general than that, and in fact this general enough to encompass the other two and anything else you can think of (this is part of the relational model).

Then, Java came along, and object/SQL mappers, and we went a step backwards.. back to the network model. Back to how things were done in the COBOL era. Everything is about black boxes linked by IDs. (I think in the COBOL days it was "files" and "pages" and "records" rather than objects, no logical difference though).

(And the XML guys want to take TWO OR MORE STEPS BACK... they want hierarchies, and everything text!)

What happens if a Ruby script needs to access data that have been stored by Hibernate? The Ruby script, or perl script, or whatever, can use plain old SQL statements just like they always have. Hibernate uses plain old SQL databases just like everything. All it does is it saves the application developer from having to write SQL code to create, retrieve, update and delete objects.

If I use the raw SQL (but I thought I didn't have to any more?) does it enforce your business logic? Is it possible to get the database in a state your Hibernate code isn't expecting (like, order items existing without corresponding orders)?

I'm actually glad you didn't give the answer the Ruby guys usually do: wrap it all in an XML-RPC interface!! Avoid SQL up and down the stack!! Hilarious.... you have to download a list of IDs (wrapped in XML) and then one at a time, retrieve the objects, then delete the ones you didn't want.........a couple hours later you get your list of objects.

I have no idea what "procedural and navigational" vs "algebraic and declarative" means. Nor should I know.

Man, if you're in a hole, stop digging! :-)

Here's an analogy, think of this as a procedural way to add some numbers:

x = 1

x = x + 1

x = x + 1

return x

Here's a declarative way:

return 1 + 1 + 1

Which one is easier to optimize (again this is very basic analogy)?

"Navigational" means, instead of asking the DBMS for "all orders that contained order item #37, along with their customers, their shipping addresses, and the date of their first order", you say: "give me all orders with item 37. Now take those and give me the customers. Now for each customer, give me the shipping address. Now give me the ..." you get the point. A bunch of discrete queries that "drill down" instead of just telling the DBMS exactly what you want in a single statement. Impossible to optimize, doesn't scale to millions and millions of rows, etc.

Hierarchic database are the worst at this, because it is IMPOSSIBLE to retrieve items without referencing their parents first. Take a look at xpath/xquery for instance and how slow and confusing it can be on non-toy datasets.

As for optimizing a sequence of statements into a simpler, equivalent sequence, yes that is EXACTLY what Hibernate and EJB3 do in an automated way

No, they take a set of pre-determined statements or templates and wrap them up in function calls. They don't optimize arbitrary queries. Let me spell it out: Within your code, you have sequences of procedural Java code that perform certain tasks, and the part of Hibernate that converts into SQL does not "know" that two lines could be combined into one, or that your for-loop could be collapsed to single SELECT, or anything else.

It's a trick question, because Java code isn't a closed algebra, but relational algebra is. Just like, say, regular algebra (which is embedded in almost all modern languages (+, - etc) .. and can be optimized).

Again I think you have no idea how these technologies (object persistence) actually work, or you wouldn't be making comments like that.

Oh I understand it all right. I've been in this business long enough to see what happens to these apps, long after the team who wrote it in their "framework of the month" have gone and a couple more teams have come and gone. I'm the guy that cleans it up.

Pick up a copy of Date's "Database in Depth" .. it's only 250 pages long and the information will likely be just as applicable 20 years from now, long after the 800-page Java books are laughably obsolete. Of course Date doesn't know much about object-oriented programming so you have to think a little when you read to tie it together, but it's all there.

Re:You have no idea what you're talking about (0)

Anonymous Coward | more than 7 years ago | (#17519578)

I think the problem is the origional poster blurted out something that quite easily can be construed as SQL sucks, use this instead and people tended to go with that.

I've heard similiar ramblings about EJB and hibernate for years from people with their heads in object oriented clouds rather than in reality and getting the job done and yes their projects failed miserably as reality cought up with them.

Object persistance really means we'll cache information from the RDBMS and who cares if the data in the RDBMS changes. I've had quite enough of that particular nonsense.

Data bindings (we don't need no sql, ignorance is bliss) have been avaliable since the days of MS access and ADO long before any of this "new" java stuff named by academics who all think their geniuous. I've personally written several systems that abstract data manipulation using readily avaliable meta-data it's quite easy to do and quite old news.

As far as optimizing queries for retreival, handling transactions in an optimal and intelligent manner and doing all of those things application developers need to but don't feel they have to be aware of because their using some java technology with the word *bean* in it (Especially when it comes to design and concurrency issues) for someone to claim that a technology exists to do these things better than someone knowledgable in SQL and familiar with RDBMS's automatically I laugh at their ignorance.

The truth is that all of the major commercial RDBMS vendors spend millions in R&D on their optimizers alone there are no quick hits and any that do exist are very RDBMS specific. Between the vendors there are some major differences in concurrency schemes for example MVC in oracle vs the microsoft servers and sybase.

When your system has the where with all to break three "or" conditions into three separate queries unioned togeather for performance reasons or can decompose a complex query into steps because it knows something that the RDBMS's optimizer doesn't or allows you to express the idea in terms that allow more intelligent determination of how to do it then come talk to me about new technologies that make SQL not worth knowing.

I can write an sql query to get the job done before you can define an object factory to produce an object to inherit the answer :)

Re:You have no idea what you're talking about (1)

Jimithing DMB (29796) | more than 7 years ago | (#17519966)

Have you had a look at Apple's WebObjects?

It is a completely different beast from something like Hibernate or even Rails. Like other frameworks it maps database tables to entities (object classes) and database rows to instances of those classes and database columns to accessors in the class. And like other frameworks it is able to model relationships from one entity to another. And of course it does pull in data and cache it in application memory. Of course, any database access is bound to do this at some point.

However, unlike some persistence frameworks it does not attempt to enforce a hierarchical view of the data. In fact, it truly is a relational system. The big difference with WebObjects is the concept of the editing context (EOEditingContext class). The editing context is responsible for tracking all changes to the object graph. It is capable of working with hierarchies of course but it is also capable of a whole lot more.

Essentially, as the user modifies objects and modifies relationships between objects the editing context keeps track of what is going on. Ultimately, when saveChanges is called on the editing context (NOTE: NOT on some object) all of the changes are batched into the database within a transaction block. That may cause one object to be modified, another to be inserted, some to be deleted, or any combination of those.

In general, inserts are not going to conflict because one typically uses a database sequence to generate integer primary keys or is using compound PKs with enough unique user-entered information that they would not conflict. Should they conflict, EO is able to trap this. With updates, EO defaults to an opportunistic locking scheme whereby update statements are qualified with all attributes of the object (not just the primary key) such that if the record has been changed in the database the program can trap it. There is a facility for defining what merge behavior is appropriate for the particular entity by implementing it in a method that will be called in the event this occurs.

Also, unlike some other frameworks it is able to model EVERYTHING you can put in a SQL database. Or a non-SQL database for that matter! I have quite successfully used it to access a (copy of a) number of COBOL files as if each COBOL file was a table in a relational database. It helps of course that the COBOL data store was sort of relational to begin with of course. Where COBOL arrays were used I have successfully defined views in PostgreSQL to unwrap these into a set of related tables which I then model in EOModeler.

I'm not saying that the Enterprise Objects part of WebObjects is perfect, but it's as close as I've seen. The reason it is so good is that it truly follows the relational model and if you really think about it it sort of is an RDMS in and of itself. Think about the act of opening an editing context then either calling saveChanges() or revert(). Does this sound like "BEGIN" followed by "ABORT" or "COMMIT" to anyone? Of course, it goes beyond that since it's doing all of this in its own memory. Gets even better when you nest editing contexts. Suppose you have a deep hierarchy of objects. Yes, I realize we're dealing with a relational system, but sometimes you really do want hierarchy. In an app using EO the user is able to drill down from the root class and change all sorts of things about it and its children and its children's children and so on. Only when the user saves back up to the parent does it commit all of this in one big block. Primary keys and foreign keys are taken care of based on the rules you specify.

It really is a pretty damn amazing system. If you admire the relational data model I assure you you will like EO. Even better, model some DB you have and point D2W at it. It is absolutely amazing. Unfortunately the UI was designed by engineers in the early to mid 90s so it does tend to look like shit. But it WORKS. And looks are easy to change. Probably EOs biggest downfall is that it does so accurately reflect the relational model that you (gasp) have to actualy know about it and not just think in terms of hierarchies of objects like with Rails and such.

And of course yes I _can_ write the SQL queries to do what I need. But the question becomes at some point do I want to? It's kind of cool to be able to have EO do all the dirty work of figuring out what the user wanted to do then batching it into the DB at once. IMO it's just wrong to push records down to the DB just because your access layer shits itself if you don't. And I shouldn't be forced to generate a PK for a parent of a hierarchy so I can use it as part of a compound PK or as an FK on some child. The access layer should take care of that shit. What I do expect to do is design a data model that accurately reflects things that are being stored. EO lets me do this.

To finish it off, I do have to say that I would much rather be working with someone like you who can apply relational database knowledge and experience to EO. About a year ago we hired someone who had experience with object databases and Java and so on. This was at my boss's insistance that since we're doing it in Java then it would be better to have someone who knows Java. BIG MISTAKE. Fortunately, she quit. We've since hired someone who hardly knew a lick of Java but had some actual RDBMS experience. Much better. Real world data is relational. If you can't think in terms of relations then you can only model the very basic stuff.

Re:You have no idea what you're talking about (1)

jadavis (473492) | more than 7 years ago | (#17520148)

You have absolutely no idea what you're talking about here.

Yes, yes, that guy knew exactly what he was talking about. In fact, his post would make an excellent starting point for you to learn about the subjects he's talking about.

This is a huge field of study, but if you are still not impressed with relational languages just consider this: you can't create an infinite loop in a relational language (in SQL it is technically possible to create an infinite loop, but only when using non-relational features like recursion). If you've worked mostly with iteration and recursion, this may seem strange. Declarative languages are very important for many reasons, and I encourage you to find out why they are so important for databases.

Re:You have no idea what you're talking about (1)

Simetrical (1047518) | more than 7 years ago | (#17527650)

just like a good C compiler normally produces better and faster assembly language than a human can normally produce.

That is flat-out incorrect. Any seriously performance-intensive programming such as computer games, C library functions, drivers, and some parts of operating systems is still done in assembly (or compiled in C and then optimized in assembly) because it's a lot faster and a lot more compact. Why would Microsoft bother putting an inline assembly feature in VC if it were slower than what the compiler produced?

If you want a more specific figure, the ballpark figure I was given at the beginning of my assembly course is that assembly is around three times faster to execute and ten times slower to write than equivalent C code. A good C compiler is hardly "better and faster" than manually-written assembler.

Re:The biggest SQL hack of all... (1)

Ayende Rahien (309542) | more than 7 years ago | (#17516900)

And you, Mr AC, has shown yourself to be _extremely_ clueless.
Hibernate _is_ a persistance system, not a DBMS, it is a tool to help you work _with_ DBS.

How you access my Hibernte DB from Ruby, well, I imagine that you would use something like: "SELECT * FROM Orders", other methods does exist for ruby, but this is the lowest level of abstraction.

I have _no_ idea what you are talking about with objection databases or network databases, both has no relation whatsoever to hibernate.

The rest of the post is not worth replying to, since it clearly has no relation to the parent post, but to some random rambling from the author's mind.

Re:The biggest SQL hack of all... (0)

Anonymous Coward | more than 7 years ago | (#17517566)

Hibernate _is_ a persistance system, not a DBMS, it is a tool to help you work _with_ DBS.

The DBMS is an abstraction: it is the part of the system that you interact with to perform your data management tasks. It implements a set of general operations. It is a logical concept or layer, it is not an application like PostgreSQL, nor is it the thing that stores the bits (the actual way the data is stored is part of the physical model, and not usually the programmer's concern). If you want to use an unnecessary term like "persistance layer", go right ahead.

I call it the DBMS because that's how programmers treat it. 10 years from now, that part of the system will be called something else by novice programmers, and everyone will be excited about this "new" thing, but DBMS will still be the correct general term.

How you access my Hibernte DB from Ruby, well, I imagine that you would use something like: "SELECT * FROM Orders", other methods does exist for ruby, but this is the lowest level of abstraction.

Which means that Hibernate is not a substitute for SQL in any way, shape, or form, as the poster asserted. It is merely a sophisticated set of templates, intended to dumb down SQL, which is already dumbed down relational algebra. It is application- (or language-) specific, which means it is useless for the next team that comes in, armed with THEIR favorite language or framework.

I have _no_ idea what you are talking about with objection databases or network databases, both has no relation whatsoever to hibernate.

If you don't have any idea what the different types of database models are, maybe you should study up? To help you look it up, the three major models in increasing generality are "hierarchic", "network", "relational" (not "objection" [sic]).

The rest of the post is not worth replying to, since it clearly has no relation to the parent post, but to some random rambling from the author's mind.

You just admitted you don't know the basic database models .. are you sure you can even identify what is random rambling and what is not, when discussing data management?

Re:The biggest SQL hack of all... (1)

Ayende Rahien (309542) | more than 7 years ago | (#17519414)

The DBMS is an abstraction

Let us have it your way, read RDBMS instead of DBMS, where the intent is to refer to applications such as PostgresSQL.
If you think that Hibernate is a DMBS because it handles talking to a database server, that is a major twisting of the accepted terminology in the field.

If you don't have any idea what the different types of database models are

Please read my post again, I was talking about having no idea why you brought it up in your post because it had no relation to the _parent post_. You may check my blog if you assume that I am not knowledgeable about SQL.

You just admitted you don't know the basic database models .

Read that part again, this time, do read it.

Oh, and... (0)

Anonymous Coward | more than 7 years ago | (#17516966)

You use an invented, ad-hoc terminology instead of an accepted term: "persistence system", instead of "database management system" or "DBMS". I assume you call it a "persistance system" to reinforce the fact that it doesn't do much for you besides remember some bits on disk. Whoever gave this guy "insightful" points doesn't understand this subject area. Hibernate is not at all a DBMS and does not take the place of a DBMS and in fact requires a DBMS to do its job. Hibernate does not remember bits on a disk, period! That is the job of a DBMS. He proves that he doesn't know what he's talking about by asking how non-Java systems could access these data. This guy needs to update his knowledge before he should be getting "insightful" points.

The primitive way to do object persistence by writing bits is to just serialize the object itself. Very simple Java programs can sometimes use built-in Java object serialization to store objects on disk, and then yes, those data are married to Java and can't be accessed by any other system, but Hibernate/EJB3 doesn't have anything to do with Java object serialization.

Ok, it's not entirely his fault that he's clueless. These are fairly new technologies. Anyone who does database-backed applications for a living these days should crack open some books on this!

Re:Oh, and... (1)

Decaff (42676) | more than 7 years ago | (#17517312)

Ok, it's not entirely his fault that he's clueless. These are fairly new technologies. Anyone who does database-backed applications for a living these days should crack open some books on this!

Object-relational mapping isn't new at all. Hibernate has been around for years, and TopLink, one of the products EJB 3 was based on, has been around since the early 90s.

Re:The biggest SQL hack of all... (0)

Anonymous Coward | more than 7 years ago | (#17517248)

After shooting your foot off it is recommended to stop pulling the trigger.

Re:The biggest SQL hack of all... (0)

Anonymous Coward | more than 7 years ago | (#17519486)

This guy is right. Let's say we have a database of baseball statistics. If I want to print out a list of batters by how many wins they have, why shouldn't I be able to write something like this:

WriteRankedList(from game in games
                group game by game.WinningPitcher into g
                orderby g.Count() descending
                select g);
Or better yet, how about a list of batters by batting average (but only for those who have more than 400 at-bats)?

var battingAverageLeaders =
    from game in games
    from play in game.GetPlays()
    where play.Result != null
    group play by play.Batter into g
    where g.Count(p => p.Result.IsAtBat) > 400
    let pd = new PlayerData {
        Player = g.Key,
        Data = (Math.Round(1000.0d *
                          ((double)g.Count(p => p.Result.IsHit) /
                            (double)g.Count(p => p.Result.IsAtBat))) / 1000.0d).
                                ToString(".000") }
    orderby pd.Data descending
    select pd;
In case you're wondering, this is the syntax for C# 3.0. And if you think it's really similar to SQL, you're right.

My point is actually that even if not written in SQL, this book should be useful because this is just another dialect of almost the exact same thing. I dare you to write a single statement to express either of those queries with Hibernate or EJB3 without resorting to SQL!

dom

Re:The biggest SQL hack of all... (1)

Amadodd (620353) | more than 7 years ago | (#17521116)

Some people just don't get it. Here is some typical business logic as defined by business:
If ItemA has Status4 and has been through Process1 three times already send it to Process4. Except if it has already been through Process4, send it through Process5. If it has been through Process5 once before set status to Status8. Except if it the last process it went through has been assigned to Employee23 or Employee46 or any employee in EmplGroup8, in which case you must add an alert to that employees queue.
And that is only for Status4. Wait till you see the rest of the spec.

Now you go implement that in Java/C# or whatever using Hibernate etc and you are gaurenteed to to make an unholy amount of requests to the server and end up with something that is not scaleable. Fact is that most business logic is best implemented in the DBMS, and if you think otherwise you haven't been there yet.

query :-) (1)

ameline (771895) | more than 7 years ago | (#17513228)

SELECT * FROM database_weenies WHERE cool_factor > 0;

0 rows returned

Re:query :-) (1)

Hoi Polloi (522990) | more than 7 years ago | (#17513412)

UPDATE database_weenies SET paycheck =100000,units='DOLLARS',cool_factor=cool_factor+1

Re:query :-) (1)

codemoose (1002724) | more than 7 years ago | (#17513546)

INSERT database_weenies (paycheck, units, cool_factor) SELECT 150000, 'DOLLARS', cool_factor*2 FROM web_weenies WHERE sql_injection IS NULL

Fi&8st (-1, Troll)

Anonymous Coward | more than 7 years ago | (#17513584)

slings are limited, something that you BE NIGGER! BE GaY! my efforts were wall: *BSD faces a on baby...don't Pooper. Nothing Parties, but here are about 7000/5 paper towels,

Seperation of concerns. (1, Insightful)

Anonymous Coward | more than 7 years ago | (#17514710)

Sounds like SQL Hacks is a kitchen sink, which may appeal to some readers.

I appreciate Joe Celko's SQL for Smarties series.
Mostly for the series pure depth, without dragging in the periphery issues or proprietary extensions.
      A) Some databases are better within their own documentation demarking something non-portable.
      B) The series points out commonly confused non-portable items and makes note of those in the standards that
            aren't implemented very broadly or correctly in some instances.

Celko's books likewise, can be instructive to most senior developers and begginers too.

---

SQL injection, while cute and handy, is mostly a programming fault of the application and not the database.

Generally one should use better libraries and practices to make the technique moot, however languages that require more discpline abound and there are plenty of hacked applications, i.e. PHP.

      How many other languages in comparison handle the proper quoting of bind variables within the library,
      rather than have the application programmer assumingly do it right within their application every single
      time?

At some point flexability and features can bite you in the ass security-wise, which is generally an afterthought.

Typically most applications and their associated databases, only have database security at the application or sub-system level. User rights and their groups are mostlty enforced within the application. Depending on the exact semantics of proper operation and security (usually very vague requirements, if any at all), it can become more complex than a sidebar's comments.

Databases (-1, Redundant)

compandsci (1045690) | more than 7 years ago | (#17514828)

Is anyone really interested in databases and SQL. This is boring. Jesus!

Not wet enough? (1)

fm6 (162816) | more than 7 years ago | (#17514858)

It's not a bone dry college text book, but it was written with a dedication to professionalism that can make a technical book tedious and will certainly keep it from becoming a classic.
So in order to be a classic, a technical book has to have all the digressions and cheap humor that plague the O'Reilly books? A classic technical work is useful, not entertaining. If you want entertainment, read a novel.

Re:Not wet enough? (0)

Anonymous Coward | more than 7 years ago | (#17515112)

So in order to be a classic, a technical book has to have all the digressions and cheap humor that plague the O'Reilly books?

Don't think so. The Knuth books are considered classics and if they have humor, it's over my head. (Though a significant portion of those books is over my head, so maybe they're full of hilarious jokes.)

Re:Not wet enough? (3, Informative)

fm6 (162816) | more than 7 years ago | (#17515570)

Actually, there is humor in Knuth. If you look up "Infinite recursion" in the index, you'll get a cross reference, to "Recursion, Infinite". Needless to say, that second entry is also a cross-reference.

Despite my sober attitude, I've been known to sneak in a bit of sly humor into my technical writing. In my last book (OK, my only book [amazon.com] that wasn't an anonymous manual), I managed to work a reference to Alphonse and Gaston [toonopedia.com] into a discussion of deadlock. There's also a reference to Dragon Ball that so far nobody seems to have spotted.

But to do that sort of thing professionally, you have to be sly so it comes in under the radar and doesn't distract. Which only makes the joke that much funnier!

Re:Not wet enough? (1)

sco08y (615665) | more than 7 years ago | (#17532300)

Actually, there is humor in Knuth. If you look up "Infinite recursion" in the index, you'll get a cross reference, to "Recursion, Infinite". Needless to say, that second entry is also a cross-reference.

Now I know who's behind those +5 Funny moderations.

Re:Not wet enough? (1)

Falesh (1000255) | more than 7 years ago | (#17516436)

The most effective learning books I have ever read were Head First HTML with CSS & XHTML and Head First Design Patterns. They also happen to be the most enjoyable.

Note that this style may not be the best for you personally as we all learn in different ways.

Re:Not wet enough? (1)

fm6 (162816) | more than 7 years ago | (#17516752)

There's something to be said for the "head first" approach, which uses humor and graphics to reinforce learning. But there's a limit to what you can teach that way. In the case of SQL, you could write an HF book to make the basic concepts easier to grasp, but once you started getting into the really complicated and abstract stuff, all that gonzo exposition just gets in the way.

Anyway, the HF books are very calculated in the way to use humor for teaching. That's not true for most humor in computer textbooks, which are written by nerds who are so in love with their own jokes they allow them to distract from the book's primary purpose.

mod uP (-1, Offtopic)

Anonymous Coward | more than 7 years ago | (#17515458)

NetBSD posts on need to join the ASSOCIATION OF whole has lost website. Mr. de won't vote in Create, manufacture never heeded a productivity Have left in achieve any of the ops or any of the you are a screaming a change to time wholesome and NetBSD posts on task. Research by clicking here which aalows come Here but now here, but what is FreeBSD had long big deal. Death to fight wha7 has play parties the open platform, all know we want. channel #GNAA on was at the same recruitment, but on baby...don't You need to succeed

anyone know of a good "schema cookbook" (3, Interesting)

doom (14564) | more than 7 years ago | (#17518512)

Something I've been wondering about off and on: is there anything like a "schema cookbook" out there?

The other day I was once again implementing yet another set of tables to represent mailing addresses, phone numbers, user names and so on; and it once again struck me as being completely ridiculous that we all don't just use the same standard schemas to do common tasks like this.

Re:anyone know of a good "schema cookbook" (1)

qohen (104310) | more than 7 years ago | (#17520360)

Such things do exist--there are some free resources online as well as books that one can buy.

Free data model collections:
http://www.databasedev.co.uk/data_models.html [databasedev.co.uk]
http://www.databaseanswers.org/data_models/ [databaseanswers.org]

Books:
A guy named Len Silverston has made this his niche--he's written these books:
"The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises"
"The Data Model Resource Book, Vol. 2: A Library of Data Models for Specific Industries"

More about them on Amazon:
http://www.amazon.com/s?ie=UTF8&search-type=ss&ind ex=books&field-author=Len%20Silverston&page=1 [amazon.com]
Or check this out:
http://www.univdata.com/book.htm [univdata.com] (univdata.com is Silverston's company).

Another book (Silverston co-authored): "The Data Model Resource Book: A Library of Logical Data and Data Warehouse Designs " http://www.amazon.com/Data-Model-Resource-Book-War ehouse/dp/0471153648 [amazon.com]

If you want a free taste of what Silverston is about, here's an article he did with a data model for clickstream analysis:
http://www.dmreview.com/article_sub.cfm?articleId= 4479 [dmreview.com]
And this is a more general article on universal data models that he wrote:
http://www.tdan.com/i010fe04.htm [tdan.com]

Re:anyone know of a good "schema cookbook" (1)

qohen (104310) | more than 7 years ago | (#17520402)

P.S. Just found a list of all the data models that Silverston has in his 2 Data Model Resource books:
http://www.wiley.com/WileyCDA/Section/id-106466.ht ml [wiley.com]

And, here's a list of all the (eleven) articles he did for Data Management Review:
http://www.univdata.com/pubs.htm [univdata.com]
(this includes the clickstream analysis data model article previously listed as well as others about "Universal Data Models for Financial Services", "Universal Data Models for Health Care", "Using "Universal Data Models to Jump-Start Your Data Modeling Effort", etc.)

Re:anyone know of a good "schema cookbook" (0)

kpharmer (452893) | more than 7 years ago | (#17522256)

I think I like David C. Hays "Data Modeling Pattern" a little more.

Re:anyone know of a good "schema cookbook" (1)

ZenFu (692407) | more than 7 years ago | (#17593562)

Jim Arlow's Enterprise Patterns and MDA may also have what you want, but in UML.

The archetypes are abstract organization and business models. Instead of customers and vendors, for example, you might have parties and roles.

The models should give you some ideas on what a generalized database should look like.

In my oft-questioned opinion, the downside to coming up with a generalized layout, regardless of the tier, is that your complexity is fixed. Sometimes such an approach feels like driving a 18-wheeler down your driveway to pick up your mail.

I bought it - pretty basic stuff (1)

oldjeep (1033328) | more than 7 years ago | (#17627924)

After reading the review I was expecting some pretty high level tips and tricks. I was disappointed. With few exceptions the "hacks" are things that most good DBA's already know. One saving grace of the book is that the examples are in several variants of SQL, so it's somewhat usefull as a translation guide.
Check for New Comments
Slashdot Login

Need an Account?

Forgot your password?