Club PA 2.0 has arrived! If you'd like to access some extra PA content and help support the forums, check it out at patreon.com/ClubPA
The image size limit has been raised to 1mb! Anything larger than that should be linked to. This is a HARD limit, please do not abuse it.
Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

[Programming] Mirror, mirror, on the wall, show the git diff for them all

18990929495100

Posts

  • bowenbowen How you doin'? Registered User regular
    Oh you made it seem like the entire system used that specific person's AD credentials and they were also a service account.

    That's a bizarre bug, maybe make a copy of the user in AD and see if it happens for that user too.

    Ladies.
  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    Okay, after some fiddling around here's some things I've found.
    Preface: I did not create this application or set it up initially, I inherited it.
    There's two different views the users can have, a detail, and a dashboard view. On the old server (which used a wide open sql service account, not the very limited AD service account I have running on the new server) everything works as it has been for the past few years.

    On the new server, the dashboard view does not work. Technically, I can get the dashboard view to work to work if I'm coming from the old EMR we used to use here at our local hospitals, there's just no results since those have been out of use for almost 2 years. If it set it to be one of the remote sites, it fails with the error that it couldn't log in the user.

    The detail view works fine. The detail view queries the exact same table/view as the dashboard view.
    :rotate:

    steam_sig.png
  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    edited May 2016
    Mystery (mostly) solved!

    The view it was hitting was accessing another table on a different server. I didn't know this (I don't actually have access to those tables, because nothing on the database side of this has changed in 3 years or would probably ever change, why would I? :rotate: ) so I didn't ask for it when getting the account provisioned.

    This doesn't explain why querying that same view for the detail view didn't cause the same error*, but at least I've fixed it.

    If the stupid exception had actually put the name of the server/database it was failing to log in to I might have saved 4 hours.

    *Not being able to look at exactly how it's built, my working theory is that based on the information the detail mode used, the query optimizer decided that table wasn't even needed before running the query that builds the view.

    Tofystedeth on
    steam_sig.png
    gavindelschuss
  • Jimmy KingJimmy King Registered User regular
    Off to take the final final!

    admanbecco the dolphincrimsoncoyoteInfidelDisruptedCapitalistbowenGhotian_alt
  • JasconiusJasconius sword criminal mad onlineRegistered User regular
    i finally got around to getting Qt5 set up and running

    this is... actually pleasant!?

    im only using QWidgets though. I'm not sure I ever want to touch their HTML/CSS stuff

    Try EVE Online with my referral code, and I'll like you a little bit: https://www.eveonline.com/signup/?invc=86622db9-a6f9-41e8-b846-c305f625b55b
  • Monkey Ball WarriorMonkey Ball Warrior A collection of mediocre hats Seattle, WARegistered User regular
    edited May 2016
    Hey programming thread, seems like it's been eons since I've posted here. How have you guys been? I'm glad to see some familiar names like Honky and Bowen are still running around.

    Anyway, I am curious if there's a better way to do something. I need to do some best-effort deduplication of some strings at a decent rate/sec. By "best-effort" i mean that false negatives (not detecting a duplicate string) are okay, in fact they are unavoidable because I can't track things forever and I have no guarantee of order. But false positives, more than astronomically rare, are unacceptable.

    Right now I'm doing a Base64 encoding of the SHA256 of the string (not just naive hex to save ~1/3 the bytes but still keep it ascii). I'm using these hashes as keys in a Guava Cache with a reasonable time expiration (as the messages, while not in order, have some amount of temporal locality). All the values are just boolean true's. I lose the cache on a restart / code update, so I expect to let some duplicates through when this happens.

    Is there a better tool than a Guava Cache for this sort of thing? Should I try to use Guava's weak key feature instead of / as well as a time-based expiration? One might consider offloading this onto another service like Redis but for now the false negatives on restart probably didn't justify propping something separate up. Anyone have any experience with this sort of thing or any advice for me?

    Monkey Ball Warrior on
    "I resent the entire notion of a body as an ante and then raise you a generalized dissatisfaction with physicality itself" -- Tycho
    urahonky
  • mightyjongyomightyjongyo Registered User regular
    Anyone want to work devops with me? Everyone I work with doesn't seem interested and I am slowlydefinitely drowning under the amount of shit that needs to be worked on.

    *sob*

    templewulf
  • bowenbowen How you doin'? Registered User regular
    Anyone want to work devops with me? Everyone I work with doesn't seem interested and I am slowlydefinitely drowning under the amount of shit that needs to be worked on.

    *sob*

    Hahahahahaha... haha..

    oh god

    Ladies.
  • bowenbowen How you doin'? Registered User regular
    I just want to be paid well, that's all, that's hard enough without being 3 people at once.

    Ladies.
    mightyjongyoiTunesIsEvilnaengwenMonkey Ball Warrior
  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    edited May 2016
    SQL hate:
    Trying to select a particular record/set of records from a table based on the mix/max of column x, grouped only by column y, but needing the actual data from columns a through whatever.
    I've never found a way to do it that doesn't involve a bunch of a nested queries. Particularly annoying when there's like 5 joins involved to get there.

    Tofystedeth on
    steam_sig.png
  • bowenbowen How you doin'? Registered User regular
    SQL hate:
    Trying to select a particular record/set of records from a table based on the mix/max of column x, grouped only by column y, but needing the actual data from columns a through whatever.
    I've never found a way to do it that doesn't involve a bunch of a nested queries. Particularly annoying when there's like 5 joins involved to get there.
    SELECT * FROM [Table] WHERE [X] BETWEEN 3 AND 5 GROUP BY [Y]
    

    No?

    Ladies.
  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    bowen wrote: »
    SQL hate:
    Trying to select a particular record/set of records from a table based on the mix/max of column x, grouped only by column y, but needing the actual data from columns a through whatever.
    I've never found a way to do it that doesn't involve a bunch of a nested queries. Particularly annoying when there's like 5 joins involved to get there.
    SELECT * FROM [Table] WHERE [X] BETWEEN 3 AND 5 GROUP BY [Y]
    

    No?
    What I need is more like
    select
    max(bill_activity.activity),
    bill_activity.bill_type,
    bill_activity.bill_amt,
    encounter.encounter_id
    from encounter (several joins later) JOIN bill_activity
    where bill_activity.bill_type in (blah, blah2, blah3)
    group by
    encounter.encounter_id,
    bill_activity.bill_type
    
    Basically, the most recent activity of each type, per encounter.
    But including other information (such as the activity amount) in the select, requires it to be put in the group by, which means that it then creates another line for each distinct amount when I need a single result per type x encounter.

    steam_sig.png
  • hippofanthippofant ティンク Registered User regular
    edited May 2016
    Ohhh shi, there is absolutely an SQL way to collapse that, but I forget what it was since I last had to use it some 10+ years ago.

    Edit: One way to do it is to select out the columns you want, and re-join. If you're using a GROUP BY, SQL demands that everything in the SELECT be in the GROUP BY or be inside an aggregate function, because otherwise, it doesn't know what value to return in the record. So you could take out bill_activity.bill_amt, use this as a subquery, and then join the results of the subquery with bill_amt to get the full resultset you want. I'm not sure how efficient that is though.

    hippofant on
  • urahonkyurahonky Registered User regular
    Anyone here have some experience with Go? I'm working with this project: https://godoc.org/golang.org/x/crypto/nacl/secretbox in Go and this: http://pynacl.readthedocs.io/en/latest/secret/ in Python.

    I'm generating the final token in python and sending it to this Go script.

    This is the key that was generated for me in python is:
    }\x04\x9b4H\x88\xcb>\x13\\"(\x01|\x12\x0f\xbfTj\x85I.DXOL\xba\xbd4S\x1b1
    

    How do I make this a byte array in Go? Or, at least, count the number of bytes in it?

    bytes.Count(nil, privateKey)) seems to return nothing.

    When I pass that key in using something like:
    var privateKey = []byte("}\x04\x9b4H\x88\xcb>\x13\\"(\x01|\x12\x0f\xbfTj\x85I.DXOL\xba\xbd4S\x1b1")
    

    Then:
    out, ok := secretbox.Open(nil, message, &nonce, &privateKey)
    

    I get:
    cannot use &privateKey (type *[]byte) as type *[32]byte in argument to secretbox.Open

  • urahonkyurahonky Registered User regular
    And, as usual, I figure it out within 5 seconds of hitting Submit.
    var privateKey [32]byte
    copy(privateKey[:], "}\x04\x9b4H\x88\xcb>\x13\\"(\x01|\x12\x0f\xbfTj\x85I.DXOL\xba\xbd4S\x1b1")
    

    bowengavindelMonkey Ball Warrior
  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    edited May 2016
    Can anybody see anything wrong with this subquery? I'm using it in my select statement to do that lookup I was talking about earlier, and for some reason it's making Oracle complain about FROM not being where it is expected. If I comment out the query and replace with a constant (like I've done with the placeholder columns after it) it works fine, so I'm guessing I've subtly botched syntax in there but damn if I can find where.
    edit: Ahah! found the extra trailing ).

    Tofystedeth on
    steam_sig.png
  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    But it returns no results. Blargh.

    steam_sig.png
  • GhotiGhoti Registered User regular
    edited May 2016
    bowen wrote: »
    SQL hate:
    Trying to select a particular record/set of records from a table based on the mix/max of column x, grouped only by column y, but needing the actual data from columns a through whatever.
    I've never found a way to do it that doesn't involve a bunch of a nested queries. Particularly annoying when there's like 5 joins involved to get there.
    SELECT * FROM [Table] WHERE [X] BETWEEN 3 AND 5 GROUP BY [Y]
    

    No?
    What I need is more like
    select
    max(bill_activity.activity),
    bill_activity.bill_type,
    bill_activity.bill_amt,
    encounter.encounter_id
    from encounter (several joins later) JOIN bill_activity
    where bill_activity.bill_type in (blah, blah2, blah3)
    group by
    encounter.encounter_id,
    bill_activity.bill_type
    
    Basically, the most recent activity of each type, per encounter.
    But including other information (such as the activity amount) in the select, requires it to be put in the group by, which means that it then creates another line for each distinct amount when I need a single result per type x encounter.

    You could do a select on the MAX criteria for bill_activity within you join... assuming your unique identifier which you are joining is the encounter_id
    
    SELECT e.encounter_id, b.activity, b2.bill_activity.bill_type, b2.bill_activity.bill_amt
    FROM encounter e
    JOIN (SELECT MAX(bill_activity.activity), encounter_id FROM bill_activity GROUP BY encounter_id) b ON e.encounter_id = b.encounter_id
    JOIN bill_activity b2 ON b.activity = b2.activity
    

    At the very least, this might help get you on the right track.

    Ghoti on
    schussEvigilant
  • EchoEcho Moderator mod
    I need to get better at writing down what I mean when I look at stuff two weeks later.

    "Refactor this"

    *tries to read mind of past self*

    Echo wrote: »
    Let they who have not posted about their balls in the wrong thread cast the first stone.
    bowenEvigilantnaengwenMvrcktemplewulf
  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    Ghoti wrote: »
    bowen wrote: »
    SQL hate:
    Trying to select a particular record/set of records from a table based on the mix/max of column x, grouped only by column y, but needing the actual data from columns a through whatever.
    I've never found a way to do it that doesn't involve a bunch of a nested queries. Particularly annoying when there's like 5 joins involved to get there.
    SELECT * FROM [Table] WHERE [X] BETWEEN 3 AND 5 GROUP BY [Y]
    

    No?
    What I need is more like
    select
    max(bill_activity.activity),
    bill_activity.bill_type,
    bill_activity.bill_amt,
    encounter.encounter_id
    from encounter (several joins later) JOIN bill_activity
    where bill_activity.bill_type in (blah, blah2, blah3)
    group by
    encounter.encounter_id,
    bill_activity.bill_type
    
    Basically, the most recent activity of each type, per encounter.
    But including other information (such as the activity amount) in the select, requires it to be put in the group by, which means that it then creates another line for each distinct amount when I need a single result per type x encounter.

    You could do a select on the MAX criteria for bill_activity within you join... assuming your unique identifier which you are joining is the encounter_id
    
    SELECT e.encounter_id, b.activity, b2.bill_activity.bill_type, b2.bill_activity.bill_amt
    FROM encounter e
    JOIN (SELECT MAX(bill_activity.activity), encounter_id FROM bill_activity GROUP BY encounter_id) b ON e.encounter_id = b.encounter_id
    JOIN bill_activity b2 ON b.activity = b2.activity
    

    At the very least, this might help get you on the right track.
    I attempted to do something along these lines, because I remembered that they want the total activity amount of each type (which could be the result of multiple activities) but only the date of the most recent activity of each type, so I was able to break out the aggregates and the group by into something that should have worked. The problem is, to get from encounter_ID to the bill_activity table takes 4-5 joins, and I have to look up around 8 different activity types for each encounter, that all need to be split out. I tried it a bunch of different ways with subqueries in the select, subqueries in the from, a materialized subquery which gets them all and then is joined using different criteria.
    All of those resulted in the query running for about 20 minutes before I canceled it and not even returning a partial result set.

    What I ended up doing is pulling all the qualifying encounters, demographic data, and the encounter balance totals that are stored directly in that encounter's entry in the encounter table on the financial side, instead of having to calculate them from the transaction log.
    Then all the other stuff is each its own separate query that I'm passing the list of qualifying encounters to, which is working so far. The problem with this approach, is because of the way this Business Objects product does it, there's an internal limit on the number of records that can be passed in a list from one query to another, so over a large enough timespan it will cause the report to fail. It's also cumbersome as fuck to set up on the report formatting side of it.

    On the other hand, the deadline on this is the 13th, and for the initial run it will work well enough, and I can translate it into the other reporting solution (which takes longer to move into the Prod environment due to restrictions from our corporate overlords) before it gets big enough to cause problems.

    steam_sig.png
  • GhotiGhoti Registered User regular
    So I am at a bit of a loss on whether or not you still would like further assistance with your query. There are probably some refinements which could help with your performance. It also sounds like your design would have the activity type as a input variable. You would have to run it several times, but it should reduce the set of information to the point where it does not fail.

    What you described your join select would look more like this:
    JOIN (SELECT MAX(bill_activity.activity) AS activity, SUM(bill_activity.bill_amt) AS amt, bill_activity.bill_type, {joinparameter} FROM bill_activity GROUP BY bill_activity.bill_type, {joinparameter})
    

    This would give you the latest activity date, sum up the amount by type and allow you to make your joins back to the encounter_id.

  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    Nah, I'm pretty much done with it by now. That's pretty much exactly what I did, and it didn't work. There's a lot of limitations in the reporting systems we have to use for our EMR that we have to work around.
    So much stuff would be easier if we were allowed to make views, or functions, or custom tables, but we can't.

    steam_sig.png
    naengwen
  • bowenbowen How you doin'? Registered User regular
    Nah, I'm pretty much done with it by now. That's pretty much exactly what I did, and it didn't work. There's a lot of limitations in the reporting systems we have to use for our EMR that we have to work around.
    So much stuff would be easier if we were allowed to make views, or functions, or custom tables, but we can't.

    bwah?

    Ladies.
  • InfidelInfidel Heretic Registered User regular
    bowen wrote: »
    Nah, I'm pretty much done with it by now. That's pretty much exactly what I did, and it didn't work. There's a lot of limitations in the reporting systems we have to use for our EMR that we have to work around.
    So much stuff would be easier if we were allowed to make views, or functions, or custom tables, but we can't.

    bwah?

    Health industry where you have COTS applications and all the integration/reporting is done custom, but you have to deal with all your vendors and are super limited to what you can and cannot do with their database.

    At least that's what it was for me!

    OrokosPA.png
    Play D&D 4e? :: Check out Orokos and upload your Character Builder sheet! :: Orokos Dice Roller
    The PhalLounge :: Chat board for Critical Failures IRC! :: #CriticalFailures and #mafia on irc.slashnet.org
    TofystedethMvrck
  • bowenbowen How you doin'? Registered User regular
    Oh I assumed he was writing an EMR not using an OTS app.

    Ladies.
  • GhotiGhoti Registered User regular
    Ah. I've been on the wrong end of Business Objects permissions as well. If this is something you will be running on a consistent basis, maybe you can share all or some of what you have built already and see if a view could be created from its design.

  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    bowen wrote: »
    Oh I assumed he was writing an EMR not using an OTS app.
    Oh lord no. I'm not writing an EMR. We're a huge organization. That way lies madness.

    We use a product that rhymes with 'burner'.

    steam_sig.png
  • bowenbowen How you doin'? Registered User regular
    bowen wrote: »
    Oh I assumed he was writing an EMR not using an OTS app.
    Oh lord no. I'm not writing an EMR. We're a huge organization. That way lies madness.

    We use a product that rhymes with 'burner'.

    There's like hundreds of thousands of EMRs!

    Ladies.
  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    edited May 2016
    hippofant wrote: »
    Ohhh shi, there is absolutely an SQL way to collapse that, but I forget what it was since I last had to use it some 10+ years ago.

    Edit: One way to do it is to select out the columns you want, and re-join. If you're using a GROUP BY, SQL demands that everything in the SELECT be in the GROUP BY or be inside an aggregate function, because otherwise, it doesn't know what value to return in the record. So you could take out bill_activity.bill_amt, use this as a subquery, and then join the results of the subquery with bill_amt to get the full resultset you want. I'm not sure how efficient that is though.

    I think I found it*, from this SO answer. Nest 2 queries. The first one gets all the columns you care about, plus an additional one that uses the Rank() analytic function to rank the records partitioned by, in this case encounter_id and activity_type_code, according to activity_date_time, then the outer query gets just the columns I want, where rank = 1.


    *Again. This is far from the first time I've had to do this sort of thing I keep forgetting how. I finally remembered the proper terms to get me back to this very same SO answer.

    Tofystedeth on
    steam_sig.png
  • gavindelgavindel The reason all your software is brokenRegistered User regular
    I sometimes wish I could talk more freely about all the most eventful stuff at work.

    But while I like sharing how to nuke things from orbit, I like having a job tomorrow more...


    ecco the dolphincrimsoncoyote
  • BigBadWolfBigBadWolf Registered User regular
    hippofant wrote: »
    Ohhh shi, there is absolutely an SQL way to collapse that, but I forget what it was since I last had to use it some 10+ years ago.

    Edit: One way to do it is to select out the columns you want, and re-join. If you're using a GROUP BY, SQL demands that everything in the SELECT be in the GROUP BY or be inside an aggregate function, because otherwise, it doesn't know what value to return in the record. So you could take out bill_activity.bill_amt, use this as a subquery, and then join the results of the subquery with bill_amt to get the full resultset you want. I'm not sure how efficient that is though.

    I think I found it*, from this SO answer. Nest 2 queries. The first one gets all the columns you care about, plus an additional one that uses the Rank() analytic function to rank the records partitioned by, in this case encounter_id and activity_type_code, according to activity_date_time, then the outer query gets just the columns I want, where rank = 1.


    *Again. This is far from the first time I've had to do this sort of thing I keep forgetting how. I finally remembered the proper terms to get me back to this very same SO answer.

    If you're using SQL Server as a back-end, you should be able to do this fairly simply using CROSS APPLY. Using the train example from the SO answer it would be something like:
    SELECT trains.Train, LastTrain.Dest, LastTrain.MaxTime
    FROM <uniqueListOfTrains> as trains
    CROSS APPLY
    (
    	SELECT TOP 1 Train, Dest, Time 
    	FROM TrainTable
    	WHERE TrainTable.Train = trains.Train
    	Order By Time DESC
    ) as LastTrain
    

    The results in the cross apply are calculated for each row in your outer query (filtered by the where clause) - so you can return as many records as you like for each record in the outer query. Performance wise I've always found it much faster than nested joins, and a lot easier to understand!

    If you're not using SQL server than rank() functions are probably your best bet.

  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    edited May 2016
    Yup. This is using Oracle.
    Though I will try and remember that bit for later, because all of our internal stuff that we do ourselves is SQL Server.

    Tofystedeth on
    steam_sig.png
  • Bendery It Like BeckhamBendery It Like Beckham Hopeless Registered User regular
    Anyone want to work devops with me? Everyone I work with doesn't seem interested and I am slowlydefinitely drowning under the amount of shit that needs to be worked on.

    *sob*

    You'd have to teach me.

  • SpawnbrokerSpawnbroker Registered User regular
    DevOps has always seemed to me like a title that was invented by companies who want to pay one person to do three jobs.

    Battle.net: Spawnbroker#1471
    Steam: Spawnbroker
    Final Fantasy XIV: Spawn Broken
    Mvrck
  • bowenbowen How you doin'? Registered User regular
    edited May 2016
    DevOps has always seemed to me like a title that was invented by companies who want to pay one person to do three jobs.

    it was

    I do it

    it's the only way I can get paid a livable wage, without having to invest 10k to move across the country

    bowen on
    Ladies.
  • TofystedethTofystedeth veni, veneri, vamoosi Registered User regular
    Yesss, gettin' a new laptop at work to replace my ancient desktop. Normally I'd stick with desktop, but because we pay Dell per device, they're trying to get us all down to one device. My current desktop is probably about 8 years old, and has only 4GB RAM.
    This way I have something I can take to meetings. Currently I have a "training" laptop that was going unused. It won't even turn on unless plugged in.

    steam_sig.png
    ecco the dolphincrimsoncoyote
  • mightyjongyomightyjongyo Registered User regular
    Anyone want to work devops with me? Everyone I work with doesn't seem interested and I am slowlydefinitely drowning under the amount of shit that needs to be worked on.

    *sob*

    You'd have to teach me.

    Do you know what a linux is? Is source control not a foreign concept? If so then that puts you ahead of most people.
    bowen wrote: »
    DevOps has always seemed to me like a title that was invented by companies who want to pay one person to do three jobs.

    it was

    I do it

    it's the only way I can get paid a livable wage, without having to invest 10k to move across the country

    Yea. It's much cheaper to have me do three jobs for additional pay than invest in three people. Until I get burned out and quit, anyway.

    bowen
  • bowenbowen How you doin'? Registered User regular
    Anyone want to work devops with me? Everyone I work with doesn't seem interested and I am slowlydefinitely drowning under the amount of shit that needs to be worked on.

    *sob*

    You'd have to teach me.

    Do you know what a linux is? Is source control not a foreign concept? If so then that puts you ahead of most people.

    That's pretty pathetic, yet, so hard to find a job that pays well still :(

    Ladies.
  • mightyjongyomightyjongyo Registered User regular
    We had a training on DevOps recently, it was pretty much coding for sysadmins. Not trying to disparage sysadmins, but it was touting "infrastracture as code" as if code were a foreign concept.

  • zeenyzeeny Registered User regular
    edited May 2016
    Some of the most fun stuff I've done in the past 6 months has been DevOps and I've had a chance to work on a lot of fun things.
    I love the concept of you build it, you own it.

    zeeny on
    Infidel
This discussion has been closed.