2019-03-25

copy files timestamp

-rw-r--r-- 1 tk staff 40988 3 22 21:58 2.log
-rw-r--r-- 1 tk staff 40988 3 25 03:16 3.log

tk$ touch -r 2.log -m 3.log

-rw-r--r-- 1 tk staff 40988 3 22 21:58 3.log
-rw-r--r-- 1 tk staff 40988 3 22 21:58 2.log

2019-03-04

file and directory counts

count files

find /Users/tk/ -type f | wc -l

count directory

find /Users/tk/ -type d | wc -l

and.. list full path with subDirectory

ls -ltrh -d $(find `pwd`) > list.log

2019-01-30

how to save [jsonb_pretty] format data

1)data is below

prototype=# select jsonb_pretty(memo) from foo

prototype-# where id = 1;

jsonb_pretty

---------------------------

{ +

"name": "japan", +

"currency": "JPY", +

"population": "10000"+

}

(1 row)

2)if you use copy to stdout,

psql -d prototype -c "\COPY (select jsonb_pretty(memo) from foo where id=1) TO STDOUT" >test.json

result are...

cat test2.json

{\n "name": "japan",\n "currency": "JPY",\n "population": "10000"\n}

3)make select script and call psql -c command like this

$ cat sel.sql

select jsonb_pretty(memo) from foo where id=1

and

psql -d prototype -f sel.sql | cat -n > sample.txt

more sample.txt

1 jsonb_pretty

2 ---------------------------

3 { +

4 "name": "japan", +

5 "currency": "JPY", +

6 "population": "10000"+

7 }

8 (1 row)

2019-01-27

how to get and put pdf in bytea (postgres)

only using sql and sql-command

0)main table to store binary file below

prototype=# \d storedpdf
Table "public.storedpdf"

col name	type	description
id	integer	key
name	character varying	file name
pdf	bytea	to store files

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;

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-05-20

delete file if query results not returned


package homeWork;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.io.FileUtils;
import org.dbunit.ext.postgresql.*;

public class dbTest {
	
	private static final String file_path="/Users/tk/foo_selects.json";
	
    public static void main(String[] args) throws Exception {
        	
    		Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;

        //connection string
        String url = "jdbc:postgresql://localhost:5432/prototype";
        String user = "tk";
        String password = "";
        try{
            Class.forName("org.postgresql.Driver");

            //connect db
            conn = DriverManager.getConnection(url, user, password);
            FileOutputStream stream = new FileOutputStream(file_path);
            
            //execute query
            stmt = conn.createStatement();
            String sql = "select memo from foo where id = 99";
            rset = stmt.executeQuery(sql);
            
            //get results
	            while(rset.next()){
	                String col = rset.getString(1);
	                stream.write(rset.getBytes(1));
	                System.out.println(col);
	            }
        }
        catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        catch (SQLException e){
            e.printStackTrace();
        }
        finally {
            try {
                if(rset != null)rset.close();
                if(stmt != null)stmt.close();
                if(conn != null)conn.close();
                //if no results delete 0 byte file
                File tmp = new File(file_path);
        			FileUtils.forceDelete(tmp);
            }
            catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}