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
|
package journal_database;
use DBD::SQLite;
use DBI;
use Data::Dumper;
my $dbh = DBI->connect("dbi:SQLite:dbname=./database/journals.db","","");
CREATE TABLE journals(
id INTEGER PRIMARY KEY NOT NULL, --optional
date INTEGER NOT NULL,
fulltext TEXT,
image_url TEXT
);
sub remove_from_db{
my $id_to_remove = shift;
my $sth = $dbh->prepare(
"DELETE FROM journals ".
"WHERE id=$id_to_remove;"
);
$sth->execute();
}
sub get_entry_by_date {
my $date_int = shift;
my $sth = $dbh->prepare(
"SELECT *".
"FROM journals ".
"WHERE `date`=? "
);
$sth->execute($date_int);
return $sth->fetchrow_hashref();
}
sub add_journal_entry{
my $entry = shift;
my $sqlCmd = "insert into journals values (?,?,?,?);" ;
# ."on duplicate key update ".
# "id = ?,".
# "date = ?,".
# "fulltext = ?,".
# "image_url = ?,".
# ";";
my $sth = $dbh->do($sqlCmd,
undef,
$entry->{id},
$entry->{date} || time(),
$entry->{fulltext},
$entry->{image_url}
);
}
my $entry = {};
$entry->{id} = '12';
$entry->{date} = '123432';
$entry->{fulltext} = 'this is a test of the fulltext';
$entry->{image_url} = 'this is a test of the image url';
add_journal_entry($entry);
print Dumper get_entry_by_date(123432);
remove_from_db(12);
|
