2
2
This file is part of Blogilo, A KDE Blogging Client
4
Copyright (C) 2008-2009 Mehrdad Momeny <mehrdad.momeny@gmail.com>
5
Copyright (C) 2008-2009 Golnaz Nilieh <g382nilieh@gmail.com>
4
Copyright (C) 2008-2010 Mehrdad Momeny <mehrdad.momeny@gmail.com>
5
Copyright (C) 2008-2010 Golnaz Nilieh <g382nilieh@gmail.com>
7
7
This program is free software; you can redistribute it and/or
8
8
modify it under the terms of the GNU General Public License as
36
36
#include <QSqlError>
37
37
#include <QSqlQuery>
39
#include <QSqlDatabase>
44
KWallet::Wallet* mWallet;
45
QString mLastErrorText;
47
QMap<int, BilboBlog*> mBlogList;
43
mWallet = KWallet::Wallet::openWallet( KWallet::Wallet::LocalWallet(), 0 );
46
if ( !mWallet->setFolder( "blogilo" ) ) {
47
mWallet->createFolder( "blogilo" );
48
mWallet->setFolder( "blogilo" );
55
d->mWallet = KWallet::Wallet::openWallet( KWallet::Wallet::LocalWallet(), 0 );
58
if ( !d->mWallet->setFolder( "blogilo" ) ) {
59
d->mWallet->createFolder( "blogilo" );
60
d->mWallet->setFolder( "blogilo" );
50
62
kDebug() << "Wallet successfully opened.";
53
65
kDebug() << "Could not use Wallet service, will use database to store passwords";
56
68
if ( !QFile::exists( CONF_DB ) ) {
57
69
if ( !this->createDB() ) {
58
70
KMessageBox::detailedError( 0, i18n( "Cannot create database" ),
59
i18n( db.lastError().text().toUtf8().data() ) );
60
kDebug() << "Cannot create database, SQL error: " << db.lastError().text() << endl;
71
i18n( d->db.lastError().text().toUtf8().data() ) );
72
kDebug() << "Cannot create database, SQL error: " << d->db.lastError().text() << endl;
63
75
} else if ( !connectDB() )
83
95
const QMap<int, BilboBlog*> & DBMan::blogList() const
88
100
void DBMan::reloadBlogList()
102
d->mBlogList.clear();
91
103
QList<BilboBlog*> listBlogs = this->listBlogs();
92
104
int count = listBlogs.count();
93
105
for ( int i = 0; i < count; ++i ) {
94
mBlogList [ listBlogs[i]->id() ] = listBlogs[i];
106
d->mBlogList [ listBlogs[i]->id() ] = listBlogs[i];
98
110
bool DBMan::connectDB()
103
db = QSqlDatabase::addDatabase( "QSQLITE" );
104
db.setDatabaseName( CONF_DB );
115
d->db = QSqlDatabase::addDatabase( "QSQLITE" );
116
d->db.setDatabaseName( CONF_DB );
118
if ( !d->db.open() ) {
107
119
KMessageBox::detailedError( 0, i18n( "Cannot connect to database" ),
108
i18n( db.lastError().text().toUtf8().data() ) );
109
kDebug() << "Cannot connect to database, SQL error: " << db.lastError().text();
120
i18n( d->db.lastError().text().toUtf8().data() ) );
121
kDebug() << "Cannot connect to database, SQL error: " << d->db.lastError().text();
138
151
password TEXT, style_url TEXT, api_type TEXT, title TEXT, direction TEXT,\
139
152
local_directory TEXT, icon_url TEXT)" ) ) {
141
mLastErrorText = q.lastError().text();
154
d->mLastErrorText = q.lastError().text();
148
161
is_trackback_allowed NUMERIC, link TEXT, perma_link TEXT, summary TEXT, tags TEXT,\
149
162
status NUMERIC, trackback_urls TEXT, UNIQUE(postid, blog_id));" ) ) {
151
mLastErrorText = q.lastError().text();
164
d->mLastErrorText = q.lastError().text();
154
167
///comments table!
157
170
c_time TEXT, m_time TEXT, link TEXT, password TEXT,\
158
171
status NUMERIC, UNIQUE(commentid, blog_id));" ) ) {
160
mLastErrorText = q.lastError().text();
173
d->mLastErrorText = q.lastError().text();
163
176
///categories table!
165
178
description TEXT, htmlUrl TEXT, rssUrl TEXT, categoryId TEXT, parentId TEXT,\
166
179
blog_id NUMERIC NOT NULL, UNIQUE(name,blog_id));" ) ) {
168
mLastErrorText = q.lastError().text();
181
d->mLastErrorText = q.lastError().text();
172
185
if( !q.exec( "CREATE TABLE file (fileid INTEGER PRIMARY KEY, name TEXT, blog_id NUMERIC, is_uploaded NUMERIC,\
173
186
local_url TEXT, remote_url TEXT, mime_type TEXT);" ) ) {
175
mLastErrorText = q.lastError().text();
188
d->mLastErrorText = q.lastError().text();
178
191
///connection bethween posts and categories
179
192
if ( !q.exec( "CREATE TABLE post_cat (blogId TEXT NOT NULL, postId TEXT NOT NULL,\
180
193
categoryId TEXT NOT NULL, UNIQUE(blogId,postId,categoryId));" ) ) {
182
mLastErrorText = q.lastError().text();
195
d->mLastErrorText = q.lastError().text();
185
198
///connection bethween posts and media files
186
199
if ( !q.exec( "CREATE TABLE post_file (post_id INTEGER, file_id INTEGER);" ) ) {
188
mLastErrorText = q.lastError().text();
201
d->mLastErrorText = q.lastError().text();
191
204
///local posts table
195
208
is_trackback_allowed NUMERIC, link TEXT, perma_link TEXT, summary TEXT, tags TEXT,\
196
209
status NUMERIC);" ) ) {
198
mLastErrorText = q.lastError().text();
211
d->mLastErrorText = q.lastError().text();
201
214
///Connection between local_posts and categories
202
215
if( !q.exec( "CREATE TABLE local_post_cat (local_id INT, categoryId TEXT);" ) ) {
204
mLastErrorText = q.lastError().text();
217
d->mLastErrorText = q.lastError().text();
207
220
///temporary posts table
211
224
is_trackback_allowed NUMERIC, link TEXT, perma_link TEXT, summary TEXT, tags TEXT,\
212
225
status NUMERIC);" ) ) {
214
mLastErrorText = q.lastError().text();
227
d->mLastErrorText = q.lastError().text();
217
230
///Connection between temp_posts and categories
218
231
if( !q.exec( "CREATE TABLE temp_post_cat (local_id INT, categoryId TEXT);" ) ) {
220
mLastErrorText = q.lastError().text();
233
d->mLastErrorText = q.lastError().text();
223
236
///delete related information on DB, On removing a post or a blog
249
262
int DBMan::addBlog( const BilboBlog & blog )
253
266
q.prepare( "INSERT INTO blog (blogid, blog_url, username, style_url, api_type, title,\
254
267
direction, local_directory) VALUES(?, ?, ?, ?, ?, ?, ?, ?)" );
255
if ( mWallet && mWallet->writePassword( blog.url().url() + '_' + blog.username(), blog.password() ) == 0 )
268
if ( d->mWallet && d->mWallet->writePassword( blog.url().url() + '_' + blog.username(), blog.password() ) == 0 )
256
269
kDebug() << "Password stored to kde wallet";
282
295
bool DBMan::editBlog( const BilboBlog & blog )
286
299
q.prepare( "UPDATE blog SET blogid=?, blog_url=?, username=? , style_url=? , api_type=?, \
287
300
title=?, direction=?, local_directory=? WHERE id=?" );
288
if ( mWallet && mWallet->writePassword( blog.url().url() + '_' + blog.username(), blog.password() ) == 0 )
301
if ( d->mWallet && d->mWallet->writePassword( blog.url().url() + '_' + blog.username(), blog.password() ) == 0 )
289
302
kDebug() << "Password stored to kde wallet";
317
330
bool DBMan::removeBlog( int blog_id )
319
BilboBlog *tmp = mBlogList[ blog_id ];
321
if ( mWallet && mWallet->removeEntry( tmp->url().url() + '_' + tmp->username() ) == 0 )
332
BilboBlog *tmp = d->mBlogList[ blog_id ];
334
if ( d->mWallet && d->mWallet->removeEntry( tmp->url().url() + '_' + tmp->username() ) == 0 )
322
335
kDebug() << "Password removed to kde wallet";
390
403
q2.addBindValue(blog_id);
391
404
if ( !q2.exec() ) {
392
405
kDebug() << "Cannot add one of categories to Post, SQL Error: " << q2.lastError().text();
393
mLastErrorText = q.lastError().text();
406
d->mLastErrorText = q.lastError().text();
398
mLastErrorText = q.lastError().text();
411
d->mLastErrorText = q.lastError().text();
399
412
kDebug() << "Cannot Add post to database!\n\tSQL Error: " << q.lastError().text();
633
646
postTable = "temp_post";
634
647
postCatTable = "temp_post_cat";
636
int postId = -1, localId=-1;
637
if(post.status() == KBlog::BlogPost::New) {///Post is new!
649
int localId = post.localId();
650
// if(post.status() == KBlog::BlogPost::New) {///Post is new!
651
// kDebug()<<"Post is new!";
652
if(post.localId() == -1){
639
653
///Add new post to temp_post
640
q.prepare( "INSERT OR REPLACE INTO "+ postTable +" (postid, blog_id, author, title, content,\
641
text_more, c_time, m_time, is_private, is_comment_allowed, is_trackback_allowed, link, perma_link,\
642
summary, slug, tags, status) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" );
654
kDebug()<<"Add new post to temp_post";
655
q.prepare( "INSERT OR REPLACE INTO "+ postTable +" (postid, blog_id,\
656
author, title, content, text_more, c_time, m_time, is_private, is_comment_allowed,\
657
is_trackback_allowed, link, perma_link, summary, slug, tags, status)\
658
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" );
659
// q.addBindValue( post.id() == -1 ? QVariant(QVariant::Int) : post.id() );
643
660
q.addBindValue( post.postId() );
644
661
q.addBindValue( blog_id );
645
662
q.addBindValue( post.author() );
659
676
q.addBindValue( post.status() );
661
678
if ( q.exec() ) {
662
localId = postId = q.lastInsertId().toInt();
679
localId = q.lastInsertId().toInt();
664
mLastErrorText = q.lastError().text();
681
d->mLastErrorText = q.lastError().text();
665
682
kDebug() << "Cannot Add new local post to database!\n\tSQL Error: " << q.lastError().text();
669
686
///Update post, with id!
670
q.prepare( "INSERT OR REPLACE INTO "+ postTable +" (local_id, postid, blog_id, author, title,\
671
content, text_more, c_time, m_time, is_private, is_comment_allowed, is_trackback_allowed, link,\
672
perma_link, summary, slug, tags, status)\
673
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" );
674
q.addBindValue( post.id() );
687
kDebug()<<"Update post, with id!";
688
q.prepare( "UPDATE "+ postTable +" SET postid=?, blog_id=?,\
689
author=?, title=?, content=?, text_more=?, c_time=?, m_time=?, is_private=?, is_comment_allowed=?,\
690
is_trackback_allowed=?, link=?, perma_link=?, summary=?, slug=?, tags=?, status=?\
692
// q.addBindValue( post.id() == -1 ? QVariant(QVariant::Int) : post.id() );
675
693
q.addBindValue( post.postId() );
676
694
q.addBindValue( blog_id );
677
695
q.addBindValue( post.author() );
689
707
q.addBindValue( post.slug() );
690
708
q.addBindValue( post.tags().join(QString(',')) );
691
709
q.addBindValue( post.status() );
710
q.addBindValue( post.localId() );
694
localId = postId = q.lastInsertId().toInt();
696
mLastErrorText = q.lastError().text();
713
d->mLastErrorText = q.lastError().text();
697
714
kDebug() << "Cannot Add new local post to database!\n\tSQL Error: " << q.lastError().text();
701
} else {///Post is already created at "Post" table and has a valid id, postId and blog_id. So, local_id is useless here
718
/*} else {///Post is already created at "Post" table and has a valid id, postId and blog_id. So, local_id is useless here
719
kDebug()<<"Post is already created at \"Post\" table and has a valid id, postId and blog_id. So, local_id is useless here";
702
720
q.prepare( "INSERT OR REPLACE INTO "+ postTable +" (id, postid, blog_id, author, title,\
703
721
content, text_more, c_time, m_time, is_private,\
704
722
is_comment_allowed, is_trackback_allowed, link, perma_link, summary, slug, tags, status)\
726
744
postId = post.id();
727
745
localId = q.lastInsertId().toInt();
729
mLastErrorText = q.lastError().text();
747
d->mLastErrorText = q.lastError().text();
730
748
kDebug() << "Cannot Add or Edit local post to database!\n\tSQL Error: " << q.lastError().text();
735
753
///Delete previouse Categories:
737
755
qd.prepare( "DELETE FROM " + postCatTable + " WHERE local_id=?" );
738
756
qd.addBindValue( localId );
739
757
if ( !qd.exec() ) {
740
mLastErrorText = q.lastError().text();
758
d->mLastErrorText = q.lastError().text();
741
759
kDebug() << "Cannot delete previouse categories.";
754
772
q2.addBindValue(post.categories()[i]);
755
773
q2.addBindValue(blog_id);
756
774
if ( !q2.exec() ) {
757
mLastErrorText = q.lastError().text();
775
d->mLastErrorText = q.lastError().text();
758
776
kDebug() << "Cannot add one of categories to Post, SQL Error: " << q2.lastError().text();
760
778
// kDebug()<<"category "<<post.categories()[i] <<" added.";
766
784
bool DBMan::removeLocalEntry( const BilboPost &post )
770
if(post.status() == KBlog::BlogPost::New) {
771
q.prepare( "DELETE FROM local_post WHERE local_id=?" );
773
q.prepare( "DELETE FROM local_post WHERE id=?" );
775
q.addBindValue( post.id() );
788
q.prepare( "DELETE FROM local_post WHERE local_id=?" );
789
q.addBindValue( post.localId() );
776
790
bool res = q.exec();
778
mLastErrorText = q.lastError().text();
779
kDebug() << mLastErrorText;
792
d->mLastErrorText = q.lastError().text();
793
kDebug() << d->mLastErrorText;
802
if(post.status() == KBlog::BlogPost::New) {
803
q.prepare( "DELETE FROM temp_post WHERE local_id=?" );
805
q.prepare( "DELETE FROM temp_post WHERE id=?" );
807
q.addBindValue( post.id() );
816
q.prepare( "DELETE FROM temp_post WHERE local_id=?" );
817
q.addBindValue( post.localId() );
808
818
bool res = q.exec();
810
mLastErrorText = q.lastError().text();
820
d->mLastErrorText = q.lastError().text();
811
821
kDebug() << q.lastError().text();
813
823
kDebug()<<"Id: "<<post.id()<<"\tStatus: "<<post.status();
821
831
bool res = q.exec( "DELETE FROM temp_post" );
823
mLastErrorText = q.lastError().text();
833
d->mLastErrorText = q.lastError().text();
824
834
kDebug() << q.lastError().text();
829
const BilboBlog &DBMan::blog(int blog_id)
839
BilboBlog *DBMan::blog(int blog_id)
831
return *blogList().value(blog_id);
841
return blogList().value(blog_id);
834
844
QList< BilboBlog *> DBMan::listBlogs()
848
858
tmp->setTitle( q.value( 6 ).toString() );
849
859
tmp->setDirection(( Qt::LayoutDirection )q.value( 7 ).toInt() );
850
860
tmp->setLocalDirectory( q.value( 8 ).toString() );
853
if ( mWallet && mWallet->readPassword( tmp->url().url() + '_' + tmp->username() , buffer )
863
if ( d->mWallet && d->mWallet->readPassword( tmp->url().url() + '_' + tmp->username() , buffer )
854
864
== 0 && !buffer.isEmpty() ) {
855
865
tmp->setPassword( buffer );
856
866
kDebug() << "Password loaded from kde wallet.";
931
941
catList.append( cat );
934
mLastErrorText = q2.lastError().text();
944
d->mLastErrorText = q2.lastError().text();
936
946
tmp->setCategoryList( catList );
937
947
list.append( tmp );
940
mLastErrorText = q.lastError().text();
941
kDebug() << "Cannot get list of posts for blog with id " << blog_id << "error: "<< mLastErrorText;
950
d->mLastErrorText = q.lastError().text();
951
kDebug() << "Cannot get list of posts for blog with id " << blog_id << "error: "<< d->mLastErrorText;
996
1006
catList.append( cat );
999
mLastErrorText = q2.lastError().text();
1009
d->mLastErrorText = q2.lastError().text();
1001
1011
tmp.setCategoryList( catList );
1003
mLastErrorText = i18n( "There is no post with the requested ID" );
1013
d->mLastErrorText = i18n( "There is no post with the requested ID" );
1004
1014
kDebug() << "There isn't any post with id: " << post_id;
1005
1015
tmp.setStatus(KBlog::BlogPost::Error);
1008
mLastErrorText = q.lastError().text();
1018
d->mLastErrorText = q.lastError().text();
1009
1019
kDebug() << "Cannot get post with id " << post_id;
1010
1020
tmp.setStatus(KBlog::BlogPost::Error);
1119
1129
QMap<BilboPost*, int> list;
1121
q.prepare( "SELECT id, local_id, postid, blog_id, author, title, content, text_more, c_time,\
1131
q.prepare( "SELECT local_id, id, postid, blog_id, author, title, content, text_more, c_time,\
1122
1132
m_time, is_private, is_comment_allowed, is_trackback_allowed, link, perma_link, summary, tags, status,\
1123
1133
slug FROM temp_post ORDER BY m_time DESC" );
1124
1134
if ( q.exec() ) {
1125
1135
while ( q.next() ) {
1126
1136
BilboPost *tmp = new BilboPost();
1127
int id = q.value( 0 ).toInt();
1128
int local_id = q.value( 1 ).toInt();
1137
tmp->setLocalId( q.value( 0 ).toInt() );
1138
tmp->setId( q.value( 1 ).toInt() );
1129
1139
tmp->setPostId( q.value( 2 ).toString() );
1130
1140
int blog_id = q.value( 3 ).toInt();
1131
1141
tmp->setAuthor( q.value( 4 ).toString() );
1155
1160
category.categoryId, category.parentId\
1156
1161
FROM category JOIN temp_post_cat ON category.categoryId=temp_post_cat.categoryId\
1157
1162
WHERE temp_post_cat.local_id = ?" );
1158
q2.addBindValue( local_id );
1163
q2.addBindValue( tmp->localId() );
1159
1164
// q2.addBindValue( blog_id );
1160
1165
if ( q2.exec() ) {
1161
1166
while ( q2.next() ) {
1172
1177
tmp->setCategoryList( catList );
1173
1178
list.insert( tmp, blog_id);
1175
mLastErrorText = q2.lastError().text();
1180
d->mLastErrorText = q2.lastError().text();
1176
1181
kDebug()<<"Cannot get categories list of a post. SQL Error: "<< q2.lastError().text();
1180
mLastErrorText = q.lastError().text();
1185
d->mLastErrorText = q.lastError().text();
1181
1186
kDebug() << "Cannot get list of temporary posts, SQL Error: "<< q.lastError().text();
1216
1221
q.addBindValue(local_id);
1217
1222
if ( q.exec() ) {
1218
1223
if ( q.next() ) {
1219
int id = q.value( 0 ).toInt();
1224
tmp.setId( q.value( 0 ).toInt() );
1225
tmp.setLocalId( q.value( 1 ).toInt() );
1220
1226
tmp.setPostId( q.value( 2 ).toString() );
1221
1227
int blog_id = q.value( 3 ).toInt();
1222
1228
tmp.setAuthor( q.value( 4 ).toString() );
1263
1264
tmp.setCategoryList( catList );
1265
mLastErrorText = q2.lastError().text();
1266
d->mLastErrorText = q2.lastError().text();
1266
1267
kDebug()<<"Cannot get categories list of local post. SQL Error: "<< q2.lastError().text();
1269
mLastErrorText = i18n( "There is no local post with the requested ID " );
1270
d->mLastErrorText = i18n( "There is no local post with the requested ID " );
1270
1271
kDebug()<<"there isn't any local post with local_id "<<local_id;
1273
mLastErrorText = q.lastError().text();
1274
d->mLastErrorText = q.lastError().text();
1274
1275
kDebug() << "Cannot get local post. SQL Error: "<< q.lastError().text();