Modify

Opened 8 years ago

Closed 8 years ago

#764 closed defect (fixed)

Cannot run with Postgres backend

Reported by: tjb@… Owned by: bobbysmith007
Priority: high Component: TimingAndEstimationPlugin
Severity: major Keywords: time tracking
Cc: Trac Release: 0.10

Description

The db_helpers.py file contains a function get_database_table_names() which searches SQLITE_MASTER. This does not exist in postgres (or, I expect, mysql either)

Since the 0.9.6 time tracking support can no longer be used with the current trac, I am really keen to try the TimingAndEstimationPlugin.

Attachments (2)

trac_query.txt (6.1 KB) - added by tjb@… 8 years ago.
List of tables in a postgres database
timing1.diff (1.6 KB) - added by tjb@… 8 years ago.
Diffs to timetracking code to run with postgres

Download all attachments as: .zip

Change History (11)

comment:1 Changed 8 years ago by bobbysmith007

  • Priority changed from normal to high

This was something that I knew would come up. There are also a couple other commands that may or may not work in other database backends. I do not have a postgres / myql backend to test with, so I will have a very hard time making the code work. If you can point to a way to ask what tables are in the database without querying a "master table" I would love to add this to the plugin. Ideally we could make it work in all backends.

If there is no standard way to do this, I can make special case code for each database and use them based on which backend is being used. But again, without a postgres setup to test on, I can't really test what works there and what doesnt.

Any help is appreciated, Russ

Changed 8 years ago by tjb@…

List of tables in a postgres database

comment:2 Changed 8 years ago by tjb@…

I can find list all of the tables in a postgres database pretty easily - I have attached the query and results. I don't know if there is a standard SQL query whch would do this - I have been through the postgres documentation, and can't find a generic way to do this.

There may be a standard function provided by the Python/SQL interface (I am not familiar with this interface)

comment:3 Changed 8 years ago by coderanger

As a BTW, the general way to do this is to keep your plugin's DB version in the system table.

Another approach (though somewhat weird) is:

try:
    cursor.execute('SELECT * FROM %s LIMIT 1'%mytable)
    has_table = True
except:
    has_table = False

comment:4 Changed 8 years ago by bobbysmith007

sweet, I will try to get this implemented over teh weekend and have a new version up soon.

Russ

comment:5 Changed 8 years ago by bobbysmith007

So I have a new version ready to go that fixes the upgrade problem for postgres (presumably). I also noticed that I was using a version of strfttime that seems to be specific to only sqlite. I replaced this with the python equivalent of this code.

Hopefully this will work for you now, Russ

comment:6 Changed 8 years ago by tjb@…

I tried the 0.1.7 version, and was asked to upgrade the database. This failed on a call to strftime during the database upgrade. I patched do_user_man_update() (see attachment) to fix this, and then found problems with the SQL syntax in set_request_billing_dates()

  SELECT field AS [thing]

Postgres does not understand the [] around thing - what do they do?
By removing them, this worked, and I could run trac. I cannot do any meaningful reporting, as there are numerous instances of 'SELECT xxx AS [yyy]' in the reports, and I am also seeing the error

Dynamic variable '$NEW' not defined

Thanks Tony Butt

Changed 8 years ago by tjb@…

Diffs to timetracking code to run with postgres

comment:7 Changed 8 years ago by bobbysmith007

I will go through and remove all of those, sorry. That is SQL Server column rename syntax which also works in sqlite, but not, apparently, postgre.

Hopefully a new version will be up within an hour

thanks, Russ

comment:8 Changed 8 years ago by bobbysmith007

The last remaining problem that I see is the strftime calls in the reports. Does anyone know a more standard way to format the time on the reports. While I like it there, I dont think it is super critical for the time to be in the reports.

The times currently in the reports are: The time the report was run, the and the start and end date selected on the billing and estimation screen.

Also this error is caused, by not going to the report from the Billing and Estimation Screen:

Dynamic variable '$NEW' not defined

That screen makes sure that all the query string parameters to the reports are filled.

There is a new version in source control, but it doesnt yet fix all of the reports using strftime. I can hopefully get that sorted out soon, any postgre advise on this would be awesome.

Thanks, Russ

comment:9 Changed 8 years ago by bobbysmith007

  • Resolution set to fixed
  • Status changed from new to closed

I have updated the Billing reports to not use strftime (which is sqlite specific). I believe all things that were preventing this plugin from running with alternate database backends should be fixed. I am going to close this ticket and any new problems should be submitted as a new ticket.

I hope this works for eveyone, and thanks for all the help making this plugin work with more backends.

Russ

Add Comment

Modify Ticket

Action
as closed The owner will remain bobbysmith007.
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.