CODEWITHSUNDEEP
sqlitetype-conversion
Udo G
Udo G

Reputation: 13111

sqlite typeless columns and storage in .db file

I have a SQlite3 table that has typeless columns like in this example:

CREATE TABLE foo(
  Timestamp INT NOT NULL,
  SensorID,
  Value,
  PRIMARY KEY(Timestamp, SensorID)
);

I have specific reasons not to declare the type of the columns SensorID and Value. When inserting rows with numeric SensorID and Value columns I notice that they are being written as plain text into the .db file.

When I change the CREATE TABLE statement to...

CREATE TABLE foo(
  Timestamp INT NOT NULL,
  SensorID INT,
  Value REAL,
  PRIMARY KEY(Timestamp, SensorID)
);

...then the values seem to be written in some binary format to the .db file.

Since I need to write several millions of rows to the database, I have concerns about the file size this data produces and so would like to avoid value storage in plain text form.

Can I force SQLite to use binary representation in it's database file without using explicitly typed columns?

Note: Rows are currently written with PHP::PDO using prepared statements.

Upvotes: 0

Views: 336

Answers (1)

jpjacobs
jpjacobs

Reputation: 9549

The example in section 3.4 in the sqlite docs about types demonstrates the insertion of a number as int in a column without an explicit declaration of type. I guess the trick is leaving out the quotes around the number, which would convert it to a string (which, in the case of typed columns, would be coerced back into a number).

Section 2 in the page linked above also provides a lot of info about the type conversions taking place.

Upvotes: 2

Related Questions