Hacker News Reader

Comment by jandrese | Hacker News Reader

The more general tip is that a single regex isn't the only tool you have. You don't have to get your final product one one step. Almost every "disaster" regex comes from someone trying to do too much at once.

One other solution would have been to run the regex twice, once to pick up all instances of Tarzan, and a second on the results of the first to filter out all instances of "Tarzan".

View on HN

This is also often the problem with disaster SQL queries. I've seen some monsters that got hopelessly tangled up in their own JOIN constraints, trying to fetch all the data in one roundtrip because OMG LATENCY but then having to do full table scans over large-ish tables instead. Rewriting it as three small indexed queries reduced the runtime from 40 minutes (!) to less than a second.

Don't do too much in one operation whether it's regexes, SQL queries or OOP classes!

Do you have an example of such a query? Maybe Common Table Expressions would have been enough instead of multiple roundtrips.

I’ve used the pattern several times of “select these SQL objects into a cursor, then iterate over the cursor to assign/revoke/check permissions on the objects”.

It’s still in a single batch of SQL (stored procedure in our case, so no additional network roundtrips), but the code is vastly clearer to read/maintain this way.

In which cases are row-by-row loops clearer than set-based sql?

They're clearer in the sense that it makes it easier/possible to do things like:

While maintaining/changing the SQL, comment in/out select-statements-as-printf-debugging, and comment in/out actual execution of the statements themselves.

These cursors would often contain [identifying object reference], [category of statement], [text of SQL statement to execute]. You would write a select statement to populate the cursor, then loop over the cursor to run all the statements in the order you wanted (drops, then user/role creates, then grants, or whatever the situation called for).

It's not about logical clarity, but practical maintainability given the (overall weak) state of tooling for database queries. Is it a bastardization of SQL to do something that "should be" done in another scripting language? Maybe, but there's a lot of power in giving the DBAs tooling that works exclusively in a language and environment that's familiar for them rather than splitting it across SQL and python/tcl/ruby/whatever. Not nearly every competent [relational] DBA is competent across multiple languages. Every competent [relational] DBA is competent in SQL.

Is it even possible to use set-based SQL to call EXEC SQL EXECUTE IMMEDIATE or sp_executesql on each statement in a set?

I’m pretty decent with regex, but I often break complex regexes down into multiple steps for better clarity and easier debugging. Sure, you can use extremely clever one-liners, but the next maintainer of your code may hunt you down and murder you on the spot for wasting weeks of their time.

And, as always, the "next maintainer" is most likely your future self. So conversely, it's really nice to give your future self the gift of clarity and con-conversely look back and say "Thanks past me!"

to your point (and how to fix it in a way that seemingly nobody does) - you can make complicated regular expressions pretty simple by using named groups, and ignoring pattern whitespace because they allow you to logically separate different components and specify intent. Nobody would ask a fellow dev to debug javascript where it is all on a single line and every variable name is a1, a2, etc. Except people do it all the time with regex. Its insane. Hell, you don't even get the blessing of a1, a2, a3. It is all unnamed. Insanity.

Some rare people can figure out:

\d{1,2}[-/]\d{1,2}[-/](\d{4}|\d{2})

but a dummy can figure out this:

(?<month> \d{1,2} ) [-/] (?<day> \d{1,2} ) [-/] (?<year> \d{4} | \d{2} )

A big source of trying to do too much is environments that offer easy regex-based transformations defined as a pair of regex and a single replacement string (that may contain references to matching groups) and make other transformations hard ("while find + rest"). When you have the option to provide a "process match" closure instead of the replacement string the lure of putting too much into a single regex almost collapses.

Caveats apply. A regular expression isn't just a way of saving yourself a few lines of explicit string manipulation. It's describing a state machine that does these text operations efficiently (in some programming environments, that state machine will get optimized and compiled down to metal prior to first use).

If you're matching a couple short strings, sure, don't bother overthinking the regex. If you're matching a lot of them, and/or they're long, then the extra time spent on making a single regex work will be worth it. The regex will work smarter than your hand-rolled code, and it also won't waste memory returning partial results.

Also: in my experience, almost every "disaster" regex comes from people not bothering to document and test what they write.

This is the correct answer. Be less clever. Makes life much simpler for whoever has to maintain your code (which may well be you).

I got the feeling that a lot of those »I have to do this in a single regex« questions come from places where a single regex is basically the only API you have available. Something like form input validation where the framework provides a handy regex it uses for validation, but doesn't expose actual validation callbacks or events do to the same in code without having to redo everything around it. It's only a hunch, but when I have the opportunity to use code to validate a string I probably wouldn't assume that code to be a mandatory one-liner, even as a beginner developer.

Reply on news.ycombinator.com