SQLlite.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0e7f681c",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# Embedded  DBMS"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d323cac5",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Not a standard definition, also the [Wikipedia](https://en.wikipedia.org/wiki/Embedded_database) list is not so clear\n",
    "\n",
    "For our purposes we refer to database SQL compliant."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d26acf4",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Some bulletpoint:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc3cb5d6",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Pro:\n",
    "- Serverless: No need to \"connect\" remotely to the database, works on the edge\n",
    "- Low memory footprint\n",
    "- Encapsulated in application"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdb52edc",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Contra:\n",
    "- Not scalable\n",
    "- Not concurrent \n",
    "- Not managed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1a86dd8b",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# ACID "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "658b2704",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "![](images/acid.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "212f5014",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Atomicity\n",
    "> Atomicity means that you guarantee that either all of the transaction succeeds or none of it does. You don’t get part of it succeeding and part of it not. If one part of the transaction fails, the whole transaction fails. With atomicity, it’s either “all or nothing”."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08b8a490",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Consistency\n",
    "> This ensures that you guarantee that all data will be consistent. All data will be valid according to all defined rules, including any constraints, cascades, and triggers that have been applied on the database."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e58e4d7",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Isolation\n",
    "> Guarantees that all transactions will occur in isolation. No transaction will be affected by any other transaction. So a transaction cannot read data from any other transaction that has not yet completed."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8317ada7",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Durability\n",
    "> Durability means that, once a transaction is committed, it will remain in the system – even if there’s a system crash immediately following the transaction. Any changes from the transaction must be stored permanently. If the system tells the user that the transaction has succeeded, the transaction must have, in fact, succeeded."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87a85ab9",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# SQL Lite\n",
    "![Image](https://www.sqlite.org/images/sqlite370_banner.gif)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa782c54",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## What is SQLlite ?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fd50cbc",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "SQLite is an in-process library that implements a - self-contained (standalone, very few dependencies)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1511e66e",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- serverless (runs within the same process/thread of the application)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40b9623a",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- zero-configuration (no install, no admin, no configuration)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb938342",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "- transactional (in sense of ACID)\n",
    "\n",
    "SQL database engine."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f5f8715",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## The most deployed databased in the world\n",
    "https://www.sqlite.org/mostdeployed.html "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "824a2ec7",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "Over one trillion of devices:\n",
    "- Every Phone (Android, iPhone)\n",
    "- Every Desktop (Windows10, Mac)\n",
    "- Every browser (Chrome, Firefox, Safari)\n",
    "- Smart TV \n",
    "- Automotive multimedia systems\n",
    "- Lots of applications "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65de1daf",
   "metadata": {
    "cell_style": "split",
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "https://www.sqlite.org/famous.html\n",
    "- Adobe software (Reader, Photoshop Lightroom, ...)\n",
    "- Airbus for the flight software of some families\n",
    "- Whatsapp (at least some years ago)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "521812e3",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Features"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbfe605d",
   "metadata": {
    "cell_style": "center",
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "- Open Source\n",
    "- Single file (cross platform)\n",
    "- Think of SQLite not as a replacement for Oracle but as a replacement for fopen()\n",
    "- Long Support (2050), Strong Test suite and cycle  \n",
    "- \"Full\" support to SQL (expect very few [not included](https://www.sqlite.org/omitted.html) )"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87020c96",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Software\n",
    "\n",
    "- https://sqliteonline.com/\n",
    "- https://sqlitebrowser.org/\n",
    "- https://sqlitestudio.pl/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ad50491",
   "metadata": {},
   "source": [
    "## Libraries\n",
    "- Python: https://docs.python.org/3/library/sqlite3.html\n",
    "- R: https://cran.r-project.org/web/packages/RSQLite/vignettes/RSQLite.html"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.15"
  },
  "rise": {
   "autolaunch": true,
   "enable_chalkboard": "true",
   "footer": "<div class=\"sds-footer\"> *** Social Data Science (SDS) - Notions of Database Management Systems ***</div>",
   "header": "<div class=\"sds-header\"><div class=\"sds-header-image\"></div></div>",
   "scroll": true,
   "theme": "white"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}