root/main/trunk/greenstone2/perllib/gssql.pm @ 32561

Revision 32561, 24.4 KB (checked in by ak19, 20 months ago)

Not just tables, but database names can't have hyphens in them when using MySQL.

Line 
1###########################################################################
2#
3# gssql.pm -- DBI for SQL related utility functions used by
4# GreenstoneSQLPlugout and hereafter by GreenstoneSQLPlugin too.
5# A component of the Greenstone digital library software
6# from the New Zealand Digital Library Project at the
7# University of Waikato, New Zealand.
8#
9# Copyright (C) 1999 New Zealand Digital Library Project
10#
11# This program is free software; you can redistribute it and/or modify
12# it under the terms of the GNU General Public License as published by
13# the Free Software Foundation; either version 2 of the License, or
14# (at your option) any later version.
15#
16# This program is distributed in the hope that it will be useful,
17# but WITHOUT ANY WARRANTY; without even the implied warranty of
18# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
19# GNU General Public License for more details.
20#
21# You should have received a copy of the GNU General Public License
22# along with this program; if not, write to the Free Software
23# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
24#
25###########################################################################
26
27package gssql;
28
29use strict;
30no strict 'refs';
31no strict 'subs';
32
33use DBI; # the central package for this module used by GreenstoneSQL Plugout and Plugin
34
35# Need params_map keys:
36# - collection_name
37# - db_encoding (db content encoding) - MySQL can set this at server, db, table levels. For MySQL
38# we set the enc during connect at server level. Not sure whether other DB's support it at the
39# same levels.
40
41# For connection to MySQL, need:
42#  - db_driver, db_client_user, db_client_pwd, db_host, (db_port not used at present)
43# So these will be parameterised, but in a hashmap, for just the connect method.
44
45# Parameterise (one or more methods may use them):
46# - build_mode (like removeold)
47# - db_name (which is the GS3 sitename)
48
49# TODO: add infrastructure for db_port, AutoCommit etc
50# For port, see https://stackoverflow.com/questions/2248665/perl-script-to-connect-to-mysql-server-port-3307
51
52sub new
53
54    my $class = shift(@_);
55   
56    my ($params_map) = @_;
57   
58    # library_url: to be specified on the cmdline if not using a GS-included web server
59    # the GSDL_LIBRARY_URL env var is useful when running cmdline buildcol.pl in the linux package manager versions of GS3
60   
61    # https://stackoverflow.com/questions/7083453/copying-a-hashref-in-perl
62    # Making a shallow copy works, and can handle unknown params:
63    #my $self = $params_map;
64
65    # but being explicit for class params needed for MySQL:
66    my $self = {
67    'collection_name' => $params_map->{'collection_name'},
68    'verbosity' => $params_map->{'verbosity'} || 1
69    };
70
71    # The db_encoding option is presently not passed in to this constructor as parameter.
72    # Placed here to indicate it's sort of optional.
73    # Since docxml are all in utf8, the contents of the GS SQL database should be too,
74    # So making utf8 the hidden default at present.
75    $self->{'db_encoding'} = $params_map->{'db_encoding'} || "utf8";
76   
77    $self = bless($self, $class);   
78
79    $self->{'tablename_prefix'} = $self->sanitize_name($params_map->{'collection_name'});
80   
81    return $self;
82}
83
84
85#################################
86
87# Database access related functions
88# http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm
89# https://www.guru99.com/insert-into.html
90
91# TODO Q: What on cancelling a build: delete table? But what if it was a rebuild and the rebuild is cancelled (not the original build)?
92# Do we create a copy of the orig database as backup, then start populating current db, and if cancelled, delete current db and RENAME backup table to current?
93# https://stackoverflow.com/questions/3280006/duplicating-a-mysql-table-indexes-and-data
94# BUT what if the table is HUGE? (Think of a collection with millions of docs.) Huge overhead in copying?
95# The alternative is we just quit on cancel, but then: cancel could leave the table in a partial committed state, with no way of rolling back.
96# Unless they do a full rebuild, which will recreate the table from scratch?
97# SOLUTION-> rollback transaction on error, see https://www.effectiveperlprogramming.com/2010/07/set-custom-dbi-error-handlers/
98# But then should set AutoCommit to off on connection, and remember to commit every time
99
100#################
101# Database functions that use the perl DBI module (with the DBD driver module for mysql)
102#################
103
104################### BASIC DB OPERATIONS ##################
105
106# THE NEW DB FUNCTIONS
107# NOTE: FULLTEXT is a reserved keyword in (My)SQL. So we can't name a table or any of its columns "fulltext".
108# https://dev.mysql.com/doc/refman/5.5/en/keywords.html
109
110# TODO: Consider AutoCommit status (and Autocommit off allowing commit or rollback for GS coll build cancel) later
111
112# TODO: where should the defaults for these params be, here or in GS-SQLPlugin/Plugout?
113sub connect_to_db {
114    my $self= shift (@_);
115    my ($params_map) = @_;
116    # For proper utf8 support in MySQL, encoding should be 'utf8mb4' as 'utf8' is insufficient
117    my $db_enc = "utf8mb4" if $self->{'db_encoding'} eq "utf8";
118
119    # these are the params for connecting to MySQL
120    my $db_driver = $params_map->{'db_driver'} || "mysql";
121    my $db_user = $params_map->{'db_client_user'} || "root";
122    my $db_pwd = $params_map->{'db_client_pwd'}; # even if undef, we'll see a sensible error message
123                                           # when connect fails
124    my $db_host = $params_map->{'db_host'} || "127.0.0.1";
125    # localhost doesn't work for us, but 127.0.0.1 works
126    # https://metacpan.org/pod/DBD::mysql
127    # "The hostname, if not specified or specified as '' or 'localhost', will default to a MySQL server
128    # running on the local machine using the default for the UNIX socket. To connect to a MySQL server
129    # on the local machine via TCP, you must specify the loopback IP address (127.0.0.1) as the host."
130    #my $connect_str = "dbi:$db_driver:database=$db_name;host=$db_host";
131    my $connect_str = "dbi:$db_driver:host=$db_host"; # don't provide db - allows checking the db exists later when loading the db
132
133    if($self->{'verbosity'}) {
134    print STDERR "Away to make connection to $db_driver database with:\n";
135    print STDERR " - hostname $db_host; username: $db_user";
136    print STDERR "; and the password provided" if $db_pwd;
137    print STDERR "\nAssuming the mysql server has been started with: --character_set_server=utf8mb4\n" if $db_driver eq "mysql";
138    }
139   
140    my $dbh = DBI->connect("$connect_str", $db_user, $db_pwd,
141               {
142                   ShowErrorStatement => 1, # more informative as DBI will append failed SQL stmt to error message
143                   PrintError => 1, # on by default, but being explicit
144                   RaiseError => 0, # off by default, but being explicit
145                   AutoCommit => 1, # on by default, but being explicit
146                   mysql_enable_utf8mb4 => 1 # tells MySQL to use UTF-8 for communication and tells DBD::mysql to decode the data, see https://stackoverflow.com/questions/46727362/perl-mysql-utf8mb4-issue-possible-bug
147               });
148
149    if(!$dbh) {
150    # NOTE, despite handle dbh being undefined, error code will be in DBI->err (note caps)
151    return 0;   
152    }
153
154    # set encoding https://metacpan.org/pod/DBD::mysql
155    # https://dev.mysql.com/doc/refman/5.7/en/charset.html
156    # https://dev.mysql.com/doc/refman/5.7/en/charset-conversion.html
157    # Setting the encoding at db server level: $dbh->do("set NAMES '" . $db_enc . "'");
158    # HOWEVER:
159    # It turned out insufficient setting the encoding to utf8, as that only supports utf8 chars that
160    # need up to 3 bytes. We may need up to 4 bytes per utf8 character, e.g. chars with macron,
161    # and for that, we need the encoding to be set to utf8mb4.
162    # To set up a MySQL db to use utf8mb4 requires configuration on the server side too.
163    # https://stackoverflow.com/questions/10957238/incorrect-string-value-when-trying-to-insert-utf-8-into-mysql-via-jdbc
164    # https://stackoverflow.com/questions/46727362/perl-mysql-utf8mb4-issue-possible-bug
165    # To set up the db for utf8mb4, therefore,
166    # the MySQL server needs to be configured for that char encoding by running the server as:
167    # mysql-5.7.23-linux-glibc2.12-x86_64/bin>./mysqld_safe --datadir=/Scratch/ak19/mysql/data --character_set_server=utf8mb4
168    # AND when connecting to the server, we can can either set mysql_enable_utf8mb4 => 1
169    # as a connection option
170    # OR we need to do both "set NAMES utf8mb4" AND "$dbh->{mysql_enable_utf8mb4} = 1;" after connecting
171    #
172    # Search results for DBI Set Names imply the "SET NAMES '<enc>'" command is mysql specific too,
173    # so setting the mysql specific option during connection above as "mysql_enable_utf8mb4 => 1"
174    # is no more objectionable. It has the advantage of cutting out the 2 extra lines of doing
175    # set NAMES '<enc>' and $dbh->{mysql_enable_utf8mb4} = 1 here.
176    # These lines may be preferred if more db_driver options are to be supported in future:
177    # then a separate method called set_db_encoding($enc) can work out what db_driver we're using
178    # and if mysql and enc=utfy, then it can do the following whereas it will issue other do stmts
179    # for other db_drivers, see https://www.perlmonks.org/?node_id=259456:
180   
181    #my $stmt = "set NAMES '" . $db_enc . "'";
182    #$dbh->do($stmt) || warn("Unable to set charset encoding at db server level to: " . $db_enc . "\n"); # tells MySQL to use UTF-8 for communication
183    #$dbh->{mysql_enable_utf8mb4} = 1; # tells DBD::mysql to decode the data
184   
185    # if we're here, then connection succeeded, store handle
186    $self->{'db_handle'} = $dbh;
187    return 1;
188}
189
190# will attempt to load the specified db and the <coll>_metadata and <coll>_fulltxt for this
191# collection, or create any of these (db, tables) that don't yet exist. At the end
192# it will have loaded the requested database (in MySQL: "use <db>;")
193sub load_db_and_tables {
194    my $self= shift (@_);
195    my ($db_name, $build_mode) = @_;
196    my $dbh = $self->{'db_handle'};
197    $db_name = $self->sanitize_name($db_name);
198   
199    print STDERR "Attempting to use database $db_name\n" if($self->{'verbosity'});
200   
201    # perl DBI switch database: https://www.perlmonks.org/?node_id=995434
202    # do() returns undef on error.
203    # connection succeeded, try to load our database. If that didn't work, attempt to create db
204    my $success = $dbh->do("use $db_name");
205   
206    if(!$success && $dbh->err == 1049) { # "Unknown database" error has code 1049 (mysql only?) meaning db doesn't exist yet
207
208    print STDERR "Database $db_name didn't exist, creating it along with the tables for the current collection...\n" if($self->{'verbosity'});
209   
210    # attempt to create the db and its tables
211    $self->create_db($db_name) || return 0;
212
213    print STDERR "   Created database $db_name\n" if($self->{'verbosity'} > 1);
214   
215    # once more attempt to use db, now that it exists
216    $dbh->do("use $db_name") || return 0;
217    #$dbh->do("use localsite") or die "Error (code" . $dbh->err ."): " . $dbh->errstr . "\n";
218
219    # attempt to create tables in current db
220    $self->create_metadata_table() || return 0;
221    $self->create_fulltext_table() || return 0;
222
223    $success = 1;
224    }
225    elsif($success) { # database existed and loaded successfully, but
226    # before proceeding check that the current collection's tables exist
227
228    print STDERR "@@@ DATABASE $db_name EXISTED\n" if($self->{'verbosity'} > 2);
229   
230
231    # build_mode can be removeold or incremental. We only do something special on removeold:
232    # deleting the existing tables for this collection and recreating empty ones
233    if($build_mode eq "removeold") {
234        print STDERR "   Building with removeold option set, so deleting current collection's tables if they exist\n" if($self->{'verbosity'});
235        $self->delete_collection_tables();
236    }
237
238    # use existing tables if any
239    # attempt to create tables in current db   
240    if($build_mode eq "removeold" || !$self->table_exists($self->get_metadata_table_name())) {
241        $self->create_metadata_table() || return 0;
242    } else {
243        print STDERR "@@@ Meta table exists\n" if($self->{'verbosity'} > 2);
244    }
245    if($build_mode eq "removeold" || !$self->table_exists($self->get_fulltext_table_name())) {
246        $self->create_fulltext_table() || return 0;
247    } else {
248        print STDERR "@@@ Fulltxt table exists\n" if($self->{'verbosity'} > 2);
249    }
250   
251    }
252   
253    return $success; # could still return 0, if database failed to load with an error code != 1049
254}
255
256# GreenstoneSQLPlugin calls this method to load an existing db.
257# This will terminate if the db does not exist. Unlike load_db_and_tables() above, used by
258# GreenstoneSQLPlugout, this method will not attempt to create the requested db (nor its tables)
259# TODO: GS SQLPlugin is called before GS SQLPlugout and attempts to use_db() - called in plugin's
260# init() method. This will fail if the db does not exist. Ideally want the gssqlplugin only called
261# during buildcol.pl
262sub use_db {
263    my $self= shift (@_);
264    my ($db_name) = @_;
265    my $dbh = $self->{'db_handle'};
266    $db_name = $self->sanitize_name($db_name);
267
268   
269    print STDERR "Loading database $db_name\n" if($self->{'verbosity'} > 1);
270   
271    # perl DBI switch database: https://www.perlmonks.org/?node_id=995434
272    # do() returns undef on error.
273    # connection succeeded, try to load our database. If that didn't work, attempt to create db
274    return $dbh->do("use $db_name") || warn();
275}
276
277# disconnect from db - https://metacpan.org/pod/DBI#disconnect
278# TODO: make sure to have committed or rolled back before disconnect
279# and that you've call finish() on statement handles if any fetch remnants remain
280sub disconnect_from_db {
281    my $self= shift (@_);   
282    my $dbh = $self->{'db_handle'};
283
284    # make sure any active stmt handles are finished
285    # NO: "When all the data has been fetched from a SELECT statement, the driver will automatically call finish for you. So you should not call it explicitly except when you know that you've not fetched all the data from a statement handle and the handle won't be destroyed soon."
286   
287    #$meta_sth = $self->{'metadata_prepared_insert_statement_handle'};
288    #$txt_sth = $self->{'fulltxt_prepared_insert_statement_handle'};
289    #$meta_sth->finish() if($meta_sth);
290    #$txt_sth->finish() if($txt_sth);
291
292    print STDERR "Disconnecting from database\n" if($self->{'verbosity'} > 1);
293   
294    my $rc = $dbh->disconnect or warn $dbh->errstr; # The handle is of little use after disconnecting. Possibly PrintError already prints a warning and this duplicates it?
295    return $rc;
296}
297
298sub create_db {
299    my $self= shift (@_);
300    my ($db_name) = @_;
301    my $dbh = $self->{'db_handle'};
302    $db_name = $self->sanitize_name($db_name);
303   
304    # https://stackoverflow.com/questions/5025768/how-can-i-create-a-mysql-database-from-a-perl-script
305    return $dbh->do("create database $db_name"); # do() will return undef on fail, https://metacpan.org/pod/DBI#do
306}
307
308
309sub create_metadata_table {
310    my $self= shift (@_);
311    my $dbh = $self->{'db_handle'};
312   
313    my $table_name = $self->get_metadata_table_name();
314    print STDERR "   Creating table $table_name\n" if($self->{'verbosity'} > 1);
315   
316    # If using an auto incremented primary key:
317    my $stmt = "CREATE TABLE $table_name (id INT NOT NULL AUTO_INCREMENT, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, metaname VARCHAR(127) NOT NULL, metavalue VARCHAR(1023) NOT NULL, PRIMARY KEY(id));";
318    return $dbh->do($stmt);
319}
320
321# TODO: Investigate: https://dev.mysql.com/doc/search/?d=10&p=1&q=FULLTEXT
322# 12.9.1 Natural Language Full-Text Searches
323# to see whether we have to index the 'fulltxt' column of the 'fulltext' tables
324# or let user edit this file, or add it as another option
325sub create_fulltext_table {
326    my $self= shift (@_);
327    my $dbh = $self->{'db_handle'};
328   
329    my $table_name = $self->get_fulltext_table_name();
330    print STDERR "   Creating table $table_name\n" if($self->{'verbosity'} > 1);
331   
332    # If using an auto incremented primary key:
333    my $stmt = "CREATE TABLE $table_name (id INT NOT NULL AUTO_INCREMENT, did VARCHAR(63) NOT NULL, sid VARCHAR(63) NOT NULL, fulltxt LONGTEXT, PRIMARY KEY(id));";
334    return $dbh->do($stmt);
335
336}
337
338# "IF EXISTS is used to prevent an error from occurring if the database does not exist. ... DROP DATABASE returns the number of tables that were removed. The DROP DATABASE statement removes from the given database directory those files and directories that MySQL itself may create during normal operation.Jun 20, 2012"
339# MySQL 8.0 Reference Manual :: 13.1.22 DROP DATABASE Syntax
340# https://dev.mysql.com/doc/en/drop-database.html
341sub delete_collection_tables {
342    my $self= shift (@_);
343    my $dbh = $self->{'db_handle'};
344   
345    # drop table <tablename>
346    my $table = $self->get_metadata_table_name();
347    if($self->table_exists($table)) {
348    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
349    }
350    $table = $self->get_fulltext_table_name();
351    if($self->table_exists($table)) {
352    $dbh->do("drop table $table") || warn("@@@ Couldn't delete $table");
353    }
354}
355
356# Don't call this: it will delete the meta and full text tables for ALL collections in $db_name (localsite by default)!
357# This method is just here for debugging (for testing creating a database when there is none)
358sub _delete_database {
359    my $self= shift (@_);
360    my ($db_name) = @_;
361    my $dbh = $self->{'db_handle'};
362    $db_name = $self->sanitize_name($db_name);
363   
364    print STDERR "!!! Deleting database $db_name\n" if($self->{'verbosity'});
365   
366    # "drop database dbname"
367    $dbh->do("drop database $db_name") || return 0;
368
369    return 1;
370}
371
372
373########################### DB STATEMENTS ###########################
374
375# USEFUL: https://metacpan.org/pod/DBI
376# "Many methods have an optional \%attr parameter which can be used to pass information to the driver implementing the method. Except where specifically documented, the \%attr parameter can only be used to pass driver specific hints. In general, you can ignore \%attr parameters or pass it as undef."
377
378
379# https://www.guru99.com/insert-into.html
380# and https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html
381#     for inserting multiple rows at once
382# https://www.perlmonks.org/bare/?node_id=316183
383# https://metacpan.org/pod/DBI#do
384# https://www.quora.com/What-is-the-difference-between-prepare-and-do-statements-in-Perl-while-we-make-a-connection-to-the-database-for-executing-the-query
385# https://docstore.mik.ua/orelly/linux/dbi/ch05_05.htm
386
387# https://metacpan.org/pod/DBI#performance
388# 'The q{...} style quoting used in this example avoids clashing with quotes that may be used in the SQL statement. Use the double-quote like qq{...} operator if you want to interpolate variables into the string. See "Quote and Quote-like Operators" in perlop for more details.'
389sub prepare_insert_metadata_row_stmthandle {
390    my $self = shift (@_);   
391    #my ($did, $sid, $metaname, $metavalue) = @_;
392    my $dbh = $self->{'db_handle'};
393   
394    my $tablename = $self->get_metadata_table_name();
395
396    #my $stmt = "INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES ('$did', '$sid', '$metaname', '$metavalue');"; # ?, ?, ?, ?
397
398    # using qq{} since we want $tablename placeholder to be filled in
399    # returns Statement Handle object!
400    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, metaname, metavalue) VALUES (?, ?, ?, ?)}) || warn("Could not prepare insert statement for metadata table\n");
401
402    print STDERR "@@@@ Prepared meta insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2);
403   
404    return $sth;
405}
406
407sub prepare_insert_fulltxt_row_stmthandle {
408    my $self = shift (@_);
409    #my ($did, $sid, $fulltext) = @_;
410    my $dbh = $self->{'db_handle'};
411   
412    my $tablename = $self->get_fulltext_table_name();
413
414    #my $stmt = "INSERT INTO $tablename (did, sid, fulltxt) VALUES ('$did', '$sid', '$fulltext');"; ?, ?, ?
415
416    # using qq{} since we want $tablename placeholder to be filled in
417    # returns Statement Handle object!
418    my $sth = $dbh->prepare(qq{INSERT INTO $tablename (did, sid, fulltxt) VALUES (?, ?, ?)}) || warn("Could not prepare insert statement for fulltxt table\n");
419   
420    print STDERR "@@@@ Prepared fulltext insert statement: ".$sth->{'Statement'}."\n" if($self->{'verbosity'} > 2);
421   
422    return $sth;
423}
424
425
426## The 2 select statements used by GreenstoneSQLPlugin
427
428# Returns the statement handle that prepared and executed
429# a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement.
430# Caller can call fetchrow_array() on returned statement handle, $sth
431# Have to use prepare() and execute() instead of do() since do() does
432# not allow for fetching result set thereafter:
433# do(): "This method  is typically most useful for non-SELECT statements that either cannot be prepared in advance (due to a limitation of the driver) or do not need to be executed repeatedly. It should not be used for SELECT statements because it does not return a statement handle (so you can't fetch any data)." https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm#do
434sub select_from_metatable_matching_docid {
435    my $self= shift (@_);
436    my ($oid) = @_;
437   
438    my $dbh = $self->{'db_handle'};
439    my $meta_table = $self->get_metadata_table_name();
440   
441    my $sth = $dbh->prepare(qq{SELECT * FROM $meta_table WHERE did = ?});
442    $sth->execute( $oid ); # will print msg on fail
443   
444    return $sth; # caller can call fetchrow_array() on returned statement handle, sth
445}
446
447# Returns the statement handle that prepared and executed
448# a "SELECT * FROM <COLL>_metadata WHERE did = $oid" SQL statement.
449# Caller can call fetchrow_array() on returned statement handle, $sth
450sub select_from_texttable_matching_docid {
451    my $self= shift (@_);
452    my ($oid) = @_;
453   
454    my $dbh = $self->{'db_handle'};
455    my $fulltxt_table = $self->get_fulltext_table_name();
456   
457    my $sth = $dbh->prepare(qq{SELECT * FROM $fulltxt_table WHERE did = ?});
458    $sth->execute( $oid ); # will print msg on fail
459   
460    return $sth; # caller can call fetchrow_array() on returned statement handle, sth
461}
462
463# delete all records in metatable with specified docid
464# https://www.tutorialspoint.com/mysql/mysql-delete-query.htm
465# DELETE FROM table_name [WHERE Clause]
466# see example under 'do' at https://metacpan.org/pod/release/TIMB/DBI-1.634_50/DBI.pm
467sub delete_recs_from_metatable_with_docid {
468    my $self= shift (@_);
469    my ($oid) = @_;
470   
471    my $dbh = $self->{'db_handle'};
472    my $meta_table = $self->get_metadata_table_name();
473   
474    #my $rows_deleted =
475    $dbh->do(qq{DELETE FROM $meta_table WHERE did = ?}, undef, $oid)
476    or warn $dbh->errstr;
477}
478
479# delete all records in metatable with specified docid
480sub delete_recs_from_texttable_with_docid {
481    my $self= shift (@_);
482    my ($oid) = @_;
483   
484    my $dbh = $self->{'db_handle'};   
485    my $fulltxt_table = $self->get_fulltext_table_name();
486   
487    $dbh->do(qq{DELETE FROM $fulltxt_table WHERE did = ?}, undef, $oid)
488    or warn $dbh->errstr;
489}
490
491# Can call this after connection succeeded to get the database handle, dbh,
492# if any specific DB operation (SQL statement, create/delete)
493# needs to be executed that is not already provided as a method of this class.
494sub get_db_handle {
495    my $self= shift (@_);
496    return $self->{'db_handle'};
497}
498
499################ HELPER METHODS ##############
500
501# More basic helper methods
502sub get_metadata_table_name {
503    my $self= shift (@_);
504    my $table_name = $self->{'tablename_prefix'} . "_metadata";
505    return $table_name;
506}
507
508# FULLTEXT is a reserved keyword in (My)SQL. https://dev.mysql.com/doc/refman/5.5/en/keywords.html
509# So we can't name a table or any of its columns "fulltext". We use "fulltxt" instead.
510sub get_fulltext_table_name {
511    my $self= shift (@_);
512    my $table_name = $self->{'tablename_prefix'} . "_fulltxt";
513    return $table_name;
514}
515
516# Attempt to make sure the name parameter (for db or table name) is acceptable syntax
517# for the db in question, e.g. for mysql. For example, (My)SQL doesn't like tables or
518# databases with '-' (hyphens) in their names
519sub sanitize_name {
520    my $self= shift (@_);
521    my ($name) = @_;
522    $name =~ s/-/_/g;
523    return $name;
524}
525
526
527# I can get my version of table_exists to work, but it's not so ideal
528# Interesting that MySQL has non-standard command to CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS,
529# see https://www.perlmonks.org/bare/?node=DBI%20Recipes
530#    The page further has a table_exists function that could work with proper comparison
531# TODO Q: Couldn't get the first solution at https://www.perlmonks.org/bare/?node_id=500050 to work though
532sub table_exists {
533    my $self = shift (@_);
534    my $dbh = $self->{'db_handle'};
535    my ($table_name) = @_;
536
537    my @table_list = $dbh->tables;
538    #my $tables_str = @table_list[0];
539    foreach my $table (@table_list) {
540    return 1 if ($table =~ m/$table_name/);
541    }
542    return 0;
543}
544
5451;
Note: See TracBrowser for help on using the browser.