Database

From Things and Stuff Wiki
Revision as of 04:59, 19 November 2018 by Milk (talk | contribs) (→‎SQLite)
Jump to navigation Jump to search


to sort

General

  • https://en.wikipedia.org/wiki/Database - an organized collection of data. The data are typically organized to model relevant aspects of reality in a way that supports processes requiring this information. For example, modeling the availability of rooms in hotels in a way that supports finding a hotel with vacancies.

Database management systems (DBMSs) are specially designed applications that interact with the user, other applications, and the database itself to capture and analyze data. A general-purpose database management system (DBMS) is a software system designed to allow the definition, creation, querying, update, and administration of databases. Well-known DBMSs include MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Microsoft Access, Oracle, SAP, dBASE, FoxPro, IBM DB2, LibreOffice Base and FileMaker Pro. A database is not generally portable across different DBMS, but different DBMSs can inter-operate by using standards such as SQL and ODBC or JDBC to allow a single application to work with more than one database.


  • https://en.wikipedia.org/wiki/Query_language - an be classified according to whether they are database query languages or information retrieval query languages. The difference is that a database query language attempts to give factual answers to factual questions, while an information retrieval query language attempts to find documents containing information that is relevant to an area of inquiry.



  • https://en.wikipedia.org/wiki/Column_(database) - a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed. When a column allows data values of a single type, it does not essentially mean it only has simple text values. In relational database terminology, column's equivalent is called attribute.
  • https://en.wikipedia.org/wiki/Row_(database) - also called a record or tuple — represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields. Each row in a table represents a set of related data, and every row in the table has the same structure.
  • https://en.wikipedia.org/wiki/Field_(computer_science) - a record divided into fields. Relational databases arrange data as sets of database records, also called rows. Each record consists of several fields; the fields of all records form the columns. Examples of fields: name, gender, hair colour.


  • https://en.wikipedia.org/wiki/View_(SQL) - the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view. In some NoSQL databases, views are the only way to query data.

Database practitioners can define views as read-only or updatable. If the database system can determine the reverse mapping from the view schema to the schema of the underlying base tables, then the view is updatable. INSERT, UPDATE, and DELETE operations can be performed on updatable views. Read-only views do not support such operations because the DBMS cannot map the changes to the underlying base tables. A view update is done by key preservation.


  • https://en.wikipedia.org/wiki/Materialized_view - a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.



  • https://en.wikipedia.org/wiki/Database_index - a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.


  • https://en.wikipedia.org/wiki/CAP_theorem - also named Brewer's theorem after computer scientist Eric Brewer, states that it is impossible for a distributed data store to simultaneously provide more than two out of the following three guarantees:
  • Consistency: Every read receives the most recent write or an error
  • Availability: Every request receives a (non-error) response – without guarantee that it contains the most recent write
  • Partition tolerance: The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes

In particular, the CAP theorem implies that in the presence of a network partition, one has to choose between consistency and availability. Note that consistency as defined in the CAP theorem is quite different from the consistency guaranteed in ACID database transactions.

  • https://en.wikipedia.org/wiki/PACELC_theorem - an extension to the CAP theorem. It states that in case of network partitioning (P) in a distributed computer system, one has to choose between availability (A) and consistency (C) (as per the CAP theorem), but else (E), even when the system is running normally in the absence of partitions, one has to choose between latency (L) and consistency (C).


  • https://en.wikipedia.org/wiki/ACID_(computer_science) - Atomicity, Consistency, Isolation, Durability, is a set of properties of database transactions intended to guarantee validity even in the event of errors, power failures, etc. In the context of databases, a sequence of database operations that satisfies the ACID properties (and these can be perceived as a single logical operation on the data) is called a transaction. For example, a transfer of funds from one bank account to another, even involving multiple changes such as debiting one account and crediting another, is a single transaction.






  • https://en.wikipedia.org/wiki/Correlation_database - a database management system (DBMS) that is data-model-independent and designed to efficiently handle unplanned, ad hoc queries in an analytical system environment. Because a correlation DBMS stores each unique data value only once, the physical database size is significantly smaller than relational or column-oriented databases, without the use of data compression techniques. Above approximately 30GB, a correlation DBMS may become smaller than the raw data set.

Relational





  • https://en.wikipedia.org/wiki/Relational_calculus - consists of two calculi, the tuple relational calculus and the domain relational calculus, that are part of the relational model for databases and provide a declarative way to specify database queries. This in contrast to the relational algebra which is also part of the relational model but provides a more procedural way for specifying queries.


  • https://en.wikipedia.org/wiki/Relational_algebra - first created by E.F. Codd while at IBM, is a family of algebras with a well-founded semantics used for modelling the data stored in relational databases, and defining queries on it. The main application of relational algebra is providing a theoretical foundation for relational databases, particularly query languages for such databases, chief among which is SQL.



  • etc.




  • Jailer - a tool for database subsetting, schema and data browsing. It exports consistent, referentially intact row-sets from relational databases. It removes obsolete data without violating integrity. It is DBMS agnostic (by using JDBC), platform independent, and generates DbUnit datasets, hierarchically structured XML, and topologically sorted SQL-DML.


Object


SQL

ugh 





SELECT * from table;










  • SQL-DK – an batch/terminal client for relational databases


  • https://en.wikipedia.org/wiki/Information_schema - (information_schema) is an ANSI-standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database. It can be used as a source of the information which some databases make available through non-standard commands


MySQL

MariaDB is compatible with MySQL, you probably want to use that, if not Postgres





  • YouTube: MySQL - A series covering working with MySQL including managing databases, tables and data.

Connecting

mysql -p
  # connect with anonymous user, prompt for password

mysql -u username -ppassword -h nonlocalhost dbname
  # specify user, password and nonlocalhost address, USE dbname



Admin


mysqladmin - a client for performing administrative operations. You can use it to check the server's configuration and current status, to create and drop databases, and more.




SHOW engines;

SHOW processlist;

Show variables;

Database management

SHOW databases;

USE [db name];

CREATE DATABASE [dbname];

CREATE DATABASE IF NOT EXISTS [dbname]

mysql -u username -p -e "CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci";
  # drupal 7

select database();
  # show which database is in use

DROP database dbname;
  # remove dbname from db;

User management


CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'newpassword';



SELECT User FROM mysql.user;
  # show all database users



USE mysql;
SET PASSWORD FOR 'user-name-here'@'hostname-name-here' = PASSWORD('new-password-here');

UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE User='user-name-here' AND Host='host-name-here';


Passwords
SET PASSWORD FOR 'user'@'localhost' = PASSWORD('mypass');

mysqladmin -u user_name -h host_name password "newpwd"



Password=PASSWORD('NewPassword') WHERE User='root'; FLUSH PRIVILEGES;


Permissions


SHOW GRANTS;
  # show permissions for current db user

SHOW GRANTS FOR user@localhost;
  # show permissions for a user

SELECT CONCAT("SHOW GRANTS FOR '",user,"'@'",host,"';") FROM mysql.user WHERE host!='localhost';
 # Create command list for showing user grants [8]



GRANT all on [dbname].* TO '[username]'; [9]



GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON databasename.* TO 'username'@'localhost' IDENTIFIED BY 'password';
  # drupal 7

create database wikidb;
grant index, create, select, insert, update, delete, alter, lock tables on wikidb.* to 'wikiuser'@'localhost' identified by 'password';
#mediawiki??



REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...



FLUSH privileges;
  # Reloads the privileges from the grant tables in the mysql database.

The server caches information in memory as a result of GRANT, CREATE USER, CREATE SERVER, and INSTALL PLUGIN statements. This memory is not released by the corresponding REVOKE, DROP USER, DROP SERVER, and UNINSTALL PLUGIN statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.

Table management

SHOW tables;

SELECT * from mysql.user;
  # return ascii table with rows from user table from mysql database

SELECT * from mysql.user\G;
  # return vertical row information from user table from mysql database

pager less -SFX
  # info will now be returned via less, use arrow keys to navigate large tables, q to quit this mode

nopager
  # reset output from pager to stdout

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;



13.8.1. DESCRIBE provides information about the columns in a table.


Troubleshooting


Backup and restore


mysql < database.sql
  # if database file was saved with CREATE DATABASE

mysql databasename < database.sql
  # specify database name to import database file
mysqldump
mysqldump [options] db_name [tbl_name ...]

mysqldump -u [username] -p -A -R -E --triggers --single-transaction > full_backup.sql
  # full database backup [10]
  # -A For all databases (you can also use --all-databases)
  # -R For all routines (stored procedures & triggers)
  # -E For all events
  # --single-transaction Without locking the tables i.e., without interrupting any connection (R/W).

mysqldump -u root -ppassword dbname | mysql -u root -ppassword --host=remote-server -C dbname
  # copy direct to new database instance, direct db connection
  # REMEMBER - zsh, space before the command to hide from history

mysqldump -u username -ppassword dbname | ssh user@remote.box.com mysql -u username -ppassword dbname
  # copy direct to new database instance, via ssh connection
  # REMEMBER - zsh, space before the command to hide from history
  # fails for large DBs

mysqldump -u username -ppassword dbname | gzip -c | ssh USERNAME@YOUR_TO_HOST 'cat > ~/dump.sql.gz'
  # gzip across the wire to a compressed file on the other end
  # REMEMBER - zsh, space before the command to hide from history



mysqlbackup

Install the mysql-client package to access.

For a non-busy server; 

mysqlbackup --port=3306 --protocol=tcp --user=root --password --backup-dir=/home/user/backupdir backup-and-apply-log


mysqlhotcopy


Xtrabackup
  • Percona XtraBackup - Documentation - an open-source hot backup utility for MySQL - based servers that doesn’t lock your database during the backup. It can back up data from InnoDB, XtraDB, and MyISAM tables on MySQL 5.1, 5.5, 5.6 and 5.7 servers, as well as Percona Server with XtraDB.

Replication

Tools



  • dBug - "PHP version of ColdFusion’s cfdump. Outputs colored and structured tabular variable information. Variable types supported are: Arrays, Classes/Objects, Database and XML Resources."


  • anywhereindb - Sometime we need to find out a small piece of string in big Database. Like where is the configuration is saved, or where is Jon's Date of birth is saved. This code is search all the tables and all the rows and columns in a MYSQL Database. The code is written in PHP. For faster result, we are only searching in the varchar field.








Scripts

./searchreplacedb2cli.php --host localhost --user root --database test --pass "pass"
     --charset utf\-8 --search "findMe" --replace "replaceMe"
--dry-run

Performance

Native clients

  • SQL Workbench/J - a free, DBMS-independent, cross-platform SQL query tool. It is written in Java and should run on any operating system that provides a Java Runtime Environment. Its main focus is on running SQL scripts (either interactively or as a batch) and export/import features. Graphical query building or more advanced DBA tasks are not the focus and are not planned
SQuirreL SQL Client
  • SQuirreL SQL Client - a graphical SQL client written in Java that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc.
HeidiSQL
  • HeidiSQL - a useful and reliable tool designed for web developers using the popular MySQL server, Microsoft SQL databases and PostgreSQL. It enables you to browse and edit data, create and edit tables, views, procedures, triggers and scheduled events. Also, you can export structure and data either to SQL file, clipboard or to other servers.
DBeaver
  • DBeaver - Free multi-platform database tool for developers, SQL programmers, database administrators and analysts. Supports all popular databases: MySQL, PostgreSQL, MariaDB, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Derby, etc.

Web clients

phpMyAdmin
Chive
SQL Buddy
Adminer
wget http://www.adminer.org/latest-mysql-en.php -O adminer.php
wget http://www.adminer.org/latest-en.php -O adminer.php

MariaDB

Fork of MySQL, drop in replacement.

Percona Server

SQLite




  • DB Browser for SQLite - a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.It is for users and developers wanting to create databases, search, and edit data. It uses a familiar spreadsheet-like interface, and you don't need to learn complicated SQL commands.


PostgreSQL






Ingres

Other

IBM DB2

Oracle Database

IBM System R

NoSQL


dbm

Redis

See also Server#Redis

Used in the BOA stack for caching.

CouchDB

Couchbase

MongoDB

JavaScript

MDBM


Other






  • replikativ - an open, scalable and distributive infrastructure for a data-driven community of applications. It can serve as a storage backend for your applications and make your application state always accessible on all your endpoints. For our applications it radically simplifies frontend development by streaming state changes directly into our reactive UI pipelines.


  • Fauna - the only Mission-Critical NoSQL Database that guarantees data correctness without operational complexity from the team that scaled Twitter.


to sort



  • Trousseau is a gpg encrypted key-value store designed to be a simple, safe and relient place for your data. It stores data in a single multi-recipients encrypted file and can supports both local and remote storage sources (S3 and ssh so far) import/export.

gpu;



Virtuoso Universal Server

  • http://en.wikipedia.org/wiki/Virtuoso_Universal_Server - a middleware and database engine hybrid that combines the functionality of a traditional Relational database management system (RDBMS), Object-relational database (ORDBMS), virtual database, RDF, XML, free-text, web application server and file server functionality in a single system. Rather than have dedicated servers for each of the aforementioned functionality realms, Virtuoso is a "universal server"; it enables a single multithreaded server process that implements multiple protocols. The free and open source edition of Virtuoso Universal Server is also known as OpenLink Virtuoso. The software has been developed by OpenLink Software with Kingsley Uyi Idehen and Orri Erling as the chief software architects.

Graph


  • Apache TinkerPop - a graph computing framework for both graph databases (OLTP) and graph analytic systems (OLAP).

Replication

  • http://www.symmetricds.org/ - open source software for database and file synchronization with Multi-master replication, filtered synchronization, and transformation capabilities. It is designed to scale for a large number of nodes, work across low-bandwidth connections, and withstand periods of network outage. Data synchronization occurs asynchronously from a scheduled job, with data changes being sent over a push or pull operation.
  • https://en.wikipedia.org/wiki/SymmetricDS

Distributed

H-Store

  • H-Store - an experimental main-memory, parallel database management system that is optimized for on-line transaction processing (OLTP) applications. It is a highly distributed, row-store-based relational database that runs on a cluster on shared-nothing, main memory executor nodes. The H-Store project is a collaboration between MIT, Brown University, Carnegie Mellon University, Yale University, and Intel.


HBase

RethinkDB


FoundationDB

  • FoundationDB - gives you the power of ACID transactions in a distributed database.[28]

to sort


TiDB

  • https://github.com/pingcap/tidb - an open source distributed scalable Hybrid Transactional and Analytical Processing (HTAP) database built by PingCAP. Inspired by the design of Google F1 and Google Spanner, TiDB features infinite horizontal scalability, strong consistency, and high availability. The goal of TiDB is to serve as a one-stop solution for both OLTP (Online Transactional Processing) and OLAP (Online Analytical Processing).

tidis

  • https://github.com/yongman/tidis - a Distributed NoSQL database, providing a redis-protocal api(string,list,hash,set,sorted-set), written in Go. Tidis is like TiDB layer, providing protocol transform, powered by tikv backend distributed storage which use raft for data replication and 2PC for distributed transaction. [30]

Scuttlebot

  • Scuttlebot - an open source peer-to-peer log store used as a database, identity provider, and messaging system. It features global replication, file-syncronization, and end-to-end encryption.

GUN

Social

Other




Retrieved from "https://wiki.thingsandstuff.org/index.php?title=Database&oldid=48294"