![]() ![]() SQLite comes with a variety of common functions for forming expressions. A smaller speedup in this case, but still worth noting. If you're not worried about how many rows are affected following an UPDATE or INSERT, consider using PRAGMA count_changes=OFF, which will disable counting of affected rows. On truly mission-critical apps, this may be necessary, but generally you can turn this off. When set, queries will wait for a database to be completely written before executing. Use PRAGMA cache_size to set.īy calling PRAGMA default_synchronous=OFF, you can turn off the intensive synchronization of the database. (Especially updates to large tables.) This setting can dramatically speedup such situations. Consider increasing this before executing queries on large sets of data. The default settings is 2000, counted in 1KB chunks. The cache_size setting determines how many database pages can be kept in memory. Futher optimizations can be had at the SQLite Optimization FAQ. These are optimizations that have consequences and I present them for your careful thought. SQLite has a few features enabled by default that you might consider disabling. In such a case, it becomes a sort of "each_row" for a query, each time receiving a Hash of field-value pairs.ĭb.execute( "SELECT * FROM sites " ) do |site| Passing a block into execute will cause the block to be called on each successive loading of a row. On its own, execute will simply return an Array of Hashes as the resultset. The execute method can be used to pass queries to your database, once it is open. The VACUUM statement can be run alone to clean the whole database. To free the disk space once again, you'll need to execute the VACUUM statement, which cleans up tables and indices. Even after you've deleted rows or entire tables, your file may not decrease in size. The disadvantage to a single database file is that this file can grow quite large. The same database file can be included with your software and accessed on Windows, Linux, or any other supported platform. ![]() The advantage is that this single file can be easily transported wherever you like. ![]() This encompasses all indices, tables and schemas for the entire database. SQLite stores all of the data for a database inside a single file. All databases are opened for both reading and writing, though it is anticipated that readonly databases could be added in the future. To open a new database (or an existing one), simply instantiate a SQLite::Database object with the name of the database file:ĭb = SQLite::Database.new( 'sample.db', 0644 )Īccording to SQLite docs, the second argument passed to the constructor is "intended to signal whether the database is going to be used for reading and writing or just for reading." But in current implementations, this argument is ignored. Either will work fine, but the rest of this tutorial will focus on using sqlite-ruby. I suggest sqlite-ruby, as it is a bit more feature complete. The other two libraries (ruby-sqlite and sqlite-ruby) have custom APIs for accessing SQLite, which will allow us to add custom functions and aggregates, as well as set table metadata. (If you plan on using ruby-dbi, I would be aware of how SQLite compares to SQL-92, so your queries can be portable as well.) The ruby-dbi module is great if you want your code to work if you switch databases, but you're hampered in using some of SQLite's features. In RAA, you'll find several Ruby libraries for using SQLite. You can find a decent tutorial for starting with SQLite here. SQLite comes with a command-line tool for managing databases. SQLite is available for most platforms (Linux, BSD, Windows) from the download page. This is my favorite feature of SQLite, which we will explore shortly. SQLite allows custom functions and aggregates.This allows extreme flexibility and avoidance of type errors. Any type or length of data may be stored in a column, regardless of the declared type. Using SQLite, you access a database file. SQLite is not a large database server, such as MySQL.Some speed comparisons with MySQL and PostgreSQL are here. In my own testing, I have found it to be speedy. So, let's talk about SQLite's handsome features: The problem is that there isn't enough documentation for Ruby users who want to take advantage of SQLite's features. I spent last night playing with SQLite and am convinced that this is a tool which could prove incredibly useful to coders and a great tool for learners to check out SQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |