Cannot run with Postgres backend

[tjb@…]

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.

[Russ Tyndall]

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

[tjb@…]

List of tables in a postgres database

[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)

[Noah Kantrowitz]

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

[Russ Tyndall]

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

Russ

[Russ Tyndall]

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

[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

[tjb@…]

Diffs to timetracking code to run with postgres

[Russ Tyndall]

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

[Russ Tyndall]

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

[Russ Tyndall]

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