SQL Formatting

CreatedToDislikeThis

@ben_lubar Other languages have real string concatenation instead.

Bort

How do people feel about prefixing table names with "tbl", views with "vw", storedprocs with "sp", etc?

I think it's fucking stupid.

But I wanted to hear from anyone who prefers it.

boomzilla

@Bort said in SQL Formatting:

I think it's fucking stupid.

Hah! Sucker! We use t_ and v_ for our tables and views. Our DBA didn't think head for stored procedures or functions so we have no convention for those.

I guess I've just gotten used to it and don't really even notice it any more.

Bort

@boomzilla said in SQL Formatting:

don't really even notice

So you start a tread to critique SQL formatting to an almost sarcastic level of detail and having meaningless prefixes on every table and view is something you just don't notice?

It's especially bad in our database because it's not even used consistently. Some tables are like "tblSomeTable", some are like "tbl_some_table" and some are like "SOME_TABLE". And they're in three different schemas that have nothing to do with which application uses them, and have a name prefix like "appname_SomeTable" or "APPNAME_SOME_TABLE" even though they also don't belong to AppName.

PleegWat

@Bort We use prefixes, but they denote which subsystem manages that table's structure.

boomzilla

@Bort said in SQL Formatting:

So you start a tread to critique SQL formatting to an almost sarcastic level of detail and having meaningless prefixes on every table and view is something you just don't notice?

Yes. I lost that fight nearly a decade ago. I think I'm actually somewhat mentally crippled now because when I look at stuff that doesn't have the prefixes it seems like key information is missing.

@Bort said in SQL Formatting:

It's especially bad in our database because it's not even used consistently.

We're consistent, at least.

@PleegWat said in SQL Formatting:

We use prefixes, but they denote which subsystem manages that table's structure.

A third party product that we integrate with does that. It's definitely better than our system. But their schema is probably an order of magnitude bigger that ours, so it's practically required to do something like that.

Karla

I think it must be because I live in NYC that I like to preserve vertical space.

select a, b, c, d, e
from SomeTable st inner join SomeOtherTable sot on st.id = sot.id
	inner join AnotherTable at on sot.otherId = at.id
		and at.isActive = 1
where foo = @bar
	and (SomeTableDate between @start and @end
			or SomeTableDate is null)
order by b, d, e```

antiquarian

@Bort said in SQL Formatting:

having meaningless prefixes on every table and view

They're not meaningless. How else would you know whether it's a table or view?

Seriously, though, Hungarian notation needs to be nuked from space. It's the only way to be sure.

Bort

@antiquarian said in SQL Formatting:

How else would you know whether it's a table or view?

FFFFFFFFUUUUUUUUUUU

ChaosTheEternal

My personal preference for Select/Update/Delete queries in the query tool:

Keywords in all caps
Match the case of the table/column names
Separate lines per component of the query, and for each clause in a WHERE (and for an ON, but only if there is more than one clause in the ON)
Right align the first keyword against column 6

So I get something that looks like this:

SELECT t1.col1, t2.col1, t2.col3
  FROM table1 t1
 INNER JOIN table2 t2 ON t2.col2 = t1.col1
 WHERE t1.col2 = 'something'
   AND t1.col3 = 'something else'
 ORDER BY t2.col1 ASC, t1.col1 ASC

I never saw anyone else doing this to inherit it as far as I can recall, it's just what I came up with and have stuck with.

another_sam

@Bort said in SQL Formatting:

How do people feel about prefixing table names with "tbl", views with "vw", storedprocs with "sp", etc?

We integrate with a third-party product with table names like table_contact, table_site, etc.

@Bort said in SQL Formatting:

I think it's fucking stupid.

@Bort said in SQL Formatting:

But I wanted to hear from anyone who prefers it.

Never prefixes, but sometimes suffixes are useful. We have so much legacy data and old schemas it's ridiculous and we usually use database views to match the database structure to our data model and pretend that one day we'll migrate the data to the new structure. So we sometimes end up with a view on top of an existing table. So same_table_name_v works for us.

People who name columns with the table name as a prefix can die in a fire.

Arantor

@another_sam how about naming tables with the database name as a prefix?

Our product has... let's say 12 databases. It doesn't need 12 but it currently has 12. One of them is 'emails'. We have tables like 'emails_content' and 'emails_log'. IN THE DAMNED EMAILS DATABASE.

There are other DB names that are longer (like 11 letters that are also doxxable) and which the tables also have the same damned prefix.

And because using aliases are weird around here, and a shitty query builder, you end up with this shit:

SELECT
productname.productname_productthing.columnA,
productname.productname_productthing.columnB,
differentdb.differentdb_differenttable.columnC
FROM
productname.productname_productthing
JOIN
differentdb.differentdb_differenttable
ON
differentdb.differentdb_differenttable.ID = productname.productname_productthing.ID

Slowly, and surely, I am drawing plans against this.

another_sam

@Arantor said in SQL Formatting:

Our product has... let's say 12 databases. It doesn't need 12 but it currently has 12

So it's a lightweight product? Ours now has 13 configured DataSources, down from about 22 a few years ago. Many of them are for different schemas in the same 4 or so database instances (Oracle).

But my excuse is I'm dealing with legacy data in legacy schemas from other products and old software systems. I wish that really were my excuse, it's only applicable to about 20% of our schemas. My other excuse is we're using Jira for business workflow (we're truly retarded) which means four different Jira instances to handle the workload and number of issues we create, and Jira doesn't share database instances with other Jira instances.

@Arantor said in SQL Formatting:

We have tables like 'emails_content' and 'emails_log'. IN THE DAMNED EMAILS DATABASE.

Of course. Where else would you put them?

@Arantor said in SQL Formatting:

Slowly, and surely, I am drawing plans against this.

So are we: We're going to use a single datasource for each database instance and give the appropriate grants and use cross-schema joins to get at everything.

The bigger plan is to merge everything into one big database instance, but I'm not sure how far we'll get with that. There are other implications like hardware and DMZ vs internal and other stuff to worry about. Plus Jira. Plus we have one product, out of support (the vendor went bankrupt at least five or six years ago and it was an old product even then; my organisation is dysfunctional and makes the same mistakes over and over) that still needs Oracle 9. The only future for that product is the off button but it's critical for our business and we have no replacement.

Risk? I don't know what you're talking about.

Arantor

@another_sam There really is no reason for not having a single DB for this as far as I'm concerned.

We're SaaS, our largest customer's DB is... maybe 60GB. Most of that is archived reporting stats where they've been around a while. Our platform did not exist 5 years ago.

As for 'emails_content' in the emails database, sure, that's where they should live. But you don't need 'emails' in both the database and table name. One of these will do just fine.

Also, did I mention... MySQL.

Karla

@Arantor That would drive me crazy. I had pointlessly long names. I had redundancy. Our views tend to have prefixes.

Developers here have a tendency to use the query builder tool to create their SQL for views and sprocs.

If I have to touch it...I absolutely must reformat first.

Arantor

@Karla we have no views at all. Nor sprocs.

another_sam

@Arantor said in SQL Formatting:

We're SaaS, our largest customer's DB is... maybe 60GB.

I write in-house business systems and our biggest database is somewhere a bit over 1TB. Most of that is our media service, full of photographs and other images, emails and other correspondence including attachments, scanned documents and other materials submitted by our customers. The other databases aren't tiny either.

@Arantor said in SQL Formatting:

Also, did I mention... MySQL.

As much as I hate Oracle (and I do) I still prefer to never ever touch MySQL ever again.

another_sam

@Karla said in SQL Formatting:

Developers here have a tendency to use the query builder tool to create their SQL for views and sprocs.

Ew. We write all SQL by hand, except for what's generated by the ORMs but we never look at that unless debugging.

pydsigner

@Lorne-Kates said in SQL Formatting:

Redlines mine to demonstrate how everything lines up, so it's easy to just scan down the query and see the important parts.

If it's so easy, why do we need the red lines?

Lorne Kates

@another_sam said in SQL Formatting:

I work with Oracle

Oh god, I'm so sorry to hear that. When did you find out? Have you informed your past partners yet?

Lorne Kates

@Bort said in SQL Formatting:

@antiquarian said in SQL Formatting:

How else would you know whether it's a table or view?

FFFFFFFFUUUUUUUUUUU

srsly, though-- I don't mind the prefix for non-table objects where the context isn't obvious from reading the query.

FROM view_something -- it's a view!

FROM stupidThing(@param) -- table-based stored proc

FROM peeps -- It's a table because that's the default assumption.

The thing I don't like about prefixes is that it makes it harder to do auto-complete. Every table is filed under "t". Or if you're typing to jump around the Object Explorer list, I can't just type "peeps" to get to the peeps table. I have to type "tblpeeps". Annoying.

Lorne Kates

@Arantor said in SQL Formatting:

how about naming tables with the database name as a prefix?

select * from wp_posts

Tsaukpaetra

@Lorne-Kates said in SQL Formatting:

The thing I don't like about prefixes is that it makes it harder to do auto-complete. Every table is filed under "t". Or if you're typing to jump around the Object Explorer list, I can't just type "peeps" to get to the peeps table. I have to type "tblpeeps". Annoying.

No. What's annoying is when they also have application prefixes to the table names.

So it's APPPREFIX_tbl_TableNameHere. I've gotten out of the habit of "typing in the object explorer" to jump around to specific tables, because of course trees don't seek if you miss one keystroke (or type too slow apparently).

Edit:

Lorne Kates

@Karla said in SQL Formatting:

Developers here have a tendency to use the query builder tool to create their SQL for views and sprocs.

Fuck them right in their nose.

If I ever see SQL with a full tablename as a prefix, that shit's getting rewritten.

It ALWAYS comes down to a human being able to read that query to change logic and find bugs. Always. ALWAYS.

Lorne Kates

@pydsigner said in SQL Formatting:

@Lorne-Kates said in SQL Formatting:

Redlines mine to demonstrate how everything lines up, so it's easy to just scan down the query and see the important parts.

If it's so easy, why do we need the red lines?

To demonstrate why I did it. =P

Lorne Kates

@Tsaukpaetra said in SQL Formatting:

APPPREFIX_tbl_TableNameHere

:horror:

Forget nose-fucking. That design decision requires whole new orifices to be painfully drilled into their heads for the sole purpose of fucking.

Karla

@Arantor said in SQL Formatting:

@Karla we have no views at all. Nor sprocs.

All you SQL is inline? :shudders:

Karla

@another_sam said in SQL Formatting:

@Karla said in SQL Formatting:

Developers here have a tendency to use the query builder tool to create their SQL for views and sprocs.

Ew. We write all SQL by hand, except for what's generated by the ORMs but we never look at that unless debugging.

Yeah, I never use the query builder. Never used and web page generator either I think that makes me elitist. or something..

Arantor

@Lorne-Kates WP does it because it could be co-existing with other applications on the same server. Our product is explicitly SaaS running on dedicated servers with nothing else.

Arantor

@Karla oh yes. It's good fun especially given how much copy/pasta is in our product.

cheong

@Arantor said in SQL Formatting:

@Karla especially given how much copy/pasta is in our product.

QFT

Arantor

@cheong It really is in our platform. Makes fine spaghetti.

Jaloopa

@Bort said in SQL Formatting:

prefixing table names with "tbl", views with "vw", storedprocs with "sp", etc?

A place I used to work at did this. Schema changes came over the years, and tables were replaced with identically named views for for backwards compatibility, with INSTEAD OF triggers to insert into the replacement tables.

This meant that there were views with the T prefix, rendering the whole thing pointless.

Another place went even further and Hungarianised column names. Similar thing happened, ti prefixed columns became ints, sz10 columns were expanded to 20 characters. Column names could never be updated.

@Arantor said in SQL Formatting:

we have no views at all

Another previous job had views referencing views referencing views... to something like 14 levels of nesting. I improved performance by an order of magnitude by replacing a call to one of the views with just the tables I needed. In my spare time I was working on a tool to unpack the view definitions so you could see exactly what the equivalent SQL was.

Luhmann

@Arantor said in SQL Formatting:

fine spaghetti.

so no macaroni?

Arantor

@Luhmann we have some spaghetti code and we have some ravioli code in our copy pasta. Most of it doesn't taste nice.

Jaloopa

@Arantor said in SQL Formatting:

Makes fine spaghetti

not Mom's spaghetti?

Yamikuronue

@Arantor said in SQL Formatting:

WP does it because it could be co-existing with other applications on the same server.

No, that's why it creates its own database. It prefixes tables (and makes the prefix customizable) so it can co-exist with another install of itself. Though that's deprecated now that it has a multi-site mode built in.

Luhmann

@Arantor said in SQL Formatting:

spaghetti code and we have some ravioli code

No lasagna?

Arantor

@Yamikuronue it did not used to mandate its own DB, because some hosting companies only give users one DB, and it really isn't just about coordinating multiple installs in one DB, but installing with other apps too.

Certainly the many people I know that run WP and SMF together do it this way...