SQL log style format

[NoFantasy]

After making some changes in queries using PExecuteLog, the issue came up: what style should we use for the SQL.

It looks like it's pretty much about one thing: what each of us find most easy to read, in addition to the needed style for portability.

A few imaginary queries:

UPDATE creature_template SET unit_flags=1 WHERE entry=1;

UPDATE creature_template SET unit_flags = 1 WHERE entry = 1;

INSERT INTO creature_template (entry,modelid_1) VALUES
(1000,150);

INSERT INTO creature_template (entry, modelid_1) VALUES
(1000, 150);

DELETE FROM creature_template WHERE entry<1;

DELETE FROM creature_template WHERE entry < 1;

It is mostly about the spacing, where do we add space, where do we not add any.

It doesn't appear this forum has any feature for polls, so I guess the old fashioned reply feature has to be used instead. Personally i don't care as much what the outcome is, i care more about consistency in what we do. Feel free to add your comment with the preferred style

[Undergarun]

As i wrote on GitHub y prefer the second one

UPDATE creature_template SET unit_flags = 1 WHERE entry = 1;

better than

UPDATE creature_template SET unit_flags=1 WHERE entry=1;

And the same for remaining cases.

Best regards and kindly yours

[Schmoozerd]

I can agree that for small UPDATEs I too think SET unit_flags = 1 is more beautiful.

However, compare

UPDATE creature_template SET min_dmg = 356, max_dmg = 654, attack_power = 130, dmg_multiplier = 13 WHERE entry = 448;

to

UPDATE creature_template SET min_dmg=356, max_dmg=654, attack_power=130, dmg_multiplier=13 WHERE entry = 448;

or to

UPDATE creature_template SET min_dmg=356, max_dmg=654, attack_power=130, dmg_multiplier=13 WHERE entry=448;

From my view, once an Update has more updated fields, it is better readable if the style is "field=value", and for consistency the last one is then easier to read.

Though I think that using entry=%u as done in the MaNGOS-sql queries is easy to read

Also a small note: the SQL-Query files become smaller, if blanks are skipped, and I think there are reasons to use the same style in core-sqls, DB-sqls, sd2-sqls and so on.

[Diablox]

I prefer no.2 in all cases. It's easy to read (i write queries that way).

[ankso]

For me, I prefer 2nd case

[Darkknight900]

i also prefer the 2nd style and i would appreciate if this would be the standard

[Patman128]

Comments (with dates) would be nice.

[DasBlub]

note: I am working with Oracle databases all day and i'm used to write huge queries (>= 10 tables in the select, many WHERE statements, etc.)

i'm used to break up SQL queries into several rows, so a query would look like this:

UPDATE creature_template ct
SET    ct.unit_flags = 1 /* UNIT_FLAG_UNKNOWN7  */
WHERE  ct.entry      = 1 /* Hogger */
;

1. i align the values (if possible i always align the operators ("=", ">", "<", "LIKE", "IN", etc.)

2. i break it up, one line for every table (here we only have one), one line for every SET (here we only have one), one for every WHERE (again, only one here).

3. i added an alias ("ct") for the table. i always do that, because i never know whether i'll have to add other tables to the query later on. of course this does not have to be done when you have finished the query, but while building it up (and you don't know yet whether it'll be enough to go only on that table or not) it's pretty useful. i just did it here too, to show how a bigger sql would (should) look like.

for a simple query like this one this looks a bit like an overkill, but it's more consistent if you do it for all queries (and you never know whether you'll have to expand it once or not). and to make an example, i just used NF's query. if you want, you can also throw a bigger query at me and i can format it too.

btw, my formating is similar to the formatting from the Toad Formatter (part of the very cool sql editor Toad which i'm using both, at home (MySQL) and at work (Oracle); i can only talk about the formatter from "Toad for Oracle", i never used it in MySQL)

[Unkle Nuke]

From an SQL coding perspective, using spaces to pretty things up with short queries is easier on the eyes, but I much prefer DasBlub's approach to long queries. Nothing annoys me more than having to work with a very large, one-line query that scrolls across the width of three screens!

Both methods make reading and finding the data a lot simpler and more efficient, so I say we use those two.

Delineating comments need to be standardized. I prefer the double-dash " -- " as it automatically provides a two-space indent to draw your eyes to a block of comments and looks less cluttered. Also, being less terse with comments would be great for those trying to learn what more experienced SQL devs take for granted.