com web/doc: Created indexes for database: include/lib_revche ck.inc.php scripts/rev.php www/revcheck.php
| From: | sobak@php.net | Date: | Sun, 30 Mar 2014 06:14:05 +0000 |
| Subject: | com web/doc: Created indexes for database: include/lib_revche ck.inc.php scripts/rev.php www/revcheck.php | ||
| Groups: | php.doc.web | ||
| Request: | Send a blank email to doc-web+get-2352@lists.php.net to get a copy of this message | ||
Commit: 4208d27d37b4279c622ed29184dbfa7ecede571f
Author: Sobak <msobaczewski@gmail.com> Sun, 30 Mar 2014 08:14:05 +0200
Parents: f01c461f5f7df054483e4114aa38baf72ecf61ce
Branches: master
Link: http://git.php.net/?p=web/doc.git;a=commitdiff;h=4208d27d37b4279c622ed29184dbfa7ecede571f
Log:
Created indexes for database
Indexes projected by bjori (Hannes Magnusson). This change fixes performance problems.
Changed paths:
M include/lib_revcheck.inc.php
M scripts/rev.php
M www/revcheck.php
Diff:
diff --git a/include/lib_revcheck.inc.php b/include/lib_revcheck.inc.php
index 4b744b4..a34c5ad 100644
--- a/include/lib_revcheck.inc.php
+++ b/include/lib_revcheck.inc.php
@@ -25,11 +25,10 @@ define("ALERT_SIZE", 3); // translation is 3 or more kB smaller
than the en o
define("ALERT_DATE", -30); // translation is 30 or more days older than the en one
// Return an array of directory containing outdated files
-function get_dirs($idx, $lang)
-{
+function get_dirs($idx, $lang) {
$sql = 'SELECT
distinct b.name AS name,
- a.dir as dir
+ a.dir AS dir
FROM
files a,
dirs b
@@ -161,7 +160,7 @@ function revcheck_available_languages($idx)
return FALSE;
}
- $sql = 'SELECT distinct lang FROM description';
+ $sql = 'SELECT lang FROM description';
$result = @$idx->query($sql);;
if (!$result) {
diff --git a/scripts/rev.php b/scripts/rev.php
index ae17c0b..611c969 100644
--- a/scripts/rev.php
+++ b/scripts/rev.php
@@ -48,13 +48,11 @@ if (count($LANGS) == 0) {
$SQL_BUFF = "INSERT INTO dirs (id, name) VALUES (1, '/');\n";
-$CREATE =<<<SQL
+$CREATE = <<<SQL
CREATE TABLE description (
lang TEXT,
intro TEXT,
- date TEXT,
- charset TEXT,
UNIQUE (lang)
);
@@ -80,23 +78,29 @@ CREATE TABLE dirs (
UNIQUE (name)
);
+CREATE INDEX dirs_1 ON dirs (id);
+
CREATE TABLE files (
- lang TEXT,
- dir TEXT,
- name TEXT,
- revision TEXT,
- size TEXT,
- mdate TEXT,
- maintainer TEXT,
- status TEXT,
- UNIQUE(lang, dir, name)
+ lang TEXT,
+ dir TEXT,
+ name TEXT,
+ revision TEXT,
+ size TEXT,
+ mdate TEXT,
+ maintainer TEXT,
+ status TEXT,
+ UNIQUE(lang, dir, name)
);
+CREATE INDEX files_1 ON files (dir, name);
+CREATE INDEX files_2 ON files (lang, revision, size, dir);
+CREATE INDEX files_3 ON files (lang, size, mdate, revision, size, dir);
+
CREATE TABLE old_files (
- lang TEXT,
- dir TEXT,
- file TEXT,
- size INT
+ lang TEXT,
+ dir TEXT,
+ file TEXT,
+ size INT
);
SQL;
@@ -135,11 +139,11 @@ function parse_translation($lang)
// Get intro text
if (preg_match("!<intro>(.+)</intro>!s", $txml, $match)) {
- $intro = @iconv($charset, 'UTF-8//IGNORE', trim($match[1]));
+ $intro = SQLite3::escapeString(@iconv($charset, 'UTF-8//IGNORE',
trim($match[1])));
}
}
- $SQL_BUFF .= "INSERT INTO description VALUES ('$lang', '" .
SQLite3::escapeString($intro) . "', DATE(), '$charset');\n";
+ $SQL_BUFF .= "INSERT INTO description VALUES ('$lang',
'$intro');\n";
if (isset($txml)) {
// Find all persons matching the pattern
@@ -349,15 +353,12 @@ function check_old_files($dir = '', $lang) {
if (sizeof($entriesFiles) > 0 ) {
foreach($entriesFiles as $file) {
-
$path_en = $DOCS . 'en/' . $dir . '/' . $file;
$path = $DOCS . $lang . $dir . '/' . $file;
if( !@is_file($path_en) ) {
-
$size = intval(filesize($path) / 1024);
$SQL_BUFF .= "INSERT INTO old_files VALUES ('$lang',
'$dir', '$file', '$size');\n";
-
}
}
}
@@ -375,8 +376,7 @@ function check_old_files($dir = '', $lang) {
closedir($dh);
}
-function get_tags($file)
-{
+function get_tags($file) {
// Read the first 500 chars. The comment should be at
// the begining of the file
$fp = @fopen($file, "r") or die("Unable to read $file.");
@@ -386,7 +386,6 @@ function get_tags($file)
// No match before the preg
$match = array ();
-
// Check for the translations "revision tag"
if
(preg_match("/<!--\s*EN-Revision:\s*(\d+)\s*Maintainer:\s*(\\S*)\s*Status:\s*(.+)\s*-->/U",
$line, $match)) {
@@ -407,8 +406,7 @@ function get_tags($file)
} // get_tags() function end
-function get_original_rev($file)
-{
+function get_original_rev($file) {
// Read the first 500 chars. The comment should be at
// the begining of the file
$fp = @fopen($file, "r") or die ("Unable to read $file.");
@@ -444,7 +442,6 @@ if (is_file($tmp_db)) {
}
}
-
// 2 - Create the new database
try {
$db = new SQLite3($tmp_db);
diff --git a/www/revcheck.php b/www/revcheck.php
index 2fa104f..d899c46 100644
--- a/www/revcheck.php
+++ b/www/revcheck.php
@@ -26,7 +26,7 @@ $DBLANG = SQLITE_DIR . 'rev.php.sqlite';
// Check if db connection can be established and if revcheck for requested lang exists
if ($dbhandle = new SQLite3($DBLANG)) {
- $check_lang_tmp = $dbhandle->query("SELECT COUNT(charset) AS count FROM description
WHERE lang = '$lang'");
+ $check_lang_tmp = $dbhandle->query("SELECT COUNT(lang) AS count FROM description WHERE
lang = '$lang'");
$check_lang = $check_lang_tmp->fetchArray();
if ($lang != 'en' && $check_lang['count'] < 0) {
site_header();