Databases SQL, NoSQL, ORMs, REST/GraphQL, JSON/gRPC SQL QL (1974) - PowerPoint PPT Presentation

Databases SQL, NoSQL, ORMs, REST/GraphQL, JSON/gRPC

SQL QL (1974) 4)  Initially, relational databases with query languages based on mathematical logic  SQL (Chamberlin, Boyce @ IBM Research)  Query language accessible to those without formal training in mathematics or computer programming  Hugely successful  DB2, Oracle, SQL Server, PostgreSQL  Databases support ACID properties  Atomic (transactions fully completed or not begun at all)  Consistent (system must remain in valid state, errors rolled back)  Isolated (each transaction executes as if it is the only one)  Durable (all changes permanent)  Then, the Internet and WWW happened…. Portland State University CS 430P/530 Internet, Web & Cloud Systems

WWW WWW  High volume, high velocity data  Non-uniform, "dirty" data  Can not be handled by traditional relational database  ACID properties not required (and difficult to scale-up to large amounts of data)  Motivates move to non-relational databases  Bigtable, Dynamo, MongoDB, Cassandra, etc.  Eschew the use of SQL to store, query, and retrieve data “ NoSQL ” Portland State University CS 430P/530 Internet, Web & Cloud Systems

NoSQL QL  Large-scale datastores with no standard query language  Often schemaless (e.g. key-value storage of arbitrary types)  Like a Python dictionary  Akin to static typing (SQL) vs. dynamic typing (NoSQL)  Limited querying support (i.e. no JOINs)  Often with weak consistency (BASE)  Basically Available (requests may return failure or a transient inconsistent state)  Soft state (system always moving towards consistent state)  Eventual consistency (when input stops, system will reach a consistent state)  But, get performance and scale!  Huge variety of implementations tailored to specific application  http://nosql-database.org/  How to choose? (Take 486/586 + Cloud and Cluster course) Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL QL vs. s. NoSQL QL SQL NoSQL (initially) Relational (structured) data Non-relational (unstructured) data Complex querying (JOINs) Simple key-value lookup Schema No schema ACID (Atomic, Consistent, Isolated, BASE (Basic Availability Soft-state Eventual) Durable) consistency consistency Portland State University CS 430P/530 Internet, Web & Cloud Systems

NewS wSQL: QL: Reven enge ge of SQL QL?  Bolt on SQL features to NoSQL  Transaction processing (OLTP)  SQL interfaces on top of Hadoop and Spark  Bolt on NoSQL features to SQL  Horizontally scalable SQL systems (H-Store, Spanner)  Get the best of both worlds  SQL querying and consistency  NoSQL scaling Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL QL as s th the un e unifyi ifying ng lang nguage? uage?  Timescale blog  https://blog.timescale.com/why-sql-beating-nosql-what-this-means- for-future-of-data-time-series-database-348b777b847a Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL http://www.w3schools.com/sql

SQL QL: : Str truct ucture ured d Qu Quer ery y Lang ngua uage ge  Standard language for accessing and manipulating databases  MySQL, Postgres SQL, SQLite, SQL Server, Access, Oracle, Sybase, DB2, etc.  Major commands are same between them  Includes two things  Data Definition Language (DDL)  Data Manipulation Language (DML) Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL Data Definition Language

SQL QL DD DDL (Da Data ta De Defini inition) tion)  Permits databases and database tables to be created or deleted (among other operations).  CREATE creates a new database, table, index  DROP : deletes a database, table, index Portland State University CS 430P/530 Internet, Web & Cloud Systems

CREA EATE TE  CREATE DATABASE  Used to create a database CREATE DATABASE database_name; CREATE DATABASE my_db;  CREATE TABLE  Used to create a table within database CREATE TABLE table_name( column_name1 data_type, column_name2 data_type, . . . ); CREATE TABLE persons ( P_Id INT, LastName VARCHAR(25), FirstName VARCHAR(25), Address VARCHAR(25), City VARCHAR(15), PRIMARY KEY (P_Id) ); Portland State University CS 430P/530 Internet, Web & Cloud Systems

Ba Basi sic c SQL QL Da Dataty tatypes pes  Note: Data must be clean! (Not easy to guarantee) Data type Description Storage Byte Allows whole numbers from 0 to 255 1 byte Integer Allows whole numbers between -32,768 and 32,767 2 bytes Long Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes Single Single precision floating-point. Will handle most decimals 4 bytes Double Double precision floating-point. Will handle most decimals 8 bytes Portland State University CS 430P/530 Internet, Web & Cloud Systems

SQL Data Manipulation Language

Ba Basi sic c fun unctions ctions us used ed in web eb app pps  CRUD  Create = INSERT INTO  Read = SELECT  Update = UPDATE  Delete = DELETE Portland State University CS 430P/530 Internet, Web & Cloud Systems

SEL ELEC ECT  Used to read data from a database  Result stored in a table, called the result-set SELECT column_name(s) FROM table_name; Persons Table P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger SELECT FirstName, LastName FROM Persons; SELECT * FROM Persons; Portland State University CS 430P/530 Internet, Web & Cloud Systems

WHER ERE E clause ause  Add a predicate to limit what is returned SELECT column_name(s) FROM table_name WHERE column_name operator value ; Persons Table P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger SELECT * FROM persons WHERE city='Sandnes';  Text values should be quoted by single quotes or double quotes  Numeric values do not need to be enclosed in quotes Portland State University CS 430P/530 Internet, Web & Cloud Systems

INSER ERT T INTO  Used to create new records in a table  Example CREATE DATABASE test; CREATE TABLE persons ( P_Id INT, LastName VARCHAR(25), FirstName VARCHAR(25), Address VARCHAR(25), City VARCHAR(15), PRIMARY KEY (P_Id) ); INSERT INTO persons VALUES (1, 'Hansen', 'Ola', 'Timoteivn10', 'Sandnes'); INSERT INTO persons VALUES (2, 'Svendson', 'Tove', 'Borgvn23','Sandnes'); INSERT INTO persons VALUES (3, 'Pettersen', 'Kari', 'Storgt20', 'Stavanger'); Persons Table P_Id LastName FirstName Address City 1 Hansen Ola Timoteivn 10 Sandnes 2 Svendson Tove Borgvn 23 Sandnes 3 Pettersen Kari Storgt 20 Stavanger Portland State University CS 430P/530 Internet, Web & Cloud Systems

UPDATE TE  Used to update records in a table UPDATE table_name SET column=value, column2=value2,… WHERE some_column=some_value; UPDATE Persons SET Address='Nissestien 67', city='Sandnes' WHERE lastname='Tjessem' AND firstname='Jakob'; Portland State University CS 430P/530 Internet, Web & Cloud Systems

DE DELET ETE E st statement ement  Used to delete records in a table DELETE FROM table_name WHERE some_column=some_value; DELETE FROM persons WHERE lastname='Tjessem' AND firstname='Jakob'; Portland State University CS 430P/530 Internet, Web & Cloud Systems

Ot Other er comm mmon on SQL QL pr primi mitiv tives es  Pattern matching  % wildcard for matching 0 or more characters  _ wildcard for matching exactly 1 character  Result processing  LIMIT  ORDER BY / DESC / ASC  DISTINCT  Table combining  JOIN (INNER, LEFT, RIGHT, FULL)  Constraints  NOT NULL  UNIQUE  PRIMARY KEY  FOREIGN KEY  CHECK (constraint) Portland State University CS 430P/530 Internet, Web & Cloud Systems

Gu Gues estbo tbook ok app pp . ├── Model.py ├── model_pylist.py  https://bitbucket.org/wuchangfeng/cs430-src ├── model_sqlite3.py  WebDev_Guestbook_v3_nginx_uwsgi ├── app.py ├── requirements.txt  Simple MVC app in Python/Flask ├── templates  Models │ ├── layout.html  sqlite3 model in model_sqlite3.py based on previously │ └── index.html └── static defined interface in Model.py └── style.css  sqlite3 uses a file in the file system to store database (see iPhone) Portland State University CS 430P/530 Internet, Web & Cloud Systems