Implement ingestion of list types for SQLite

### What feature or improvement would you like to see?

In https://github.com/apache/arrow-adbc/issues/2066 @skarakuzu and I outlined a use case for streaming LIST data into PostgreSQL and SQLite via ADBC. Support for PostgreSQL was [recently added](https://github.com/apache/arrow-adbc/pull/2153). (Thanks, @paleolimbot!)

Would it be justifiable to add LIST support for SQLite as well? Our use case is embedded scientific data collection scenarios, where it is not feasible to deploy PostgreSQL but we'd like a compatible-as-possible data model. In #2066 had floated an idea of using a SQLite JSON or JSONB column for this, as SQLite is generally loose about types and lacks any array/list type.

We would be happy to try to contribute this if that is feasible.

Start by reading issue #2066 and the PostgreSQL implementation in pull request #2153 to understand the existing LIST-ingestion approach. Then determine how SQLite JSON or JSONB columns should represent LIST data and what tests would demonstrate compatible ingestion; done means the design is agreed and SQLite support is covered by tests.

Written by the indexing model from the issue text.