<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">
<br><div><div>On Dec 7, 2007, at 8:24 PM, Mike McGonagle wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><br><br><div class="gmail_quote">On Dec 7, 2007 6:44 PM, Hans-Christoph Steiner <<a href="mailto:hans@eds.org">hans@eds.org</a>> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"> <div style="word-wrap:break-word"> <div><br></div><div>Since both the current Pd-SQL devs (Mike and Jamie :) have posted to this thread, I want to bring up the interface again. What do you guys think about the two inlet interface for SQL objects? I just find the sql/sqlend thing very strange, and it could end up being difficult to work with. I see the interface like this: the hot inlet is for meta commands, like "open", "close", "export", "connect", etc. Then the second, cold inlet is for building SQL queries. Having a cold inlet devoted to building SQL queries would mean that you would not need any special "sql/sqlend" style tags at all. </div></div></blockquote><div><br class="webkit-block-placeholder"></div><div>Hans, I still think you are missing the point. The end tag 'endsql' and 'submitsql' are needed so that we can reinsert the commas that PD strips out of the messages. Without having the end tag, there would be no way to tell exactly where the SQL statement ends. </div><div><br class="webkit-block-placeholder"></div><div>And as far as it being difficult to work with, I just think of the closing tag as being a replacement for a semi-colon in the SQL. Pretty much everything else between the two tags is assumed to be PURE SQL, and gets placed into the SQL buffer. As PD splits up the messages based on where the comma's fall, we are assuming that each time the 'anything' method gets called, that we have to reinsert the comma into the SQL. The doesn't happen with the first 'sql' tag. </div><div><br class="webkit-block-placeholder"></div><div>As far as the second inlet, I can see doing that, but only for the reason that this 'syntax' is a little different than what PD normally expects. Other than that, I really don't see any reason to have a second inlet. </div><div><br class="webkit-block-placeholder"></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div style="word-wrap:break-word"><div></div><div><br></div><div> Instead, you'd just send messages to the cold inlet, those messages would be appended to the existing SQL message buffer. Then when the query is ready to be sent, send a "submit" or "run" or whatever to the hot inlet, and it would run the buffer from the cold inlet and clear it. There would also be a "clear" command to just clear the buffer without sending it. </div></div></blockquote><div><br class="webkit-block-placeholder"></div><div>So, then, how would we allow the ability to insert multiple SQL statements into a single buffer? The point of having the sql/endsql tag pairs is to delimit a single statement. I don't think that Jamie's external will allow multiple statements, I could be wrong, but I think it would be very desirable to have the ability to build an SQL buffer, for instance to do multiple 'inserts'. I have also worked with the idea of being able to insert arbitrary text into the buffer, so you can build dynamic SQL statements (for instance, adding [or leaving out] a 'where' clause, or a 'limit/offset' clause). </div><div><br class="webkit-block-placeholder"></div><div>If we do it the way you are asking for, there would be no way to insert more than a single statement into the SQL buffer.</div></div></blockquote><div><br class="webkit-block-placeholder"></div><div>The cold inlet would be an append operation, so to build up the message, you would just keep sending chunks to the cold inlet, then when the whole thing is ready, send [submit( to the hot inlet.</div><br><blockquote type="cite"><div class="gmail_quote"><div><br class="webkit-block-placeholder"> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div style="word-wrap:break-word"><div></div><div><br></div><div>This would allow for the comma trick, etc. and I think it would fit better into Pd's message processing. In addition, there could be a single line message for the hot inlet, like this: </div><div><br></div><div>"submit insert into mine (name) values ('the_text')" </div><div><br></div><div>I'll help implement this if you want. I just have a strong aversion to the "sqlend" stuff since I think it could end up being strange to use within Pd. Plus I think these are very valuable objects to have, so I think it's important to get the interface right. Basically, I am thinking of something along the lines of the [textfile] interface, as much as possible. Here's a sketch: </div></div></blockquote><div><br class="webkit-block-placeholder"></div><div>Why is it strange? We can't use the characters ',' and ';' due to PD, so we have to come up with some other way of dealing with them. </div><div><br class="webkit-block-placeholder"></div><div>Also, how would you handle something like this... (using your method)</div><div><br class="webkit-block-placeholder"></div><div>"submit insert into mine (id, name, something) values (1, 'santa', 'more stuff')" </div><div><br class="webkit-block-placeholder"></div><div>How would your external know when to actually submit the SQL buffer to the database, as this is actually a series of 5 PD messages being sent to the external?</div> <div><br class="webkit-block-placeholder"></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div style="word-wrap:break-word"><div></div><div><br></div><div>__hot inlet__ </div><div>[open $1(</div><div style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="Helvetica" size="3" style="font:12.0px Helvetica">[close(</font></div><div style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"> <font face="Helvetica" size="3" style="font:12.0px Helvetica">[export $1( - this could be used to export the database to a .sql file</font></div><div><br></div><div>[clear (</div><div>[submit (</div><div><div style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"> <font face="Helvetica" size="3" style="font:12.0px Helvetica">[submit insert into mytable (name) values ('$1') (</font></div><div style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><br></div> </div><div><br></div><div>__cold inlet__</div><div><div style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px"><font face="Helvetica" size="3" style="font:12.0px Helvetica">[insert into mytable (name) values ('$1') ( </font></div></div><div>[CREATE TABLE datatable(id INTEGER, duration FLOAT, type VARCHAR, datetime DATETIME) (</div><div>[SELECT id, ABS((duration - 1500)/1500) AS error FROM datatable ORDER BY error LIMIT 1(</div></div> </blockquote><div><br class="webkit-block-placeholder"></div><div>And the cold inlet would actually get these PD message...</div><div><br class="webkit-block-placeholder"></div><div>insert into mytable (name) values ('$1') </div><div>CREATE TABLE datatable(id INTEGER</div><div>duration FLOAT</div><div>type VARCHAR</div><div>datetime DATETIME</div><div>SELECT id</div><div>ABS((duration - 1500)/1500 AS error FROM datatable ODER BY ERROR LIMIT 1 </div><div><br class="webkit-block-placeholder"></div><div>Exactly how would the external know when to terminate an SQL statement and insert a semi-colon? There really is nothing there to indicate that there is more than one SQL statement.</div></div></blockquote><div><br class="webkit-block-placeholder"></div><div>On the cold inlet, everything would just be appended to the binbuf. Then when a [submit( is sent on the hot inlet, the whole binbuf would be dumped to the database with a semi-colon appended. So basically, you tell it where the semi-colon is. The [clear( message to the hot inlet would set the binbuf to blank.</div><div><br class="webkit-block-placeholder"></div><div>The above examples are meant to be a list of example messages, not necessarily execution order. Sorry, I wasn't more clear. The example patch should be much clearer.</div><br><blockquote type="cite"><div class="gmail_quote"><div> </div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div style="word-wrap:break-word"><div></div><div><br></div><div>Also, on a different note, I think it could be useful to have various status and meta data queries output on the second inlet. Currently in [psql], there is just a bang on complete. But there could be things information about the database that aren't available via SQL. </div></div></blockquote><div><br class="webkit-block-placeholder"></div><div>SQLite already does this. Any messages coming back from the database are output as a list on the second outlet. There are also symbols that are output, more or less as a good indication (indicating that a statement processed, for things that don't return a result set). A bang on the second outlet indicates that the current result set has been exhausted.</div></div></blockquote><div><br></div><blockquote type="cite"><div class="gmail_quote"><div> </div><div><span class="Apple-style-span" style="-webkit-text-stroke-width: -1; ">One other thing that I have thought about is other database layers, upon submitting an INSERT, will return the ROWID of the row that was just inserted. I was thinking that to implement this, this number could be output on the second outlet as a float.</span></div><div><br class="webkit-block-placeholder"></div><div>So this would be:</div><div><br class="webkit-block-placeholder"></div><div>bang: indicates result set done</div><div>symbol/list: status message from database</div> <div>float: ROWID of the last insert</div></div></blockquote><div><br class="webkit-block-placeholder"></div><div>You might be interested in the Max/MSP external [mysql]:</div><div><br class="webkit-block-placeholder"></div><div><a href="http://www.publicbeta.cx/maxx/">http://www.publicbeta.cx/maxx/</a></div><div><br class="webkit-block-placeholder"></div><div>it has three outlets:</div><div><br class="webkit-block-placeholder"></div><div>data - "number of affected rows" - "bang when done"</div><div><br class="webkit-block-placeholder"></div><br><blockquote type="cite"><div class="gmail_quote"><div><br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div style="word-wrap:break-word"><div></div><div><br></div><div> Finally, there is the source code for the Max/MSP mysql external, so I think it should be pretty easy to make a mysql version of this interface as well. Then we'll have a nice trio of SQL options: embedded, PGSQL, and MySQL. </div></div></blockquote><div> </div><div>Well, as far as this goes, I think we should make every effort to have the interfaces for ALL of these objects be the same, so that these databases can be swapped on the fly.</div> <div><br></div></div></blockquote><br></div><div>I agree, I think it should be possible. The network connection stuff won't be needed in [sqlite] though, so that would be different.</div><div><br class="webkit-block-placeholder"></div><div>.hc</div><div> <span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><br class="Apple-interchange-newline"><span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><div><br></div><div>----------------------------------------------------------------------------</div><div><br></div><div>Looking at things from a more basic level, you can come up with a more direct solution... It may sound small in theory, but it in practice, it can change entire economies.<span class="Apple-converted-space"> </span> - Amy Smith</div><br class="Apple-interchange-newline"></span></span> </div><br></body></html>