Wednesday, 11 July 2007

initial observation of sqlite

Now-a-days i see many OSS projects adopting sqlite for their needs. One good example would be mozilla's firefox 3. Firefox is not a database centric project, but they do use it for their internal use in firefox3 . It seems to be the db of choice for use as embedded db. I dont know how far Oracle is going to tackle this. On the side of oracle they have many recently bought companies that deal in this area. Embedded db would be an interesting area to watch.

Now to some technical details i have found on sqlite.

SQLite has

  • incomplete support of:
    • triggers
    • Foreign keys
    • ALTER tables (only rename & add are supported)(not supported include drop, alter, add , etc)
    • joins
    • views (they are read only)
  • Not supported
    • Nested transaction. Only one transaction are allowed at a time.
    • Right outer join & full outer join.
    • Permissions such as grant and revoke . Since its a embedded db engine, such feature are highly unlikely to implemented.
Limits


  • Maximum length of a string or BLOB = 2147483647 Bytes
  • Maximum Number Of Columns = 32676 , default max = 2000
  • Maximum Number Of Tables In A Join = 64 tables
  • Default value of Maximum Depth Of An Expression Tree = 1000.
  • Maximum Number Of Attached Databases = 62 in 64-bit & 30 in 32-bit
  • Maximum Database Page Size = 32768
  • Maximum Number of Pages In a database file is normally set to few kilo bytes of memory or few megabytes. Currently it does not allow gigabyte sized memory.

Advantages

  • no setup or administration
  • Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.
  • Implements most of SQL92
  • A complete database is stored in a single disk file. Database files can be freely shared between machines with different byte orders.
  • Supports terabyte-sized databases and gigabyte-sized strings and blobs.
  • Small code footprint: less than 250KiB fully configured or less than 150KiB with optional features omitted.
  • Well-commented source code with over 98% test coverage. Available as a single ANSI-C source-code file that you can easily drop into another project. Sources are in the public domain. Use for any purpose.


Source : http://www.sqlite.org/

No comments:

Post a Comment