Amalgalite 0.2.0 Released

Ruby
1

amalgalite version 0.2.0 has been released.

http://www.copiousfreetime.org/articles/2008/07/04/amalgalite-0-2-0-released.html

Amalgalite embeds the SQLite database engine in a ruby extension. There
is no
need to install SQLite separately. Look in the examples/ directory for
examples
on:

  • general usage
  • blob io
  • schema information

Also Scroll through Amalgalite::Database for a quick example, and a
general
overview of the API.

{{ Release notes for Version 0.2.0 }}

  • Major Enhancements

    • blob support, both incremental access and normal access

  • Minor Enhancements

    • added examples/gem_db.rb script demonstrating taps and prepared
      statements
    • added examples/schema-info.rb script demonstrating meta information
    • added examples/blob.rb demonstrating incremental blob IO
    • added acces to the SQLite3 errcode and errmsg api

  • Bugfixes

    • added taps.rb for requiring
    • fixed prepared statement reset
    • caught an error in executing prepared statements earlier in the
      process so
      the correct error is reported
2

Jeremy H. [email protected] wrote:

Amalgalite embeds the SQLite database engine in a ruby extension.
There is no need to install SQLite separately.
Look in the examples/ directory for examples
on:

  • general usage
  • blob io
  • schema information

i’ve tested your online example “gem-db.rb”, looks good at insertion,
however get :

gem-db.rb

Opening database (version 0.2.0)
Establishing taps
Create schema
Inserting 99 rows of gem information…
Took 0.246415 seconds
Done Inserting
NoMethodError: undefined method ‘columns’ for nil:NilClass
method is_column_rowid?
in statement.rb at line 349
method result_meta
in statement.rb at line 333
method times
in statement.rb at line 320
method result_meta
in statement.rb at line 320
method next_row
in statement.rb at line 250
method all_rows
in statement.rb at line 298
method execute
in database.rb at line 252
at top level
in gem-db.rb at line 72

line 72 being :
authors_by_number = db.execute(“SELECT author, count( name ) as num_gems
FROM gems GROUP BY author ORDER BY num_gems DESC”)

notice i’m now able to read the Firefox 3 bookmarks file
(“places.sqlite”) having “LONGVARCHAR” (wasn’t OK with version 0.1).

3

Jeremy H. [email protected] wrote:

Or download a new example script from github:

GitHub - copiousfreetime/amalgalite: SQLite database engine embedded in a ruby extension.
/tree/master/examples/gem-db.rb

fine, thanks, it rocks !

notice i’m now able to read the Firefox 3 bookmarks file
(“places.sqlite”) having “LONGVARCHAR” (wasn’t OK with version 0.1).

I haven’t done any testing with a Firefox 3 bookmarks file. I haven’t
done anything specifically to address this, but I could see if SQLite
internally had decided that one of the values in there was to be stored as
a BLOB storage type that amalgalite would have failed before.

favicons are stored there as blob.

best,

4

On Sun, Jul 06, 2008 at 02:56:40PM +0900, Une B?vue wrote:

(“places.sqlite”) having “LONGVARCHAR” (wasn’t OK with version 0.1).

I haven’t done any testing with a Firefox 3 bookmarks file. I haven’t
done anything specifically to address this, but I could see if SQLite
internally had decided that one of the values in there was to be stored as
a BLOB storage type that amalgalite would have failed before.

favicons are stored there as blob.

Then yup, firefox 3 bookmarks file can be accessed via amalgalite now it
look
like. That’s very cool.

enjoy,

-jeremy

5

Jeremy H. [email protected] wrote:

favicons are stored there as blob.

Then yup, firefox 3 bookmarks file can be accessed via amalgalite now it look
like. That’s very cool.

meta info about that :
Table: moz_favicons

Column : expiration
|…default_value :
|…declared_data_type : LONG
|collation_sequence_name : BINARY
|…not_null_constraint : false
|…primary_key : false
|…auto_increment : false

Column : url
|…default_value :
|…declared_data_type : LONGVARCHAR
|collation_sequence_name : BINARY
|…not_null_constraint : false
|…primary_key : false
|…auto_increment : false

Column : mime_type
|…default_value :
|…declared_data_type : VARCHAR(32)
|collation_sequence_name : BINARY
|…not_null_constraint : false
|…primary_key : false
|…auto_increment : false

Column : id
|…default_value :
|…declared_data_type : INTEGER
|collation_sequence_name : BINARY
|…not_null_constraint : false
|…primary_key : true
|…auto_increment : false

Column : data
|…default_value :
|…declared_data_type : BLOB
|collation_sequence_name : BINARY
|…not_null_constraint : false
|…primary_key : false
|…auto_increment : false

I’ll try to extract those icons and build an html page with to learn with.

I get the first one, saved to file, using :
#! /usr/bin/env ruby

require ‘rubygems’
require ‘amalgalite’
require ‘rexml/document’
require ‘net/http’
require ‘uri’

db=Amalgalite::Database.new( “places.sqlite” )
all_rows=db.execute( “SELECT url, mime_type, data FROM moz_favicons” )
blob_row = all_rows.first
puts blob_row[‘url’]
puts blob_row[‘mime_type’]
uri = URI.parse(blob_row[‘url’])
p “uri.host = #{uri.host}, uri.port = #{uri.port}, uri.scheme =
#{uri.scheme}, uri.path = #{uri.path}”
puts “#{uri.host}#{File.basename(uri.path)}"
blob_row[‘data’].write_to_file( "#{uri.host}#{File.basename(uri.path)}”
) if !blob_row[‘data’].nil?

works great !

now, i need that blob as a string to base64 encode it and make use of :

<img
src=‘data:#{blob_row[‘mime_type’]};base64,#{Base64.b64encode(blob_row[‘d
ata’].to_string_io.read)}’ />

for the time of posting i get no output from #to_string_io.read ?

6

On Sat, Jul 05, 2008 at 07:21:44PM +0900, Une B?vue wrote:

NoMethodError: undefined method ‘columns’ for nil:NilClass
method all_rows
in statement.rb at line 298
method execute
in database.rb at line 252
at top level
in gem-db.rb at line 72

This would be a bug in the example. I’ve run it so many times that the
db
already existed all the time ran it. This is the patch:

@@ -37,6 +37,7 @@ unless schema.tables[‘gems’]
author VARCHAR(128)
);
SQL

  • db.reload_schema!
    end

Or download a new example script from github:

http://github.com/copiousfreetime/amalgalite/tree/master/examples/gem-db.rb

notice i’m now able to read the Firefox 3 bookmarks file
(“places.sqlite”) having “LONGVARCHAR” (wasn’t OK with version 0.1).

I haven’t done any testing with a Firefox 3 bookmarks file. I haven’t
done
anything specifically to address this, but I could see if SQLite
internally had
decided that one of the values in there was to be stored as a BLOB
storage type
that amalgalite would have failed before.

enjoy,

-jeremy

7

Une Bévue [email protected] wrote:

for the time of posting i get no output from #to_string_io.read ?

the method is #string )))

#! /usr/bin/env ruby

require ‘rubygems’
require ‘amalgalite’
require ‘uri’
require ‘base64’

write the first favicon to “data” URL scheme

see http://www.ietf.org/rfc/rfc2397.txt

db=Amalgalite::Database.new( “places.sqlite” )
all_rows=db.execute( “SELECT url, mime_type, data FROM moz_favicons” )
blob_row = all_rows.first
uri = URI.parse(blob_row[‘url’])

html=“

#{uri.host} #{File.basename(uri.path)}:
<img
src=‘data:#{blob_row[‘mime_type’]};base64,#{Base64.b64encode(blob_row[‘d
ata’].to_string_io.string)}’ />

”
File.open(“#{uri.host}_favicon.html”,“w”){|f| f.print html}

resulting in :

www.google.com favicon.ico: