1.1 --- a/s4-blog.sh	Thu Dec 05 12:36:28 2019 +0900
     1.2 +++ b/s4-blog.sh	Sat Dec 07 15:36:47 2019 +0900
     1.3 @@ -701,15 +701,36 @@
     1.4        # we take the line as column list.
     1.5        # Otherwise we produce two column CSV as below:
     1.6        # USER,ANSWER
     1.7 +      query "DROP TABLE IF EXISTS tmp_q;"
     1.8        if [ -n "$csvline" ]; then
     1.9 -	:
    1.10 +	query <<-EOF
    1.11 +	  CREATE TEMPORARY TABLE tmp_q("user", $csvline);
    1.12 +	EOF
    1.13 +	if [ $? != 0 ]; then
    1.14 +	  contenttype; echo
    1.15 +	  cat <<-EOF | html p; exit
    1.16 +		掲示板のヘッダにあるCSV定義が不正でCSV出力できません。
    1.17 +		$csvline
    1.18 +		空白なしの項目名を半角カンマ区切りで1行で書いてください。
    1.19 +		EOF
    1.20 +	fi
    1.21        else
    1.22  	query <<-EOF
    1.23 -	  DROP TABLE IF EXISTS tmp_q;
    1.24  	  CREATE TEMPORARY TABLE tmp_q(user text PRIMARY KEY, answer);
    1.25  	EOF
    1.26        fi
    1.27    esac
    1.28 +  if $copy2csv; then
    1.29 +    mkdir $bd/$rid
    1.30 +    outcsv=$bd/$rid/migrate-$rid.csv
    1.31 +    sq "$db" <<-EOF | tr '|' ',' > $outcsv
    1.32 +	SELECT author as "USER",
    1.33 +	       replace(val, x'0a', ',') as "${csvline:-ANSWER}"
    1.34 +	FROM article a JOIN article_s s ON a.id=s.id
    1.35 +	     	AND blogid=(SELECT id FROM blog WHERE rowid=$rid)
    1.36 +	     	AND s.key='text';
    1.37 +	EOF
    1.38 +  fi
    1.39    query <<-EOF |
    1.40  	SELECT a.rowid, a.id artid, a.author, s.val
    1.41  	FROM article a JOIN article_s s ON a.id=s.id
    1.42 @@ -721,11 +742,6 @@
    1.43      mkdir -p $dir
    1.44      echo "$author" > $dir/Author
    1.45      echo "$text" > $dir/Text
    1.46 -    if $copy2csv; then
    1.47 -      query "REPLACE INTO tmp_q SELECT author, val
    1.48 -	   FROM article a NATURAL JOIN article_s s
    1.49 -	   WHERE blogid='$blogid' AND key='text';"
    1.50 -    fi
    1.51      i=0
    1.52      query "SELECT m.rowid, m.val FROM article_m m \
    1.53  	   WHERE id='$artid' AND m.key IN ('image', 'document', 'binary');" \
    1.54 @@ -760,6 +776,7 @@
    1.55      query <<-EOF > $bd/$rid/all-text-$rid.csv
    1.56  	.mode csv
    1.57  	.head 1
    1.58 +	.import $outcsv tmp_q
    1.59  	SELECT * FROM tmp_q;
    1.60  	.mode list
    1.61  	.head 0