As indicated by #983438: Don't allow batched export on PostgreSQL, this module doesn't support postgreSQL databases due to database query incompatibilities. As I use postgreSQL as a backend and can't change to MySQL for various reasons, I proposed that support for PostgreSQL be added to this module. I'm quite familiar with postgreSQL and thus intend to start working on a patch... I intend to keep a log of my progress here and would appreciate any help that like-wise minded developers would like to provide.
A note to the maintainers: If you could point out the places that postgreSQL incompatibilities exist that would greatly aid my efforts!
Comment | File | Size | Author |
---|---|---|---|
#3 | postgresql_support-1293788-2.patch | 2.24 KB | laceysanderson |
Comments
Comment #1
laceysanderson CreditAttribution: laceysanderson commentedJust finished confirming that the non-batched export works on postgreSQL. I was able to export both csv and xls of 8,000 lines + with no problem. However, a batched export would be ideal for these situations.
Progress on Batched Export:
I had hoped that this would be as simple as adding an if statement that would use CREATE TABLE {tablename} AS... instead of CREATE TABLE {tablename} SELECT... when the database driver is pgsql. However, even after making that change, I still get a number of errors and an empty file (except for header). Next it is complaining about the
@row := @row + 1 AS vde_weight
bit of the statement... However, I have never seen such notation before (being completely unfamiliar with MySQL).If anyone can explain this notation to me so that I can determine a postgreSQL equivalent it would help a Lot!
~Lacey
Comment #2
laceysanderson CreditAttribution: laceysanderson commentedOkay, I've fixed the problem :) That said, I don't use git and can't seem to even clone this project let alone create a patch for it *Sighs!*
I'm using Debian 6.0; Git 1.7.2.5
I'm trying to run
git clone --branch 6.x-2.0-beta5 http://git.drupal.org/project/views_data_export.git
but I get an empty directory... Any idea what I'm doing wrong?Anyway, Changes that need to be made: in plugins/views_data_export_plugin_display_export.inc Line 547 gets changed to
The following is added directly above Line 555
And the incompatible pgsql driver gets removed from the array at Line 781.
Comment #3
laceysanderson CreditAttribution: laceysanderson commentedFigured it out!
git clone --branch 6.x-2.x http://git.drupal.org/project/views_data_export.git
Anyway I've attached a patch.
Testing:
This patch was tested on a Drupal 6.22 site with a postgresql 8.4.7 backend. First I needed a node view to test it on. I enabled the default frontpage view and changed the style of the Default display to table. I also added the title, teaser, post date and type fields. The view worked properly. I then added a Data Display of style csv with batched export set to yes. I also attached the data export display to the page display. When I click on the csv link on the frontpage page, I was brought to a progress bar which completely within 3 minutes and presented me with a csv file with 34,783 lines which corresponded to the number of rows in my view :) There were no errors displayed during this process. The index table was created properly.
As for justification of my changes:
Since the problem was the CREATE TABLE {tablename} SELECT ... which was causing problems for postgresql backends, I added an if statement to use a postgresql specific query if the driver used was pgsql and otherwise to use the original insert query. The postgresql specific query uses the postgresql specific CREATE TABLE {tablename} AS SELECT ....
Another postgresql incompatible piece of the query was the @row := @row + 1 which I interpreted to add a sequential row number to the table. The recommended way to do this in postgresql (compatible with earlier versions, easy to understand and read, better performance: see http://www.postgresonline.com/journal/archives/79-Simulating-Row-Number-...) is by creating a temporary sequence (will be dropped at end of session) and using that to create the row number -hence the next_val('sequencename'). I used the name of the table concatenated to _seq in order to ensure that the temporary sequence was unique and would not cause collisions.
Comment #4
Ben Coleman CreditAttribution: Ben Coleman commentedI need this, but I need it in 7.x-3.x. I'll try to set up a Drupal 6 site here to review this. Think it will be hard to port this to 7.x-3.x?
Comment #5
Ben Coleman CreditAttribution: Ben Coleman commentedI'll confirm this works for both PostgreSQL and MySQL (i.e. it works for PostgreSQL, and doesn't break MySQL). I ran tests on both, outputting the same view both as batched, and as non-batched. For both, the batched and non-batched output was identical. Looks good to me.
I'm going to take this patch and see if the same kind of thing can be done for 7.x-3.x. Will post to a separate issue.
Comment #6
christine1126x CreditAttribution: christine1126x commentedThanks laceysanderson,
Patch works perfectly :)
Comment #7
Steven Jones CreditAttribution: Steven Jones commentedHuge thanks for the patch and reviews, I've incldued this in 6.x-2.x and 7.x-3.x.