https://proglib.io/p/11-tipov-sovremennyh-baz-dannyh-kratkie-opisaniya-shemy-i-primery-bd-2020-01-07 (отдельно в раздел основы БД)
ACID – https://habr.com/ru/articles/535616/ (отдельно в раздел основы БД)
PEP 249 – Python Database API Specification v2.0
Как поднять базу данных
Основные СУБД
-
Различия между основными СУБД
№ SQLite MySQL PostgreSQL MariaDB 1. SQLite is a self-contained, file-based, and fully open-source RDBMS known for its portability, reliability, and strong performance even in low-memory environments.
SQLite strives to provide local data storage for individual applications and devices. SQLite emphasizes economy, efficiency, reliability, independence, and simplicity.
SQLite does not compete with client/server databases. SQLite competes with fopen().It is the most popular Database. It is the most advanced Database. It is a MySQL application compatible open source RDBMS, enhanced with high availability, security, interoperability and performance capabilities. 2. The SQLite project’s website
describes it as a “serverless” database. Most relational database engines are implemented as a server process in which programs communicate with the host server through an interprocess communication that relays requests. In contrast, SQLite allows any process that accesses the database to read and write to the database disk file directly. This simplifies SQLite’s setup process, since it eliminates any need to configure a server process. Likewise, there’s no configuration necessary for programs that will use the SQLite database: all they need is access to the disk.3. It is a relational-based DBMS. It is an object-based relational DBMS The primary database model for MariaDB is Relational DBMS. 4. It was developed in 1995 by a Swedish company named MySQL AB. It was developed at the University of California, Department of Computer Science. Developed by MariaDB Corporation Ab and MariaDB Foundation on 2009. 5. The implementation language is C/C++ The implementation language is C MariaDB is written in C and C++ languages. 6. Its transactions are ACID-compliant, even in cases where the system crashes or undergoes a power outage. It is an ACID-compliant only when used with InnoDB and NDB cluster engines. ACID stands for Atomicity, Consistency, Isolation and Durability. It is an ACID-compliant from the ground up. 7. It does not support partial, bitmap, and expression indexes. It supports all of these 8. It doesn’t provide support for Materialised views and Table inheritance. PostgreSQL provides both of them. 9. MySQL supports various data types that help a variety of applications store and process data in different formats, including the following: traditional data types to store Integers, Characters or Strings, Date with Timestamps and Time Zones, Boolean, Float, Decimal, Large Text, and BLOB to store binary data (like images). There is no support for geometric data types in MySQL. PostgreSQL not only supports traditional SQL data types (e.g., Numeric, Strings, Date, Decimal, etc.) but also supports unstructured data types (e.g., JSON, XML, and HSTORE) as well as network data types, bit strings, etc. What makes PostgreSQL stand out is its support for a wider range of data types, such as ARRAYs, NETWORK types, and Geometric data types (including advanced spatial data functions) to store and process spatial data. Supported data types can be found here. The support for spatial data types and functions comes from an external module called PostGIS, which is an open-source extension. 10. SQL provides limited MVCC support ( in InnoDB) Full multi-version concurrency control (MVCC) support. 11. It is best suitable for simple operations like write and reading. It is commonly used for large and complex operations. 12. In MySQL, every connection created is an OS thread. In PostgreSQL, every connection created is an OS process. 13. It has geo-tagging support. 14. MySQL has limited NoSQL capabilities. JSON data types have been introduced in MySQL from Version 5.7 and have a long way to go to become more mature. PostgreSQL has become a very popular NoSQL choice for developers in the last years and has enormous JSON capabilities. With JSON and JSONB data types, JSON-based data operations can be significantly faster and more efficient. JSON data can also be Indexed with B-Tree and GIN for improved searches, and XML and HSTORE data types can handle XML formats and other complex text-format data as well. With the support for spatial data types, PostgreSQL is no doubt a complete multi-model database. Advantages of SQLite
**
Small footprint
: As its name implies, the SQLite library is very lightweight. Although the space it uses varies depending on the system where it’s installed, it can take up less than 600KiB of space. Additionally, it’s fully self-contained, meaning there aren’t any external dependencies you have to install on your system for SQLite to work.
User-friendly
: SQLite is sometimes described as a “zero-configuration” database that’s ready for use out of the box. SQLite doesn’t run as a server process, which means that it never needs to be stopped, started, or restarted and doesn’t come with any configuration files that need to be managed. These features help to streamline the path from installing SQLite to integrating it with an application.
Portable:
**Unlike other database management systems, which typically store data as a large batch of separate files, an entire SQLite database is stored in a single file. This file can be located anywhere in a directory hierarchy, and can be shared via removable media or fileDisadvantages of SQLite
**
Limited concurrency
: Although multiple processes can access and query an SQLite database at the same time, only one process can make changes to the database at any given time. This means that while SQLite supports greater concurrency than most other embedded database management systems, it cannot support as much as client/server RDBMSs like MySQL or PostgreSQL.
No user management
: Database systems often come with support for users, or managed connections with predefined access privileges to the database and tables. Because SQLite reads and writes directly to an ordinary disk file, the only applicable access permissions are the typical access permissions of the underlying operating system. This makes SQLite a poor choice for applications that require multiple users with special access permissions.
Security
**: A database engine that uses a server can, in some instances, provide better protection from bugs in the client application than a serverless database like SQLite. For example, stray pointers in a client cannot corrupt memory on the server. Also, because a server is a single persistent process, a client-server database can control data access with more precision than a serverless database. This allows for more fine-grained locking and better concurrency.When To Use SQLite
**
Embedded applications
: SQLite is a great choice of database for applications that need portability and don’t require future expansion. Examples include single-user local applications, mobile applications, or games.
Disk access replacement
: In cases where an application needs to read and write files to disk directly, it can be beneficial to use SQLite for the additional functionality and simplicity that comes with using SQL.
Testing:
**For many applications it can be overkill to test their functionality with a DBMS that uses an additional server process. SQLite has an in-memory mode which can be used to run tests quickly without the overhead of actual database operations, making it an ideal choice for testing.When Not To Use SQLite
**
Working with lots of data:
SQLite can technically support a database up to 140TB in size, as long as the disk drive and file system also support the database’s size requirements. However, the SQLite website recommends that any database approaching 1TB be housed on a centralized client-server database, as an SQLite database of that size or larger would be difficult to manage.
High write volumes
: SQLite allows only one write operation to take place at any given time, which significantly limits its throughput. If your application requires lots of write operations or multiple concurrent writers, SQLite may not be adequate for your needs.
Network access is required
**: Because SQLite is a serverless database, it doesn’t provide direct network access to its data. This access is built into the application. If the data in SQLite is located on a separate machine from the application, it will require a high bandwidth engine-to-disk link across the network. This is an expensive, inefficient solution, and in such cases a client-server DBMS may be a better choice.
Основные типы данных в БД
MySQL :: MySQL 8.0 Reference Manual :: 11 Data Types
MySQL supports SQL data types in several categories: numeric types, date and time types, string (character and byte) types, spatial types, and the data type.
https://dev.mysql.com/doc/refman/8.0/en/data-types.html
Посмотреть типы данных в реальной базе Postgres при помощи pgAdmin (потому что в справочнике их просто уйма!)
Chapter 8. Data Types
has a rich set of native data types available to users.
https://www.postgresql.org/docs/9.5/datatype.html
Типы связей в реляционных БД
Один к одному (one-to-one) – используется не часто. Один ко многим (one-to-many) Многие ко мнигим (many-to-many) – нужна таблица-посредник.
📂 Базы данных | Последнее изменение: 11.08.2024 10:41