source: contrib/MailArchiver/sources/src/serpro/mailarchiver/util/Sqlite.java @ 6785

Revision 6785, 25.7 KB checked in by rafaelraymundo, 12 years ago (diff)

Ticket #2946 - Liberado codigo do MailArchiver?. Documentação na subpasta DOCS.
Line 
1/**
2 * MailArchiver is an application that provides services for storing and managing e-mail messages through a Web Services SOAP interface.
3 * Copyright (C) 2012  Marcio Andre Scholl Levien and Fernando Alberto Reuter Wendt and Jose Ronaldo Nogueira Fonseca Junior
4 *
5 * This program is free software: you can redistribute it and/or modify
6 * it under the terms of the GNU Affero General Public License as
7 * published by the Free Software Foundation, either version 3 of the
8 * License, or (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13 * GNU Affero General Public License for more details.
14 *
15 * You should have received a copy of the GNU Affero General Public License
16 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
17 */
18
19/******************************************************************************\
20*
21*  This product was developed by
22*
23*        SERVIÇO FEDERAL DE PROCESSAMENTO DE DADOS (SERPRO),
24*
25*  a government company established under Brazilian law (5.615/70),
26*  at Department of Development of Porto Alegre.
27*
28\******************************************************************************/
29
30package serpro.mailarchiver.util;
31
32import java.io.File;
33import java.sql.Connection;
34import java.sql.DriverManager;
35import java.sql.ResultSet;
36import java.sql.SQLException;
37import java.text.SimpleDateFormat;
38import java.util.ArrayList;
39import java.util.Date;
40import java.util.HashMap;
41import java.util.HashSet;
42import java.util.LinkedList;
43import java.util.List;
44import java.util.Map;
45import java.util.Set;
46
47public class Sqlite {
48
49    public static final String PERMISSIONS_DATABASE_NAME = "permissions.db";
50
51    public static final String LOCALSERVER_DATABASE_NAME = "localserver.db";
52
53    private String browserArchiverPath = null;
54
55    private static Sqlite instance = new Sqlite();
56
57    private static final Logger log = Logger.getLocalLogger();
58
59    public static Sqlite getInstance(){
60        try {
61            Class.forName("org.sqlite.JDBC");
62            if(instance!=null){
63                return instance;
64            }
65        } catch (ClassNotFoundException ex) {
66            log.error(ex);
67        }
68        return new Sqlite();
69    }
70
71    public void setBrowserArchiverPath(String browserArchiverPath) {
72        this.browserArchiverPath = browserArchiverPath;
73        log.debug("Configurando origem do arquivamento gears..: " + browserArchiverPath);
74    }
75
76    public Map<String, List<String>> listDatabasePaths(){
77
78//        System.out.println("Sqlite.listDatabasePaths sendo executado... ");
79
80        log.debug("Sqlite.listDatabasePaths sendo executado... ");
81
82        Map<String, List<String>> result = new HashMap<String, List<String>>();
83
84        try{
85            if(browserArchiverPath!=null){
86                String databasePermissions = GearsUtil.loadFilePaths(browserArchiverPath, PERMISSIONS_DATABASE_NAME);
87
88                Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePermissions);
89
90//                System.out.println("Consultando databaseNames... ");
91
92                log.debug("Consultando databaseNames... ");
93
94                ResultSet rs = connection.createStatement().
95                    executeQuery("select d.origin, d.basename from databasenames as d, "
96                        + "access a where d.origin=a.name "
97                        + "and d.isdeleted=0");
98
99//                System.out.println("Databases encontrados no path de arquivamento..: " + browserArchiverPath);
100
101                List<String> databasePaths = null; //armazenar os databasePaths por origin url
102
103                while(rs.next()){
104                    int index = rs.getString(1).indexOf("//");
105
106                    String origin = null;
107
108                    String protocol = null;
109
110                    String databasePath = null;
111
112                    if(index!=-1){
113
114                        if(rs.getString(1).contains("https")){
115                            protocol = File.separatorChar + "https_443";
116                        }else{
117                            protocol = File.separatorChar + "http_80";
118                        }
119
120                        index++;
121
122                        origin = browserArchiverPath + rs.getString(1).substring(index++, rs.getString(1).length());
123
124                        origin += protocol;
125
126                        if(origin!=null && origin.length()>0 && !result.containsKey(origin)){
127
128                            databasePaths = new ArrayList<String>();
129
130                            databasePath = GearsUtil.loadFilePaths(origin, rs.getString(2));
131
132                            if(databasePath!=null){
133
134                                databasePaths.add(databasePath);
135
136//                                System.out.println("Origem arquivamento..: " + origin + " DatabasePath do arquivamento..: " + databasePath);
137
138                                log.debug("Origem arquivamento..: " + origin + " DatabasePath do arquivamento..: " + databasePath);
139                            }
140                            result.put(origin, databasePaths);
141                        }else if(result.containsKey(origin)){
142                            databasePath = GearsUtil.loadFilePaths(origin, rs.getString(2));
143
144                            databasePaths.add(databasePath);//apenas adiciona databasePaths ao map
145
146//                            System.out.println("Origem arquivamento..: " + origin + " DatabasePath do arquivamento..: " + databasePath);
147
148                            log.debug("Origem arquivamento..: " + origin + " DatabasePath do arquivamento..: " + databasePath);
149                        }
150                    }
151                }
152                rs.close();
153                connection.close();
154            }
155        }catch (SQLException ex) {
156            log.error("Falha na obtenção dos databaseNames da origem de arquivamento gears..: " + browserArchiverPath, ex);
157        }
158//        System.out.println("Sqlite.listDatabasePaths sendo finalizado... ");
159
160        log.debug("Sqlite.listDatabasePaths sendo finalizado... ");
161
162        return result;
163   }
164
165    // valida se a estrutura de banco sqlite é utilizada pela suite expresso
166    public boolean isExpressoDatabase(String databasePath){
167
168//        System.out.println("Sqlite.isExpressoDatabase sendo executado... ");
169
170        log.debug("Sqlite.isExpressoDatabase sendo executado... ");
171
172        boolean result = false;
173
174        try{
175            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
176
177//            System.out.println("Sqlite Driver Version..: " + connection.getMetaData().getDriverVersion());
178
179//            System.out.println("Validando estrutura da tabela.: " + databasePath + "... ");
180
181            log.debug("Validando estrutura da tabela.: " + databasePath + "... ");
182
183            //#define SQLITE_CORRUPT     11   /* The database disk image is malformed */
184            ResultSet rs = connection.createStatement().
185                executeQuery(
186                    "select name from sqlite_master where type='table' order by name;");
187
188            int index=0;
189
190            while(rs.next()){
191
192                if(rs.getString(1).equalsIgnoreCase("anexo")
193                        || rs.getString(1).equalsIgnoreCase("folder")
194                        || rs.getString(1).equalsIgnoreCase("mail")){
195                    index++;
196                    if(index==3) break;
197                }
198            }
199            if(index == 3){
200
201//                System.out.println("Estrutura da tabela foi validada com sucesso! ");
202
203                log.debug("Estrutura da tabela foi validada com sucesso! ");
204
205                createArchiveImporter(connection, databasePath); //cria a tabela de controle caso ainda não exista
206
207                result = true;
208
209            }else{
210//                System.out.println("Estrutura da tabela inválida! ");
211
212                log.debug("Estrutura da tabela inválida! ");
213            }
214
215            rs.close();
216
217            connection.close();
218
219        }catch (SQLException ex) {
220
221            log.error("Falha na validação da estrutura sqlite referente ao databasePath..: " + databasePath, ex);
222        }
223
224//        System.out.println("Sqlite.isExpressoDatabase sendo finalizado... ");
225
226        log.debug("Sqlite.isExpressoDatabase sendo finalizado... ");
227
228        return result;
229    }
230
231
232    public boolean isUserDatabase(String databasePath, String user){
233
234        System.out.println("Sqlite.isUserDatabase sendo executado... ");
235
236        log.debug("Sqlite.isUserDatabase sendo executado... ");
237
238        boolean result = false;
239
240        try{
241            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
242
243            ResultSet rs = connection.createStatement().executeQuery("select count(*) from folder where uid_usuario='" + user +  "'");
244
245            System.out.println("Consultando folders para o usuário " + user + " ... ");
246
247            log.debug("Consultando folders para o usuário " + user + " ... ");
248
249            int folders = Integer.parseInt(rs.getString(1));
250
251            if(folders > 0){
252                result = true;
253            }
254
255            rs.close();
256
257            connection.close();
258
259        }catch (SQLException ex) {
260            log.error("Falha ao consultar permissão de usuário em folders para o databasePath..: " + databasePath, ex);
261        }
262
263        System.out.println("Sqlite.isUserDatabase sendo finalizado... ");
264
265        log.debug("Sqlite.isUserDatabase sendo finalizado... ");
266
267        return result;
268    }
269
270    // seleciona todos os folders(names) dos folders encontrados
271    public Set<String> listFolders(String databasePath, String user){
272
273//        System.out.println("Sqlite.listFolders sendo executado... ");
274
275        log.debug("Sqlite.listFolders sendo executado... ");
276
277        Set<String> result = new HashSet<String>();
278
279        try{
280            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
281
282            ResultSet rs = connection.createStatement().executeQuery("select folder from folder where uid_usuario='" + user + "' order by rowid");
283
284//            System.out.println("Consultando folders... ");
285
286            log.debug("Consultando folders... ");
287
288            while(rs.next())
289                result.add(rs.getString(1));
290
291            rs.close();
292
293            connection.close();
294
295        }catch (SQLException ex) {
296            log.error("Falha na listagem de folder para o databasePath..: " + databasePath, ex);
297        }
298
299//        System.out.println("Sqlite.listFolders sendo finalizado... ");
300
301        log.debug("Sqlite.listFolders sendo finalizado... ");
302
303        return result;
304    }
305
306    // seleciona todos os folders(names) já criados pelo mailarchiver
307    // por usuário e por mailArcID
308    public Set<String> listArchivedFolders(String databasePath, String mailArcID, String user){
309
310//        System.out.println("Sqlite.listArchivedFolders sendo executado... ");
311
312        log.debug("Sqlite.listArchivedFolders sendo executado... ");
313
314        Set<String> result = new HashSet<String>();
315
316        try{
317            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
318
319            ResultSet rs = connection.createStatement().executeQuery("select folder from user_archive_importer "
320                    + "where imported='true' and uid_usuario='" + user +  "' order by rowid");
321
322//            System.out.println("Consultando folders arquivados... ");
323
324            log.debug("Consultando folders arquivados... ");
325
326            while(rs.next())
327                result.add(rs.getString(1));
328
329            rs.close();
330
331            connection.close();
332
333        }catch (SQLException ex) {
334            log.error("Falha na listagem de folder arquivado para o databasePath..: " + databasePath, ex);
335        }
336
337//        System.out.println("Sqlite.listArchivedFolders sendo finalizado... ");
338
339        log.debug("Sqlite.listArchivedFolders sendo finalizado... ");
340
341        return result;
342    }
343
344    // carrega o total de mensagens por databasePath
345    public Integer getTotalMessages(String databasePath, String mailArcID, String user){
346
347//        System.out.println("Sqlite.getTotalMessages sendo executado... ");
348
349        log.debug("Sqlite.getTotalMessages sendo executado... ");
350
351        Integer result = 0;
352
353        try{
354            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
355
356            ResultSet rs = connection.createStatement().
357                    executeQuery("select count(*) rowid from mail where rowid not in("
358                      + "select archive.mailid from user_archive_importer as archive, mail as mail "
359                      + "where mail.rowid = archive.mailid and archive.uid_usuario='" + user + "' "
360                      + "and archive.mailarcid='" + mailArcID + "') and uid_usuario='" + user + "'");
361
362            while(rs.next()){
363                result = Integer.parseInt(rs.getString(1));
364            }
365
366//            System.out.println(result + " emails contabilizados para o databasePath " + databasePath);
367
368            log.debug(result + " emails contabilizados para o databasePath " + databasePath);
369
370            rs.close();
371
372            connection.close();
373
374        }catch (SQLException ex) {
375            log.error("Falha na contagem de emails para o databasePath..: " + databasePath, ex);
376        }
377
378//        System.out.println("Sqlite.getTotalMessages sendo finalizado... ");
379
380        log.debug("Sqlite.getTotalMessages sendo finalizado... ");
381
382        return result;
383    }
384
385    // carrega o total de mensagens validas por databasePath para importação pelo mailarchiver
386    public Integer getTotalValidMessages(String databasePath, String mailArcID, String user){
387
388//        System.out.println("Sqlite.getTotalValidMessages sendo executado... ");
389
390        log.debug("Sqlite.getTotalValidMessages sendo executado... ");
391
392        Integer result = 0;
393
394        try{
395            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
396
397            ResultSet rs = connection.createStatement().executeQuery("select count(*) rowid from user_archive_importer "
398                    + "where imported='false' and uid_usuario='" + user + "' and mailarcid='" + mailArcID + "'");
399
400            while(rs.next()){
401                result = Integer.parseInt(rs.getString(1));
402            }
403
404//            System.out.println(result + " emails válidos contabilizados para o databasePath " + databasePath);
405
406            log.debug(result + " emails válidos contabilizados para o databasePath " + databasePath);
407
408            rs.close();
409
410            connection.close();
411
412        }catch (SQLException ex) {
413            log.error("Falha na contagem de emails para o databasePath..: " + databasePath, ex);
414        }
415
416//        System.out.println("Sqlite.getTotalValidMessages sendo finalizado... ");
417
418        log.debug("Sqlite.getTotalValidMessages sendo finalizado... ");
419
420        return result;
421    }
422
423    // leitura dos dados da tabela mail a serem importados por folder
424    public Map<String, Map<String, String>> listMessages(String databasePath, String folder, String mailArcID, String user){
425
426//        System.out.println("Sqlite.listMessages sendo executado... ");
427
428        log.debug("Sqlite.listMessages sendo executado... ");
429
430        Map<String, Map<String, String>> result = new HashMap<String, Map<String, String>>();
431
432        try{
433            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
434
435            ResultSet rs = connection.createStatement().
436                    executeQuery("select m.rowid, m.subject, m.mail from mail as m, folder as f "
437                    + "where m.id_folder = f.rowid and f.folder ='" + folder + "' "
438                    + "and m.rowid not in(select archive.mailid from "
439                    + "user_archive_importer as archive, mail as mail "
440                    + "where mail.rowid = archive.mailid "
441                    + "and archive.uid_usuario='" + user + "' "
442                    + "and archive.mailarcid='" + mailArcID + "') and m.uid_usuario='" + user + "'");
443
444//            System.out.println("Consultando emails... ");
445
446            log.debug("Consultando emails... ");
447
448//            int messages = 0;
449
450            while(rs.next())   {
451
452                Map values = new HashMap<String, String>();
453
454                values.put(rs.getString(2), rs.getString(3));
455
456                result.put(rs.getString(1), values);
457
458//                messages++;
459            }
460
461            rs.close();
462
463            connection.close();
464
465        }catch (SQLException ex) {
466            log.error("Falha na listagem de mensagens do folder " + folder + " referentes ao databasePath..: " + databasePath, ex);
467        }
468
469//        System.out.println("Sqlite.listMessages sendo finalizado... ");
470
471        log.debug("Sqlite.listMessages sendo finalizado... ");
472
473        return result;
474    }
475   
476    // carrega o filepath extraido da urlExportFile passada como parâmetro
477    public String getResponseBodiesFilePath(String urlExportFile) throws SQLException{
478
479//        System.out.println("Sqlite.getResponseBodiesFilePath sendo executado... ");
480
481        log.debug("Sqlite.getResponseBodiesFilePath sendo executado... ");
482
483        String result = null;
484
485        String databasePath = GearsUtil.loadFilePaths(browserArchiverPath, LOCALSERVER_DATABASE_NAME);
486
487        Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
488
489        ResultSet rs = connection.createStatement().
490                executeQuery("select resp.filepath from responsebodies as resp, entries as ent "
491                    + "where ent.payloadid=resp.bodyid "
492                    + "and ent.url like '%" + urlExportFile + "'");
493
494        if(rs.getString(1)!=null && rs.getString(1).length()>0){
495            result = rs.getString(1);
496        }
497
498        rs.close();
499
500        connection.close();
501
502//        System.out.println("Sqlite.getResponseBodiesFilePath sendo finalizado... ");
503
504        log.debug("Sqlite.getResponseBodiesFilePath sendo finalizado... ");
505
506        //Ajusta o caminho correto para a carga do arquivo fonte da mensagem, de acordo com o sistema operacional em uso
507        //implementado para possibilitar a importação de mensagens caso o usuário copie o seu Gears do Windows para o Linux
508       result = result.replace('\\', '/');
509       log.debug("ImportGears: ajustado o URLFilePath de acordo com o sistema operacional ::'" + result + "'");
510       return result;
511    }
512
513    // cria tabela de controle de importação de emails do google gears
514    public void createArchiveImporter(Connection connection,String databasePath){
515
516//        System.out.println("Sqlite.createArchiveImporter sendo executado... ");
517
518        log.debug("Sqlite.createArchiveImporter sendo executado... ");
519
520        try{
521//            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
522//
523//            System.out.println("Criando tabela de controle archive_importer em..: " + databasePath);
524
525            log.debug("Criando tabela de controle user_archive_importer... ");
526
527            connection.createStatement().
528                executeUpdate("create table if not exists user_archive_importer(mailid text,mailarcid text,"
529                    + "folder text,filepath text,imported boolean,import_date text,uid_usuario text,unique(mailid,mailarcid,uid_usuario))");
530
531//            System.out.println("Tabela de controle archive_importer criada. ");
532
533            log.debug("Tabela de controle user_archive_importer criada. ");
534
535//            connection.close();
536
537        }catch (SQLException ex) {
538            log.error("Falha na criação da tabela de controle de arquivamento user_archive_importer", ex);
539        }
540//        System.out.println("Sqlite.createArchiveImporter sendo finalizado... ");
541
542        log.debug("Sqlite.createArchiveImporter sendo finalizado... ");
543    }
544
545    // insere registro de controle de importação de emails do google gears
546    // utiliza conexão já ativa
547    public boolean insertMessageParameters(String databasePath, String folder, String mailId, String mailarcId, String filePath, String user) throws SQLException{
548
549//        System.out.println("Sqlite.insertMessageParameters sendo executado... ");
550
551        log.debug("Sqlite.insertMessageParameters sendo executado... ");
552
553        boolean result = false;
554
555//        System.out.println("Inserindo registro na tabela de controle archive_importer... ");
556
557        log.debug("Inserindo registro na tabela de controle user_archive_importer... ");
558
559        Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
560
561        connection.createStatement().
562                executeUpdate("BEGIN TRANSACTION; "
563                                + "insert into user_archive_importer values('"
564                                + mailId + "','" + mailarcId + "','" + folder + "','"
565                                + filePath + "','" + false + "','"
566                                + new SimpleDateFormat("dd-MMMM-yyyy HH:mm:ss").
567                                format(new Date()) + "','" + user + "'); "
568                            + "COMMIT;");
569
570        result = true;
571
572//        System.out.println("Tabela de controle archive_importer atualizada. ");
573
574        log.debug("Tabela de controle user_archive_importer atualizada. ");
575
576        connection.close();
577
578//        System.out.println("Sqlite.insertMessageParameters sendo finalizado... ");
579
580        log.debug("Sqlite.insertMessageParameters sendo finalizado... ");
581
582        return result;
583    }
584
585     // atualiza tabela de controle de importação de emails do google gears após importação pelo mailarchiver
586    public void updateMessageParameters(String databasePath, String mailId, String mailarcId, String user) throws SQLException{
587
588//        System.out.println("Sqlite.updateMessageParameters sendo executado... ");
589
590        log.debug("Sqlite.updateMessageParameters sendo executado... ");
591
592//        System.out.println("Atualizando a tabela de controle archive_importer ... ");
593
594        log.debug("Atualizando a tabela de controle user_archive_importer ... ");
595
596        Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
597
598        connection.createStatement().
599                executeUpdate("BEGIN TRANSACTION; "
600                                + "update user_archive_importer set imported='true', import_date='"
601                                + new SimpleDateFormat("dd-MMMM-yyyy HH:mm:ss").format(new Date()) + "' "
602                                + "where mailid='" + mailId + "' and uid_usuario='" + user + "' "
603                                + "and mailarcid='" + mailarcId + "'; "
604                            + "COMMIT;");
605
606//        System.out.println("Tabela de controle archive_importer atualizada. ");
607
608        log.debug("Tabela de controle user_archive_importer atualizada. ");
609
610        connection.close();
611
612//        System.out.println("Sqlite.updateMessageParameters sendo finalizado... ");
613
614        log.debug("Sqlite.updateMessageParameters sendo finalizado... ");
615    }
616
617    // deleta os registros da tabela de controle de importação de emails do google gears para o mailarcid passado
618    public void deleteMessageParameters(String databasePath, String mailarcId, String user) throws SQLException{
619
620//        System.out.println("Sqlite.deleteMessageParameters sendo executado... ");
621
622        log.debug("Sqlite.deleteMessageParameters sendo executado... ");
623
624//        System.out.println("Excluindo registros da tabela de controle archive_importer ... ");
625
626        log.debug("Excluindo registros da tabela de controle user_archive_importer ... ");
627
628        Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
629
630        connection.createStatement().
631                executeUpdate("delete from user_archive_importer where mailarcid='" + mailarcId + "' and uid_usuario='" + user + "'");
632
633//        System.out.println("Tabela de controle archive_importer atualizada. ");
634
635        log.debug("Tabela de controle user_archive_importer atualizada. ");
636
637        connection.close();
638
639//        System.out.println("Sqlite.deleteMessageParameters sendo finalizado... ");
640
641        log.debug("Sqlite.deleteMessageParameters sendo finalizado... ");
642    }
643
644    // lista os registros da tabela de controle de importação de emails do google gears para o mailarcid passado
645    public LinkedList<String[]> listMessageParameters(String databasePath, String mailArcId, String user){
646
647//        System.out.println("Sqlite.listMessageParameters sendo executado... ");
648
649        log.debug("Sqlite.listMessageParameters sendo executado... ");
650
651        LinkedList<String[]> result = new LinkedList<String[]>();//manter a ordem de folder
652
653        try{
654//            System.out.println("Listando registros da tabela de controle archive_importer ... ");
655
656            log.debug("Listando registros da tabela de controle user_archive_importer ... ");
657
658            Connection connection = DriverManager.getConnection("jdbc:sqlite:" + databasePath);
659
660            ResultSet rs = connection.createStatement().executeQuery("select archive.folder, archive.mailid, archive.filepath, m.header, m.unseen from user_archive_importer as archive, mail as m "
661                    + "where archive.mailid=m.rowid and archive.mailarcid='" + mailArcId + "' and archive.imported='false' and archive.uid_usuario='" + user + "' order by archive.folder");
662
663            while(rs.next()){
664                result.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)});
665            }
666
667            connection.close();
668
669        }catch (SQLException ex) {
670            log.error("Falha na listagem de registros da tabela user_archive_importer para o mailArcId..: " + mailArcId, ex);
671        }
672//        System.out.println("Sqlite.listMessageParameters sendo finalizado... ");
673
674        log.debug("Sqlite.listMessageParameters sendo finalizado... ");
675
676        return result;
677    }
678}
Note: See TracBrowser for help on using the repository browser.