SQLにてデータをインポート
カテゴリ情報
begin; insert into dtb_category(category_name, parent_category_id, "level", creator_id, del_flg) select distinct c058 as category_name , 0 as parent_category_id, 1 as "level", 0 as creator_id, 0 as del_flg from wrk_articles; commit;
もしもドロップシッピングをECCubeを使って管理したい(2)
続きです。
なぜか、商品CSVアップロードからアップロードできませんでした。新しいウィンドウが開き、しばらくすると商品CSVアップロード画面が再度表示されてしまう。該当するソース(/data/class/pages/admin/products/LC_Page_Admin_Products_UploadCSV.php)を眺めていても、特に・・・と思っていたら、ピーンと来ました。post_max_size と upload_max_filesizeがアップロードするファイルに対して（85M!!)小さすぎました。
ということで、php.iniの値を変更するとともに、/html/.htaccessのphp_value upload_max_filesizeを100Mに変更し、再度トライ。結果、「CSVファイルではありません」とのこと。いずれにしても85Mのアップロードは、現実的ではないので、直接SQLをたたくようにしようと方向転換します。
ちなみに、同じところで詰まっていた人の情報で、下記を修正する必要があるようです。SQLを直接たたくなら、必要ないかも。
/data/class/pages/admin/products/LC_Page_Admin_Products_UploadCSV.php
の229行目付近のにCSVの項目が記述されていますが
ここで必須項目（存在の有無）を確認する"EXIST_CHECK" "FILE_EXISTS"を削除するとできました。
$this->objFormParam->addParam("メイン画像", "main_image", LTEXT_LEN, "KVa", array("EXIST_CHECK","FILE_EXISTS","SPTAB_CHECK","MAX_LENGTH_CHECK"));
↓
$this->objFormParam->addParam("メイン画像", "main_image", LTEXT_LEN, "KVa", array("SPTAB_CHECK","MAX_LENGTH_CHECK"));
もしもドロップシッピングをECCubeを使って管理したい(1)
このご時世、ちょっとでもお金を稼ごうと思いまして、もしもドロップシッピングをECCubeを利用して管理しようと考えました。
今回は、商品データベースにデータを入れます。
-- ダウンロードしたデータをインポートするテーブル CREATE TABLE articles ( goods_id integer NOT NULL, -- 商品ID goods_name text, category_name text, tag_name text, maker_name text, goods_number text, wholesale_price integer, low_price integer, selling_price integer, recommended_price integer, catch_copy text, shop_explain text, goods_explain text, spec text, image_code text, image_num integer, same_parcel text, settlement_method text, last_update timestamp with time zone, CONSTRAINT articles_pkey PRIMARY KEY (goods_id) ) -- ECCube対応のCSV出力を行うために利用するテーブル CREATE TABLE wrk_articles ( c001 text NOT NULL, c002 text, c003 text, c004 text, c005 text, c006 text, c007 text, c008 text, c009 text, c010 text, c011 text, c012 text, c013 text, c014 text, c015 text, c016 text, c017 text, c018 text, c019 text, c020 text, c021 text, c022 text, c023 text, c024 text, c025 text, c026 text, c027 text, c028 text, c029 text, c030 text, c031 text, c032 text, c033 text, c034 text, c035 text, c036 text, c037 text, c038 text, c039 text, c040 text, c041 text, c042 text, c043 text, c044 text, c045 text, c046 text, c047 text, c048 text, c049 text, c050 text, c051 text, c052 text, c053 text, c054 text, c055 text, c056 text, c057 text, c058 text, CONSTRAINT wrk_articles_pkey PRIMARY KEY (c001) )
手順
１．もしもドロップシッピングよりデータ(articles.csv)をダウンロードする(https://www.moshimo.com/shop/download#specific-url-article-image)
２．articles.csvをインポート
set client_encoding to ‘SJIS′; COPY articles FROM E'C:\\tmp\\articles.txt' with CSV;
３．ワークテーブルにデータを挿入する
INSERT INTO wrk_articles( c001, c002, c003, c004, c005, c006, c007, c008, c009, c010, c011, c012, c013, c014, c015, c016, c017, c018, c019, c020, c021, c022, c023, c024, c025, c026, c027, c028, c029, c030, c031, c032, c033, c034, c035, c036, c037, c038, c039, c040, c041, c042, c043, c044, c045, c046, c047, c048, c049, c050, c051, c052, c053, c054, c055, c056, c057, c058) SELECT goods_id as c001 , -- 商品ID goods_id goods_number as c002 , -- 商品規格ID goods_number null as c003 , -- 規格名1 null as c004 , -- 規格名2 goods_name as c005 , -- 商品名(※ 必須) goods_name null as c006 , -- 公開フラグ(1:公開 2:非公開)(※ 必須) null as c007 , -- 商品ステータス(※ 必須) null as c008 , -- 商品コード recommended_price as c009 , -- 通常価格 recommended_price selling_price as c010 , -- 販売価格(※ 必須) selling_price null as c011 , -- 在庫数 null as c012 , -- 送料 null as c013 , -- ポイント付与率(※ 必須) null as c014 , -- 購入制限 maker_name as c015 , -- メーカーURL maker_name null as c016 , -- 検索ワード tag_name null as c017 , -- 備考欄(SHOP専用) shop_explain,wholesale_price,low_price,image_num catch_copy as c018 , -- 一覧-メインコメント(※ 必須) catch_copy 'http://image.moshimo.com/item_image/'||image_code||'/1/m.jpg' as c019 , -- 一覧-メイン画像(※ 必須) http://image.moshimo.com/item_image/image_code/1/m.jpg goods_explain as c020 , -- メインコメント(※ 必須) goods_explain 'http://image.moshimo.com/item_image/'||image_code||'/1/m.jpg' as c021 , -- メイン画像(※ 必須) http://image.moshimo.com/item_image/image_code/1/m.jpg 'http://image.moshimo.com/item_image/'||image_code||'/1/l.jpg' as c022 , -- メイン拡大画像 http://image.moshimo.com/item_image/image_code/1/l.jpg null as c023 , -- カラー比較画像 null as c024 , -- 商品詳細ファイル 'スペック' as c025 , -- 詳細-サブタイトル(1) スペック spec as c026 , -- 詳細-サブコメント(1) spec null as c027 , -- 詳細-サブ画像(1) null as c028 , -- 詳細-サブ拡大画像(1) '同梱可否' as c029 , -- 詳細-サブタイトル(2) 同梱可否 'same_parcel' as c030 , -- 詳細-サブコメント(2) same_parcel null as c031 , -- 詳細-サブ画像(2) null as c032 , -- 詳細-サブ拡大画像(2) '決済方法' as c033 , -- 詳細-サブタイトル(3) "決済方法 " settlement_method as c034 , -- 詳細-サブコメント(3) settlement_method null as c035 , -- 詳細-サブ画像(3) null as c036 , -- 詳細-サブ拡大画像(3) '最終更新日時' as c037 , -- 詳細-サブタイトル(4) 最終更新日時 last_update as c038 , -- 詳細-サブコメント(4) last_update null as c039 , -- 詳細-サブ画像(4) null as c040 , -- 詳細-サブ拡大画像(4) null as c041 , -- 詳細-サブタイトル(5) null as c042 , -- 詳細-サブコメント(5) null as c043 , -- 詳細-サブ画像(5) null as c044 , -- 詳細-サブ拡大画像(5) null as c045 , -- 発送日目安 null as c046 , -- おすすめ商品(1) null as c047 , -- 詳細-サブコメント(1) null as c048 , -- おすすめ商品(2) null as c049 , -- 詳細-サブコメント(2) null as c050 , -- おすすめ商品(3) null as c051 , -- 詳細-サブコメント(3) null as c052 , -- おすすめ商品(4) null as c053 , -- 詳細-サブコメント(4) null as c054 , -- おすすめ商品(5) null as c055 , -- 詳細-サブコメント(5) null as c056 , -- おすすめ商品(6) null as c057 , -- 詳細-サブコメント(6) category_name as c058 -- 商品カテゴリ(※ 必須) category_name FROM public.articles;
４．CSVファイルとしてエクスポートする
COPY wrk_articles TO E'c:\\tmp\\import.txt' DELIMITERS ',';
なお、wrk_articlesテーブルで確認するSQLは下記通り。
SELECT c001, c005, c006, c007, c010, c013, c018, c019, c020, c021, c058 FROM wrk_articles where c005 = null or c006 = null or c007 = null or c010 = null or c013 = null or c018 = null or c019 = null or c020 = null or c021 = null or c058 = null
エクスポートデータしたデータを商品インポートよりインポートにて完了・・・と思いきや、なぜかうまくいかない・・・。疲れたから、今日はこれにて終了。
PostgreSQLのCSVからのインポート処理についてのまとめ
-- 「\」1つではエスケープされるのでだめ COPY tablename FROM 'C:\test\testdata.csv' WITH CSV; -- WARNING: nonstandard use of escape in a string literal -- ERROR: could not open file "C:testtestdata.csv" for reading: No such file or directory -- 「\」2つでもなぜかエラーが出てだめ、ではなくて、警告が出るが実行はOK COPY tablename FROM 'C:\\test\\testdata.csv' WITH CSV; -- WARNING: nonstandard use of \\ in a string literal -- HINT: Use the escape string syntax for backslashes, e.g., E'\\'. -- ヒントにあるように「E」をつければ警告なしで実行OK COPY tablename FROM E'C:\\test\\testdata.csv' WITH CSV; -- パスに日本語が入っているとだめ(マルチバイトへの何らかの対応が必要？) COPY tablename FROM 'C:/Documents and Settings/User/デスクトップ/testdata.csv' WITH CSV; -- ERROR: could not open file "C:/Documents and Settings/User/デスクトップ/testdata.csv" for reading: No such file or directory -- 文字コードがファイルとDBで異なっていてもエラーとなります。 -- ERROR: invalid byte sequence for encoding "UTF8" --EXCELで作成したCSVだとShift-JISになってしまうので、エディタで変換して保存し直すか、下記にて対応 SET client_encoding TO 'SJIS'; COPY tablename FROM 'C:/test/testdata.csv' WITH CSV;
デミリタを変えれば、ダブルコーテーション問題は解決。
・・・と思っていたら、
ERROR: extra data after last expected column
と、エラーメッセージが出力される。
結論。
COPY test FROM '/home/postgres/test.csv' WITH CSV;
で解決。
インポートは、
set client_encoding to ‘SJIS′; COPY articles FROM E'C:\\tmp\\articles.txt' with CSV;
でOK。
PHPと設定ファイルについて
PHPで定数を設定するファイルを何にしようか考え中。
今は、通常のPHPファイルに宣言していて、特に不満はないのだけれど、外部ファイル化することで、ソースに手を入れなくても設定が変更できるのは捨てがたい。
iniファイルは、PHPに読み込み、書き込みの関数が用意されているので、簡単に使えるのだが、フォーマットの仕様上、配列のような使い方や、定数のネスト表現ができない。ちょっと不満が残る。
YAMLファイルはフォーマットがわかりやすいイメージがあるのだけれど、ライブラリを別途用意しないと利用できない（もちろん自分で作ることも可能だけれども）ので、バージョンアップなどを考え、二の足を踏んでいる。
となると、PHP5からバンドルされているsimplexmlを利用して、XML形式で作成するのがよさそうなのだが、XMLファイルって何気に面倒くさい。
今だ、悩み中。
お勉強
MySQLとPostgreSQLの型の違い
数値データ型について
PostgreSQL smallint、int、bigint、decimal、numeric MySQL tinyint、smallint、mediumint、int、bigint、decimal、numeric
smallint,int,bigint,decimal,numericは共用できそうだ。ただし、桁数が違う可能性があるので注意が必要。
(http://www.thinkit.co.jp/free/article/0703/13/1/)
文字列型
PostgreSQL char、varchar、text MySQL char、varchar、text、mediumtext、longtext
char、varchar、textは共用できる。
MySQLは、char型は最大255文字の制限が明確になっており、それ以上はtext型を使う。一方、PostgreSQLは1,000万文字ほどの文字列をchar型に格納できる。ここはMySQLに準拠したほうがよさそうだ。
PostgreSQLのtext型は無制限だが、MySQLのtext型は65535文字までしか格納できない。text型についても、MySQLに合わせる。
日付型
PostgreSQL date、time、timestamp、interval MySQL date、time、timestamp、datetime、year
date、time、timestampは共用可能。日付型は自動更新が可能だが、DBの種類とバージョンによって指定方法が異なる。
PostgreSQLの場合は、デフォルト制約で「current_timestamp」を定義しておく必要がある。また、自動更新は新規挿入時のみ(insert)で、更新時(update)には更新されない。
一方、MySQLのtimestamp型は、INSERTやUPDATEによって現在の日付時刻を自動で更新する。
MySQL(Ver4.x)では、システム変数は指定できず、定数のみ指定できない。(http://oshiete1.goo.ne.jp/qa1176802.html)
ただし、TIMESTAMP型にすれば、一つのテーブルにつき一つだけ値を自動更新可能。(http://www.mysql.gr.jp/Manual/mysql-4.00.12/manual.ja_Reference.htm...)
以上を踏まえ、日付更新処理はDB側の自動更新を使わずアプリ側で対応したほうが無難と言える。
また、格納する日付書式が間違っている場合、PostgreSQLはエラーを返すがMySQLは「0000-00-00」といった値（といった値って何だろう？）が格納されるようだ。
これは、MySQLが「SQL Modes」と呼ばれるMySQLサーバの設定状態があるため。PostgreSQLでも同様に設定可能なので、DBのチェックに頼らず、アプリ側で日付書式チェックを行う。また、日付型を使わず、文字列型で対応することも検討。
# Selectするときにキャストするのが面倒なんだよなぁ
参照URL
- http://www.thinkit.co.jp/free/article/0603/10/1/
- http://www.thinkit.co.jp/free/article/0703/13/1/
- http://labs.s-cubism.com/blog/2009/03/13/82/
- http://oshiete1.goo.ne.jp/qa1176802.html
- http://www.atmarkit.co.jp/flinux/rensai/mysql5_06/mysql5_06d.html
- http://www.magic3.org/doc/index.php?MySQL%E3%81%A8PostgreSQL%E3%81%AE%E9%81%95%E3%81%84
- http://blog.flup.jp/2007/11/30/php_capsule_library/
- http://www.mygenerationsoftware.com/portal/default.aspx
SQL文
おおむね、PostgreSQLに合わせておけば大丈夫。というか、標準の(SQL99だっけ？）に準拠すれば、大体の場合は大丈夫だと思われる。
文字コード
PostgreSQL 8.1.8 EUC-JP UTF-8 MySQL 5.0.22 EUC-JP Shift-JIS UTF-8 UCS-2 cp932
ビュー、ストアドプロシージャ、トリガー
ビューは、閲覧目的で利用するのみとしたい。更新対応まで考えると挙動を合わせるのが複雑になる可能性があるから。
ストアドプロシージャ、トリガーとも、基本的には利用しない方向にしたい。利用する場合には、個別対応とすること。
PostgreSQLのメタデータを取得するためのSQL
select * from pg_tables where not tablename like 'pg%' and schemaname like 'public' order by tablename ; select attname, atttypid, attlen from pg_attribute where attnum > 0 and attrelid = (select relfilenode from pg_class where relname = 'tbl_news') order by attnum ; select * from pg_class where relname = 'tbl_news' SELECT sut.relname, pg_attribute.attnum AS NUM, pg_attribute.attname AS COL_NAME, typ.typname AS COL_TYPE, CASE typ.typname WHEN 'varchar' THEN pg_attribute.atttypmod-4 WHEN 'bpchar' THEN pg_attribute.atttypmod-4 WHEN 'numeric' THEN pg_attribute.atttypmod/65536 WHEN 'date' THEN pg_attribute.attlen WHEN 'timestamp' THEN pg_attribute.attlen END AS COL_LENGTH, pg_attribute.attnotnull FROM pg_stat_user_tables sut inner join pg_attribute on pg_attribute.attrelid = sut.relid inner join pg_type typ on pg_attribute.atttypid = typ.oid WHERE pg_attribute.attnum > 0 ORDER BY sut.relid , pg_attribute.attnum
