Support for "string"

[diekaines]

Please add support for "string" column type.

I have this on sqlite

CREATE TABLE "posts" (
	"postid"	INTEGER,
	"authorname"	STRING,
);

and it didnt work till i added the "STRING" on the code

        if data_type in {"TEXT", "CLOB","STRING"}:
            return "TEXT"

Sqlite3 has the "string" as a valid column type as you can see here
https://github.com/WinBuilds/sqlite3/blob/1ef6f39d2238c8d75bb1a555a98e583340a33195/test/types.test#L275

Thank you

[soulseekah]

Since STRING is treated as an unknown column type in SQLite the 5th affinity rule will apply, it's going to be NUMERIC.

Unfortunately STRING is not a SQLite TEXT column internally. Question is: what should be the behavior? Should we break this when converting? Or should we add STRING in the 1st rule to affine `TEXT?

The larger problem is that this plugin doesn't seem to follow the type affinity rules in SQLite3. SQLite is too flexible and allows absolutely any type to be set for a column. I don't think this plugin should error out really, maybe warn and try to INT them as per affinity rules?

[techouse]

Well yes, as @soulseekah noted, SQLite is indeed flexible. I designed this to cover most cases and to be honest I never came across a STRING column in SQLite. It was usually TEXT or VARCHAR. Not sure what the benefit of supporting all these custom column types is, but yes, sure I could add it to that condition https://github.com/techouse/sqlite3-to-mysql/blob/master/sqlite3_to_mysql/transporter.py#L230

Question is: should I or should you use VARCHAR or TEXT in your SQLite?

[soulseekah]

Well, it's easy to make the mistake and use STRING in the definitions, SQLite aims to be back-compatible.
And as @kotfarepra mentioned above even SQLite maintainers made the mistake here: https://github.com/WinBuilds/sqlite3/blob/1ef6f39d2238c8d75bb1a555a98e583340a33195/test/types.test#L275 in their tests :) so seems justifiable enough to be supported.
There are also a handful of questions regarding people asking what's the difference between STRING and TEXT in SQLite in many languages.

Question is: should I or should you use VARCHAR or TEXT in your SQLite?

If you look at the affinity charts SQLite does not support strict VARCHAR to TEXT affinities. Things like FULLTEXT and silly things like MYTEXT are valid SQLite column names that are transformed to TEXT. I agree it's an edge case, but this is a feature of SQLite. No other database allows this.

So all the example affinity rules should actually apply in the same way as in SQLite https://github.com/WinBuilds/sqlite3/blob/1ef6f39d2238c8d75bb1a555a98e583340a33195/src/build.c#L1177

So STRING will actually become a NUMERIC, which in MySQL should be a DOUBLE I guess? With import errors to deal with individually.

Another question is why the default column for STRING didn't kick in to VARCHAR(255)? That's the default for -mysql-string-type, right? Or am I confused? What's the error that @kotfarepra got?

Moreover, this whole issue uncovers a much higher level issue: configuration. Is there a way to configure custom mappings for a 0-tier affinity? Like --column STRING=TEXT --column CUSTOM=INT? I think there would be a benefit seeing that SQLite is so flexible.

Thoughts?

[techouse]

Well yes, sure this could be added but as I stated above, I designed this to cover most cases, but most certainly not all and/or edge cases. If you check the comment here you'll see what I mean 😄

If you feel like it, please make a PR and I'll merge it. I have had very little time lately to work on this project more actively 😞

[techouse]

Added in v1.3.3