• somePotato@sh.itjust.works
      link
      fedilink
      arrow-up
      54
      arrow-down
      2
      ·
      edit-2
      10 months ago

      I was disgusted by the XML at first, but it’s a readable query returning a sane JSON object.

      Meanwhile, I’m mantaining Java code where the SQL is a perfectly square wall of text, and some insane mofo decided the way to read the resulting list of Object[] 🤮 is getting each column by index… so I’d switch to SQXMLL in a heartbeat.

      • cmdrkeen@programming.dev
        link
        fedilink
        arrow-up
        23
        arrow-down
        1
        ·
        10 months ago

        React basically figured out how to make XML work.

        Remember, XML was actually designed for use cases like this, that’s why it came with XPath and XSLT, which let you make it executable in a sense by performing arbitrary transformations on an XML tree.

        Back in the day, at my first coding job, we had an entire program that had a massive data model encoded in XML, and we used a bunch of XSL to programmatically convert that into Java objects, SQL queries, and HTML forms. Actually worked fairly well, except of course that XSL was an awful language to do that all in.

        React simply figured out how to use JavaScript as the transformation language instead.

      • leftzero@lemmynsfw.com
        link
        fedilink
        arrow-up
        5
        arrow-down
        1
        ·
        10 months ago

        it’s a readable query returning a sane JSON object.

        No it’s not. What table is the data supposed to be coming from…?

    • sunnie@lemmy.caOP
      link
      fedilink
      arrow-up
      7
      arrow-down
      3
      ·
      10 months ago

      true, but having it look like a component might get annoying. since this is likely to stay at the top, having an island of non components between two components might make it hard to see where functions start and end. and if this isn’t used directly inside a component it’ll just look dumb and inefficient (this also looks like it’ll take way more to edit once you change something)

    • xmunk@sh.itjust.works
      link
      fedilink
      arrow-up
      17
      arrow-down
      3
      ·
      10 months ago

      I’d like you to think for a moment about CTEs, the HAVING clause, window functions and every other funky and useful thing you can do in SQL … Now just think, do you think that this syntax supports all those correctly?

  • RustyNova@lemmy.world
    link
    fedilink
    arrow-up
    43
    arrow-down
    3
    ·
    10 months ago

    Ah yes. That’s what the kids call “sqlx” right?

    NGL, if it has real time code completion and compile time SQL checks, this is fine.

  • akash_rawal@lemmy.world
    link
    fedilink
    arrow-up
    41
    arrow-down
    3
    ·
    10 months ago

    I actually like this. This would allow reuse of all the infrastructure we have around XML. No more SQL injection and dealing with query parameters? Sign me up!

      • akash_rawal@lemmy.world
        link
        fedilink
        arrow-up
        2
        arrow-down
        1
        ·
        10 months ago

        Better than parameterized queries. Yes, we have stuff like query("INSERT INTO table(status, name) VALUES ($1, $2);").bind(ent.status).bind(ent.name).execute..., but that’s kind of awful isn’t it? With XML queries, we could use any of the XML libraries we have to create and manipulate XML queries without risking ‘XML injection’. e.g we could convert ordinary structs/classes into column values automatically without having to use any ORM.

        • Doc Avid Mornington@midwest.social
          link
          fedilink
          English
          arrow-up
          2
          ·
          edit-2
          10 months ago

          I mean, that’s just a bad library interface. With a halfway decent interface, you can do something like

          query('insert into foo (status, name) values (:status, :name)', ent)
          

          No orm required. With tagged templates in JS, you can do

          q`insert into foo (status, name) values (${ent.status}, ${ent.name})`
          

          Even wrap it in a function with destructuring to get rid of ent:

          const addFoo = (q, {status, name}) =>
              q`insert into foo (status, name) values (${status}, ${name})`
          

          Typescript can add type safety on top of that, of course. And there’s the option to prepare a query once and execute it multiple times.

          Honestly, the idea of manipulating XML queries, if you mean anything more fancy than the equivalent of parameter injection, sounds over-complicated, but I’d love to see a more concrete example of what you mean by that.

          • akash_rawal@lemmy.world
            link
            fedilink
            arrow-up
            2
            ·
            edit-2
            10 months ago

            I was thinking along the lines of

            Plenty of libraries can build the XML using structs/classes. e.g. with serde:

            //Data type for row
            #[derive(serde::Serialize)]
            pub struct Foo {
            	pub status: String,
            	pub name: String,
            }
            
            //Example row
            let ent = Foo {
                status: "paid".into(),
                name: "bob".into(),
            }
            
            //Example execution
            sqlx::query(&serde_xml_rs::to_string(&InsertStmt{
            	table: "foo".into(),
            	value: &ent,
            })?).execute(&conn)?;
            

            Or with jackson-dataformat-xml:

            //Data type for row
            public class Foo {
                public string status;
                public string name;
            }
            
            //Example row
            Foo ent = new Foo();
            foo.status = "paid";
            foo.value = "bob";
            
            //Example execution
            XmlMapper xmlMapper = new XmlMapper();
            String xml = xmlMapper.writeValueAsString(new InsertStmt("foo", ent));
            try (Statement stmt = conn.createStatement()) {
                stmt.executeUpdate(xml)
            }
            

            I don’t do JS (yet) but maybe JSX could also do similar things with XML queries.

            No more matching $1, $2, … (or ? for mysql) with individual columns, I could dump entire structs/objects into a query and it would work.

  • expr@programming.dev
    link
    fedilink
    arrow-up
    40
    arrow-down
    2
    ·
    10 months ago

    Not only is this really gross, it’s also straight up wrong. It’s missing a from clause, and it makes no sense for a where clause to be nested under the select. The select list selects columns from rows that have already been filtered by the where clause. Same for the limit.

    Also just gonna go ahead and assume the JSX parser will happily allow SQL injection attacks…

  • namelivia@lemmy.world
    link
    fedilink
    arrow-up
    32
    arrow-down
    1
    ·
    edit-2
    10 months ago

    When you are assigned to write database queries at work and your academical background is that online react bootcamp

  • Primarily0617@kbin.social
    link
    fedilink
    arrow-up
    39
    arrow-down
    10
    ·
    10 months ago

    if you don’t believe that adding more structure to the absolute maniacal catastrophe that is sql is a good thing then i’m going to start to have doubts about your authenticity as a human being

    • Solemarc@lemmy.world
      link
      fedilink
      arrow-up
      27
      arrow-down
      2
      ·
      10 months ago

      If you think this is more structured than traditional SQL, I really disagree. Is this a select * query, it’s ambiguous. Also what table is being queried here there’s no from or other table identifier.

    • QuazarOmega@lemy.lol
      link
      fedilink
      arrow-up
      8
      arrow-down
      6
      ·
      edit-2
      10 months ago

      Me trying to remember on whose output data having, count, sum, etc. work

      Once you know functions you would have no reason to go back.
      I propose we make SQL into this:

      const MAX_AMOUNT = 42, MIN_BATCHES = 2
      
      database
          .from(table)
          .where(
              (amount) => amount < MAX_AMOUNT,
              table.field3
          )
          .select(table.field1, table.field3)
          .group_by(table.field1)
          .having(
              (id) => count(id) >MIN_BATCHES
              table.field0
          )
      

      (Sorry for any glaring mistakes, I’m too lazy right now to know what I’m doing)

      …and I bet I just reinvented the wheel, maybe some JavaScript ORM?

      • expr@programming.dev
        link
        fedilink
        arrow-up
        7
        arrow-down
        2
        ·
        edit-2
        10 months ago

        Because you never learned SQL properly, from the sound of it.

        Also, ORMs produce trash queries and are never expressive enough.

        • QuazarOmega@lemy.lol
          link
          fedilink
          arrow-up
          4
          arrow-down
          1
          ·
          edit-2
          10 months ago

          Because you never learned SQL properly, from the sound of it.

          You might be right, though, to be fair, I also keep forgetting syntax of stuff when I don’t use it very often (read SQL (._.`))

          Also, ORMa produce trash queries and are never expressive enough.

          I meant to say that I would like the raw SQL syntax to be more similar to other programming languages to avoid needing to switch between thinking about different flows of logic

        • emptyother@programming.dev
          link
          fedilink
          arrow-up
          4
          arrow-down
          1
          ·
          10 months ago

          ORMs produce good queries if you know what you do. Which requires proper knowledge of SQL, unfortunately.

      • marcos@lemmy.world
        link
        fedilink
        arrow-up
        4
        arrow-down
        1
        ·
        10 months ago

        Well, if you lose the OOPism of those dots, we can talk.

        Anyway, I’m really against the “having” tag. You need another keyword so that you can apply your filter after the group by?

        • QuazarOmega@lemy.lol
          link
          fedilink
          arrow-up
          2
          ·
          10 months ago

          Well, if you lose the OOPism of those dots, we can talk.

          That’s a good point, I didn’t even think about it, maybe a more functional style would make more sense?

          • marcos@lemmy.world
            link
            fedilink
            arrow-up
            2
            ·
            10 months ago

            Yes, I do. It’s a lot of effort and hidden functionality to try to paper over the fact that the statements do not compose.

        • Baby Shoggoth [she/her]@lemmy.blahaj.zone
          link
          fedilink
          English
          arrow-up
          0
          ·
          10 months ago

          having is less annoying way of not doing needless/bug-prone repetition. if you select someCalculatedValue(someInput) as lol you can add having lol > 42 in mysql, whereas without (ie in pgsql) you’d need to do where someCalculatedValue(someInput) > 42, and make sure changes to that call stay in sync despite how far apart they are in a complex sql statement.

          • Doc Avid Mornington@midwest.social
            link
            fedilink
            English
            arrow-up
            1
            ·
            10 months ago

            Postgres has the having clause. If it didn’t, that wouldn’t work, as you can’t use aggregates in a where. If you have to make do without having, for some reason, you can use a subquery, something like select * from (select someCalculatedValue(someInput) as lol) as stuff where lol > 42, which is very verbose, but doesn’t cause the sync problem.

            Also, I don’t think they were saying the capability having gives is bad, but that a new query language should be designed such that you get that capability without it.

        • QuazarOmega@lemy.lol
          link
          fedilink
          arrow-up
          1
          ·
          edit-2
          10 months ago

          Thanks for the suggestion! It looks interesting, not quite what I expected looking at that file*, but that may very well be better

          Edit: other examples seem a bit more similar to mine, cool!

      • drathvedro@lemm.ee
        link
        fedilink
        arrow-up
        1
        ·
        10 months ago

        No. The arrow function in where eliminates any possibility of using indexes. And how do you propose to deal with logical expressions without resorting to shit like .orWhereNot() and callback hell? And, most importantly, what about joins?

    • GBU_28@lemm.ee
      link
      fedilink
      English
      arrow-up
      1
      arrow-down
      1
      ·
      10 months ago

      Huh? Sql is one of the most powerful, action packed (as in you can move lots of shit with few commands) languages out there.

      It’s transferable and ubiquitous.

      • Primarily0617@kbin.social
        link
        fedilink
        arrow-up
        1
        ·
        10 months ago

        powerful isn’t the same as well-structured

        it was written to be a language that anybody could read or write as well as english, which just like every other time that’s been tried, results in a language that’s exactly as anal about grammar as C or Python except now it’s impossible to remember what that structure is because adding anything to the language to make that easier is forbidden

        when you write a language where its designers were so keen for it to remain human readable that they made deleting all rows in a table the default action, i don’t think “well structured” can be used to describe it

        • GBU_28@lemm.ee
          link
          fedilink
          English
          arrow-up
          1
          ·
          10 months ago

          Disagree, the difference between “week structured” and needing to know the rules of the verbs is pretty big, to me.

      • expr@programming.dev
        link
        fedilink
        arrow-up
        11
        ·
        10 months ago

        SQL is incredibly structured. It’s also a very good language, and developers need to stop piling on junk on top of it and producing terrible queries. Learn the damn language. It’s not that hard

  • kpw@kbin.social
    link
    fedilink
    arrow-up
    26
    arrow-down
    5
    ·
    10 months ago

    The most offensive thing here is the amount={5} attribute. What is it? It’s not XML.

    • III@lemmy.world
      link
      fedilink
      English
      arrow-up
      10
      arrow-down
      9
      ·
      10 months ago

      It never ceases to amaze me how far idiots will go to avoid learning the most simple things. SQL isn’t hard, people’s difficulty with it says a lot more about them than it does SQL.

      • emptyother@programming.dev
        link
        fedilink
        arrow-up
        11
        arrow-down
        2
        ·
        10 months ago

        People think in different ways. What might seem logical to you might look alien to another. I know SQL well enough to optimize queries, but I find it a lot easier to think about and write queries as LINQ methods. A lot more cleaner and logical to my brain.

      • MonkderZweite@feddit.ch
        link
        fedilink
        arrow-up
        1
        ·
        10 months ago

        Neither is sending form data to the server without any JS. It’s more robust too. Yet almost no form on the web works without JS.

  • JoYo@lemmy.ml
    link
    fedilink
    English
    arrow-up
    16
    arrow-down
    2
    ·
    10 months ago

    still more readable than sqlalchemy exceptions