Creating database fails for postgreSQL 9.1

[ebuz]

Database table creation fails for postgreSQL. The problem is from specifying a size for the datastring column in the participant model. Text column length is optional for MySQL and SQLite databases but postgreSQL doesn't use (or apparently accept) a length specification. I can initiate a pull request with the fix.

Upshot: Fixes the problem for postgreSQL though I have not looked into how it changes the datastring length upper bound in MySQL databases.

[JSlote]

@ebuz would it be possible to check the protocol from the config and then modify the pertinent SQLAlchemy stuff based on the protocol? That way we'd be keeping everything safe.

[gureckis]

@JSlote @ebuz That would be my suggestion as well. I seem to remember specifying the datastring size ended up being important because in MySQL it defaults to something seemingly small and some psiTurk users were reporting data truncation issues (see this commit: 63dcc80).

[ebuz]

@gureckis that's what I was afraid of, looking through the documentation of MySQL it looks like there are actually four different Text types with differing lengths and which SQLalchemy picks to use when building the table seems related to the length parameter, if it isn't specified it likely defaults to one of the smaller of those. Using the SQLalchemy String type instead of Text doesn't work either. The 4294967295 value specified in the current declaration is too large since postgreSQL's character length limit is 10485760. And I think that value is too big for MySQL's varchar limit. Oy.

@JSlote A conditional declaration in models.py can fix it:

if 'postgres' in config.get('Database Parameters', 'database_url'):
  datastring = Column(Text)
else:
  datastring = Column(Text(4294967295))

I just tested it and it works but seems like a hack, though I don't know of any other solutions that aren't equally hackish.

[gureckis]

although SQLAlchemy aims at DB agnosticism, I think in practice it is nearly impossible to be a purist. given that the current solution of a large text field appears to work for both MySQL and SQLLite, i'm ok with a few conditional statements like this for postgreSQL, if it make things work... happy to merge a pull request to this effect.