SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

iamstinky: Feb 4, 2004; This is not as easy as it looks.

I have 3 MySQL DB servers, one in the DMZ and 2 inside on VM sessions. Basically what is in the DMZ is a stripped down version of the production server on the inside (it only has the schemas and tables it absolutely needs to function). Of the course of time since before I started this job a few of the tables got out of sync with each other and because of seriously poor design on the part of my predecessor, I can't just pick a table that is the the definitive source and recreate the other with the "definitive" data. The reason being that some internal apps are built on the data on the internal DB that is different than the data in the external DB which has a separate app that is built on the same data.

I end up needing to do a row by row comparison of every one of the tables in the schema that is out of sync. Is there a better way to do this than to run a query for each DB, dump it to a csv and then diff the two csvs?

# ? Jan 29, 2008 14:40

Adbot: ADBOT LOVES YOU

# ? Apr 29, 2024 10:04

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

I understand the idea of separating logic in a model-view-control system, but I don't understand the concept of separating the SQL with a templating system. I'm working in PHP, where can I find more info about how and why to do it?

# ? Jan 30, 2008 00:28

Pi: Feb 15, 2001

Fly posted:

Does anyone here know whether Hibernate Query Language (and the JPA's QL implementations) supports the generation of dynamic multi-table queries with the correct JOINing of secondary tables?

I think you may want to look at Hibernate's criteria API for dynamically generating queries.

# ? Jan 30, 2008 00:50

npe: Oct 15, 2004

fletcher posted:

I understand the idea of separating logic in a model-view-control system, but I don't understand the concept of separating the SQL with a templating system. I'm working in PHP, where can I find more info about how and why to do it?

I wouldn't sweat SQL templating, it's not a very standard approach and if you don't see the benefit then don't worry about it. For web frameworks, ORM classes seem to be more popular anyways.

# ? Jan 30, 2008 00:58

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

fletcher posted:

I understand the idea of separating logic in a model-view-control system, but I don't understand the concept of separating the SQL with a templating system. I'm working in PHP, where can I find more info about how and why to do it?

What do you mean by SQL templates exactly? I can interpret what you said in 3 ways:

1) Making a paramatized SQL query, then using "prepare(query)" and "execute(query_handle, parameters)" to actually execute it.

2) A set of common-place SQL tasks which have already been written for you, you just fill in the blanks. Kind of like a sample web-site layout.

3) Abstracting persistent record information away from the main code by using some sort of Persistence Layer (also known as ActiveRecord).

Or did you mean something else?

# ? Jan 30, 2008 01:34

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

minato posted:

What do you mean by SQL templates exactly?

Just separating SQL from the rest of my code. Somebody mentioned that it's important and helpful to do that, and I just wasn't sure why or how you do it.

# ? Jan 30, 2008 01:52

npe: Oct 15, 2004

Ok, let me clarify on why we do it, maybe it will help.

In our particular case, we have an application backed by an extremely complex schema with quite a lot of rows (tables in the billions of records). Of course, we have a team of developers who focus on writing Oracle PL/SQL. In the same way that you might hire an HTML/CSS guy to do design and layout, we've hired developers who worry about the performance of the myriad queries we rely on.

Because of this we can't use an ORM approach, because query tuning on this scale is extremely finicky. For applications where all you do is CRUD operations, ORM's are fine, but that's not us.

For insert/update operations we naturally use stored procedures, so those aren't a problem, and for the more common complex queries they maintain views for us to use. But overall, there are probably hundreds of types of queries in use by various facets of our application, and encoding all of them into procedures or views would pose a different set of problems.

This originally meant that for the static, non-changing queries we pretty much just hardcoded them into the application source directly, and for dynamic queries we'd end up doing custom query building from string concatenation.

However, this meant working with the Oracle guys was a huge pain, because our queries were embedded in our application code (which was mostly perl). For the static queries it was more of a nuisance, but for dynamically generated queries it was a disaster. What would happen is an Oracle guy would see an inefficient query running, and send us a revised query that could replace it. But the revised query would involve completely restructuring the query building function, and usually we'd avoid doing that and just put up with crappy query performance when we could.

With templated SQL, it's very much like templated HTML. We can send the .sql files to the Oracle guys, and they can run the static queries through their query analyzers, and with a little extra effort they can pretty clearly do the same for the dynamic queries. They can now do this without having to worry at all about the application code itself.

Again, it's sort of specific to a situation where separation of application logic and database queries is important.

npe fucked around with this message at 02:21 on Jan 30, 2008

# ? Jan 30, 2008 02:19

jwnin: Aug 3, 2003

yaoi prophet posted:

Lots of great wisdom

I cannot agree more with this. I get uncomfortable with ORM after a certain load.

It really seems to me like there are two entirely different camps regarding databases:
1) Clean separation between the app tier and the database tiers. Enforced through by denying access to tables and driving access through stored procs, views, etc, allowing for a highly secure design and the ability to change the physical data model with no impact to the application tier.
2) The ORM camp. I hate to be short sighted, but it just seems like a pattern used by developers who see the database as nothing more than a file system. I know this is not correct, but there is some truth to it.

# ? Jan 30, 2008 04:21

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

jwnin posted:

I cannot agree more with this. I get uncomfortable with ORM after a certain load.

It really seems to me like there are two entirely different camps regarding databases:
1) Clean separation between the app tier and the database tiers. Enforced through by denying access to tables and driving access through stored procs, views, etc, allowing for a highly secure design and the ability to change the physical data model with no impact to the application tier.
2) The ORM camp. I hate to be short sighted, but it just seems like a pattern used by developers who see the database as nothing more than a file system. I know this is not correct, but there is some truth to it.

We use ORM via a bespoke Persistence Layer mechanism, and for the most part it is great. It's so convenient to be able to write code like this:

php:

<?
$org_name = User::getByOID($user_id)->getOrganization()->getName();
?>

Internally, the first function call will get the row containing the user object, convert it into an object. The second "->getOrganization()" will use some shadow info to retrieve the row containing that user's organization, convert that into an object. And the final call "->getName()" will retrieve the Name of that organization. So internally, 2 SQL calls are made to retrieve the User and Organization rows respectively.

This kind of code is very clear, there's no SQL involved so it's more maintainable and there's less for the app developer to understand, and it's very easy to write.

But where it falls down is that it's very difficult to optimize without breaking the paradigm. Say I wanted to do this kind of thing in a loop:

php:

<?
foreach($user_id_array as $user_id) {
   $org_names[] = User::getByOID($user_id)->getOrganization()->getName();
   ...
}
?>

Internally that's still 2 SQL calls per user-id which is hardly scaleable. A SQL developer would know that he could get the entire list with just 1, but it's necessary to break out of the ORM system in order to do that.

My experience seems to jibe with that of jwnin's above, in that some of my co-developers are only willing to use ORM and will go to any lengths to avoid writing SQL (because they're not very good at it), rather than seeing ORM as just a convenient tool that should be abandoned once performance gets ridiculously slow.

# ? Jan 30, 2008 04:42

nbv4: Aug 21, 2002; by Duchess Gummybuns

quick question, this is my query:

code:

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5'
ORDER BY manufacturer

If the field is NULL, it has that at the top. How can I change it so that NULL to be sorted at the bottom? Adding DESC at the end will do this, but I want the rest of the items to be alphabetically sorted.

# ? Feb 1, 2008 06:39

Victor: Jun 18, 2004

code:

order by coalesce(manufacturer, chr(127))

Use whatever function generates a character from its code. Increase the number if you're using Unicode.

# ? Feb 1, 2008 06:51

nbv4: Aug 21, 2002; by Duchess Gummybuns

Victor posted:

code:
order by coalesce(manufacturer, chr(127))
Use whatever function generates a character from its code. Increase the number if you're using Unicode.

cool that worked. I couldn't get that char() thing to work, so I just used

order by coalesce(manufacturer, 'ZZZZZZZZZZZ')

and it worked fine.

# ? Feb 1, 2008 21:34

Victor: Jun 18, 2004

I had "zzzzzz" written out, but then decided that was lame, breakable, and tried for something better. Oh well...

# ? Feb 1, 2008 21:46

kalleboo: Jan 13, 2001; Hj�lp

nbv4 posted:

cool that worked. I couldn't get that char() thing to work, so I just used

order by coalesce(manufacturer, 'ZZZZZZZZZZZ')

and it worked fine.

�ngstr�m, or god forbid 甘い贅沢 will break your system

I don't know about other DB systems, but MySQL lets you do

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5'
ORDER BY manufacturer IS NULL, manufacturer

kalleboo fucked around with this message at 18:12 on Feb 2, 2008

# ? Feb 2, 2008 18:07

chocojosh: Jun 9, 2007; D00D.

kalleboo posted:

�ngstr�m, or god forbid 甘い贅沢 will break your system

I don't know about other DB systems, but MySQL lets you do

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5'
ORDER BY manufacturer IS NULL, manufacturer

If you can't do that, how about the union clause (yes, I realize that this is probably not the most efficient)?

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5' AND
manufacturer IS NOT NULL
ORDER BY manufacturer
UNION ALL
SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5' AND
manufacturer IS NOT NULL
ORDER BY manufacturer

# ? Feb 3, 2008 18:29

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

chocojosh posted:

If you can't do that, how about the union clause (yes, I realize that this is probably not the most efficient)?

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5' AND
manufacturer IS NOT NULL
ORDER BY manufacturer
UNION ALL
SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5' AND
manufacturer IS NOT NULL
ORDER BY manufacturer

I think you meant to make one of those "IS NOT NULL"s an "IS NULL". Also, I think most DBs only let you have one ORDER BY clause which comes after all of the SELECT statments and orders the entire query.

kalleboo posted:

�ngstr�m, or god forbid 甘い贅沢 will break your system

I don't know about other DB systems, but MySQL lets you do

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5'
ORDER BY manufacturer IS NULL, manufacturer

In T-SQL you could do it that way, though you'd probably have to change it to

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5'
ORDER BY CASE WHEN manufacturer IS NULL THEN 1 ELSE 0 END, manufacturer

# ? Feb 3, 2008 19:29

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Lets say you have products in one table and votes for the products in another table. How do you get the products and total votes for each product?

# ? Feb 4, 2008 05:31

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

fletcher posted:

Lets say you have products in one table and votes for the products in another table. How do you get the products and total votes for each product?

code:

SELECT product_name, COUNT(votes)
FROM products
   JOIN votes USING (product_id)
GROUP BY product_name

# ? Feb 4, 2008 06:05

chocojosh: Jun 9, 2007; D00D.

Jethro posted:

I think you meant to make one of those "IS NOT NULL"s an "IS NULL". Also, I think most DBs only let you have one ORDER BY clause which comes after all of the SELECT statments and orders the entire query.

Entirely correct; this is what happens when I post from memory (I don't use SQL every day at my job either).

# ? Feb 4, 2008 06:30

Victor: Jun 18, 2004

Jethro posted:

ORDER BY CASE WHEN manufacturer IS NULL THEN 1 ELSE 0 END, manufacturer

Nice, this one is perfectly robust.

# ? Feb 4, 2008 07:09

Wazzerphuk: Feb 9, 2001; Hating Chelsea before it was cool
Winner of the PWM POTM for September
Winner of the PWM POTM for January
Co-Winner of the PWM POTM for March

code:

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5'
ORDER BY manufacturer NULLS LAST

I like Postgres

# ? Feb 4, 2008 17:00

Snozzberry Smoothie: Jun 27, 2005; by Fragmaster

Using MySQL, how can I update a table column to make all the data UPPERCASE?

# ? Feb 5, 2008 01:46

noonches: Dec 5, 2005; It represents my lifestyle and stratus as a street savy irreverent youth, who lives large, and yet hungers for the next level in life.

Snozzberry Smoothie posted:

Using MySQL, how can I update a table column to make all the data UPPERCASE?

I believe upper() is what you are looking for.

# ? Feb 5, 2008 01:52

ray2k: Feb 7, 2004; Puppet scum beware!

Alright, what's the norm for getting aggregate counts of 1:1 relationships? Like, if I have a Product table with a CategoryId that foreign keys to Category.CategoryId, how would I get the number of Products by CategoryId, including 0's for any CategoryId's not being used by a Product record? I'm looking for a single T-SQL query, I know I could just select the source data and aggregate programmatically in either a stored procedure using temp tables or in C# code, but I'm going to have to do quite a few that would make that approach too unwieldy.

# ? Feb 5, 2008 19:24

kalleboo: Jan 13, 2001; Hj�lp

ray2k posted:

Alright, what's the norm for getting aggregate counts of 1:1 relationships? Like, if I have a Product table with a CategoryId that foreign keys to Category.CategoryId, how would I get the number of Products by CategoryId, including 0's for any CategoryId's not being used by a Product record?

do you mean something like SELECT Category.CategoryId, COUNT(Product.ProductId) FROM Category LEFT JOIN Product ON Product.CategoryId=Category.CategoryId GROUP BY Category.CategoryId or is there some catch in your scenario I'm missing?

# ? Feb 5, 2008 19:52

ray2k: Feb 7, 2004; Puppet scum beware!

kalleboo posted:

do you mean something like SELECT Category.CategoryId, COUNT(Product.ProductId) FROM Category LEFT JOIN Product ON Product.CategoryId=Category.CategoryId GROUP BY Category.CategoryId or is there some catch in your scenario I'm missing?

Yeah, I did some more googling and got something working similarly. I knew I would have a left join but I wasn't aware COUNT() was deterministic (I couldn't put two and two together). Thanks though.

# ? Feb 5, 2008 20:08

Victor: Jun 18, 2004

kalleboo, where did you put the [code][/code] tags?

# ? Feb 5, 2008 20:09

kalleboo: Jan 13, 2001; Hj�lp

Victor posted:

kalleboo, where did you put the [code][/code] tags?

It was so short and simple I felt like it fit best inline. SA needs more \verb

# ? Feb 5, 2008 23:53

Victor: Jun 18, 2004

Awww, I thought you were going to say that the forums were temporarily out code tags or something.

# ? Feb 6, 2008 00:37

kalleboo: Jan 13, 2001; Hj�lp

php:

<?
SELECT Category.CategoryId, COUNT(Product.ProductId)
FROM Category 
LEFT JOIN Product ON Product.CategoryId=Category.CategoryId
GROUP BY Category.CategoryId?>

Better?

# ? Feb 6, 2008 00:41

Victor: Jun 18, 2004

It's blue! Prettttty...

# ? Feb 6, 2008 01:02

increased: Feb 20, 2001

MySQL Indices

Can someone quickly summarize the difference between having an index on one field, and having an index on multiple fields?

# ? Feb 7, 2008 00:43

Rumsfoord: Jan 29, 2005; Eat or Be Eaten

Filash posted:

MySQL Indices

Can someone quickly summarize the difference between having an index on one field, and having an index on multiple fields?

http://dev.mysql.com/doc/userguide/en/indexing-composite.html

# ? Feb 7, 2008 02:10

increased: Feb 20, 2001

Rumsfoord posted:

http://dev.mysql.com/doc/userguide/en/indexing-composite.html

Big ups.

# ? Feb 7, 2008 02:16

Walked: Apr 14, 2003

Can someone help me understand:

http://msdn2.microsoft.com/en-us/library/ms186734.aspx

I am not getting what OVER does in this statement - I think I understand the remainder of how the paging works, but I'm somewhat new with SQL and I dont really "get" the OVER clause.

:confused:

edit: Or feel free to suggest me a paging solution for SQL Server 2005 - I'm trying to avoid using the ASP.NET PagedDataSource :downs:

Walked fucked around with this message at 04:59 on Feb 7, 2008

# ? Feb 7, 2008 04:51

nbv4: Aug 21, 2002; by Duchess Gummybuns

I have a PHP script that makes a few hundred mysql queries, then executes them one by one. All of these queries are INSERT queries. The way I way it is now, if one of those queries fails, it's a pain in the rear end to go through and fix whatever problem that may have occurred. I want to have it so the system does a dry run through each query, and only if every single query is valid, it executes them all. Is there any way to test a query against the database? It's probably very simple, but Googling gives me nothing.

# ? Feb 7, 2008 10:46

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

nbv4 posted:

I have a PHP script that makes a few hundred mysql queries, then executes them one by one. All of these queries are INSERT queries. The way I way it is now, if one of those queries fails, it's a pain in the rear end to go through and fix whatever problem that may have occurred. I want to have it so the system does a dry run through each query, and only if every single query is valid, it executes them all. Is there any way to test a query against the database? It's probably very simple, but Googling gives me nothing.

Why would a query fail? If a query fails, that's a sign that either your data or code is broken.

Do you mean "Don't fail if the record is already there?". Because to do that, you can always do:

code:

INSERT INTO foo (foo_id, bar)
SELECT $foo_id, $bar
WHERE NOT EXISTS (SELECT 1
   FROM foo
   WHERE foo_id = $foo_id
   )

minato fucked around with this message at 11:33 on Feb 7, 2008

# ? Feb 7, 2008 11:24

nbv4: Aug 21, 2002; by Duchess Gummybuns

minato posted:

Why would a query fail? If a query fails, that's a sign that either your data or code is broken.

Do you mean "Don't fail if the record is already there?". Because to do that, you can always do:
code:
INSERT INTO foo (foo_id, bar)
SELECT $foo_id, $bar
WHERE NOT EXISTS (SELECT 1
   FROM foo
   WHERE foo_id = $foo_id
   )

the user uploads a file that is transformed into about 600 queries. If one line of that data is not right for whatever reason, only 599 lines will get entered, and it'll be absolute hell to get things right. I need some way to ensure 100% of the queries are correct, or else it tells the user his data needs looking over.

# ? Feb 7, 2008 11:31

minato: Jun 7, 2004; cutty cain't hang, say 7-up.; Taco Defender

nbv4 posted:

the user uploads a file that is transformed into about 600 queries. If one line of that data is not right for whatever reason, only 599 lines will get entered, and it'll be absolute hell to get things right. I need some way to ensure 100% of the queries are correct, or else it tells the user his data needs looking over.

You also sound like you might want to learn about transactions. That's where you run a "BEGIN" statement to take a snapshot of the database, run a bunch of SQL statements, and if any one of them fails you can "ROLLBACK" to the state of the database when you took the snapshot. If they all work and you're happy with it, you run "COMMIT" and it saves them all.

(Edit: transactions are only supported in relatively recent versions of MySQL, I don't think 4 supports them)

But really, you should be validating all data before it gets anywhere near the database before beginning any inserts.

# ? Feb 7, 2008 11:34

Adbot: ADBOT LOVES YOU

# ? Apr 29, 2024 10:04

nbv4: Aug 21, 2002; by Duchess Gummybuns

minato posted:

But really, you should be validating all data before it gets anywhere near the database before beginning any inserts.

I am, I just would like one last line of defence against total query entry, just for piece of mind against anything unforeseen... I tried running through each query once with "LIMIT 0" attached, but that didn't work. I'll look into the transaction thing.

# ? Feb 7, 2008 11:55

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »