Can't close a project?

[endquote]

If I check the "closed" box on a project and then submit the form, I'm taken back to the projects list -- but no projects are shown! Scary! If I reload it, they come back, but the project I tried to close is still not closed.

[falkb]

This is strange. I saw such misbehaviour before my big svn commit of model.py repairing the db access routines, but I think you're already on the latest revision. The reason was that db writing did not work because of some mistakes in model.py. Uhmm... all projects I've closed lately didn't show that bug anymore. Can you see something in trac.log?

[endquote]

I removed and reinstalled the plugin from the latest source and the behavior remains.

I did see this in the log following the pressing of the "modify" button after checking the "closed" box.

2014-02-09 12:47:41,808 Trac[admin] INFO: Simple Multi Project: Modify project SST
2014-02-09 12:47:41,823 Trac[admin] ERROR: Modify Project Error: integer out of range
2014-02-09 12:47:41,823 Trac[admin] ERROR: SimpleMultiProject Error: Failed to added project 'SST'

[anonymous]

What is your db type (SQLite, MySQL, ...)?

[endquote]

postgres 9.3.

[anonymous]

I wonder if function update_project() has that exception in self.__SmpModel.update_project(). Could you put self.log.info("%s modified." % (name)) after self.__SmpModel.update_project() and tell me if this logging appears in trac.log then?

[anonymous]

should be line 65 in admin.py

[endquote]

I downloaded the zipped source again, added that line to admin.py, re-compiled and re-installed the egg, restarted trac, and tried to close the project again.

2014-02-10 09:55:46,335 Trac[main] DEBUG: Dispatching <RequestWithSession "POST '/admin/projects/simplemultiproject/5'">
2014-02-10 09:55:46,335 Trac[webui] DEBUG: T&E matched: /admin/projects/simplemultiproject/5  None
2014-02-10 09:55:46,338 Trac[session] DEBUG: Retrieving session for ID u'josh'
2014-02-10 09:55:46,348 Trac[admin] INFO: Simple Multi Project: Modify project SST
2014-02-10 09:55:46,349 Trac[admin] ERROR: Modify Project Error: integer out of range
2014-02-10 09:55:46,349 Trac[admin] ERROR: SimpleMultiProject Error: Failed to added project 'SST'
2014-02-10 09:55:46,349 Trac[chrome] DEBUG: Prepare chrome data for request

So the new log line is not appearing, which does seem to indicate an exception in self.__SmpModel.update_project().

[Ryan J Ollos]

You might want to try enabling SQL debugging. See TracIni#trac-section: [trac] debug_sql.

[endquote]

It looks like the closed field is being set to 1392068154000000L, which I'm guessing isn't what postgres wants.

Log with debug_sql on.

2014-02-10 13:36:10,612 Trac[session] DEBUG: Retrieving session for ID u'josh'
2014-02-10 13:36:10,612 Trac[util] DEBUG: SQL: 
                    SELECT last_visit FROM session
                    WHERE sid=%s AND authenticated=%s
                    
2014-02-10 13:36:10,612 Trac[util] DEBUG: args: (u'josh', 1)
2014-02-10 13:36:10,612 Trac[util] DEBUG: SQL: 
                    SELECT name, value FROM session_attribute
                    WHERE sid=%s and authenticated=%s
                    
2014-02-10 13:36:10,612 Trac[util] DEBUG: args: (u'josh', 1)
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: SELECT name FROM auth_cookie WHERE cookie=%s
2014-02-10 13:36:10,628 Trac[util] DEBUG: args: ('766967c4a0f40ab830568043280825ce',)
2014-02-10 13:36:10,628 Trac[api] DEBUG: action controllers for ticket workflow: ['ConfigurableTicketWorkflow']
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: SELECT id, generation FROM cache
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: SELECT generation FROM cache WHERE id=%s
2014-02-10 13:36:10,628 Trac[util] DEBUG: args: (1722364385L,)
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: SELECT username, action FROM permission
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: SELECT
                        id_project,name,summary,description,closed,"restrict"
                   FROM
                        smp_project
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: SELECT
                        name
                   FROM
                        smp_project
                   WHERE
                        id_project=%s
2014-02-10 13:36:10,628 Trac[util] DEBUG: args: ['5']
2014-02-10 13:36:10,628 Trac[admin] INFO: Simple Multi Project: Modify project SST
2014-02-10 13:36:10,628 Trac[util] DEBUG: SQL: UPDATE
                        smp_project
                      SET
                        name = %s, summary = %s, description = %s, closed = %s, "restrict" = %s
                      WHERE
                        id_project = %s
2014-02-10 13:36:10,628 Trac[util] DEBUG: args: [u'SST', u'xxx', u'', 1392068154000000L, u'employees, SST-external', u'5']
2014-02-10 13:36:10,628 Trac[util] DEBUG: execute exception: DataError('integer out of range\n',)
2014-02-10 13:36:10,628 Trac[admin] ERROR: Modify Project Error: integer out of range

2014-02-10 13:36:10,628 Trac[admin] ERROR: SimpleMultiProject Error: Failed to added project 'SST'

The contents of the smp_project table:

1;"DOT";"''";"xxx";0;"employees, DOT-external"
2;"MVI3";"''";"xxx";0;"employees"
3;"QCC2";"''";"xxx";0;"employees"
4;"LAB2";"''";"xxx ";0;"employees"
5;"SST";"''";"xxx";0;"employees, SST-external"

[anonymous]

​PostgreSQL int max val = 2147483647

The question is how timestamp integer values must be saved in the right way. Any idea?

[endquote]

Nice sleuthing. I changed smp_project.closed to a bigint and was able to close the project.

​http://www.postgresql.org/docs/8.4/static/datatype-numeric.html

[Ryan J Ollos]

Trac has a database abstraction that allows a large integer to be declared in the schema using the keyword type=int64: ​browser:/branches/1.0-stable/trac/db_default.py@11490:114-115#L110.

It looks like the issue is that directly altering the table is not compatible across database backends: simplemultiprojectplugin/trunk/simplemultiproject/environmentSetup.py@13590:148#L146​. Compare that to what is done in various Trac upgrade steps, such as ​browser/branches/1.0-stable/trac/upgrades/db23.py.

[falkb]

Thanks a lot for this excellent analysis. An update is in work now.

[falkb]

It's like a curse, changing the column type of a table is not possible with SQLite. :( The problem is described ​here. It seems to me, I must solve it in the way as Rajesh suggested there, which means to copy the table to a new one.

[Ryan J Ollos]

I'm not sure it would help even if you could change the column type for SQLite, because the database backends have different keywords for defining the datatypes.

Copying the table, so to speak, seems like the right approach, because that's how it is done in the Trac core when tables are upgraded. Make a temporary table to hold the data, drop the table, insert a new schema using the database abstraction, and then copy the data from the temporary table into the new table.

[falkb]

Replying to rjollos:

Copying the table, so to speak, seems like the right approach,

This looks like a bigger patch. If anybody wants to help out, a patch is welcome. Likely I wont find the time for the next 2 weeks.

... insert a new schema using the database abstraction...

you mean to use just 'int64' but the rest remains the same as in the earlier upgrade, right?

[falkb]

maybe it's just necessary to fix the plugin with another db upgrade for PostgreSQL, and maybe changing the type by altering the db table isn't a problem there...

[Ryan J Ollos]

The idea with using the classes in trac.db.schema is that they handle cross-db compatibility for you. You could probably find a way to fix this by modifying the schema for PostgreSQL, but then you may need to do the same for MySQL as well. It seems like more work, and a riskier approach, then just studying what has been done in ​trac:/browser/trunk/trac/upgrades and following the same approach.

[falkb]

Hi! Please, review and test my fix. (Note: I also used the opportunity to fix the bad table column name 'restrict' as well):

environmentSetup.py

@@ -14 +14 @@
 
 # Database schema variables
 db_version_key = 'simplemultiproject_version'
-db_version = 5
+db_version = 6
 
 tables = [
     Table('smp_project', key = 'id_project') [
@@ -157 +157 @@
             sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s"""
             cursor.execute(sqlInsertVersion, [db_version, db_version_key])
             db_installed_version = 5
+
+        if db_installed_version < 6:
+            sql = [
+            # TH ticket 11553 (problems with int range in PostgreSQL):
+            # fix: make field 'closed' of table 'smp_project' which is int a int64
+            """CREATE TEMPORARY TABLE smp_project_old AS SELECT * FROM smp_project""",
+            """DROP TABLE smp_project""",
+            """CREATE TABLE smp_project (
+                     id_project      integer PRIMARY KEY,
+                     name            varchar(255),
+                     description     varchar(255),
+                     summary         varchar(255),
+                     closed          int64,
+                     restrict_to     text,
+                     UNIQUE(name)
+            )"""
+            ]
+
+            for s in sql:
+                cursor.execute(s)
+            cursor.execute("""INSERT INTO
+                                   smp_project
+                                   (id_project,name,description,summary,closed,restrict_to)
+                              SELECT
+                                   id_project,name,description,summary,closed,%s
+                              FROM smp_project_old"""
+                           % db.quote('restrict'))
+            cursor.execute("""DROP TABLE smp_project_old""")
+
+            sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s"""
+            cursor.execute(sqlInsertVersion, [db_version, db_version_key])
+            db_installed_version = 6

model.py

@@ -66 +66 @@
         else:
             db = self.env.get_read_db()
         query = """SELECT
-                        id_project,name,summary,description,closed,%s
+                        id_project,name,summary,description,closed,restrict_to
                    FROM
                         smp_project
                    WHERE
-                        name = %%s""" % db.quote('restrict')
+                        name = %s"""
 
         if VERSION < '0.12':
             db = self.env.get_db_cnx()
         else:
             db = self.env.get_read_db()
         cursor = db.cursor()
-        cursor.execute(query, [name])
+        cursor.execute(query, [str(name)])
         return  cursor.fetchone()
         
     def get_all_projects(self):
@@ -86 +86 @@
         else:
             db = self.env.get_read_db()
         query = """SELECT
-                        id_project,name,summary,description,closed,%s
+                        id_project,name,summary,description,closed,restrict_to
                    FROM
-                        smp_project""" % db.quote('restrict')
+                        smp_project"""
 
         if VERSION < '0.12':
             db = self.env.get_db_cnx()
@@ -205 +205 @@
         else:
             db = self.env.get_read_db()
         query    = """INSERT INTO
-                        smp_project (name, summary, description, closed, %s)
-                      VALUES (%%s, %%s, %%s, %%s, %%s);""" % db.quote('restrict')
+                        smp_project (name, summary, description, closed, restrict_to)
+                      VALUES (%s, %s, %s, %s, %s);"""
 
         if VERSION < '0.12':
             db = self.env.get_db_cnx()
@@ -244 +244 @@
         query    = """UPDATE
                         smp_project
                       SET
-                        name = %%s, summary = %%s, description = %%s, closed = %%s, %s = %%s
+                        name = %s, summary = %s, description = %s, closed = %s, restrict_to = %s
                       WHERE
-                        id_project = %%s""" % db.quote('restrict')
+                        id_project = %s"""
 
         if VERSION < '0.12':
             db = self.env.get_db_cnx()

[endquote]

I installed the new version and was still able to close and open projects. I'm not sure that's really testing it, though.

[falkb]

Replying to endquote:

I installed the new version and was still able to close and open projects. I'm not sure that's really testing it, though.

If you've applied the patch of comment:19, table smp_project should contain a column 'restrict_to' and column 'closed' should be of type in64. After all the table shouldn't have lost any entries during the conversion. And table 'system' should contain version 6 for the SMP plugin entry.

[endquote]

I'm not having any luck applying the patched. TortoiseMerge just complains about "rejected patch hunks". If you can supply the patched file I'll give it a shot.

[falkb]

endquote, would be cool if you tried with patchedFiles.zip

[endquote]

I installed the new plugin.

C:\Users\trac\Downloads\simplemultiprojectplugin\trunk>trac-admin c:\trac\stimulant upgrade
Upgrading SimpleMultiProject database schema
SimpleMultiProject database schema version is 5, should be 6
The upgrade failed. Please fix the issue and try again.

ProgrammingError: type "int64" does not exist
LINE 6:                      closed          int64,                                         ^

[Ryan J Ollos]

The patch isn't cross-DB compatible because the DB abstraction in trac.db.api isn't being utilized. See ​trac:browser:/branches/1.0-stable/trac/db_default.py?rev=12533#L32 for examples.

[falkb]

Replying to rjollos:

Trac has a database abstraction that allows a large integer to be declared in the schema using the keyword type=int64: ​browser:/branches/1.0-stable/trac/db_default.py@11490:114-115#L110.

I'm quite confused now because I just was doing what rjollos suggested, I used that int64.

[falkb]

I understood int64 as the cross-platform alias for long integers. Not sure how my patch must look like to be correct.

[Ryan J Ollos]

You need to use the Table, Column and Index classes when constructing your SQL in order to get cross-compatibility.

[falkb]

Replying to rjollos:

You need to use the Table, Column and Index classes when constructing your SQL in order to get cross-compatibility.

???

Replying to rjollos:

The idea with using the classes in trac.db.schema is that they handle cross-db compatibility for you. You could probably find a way to fix this by modifying the schema for PostgreSQL, but then you may need to do the same for MySQL as well. It seems like more work, and a riskier approach, then just studying what has been done in ​trac:/browser/trunk/trac/upgrades and following the same approach.

Following this I have used ​trac:/browser/trunk/trac/upgrades/db5.py as template since you stated this is cross-db compatible.

[Ryan J Ollos]

The upgrade step db5.py is very old, from before PostgreSQL and MySQL were supported. It is not cross-db compatible.

​The code in ​trac:browser:/branches/1.0-stable/trac/db_default.py@11490:114-115#L110 shows how to construct SQL using the Table, Column and Index classes. I can't think of a better way to show you what needs to be done other than writing the code myself, which I don't have time to do at the moment.

(I'm not sure why the text in the previous paragraph is indented. It seems to be wrapped in a blockquote).

[Ryan J Ollos]

Replying to rjollos:

The upgrade step db5.py is very old, from before PostgreSQL and MySQL were supported. It is not cross-db compatible.

Actually, what I said about PostgreSQL and MySQL not having been supported at that time may not be correct. However, if you write raw SQL, then you need to ensure it is cross-db compatible. If you use the classes I suggested, the API will handle it for you.

The problem is that you've only half-implemented what I suggested (comment:12). I didn't say you could use int64 in SQL. I directed you to code showing how it can be used to construct SQL using the classes in trac.db.api.

[falkb]

Replying to rjollos:

The upgrade step db5.py is very old, from before PostgreSQL and MySQL were supported. It is not cross-db compatible.

I've just seen it's from 04/09/2013.

​The code in ​trac:browser:/branches/1.0-stable/trac/db_default.py@11490:114-115#L110 shows how to construct SQL using the Table, Column and Index classes.

I just see an assignment of a table construct to a variable called schema. No context how to put that together with the quite clear way of db5.py.

I can't think of a better way to show you what needs to be done other than writing the code myself, which I don't have time to do at the moment.

I still have hope to get a sudden enlightment but it's time-consuming to find the necessary info between the lines.

[falkb]

thanks a lot anyway for your patience and help

[Ryan J Ollos]

Replying to falkb:

I still have hope to get a sudden enlightment but it's time-consuming to find the necessary info between the lines.

Ah, the life of an open-source developer ;)

Here is a simpler example: tagsplugin/trunk/tractags/upgrades/db4.py​.

[falkb]

Looking twice at ​trac:/browser/branches/1.0-stable/trac/upgrades/db23.py again I can slightly imagine now this may help me as template as well... stay tuned.

[falkb]

don't know how I get to db5.py, likely I was looking for an easier one to use as template. Anyway it's clear now that the point is to use those 3 classes on creating new tables. Thanks for that hint, Ryan.

[Ryan J Ollos]

Yeah db23.py looks like a good example.

[falkb]

New trial for environmentSetup.py! :-D

The file model.py is the same as from the previous patch. I wonder if I should also take the chance to add the two new desired columns 'default_cc' and 'default_milestone' of #11543 here, I would at least save one more need to upgrade.

simplemultiprojectplugin/trunk/simplemultiproject/environmentSetup.py

@@ -14 +14 @@
 
 # Database schema variables
 db_version_key = 'simplemultiproject_version'
-db_version = 5
+db_version = 6
 
 tables = [
     Table('smp_project', key = 'id_project') [
@@ -47 +47 @@
     ],
 ]
 
+tables_v6 = [
+    Table('smp_project', key = 'id_project') [
+        Column('id_project', type = 'integer', auto_increment = True),
+        Column('name',type = 'varchar(255)'),
+        Column('description',type='varchar(255)'),
+        Column('summary',type='varchar(255)'),
+        Column('closed',type='int64'),
+        Column('restrict_to')
+    ],
+]
+
 """
 Extension point interface for components that need to participate in the
 creation and upgrading of Trac environments, for example to create
@@ -157 +168 @@
             sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s"""
             cursor.execute(sqlInsertVersion, [db_version, db_version_key])
             db_installed_version = 5
+
+        if db_installed_version < 6:
+            # TH ticket 11553 (problems with int range in PostgreSQL):
+            # fix: make field 'closed' of table 'smp_project' which is int a int64
+            cursor.execute("""CREATE TEMPORARY TABLE smp_project_old AS SELECT * FROM smp_project""")
+            cursor.execute("""DROP TABLE smp_project""")
+            # Create tables
+            for table in tables_v6:
+                for statement in db_connector.to_sql(table):
+                    cursor.execute(statement)
+
+            cursor.execute("""INSERT INTO
+                                   smp_project
+                                   (id_project,name,description,summary,closed,restrict_to)
+                              SELECT
+                                   id_project,name,description,summary,closed,%s
+                              FROM smp_project_old"""
+                           % db.quote('restrict'))
+            cursor.execute("""DROP TABLE smp_project_old""")
+
+            sqlInsertVersion = """UPDATE system SET value=%s WHERE name=%s"""
+            cursor.execute(sqlInsertVersion, [db_version, db_version_key])
+            db_installed_version = 6

P.S.: In case you already tried the patch of comment:19 you'll need to reset your trac.db to version 5 and probably rename column 'restrict_to' back to 'restrict' to get a consistent starting point.

[falkb]

second version

[endquote]

Replying to falkb:

P.S.: In case you already tried the patch of comment:19 you'll need to reset your trac.db to version 5 and probably rename column 'restrict_to' back to 'restrict' to get a consistent starting point.

How would I do that? I'm not sure where trac.db is stored.

[falkb]

Replying to endquote:

Replying to falkb:

P.S.: In case you already tried the patch of comment:19 you'll need to reset your trac.db to version 5 and probably rename column 'restrict_to' back to 'restrict' to get a consistent starting point.

How would I do that? I'm not sure where trac.db is stored.

A trac instance consists of conf\trac.ini and in parallel is db\trac.db, you'll just need to open that trac.db with a database browser and change the version value of simplemultiprojectplugin in table 'system' from 6 back to 5. Also rename db table column 'restrict_to' back to 'restrict' in db table 'smp_project'. Then you've "reset" to the state before an upgrade from version 5 to 6.

[endquote]

Except for my database_version is currently 29. Here is the contents of the system table:

"custom_report_manager_version";"1"
"database_version";"29"
"initial_database_version";"29"
"mastertickets";"2"
"simplemultiproject_version";"5"
"subtickets";"2"
"T&E-statuses";"assigned,accepted,closed,new,reopened"
"TimingAndEstimationPlugin_Db_Version";"6"
"tt_version";"4"
"";""

[falkb]

OK, good. Then just check db table 'smp_project' if there's a column 'restrict' or 'restrict_to'. In case of the latter rename it to 'restrict'. Then you're ready to try the patched files model.py of attachment:patchedFiles.zip​ plus environmentSetup.py of attachment:patchedFiles.2.zip​ . Rebuild the plugin with it, reinstall that .egg file and restart Trac. Then it'll force you to upgrade which should work correctly now.

[endquote]

The smp_project field was already "restrict".

I checked out from source, applied patches, compiled, shut down trac, installed, upgraded:

C:\Users\trac>trac-admin c:\trac\stimulant upgrade
Upgrading SimpleMultiProject database schema
SimpleMultiProject database schema version is 5, should be 6
Upgrade done.

Restarted trac and didn't see anything obvious wrong.

The smp_project field is now "restrict_to", the "closed" field is a bigint (though I had made that change myself before), and the simplemultiproject_version is 6.

Looks like everything is good?

[falkb]

endquote, thanks a lot, you're great! Yeah, it works now. :-)

[falkb]

In 13735:

• fixed #11553: timestamps need to be bigint with PostgreSQL
• get rid of table 'smp_project' column 'restrict' because that is a db keyword and therefore not suitable as column name, renamed it to 'restrict_to' (see #11461)
• => plugin db schema version increased to 6