library(adbcdrivermanager)

# Use the driver manager to connect to a database
db <- adbc_database_init(adbcsqlite::adbcsqlite(), uri = ":memory:")
con <- adbc_connection_init(db)

# Write a table
flights <- nycflights13::flights
# (timestamp not supported yet)
flights$time_hour <- NULL

stmt <- adbc_statement_init(con, adbc.ingest.target_table = "flights")
adbc_statement_bind(stmt, flights)
adbc_statement_execute_query(stmt)
#> [1] 336776
adbc_statement_release(stmt)

# March flights
stmt <- adbc_statement_init(con)
adbc_statement_set_sql_query(stmt, "SELECT * from flights WHERE month = 3")
stream <- nanoarrow::nanoarrow_allocate_array_stream()
adbc_statement_execute_query(stmt, stream)
#> [1] -1
result <- tibble::as_tibble(stream)
adbc_statement_release(stmt)

result
#> # A tibble: 28,834 × 18
#>     year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
#>    <dbl> <dbl> <dbl>    <dbl>          <dbl>     <dbl>    <dbl>          <dbl>
#>  1  2013     3     1        4           2159       125      318             56
#>  2  2013     3     1       50           2358        52      526            438
#>  3  2013     3     1      117           2245       152      223           2354
#>  4  2013     3     1      454            500        -6      633            648
#>  5  2013     3     1      505            515       -10      746            810
#>  6  2013     3     1      521            530        -9      813            827
#>  7  2013     3     1      537            540        -3      856            850
#>  8  2013     3     1      541            545        -4     1014           1023
#>  9  2013     3     1      549            600       -11      639            703
#> 10  2013     3     1      550            600       -10      747            801
#> # ℹ 28,824 more rows
#> # ℹ 10 more variables: arr_delay <dbl>, carrier <chr>, flight <dbl>,
#> #   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
#> #   hour <dbl>, minute <dbl>

# March flights with a parameter, not passing parameter
stmt <- adbc_statement_init(con)
adbc_statement_set_sql_query(stmt, "SELECT * from flights WHERE month = ?")
stream <- nanoarrow::nanoarrow_allocate_array_stream()
adbc_statement_execute_query(stmt, stream)
#> [1] -1
result <- tibble::as_tibble(stream)
adbc_statement_release(stmt)

result
#> # A tibble: 0 × 18
#> # ℹ 18 variables: year <dbl>, month <dbl>, day <dbl>, dep_time <dbl>,
#> #   sched_dep_time <dbl>, dep_delay <dbl>, arr_time <dbl>,
#> #   sched_arr_time <dbl>, arr_delay <dbl>, carrier <dbl>, flight <dbl>,
#> #   tailnum <dbl>, origin <dbl>, dest <dbl>, air_time <dbl>, distance <dbl>,
#> #   hour <dbl>, minute <dbl>

# March flights with a parameter
stmt <- adbc_statement_init(con)
adbc_statement_set_sql_query(stmt, "SELECT * from flights WHERE month = ?")
adbc_statement_bind_stream(stmt, data.frame(a = 3))
stream <- nanoarrow::nanoarrow_allocate_array_stream()
adbc_statement_execute_query(stmt, stream)
#> [1] -1
result <- tibble::as_tibble(stream)
adbc_statement_release(stmt)

result
#> # A tibble: 1 × 18
#>    year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
#>   <dbl> <dbl> <dbl>    <dbl>          <dbl>     <dbl>    <dbl>          <dbl>
#> 1  2013     3     1        4           2159       125      318             56
#> # ℹ 10 more variables: arr_delay <dbl>, carrier <chr>, flight <dbl>,
#> #   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
#> #   hour <dbl>, minute <dbl>

# March flights with multiple parameters
stmt <- adbc_statement_init(con)
adbc_statement_set_sql_query(stmt, "SELECT * from flights WHERE month = ?")
adbc_statement_bind_stream(stmt, data.frame(a = 2:4))
stream <- nanoarrow::nanoarrow_allocate_array_stream()
adbc_statement_execute_query(stmt, stream)
#> [1] -1
result <- tibble::as_tibble(stream)
adbc_statement_release(stmt)

result
#> # A tibble: 3 × 18
#>    year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
#>   <dbl> <dbl> <dbl>    <dbl>          <dbl>     <dbl>    <dbl>          <dbl>
#> 1  2013     2     1      456            500        -4      652            648
#> 2  2013     3     1        4           2159       125      318             56
#> 3  2013     4     1      454            500        -6      636            640
#> # ℹ 10 more variables: arr_delay <dbl>, carrier <chr>, flight <dbl>,
#> #   tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
#> #   hour <dbl>, minute <dbl>

# Clean up
adbc_connection_release(con)
adbc_database_release(db)