From: Jeremy Hinegardner <jeremy@...>
Date: 2008-06-30T09:54:41+09:00
Subject: Re: [ANN] Initial release of amalgalite - v0.1.0

On Thu, Jun 26, 2008 at 03:22:52PM +0900, Une B?vue wrote:
> Jeremy Hinegardner <jeremy@hinegardner.org> 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 ;-)
> 
> 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 ?

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

-- 
========================================================================
 Jeremy Hinegardner                              jeremy@hinegardner.org