<commit_before># coding: utf-8
require 'sqlite3'

# Database stuff
class Db
  attr_accessor :db
  OUT = 1
  IN = 0
  def initialize
    @db = SQLite3::Database.new DB_FILENAME
    @db.results_as_hash = true
    # TODO : Puts these two script in a Rake Task ?
    create_schema unless schema?
    synchronize_image!
    @item = empty_row
  end

  # Create a table
  def create_schema
    puts "  Creating db schema..."
    puts "  Creating table items..."
    @db.execute "
      create table items (
        code varchar(50) PRIMARY KEY,
        name varchar(255),
        description varchar(2000),
        image_link varchar2(2000),
        checkout varchar(1) default 'N',
        chkout_date timestamp,
        chkin_date timestamp
      );"
    puts "  Creating table tags..."
    @db.execute 'create table tags (
        id integer,
        tag varchar(255),
        color varchar(50),
        firstname varchar(255) default null,
        lastname varchar(255) default null,
        email varchar(255) default null
        );'
    puts "  Creating table tags_items..."
    @db.execute 'create table tags_items (
        item_code varchar(50),
        tag_id integer
        );'
    puts "  Creating table items_log..."
    @db.execute 'create table items_log (
        item_code varchar(50),
        tag_id integer,
        move integer,
        move_date timestamp
        );'
  end

  #Is the db schema exists ?
  def schema?
    begin
      r = db.prepare "select * from Items"
      true
    rescue
      false
    end
  end

  # True if database is supposed empty
  def empty_DB?
    r = @db.execute "select count(1) as cnt from Items" 
    return r[0]["cnt"] == 0
  end

  # Synchronize image with DB
  def synchronize_image!
    imgs = {}

    # 1. Unlink from DB all unfound images
    puts "  Synchronizing images with DB..."
    r = select_all_items
    r.each { |i|
      unless i['image_link'].empty?
        # Hash with all used images
        imgs[i['image_link']] = i['code']
        unless  File::exists? "#{APP_ROOT}/public/pictures/#{i['image_link']}"
          puts "unlinking #{i['image_link']} from item ##{i['code']}"
          update_item_image_link i['code'], ''
        end
      end
    }

    # 2. Delete all unreferenced image from directory
    Dir.entries("#{APP_ROOT}/public/pictures/")
      .reject { |f| f == '.' || f == '..'  }
      .reject { |f| f[0..4]  == 'thumb'}
      .each { |f| 
        unless imgs.has_key? f
          File::unlink "#{APP_ROOT}/public/pictures/#{f}" if File::exists? "#{APP_ROOT}/public/pictures/#{f}"
          File::unlink "#{APP_ROOT}/public/pictures/thumb-#{f}" if File::exists? "#{APP_ROOT}/public/pictures/thumb-#{f}"
        end
    }

  end


    # Get the highest id from a table
    # Can't work with a varchar column.
  def lastid(table_name, col='code')
    last = 0
    r = @db.get_first_row 'select ' + col + ' as max from ' + table_name + ' order by cast(' + col + ' As Int) desc'
    unless r.nil?
      last = r['max']
    end
    puts "selecting lastid from #{table_name}.#{col} : #{last}"
    last
  end

  def empty_row
    [{'code' => '', 'name' => '', 'description' => '',  'image_link' => '', 'checkout' => ''}]
  end

  # select data
  def select_all_items(extract_tag=true)
    res = @db.execute "select * from items order by name"
    res.each { |row|
      taglist = select_tags_for_item(row[0]).flatten
      row['taglist'] = taglist
    } if extract_tag
    res
  end

  def read(code)
    code = code.upcase unless code.nil?
    r = @db.execute 'select * from items where upper(code) = ?', code
    if r[0].nil?
      return empty_row
    end
    r
  end

  # see if this item exists in database
  def exists?(code)
    @item = empty_row
    @item = read code
    !@item[0]['code'].empty?
  end

  # Is item checked out ?
  def checkout?(code)
    if exists? code
      return @item[0]['checkout'] == 'O'
    end
    false
  end

  def checkout(code, chkout_date, chkin_date)
    @db.execute "update items set checkout = 'O', chkout_date = '" + chkout_date.to_s + "'" +
                ", chkin_date = '" + chkin_date.to_s + "' where upper(code) = ?", code.upcase
  end

  def checkin(code)
    @db.execute "update items set checkout = 'N', chkout_date = '' " + 
                ", chkin_date = '' where upper(code) = ?", code.upcase
  end

  # Adds an item
  def add_item(code, name, desc, image_link, checkout, chkout_date, chkin_date)
    @db.execute 'INSERT INTO items (code, name, 
        description, image_link, checkout, chkout_date, chkin_date) VALUES (?, ?, ?, ?, ?, ?, ?)', 
        [code.to_s.upcase, name.to_s, desc.to_s, image_link.to_s, checkout.to_s, chkout_date, chkin_date]
  end

  # update an item
  def update_item(code, name, desc, image_link)
    @db.execute 'update items  set name = ?, description = ?, image_link = ?
        where upper(code) = ?', [name.to_s, desc.to_s, image_link.to_s, code.to_s.upcase]
  end

  # Updates an item image_link
  def update_item_image_link(code, image_link)
    @db.execute 'update items set image_link = ? where upper(code) = ?', [image_link.to_s, code.to_s.upcase]
  end

  def add_serveral_items (data)
    n = 0
    data.reject! { |k, v| k == 'code' }
    .reject! { | row | exists? row[0] }
    .each { |row|
      puts "adding #{row[1]} ##{row[0]}"
      add_item(row[0], row[1], row[2], row[3], row[4], row[5], row[6]) unless row[0].empty?
      n = n + 1
    }
    puts "#{n} inserted rows"
  end

  # deletes an item
  def delete_item(code)
    unlink_item code
    @db.execute "delete from items where upper(code) = ?", code.upcase
  end

  #
  # Tags routines
  #
  def select_all_tags
    @db.execute "select t.id, t.tag, t.color, t.firstname, t.lastname, t.email, count(ti.item_code) count_items<commit_msg>bugfix on massive import, set chechout to 'N' if nil<commit_after># coding: utf-8
require 'sqlite3'

# Database stuff
class Db
  attr_accessor :db
  OUT = 1
  IN = 0
  def initialize
    @db = SQLite3::Database.new DB_FILENAME
    @db.results_as_hash = true
    # TODO : Puts these two script in a Rake Task ?
    create_schema unless schema?
    synchronize_image!
    @item = empty_row
  end

  # Create a table
  def create_schema
    puts "  Creating db schema..."
    puts "  Creating table items..."
    @db.execute "
      create table items (
        code varchar(50) PRIMARY KEY,
        name varchar(255),
        description varchar(2000),
        image_link varchar2(2000),
        checkout varchar(1) default 'N',
        chkout_date timestamp,
        chkin_date timestamp
      );"
    puts "  Creating table tags..."
    @db.execute 'create table tags (
        id integer,
        tag varchar(255),
        color varchar(50),
        firstname varchar(255) default null,
        lastname varchar(255) default null,
        email varchar(255) default null
        );'
    puts "  Creating table tags_items..."
    @db.execute 'create table tags_items (
        item_code varchar(50),
        tag_id integer
        );'
    puts "  Creating table items_log..."
    @db.execute 'create table items_log (
        item_code varchar(50),
        tag_id integer,
        move integer,
        move_date timestamp
        );'
  end

  #Is the db schema exists ?
  def schema?
    begin
      r = db.prepare "select * from Items"
      true
    rescue
      false
    end
  end

  # True if database is supposed empty
  def empty_DB?
    r = @db.execute "select count(1) as cnt from Items" 
    return r[0]["cnt"] == 0
  end

  # Synchronize image with DB
  def synchronize_image!
    imgs = {}

    # 1. Unlink from DB all unfound images
    puts "  Synchronizing images with DB..."
    r = select_all_items
    r.each { |i|
      unless i['image_link'].empty?
        # Hash with all used images
        imgs[i['image_link']] = i['code']
        unless  File::exists? "#{APP_ROOT}/public/pictures/#{i['image_link']}"
          puts "unlinking #{i['image_link']} from item ##{i['code']}"
          update_item_image_link i['code'], ''
        end
      end
    }

    # 2. Delete all unreferenced image from directory
    Dir.entries("#{APP_ROOT}/public/pictures/")
      .reject { |f| f == '.' || f == '..'  }
      .reject { |f| f[0..4]  == 'thumb'}
      .each { |f| 
        unless imgs.has_key? f
          File::unlink "#{APP_ROOT}/public/pictures/#{f}" if File::exists? "#{APP_ROOT}/public/pictures/#{f}"
          File::unlink "#{APP_ROOT}/public/pictures/thumb-#{f}" if File::exists? "#{APP_ROOT}/public/pictures/thumb-#{f}"
        end
    }

  end


    # Get the highest id from a table
    # Can't work with a varchar column.
  def lastid(table_name, col='code')
    last = 0
    r = @db.get_first_row 'select ' + col + ' as max from ' + table_name + ' order by cast(' + col + ' As Int) desc'
    unless r.nil?
      last = r['max']
    end
    puts "selecting lastid from #{table_name}.#{col} : #{last}"
    last
  end

  def empty_row
    [{'code' => '', 'name' => '', 'description' => '',  'image_link' => '', 'checkout' => ''}]
  end

  # select data
  def select_all_items(extract_tag=true)
    res = @db.execute "select * from items order by name"
    res.each { |row|
      taglist = select_tags_for_item(row[0]).flatten
      row['taglist'] = taglist
    } if extract_tag
    res
  end

  def read(code)
    code = code.upcase unless code.nil?
    r = @db.execute 'select * from items where upper(code) = ?', code
    if r[0].nil?
      return empty_row
    end
    r
  end

  # see if this item exists in database
  def exists?(code)
    @item = empty_row
    @item = read code
    !@item[0]['code'].empty?
  end

  # Is item checked out ?
  def checkout?(code)
    if exists? code
      return @item[0]['checkout'] == 'O'
    end
    false
  end

  def checkout(code, chkout_date, chkin_date)
    @db.execute "update items set checkout = 'O', chkout_date = '" + chkout_date.to_s + "'" +
                ", chkin_date = '" + chkin_date.to_s + "' where upper(code) = ?", code.upcase
  end

  def checkin(code)
    @db.execute "update items set checkout = 'N', chkout_date = '' " + 
                ", chkin_date = '' where upper(code) = ?", code.upcase
  end

  # Adds an item
  def add_item(code, name, desc, image_link, checkout, chkout_date, chkin_date)
    @db.execute 'INSERT INTO items (code, name, 
        description, image_link, checkout, chkout_date, chkin_date) VALUES (?, ?, ?, ?, ?, ?, ?)', 
        [code.to_s.upcase, name.to_s, desc.to_s, image_link.to_s, checkout.to_s, chkout_date, chkin_date]
  end

  # update an item
  def update_item(code, name, desc, image_link)
    @db.execute 'update items  set name = ?, description = ?, image_link = ?
        where upper(code) = ?', [name.to_s, desc.to_s, image_link.to_s, code.to_s.upcase]
  end

  # Updates an item image_link
  def update_item_image_link(code, image_link)
    @db.execute 'update items set image_link = ? where upper(code) = ?', [image_link.to_s, code.to_s.upcase]
  end

  def add_serveral_items (data)
    n = 0
    data.reject! { |k, v| k == 'code' }
    .reject! { | row | exists? row[0] }
    
    data.each { |row|
      puts "adding #{row[1]} ##{row[0]}"
      add_item(row[0], row[1], row[2], row[3], row[4] || "N", row[5], row[6]) unless row[0].empty?
      n = n + 1
    }
    puts "#{n} inserted rows"
  end

  # deletes an item
  def delete_item(code)
    unlink_item code
    @db.execute "delete from items where upper(code) = ?", code.upcase
  end

  #
  # Tags routines
  #
  def select_all_tags
    @db.execute "select t.id, t.tag, t.color, t.firstname, t.lastname, t.email, count(ti.item_code) count_items
{'fraction_non_alphanumeric': 0.13476276586584002, 'fraction_numerical': 0.0108499095840868, 'mean_word_length': 2.4908325819056207, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5669351', 'n_tokens_mistral': 4157, 'n_tokens_neox': 3996, 'n_words': 1089}