On Thursday, January 24, 2013 10:45:56 AM UTC-8, Charles Monteiro wrote: > > sorry somehow hit the enter button. I'll repeat here: > > We are porting some stored procs and need a cross driver implementation > specifically across oracle and postgres but ideally across all major > popular rdms and at the very least MS Sequel Server. > > Our approach is to use the jdbc drivers since we want to deploy on Jruby > for various reasons. > > Given that it seems that our only strategy would be to use pagination but > just in case I want to create some leeway in case we decide for example to > support the native pg driver which now Sequel has the use_cursor support. > > thus my idea is to do something like this: > > def cursor a_table > > a_table.paginate > > end > > the idea is that this will return a "paginated" view to which we can apply > where criteria. >
I personally don't advocate using the pagination extension. It uses extend at runtime, and offers relatively little in return. I've kept it around for backwards compatibility reasons. Unless you really need the convenience methods it provides (e.g. first_page?, last_page?) using Dataset#limit instead is recommended. If you just want to process the whole dataset at once without keeping all rows in memory, that's how Sequel is designed to work (results are yielded one-at-a-time using Dataset#each). Now, many of the database drivers Sequel supports do buffer all rows in memory, but that is outside of Sequel's control. I think some of the JDBC drivers offer the ability to not buffer all rows in memory, see http://simplefeatures.wordpress.com/2011/09/30/jdbc-tweaks-to-enable-streaming-result-sets/. However, it doesn't look like the PostgreSQL JDBC driver work arounds for cursor use will work with Sequel (as Sequel uses its own transaction handling and doesn't turn autocommit off). I'll be happy to consider patches that allow jdbc/postgres to use cursors, if they don't cause other problems. > > Here's an example of the stored proc code I'm trying to port to Sequel: > > CURSOR my_cursor RETURN my_table%ROWTYPE IS > SELECT * FROM my_table where col1 = 'V' > ORDER BY col2 > FOR UPDATE; > > the idea is that later if need be I can add some instrospection to said > method above to determine whether a native pg driver is being use , if so > then leverage the "use_cursor" call but otherwise default to the use of > pagination. > If you are using the native pg driver, I would recommend using sequel_pg and its streaming support instead of use_cursor. That won't work on jruby, though. > So concretely I guess I'm asking: > > > 1. do paginated datasets implement all dataset behaviors and thus > allow me to use the same filter , where and etc methods ? > > They do, since a paginated dataset is just a clone of a dataset that is extended with a module. However, as I mentioned above, I recommend against using the pagination extension in new code. > > 1. what instrospection is available so that I can determine if I'm > using a native pg driver i.e. do I just determine that by looking at the > connection string or is there something more elegant. > > Database#database_type gives you the type of database you are connecting to :postgres, :mssql, :oracle. Database#adapter_scheme gives the type of adapter you are using :postgres, :jdbc, :mysql2. Some adapters only support a single database type, some support multiple database types. > > 1. If anything else comes to mind pls share:) > > This seems interesting, I'd appreciate it if you could share more details of the implementation you chose after you have been using it for a while. If you have more questions, please ask. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
