Skip to contents

Update or append rows in a flytable database

Source: R/flytable.R
flytable_update_rows.Rd

flytable_select_options returns the option names currently defined for one or more single- or multiple-select columns.

flytable_add_select_options adds one or more new options to a single- or multiple-select column's vocabulary.

flytable_update_rows updates existing rows in a table, returning TRUE on success.

flytable_append_rows appends data to an existing table, returning TRUE on success. You do not need a _id column as part of your input for this to work.

flytable_nrow returns the number or rows in one or more flytable tables using a SQL COUNT query.

Usage

flytable_select_options(table, col = NULL, base = NULL)

flytable_add_select_options(table, col, options, base = NULL)

flytable_update_rows(
  df,
  table,
  base = NULL,
  append_allowed = TRUE,
  chunksize = 1000L,
  multi_select_cols = NULL,
  allow_new_options = FALSE,
  ...
)

flytable_append_rows(
  df,
  table,
  base = NULL,
  chunksize = 1000L,
  multi_select_cols = NULL,
  allow_new_options = FALSE,
  ...
)

flytable_nrow(table, base = NULL)

Arguments

table

Character vector naming a table

col

Character vector of single- or multiple-select column name(s). The default NULL returns options for every such column in the table.

base

Character vector naming a seatable base (recommended) or a Base object returned by flytable_base (expert use).

options

Character vector of new option name(s) to add.

df

A data.frame containing the data to upload including an _id column that can identify each row in the remote table.

append_allowed

Whether rows without row identifiers can be appended.

chunksize

To split large requests into smaller ones with max this many rows.

multi_select_cols

Character vector of column names to treat as multiple-select (list-per-cell) columns. The default NULL auto-detects these from the table's column metadata.

allow_new_options

When a multi-select value is not already a defined option for its column, whether to add it automatically (via flytable_add_select_options) rather than raising an error.

...

Additional arguments passed to pbsapply which might include cl=2 to specify a number of parallel jobs to run.

Value

flytable_select_options a named list of character vectors (one per column) giving that column's currently defined option names.

flytable_add_select_options the response from the seatable API, invisibly.

Logical indicating success, invisibly (failures will normally cause premature termination with errors written to the console).

Details

seatable automatically maintains a unique id for each row in a _id column. This is returned by flytable_query and friends. If you modify data and then want to update again, you need to keep the column containing this row _id.

You do not need to provide this _id column when appending new rows. Indeed you will get a warning when doing so.

The chunksize argument is required because it seems that there is a maximum of 1000 rows per update action.

Multiple-select columns (e.g. initials, annotator) need special handling: seatable expects a genuine list of option names per cell, otherwise it silently creates a new bogus option out of whatever string it was given. By default any column that seatable reports as type "multiple-select" is auto-detected and routed through this list-per-cell path; pass multi_select_cols explicitly to override detection. A plain scalar value (e.g. "AB", or a comma-joined "AB,CD") is accepted as shorthand and split on commas – symmetric with how a multi-select cell reads back by default – or you can supply a list-column directly (e.g. I(list(c("AB","CD")))), which is also how you write a literal option name that itself contains a comma (a list-column cell is taken verbatim, not split). Either way every resulting value is checked against the column's existing option vocabulary and rejected (by default) unless it is already a known option – see allow_new_options and flytable_add_select_options.

See also

Other flytable: flytable-queries, flytable_cached_table(), flytable_list_selected(), flytable_login()

Examples

# \donttest{
flytable_select_options("testfruit", "initials")
#> $initials
#>   [1] "AB"                                         
#>   [2] "CD"                                         
#>   [3] "EF"                                         
#>   [4] "zztest-allow-new-options"                   
#>   [5] "zztest-allow-new-options-20260806090909.154"
#>   [6] "zztest-allow-new-options-20260806091008.674"
#>   [7] "zztest-allow-new-options-20260806091133.306"
#>   [8] "zztest-allow-new-options-20260806091256.771"
#>   [9] "zztest-allow-new-options-20260806234705.956"
#>  [10] "zztest-allow-new-options-20260806231622.141"
#>  [11] "zztest-allow-new-options-20260806231952.238"
#>  [12] "zztest-allow-new-options-20260806232219.920"
#>  [13] "zztest-allow-new-options-20260807033343.576"
#>  [14] "zztest-allow-new-options-20260807033546.735"
#>  [15] "zztest-allow-new-options-20260807033645.979"
#>  [16] "zztest-allow-new-options-20260807050451.091"
#>  [17] "zztest-allow-new-options-20260807050706.787"
#>  [18] "zztest-allow-new-options-20260807050942.710"
#>  [19] "zztest-allow-new-options-20260807113708.958"
#>  [20] "zztest-allow-new-options-20260807113935.288"
#>  [21] "zztest-allow-new-options-20260807114015.189"
#>  [22] "zztest-allow-new-options-20260807115513.688"
#>  [23] "zztest-allow-new-options-20260807115614.215"
#>  [24] "zztest-allow-new-options-20260807115908.771"
#>  [25] "zztest-allow-new-options-20260807121608.172"
#>  [26] "zztest-allow-new-options-20260807121611.270"
#>  [27] "zztest-allow-new-options-20260807140539.235"
#>  [28] "zztest-allow-new-options-20260807140803.047"
#>  [29] "zztest-allow-new-options-20260807140857.255"
#>  [30] "zztest-allow-new-options-20260807144305.796"
#>  [31] "zztest-allow-new-options-20260807212759.498"
#>  [32] "zztest-allow-new-options-20260807225902.752"
#>  [33] "zztest-allow-new-options-20260807230734.014"
#>  [34] "zztest-allow-new-options-20260807225231.885"
#>  [35] "zztest-allow-new-options-20260807225419.575"
#>  [36] "zztest-allow-new-options-20260807232417.812"
#>  [37] "zztest-allow-new-options-20260807232455.095"
#>  [38] "zztest-allow-new-options-20260807232548.869"
#>  [39] "zztest-allow-new-options-20260807232708.903"
#>  [40] "zztest-allow-new-options-20260807232725.355"
#>  [41] "zztest-allow-new-options-20260808065126.205"
#>  [42] "zztest-allow-new-options-20260808065205.990"
#>  [43] "zztest-allow-new-options-20260808065257.394"
#>  [44] "zztest-allow-new-options-20260808103814.458"
#>  [45] "zztest-allow-new-options-20260808104049.749"
#>  [46] "zztest-allow-new-options-20260808094324.660"
#>  [47] "zztest-allow-new-options-20260808094730.510"
#>  [48] "zztest-allow-new-options-20260808094739.780"
#>  [49] "zztest-allow-new-options-20260808095218.329"
#>  [50] "zztest-allow-new-options-20260808095305.148"
#>  [51] "zztest-allow-new-options-20260808095413.678"
#>  [52] "zztest-allow-new-options-20260808101748.882"
#>  [53] "zztest-allow-new-options-20260808102030.663"
#>  [54] "zztest-allow-new-options-20260808102204.856"
#>  [55] "zztest-allow-new-options-20260808112719.490"
#>  [56] "zztest-allow-new-options-20260808112747.510"
#>  [57] "zztest-allow-new-options-20260808112810.012"
#>  [58] "zztest-allow-new-options-20260808112903.209"
#>  [59] "zztest-allow-new-options-20260808112928.692"
#>  [60] "zztest-allow-new-options-20260808113020.653"
#>  [61] "zztest-allow-new-options-20260809065301.314"
#>  [62] "zztest-allow-new-options-20260809065453.320"
#>  [63] "zztest-allow-new-options-20260809065609.011"
#>  [64] "zztest-allow-new-options-20260809075447.796"
#>  [65] "zztest-allow-new-options-20260809075523.781"
#>  [66] "zztest-allow-new-options-20260809075658.552"
#>  [67] "zztest-allow-new-options-20260809075830.983"
#>  [68] "zztest-allow-new-options-20260809092708.481"
#>  [69] "zztest-allow-new-options-20260809093012.036"
#>  [70] "zztest-allow-new-options-20260809093217.582"
#>  [71] "zztest-allow-new-options-20260812001931.031"
#>  [72] "zztest-allow-new-options-20260812002217.474"
#>  [73] "zztest-allow-new-options-20260812002435.287"
#>  [74] "zztest-allow-new-options-20260812031954.953"
#>  [75] "zztest-allow-new-options-20260812032300.810"
#>  [76] "zztest-allow-new-options-20260812032308.731"
#>  [77] "zztest-allow-new-options-20260812221128.236"
#>  [78] "zztest-allow-new-options-20260812221144.298"
#>  [79] "zztest-allow-new-options-20260812223035.286"
#>  [80] "zztest-allow-new-options-20260812223146.577"
#>  [81] "zztest-allow-new-options-20260812223432.979"
#>  [82] "zztest-allow-new-options-20260816184734.908"
#>  [83] "zztest-allow-new-options-20260816184958.189"
#>  [84] "zztest-allow-new-options-20260816185014.716"
#>  [85] "zztest-allow-new-options-20260816192827.686"
#>  [86] "zztest-allow-new-options-20260816193150.040"
#>  [87] "zztest-allow-new-options-20260816193225.885"
#>  [88] "zztest-allow-new-options-20260816193303.616"
#>  [89] "zztest-allow-new-options-20260816193412.222"
#>  [90] "zztest-allow-new-options-20260816193445.800"
#>  [91] "zztest-allow-new-options-20260817010320.436"
#>  [92] "zztest-allow-new-options-20260817010321.403"
#>  [93] "zztest-allow-new-options-20260817010713.416"
#>  [94] "zztest-allow-new-options-20260820021309.256"
#>  [95] "zztest-allow-new-options-20260820021638.059"
#>  [96] "zztest-allow-new-options-20260820021718.711"
#>  [97] "zztest-allow-new-options-20260820025806.295"
#>  [98] "zztest-allow-new-options-20260820030217.720"
#>  [99] "zztest-allow-new-options-20260820030231.299"
#> [100] "zztest-allow-new-options-20260820124656.207"
#> [101] "zztest-allow-new-options-20260820124910.579"
#> [102] "zztest-allow-new-options-20260820125133.768"
#> [103] "zztest-allow-new-options-20260820131311.653"
#> [104] "zztest-allow-new-options-20260820131400.413"
#> [105] "zztest-allow-new-options-20260820131444.093"
#> [106] "zztest-allow-new-options-20260820132317.456"
#> [107] "zztest-allow-new-options-20260820132605.010"
#> [108] "zztest-allow-new-options-20260820132920.675"
#> [109] "zztest-allow-new-options-20260821153913.605"
#> [110] "zztest-allow-new-options-20260821154101.017"
#> [111] "zztest-allow-new-options-20260821154108.802"
#> [112] "zztest-allow-new-options-20260821161937.281"
#> [113] "zztest-allow-new-options-20260821162037.940"
#> [114] "zztest-allow-new-options-20260821162116.928"
#> [115] "zztest-allow-new-options-20260821162317.343"
#> [116] "zztest-allow-new-options-20260821165642.086"
#> [117] "zztest-allow-new-options-20260821165651.570"
#> [118] "zztest-allow-new-options-20260821170044.509"
#> [119] "zztest-allow-new-options-20260911161212.386"
#> [120] "zztest-allow-new-options-20260911161333.826"
#> [121] "zztest-allow-new-options-20260911161641.739"
#> [122] "zztest-allow-new-options-20260911174721.681"
#> [123] "zztest-allow-new-options-20260911192554.314"
#> [124] "zztest-allow-new-options-20260911192658.922"
#> [125] "zztest-allow-new-options-20260911192859.952"
#> [126] "zztest-allow-new-options-20260911225009.906"
#> [127] "zztest-allow-new-options-20260911225050.170"
#> [128] "zztest-allow-new-options-20260911225108.615"
#> [129] "zztest-allow-new-options-20260914143724.117"
#> [130] "zztest-allow-new-options-20260914144017.139"
#> [131] "zztest-allow-new-options-20260914152527.329"
#> 
# }
if (FALSE) { # \dontrun{
flytable_add_select_options("testfruit", "initials", "AN")
} # }
if (FALSE) { # \dontrun{
fruit=flytable_list_rows('testfruit')
flytable_update_rows(table='testfruit', fruit[1:2, c(1,4:6)])

# writing a multiple-select column
flytable_update_rows(table='testfruit',
  data.frame(row_id=fruit$`_id`[1], initials=I(list(c("AB","CD")))))
} # }
if (FALSE) { # \dontrun{
flytable_append_rows(table="testfruit",
  data.frame(fruitname='lemon', person='David', nid=4))
} # }