🔗 Collection of Insane and Fun Facts About SQLite

1. Most Deployed and Used Database
   SQLite is the most deployed and commonly used database, with over one trillion databases in active use. It is maintained by three people who don't allow outside contributions.

2. Extensive Usage
   SQLite is used more than all other database engines combined, with billions of copies in use.

   

3. Top Software Module
   SQLite is likely among the top five most deployed software modules.

   

4. Company Behind SQLite
   Hwaci is the company behind SQLite and also engages in music.

   

5. Origin on a US Warship
   D. Richard Hipp (DRH) initiated SQLite while building software for the USS Oscar Austin because the server dependencies were unreliable. DRH realized the need for a server-less database.

   "Why do we even need a server? Why can't I pull this directly off the disk drive? That way if the computer is healthy enough, ..."

6. Unique Licensing
   Unlike standard open-source projects, SQLite is not under an OSI-approved license. It's in the public domain but sells licenses, such as the 'Warranty of Title'.

   

7. No Outside Contributions
   Contributions to SQLite are invite-only, and submissions require dedication to the public domain.

   

8. Testing Philosophy
   SQLite has extensive test coverage, with over 600 lines of test code per code line. The tests include fuzz tests, regression tests, and more.

9. Proprietary Test Suite
   Some tests, like the TH3 (Test Harness 3), are proprietary and achieve 100% branch coverage. Access requires membership in the SQLite Consortium.

10. Business Model
    Revenue sources include licenses, support, maintenance, consortium membership, and commercial extensions.

11. Code of Ethics
    SQLite has its Code of Ethics derived from the Rule of St. Benedict instead of a standard Code of Conduct.

    

12. Unique Blessing
    Each source code file includes a blessing.

    

13. Fast Performance
    SQLite's speed rivals fopen; for some use cases, it's up to 35% faster than filesystem operations.

    

14. SQLite vs Redis
    SQLite can outperform Redis for some use cases due to lesser network and serialization overheads.

    

15. Single Writer Model
    SQLite initially supported only one writer, but the WAL mode introduced in 2010 allows for concurrent readers.

16. Unique Features

    • Defaults to rollback journal mode.
    • Foreign keys are disabled by default.
    • Uses "type affinity" instead of strong typing, allowing flexible data insertion.

    

    CREATE TABLE user(id INTEGER);
    INSERT into user VALUES ("YOLO!"); -- This works!
    CREATE TABLE t(value TIMMYSTAMP);
    

17. Type Affinity Quirks
    Declared types like "FLOATING POINT" give INTEGER affinity due to substring matching.

    CREATE TABLE t(value SPONGEBLOB) -- This is BLOB type!
    

    "FLOATING POINT" would give INTEGER affinity, not REAL."

18. Lore on Prefix Change
    SQLite changed its default prefix due to user confusion.

    

19. Backward Compatibility
    SQLite maintains backward compatibility since version 3.0.0.

20. Persisting Bugs for Compatibility
    They won't fix certain bugs to maintain backward compatibility.

    

21. SQLite's Version Control and Parser
    DRH developed Fossil and the Lemon parser generator.

22. Developing on a Plane
    DRH coded the B-Tree algorithm from Donald Knuth's TAOCP book while traveling.

23. Pronunciation Debate
    SQLite can be pronounced "S-Q-L-ite," and DRH is open to various pronunciations.

    "I wrote SQLite, and I think it should be pronounced 'S-Q-L-ite.' Like a mineral..."

continue reading on avi.im

⚠️ This post links to an external website. ⚠️