# Hosting - GCP: 1x e2 micro per month free - https://gcloud-compute.com/e2-micro.html - https://cloud.google.com/free/docs/free-cloud-features#compute - DigitalOcean @ https://www.digitalocean.com/ # AirTable AirTable itself https://saltcorn.com/ > Saltcorn is a platform for building database web applications without writing a single line of code. Use the intuitive point-and-click, drag-and-drop user interface to build the whole application https://www.nocodb.com/ > NocoDB is an open source "#NoCode" platform that turns any database into a smart spreadsheet. # Automations Zapier https://n8n.io/ # Passwords - BitWarden preferred - LastPass tolerable # BI / Dashboard solutions - Captures something about why Shiny > PowerBI, and both PowerBI and Tableau suck: - https://www.reddit.com/r/rstats/comments/kw2lp0/comment/gj2lap1/?utm_source=share&utm_medium=web3x - Critically, also, version control and collaboration ### Power BI - Great summary of some critiques: https://senturus.com/blog/taking-the-pain-out-of-printing-power-bi-reports/ - Also should consider that a dashboard is 'unitary' - by definition you can't collaborate simultaneously on development - Seems to be true for all of these tools: - https://community.tableau.com/s/question/0D54T00000F34aNSAR/tableau-conflicts-using-github - https://community.tableau.com/s/question/0D54T00000WWy5KSAT/can-more-than2-two-people-work-on-the-same-workbook-and-merge-changes-easily-and-upload-to-the-server - Some of this might be changing with the newest 2023 version of Power BI: they're converting things to be actual text-based files? So you can version control and, hopefully, collaborate on things that way. # Experiments ## Metadata - Setting up a Shiny app to explore & manage metadata for a SQL Server data warehouse - Using the {golem} framework: project name GolemMetadata - This will, however, require a SQL Server instance - Looked at doing something cool/weird with cloud resources - Doing like one of the free E2 instances on GCP just probably (a) wouldn't have the heft, and (b) would involve some weird networking and security steps... - Digital Ocean didn't have any freebies - Azure has all the right tech (obviously - seeing as Nous is on the Microsoft stack) but most of it is like timeboxed free. - So we'll focus on building a version that's just supposed to be run locally, rather than doing anything deployed to shinyapps or similar. ### Getting a working SQL Server - Installed a Docker container to run a SQL Server instance - _Shockingly_ painless - https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver16&pivots=cs1-powershell - Basically: - Installed [Docker Desktop](https://www.docker.com/products/docker-desktop/) - This prompted me to install WSL - not sure why it didn't come with - https://docs.docker.com/desktop/windows/wsl/ - Directs to https://learn.microsoft.com/en-us/windows/wsl/install-manual#step-4---download-the-linux-kernel-update-package which has a direct download link - Had to fully kill it once (killed a number of processes in Task Manager) as it got stuck on "Setting up Docker engine" or whatever - Then set up the SQL Server container - Boot up PowerShell ``` docker pull mcr.microsoft.com/mssql/server:2022-latest docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrong@Passw0rd>" ` -p 1433:1433 --name sql1 --hostname sql1 ` -d ` mcr.microsoft.com/mssql/server:2022-latest # Diagnostic: should give you "SQL Server is now ready for client connections..." docker exec -t sql1 cat /var/opt/mssql/log/errorlog | grep connection ``` Docker Desktop was a smooth experience too - the dashboard was clean & useful, with handy stop/resume buttons for containers. From this point: you can use the sqlcmd utility inside bash inside the container to do stuff in an interactive session - CREATE SCHEMA, CREATE TABLE, the lot... ``` docker exec -it sql1 "bash" /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<YourStrong@Passw0rd>" ``` ...OR you can just install SSMS and connect that way (just username = SA, password = password, and because it's local you can just specify the name of the SQL Server, which we set up to be "sql1" same as the container name) (https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-manage-ssms?view=sql-server-ver16) OR start connecting in R! Note also that the container - which is like a free development version of SQL Server, as the full version *is* licensed - comes with some stuff pre-configured: you get a few databases already set up. By default you'll be working in `master`. ``` library(DBI) # require(odbc) conn <- dbConnect( odbc::odbc(), Driver = "SQL Server", Server = "sql1", Database = "master", UID = "SA", # System Administrator - only acct by default PWD = "<YourStrong@Passw0rd>", Port = 1433 # Can see in Docker - container defaults to this one ) ``` ==**However**==, note that this is not an *entirely* frictionless experience: after quitting Docker, Windows will still be provisioning memory for the Linux installation (naughty Windows) (this manifests as the process "Vmmem" still using like `min(50%, 8GB)` of memory). This can be remedied by going `wsl --shutdown` in PowerShell (maybe bash or cmd as well?)