How get the field type for a null field?

[mamcx]

Related to https://github.com/jgallagher/rusqlite/issues/372.

I can query a Value for the type, but if the field allow null, and the data return null, the original type is lost.

I can get the column names, but not the types.

[thomcc]

I'm not sure this makes sense. NULL values in SQLite don't have a type besides NULL. SQLite's type system can be a bit strange, so you might want to read over https://www.sqlite.org/datatype3.html.

[mamcx]

Yeah, I know.

But the field have:

CREATE TABLE config (
    config_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    value TEXT
);

Now If I do:

SELECT * FROM config;

I need to know that value is Text.

BTW: And the rest of the info too, like if is PK, Unique, etc...

[gwenn]

Currently, there is no helper to access metadata:

• sqlite3_column_decltype
• or/and table_info

[gwenn]

In postgres crate:

• Statement.columns
• Rows.columns

[mamcx]

Yes, that is what is expected.

[gwenn]

Fixed by 0.18 release.

[ghost]

Is it currently possible to get more metadata, such as which columns are nullable? columns seems to only give declared type without that kind of extra information.

[gwenn]

If you want such information, you have to use:

1. sqlite3_column_origin_name => col_name or null for expression
2. sqlite3_column_table_name => tbl_name or null for expression
3. sqlite3_column_database_name => db_name or null for expression
   And then:

sqlite> SELECT "notnull" FROM <db_name>.pragma_table_info('<tbl_name>') WHERE name like '<col_name>';

Currently, there is no such feature in rusqlite. We can add such information behind a new 'metadata' feature.

[ghost]

So is it just impossible right now (without directly using the ffi)? I've been trying to use queries but it looks like they basically only return rows. I can't figure out how to run something like sqlite> SELECT "notnull" FROM <db_name>.pragma_table_info('<tbl_name>') WHERE name like '<col_name>'; and get its result.

[gwenn]

@billop There is already this test which works fine !

[ghost]

Thanks for the help, I basically got it working:

    fn get_nullable_stats(self: Self) -> Result<Vec<i64>, Error> {
        let conn = self.connect()?;
        let mut table_info =
            conn.prepare(r#"SELECT "notnull" FROM books.pragma_table_info("books")"#)?;
        let mut columns = Vec::new();
        let mut rows = table_info.query(NO_PARAMS)?;

        dbg!(rows.columns());

        while let Some(row) = rows.next()? {
            let row = row;
            let column: i64 = row.get(0)?;
            columns.push(column);
        }

        Ok(columns)
    }

[gwenn]

@billop You can also return booleans:

fn get_nullable_stats(self: Self) -> Result<Vec<bool>, Error> {
...
            let column: bool = row.get(0)?;