Hatena::ブログ(Diary)

Dieu merci


2019-01-27

how to get and put pdf in bytea (postgres)

only using sql and sql-command

<upload>

0)main table to store binary file below
prototype=# \d  storedpdf
                  Table "public.storedpdf"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 id          | integer           |           |          | 
 name    | character varying |           |          | 
 pdf        | bytea             |           |          | 

1)create temporary table

prototype=# create table media(val text);

2)copy hex text stream to table(text)

cat /test/get.pdf | xxd -p | tr -d '\n' | psql -d prototype -c "\COPY media(val)  FROM STDIN"

3)convert text to binary by decode function

prototype=# insert into storedpdf(pdf) select decode(val,'hex') from media;

<download>

psql -d prototype -c "\COPY (select pdf from storedpdf ) TO STDOUT" | xxd -r -p>get.pdf

2018-08-23

sample cat files in directory listed

code:

#!/bin/sh

dir_test=/Users/tk/test/
(
IFS=$'\n';
ls -1 test/ | while read line
do

 for linef in `cat $dir_test$line`
 do

   echo $linef

 done

done
)

2018-08-21

named pipe & combination with application

<parent.sh>

#!/bin/sh


./test.sh &

[ -p /Users/tk/hogepipe ] || sleep 5


while true
do
 #echo "read from pipe"
 if read line </Users/tk/hogepipe; then
   if [[ "$line" == 'quit' ]]; then
     break
   fi
   echo $line
 fi
done

#rm /Users/tk/hogepipe

echo "named pipe removed"
exit 0

<child.sh>

icnt=0
idx_cnt=100


[ -p /Users/tk/hogepipe ] || mkfifo /Users/tk/hogepipe 


exec 3<>/Users/tk/hogepipe
(
IFS=$'\n';
for line in `java -cp Untitled.jar homeWork.outData 10000`
do

 echo ${line}>&3

  ((++icnt))

  if [[ $(($icnt%$idx_cnt)) -eq 0 ]] ; then

    `sh ./rec.sh`
    wait
  fi

done
)
echo "quit" > /Users/tk/hogepipe

#exec 3>&-

exit 0;

2018-08-20

named pipe(2)

parent.sh(read fifo)

#!/bin/sh

./test.sh &

[ -p /Users/tk/hogepipe ] || sleep 5


while true
do
 if read line </Users/tk/hogepipe; then
   if [[ "$line" == 'quit' ]]; then
     break
   fi
   echo $line
 fi
done

#you should parent side...below
#rm /Users/tk/hogepipe

echo "named pipe removed"
exit 0

child.sh(write fifo):

#!/bin/sh


end_word='"e"'
icnt=0
idx_cnt=10
CSV_FILE=csvFile.csv

[ -p /Users/tk/hogepipe ] || mkfifo /Users/tk/hogepipe # make fifo unless 



for line in `cat ${CSV_FILE}`
do

 echo $line>/Users/tk/hogepipe

 col1=`echo ${line} | cut -d ',' -f 1`
 if [ ${col1} = ${end_word} ]; then

  ((++icnt))

  if [[ $(($icnt%$idx_cnt)) -eq 0 ]] ; then

    `sh ./rec.sh`
    wait
  fi
fi

done

echo "quit" > /Users/tk/hogepipe


exit 0;

2018-08-19 a way re-index in running and using named pipe

index rebuild and named pipe

sample table is below:

prototype=# \d test
                           Table "public.test"
  Column  |            Type             | Collation | Nullable | Default 
----------+-----------------------------+-----------+----------+---------
 scheme   | character varying(4)        |           | not null | 
 item     | character varying(4)        |           | not null | 
 value    | text                        |           |          | 
 datetime | timestamp without time zone |           |          | 
Indexes:
    "pk_test" PRIMARY KEY, btree (scheme, item)
    "new_idx" btree (item)

rebuild script: test.sql

  1 CREATE INDEX CONCURRENTLY tmp_idx
  2 ON public.test USING btree
  3     (item COLLATE pg_catalog."default");
  4 BEGIN;
  5 DROP INDEX new_idx;
  6 ALTER INDEX tmp_idx RENAME TO new_idx;
  7 COMMIT;

execute script:

$ psql  prototype tk -f test.sql

option recreate primary key:

  1 --original pkey --> tmp pkey
  2 CREATE UNIQUE INDEX CONCURRENTLY pkey_test_tmp ON test (scheme,item);
  3 
  4 ALTER TABLE test DROP CONSTRAINT pkey_test,ADD PRIMARY KEY USING INDEX pkey_test_tmp;
  5 
  6 --tmp pkey ---> original pkey
  7 CREATE UNIQUE INDEX CONCURRENTLY pkey_test ON test (scheme,item);
  8 
  9 ALTER TABLE test DROP CONSTRAINT pkey_test_tmp,ADD PRIMARY KEY USING INDEX pkey_test;


child: test.sh

  1 #!/bin/sh
  2 
  3 
  4 
  5 #end_wordをカウントし、10こごとに
  6 #sleepしながら名前付きパイプに
  7 #レコードを書き込む
  8 
  9 end_word='"e"'
 10 #echo $end_word
 11 icnt=0
 12 idx_cnt=10
 13 CSV_FILE=csvFile.csv
 14 
 15 [ -p /Users/tk/hogepipe ] || mkfifo /Users/tk/hogepipe # 名前付きパイプを作る
 16 
 17 for line in `cat ${CSV_FILE}`
 18 do
 19  echo $line > /Users/tk/hogepipe
 20  col1=`echo ${line} | cut -d ',' -f 1`
 21  if [ ${col1} = ${end_word} ]; then
 22 
 23   ((++icnt))
 24 
 25   if [[ $(($icnt%$idx_cnt)) -eq 0 ]] ; then
 26     #echo $line
 27     echo "${icnt} completed then reindex start" > /Users/tk/hogepipe
 28     sleep 20
 29   fi
 30 fi
 31 
 32 done
 33 
 34 echo "end" > /Users/tk/hogepipe


parent : p_test.sh

  1 #!/bin/sh
  2 
  3 
  4 ./test.sh &
  5 
  6 while :; do
  7  echo "read from pipe"
  8  cat /Users/tk/hogepipe
  9 
 10 done