In Liubuntu/SQLDataFrame: Representation of database tables in DataFrame metaphor.

update (4/5/2019)

1. Add mutate method and modified saveSQLDataFrame accordingly.
2. modified .join_union_prepare() function to have "mutate + rbind/join" work!
3. vignettes (general + internal)
4. documentations with easy examples.

update (3/29/2019)

1. saveSQLDataFrame returns SQLDataFrame constructed from new database and table.
2. makeSQLDataFrame function added to read text files into SQLite database and return the SQLDataFrame constructed according to the database, table, and dbkey.
3. Combined *_join and union functions with utility function to avoid duplicate code.
4. Rewrote union()@dbconcatKey to be more efficient.
5. BiocGenerics:::replaceSlots().

update (3/25/2019)

1. .join_union_prepare for attaching database to current connection.
2. rbind do take-one-at-a-time union.
3. saveSQLDataFrame write unique index with dbkey(x). Save ridx(x) if input SQLDataFrame comes from rbind (with non-null ridx(x)).
4. SQLDataFrame() constructor read ridx() if exists.
5. rewrote dbtable() method. Only works for @tbldata with real database on disk. Doesn't work for lazy tbl.

Questions:

1. Try real database with different SQL format. (MySQL, BigQuery, ...)
2. apply()?? (e.g., print...)
3. coerce into matrix for row/colSums, etc.
4. tryCatch: Error in result_create(conn@ptr, statement) : parser stack overflow. -- show, .join_union_prepare.
5. as.data.frame(), show(), extra layer of query?
6. *_join no S4 generics. -- Keep S3 method.
7. show() of rbind(ss11, ss21, ss31, ss22) slow. But after saveSQLDataFrame, show is fast. So the many layers of queries slowed down the total performance, including showing of @tblData. Message for users: when you notice slow down, do "saveSQLDataFrame"!
8. Should overwrite = FALSE check dbname or dbtable? (in makeSQLDataFrame() and saveSQLDataFrame()). Use 2 arguments. add "index = TRUE" for saveSQLDF.
9. try big database.
10. add unit tests.
11. [,]<- assignment, calls mutate().
12. update DESCRIPTION (description) and vignette intro.

misc:

• write ridx(SDF) setter?
• write getter for SDF@tblData?

add a new table "table_metadata",

2 additional tables to add to database: 1) dbkey info. serialize(dbkey(x), NULL) to convert to "raw", in "BLOB" (long binary??).

• modify "saveSQLDataFrame" to be compatible with op_double that from join or union.
• add new table for metadata (@indexes) within saveSQLDataFrame? See how to retrieve the SQL index table and make use of it.

Update:

1. .fun() to do complicated calls like $src$con@dbname. ident(dbtable(sdf)), ridx(sdf), normalizeRowindex(sdf) ...
2. rbind(), 1) write database table as unique rows. 2) return SDF with updateded @indexes.
3. debug: rbind(ss3, ss2)
4. ** union, then rbind. -- todo! (sql union? )

5. ** union, lazy? dbplyr::union.tbl_lazy

6. printROWS for show method, subset again [1:nhead, ]

7. .printROWS(), rewrite.
8. .extract_tbl_rows_by_key,
9. as.data.frame() rewrite ( match ridx to sort(unique(ridx)) ).

sprint update for rbind

1st trick: attach database as aux 2nd trick: insert into from aux. with queries. (lazy tbl from aux.) 3rd trick: unique database table, updated indexes with returned sdf.

Update:

1. saveSQLDataFrame create a new path is !exists()?

2. sdf[list(), ]

3. sdf[sdf, ]
4. filter(sdf, key1=="" & key2 > ..); sdf %>% filter(key1 ... key2 ... )
5. rbind(), copy_to/db_insert_into/dbWriteTable ??
6. SQLDataFrame show method, print the source? (refer saveSQLDataFrame print message).

row subsetting with numeric indexes,

• use raw sql command instead of "dplyr::filter"...
• rowsubsetting using "list()", or "filter() => list()". e.g., sdf[filter(col1 == "...", col1 %in% c("..."), ]. --- wait for the writing out function.

• col subsetting using "select(col1, col2, ...)", translate into col id and pass into .extractCOLS.

• must realize the key columns. -- done!

• Add ROWNAMES(SQLDataFrame) for composite key. by concatenating key columns. -- done! (unite does not work on tbl_dbi directly, but paste works, or collect%>%unite)

• Add row subsetting using [list(key1 = c(...), key2 == c(...), ), ], and translate into concatenated key values, then translate into numeric indexes (normalizeSingleBracketSubscript) for [ subsetting. -- done!

• write out subsetted SQL table? Write out into a new path, new database (local?) file. -- done!

• [, subsetting using filter(c1 = , c2 %in%, c3 > ...) or using "&"

• SQL raw filters translation.

• message for [numeric, ], to suggest language filtering.

• coercion of DataFrame/data.frame/ANY into SQLDataFrame.

• numeric calculations, e.g., sum(), max(), min, row/colSums, row/colMeans, row/colMin, row/colMaxs, apply, ... need to define.

• dbplyr::join.tbl_sql, left_join, reordering... rbind,SQLDataFrame? Maybe not, now one SQLDataFrame only corresponds to just one tbl_dbi object... Will there be any need for rbinding data from different database table?

• inst/test.db? only keep "colDatal" and rename as "colData"?? update testthat, examples... -- done!

DOING

• extractCOLS,SQLDataFrame, add generic in S4Vectors, update DelayedDataFrame and SQLDataFrame...

DONE

3/5/2019 - modify the "state" table, for unique "region+population". - add "union" method for SDF, which returns union with unique rows with automatic sorting. - reimplement "rbind" method, which extend "union" and update slots of "dbconcatKey" and "indexes". - add @dbconcatKey slot which corresponds to @tblData (has '.0' for numeric columns). now each SQLDF has @dbconcatKey, which is heavy... But anyway it will have the key cols evaluated when [ subsetting. ?? connect the current slot with the - ROWNAMES() applies ridx(x)to dbconcatKey(x), good for filter(, condition) where 'condition' has or doesn't have '.0' in the end. But for '[rnm, ]', has to include '.0' to match. Do not encourage.

• remove includeKey slot.
• remove "rownames", from slots/constructor/accessor/, test functions..
• add head(), tail() methods
• realization methods: as.data.frame(), as(,"DataFrame")

reimplementation of SQLDataFrame to keep key columns as fixed and show on the left-hand-side, with | in between to separate key columns with other columns. ncol, and colnames would only correspond to the non-key-columns.

• colnames() update to return colnames(tbl)[-dbkey(x)]
• ncol()? ncol(tbl) - length(dbkey(x)) ??
• remove @includeKey slot.
• remove .wheredbkey()? -- keep for now
• define dimnames, then colnames() would automatically work.
• define dim, then nrow/ncol() would automatically work.
• extract_tbl_from_SQLDataFrame, .printROWS.
• [[ extract and realize from the non-key-columns.
• extractROWS, .extractCOLS_SQLDataFrame.
• [ subsetting, add to @indexes. only refer to the non-key columns.
• show method refer to rowRanges(airway) for formatting.
• use key values for row subsetting.
• [["key", or ["key", to return realized key value?
• dbkey subsetting as rownames for single-key-SQLDataFrame only.
• new example, with primary key in middle columns, test.

always keep key() column, show as first column.

• add additional slot @includeKey = TRUE by default.
• dbkey() returns key column name. wheredbkey() returns the positions of key column e.g., match(dbkey(), colnames(SQLDataFrame)).
• update colnames(SQLDataFrame), check if @includeKey.

• update ncol(x), if !x@includeKey, return nc-1.

• "[,SQLDataFrame" always keep key column in @indexes[[2]].

• if ! wheredbkey(x) %in% j:
  • update @indexes[[2]] by adding wheredbkey(x).
  • update @includeKey as FALSE.

• [i,j], "j" should correspond to the original col orders.

• "show, SQLDataFrame" Always show key column as first column. e.g., "key | original columns...". key column save once, show twice!

• show(don't show) key column after | if @includeKey is TRUE(FALSE).
• .extract_tbl_from_SQLDataFrame will always have key col in returned tbl. (no work needed)
• .extract_tbl_rows_by_key, (no work)
• .printROWS, print "key | original columns... ". check @includeKey(x) to see if print the key column after |.
• [j] list_style_subsetting returns SQLDataFrame. show doesn't need change.
• [, j] single column returns realized by default drop=TRUE. (no change need)

Initial

• define a new class, SQLDataFrame, DO NOT extend DataFrame, save tbl_dbi object as one separate slot.
• define accessors and basic methods: dbname, dbtable, dbkey, rownames, colnames, nrow, ncol, dim, length ...
• define show,SQLDataFrame to work as show,DataFrame. Ignore the existing internal functions from DataFrame, including extractROWS, as.list, lapply...

slots - add extra slots @indexes in SQLDataFrame to save the row/col indexes. - ?? remove the @colnames slot? and keep colnames() accessor? (consistent with DataFrame) - renamed the SQLDataFrame@rownames slot into dbrownames.

validity check - validity check for dbtable() name. - validity check for the length of @indexes slot.

constructor - update SQLDataFrame() constructor for @indexes slot. - update SQLDataFrame() constructor with specified columns and error message for "col.names" argument.

accessors - update accessors of nrow(), ncol(), dim(), length(), colnames(), rownames() to reflect the @indexes slot.

show - add utility function .extract_tbl_from_SQLDataFrame() to return tbl_dbi object with row/col filter/selection from @indexes. - ??? add utility function .extract_tbl_rows_by_key() to extract certain rows by key. Need to rewrite, by removing the call of dbkey(x). - update show,SQLDataFrame to reflect the row/col indexes.

[, [[ - add extractROWS,SQLDataFrame, with both input and output to be SQLDataFrame object. - define [[,SQLDataFrame to return SQLDataFrame object. Only for single column extraction and do realize automatically. (works like "drop=TRUE") - define $,SQLDataFrame method, which calls [[,SQLDataFrame. - define [,SQLDataFrame to return SQLDataFrame object by adding/updating @indexes slot.

Liubuntu/SQLDataFrame documentation built on May 17, 2019, 7:43 a.m.