character limits with sqlite3

[geeseven]

I just got around to kicking the tires with this project. Due to a copy/paste mistake, I discovered that there are virtually no character limits in most (maybe all) fields when using sqlite. For the most part, both the web interface and the mobile app deal with 1600 characters for application name and description along with message title and message. I have a feeling that is not the intended use case, as varchar(255) is used throughout the sqlite database.

Doing some digging, I learned that sqlite3 will allow the of use VARCHAR during creation, but it will only treat it as TEXT without the limit. See the sqlite section of this page. The sqlite documentation on limits state that sqlite3_limit needs to be used at run time to set SQLITE_LIMIT_LENGTH or it needs to be set at compile time. Looks like the run time option is possible with go-sqlite3.

FYI, my testing was done with the gotify/server docker container and sqlite3 3.26.0. Here is as high as my testing got:

$ sqlite3 gotify.db 'select length(message) from messages where id=17;'
46846

[jmattheis]

Thanks for trying out Gotify (:.

Interesting finding! In MySQL varchar(255) is used and enforced, therefore it's only possible to send messages with length <= 255, this seems like a bug. In postgres the type text is used instead of varchar(255) so there is no limit like in sqlite.

It should be consistent between all databases.

[eternal-flame-AD]

Explicitly adding gorm:"type:text" tag to message field solves the problem, but auto migration does not automatically upgrade obsolete schemas. I think this needs to be fixed manually.

[jmattheis]

Yeah, it still should be changed in our models.

[eternal-flame-AD]

Okay, I think only message needs to be changed to text, right?

[jmattheis]

I'd say, to stay consistent, we have to change all fields to text. In Postgres & sqlite all fields are type text too.