|
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 |
|
|
# ? May 16, 2024 18:03 |
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 |
|
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 |
|
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 |
|
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 |
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 |
|
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 |
|
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 |
|
jwnin posted:I cannot agree more with this. I get uncomfortable with ORM after a certain load. 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(); ?> 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(); ... } ?> 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 |
|
quick question, this is my query:code:
|
# ? Feb 1, 2008 06:39 |
|
code:
|
# ? Feb 1, 2008 06:51 |
|
Victor 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.
|
# ? Feb 1, 2008 21:34 |
|
I had "zzzzzz" written out, but then decided that was lame, breakable, and tried for something better. Oh well...
|
# ? Feb 1, 2008 21:46 |
|
nbv4 posted:cool that worked. I couldn't get that char() thing to work, so I just used 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 |
|
kalleboo posted:Ångström, or god forbid 甘い贅沢 will break your system 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 |
|
chocojosh posted:If you can't do that, how about the union clause (yes, I realize that this is probably not the most efficient)? 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 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 |
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 |
|
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:
|
# ? Feb 4, 2008 06:05 |
|
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 |
|
Jethro posted:ORDER BY CASE WHEN manufacturer IS NULL THEN 1 ELSE 0 END, manufacturer
|
# ? Feb 4, 2008 07:09 |
|
code:
|
# ? Feb 4, 2008 17:00 |
|
Using MySQL, how can I update a table column to make all the data UPPERCASE?
|
# ? Feb 5, 2008 01:46 |
|
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 |
|
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 |
|
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?
|
# ? Feb 5, 2008 19:52 |
|
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 |
|
kalleboo, where did you put the [code][/code] tags?
|
# ? Feb 5, 2008 20:09 |
|
Victor posted:kalleboo, where did you put the [code][/code] tags?
|
# ? Feb 5, 2008 23:53 |
|
Awww, I thought you were going to say that the forums were temporarily out code tags or something.
|
# ? Feb 6, 2008 00:37 |
|
php:<? SELECT Category.CategoryId, COUNT(Product.ProductId) FROM Category LEFT JOIN Product ON Product.CategoryId=Category.CategoryId GROUP BY Category.CategoryId?>
|
# ? Feb 6, 2008 00:41 |
|
It's blue! Prettttty...
|
# ? Feb 6, 2008 01:02 |
|
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 |
|
Filash posted:MySQL Indices http://dev.mysql.com/doc/userguide/en/indexing-composite.html
|
# ? Feb 7, 2008 02:10 |
|
Rumsfoord posted:http://dev.mysql.com/doc/userguide/en/indexing-composite.html Big ups.
|
# ? Feb 7, 2008 02:16 |
|
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. edit: Or feel free to suggest me a paging solution for SQL Server 2005 - I'm trying to avoid using the ASP.NET PagedDataSource Walked fucked around with this message at 04:59 on Feb 7, 2008 |
# ? Feb 7, 2008 04:51 |
|
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 |
|
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. Do you mean "Don't fail if the record is already there?". Because to do that, you can always do: code:
minato fucked around with this message at 11:33 on Feb 7, 2008 |
# ? Feb 7, 2008 11:24 |
|
minato posted:Why would a query fail? If a query fails, that's a sign that either your data or code is broken. 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 |
|
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 |
|
|
# ? May 16, 2024 18:03 |
|
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 |