Re: Insert/select...

--e89a8fb1f620c50dbb04d7362968
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Hello Franck,

I think that the important thing is that it be practically useful.  So what
you have done is good.  Update and Delete should follow the same pattern,
so it might be nice to add them while you are at it, but not important.
 Not sure about name.

OR really requires parenthesis, and they are messy.  There are two
approaches, one is to add .leftParen(), .rightParen() etc. and the other is
to do it properly building up an expression tree of objects.  The trouble
with the latter is it greatly complicates the simple, common case.
 Non-equijoin references would take it to a whole new and rather scary
level.

Incidentally, I think that even Oracle now supports the Datalog style
recursive querys (WITH ... UNION).  So almost a real standard.

Regards,

Anthony



On Tue, Mar 5, 2013 at 6:29 PM, Franck Routier <[email protected]>wr=
ote:

>  Hi Anthony,
>
> I am very conscious this is the very limit of what an ORM should do. And =
I
> know we want to keep SimpleORM simple :-)
> But including the feature into the library made it possible to achieve it
> with very few lines of code...
>
> 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 everythi=
ng.
> 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 =
:
> - OR
> - parenthesis
> - maybe SFieldReference based on non equi-join
> The latter can be very convinient when using nested-sets based hierarchie=
s
> (http://en.wikipedia.org/wiki/Nested_set_model) 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 o=
f
> hierarchies, which we use a lot in our app...
>
> 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)... :-(
> So maybe I should rename it something like SRawServerUpdate and give it a
> mode (INSERT, UPDATE, DELETE). Or create different objects... Or just lea=
ve
> 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
> deletes would be next on this agenda.
>
>  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.
>
>  Anthony
>
> On Mon, Mar 4, 2013 at 8:43 PM, Franck Routier <[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 sq=
l
>> 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 OR=
M
>> 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).s=
electRaw("nextval('mysequence')");
>>
>> SInsertSelect<Stats> insSel =3D new SInsertSelect(Stats.meta, qryTrs);
>>
>> insSel.addField(Stats.productId).addField(Stats.month).addField(Stats.av=
erageOrder).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
>>
>>
>
>
>  --
>
>  Dr Anthony Berglas, [email protected]       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=20
>
>
>


--=20

Dr Anthony Berglas, [email protected]       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.

--e89a8fb1f620c50dbb04d7362968
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: quoted-printable





<head>

<style type=3D"text/css">
<!--

/* start of attachment style */
       .ygrp-photo-title{
         clear: both;
         font-size: smaller;
         height: 15px;
         overflow: hidden;
         text-align: center;
         width: 75px;
       }
       div.ygrp-photo{
         background-position: center;
         background-repeat: no-repeat;
         background-color: white;
         border: 1px solid black;
         height: 62px;
         width: 62px;
       }

       div.photo-title=20
         a,
         div.photo-title a:active,
         div.photo-title a:hover,
         div.photo-title a:visited {
           text-decoration: none;=20
       }

       div.attach-table div.attach-row {
         clear: both;
       }

       div.attach-table div.attach-row div {
         float: left;
         /* margin: 2px;*/
       }

       p {
         clear: both;
         padding: 15px 0 3px 0;
	 overflow: hidden;
       }

       div.ygrp-file {
         width: 30px;
         valign: middle;
       }
       div.attach-table div.attach-row div div a {
         text-decoration: none;
       }

       div.attach-table div.attach-row div div span {
         font-weight: normal;
       }

       div.ygrp-file-title {
         font-weight: bold;
       }
 /* end of attachment style */
        -->
        </style>
        </head>
<html>
<head>
<style type=3D"text/css">
<!--
#ygrp-mkp {
  border: 1px solid #d8d8d8;
  font-family: Arial;
  margin: 10px 0;
  padding: 0 10px;
}

#ygrp-mkp hr {
  border: 1px solid #d8d8d8;
}

#ygrp-mkp #hd {
  color: #628c2a;
  font-size: 85%;
  font-weight: 700;
  line-height: 122%;
  margin: 10px 0;
}

#ygrp-mkp #ads {
  margin-bottom: 10px;
}

#ygrp-mkp .ad {
  padding: 0 0;
}

#ygrp-mkp .ad p {
  margin: 0;
}

#ygrp-mkp .ad a {
  color: #0000ff;
  text-decoration: none;
}
-->
</style>
</head>
<body>





Hello Franck,<div><br></div><div>I think that the important thing is that i=
t be practically useful. =A0So what you have done is good. =A0Update and De=
lete should follow the same pattern, so it might be nice to add them while =
you are at it, but not important. =A0Not sure about name.</div>

<div><br></div><div>OR really requires parenthesis, and they are messy. =A0=
There are two approaches, one is to add .leftParen(), .rightParen() etc. an=
d the other is to do it properly building up an expression tree of objects.=
 =A0The trouble with the latter is it greatly complicates the simple, commo=
n case. =A0Non-equijoin references would take it to a whole new and rather =
scary level.</div>

<div><br></div><div>Incidentally, I think that even Oracle now supports the=
 Datalog style recursive querys (WITH ... UNION). =A0So almost a real stand=
ard.</div><div><br></div><div>Regards,</div><div><br></div><div>Anthony</di=
v>

<div><br></div><div><br><br><div class=3D"gmail_quote">On Tue, Mar 5, 2013 =
at 6:29 PM, Franck Routier <span dir=3D"ltr">&lt;<a href=3D"mailto:franck.r=
[email protected]" target=3D"_blank">[email protected]</a>&gt;</span>=
 wrote:<br>

<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex">
=20=20
=20=20=20=20
=20=20
  <div bgcolor=3D"#FFFFFF" text=3D"#000000">
    <div>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&#39;=
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 href=3D"http://en.wikipedia.org/wiki/Nested_set_model=
" target=3D"_blank">http://en.wikipedia.org/wiki/Nested_set_model</a>) to
      avoid recursivity and handle hierarchies in sql without relying on
      proprietary extensions like Oracle&#39;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&#39;ve been a bit shortsighted, as I just
      didn&#39;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&#39;s your feeling ?<br>
      <br>
      Regards,<br>
      Franck<br>
      <br>
      <br>
      <br>
      Le 05/03/2013 00:13, anthony berglas a =E9crit=A0:<br>
    </div>
    <blockquote type=3D"cite">
      <span>=A0</span>
=20=20=20=20=20=20
      <div>
        <div><div><div class=3D"h5">
=20=20=20=20=20=20=20=20=20=20
          <div>
            <p>Cool. =A0Good that you called it raw. =A0I suppose multi-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 href=3D"mailto:fran=
[email protected]" target=3D"_blank">[email protected]</a>&gt;</s=
pan>
                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(&quot;nextval(&#39;mysequence&#39;)&quot;);<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><fo=
nt 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 href=3D"mailto:[email protected]"=
 target=3D"_blank">[email protected]</a>=A0=A0=A0=A0=A0=A0
                Mobile: <a href=3D"tel:%2B61%204%204838%208874" value=3D"+6=
1448388874" target=3D"_blank">+61 4 4838 8874</a><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
          </div></div><div style=3D"color:#fff;min-height:0"></div>
=20=20=20=20=20=20
=20=20=20=20=20=20
=20=20=20=20=20=20
=20=20=20=20=20=20
=20=20=20=20=20=20
    </blockquote>
    <br>
  </div>

</blockquote></div><br><br clear=3D"all"><div><br></div>-- <br><p>
</p><p>Dr Anthony Berglas, <a href=3D"mailto:[email protected]" target=3D=
"_blank">[email protected]</a>=A0=A0=A0=A0=A0=A0 Mobile: +61 4 4838 8874<=
br>
Just because it is possible to push twigs along the ground with ones nose<b=
r>
does not necessarily mean that that is the best way to collect firewood.</p=
>
</div>




<!-- |**|begin egp html banner|**| -->

<br>

=20=20=20=20
=20=20=20=20
<br>

<!-- |**|end egp html banner|**| -->


<div width=3D"1" style=3D"color: white; clear: both;"/>__._,_.___</div>

<!-- Start Recommendations -->
<!-- End Recommendations -->



<!-- |**|begin egp html banner|**| -->

  <img src=3D"http://geo.yahoo.com/serv?s=3D97476590/grpId=3D7360226/grpspI=
d=3D1705006905/msgId=3D2085/stime=3D1362527538" width=3D"1" height=3D"1"> <=
br>

<!-- |**|end egp html banner|**| -->

=20=20
<!-- |**|begin egp html banner|**| -->

<br>
      <div style=3D"font-family: verdana; font-size: 77%; border-top: 1px s=
olid #666; padding: 5px 0;" >
      Your email settings: Individual Email|Traditional <br>
      <a href=3D"http://groups.yahoo.com/group/SimpleORM/join;_ylc=3DX3oDMT=
JmOXF0NzkxBF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY=
wNmdHIEc2xrA3N0bmdzBHN0aW1lAzEzNjI1Mjc1Mzg-">Change settings via the Web</a=
> (Yahoo! ID required) <br>
      Change settings via email: <a href=3D"mailto:SimpleORM-digest@yahoogr=
oups.com?subject=3DEmail Delivery: Digest">Switch delivery to Daily Digest<=
/a> | <a href =3D "mailto:[email protected]?subject=3D=
Change Delivery Format: Fully Featured">Switch to Fully Featured</a> <br>
           <a href=3D"http://groups.yahoo.com/group/SimpleORM;_ylc=3DX3oDMT=
JkOHE1bjJmBF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY=
wNmdHIEc2xrA2hwZgRzdGltZQMxMzYyNTI3NTM4">
        Visit Your Group=20
      </a> |
      <a href=3D"http://docs.yahoo.com/info/terms/">
        Yahoo! Groups Terms of Use
      </a> |
      <a href=3D"mailto:[email protected]?subject=3DUns=
ubscribe">
       Unsubscribe=20
      </a>=20
 <br>
    </div>
  <br>

<!-- |**|end egp html banner|**| -->


<div style=3D"color: white; clear: both;"/>__,_._,___</div>
</body>

</html>

--e89a8fb1f620c50dbb04d7362968--