Repository: diesel-rs/diesel (language: rust)

Issue:
Error: `Unexpected end of row` when setup `diesel`
## Setup

### Versions

- **Rust:** rustc 1.68.2 (9eb3afe9e 2023-03-27)
- **Diesel:** 2.0.3
- **Database:** SQLite
- **Operating System:** WSL2 - Ubuntu 18.04

### Feature Flags

- **diesel:** sqlite

## Problem Description

I got these errors when setup diesel:
```bash
> diesel migration run
Running migration 2023-03-30-181339_create_songs
Running migration 2023-03-31-021724_create_users
Unexpected end of row

> diesel print-schema
Unexpected end of row
```

The migrations are applied. I think the error is for `print-schema`.

The migrations:

```sql
-- 2023-03-30-181339_create_songs
CREATE TABLE songs (
  id INTEGER PRIMARY KEY,
  url VARCHAR(255) NOT NULL,
  status VARCHAR(10) NOT NULL DEFAULT 'queued',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX songs_status_idx ON songs (status);
CREATE INDEX songs_created_at_idx ON songs (created_at);

-- 2023-03-31-021724_create_users
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  avatar VARCHAR(255)
);
```


Discussion / hints:
I cannot reproduce that locally. Can you provide details about your sqlite version?
Closed as this misses a reproduction.
```
$ cargo tree -i libsqlite3-sys --depth 1
libsqlite3-sys v0.26.0
└── diesel v2.1.0
libsqlite3-sys v0.26.0

$ sqlite3 --version
sqlite3 --version
3.24.0 2018-06-04 14:10:15 95fbac39baaab1c3a84fdfc82ccb7f42398b2e92f18a2a57bce1d4a713cbaapl

$ uname -vr
uname -vr
18.7.0 Darwin Kernel Version 18.7.0: Tue Jun 22 19:37:08 PDT 2021; root:xnu-4903.278.70~1/RELEASE_X86_64

$ diesel migration run
Unexpected end of row
```

This appears to repro the issue for me:

```sh
#!/usr/bin/env bash

PROJECT_DIR="$(mktemp -d)"
DB_FILE="$(mktemp)"

cd "${PROJECT_DIR}"
export DATABASE_URL="sqlite://${DB_FILE}"

echo "*** Project directory: ${PROJECT_DIR}"
echo "*** DB: ${DATABASE_URL}"
echo

echo "=== Setup: "
diesel setup
echo "=== Exit: $?"
echo

echo "=== Creating migration"
MIGRATION_UP=$(diesel migration generate migration1 | head -1 | awk '{ print $2 }')
MIGRATION_DOWN="$(dirname "${MIGRATION_UP}")/down.sql"

cat > "${MIGRATION_UP}" << EOF
CREATE TABLE sample_table (
  id INTEGER NOT NULL,
  value INTEGER NOT NULL
);
EOF

cat > "${MIGRATION_DOWN}" << EOF
DROP TABLE IF EXISTS sample_table;
EOF

echo

echo "=== Running migration up:"
diesel migration run
echo "=== Exit: $?"
echo

echo "=== Print schema:"
diesel print-schema
echo "=== Exit: $?"
echo

echo "=== Running migration down:"
diesel migration revert --all
echo "=== Exit: $?"
echo

rm -i "${DB_FILE}"
```
Digging into this a little more it seems like there's some interaction with the system version of sqlite3 (at least for me) and using `sqlite-bundled` instead of `sqlite` appears to get things working.
The problem is that the version of sqlite bundled with the version of OSX I'm using does not support `pragma table_xinfo` and the codepath for `pragma table_info` does not work.  `table_xinfo` returns an extra column and the `table_info` path expects that extra column.
Thanks for investigating. These insights are really helpful to understand the underlying problem. I will try to implement a fix for that in the next weeks. I likely need some help to testing the fix as soon as it is ready. Can I just ping you from the relevant PR then?
Sure
I had some moments to look through some code and I think I know whats happening here:

The problematic code is here: https://github.com/diesel-rs/diesel/blob/master/diesel_cli/src/infer_schema_internals/sqlite.rs#L144

For newer sqlite version we call `PRAGMA table_xinfo(...)`, for older versions we call `PRAGMA table_info(...)`. The former returns one more column than the later call. The linked code (specifically the `Queryable` impl some lines below) assumes the larger column number from `table_xinfo(...)`

I would like to change the code so that:

* It uses `sql_query` instead `dsl::sql`
* The `Queryable` impl is replaced by a `QueryableByName` impl that just uses a default value if the additional field is not there.


Yeah that's my understanding of what's happening.  I've not taken the time to go much further, but if the `hidden` column is not being used then it should be safe to simply use `table_info` regardless.  From the SQLite docs:

> This pragma returns one row for each column in the named table, including generated columns and hidden columns. The output has the same columns as for PRAGMA table_info plus a column, "hidden", whose value signifies a normal column (0), a dynamic or stored generated column (2 or 3), or a hidden column in a virtual table (1). The rows for which this field is non-zero are those omitted for PRAGMA table_info.
It's necessary to use `table_xinfo` as we want to get information about generated columns as well.
Ah, ok.  In that case, yes, using `QueryableByName` seems like a good idea to me.

Fix patch:
diff --git a/diesel_cli/src/infer_schema_internals/sqlite.rs b/diesel_cli/src/infer_schema_internals/sqlite.rs
index a71167a3dffd..3cdaa8c716f3 100644
--- a/diesel_cli/src/infer_schema_internals/sqlite.rs
+++ b/diesel_cli/src/infer_schema_internals/sqlite.rs
@@ -1,7 +1,9 @@
 use std::error::Error;
 
-use diesel::deserialize::{self, FromStaticSqlRow, Queryable};
+use diesel::deserialize::{self, Queryable};
 use diesel::dsl::sql;
+use diesel::row::NamedRow;
+use diesel::sql_types::{Bool, Text};
 use diesel::sqlite::Sqlite;
 use diesel::*;
 
@@ -15,18 +17,6 @@ table! {
     }
 }
 
-table! {
-    pragma_table_info (cid) {
-        cid ->Integer,
-        name -> VarChar,
-        type_name -> VarChar,
-        notnull -> Bool,
-        dflt_value -> Nullable<VarChar>,
-        pk -> Bool,
-        hidden -> Integer,
-    }
-}
-
 table! {
     pragma_foreign_key_list {
         id -> Integer,
@@ -141,7 +131,10 @@ pub fn get_table_data(
     } else {
         format!("PRAGMA TABLE_INFO('{}')", &table.sql_name)
     };
-    let mut result = sql::<pragma_table_info::SqlType>(&query).load(conn)?;
+
+    // See: https://github.com/diesel-rs/diesel/issues/3579 as to why we use a direct
+    // `sql_query` with `QueryableByName` instead of using `sql::<pragma_table_info::SqlType>`.
+    let mut result = sql_query(query).load::<ColumnInformation>(conn)?;
     match column_sorting {
         ColumnSorting::OrdinalPosition => {}
         ColumnSorting::Name => {
@@ -153,28 +146,35 @@ pub fn get_table_data(
     Ok(result)
 }
 
-impl<ST> Queryable<ST, Sqlite> for ColumnInformation
-where
-    (i32, String, String, bool, Option<String>, bool, i32): FromStaticSqlRow<ST, Sqlite>,
-{
-    type Row = (i32, String, String, bool, Option<String>, bool, i32);
-
-    fn build(row: Self::Row) -> deserialize::Result<Self> {
-        Ok(ColumnInformation::new(
-            row.1, row.2, None, !row.3, None, None,
+impl QueryableByName<Sqlite> for ColumnInformation {
+    fn build<'a>(row: &impl NamedRow<'a, Sqlite>) -> deserialize::Result<Self> {
+        let column_name = NamedRow::get::<Text, String>(row, "name")?;
+        let type_name = NamedRow::get::<Text, String>(row, "type")?;
+        let notnull = NamedRow::get::<Bool, bool>(row, "notnull")?;
+
+        Ok(Self::new(
+            column_name,
+            type_name,
+            None,
+            !notnull,
+            None,
+            None,
         ))
     }
 }
 
-#[derive(Queryable)]
-struct FullTableInfo {
-    _cid: i32,
+struct PrimaryKeyInformation {
     name: String,
-    _type_name: String,
-    _not_null: bool,
-    _dflt_value: Option<String>,
     primary_key: bool,
-    _hidden: i32,
+}
+
+impl QueryableByName<Sqlite> for PrimaryKeyInformation {
+    fn build<'a>(row: &impl NamedRow<'a, Sqlite>) -> deserialize::Result<Self> {
+        let name = NamedRow::get::<Text, String>(row, "name")?;
+        let primary_key = NamedRow::get::<Bool, bool>(row, "pk")?;
+
+        Ok(Self { name, primary_key })
+    }
 }
 
 #[derive(Queryable)]
@@ -204,7 +204,7 @@ pub fn get_primary_keys(
     } else {
         format!("PRAGMA TABLE_INFO('{}')", &table.sql_name)
     };
-    let results = sql::<pragma_table_info::SqlType>(&query).load::<FullTableInfo>(conn)?;
+    let results = sql_query(query).load::<PrimaryKeyInformation>(conn)?;
     let mut collected: Vec<String> = results
         .iter()
         .filter_map(|i| {


Test patch: