require 'sqlite3'
include SQLite3

class Db
    private     :exec
    attr_reader :structure

    def initialize(db_name, hash_structure={})
        # this hash has the tables names as key and an array composed of arries of string as value,
        # each string array being of form %w( varname type )
        # e.g.: @structure = { 
        #           "table1" => [ ["id", "integer primary key"], %w(name varchar), %w(date datetime) ]
        #           "table2" => [ %w(place varstring), %w(personnumber int) ]
        #       }
        @structure = hash_structure

        if not File.exist?(db_name)
            @db = Database.new(db_name)
            @structure.each { |table, value|
                command = "create table #{table}("
                value.each { |column|
                    command += " #{column[0]} #{column[1]}"
                    command += (value.index(column) + 1 == value.length ? ')' : ',' )
                }
                @db.execute(command)
            }
        else
            @db = Database.new(db_name)

            # populate @structure with the tables of the db
            tables = @db.execute('select * from sqlite_master')
            tables.each { |table|
                types = table[4][/\(.*\)/].delete("(").delete(")").split(',')
                @structure[table[1]] = types.each_index { |type| types[type] = types[type].split(' ') }
            }
        end
    end

    def add_table(name, array_structure)
        return false if exist?(name)
        command = "create table '#{name}'("
        array_structure.each { |column|
            command += " #{column[0]} #{column[1]}"
            command += (array_structure.index(column) + 1 == array_structure.length ? ')' : ',' )
        }
        exec(command) ? @structure[name] = array_structure : (return false)
        return true
    end

    def add(table, ar_fields=nil, *values)
        str_value = ""
        values.each { |value| str_value += "'#{value}'," }
        str_value.chop!
        str_fields = "("
        ar_fields.each { |field| str_fields += "#{field}," }
        str_fields = str_fields.chop + ")"
        str_fields = nil if not ar_fields 
        return true if exist?(table) and exec("insert into '#{table}'#{str_fields} values(#{str_value})")
        return false
    end

    def find(table, condition=nil, *columns)
        return false if not exist?(table)
        if columns.length != 0
            columns.each { |column| return false if not exist?(table, column) }
        end

        columns = (columns == [] ? '*' : columns.each_index { |i| columns[i] += ' ' }.to_s)
        result = exec("select #{columns} from '#{table}' #{condition}")
        return (result != [] ? result : false)
    end

    def erase(table, condition)
        return false if not exist?(table)
        return exec("delete from #{table} #{condition}")
    end

    def update(table, column, new_value, condition=nil)
        return false if not exist?(table, column)
        new_value = %Q-"#{new_value}"- if new_value.class == String
        return exec("update '#{table}' set #{column} = #{new_value} #{condition}")
    end

    def exist?(table, column=nil)
        if @structure.has_key?(table)
            return true if not column 
            @structure[table].length.times { |i|
                return true if @structure[table][i][0] == column
            }
        end
        return false
    end

    def exec(string)
        begin
            result = @db.execute(string)
            puts string.inspect # debug
            return result
        rescue SQLException
            puts string
            puts "error: " + $!
            return false
        end
    end
end