Post #399,844
3/9/15 6:24:46 PM
3/10/15 11:20:47 AM
|
Re: Here is the easiest question I've received on SQL
As in PostgreSQL.
:deal_id_1 and :deal_id_2 are the variables that would be procedure parameters.
This can be tested in psql by setting them so:
\set deal_id_1 1 \set deal_id_2 2
select s.issue_id, ii.issue_name, s.d1_par, s.d2_par, abs(coalesce(s.d1_par, 0.0) - coalesce(s.d2_par, 0.0)) as par_difference from issue ii, (select a.issue_id, sum(d1.par_amount) d1_par, sum(d2.par_amount) d2_par from issue i join asset a on a.issue_id = i.issue_id left outer join asset d1 on a.issue_id = d1.issue_id and d1.deal_id = :deal_id_1 left outer join asset d2 on a.issue_id = d2.issue_id and d2.deal_id = :deal_id_2 group by a.issue_id) s where s.issue_id = ii.issue_id;
Regards, -scott Welcome to Rivendell, Mr. Anderson.
Edited by malraux
March 10, 2015, 11:20:47 AM EDT
|
Post #399,857
3/10/15 11:16:31 AM
|
deal_1_par_amount, deal_2_par_amount not displayed
I modified the top SELECT statement because these 2 fields are requested to be displayed:
select s.issue_id, ii.issue_name,d1.par_amount as deal_1_par_amount,d2.par_amount as deal_2_par_amount, abs(coalesce(s.d1_par, 0.0) - coalesce(s.d2_par, 0.0)) as par_difference
[...]
However, T-SQL throws the following errors:
"Msg 4104, Level 16, State 1, Line 5 The multi-part identifier "d1.par_amount" could not be bound. Msg 4104, Level 16, State 1, Line 5 The multi-part identifier "d2.par_amount" could not be bound."
because those two fields are in the nested SELECT.
Satan (impatiently) to Newcomer: The trouble with you Chicago people is, that you think you are the best people down here; whereas you are merely the most numerous. - - - Mark Twain, "Pudd'nhead Wilson's New Calendar" 1897
|
Post #399,858
3/10/15 11:21:39 AM
|
Re: deal_1_par_amount, deal_2_par_amount not displayed
s.d1_par and s.d2_par are what you want. Note that they are already being used in the par_difference calculation.
Regards, -scott Welcome to Rivendell, Mr. Anderson.
|
Post #399,859
3/10/15 11:49:33 AM
|
FWIW I hate overly-terse table names like that
"i" "ii" and "s" are plenty easy to type, and they're probably the initials of the logical table identifier in your head, but I picked different names and the short names you used make it hard for me to read.
|
Post #399,860
3/10/15 12:04:53 PM
|
Eh.
Not what I would use in actual SQL.
And actually, I so rarely write raw SQL these days that I couldn't care less what they're named. That's what the ORM is for.
Regards, -scott Welcome to Rivendell, Mr. Anderson.
|
Post #399,861
3/10/15 12:48:52 PM
|
Ignorant question
I've never used a SQL generating tool or OR mapper. The last time I looked at them, the ones that were available to me (meaning free/OSS) generated nasty unreadable crap.
So, have the tools gotten so good that writing SQL by hand isn't really needed any more? Or are they just good enough, often enough that you take what they give you and only dig into the SQL when the profiler has a problem it can't fix?
|
Post #399,865
3/10/15 1:30:28 PM
|
I use Django's ORM these days
On rare occasions I may come across a reporting query that I want to run that isn't easily represented by the ORM. Additionally, I only look at the generated SQL when there's a problem to debug, which is pretty rare. Customer.objects.filter(status=Customer.STATUS.active, subscription__cancelation_date__lte=some_date) SELECT * from customer INNER JOIN subscription ON subscription.customer_id = customer.id WHERE customer.status = 'active' AND subscription.cancelation_date >= :some_date:;
A very large benefit of using the Django ORM, beyond the automatic CRUD operations and query generation, is the database migration system. I make a change to the class, like adding a new CharField, and run a schemamigration command that creates a migration script with forward and backward migrations. In each of my environments, the script runs and automatically applies the migration if it hasn't been already. Our deployments are 100% automated including database changes.
Regards, -scott Welcome to Rivendell, Mr. Anderson.
|
Post #399,868
3/10/15 1:40:38 PM
|
The tools have definitely gotten better, then
I actually worked on two different DB abstraction classes for PHP before the PEAR one was good enough to switch to it. Now, that's one of those things that you'd be a fool to write yourself.
Of course, that just abstracts the connections and the basic operations. You still write your own SQL.
|
Post #399,870
3/10/15 1:58:19 PM
|
PHP ORMs are execrable.
We used Doctrine here... terrible.
I've been using the Django ORM for years, since 2009 or so. It hasn't changed much... PHP is just full of crap libraries, IMO.
Regards, -scott Welcome to Rivendell, Mr. Anderson.
|
Post #399,872
3/10/15 2:49:05 PM
|
Only PHP library I care about now is WordPress
Yeah, it's a beast, but it does most of what I need.
|
Post #399,896
3/11/15 12:26:43 AM
|
This is unfortunately true.
Back when PEAR was in its infancy, I looked at what was on offer and all were shit. I wrote my own DB handler and my own ORM on top of it. Have re-implemented both a few times at successive jobs.
I wold posit that many PHP programmers just don't really understand how to write scalable, memory-efficient code. Sometimes you just can't give your script 2Gb of memory.
Wade.
|
Post #400,070
3/17/15 8:33:28 AM
|
Newbie ORM question.
Are all connections to the database made in the same user context? Here's why I'm asking. Way back in the mid-1990's I worked in MS Sql Server transaction environments where *no one* had access to base tables. Insert/Update/Delete was done exclusively through stored procedures and selects were done exclusively on stored procedures or views (there was a ton-o-regulation in this particular industry and still is - even every select had to be logged with timestamp and current_user). I haven't been coding in quite some time and having virtually no knowledge of the "proper" way to use ORM. How is the data security I was introduced to 20 years ago maintained now? I mean, you can't give every user of some application select/update/delete permissions on the base tables, right? Are the apps themselves responsible for all the logging of user data access?
|
Post #400,089
3/17/15 3:59:19 PM
|
Depends on the ORM, naturally
Generally speaking application servers will use a single or low number of database users, by context. That is, you may have a single application user named 'app' with CRUD permissions on all of the application tables. This enables connection pooling. If there are audit requirements, they can be built into the application, which is a trusted party in this mode of operation.
If that's not sufficient, then any connection pooling framework, ORM or otherwise, may not be indicated. Generally speaking only internal applications require this level of scrutiny, and a public-facing ecommerce site (or extranet situation) won't need it.
Note that Oracle has a means of operation that enables tagging of connections with audit information passed through from the application. This is another option if your RDBMS supports it.
Regards, -scott Welcome to Rivendell, Mr. Anderson.
|
Post #400,091
3/17/15 4:43:33 PM
|
Thanks.
|