From: unbewusst.sein@... (=?ISO-8859-1?Q?Une_B=E9v?= =?ISO-8859-1?Q?ue?=)
Date: 2008-07-07T05:36:32+09:00
Subject: #pragma and index_info() was ([ANN] amalgalite 0.2.1 Released)

Jeremy Hinegardner <jeremy@hinegardner.org> wrote:

> {{ Release notes for Version 0.2.1 }}

now, i'm running this version.
because i want to switch from ruby-sqlite3 to amalgalite, i've tried
translating a ruby-sqlite3 script to amalgalite.

on the ruby-sqlite3 side i'm using PRAGMA :

#! /usr/bin/env ruby

require 'rubygems'
require 'sqlite3'

file='places.sqlite'
#file=Dir.glob("#{ENV['HOME']}/Library/Application
Support/Firefox/Profiles/*.default/places.sqlite").last
db = SQLite3::Database.new( "#{file}" )

tables=[]
db.execute( "SELECT name FROM sqlite_master WHERE type='table' ORDER BY
name;" ) do |row|
  tables<<row[0]
end

tables.each do |table|
  puts table+":"
  index_list=[]
  db.execute( "PRAGMA index_list( #{table} );" ) do |row|
    index_list<<row[1]
  end
  index_list.each do |index_name|
    puts "  #{index_name}:"
    columns=nil
    db.execute2( "PRAGMA index_info( #{index_name} );" ) do |row|
      if !columns
        columns=row.join(' ')
        puts "    "+columns
      else
        puts "    "+row.join(' ')
      end
    end
  end
  puts
end


and get :

moz_anno_attributes:
  sqlite_autoindex_moz_anno_attributes_1:
    seqno cid name
    0 1 name

moz_annos:
  moz_annos_placeattributeindex:
    seqno cid name
    0 1 place_id
    1 2 anno_attribute_id
...

on the amalgalite side, i've tried :
#! /usr/bin/env ruby

require 'rubygems'
require 'amalgalite'

class String
  def underline
    return self+"\n+"+("-"*(self.length-2))+"+"
  end
end

db = Amalgalite::Database.new( "places.sqlite" )
index_info=%w[ sql ]
max_width=index_info.collect { |c| c.length }.sort.last
pragma_info=%w[ seqno cid name ]
pragma_max_width=pragma_info.collect { |c| c.length }.sort.last

db.schema.tables.keys.sort.each do |table_name|
  puts "Table: #{table_name}".underline
  indexes=db.schema.tables[table_name].indexes
  indexes.each do |index|
    puts "  Index : #{index.name}"
    index_info.each do |ci|
      puts "    |#{ci.rjust( max_width, "." )} : #{index.send( ci )}" 
    end
    db.pragma("index_info( #{index.name} )") do |index_field|
      pragma_info.each do |ci|
        puts "      |#{ci.rjust( pragma_max_width, "." )} :
#{index_field.send( ci )}" 
      end
    end
    puts 
  end 
end

db.close


where i get :
Table: moz_anno_attributes
+------------------------+
  Index : sqlite_autoindex_moz_anno_attributes_1
    |sql : 

Table: moz_annos
+--------------+
  Index : moz_annos_placeattributeindex
    |sql : CREATE UNIQUE INDEX moz_annos_placeattributeindex ON
moz_annos (place_id, anno_attribute_id)

...

then it seems that db.pragma("index_info( #{index.name} )") returns an
empty array, although the source code gives:
     # File lib/amalgalite/database.rb, line 440
440:     def pragma( cmd )
441:       execute("PRAGMA #{cmd}")
442:     end

showing db.execute2( "PRAGMA index_info( #{index_name} );" ) and
#pragma( cmd ) are equivalent (apart from columns names).

where am i wrong here ?

-- 
Une B�vue