3
# mysql database backend. see docu: perldoc NOTEDB::mysql
9
$NOTEDB::mysql::VERSION = "1.51";
17
use vars qw(@ISA @EXPORT);
18
@ISA = qw(NOTEDB Exporter);
24
my($this, %param) = @_;
26
my $class = ref($this) || $this;
30
my $dbname = $param{dbname} || "note";
31
my $dbhost = $param{dbhost} || "localhost";
32
my $dbuser = $param{dbuser} || "";
33
my $dbpasswd = $param{dbpasswd} || "";
34
my $dbport = $param{dbport} || "";
42
$database = "DBI:mysql:$dbname;host=$dbhost:$dbport";
45
$database = "DBI:mysql:$dbname;host=$dbhost";
48
$self->{table} = "note";
50
$self->{sql_getsingle} = "SELECT $fnote,$fdate,$ftopic FROM $self->{table} WHERE $fnum = ?";
51
$self->{sql_all} = "SELECT $fnum,$fnote,$fdate,$ftopic FROM $self->{table}";
52
$self->{sql_nextnum} = "SELECT max($fnum) FROM $self->{table}";
53
$self->{sql_incrnum} = "SELECT $fnum FROM $self->{table} ORDER BY $fnum";
54
$self->{sql_setnum} = "UPDATE $self->{table} SET $fnum = ? WHERE $fnum = ?";
55
$self->{sql_edit} = "UPDATE $self->{table} SET $fnote = ?, $fdate = ?, $ftopic = ? WHERE $fnum = ?";
56
$self->{sql_insertnew} = "INSERT INTO $self->{table} VALUES (?, ?, ?, ?)";
57
$self->{sql_del} = "DELETE FROM $self->{table} WHERE $fnum = ?";
58
$self->{sql_del_all} = "DELETE FROM $self->{table}";
60
$self->{DB} = DBI->connect($database, $dbuser, $dbpasswd) or die DBI->errstr();
70
$this->{DB}->disconnect;
77
my $lock = $this->{DB}->prepare("LOCK TABLES $this->{table} WRITE")
78
|| die $this->{DB}->errstr();
79
$lock->execute() || die $this->{DB}->errstr();
85
my $unlock = $this->{DB}->prepare("UNLOCK TABLES") || die $this->{DB}->errstr;
86
$unlock->execute() || die $this->{DB}->errstr();
92
return $this->{version};
99
my($note, $date, $topic);
100
my $statement = $this->{DB}->prepare($this->{sql_getsingle}) || die $this->{DB}->errstr();
102
$statement->execute($num) || die $this->{DB}->errstr();
103
$statement->bind_columns(undef, \($note, $date, $topic)) || die $this->{DB}->errstr();
105
while($statement->fetch) {
106
$note = $this->ude($note);
108
$note = "$topic\n" . $note;
110
return $note, $this->ude($date);
118
my($num, $note, $date, %res, $topic);
120
if ($this->unchanged) {
121
return %{$this->{cache}};
124
my $statement = $this->{DB}->prepare($this->{sql_all}) or die $this->{DB}->errstr();
126
$statement->execute or die $this->{DB}->errstr();
127
$statement->bind_columns(undef, \($num, $note, $date, $topic)) or die $this->{DB}->errstr();
129
while($statement->fetch) {
130
$res{$num}->{'note'} = $this->ude($note);
131
$res{$num}->{'date'} = $this->ude($date);
133
$res{$num}->{'note'} = "$topic\n" . $res{$num}->{'note'};
146
if ($this->unchanged) {
148
foreach (keys %{$this->{cache}}) {
154
my $statement = $this->{DB}->prepare($this->{sql_nextnum}) || die $this->{DB}->errstr();
156
$statement->execute || die $this->{DB}->errstr();
157
$statement->bind_columns(undef, \($num)) || die $this->{DB}->errstr();
159
while($statement->fetch) {
166
my($this, $searchstring) = @_;
167
my($num, $note, $date, %res, $match, $use_cache, $topic);
169
my $regex = $this->generate_search($searchstring);
172
print "invalid expression: \"$searchstring\"!\n";
177
if ($this->unchanged) {
178
foreach my $num (keys %{$this->{cache}}) {
179
$_ = $this->{cache}{$num}->{note};
182
$res{$num}->{note} = $this->{cache}{$num}->{note};
183
$res{$num}->{date} = $this->{cache}{$num}->{date}
190
my $statement = $this->{DB}->prepare($this->{sql_all}) or die $this->{DB}->errstr();
192
$statement->execute or die $this->{DB}->errstr();
193
$statement->bind_columns(undef, \($num, $note, $date, $topic)) or die $this->{DB}->errstr();
195
while($statement->fetch) {
196
$note = $this->ude($note);
197
$date = $this->ude($date);
199
$note = "$topic\n" . $note;
204
$res{$num}->{'note'} = $note;
205
$res{$num}->{'date'} = $date;
217
my($this, $num, $note, $date) = @_;
220
my $statement = $this->{DB}->prepare($this->{sql_edit}) or die $this->{DB}->errstr();
222
$note =~ s/\\/\\\\/g;
223
$statement->execute($this->uen($note), $this->uen($date), $num)
224
or die $this->{DB}->errstr();
232
my($this, $num, $note, $date) = @_;
234
my $statement = $this->{DB}->prepare($this->{sql_insertnew}) || die $this->{DB}->errstr();
236
my ($topic, $note) = $this->get_topic($note);
239
$note =~ s/\\/\\\\/g;
240
$topic =~ s/\\/\\\\/g;
241
$statement->execute($num, $this->uen($note), $this->uen($date), $topic) || die $this->{DB}->errstr();
249
my($this, $num) = @_;
250
my($note, $date, $T);
253
($note, $date) = $this->get_single($num);
255
return "ERROR" if ($date !~ /^\d/);
258
my $statement = $this->{DB}->prepare($this->{sql_del}) || die $this->{DB}->errstr();
259
$statement->execute($num) || die $this->{DB}->errstr();
270
my $statement = $this->{DB}->prepare($this->{sql_del_all}) || die $this->{DB}->errstr();
271
$statement->execute() || die $this->{DB}->errstr();
277
sub set_recountnums {
282
my(@count, $i, $num, $setnum, $pos);
284
$pos=0; $i=0; @count = ();
286
my $statement = $this->{DB}->prepare($this->{sql_incrnum}) || die $this->{DB}->errstr();
287
$statement->execute || die $this->{DB}->errstr();
288
$statement->bind_columns(undef, \($num)) || die $this->{DB}->errstr();
289
# store real id's in an array!
290
while($statement->fetch) {
295
my $sub_statement = $this->{DB}->prepare($this->{sql_setnum}) || die $this->{DB}->errstr();
296
for($pos=0;$pos<$i;$pos++) {
298
$sub_statement->execute($setnum,$count[$pos]) || die $this->{DB}->errstr();
305
my ($this, $data) = @_;
306
foreach my $num (keys %{$data}) {
307
my $pos = $this->get_nextnum();
308
$this->set_new($pos, $data->{$num}->{note}, $data->{$num}->{date});
316
if($NOTEDB::crypt_supported == 1) {
318
$T = pack("u", $this->{cipher}->encrypt($_[0]));
332
if($NOTEDB::crypt_supported == 1) {
334
$T = $this->{cipher}->decrypt(unpack("u",$_[0]))
344
my ($this, $data) = @_;
345
if ($data =~ /^\//) {
346
my($topic, $note) = split /\n/, $data, 2;
347
return ($topic, $note);
360
NOTEDB::mysql - module lib for accessing a notedb from perl
367
# create a new NOTEDB object (the last 4 params are db table/field names)
368
$db = new NOTEDB("mysql","note","localhost","username","password","note","number","note","date");
371
($note, $date) = $db->get_single(1);
373
# search for a certain note
374
%matching_notes = $db->get_search("somewhat");
375
# format of returned hash:
376
#$matching_notes{$numberofnote}->{'note' => 'something', 'date' => '23.12.2000 10:33:02'}
378
# get all existing notes
379
%all_notes = $db->get_all();
380
# format of returnes hash like the one from get_search above
382
# get the next noteid available
383
$next_num = $db->get_nextnum();
385
# recount all noteids starting by 1 (usefull after deleting one!)
386
$db->set_recountnums();
388
# modify a certain note
389
$db->set_edit(1, "any text", "23.12.2000 10:33:02");
392
$db->set_new(5, "any new text", "23.12.2000 10:33:02");
394
# delete a certain note
397
# turn on encryption. CryptMethod must be IDEA, DES or BLOWFISH
398
$db->use_crypt("passphrase", "CryptMethod");
400
# turn off encryption. This is the default.
405
You can use this module for accessing a note database. There are currently
406
two versions of this module, one version for a SQL database and one for a
407
binary file (note's own database-format).
408
However, both versions provides identical interfaces, which means, you do
409
not need to change your code, if you want to switch to another database format.
411
Currently, NOTEDB module is only used by note itself. But feel free to use it
412
within your own project! Perhaps someone want to implement a webinterface to
417
please see the section SYNOPSIS, it says it all.
421
Thomas Linden <tom@daemon.de>.