Re: ACFinder 140105test exe のみ

このトピックの投稿一覧へ

なし Re: ACFinder 140105test exe のみ

msg# 1.3.1.1.1.1.1.1
depth:
7
前の投稿 - 次の投稿 | 親投稿 - 子投稿なし | 投稿日時 2014.01.06 15:54
OhYeah!  管理人   投稿数: 983 オンライン
ZIP 解凍しか試してませんが、FAMIC CSV と MACS SQLite DB の解凍は OK でした。

ところで、薬剤タブの成分テーブルについては、1成分1行表示のためには新 rac ビューによる表示しか方法がありませんが、作物タブ、病害虫タブについては、対象病害虫のカテゴリに合わせた RAC コードのみを表示した方がより親切で分かりやすいです。これを可能にするため、rac_mg に病害虫カテゴリ ID を追加し、新しい raccode というビューを作りました。
新しいテーブルとビューは、SQL タブで下記コードを実行すれば作成できます。が、すでに spec.frac.txt, spec.irac.txt, spec.hrac.txt で新テーブルとビューを作成するようにしてあるので、ACFinder 側でテンポラリテーブルとビューを作成しないようにしてもらえば OK です。

--/d
/* パーマネント統合テーブル&ビュー作成 */
--旧バージョン個別ビュー削除
drop view if exists spec.frac_g;
drop view if exists spec.frac;
drop view if exists spec.irac_g;
drop view if exists spec.irac;
--ACFinderテンポラリテーブル&ビュー削除 
drop table if exists temp.rac_mg;
drop table if exists temp.rac_sg;
drop table if exists temp.rac_ai;
drop view if exists temp.rac_g;
drop view if exists temp.rac;
--新バージョンテーブル&ビュー作成 
create table if not exists spec.rac_mg (cid integer, mid varchar primary key, sayoten varchar, sayokiko varchar, risk varchar);
delete from spec.rac_mg where mid like 'F:%';
insert into spec.rac_mg select 1, 'F:'||mid, sayoten, sayokiko, risk from frac_mg;
delete from spec.rac_mg where mid like 'I:%';
insert into spec.rac_mg select 2, 'I:'||mid, sayoten, sayokiko, null from irac_mg;
delete from spec.rac_mg where mid like 'H:%';
insert into spec.rac_mg select 3, 'H:'||mid, sayoten, null, null from hrac_mg;
create table if not exists spec.rac_sg (mid varchar, sid varchar primary key, keito varchar, fgroup varchar);
delete from spec.rac_sg where sid like 'F:%';
insert into spec.rac_sg select 'F:'||mid as mid, 'F:'||sid as sid, keito, fgroup from frac_sg;
delete from spec.rac_sg where sid like 'I:%';
insert into spec.rac_sg select 'I:'||mid as mid, 'I:'||sid as sid, keito, null from irac_sg;
delete from spec.rac_sg where sid like 'H:%';
insert into spec.rac_sg select 'H:'||mid as mid, 'H:'||sid as sid, keito, null from hrac_sg;
create table if not exists spec.rac_ai (sid varchar, ippanmei varchar);
delete from spec.rac_ai where sid like 'F:%';
insert into spec.rac_ai select 'F:'||sid as sid, ippanmei from frac_ai;
delete from spec.rac_ai where sid like 'I:%';
insert into spec.rac_ai select 'I:'||sid as sid, ippanmei from irac_ai;
delete from spec.rac_ai where sid like 'H:%';
insert into spec.rac_ai select 'H:'||sid as sid, ippanmei from hrac_ai;
drop index if exists spec.idxrac;
create index spec.idxRac on rac_ai (ippanmei);
create view if not exists spec.rac_g as select cid, mid, sid, keito, sayoten, sayokiko, fgroup, risk from rac_sg left join rac_mg using(mid);
create view if not exists spec.rac as select ippanmei, concat(', ', keito) as keito, concat(', ', mid) as mid, concat(', ', sayoten) as sayoten, concat(', ', sayokiko) as sayokiko, fgroup, risk from rac_ai left join rac_g using(sid) group by ippanmei;
create view if not exists spec.raccode as select cid, mid, ippanmei from rac_ai left join rac_g using(sid);


で、作物タブなら、ttTekiyoSaku を下記のように作成すれば、病害は FRAC、害虫は IRAC、雑草は HRAC の分類コードのみが表示されるようになります。病害虫タブの ttTekiyoByochu も同様です。
--/d
DROP TABLE IF EXISTS ttTekiyoSaku;
CREATE TEMP TABLE ttTekiyoSaku AS SELECT
  bango,shurui,meisho,tsusho,idsaku,sakumotsu,idbyochu,byochu,mokuteki,jiki,baisu,ekiryo,hoho,basho,jikan,ondo,dojo,chitai,tekiyaku,kongo,kaisu,
  seibun1,keito1,kaisu1,(select mid from raccode where ippanmei = seibun1 and cid = substr(idbyochu, 1, 1)) as mid1,
  seibun2,keito2,kaisu2,(select mid from raccode where ippanmei = seibun2 and cid = substr(idbyochu, 1, 1)) as mid2,
  seibun3,keito3,kaisu3,(select mid from raccode where ippanmei = seibun3 and cid = substr(idbyochu, 1, 1)) as mid3,
  seibun4,keito4,kaisu4,(select mid from raccode where ippanmei = seibun4 and cid = substr(idbyochu, 1, 1)) as mid4,
  seibun5,keito5,kaisu5,(select mid from raccode where ippanmei = seibun5 and cid = substr(idbyochu, 1, 1)) as mid5,
  yoto,koka,zaikei,ryakusho
FROM tekiyo WHERE idSaku IN (SELECT idSaku FROM m_sakumotsu WHERE idSaku REGEXP ...);
--/d
DROP TABLE IF EXISTS ttTekiyoByochu;
CREATE TEMP TABLE ttTekiyoByochu AS SELECT
  bango,shurui,meisho,tsusho,idsaku,sakumotsu,idbyochu,byochu,mokuteki,jiki,baisu,ekiryo,hoho,basho,jikan,ondo,dojo,chitai,tekiyaku,kongo,kaisu,
  seibun1,keito1,kaisu1,(select mid from raccode where ippanmei = seibun1 and cid = substr(idbyochu, 1, 1)) as mid1,
  seibun2,keito2,kaisu2,(select mid from raccode where ippanmei = seibun2 and cid = substr(idbyochu, 1, 1)) as mid2,
  seibun3,keito3,kaisu3,(select mid from raccode where ippanmei = seibun3 and cid = substr(idbyochu, 1, 1)) as mid3,
  seibun4,keito4,kaisu4,(select mid from raccode where ippanmei = seibun4 and cid = substr(idbyochu, 1, 1)) as mid4,
  seibun5,keito5,kaisu5,(select mid from raccode where ippanmei = seibun5 and cid = substr(idbyochu, 1, 1)) as mid5,
  yoto,koka,zaikei,ryakusho
FROM tekiyo WHERE bango IN (SELECT bango FROM m_tekiyo WHERE byochu IN (SELECT byochu FROM tByochu) OR mokuteki IN (SELECT byochu FROM tByochu));
投票数:0 平均点:0.00

投稿ツリー

  条件検索へ