Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Think about listing temporary tables #12

Open
1 of 3 tasks
krlmlr opened this issue Oct 5, 2016 · 12 comments
Open
1 of 3 tasks

Think about listing temporary tables #12

krlmlr opened this issue Oct 5, 2016 · 12 comments
Labels
interface 🔌

Comments

@krlmlr
Copy link
Member

krlmlr commented Oct 5, 2016

temporary argument, like with dbCreateTable(). Default NA lists everything, TRUE or FALSE lists only temporary or not.

r-dbi/RSQLite@8f49651b12bbd0

  • dbListTables()
  • dbExistsTable()
  • dbRemoveTable()
@krlmlr
Copy link
Member Author

krlmlr commented Mar 22, 2018

Handled as part of r-dbi/DBItest#159.

@krlmlr krlmlr closed this as completed Mar 22, 2018
@krlmlr
Copy link
Member Author

krlmlr commented Mar 22, 2018

@jimhester @edgararuiz: Maybe it's more useful to return a third column type in dbListObjects(), with possible values "table", "view" and "temporary"? Or maybe two new columns, "is_view" and "is_temporary"? Do we need to provide filtering options in dbListObjects()?

@krlmlr krlmlr reopened this Mar 22, 2018
@jimhester
Copy link

There are unfortunately many more table types ODBC data sources can use than table, view and temporary

"TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM", or a data source–specific type name.

https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqltables-function

@krlmlr
Copy link
Member Author

krlmlr commented Mar 23, 2018

Then it would be the backend's job to interpret its own values for the table type and fit it into the categories we provide.?

@edgararuiz-zz
Copy link

That's always a challenge to design, because if we do that @krlmlr , we would need a "catch-all" category for those categories we don't explicitly catalog. I'm thinking of those database types that may have their own, very unique, table designations. This is a long way to say, that if we do, we'll need an "Other" category.

@krlmlr
Copy link
Member Author

krlmlr commented Mar 23, 2018

So maybe "type" (character) and "temporary" (logical), both supporting NA for not yet specified or unknown information?

@jimhester
Copy link

Yeah that seems reasonable to me

@krlmlr
Copy link
Member Author

krlmlr commented Apr 25, 2018

dbRemoveTable() needs a temporary argument to make sure the right kind of table is removed with dbWriteTable(overwrite = TRUE).

@krlmlr
Copy link
Member Author

krlmlr commented Apr 26, 2018

Only MySQL/MariaDB seems to support dropping temporary tables via DROP TEMPORARY TABLE. The other two support a temp or pg_temp schema.

krlmlr referenced this issue in r-dbi/DBItest Apr 26, 2018
@krlmlr
- Support temporary argument in dbRemoveTable() (default: FALSE…
6cdea59 
…) (r-dbi/DBI#141).
krlmlr referenced this issue in r-dbi/RPostgres Apr 26, 2018
@krlmlr
- Support temporary argument in dbRemoveTable() (default: FALSE…
763bddf 
…) (r-dbi/DBI#141).
krlmlr referenced this issue in r-dbi/RMariaDB Apr 26, 2018
@krlmlr
- Support temporary argument in dbRemoveTable() (default: FALSE…
5498676 
…) (r-dbi/DBI#141).
krlmlr referenced this issue in r-dbi/RSQLite Apr 26, 2018
@krlmlr
- Support temporary argument in dbRemoveTable() (default: FALSE…
34b9f28 
…) (r-dbi/DBI#141).
clrpackages referenced this issue in clearlinux-pkgs/R-RSQLite Jul 23, 2018
@fenrus75
[update to 2.1.1] - Support temporary argument in dbRemoveTable()…
52d250f 
… (default: `FALSE`) (r-dbi/DBI#141).

Kirill Müller (43):
      fledge: Bump version to 2.1.0.9000
      ignore
      safer tic
      export dbIsReadOnly()
      bump
      Revert "export dbIsReadOnly()"
      Deploy from Travis build 1509 [ci skip]
      Deploy from Travis build 1510 [ci skip]
      Deploy from Travis build 1511 [ci skip]
      Deploy from Travis build 1514 [ci skip]
      Deploy from Travis build 1515 [ci skip]
      Deploy from Travis build 1516 [ci skip]
      Deploy from Travis build 1530 [ci skip]
      Deploy from Travis build 1531 [ci skip]
      Deploy from Travis build 1533 [ci skip]
      install DBI and DBItest from GitHub
      reexport dbIsReadOnly() and dbCanConnect()
      implement format()
      implement dbRemoveTable(fail_if_missing = FALSE)
      document
      Deploy from Travis build 1539 [ci skip]
      use dbCreateTable() and dbAppendTable()
      Deploy from Travis build 1542 [ci skip]
      support bigint argument to dbConnect()
      Deploy from Travis build 1543 [ci skip]
      - Data frames resulting from a query always have unique non-empty column names (r-dbi/DBItest#137).
      collate
      - Support `temporary` argument in `dbRemoveTable()` (default: `FALSE`) (r-dbi/DBI#141).
      Deploy from Travis build 1546 [ci skip]
      reexport Id()
      oops
      fledge: Bump version to 2.1.0.9001
      up drake-based revdep
      test bioc but only one level deep
      add revdep results
      remove remotes, bump dependency
      fledge: Bump version to 2.1.1
      up CRAN comments
      reexport Id()
      Deploy from Travis build 1565 [ci skip]
      NEWS
      Deploy from Travis build 1566 [ci skip]
      Deploy from Travis build 1567 [ci skip]
@krlmlr
Copy link
Member Author

krlmlr commented Sep 14, 2021

This was difficult for Redshift. Need to revisit.

@krlmlr
Copy link
Member Author

krlmlr commented Oct 17, 2021

Perhaps connections should keep track of temporary tables? Easier to do with a rewrite.

@krlmlr krlmlr closed this as completed Oct 17, 2021
@krlmlr krlmlr transferred this issue from r-dbi/DBI Oct 31, 2021
@krlmlr
Copy link
Member Author

krlmlr commented Oct 31, 2021

We can register the temporary tables created through "official" means; it's not perfect but can help for databases that make it hard to enumerate temporary tables.

@krlmlr krlmlr reopened this Oct 31, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
interface 🔌
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@jimhester @krlmlr @edgararuiz-zz