Re: Insert/select...

Franck Routier <[email protected]> Tue, 05 Mar 2013 09:29:05 +0100
Newsgroups	gmane.comp.java.orm.simpleorm
Message-ID	<[email protected]>
--------------ms040900050403020109020908
Content-Type: multipart/alternative;
 boundary="------------000102050705030900050604"

--------------000102050705030900050604
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi Anthony,

I am very conscious this is the very limit of what an ORM should do. And=20
I know we want to keep SimpleORM simple :-)
But including the feature into the library made it possible to achieve=20
it with very few lines of code...

Another possibility would be to factor out all the query generation part=20
of SimpleORM into a separate package, but I didn't want to break everything=
.
I also still have in mind that if we take that direction, we will want=20
to include some missing functionalities, like more complex query=20
conditions :
- OR
- parenthesis
- maybe SFieldReference based on non equi-join
The latter can be very convinient when using nested-sets based=20
hierarchies (http://en.wikipedia.org/wiki/Nested_set_model) to avoid=20
recursivity and handle hierarchies in sql without relying on proprietary=20
extensions like Oracle's connect by. Currently I use SQuery.rawJoin to=20
handle this kind of hierarchies, which we use a lot in our app...

That said, I realize I've been a bit shortsighted, as I just didn't=20
think of update/delete with the new construct (was not in my use=20
case)... :-(
So maybe I should rename it something like SRawServerUpdate and give it=20
a mode (INSERT, UPDATE, DELETE). Or create different objects... Or just=20
leave it as is until someone needs it...

What's your feeling ?

Regards,
Franck



Le 05/03/2013 00:13, anthony berglas a =E9crit :
>
> Cool.  Good that you called it raw.  I suppose multi-row updates and=20
> deletes would be next on this agenda.
>
>
> I had intended to leave that out of SimpleOrm as you know, but I can=20
> see it is nice to use the OO fields to build the queries.
>
> Anthony
>
> On Mon, Mar 4, 2013 at 8:43 PM, Franck Routier=20
> <[email protected] <mailto:[email protected]>> wrote:
>
>     Hi all,
>
>     I have added a new SInsertSelect construct in SimpleORM.
>
>     This is a low level tool to generate insert into ... select ...
>     style sql based on Metas and SQueryTransient.
>     This has the advantage of:
>     - using object oriented SRecordMeta and SFieldScalar to build your
>     query
>     - delegating the work to the database, which will be the most
>     efficient at this task
>
>     But keep in mind this is a low level trick, closer to jdbc than to
>     an ORM tool.
>     In particular:
>     - this will bypass any validation done by
>     SRecordInstance.onValidateRecord().
>
>
>     So that said, here is how it works... Lets say you have a sales
>     order table, and a stats tables.
>     You can do the following:
>
>     SQueryTransient qryTrs =3D new SQueryTransient(new
>     SQuery<Order>(Order.meta));
>     qryTrs.groupBy(Order.productId).groupBy(Order.month).avg(Order.amount=
).selectRaw("nextval('mysequence')");
>
>     SInsertSelect<Stats> insSel =3D new SInsertSelect(Stats.meta, qryTrs)=
;
>     insSel.addField(Stats.productId).addField(Stats.month).addField(Stats=
.averageOrder).addField(Stats.id);
>
>     session.begin();
>     session.rawInsertSelectNoFlush(insSel);
>     session.commit();
>
>
>     Notice the method is called rawInsertSelectNoFlush to reflect the
>     fact that this is a low level construct.
>     Also notice that the sequence is added using a selectRaw method,
>     and is database dependant (the example will work with Postgresql).
>
>     Hope this can be useful. Any comment is welcome.
>
>     Franck
>
>
>
>
> --=20
>
> Dr Anthony Berglas, [email protected] <mailto:[email protected]>=20
> Mobile: +61 4 4838 8874
> Just because it is possible to push twigs along the ground with ones nose
> does not necessarily mean that that is the best way to collect firewood.
>
>=20


--------------000102050705030900050604
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<html>
  <head>
    <meta content=3D"text/html; charset=3DISO-8859-1"
      http-equiv=3D"Content-Type">
  </head>
  <body bgcolor=3D"#FFFFFF" text=3D"#000000">
    <div class=3D"moz-cite-prefix">Hi Anthony,<br>
      <br>
      I am very conscious this is the very limit of what an ORM should
      do. And I know we want to keep SimpleORM simple :-)<br>
      But including the feature into the library made it possible to
      achieve it with very few lines of code...<br>
      <br>
      Another possibility would be to factor out all the query
      generation part of SimpleORM into a separate package, but I didn't
      want to break everything.<br>
      I also still have in mind that if we take that direction, we will
      want to include some missing functionalities, like more complex
      query conditions :<br>
      - OR<br>
      - parenthesis<br>
      - maybe SFieldReference based on non equi-join<br>
      The latter can be very convinient when using nested-sets based
      hierarchies (<a class=3D"moz-txt-link-freetext" href=3D"http://en.wik=
ipedia.org/wiki/Nested_set_model">http://en.wikipedia.org/wiki/Nested_set_m=
odel</a>) to
      avoid recursivity and handle hierarchies in sql without relying on
      proprietary extensions like Oracle's connect by. Currently I use
      SQuery.rawJoin to handle this kind of hierarchies, which we use a
      lot in our app...<br>
      <br>
      That said, I realize I've been a bit shortsighted, as I just
      didn't think of update/delete with the new construct (was not in
      my use case)... :-(<br>
      So maybe I should rename it something like SRawServerUpdate and
      give it a mode (INSERT, UPDATE, DELETE). Or create different
      objects... Or just leave it as is until someone needs it...<br>
      <br>
      What's your feeling ?<br>
      <br>
      Regards,<br>
      Franck<br>
      <br>
      <br>
      <br>
      Le 05/03/2013 00:13, anthony berglas a &eacute;crit&nbsp;:<br>
    </div>
    <blockquote
cite=3D"mid:[email protected].=
com"
      type=3D"cite">
      <span style=3D"display:none">&nbsp;</span>
=20=20=20=20=20=20
          <div id=3D"ygrp-text">
            <p>Cool. &nbsp;Good that you called it raw. &nbsp;I suppose mul=
ti-row
              updates and deletes would be next on this agenda.</p>
            <div><br>
            </div>
            <div>I had intended to leave that out of SimpleOrm as you
              know, but I can see it is nice to use the OO fields to
              build the queries.</div>
            <div><br>
            </div>
            <div>Anthony<br>
              <br>
              <div class=3D"gmail_quote">On Mon, Mar 4, 2013 at 8:43 PM,
                Franck Routier <span dir=3D"ltr">&lt;<a
                    moz-do-not-send=3D"true"
                    href=3D"mailto:[email protected]"
                    target=3D"_blank">[email protected]</a>&gt;</spa=
n>
                wrote:<br>
                <blockquote class=3D"gmail_quote" style=3D"border-left:1px
                  #ccc solid;">Hi all,<br>
                  <br>
                  I have added a new SInsertSelect construct in
                  SimpleORM.<br>
                  <br>
                  This is a low level tool to generate insert into ...
                  select ... style sql based on Metas and
                  SQueryTransient.<br>
                  This has the advantage of:<br>
                  - using object oriented SRecordMeta and SFieldScalar
                  to build your query<br>
                  - delegating the work to the database, which will be
                  the most efficient at this task<br>
                  <br>
                  But keep in mind this is a low level trick, closer to
                  jdbc than to an ORM tool.<br>
                  In particular:<br>
                  - this will bypass any validation done by
                  SRecordInstance.onValidateRecord().<br>
                  <br>
                  <br>
                  So that said, here is how it works... Lets say you
                  have a sales order table, and a stats tables.<br>
                  You can do the following:<br>
                  <br>
                  SQueryTransient qryTrs =3D new SQueryTransient(new
                  SQuery&lt;Order&gt;(Order.meta));<br>
                  qryTrs.groupBy(Order.productId).groupBy(Order.month).avg(=
Order.amount).selectRaw("nextval('mysequence')");<br>
                  <br>
                  SInsertSelect&lt;Stats&gt; insSel =3D new
                  SInsertSelect(Stats.meta, qryTrs);<br>
                  insSel.addField(Stats.productId).addField(Stats.month).ad=
dField(Stats.averageOrder).addField(Stats.id);<br>
                  <br>
                  session.begin();<br>
                  session.rawInsertSelectNoFlush(insSel);<br>
                  session.commit();<br>
                  <br>
                  <br>
                  Notice the method is called rawInsertSelectNoFlush to
                  reflect the fact that this is a low level construct.<br>
                  Also notice that the sequence is added using a
                  selectRaw method, and is database dependant (the
                  example will work with Postgresql).<br>
                  <br>
                  Hope this can be useful. Any comment is welcome.<span
                    class=3D"HOEnZb"><font color=3D"#888888"><br>
                      <br>
                      Franck<br>
                      <br>
                    </font></span></blockquote>
              </div>
              <br>
              <br clear=3D"all">
              <div><br>
              </div>
              -- <br>
              <p>
              </p>
              <p>Dr Anthony Berglas, <a moz-do-not-send=3D"true"
                  href=3D"mailto:[email protected]" target=3D"_blank">ant=
[email protected]</a>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                Mobile: +61 4 4838 8874<br>
                Just because it is possible to push twigs along the
                ground with ones nose<br>
                does not necessarily mean that that is the best way to
                collect firewood.</p>
            </div>
          </div>
=20=20=20=20=20=20=20=20=20=20
=20=20=20=20=20=20
      <!-- end group email -->
    </blockquote>
    <br>
  </body>
</html>

--------------000102050705030900050604--

--------------ms040900050403020109020908
Content-Type: application/pkcs7-signature; name="smime.p7s"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="smime.p7s"
Content-Description: Signature cryptographique S/MIME

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIINYjCC
BjQwggQcoAMCAQICAR4wDQYJKoZIhvcNAQEFBQAwfTELMAkGA1UEBhMCSUwxFjAUBgNVBAoT
DVN0YXJ0Q29tIEx0ZC4xKzApBgNVBAsTIlNlY3VyZSBEaWdpdGFsIENlcnRpZmljYXRlIFNp
Z25pbmcxKTAnBgNVBAMTIFN0YXJ0Q29tIENlcnRpZmljYXRpb24gQXV0aG9yaXR5MB4XDTA3
MTAyNDIxMDE1NVoXDTE3MTAyNDIxMDE1NVowgYwxCzAJBgNVBAYTAklMMRYwFAYDVQQKEw1T
dGFydENvbSBMdGQuMSswKQYDVQQLEyJTZWN1cmUgRGlnaXRhbCBDZXJ0aWZpY2F0ZSBTaWdu
aW5nMTgwNgYDVQQDEy9TdGFydENvbSBDbGFzcyAxIFByaW1hcnkgSW50ZXJtZWRpYXRlIENs
aWVudCBDQTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMcJg8zOLdgasSmkLhOr
lr6KMoOMpohBllVHrdRvEg/q6r8jR+EK75xCGhR8ToREoqe7zM9/UnC6TS2y9UKTpT1v7RSM
zR0t6ndl0TWBuUr/UXBhPk+Kmy7bI4yW4urC+y7P3/1/X7U8ocb8VpH/Clt+4iq7nirMcNh6
qJR+xjOhV+VHzQMALuGYn5KZmc1NbJQYclsGkDxDz2UbFqE2+6vIZoL+jb9x4Pa5gNf1TwSD
kOkikZB1xtB4ZqtXThaABSONdfmv/Z1pua3FYxnCFmdr/+N2JLKutIxMYqQOJebr/f/h5t95
m4JgrM3Y/w7YX9d7YAL9jvN4SydHsU6n65cCAwEAAaOCAa0wggGpMA8GA1UdEwEB/wQFMAMB
Af8wDgYDVR0PAQH/BAQDAgEGMB0GA1UdDgQWBBRTcu2SnODaywFcfH6WNU7y1LhRgjAfBgNV
HSMEGDAWgBROC+8apEBbpRdphzDKNGhD0EGu8jBmBggrBgEFBQcBAQRaMFgwJwYIKwYBBQUH
MAGGG2h0dHA6Ly9vY3NwLnN0YXJ0c3NsLmNvbS9jYTAtBggrBgEFBQcwAoYhaHR0cDovL3d3
dy5zdGFydHNzbC5jb20vc2ZzY2EuY3J0MFsGA1UdHwRUMFIwJ6AloCOGIWh0dHA6Ly93d3cu
c3RhcnRzc2wuY29tL3Nmc2NhLmNybDAnoCWgI4YhaHR0cDovL2NybC5zdGFydHNzbC5jb20v
c2ZzY2EuY3JsMIGABgNVHSAEeTB3MHUGCysGAQQBgbU3AQIBMGYwLgYIKwYBBQUHAgEWImh0
dHA6Ly93d3cuc3RhcnRzc2wuY29tL3BvbGljeS5wZGYwNAYIKwYBBQUHAgEWKGh0dHA6Ly93
d3cuc3RhcnRzc2wuY29tL2ludGVybWVkaWF0ZS5wZGYwDQYJKoZIhvcNAQEFBQADggIBAAqD
CH14qywGXLhjjF6uHLkjd02hcdh9hrw+VUsv+q1eeQWB21jWj3kJ96AUlPCoEGZ/ynJNScWy
6QMVQjbbMXltUfO4n4bGGdKo3awPWp61tjAFgraLJgDk+DsSvUD6EowjMTNx25GQgyYJ5RPI
zKKR9tQW8gGK+2+RHxkUCTbYFnL6kl8Ch507rUdPPipJ9CgJFws3kDS3gOS5WFMxcjO5DwKf
KSETEPrHh7p5shuuNktvsv6hxHTLhiMKX893gxdT3XLS9OKmCv87vkINQcNEcIIoFWbP9HOR
z9v3vQwR4e3ksLc2JZOAFK+ssS5XMEoznzpihEP0PLc4dCBYjbvSD7kxgDwZ+Aj8Q9PkbvE9
sIPP7ON0fz095HdThKjiVJe6vofq+n6b1NBc8XdrQvBmunwxD5nvtTW4vtN6VY7mUCmxsCie
uoBJ9OlqmsVWQvifIYf40dJPZkk9YgGTzWLpXDSfLSplbY2LL9C9U0ptvjcDjefLTvqSFc7t
w1sEhF0n/qpA2r0GpvkLRDmcSwVyPvmjFBGqUp/pNy8ZuPGQmHwFi2/14+xeSUDG2bwnsYJQ
G2EdJCB6luQ57GEnTA/yKZSTKI8dDQa8Sd3zfXb19mOgSF0bBdXbuKhEpuP9wirslFe6fQ1t
5j5R0xi72MZ8ikMu1RQZKCyDbMwazlHiMIIHJjCCBg6gAwIBAgIDBNFnMA0GCSqGSIb3DQEB
BQUAMIGMMQswCQYDVQQGEwJJTDEWMBQGA1UEChMNU3RhcnRDb20gTHRkLjErMCkGA1UECxMi
U2VjdXJlIERpZ2l0YWwgQ2VydGlmaWNhdGUgU2lnbmluZzE4MDYGA1UEAxMvU3RhcnRDb20g
Q2xhc3MgMSBQcmltYXJ5IEludGVybWVkaWF0ZSBDbGllbnQgQ0EwHhcNMTIwODI5MjAxNTIz
WhcNMTMwODMxMDg0MTExWjBnMRkwFwYDVQQNExBmQ0cxTUdlTUM1dkZVNVlKMSEwHwYDVQQD
DBhmcmFuY2sucm91dGllckBheGVnZS5jb20xJzAlBgkqhkiG9w0BCQEWGGZyYW5jay5yb3V0
aWVyQGF4ZWdlLmNvbTCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAK5kIRjbP769
72PM00wgFczTGqmiU5dAY8XlSziUNq434SJZ3rFZoL0MY6hqVb/p5+u+u6TdhsVywJclPaZE
v+FgGtk74JvlX6oeSWJTOiGLTrk0TRwy7Q+80kbneFdhL+yk/ZTxxcj15IEOeWMuyG/IyePq
H4GtZMkCpsuCOL5bkq9g/H0JHyLSM0tWDbMuD4vKbSeBkzwiz4pSwuANCFMFeRr3d9iZQU0P
VzyKvu/cEUcdoDcVGMuMxX6FwgqrS5c70v/RPM8FafOrqprYfIxVQwxK9IhPgqM9kT6riQo6
OM62d2a21Y6iAeWe/1rssTKn+Stj8jH706LmVJlEGtcCAwEAAaOCA7MwggOvMAkGA1UdEwQC
MAAwCwYDVR0PBAQDAgSwMB0GA1UdJQQWMBQGCCsGAQUFBwMCBggrBgEFBQcDBDAdBgNVHQ4E
FgQUCfIkqGdMRvMTjM6TIpdcUPqdYW8wHwYDVR0jBBgwFoAUU3Ltkpzg2ssBXHx+ljVO8tS4
UYIwIwYDVR0RBBwwGoEYZnJhbmNrLnJvdXRpZXJAYXhlZ2UuY29tMIICIQYDVR0gBIICGDCC
AhQwggIQBgsrBgEEAYG1NwECAjCCAf8wLgYIKwYBBQUHAgEWImh0dHA6Ly93d3cuc3RhcnRz
c2wuY29tL3BvbGljeS5wZGYwNAYIKwYBBQUHAgEWKGh0dHA6Ly93d3cuc3RhcnRzc2wuY29t
L2ludGVybWVkaWF0ZS5wZGYwgfcGCCsGAQUFBwICMIHqMCcWIFN0YXJ0Q29tIENlcnRpZmlj
YXRpb24gQXV0aG9yaXR5MAMCAQEagb5UaGlzIGNlcnRpZmljYXRlIHdhcyBpc3N1ZWQgYWNj
b3JkaW5nIHRvIHRoZSBDbGFzcyAxIFZhbGlkYXRpb24gcmVxdWlyZW1lbnRzIG9mIHRoZSBT
dGFydENvbSBDQSBwb2xpY3ksIHJlbGlhbmNlIG9ubHkgZm9yIHRoZSBpbnRlbmRlZCBwdXJw
b3NlIGluIGNvbXBsaWFuY2Ugb2YgdGhlIHJlbHlpbmcgcGFydHkgb2JsaWdhdGlvbnMuMIGc
BggrBgEFBQcCAjCBjzAnFiBTdGFydENvbSBDZXJ0aWZpY2F0aW9uIEF1dGhvcml0eTADAgEC
GmRMaWFiaWxpdHkgYW5kIHdhcnJhbnRpZXMgYXJlIGxpbWl0ZWQhIFNlZSBzZWN0aW9uICJM
ZWdhbCBhbmQgTGltaXRhdGlvbnMiIG9mIHRoZSBTdGFydENvbSBDQSBwb2xpY3kuMDYGA1Ud
HwQvMC0wK6ApoCeGJWh0dHA6Ly9jcmwuc3RhcnRzc2wuY29tL2NydHUxLWNybC5jcmwwgY4G
CCsGAQUFBwEBBIGBMH8wOQYIKwYBBQUHMAGGLWh0dHA6Ly9vY3NwLnN0YXJ0c3NsLmNvbS9z
dWIvY2xhc3MxL2NsaWVudC9jYTBCBggrBgEFBQcwAoY2aHR0cDovL2FpYS5zdGFydHNzbC5j
b20vY2VydHMvc3ViLmNsYXNzMS5jbGllbnQuY2EuY3J0MCMGA1UdEgQcMBqGGGh0dHA6Ly93
d3cuc3RhcnRzc2wuY29tLzANBgkqhkiG9w0BAQUFAAOCAQEAGSGlokCrFniLxjc6W072LqWy
ISRKEqeC9u0pYSoQGVPAy1mNbu28ThrfAk8umOfFEDkzVH0TXH33TbsCViwsE/rCk6FC0U+Q
AMOn1Ajjf/9z7w8+zCHgmbLyABtYrmBO6AhCD7YLKYPE1VXkJvgdcHCKzVLIkvdIyKSkRn+R
lSorTXhg64lR8vQSX8C9EAY0T/hQyjFS9SDcEhsfOjkYdAiG7fVBft24Gj+3oDgk2479W47l
phG1aHhkflrDeho0h6LnGxY0+PATPuUkvDsr/RFE2VviGFH9UDxjNJmikrmTibCvS4seQFOm
ppHmJzqIcxcn5dqeBDVQYZmLhDkszjGCA90wggPZAgEBMIGUMIGMMQswCQYDVQQGEwJJTDEW
MBQGA1UEChMNU3RhcnRDb20gTHRkLjErMCkGA1UECxMiU2VjdXJlIERpZ2l0YWwgQ2VydGlm
aWNhdGUgU2lnbmluZzE4MDYGA1UEAxMvU3RhcnRDb20gQ2xhc3MgMSBQcmltYXJ5IEludGVy
bWVkaWF0ZSBDbGllbnQgQ0ECAwTRZzAJBgUrDgMCGgUAoIICHTAYBgkqhkiG9w0BCQMxCwYJ
KoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0xMzAzMDUwODI5MDVaMCMGCSqGSIb3DQEJBDEW
BBSpShfomfplqUqnxz4t9aZ/gCdzGjBsBgkqhkiG9w0BCQ8xXzBdMAsGCWCGSAFlAwQBKjAL
BglghkgBZQMEAQIwCgYIKoZIhvcNAwcwDgYIKoZIhvcNAwICAgCAMA0GCCqGSIb3DQMCAgFA
MAcGBSsOAwIHMA0GCCqGSIb3DQMCAgEoMIGlBgkrBgEEAYI3EAQxgZcwgZQwgYwxCzAJBgNV
BAYTAklMMRYwFAYDVQQKEw1TdGFydENvbSBMdGQuMSswKQYDVQQLEyJTZWN1cmUgRGlnaXRh
bCBDZXJ0aWZpY2F0ZSBTaWduaW5nMTgwNgYDVQQDEy9TdGFydENvbSBDbGFzcyAxIFByaW1h
cnkgSW50ZXJtZWRpYXRlIENsaWVudCBDQQIDBNFnMIGnBgsqhkiG9w0BCRACCzGBl6CBlDCB
jDELMAkGA1UEBhMCSUwxFjAUBgNVBAoTDVN0YXJ0Q29tIEx0ZC4xKzApBgNVBAsTIlNlY3Vy
ZSBEaWdpdGFsIENlcnRpZmljYXRlIFNpZ25pbmcxODA2BgNVBAMTL1N0YXJ0Q29tIENsYXNz
IDEgUHJpbWFyeSBJbnRlcm1lZGlhdGUgQ2xpZW50IENBAgME0WcwDQYJKoZIhvcNAQEBBQAE
ggEADJ1fUE/8OMh1gYu3qd2u3RXUHRXsoV0pvCQlY1pzrnrqn/SVLF5KPMvorDEL0n9UpvjR
XVLPCnrsAk1Q6eIIu/HCYxQn+rUD0syXOOlPtgswCuasWwu/3Gze0su25qre/J5iaTeJhSLr
ILoBDb4LAIU7rK6U6AotgjosO4imJWefBDSQqiiOnderl5/ekYQ/gTXeYdzHy3+0xsYFstwq
+VRiyYWaQ34maDSNXFlly/M5GodcTFbsUkqV8hnkQ37B4KTpOHZUH7MITjfSzUHUlw2m4EtK
CsbxrZc3735/heoxxN3sPxpL1RvfscM39Opm2sClt9hvJcpOuf+7vRoS2AAAAAAAAA==
--------------ms040900050403020109020908--