src/aoi.cxx

   1 /*
   2     Copyright 2013 Karel Matas
   3
   4     This program is free software: you can redistribute it and/or modify
   5     it under the terms of the GNU General Public License as published by
   6     the Free Software Foundation, either version 3 of the License, or
   7     (at your option) any later version.
   8
   9     This program is distributed in the hope that it will be useful,
  10     but WITHOUT ANY WARRANTY; without even the implied warranty of
  11     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12     GNU General Public License for more details.
  13
  14     You should have received a copy of the GNU General Public License
  15     along with this program.  If not, see <http://www.gnu.org/licenses/>.
  16 */
  17 #include <cmath>
  18 #include <set>
  19 #include <FL/fl_ask.H>
  20 #include "aoi.hxx"
  21 #include "utils.hxx"
  22
  23 namespace aoi {
  24
  25 using std::set;
  26 using aoi_ui::TextStyle;
  27 using utils::to_string;
  28
  29 //! See App::load_dbscript() and App::App()
  30 const char *DB_BACKUP_FILE            = "db.backup";
  31 const char *LOG_FILE                  = "aoi.log";
  32 const char SENSE_SEARCH_CHAR          = ':';
  33
  34 App* App::instance_ = nullptr;
  35
  36 App::App ()
  37 {
  38   remove(LOG_FILE);
  39   logger_.filename(LOG_FILE);
  40   logger_.loglevel(Logger::MSG_DEBUG);
  41
  42   db_   = nullptr;  // NECESSARY ( if db_ undefined then SEGFAULT in open_database() )
  43   cfg_  = new aoi_config::Config();
  44   ui_   = new aoi_ui::GUI(this);
  45   rmn_  = new Romanization();
  46
  47   if ( utils::file_exists( DB_BACKUP_FILE ) ){
  48     log_w("Backup copy of the database found.");
  49     std::stringstream ss;
  50     ss << "Backup copy of the database found. Delete it?\n"
  51       << "WARNING: You should check whether the current database\n"
  52       << "works before deleting the backup.";
  53     if ( ui_->choice(ss.str(), "Delete", "No") == 0 ){
  54       log("Deleting the backup copy of the database.");
  55       remove(DB_BACKUP_FILE);
  56     }
  57   }
  58
  59   ui_->progress(0,"Opening database...");
  60   open_database();
  61
  62   ui_->progress( 30, "Checking tables..." );
  63   check_tables();
  64   ui_->progress( 60, "Checking indexes..." );
  65   check_indexes();
  66
  67   load_config();
  68   init_dicview();
  69
  70   #ifdef DEBUG
  71 //    ui_->progress(90, "DEBUG query..." );
  72 //    parse_dic_input("ana*");
  73 //    cb_kanji_search();
  74 //    ui_->cb_toggle_group(nullptr);
  75   #endif
  76
  77   ui_->progress( 98, "Initializing fonts..." );
  78   ui_->fontname_kanji( get_config("font/kanji") );
  79   ui_->help_file( get_config("sources/help_index") );
  80
  81   ui_->progress_hide();
  82
  83
  84   auto q = query("select val from aoi where key='jmdict_version'");
  85   string jmdict_version = (q.empty()) ? "NONE":q[0];
  86   q = query("select val from aoi where key='kanjidic_version'");
  87   string kanjidic_version = q.empty() ? "NONE":q[0];
  88   if ( jmdict_version == "NONE" || kanjidic_version == "NONE" )
  89     cb_manage_db();
  90
  91
  92   #ifdef DEBUG
  93     logger_.loglevel(Logger::MSG_DEBUG);
  94   #endif
  95
  96   instance_ = this;
  97 }
  98
  99
 100 App::~App ()
 101 {
 102   delete db_;
 103   delete rmn_;
 104   delete ui_;
 105   delete cfg_;
 106 }
 107
 108
 109 void App::init_dicview ()
 110 {
 111   // initialize textstyles
 112   TextStyle style_default(FL_HELVETICA,1.2);
 113   TextStyle style_reading(aoi_ui::FONT_KANJI,1.5);
 114   TextStyle style_reading_freq = style_reading;
 115   style_reading_freq.color = get_color("frequent");
 116   TextStyle style_kanji(aoi_ui::FONT_KANJI,1.7);
 117   TextStyle style_kanji_freq = style_kanji;
 118   style_kanji_freq.color = get_color("frequent");
 119   TextStyle style_inf(FL_HELVETICA_ITALIC,0.8,get_color("pos"));
 120   style_inf.offset_y = 3;
 121   TextStyle style_pos = style_inf;
 122   TextStyle style_misc = style_pos;
 123   style_misc.color = get_color("misc");
 124   TextStyle style_field = style_pos;
 125   style_field.color = get_color("field");
 126   TextStyle style_dial = style_pos;
 127
 128   // register TextStyles
 129   ui_->register_tag_dicview( "default",       style_default );
 130   ui_->register_tag_dicview( "reading",       style_reading );
 131   ui_->register_tag_dicview( "reading_freq",  style_reading_freq );
 132   ui_->register_tag_dicview( "kanji",         style_kanji );
 133   ui_->register_tag_dicview( "kanji_freq",    style_kanji_freq );
 134   ui_->register_tag_dicview( "kinf",          style_inf );
 135   ui_->register_tag_dicview( "rinf",          style_inf );
 136   ui_->register_tag_dicview( "pos",           style_pos );
 137   ui_->register_tag_dicview( "misc",          style_misc );
 138   ui_->register_tag_dicview( "field",         style_field );
 139   ui_->register_tag_dicview( "dial",          style_dial );
 140 }
 141
 142
 143 vector<string> App::query ( const char *q, bool log_query, bool replace_separator )
 144 {
 145   if ( !db_ ){
 146     log_e("App::query(): Database does not exist.");
 147     return {};
 148   }
 149   ui_->cursor_wait();
 150   vector<string> result;
 151   try {
 152     if ( log_query )
 153       log_d(string(q));
 154     result = db_->query(q);
 155   }
 156   catch (SQLite3::DatabaseError &e){
 157     log_e("App: DatabaseError:: " + string(e.what()) + string("\nQuery: ")
 158       + string(e.query()) );
 159     ui_->cursor_default();
 160   }
 161   string msg = std::to_string(db_->result_rows()) + " results";
 162   log( "App::query(): " + msg);
 163   if ( replace_separator )
 164     for ( string &s: result )
 165       utils::replace_all(s, SEPARATOR_SQL, ", ");
 166   ui_->cursor_default();
 167   return result;
 168 }
 169
 170
 171 void App::open_database ()
 172 {
 173   try {
 174     if ( !db_ )
 175       db_ = new SQLite3::SQLite3( get_config("db/file_main").c_str() );
 176     else{
 177       db_->close();
 178       db_->open(get_config("db/file_main").c_str());
 179     }
 180   }
 181   catch (SQLite3::CantOpenDatabase &e){
 182     log_e( "Aoi: Can't open database '"  + string(e.what()) + "'.");
 183     return;
 184   }
 185   std::stringstream ss;
 186     ss << "ATTACH DATABASE '" << get_config("db/file_user") << "'as user;";
 187   db_->query(ss.str().c_str());
 188 }
 189
 190
 191 void App::cb_set_components ()
 192 {
 193   if ( curr_components_.empty() )
 194     return;
 195
 196   using aoi_ui::ComponentView;
 197
 198   // prepare cells
 199   vector<string> included = ui_->components_include();
 200   vector<string> excluded = ui_->components_exclude();
 201
 202   vector<ComponentView::Cell> v;
 203   for ( string &s: included )
 204     v.push_back( ComponentView::Cell(s,ComponentView::CELL_SELECTED_1) );
 205   for ( string &s: excluded )
 206     v.push_back( ComponentView::Cell(s,ComponentView::CELL_SELECTED_2) );
 207
 208   // sort by occurences
 209   if ( !ui_->sort_components_by_strokes() ){
 210     for ( auto mi = curr_components_.rbegin(); mi!=curr_components_.rend(); ++mi ){
 211       if ( !utils::is_in( included, mi->second )
 212             && !utils::is_in( excluded, mi->second ) )
 213         v.push_back( ComponentView::Cell(mi->second) );
 214     }
 215   }
 216   // sort by strokes
 217   else {
 218     vector<string> comps_by_strokes;
 219     for ( auto mi: curr_components_ ){
 220       if ( mi.first < get_config<int>("knj/min_compo_count") )
 221         continue;
 222       if ( !utils::is_in( included, mi.second )
 223             && !utils::is_in( excluded, mi.second ) )
 224         comps_by_strokes.push_back(mi.second);
 225     }
 226
 227     struct SortByStrokes {
 228       map<string,int> comps;
 229       SortByStrokes( const map<string,int> &c ): comps(c){};
 230       bool operator() (const string &c1, const string &c2 ){
 231         return ( this->comps[c1] < this->comps[c2] );
 232       }
 233     } sbc(components_);
 234
 235     std::sort( comps_by_strokes.begin(), comps_by_strokes.end(), sbc );
 236
 237     int prev_strokes=0;
 238     for ( string &s: comps_by_strokes ){
 239       int curr_strokes = components_[s];
 240       if ( prev_strokes != curr_strokes )
 241         v.push_back( ComponentView::Cell( std::to_string(curr_strokes),
 242                                           ComponentView::CELL_LABEL) );
 243       v.push_back( ComponentView::Cell(s) );
 244       prev_strokes = curr_strokes;
 245     }
 246   }
 247
 248   ui_->set_components( v );
 249 }
 250
 251
 252 void App::cb_kanji_search ()
 253 {
 254
 255   if ( components_.empty() ){
 256     log("Loading components...");
 257     vector<string> res = query("select component, strokes from components");
 258     int skipped = 0;
 259     for ( size_t i=0; i<res.size(); i=i+2 ){
 260       int strokes = 0;
 261       if ( res[i+1].empty() )
 262         skipped++;
 263       else
 264         strokes = std::stoi(res[i+1]);
 265       components_[res[i]] = strokes;
 266     }
 267     if ( skipped>0 )
 268       log_d(std::to_string(skipped)+" components without strokes data.");
 269   }
 270
 271   // strokes
 272   std::pair<int,int> strokes = utils::parse_range( utils::strip( ui_->strokes() ) );
 273   // jlpt
 274   std::pair<int,int> jlpt = utils::parse_range( utils::strip( ui_->jlpt() ) );
 275   // grade
 276   std::pair<int,int> grade = utils::parse_range( utils::strip( ui_->grade() ) );
 277
 278   // skip
 279   vector<string> skip = utils::split_string( ui_->skip() );
 280   std::stringstream sskip;
 281   if ( skip.size() > 0 ){
 282     string skip1 = utils::strip(skip[0].c_str());
 283     sskip << " S.skip1=" << skip1;
 284   }
 285   if ( skip.size() > 1 ){
 286     std::pair<int,int> skip2 = utils::parse_range( utils::strip(skip[1].c_str()) );
 287     sskip << " and S.skip2>=" << skip2.first << " and S.skip2<=" << skip2.second;
 288   }
 289   if ( skip.size() > 2 ){
 290     std::pair<int,int> skip3 = utils::parse_range( utils::strip(skip[2].c_str()) );
 291     sskip << " and S.skip3>=" << skip3.first << " and S.skip3<=" << skip3.second;
 292   }
 293   if ( !sskip.str().empty() )
 294     sskip << " and ";
 295
 296   // ordering
 297   string order_by;
 298   switch ( ui_->sort_mode() ){
 299     case SORT_FREQ_ASC:
 300       order_by = "(case freq when 0 then 9999 else freq end) asc";
 301       break;
 302     case SORT_FREQ_DESC:
 303       order_by = "(case freq when 0 then 9999 else freq end) desc";
 304       break;
 305     case SORT_STROKES_ASC:
 306       order_by = "strokes asc";
 307       break;
 308     case SORT_STROKES_DESC:
 309       order_by = "strokes desc";
 310       break;
 311   }
 312
 313   vector<string> components_include = ui_->components_include();
 314   vector<string> components_exclude = ui_->components_exclude();
 315   std::stringstream comps;
 316   for ( auto c: components_include )
 317     comps << " and components like '%" << c << "%'";
 318   for ( auto c: components_exclude )
 319     comps << " and components not like '%" << c << "%'";
 320   printf("%s\n",comps.str().c_str());
 321
 322   string jis208 = "";
 323   if ( get_config<bool>("knj/jis208_only") )
 324     jis208 = " flags glob '*jis208*' and ";
 325
 326   // build query
 327   std::stringstream ss;
 328   ss  <<  "select distinct "
 329       <<    "K.kanji,freq,components,flags "
 330       <<  " from k_kanji as K"
 331       <<    (sskip.str().empty() ? " where ":", k_skip as S where K.kanji=S.kanji and")
 332       <<    jis208
 333       <<    sskip.str()
 334       <<    " strokes>=" << strokes.first << " and strokes<=" << strokes.second
 335       <<    " and jlpt>=" << jlpt.first << " and jlpt<=" << jlpt.second
 336       <<    " and grade>=" << grade.first << " and grade<=" << grade.second
 337       <<    comps.str()
 338       <<  " order by " << order_by;
 339
 340
 341   // perform query
 342   vector<string> q = query( ss.str().c_str(), true, false );
 343   vector<aoi_ui::KanjiView::Cell> data;
 344   vector<string> components;
 345   set<string> flags;
 346   for ( size_t i=0; i<q.size(); i+=4 ){
 347     string kanji = q[i];
 348     int freq = std::stoi(q[i+1]);
 349     data.push_back(
 350       aoi_ui::KanjiView::Cell(
 351         kanji,
 352         (freq>0)? get_color("frequent"):-1
 353       )
 354     );
 355     components.push_back(q[i+2]);
 356     for ( string &s: utils::split_string( q[i+3], SEPARATOR_SQL) )
 357       flags.insert(s);
 358   }
 359
 360   log_d("Groups: " + utils::to_string(flags));
 361
 362   ui_->set_kanjiview( data );
 363   char b[32];
 364   sprintf( b, "%d results", db_->result_rows() );
 365   ui_->set_kanji_results( b );
 366
 367   utils::Histogram<string> histogram;
 368   for ( string &s: components )
 369     histogram.add( utils::str_to_chars(s.c_str()) );
 370   curr_components_ = histogram.sorted();
 371
 372   cb_set_components();
 373 }
 374
 375
 376 void App::alert ( const string &msg, const string &desc )
 377 {
 378   std::stringstream ss;
 379   ss << msg;
 380   if ( !desc.empty() )
 381     ss << "\n\nDetails:\n" << desc;
 382   log_e(ss);
 383   ui_->alert(ss.str());
 384 }
 385
 386
 387 int App::run ( int argc, char **argv )
 388 {
 389   return ui_->run(argc,argv);
 390 }
 391
 392
 393 void App::check_tables ()
 394 {
 395   for ( auto &dbit: aoi_config::db_tables) {
 396     // no need for vacuum here, it will be done by check_indexes()
 397     log("Checking tables in database: " + string(dbit.first));
 398     std::stringstream q;
 399     q << "SELECT name FROM " << dbit.first << "." << "sqlite_master WHERE type='table'";
 400     vector<string> existing = query(q.str().c_str());
 401     // CREATE TABLE name ( column1 TYPE, column2 TYPE, ... )
 402     for ( auto &table: dbit.second ){
 403       if ( utils::is_in(existing, string(table.first)) )
 404         continue;
 405       log("Creating table " + string(table.first));
 406       vector<string> v;
 407       for ( auto &column: table.second ){
 408         std::stringstream sstr;
 409         sstr << column.name << " " << column.type;
 410         v.push_back(sstr.str());
 411       }
 412       std::stringstream ss;
 413       ss << "CREATE TABLE " << dbit.first << "." << table.first
 414         << " (" << to_string(v,",") << ");\n";
 415       query(ss.str().c_str());
 416     }
 417     log("Check done.");
 418   }
 419 }
 420
 421
 422 void App::check_indexes ()
 423 {
 424   bool do_vacuum = false;
 425   log("Checking indexes...");
 426   for ( auto &dbit: aoi_config::db_tables ) {
 427     std::stringstream q;
 428     q << "SELECT name FROM " << dbit.first << "." << "sqlite_master WHERE type='index'";
 429     vector<string> existing = query(q.str().c_str());
 430     // CREATE INDEX idx_table_column ON table ( column ASC )
 431     for ( auto &mi: dbit.second ){  // tables
 432       for ( auto &c: mi.second ){ // columns
 433         std::stringstream idx_name;
 434         idx_name << "idx_" << mi.first << "_" << c.name;
 435         if ( c.index && !utils::is_in( existing, idx_name.str() ) ){
 436           log(string("Creating index ") + idx_name.str());
 437           std::stringstream ss;
 438           ss << "CREATE INDEX " << idx_name.str() << " ON " << mi.first
 439             << "(" << c.name << " " << c.sort << ")";
 440           query(ss.str().c_str());
 441           do_vacuum = true;
 442         }
 443       }
 444     }
 445   }
 446   if ( do_vacuum )
 447     query("VACUUM");
 448   log("Check done.");
 449 }
 450
 451
 452 void App::load_dbscript ( const char *fname )
 453 {
 454   const char  *TEMP_DBSCRIPT  = "dbscript.temp";
 455   const char  *TEMP_DATABASE  = "temp.db";
 456   const int   LINE_BUFFER     = 4096;
 457
 458   // hide manage db_dialog if visible
 459   auto *d = ui_->dlg_manage_db();
 460   if ( d && d->visible() )
 461     d->hide();
 462
 463   log("Loading dbscript: "+string(fname));
 464
 465   log("Decompressing file (if necessary)...");
 466   utils::gzip_decompress_file( fname, TEMP_DBSCRIPT);
 467
 468   log("Opening temporary database.");
 469   remove(TEMP_DATABASE);
 470   SQLite3::SQLite3 db(TEMP_DATABASE);
 471
 472   log("Loading...");
 473   std::ifstream f;
 474   char line[LINE_BUFFER];
 475   f.open(TEMP_DBSCRIPT);
 476   // n lines should be > 2e6 -> percent=n*2e6/100
 477   size_t n = 0;
 478   while ( f.good() ){
 479     if ( n % 5000 == 0 ){
 480       char b[64];
 481       snprintf( b, 64, "Loading line: %d", n);
 482       ui_->progress(n/float(2.5e4), b);
 483     }
 484     f.getline( line, LINE_BUFFER );
 485     db.query(line);
 486     n++;
 487   }
 488   f.close();
 489
 490   log("Closing temporary database...");
 491   db.close();
 492   remove(TEMP_DBSCRIPT);
 493
 494   log("Closing old database.");
 495   db_->close();
 496
 497   string dbfile = get_config("db/file_main");
 498
 499   log("Renaming old database to main.db.bckp.");
 500   rename( dbfile.c_str(), DB_BACKUP_FILE );
 501
 502   log("Renaming new database.");
 503   rename( TEMP_DATABASE, dbfile.c_str() );
 504
 505   log("Switching to the new database");
 506   open_database();
 507
 508   ui_->progress(90, "Checking tables...");
 509   check_tables();
 510   ui_->progress(95, "Checking indexes...");
 511   check_indexes();
 512
 513   // show informations about db
 514   cb_manage_db();
 515 }
 516
 517
 518 void App::cb_dic_input ()
 519 {
 520   parse_dic_input( ui_->get_dic_input() );
 521   ui_->reset_filters();
 522 }
 523
 524
 525 //void App::on_dic_selected ( int id )
 526 //{
 527 //  log_d("App::on_dic_selected()");
 528 //}
 529
 530
 531 void  App::cb_download_db ()
 532 {
 533   log_d("Download DB");
 534   string path = ui_->download_dialog( get_config("sources/url_database") );
 535   if ( !path.empty() && !utils::file_exists( path ) ){
 536     log_w("App::cb_download_db(): Not a file: "+path);
 537     return;
 538   }
 539 }
 540
 541
 542 void App::cb_edit_word ()
 543 {
 544   std::stringstream ss;
 545   int id = ui_->dicview_selected_rowid();
 546   ss << "App::edit_word( " << id << " )";
 547   log_d(ss);
 548   ui_->edit_word( db_get_word(id));
 549 }
 550
 551
 552 void App::cb_popup_kanji ( const string &kanji )
 553 {
 554   std::stringstream ss;
 555   ss << "App::on_kanji_clicked()" << kanji;
 556   Kanji k = db_get_kanji(kanji);
 557   ui_->popup_kanji( k );
 558   // XXX: ? this should be somewhere else (it is not logical here)
 559   ui_->highlight_components( k.components() );
 560   log(ss);
 561 }
 562
 563
 564 void App::set_listview ( const vector<string> &v )
 565 {
 566   if ( !listview_items_.empty() ) listview_items_.clear();
 567   vector<string> d;
 568   vector<int> cell_ids;
 569   size_t i = 0;
 570   while ( i < v.size() ) {
 571     int cell_id = std::stoi(v[i]);  // jmdict id
 572     cell_ids.push_back(cell_id);
 573     cell_ids.push_back(cell_id);
 574     cell_ids.push_back(cell_id);
 575     // pos
 576     set<string> pos;
 577     for ( string &elt: utils::split_string( v[i+1], ",") )
 578       if ( elt.size() > 0 )
 579         pos.insert(utils::strip(elt.c_str()));
 580     d.push_back( v[i+2] );            // reading
 581     d.push_back( v[i+3] );            // kanji
 582     d.push_back( v[i+4] );            // sense
 583     listview_items_.push_back( {cell_id, pos, v[i+2], v[i+3], v[i+4]} );
 584     i += 5;
 585   }
 586   char buff[32];
 587   sprintf( buff, "%d results", db_->result_rows() );
 588   ui_->set_dic_results( buff );
 589   ui_->set_listview(d,cell_ids);
 590 }
 591
 592
 593 void App::parse_dic_input ( const char *str )
 594 {
 595   log_d(string("App::parse_dic_input: \"") +string(str) + string("\""));
 596
 597   string stripped = utils::strip(str);
 598
 599   if ( stripped.empty() )
 600     return;
 601
 602   const char *s = stripped.c_str();
 603
 604   string qq;
 605   if ( s[0] != SENSE_SEARCH_CHAR ){
 606     DictionaryInputParser p;
 607     qq = p.parse(s);
 608
 609     if ( p.warning() ){
 610       int res = ui_->choice(
 611         "Too broad search.\n"\
 612         "Your computer may become unresponsible for a long time.\n"\
 613         "Proceed?",
 614         "Proceed",
 615         "Cancel"
 616         );
 617         if ( res == 1 ) // Cancel
 618           return;
 619     }
 620
 621     // append * at the end of the simple string (just text and nothing else)
 622     if ( !strchr(s,'*') && !strchr(s,'?') && !strchr(s,'[') && !strchr(s,'{')
 623         && !strchr(s,'(') )
 624       qq += "*";
 625   }
 626
 627   std::stringstream q;
 628   if ( s[0] == SENSE_SEARCH_CHAR ){
 629     q << "select did,"
 630       << "group_concat(pos) as pos,"
 631       << q_reading("d_sense.did")
 632       << q_kanji("d_sense.did")
 633       << q_sense()
 634       << " from d_sense where gloss glob '*" << stripped.substr(1) << "*'"
 635       << " group by did";
 636   }
 637   else if ( rmn_->contains_kanji( qq.c_str() ) ) {
 638     q << "select d_kanji.did as did,"
 639       << "(select group_concat(pos) from d_sense where d_sense.did = d_kanji.did) as pos,"
 640       << q_kanji()
 641       << q_reading("d_kanji.did")
 642       << q_sense("d_kanji.did")
 643       << "from d_kanji where "
 644         << "kanji glob '" << rmn_->romaji_to_hiragana(qq.c_str()) << "' "
 645         << " or kanji glob '" << rmn_->romaji_to_katakana(qq.c_str()) << "' "
 646       << " group by did order by d_kanji.freq desc, d_kanji.kanji asc";
 647   }
 648   else {
 649     q << "select d_reading.did as did,"
 650       << "(select group_concat(pos) from d_sense where d_sense.did = d_reading.did) as pos,"
 651       << q_reading()
 652       << q_kanji("d_reading.did")
 653       << q_sense("d_reading.did")
 654       << "from d_reading where "
 655         << "reading glob '" << rmn_->romaji_to_hiragana(qq.c_str())
 656         << "' or reading glob '"<< rmn_->romaji_to_katakana(qq.c_str()) << "' "
 657       << " group by did order by d_reading.freq desc, d_reading.reading asc";
 658   }
 659   set_listview(query(q.str().c_str()));
 660 }
 661
 662
 663 void App::cb_examples ()
 664 {
 665   log_d("cb_examples():" + std::to_string(ui_->dicview_selected_rowid()));
 666   DicWord w = db_get_word( ui_->dicview_selected_rowid() );
 667   if ( w.k_ele().empty() )
 668     return;
 669   std::stringstream q;
 670   // returns: japanese sentece, english sentence, string to be highlighted
 671   q << "select"
 672       << " (select text from sentences where id=sid) as jp,"
 673       << " (select text from sentences where id=mid) as en,"
 674       << " good_example,"
 675       << " form"
 676     << " from indices where headword='"  << w.k_ele()[0].kanji()
 677     << "' order by good_example desc;";
 678   vector<string> res = query(q.str().c_str());
 679   std::stringstream ss;
 680   for ( size_t i=0; i<res.size(); i+=4 ){
 681     bool good_example =  std::stoi(res[i+2]);
 682     ss << "<font face=\"symbol\"";
 683     if ( good_example )
 684       ss << " color=\"blue\"";
 685     ss << ">" << res[i] << "</font><br>";
 686     if ( !res[i+1].empty() )
 687       ss << res[i+1] << "<br>";
 688     ss << "&nbsp;<br>";
 689     // form = res[i+3]
 690   }
 691   ui_->show_html(ss.str());
 692 }
 693
 694
 695 DicWord App::db_get_word ( int id )
 696 {
 697   DicWord w(id);
 698   std::stringstream ss;
 699
 700   // kanji
 701   ss << "select kid,kanji,inf,freq from d_kanji where did=" << id <<";";
 702   vector<string> res = query( ss.str().c_str() );
 703   for ( size_t i=0; i<res.size(); i+=4 ){
 704     int kid             = std::stoi(res[i]);
 705     string kanji        = res[i+1];
 706     vector<string> inf  = utils::split_string(res[i+2],SEPARATOR_SQL);
 707     bool freq           = std::stoi(res[i+3]);
 708     w.k_ele( ElementKanji( kid, kanji, inf, freq ) );
 709   }
 710
 711   // reading
 712   ss.str("");
 713   ss.clear();
 714   ss << "select rid,reading,inf,nokanji,freq from d_reading where did=" << id << ";";
 715   res = query( ss.str().c_str() );
 716   for ( size_t i=0; i<res.size(); i+=5 ){
 717     int rid             = std::stoi(res[i]);
 718     string reading      = res[i+1];
 719     vector<string> inf  = utils::split_string(res[i+2],SEPARATOR_SQL);
 720     bool nokanji        = std::stoi(res[i+3]);
 721     bool freq           = std::stoi(res[i+4]);
 722     w.r_ele( ElementReading( rid, reading, nokanji, {/*restr*/}, inf, freq ) );
 723   }
 724
 725   // sense
 726   ss.str("");
 727   ss.clear();
 728   ss << "select sid,gloss,xref,ant,inf,pos,field,misc,dial from d_sense where did=" << id << ";";
 729   res = query( ss.str().c_str() );
 730   for ( size_t i=0; i<res.size(); i+=9 ){
 731     int sid               = std::stoi(res[i]);
 732     vector<string> gloss  = utils::split_string(res[i+1],SEPARATOR_SQL);
 733     vector<string> xref   = utils::split_string(res[i+2],SEPARATOR_SQL);
 734     vector<string> ant    = utils::split_string(res[i+3],SEPARATOR_SQL);
 735     vector<string> inf    = utils::split_string(res[i+4],SEPARATOR_SQL);
 736     vector<string> pos    = utils::split_string(res[i+5],SEPARATOR_SQL);
 737     vector<string> field  = utils::split_string(res[i+6],SEPARATOR_SQL);
 738     vector<string> misc   = utils::split_string(res[i+7],SEPARATOR_SQL);
 739     vector<string> dial   = utils::split_string(res[i+8],SEPARATOR_SQL);
 740     w.s_ele( ElementSense(  sid, gloss, {/*stagk*/}, {/*stagr*/}, pos, xref,
 741                             ant, field, misc, dial, inf) );
 742   }
 743
 744   return w;
 745 }
 746
 747
 748 Kanji App::db_get_kanji ( const string &kanji )
 749 {
 750   log("App::db_get_kanji()");
 751   std::stringstream q;
 752   q << "select kanji,strokes,ucs, rad_classic, rad_nelson, "
 753           << "jlpt, grade, freq, onyomi, kunyomi, nanori, meaning,flags,components "
 754     << "from k_kanji where kanji='" << kanji << "';";
 755
 756   vector<string> res = query(q.str().c_str());
 757   Kanji kk(res[0]);
 758   kk.strokes(std::stoi(res[1]));
 759   kk.ucs(res[2]);
 760   kk.rad_classic(std::stoi(res[3]));
 761   kk.rad_nelson( (res[4].empty()) ? -1:std::stoi(res[4]));
 762   kk.jlpt(std::stoi(res[5]));
 763   kk.grade(std::stoi(res[6]));
 764   kk.freq(std::stoi(res[7]));
 765   kk.onyomi( utils::split_string(res[8],SEPARATOR_SQL) );
 766   kk.kunyomi( utils::split_string(res[9],SEPARATOR_SQL) );
 767   kk.nanori( utils::split_string(res[10],SEPARATOR_SQL) );
 768   kk.meaning( utils::split_string(res[11],SEPARATOR_SQL) );
 769   kk.flags( utils::split_string(res[12],SEPARATOR_SQL) );
 770   kk.components( res[13] );
 771
 772   string qq = "select skip1,skip2,skip3,misclass from k_skip where kanji='"
 773     + kanji + "';";
 774   vector<string> res2 = query(qq.c_str());
 775   if ( res2.size() % 4 != 0 ){
 776     std::stringstream ss;
 777     ss << "Wrong SKIP count. Kanji: " << kanji
 778       << "Query result size: " << res2.size()
 779       << " (should be 4,8 or 12).  SKIP not loaded.";
 780     log_e(ss);
 781     return kk;
 782   }
 783   for ( size_t i=0; i < res2.size(); i+=4 )
 784     kk.skip( res2[i], res2[i+1], res2[i+2], res2[i+3] );
 785   return kk;
 786 }
 787
 788
 789 void App::cb_filter_listview ()
 790 {
 791   vector<string> pos = ui_->listview_filters();
 792   log_d("App::cb_filter_listview(): pos: " + utils::to_string(pos));
 793   bool filter_expr  = ( utils::is_in( pos, string("expr") ) );
 794   bool filter_noun  = ( utils::is_in( pos, string("noun") ) );
 795   bool filter_verb  = ( utils::is_in( pos, string("verb") ) );
 796   bool filter_adj   = ( utils::is_in( pos, string("adj")  ) );
 797   vector<string> data;
 798   vector<int> ids;
 799   size_t n = 0;
 800   for ( auto &elt: listview_items_ ){
 801     auto start = elt.pos.begin();
 802     auto end = elt.pos.end();
 803     if ( filter_expr && std::find( start, end, "exp") == end )
 804       continue;
 805     if ( filter_noun && std::find( start, end, "n" ) == end )
 806       continue;
 807     if ( filter_adj && std::find_if( start, end,
 808       [](const string &s){ return strncmp(s.c_str(),"adj",3)==0;} ) == end )
 809       continue;
 810     if ( filter_verb && std::find_if( start, end,
 811       [](const string &s){ return strncmp(s.c_str(),"v",1)==0;} ) == end )
 812       continue;
 813     ids.push_back( elt.did );
 814     ids.push_back( elt.did );
 815     ids.push_back( elt.did );
 816     data.push_back ( elt.reading );
 817     data.push_back ( elt.kanji );
 818     data.push_back ( elt.sense );
 819     n++;
 820   }
 821   ui_->set_listview( data, ids );
 822   std::stringstream ss;
 823   ss << n << " results";
 824   if (  listview_items_.size() != n )
 825     ss << " (" <<  listview_items_.size()-n << " hidden)";
 826   log(ss.str());
 827   ui_->set_dic_results( ss.str() );
 828 }
 829
 830
 831 void App::cb_dicview_rightclick ( int did )
 832 {
 833   string q = "select group_concat(kanji,'') from d_kanji where did="
 834     + std::to_string(did);
 835   vector<string> res = query( q.c_str() );
 836   set<string> kanji;
 837   for ( string &c: utils::str_to_chars(res[0].c_str()) )
 838     if ( App::get()->rmn()->is_kanji(c.c_str()) )
 839       kanji.insert(c);
 840   ui_->dicview_menu( did, vector<string>(kanji.begin(),kanji.end()), false, true );
 841 }
 842
 843
 844 void App::apply_config ()
 845 {
 846   ui_->font_base_size( get_config<int>("font/base_size"));
 847   logger_.loglevel( get_config("log/level") );
 848   ui_->init_colors();
 849   Fl::check();
 850 }
 851
 852
 853 void App::cb_manage_db ()
 854 {
 855   auto q = query("select key, val from aoi");
 856
 857   std::map<string,string> mm;
 858   for ( size_t i=0; i<q.size(); i+=2 )
 859     mm[q[i]] = q[i+1];
 860
 861   auto *d = ui_->dlg_manage_db();
 862   d->main_version(      mm["db_version"]);
 863   d->main_created(      mm["db_created"]);
 864   d->main_ver_jmdict(   mm["jmdict_version"]);
 865   d->main_ver_kanjidic( mm["kanjidic_version"]);
 866   d->main_ver_kradfile( mm["kradfile_version"]);
 867   d->main_ver_tatoeba(  mm["tatoeba_version"]);
 868   d->user_checked_against("UNKNOWN");
 869   d->cb_download( scb_download_db, (void*)this );
 870   d->show();
 871 }
 872
 873
 874 void App::load_config ()
 875 {
 876   log("Loading config...");
 877   vector<string> res = query("select key,val from config;");
 878   for ( size_t i=0; i < res.size(); i+=2 ){
 879     set_config( res[i], res[i+1] );
 880   }
 881   apply_config();
 882 }
 883
 884
 885 void App::save_config ( const std::map<string,aoi_config::Config::Value> &newmap)
 886 {
 887   log("Saving config...");
 888
 889   typedef std::pair<string,aoi_config::Config::Value> cfg_pair;
 890
 891   // merge new and old config
 892   for ( const cfg_pair &p: newmap )
 893     set_config( p.first, p.second.val );
 894   apply_config();
 895
 896   // prepare SQL script
 897   std::stringstream ss;
 898   ss << "BEGIN TRANSACTION;\n";
 899   ss << "DROP TABLE IF EXISTS user.config;\n";
 900   ss << "CREATE TABLE user.config (key TEXT, val TEXT);\n";
 901   for ( const cfg_pair &p: get_config_map() )
 902     ss << "INSERT INTO user.config (key,val) VALUES('"
 903       << p.first << "','" << p.second.val << "');\n";
 904   ss << "END TRANSACTION;\n";
 905
 906   query(ss.str().c_str());
 907
 908   // apply new fonts and colors
 909   init_dicview();
 910 }
 911
 912 } // namespace aoi