January 2015 - Pharo-dev - lists.pharo.org

Re: [Pharo-dev] Prepared Statements on PostgresV2?

by Levente Uzonyi

The V2 version of the protocol doesn't support prepared statements. When we were writing the PostgresV3 package, we planned to implement prepared statements (via the extended query protocol), but later we decided to not do it. Why? The protocol is way more complex than the simple query protocol (which is used for regular queries), and involves more message sends, which can increase the network latency. The security that prepared statements give is equivalent to proper escaping. That can and should be implemented for standard queries too. The performance "improvement" what prepared statements give has two sides. You save the cost of parsing and planning, but the query is executed with the same plan, no matter what the arguments are. This can lead to sub-optimal performance. With the extended query protocol you can execute one statement per query. With the simple query protocol you can execute as many as you want. This can increase the network latency significantly for prepared statements. In PostgreSQL, calling functions using the simple query protocol behave similarly to prepared statements. You don't have to pass your SQL query to the server all the time, just the ame of the function and its arguments wrapped in a very simple SQL query. This way the plan is created when the function is executed the first time (so it's even superior to prepared statements, because there's no per-connection parsing and planning cost). Functions also let you to use any procedural language PostgreSQL supports, which are currently SQL, PL/pgSQL, Pl/Tcl, PL/Perl, PL/Python. Using the high level languages makes it easier to write complex queries, or express complex logic, which wouldn't be possible with a single SQL query. So we decided to use functions instead. We added some image side tools to make it easier to write, save, load and debug functions. Currently only the PL/pgSQL language is supported by these tools, and there are some other limitations, but we'll implement new features as time permits. What about performance? Using our toolchain, a single process can execute 2500-3000 queries (using a modern CPU, and CogVM) per second, and an image can make about 5-6k queries per second overall. This includes the overhead of using the connection pool, and the generation of the textual SQL query for each function call. Without these it's possible to go above 10k/second. Text or binary? We implemented the text-based protocol, but added the hooks for the binary protocol too. Why? The binary protocol is undocumented (its documentation is the C source code), and it's subject to change with each release. The text-based protocol is a bit better documented, and it should work with all versions of PostgreSQL starting from 7.4. Levente On Sat, 10 Jan 2015, David Carlos Manuelda wrote: > I've installed and tested PostgresV2 under pharo with the following doits > > Gofer new > smalltalkhubUser: 'PharoExtras' project: 'PostgresV2'; > configuration; > load. > (#ConfigurationOfPostgresV2 asClass project version: '2.4') load > > While it works good, it is missing a very important feature from both > security and performance point of view: The prepared statements. > > As a brief, prepared statements are parameterized SQL statements that are > loaded ONCE per connection instead of sending the whole query to DB every > time, and also, they are parameterized, so it completelly prevents SQL > injection, as the parameters are automatically 'detected' and scaped and/or > handled accordingly without allowing in any case a parameter to alter the > SQL meaning, which can happen by using regular SQL queries made by string > concatenation. > > I browsed the class and did not find any prepare: method nor anything > similar. > > Also, you can give a name to a SQL sentence, which makes the code much more > readable without messing too much logic with SQL commands and string > concatenation. > > An example is as follows: (supposing we have an instance variable > connection, already initialized and connected via PGConnection class) > > Instead of: > > self connection execute: 'SELECT data FROM mytable WHERE name=''', anUser > userName, ''';'. > > Would be something like this: > self connection executePrepared: 'getUserData' with: anUser userName. > > And another suggestion could be something like: > self connection prepare: 'getUserData' withSQL: 'SELECT data FROM mytable > WHERE name=$1' > > Any plan for this to be implemented or any hint to other PostgreSQL class > that already has it? > > David. > > P.S. I've written in my blog about this some time ago: > http://stormbyte.blogspot.com.es/2012/06/programming-with-database-using.ht… if someone finds it useful. > > >

Jan. 10, 2015

Re: [Pharo-dev] PettitParser and Python

by Tudor Girba

Hi, The challenge with Python parsing is dealing with whitespace properly. Jan Kurs was working on such a parser. I do not know how advanced it is. Jan? Cheers, Doru On Fri, Jan 9, 2015 at 7:30 PM, kilon alios <kilon.alios(a)gmail.com> wrote: > Hello guys I am learning PettitParser , I dowloaded the video tutorials of > Tudor Girba (very good tutorial Tudor , well done ) and other > documentation and I was wondering if a parser for Python already exists . I > hope I am not asking something obvious. > > For my project I want to parse python types , that will be given to me as > strings , to pharo objects. So that means parsing lists, dictionaries and > other usual candidates. For example a python list is like > > '[1, 3, 50, [45, 'hello', 0.2], 63]' > > etc > > Anyone already worked on such problem ? > > > -- www.tudorgirba.com "Every thing has its own flow"

Jan. 10, 2015

Re: [Pharo-dev] Prepared Statements on PostgresV2?

by Sven Van Caekenberghe

Hi Stephan, Very interesting ! It would be good to switch from the textual V2 protocol to the binary V3, not only is it faster (or should it be faster), it has more features and it is more modern (ie. the way forward). Do you think is would make sense to separate the different layers (or maybe this is already the case) ? The code mirroring should be optional as it has much more dependencies. We should definitively talk about this during the Pharo Days ! Sven > On 10 Jan 2015, at 10:59, Stephan Eggermont <stephan(a)stack.nl> wrote: > > Stef wrote: >> May be this is the time to improve this package :) >> I'm sorry I have no DB experience. >> As part of the consortium agenda for this year we have >> - better ffi >> - better db support > > Moving to Levente's PostgreSQLv3 and migrating that > might be a better idea. He has been using it in production > systems for a long time AFAIK. > > The code mirror is very interesting. Porting it needs > someone with close access to Opal/old compiler knowledge. > The way it works now in Squeak looks ideal for someone > with a PostgreSQL background. Using a code browser > to edit plsql, and having a directly mirrored smalltalk > class for each table. > > http://forum.world.st/Status-of-PostgresV3-td4780110.html > I have verified that the connection works and that I can get > data out of postgres. > Missing: > - switch the code highlighter from smalltalk to one > for plsql in nautilus. > - connect to glorp > - (later) add a real plsql parser (petitsql?) > > Stephan > >

Jan. 10, 2015

[ANN] Updated SQLite3 wrapper project based on NB

by Torsten Bergmann

Some of you may already have noticed that it is already available but there was not yet a formal announcement, so here it is: There is a new way/project to work with the SQLite3 embeddable database. While in the past there was already a SQLite wrapper project for Pharo based on FFI this one is based on NativeBoost and also has some support for Glorp. The "NBSQLite3 for Pharo" is a project to provide an API and access to the SQLite3 database from within the Pharo image. With this tool you can use the small relational SQL engine into own Pharo projects. The project page is located on http://smalltalkhub.com/#!/~PharoExtras/NBSQLite3 More details can be found on the project page or on blog posts of PierceNg (the orginal author): http://www.samadhiweb.com/blog The project is now additionally refactored, supported with green tests, documented, split in seperate loadable packages and moved to the PharoExtras team at STHub. A Metacello configuration for Pharo 4 is ready in the Pharo 4 configuration browser. If you like you can help to test on/adopt to different platforms (at a minimum known to work on Windows) or improve it further. Have fun Pierce and T.

Jan. 10, 2015

Re: [Pharo-dev] Prepared Statements on PostgresV2?

by Alain Rastoul

Hi, Nice document, however, about ODBC support column, looking at and old (Pharo 1.3) image I have on my pc, I can see: ODBCLibray>>moduleName "Return the name of the module for this library" SmalltalkImage current platformName = 'Win32' ifTrue: [ ^ 'odbc32' ]. SmalltalkImage current platformName = 'unix' ifTrue: [ ^ 'libodbc.so' ]. ^ self error: 'Don''t know the ODBC library name' I thought odbc was supported under linux in the FFI ODBC package. odbc rocks under windows, for non dotNet apps, (my opinion), and MS has released an Mssql odbc driver for linux (They say they love linux now :) ). http://www.microsoft.com/en-us/download/details.aspx?id=28160 Oracle is also supported for 12 (I think at least since 10g) https://docs.oracle.com/database/121/ODBCR/toc.htm#ODBCR101 Could it be interesting to re-investigate ODBC support ? Or DbxTalk is the Pharo standard (and it doesn't mind) ? Regards, Alain Le 10/01/2015 11:32, stepharo a Ã©crit : > > Le 10/1/15 10:59, Stephan Eggermont a Ã©crit : >> Stef wrote: >>> May be this is the time to improve this package :) >>> I'm sorry I have no DB experience. >>> As part of the consortium agenda for this year we have >>> - better ffi >>> - better db support >> Moving to Levente's PostgreSQLv3 and migrating that >> might be a better idea. He has been using it in production >> systems for a long time AFAIK. > > when I meant db support it is not limited to one implementation. > Olivier Auverlot started to do a document listing all the solutions and > their status. > https://docs.google.com/spreadsheets/d/1uD6UFmwCJxAkOC9tlvdJsHtt10C0KHJ6Zp8… > > Add the information avout SQLv3 > > Now I do not get it. > Did you port it to Pharo? >> >> The code mirror is very interesting. Porting it needs >> someone with close access to Opal/old compiler knowledge. >> The way it works now in Squeak looks ideal for someone >> with a PostgreSQL background. Using a code browser >> to edit plsql, and having a directly mirrored smalltalk >> class for each table. >> >> http://forum.world.st/Status-of-PostgresV3-td4780110.html >> I have verified that the connection works and that I can get >> data out of postgres. >> Missing: >> - switch the code highlighter from smalltalk to one >> for plsql in nautilus. >> - connect to glorp >> - (later) add a real plsql parser (petitsql?) >> >> Stephan >> >> >> >> > > >

Jan. 10, 2015

Re: [Pharo-dev] Athens/Morphic

by Nicolai Hess

I think I got the MorphTreeTransformMorph now working, need to clean up the code and maybe I 'll upload this to the athens repository. nicolai 2015-01-08 12:27 GMT+01:00 Nicolai Hess <nicolaihess(a)web.de>: > A new Version of the AthensWrapWorldMorph (useful for testing Athens based > drawing for morphs). And some changes for using Athens with the existing > Morphs: > > Many things are working, some important not. > I had some terrible, terrible hours fighting with clipping, > MorphTransformation and Textrendering. > > MorphTreeTransform still doesn't work. > > Textrendering: > I solved by using some of > AthensText-classes. Those aren't fully working. > Tabs or similiar control characters don't work. > Text emphasis doesn't work. > Different fonts and different colors in one TextMorph is working now! > And I still don't know if this is the right way and I did not get any > review/feedback on this (issue 13449). > (In the meantime we have similiar TxTextModel based classes in the image, > maybe I am wasting my time). > > I had to make one change for creating a pattern paint > from a form: AthensCairoSurface>>#fromForm: > I am not sure about this change, but image fillstyle with 1 pixel height > didn't work, > but now they do (for example, change to vistary or watery theme, they > make some heavy use on fillstyles, and it works now with athens). > > > athens_wrap_world.cs is a new AthensWrapWorldMorph for testing > Athens rendering, every Morph put on this WrapWorld will be drawn > by athens. > > athens_morphic_support.cs and athens_morphic_textsupports.cs > add/fix athens related methods. > > athens_morphic_drawonathens.cs defines some more drawOnAthensCanvas: > methods. > > > >

Jan. 10, 2015

Re: [Pharo-dev] Prepared Statements on PostgresV2?

by Alain Rastoul

Hi Carlos, I have no (or very small) experience with Postgres, but I guess it works the same as for Mssql Server and Oracle. The way it works with Mssql MsSql (and Oracle) is: Prepared statements are not shared between connections from the client side, the gain is on the server side. Use case of prepared statement is tipically reusability: a statement you will execute thousand times with different parameters, looping in you execute call. The server keep the same compiled plan for each execution (note it can have some tricky drawbacks too: parameter sniffing, bad cardinality estimations hence bad plans ). The server will detect that you are using nearly same statements, based on query plans in query plan cache and will not recompile it. The query plan cache being global to the server, it can share plans between different connections, there is a gain here when you ahave a lot of connections executing same statements too. It means that you have to prepare the statement once in each connection, then you can loop in execute. This is by design. HTH. Regards, Alain Le 10/01/2015 12:09, David Carlos Manuelda a Ã©crit : > phil(a)highoctane.be wrote: > >> This was asked a while ago, here is what I could find: >> >> On 29/10/2013 9:32 PM, Yanni Chiu wrote: >> >>> >>> Looking at the postgres docs, I see PREPARE and EXECUTE. I'll try to >>> start up a postgres server, and see if I can get it to work. >>> >> >> Here's an example: >> >> TestPGConnection new executeAll: #( >> 'PREPARE test1 (text,text,integer,text) AS >> INSERT INTO films (code, title, did, kind) >> VALUES ($1,$2,$3,$4)' >> 'EXECUTE test1(''T_601'', ''Yojimbo'', 106, ''Drama'')' >> ). >> >> TestPGConnection new executeAll: #( >> 'PREPARE test2 (text) AS SELECT * FROM films WHERE code LIKE $1' >> 'EXECUTE test2(''%0%'')' >> ). >> >> TestPGConnection new executeAll: #( >> 'EXECUTE test2(''%1%'')' >> ). >> >> But, the third example returned: >> ERROR: prepared statement "test2" does not exist >> >> So it seems to not remember the prepared statements across connection >> cycles. Re-sending the prepared statement isn't good for performance, but >> it does solve your security issue. >> >> HTH >> Phil > Thanks for the info, yes it can be done via execute manually, just that I > was suggesting to have proper code for handling it. > > Furthermore, as per the way prepared statements are designed, they are not > saved across database connections, so on every new connection you have to > send them, or at least the ones you will be using (calling). > >> >> On Sat, Jan 10, 2015 at 2:06 AM, David Carlos Manuelda >> <stormbyte(a)gmail.com> wrote: >> >>> I've installed and tested PostgresV2 under pharo with the following doits >>> >>> Gofer new >>> smalltalkhubUser: 'PharoExtras' project: 'PostgresV2'; >>> configuration; >>> load. >>> (#ConfigurationOfPostgresV2 asClass project version: '2.4') load >>> >>> While it works good, it is missing a very important feature from both >>> security and performance point of view: The prepared statements. >>> >>> As a brief, prepared statements are parameterized SQL statements that are >>> loaded ONCE per connection instead of sending the whole query to DB every >>> time, and also, they are parameterized, so it completelly prevents SQL >>> injection, as the parameters are automatically 'detected' and scaped >>> and/or handled accordingly without allowing in any case a parameter to >>> alter the SQL meaning, which can happen by using regular SQL queries made >>> by string concatenation. >>> >>> I browsed the class and did not find any prepare: method nor anything >>> similar. >>> >>> Also, you can give a name to a SQL sentence, which makes the code much >>> more readable without messing too much logic with SQL commands and string >>> concatenation. >>> >>> An example is as follows: (supposing we have an instance variable >>> connection, already initialized and connected via PGConnection class) >>> >>> Instead of: >>> >>> self connection execute: 'SELECT data FROM mytable WHERE name=''', anUser >>> userName, ''';'. >>> >>> Would be something like this: >>> self connection executePrepared: 'getUserData' with: anUser userName. >>> >>> And another suggestion could be something like: >>> self connection prepare: 'getUserData' withSQL: 'SELECT data FROM mytable >>> WHERE name=$1' >>> >>> Any plan for this to be implemented or any hint to other PostgreSQL class >>> that already has it? >>> >>> David. >>> >>> P.S. I've written in my blog about this some time ago: >>> >>> http://stormbyte.blogspot.com.es/2012/06/programming-with-database-using.ht… >>> if someone finds it useful. >>> >>> >>> > > > >

Jan. 10, 2015

Re: [Pharo-dev] TextMorph broken?

by Atlas

Thanks Stef, that's good to know. It's certainly not an easy task, and I am glad that there is progress! Thanks Alain! -- View this message in context: http://forum.world.st/TextMorph-broken-tp4798645p4798755.html Sent from the Pharo Smalltalk Developers mailing list archive at Nabble.com.

Jan. 10, 2015

Re: [Pharo-dev] Prepared Statements on PostgresV2?

by David Carlos Manuelda

phil(a)highoctane.be wrote: > This was asked a while ago, here is what I could find: > > On 29/10/2013 9:32 PM, Yanni Chiu wrote: > >> >> Looking at the postgres docs, I see PREPARE and EXECUTE. I'll try to >> start up a postgres server, and see if I can get it to work. >> > > Here's an example: > > TestPGConnection new executeAll: #( > 'PREPARE test1 (text,text,integer,text) AS > INSERT INTO films (code, title, did, kind) > VALUES ($1,$2,$3,$4)' > 'EXECUTE test1(''T_601'', ''Yojimbo'', 106, ''Drama'')' > ). > > TestPGConnection new executeAll: #( > 'PREPARE test2 (text) AS SELECT * FROM films WHERE code LIKE $1' > 'EXECUTE test2(''%0%'')' > ). > > TestPGConnection new executeAll: #( > 'EXECUTE test2(''%1%'')' > ). > > But, the third example returned: > ERROR: prepared statement "test2" does not exist > > So it seems to not remember the prepared statements across connection > cycles. Re-sending the prepared statement isn't good for performance, but > it does solve your security issue. > > HTH > Phil Thanks for the info, yes it can be done via execute manually, just that I was suggesting to have proper code for handling it. Furthermore, as per the way prepared statements are designed, they are not saved across database connections, so on every new connection you have to send them, or at least the ones you will be using (calling). > > On Sat, Jan 10, 2015 at 2:06 AM, David Carlos Manuelda > <stormbyte(a)gmail.com> wrote: > >> I've installed and tested PostgresV2 under pharo with the following doits >> >> Gofer new >> smalltalkhubUser: 'PharoExtras' project: 'PostgresV2'; >> configuration; >> load. >> (#ConfigurationOfPostgresV2 asClass project version: '2.4') load >> >> While it works good, it is missing a very important feature from both >> security and performance point of view: The prepared statements. >> >> As a brief, prepared statements are parameterized SQL statements that are >> loaded ONCE per connection instead of sending the whole query to DB every >> time, and also, they are parameterized, so it completelly prevents SQL >> injection, as the parameters are automatically 'detected' and scaped >> and/or handled accordingly without allowing in any case a parameter to >> alter the SQL meaning, which can happen by using regular SQL queries made >> by string concatenation. >> >> I browsed the class and did not find any prepare: method nor anything >> similar. >> >> Also, you can give a name to a SQL sentence, which makes the code much >> more readable without messing too much logic with SQL commands and string >> concatenation. >> >> An example is as follows: (supposing we have an instance variable >> connection, already initialized and connected via PGConnection class) >> >> Instead of: >> >> self connection execute: 'SELECT data FROM mytable WHERE name=''', anUser >> userName, ''';'. >> >> Would be something like this: >> self connection executePrepared: 'getUserData' with: anUser userName. >> >> And another suggestion could be something like: >> self connection prepare: 'getUserData' withSQL: 'SELECT data FROM mytable >> WHERE name=$1' >> >> Any plan for this to be implemented or any hint to other PostgreSQL class >> that already has it? >> >> David. >> >> P.S. I've written in my blog about this some time ago: >> >> http://stormbyte.blogspot.com.es/2012/06/programming-with-database-using.ht… >> if someone finds it useful. >> >> >>

Jan. 10, 2015

Re: [Pharo-dev] TextMorph broken?

by stepharo

Le 10/1/15 02:04, Atlas a Ã©crit : > I used PluggableTextMorph instead and an override to vIsScrollbarNeeded. > However, it seems problem (4) is also present in the PluggableTextMorph > class. Also, there is no font change button for LabelItems in the morphic > menu. > Looking at the, sometimes rather obscure code in Morphic classes, it seems > that some major refactoring is in order. > > Are there any plans to revise Morphic? yes since years we are cleaning it but we need more time/money. Alain is rewriting a totally new kernel and he is making really nice progress. Stef > Also, is there additional documentation for Morphic? (Unfortunately, the > Squeak Wiki is rather superficial :(). > Well, at least the live environment makes inspection and experimentation > easy :) > > > > > -- > View this message in context: http://forum.world.st/TextMorph-broken-tp4798645p4798713.html > Sent from the Pharo Smalltalk Developers mailing list archive at Nabble.com. > >

Jan. 10, 2015