h2 blobs

class DbH2Script

  def self.create_table
    sql = "create table scripts(
           id bigint identity,
           use_from timestamp,
           use_to timestamp,
           name varchar,
           type varchar,
           zone int,
           version int,
           script_name varchar,
           content blob,
           created timestamp,
           modified timestamp);"
    stmt = $info[:db].create_statement
    stmt.execute(sql)
    stmt.close
  end

  #   opts[:script_path]
  #   opts[:name]
  #   opts[:type]
  #   opts[:zone]
  #   opts[:version]
  #      or
  # :script_path => 'path', :name => 'name', :type => :ruby, :zone => 3, :version => '1.4.3'
  
 def self.to_db(opts)
    begin
      script_version = script_version_for_db(opts[:version])

      case opts[:type]
      when :ruby
        script_name = "#{opts[:name]}-#{opts[:zone]}.rb"
      when :sketch
        script_name = "#{opts[:name]}-#{opts[:zone]}.rb"
      else
        puts "Script To Database Bad Script Type : #{opts[:type]}"
        abort
      end

      compressed_byte_array = compress_byte_array(File.read(opts[:script_path]).to_java_bytes)

      byte_array_input_stream = JIO::ByteArrayInputStream.new(compressed_byte_array)
      sql = "insert into scripts (id,name,type,zone,version,script_name,content,use_from,use_to,created,modified)
             values(null,'#{opts[:name]}','#{opts[:type]}',#{opts[:zone]},#{script_version},'#{script_name}',?,?,?,?,?);"
      #      puts sql
      prepared_statement = $info[:db].prepareStatement(sql)
      prepared_statement.setBinaryStream(1,byte_array_input_stream)

      sql_date = JavaSql::Timestamp.new(JUtil::Date.new.getTime)
      sql_date_0 = JavaSql::Timestamp.new(0)

      opts[:use_from] ||= sql_date_0
      opts[:use_to] ||= sql_date_0
      prepared_statement.setTimestamp(2, opts[:use_from])
      prepared_statement.setTimestamp(3, opts[:use_to])
      prepared_statement.setTimestamp(4, sql_date)
      prepared_statement.setTimestamp(5, sql_date)
      prepared_statement.executeUpdate
      puts "Inserting Successfully!"
      prepared_statement.close
    rescue Exception => e
      puts "Insert Failed : #{e}"
    end
  end

  def self.script_version_from_db(db_version)
    tmp = db_version.to_s
    ary = []
    master_version_id = tmp[-tmp.size,tmp.size-6]
    ary << master_version_id.to_i.to_s if master_version_id
    minor_version_id = tmp[-6,3]
    ary << minor_version_id.gsub(/^0+/, '').to_i.to_s if minor_version_id
    sub_minor_version_id = tmp[-3,3]
    ary << sub_minor_version_id.gsub(/^0+/, '').to_i.to_s if sub_minor_version_id
    ary.join('.')
  end

  def self.script_version_for_db(script_version)
    ary = script_version.split(/\./)
    return ary[0].to_i*1000000 + ary[1].to_i*1000 +ary[2].to_i
  end



  def self.load_by_name(script_name, version = '')
    stmt = $info[:db].create_statement
    if version == '' # then get latest
      result_set = stmt.executeQuery("select name,version,full_name,content from scripts order by version")
    else
      tmp_script_name = "#{script_name}-#{version}"
      result_set = stmt.executeQuery("select name,version,full_name,content from scripts where full_name = '#{tmp_script_name}'")
    end
    if result_set.last # nil if no rows returned
      script_name = result_set.get_string(1)
      script_version = script_version_from_db(result_set.get_int(2))
      script_full_name = result_set.get_string(3)
      zip_file = File.join($info[:temp_directory], "#{script_full_name}.zip")
      file_output_stream = JIO::FileOutputStream.new(zip_file)
      file_output_stream.write(result_set.get_bytes(4))
      file_output_stream.close
      result_set.close
      stmt.close

      unzip_file(zip_file, $info[:scripts_directory])
      FileUtils.remove_dir(zip_file, true)

      #    register script
      $LOAD_PATH.unshift File.join($info[:scripts_directory], script_full_name, 'lib')
      require script_name
    else
      # TODO go out to network and get it
      version = 'latest' if version == ''
      puts "script Not Found : #{script_name}-#{version}"
    end
  end



  def self.load_by_name(script_name, version = '')
    stmt = $info[:db].create_statement
    if version == '' # then get latest
      result_set = stmt.executeQuery("select name,version,full_name,content from scripts order by version")
    else
      tmp_script_name = "#{script_name}-#{version}"
      result_set = stmt.executeQuery("select name,version,full_name,content from scripts where full_name = '#{tmp_script_name}'")
    end
    if result_set.last # nil if no rows returned
      script_name = result_set.get_string(1)
      script_version = script_version_from_db(result_set.get_int(2))
      script_full_name = result_set.get_string(3)
      zip_file = File.join($info[:temp_directory], "#{script_full_name}.zip")
      file_output_stream = JIO::FileOutputStream.new(zip_file)
      file_output_stream.write(result_set.get_bytes(4))
      file_output_stream.close
      result_set.close
      stmt.close

      unzip_file(zip_file, $info[:scripts_directory])
      FileUtils.remove_dir(zip_file, true)

      #    register script
      $LOAD_PATH.unshift File.join($info[:scripts_directory], script_full_name, 'lib')
      require script_name
    else
      # TODO go out to network and get it
      version = 'latest' if version == ''
      puts "script Not Found : #{script_name}-#{version}"
    end
  end

  def self.load_by_id(id)
    stmt = $info[:db].create_statement
    result_set = stmt.executeQuery("select * from scripts where id = #{id}")


    result_set.first
    rslt = {}
    rslt[:id] = result_set.get_long(1)
    rslt[:name] = result_set.get_string(2)
    rslt[:type] = result_set.get_string(3)
    rslt[:zone] = result_set.get_int(4)
    rslt[:version] = script_version_from_db(result_set.get_int(5))
    rslt[:full_name] = result_set.get_string(6)
    rslt[:content] = result_set.get_bytes(7)
    rslt[:use_from] = result_set.get_timestamp(8)
    rslt[:use_to] = result_set.get_timestamp(9)
    rslt[:created] = result_set.get_timestamp(10)
    rslt[:modified] = result_set.get_timestamp(11)

    result_set.close
    stmt.close

    script = String.from_java_bytes(decompress_byte_array(rslt[:content]))
    #    puts script
  end






end