1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
|
/*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; version 2 dated June, 1991.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA
* 02111-1307, USA.
*/
#include <stdio.h>
#include <sqlite.h>
#include <sys/time.h>
#include "includes.h"
#include "sqlite_functions.h"
extern int fd;
extern char *zErrMsg;
extern int delete_timer;
extern int min_time_save;
/* This function will create the database if doesn't exist*/
void update_db()
{
char *err;
/* We launch the SELECT request*/
rc = sqlite_exec(db, "select version from info",callback_check_table_presence, 0, &zErrMsg);
if(hits == 0)
{
printf("Creating Database ...\n");
sqlite_exec_printf(db,"create table stats(id INTEGER PRIMARY KEY, name, length, hits,listen_time, last_listen)",0, 0, &err);
sqlite_exec_printf(db,"create table info(version PRIMARY KEY, name,author)",0, 0, &err);
sqlite_exec_printf(db,"INSERT INTO info ('version','name','author') values('%s','%s','Renaud Galante <diodo@oute.org>')",0,0,&err,VERSION,PLUGIN_NAME);
}
else delete_old_songs();
hits=0;
}
void insert_data(struct RecordStat record)
{
struct timeval current_time;
struct timezone tz;
char *err;
char *request = "select hits, listen_time from stats where name=\"";
char buf_request[strlen(request)+strlen(record.title)+2];
strncpy(buf_request,request,strlen(request)+1);
strncat(buf_request,record.title,strlen(record.title)+1);
strncat(buf_request,"\"",1);
/* We launch the SELECT request*/
rc = sqlite_exec(db, buf_request, callback_nb_result, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
printf("SQL error_select: %s\n", zErrMsg);
return;
}
gettimeofday(¤t_time,&tz);
/* If no data found, the callback_nb_result cannot set hits.
So, if hits == 0, we must add the song*/
if (!hits)
{
if ( (min_time_save > 0) && (record.timelistening > (min_time_save*1000)))
{
sqlite_exec_printf(db,"INSERT INTO stats('name','length','listen_time','hits','last_listen') VALUES('%q','%d','%d','1','%d')",0, 0, &err,record.title,record.timelength,record.timelistening,current_time.tv_sec);
if(err)
printf("SQL_error_insert: %s.\n",err);
}
else if ((min_time_save == 0) && ( (record.timelistening + 3000) > record.timelength ))
{
sqlite_exec_printf(db,"INSERT INTO stats('name','length','listen_time','hits','last_listen') VALUES('%q','%d','%d','1','%d')",0, 0, &err,record.title,record.timelength,record.timelength,current_time.tv_sec);
}
}
/* else we just make an update */
else
{
if( (min_time_save > 0) && (record.timelistening > (min_time_save*1000)))
{
sqlite_exec_printf(db,
"UPDATE stats SET length='%d',listen_time='%d',hits='%d', last_listen='%d' where name='%q'"
,0,0,&err,record.timelength, record.timelistening + listen_time,
hits+1,current_time.tv_sec,record.title);
if(err)
printf("SQL_error_update: %s.\n",err);
}
else if ((min_time_save == 0) && ( (record.timelistening + 3000) > record.timelength ))
{
sqlite_exec_printf(db,
"UPDATE stats SET length='%d',listen_time='%d',hits='%d', last_listen='%d' where name='%q'"
,0,0,&err,record.timelength, record.timelength + listen_time,
hits+1,current_time.tv_sec,record.title);
}
}
hits=0;
}
/* Delete song from database when they're too old*/
void delete_old_songs()
{
struct timeval current_time;
struct timezone tz;
char *err;
gettimeofday(¤t_time,&tz);
sqlite_exec_printf(db,"DELETE from stats WHERE id in (SELECT id from stats where ((%d - last_listen)>%d))",0,0,&err,current_time.tv_sec,delete_timer);
if(err)
printf("SQL_error_purge: %s.\n",err);
}
|
