Data mapping for SQLite

[NoComprende]

I'm considering switching from MySQL to SQLite but one of the things that's been putting me off is the restricted datatypes in SQLite. e.g. For some of my MySQL float columns I could get the required precision by storing them in a 3 byte integer but every time I retrieved the value I'd need to divide by 10000 to get the true value and multiply them by 10000 (rounded to integer) to store them. Similarly I have many unsigned integers (tinyint unsigned, smallint unsigned, int unsigned) and from what I can see SQLite only supports unsigned largeints. Storing and retrieving them would require adding/subtracting a value for every retrieval/store (e.g. adding/subtracting 128 to every signed tinyint I retrieved/stored). I'd also need to do both multiplication/division and adding/subtracting for retrieving/storing my many unsigned float columns.

Would Devart's data mapping take care of all this for me? Is this data mapper something I purchase separately or does it come with LiteDAC? Also, I'm confused by the array of products you have. Are there any specific tools that would help me manage sqlite databases or any that would help me migrate from MySQL to SQLite?

Edit: I've been looking again at SQLite data types and I've obviously misunderstood the way SQLite stores data. I've been thinking along the lines of fixed row format (as in MyISAM tables) whereas SQLite seemingly just allocates the minimum storage for a numeric value. I'm finding it hard to get my head round this. e.g. If it stores the numeric value 25 for a column in a single byte what happens to the row when you change the value for that column to an integer value that requires 8 bytes? Does it have to shift the whole row to a new location that can accomodate it? With this in mind I can't imagine it being able to match the speed of reading/writing data from/to MyISAM fixed row tables. Anyway, I imagine my original post probably doesn't make much sense now.

[AlexP]

Hello,


SQLite has 4 types ( https://www.sqlite.org/datatype3.html ):

TEXT
NUMERIC
INTEGER
REAL
NONE

And not depending on how you are declaring a field (SQLite allows to specify any name for a type), the field will have one of the listed types. So you should focus on these types when mapping field types. DataTypeMapping allows mapping of database types to Delphi types and vice versa, and it doesn't affect saving data in the database.

[NoComprende]

Thanks Alex. I'm currently studying the sqlite documentation and I'm now a bit embarrassed by my original question. I should look before I leap.

[AlexP]

If you have any further questions, feel free to contact us.