rsqliteadmin : sqlite admin tool in R shiny

Background

R provides facilities to handle sqlite databases which are very light and efficient for small projects. An interactive module in shiny will be a very handy tool to quickly explore and manage the sqlite databases.

Related work

There are several packages that can read and write to sqlite database but nothing interactive is available to our knowledge

• RSQLite
• sqldf
• Rstudio connections pane https://db.rstudio.com/rstudio/connections/

Details of your coding project

A lot of ideas can be borrowed from phpmyadmin which provides an admin dashboard for MySQL and is developed in php. Basic operations like creations of database, and tables, exploring table structures and running sql statements are must. Other additional features are up for discussion between mentors and students.

Expected impact

A quick and handy tool to explore sqlite databases from within R.

Mentors

Students, please contact mentors below after completing at least one of the tests below.

• EVALUATING MENTOR: Vijay Barve [email protected] is the author and maintainer of bdvis and a key member in the bdverse development team. Vijay is a biodiversity data scientist and has been a GSoC student and mentor since 2012 with the R project organization. Vijay has contributed to several packages on CRAN.
• Thiloshon Nagarajah [email protected] is the Shiny lead of the bdverse development team. He was past GSoC and GCI student for Fedora Project, Sahana Foundation and R Language. Thiloshon joined bdverse through Google Summer of Code in 2017 and has been a student, contributor, mentor and now a core member of the bdverse team.
• Narayani Barve [email protected] is a biodiversity informatics scientist and was a GSoC student (2014) as well as a mentor (2016-2019) with the R project organization. She developed the package ENMGadgets and has contributed to various other R packages. She has extensive experience working with spatial information and large scale databases.

Tests

Students, please do one or more of the following tests before contacting the mentors above.

• Easy: Create a SQLite database from R and populate dummy data.
• Easy: Document differences of SQLite vs other DBs, and when to use which in a markdown file.
• Medium: Create a function to take multiple search parameters as an argument and query DB by filtering out from these queries.
• Medium: Document connection between SQLite library vs DBI using a markdown file.
• Hard: Create a simple R package that incorporates the function that was developed as part of the Medium task.
• Hard: Create a simple package using Golem R package that contains a Shiny application to view the databases created using SQLite.

Solutions of tests

Students, please post a link to your test results here.

• EXAMPLE STUDENT 1 NAME, LINK TO GITHUB PROFILE, LINK TO TEST RESULTS.

• VISHESH TRIPATHI Github profile. Tests.

• Ashwin Ginoria, Tests

• Divyansh Chawla: GitHub Profile, Tests:

  easy1_medium1_search, (rmd source file)

  easy1_medium1_insert, (rmd source file)

  hard_test_1

  hard_test_2, (Screencast)

• Sonali Sharma : Github Profile. Tests.