Initial release of amalgalite - v0.1.0

Ruby
1

Amalgalite embeds the SQLite database engine in a ruby extension.

http://copiousfreetime.org/articles/2008/06/21/amalgalite-0-1-0-released.html

I would consider Amalgalite beta right now. It is fully usable; but it
does not
contain all the features I want yet.

Give it a whirl and let me know what you think.

Current Functionality

The normal stuff:

  • open or create databases
  • run SQL in single statements or in batches.
  • use prepared statements with parameters
  • access result sets by index or by column name
  • transactions
  • type conversion between database types and Ruby types

The fun/happy/cool stuff

  • trace information
  • profile information
  • read only databases
  • a type conversion protocol

Future plans

  • ActiveRecord driver
  • DataMapper driver
  • Sequel driver
  • progress and authorizer callbacks
  • ‘amalgalite’ command line tool similar to the ‘sqlite3’ command line
    tool
  • utf-16 support
  • blob support
  • and more

{{ Release notes for Version 0.1.0 }}

  • Initial public release
2

Jeremy H. wrote:

Amalgalite embeds the SQLite database engine in a ruby extension.

Is that really better than linking with libsqlite?

3

Jeremy H. wrote:

Also, eventually I’m going to expose two SQLite extensions, full text
search and R-trees. I’m sure everyone knows what full text search is
for, and the R-tree extension allows for extremely fast spatial
searching, i.e. latitude, longitude.

You mean like postgis, but in a sqlite sized package? That would be
very, very cool. I didn’t know sqlite even had such an extension.

4

On Sun, Jun 22, 2008 at 10:46:28AM +0900, Joel VanderWerf wrote:

Jeremy H. wrote:

Also, eventually I’m going to expose two SQLite extensions, full text
search and R-trees. I’m sure everyone knows what full text search is
for, and the R-tree extension allows for extremely fast spatial
searching, i.e. latitude, longitude.

You mean like postgis, but in a sqlite sized package? That would be very,
very cool. I didn’t know sqlite even had such an extension.

Yup, its in the source tree but not part of the released version of
SQLite yet.

http://www.sqlite.org/cvstrac/dir?d=sqlite%2Fext%2Frtree

I’m guessing it’ll be in the next official release.

enjoy,

-jeremy

5

Jeremy H. [email protected] wrote:

Amalgalite embeds the SQLite database engine in a ruby extension.

I’ve downloaded the gem, however install failed :
~/gems%> sudo gem install -l amalgalite
Password:
ERROR: Error installing amalgalite:
amalgalite requires configuration (>= 0.0.5)

if i try to install configuration, i get :
~/src/ruby/rubygems-1.2.0%> sudo gem install -r configuration
Password:
Bulk updating Gem source index for: http://gems.rubyforge.org/
ERROR: could not find configuration locally or in a repository

are u using the latest rubygems vesion ?

i’m running MacOS X 10.4.11
$> ruby --version
ruby 1.8.6 (2007-09-24 patchlevel 111) [powerpc-darwin8.11.0]
$ gem env
RubyGems Environment:

  • RUBYGEMS VERSION: 1.1.0 (1.1.0)
  • RUBY VERSION: 1.8.6 (2007-09-24 patchlevel 111)
    [powerpc-darwin8.11.0]
  • INSTALLATION DIRECTORY: /opt/local/lib/ruby/gems/1.8
  • RUBY EXECUTABLE: /opt/local/bin/ruby
  • RUBYGEMS PLATFORMS:
    • ruby
    • powerpc-darwin-8
  • GEM PATHS:
    • /opt/local/lib/ruby/gems/1.8
  • GEM CONFIGURATION:
    • :update_sources => true
    • :verbose => true
    • :benchmark => false
    • :backtrace => false
    • :bulk_threshold => 1000
  • REMOTE SOURCES:
6

On Jun 24, 2008, at 5:33 AM, Une Bévue wrote:

Password:
ERROR: Error installing amalgalite:
amalgalite requires configuration (>= 0.0.5)

cfp:~ > sudo gem install configuration
Bulk updating Gem source index for: http://gems.rubyforge.org
Successfully installed configuration-0.0.5
1 gem installed

it’s on http://codeforpeople.com or
http://rubyforge.org/projects/codeforpeople
if you just want to grab the gem - no idea what’s up with you gem
command ;-(

a @ http://codeforpeople.com/

7

On Sun, Jun 22, 2008 at 09:15:46AM +0900, Joel VanderWerf wrote:

Jeremy H. wrote:

Amalgalite embeds the SQLite database engine in a ruby extension.

Is that really better than linking with libsqlite?

That depends on what you mean by “better” :-). If it is performane, I
have no idea, as I haven’t run any performance tests yet.

For me, it is a few it is a few other reasons:

  1. Windows support, because sqlite is embedded in the extension, I can
    ship a Windows gem that doesn’t require sqlite to be aleady
    installed. I haven’t built the windows gem yet, that should be
    coming along in the next week or two.

  2. SQLite “Professional Support Options”. There are a couple of options
    available from SQLite Professional Support that
    I would like to be able to support. Namely the Encrypted Database
    option. Having sqlite3.c embedded in the extension allows someone
    who purchases one of those options to just drop the source code into
    the gem and rebuild.

  3. Feature control. I’m utilizing compliation dependent API calls.
    Right now I’m utilizing the sqlite3_column_XXXX_name calls to get
    column meta data for a result set. These API calls are only exposed
    when SQLITE_ENABLE_COLUMN_METADATA=1 during compliation.

Also, eventually I’m going to expose two SQLite extensions, full text
search and R-trees. I’m sure everyone knows what full text search is
for, and the R-tree extension allows for extremely fast spatial
searching, i.e. latitude, longitude.

Thoughts, comments anyone? Do those sound like things you might use?

enjoy,

-jeremy

8

ara.t.howard [email protected] wrote:

cfp:~ > sudo gem install configuration
Bulk updating Gem source index for: http://gems.rubyforge.org
Successfully installed configuration-0.0.5
1 gem installed

may be u aren’t running Mac OS X 10.4.11 ??? :[

i’ve heard, i don’t remember where and when, rubygems has probs with
remote install on mac os x 10.4.11.

however, in the meantime i did install sproutcore (from Apple) without
any prob…

notice i’m still unable to upgrade rubygems to 1.2…

it’s on http://codeforpeople.com or
http://rubyforge.org/projects/codeforpeople
if you just want to grab the gem - no idea what’s up with you gem
command ;-(

a @ http://codeforpeople.com/

right now i get trouble with mkrf )) :
~/gems%> sudo gem install -l configuration
Password:
Successfully installed configuration-0.0.5
1 gem installed

~/gems%> sudo gem install -l amalgalite
Building native extensions. This could take a while…
ERROR: Error installing amalgalite:
ERROR: Failed to build gem native extension.

/opt/local/bin/ruby mkrf_conf.rb install -l amalgalite
/opt/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in
gem_original_require': no such file to load -- mkrf (LoadError) from /opt/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in require’
from mkrf_conf.rb:2

Gem files will remain installed in
/opt/local/lib/ruby/gems/1.8/gems/amalgalite-0.1.0 for inspection.
Results logged to
/opt/local/lib/ruby/gems/1.8/gems/amalgalite-0.1.0/ext/gem_make.out
~/gems%>

anyway, thanks, i got “configuration” !

9

Une Bévue [email protected] wrote:

just to say, finally i get it (ie. amalgalite), after mkrf update.

10

Jeremy H. [email protected] wrote:

That would be a bug on my part in amalgalite, thanks for fiding it.
I forgot to put a gem dependency on mkrf. Its fixed in the repository.

fine !

i did a first try reading a database created with PHP :wink:

no prob.

i’ve also listed the tables in this db using :
db.execute( “SELECT name FROM sqlite_master WHERE type=‘table’ ORDER BY
name;” )

no prob too.

but, for the time being, i’m unable to find a way to list the columns
name within a given table…

are those info written into “sqlite_master” too ?

11

On Wed, Jun 25, 2008 at 03:08:02AM +0900, Une B?vue wrote:

Une B?vue [email protected] wrote:

just to say, finally i get it (ie. amalgalite), after mkrf update.

That would be a bug on my part in amalgalite, thanks for fiding it.
I forgot to put a gem dependency on mkrf. Its fixed in the
repository.

enjoy,

-jeremy

12

Jeremy H. [email protected] wrote:

The sqlite_master has information about tables and views, but no columnar
information.

With the sqlite3 command line you can get some column information by using the
‘pragma table_info( tbl_name )’ command.

[…]

Using Amalgalite you may also find out:

  • the default collation sequence
  • whether or not the column is auto increment or not

fine, thanks !

13

On Thu, Jun 26, 2008 at 03:22:52PM +0900, Une B?vue wrote:

i’ve also listed the tables in this db using :
db.execute( “SELECT name FROM sqlite_master WHERE type=‘table’ ORDER BY
name;” )

no prob too.

but, for the time being, i’m unable to find a way to list the columns
name within a given table…

are those info written into “sqlite_master” too ?

The sqlite_master has information about tables and views, but no
columnar
information.

With the sqlite3 command line you can get some column information by
using the
‘pragma table_info( tbl_name )’ command.

Meta information is available from within Amalgalite itself try this
out:

db = Amalgalite::Database.new( db_name )
col_info = %w[ default_value declared_data_type
collation_sequence_name not_null_constraint primary_key auto_increment ]
max_width = col_info.collect { |c| c.length }.sort.last

db.schema.tables.keys.sort.each do |table_name|
puts “Table: #{table_name}”
puts “=” * 42
db.schema.tables[table_name].columns.each_pair do |col_name, col|
puts " Column : #{col.name}"
col_info.each do |ci|
puts " |#{ci.rjust( max_width, “.” )} : #{col.send( ci )}"
end
puts
end
end

Amalgalite uses the SQLITE_ENABLE_COLUMN_METADATA compilation option so
more
detailed metadata is available than in the default builds of sqlite.

pragma table_info can give you,

  • name
  • type
  • whether the column has a not-null constraint or not
  • default value
  • whehter the column is a primary key or not.

Using Amalgalite you may also find out:

  • the default collation sequence
  • whether or not the column is auto increment or not

enjoy,

-jeremy