5 reasons why you should use SQLite in 2016
What the? You don't know sqlite yet? So I'm telling you, SQLite is a terrific database that can do real work in a real production environment. In this article, I will briefly explain the 5 reasons why I think you should use SQLite in 2016.
1. Simple management. Have you managed the Postgres database before? In order to ensure the correct configuration of the database server (shared buffer, effective cache size, work mem, maintain working mem,wal buffer ...) ), you need to understand and learn quite a lot of things. Upgrade can be said to be a terrible process, and you may need to use the database offline, run a special upgrade program, and then silently look forward to in the heart when you back up, it can continue to work. Besides, do you know exactly where your postgres database is stored? Can you just point to a place and say, "That's My database"? (It is well known that there are many cases where you can only use Postgres (or mysql,oracle,sql server, etc.) to meet the needs of your application. But the purpose of what I say here is not so. I just want to highlight the difference between managing SQLite databases and other typical database servers. ）
SQLite is easy to manage-because it is a single file (or at some point a file + transaction log). The file format is very stable on many major versions, so suppose I have a SQLite database file starting with the 3.0.0 Version (2004), then I can also read it using the latest SQLite 3.10.0. If I want to put the database file on a USB flash drive, I just need to copy the file, or better yet save the file in the Dropbox folder. If I want to back up every night, then I just synchronize the database files to S3. If I want to share some data analysis with colleagues, then you can send a copy of the database file to them, and then they can use it. The database exists in the form of a single file and has a stable format, which is the feature of SQLite.
More importantly, SQLite is easy to configure. The functionality of SQLite can be managed in two ways: compile flags and pragma statements (run-time configuration). Without the so-called configuration file, you just need to build the library you want, and then configure the run-time options when you create a database connection.
2. Continuous improvement and rock-solid stability. SQLite is actively developed by some really great software engineers. New high-quality features are being added at an alarming rate. Just recently, SQLite has added support for JSON data through the json1 extension. SQLite also released an improved version of the full-text search extension, including results rankings using the BM25 algorithm. In addition to adding new features, SQLite developers are also working to make the library more performance. In the 3.8.11 release, the release notes include this small description: SQLite now runs at 3.8 times the 0 version twice times the 3.3.9-fold version.
Although there have been so many changes and improvements, SQLite rarely introduces bugs. SQLite's test suites are widely regarded as one of the best test suites in the industry, and the pages about how SQLite tests are often present on hackernews, as developers wave upon wave to discover this impressive document.
3. Scalable and controllable. About SQLite, my personal favorite feature is its extensibility. Since SQLite is embedded in the application, it runs in the same address space and can execute the application code on your behalf. Whether it's the Python standard library SQLite driver--pysqlite or the optional drive ASPW, you can provide APIs that define custom SQL functions, aggregate functions, and collations. ASPW further, the provided API can also be used to define virtual tables and virtual file systems! A virtual table, currently uniquely supported by ASPW, allows you to define a table in your code and then query it as if it were a normal SQL table, even though the backup data might be completely dynamic. For example, I wrote a simple virtual table that allows you to query Redis as if it were a SQL table.
You can also write a function with the same name, which is a function that returns the result of a 0...N row. One example is a regular expression retrieval function that processes input and produces a matching token row. I wrote a library, Sqlite-vtfunc, which makes it very easy for us to write these types of functions. In fact, every aspect of SQLite is manipulated by your application.
4. Fast as lightning. SQLite is very fast. Because it runs on the same machine, there is no network burden when executing a query or reading the results. And because it runs in the same address space, there is no line protocol, serialization, or communication through a UNIX socket. SQLite can also run on mobile devices when resources are scarce and efficiency is critical. SQLite also supports a large number of compilation identifiers that allow you to remove features that you do not intend to use.
The speed of sqlite compensates for one of its biggest shortcomings, that is, database file lock write. Incredibly fast write data, database locking becomes a problem only when there is a large number of concurrent write services available.
5, Wal mode. The 3.7.0 version of SQLite complements a new logging method that leverages pre-written logs. While it's not really exciting news for itself, it means that the Web application developer (or anyone who handles concurrency) implies that the read service no longer blocks the write service and vice versa. In other words, reading and writing can occur simultaneously. Without the Wal pattern, to write to the database, the write service needs exclusive access to the database and cannot read the service until the end of the written service. However, it is worth mentioning that even if you do not enable the Wal mode, the write service usually occurs in milliseconds. This time is so short that you will notice problems only when you are very high or write a very long transaction.
Bonus points Reason: BerkeleyDB. BerkeleyDB's SQLite integration gives application developers the concurrency of database access and even better performance, because it is not locking the entire database, BerkeleyDB only need to lock individual pages. This allows the berkeleydb to scale more efficiently under concurrent database load, providing transactions that do not compete for data on the same page. BerkeleyDB also supports multiple versions of concurrency control (MVCC), allowing read operations to continue to occur on data pages and to process data through a write transaction.
BerkeleyDB also has the advantage of improving efficiency. In other words, BerkeleyDB can use fewer system resources to perform fewer system calls. You can read more details in this white paper and in this brief technical overview. BerkeleyDB's SQL interface is a plug-in replacement for SQLite and supports the same APIs and features. BerkeleyDB provides some additional features, such as Replication (SQLite has a backup tool, but I don't think it's as powerful as BDB), encryption, and of course all the features of BerkeleyDB itself.
One of the main drawbacks of using berkeleydb is that it is very sensitive to configuration values and requires a deep knowledge to get the correct page size, cache size, and other settings. Another drawback is the license-read more about the BerkeleyDB license and view the Oracle License page.
Finally, I hope you can try SQLite. Don't listen to the conservative people who say it's not good for production, or that it's not suitable for use in Web applications.
If you want to learn more, SQLite itself has a good description of when to use SQLite documentation, which also includes a series of scenarios to make another RDBMS work better.
Brotherhood high Lopheng free an apprentice: http://www.hdb.com/party/lzcw-comm.html
Free to receive the lamp brother even original PHP video tutorial CD/"Detailed PHP" Essentials Edition, details of the website customer service:
Http://www.lampbrother.net
"Brother Even it education" learn PHP, Linux, HTML5, UI, Android and other video tutorials (Courseware + notes + video)!
Web Drive Tutorial Download: Http://pan.baidu.com/s/1sjOJiAL
The above describes 5 reasons why you should use SQLite for 2016 years, including the contents of the content, I hope that the PHP tutorial interested in a friend helpful.
