- 浏览: 178844 次
- 来自: ...
文章分类
最新评论
-
fsword:
同事拿着试了一下,说是把第五步放到第二步之前就可以了
在windows下安装ruby1.9.2,rails3.0.3和DevKit--转 -
dothwinds:
[flash=200,200][/flash][url][/u ...
打包ruby or rails成exe(英文)
A Quick Guide to SQLite and Ruby
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. The problem is that there isn’t enough documentation for Ruby users who want to take advantage of SQLite’s features.
So, let’s talk about SQLite’s handsome features:
- SQLite is swift. In my own testing, I have found it to be speedy. Some speed comparisons with MySQL and PostgreSQL are here.
- SQLite is not a large database server, such as MySQL. You don’t connect to the database. Using SQLite, you access a database file. Everything happens in-process.
- SQLite is an ACID database. Supports transactions, triggers.
- SQLite is public domain. Absolutely no licensing issues.
- SQLite is typeless. Any type or length of data may be stored in a column, regardless of the declared type. This allows extreme flexibility and avoidance of type errors.
- SQLite allows custom functions and aggregates. This is my favorite feature of SQLite, which we will explore shortly.
Getting Started with SQLite
SQLite is available for most platforms (Linux, BSD, Windows) from the download page. SQLite comes with a command-line tool for managing databases. You can find a decent tutorial for starting with SQLite here.
In RAA, you’ll find several Ruby libraries for using SQLite. 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. (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 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. I suggest sqlite-ruby, as it is a bit more feature complete. Either will work fine, but the rest of this tutorial will focus on using sqlite-ruby.
Creating a Database
To open a new database (or an existing one), simply instantiate a SQLite::Database
object with the name of the database
file:
require 'sqlite' db = SQLite::Database.new( 'sample.db', 0644 )
According 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. All databases are opened for both reading and writing, though it is anticipated that readonly databases could be added in the future.
SQLite stores all of the data for a database inside a single file. This encompasses all indices, tables and schemas for the entire database. The advantage is that this single file can be easily transported wherever you like. The same database file can be included with your software and accessed on Windows, Linux, or any other supported platform.
The disadvantage to a single database file is that this file can grow quite
large. Even after you’ve deleted rows or entire tables, your file may not
decrease in size. To free the disk space once again, you’ll need to execute the
VACUUM
statement, which cleans up tables and
indices. The VACUUM
statement can be run
alone to clean the whole database.
Passing Queries to SQLite
The execute
method can be used to pass
queries to your database, once it is open.
db.execute <<SQL CREATE TABLE sites ( idx INTEGER PRIMARY KEY, url VARCHAR(255) ); SQL
You can also test the completeness of your SQL
statements with the complete?
methods.
>> db.complete? "SELECT *" => false >> db.complete? "SELECT * FROM email;" => true
On its own, execute
will simply return
an Array of Hashes as the resultset. Passing a block into execute
will cause the block to be called on each
successive loading of a row. In such a case, it becomes a sort of “each_row” for
a query, each time receiving a Hash of field-value pairs.
db.execute( "SELECT * FROM sites;" ) do |site| puts "-> Site #%d %s" % [ site['idx'], site['url'] ] end
Vital Pragma
SQLite has a few features enabled by default that you might consider disabling. These are optimizations that have consequences and I present them for your careful thought. I am giving you the basics. Futher optimizations can be had at the SQLite Optimization FAQ.
The cache_size
setting determines how
many database pages can be kept in memory. The default settings is 2000
, counted in 1KB chunks. Consider increasing this
before executing queries on large sets of data. (Especially updates to large
tables.) This setting can dramatically speedup such situations. Use PRAGMA cache_size
to set.
By calling PRAGMA
default_synchronous=OFF;
, you can turn off the intensive
synchronization of the database. When set, queries will wait for a database to
be completely written before executing. On truly mission-critical apps, this may
be necessary, but generally you can turn this off.
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. A smaller speedup in this case, but still worth
noting.
Custom Functions
SQLite comes with a variety of common functions for forming expressions. For example, you may want to uppercase a field you are reading:
db.execute( "SELECT UPPER(url) FROM sites;" )
You can add your own Ruby functions to SQLite by using the create_function
method. To make our own function for
reversing a field’s contents:
db.create_function( 'revers', 1, proc { |ctx,s| s.to_s.reverse }, nil )
The first parameter we pass in is the name of the function to create. SQLite will ignore casing of this string. The second parameter indicates the number of parameters to send to the function. The third parameter is a Proc object. The fourth parameter should allow you to pass further data into the Proc, but doesn’t appear to be implemented at the time of this writing.
The proc object you create should receive an extra initial argument, listed
above as ctx
. This is a SQLite::Context
object, which allows you store data
between calls. I’ve found this object to be quite buggy when used in functions.
But, hey, it’s there.
To call our new revers
function:
db.execute( "SELECT REVERS(url) FROM sites;" )
One thing to note about the create_function
method is that your proc should not
return any sort of object which is a collection (Array, Hash, etc.) The object
won’t make the translation in and out of the database.
Like Ruby, you may also override the current set of functions. For example,
the @Y LIKE X@ syntax is syntactical sugar for
the like(X,Y)= function. If you want to support regular
expressions in your =LIKE
statement, you could override
LIKE to do so:
like_function = proc do |ctx, x, y| 1 if /#{ x }/ =~ y end db.create_function( 'like', 2, like_function, nil ) db.execute( "SELECT url FROM sites WHERE url LIKE '^http:'" )
Custom Aggregates
Aggregates are similiar to functions, but their return is totaled for a set
of rows. If you’ve used much SQL, you’ve seen these
before in the form of count
, avg
, or sum
functions.
To create an aggregate, you provide two procs. One which is called for each row like a function. The other proc is called upon completion of the query and provides a final total.
sum_up_1 = proc do |ctx, a| ctx.properties["sum"] ||= 0 ctx.properties["sum"] += a.length end sum_up_2 = proc do |ctx| ctx.properties["sum"] end db.create_aggregate( 'letter_count', 1, sum_up_1, sum_up_2, nil ) db.execute( "SELECT LETTER_COUNT(address) FROM email" )
The above code totals the letter count for all of the address fields in a set of rows.
So how does SQLite do this? Remember that since SQLite is executed in-process, you can pass memory addresses to it. A function pointer is passed inside the SQLite extension, which calls your proc. I haven’t done any benchmarking, but I imagine the figures are pretty tight for these calls.
Storing Binary Data
Storing binary data is a big use case for SQLite. If I was going to write an adventure game in Ruby, I would lodge all my scenes and characters in an SQLite database.
But remember I said that SQLite was typeless? This means that you
can’t get away with storing binary data in a BLOB.
BLOBs, CHARs, TEXTs are all the
same datatypes which only store null-terminated strings. SQLite comes with two
API functions, sqlite_encode_binary
and sqlite_decode_binary
, but these aren’t implemented in
any Ruby APIs currently.
A quick solution is to use Ruby’s base64
library. Really, base64
is a bit much, since we really only need to
escape ’\000’ (which is what sqlite_encode_binary
does). Until we can get those
function exposed, though, certainly use base64
.
Let’s declare our table with a BLOB to indicate that we plan to store binary data and to give our table some degree of portability.
db.execute << SQL CREATE TABLE scenes ( idx INTEGER PRIMARY KEY, background_png BLOB ); SQL
To store binary data in our table:
require 'base64' background_png = File.open( 'background.png' ).read db.execute( "INSERT INTO scenes (background_png) VALUES " + "('#{ encode64( background_png ) }');" )
To read binary data from our table and write it out to files:
db.execute( "SELECT * FROM scenes" ) do |scene| background_png = decode64( scene['background_png'] ) File.open( "back-#{ idx }.png", "w" ) do |back_out| back_out << background_png end end
Alternatively (if you’re mental), you could load the schema for your database
and parse out the blobs. Try this query, after creating the scenes
table:
SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE tbl_name = 'scenes' AND type != 'meta'
You’ll receive the CREATE TABLE
statement we used to create the table. BLOBs could be
parsed out when the database is loaded and handled differently. (To myself:
why am I even suggesting this?! Probably to demonstrate metadata access
without having to write a new section on it!)
Conclusion
Hopefully this is a fitting introduction to SQLite in Ruby. If not, please contact me and spew wisdom.
发表评论
-
ruby知识点滴
2011-04-25 16:20 7211、加入文件所在路径 $: << (" ... -
ruby读取SQLite的图片数据并插入word中
2011-04-24 11:18 1133表的结构请参看 http://xineohpanihc ... -
SQLite/Ruby FAQ --转
2011-04-24 10:18 935SQLite/Ruby FAQ How do I ... -
ruby 将图片读入sqlite,再取出
2011-04-24 09:32 1574# -*- coding: utf-8 -*- req ... -
ruby 读写图片文件
2011-04-24 09:25 1523# -*- coding: utf-8 -*- f ... -
ruby textfile vs binaryfile
2011-04-23 22:05 970The Difference Between Binar ... -
在windows下安装ruby1.9.2,rails3.0.3和DevKit--转
2011-04-23 15:54 1865在windows下安装ruby1.9.2,rails3 ... -
ruby操作word--输入文字和设置格式
2011-04-23 10:19 1722# coding: utf-8require "wi ... -
ruby操作sqlite
2011-04-23 10:14 879require 'sqlite3'db = SQLite3:: ... -
Ruby操作word时遇到中文问题
2011-04-22 17:56 754# coding: utf-8 require &qu ... -
打包ruby or rails成exe(英文)
2008-11-14 12:34 2250ruby2exe有两种选择: 1、使用rubyscript2e ... -
Ruby操作excel
2008-11-13 16:34 3698一、新建并打开excel文件 #newEmptyExcel.r ...
相关推荐
### SQLite编译与应用实例详解 #### 一、SQLite简介 SQLite是一款轻量级的嵌入式数据库系统,它以C语言编写,为多种应用程序提供可靠的数据存储支持。该数据库的特点在于无需单独的服务器进程或者管理员权限即可...
#### 八、SQLite3命令行操作示例 - **创建数据库和表:** ```sql sqlite3 ex1 SQLite version 3.3.17 Enter ".help" for instructions sqlite> create table tbl1 (one varchar(10), two smallint); sqlite> ...
4. **数据库存储**:ExceptionTrack将异常信息存储在数据库中,可能是为了方便持久化和批量分析,常见的数据库如MySQL、PostgreSQL或SQLite可以被Rails应用集成。 5. **调试与错误追踪**:通过ExceptionTrack,...
### Win7 64位Qt5.4.0静态编译配置详解 #### 系统环境及准备 本文档详细介绍了在Windows 7 64位系统环境下进行Qt5.4.0静态编译的步骤。为了顺利完成编译工作,需要预先安装一系列支持工具,并对环境变量进行必要的...
2. **依赖工具准备**:为了能够顺利地完成编译工作,还需要安装Ruby、Perl、Python等辅助工具,并确保它们达到Qt5.12.0所要求的版本。此外,Visual Studio 2017也需要准备好,它是本次编译的核心工具。这些工具的...
### osgEarth入门教程知识点详解 #### 一、OSGEARTH安装流程详解 ##### 1.1 获取源码 - **Option 1: 使用GIT** - **简介**:`osgEarth`源码托管于GitHub,因此需要使用Git客户端进行源码获取。 - **推荐工具**...
### osgEarth入门开发指南知识点详解 #### 一、OSGEARTH安装 **1.1 获取源码** **Option 1: 使用GIT** - **简介**:osgEarth源代码托管于GitHub,因此首先需要一个Git客户端。 - **推荐工具**:在Windows环境下...
8. **数据存储**:可能涉及到本地存储(如SQLite)或远程数据库(如MySQL、PostgreSQL)的操作,理解SQL查询。 9. **测试**:了解如何使用RSpec或其他测试框架编写单元测试和集成测试,确保代码质量。 10. **文档**...
### Redmine 安装与使用详解 #### 一、Redmine简介 Redmine是一款功能强大的基于Web的项目管理和问题追踪工具。它采用Ruby on Rails框架进行开发,支持跨平台部署,能够很好地兼容多种数据库系统。相比于传统的...
### Titanium开发者平台知识点详解 #### 一、平台概述 Titanium开发者平台是一个完全免费且开源的应用程序开发框架,它允许开发者使用自己熟悉的Web技术(如JavaScript、HTML、CSS、Python、Ruby以及PHP)来构建跨...
### Android学习之路——核心知识点详解 #### 一、Java基础 **重要性**: Java是Android应用开发的基础语言,没有良好的Java基础,很难深入理解和开发高质量的Android应用。 **知识点**: 1. **Java基础语法**: ...
**Shoppy:图灵社区之夜的Sinatra应用详解** Shoppy是一个基于Sinatra框架的微型Web应用程序,设计用于在内存中存储购物清单。这个项目在图灵社区之夜被提出,作为一个初学者友好的Ruby编程练习,帮助参与者了解...
ActiveRecord是Rails中的ORM(对象关系映射)工具,使得开发者可以用Ruby代码操作数据库。 4. **路由**:Rails的路由系统定义了URL到控制器动作的映射,允许用户通过HTTP请求执行不同的操作。在`config/routes.rb`...
”应用详解》 Ruby on Rails(简称Rails)是一种基于Ruby语言的开源Web开发框架,它遵循MVC(模型-视图-控制器)架构模式,旨在提升开发效率和代码的可读性。在本文中,我们将深入探讨如何使用Rails创建一个基础的...
《Ruby on Rails 教程:静态样本应用详解》 Ruby on Rails(简称Rails)是一款基于Ruby语言的开源Web开发框架,以其MVC(Model-View-Controller)架构、DRY(Don't Repeat Yourself)原则以及“Convention over ...
【个人博客源码详解】 在IT领域,个人博客源码是一种非常重要的资源,它为开发者提供了自定义和构建个性化博客的可能。"个人博客源码,不错的网站"这个标题表明这是一个高质量的博客源码,适合用于创建具有独特特性...
以下是该文的知识点详解: 1. **选择编程语言**:首先推荐学习一门脚本语言,如Python或Ruby。Python因其简洁易懂的语法和强大的库支持,是初学者的首选。通过编写处理文本文件、CSV数据、读取本地文件、遍历文件...