[SOLVED] What is the best way to dump SQLite3 row values into SQL text when the table structure is unknown at compile time?

help
1

In a project I use to learn Rust, I would like to have an utility to export SQlite3 tables into a series of SQL statements suitable for Postgres (and same for the other direction). I know about pg_loader, ETLAlchemy, etc, - but since the project right now has codegens in both C# entity framework and diesel, and needs to work transparently on both sqlite3 and postgresql backends, moving the data between the two databases with the above involves a lot of brittle massaging with awk and sed, so I decided to give a pure-rust approach a shot. Postgres->Sqlite3 worked nicely, but I am not finding my Sqlite3->postgres solution too elegant.

I could only get Row::get*(i) function calls to compile when using a constant for "i", (thus the obvious solution of just iterating over N values and getting the value depending on type didn't work), so the best I could come with is what I consider a sort of an abuse of macros:

Even though it works fine, the "1, 2, 3, 4, 5 ..." sequence of parameters seems a bit ugly...

Is there a better way ? I know it is a bit of a special use case, so maybe I am using the microscope as a hammer :slight_smile:

2

I don't think the "constant" bit is quite correct... Looking at the docs you can use get() with anything which is RowIndex, so either i32 or &str (presumably the column name and index). It then retrieves that value as some T which is FromSql. By the looks of things rusqlite also provides a catch-all Value type which you can deserialize into.

Would something like this work?

let num_columns = row.column_count();

for i in 0..num_columns {
  let thing: Value = row.get(i).unwrap();
}
1 Like
3

oh, I did miss that type, it looks like it would be perfect!

But the compiler complains:

error[E0619]: the type of this value must be known in this context
   --> src/bin/export-a3s-sqlite3-to-postgres.rs:237:30
    |
237 |           let thing: Value = row.get(i).unwrap();
    |                              ^^^^^^^^^^^^^^^^^^^

Now, finally, when nothing helps, time to read the manual... :slight_smile: - and this shines the light on what was surprising a bit (other than missing the definition of "Value"), and probably triggered me yesterday to think a "constant" was needed:

row.rs has this definition:

pub fn get<I: RowIndex, T: FromSql>(&self, idx: I) -> T {
         self.get_checked(idx).unwrap()
}

so it seems the problem is not with the column type, but with the type used to index the column!
so it seems the problem is with using unpack(), which can not decide which value it might unpack as.

And indeed, rewriting this slightly to nudge the type system makes it compile:

      let thing = row.get::<i32, Value>(i);

So appears it is just the unwrap() that was making the compiler unhappy with above. I think in the query_map() method you had to unwrap the value, though...

In any case, now I can of course match on thing, and the code will look much much saner! Thanks a lot!

1 Like
4

Even row.get::<_, Value>(i) should work, since the i32 can be inferred from column_count()s return type :smile:

By the way, the ::<> is affectionately known as the "turbofish", in case you ever need to search for it. (Also "type inference operator", I believe)

1 Like
5

Yep, it does indeed! :slight_smile: Thanks a lot! Just a few weeks with Rust and really starting to enjoy it. At least for my little not-so-difficult tasks :slight_smile: And thanks again for all the help!

1 Like
6

That's simultaneously the lovely and horrible thing in Rust: its focus on explicitness and correctness, even for little things, makes you realise how sloppy virtually all other languages allow you to get. :slight_smile:

3 Likes
7

Indeed. I notice even when after some Rust I return to the "main day job" C, I start to think differently, and it is excellent. Rust is like a complement to good diet and exercise :slight_smile:

3 Likes