Re: [devel] Re: new fortune package?

["Alexey I. Froloff" <sir_raorn@immo.ru>]

[-- Attachment #1.1: Type: text/plain, Size: 659 bytes --]

On Mon, Sep 01, 2003 at 02:07:15PM +0400, Vitaly Ostanin wrote:
> > Я со своей стороны могу поделиться структурой постресовой базы
> > и наколенным скриптиком на ruby для добавления и обработки
> > цитат;-)
> Можно в меня бросить? Для других цитат :)
Вот прям в рассылку и брошу.

Постгресовая база (одна таблица):

CREATE TABLE altlinux (
    id serial NOT NULL,
    text text NOT NULL,
    author character varying(20) NOT NULL,
    list character varying(20) NOT NULL,
    confirmed boolean DEFAULT false NOT NULL
);

CREATE UNIQUE INDEX text_altlinux_ukey ON altlinux USING btree (text);

Скрипт в аттаче.

-- 
Regards,
Sir Raorn.

[-- Attachment #1.2: quotes --]
[-- Type: text/plain, Size: 3471 bytes --]

#! /usr/bin/ruby -w

require 'postgres'

if ARGV.size < 2 then
    puts "Usage: quotes TABLE command [args...]"
    exit(1)
end

pghost = nil
pgport = nil
pgoptions = nil
pgtty = nil
dbname = "fortunes"
table = ARGV[0]
command = ARGV[1]

begin
    conn = PGconn.connect(pghost,pgport,pgoptions,pgtty,dbname)

    case command
    when "get"
	res = conn.exec("SELECT convert(\"text\", 'KOI8-R', 'UTF-8') as \"text\", \"author\", \"list\" FROM \"#{table}\" WHERE \"confirmed\" = 't' ORDER BY \"id\" ASC")

	if res.status == PGresult::TUPLES_OK then
	    res.result.each { |q|
		puts "#{q[0]}\n\t\t-- #{q[1]} in #{q[2]}@\n%"
	    }
	end
    when "show"
	if ARGV.size != 3 then
	    printf "Usage: quotes TABLE show ID"
	else
	    res = conn.exec("SELECT \"id\", \"text\", \"author\", \"list\", \"confirmed\" FROM \"#{table}\" WHERE \"id\" = #{ARGV[2]}")

	    if res.status == PGresult::TUPLES_OK and res.result.size == 1 then
		puts "Id: #{res.result[0][0]}"
		puts "Author: #{res.result[0][2]}"
		puts "List: #{res.result[0][3]}@"
		puts "Status: #{res.result[0][4] == 't' ? "OK" : "UNCONFIRMED"}"
		puts "Text:\n#{res.result[0][1]}"
	    else
		if res.result.size == 0 then
		    puts "No such quote"
		else
		    printf(STDERR, conn.error)
		end
	    end
	end
    when "stats"
	res = conn.exec("SELECT \"author\", \"list\" FROM \"#{table}\" WHERE \"confirmed\" = 't'")

	if res.status == PGresult::TUPLES_OK and res.result.size > 0 then
	    qpa = Hash.new(0)
	    qpl = Hash.new(0)
	    total = 0
	    res.result.each { |q|
		qpa[q[0]] += 1
		qpl[q[1]] += 1
		total += 1
	    }
	    puts "Quotes per author:"
	    qpa.keys.sort.each { |k|
		printf "%-10s - %d\n", k, qpa[k]
	    }
	    puts "\nQuotes per list:"
	    qpl.keys.sort.each { |k|
		printf "%-15s - %d\n", k, qpl[k]
	    }
	    puts "\nTotal: #{total}"
	end
    when "unconfirmed"
	res = conn.exec("SELECT \"id\", \"author\", \"list\" FROM \"#{table}\" WHERE \"confirmed\" = 'f' ORDER BY \"id\" ASC")

	if res.status == PGresult::TUPLES_OK and res.result.size > 0 then
	    total = 0
	    res.result.each { |q|
		printf "%5d - %s in %s@\n", q[0], q[1], q[2]
		total += 1
	    }
	    puts "Total: #{total}"
	else
	    if res.result.size == 0 then
		puts "No unconfirmed quotes"
	    else
		printf(STDERR, conn.error)
	    end
	end
    when "add"
	if ARGV.size != 4 then
	    printf "Usage: quotes TABLE add AUTHOR LIST"
	else
	    author = ARGV[2]
	    list = ARGV[3]
	    text = ""

	    puts "Adding quote by #{author} from #{list}@"
	    puts "Enter text, end with ^D"
	    $stdin.each_line { |l|
		text << l
	    }
	    text.chomp!

	    puts "\nAdding:"
	    puts text
	    puts "\t\t-- #{author} in #{list}@"

	    #puts "INSERT INTO \"#{table}\" (\"text\", \"author\", \"list\") VALUES ('#{text.gsub(/([\\'])/, '\\\\\1')}', '#{author}', '#{list}')"
	    conn.exec("INSERT INTO \"#{table}\" (\"text\", \"author\", \"list\") VALUES ('#{text.gsub(/([\\'])/, '\\\\\1')}', '#{author}', '#{list}')")
	end
    when "confirm"
	if ARGV.size != 3 then
	    printf "Usage: quotes TABLE confirm ID"
	else
	    conn.exec("UPDATE \"#{table}\" SET \"confirmed\"='t' WHERE \"id\" = #{ARGV[2]}")
	end
    end

    conn.close
rescue PGError
    if (conn == nil or conn.status == PGconn::CONNECTION_BAD)
	printf(STDERR, "We have lost the connection to the backend, so ")
	printf(STDERR, "further processing is impossible.\n")
	printf(STDERR, "Terminating.\n")
    else
	printf(STDERR, conn.error)
    end
    exit(1)
end

[-- Attachment #2: Type: application/pgp-signature, Size: 189 bytes --]