feat: Dynamic Column Adjustments

[wbh1]

Currently, I hardcode all the tables/columns with boolean types that need to be converted when doing imports. This is tedious and difficult to maintain. Instead, we should dynamically determine which columns need adjustment.

Some options:

• Run queries on sqlite to find tables/columns ahead of time
• Use expected error messages to dynamically retry with conversion
  • I like this one less.

Related issues

• Feat: Ignore source tables missing in target model #44

[bumarcell]

Is this error related? :(
INFO[2022-11-03T10:10:04Z] 📁 SQLlite file: /grafana.db INFO[2022-11-03T10:10:04Z] 📁 Dump directory: /tmp INFO[2022-11-03T10:10:04Z] ✅ sqlite3 command exists INFO[2022-11-03T10:10:07Z] ✅ sqlite3 database dumped to /tmp/grafana.sql INFO[2022-11-03T10:10:07Z] ✅ CREATE statements removed from dump file INFO[2022-11-03T10:10:09Z] ✅ sqlite3 dump sanitized INFO[2022-11-03T10:10:10Z] ✅ migration_log statements removed INFO[2022-11-03T10:10:10Z] ✅ char keyword transformed INFO[2022-11-03T10:10:11Z] ✅ hex-encoded data decoded FATAL[2022-11-03T10:11:19Z] ❌ pq: column "is_revoked" is of type boolean but expression is of type integer INSERT INTO "api_key" VALUES(1,1,'argocd','721b6988520348318be28ff74596c450f980f1fe44eec295168bc8439821a751c202e93bbd885aa94d3ade39ce23267369f7','Editor','2022-09-03 13:26:12','2022-09-03 13:26:12',1977571572,NULL,'2022-11-03 09:44:32',0) - failed to import dump file to Postgres.

[wbh1]

@bumarcell yep! To fix that, the table/column needs to be added to this file: https://github.com/wbh1/grafana-sqlite-to-postgres/blob/master/pkg/postgresql/tablechanges.go

[bumarcell]

Many thanx 🙏

Also worthy of noting is that my other two DCs didn't have that problem, although all have an API key which isn't revoked (is_revoked=false) but it worked without problems -> I checked the postgres and the value was f

[komkomissarov]

Today I encountered the same issue, but I opted for a simpler solution since I had only one API key – I simply deleted it. The migration went smoothly, and now everything is working.

Grafana 10.0.2
SQLite to Postgres 14

Thanks!