« back to all changes in this revision
Viewing changes to src/data.cc
- browse files at revision 9
- compare with another revision
- compare with revision 9
- download diff
- download tarball
- view history from revision 9
- Committer: Bazaar Package Importer
- Author(s): Scott Evans
- Date: 2009-06-27 00:46:22 UTC
- mfrom: (1.1.7 upstream)
- Revision ID: james.westby@ubuntu.com-20090627004622-q0zvsie6ioa60vh3
Tags: 0.9.4-0ubuntu1
* New upstream release (LP: #379706)
- Fix to stop EPG update crashing the application
after faulty save (LP: #72872)
- Fixed spin buttons from GtkBuilder conversion (LP: #382197)
- Fixed icon on application popup menu (LP: #379685)
- Fixed compiling of me-tv-0.8.12 fails on Fedora 11 (LP: #377020)
- Fixed Failed to lock to channel at boot (LP: #377050)
- Increased timeout to 5 seconds again (LP: #371165)
- Fixed me-tv unusually slow, often freezes (LP: #351510)
- Fixed channel persistence (LP: #361514)
- Fix for forward slashes in description (LP: #359710)
- Fixed Must create .me-tv directory manually (LP: #353796)
- Fixed audio stream can't be changed (LP: #350402)
* debian/control:
- Removed dependency libxine1-ffmpeg, libxine1-x
- Removed libglademm-2.4-dev Build-Depends.
- Fix to stop EPG update crashing the application
after faulty save (LP: #72872)
- Fixed spin buttons from GtkBuilder conversion (LP: #382197)
- Fixed icon on application popup menu (LP: #379685)
- Fixed compiling of me-tv-0.8.12 fails on Fedora 11 (LP: #377020)
- Fixed Failed to lock to channel at boot (LP: #377050)
- Increased timeout to 5 seconds again (LP: #371165)
- Fixed me-tv unusually slow, often freezes (LP: #351510)
- Fixed channel persistence (LP: #361514)
- Fix for forward slashes in description (LP: #359710)
- Fixed Must create .me-tv directory manually (LP: #353796)
- Fixed audio stream can't be changed (LP: #350402)
* debian/control:
- Removed dependency libxine1-ffmpeg, libxine1-x
- Removed libglademm-2.4-dev Build-Depends.
- files added:
- src/channel_manager.cc
- files removed:
- src/me-tv.glade
- files modified:
- ChangeLog
added
removed
src/data.cc
25
25
26
26
Glib::RecMutex statement_mutex;
27
27
28
using namespace Data;
29
28
30
class SQLiteException : public Exception
29
31
{
30
32
public:
31
SQLiteException(sqlite3* database, const Glib::ustring& exception_message) :
32
Exception(Glib::ustring::compose("%1: %2", exception_message, Glib::ustring(sqlite3_errmsg(database)))) {}
33
};
34
35
typedef enum
36
{
37
PARAMETER_TYPE_NULL,
38
PARAMETER_TYPE_STRING,
39
PARAMETER_TYPE_INTEGER
40
} ParameterType;
41
42
class Parameter
43
{
44
public:
45
Parameter(const Glib::ustring& parameter_name) :
46
name(parameter_name), parameter_type(PARAMETER_TYPE_NULL) {}
47
Parameter(const Glib::ustring& parameter_name, const Glib::ustring& value) :
48
name(parameter_name), string_value(value), parameter_type(PARAMETER_TYPE_STRING) {}
49
Parameter(const Glib::ustring& parameter_name, gint value) :
50
name(parameter_name), int_value(value), parameter_type(PARAMETER_TYPE_INTEGER) {}
51
52
Glib::ustring name;
53
guint int_value;
54
Glib::ustring string_value;
55
ParameterType parameter_type;
56
};
57
58
class ParameterList : public std::list<Parameter>
59
{
60
public:
61
Glib::ustring get_names();
62
Glib::ustring get_values();
63
void add(const Glib::ustring& name, const Glib::ustring& value);
64
void add(const Glib::ustring& name, gint value);
65
void add(const Glib::ustring& name);
66
};
67
68
Glib::ustring ParameterList::get_names()
69
{
70
gboolean first = true;
71
Glib::ustring result;
72
for (std::list<Parameter>::iterator i = begin(); i != end(); i++)
73
{
74
if (!first)
75
{
76
result += ", ";
77
}
78
else
79
{
80
first = false;
81
}
82
83
result += (*i).name;
84
}
85
return result;
86
}
87
88
Glib::ustring ParameterList::get_values()
89
{
90
gboolean first = true;
91
Glib::ustring result;
92
93
for (std::list<Parameter>::iterator i = begin(); i != end(); i++)
94
{
95
if (!first)
96
{
97
result += ", ";
98
}
99
else
100
{
101
first = false;
102
}
103
104
Parameter& parameter = *i;
105
if (parameter.parameter_type == PARAMETER_TYPE_NULL)
106
{
107
result += "NULL";
108
}
109
else if (parameter.parameter_type == PARAMETER_TYPE_INTEGER)
110
{
111
result += Glib::ustring::compose("%1", parameter.int_value);
112
}
113
else if (parameter.parameter_type == PARAMETER_TYPE_STRING)
114
{
115
result += Glib::ustring::compose("\"%1\"", parameter.string_value);
116
}
117
}
118
return result;
119
}
120
121
void ParameterList::add(const Glib::ustring& name, const Glib::ustring& value)
122
{
123
push_back(Parameter(name, value));
124
}
125
126
void ParameterList::add(const Glib::ustring& name, gint value)
127
{
128
push_back(Parameter(name, value));
129
}
130
131
void ParameterList::add(const Glib::ustring& name)
132
{
133
push_back(Parameter(name));
134
}
135
136
Statement::Statement(sqlite3* statement_database, const Glib::ustring& statement_command) :
137
lock(statement_mutex), database(statement_database), command(statement_command)
33
SQLiteException(sqlite3* connection, const Glib::ustring& exception_message) :
34
Exception(Glib::ustring::compose("%1: %2", exception_message, Glib::ustring(sqlite3_errmsg(connection)))) {}
35
SQLiteException(Connection& connection, const Glib::ustring& exception_message) :
36
Exception(Glib::ustring::compose("%1: %2", exception_message, connection.get_error_message())) {}
37
};
38
39
Statement::Statement(Connection& connection, const Glib::ustring& command) :
40
lock(statement_mutex), connection(connection), command(command)
138
41
{
139
42
statement = NULL;
140
43
const char* remaining = NULL;
141
// g_debug("Command: %s", command.c_str());
142
44
143
if (sqlite3_prepare_v2(database, command.c_str(), -1, &statement, &remaining) != 0)
45
if (sqlite3_prepare_v2(connection.get_connection(), command.c_str(), -1, &statement, &remaining) != 0)
144
46
{
145
47
if (remaining == NULL || *remaining == 0)
146
48
{
147
throw SQLiteException(database, Glib::ustring::compose(_("Failed to prepare statement: %1"), command));
49
throw SQLiteException(connection, Glib::ustring::compose(
50
_("Failed to prepare statement: %1"), command));
148
51
}
149
52
else
150
53
{
151
throw SQLiteException(database, Glib::ustring::compose(_("Failed to prepare statement: %1"), Glib::ustring(remaining)));
54
throw SQLiteException(connection, Glib::ustring::compose(
55
_("Failed to prepare statement: %1"), Glib::ustring(remaining)));
152
56
}
153
57
}
154
58
155
59
if (remaining != NULL && *remaining != 0)
156
60
{
157
throw SQLiteException(database, Glib::ustring::compose(_("Prepare statement had remaining data: %1"), Glib::ustring(remaining)));
61
throw SQLiteException(connection, Glib::ustring::compose(
62
_("Prepared statement had remaining data: %1"), Glib::ustring(remaining)));
158
63
}
159
64
160
65
if (statement == NULL)
161
66
{
162
throw SQLiteException(database, _("Failed to create statement"));
67
throw SQLiteException(connection, _("Failed to create statement"));
163
68
}
164
69
}
165
70
167
72
{
168
73
if (sqlite3_finalize(statement) != 0)
169
74
{
170
throw SQLiteException(database, _("Failed to finalise statement"));
75
throw SQLiteException(connection, _("Failed to finalise statement"));
171
76
}
172
77
}
78
79
void Statement::reset()
80
{
81
sqlite3_reset(statement);
82
sqlite3_clear_bindings(statement);
83
}
173
84
174
gint Statement::step()
85
guint Statement::step()
175
86
{
176
87
int result = sqlite3_step(statement);
177
88
181
92
usleep(10000);
182
93
result = sqlite3_step(statement);
183
94
}
95
96
switch (result)
97
{
98
case SQLITE_DONE:
99
case SQLITE_OK:
100
case SQLITE_ROW:
101
break;
102
default:
103
throw SQLiteException(
104
connection,
105
Glib::ustring::compose(_("Failed to execute statement: %1"), command));
106
}
184
107
185
108
return result;
186
109
}
195
118
return (gchar*)sqlite3_column_text(statement, column);
196
119
}
197
120
198
Data::Data(gboolean initialise)
199
{
200
Glib::ustring database_path = Glib::build_filename(get_application().get_application_dir(), "/me-tv.db");
201
202
g_debug("Opening database file '%s'", database_path.c_str());
203
if (sqlite3_open(database_path.c_str(), &database) != 0)
204
{
205
throw SQLiteException(database, _("Failed to connect to Me TV database"));
206
}
207
208
if (initialise)
209
{
210
execute_non_query(
211
"CREATE TABLE IF NOT EXISTS PROFILE ("\
212
"PROFILE_ID INTEGER PRIMARY KEY AUTOINCREMENT, "\
213
"NAME CHAR(50) NOT NULL, "\
214
"UNIQUE (NAME));");
215
216
execute_non_query(
217
"CREATE TABLE IF NOT EXISTS CHANNEL ("\
218
"CHANNEL_ID INTEGER PRIMARY KEY AUTOINCREMENT, "\
219
"PROFILE_ID INTEGER NOT NULL, "\
220
"NAME CHAR(50) NOT NULL, "\
221
"FLAGS INTEGER NOT NULL, "\
222
"SORT_ORDER INTEGER NOT NULL, "\
223
"MRL CHAR(1024), "\
224
"SERVICE_ID INTEGER, "\
225
"FREQUENCY INTEGER, "\
226
"INVERSION INTEGER, "\
227
228
"BANDWIDTH INTEGER, "\
229
"CODE_RATE_HP INTEGER, "\
230
"CODE_RATE_LP INTEGER, "\
231
"CONSTELLATION INTEGER, "\
232
"TRANSMISSION_MODE INTEGER, "\
233
"GUARD_INTERVAL INTEGER, "\
234
"HIERARCHY_INFORMATION INTEGER, "\
235
236
"SYMBOL_RATE INTEGER, "\
237
"FEC_INNER INTEGER, "\
238
"MODULATION INTEGER, "\
239
240
"UNIQUE (NAME));");
241
242
execute_non_query(
243
"CREATE TABLE IF NOT EXISTS EPG_EVENT ("\
244
"EPG_EVENT_ID INTEGER PRIMARY KEY AUTOINCREMENT, "\
245
"CHANNEL_ID INTEGER NOT NULL, "\
246
"EVENT_ID INTEGER NOT NULL, "\
247
"START_TIME INTEGER NOT NULL, "\
248
"DURATION INTEGER NOT NULL, "\
249
"UNIQUE (CHANNEL_ID, EVENT_ID));");
250
251
execute_non_query(
252
"CREATE TABLE IF NOT EXISTS EPG_EVENT_TEXT ("\
253
"EPG_EVENT_TEXT_ID INTEGER PRIMARY KEY AUTOINCREMENT, "\
254
"EPG_EVENT_ID INTEGER NOT NULL, "\
255
"LANGUAGE CHAR(3) NOT NULL, "\
256
"TITLE CHAR(100) NOT NULL, "\
257
"DESCRIPTION CHAR(200) NOT NULL, "\
258
"UNIQUE (EPG_EVENT_ID, LANGUAGE));");
259
260
execute_non_query(
261
"CREATE TABLE IF NOT EXISTS SCHEDULED_RECORDING ("\
262
"SCHEDULED_RECORDING_ID INTEGER PRIMARY KEY AUTOINCREMENT, "\
263
"DESCRIPTION CHAR(100) NOT NULL, "\
264
"TYPE INTEGER NOT NULL, " \
265
"CHANNEL_ID INTEGER NOT NULL, " \
266
"START_TIME INTEGER NOT NULL, " \
267
"DURATION INTEGER NOT NULL);");
268
269
delete_old_scheduled_recordings();
270
}
271
}
272
273
Data::~Data()
274
{
275
if (database != NULL)
276
{
277
sqlite3_close(database);
278
database = NULL;
279
}
280
}
281
282
guint Data::execute_non_query(const Glib::ustring& command)
283
{
284
Statement statement(database, command);
285
return statement.step();
286
}
287
288
void fix_quotes(Glib::ustring& text)
289
{
290
replace_text(text, "'", "''");
291
}
292
293
void Data::replace_epg_event(EpgEvent& epg_event)
294
{
295
if (epg_event.channel_id == 0)
296
{
297
throw Exception(_("ASSERT: epg_event.channel_id == 0"));
298
}
299
300
Glib::ustring insert_command = Glib::ustring::compose
301
(
302
"REPLACE INTO EPG_EVENT "\
303
"(CHANNEL_ID, EVENT_ID, START_TIME, DURATION) "\
304
"VALUES (%1, %2, %3, %4);",
305
epg_event.channel_id, epg_event.event_id, epg_event.start_time, epg_event.duration
306
);
307
308
execute_non_query(insert_command);
309
310
if (epg_event.epg_event_id == 0)
311
{
312
epg_event.epg_event_id = sqlite3_last_insert_rowid(database);
313
}
314
315
if (epg_event.epg_event_id == 0)
316
{
317
Glib::ustring select_command = Glib::ustring::compose
318
(
319
"SELECT EPG_EVENT_ID FROM EPG_EVENT WHERE CHANNEL_ID = %1 AND EVENT_ID = %2",
320
epg_event.channel_id, epg_event.event_id
321
);
322
323
Statement statement(database, select_command);
324
if (statement.step() == SQLITE_ROW)
325
{
326
epg_event.epg_event_id = statement.get_int(0);
327
}
328
329
if (epg_event.epg_event_id == 0)
330
{
331
throw Exception(_("Failed to get epg_event_id"));
332
}
333
}
334
335
for (EpgEventTextList::iterator i = epg_event.texts.begin(); i != epg_event.texts.end(); i++)
336
{
337
EpgEventText& epg_event_text = *i;
338
epg_event_text.epg_event_id = epg_event.epg_event_id;
339
replace_epg_event_text(epg_event_text);
340
}
341
}
342
343
void Data::replace_epg_event_text(EpgEventText& epg_event_text)
344
{
345
Glib::ustring command;
346
347
if (epg_event_text.epg_event_id == 0)
348
{
349
throw Exception(_("Event ID was 0"));
350
}
351
352
Glib::ustring fixed_title = epg_event_text.title;
353
Glib::ustring fixed_description = epg_event_text.description;
354
355
fix_quotes(fixed_title);
356
fix_quotes(fixed_description);
357
358
guint existing_epg_event_text_id = 0;
359
360
// Make sure that statement exists before we start the next command
361
{
362
Statement statement(database, Glib::ustring::compose(
363
"SELECT EPG_EVENT_TEXT_ID FROM EPG_EVENT_TEXT WHERE EPG_EVENT_ID=%1;",
364
epg_event_text.epg_event_id));
365
if (statement.step() == SQLITE_ROW)
366
{
367
existing_epg_event_text_id = statement.get_int(0);
368
}
369
}
370
371
if (existing_epg_event_text_id != 0)
372
{
373
if (epg_event_text.is_extended)
374
{
375
command = Glib::ustring::compose
376
(
377
"UPDATE EPG_EVENT_TEXT SET DESCRIPTION = DESCRIPTION || '%1' WHERE EPG_EVENT_TEXT_ID=%2;",
378
fixed_description, existing_epg_event_text_id
379
);
380
}
381
else
382
{
383
command = Glib::ustring::compose
384
(
385
"UPDATE EPG_EVENT_TEXT SET TITLE = '%1' WHERE EPG_EVENT_TEXT_ID=%2;",
386
fixed_title, existing_epg_event_text_id
387
);
388
}
389
}
390
else
391
{
392
if (epg_event_text.is_extended)
393
{
394
command = Glib::ustring::compose
395
(
396
"INSERT INTO EPG_EVENT_TEXT "\
397
"(EPG_EVENT_ID, LANGUAGE, TITLE, DESCRIPTION) "\
398
"VALUES (%1, '%2', '%3', '%4');",
399
epg_event_text.epg_event_id, epg_event_text.language, fixed_title, fixed_description
400
);
401
}
402
else
403
{
404
command = Glib::ustring::compose
405
(
406
"INSERT INTO EPG_EVENT_TEXT "\
407
"(EPG_EVENT_ID, LANGUAGE, TITLE, DESCRIPTION) "\
408
"VALUES (%1, '%2', '%3', '%4');",
409
epg_event_text.epg_event_id, epg_event_text.language, fixed_title, fixed_description
410
);
411
}
412
}
413
414
execute_non_query(command);
415
416
if (epg_event_text.epg_event_text_id == 0)
417
{
418
epg_event_text.epg_event_text_id = sqlite3_last_insert_rowid(database);
419
}
420
}
421
422
void Data::load_epg_event(Statement& statement, EpgEvent& epg_event)
423
{
424
epg_event.epg_event_id = statement.get_int(0);
425
epg_event.channel_id = statement.get_int(1);
426
epg_event.event_id = statement.get_int(2);
427
epg_event.start_time = statement.get_int(3);
428
epg_event.duration = statement.get_int(4);
429
epg_event.save = false;
430
431
Glib::ustring text_select_command = Glib::ustring::compose
432
(
433
"SELECT * FROM EPG_EVENT_TEXT WHERE EPG_EVENT_ID=%1;",
434
epg_event.epg_event_id
435
);
436
437
Statement text_statement(database, text_select_command);
438
while (text_statement.step() == SQLITE_ROW)
439
{
440
EpgEventText epg_event_text;
441
epg_event_text.epg_event_text_id = text_statement.get_int(0);
442
epg_event_text.epg_event_id = text_statement.get_int(1);
443
epg_event_text.language = text_statement.get_text(2);
444
epg_event_text.title = text_statement.get_text(3);
445
epg_event_text.description = text_statement.get_text(4);
446
epg_event.texts.push_back(epg_event_text);
447
}
448
}
449
450
EpgEventList Data::get_epg_events(const Channel& channel)
451
{
452
EpgEventList result;
453
454
Glib::ustring select_command = Glib::ustring::compose
455
(
456
"SELECT * FROM EPG_EVENT WHERE CHANNEL_ID=%1 ORDER BY START_TIME;",
457
channel.channel_id
458
);
459
460
Statement statement(database, select_command);
461
while (statement.step() == SQLITE_ROW)
462
{
463
EpgEvent epg_event;
464
load_epg_event(statement, epg_event);
465
result.push_back(epg_event);
466
467
g_debug("Event %d (%s) loaded", epg_event.event_id, epg_event.get_title().c_str());
468
}
469
470
return result;
471
}
472
473
void Data::replace_channel(Channel& channel)
474
{
475
ParameterList parameters;
476
477
Glib::ustring fixed_name = channel.name;
478
fix_quotes(fixed_name);
479
480
// General
481
if (channel.channel_id == 0)
482
{
483
parameters.add("CHANNEL_ID");
484
}
485
else
486
{
487
parameters.add("CHANNEL_ID", channel.channel_id);
488
}
489
490
g_debug("Saving channel %d (%s)", channel.channel_id, channel.name.c_str());
491
492
parameters.add("PROFILE_ID", channel.profile_id);
493
parameters.add("NAME", fixed_name);
494
parameters.add("FLAGS", channel.flags);
495
parameters.add("SORT_ORDER", channel.sort_order);
496
parameters.add("MRL", channel.mrl);
497
parameters.add("SERVICE_ID", channel.service_id);
498
parameters.add("FREQUENCY", (guint)channel.frontend_parameters.frequency);
499
parameters.add("INVERSION", (guint)channel.frontend_parameters.inversion);
500
501
if (channel.flags & CHANNEL_FLAG_DVB_T)
502
{
503
parameters.add("BANDWIDTH", (guint)channel.frontend_parameters.u.ofdm.bandwidth);
504
parameters.add("CODE_RATE_HP", (guint)channel.frontend_parameters.u.ofdm.code_rate_HP);
505
parameters.add("CODE_RATE_LP", (guint)channel.frontend_parameters.u.ofdm.code_rate_LP);
506
parameters.add("CONSTELLATION", (guint)channel.frontend_parameters.u.ofdm.constellation);
507
parameters.add("TRANSMISSION_MODE", (guint)channel.frontend_parameters.u.ofdm.transmission_mode);
508
parameters.add("GUARD_INTERVAL", (guint)channel.frontend_parameters.u.ofdm.guard_interval);
509
parameters.add("HIERARCHY_INFORMATION", (guint)channel.frontend_parameters.u.ofdm.hierarchy_information);
510
}
511
else if (channel.flags & CHANNEL_FLAG_DVB_C)
512
{
513
parameters.add("SYMBOL_RATE", (guint)channel.frontend_parameters.u.qam.symbol_rate);
514
parameters.add("FEC_INNER", (guint)channel.frontend_parameters.u.qam.fec_inner);
515
parameters.add("MODULATION", (guint)channel.frontend_parameters.u.qam.modulation);
516
}
517
else if (channel.flags & CHANNEL_FLAG_ATSC)
518
{
519
parameters.add("MODULATION", (guint)channel.frontend_parameters.u.vsb.modulation);
520
}
521
else
522
{
523
throw Exception(_("Invalid channel flag"));
524
}
525
526
Glib::ustring insert_command = Glib::ustring::compose
527
(
528
"REPLACE INTO CHANNEL (%1) VALUES (%2);",
529
parameters.get_names(), parameters.get_values()
530
);
531
532
execute_non_query(insert_command);
533
534
if (channel.channel_id == 0)
535
{
536
channel.channel_id = sqlite3_last_insert_rowid(database);
537
}
538
539
EpgEventList epg_event_list = channel.epg_events.get_list(true);
540
for (EpgEventList::iterator i = epg_event_list.begin(); i != epg_event_list.end(); i++)
541
{
542
EpgEvent& epg_event = *i;
543
if (epg_event.save)
544
{
545
replace_epg_event(epg_event);
546
g_debug("Saved EPG event %d (%s)", epg_event.event_id, epg_event.get_title().c_str());
547
}
548
}
549
}
550
551
void Data::replace_profile(Profile& profile)
552
{
553
ParameterList parameters;
554
Glib::ustring fixed_name = profile.name;
555
556
fix_quotes(fixed_name);
557
558
if (profile.profile_id == 0)
559
{
560
parameters.add("PROFILE_ID");
561
}
562
else
563
{
564
parameters.add("PROFILE_ID", profile.profile_id);
565
}
566
parameters.add("NAME", profile.name);
567
568
Glib::ustring replace_command = Glib::ustring::compose
569
(
570
"REPLACE INTO PROFILE (%1) VALUES (%2);",
571
parameters.get_names(), parameters.get_values()
572
);
573
574
execute_non_query(replace_command);
575
576
if (profile.profile_id == 0)
577
{
578
profile.profile_id = sqlite3_last_insert_rowid(database);
579
if (profile.profile_id == 0)
580
{
581
throw Exception("ASSERT: profile.profile_id == 0");
582
}
583
}
584
585
// First, remove channels
586
Statement statement(database,
587
Glib::ustring::compose("SELECT CHANNEL_ID FROM CHANNEL WHERE PROFILE_ID=%1;", profile.profile_id));
588
while (statement.step() == SQLITE_ROW)
589
{
590
gint channel_id = statement.get_int(0);
591
if (profile.find_channel(channel_id) == NULL)
592
{
593
delete_channel(channel_id);
594
}
595
}
596
597
ChannelList& channels = profile.get_channels();
598
for (ChannelList::iterator channel_iterator = channels.begin(); channel_iterator != channels.end(); channel_iterator++)
599
{
600
Channel& channel = *channel_iterator;
601
channel.profile_id = profile.profile_id;
602
replace_channel(channel);
603
}
604
}
605
606
ProfileList Data::get_all_profiles()
607
{
608
ProfileList profiles;
609
610
Statement statement(database, "SELECT * FROM PROFILE ORDER BY PROFILE_ID");
611
while (statement.step() == SQLITE_ROW)
612
{
613
Profile profile;
614
profile.profile_id = statement.get_int(0);
615
profile.name = statement.get_text(1);
616
617
Statement channel_statement(database,
618
Glib::ustring::compose("SELECT * FROM CHANNEL WHERE PROFILE_ID = %1 ORDER BY SORT_ORDER", profile.profile_id));
619
while (channel_statement.step() == SQLITE_ROW)
620
{
621
Channel channel;
622
623
channel.channel_id = channel_statement.get_int(0);
624
channel.profile_id = channel_statement.get_int(1);
625
channel.name = channel_statement.get_text(2);
626
channel.flags = channel_statement.get_int(3);
627
channel.sort_order = channel_statement.get_int(4);
628
channel.mrl = channel_statement.get_text(5);
629
channel.service_id = channel_statement.get_int(6);
630
631
channel.frontend_parameters.frequency = channel_statement.get_int(7);
632
channel.frontend_parameters.inversion = (fe_spectral_inversion_t)channel_statement.get_int(8);
633
634
if (channel.flags & CHANNEL_FLAG_DVB_T)
635
{
636
channel.frontend_parameters.u.ofdm.bandwidth = (fe_bandwidth_t)channel_statement.get_int(9);
637
channel.frontend_parameters.u.ofdm.code_rate_HP = (fe_code_rate_t)channel_statement.get_int(10);
638
channel.frontend_parameters.u.ofdm.code_rate_LP = (fe_code_rate_t)channel_statement.get_int(11);
639
channel.frontend_parameters.u.ofdm.constellation = (fe_modulation_t)channel_statement.get_int(12);
640
channel.frontend_parameters.u.ofdm.transmission_mode = (fe_transmit_mode_t)channel_statement.get_int(13);
641
channel.frontend_parameters.u.ofdm.guard_interval = (fe_guard_interval_t)channel_statement.get_int(14);
642
channel.frontend_parameters.u.ofdm.hierarchy_information = (fe_hierarchy_t)channel_statement.get_int(15);
643
}
644
else if (channel.flags & CHANNEL_FLAG_DVB_C)
645
{
646
channel.frontend_parameters.u.qam.symbol_rate = channel_statement.get_int(16);
647
channel.frontend_parameters.u.qam.fec_inner = (fe_code_rate_t)channel_statement.get_int(17);
648
channel.frontend_parameters.u.qam.modulation = (fe_modulation_t)channel_statement.get_int(18);
649
}
650
else if (channel.flags & CHANNEL_FLAG_ATSC)
651
{
652
channel.frontend_parameters.u.vsb.modulation = (fe_modulation_t)channel_statement.get_int(18);
653
}
654
channel.epg_events.insert(get_epg_events(channel));
655
profile.add_channel(channel);
656
657
g_debug("Loaded channel: %s", channel.name.c_str());
658
}
659
660
profiles.push_back(profile);
661
662
g_debug("Loaded profile: %s", profile.name.c_str());
663
}
664
665
return profiles;
666
}
667
668
void Data::replace_scheduled_recording(ScheduledRecording& scheduled_recording)
669
{
670
Glib::ustring fixed_description = scheduled_recording.description;
671
672
Glib::ustring select_command = Glib::ustring::compose
673
(
674
"SELECT * FROM SCHEDULED_RECORDING WHERE "\
675
"((START_TIME >= %1 AND START_TIME <= %2) OR "\
676
"(START_TIME+DURATION >= %1 AND START_TIME+DURATION <= %2) OR "\
677
"(START_TIME <= %1 AND START_TIME+DURATION >= %2)) AND "\
678
"CHANNEL_ID != %3",
679
scheduled_recording.start_time,
680
scheduled_recording.start_time + scheduled_recording.duration,
681
scheduled_recording.channel_id
682
);
683
684
Statement statement(database, select_command);
685
if (statement.step() == SQLITE_ROW)
686
{
687
Glib::ustring message = Glib::ustring::compose
688
(
689
_("Failed to save scheduled recording because it conflicts with another scheduled recording called '%1'"),
690
statement.get_text(1)
691
);
121
void Statement::set_int_parameter(guint index, int value)
122
{
123
sqlite3_bind_int(statement, index, value);
124
}
125
126
void Statement::set_string_parameter(guint index, const Glib::ustring& value)
127
{
128
sqlite3_bind_text(statement, index, value.c_str(), -1, NULL);
129
}
130
131
guint Statement::get_parameter_index(const Glib::ustring& name)
132
{
133
int index = sqlite3_bind_parameter_index(statement, name.c_str());
134
if (index == 0)
135
{
136
Glib::ustring message = Glib::ustring::compose("Unknown parameter name '%1'", name);
692
137
throw Exception(message);
693
}
694
695
fix_quotes(fixed_description);
696
697
Glib::ustring replace_command = Glib::ustring::compose
698
(
699
"REPLACE INTO SCHEDULED_RECORDING " \
700
"(SCHEDULED_RECORDING_ID, DESCRIPTION, TYPE, CHANNEL_ID, START_TIME, DURATION) VALUES " \
701
"(%1, '%2', %3, %4, %5, %6);",
702
scheduled_recording.scheduled_recording_id == 0 ? "NULL" : Glib::ustring::compose("%1", scheduled_recording.scheduled_recording_id),
703
fixed_description,
704
scheduled_recording.type,
705
scheduled_recording.channel_id,
706
scheduled_recording.start_time,
707
scheduled_recording.duration
708
);
709
710
execute_non_query(replace_command);
711
712
if (scheduled_recording.scheduled_recording_id == 0)
713
{
714
scheduled_recording.scheduled_recording_id = sqlite3_last_insert_rowid(database);
715
if (scheduled_recording.scheduled_recording_id == 0)
716
{
717
throw Exception(_("ASSERT: scheduled_recording.scheduled_recording_id == 0"));
718
}
719
}
720
721
g_debug("Scheduled recording ID '%d' saved", scheduled_recording.scheduled_recording_id);
722
}
723
724
ScheduledRecordingList Data::get_scheduled_recordings()
725
{
726
ScheduledRecordingList result;
727
728
Glib::ustring select_command = "SELECT * FROM SCHEDULED_RECORDING ORDER BY START_TIME";
729
730
Statement statement(database, select_command);
138
}
139
return index;
140
}
141
142
void Statement::set_int_parameter(const Glib::ustring& name, int value)
143
{
144
set_int_parameter(get_parameter_index(name), value);
145
}
146
147
void Statement::set_string_parameter(const Glib::ustring& name, const Glib::ustring& value)
148
{
149
set_string_parameter(get_parameter_index(name), value);
150
}
151
152
Connection::Connection(const Glib::ustring& filename)
153
{
154
gboolean database_exists = Gio::File::create_for_path(filename)->query_exists();
155
156
g_debug("Database '%s' ", database_exists ? "exists" : "does not exist");
157
g_debug("Opening database file '%s'", filename.c_str());
158
if (sqlite3_open(filename.c_str(), &connection) != 0)
159
{
160
Glib::ustring message = Glib::ustring::compose(_("Failed to connect to Me TV database '%1'"), filename);
161
throw SQLiteException(connection, message);
162
}
163
164
database_created = !database_exists;
165
}
166
167
Connection::~Connection()
168
{
169
if (connection != NULL)
170
{
171
sqlite3_close(connection);
172
connection = NULL;
173
}
174
}
175
176
guint Connection::get_last_insert_rowid()
177
{
178
return sqlite3_last_insert_rowid(connection);
179
}
180
181
void SchemaAdapter::initialise_schema()
182
{
183
for (Tables::iterator i = schema.tables.begin(); i != schema.tables.end(); i++)
184
{
185
Table& table = i->second;
186
initialise_table(table);
187
}
188
}
189
190
void SchemaAdapter::initialise_table(Table& table)
191
{
192
g_debug("Initialising table '%s'", table.name.c_str());
193
194
Glib::ustring command = "CREATE TABLE IF NOT EXISTS ";
195
command += table.name;
196
command += " (";
197
gboolean first_column = true;
198
for (Columns::const_iterator j = table.columns.begin(); j != table.columns.end(); j++)
199
{
200
const Column& column = *j;
201
202
if (first_column)
203
{
204
first_column = false;
205
}
206
else
207
{
208
command += ", ";
209
}
210
211
command += column.name;
212
command += " ";
213
214
switch (column.type)
215
{
216
case DATA_TYPE_INTEGER:
217
command += "INTEGER";
218
break;
219
case DATA_TYPE_STRING:
220
command += Glib::ustring::compose("CHAR(%1)", column.size);
221
break;
222
default:
223
break;
224
}
225
226
if (column.name == table.primary_key)
227
{
228
if (column.type != DATA_TYPE_INTEGER)
229
{
230
throw Exception(_("Only integers can be primary keys"));
231
}
232
233
command += " PRIMARY KEY AUTOINCREMENT";
234
}
235
else
236
{
237
if (!column.nullable)
238
{
239
command += " NOT NULL";
240
}
241
}
242
}
243
244
for (Constraints::const_iterator j = table.constraints.begin(); j != table.constraints.end(); j++)
245
{
246
const Constraint& constraint = *j;
247
if (constraint.type == ConstraintTypeUnique)
248
{
249
gboolean first_constraint = true;
250
command += ", UNIQUE (";
251
for (StringList::const_iterator k = constraint.columns.begin(); k != constraint.columns.end(); k++)
252
{
253
Glib::ustring column = *k;
254
255
if (first_constraint)
256
{
257
first_constraint = false;
258
}
259
else
260
{
261
command += ", ";
262
}
263
264
command += column;
265
}
266
command += ")";
267
}
268
}
269
270
command += ");";
271
272
Statement statement(connection, command);
273
statement.step();
274
}
275
276
void SchemaAdapter::drop_schema()
277
{
278
for (Tables::const_iterator i = schema.tables.begin(); i != schema.tables.end(); i++)
279
{
280
const Table& table = i->second;
281
282
g_debug("Dropping table '%s'", table.name.c_str());
283
284
Glib::ustring command = Glib::ustring::compose("DROP TABLE %1", table.name);
285
Statement statement(connection, command);
286
statement.step();
287
}
288
}
289
290
TableAdapter::TableAdapter(Connection& connection, Table& table)
291
: connection(connection), table(table)
292
{
293
Glib::ustring fields;
294
Glib::ustring replace_fields;
295
Glib::ustring replace_values;
296
297
for (Columns::const_iterator j = table.columns.begin(); j != table.columns.end(); j++)
298
{
299
const Column& column = *j;
300
301
if (fields.length() > 0) fields += ", ";
302
if (replace_fields.length() > 0) replace_fields += ", ";
303
if (replace_values.length() > 0) replace_values += ", ";
304
305
fields += column.name;
306
replace_fields += column.name;
307
replace_values += ":" + column.name;
308
}
309
310
select_command = Glib::ustring::compose("SELECT %1 FROM %2", fields, table.name);
311
replace_command = Glib::ustring::compose("REPLACE INTO %1 (%2) VALUES (%3)", table.name, replace_fields, replace_values);
312
delete_command = Glib::ustring::compose("DELETE FROM %1", table.name);
313
update_command = Glib::ustring::compose("UPDATE %1 SET", table.name);
314
}
315
316
void TableAdapter::replace_rows(DataTable& data_table)
317
{
318
Glib::ustring primary_key = data_table.table.primary_key;
319
320
Statement statement(connection, replace_command);
321
322
if (data_table.rows.size() > 0)
323
{
324
Statement statementBegin(connection, "BEGIN");
325
statementBegin.step();
326
327
for (Data::Rows::iterator i = data_table.rows.begin(); i != data_table.rows.end(); i++)
328
{
329
Data::Row& row = *i;
330
331
for (Columns::iterator j = data_table.table.columns.begin(); j != data_table.table.columns.end(); j++)
332
{
333
Column& column = *j;
334
335
if (column.name != primary_key || row[column.name].int_value != 0)
336
{
337
switch(column.type)
338
{
339
case DATA_TYPE_INTEGER:
340
statement.set_int_parameter(":" + column.name, row[column.name].int_value);
341
break;
342
case DATA_TYPE_STRING:
343
statement.set_string_parameter(":" + column.name, row[column.name].string_value);
344
break;
345
default:
346
break;
347
}
348
}
349
}
350
351
statement.step();
352
353
if (row.auto_increment != NULL && *row.auto_increment == 0)
354
{
355
*(row.auto_increment) = connection.get_last_insert_rowid();
356
g_debug("%s row replaced for id %d",
357
data_table.table.name.c_str(), *(row.auto_increment));
358
}
359
else
360
{
361
g_debug("%s row replaced", data_table.table.name.c_str());
362
}
363
364
statement.reset();
365
}
366
367
Statement statementEnd(connection, "End");
368
statementEnd.step();
369
}
370
}
371
372
DataTable TableAdapter::select_row(guint key)
373
{
374
Glib::ustring where = Glib::ustring::compose(
375
"%1 = %2", table.name, table.primary_key, key);
376
return select_rows(where);
377
}
378
379
DataTable TableAdapter::select_rows(const Glib::ustring& where, const Glib::ustring& sort)
380
{
381
DataTable data_table(table);
382
Glib::ustring command = select_command;
383
if (where.length() > 0)
384
{
385
command += Glib::ustring::compose(" WHERE %1", where);
386
}
387
if (sort.length() > 0)
388
{
389
command += Glib::ustring::compose(" ORDER BY %1", sort);
390
}
391
392
Statement statement(connection, command);
731
393
while (statement.step() == SQLITE_ROW)
732
394
{
733
ScheduledRecording scheduled_recording;
734
load_scheduled_recording(statement, scheduled_recording);
735
result.push_back(scheduled_recording);
736
}
737
738
return result;
739
}
740
741
void Data::delete_scheduled_recording(guint scheduled_recording_id)
742
{
743
Glib::ustring command = Glib::ustring::compose(
744
"DELETE FROM SCHEDULED_RECORDING WHERE SCHEDULED_RECORDING_ID=%1",
745
scheduled_recording_id);
746
execute_non_query(command);
747
}
748
749
void Data::load_scheduled_recording(Statement& statement, ScheduledRecording& scheduled_recording)
750
{
751
scheduled_recording.scheduled_recording_id = statement.get_int(0);
752
scheduled_recording.description = statement.get_text(1);
753
scheduled_recording.type = statement.get_int(2);
754
scheduled_recording.channel_id = statement.get_int(3);
755
scheduled_recording.start_time = statement.get_int(4);
756
scheduled_recording.duration = statement.get_int(5);
757
}
758
759
gboolean Data::get_scheduled_recording(guint scheduled_recording_id, ScheduledRecording& scheduled_recording)
760
{
761
gboolean result = false;
762
Glib::ustring select_command = Glib::ustring::compose(
763
"SELECT * FROM SCHEDULED_RECORDING WHERE SCHEDULED_RECORDING_ID=%1;",
764
scheduled_recording_id
765
);
766
767
Statement statement(database, select_command);
768
if (statement.step() == SQLITE_ROW)
769
{
770
load_scheduled_recording(statement, scheduled_recording);
771
result = true;
772
}
773
774
return result;
775
}
776
777
void Data::delete_channel(guint channel_id)
778
{
779
execute_non_query(Glib::ustring::compose(
780
"DELETE FROM EPG_EVENT_TEXT WHERE EPG_EVENT_ID IN "\
781
"(SELECT EPG_EVENT_ID FROM EPG_EVENT WHERE CHANNEL_ID=%1);",
782
channel_id));
783
execute_non_query(Glib::ustring::compose(
784
"DELETE FROM EPG_EVENT WHERE CHANNEL_ID=%1;",
785
channel_id));
786
execute_non_query(Glib::ustring::compose(
787
"DELETE FROM CHANNEL WHERE CHANNEL_ID=%1;",
788
channel_id));
789
execute_non_query(Glib::ustring::compose(
790
"DELETE FROM SCHEDULED_RECORDING WHERE CHANNEL_ID=%1;",
791
channel_id));
792
}
793
794
void Data::delete_old_scheduled_recordings()
795
{
796
execute_non_query(Glib::ustring::compose(
797
"DELETE FROM SCHEDULED_RECORDING WHERE (START_TIME+DURATION)<%1;",
798
time(NULL)));
799
}
800
801
void Data::delete_old_epg_events()
802
{
803
time_t expired_time = convert_to_local_time(time(NULL));
804
805
execute_non_query(Glib::ustring::compose(
806
"DELETE FROM EPG_EVENT_TEXT WHERE EPG_EVENT_ID IN "\
807
"(SELECT EPG_EVENT_ID FROM EPG_EVENT WHERE (START_TIME+DURATION)<%1);",
808
expired_time));
809
execute_non_query(Glib::ustring::compose(
810
"DELETE FROM EPG_EVENT WHERE (START_TIME+DURATION)<%1;",
811
expired_time));
812
813
g_debug("Expired EPG events cleanup");
814
}
815
816
void Data::vacuum()
817
{
818
execute_non_query("VACUUM;");
819
}
b'\\ No newline at end of file'
395
Row row;
396
397
for (Columns::const_iterator j = table.columns.begin(); j != table.columns.end(); j++)
398
{
399
const Column& column = *j;
400
401
switch (column.type)
402
{
403
case DATA_TYPE_INTEGER:
404
row[column.name].int_value = statement.get_int(column.index);
405
break;
406
case DATA_TYPE_STRING:
407
row[column.name].string_value = statement.get_text(column.index);
408
break;
409
default:
410
break;
411
}
412
}
413
414
data_table.rows.push_back(row);
415
}
416
return data_table;
417
}
418
419
void TableAdapter::delete_row(guint key)
420
{
421
Glib::ustring where = Glib::ustring::compose("%1 = %2", table.primary_key, key);
422
delete_rows(where);
423
}
424
425
void TableAdapter::delete_rows(const Glib::ustring& where)
426
{
427
Glib::ustring command = delete_command;
428
if (where.length() > 0)
429
{
430
command += Glib::ustring::compose(" WHERE %1", where);
431
}
432
Statement statement(connection, command);
433
statement.step();
434
}
435
436
void TableAdapter::update_rows(const Glib::ustring& set, const Glib::ustring& where)
437
{
438
DataTable data_table(table);
439
Glib::ustring command = update_command;
440
if (set.length() > 0)
441
{
442
command += Glib::ustring::compose(" %1", set);
443
}
444
if (where.length() > 0)
445
{
446
command += Glib::ustring::compose(" WHERE %1", where);
447
}
448
449
Statement statement(connection, command);
450
statement.step();
451
}
452
453
void Connection::vacuum()
454
{
455
Statement statement(*this, "VACUUM");
456
statement.step();
457
}
Loggerhead is a web-based interface for Breezy
Version: 2.0.1