· 10 years ago · Dec 06, 2015, 02:33 PM
1<?php
2//
3// Project: phpLiteAdmin (http://www.phpliteadmin.org/)
4// Version: 1.9.7-dev
5// Summary: PHP-based admin tool to manage SQLite2 and SQLite3 databases on the web
6// Last updated: 2015-09-11
7// Developers:
8// Dane Iracleous (daneiracleous@gmail.com)
9// Ian Aldrighetti (ian.aldrighetti@gmail.com)
10// George Flanagin & Digital Gaslight, Inc (george@digitalgaslight.com)
11// Christopher Kramer (crazy4chrissi@gmail.com, http://en.christosoft.de)
12// Ayman Teryaki (http://havalite.com)
13// Dreadnaut (dreadnaut@gmail.com, http://dreadnaut.altervista.org)
14//
15//
16// Copyright (C) 2015, phpLiteAdmin
17//
18// This program is free software: you can redistribute it and/or modify
19// it under the terms of the GNU General Public License as published by
20// the Free Software Foundation, either version 3 of the License, or
21// (at your option) any later version.
22//
23// This program is distributed in the hope that it will be useful,
24// but WITHOUT ANY WARRANTY; without even the implied warranty of
25// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
26// GNU General Public License for more details.
27//
28// You should have received a copy of the GNU General Public License
29// along with this program. If not, see <http://www.gnu.org/licenses/>.
30//
31// ////////////////////////////////////////////////////////////////////////
32//
33// Please report any bugs you may encounter to our issue tracker here:
34// https://bitbucket.org/phpliteadmin/public/issues?status=new&status=open
35
36//
37// This is sample configuration file
38//
39// You can configure phpliteadmin in one of 2 ways:
40// 1. Rename phpliteadmin.config.sample.php to phpliteadmin.config.php and change parameters in there.
41// You can set only your custom settings in phpliteadmin.config.php. All other settings will be set to defaults.
42// 2. Change parameters directly in main phpliteadmin.php file
43//
44// Please see https://bitbucket.org/phpliteadmin/public/wiki/Configuration for more details
45
46//password to gain access
47$password = 'admin';
48
49//directory relative to this file to search for databases (if false, manually list databases in the $databases variable)
50$directory = '.';
51
52//whether or not to scan the subdirectories of the above directory infinitely deep
53$subdirectories = false;
54
55//if the above $directory variable is set to false, you must specify the databases manually in an array as the next variable
56//if any of the databases do not exist as they are referenced by their path, they will be created automatically
57$databases = array(
58 array(
59 'path'=> 'database1.sqlite',
60 'name'=> 'Database 1'
61 ),
62 array(
63 'path'=> 'database2.sqlite',
64 'name'=> 'Database 2'
65 ),
66);
67
68
69/* ---- Interface settings ---- */
70
71// Theme! If you want to change theme, save the CSS file in same folder of phpliteadmin or in folder "themes"
72$theme = 'phpliteadmin.css';
73
74// the default language! If you want to change it, save the language file in same folder of phpliteadmin or in folder "languages"
75// More about localizations (downloads, how to translate etc.): https://bitbucket.org/phpliteadmin/public/wiki/Localization
76$language = 'en';
77
78// set default number of rows. You need to relog after changing the number
79$rowsNum = 30;
80
81// reduce string characters by a number bigger than 10
82$charsNum = 300;
83
84// maximum number of SQL queries to save in the history
85$maxSavedQueries = 10;
86
87/* ---- Custom functions ---- */
88
89//a list of custom functions that can be applied to columns in the databases
90//make sure to define every function below if it is not a core PHP function
91$custom_functions = array(
92 'md5', 'sha1', 'time', 'strtotime',
93 // add the names of your custom functions to this array
94 /* 'leet_text', */
95);
96
97// define your custom functions here
98/*
99function leet_text($value)
100{
101 return strtr($value, 'eaAsSOl', '344zZ01');
102}
103*/
104
105
106/* ---- Advanced options ---- */
107
108//changing the following variable allows multiple phpLiteAdmin installs to work under the same domain.
109$cookie_name = 'pla3412';
110
111//whether or not to put the app in debug mode where errors are outputted
112$debug = false;
113
114// the user is allowed to create databases with only these extensions
115$allowed_extensions = array('db','db3','sqlite','sqlite3');
116
117
118// English language-texts.
119// Read our wiki on how to translate: https://bitbucket.org/phpliteadmin/public/wiki/Localization
120$lang = array(
121 "direction" => "LTR",
122 "date_format" => 'g:ia \o\n F j, Y (T)', // see http://php.net/manual/en/function.date.php for what the letters stand for
123 "ver" => "version",
124 "for" => "for",
125 "to" => "to",
126 "go" => "Go",
127 "yes" => "Yes",
128 "no" => "No",
129 "sql" => "SQL",
130 "csv" => "CSV",
131 "csv_tbl" => "Table that CSV pertains to",
132 "srch" => "Search",
133 "srch_again" => "Do Another Search",
134 "login" => "Log In",
135 "logout" => "Logout",
136 "view" => "View",
137 "confirm" => "Confirm",
138 "cancel" => "Cancel",
139 "save_as" => "Save As",
140 "options" => "Options",
141 "no_opt" => "No options",
142 "help" => "Help",
143 "installed" => "installed",
144 "not_installed" => "not installed",
145 "done" => "done",
146 "insert" => "Insert",
147 "export" => "Export",
148 "import" => "Import",
149 "rename" => "Rename",
150 "empty" => "Empty",
151 "drop" => "Drop",
152 "tbl" => "Table",
153 "chart" => "Chart",
154 "err" => "ERROR",
155 "act" => "Action",
156 "rec" => "Records",
157 "col" => "Column",
158 "cols" => "Columns",
159 "rows" => "row(s)",
160 "edit" => "Edit",
161 "del" => "Delete",
162 "add" => "Add",
163 "backup" => "Backup database file",
164 "before" => "Before",
165 "after" => "After",
166 "passwd" => "Password",
167 "passwd_incorrect" => "Incorrect password.",
168 "chk_ext" => "Checking supported SQLite PHP extensions",
169 "autoincrement" => "Autoincrement",
170 "not_null" => "Not NULL",
171 "attention" => "Attention",
172 "none" => "None",
173 "as_defined" => "As defined",
174 "expression" => "Expression",
175
176 "sqlite_ext" => "SQLite extension",
177 "sqlite_ext_support" => "It appears that none of the supported SQLite library extensions are available in your installation of PHP. You may not use %s until you install at least one of them.",
178 "sqlite_v" => "SQLite version",
179 "sqlite_v_error" => "It appears that your database is of SQLite version %s but your installation of PHP does not contain the necessary extensions to handle this version. To fix the problem, either delete the database and allow %s to create it automatically or recreate it manually as SQLite version %s.",
180 "report_issue" => "The problem cannot be diagnosed properly. Please file an issue report at",
181 "sqlite_limit" => "Due to the limitations of SQLite, only the field name and data type can be modified.",
182
183 "php_v" => "PHP version",
184 "new_version" => "There is a new version!",
185
186 "db_dump" => "database dump",
187 "db_f" => "database file",
188 "db_ch" => "Change Database",
189 "db_event" => "Database Event",
190 "db_name" => "Database name",
191 "db_rename" => "Rename Database",
192 "db_renamed" => "Database '%s' has been renamed to",
193 "db_del" => "Delete Database",
194 "db_path" => "Path to database",
195 "db_size" => "Size of database",
196 "db_mod" => "Database last modified",
197 "db_create" => "Create New Database",
198 "db_vac" => "The database, '%s', has been VACUUMed.",
199 "db_not_writeable" => "The database, '%s', does not exist and cannot be created because the containing directory, '%s', is not writable. The application is unusable until you make it writable.",
200 "db_setup" => "There was a problem setting up your database, %s. An attempt will be made to find out what's going on so you can fix the problem more easily",
201 "db_exists" => "A database, other file or directory of the name '%s' already exists.",
202
203 "exported" => "Exported",
204 "struct" => "Structure",
205 "struct_for" => "structure for",
206 "on_tbl" => "on table",
207 "data_dump" => "Data dump for",
208 "backup_hint" => "Hint: To backup your database, the easiest way is to %s.",
209 "backup_hint_linktext" => "download the database-file",
210 "total_rows" => "a total of %s rows",
211 "total" => "Total",
212 "not_dir" => "The directory you specified to scan for databases does not exist or is not a directory.",
213 "bad_php_directive" => "It appears that the PHP directive, 'register_globals' is enabled. This is bad. You need to disable it before continuing.",
214 "page_gen" => "Page generated in %s seconds.",
215 "powered" => "Powered by",
216 "free_software" => "This is free software.",
217 "please_donate" => "Please donate.",
218 "remember" => "Remember me",
219 "no_db" => "Welcome to %s. It appears that you have selected to scan a directory for databases to manage. However, %s could not find any valid SQLite databases. You may use the form below to create your first database.",
220 "no_db2" => "The directory you specified does not contain any existing databases to manage, and the directory is not writable. This means you can't create any new databases using %s. Either make the directory writable or manually upload databases to the directory.",
221
222 "create" => "Create",
223 "created" => "has been created",
224 "create_tbl" => "Create new table",
225 "create_tbl_db" => "Create new table on database",
226 "create_trigger" => "Creating new trigger on table",
227 "create_index" => "Creating new index on table",
228 "create_index1" => "Create Index",
229 "create_view" => "Create new view on database",
230
231 "trigger" => "Trigger",
232 "triggers" => "Triggers",
233 "trigger_name" => "Trigger name",
234 "trigger_act" => "Trigger Action",
235 "trigger_step" => "Trigger Steps (semicolon terminated)",
236 "when_exp" => "WHEN expression (type expression without 'WHEN')",
237 "index" => "Index",
238 "indexes" => "Indexes",
239 "index_name" => "Index name",
240 "name" => "Name",
241 "unique" => "Unique",
242 "seq_no" => "Seq. No.",
243 "emptied" => "has been emptied",
244 "dropped" => "has been dropped",
245 "renamed" => "has been renamed to",
246 "altered" => "has been altered successfully",
247 "inserted" => "inserted",
248 "deleted" => "deleted",
249 "affected" => "affected",
250 "blank_index" => "Index name must not be blank.",
251 "one_index" => "You must specify at least one index column.",
252 "docu" => "Documentation",
253 "license" => "License",
254 "proj_site" => "Project Site",
255 "bug_report" => "This may be a bug that needs to be reported at",
256 "return" => "Return",
257 "browse" => "Browse",
258 "fld" => "Field",
259 "fld_num" => "Number of Fields",
260 "fields" => "Fields",
261 "type" => "Type",
262 "operator" => "Operator",
263 "val" => "Value",
264 "update" => "Update",
265 "comments" => "Comments",
266
267 "specify_fields" => "You must specify the number of table fields.",
268 "specify_tbl" => "You must specify a table name.",
269 "specify_col" => "You must specify a column.",
270
271 "tbl_exists" => "Table of the same name already exists.",
272 "show" => "Show",
273 "show_rows" => "Showing %s row(s). ",
274 "showing" => "Showing",
275 "showing_rows" => "Showing rows",
276 "query_time" => "(Query took %s sec)",
277 "syntax_err" => "There is a problem with the syntax of your query (Query was not executed)",
278 "run_sql" => "Run SQL query/queries on database '%s'",
279 "recent_queries" => "Recent Queries",
280 "full_texts" => "Show full texts",
281 "no_full_texts" => "Shorten long texts",
282
283 "ques_empty" => "Are you sure you want to empty the table '%s'?",
284 "ques_drop" => "Are you sure you want to drop the table '%s'?",
285 "ques_drop_view" => "Are you sure you want to drop the view '%s'?",
286 "ques_del_rows" => "Are you sure you want to delete row(s) %s from table '%s'?",
287 "ques_del_db" => "Are you sure you want to delete the database '%s'?",
288 "ques_column_delete" => "Are you sure you want to delete column(s) %s from table '%s'?",
289 "ques_del_index" => "Are you sure you want to delete index '%s'?",
290 "ques_del_trigger" => "Are you sure you want to delete trigger '%s'?",
291 "ques_primarykey_add" => "Are you sure you want to add a primary key for the column(s) %s in table '%s'?",
292
293 "export_struct" => "Export with structure",
294 "export_data" => "Export with data",
295 "add_drop" => "Add DROP TABLE",
296 "add_transact" => "Add TRANSACTION",
297 "fld_terminated" => "Fields terminated by",
298 "fld_enclosed" => "Fields enclosed by",
299 "fld_escaped" => "Fields escaped by",
300 "fld_names" => "Field names in first row",
301 "rep_null" => "Replace NULL by",
302 "rem_crlf" => "Remove CRLF characters within fields",
303 "put_fld" => "Put field names in first row",
304 "null_represent" => "NULL represented by",
305 "import_suc" => "Import was successful.",
306 "import_into" => "Import into",
307 "import_f" => "File to import",
308 "rename_tbl" => "Rename table '%s' to",
309
310 "rows_records" => "row(s) starting from record # ",
311 "rows_aff" => "row(s) affected. ",
312
313 "as_a" => "as a",
314 "readonly_tbl" => "'%s' is a view, which means it is a SELECT statement treated as a read-only table. You may not edit or insert records.",
315 "chk_all" => "Check All",
316 "unchk_all" => "Uncheck All",
317 "with_sel" => "With Selected",
318
319 "no_tbl" => "No table in database.",
320 "no_chart" => "If you can read this, it means the chart could not be generated. The data you are trying to view may not be appropriate for a chart.",
321 "no_rows" => "There are no rows in the table for the range you selected.",
322 "no_sel" => "You did not select anything.",
323
324 "chart_type" => "Chart Type",
325 "chart_bar" => "Bar Chart",
326 "chart_pie" => "Pie Chart",
327 "chart_line" => "Line Chart",
328 "lbl" => "Labels",
329 "empty_tbl" => "This table is empty.",
330 "click" => "Click here",
331 "insert_rows" => "to insert rows.",
332 "restart_insert" => "Restart insertion with ",
333 "ignore" => "Ignore",
334 "func" => "Function",
335 "new_insert" => "Insert As New Row",
336 "save_ch" => "Save Changes",
337 "def_val" => "Default Value",
338 "prim_key" => "Primary Key",
339 "tbl_end" => "field(s) at end of table",
340 "query_used_table" => "Query used to create this table",
341 "query_used_view" => "Query used to create this view",
342 "create_index2" => "Create an index on",
343 "create_trigger2" => "Create a new trigger",
344 "new_fld" => "Adding new field(s) to table '%s'",
345 "add_flds" => "Add Fields",
346 "edit_col" => "Editing column '%s'",
347 "vac" => "Vacuum",
348 "vac_desc" => "Large databases sometimes need to be VACUUMed to reduce their footprint on the server. Click the button below to VACUUM the database '%s'.",
349 "event" => "Event",
350 "each_row" => "For Each Row",
351 "define_index" => "Define index properties",
352 "dup_val" => "Duplicate values",
353 "allow" => "Allowed",
354 "not_allow" => "Not Allowed",
355 "asc" => "Ascending",
356 "desc" => "Descending",
357 "warn0" => "You have been warned.",
358 "warn_passwd" => "You are using the default password, which can be dangerous. You can change it easily at the top of %s.",
359 "warn_dumbass" => "You didn't change the value dumbass ;-)",
360 "counting_skipped" => "Counting of records has been skipped for some tables because your database is comparably big and some tables don't have primary keys assigned to them so counting might be slow. Add a primary key to these tables or %sforce counting%s.",
361 "sel_state" => "Select Statement",
362 "delimit" => "Delimiter",
363 "back_top" => "Back to Top",
364 "choose_f" => "Choose File",
365 "instead" => "Instead of",
366 "define_in_col" => "Define index column(s)",
367
368 "delete_only_managed" => "You can only delete databases managed by this tool!",
369 "rename_only_managed" => "You can only rename databases managed by this tool!",
370 "db_moved_outside" => "You either tried to move the database into a directory where it cannot be managed anylonger, or the check if you did this failed because of missing rights.",
371 "extension_not_allowed" => "The extension you provided is not within the list of allowed extensions. Please use one of the following extensions",
372 "add_allowed_extension" => "You can add extensions to this list by adding your extension to \$allowed_extensions in the configuration.",
373 "directory_not_writable" => "The database-file itself is writable, but to write into it, the containing directory needs to be writable as well. This is because SQLite puts temporary files in there for locking.",
374 "tbl_inexistent" => "Table %s does not exist",
375
376 // errors that can happen when ALTER TABLE fails. You don't necessarily have to translate these.
377 "alter_failed" => "Altering of Table %s failed",
378 "alter_tbl_name_not_replacable" => "could not replace the table name with the temporary one",
379 "alter_no_def" => "no ALTER definition",
380 "alter_parse_failed" =>"failed to parse ALTER definition",
381 "alter_action_not_recognized" => "ALTER action could not be recognized",
382 "alter_no_add_col" => "no column to add detected in ALTER statement",
383 "alter_pattern_mismatch"=>"Pattern did not match on your original CREATE TABLE statement",
384 "alter_col_not_recognized" => "could not recognize new or old column name",
385 "alter_unknown_operation" => "Unknown ALTER operation!",
386
387 /* Help documentation */
388 "help_doc" => "Help Documentation",
389 "help1" => "SQLite Library Extensions",
390 "help1_x" => "%s uses PHP library extensions that allow interaction with SQLite databases. Currently, %s supports PDO, SQLite3, and SQLiteDatabase. Both PDO and SQLite3 deal with version 3 of SQLite, while SQLiteDatabase deals with version 2. So, if your PHP installation includes more than one SQLite library extension, PDO and SQLite3 will take precedence to make use of the better technology. However, if you have existing databases that are of version 2 of SQLite, %s will be forced to use SQLiteDatabase for only those databases. Not all databases need to be of the same version. During the database creation, however, the most advanced extension will be used.",
391 "help2" => "Creating a New Database",
392 "help2_x" => "When you create a new database, the name you entered will be appended with the appropriate file extension (.db, .db3, .sqlite, etc.) if you do not include it yourself. The database will be created in the directory you specified as the \$directory variable.",
393 "help3" => "Tables vs. Views",
394 "help3_x" => "On the main database page, there is a list of tables and views. Since views are read-only, certain operations will be disabled. These disabled operations will be apparent by their omission in the location where they should appear on the row for a view. If you want to change the data for a view, you need to drop that view and create a new view with the appropriate SELECT statement that queries other existing tables. For more information, see <a href='http://en.wikipedia.org/wiki/View_(database)' target='_blank'>http://en.wikipedia.org/wiki/View_(database)</a>",
395 "help4" => "Writing a Select Statement for a New View",
396 "help4_x" => "When you create a new view, you must write an SQL SELECT statement that it will use as its data. A view is simply a read-only table that can be accessed and queried like a regular table, except it cannot be modified through insertion, column editing, or row editing. It is only used for conveniently fetching data.",
397 "help5" => "Export Structure to SQL File",
398 "help5_x" => "During the process for exporting to an SQL file, you may choose to include the queries that create the table and columns.",
399 "help6" => "Export Data to SQL File",
400 "help6_x" => "During the process for exporting to an SQL file, you may choose to include the queries that populate the table(s) with the current records of the table(s).",
401 "help7" => "Add Drop Table to Exported SQL File",
402 "help7_x" => "During the process for exporting to an SQL file, you may choose to include queries to DROP the existing tables before adding them so that problems do not occur when trying to create tables that already exist.",
403 "help8" => "Add Transaction to Exported SQL File",
404 "help8_x" => "During the process for exporting to an SQL file, you may choose to wrap the queries around a TRANSACTION so that if an error occurs at any time during the importation process using the exported file, the database can be reverted to its previous state, preventing partially updated data from populating the database.",
405 "help9" => "Add Comments to Exported SQL File",
406 "help9_x" => "During the process for exporting to an SQL file, you may choose to include comments that explain each step of the process so that a human can better understand what is happening.",
407 "help10" => "Partial Indexes",
408 "help10_x" => "Partial indexes are indexes over a subset of the rows of a table specified by a WHERE clause. Note this requires at least SQLite 3.8.0 and database files with partial indexes won't be readable or writable by older versions. See the <a href='https://www.sqlite.org/partialindex.html' target='_blank'>SQLite documentation.</a>"
409
410);
411
412//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
413//there is no reason for the average user to edit anything below this comment
414//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
415
416//- Initialization
417
418// load optional configuration file
419$config_filename = './phpliteadmin.config.php';
420if (is_readable($config_filename)) {
421 include_once $config_filename;
422}
423
424//constants 1
425define("PROJECT", "phpLiteAdmin");
426define("VERSION", "1.9.7-dev");
427define("PAGE", basename(__FILE__));
428define("FORCETYPE", false); //force the extension that will be used (set to false in almost all circumstances except debugging)
429define("SYSTEMPASSWORD", $password); // Makes things easier.
430define('PROJECT_URL','http://www.phpliteadmin.org/');
431define('DONATE_URL','http://www.phpliteadmin.org/donate/');
432define('VERSION_CHECK_URL','https://www.phpliteadmin.org/current_version.php');
433define('PROJECT_BUGTRACKER_LINK','<a href="https://bitbucket.org/phpliteadmin/public/issues?status=new&status=open" target="_blank">https://bitbucket.org/phpliteadmin/public/issues?status=new&status=open</a>');
434define('PROJECT_INSTALL_LINK','<a href="https://bitbucket.org/phpliteadmin/public/wiki/Installation" target="_blank">https://bitbucket.org/phpliteadmin/public/wiki/Installation</a>');
435
436// Resource output (css and javascript files)
437// we get out of the main code as soon as possible, without inizializing the session
438if (isset($_GET['resource'])) {
439 Resources::output($_GET['resource']);
440 exit();
441}
442
443// don't mess with this - required for the login session
444ini_set('session.cookie_httponly', '1');
445session_start();
446
447if($debug==true)
448{
449 ini_set("display_errors", 1);
450 error_reporting(E_STRICT | E_ALL);
451} else
452{
453 @ini_set("display_errors", 0);
454}
455
456// start the timer to record page load time
457$pageTimer = new MicroTimer();
458
459// load language file
460if($language != 'en') {
461 $temp_lang=$lang;
462 if(is_file('languages/lang_'.$language.'.php'))
463 include('languages/lang_'.$language.'.php');
464 elseif(is_file('lang_'.$language.'.php'))
465 include('lang_'.$language.'.php');
466 $lang = array_merge($temp_lang, $lang);
467 unset($temp_lang);
468}
469// version-number added so after updating, old session-data is not used anylonger
470// cookies names cannot contain symbols, except underscores
471define("COOKIENAME", preg_replace('/[^a-zA-Z0-9_]/', '_', $cookie_name . '_' . VERSION) );
472
473// stripslashes if MAGIC QUOTES is turned on
474// This is only a workaround. Please better turn off magic quotes!
475// This code is from http://php.net/manual/en/security.magicquotes.disabling.php
476if (get_magic_quotes_gpc()) {
477 $process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
478 while (list($key, $val) = each($process)) {
479 foreach ($val as $k => $v) {
480 unset($process[$key][$k]);
481 if (is_array($v)) {
482 $process[$key][stripslashes($k)] = $v;
483 $process[] = &$process[$key][stripslashes($k)];
484 } else {
485 $process[$key][stripslashes($k)] = stripslashes($v);
486 }
487 }
488 }
489 unset($process);
490}
491
492
493//data types array
494$sqlite_datatypes = array("INTEGER", "REAL", "TEXT", "BLOB","NUMERIC","BOOLEAN","DATETIME");
495
496//available SQLite functions array (don't add anything here or there will be problems)
497$sqlite_functions = array("abs", "hex", "length", "lower", "ltrim", "random", "round", "rtrim", "trim", "typeof", "upper");
498
499//- Support functions
500
501//function that allows SQL delimiter to be ignored inside comments or strings
502function explode_sql($delimiter, $sql)
503{
504 $ign = array('"' => '"', "'" => "'", "/*" => "*/", "--" => "\n"); // Ignore sequences.
505 $out = array();
506 $last = 0;
507 $slen = strlen($sql);
508 $dlen = strlen($delimiter);
509 $i = 0;
510 while($i < $slen)
511 {
512 // Split on delimiter
513 if($slen - $i >= $dlen && substr($sql, $i, $dlen) == $delimiter)
514 {
515 array_push($out, substr($sql, $last, $i - $last));
516 $last = $i + $dlen;
517 $i += $dlen;
518 continue;
519 }
520 // Eat comments and string literals
521 foreach($ign as $start => $end)
522 {
523 $ilen = strlen($start);
524 if($slen - $i >= $ilen && substr($sql, $i, $ilen) == $start)
525 {
526 $i+=strlen($start);
527 $elen = strlen($end);
528 while($i < $slen)
529 {
530 if($slen - $i >= $elen && substr($sql, $i, $elen) == $end)
531 {
532 // SQL comment characters can be escaped by doubling the character. This recognizes and skips those.
533 if($start == $end && $slen - $i >= $elen*2 && substr($sql, $i, $elen*2) == $end.$end)
534 {
535 $i += $elen * 2;
536 continue;
537 }
538 else
539 {
540 $i += $elen;
541 continue 3;
542 }
543 }
544 $i++;
545 }
546 continue 2;
547 }
548 }
549 $i++;
550 }
551 if($last < $slen)
552 array_push($out, substr($sql, $last, $slen - $last));
553 return $out;
554}
555
556//function to scan entire directory tree and subdirectories
557function dir_tree($dir)
558{
559 $path = '';
560 $stack[] = $dir;
561 while($stack)
562 {
563 $thisdir = array_pop($stack);
564 if($dircont = scandir($thisdir))
565 {
566 $i=0;
567 while(isset($dircont[$i]))
568 {
569 if($dircont[$i] !== '.' && $dircont[$i] !== '..')
570 {
571 $current_file = $thisdir.DIRECTORY_SEPARATOR.$dircont[$i];
572 if(is_file($current_file))
573 {
574 $path[] = $thisdir.DIRECTORY_SEPARATOR.$dircont[$i];
575 }
576 elseif (is_dir($current_file))
577 {
578 $path[] = $thisdir.DIRECTORY_SEPARATOR.$dircont[$i];
579 $stack[] = $current_file;
580 }
581 }
582 $i++;
583 }
584 }
585 }
586 return $path;
587}
588
589//the function echo the help [?] links to the documentation
590function helpLink($name)
591{
592 global $lang;
593 return "<a href='?help=1' onclick='openHelp(\"".$name."\"); return false;' class='helpq' title='".$lang['help'].": ".$name."' target='_blank'><span>[?]</span></a>";
594}
595
596// function to encode value into HTML just like htmlentities, but with adjusted default settings
597function htmlencode($value, $flags=ENT_QUOTES, $encoding ="UTF-8")
598{
599 return htmlentities($value, $flags, $encoding);
600}
601
602// 22 August 2011: gkf added this function to support display of
603// default values in the form used to INSERT new data.
604function deQuoteSQL($s)
605{
606 return trim(trim($s), "'");
607}
608
609// reduce string chars
610function subString($str)
611{
612 global $charsNum;
613 if($charsNum > 10 && (!isset($_SESSION[COOKIENAME.'fulltexts']) || !$_SESSION[COOKIENAME.'fulltexts']) && strlen($str)>$charsNum)
614 {
615 $str = substr($str, 0, $charsNum).'...';
616 }
617 return $str;
618}
619
620// checks the (new) name of a database file
621function checkDbName($name)
622{
623 global $allowed_extensions;
624 $info = pathinfo($name);
625 if(isset($info['extension']) && !in_array($info['extension'], $allowed_extensions))
626 {
627 return false;
628 } else
629 {
630 return (!is_file($name) && !is_dir($name));
631 }
632
633}
634
635// check whether a path is a db managed by this tool
636// requires that $databases is already filled!
637// returns the key of the db if managed, false otherwise.
638function isManagedDB($path)
639{
640 global $databases;
641 foreach($databases as $db_key => $database)
642 {
643 if($path == $database['path'])
644 {
645 // a db we manage. Thats okay.
646 // return the key.
647 return $db_key;
648 }
649 }
650 // not a db we manage!
651 return false;
652}
653
654// from a typename of a colun, get the type of the column's affinty
655// see http://www.sqlite.org/datatype3.html section 2.1 for rules
656function get_type_affinity($type)
657{
658 if (preg_match("/INT/i", $type))
659 return "INTEGER";
660 else if (preg_match("/(?:CHAR|CLOB|TEXT)/i", $type))
661 return "TEXT";
662 else if (preg_match("/BLOB/i", $type) || $type=="")
663 return "NONE";
664 else if (preg_match("/(?:REAL|FLOA|DOUB)/i", $type))
665 return "REAL";
666 else
667 return "NUMERIC";
668}
669
670
671//- Check user authentication, login and logout
672$auth = new Authorization(); //create authorization object
673
674// check if user has attempted to log out
675if (isset($_POST['logout']))
676 $auth->revoke();
677// check if user has attempted to log in
678else if (isset($_POST['login']) && isset($_POST['password']))
679 $auth->attemptGrant($_POST['password'], isset($_POST['remember']));
680
681//- Actions on database files and bulk data
682if ($auth->isAuthorized())
683{
684
685 //- Create a new database
686 if(isset($_POST['new_dbname']))
687 {
688 if($_POST['new_dbname']=='')
689 {
690 // TODO: Display an error message (do NOT echo here. echo below in the html-body!)
691 }
692 else
693 {
694 $str = preg_replace('@[^\w-.]@','', $_POST['new_dbname']);
695 $dbname = $str;
696 $dbpath = $str;
697 if(checkDbName($dbname))
698 {
699 $tdata = array();
700 $tdata['name'] = $dbname;
701 $tdata['path'] = $directory.DIRECTORY_SEPARATOR.$dbpath;
702 $td = new Database($tdata);
703 $td->query("VACUUM");
704 } else
705 {
706 if(is_file($dbname) || is_dir($dbname)) $dbexists = true;
707 else $extension_not_allowed=true;
708 }
709 }
710 }
711
712 //- Scan a directory for databases
713 if($directory!==false)
714 {
715 if($directory[strlen($directory)-1]==DIRECTORY_SEPARATOR) //if user has a trailing slash in the directory, remove it
716 $directory = substr($directory, 0, strlen($directory)-1);
717
718 if(is_dir($directory)) //make sure the directory is valid
719 {
720 if($subdirectories===true)
721 $arr = dir_tree($directory);
722 else
723 $arr = scandir($directory);
724 $databases = array();
725 $j = 0;
726 for($i=0; $i<sizeof($arr); $i++) //iterate through all the files in the databases
727 {
728 if($subdirectories===false)
729 $arr[$i] = $directory.DIRECTORY_SEPARATOR.$arr[$i];
730
731 if(@!is_file($arr[$i])) continue;
732 $con = file_get_contents($arr[$i], NULL, NULL, 0, 60);
733 if(strpos($con, "** This file contains an SQLite 2.1 database **", 0)!==false || strpos($con, "SQLite format 3", 0)!==false)
734 {
735 $databases[$j]['path'] = $arr[$i];
736 if($subdirectories===false)
737 $databases[$j]['name'] = basename($arr[$i]);
738 else
739 $databases[$j]['name'] = $arr[$i];
740 $databases[$j]['writable'] = is_writable($databases[$j]['path']);
741 $databases[$j]['writable_dir'] = is_writable(dirname($databases[$j]['path']));
742 $databases[$j]['readable'] = is_readable($databases[$j]['path']);
743 $j++;
744 }
745 }
746 // 22 August 2011: gkf fixed bug #50.
747 sort($databases);
748 if(isset($tdata))
749 {
750 foreach($databases as $db_id => $database)
751 {
752 if($database['path'] == $tdata['path'])
753 {
754 $_SESSION[COOKIENAME.'currentDB'] = $database;
755 break;
756 }
757 }
758 }
759 }
760 else //the directory is not valid - display error and exit
761 {
762 echo "<div class='confirm' style='margin:20px;'>".$lang['not_dir']."</div>";
763 exit();
764 }
765 }
766 else
767 {
768 for($i=0; $i<sizeof($databases); $i++)
769 {
770 if(!file_exists($databases[$i]['path']))
771 {
772 // the file does not exist and will be created when clicked, if permissions allow to
773 $databases[$i]['writable'] = is_writable(dirname($databases[$i]['path']));
774 $databases[$i]['writable_dir'] = is_writable(dirname($databases[$i]['path']));
775 $databases[$i]['readable'] = is_writable(dirname($databases[$i]['path']));
776 }
777 else
778 {
779 $databases[$i]['writable'] = is_writable($databases[$i]['path']);
780 $databases[$i]['writable_dir'] = is_writable(dirname($databases[$i]['path']));
781 $databases[$i]['readable'] = is_readable($databases[$i]['path']);
782 }
783 }
784 sort($databases);
785 }
786 // we now have the $databases array set. Check whethet currentDB is a managed Db (is in this array)
787 if(isset($_SESSION[COOKIENAME.'currentDB']) && isManagedDB($_SESSION[COOKIENAME.'currentDB']['path']) === false)
788 unset($_SESSION[COOKIENAME.'currentDB']);
789
790 //- Delete an existing database
791 if(isset($_GET['database_delete']))
792 {
793 $dbpath = $_POST['database_delete'];
794 // check whether $dbpath really is a db we manage
795 $checkDB = isManagedDB($dbpath);
796 if($checkDB !== false)
797 {
798 unlink($dbpath);
799 unset($_SESSION[COOKIENAME.'currentDB']);
800 unset($databases[$checkDB]);
801 } else die($lang['err'].': '.$lang['delete_only_managed']);
802 }
803
804 //- Rename an existing database
805 if(isset($_GET['database_rename']))
806 {
807 $oldpath = $_POST['oldname'];
808 $newpath = $_POST['newname'];
809 $oldpath_parts = pathinfo($oldpath);
810 $newpath_parts = pathinfo($newpath);
811 // only rename?
812 $newpath = $oldpath_parts['dirname'].DIRECTORY_SEPARATOR.basename($_POST['newname']);
813 if($newpath != $_POST['newname'] && $subdirectories)
814 {
815 // it seems that the file should not only be renamed but additionally moved.
816 // we need to make sure it stays within $directory...
817 $new_realpath = realpath($newpath_parts['dirname']).DIRECTORY_SEPARATOR;
818 $directory_realpath = realpath($directory).DIRECTORY_SEPARATOR;
819 if(strpos($new_realpath, $directory_realpath)===0)
820 {
821 // its okay, the new directory is within $directory
822 $newpath = $_POST['newname'];
823 }
824 else die($lang['err'].': '.$lang['db_moved_outside']);
825 }
826
827 if(checkDbName($newpath))
828 {
829 $checkDB = isManagedDB($oldpath);
830 if($checkDB !==false )
831 {
832 rename($oldpath, $newpath);
833 $databases[$checkDB]['path'] = $newpath;
834 $databases[$checkDB]['name'] = basename($newpath);
835 $_SESSION[COOKIENAME.'currentDB'] = $databases[$checkDB];
836 $justrenamed = true;
837 }
838 else die($lang['err'].': '.$lang['rename_only_managed']);
839 }
840 else
841 {
842 if(is_file($newpath) || is_dir($newpath)) $dbexists = true;
843 else $extension_not_allowed = true;
844 }
845 }
846
847
848 //- Export (download a dump) an existing database
849 if(isset($_POST['export']))
850 {
851 $export_filename = str_replace(array("\r", "\n"), '',$_POST['filename']); // against http header injection (php < 5.1.2 only)
852 if($_POST['export_type']=="sql")
853 {
854 header('Content-Type: text/sql');
855 header('Content-Disposition: attachment; filename="'.$export_filename.'.'.$_POST['export_type'].'";');
856 if(isset($_POST['tables']))
857 $tables = $_POST['tables'];
858 else
859 {
860 $tables = array();
861 $tables[0] = $_POST['single_table'];
862 }
863 $drop = isset($_POST['drop']);
864 $structure = isset($_POST['structure']);
865 $data = isset($_POST['data']);
866 $transaction = isset($_POST['transaction']);
867 $comments = isset($_POST['comments']);
868 $db = new Database($_SESSION[COOKIENAME.'currentDB']);
869 echo $db->export_sql($tables, $drop, $structure, $data, $transaction, $comments);
870 }
871 else if($_POST['export_type']=="csv")
872 {
873 header("Content-type: application/csv");
874 header('Content-Disposition: attachment; filename="'.$export_filename.'.'.$_POST['export_type'].'";');
875 header("Pragma: no-cache");
876 header("Expires: 0");
877 if(isset($_POST['tables']))
878 $tables = $_POST['tables'];
879 else
880 {
881 $tables = array();
882 $tables[0] = $_POST['single_table'];
883 }
884 $field_terminate = $_POST['export_csv_fieldsterminated'];
885 $field_enclosed = $_POST['export_csv_fieldsenclosed'];
886 $field_escaped = $_POST['export_csv_fieldsescaped'];
887 $null = $_POST['export_csv_replacenull'];
888 $crlf = isset($_POST['export_csv_crlf']);
889 $fields_in_first_row = isset($_POST['export_csv_fieldnames']);
890 $db = new Database($_SESSION[COOKIENAME.'currentDB']);
891 echo $db->export_csv($tables, $field_terminate, $field_enclosed, $field_escaped, $null, $crlf, $fields_in_first_row);
892 }
893 exit();
894 }
895
896 //- Import a file into an existing database
897 if(isset($_POST['import']))
898 {
899 $db = new Database($_SESSION[COOKIENAME.'currentDB']);
900 $db->registerUserFunction($custom_functions);
901 if($_POST['import_type']=="sql")
902 {
903 $data = file_get_contents($_FILES["file"]["tmp_name"]);
904 $importSuccess = $db->import_sql($data);
905 }
906 else
907 {
908 $field_terminate = $_POST['import_csv_fieldsterminated'];
909 $field_enclosed = $_POST['import_csv_fieldsenclosed'];
910 $field_escaped = $_POST['import_csv_fieldsescaped'];
911 $null = $_POST['import_csv_replacenull'];
912 $fields_in_first_row = isset($_POST['import_csv_fieldnames']);
913 $importSuccess = $db->import_csv($_FILES["file"]["tmp_name"], $_POST['single_table'], $field_terminate, $field_enclosed, $field_escaped, $null, $fields_in_first_row);
914 }
915 }
916 //- Download (backup) a database file (as SQLite file, not as dump)
917 if(isset($_GET['download']) && isManagedDB($_GET['download'])!==false)
918 {
919 header("Content-type: application/octet-stream");
920 header('Content-Disposition: attachment; filename="'.basename($_GET['download']).'";');
921 header("Pragma: no-cache");
922 header("Expires: 0");
923 readfile($_GET['download']);
924 exit;
925 }
926}
927
928//- HTML: output starts here
929header('Content-Type: text/html; charset=utf-8');
930?>
931<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
932<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
933<head>
934<!-- Copyright <?php echo date("Y").' '.PROJECT.' ('.PROJECT_URL.')'; ?> -->
935<meta http-equiv='Content-Type' content='text/html; charset=UTF-8' />
936<link rel="shortcut icon" href="?resource=favicon" />
937<title><?php echo PROJECT ?></title>
938
939<?php
940//- HTML: css/theme include
941if(isset($_GET['theme'])) $theme = basename($_GET['theme']);
942
943// allow themes to be dropped in subfolder "themes"
944if(is_file('themes/'.$theme)) $theme = 'themes/'.$theme;
945
946if (file_exists($theme))
947 // an external stylesheet exists - import it
948 echo "<link href='{$theme}' rel='stylesheet' type='text/css' />", PHP_EOL;
949else
950 // only use the default stylesheet if an external one does not exist
951 echo "<link href='?resource=css' rel='stylesheet' type='text/css' />", PHP_EOL;
952
953// HTML: output help text, then exit
954if(isset($_GET['help']))
955{
956 //help section array
957 $help = array
958 (
959 $lang['help1'] => sprintf($lang['help1_x'], PROJECT, PROJECT, PROJECT), $lang['help2'] => $lang['help2_x'], $lang['help3'] => $lang['help3_x'],
960 $lang['help4'] => $lang['help4_x'], $lang['help5'] => $lang['help5_x'], $lang['help6'] => $lang['help6_x'],
961 $lang['help7'] => $lang['help7_x'], $lang['help8'] => $lang['help8_x'], $lang['help9'] => $lang['help9_x'], $lang['help10'] => $lang['help10_x']
962 );
963 ?>
964 </head>
965 <body style="direction:<?php echo $lang['direction']; ?>;">
966 <div id='help_container'>
967 <?php
968 echo "<div class='help_list'>";
969 echo "<span style='font-size:18px;'>".PROJECT." v".VERSION." ".$lang['help_doc']."</span><br/><br/>";
970 foreach((array)$help as $key => $val)
971 {
972 echo "<a href='#".$key."'>".$key."</a><br/>";
973 }
974 echo "</div>";
975 echo "<br/><br/>";
976 foreach((array)$help as $key => $val)
977 {
978 echo "<div class='help_outer'>";
979 echo "<a class='headd' name='".$key."'>".$key."</a>";
980 echo "<div class='help_inner'>";
981 echo $val;
982 echo "</div>";
983 echo "<a class='help_top' href='#top'>".$lang['back_top']."</a>";
984 echo "</div>";
985 }
986 ?>
987 </div>
988 </body>
989 </html>
990 <?php
991 exit();
992}
993
994//- Javascript include
995?>
996<!-- JavaScript Support -->
997<script type='text/javascript' src='?resource=javascript'></script>
998</head>
999<body style="direction:<?php echo $lang['direction']; ?>;">
1000<?php
1001if(ini_get("register_globals") == "on" || ini_get("register_globals")=="1") //check whether register_globals is turned on - if it is, we need to not continue
1002{
1003 echo "<div class='confirm' style='margin:20px;'>".$lang['bad_php_directive']."</div>";
1004 echo "</body></html>";
1005 exit();
1006}
1007
1008//- HTML: login screen if not authorized, exit
1009if(!$auth->isAuthorized())
1010{
1011 echo "<div id='loginBox'>";
1012 echo "<h1><span id='logo'>".PROJECT."</span> <span id='version'>v".VERSION."</span></h1>";
1013 echo "<div style='padding:15px; text-align:center;'>";
1014 if ($auth->isFailedLogin())
1015 echo "<span class='warning'>".$lang['passwd_incorrect']."</span><br/><br/>";
1016 echo "<form action='".PAGE."' method='post'>";
1017 echo $lang['passwd'].": <input type='password' name='password'/><br/>";
1018 echo "<label><input type='checkbox' name='remember' value='yes' checked='checked'/> ".$lang['remember']."</label><br/><br/>";
1019 echo "<input type='submit' value='".$lang['login']."' class='btn'/>";
1020 echo "<input type='hidden' name='login' value='true' />";
1021 echo "</form>";
1022 echo "</div>";
1023 echo "</div>";
1024 echo "<br/>";
1025 echo "<div style='text-align:center;'>";
1026 echo "<span style='font-size:11px;'>".$lang['powered']." <a href='".PROJECT_URL."' target='_blank' style='font-size:11px;'>".PROJECT."</a> | ";
1027 printf($lang['page_gen'], $pageTimer);
1028 echo "</span></div>";
1029 echo "</body></html>";
1030 exit();
1031}
1032
1033//- User is authorized, display the main application
1034
1035//- Select database (from session or first available)
1036if(!isset($_SESSION[COOKIENAME.'currentDB']) && count($databases)>0)
1037{
1038 //set the current database to the first existing one in the array (default)
1039 $_SESSION[COOKIENAME.'currentDB'] = reset($databases);
1040}
1041if(sizeof($databases)>0)
1042 $currentDB = $_SESSION[COOKIENAME.'currentDB'];
1043else // the database array is empty, offer to create a new database
1044{
1045 //- HTML: form to create a new database, exit
1046 if($directory!==false && is_writable($directory))
1047 {
1048 echo "<div class='confirm' style='margin:20px;'>";
1049 printf($lang['no_db'], PROJECT, PROJECT);
1050 echo "</div>";
1051 if(isset($extension_not_allowed))
1052 {
1053 echo "<div class='confirm' style='margin:10px 20px;'>";
1054 echo $lang['err'].': '.$lang['extension_not_allowed'].': ';
1055 echo implode(', ', array_map('htmlencode', $allowed_extensions));
1056 echo '<br />'.$lang['add_allowed_extension'];
1057 echo "</div><br/>";
1058 }
1059 echo "<fieldset style='margin:15px;'><legend><b>".$lang['db_create']."</b></legend>";
1060 echo "<form name='create_database' method='post' action='".PAGE."'>";
1061 echo "<input type='text' name='new_dbname' style='width:150px;'/> <input type='submit' value='".$lang['create']."' class='btn'/>";
1062 echo "</form>";
1063 echo "</fieldset>";
1064 }
1065 else
1066 {
1067 echo "<div class='confirm' style='margin:20px;'>";
1068 echo $lang['err'].": ".sprintf($lang['no_db2'], PROJECT);
1069 echo "</div><br/>";
1070 }
1071 exit();
1072}
1073
1074//- Switch to a different database with drop-down menu
1075if(isset($_POST['database_switch']))
1076{
1077 foreach($databases as $db_id => $database)
1078 {
1079 if($database['path'] == $_POST['database_switch'])
1080 {
1081 $_SESSION[COOKIENAME."currentDB"] = $database;
1082 break;
1083 }
1084 }
1085 $currentDB = $_SESSION[COOKIENAME.'currentDB'];
1086}
1087else if(isset($_GET['switchdb']))
1088{
1089 foreach($databases as $db_id => $database)
1090 {
1091 if($database['path'] == $_GET['switchdb'])
1092 {
1093 $_SESSION[COOKIENAME."currentDB"] = $database;
1094 break;
1095 }
1096 }
1097 $currentDB = $_SESSION[COOKIENAME.'currentDB'];
1098}
1099if(isset($_SESSION[COOKIENAME.'currentDB']) && in_array($_SESSION[COOKIENAME.'currentDB'], $databases))
1100 $currentDB = $_SESSION[COOKIENAME.'currentDB'];
1101
1102//- Open database (creates a Database object)
1103$db = new Database($currentDB); //create the Database object
1104$db->registerUserFunction($custom_functions);
1105
1106// collect parameters early, just once
1107$target_table = isset($_GET['table']) ? $_GET['table'] : null;
1108
1109//- Switch on $_GET['action'] for operations without output
1110if(isset($_GET['action']) && isset($_GET['confirm']))
1111{
1112 switch($_GET['action'])
1113 {
1114 //- Table actions
1115
1116 //- Create table (=table_create)
1117 case "table_create":
1118 $num = intval($_POST['rows']);
1119 $name = $_POST['tablename'];
1120 $primary_keys = array();
1121 for($i=0; $i<$num; $i++)
1122 {
1123 if($_POST[$i.'_field']!="" && isset($_POST[$i.'_primarykey']))
1124 {
1125 $primary_keys[] = $_POST[$i.'_field'];
1126 }
1127 }
1128 $query = "CREATE TABLE ".$db->quote($name)." (";
1129 for($i=0; $i<$num; $i++)
1130 {
1131 if($_POST[$i.'_field']!="")
1132 {
1133 $query .= $db->quote($_POST[$i.'_field'])." ";
1134 $query .= $_POST[$i.'_type']." ";
1135 if(isset($_POST[$i.'_primarykey']))
1136 {
1137 if(count($primary_keys)==1)
1138 {
1139 $query .= "PRIMARY KEY ";
1140 if(isset($_POST[$i.'_autoincrement']) && $db->getType() != "SQLiteDatabase")
1141 $query .= "AUTOINCREMENT ";
1142 }
1143 $query .= "NOT NULL ";
1144 }
1145 if(!isset($_POST[$i.'_primarykey']) && isset($_POST[$i.'_notnull']))
1146 $query .= "NOT NULL ";
1147 if($_POST[$i.'_defaultoption']!='defined' && $_POST[$i.'_defaultoption']!='none' && $_POST[$i.'_defaultoption']!='expr')
1148 $query .= "DEFAULT ".$_POST[$i.'_defaultoption']." ";
1149 elseif($_POST[$i.'_defaultoption']=='expr')
1150 $query .= "DEFAULT (".$_POST[$i.'_defaultvalue'].") ";
1151 elseif(isset($_POST[$i.'_defaultvalue']) && $_POST[$i.'_defaultoption']=='defined')
1152 {
1153 $typeAffinity = get_type_affinity($_POST[$i.'_type']);
1154 if(($typeAffinity=="INTEGER" || $typeAffinity=="REAL" || $typeAffinity=="NUMERIC") && is_numeric($_POST[$i.'_defaultvalue']))
1155 $query .= "DEFAULT ".$_POST[$i.'_defaultvalue']." ";
1156 else
1157 $query .= "DEFAULT ".$db->quote($_POST[$i.'_defaultvalue'])." ";
1158 }
1159 $query = substr($query, 0, sizeof($query)-2);
1160 $query .= ", ";
1161 }
1162 }
1163 if (count($primary_keys)>1)
1164 {
1165 $compound_key = "";
1166 foreach ($primary_keys as $primary_key)
1167 {
1168 $compound_key .= ($compound_key=="" ? "" : ", ") . $db->quote($primary_key);
1169 }
1170 $query .= "PRIMARY KEY (".$compound_key."), ";
1171 }
1172 $query = substr($query, 0, sizeof($query)-3);
1173 $query .= ")";
1174 $result = $db->query($query);
1175 if($result===false)
1176 $error = true;
1177 $completed = $lang['tbl']." '".htmlencode($_POST['tablename'])."' ".$lang['created'].".<br/><span style='font-size:11px;'>".htmlencode($query)."</span>";
1178 $backlinkParameters = "&action=column_view&table=".urlencode($name);
1179 break;
1180
1181 //- Empty table (=table_empty)
1182 case "table_empty":
1183 $query = "DELETE FROM ".$db->quote_id($_POST['tablename']);
1184 $result = $db->query($query);
1185 if($result===false)
1186 $error = true;
1187 $query = "VACUUM";
1188 $result = $db->query($query);
1189 if($result===false)
1190 $error = true;
1191 $completed = $lang['tbl']." '".htmlencode($_POST['tablename'])."' ".$lang['emptied'].".<br/><span style='font-size:11px;'>".htmlencode($query)."</span>";
1192 $backlinkParameters = "&action=row_view&table=".urlencode($name);
1193 break;
1194
1195 //- Create view (=view_create)
1196 case "view_create":
1197 $query = "CREATE VIEW ".$db->quote($_POST['viewname'])." AS ".$_POST['select'];
1198 $result = $db->query($query);
1199 if($result===false)
1200 $error = true;
1201 $completed = $lang['view']." '".htmlencode($_POST['viewname'])."' ".$lang['created'].".<br/><span style='font-size:11px;'>".htmlencode($query)."</span>";
1202 $backlinkParameters = "&action=column_view&table=".urlencode($_POST['viewname']);
1203 break;
1204
1205 //- Drop table (=table_drop)
1206 case "table_drop":
1207 $query = "DROP TABLE ".$db->quote_id($_POST['tablename']);
1208 $result=$db->query($query);
1209 if($result===false)
1210 $error = true;
1211 $completed = $lang['tbl']." '".htmlencode($_POST['tablename'])."' ".$lang['dropped'].".";
1212 $backlinkParameters = "";
1213 break;
1214
1215 //- Drop view (=view_drop)
1216 case "view_drop":
1217 $query = "DROP VIEW ".$db->quote_id($_POST['viewname']);
1218 $result=$db->query($query);
1219 if($result===false)
1220 $error = true;
1221 $completed = $lang['view']." '".htmlencode($_POST['viewname'])."' ".$lang['dropped'].".";
1222 $backlinkParameters = "";
1223 break;
1224
1225 //- Rename table (=table_rename)
1226 case "table_rename":
1227 $query = "ALTER TABLE ".$db->quote_id($_POST['oldname'])." RENAME TO ".$db->quote($_POST['newname']);
1228 if($db->getVersion()==3)
1229 $result = $db->query($query, true);
1230 else
1231 $result = $db->query($query, false);
1232 if($result===false)
1233 $error = true;
1234 $completed = $lang['tbl']." '".htmlencode($_POST['oldname'])."' ".$lang['renamed']." '".htmlencode($_POST['newname'])."'.<br/><span style='font-size:11px;'>".htmlencode($query)."</span>";
1235 $backlinkParameters = "&action=row_view&table=".urlencode($_POST['newname']);
1236 break;
1237
1238 //- Row actions
1239
1240 //- Create row (=row_create)
1241 case "row_create":
1242 $completed = "";
1243 $num = $_POST['numRows'];
1244 $fields = explode(":", $_POST['fields']);
1245 $z = 0;
1246
1247 $query = "PRAGMA table_info(".$db->quote_id($target_table).")";
1248 $result = $db->selectArray($query);
1249
1250 for($i=0; $i<$num; $i++)
1251 {
1252 if(!isset($_POST[$i.":ignore"]))
1253 {
1254 $query_cols = "";
1255 $query_vals = "";
1256 $all_default = true;
1257 for($j=0; $j<sizeof($fields); $j++)
1258 {
1259 if($result[$j]['name']!=$fields[$j])
1260 die($lang['err'].' - schema missmatch');
1261
1262 $null = isset($_POST[$i.":".$j."_null"]);
1263 if(!$null)
1264 $value = $_POST[$i.":".$j];
1265 else
1266 $value = "";
1267 if($value===$result[$j]['dflt_value'])
1268 {
1269 // if the value is the default value, skip it
1270 continue;
1271 } else
1272 $all_default = false;
1273 $query_cols .= $db->quote_id($fields[$j]).",";
1274
1275 $type = $result[$j]['type'];
1276 $typeAffinity = get_type_affinity($type);
1277 $function = $_POST["function_".$i."_".$j];
1278 if($function!="")
1279 $query_vals .= $function."(";
1280 if(($typeAffinity=="TEXT" || $typeAffinity=="NONE") && !$null)
1281 $query_vals .= $db->quote($value);
1282 elseif(($typeAffinity=="INTEGER" || $typeAffinity=="REAL"|| $typeAffinity=="NUMERIC") && $value=="")
1283 $query_vals .= "NULL";
1284 elseif($null)
1285 $query_vals .= "NULL";
1286 else
1287 $query_vals .= $db->quote($value);
1288 if($function!="")
1289 $query_vals .= ")";
1290 $query_vals .= ",";
1291 }
1292 $query = "INSERT INTO ".$db->quote_id($target_table);
1293 if(!$all_default)
1294 {
1295 $query_cols = substr($query_cols, 0, strlen($query_cols)-1);
1296 $query_vals = substr($query_vals, 0, strlen($query_vals)-1);
1297
1298 $query.=" (". $query_cols . ") VALUES (". $query_vals. ")";
1299 } else {
1300 $query .= " DEFAULT VALUES";
1301 }
1302 $result1 = $db->query($query);
1303 if($result1===false)
1304 $error = true;
1305 $completed .= "<span style='font-size:11px;'>".htmlencode($query)."</span><br/>";
1306 $z++;
1307 }
1308 }
1309 $completed = $z." ".$lang['rows']." ".$lang['inserted'].".<br/><br/>".$completed;
1310 $backlinkParameters = "&action=column_view&table=".urlencode($target_table);
1311 break;
1312
1313 //- Delete row (=row_delete)
1314 case "row_delete":
1315 $pks = json_decode($_GET['pk']);
1316
1317 $query = "DELETE FROM ".$db->quote_id($target_table)." WHERE (".$db->wherePK($target_table,json_decode($pks[0])).")";
1318 for($i=1; $i<sizeof($pks); $i++)
1319 {
1320 $query .= " OR (".$db->wherePK($target_table,json_decode($pks[$i])).")";
1321 }
1322 $result = $db->query($query);
1323 if($result===false)
1324 $error = true;
1325 $completed = sizeof($pks)." ".$lang['rows']." ".$lang['deleted'].".<br/><span style='font-size:11px;'>".htmlencode($query)."</span>";
1326 $backlinkParameters = "&action=row_view&table=".urlencode($target_table);
1327 break;
1328
1329 //- Edit row (=row_edit)
1330 case "row_edit":
1331 $pks = json_decode($_GET['pk']);
1332 $fields = explode(":", $_POST['fieldArray']);
1333
1334 $z = 0;
1335
1336 $query = "PRAGMA table_info(".$db->quote_id($target_table).")";
1337 $result = $db->selectArray($query);
1338
1339 if(isset($_POST['new_row']))
1340 $completed = "";
1341 else
1342 $completed = sizeof($pks)." ".$lang['rows']." ".$lang['affected'].".<br/><br/>";
1343
1344 for($i=0; $i<sizeof($pks); $i++)
1345 {
1346 if(isset($_POST['new_row']))
1347 {
1348 $query_cols = "";
1349 $query_vals = "";
1350 $all_default = true;
1351 for($j=0; $j<sizeof($fields); $j++)
1352 {
1353 if($result[$j]['name']!=$fields[$j])
1354 die($lang['err'].' - schema missmatch');
1355 $null = isset($_POST[$j."_null"][$i]);
1356 if(!$null)
1357 {
1358 $value = $_POST[$j][$i];
1359 }
1360 else
1361 $value = "";
1362 if($value===$result[$j]['dflt_value'])
1363 {
1364 // if the value is the default value, skip it
1365 continue;
1366 } else
1367 $all_default = false;
1368 $query_cols .= $db->quote_id($fields[$j]).",";
1369
1370 $type = $result[$j]['type'];
1371 $typeAffinity = get_type_affinity($type);
1372 $function = $_POST["function_".$j][$i];
1373 if($function!="")
1374 $query_vals .= $function."(";
1375 if(($typeAffinity=="TEXT" || $typeAffinity=="NONE") && !$null)
1376 $query_vals .= $db->quote($value);
1377 elseif(($typeAffinity=="INTEGER" || $typeAffinity=="REAL"|| $typeAffinity=="NUMERIC") && $value=="")
1378 $query_vals .= "NULL";
1379 elseif($null)
1380 $query_vals .= "NULL";
1381 else
1382 $query_vals .= $db->quote($value);
1383 if($function!="")
1384 $query_vals .= ")";
1385 $query_vals .= ",";
1386 }
1387 $query = "INSERT INTO ".$db->quote_id($target_table);
1388 if(!$all_default)
1389 {
1390 $query_cols = substr($query_cols, 0, strlen($query_cols)-1);
1391 $query_vals = substr($query_vals, 0, strlen($query_vals)-1);
1392
1393 $query.=" (". $query_cols . ") VALUES (". $query_vals. ")";
1394 } else {
1395 $query .= " DEFAULT VALUES";
1396 }
1397 $result1 = $db->query($query);
1398 if($result1===false)
1399 $error = true;
1400 $z++;
1401 }
1402 else
1403 {
1404 $query = "UPDATE ".$db->quote_id($target_table)." SET ";
1405 for($j=0; $j<sizeof($fields); $j++)
1406 {
1407 $function = $_POST["function_".$j][$i];
1408 $null = isset($_POST[$j."_null"][$i]);
1409 $query .= $db->quote_id($fields[$j])."=";
1410 if($function!="")
1411 $query .= $function."(";
1412 if($null)
1413 $query .= "NULL";
1414 else
1415 $query .= $db->quote($_POST[$j][$i]);
1416 if($function!="")
1417 $query .= ")";
1418 $query .= ", ";
1419 }
1420 $query = substr($query, 0, sizeof($query)-3);
1421 $query .= " WHERE ".$db->wherePK($target_table, json_decode($pks[$i]));
1422 $result1 = $db->query($query);
1423 if($result1===false)
1424 {
1425 $error = true;
1426 }
1427 }
1428 $completed .= "<span style='font-size:11px;'>".htmlencode($query)."</span><br/>";
1429 }
1430 if(isset($_POST['new_row']))
1431 $completed = $z." ".$lang['rows']." ".$lang['inserted'].".<br/><br/>".$completed;
1432 $backlinkParameters = "&action=row_view&table=".urlencode($target_table);
1433 break;
1434
1435 //- Column actions
1436
1437 //- Create column (=column_create)
1438 case "column_create":
1439 $num = intval($_POST['rows']);
1440 for($i=0; $i<$num; $i++)
1441 {
1442 if($_POST[$i.'_field']!="")
1443 {
1444 $query = "ALTER TABLE ".$db->quote_id($target_table)." ADD ".$db->quote($_POST[$i.'_field'])." ";
1445 $query .= $_POST[$i.'_type']." ";
1446 if(isset($_POST[$i.'_primarykey']))
1447 $query .= "PRIMARY KEY ";
1448 if(isset($_POST[$i.'_notnull']))
1449 $query .= "NOT NULL ";
1450 if($_POST[$i.'_defaultoption']!='defined' && $_POST[$i.'_defaultoption']!='none' && $_POST[$i.'_defaultoption']!='expr')
1451 $query .= "DEFAULT ".$_POST[$i.'_defaultoption']." ";
1452 elseif($_POST[$i.'_defaultoption']=='expr')
1453 $query .= "DEFAULT (".$_POST[$i.'_defaultvalue'].") ";
1454 elseif(isset($_POST[$i.'_defaultvalue']) && $_POST[$i.'_defaultoption']=='defined')
1455 {
1456 $typeAffinity = get_type_affinity($_POST[$i.'_type']);
1457 if(($typeAffinity=="INTEGER" || $typeAffinity=="REAL" || $typeAffinity=="NUMERIC") && is_numeric($_POST[$i.'_defaultvalue']))
1458 $query .= "DEFAULT ".$_POST[$i.'_defaultvalue']." ";
1459 else
1460 $query .= "DEFAULT ".$db->quote($_POST[$i.'_defaultvalue'])." ";
1461 }
1462 if($db->getVersion()==3 &&
1463 ($_POST[$i.'_defaultoption']=='defined' || $_POST[$i.'_defaultoption']=='none' || $_POST[$i.'_defaultoption']=='NULL')
1464 // Sqlite3 cannot add columns with default values that are not constant
1465 && !isset($_POST[$i.'_primarykey'])
1466 // sqlite3 cannot add primary key columns
1467 && (!isset($_POST[$i.'_notnull']) || $_POST[$i.'_defaultoption']!='none')
1468 // SQLite3 cannot add NOT NULL columns without DEFAULT even if the table is empty
1469 )
1470 // use SQLITE3 ALTER TABLE ADD COLUMN
1471 $result = $db->query($query, true);
1472 else
1473 // use ALTER TABLE workaround
1474 $result = $db->query($query, false);
1475 if($result===false)
1476 $error = true;
1477 }
1478 }
1479 $completed = $lang['tbl']." '".htmlencode($target_table)."' ".$lang['altered'].".";
1480 $backlinkParameters = "&action=column_view&table=".urlencode($target_table);
1481 break;
1482
1483 //- Delete column (=column_delete)
1484 case "column_delete":
1485 $pks = explode(":", $_GET['pk']);
1486 $query = "ALTER TABLE ".$db->quote_id($target_table).' DROP '.$db->quote_id($pks[0]);
1487 for($i=1; $i<sizeof($pks); $i++)
1488 {
1489 $query .= ", DROP ".$db->quote_id($pks[$i]);
1490 }
1491 $result = $db->query($query);
1492 if($result===false)
1493 $error = true;
1494 $completed = $lang['tbl']." '".htmlencode($target_table)."' ".$lang['altered'].".";
1495 $backlinkParameters = "&action=column_view&table=".urlencode($target_table);
1496 break;
1497
1498 //- Add a primary key (=primarykey_add)
1499 case "primarykey_add":
1500 $pks = explode(":", $_GET['pk']);
1501 $query = "ALTER TABLE ".$db->quote_id($target_table).' ADD PRIMARY KEY ('.$db->quote_id($pks[0]);
1502 for($i=1; $i<sizeof($pks); $i++)
1503 {
1504 $query .= ", ".$db->quote_id($pks[$i]);
1505 }
1506 $query .= ")";
1507 $result = $db->query($query);
1508 if($result===false)
1509 $error = true;
1510 $completed = $lang['tbl']." '".htmlencode($target_table)."' ".$lang['altered'].".";
1511 $backlinkParameters = "&action=column_view&table=".urlencode($target_table);
1512 break;
1513
1514 //- Edit column (=column_edit)
1515 case "column_edit":
1516 $query = "ALTER TABLE ".$db->quote_id($target_table).' CHANGE '.$db->quote_id($_POST['oldvalue'])." ".$db->quote($_POST['0_field'])." ".$_POST['0_type'];
1517 $result = $db->query($query);
1518 if($result===false)
1519 $error = true;
1520 $completed = $lang['tbl']." '".htmlencode($target_table)."' ".$lang['altered'].".";
1521 $backlinkParameters = "&action=column_view&table=".urlencode($target_table);
1522 break;
1523
1524 //- Delete trigger (=trigger_delete)
1525 case "trigger_delete":
1526 $query = "DROP TRIGGER ".$db->quote_id($_GET['pk']);
1527 $result = $db->query($query);
1528 if($result===false)
1529 $error = true;
1530 $completed = $lang['trigger']." '".htmlencode($_GET['pk'])."' ".$lang['deleted'].".<br/><span style='font-size:11px;'>".htmlencode($query)."</span>";
1531 $backlinkParameters = "&action=column_view&table=".urlencode($target_table);
1532 break;
1533
1534 //- Delete index (=index_delete)
1535 case "index_delete":
1536 $query = "DROP INDEX ".$db->quote_id($_GET['pk']);
1537 $result = $db->query($query);
1538 if($result===false)
1539 $error = true;
1540 $completed = $lang['index']." '".htmlencode($_GET['pk'])."' ".$lang['deleted'].".<br/><span style='font-size:11px;'>".htmlencode($query)."</span>";
1541 $backlinkParameters = "&action=column_view&table=".urlencode($target_table);
1542 break;
1543
1544 //- Create trigger (=trigger_create)
1545 case "trigger_create":
1546 $str = "CREATE TRIGGER ".$db->quote($_POST['trigger_name']);
1547 if($_POST['beforeafter']!="")
1548 $str .= " ".$_POST['beforeafter'];
1549 $str .= " ".$_POST['event']." ON ".$db->quote_id($target_table);
1550 if(isset($_POST['foreachrow']))
1551 $str .= " FOR EACH ROW";
1552 if($_POST['whenexpression']!="")
1553 $str .= " WHEN ".$_POST['whenexpression'];
1554 $str .= " BEGIN";
1555 $str .= " ".$_POST['triggersteps'];
1556 $str .= " END";
1557 $query = $str;
1558 $result = $db->query($query);
1559 if($result===false)
1560 $error = true;
1561 $completed = $lang['trigger']." ".$lang['created'].".<br/><span style='font-size:11px;'>".htmlencode($query)."</span>";
1562 $backlinkParameters = "&action=column_view&table=".urlencode($target_table);
1563 break;
1564
1565 //- Create index (=index_create)
1566 case "index_create":
1567 $num = $_POST['num'];
1568 if($_POST['name']=="")
1569 {
1570 $completed = $lang['blank_index'];
1571 }
1572 else if($_POST['0_field']=="")
1573 {
1574 $completed = $lang['one_index'];
1575 }
1576 else
1577 {
1578 $str = "CREATE ";
1579 if($_POST['duplicate']=="no")
1580 $str .= "UNIQUE ";
1581 $str .= "INDEX ".$db->quote($_POST['name'])." ON ".$db->quote_id($target_table)." (";
1582 $str .= $db->quote_id($_POST['0_field']).$_POST['0_order'];
1583 for($i=1; $i<$num; $i++)
1584 {
1585 if($_POST[$i.'_field']!="")
1586 $str .= ", ".$db->quote_id($_POST[$i.'_field']).$_POST[$i.'_order'];
1587 }
1588 $str .= ")";
1589 if(isset($_POST['where']) && $_POST['where']!='')
1590 $str.=" WHERE ".$_POST['where'];
1591 $query = $str;
1592 $result = $db->query($query);
1593 if($result===false)
1594 $error = true;
1595 $completed = $lang['index']." ".$lang['created'].".<br/><span style='font-size:11px;'>".htmlencode($query)."</span>";
1596 }
1597 $backlinkParameters = "&action=column_view&table=".urlencode($target_table);
1598 break;
1599 }
1600}
1601
1602// are we working on a view? let's check once here
1603$target_table_type = $target_table ? $db->getTypeOfTable($target_table) : null;
1604
1605//- HTML: sidebar
1606echo '<table class="body_tbl" width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td valign="top" class="left_td" style="width:100px; padding:9px 2px 9px 9px;">';
1607echo "<div id='leftNav'>";
1608echo "<h1><a href='".PAGE."'>";
1609echo "<span id='logo'>".PROJECT."</span> <span id='version'>v".VERSION."</span>";
1610echo "</a></h1>";
1611echo "<div id='headerlinks'>";
1612echo "<a href='javascript:void' onclick='openHelp(\"top\");'>".$lang['docu']."</a> | ";
1613echo "<a href='http://www.gnu.org/licenses/gpl.html' target='_blank'>".$lang['license']."</a> | ";
1614echo "<a href='".PROJECT_URL."' target='_blank'>".$lang['proj_site']."</a>";
1615echo "</div>";
1616
1617//- HTML: database list
1618$db->print_db_list();
1619echo "<fieldset style='margin:15px;'><legend>";
1620echo "<a href='".PAGE."'";
1621if (!$target_table)
1622 echo " class='active_table'";
1623echo ">".htmlencode($currentDB['name'])."</a>";
1624echo "</legend>";
1625
1626//- HTML: table list
1627$query = "SELECT type, name FROM sqlite_master WHERE type='table' OR type='view' ORDER BY name";
1628$result = $db->selectArray($query);
1629$j=0;
1630for($i=0; $i<sizeof($result); $i++)
1631{
1632 if(substr($result[$i]['name'], 0, 7)!="sqlite_" && $result[$i]['name']!="")
1633 {
1634 echo "<span class='sidebar_table'>[".$lang[$result[$i]['type']=='table'?'tbl':'view']."]</span> ";
1635 echo "<a href='?action=row_view&table=".urlencode($result[$i]['name'])."'";
1636 if ($target_table == $result[$i]['name'])
1637 echo " class='active_table'";
1638 echo ">".htmlencode($result[$i]['name'])."</a><br/>";
1639 $j++;
1640 }
1641}
1642if($j==0)
1643 echo $lang['no_tbl'];
1644echo "</fieldset>";
1645
1646//- HTML: form to create a new database
1647if($directory!==false && is_writable($directory))
1648{
1649 echo "<fieldset style='margin:15px;'><legend><b>".$lang['db_create']."</b> ".helpLink($lang['help2'])."</legend>";
1650 echo "<form name='create_database' method='post' action='".PAGE."'>";
1651 echo "<input type='text' name='new_dbname' style='width:150px;'/> <input type='submit' value='".$lang['create']."' class='btn'/>";
1652 echo "</form>";
1653 echo "</fieldset>";
1654}
1655
1656echo "<div style='text-align:center;'>";
1657echo "<form action='".PAGE."' method='post'>";
1658echo "<input type='submit' value='".$lang['logout']."' name='logout' class='btn'/>";
1659echo "</form>";
1660echo "</div>";
1661echo "</div>";
1662echo '</td><td valign="top" id="main_column" class="right_td" style="padding:9px 2px 9px 9px;">';
1663
1664//- HTML: breadcrumb navigation
1665echo "<a href='".PAGE."'>".htmlencode($currentDB['name'])."</a>";
1666if ($target_table)
1667 echo " → <a href='?table=".urlencode($target_table)."&action=row_view'>".htmlencode($target_table)."</a>";
1668echo "<br/><br/>";
1669
1670//- HTML: confirmation panel
1671//if the user has performed some action, show the resulting message
1672if(isset($_GET['confirm']))
1673{
1674 echo "<div id='main'>";
1675 echo "<div class='confirm'>";
1676 if(isset($error) && $error) //an error occured during the action, so show an error message
1677 echo $lang['err'].": ".$db->getError()."<br/>".$lang['bug_report'].' '.PROJECT_BUGTRACKER_LINK;
1678 else //action was performed successfully - show success message
1679 echo $completed;
1680 echo "</div>";
1681 if($_GET['action']=="row_delete" || $_GET['action']=="row_create" || $_GET['action']=="row_edit")
1682 echo "<br/><br/><a href='?table=".urlencode($target_table)."&action=row_view'>".$lang['return']."</a>";
1683 else if($_GET['action']=="column_create" || $_GET['action']=="column_delete" || $_GET['action']=="column_edit" || $_GET['action']=="index_create" || $_GET['action']=="index_delete" || $_GET['action']=="trigger_delete" || $_GET['action']=="trigger_create")
1684 echo "<br/><br/><a href='?table=".urlencode($target_table)."&action=column_view'>".$lang['return']."</a>";
1685 else
1686 echo "<br/><br/><a href='".PAGE.(isset($backlinkParameters)?"?".$backlinkParameters:'')."'>".$lang['return']."</a>";
1687 echo "</div>";
1688}
1689
1690//- Show the various tab views for a table
1691if(!isset($_GET['confirm']) && $target_table && isset($_GET['action']) && ($_GET['action']=="table_export" || $_GET['action']=="table_import" || $_GET['action']=="table_sql" || $_GET['action']=="row_view" || $_GET['action']=="row_create" || $_GET['action']=="column_view" || $_GET['action']=="table_rename" || $_GET['action']=="table_search" || $_GET['action']=="table_triggers"))
1692{
1693 //- HTML: tabs for tables
1694 if($target_table_type == 'table')
1695 {
1696 echo "<a href='?table=".urlencode($target_table)."&action=row_view' ";
1697 if($_GET['action']=="row_view")
1698 echo "class='tab_pressed'";
1699 else
1700 echo "class='tab'";
1701 echo ">".$lang['browse']."</a>";
1702 echo "<a href='?table=".urlencode($target_table)."&action=column_view' ";
1703 if($_GET['action']=="column_view")
1704 echo "class='tab_pressed'";
1705 else
1706 echo "class='tab'";
1707 echo ">".$lang['struct']."</a>";
1708 echo "<a href='?table=".urlencode($target_table)."&action=table_sql' ";
1709 if($_GET['action']=="table_sql")
1710 echo "class='tab_pressed'";
1711 else
1712 echo "class='tab'";
1713 echo ">".$lang['sql']."</a>";
1714 echo "<a href='?table=".urlencode($target_table)."&action=table_search' ";
1715 if($_GET['action']=="table_search")
1716 echo "class='tab_pressed'";
1717 else
1718 echo "class='tab'";
1719 echo ">".$lang['srch']."</a>";
1720 echo "<a href='?table=".urlencode($target_table)."&action=row_create' ";
1721 if($_GET['action']=="row_create")
1722 echo "class='tab_pressed'";
1723 else
1724 echo "class='tab'";
1725 echo ">".$lang['insert']."</a>";
1726 echo "<a href='?table=".urlencode($target_table)."&action=table_export' ";
1727 if($_GET['action']=="table_export")
1728 echo "class='tab_pressed'";
1729 else
1730 echo "class='tab'";
1731 echo ">".$lang['export']."</a>";
1732 echo "<a href='?table=".urlencode($target_table)."&action=table_import' ";
1733 if($_GET['action']=="table_import")
1734 echo "class='tab_pressed'";
1735 else
1736 echo "class='tab'";
1737 echo ">".$lang['import']."</a>";
1738 echo "<a href='?table=".urlencode($target_table)."&action=table_rename' ";
1739 if($_GET['action']=="table_rename")
1740 echo "class='tab_pressed'";
1741 else
1742 echo "class='tab'";
1743 echo ">".$lang['rename']."</a>";
1744 echo "<a href='?action=table_empty&table=".urlencode($target_table)."' ";
1745 echo "class='tab empty'";
1746 echo ">".$lang['empty']."</a>";
1747 echo "<a href='?action=table_drop&table=".urlencode($target_table)."' ";
1748 echo "class='tab drop'";
1749 echo ">".$lang['drop']."</a>";
1750 echo "<div style='clear:both;'></div>";
1751 }
1752 else
1753 //- HTML: tabs for views
1754 {
1755 echo "<a href='?table=".urlencode($target_table)."&action=row_view' ";
1756 if($_GET['action']=="row_view")
1757 echo "class='tab_pressed'";
1758 else
1759 echo "class='tab'";
1760 echo ">".$lang['browse']."</a>";
1761 echo "<a href='?table=".urlencode($target_table)."&action=column_view' ";
1762 if($_GET['action']=="column_view")
1763 echo "class='tab_pressed'";
1764 else
1765 echo "class='tab'";
1766 echo ">".$lang['struct']."</a>";
1767 echo "<a href='?table=".urlencode($target_table)."&action=table_sql' ";
1768 if($_GET['action']=="table_sql")
1769 echo "class='tab_pressed'";
1770 else
1771 echo "class='tab'";
1772 echo ">".$lang['sql']."</a>";
1773 echo "<a href='?table=".urlencode($target_table)."&action=table_search' ";
1774 if($_GET['action']=="table_search")
1775 echo "class='tab_pressed'";
1776 else
1777 echo "class='tab'";
1778 echo ">".$lang['srch']."</a>";
1779 echo "<a href='?table=".urlencode($target_table)."&action=table_export' ";
1780 if($_GET['action']=="table_export")
1781 echo "class='tab_pressed'";
1782 else
1783 echo "class='tab'";
1784 echo ">".$lang['export']."</a>";
1785 echo "<a href='?action=view_drop&table=".urlencode($target_table)."' ";
1786 echo "class='tab drop'";
1787 echo ">".$lang['drop']."</a>";
1788 echo "<div style='clear:both;'></div>";
1789 }
1790}
1791
1792//- Switch on $_GET['action'] for operations with output
1793if(isset($_GET['action']) && !isset($_GET['confirm']))
1794{
1795 echo "<div id='main'>";
1796 switch($_GET['action'])
1797 {
1798 //- Table actions
1799
1800 //- Create table (=table_create)
1801 case "table_create":
1802 $query = "SELECT name FROM sqlite_master WHERE type='table' AND name=".$db->quote($_POST['tablename']);
1803 $results = $db->selectArray($query);
1804 if(sizeof($results)>0)
1805 $exists = true;
1806 else
1807 $exists = false;
1808 echo "<h2>".$lang['create_tbl'].": '".htmlencode($_POST['tablename'])."'</h2>";
1809 if($_POST['tablefields']=="" || intval($_POST['tablefields'])<=0)
1810 echo $lang['specify_fields'];
1811 else if($_POST['tablename']=="")
1812 echo $lang['specify_tbl'];
1813 else if($exists)
1814 echo $lang['tbl_exists'];
1815 else
1816 {
1817 $num = intval($_POST['tablefields']);
1818 $name = $_POST['tablename'];
1819 echo "<form action='?action=table_create&confirm=1' method='post'>";
1820 echo "<input type='hidden' name='tablename' value='".htmlencode($name)."'/>";
1821 echo "<input type='hidden' name='rows' value='".$num."'/>";
1822 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
1823 echo "<tr>";
1824 $headings = array($lang['fld'], $lang['type'], $lang['prim_key']);
1825 if($db->getType() != "SQLiteDatabase") $headings[] = $lang['autoincrement'];
1826 $headings[] = $lang['not_null'];
1827 $headings[] = $lang['def_val'];
1828 for($k=0; $k<count($headings); $k++)
1829 echo "<td class='tdheader'>" . $headings[$k] . "</td>";
1830 echo "</tr>";
1831
1832 for($i=0; $i<$num; $i++)
1833 {
1834 $tdWithClass = "<td class='td" . ($i%2 ? "1" : "2") . "'>";
1835 echo "<tr>";
1836 echo $tdWithClass;
1837 echo "<input type='text' name='".$i."_field' style='width:200px;'/>";
1838 echo "</td>";
1839 echo $tdWithClass;
1840 echo "<select name='".$i."_type' id='i".$i."_type' onchange='toggleAutoincrement(".$i.");'>";
1841 foreach ($sqlite_datatypes as $t) {
1842 echo "<option value='".htmlencode($t)."'>".htmlencode($t)."</option>";
1843 }
1844 echo "</select>";
1845 echo "</td>";
1846 echo $tdWithClass;
1847 echo "<label><input type='checkbox' name='".$i."_primarykey' id='i".$i."_primarykey' onclick='toggleNull(".$i."); toggleAutoincrement(".$i.");'/> ".$lang['yes']."</label>";
1848 echo "</td>";
1849 if($db->getType() != "SQLiteDatabase")
1850 {
1851 echo $tdWithClass;
1852 echo "<label><input type='checkbox' name='".$i."_autoincrement' id='i".$i."_autoincrement'/> ".$lang['yes']."</label>";
1853 echo "</td>";
1854 }
1855 echo $tdWithClass;
1856 echo "<label><input type='checkbox' name='".$i."_notnull' id='i".$i."_notnull'/> ".$lang['yes']."</label>";
1857 echo "</td>";
1858 echo $tdWithClass;
1859 echo "<select name='".$i."_defaultoption' id='i".$i."_defaultoption' onchange=\"if(this.value!='defined' && this.value!='expr') document.getElementById('i".$i."_defaultvalue').value='';\">";
1860 echo "<option value='none'>".$lang['none']."</option><option value='defined'>".$lang['as_defined'].":</option><option>NULL</option><option>CURRENT_TIME</option><option>CURRENT_DATE</option><option>CURRENT_TIMESTAMP</option><option value='expr'>".$lang['expression'].":</option>";
1861 echo "</select>";
1862 echo "<input type='text' name='".$i."_defaultvalue' id='i".$i."_defaultvalue' style='width:100px;' onchange=\"if(document.getElementById('i".$i."_defaultoption').value!='expr') document.getElementById('i".$i."_defaultoption').value='defined';\"/>";
1863 echo "</td>";
1864 echo "</tr>";
1865 }
1866 echo "<tr>";
1867 echo "<td class='tdheader' style='text-align:right;' colspan='6'>";
1868 echo "<input type='submit' value='".$lang['create']."' class='btn'/> ";
1869 echo "<a href='".PAGE."'>".$lang['cancel']."</a>";
1870 echo "</td>";
1871 echo "</tr>";
1872 echo "</table>";
1873 echo "</form>";
1874 if($db->getType() != "SQLiteDatabase") echo "<script type='text/javascript'>window.onload=initAutoincrement;</script>";
1875 }
1876 break;
1877
1878 //- Perform SQL query on table (=table_sql)
1879 case "table_sql":
1880 $isSelect = false;
1881 if(isset($_POST['query']) && $_POST['query']!="")
1882 {
1883 $delimiter = $_POST['delimiter'];
1884 $queryStr = $_POST['queryval'];
1885 //save the queries in history if necessary
1886 if($maxSavedQueries!=0 && $maxSavedQueries!=false)
1887 {
1888 if(!isset($_SESSION['query_history']))
1889 $_SESSION['query_history'] = array();
1890 $_SESSION['query_history'][md5(strtolower($queryStr))] = $queryStr;
1891 if(sizeof($_SESSION['query_history']) > $maxSavedQueries)
1892 array_shift($_SESSION['query_history']);
1893 }
1894 $query = explode_sql($delimiter, $queryStr); //explode the query string into individual queries based on the delimiter
1895
1896 for($i=0; $i<sizeof($query); $i++) //iterate through the queries exploded by the delimiter
1897 {
1898 if(str_replace(" ", "", str_replace("\n", "", str_replace("\r", "", $query[$i])))!="") //make sure this query is not an empty string
1899 {
1900 $queryTimer = new MicroTimer();
1901 $result = $db->selectArray($query[$i], "assoc");
1902 $queryTimer->stop();
1903
1904 echo "<div class='confirm'>";
1905 echo "<b>";
1906
1907 if($result !== NULL)
1908 {
1909
1910 if(sizeof($result)>0 || $db->getAffectedRows()==0)
1911 {
1912 printf($lang['show_rows'], sizeof($result));
1913 }
1914 if($db->getAffectedRows()>0 || sizeof($result)==0)
1915 {
1916 echo $db->getAffectedRows()." ".$lang['rows_aff']." ";
1917 }
1918 printf($lang['query_time'], $queryTimer);
1919 echo "</b><br/>";
1920 }
1921 else
1922 {
1923 echo $lang['err'].": ".$db->getError()."</b><br/>";
1924 }
1925
1926 echo "<span style='font-size:11px;'>".htmlencode($query[$i])."</span>";
1927 echo "</div><br/>";
1928 if(sizeof($result)>0)
1929 {
1930 $headers = array_keys($result[0]);
1931
1932 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
1933 echo "<tr>";
1934 for($j=0; $j<sizeof($headers); $j++)
1935 {
1936 echo "<td class='tdheader'>";
1937 echo htmlencode($headers[$j]);
1938 echo "</td>";
1939 }
1940 echo "</tr>";
1941 for($j=0; $j<sizeof($result); $j++)
1942 {
1943 $tdWithClass = "<td class='td".($j%2 ? "1" : "2")."'>";
1944 echo "<tr>";
1945 for($z=0; $z<sizeof($headers); $z++)
1946 {
1947 echo $tdWithClass;
1948 if($result[$j][$headers[$z]]==="")
1949 echo " ";
1950 elseif($result[$j][$headers[$z]]===NULL)
1951 echo "<i class='null'>NULL</i>";
1952 else
1953 echo htmlencode(subString($result[$j][$headers[$z]]));
1954 echo "</td>";
1955 }
1956 echo "</tr>";
1957 }
1958 echo "</table><br/><br/>";
1959 }
1960 }
1961 }
1962 }
1963 else
1964 {
1965 $delimiter = ";";
1966 $queryStr = "SELECT * FROM ".$db->quote_id($target_table)." WHERE 1";
1967 }
1968
1969 echo "<fieldset>";
1970 echo "<legend><b>".sprintf($lang['run_sql'],htmlencode($db->getName()))."</b></legend>";
1971 echo "<form action='?table=".urlencode($target_table)."&action=table_sql' method='post'>";
1972 if(isset($_SESSION['query_history']) && sizeof($_SESSION['query_history'])>0)
1973 {
1974 echo "<b>".$lang['recent_queries']."</b><ul>";
1975 foreach($_SESSION['query_history'] as $key => $value)
1976 echo "<li><a onclick='document.getElementById(\"queryval\").value = this.textContent' href='#'>".htmlencode($value)."</a></li>";
1977 echo "</ul><br/><br/>";
1978 }
1979 echo "<div style='float:left; width:70%;'>";
1980 echo "<textarea style='width:97%; height:300px;' name='queryval' id='queryval' cols='50' rows='8'>".htmlencode($queryStr)."</textarea>";
1981 echo "</div>";
1982 echo "<div style='float:left; width:28%; padding-left:10px;'>";
1983 echo $lang['fields']."<br/>";
1984 echo "<select multiple='multiple' style='width:100%;' id='fieldcontainer'>";
1985 $query = "PRAGMA table_info(".$db->quote_id($target_table).")";
1986 $result = $db->selectArray($query);
1987 for($i=0; $i<sizeof($result); $i++)
1988 {
1989 echo "<option value='".htmlencode($result[$i][1])."'>".htmlencode($result[$i][1])."</option>";
1990 }
1991 echo "</select>";
1992 echo "<input type='button' value='<<' onclick='moveFields();' class='btn'/>";
1993 echo "</div>";
1994 echo "<div style='clear:both;'></div>";
1995 echo $lang['delimit']." <input type='text' name='delimiter' value='".htmlencode($delimiter)."' style='width:50px;'/> ";
1996 echo "<input type='submit' name='query' value='".$lang['go']."' class='btn'/>";
1997 echo "</form>";
1998 echo "</fieldset>";
1999 break;
2000
2001 //- Empty table (=table_empty)
2002 case "table_empty":
2003 echo "<form action='?action=table_empty&confirm=1' method='post'>";
2004 echo "<input type='hidden' name='tablename' value='".htmlencode($target_table)."'/>";
2005 echo "<div class='confirm'>";
2006 echo sprintf($lang['ques_empty'], htmlencode($target_table))."<br/><br/>";
2007 echo "<input type='submit' value='".$lang['confirm']."' class='btn'/> ";
2008 echo "<a href='".PAGE."'>".$lang['cancel']."</a>";
2009 echo "</div>";
2010 break;
2011
2012 //- Drop table (=table_drop)
2013 case "table_drop":
2014 echo "<form action='?action=table_drop&confirm=1' method='post'>";
2015 echo "<input type='hidden' name='tablename' value='".htmlencode($target_table)."'/>";
2016 echo "<div class='confirm'>";
2017 echo sprintf($lang['ques_drop'], htmlencode($target_table))."<br/><br/>";
2018 echo "<input type='submit' value='".$lang['confirm']."' class='btn'/> ";
2019 echo "<a href='".PAGE."'>".$lang['cancel']."</a>";
2020 echo "</div>";
2021 break;
2022
2023 //- Drop view (=view_drop)
2024 case "view_drop":
2025 echo "<form action='?action=view_drop&confirm=1' method='post'>";
2026 echo "<input type='hidden' name='viewname' value='".htmlencode($target_table)."'/>";
2027 echo "<div class='confirm'>";
2028 echo sprintf($lang['ques_drop_view'], htmlencode($target_table))."<br/><br/>";
2029 echo "<input type='submit' value='".$lang['confirm']."' class='btn'/> ";
2030 echo "<a href='".PAGE."'>".$lang['cancel']."</a>";
2031 echo "</div>";
2032 break;
2033
2034 //- Export table (=table_export)
2035 case "table_export":
2036 echo "<form method='post' action='".PAGE."'>";
2037 echo "<fieldset style='float:left; width:260px; margin-right:20px;'><legend><b>".$lang['export']."</b></legend>";
2038 echo "<input type='hidden' value='".htmlencode($target_table)."' name='single_table'/>";
2039 echo "<label><input type='radio' name='export_type' checked='checked' value='sql' onclick='toggleExports(\"sql\");'/> ".$lang['sql']."</label>";
2040 echo "<br/><label><input type='radio' name='export_type' value='csv' onclick='toggleExports(\"csv\");'/> ".$lang['csv']."</label>";
2041 echo "</fieldset>";
2042
2043 echo "<fieldset style='float:left; max-width:350px;' id='exportoptions_sql'><legend><b>".$lang['options']."</b></legend>";
2044 echo "<label><input type='checkbox' checked='checked' name='structure'/> ".$lang['export_struct']."</label> ".helpLink($lang['help5'])."<br/>";
2045 echo "<label><input type='checkbox' checked='checked' name='data'/> ".$lang['export_data']."</label> ".helpLink($lang['help6'])."<br/>";
2046 echo "<label><input type='checkbox' name='drop'/> ".$lang['add_drop']."</label> ".helpLink($lang['help7'])."<br/>";
2047 echo "<label><input type='checkbox' checked='checked' name='transaction'/> ".$lang['add_transact']."</label> ".helpLink($lang['help8'])."<br/>";
2048 echo "<label><input type='checkbox' checked='checked' name='comments'/> ".$lang['comments']."</label> ".helpLink($lang['help9'])."<br/>";
2049 echo "</fieldset>";
2050
2051 echo "<fieldset style='float:left; max-width:350px; display:none;' id='exportoptions_csv'><legend><b>".$lang['options']."</b></legend>";
2052 echo "<div style='float:left;'>".$lang['fld_terminated']."</div>";
2053 echo "<input type='text' value=';' name='export_csv_fieldsterminated' style='float:right;'/>";
2054 echo "<div style='clear:both;'></div>";
2055 echo "<div style='float:left;'>".$lang['fld_enclosed']."</div>";
2056 echo "<input type='text' value='\"' name='export_csv_fieldsenclosed' style='float:right;'/>";
2057 echo "<div style='clear:both;'></div>";
2058 echo "<div style='float:left;'>".$lang['fld_escaped']."</div>";
2059 echo "<input type='text' value='\' name='export_csv_fieldsescaped' style='float:right;'/>";
2060 echo "<div style='clear:both;'></div>";
2061 echo "<div style='float:left;'>".$lang['rep_null']."</div>";
2062 echo "<input type='text' value='NULL' name='export_csv_replacenull' style='float:right;'/>";
2063 echo "<div style='clear:both;'></div>";
2064 echo "<label><input type='checkbox' name='export_csv_crlf'/> ".$lang['rem_crlf']."</label><br/>";
2065 echo "<label><input type='checkbox' checked='checked' name='export_csv_fieldnames'/> ".$lang['put_fld']."</label>";
2066 echo "</fieldset>";
2067
2068 echo "<div style='clear:both;'></div>";
2069 echo "<br/><br/>";
2070 echo "<fieldset><legend><b>".$lang['save_as']."</b></legend>";
2071 $file = pathinfo($db->getPath());
2072 $name = $file['filename'];
2073 echo "<input type='text' name='filename' value='".htmlencode($name)."_".htmlencode($target_table)."_".date("Y-m-d").".dump' style='width:400px;'/> <input type='submit' name='export' value='".$lang['export']."' class='btn'/>";
2074 echo "</fieldset>";
2075 echo "</form>";
2076 echo "<div class='confirm' style='margin-top: 2em'>".sprintf($lang['backup_hint'], "<a href='?download=".urlencode($currentDB['path'])."' title='".$lang['backup']."'>".$lang["backup_hint_linktext"]."</a>")."</div>";
2077 break;
2078
2079 //- Import table (=table_import)
2080 case "table_import":
2081 if(isset($_POST['import']))
2082 {
2083 echo "<div class='confirm'>";
2084 if($importSuccess===true)
2085 echo $lang['import_suc'];
2086 else
2087 echo $lang['err'].': '.$importSuccess;
2088 echo "</div><br/>";
2089 }
2090 echo "<form method='post' action='?table=".urlencode($target_table)."&action=table_import' enctype='multipart/form-data'>";
2091 echo "<fieldset style='float:left; width:260px; margin-right:20px;'><legend><b>".$lang['import_into']." ".htmlencode($target_table)."</b></legend>";
2092 echo "<label><input type='radio' name='import_type' checked='checked' value='sql' onclick='toggleImports(\"sql\");'/> ".$lang['sql']."</label>";
2093 echo "<br/><label><input type='radio' name='import_type' value='csv' onclick='toggleImports(\"csv\");'/> ".$lang['csv']."</label>";
2094 echo "</fieldset>";
2095
2096 echo "<fieldset style='float:left; max-width:350px;' id='importoptions_sql'><legend><b>".$lang['options']."</b></legend>";
2097 echo $lang['no_opt'];
2098 echo "</fieldset>";
2099
2100 echo "<fieldset style='float:left; max-width:350px; display:none;' id='importoptions_csv'><legend><b>".$lang['options']."</b></legend>";
2101 echo "<input type='hidden' value='".htmlencode($target_table)."' name='single_table'/>";
2102 echo "<div style='float:left;'>".$lang['fld_terminated']."</div>";
2103 echo "<input type='text' value=';' name='import_csv_fieldsterminated' style='float:right;'/>";
2104 echo "<div style='clear:both;'>";
2105 echo "<div style='float:left;'>".$lang['fld_enclosed']."</div>";
2106 echo "<input type='text' value='\"' name='import_csv_fieldsenclosed' style='float:right;'/>";
2107 echo "<div style='clear:both;'>";
2108 echo "<div style='float:left;'>".$lang['fld_escaped']."</div>";
2109 echo "<input type='text' value='\' name='import_csv_fieldsescaped' style='float:right;'/>";
2110 echo "<div style='clear:both;'>";
2111 echo "<div style='float:left;'>".$lang['rep_null']."</div>";
2112 echo "<input type='text' value='NULL' name='import_csv_replacenull' style='float:right;'/>";
2113 echo "<div style='clear:both;'>";
2114 echo "<label><input type='checkbox' checked='checked' name='import_csv_fieldnames'/> ".$lang['fld_names']."</label>";
2115 echo "</fieldset>";
2116
2117 echo "<div style='clear:both;'></div>";
2118 echo "<br/><br/>";
2119
2120 echo "<fieldset><legend><b>".$lang['import_f']."</b></legend>";
2121 echo "<input type='file' value='".$lang['choose_f']."' name='file' style='background-color:transparent; border-style:none;'/> <input type='submit' value='".$lang['import']."' name='import' class='btn'/>";
2122 echo "</fieldset>";
2123 break;
2124
2125 //- Rename table (=table_rename)
2126 case "table_rename":
2127 echo "<form action='?action=table_rename&confirm=1' method='post'>";
2128 echo "<input type='hidden' name='oldname' value='".htmlencode($target_table)."'/>";
2129 printf($lang['rename_tbl'], htmlencode($target_table));
2130 echo " <input type='text' name='newname' style='width:200px;'/> <input type='submit' value='".$lang['rename']."' name='rename' class='btn'/>";
2131 echo "</form>";
2132 break;
2133
2134 //- Search table (=table_search)
2135 case "table_search":
2136 $searchValues = array();
2137 if(isset($_GET['done']))
2138 {
2139 $query = "PRAGMA table_info(".$db->quote_id($target_table).")";
2140 $result = $db->selectArray($query);
2141 $primary_key = $db->getPrimaryKey($target_table);
2142 $j = 0;
2143 $arr = array();
2144 for($i=0; $i<sizeof($result); $i++)
2145 {
2146 $field = $result[$i][1];
2147 $field_index = str_replace(" ","_",$field);
2148 $operator = $_POST[$field_index.":operator"];
2149 $value = $_POST[$field_index];
2150 if($value!="" || $operator=="!= ''" || $operator=="= ''" || $operator == 'IS NULL' || $operator == 'IS NOT NULL')
2151 {
2152 if($operator=="= ''" || $operator=="!= ''" || $operator == 'IS NULL' || $operator == 'IS NOT NULL')
2153 $arr[$j] = $db->quote_id($field)." ".$operator;
2154 else{
2155 if($operator == "LIKE%"){
2156 $operator = "LIKE";
2157 if(!preg_match('/(^%)|(%$)/', $value)) $value = '%'.$value.'%';
2158 $searchValues[$field] = array($value);
2159 $value_quoted = $db->quote($value);
2160 }
2161 elseif($operator == 'IN' || $operator == 'NOT IN')
2162 {
2163 $value = trim($value, '() ');
2164 $values = explode(',',$value);
2165 $values = array_map('trim', $values, array_fill(0,count($values),' \'"'));
2166 if($operator == 'IN')
2167 $searchValues[$field] = $values;
2168 $values = array_map([$db, 'quote'], $values);
2169 $value_quoted = '(' .implode(', ', $values) . ')';
2170 }
2171 else
2172 {
2173 $searchValues[$field] = array($value);
2174 $value_quoted = $db->quote($value);
2175 }
2176 $arr[$j] = $db->quote_id($field)." ".$operator." ".$value_quoted;
2177 }
2178 $j++;
2179 }
2180 }
2181 $query = "SELECT *";
2182 // select the primary key column(s) last (ROWID if there is no PK).
2183 // this will be used to identify rows, e.g. when editing/deleting rows
2184 $primary_key = $db->getPrimaryKey($target_table);
2185 foreach($primary_key as $pk)
2186 {
2187 $query.= ', '.$db->quote_id($pk);
2188 $query.= ', typeof('.$db->quote_id($pk).')';
2189 }
2190 $query .= " FROM ".$db->quote_id($target_table);
2191 $whereTo = '';
2192 if(sizeof($arr)>0)
2193 {
2194 $whereTo .= " WHERE ".$arr[0];
2195 for($i=1; $i<sizeof($arr); $i++)
2196 {
2197 $whereTo .= " AND ".$arr[$i];
2198 }
2199 }
2200 $query .= $whereTo;
2201 $query_disp = "SELECT * FROM " . $db->quote_id($target_table) . $whereTo;
2202 $queryTimer = new MicroTimer();
2203 $arr = $db->selectArray($query);
2204 $queryTimer->stop();
2205
2206 echo "<div class='confirm'>";
2207 echo "<b>";
2208 if($arr!==false)
2209 {
2210 $affected = sizeof($arr);
2211 echo $lang['showing']." ".$affected." ".$lang['rows'].". ";
2212 printf($lang['query_time'], $queryTimer);
2213 echo "</b><br/>";
2214 }
2215 else
2216 {
2217 echo $lang['err'].": ".$db->getError().".</b><br/>".$lang['bug_report'].' '.PROJECT_BUGTRACKER_LINK.'<br/>';
2218 }
2219 echo "<span style='font-size:11px;'>".htmlencode($query_disp)."</span>";
2220 echo "</div><br/>";
2221
2222 if(sizeof($arr)>0)
2223 {
2224 if($target_table_type == 'view')
2225 {
2226 echo sprintf($lang['readonly_tbl'], htmlencode($target_table))." <a href='http://en.wikipedia.org/wiki/View_(database)' target='_blank'>http://en.wikipedia.org/wiki/View_(database)</a>";
2227 echo "<br/><br/>";
2228 }
2229
2230 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
2231 echo "<tr>";
2232 if($target_table_type == 'table')
2233 {
2234 echo "<td colspan='2' class='tdheader' style='text-align:center'>";
2235 #todo: make sure the search keywords are kept
2236 #echo "<a href='?action=table_search&done=1&table=".$target_table."&fulltexts=".($_SESSION[COOKIENAME.'fulltexts']?0:1)."' title='".$lang[($_SESSION[COOKIENAME.'fulltexts']?'no_full_texts':'full_texts')]."'>";
2237 #echo "<b>&".($_SESSION[COOKIENAME.'fulltexts']?'r':'l')."arr;</b> T <b>&".($_SESSION[COOKIENAME.'fulltexts']?'l':'r')."arr;</b></a>";
2238 echo "</td>";
2239 }
2240
2241 $header = array();
2242 for($j=0; $j<sizeof($result); $j++)
2243 {
2244 $headers[$j]=$result[$j]['name'];
2245 echo "<td class='tdheader'>";
2246 echo htmlencode($headers[$j]);
2247 echo "</td>";
2248 }
2249 echo "</tr>";
2250
2251 $pkFirstCol = sizeof($result)+1;
2252 for($j=0; $j<sizeof($arr); $j++)
2253 {
2254 // -g-> $pk will always be the last columns in each row of the array because we are doing "SELECT *, PK_1, typeof(PK_1), PK2, typeof(PK_2), ... FROM ..."
2255 $pk_arr = array();
2256 for($col = $pkFirstCol; array_key_exists($col, $arr[$j]); $col=$col+2)
2257 {
2258 // in $col we have the type and in $col-1 the value
2259 if($arr[$j][$col]=='integer' || $arr[$j][$col]=='real')
2260 // json encode as int or float, not string
2261 $pk_arr[] = $arr[$j][$col-1]+0;
2262 else
2263 // encode as json string
2264 $pk_arr[] = $arr[$j][$col-1];
2265 }
2266 $pk = json_encode($pk_arr);
2267 $tdWithClass = "<td class='td".($j%2 ? "1" : "2")."'>";
2268 echo "<tr>";
2269 if($target_table_type == 'table')
2270 {
2271 echo $tdWithClass."<a href='?table=".urlencode($target_table)."&action=row_editordelete&pk=".urlencode($pk)."&type=edit' title='".$lang['edit']."' class='edit'><span>".$lang['edit']."</span></a></td>";
2272 echo $tdWithClass."<a href='?table=".urlencode($target_table)."&action=row_editordelete&pk=".urlencode($pk)."&type=delete' title='".$lang['del']."' class='delete'><span>".$lang['del']."</span></a></td>";
2273 }
2274 for($z=0; $z<sizeof($result); $z++)
2275 {
2276 echo $tdWithClass;
2277 $fldResult = $arr[$j][$headers[$z]];
2278 if(isset($searchValues[$headers[$z]]) && is_array($searchValues[$headers[$z]]))
2279 {
2280 foreach($searchValues[$headers[$z]] as $searchValue)
2281 {
2282 $foundVal = str_replace('%', '', $searchValue);
2283 $fldResult = str_ireplace($foundVal, '[fnd]'.$foundVal.'[/fnd]', $fldResult);
2284 // we replace with [fnd] first because we need to htmlencode _afterwards_ without breaking the found-markers
2285 // htmlencoing _before_ would mean we might highlight stuff inside of htmlcode thus breaking it
2286 }
2287 }
2288 echo str_replace(array('[fnd]', '[/fnd]'), array('<u class="found">', '</u>'), htmlencode($fldResult));
2289 echo "</td>";
2290 }
2291 echo "</tr>";
2292 }
2293 echo "</table><br/><br/>";
2294 }
2295
2296 echo "<a href='?table=".urlencode($target_table)."&action=table_search'>".$lang['srch_again']."</a>";
2297 }
2298 else
2299 {
2300 $query = "PRAGMA table_info(".$db->quote_id($target_table).")";
2301 $result = $db->selectArray($query);
2302
2303 echo "<form action='?table=".urlencode($target_table)."&action=table_search&done=1' method='post'>";
2304
2305 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
2306 echo "<tr>";
2307 echo "<td class='tdheader'>".$lang['fld']."</td>";
2308 echo "<td class='tdheader'>".$lang['type']."</td>";
2309 echo "<td class='tdheader'>".$lang['operator']."</td>";
2310 echo "<td class='tdheader'>".$lang['val']."</td>";
2311 echo "</tr>";
2312
2313 for($i=0; $i<sizeof($result); $i++)
2314 {
2315 $field = $result[$i][1];
2316 $type = $result[$i]['type'];
2317 $typeAffinity = get_type_affinity($type);
2318 $tdWithClass = "<td class='td".($i%2 ? "1" : "2")."'>";
2319 $tdWithClassLeft = "<td class='td".($i%2 ? "1" : "2")."' style='text-align:left;'>";
2320 echo "<tr>";
2321 echo $tdWithClassLeft;
2322 echo htmlencode($field);
2323 echo "</td>";
2324 echo $tdWithClassLeft;
2325 echo htmlencode($type);
2326 echo "</td>";
2327 echo $tdWithClassLeft;
2328 echo "<select name='".htmlencode($field).":operator' onchange='checkLike(\"".htmlencode($field)."_search\", this.options[this.selectedIndex].value); '>";
2329 echo "<option value='='>=</option>";
2330 if($typeAffinity=="INTEGER" || $typeAffinity=="REAL" || $typeAffinity=="NUMERIC")
2331 {
2332 echo "<option value='>'>></option>";
2333 echo "<option value='>='>>=</option>";
2334 echo "<option value='<'><</option>";
2335 echo "<option value='<='><=</option>";
2336 }
2337 else if($typeAffinity=="TEXT" || $typeAffinity=="NONE")
2338 {
2339 echo "<option value='= '''>= ''</option>";
2340 echo "<option value='!= '''>!= ''</option>";
2341 }
2342 echo "<option value='!='>!=</option>";
2343 if($typeAffinity=="TEXT" || $typeAffinity=="NONE")
2344 echo "<option value='LIKE' selected='selected'>LIKE</option>";
2345 else
2346 echo "<option value='LIKE'>LIKE</option>";
2347 echo "<option value='LIKE%'>LIKE %...%</option>";
2348 echo "<option value='NOT LIKE'>NOT LIKE</option>";
2349 echo "<option value='IN'>IN (..., ...)</option>";
2350 echo "<option value='NOT IN'>NOT IN (..., ...)</option>";
2351 echo "<option value='IS NULL'>IS NULL</option>";
2352 echo "<option value='IS NOT NULL'>IS NOT NULL</option>";
2353 echo "</select>";
2354 echo "</td>";
2355 echo $tdWithClassLeft;
2356 if($typeAffinity=="INTEGER" || $typeAffinity=="REAL" || $typeAffinity=="NUMERIC")
2357 echo "<input type='text' id='".htmlencode($field)."_search' name='".htmlencode($field)."'/>";
2358 else
2359 echo "<textarea id='".htmlencode($field)."_search' name='".htmlencode($field)."' rows='1' cols='60'></textarea>";
2360 echo "</td>";
2361 echo "</tr>";
2362 }
2363 echo "<tr>";
2364 echo "<td class='tdheader' style='text-align:right;' colspan='4'>";
2365 echo "<input type='submit' value='".$lang['srch']."' class='btn'/>";
2366 echo "</td>";
2367 echo "</tr>";
2368 echo "</table>";
2369 echo "</form>";
2370 }
2371 break;
2372
2373 //- Row actions
2374
2375 //- View row (=row_view)
2376 case "row_view":
2377 if(!isset($_POST['startRow']))
2378 $_POST['startRow'] = 0;
2379
2380 if(isset($_POST['numRows']))
2381 $_SESSION[COOKIENAME.'numRows'] = $_POST['numRows'];
2382
2383 if(!isset($_SESSION[COOKIENAME.'numRows']))
2384 $_SESSION[COOKIENAME.'numRows'] = $rowsNum;
2385
2386 if(isset($_GET['fulltexts']))
2387 $_SESSION[COOKIENAME.'fulltexts'] = $_GET['fulltexts'];
2388
2389 if(!isset($_SESSION[COOKIENAME.'fulltexts']))
2390 $_SESSION[COOKIENAME.'fulltexts'] = false;
2391
2392 if(isset($_SESSION[COOKIENAME.'currentTable']) && $_SESSION[COOKIENAME.'currentTable']!=$target_table)
2393 {
2394 unset($_SESSION[COOKIENAME.'sortRows']);
2395 unset($_SESSION[COOKIENAME.'orderRows']);
2396 }
2397 if(isset($_POST['viewtype']))
2398 {
2399 $_SESSION[COOKIENAME.'viewtype'] = $_POST['viewtype'];
2400 }
2401
2402 $rowCount = $db->numRows($target_table);
2403 $lastPage = intval($rowCount / $_SESSION[COOKIENAME.'numRows']);
2404 $remainder = intval($rowCount % $_SESSION[COOKIENAME.'numRows']);
2405 if($remainder==0)
2406 $remainder = $_SESSION[COOKIENAME.'numRows'];
2407
2408 //- HTML: pagination buttons
2409 echo "<div style=''>";
2410 //previous button
2411 if($_POST['startRow']>0)
2412 {
2413 echo "<div style='float:left;'>";
2414 echo "<form action='?action=row_view&table=".urlencode($target_table)."' method='post'>";
2415 echo "<input type='hidden' name='startRow' value='0'/>";
2416 echo "<input type='hidden' name='numRows' value='".$_SESSION[COOKIENAME.'numRows']."'/> ";
2417 echo "<input type='submit' value='←←' name='previous' class='btn'/> ";
2418 echo "</form>";
2419 echo "</div>";
2420 echo "<div style='float:left; overflow:hidden; margin-right:20px;'>";
2421 echo "<form action='?action=row_view&table=".urlencode($target_table)."' method='post'>";
2422 echo "<input type='hidden' name='startRow' value='".max(0,intval($_POST['startRow']-$_SESSION[COOKIENAME.'numRows']))."'/>";
2423 echo "<input type='hidden' name='numRows' value='".$_SESSION[COOKIENAME.'numRows']."'/> ";
2424 echo "<input type='submit' value='←' name='previous_full' class='btn'/> ";
2425 echo "</form>";
2426 echo "</div>";
2427 }
2428
2429 //show certain number buttons
2430 echo "<div style='float:left;'>";
2431 echo "<form action='?action=row_view&table=".urlencode($target_table)."' method='post'>";
2432 echo "<input type='submit' value='".$lang['show']." : ' name='show' class='btn'/> ";
2433 echo "<input type='text' name='numRows' style='width:50px;' value='".$_SESSION[COOKIENAME.'numRows']."'/> ";
2434 echo $lang['rows_records'];
2435
2436 if(intval($_POST['startRow']+$_SESSION[COOKIENAME.'numRows']) < $rowCount)
2437 echo "<input type='text' name='startRow' style='width:90px;' value='".intval($_POST['startRow']+$_SESSION[COOKIENAME.'numRows'])."'/>";
2438 else
2439 echo "<input type='text' name='startRow' style='width:90px;' value='0'/> ";
2440 echo $lang['as_a'];
2441 echo " <select name='viewtype'>";
2442 if(!isset($_SESSION[COOKIENAME.'viewtype']) || $_SESSION[COOKIENAME.'viewtype']=="table")
2443 {
2444 echo "<option value='table' selected='selected'>".$lang['tbl']."</option>";
2445 echo "<option value='chart'>".$lang['chart']."</option>";
2446 }
2447 else
2448 {
2449 echo "<option value='table'>".$lang['tbl']."</option>";
2450 echo "<option value='chart' selected='selected'>".$lang['chart']."</option>";
2451 }
2452 echo "</select>";
2453 echo "</form>";
2454 echo "</div>";
2455
2456 //next button
2457 if(intval($_POST['startRow']+$_SESSION[COOKIENAME.'numRows'])<$rowCount)
2458 {
2459 echo "<div style='float:left; margin-left:20px; '>";
2460 echo "<form action='?action=row_view&table=".urlencode($target_table)."' method='post'>";
2461 echo "<input type='hidden' name='startRow' value='".intval($_POST['startRow']+$_SESSION[COOKIENAME.'numRows'])."'/>";
2462 echo "<input type='hidden' name='numRows' value='".$_SESSION[COOKIENAME.'numRows']."'/> ";
2463 echo "<input type='submit' value='→' name='next' class='btn'/> ";
2464 echo "</form>";
2465 echo "</div>";
2466 echo "<div style='float:left; '>";
2467 echo "<form action='?action=row_view&table=".urlencode($target_table)."' method='post'>";
2468 echo "<input type='hidden' name='startRow' value='".intval($rowCount-$remainder)."'/>";
2469 echo "<input type='hidden' name='numRows' value='".$_SESSION[COOKIENAME.'numRows']."'/> ";
2470 echo "<input type='submit' value='→→' name='next_full' class='btn'/> ";
2471 echo "</form>";
2472 echo "</div>";
2473 }
2474 echo "<div style='clear:both;'></div>";
2475 echo "</div>";
2476
2477 //- Query execution
2478 if(!isset($_GET['sort']))
2479 $_GET['sort'] = NULL;
2480 if(!isset($_GET['order']))
2481 $_GET['order'] = NULL;
2482
2483 $numRows = $_SESSION[COOKIENAME.'numRows'];
2484 $startRow = $_POST['startRow'];
2485 if(isset($_GET['sort']))
2486 {
2487 $_SESSION[COOKIENAME.'sortRows'] = $_GET['sort'];
2488 $_SESSION[COOKIENAME.'currentTable'] = $target_table;
2489 }
2490 if(isset($_GET['order']))
2491 {
2492 $_SESSION[COOKIENAME.'orderRows'] = $_GET['order'];
2493 $_SESSION[COOKIENAME.'currentTable'] = $target_table;
2494 }
2495 $_SESSION[COOKIENAME.'numRows'] = $numRows;
2496 $query = "SELECT * ";
2497 // select the primary key column(s) last (ROWID if there is no PK).
2498 // this will be used to identify rows, e.g. when editing/deleting rows
2499 $primary_key = $db->getPrimaryKey($target_table);
2500 foreach($primary_key as $pk)
2501 {
2502 $query.= ', '.$db->quote_id($pk);
2503 $query.= ', typeof('.$db->quote_id($pk).')';
2504 }
2505 $query .= " FROM ".$db->quote_id($target_table);
2506 $queryDisp = "SELECT * FROM ".$db->quote_id($target_table);
2507 $queryCount = "SELECT MIN(COUNT(*),".$numRows.") AS count FROM ".$db->quote_id($target_table);
2508 $queryAdd = "";
2509 if(isset($_SESSION[COOKIENAME.'sortRows']))
2510 $queryAdd .= " ORDER BY ".$db->quote_id($_SESSION[COOKIENAME.'sortRows']);
2511 if(isset($_SESSION[COOKIENAME.'orderRows']))
2512 $queryAdd .= " ".$_SESSION[COOKIENAME.'orderRows'];
2513 $queryAdd .= " LIMIT ".$startRow.", ".$numRows;
2514 $query .= $queryAdd;
2515 $queryDisp .= $queryAdd;
2516
2517 $resultRows = $db->select($queryCount)['count'];
2518
2519 //- Show results
2520 if($resultRows>0)
2521 {
2522 $queryTimer = new MicroTimer();
2523 $table_result = $db->query($query);
2524 $queryTimer->stop();
2525
2526
2527 echo "<br/><div class='confirm'>";
2528 echo "<b>".$lang['showing_rows']." ".$startRow." - ".($startRow + $resultRows-1).", ".$lang['total'].": ".$rowCount." ";
2529 printf($lang['query_time'], $queryTimer);
2530 echo "</b><br/>";
2531 echo "<span style='font-size:11px;'>".htmlencode($queryDisp)."</span>";
2532 echo "</div><br/>";
2533
2534 if($target_table_type == 'view')
2535 {
2536 echo sprintf($lang['readonly_tbl'], htmlencode($target_table))." <a href='http://en.wikipedia.org/wiki/View_(database)' target='_blank'>http://en.wikipedia.org/wiki/View_(database)</a>";
2537 echo "<br/><br/>";
2538 }
2539
2540 $query = "PRAGMA table_info(".$db->quote_id($target_table).")";
2541 $result = $db->selectArray($query);
2542 $pkFirstCol = sizeof($result)+1;
2543 //- Table view
2544 if(!isset($_SESSION[COOKIENAME.'viewtype']) || $_SESSION[COOKIENAME.'viewtype']=="table")
2545 {
2546 echo "<form action='?action=row_editordelete&table=".urlencode($target_table)."' method='post' name='checkForm'>";
2547 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
2548 echo "<tr>";
2549 if($target_table_type == 'table')
2550 {
2551 echo "<td colspan='3' class='tdheader' style='text-align:center'>";
2552 echo "<a href='?action=row_view&table=".$target_table."&fulltexts=".($_SESSION[COOKIENAME.'fulltexts']?0:1)."' title='".$lang[($_SESSION[COOKIENAME.'fulltexts']?'no_full_texts':'full_texts')]."'>";
2553 echo "<b>&".($_SESSION[COOKIENAME.'fulltexts']?'r':'l')."arr;</b> T <b>&".($_SESSION[COOKIENAME.'fulltexts']?'l':'r')."arr;</b></a>";
2554 echo "</td>";
2555 }
2556
2557 for($i=0; $i<sizeof($result); $i++)
2558 {
2559 echo "<td class='tdheader'>";
2560 echo "<a href='?action=row_view&table=".urlencode($target_table)."&sort=".urlencode($result[$i]['name']);
2561 if(isset($_SESSION[COOKIENAME.'sortRows']))
2562 $orderTag = ($_SESSION[COOKIENAME.'sortRows']==$result[$i]['name'] && $_SESSION[COOKIENAME.'orderRows']=="ASC") ? "DESC" : "ASC";
2563 else
2564 $orderTag = "ASC";
2565 echo "&order=".$orderTag;
2566 echo "'>".htmlencode($result[$i]['name'])."</a>";
2567 if(isset($_SESSION[COOKIENAME.'sortRows']) && $_SESSION[COOKIENAME.'sortRows']==$result[$i]['name'])
2568 echo (($_SESSION[COOKIENAME.'orderRows']=="ASC") ? " <b>↑</b>" : " <b>↓</b>");
2569 echo "</td>";
2570 }
2571 echo "</tr>";
2572
2573 for($i=0; $row = $db->fetch($table_result); $i++)
2574 {
2575 // -g-> $pk will always be the last columns in each row of the array because we are doing "SELECT *, PK_1, typeof(PK_1), PK2, typeof(PK_2), ... FROM ..."
2576 $pk_arr = array();
2577 for($col = $pkFirstCol; array_key_exists($col, $row); $col=$col+2)
2578 {
2579 // in $col we have the type and in $col-1 the value
2580 if($row[$col]=='integer' || $row[$col]=='real')
2581 // json encode as int or float, not string
2582 $pk_arr[] = $row[$col-1]+0;
2583 else
2584 // encode as json string
2585 $pk_arr[] = $row[$col-1];
2586 }
2587 $pk = json_encode($pk_arr);
2588 $tdWithClass = "<td class='td".($i%2 ? "1" : "2")."'>";
2589 $tdWithClassLeft = "<td class='td".($i%2 ? "1" : "2")."' style='text-align:left;'>";
2590 echo "<tr>";
2591 if($target_table_type == 'table')
2592 {
2593 echo $tdWithClass;
2594 echo "<input type='checkbox' name='check[]' value='".htmlencode($pk)."' id='check_".htmlencode($i)."'/>";
2595 echo "</td>";
2596 echo $tdWithClass;
2597 // -g-> Here, we need to put the PK in as the link for both the edit and delete.
2598 echo "<a href='?table=".urlencode($target_table)."&action=row_editordelete&pk=".urlencode($pk)."&type=edit' title='".$lang['edit']."' class='edit'><span>".$lang['edit']."</span></a>";
2599 echo "</td>";
2600 echo $tdWithClass;
2601 echo "<a href='?table=".urlencode($target_table)."&action=row_editordelete&pk=".urlencode($pk)."&type=delete' title='".$lang['del']."' class='delete'><span>".$lang['del']."</span></a>";
2602 echo "</td>";
2603 }
2604 for($j=0; $j<sizeof($result); $j++)
2605 {
2606 $typeAffinity = get_type_affinity($result[$j]['type']);
2607 if($typeAffinity=="INTEGER" || $typeAffinity=="REAL" || $typeAffinity=="NUMERIC")
2608 echo $tdWithClass;
2609 else
2610 echo $tdWithClassLeft;
2611 if($row[$j]==="")
2612 echo " ";
2613 elseif($row[$j]===NULL)
2614 echo "<i class='null'>NULL</i>";
2615 else
2616 echo htmlencode(subString($row[$j]));
2617 echo "</td>";
2618 }
2619 echo "</tr>";
2620 }
2621 echo "</table>";
2622 if($target_table_type == 'table')
2623 {
2624 echo "<a onclick='checkAll()'>".$lang['chk_all']."</a> / <a onclick='uncheckAll()'>".$lang['unchk_all']."</a> <i>".$lang['with_sel'].":</i> ";
2625 echo "<select name='type'>";
2626 echo "<option value='edit'>".$lang['edit']."</option>";
2627 echo "<option value='delete'>".$lang['del']."</option>";
2628 echo "</select> ";
2629 echo "<input type='submit' value='".$lang['go']."' name='massGo' class='btn'/>";
2630 }
2631 echo "</form>";
2632 }
2633 else
2634 //- Chart view
2635 {
2636 if(!isset($_SESSION[COOKIENAME.$target_table.'chartlabels']))
2637 {
2638 // No label-column set. Try to pick a text-column as label-column.
2639 for($i=0; $i<sizeof($result); $i++)
2640 {
2641 if(get_type_affinity($result[$i]['type'])=='TEXT')
2642 {
2643 $_SESSION[COOKIENAME.$target_table.'chartlabels'] = $i;
2644 break;
2645 }
2646 }
2647 }
2648 if(!isset($_SESSION[COOKIENAME.'chartlabels']))
2649 // no text column found, use the first column
2650 $_SESSION[COOKIENAME.'chartlabels'] = 0;
2651
2652 if(!isset($_SESSION[COOKIENAME.$target_table.'chartvalues']))
2653 {
2654 // No value-column set. Pick the first numeric column if possible.
2655 // If not possible, pick the first column that is not the label-column.
2656
2657 $potential_value_column = null;
2658 for($i=0; $i<sizeof($result); $i++)
2659 {
2660 if($potential_value_column===null && $i != $_SESSION[COOKIENAME.$target_table.'chartlabels'])
2661 // the first column (of any type) that is not the label-column
2662 $potential_value_column = $i;
2663 // check if the col is numeric
2664 $typeAffinity = get_type_affinity($result[$i]['type']);
2665 if($typeAffinity=='INTEGER' || $typeAffinity=='REAL' || $typeAffinity=='NUMERIC')
2666 {
2667 // this is defined as a numeric column, so prefer this as a value column over $potential_value_column
2668 $_SESSION[COOKIENAME.$target_table.'chartvalues'] = $i;
2669 break;
2670 }
2671 }
2672 if(!isset($_SESSION[COOKIENAME.$target_table.'chartvalues']))
2673 {
2674 // we did not find a numeric column
2675 if($potential_value_column!==null)
2676 // use the $potential_value_column, i.e. the second column which is not the label-column
2677 $_SESSION[COOKIENAME.$target_table.'chartvalues'] = $potential_value_column;
2678 else
2679 // it's hopeless, there is only 1 column
2680 $_SESSION[COOKIENAME.$target_table.'chartvalues'] = 0;
2681 }
2682 }
2683
2684 if(!isset($_SESSION[COOKIENAME.'charttype']))
2685 $_SESSION[COOKIENAME.'charttype'] = 'bar';
2686
2687 if(isset($_POST['chartsettings']))
2688 {
2689 $_SESSION[COOKIENAME.'charttype'] = $_POST['charttype'];
2690 $_SESSION[COOKIENAME.$target_table.'chartlabels'] = $_POST['chartlabels'];
2691 $_SESSION[COOKIENAME.$target_table.'chartvalues'] = $_POST['chartvalues'];
2692 }
2693 //- Chart javascript code
2694 ?>
2695 <script type='text/javascript' src='https://www.google.com/jsapi'></script>
2696 <script type='text/javascript'>
2697 google.load('visualization', '1.0', {'packages':['corechart']});
2698 google.setOnLoadCallback(drawChart);
2699 function drawChart()
2700 {
2701 var data = new google.visualization.DataTable();
2702 data.addColumn('string', '<?php echo $result[$_SESSION[COOKIENAME.$target_table.'chartlabels']]['name']; ?>');
2703 data.addColumn('number', '<?php echo $result[$_SESSION[COOKIENAME.$target_table.'chartvalues']]['name']; ?>');
2704 data.addRows([
2705 <?php
2706 for($i=0; $row = $db->fetch($table_result); $i++)
2707 {
2708 $label = str_replace("'", "", htmlencode($row[$_SESSION[COOKIENAME.$target_table.'chartlabels']]));
2709 $value = htmlencode($row[$_SESSION[COOKIENAME.$target_table.'chartvalues']]);
2710
2711 if($value==NULL || $value=="")
2712 $value = 0;
2713
2714 echo "['".$label."', ".$value."]";
2715 if($i<$resultRows-1)
2716 echo ",";
2717 }
2718 $height = ($resultRows+1) * 30;
2719 if($height>1000)
2720 $height = 1000;
2721 else if($height<300)
2722 $height = 300;
2723 if($_SESSION[COOKIENAME.'charttype']=="pie")
2724 $height = 800;
2725 ?>
2726 ]);
2727 var chartWidth = document.getElementById("main_column").offsetWidth - document.getElementById("chartsettingsbox").offsetWidth - 100;
2728 if(chartWidth>1000)
2729 chartWidth = 1000;
2730
2731 var options =
2732 {
2733 'width':chartWidth,
2734 'height':<?php echo $height; ?>,
2735 'title':'<?php echo $result[$_SESSION[COOKIENAME.$target_table.'chartlabels']]['name']." vs ".$result[$_SESSION[COOKIENAME.$target_table.'chartvalues']]['name']; ?>'
2736 };
2737 <?php
2738 if($_SESSION[COOKIENAME.'charttype']=="bar")
2739 echo "var chart = new google.visualization.BarChart(document.getElementById('chart_div'));";
2740 else if($_SESSION[COOKIENAME.'charttype']=="pie")
2741 echo "var chart = new google.visualization.PieChart(document.getElementById('chart_div'));";
2742 else
2743 echo "var chart = new google.visualization.LineChart(document.getElementById('chart_div'));";
2744 ?>
2745 chart.draw(data, options);
2746 }
2747 </script>
2748 <div id="chart_div" style="float:left;"><?php echo $lang['no_chart']; ?></div>
2749 <?php
2750 echo "<fieldset style='float:right; text-align:center;' id='chartsettingsbox'><legend><b>Chart Settings</b></legend>";
2751 echo "<form action='?action=row_view&table=".urlencode($target_table)."' method='post'>";
2752 echo $lang['chart_type'].": <select name='charttype'>";
2753 echo "<option value='bar'";
2754 if($_SESSION[COOKIENAME.'charttype']=="bar")
2755 echo " selected='selected'";
2756 echo ">".$lang['chart_bar']."</option>";
2757 echo "<option value='pie'";
2758 if($_SESSION[COOKIENAME.'charttype']=="pie")
2759 echo " selected='selected'";
2760 echo ">".$lang['chart_pie']."</option>";
2761 echo "<option value='line'";
2762 if($_SESSION[COOKIENAME.'charttype']=="line")
2763 echo " selected='selected'";
2764 echo ">".$lang['chart_line']."</option>";
2765 echo "</select>";
2766 echo "<br/><br/>";
2767 echo $lang['lbl'].": <select name='chartlabels'>";
2768 for($i=0; $i<sizeof($result); $i++)
2769 {
2770 if(isset($_SESSION[COOKIENAME.$target_table.'chartlabels']) && $_SESSION[COOKIENAME.$target_table.'chartlabels']==$i)
2771 echo "<option value='".$i."' selected='selected'>".htmlencode($result[$i]['name'])."</option>";
2772 else
2773 echo "<option value='".$i."'>".htmlencode($result[$i]['name'])."</option>";
2774 }
2775 echo "</select>";
2776 echo "<br/><br/>";
2777 echo $lang['val'].": <select name='chartvalues'>";
2778 for($i=0; $i<sizeof($result); $i++)
2779 {
2780 if(isset($_SESSION[COOKIENAME.$target_table.'chartvalues']) && $_SESSION[COOKIENAME.$target_table.'chartvalues']==$i)
2781 echo "<option value='".$i."' selected='selected'>".htmlencode($result[$i]['name'])."</option>";
2782 else
2783 echo "<option value='".$i."'>".htmlencode($result[$i]['name'])."</option>";
2784 }
2785 echo "</select>";
2786 echo "<br/><br/>";
2787 echo "<input type='submit' name='chartsettings' value='".$lang['update']."' class='btn'/>";
2788 echo "</form>";
2789 echo "</fieldset>";
2790 echo "<div style='clear:both;'></div>";
2791 //end chart view
2792 }
2793 }
2794 else if($rowCount>0)//no rows - do nothing
2795 {
2796 echo "<br/><br/>".$lang['no_rows'];
2797 }
2798 elseif($target_table_type == 'table')
2799 {
2800 echo "<br/><br/>".$lang['empty_tbl']." <a href='?table=".urlencode($target_table)."&action=row_create'>".$lang['click']."</a> ".$lang['insert_rows'];
2801 }
2802
2803 break;
2804
2805 //- Create new row (=row_create)
2806 case "row_create":
2807 $fieldStr = "";
2808 echo "<form action='?table=".urlencode($target_table)."&action=row_create' method='post'>";
2809 echo $lang['restart_insert'];
2810 echo " <select name='num'>";
2811 for($i=1; $i<=40; $i++)
2812 {
2813 if(isset($_POST['num']) && $_POST['num']==$i)
2814 echo "<option value='".$i."' selected='selected'>".$i."</option>";
2815 else
2816 echo "<option value='".$i."'>".$i."</option>";
2817 }
2818 echo "</select> ";
2819 echo $lang['rows'];
2820 echo " <input type='submit' value='".$lang['go']."' class='btn'/>";
2821 echo "</form>";
2822 echo "<br/>";
2823 $query = "PRAGMA table_info(".$db->quote_id($target_table).")";
2824 $result = $db->selectArray($query);
2825 echo "<form action='?table=".urlencode($target_table)."&action=row_create&confirm=1' method='post'>";
2826 if(isset($_POST['num']))
2827 $num = $_POST['num'];
2828 else
2829 $num = 1;
2830 echo "<input type='hidden' name='numRows' value='".$num."'/>";
2831 for($j=0; $j<$num; $j++)
2832 {
2833 if($j>0)
2834 echo "<label><input type='checkbox' value='ignore' name='".$j.":ignore' id='row_".$j."_ignore' checked='checked'/> ".$lang['ignore']."</label><br/>";
2835 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
2836 echo "<tr>";
2837 echo "<td class='tdheader'>".$lang['fld']."</td>";
2838 echo "<td class='tdheader'>".$lang['type']."</td>";
2839 echo "<td class='tdheader'>".$lang['func']."</td>";
2840 echo "<td class='tdheader'>Null</td>";
2841 echo "<td class='tdheader'>".$lang['val']."</td>";
2842 echo "</tr>";
2843
2844 for($i=0; $i<sizeof($result); $i++)
2845 {
2846 $field = $result[$i]['name'];
2847 if($j==0)
2848 $fieldStr .= ":".$field;
2849 $type = strtolower($result[$i]['type']);
2850 $typeAffinity = get_type_affinity($type);
2851 $tdWithClass = "<td class='td".($i%2 ? "1" : "2")."'>";
2852 $tdWithClassLeft = "<td class='td".($i%2 ? "1" : "2")."' style='text-align:left;'>";
2853 echo "<tr>";
2854 echo $tdWithClassLeft;
2855 echo htmlencode($field);
2856 echo "</td>";
2857 echo $tdWithClassLeft;
2858 echo htmlencode($type);
2859 echo "</td>";
2860 echo $tdWithClassLeft;
2861 echo "<select name='function_".$j."_".$i."' onchange='notNull(\"row_".$j."_field_".$i."_null\");'>";
2862 echo "<option value=''> </option>";
2863 foreach (array_merge($sqlite_functions, $custom_functions) as $f) {
2864 echo "<option value='".htmlencode($f)."'>".htmlencode($f)."</option>";
2865 }
2866 echo "</select>";
2867 echo "</td>";
2868 //we need to have a column dedicated to nulls -di
2869 echo $tdWithClassLeft;
2870 if($result[$i]['notnull']==0)
2871 {
2872 if($result[$i]['dflt_value']==="NULL")
2873 echo "<input type='checkbox' name='".$j.":".$i."_null' id='row_".$j."_field_".$i."_null' checked='checked' onclick='disableText(this, \"row_".$j."_field_".$i."_value\");'/>";
2874 else
2875 echo "<input type='checkbox' name='".$j.":".$i."_null' id='row_".$j."_field_".$i."_null' onclick='disableText(this, \"row_".$j."_field_".$i."_value\");'/>";
2876 }
2877 echo "</td>";
2878 echo $tdWithClassLeft;
2879 if($result[$i]['dflt_value'] === "NULL")
2880 $dflt_value = "";
2881 else
2882 $dflt_value = htmlencode(deQuoteSQL($result[$i]['dflt_value']));
2883
2884 if($typeAffinity=="INTEGER" || $typeAffinity=="REAL" || $typeAffinity=="NUMERIC")
2885 echo "<input type='text' id='row_".$j."_field_".$i."_value' name='".$j.":".$i."' value='".$dflt_value."' onblur='changeIgnore(this, \"row_".$j."_ignore\");' onclick='notNull(\"row_".$j."_field_".$i."_null\");'/>";
2886 else
2887 echo "<textarea id='row_".$j."_field_".$i."_value' name='".$j.":".$i."' rows='5' cols='60' onclick='notNull(\"row_".$j."_field_".$i."_null\");' onblur='changeIgnore(this, \"row_".$j."_ignore\");'>".$dflt_value."</textarea>";
2888 echo "</td>";
2889 echo "</tr>";
2890 }
2891 echo "<tr>";
2892 echo "<td class='tdheader' style='text-align:right;' colspan='5'>";
2893 echo "<input type='submit' value='".$lang['insert']."' class='btn'/>";
2894 echo "</td>";
2895 echo "</tr>";
2896 echo "</table><br/>";
2897 }
2898 $fieldStr = substr($fieldStr, 1);
2899 echo "<input type='hidden' name='fields' value='".htmlencode($fieldStr)."'/>";
2900 echo "</form>";
2901 break;
2902
2903 //- Edit or delete row (=row_editordelete)
2904 case "row_editordelete":
2905 if(isset($_POST['check']))
2906 $pks = $_POST['check'];
2907 else if(isset($_GET['pk']))
2908 $pks = array($_GET['pk']);
2909 else $pks[0] = "";
2910 $str = $pks[0];
2911 for($i=1; $i<sizeof($pks); $i++)
2912 {
2913 $str .= ", ".$pks[$i];
2914 }
2915 if($str=="") //nothing was selected so show an error
2916 {
2917 echo "<div class='confirm'>";
2918 echo $lang['err'].": ".$lang['no_sel'];
2919 echo "</div>";
2920 echo "<br/><br/><a href='?table=".urlencode($target_table)."&action=row_view'>".$lang['return']."</a>";
2921 }
2922 else
2923 {
2924 if((isset($_POST['type']) && $_POST['type']=="edit") || (isset($_GET['type']) && $_GET['type']=="edit")) //edit
2925 {
2926 echo "<form action='?table=".urlencode($target_table)."&action=row_edit&confirm=1&pk=".urlencode(json_encode($pks))."' method='post'>";
2927 $query = "PRAGMA table_info(".$db->quote_id($target_table).")";
2928 $result = $db->selectArray($query);
2929
2930 //build the POST array of fields
2931 $fieldStr = $result[0][1];
2932 for($j=1; $j<sizeof($result); $j++)
2933 $fieldStr .= ":".$result[$j][1];
2934
2935 $primary_key = $db->getPrimaryKey($target_table);
2936
2937 echo "<input type='hidden' name='fieldArray' value='".htmlencode($fieldStr)."'/>";
2938
2939 for($j=0; $j<sizeof($pks); $j++)
2940 {
2941 $query = "SELECT * FROM ".$db->quote_id($target_table)." WHERE " . $db->wherePK($target_table, json_decode($pks[$j]));
2942 $result1 = $db->select($query);
2943
2944 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
2945 echo "<tr>";
2946 echo "<td class='tdheader'>".$lang['fld']."</td>";
2947 echo "<td class='tdheader'>".$lang['type']."</td>";
2948 echo "<td class='tdheader'>".$lang['func']."</td>";
2949 echo "<td class='tdheader'>Null</td>";
2950 echo "<td class='tdheader'>".$lang['val']."</td>";
2951 echo "</tr>";
2952
2953 for($i=0; $i<sizeof($result); $i++)
2954 {
2955 $field = $result[$i][1];
2956 $type = $result[$i]['type'];
2957 $typeAffinity = get_type_affinity($type);
2958 $value = $result1[$i];
2959 $tdWithClass = "<td class='td".($i%2 ? "1" : "2")."'>";
2960 $tdWithClassLeft = "<td class='td".($i%2 ? "1" : "2")."' style='text-align:left;'>";
2961 echo "<tr>";
2962 echo $tdWithClass;
2963 echo htmlencode($field);
2964 echo "</td>";
2965 echo $tdWithClass;
2966 echo htmlencode($type);
2967 echo "</td>";
2968 echo $tdWithClassLeft;
2969 echo "<select name='function_".$i."[]' onchange='notNull(\"".$j.":".$i."_null\");'>";
2970 echo "<option value=''></option>";
2971 foreach (array_merge($sqlite_functions, $custom_functions) as $f) {
2972 echo "<option value='".htmlencode($f)."'>".htmlencode($f)."</option>";
2973 }
2974 echo "</select>";
2975 echo "</td>";
2976 echo $tdWithClassLeft;
2977 if($result[$i][3]==0)
2978 {
2979 if($value===NULL)
2980 echo "<input type='checkbox' name='".$i."_null[]' id='".$j.":".$i."_null' checked='checked'/>";
2981 else
2982 echo "<input type='checkbox' name='".$i."_null[]' id='".$j.":".$i."_null'/>";
2983 }
2984 echo "</td>";
2985 echo $tdWithClassLeft;
2986 if($typeAffinity=="INTEGER" || $typeAffinity=="REAL" || $typeAffinity=="NUMERIC")
2987 echo "<input type='text' name='".$i."[]' value='".htmlencode($value)."' onblur='changeIgnore(this, \"".$j."\", \"".$j.":".$i."_null\")' />";
2988 else
2989 echo "<textarea name='".$i."[]' rows='1' cols='60' class='".htmlencode($field)."_textarea' onblur='changeIgnore(this, \"".$j."\", \"".$j.":".$i."_null\")'>".htmlencode($value)."</textarea>";
2990 echo "</td>";
2991 echo "</tr>";
2992 }
2993 echo "<tr>";
2994 echo "<td class='tdheader' style='text-align:right;' colspan='5'>";
2995 // Note: the 'Save changes' button must be first in the code so it is the one used when submitting the form with the Enter key (issue #215)
2996 echo "<input type='submit' value='".$lang['save_ch']."' class='btn'/> ";
2997 echo "<input type='submit' name='new_row' value='".$lang['new_insert']."' class='btn'/> ";
2998 echo "<a href='?table=".urlencode($target_table)."&action=row_view'>".$lang['cancel']."</a>";
2999 echo "</td>";
3000 echo "</tr>";
3001 echo "</table>";
3002 echo "<br/>";
3003 }
3004 echo "</form>";
3005 }
3006 else //delete
3007 {
3008 echo "<form action='?table=".urlencode($target_table)."&action=row_delete&confirm=1&pk=".urlencode(json_encode($pks))."' method='post'>";
3009 echo "<div class='confirm'>";
3010 printf($lang['ques_del_rows'], htmlencode($str), htmlencode($target_table));
3011 echo "<br/><br/>";
3012 echo "<input type='submit' value='".$lang['confirm']."' class='btn'/> ";
3013 echo "<a href='?table=".urlencode($target_table)."&action=row_view'>".$lang['cancel']."</a>";
3014 echo "</div>";
3015 }
3016 }
3017 break;
3018
3019 //- Column actions
3020
3021 //- View table structure (=column_view)
3022 case "column_view":
3023 $query = "PRAGMA table_info(".$db->quote_id($target_table).")";
3024 $result = $db->selectArray($query);
3025
3026 echo "<form action='?table=".urlencode($target_table)."&action=column_confirm' method='post' name='checkForm'>";
3027 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
3028 echo "<tr>";
3029 if($target_table_type == 'table')
3030 echo "<td colspan='3'></td>";
3031 echo "<td class='tdheader'>".$lang['col']." #</td>";
3032 echo "<td class='tdheader'>".$lang['fld']."</td>";
3033 echo "<td class='tdheader'>".$lang['type']."</td>";
3034 echo "<td class='tdheader'>".$lang['not_null']."</td>";
3035 echo "<td class='tdheader'>".$lang['def_val']."</td>";
3036 echo "<td class='tdheader'>".$lang['prim_key']."</td>";
3037 echo "</tr>";
3038
3039 $noPrimaryKey = true;
3040
3041 for($i=0; $i<sizeof($result); $i++)
3042 {
3043 $colVal = $result[$i][0];
3044 $fieldVal = $result[$i][1];
3045 $typeVal = $result[$i]['type'];
3046 $notnullVal = $result[$i][3];
3047 $defaultVal = $result[$i][4];
3048 $primarykeyVal = $result[$i][5];
3049
3050 if(intval($notnullVal)!=0)
3051 $notnullVal = $lang['yes'];
3052 else
3053 $notnullVal = $lang['no'];
3054 if(intval($primarykeyVal)!=0)
3055 {
3056 $primarykeyVal = $lang['yes'];
3057 $noPrimaryKey = false;
3058 }
3059 else
3060 $primarykeyVal = $lang['no'];
3061
3062 $tdWithClass = "<td class='td".($i%2 ? "1" : "2")."'>";
3063 $tdWithClassLeft = "<td class='td".($i%2 ? "1" : "2")."' style='text-align:left;'>";
3064 echo "<tr>";
3065 if($target_table_type == 'table')
3066 {
3067 echo $tdWithClass;
3068 echo "<input type='checkbox' name='check[]' value='".htmlencode($fieldVal)."' id='check_".$i."'/>";
3069 echo "</td>";
3070 echo $tdWithClass;
3071 echo "<a href='?table=".urlencode($target_table)."&action=column_edit&pk=".urlencode($fieldVal)."' title='".$lang['edit']."' class='edit'><span>".$lang['edit']."</span></a>";
3072 echo "</td>";
3073 echo $tdWithClass;
3074 echo "<a href='?table=".urlencode($target_table)."&action=column_confirm&action2=column_delete&pk=".urlencode($fieldVal)."' title='".$lang['del']."' class='delete'><span>".$lang['del']."</span></a>";
3075 echo "</td>";
3076 }
3077 echo $tdWithClass;
3078 echo htmlencode($colVal);
3079 echo "</td>";
3080 echo $tdWithClassLeft;
3081 echo htmlencode($fieldVal);
3082 echo "</td>";
3083 echo $tdWithClassLeft;
3084 echo htmlencode($typeVal);
3085 echo "</td>";
3086 echo $tdWithClassLeft;
3087 echo htmlencode($notnullVal);
3088 echo "</td>";
3089 echo $tdWithClassLeft;
3090 if($defaultVal===NULL)
3091 echo "<i class='null'>".$lang['none']."</i>";
3092 elseif($defaultVal==="NULL")
3093 echo "<i class='null'>NULL</i>";
3094 else
3095 echo htmlencode($defaultVal);
3096 echo "</td>";
3097 echo $tdWithClassLeft;
3098 echo htmlencode($primarykeyVal);
3099 echo "</td>";
3100 echo "</tr>";
3101 }
3102
3103 echo "</table>";
3104 if($target_table_type == 'table')
3105 {
3106 echo "<a onclick='checkAll()'>".$lang['chk_all']."</a> / <a onclick='uncheckAll()'>".$lang['unchk_all']."</a> <i>".$lang['with_sel'].":</i> ";
3107 echo "<select name='action2'>";
3108 //echo "<option value='edit'>".$lang['edit']."</option>";
3109 echo "<option value='column_delete'>".$lang['del']."</option>";
3110 if($noPrimaryKey)
3111 echo "<option value='primarykey_add'>".$lang['prim_key']."</option>";
3112 echo "</select> ";
3113 echo "<input type='submit' value='".$lang['go']."' name='massGo' class='btn'/>";
3114 }
3115 echo "</form>";
3116 if($target_table_type == 'table')
3117 {
3118 echo "<br/>";
3119 echo "<form action='?table=".urlencode($target_table)."&action=column_create' method='post'>";
3120 echo "<input type='hidden' name='tablename' value='".htmlencode($target_table)."'/>";
3121 echo $lang['add']." <input type='text' name='tablefields' style='width:30px;' value='1'/> ".$lang['tbl_end']." <input type='submit' value='".$lang['go']."' name='addfields' class='btn'/>";
3122 echo "</form>";
3123 }
3124
3125 $query = "SELECT sql FROM sqlite_master WHERE name=".$db->quote($target_table);
3126 $master = $db->selectArray($query);
3127
3128 echo "<br/>";
3129 echo "<br/>";
3130 echo "<div class='confirm'>";
3131 echo "<b>".$lang['query_used_'.$target_table_type]."</b><br/>";
3132 echo "<span style='font-size:11px;'>".htmlencode($master[0]['sql'])."</span>";
3133 echo "</div>";
3134 echo "<br/>";
3135 if($target_table_type != 'view')
3136 {
3137 echo "<br/><hr/><br/>";
3138 //$query = "SELECT * FROM sqlite_master WHERE type='index' AND tbl_name='".$target_table."'";
3139 $query = "PRAGMA index_list(".$db->quote_id($target_table).")";
3140 $result = $db->selectArray($query);
3141 if(sizeof($result)>0)
3142 {
3143 echo "<h2>".$lang['indexes'].":</h2>";
3144 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
3145 echo "<tr>";
3146 echo "<td colspan='1'>";
3147 echo "</td>";
3148 echo "<td class='tdheader'>".$lang['name']."</td>";
3149 echo "<td class='tdheader'>".$lang['unique']."</td>";
3150 echo "<td class='tdheader'>".$lang['seq_no']."</td>";
3151 echo "<td class='tdheader'>".$lang['col']." #</td>";
3152 echo "<td class='tdheader'>".$lang['fld']."</td>";
3153 echo "</tr>";
3154 for($i=0; $i<sizeof($result); $i++)
3155 {
3156 if($result[$i]['unique']==0)
3157 $unique = $lang['no'];
3158 else
3159 $unique = $lang['yes'];
3160
3161 $query = "PRAGMA index_info(".$db->quote_id($result[$i]['name']).")";
3162 $info = $db->selectArray($query);
3163 $span = sizeof($info);
3164
3165 $tdWithClass = "<td class='td".($i%2 ? "1" : "2")."'>";
3166 $tdWithClassLeft = "<td class='td".($i%2 ? "1" : "2")."' style='text-align:left;'>";
3167 $tdWithClassSpan = "<td class='td".($i%2 ? "1" : "2")."' rowspan='".$span."'>";
3168 $tdWithClassLeftSpan = "<td class='td".($i%2 ? "1" : "2")."' style='text-align:left;' rowspan='".$span."'>";
3169 echo "<tr>";
3170 echo $tdWithClassSpan;
3171 echo "<a href='?table=".urlencode($target_table)."&action=index_delete&pk=".urlencode($result[$i]['name'])."' title='".$lang['del']."' class='delete'><span>".$lang['del']."</span></a>";
3172 echo "</td>";
3173 echo $tdWithClassLeftSpan;
3174 echo $result[$i]['name'];
3175 echo "</td>";
3176 echo $tdWithClassLeftSpan;
3177 echo $unique;
3178 echo "</td>";
3179 for($j=0; $j<$span; $j++)
3180 {
3181 if($j!=0)
3182 echo "<tr>";
3183 echo $tdWithClassLeft;
3184 echo htmlencode($info[$j]['seqno']);
3185 echo "</td>";
3186 echo $tdWithClassLeft;
3187 echo htmlencode($info[$j]['cid']);
3188 echo "</td>";
3189 echo $tdWithClassLeft;
3190 echo htmlencode($info[$j]['name']);
3191 echo "</td>";
3192 echo "</tr>";
3193 }
3194 }
3195 echo "</table><br/><br/>";
3196 }
3197
3198 $query = "SELECT * FROM sqlite_master WHERE type='trigger' AND tbl_name=".$db->quote($target_table)." ORDER BY name";
3199 $result = $db->selectArray($query);
3200 //print_r($result);
3201 if(sizeof($result)>0)
3202 {
3203 echo "<h2>".$lang['triggers'].":</h2>";
3204 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
3205 echo "<tr>";
3206 echo "<td colspan='1'>";
3207 echo "</td>";
3208 echo "<td class='tdheader'>".$lang['name']."</td>";
3209 echo "<td class='tdheader'>".$lang['sql']."</td>";
3210 echo "</tr>";
3211 for($i=0; $i<sizeof($result); $i++)
3212 {
3213 $tdWithClass = "<td class='td".($i%2 ? "1" : "2")."'>";
3214 echo "<tr>";
3215 echo $tdWithClass;
3216 echo "<a href='?table=".urlencode($target_table)."&action=trigger_delete&pk=".urlencode($result[$i]['name'])."' title='".$lang['del']."' class='delete'><span>".$lang['del']."</span></a>";
3217 echo "</td>";
3218 echo $tdWithClass;
3219 echo htmlencode($result[$i]['name']);
3220 echo "</td>";
3221 echo $tdWithClass;
3222 echo htmlencode($result[$i]['sql']);
3223 echo "</td>";
3224 }
3225 echo "</table><br/><br/>";
3226 }
3227
3228 echo "<form action='?table=".urlencode($target_table)."&action=index_create' method='post'>";
3229 echo "<input type='hidden' name='tablename' value='".htmlencode($target_table)."'/>";
3230 echo "<br/><div class='tdheader'>";
3231 echo $lang['create_index2']." <input type='text' name='numcolumns' style='width:30px;' value='1'/> ".$lang['cols']." <input type='submit' value='".$lang['go']."' name='addindex' class='btn'/>";
3232 echo "</div>";
3233 echo "</form>";
3234
3235 echo "<form action='?table=".urlencode($target_table)."&action=trigger_create' method='post'>";
3236 echo "<input type='hidden' name='tablename' value='".htmlencode($target_table)."'/>";
3237 echo "<br/><div class='tdheader'>";
3238 echo $lang['create_trigger2']." <input type='submit' value='".$lang['go']."' name='addindex' class='btn'/>";
3239 echo "</div>";
3240 echo "</form>";
3241 }
3242 break;
3243
3244 //- Create column (=column_create)
3245 case "column_create":
3246 echo "<h2>".sprintf($lang['new_fld'],htmlencode($_POST['tablename']))."</h2>";
3247 if($_POST['tablefields']=="" || intval($_POST['tablefields'])<=0)
3248 echo $lang['specify_fields'];
3249 else if($_POST['tablename']=="")
3250 echo $lang['specify_tbl'];
3251 else
3252 {
3253 $num = intval($_POST['tablefields']);
3254 $name = $_POST['tablename'];
3255 echo "<form action='?table=".urlencode($_POST['tablename'])."&action=column_create&confirm=1' method='post'>";
3256 echo "<input type='hidden' name='tablename' value='".htmlencode($name)."'/>";
3257 echo "<input type='hidden' name='rows' value='".$num."'/>";
3258 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
3259 echo "<tr>";
3260 $headings = array($lang["fld"], $lang["type"], $lang["prim_key"]);
3261 if($db->getType() != "SQLiteDatabase") $headings[] = $lang["autoincrement"];
3262 $headings[] = $lang["not_null"];
3263 $headings[] = $lang["def_val"];
3264
3265 for($k=0; $k<count($headings); $k++)
3266 echo "<td class='tdheader'>" . $headings[$k] . "</td>";
3267 echo "</tr>";
3268
3269 for($i=0; $i<$num; $i++)
3270 {
3271 $tdWithClass = "<td class='td" . ($i%2 ? "1" : "2") . "'>";
3272 echo "<tr>";
3273 echo $tdWithClass;
3274 echo "<input type='text' name='".$i."_field' style='width:200px;'/>";
3275 echo "</td>";
3276 echo $tdWithClass;
3277 echo "<select name='".$i."_type' id='i".$i."_type' onchange='toggleAutoincrement(".$i.");'>";
3278 foreach ($sqlite_datatypes as $t) {
3279 echo "<option value='".htmlencode($t)."'>".htmlencode($t)."</option>";
3280 }
3281 echo "</select>";
3282 echo "</td>";
3283 echo $tdWithClass;
3284 echo "<label><input type='checkbox' name='".$i."_primarykey'/> ".$lang['yes']."</label>";
3285 echo "</td>";
3286 if($db->getType() != "SQLiteDatabase")
3287 {
3288 echo $tdWithClass;
3289 echo "<label><input type='checkbox' name='".$i."_autoincrement' id='i".$i."_autoincrement'/> ".$lang['yes']."</label>";
3290 echo "</td>";
3291 }
3292 echo $tdWithClass;
3293 echo "<label><input type='checkbox' name='".$i."_notnull'/> ".$lang['yes']."</label>";
3294 echo "</td>";
3295 echo $tdWithClass;
3296 echo "<select name='".$i."_defaultoption' id='i".$i."_defaultoption' onchange=\"if(this.value!='defined' && this.value!='expr') document.getElementById('i".$i."_defaultvalue').value='';\">";
3297 echo "<option value='none'>".$lang['none']."</option><option value='defined'>".$lang['as_defined'].":</option><option>NULL</option><option>CURRENT_TIME</option><option>CURRENT_DATE</option><option>CURRENT_TIMESTAMP</option><option value='expr'>".$lang['expression'].":</option>";
3298 echo "</select>";
3299 echo "<input type='text' name='".$i."_defaultvalue' id='i".$i."_defaultvalue' style='width:100px;' onchange=\"if(document.getElementById('i".$i."_defaultoption').value!='expr') document.getElementById('i".$i."_defaultoption').value='defined';\"/>";
3300 echo "</td>";
3301 echo "</tr>";
3302 }
3303 echo "<tr>";
3304 echo "<td class='tdheader' style='text-align:right;' colspan='6'>";
3305 echo "<input type='submit' value='".$lang['add_flds']."' class='btn'/> ";
3306 echo "<a href='?table=".urlencode($_POST['tablename'])."&action=column_view'>".$lang['cancel']."</a>";
3307 echo "</td>";
3308 echo "</tr>";
3309 echo "</table>";
3310 echo "</form>";
3311 }
3312 break;
3313
3314 //- Delete column (=column_confirm)
3315 case "column_confirm":
3316 if(isset($_POST['check']))
3317 $pks = $_POST['check'];
3318 elseif(isset($_GET['pk']))
3319 $pks = array($_GET['pk']);
3320 else $pks = array();
3321
3322 if(sizeof($pks)==0) //nothing was selected so show an error
3323 {
3324 echo "<div class='confirm'>";
3325 echo $lang['err'].": ".$lang['no_sel'];
3326 echo "</div>";
3327 echo "<br/><br/><a href='?table=".urlencode($target_table)."&action=column_view'>".$lang['return']."</a>";
3328 }
3329 else
3330 {
3331 $str = $pks[0];
3332 $pkVal = $pks[0];
3333 for($i=1; $i<sizeof($pks); $i++)
3334 {
3335 $str .= ", ".$pks[$i];
3336 $pkVal .= ":".$pks[$i];
3337 }
3338 echo "<form action='?table=".urlencode($target_table)."&action=".$_REQUEST['action2']."&confirm=1&pk=".urlencode($pkVal)."' method='post'>";
3339 echo "<div class='confirm'>";
3340 printf($lang['ques_'.$_REQUEST['action2']], htmlencode($str), htmlencode($target_table));
3341 echo "<br/><br/>";
3342 echo "<input type='submit' value='".$lang['confirm']."' class='btn'/> ";
3343 echo "<a href='?table=".urlencode($target_table)."&action=column_view'>".$lang['cancel']."</a>";
3344 echo "</div>";
3345 }
3346 break;
3347
3348 //- Edit column (=column_edit)
3349 case "column_edit":
3350 echo "<h2>".sprintf($lang['edit_col'], htmlencode($_GET['pk']))." ".$lang['on_tbl']." '".htmlencode($target_table)."'</h2>";
3351 echo $lang['sqlite_limit']."<br/><br/>";
3352 if(!isset($_GET['pk']))
3353 echo $lang['specify_col'];
3354 else if (!$target_table)
3355 echo $lang['specify_tbl'];
3356 else
3357 {
3358 $query = "PRAGMA table_info(".$db->quote_id($target_table).")";
3359 $result = $db->selectArray($query);
3360
3361 for($i=0; $i<sizeof($result); $i++)
3362 {
3363 if($result[$i][1]==$_GET['pk'])
3364 {
3365 $colVal = $result[$i][0];
3366 $fieldVal = $result[$i][1];
3367 $typeVal = $result[$i]['type'];
3368 $notnullVal = $result[$i][3];
3369 $defaultVal = $result[$i][4];
3370 $primarykeyVal = $result[$i][5];
3371 break;
3372 }
3373 }
3374
3375 $name = $target_table;
3376 echo "<form action='?table=".urlencode($name)."&action=column_edit&confirm=1' method='post'>";
3377 echo "<input type='hidden' name='tablename' value='".htmlencode($name)."'/>";
3378 echo "<input type='hidden' name='oldvalue' value='".htmlencode($_GET['pk'])."'/>";
3379 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
3380 echo "<tr>";
3381 //$headings = array("Field", "Type", "Primary Key", "Autoincrement", "Not NULL", "Default Value");
3382 $headings = array($lang["fld"], $lang["type"]);
3383 for($k=0; $k<count($headings); $k++)
3384 echo "<td class='tdheader'>".$headings[$k]."</td>";
3385 echo "</tr>";
3386
3387 $i = 0;
3388 $tdWithClass = "<td class='td" . ($i%2 ? "1" : "2") . "'>";
3389 echo "<tr>";
3390 echo $tdWithClass;
3391 echo "<input type='text' name='".$i."_field' style='width:200px;' value='".htmlencode($fieldVal)."'/>";
3392 echo "</td>";
3393 echo $tdWithClass;
3394 echo "<select name='".$i."_type' id='i".$i."_type' onchange='toggleAutoincrement(".$i.");'>";
3395 if(!in_array($typeVal, $sqlite_datatypes))
3396 echo "<option value='".htmlencode($typeVal)."' selected='selected'>".htmlencode($typeVal)."</option>";
3397 foreach ($sqlite_datatypes as $t) {
3398 if($t==$typeVal)
3399 echo "<option value='".htmlencode($t)."' selected='selected'>".htmlencode($t)."</option>";
3400 else
3401 echo "<option value='".htmlencode($t)."'>".htmlencode($t)."</option>";
3402 }
3403 echo "</select>";
3404 echo "</td>";
3405 /*
3406 echo $tdWithClass;
3407 if($primarykeyVal)
3408 echo "<input type='checkbox' name='".$i."_primarykey' checked='checked'/> Yes";
3409 else
3410 echo "<input type='checkbox' name='".$i."_primarykey'/> Yes";
3411 echo "</td>";
3412 echo $tdWithClass;
3413 if(1==2)
3414 echo "<input type='checkbox' name='".$i."_autoincrement' id='".$i."_autoincrement' checked='checked'/> Yes";
3415 else
3416 echo "<input type='checkbox' name='".$i."_autoincrement' id='".$i."_autoincrement'/> Yes";
3417 echo "</td>";
3418 echo $tdWithClass;
3419 if($notnullVal)
3420 echo "<input type='checkbox' name='".$i."_notnull' checked='checked'/> Yes";
3421 else
3422 echo "<input type='checkbox' name='".$i."_notnull'/> Yes";
3423 echo "</td>";
3424 echo $tdWithClass;
3425 echo "<input type='text' name='".$i."_defaultvalue' value='".$defaultVal."' style='width:100px;'/>";
3426 echo "</td>";
3427 */
3428 echo "</tr>";
3429
3430 echo "<tr>";
3431 echo "<td class='tdheader' style='text-align:right;' colspan='6'>";
3432 echo "<input type='submit' value='".$lang['save_ch']."' class='btn'/> ";
3433 echo "<a href='?table=".urlencode($target_table)."&action=column_view'>".$lang['cancel']."</a>";
3434 echo "</td>";
3435 echo "</tr>";
3436 echo "</table>";
3437 echo "</form>";
3438 }
3439 break;
3440
3441 //- Delete index (=index_delete)
3442 case "index_delete":
3443 echo "<form action='?table=".urlencode($target_table)."&action=index_delete&pk=".urlencode($_GET['pk'])."&confirm=1' method='post'>";
3444 echo "<div class='confirm'>";
3445 echo sprintf($lang['ques_del_index'], htmlencode($_GET['pk']))."<br/><br/>";
3446 echo "<input type='submit' value='".$lang['confirm']."' class='btn'/> ";
3447 echo "<a href='?table=".urlencode($target_table)."&action=column_view'>".$lang['cancel']."</a>";
3448 echo "</div>";
3449 echo "</form>";
3450 break;
3451
3452 //- Delete trigger (=trigger_delete)
3453 case "trigger_delete":
3454 echo "<form action='?table=".urlencode($target_table)."&action=trigger_delete&pk=".urlencode($_GET['pk'])."&confirm=1' method='post'>";
3455 echo "<div class='confirm'>";
3456 echo sprintf($lang['ques_del_trigger'], htmlencode($_GET['pk']))."<br/><br/>";
3457 echo "<input type='submit' value='".$lang['confirm']."' class='btn'/> ";
3458 echo "<a href='?table=".urlencode($target_table)."&action=column_view'>".$lang['cancel']."</a>";
3459 echo "</div>";
3460 echo "</form>";
3461 break;
3462
3463 //- Create trigger (=trigger_create)
3464 case "trigger_create":
3465 echo "<h2>".$lang['create_trigger']." '".htmlencode($_POST['tablename'])."'</h2>";
3466 if($_POST['tablename']=="")
3467 echo $lang['specify_tbl'];
3468 else
3469 {
3470 echo "<form action='?table=".urlencode($_POST['tablename'])."&action=trigger_create&confirm=1' method='post'>";
3471 echo $lang['trigger_name'].": <input type='text' name='trigger_name'/><br/><br/>";
3472 echo "<fieldset><legend>".$lang['db_event']."</legend>";
3473 echo $lang['before']."/".$lang['after'].": ";
3474 echo "<select name='beforeafter'>";
3475 echo "<option value=''></option>";
3476 echo "<option value='BEFORE'>".$lang['before']."</option>";
3477 echo "<option value='AFTER'>".$lang['after']."</option>";
3478 echo "<option value='INSTEAD OF'>".$lang['instead']."</option>";
3479 echo "</select>";
3480 echo "<br/><br/>";
3481 echo $lang['event'].": ";
3482 echo "<select name='event'>";
3483 echo "<option value='DELETE'>".$lang['del']."</option>";
3484 echo "<option value='INSERT'>".$lang['insert']."</option>";
3485 echo "<option value='UPDATE'>".$lang['update']."</option>";
3486 echo "</select>";
3487 echo "</fieldset><br/><br/>";
3488 echo "<fieldset><legend>".$lang['trigger_act']."</legend>";
3489 echo "<label><input type='checkbox' name='foreachrow'/> ".$lang['each_row']."</label><br/><br/>";
3490 echo $lang['when_exp'].":<br/>";
3491 echo "<textarea name='whenexpression' style='width:500px; height:100px;' rows='8' cols='50'></textarea>";
3492 echo "<br/><br/>";
3493 echo $lang['trigger_step'].":<br/>";
3494 echo "<textarea name='triggersteps' style='width:500px; height:100px;' rows='8' cols='50'></textarea>";
3495 echo "</fieldset><br/><br/>";
3496 echo "<input type='submit' value='".$lang['create_trigger2']."' class='btn'/> ";
3497 echo "<a href='?table=".urlencode($_POST['tablename'])."&action=column_view'>".$lang['cancel']."</a>";
3498 echo "</form>";
3499 }
3500 break;
3501
3502 //- Create index (=index_create)
3503 case "index_create":
3504 echo "<h2>".$lang['create_index']." '".htmlencode($_POST['tablename'])."'</h2>";
3505 if($_POST['numcolumns']=="" || intval($_POST['numcolumns'])<=0)
3506 echo $lang['specify_fields'];
3507 else if($_POST['tablename']=="")
3508 echo $lang['specify_tbl'];
3509 else
3510 {
3511 echo "<form action='?table=".urlencode($_POST['tablename'])."&action=index_create&confirm=1' method='post'>";
3512 $num = intval($_POST['numcolumns']);
3513 $query = "PRAGMA table_info(".$db->quote_id($_POST['tablename']).")";
3514
3515 $result = $db->selectArray($query);
3516 echo "<fieldset><legend>".$lang['define_index']."</legend>";
3517 echo "<label for='index_name'>".$lang['index_name'].":</label> <input type='text' name='name' id='index_name'/><br/>";
3518 echo "<label for='index_duplicate'>".$lang['dup_val'].":</label>";
3519 echo "<select name='duplicate' id='index_duplicate'>";
3520 echo "<option value='yes'>".$lang['allow']."</option>";
3521 echo "<option value='no'>".$lang['not_allow']."</option>";
3522 echo "</select><br/>";
3523 if(version_compare($db->getSQLiteVersion(),'3.8.0')>=0)
3524 echo "<label for='index_where'>WHERE:</label> <input type='text' name='where' id='index_where'/> ".helpLink($lang['help10']);
3525 echo "</fieldset>";
3526 echo "<br/>";
3527 echo "<fieldset><legend>".$lang['define_in_col']."</legend>";
3528 for($i=0; $i<$num; $i++)
3529 {
3530 echo "<select name='".$i."_field'>";
3531 echo "<option value=''>--".$lang['ignore']."--</option>";
3532 for($j=0; $j<sizeof($result); $j++)
3533 echo "<option value='".htmlencode($result[$j][1])."'>".htmlencode($result[$j][1])."</option>";
3534 echo "</select> ";
3535 echo "<select name='".$i."_order'>";
3536 echo "<option value=''></option>";
3537 echo "<option value=' ASC'>".$lang['asc']."</option>";
3538 echo "<option value=' DESC'>".$lang['desc']."</option>";
3539 echo "</select><br/>";
3540 }
3541 echo "</fieldset>";
3542 echo "<br/><br/>";
3543 echo "<input type='hidden' name='num' value='".$num."'/>";
3544 echo "<input type='submit' value='".$lang['create_index1']."' class='btn'/> ";
3545 echo "<a href='?table=".urlencode($_POST['tablename'])."&action=column_view'>".$lang['cancel']."</a>";
3546 echo "</form>";
3547 }
3548 break;
3549 }
3550 echo "</div>";
3551}
3552
3553$view = "structure";
3554
3555//- HMTL: tabs for databases
3556if(!$target_table && !isset($_GET['confirm']) && (!isset($_GET['action']) || (isset($_GET['action']) && $_GET['action']!="table_create"))) //the absence of these fields means we are viewing the database homepage
3557{
3558 $view = isset($_GET['view']) ? $_GET['view'] : 'structure';
3559
3560 echo "<a href='?view=structure' ";
3561 if($view=="structure")
3562 echo "class='tab_pressed'";
3563 else
3564 echo "class='tab'";
3565 echo ">".$lang['struct']."</a>";
3566 echo "<a href='?view=sql' ";
3567 if($view=="sql")
3568 echo "class='tab_pressed'";
3569 else
3570 echo "class='tab'";
3571 echo ">".$lang['sql']."</a>";
3572 echo "<a href='?view=export' ";
3573 if($view=="export")
3574 echo "class='tab_pressed'";
3575 else
3576 echo "class='tab'";
3577 echo ">".$lang['export']."</a>";
3578 echo "<a href='?view=import' ";
3579 if($view=="import")
3580 echo "class='tab_pressed'";
3581 else
3582 echo "class='tab'";
3583 echo ">".$lang['import']."</a>";
3584 echo "<a href='?view=vacuum' ";
3585 if($view=="vacuum")
3586 echo "class='tab_pressed'";
3587 else
3588 echo "class='tab'";
3589 echo ">".$lang['vac']."</a>";
3590 if($directory!==false && is_writable($directory))
3591 {
3592 echo "<a href='?view=rename' ";
3593 if($view=="rename")
3594 echo "class='tab_pressed'";
3595 else
3596 echo "class='tab'";
3597 echo ">".$lang['db_rename']."</a>";
3598
3599 echo "<a href='?view=delete' title='".$lang['db_del']."' ";
3600 if($view=="delete")
3601 echo "class='tab_pressed delete_db'";
3602 else
3603 echo "class='tab delete_db'";
3604 echo "><span>".$lang['db_del']."</span></a>";
3605 }
3606 echo "<div style='clear:both;'></div>";
3607 echo "<div id='main'>";
3608
3609 //- Switch on $view (actually a series of if-else)
3610
3611 if($view=="structure")
3612 {
3613 //- Database structure, shows all the tables (=structure)
3614
3615 if(isset($dbexists))
3616 {
3617 echo "<div class='confirm' style='margin:10px 20px;'>";
3618 echo $lang['err'].': '.sprintf($lang['db_exists'], htmlencode($dbname));
3619 echo "</div><br/>";
3620 }
3621
3622 if($db->isWritable() && !$db->isDirWritable())
3623 {
3624 echo "<div class='confirm' style='margin:10px 20px;'>";
3625 echo $lang['attention'].': '.$lang['directory_not_writable'];
3626 echo "</div><br/>";
3627 }
3628
3629 if(isset($extension_not_allowed))
3630 {
3631 echo "<div class='confirm' style='margin:10px 20px;'>";
3632 echo $lang['extension_not_allowed'].': ';
3633 echo implode(', ', array_map('htmlencode', $allowed_extensions));
3634 echo '<br />'.$lang['add_allowed_extension'];
3635 echo "</div><br/>";
3636 }
3637
3638 if ($auth->isPasswordDefault())
3639 {
3640 echo "<div class='confirm' style='margin:20px 0px;'>";
3641 echo sprintf($lang['warn_passwd'],(is_readable('phpliteadmin.config.php')?'phpliteadmin.config.php':PAGE))."<br />".$lang['warn0'];
3642 echo "</div>";
3643 }
3644
3645 echo "<b>".$lang['db_name']."</b>: ".htmlencode($db->getName())."<br/>";
3646 echo "<b>".$lang['db_path']."</b>: ".htmlencode($db->getPath())."<br/>";
3647 echo "<b>".$lang['db_size']."</b>: ".$db->getSize()." KB<br/>";
3648 echo "<b>".$lang['db_mod']."</b>: ".$db->getDate()."<br/>";
3649 echo "<b>".$lang['sqlite_v']."</b>: ".$db->getSQLiteVersion()."<br/>";
3650 echo "<b>".$lang['sqlite_ext']."</b> ".helpLink($lang['help1']).": ".$db->getType()."<br/>";
3651 echo "<b>".$lang['php_v']."</b>: ".phpversion()."<br/>";
3652 echo "<b>".PROJECT." ".$lang["ver"]."</b>: ".VERSION;
3653 echo " <a href='".PROJECT_URL."' target='_blank' id='oldVersion' style='display: none;' class='warning'>".$lang['new_version']."</a><br/><br/>";
3654 echo "<script type='text/javascript'>checkVersion('".VERSION."','".VERSION_CHECK_URL."');</script>";
3655
3656 if(isset($_GET['sort']) && ($_GET['sort']=='type' || $_GET['sort']=='name'))
3657 $_SESSION[COOKIENAME.'sortTables'] = $_GET['sort'];
3658 if(isset($_GET['order']) && ($_GET['order']=='ASC' || $_GET['order']=='DESC'))
3659 $_SESSION[COOKIENAME.'orderTables'] = $_GET['order'];
3660
3661 $query = "SELECT type, name FROM sqlite_master WHERE (type='table' OR type='view') AND name!='' AND name NOT LIKE 'sqlite_%'";
3662 $queryAdd = "";
3663 if(isset($_SESSION[COOKIENAME.'sortTables']))
3664 $queryAdd .= " ORDER BY ".$db->quote_id($_SESSION[COOKIENAME.'sortTables']);
3665 else
3666 $queryAdd .= " ORDER BY \"name\"";
3667 if(isset($_SESSION[COOKIENAME.'orderTables']))
3668 $queryAdd .= " ".$_SESSION[COOKIENAME.'orderTables'];
3669 $query .= $queryAdd;
3670 $result = $db->selectArray($query);
3671
3672 if(sizeof($result)==0)
3673 echo $lang['no_tbl']."<br/><br/>";
3674 else
3675 {
3676 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
3677 echo "<tr>";
3678
3679 echo "<td class='tdheader'>";
3680 echo "<a href='?sort=type";
3681 if(isset($_SESSION[COOKIENAME.'sortTables']))
3682 $orderTag = ($_SESSION[COOKIENAME.'sortTables']=="type" && $_SESSION[COOKIENAME.'orderTables']=="ASC") ? "DESC" : "ASC";
3683 else
3684 $orderTag = "ASC";
3685 echo "&order=".$orderTag;
3686 echo "'>".$lang['type']."</a> ".helpLink($lang['help3']);
3687 if(isset($_SESSION[COOKIENAME.'sortTables']) && $_SESSION[COOKIENAME.'sortTables']=="type")
3688 echo (($_SESSION[COOKIENAME.'orderTables']=="ASC") ? " <b>↑</b>" : " <b>↓</b>");
3689 echo "</td>";
3690
3691 echo "<td class='tdheader'>";
3692 echo "<a href='?sort=name";
3693 if(isset($_SESSION[COOKIENAME.'sortTables']))
3694 $orderTag = ($_SESSION[COOKIENAME.'sortTables']=="name" && $_SESSION[COOKIENAME.'orderTables']=="ASC") ? "DESC" : "ASC";
3695 else
3696 $orderTag = "ASC";
3697 echo "&order=".$orderTag;
3698 echo "'>".$lang['name']."</a>";
3699 if(isset($_SESSION[COOKIENAME.'sortTables']) && $_SESSION[COOKIENAME.'sortTables']=="name")
3700 echo (($_SESSION[COOKIENAME.'orderTables']=="ASC") ? " <b>↑</b>" : " <b>↓</b>");
3701 echo "</td>";
3702
3703 echo "<td class='tdheader' colspan='10'>".$lang['act']."</td>";
3704 echo "<td class='tdheader'>".$lang['rec']."</td>";
3705 echo "</tr>";
3706
3707 $totalRecords = 0;
3708 $skippedTables = false;
3709 for($i=0; $i<sizeof($result); $i++)
3710 {
3711 $records = $db->numRows($result[$i]['name'], (!isset($_GET['forceCount'])));
3712 if($records == '?')
3713 {
3714 $skippedTables = true;
3715 $records = "<a href='?forceCount=1'>?</a>";
3716 }
3717 else
3718 $totalRecords += $records;
3719 $tdWithClass = "<td class='td".($i%2 ? "1" : "2")."'>";
3720 $tdWithClassLeft = "<td class='td".($i%2 ? "1" : "2")."' style='text-align:left;'>";
3721
3722 if($result[$i]['type']=="table")
3723 {
3724 echo "<tr>";
3725 echo $tdWithClassLeft;
3726 echo $lang['tbl'];
3727 echo "</td>";
3728 echo $tdWithClassLeft;
3729 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=row_view'>".htmlencode($result[$i]['name'])."</a>";
3730 echo "</td>";
3731 echo $tdWithClass;
3732 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=row_view'>".$lang['browse']."</a>";
3733 echo "</td>";
3734 echo $tdWithClass;
3735 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=column_view'>".$lang['struct']."</a>";
3736 echo "</td>";
3737 echo $tdWithClass;
3738 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=table_sql'>".$lang['sql']."</a>";
3739 echo "</td>";
3740 echo $tdWithClass;
3741 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=table_search'>".$lang['srch']."</a>";
3742 echo "</td>";
3743 echo $tdWithClass;
3744 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=row_create'>".$lang['insert']."</a>";
3745 echo "</td>";
3746 echo $tdWithClass;
3747 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=table_export'>".$lang['export']."</a>";
3748 echo "</td>";
3749 echo $tdWithClass;
3750 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=table_import'>".$lang['import']."</a>";
3751 echo "</td>";
3752 echo $tdWithClass;
3753 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=table_rename'>".$lang['rename']."</a>";
3754 echo "</td>";
3755 echo $tdWithClass;
3756 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=table_empty' class='empty'>".$lang['empty']."</a>";
3757 echo "</td>";
3758 echo $tdWithClass;
3759 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=table_drop' class='drop'>".$lang['drop']."</a>";
3760 echo "</td>";
3761 echo $tdWithClass;
3762 echo $records;
3763 echo "</td>";
3764 echo "</tr>";
3765 }
3766 else
3767 {
3768 echo "<tr>";
3769 echo $tdWithClassLeft;
3770 echo "View";
3771 echo "</td>";
3772 echo $tdWithClassLeft;
3773 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=row_view'>".htmlencode($result[$i]['name'])."</a>";
3774 echo "</td>";
3775 echo $tdWithClass;
3776 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=row_view'>".$lang['browse']."</a>";
3777 echo "</td>";
3778 echo $tdWithClass;
3779 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=column_view'>".$lang['struct']."</a>";
3780 echo "</td>";
3781 echo $tdWithClass;
3782 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=table_sql'>".$lang['sql']."</a>";
3783 echo "</td>";
3784 echo $tdWithClass;
3785 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=table_search'>".$lang['srch']."</a>";
3786 echo "</td>";
3787 echo $tdWithClass;
3788 echo "";
3789 echo "</td>";
3790 echo $tdWithClass;
3791 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=table_export'>".$lang['export']."</a>";
3792 echo "</td>";
3793 echo $tdWithClass;
3794 echo "";
3795 echo "</td>";
3796 echo $tdWithClass;
3797 echo "";
3798 echo "</td>";
3799 echo $tdWithClass;
3800 echo "";
3801 echo "</td>";
3802 echo $tdWithClass;
3803 echo "<a href='?table=".urlencode($result[$i]['name'])."&action=view_drop' class='drop'>".$lang['drop']."</a>";
3804 echo "</td>";
3805 echo $tdWithClass;
3806 echo $records;
3807 echo "</td>";
3808 echo "</tr>";
3809 }
3810 }
3811 echo "<tr>";
3812 echo "<td class='tdheader' colspan='12'>".sizeof($result)." ".$lang['total']."</td>";
3813 echo "<td class='tdheader' colspan='1' style='text-align:right;'>".$totalRecords.($skippedTables?" <a href='?forceCount=1'>+ ?</a>":"")."</td>";
3814 echo "</tr>";
3815 echo "</table>";
3816 echo "<br/>";
3817 if($skippedTables)
3818 echo "<div class='confirm' style='margin-bottom:20px;'>".sprintf($lang["counting_skipped"],"<a href='?forceCount=1'>","</a>")."</div>";
3819 }
3820 echo "<fieldset>";
3821 echo "<legend><b>".$lang['create_tbl_db']." '".htmlencode($db->getName())."'</b></legend>";
3822 echo "<form action='?action=table_create' method='post'>";
3823 echo $lang['name'].": <input type='text' name='tablename' style='width:200px;'/> ";
3824 echo $lang['fld_num'].": <input type='text' name='tablefields' style='width:90px;'/> ";
3825 echo "<input type='submit' name='createtable' value='".$lang['go']."' class='btn'/>";
3826 echo "</form>";
3827 echo "</fieldset>";
3828 echo "<br/>";
3829 echo "<fieldset>";
3830 echo "<legend><b>".$lang['create_view']." '".htmlencode($db->getName())."'</b></legend>";
3831 echo "<form action='?action=view_create&confirm=1' method='post'>";
3832 echo $lang['name'].": <input type='text' name='viewname' style='width:200px;'/> ";
3833 echo $lang['sel_state']." ".helpLink($lang['help4']).": <input type='text' name='select' style='width:400px;'/> ";
3834 echo "<input type='submit' name='createtable' value='".$lang['go']."' class='btn'/>";
3835 echo "</form>";
3836 echo "</fieldset>";
3837 }
3838 else if($view=="sql")
3839 {
3840 //- Database SQL editor (=sql)
3841 $isSelect = false;
3842 if(isset($_POST['query']) && $_POST['query']!="")
3843 {
3844 $delimiter = $_POST['delimiter'];
3845 $queryStr = $_POST['queryval'];
3846 //save the queries in history if necessary
3847 if($maxSavedQueries!=0 && $maxSavedQueries!=false)
3848 {
3849 if(!isset($_SESSION['query_history']))
3850 $_SESSION['query_history'] = array();
3851 $_SESSION['query_history'][md5(strtolower($queryStr))] = $queryStr;
3852 if(sizeof($_SESSION['query_history']) > $maxSavedQueries)
3853 array_shift($_SESSION['query_history']);
3854 }
3855 $query = explode_sql($delimiter, $queryStr); //explode the query string into individual queries based on the delimiter
3856
3857 for($i=0; $i<sizeof($query); $i++) //iterate through the queries exploded by the delimiter
3858 {
3859 if(str_replace(" ", "", str_replace("\n", "", str_replace("\r", "", $query[$i])))!="") //make sure this query is not an empty string
3860 {
3861 $queryTimer = new MicroTimer();
3862 $result = $db->selectArray($query[$i], "assoc");
3863 $queryTimer->stop();
3864
3865 echo "<div class='confirm'>";
3866 echo "<b>";
3867
3868 if($result !== NULL)
3869 {
3870
3871 if(sizeof($result)>0 || $db->getAffectedRows()==0)
3872 {
3873 printf($lang['show_rows'], sizeof($result));
3874 }
3875 if($db->getAffectedRows()>0 || sizeof($result)==0)
3876 {
3877 echo $db->getAffectedRows()." ".$lang['rows_aff']." ";
3878 }
3879 printf($lang['query_time'], $queryTimer);
3880 echo "</b><br/>";
3881 }
3882 else
3883 {
3884 echo $lang['err'].": ".$db->getError()."</b><br/>";
3885 }
3886 echo "<span style='font-size:11px;'>".htmlencode($query[$i])."</span>";
3887 echo "</div><br/>";
3888 if(sizeof($result)>0)
3889 {
3890 $headers = array_keys($result[0]);
3891
3892 echo "<table border='0' cellpadding='2' cellspacing='1' class='viewTable'>";
3893 echo "<tr>";
3894 for($j=0; $j<sizeof($headers); $j++)
3895 {
3896 echo "<td class='tdheader'>";
3897 echo htmlencode($headers[$j]);
3898 echo "</td>";
3899 }
3900 echo "</tr>";
3901 for($j=0; $j<sizeof($result); $j++)
3902 {
3903 $tdWithClass = "<td class='td".($j%2 ? "1" : "2")."'>";
3904 echo "<tr>";
3905 for($z=0; $z<sizeof($headers); $z++)
3906 {
3907 echo $tdWithClass;
3908 if($result[$j][$headers[$z]]==="")
3909 echo " ";
3910 elseif($result[$j][$headers[$z]]===NULL)
3911 echo "<i class='null'>NULL</i>";
3912 else
3913 echo htmlencode(subString($result[$j][$headers[$z]]));
3914 echo "</td>";
3915 }
3916 echo "</tr>";
3917 }
3918 echo "</table><br/><br/>";
3919 }
3920 }
3921 }
3922 }
3923 else
3924 {
3925 $delimiter = ";";
3926 $queryStr = "";
3927 }
3928
3929 echo "<fieldset>";
3930 echo "<legend><b>".sprintf($lang['run_sql'],htmlencode($db->getName()))."</b></legend>";
3931 echo "<form action='?view=sql' method='post'>";
3932 if(isset($_SESSION['query_history']) && sizeof($_SESSION['query_history'])>0)
3933 {
3934 echo "<b>".$lang['recent_queries']."</b><ul>";
3935 foreach($_SESSION['query_history'] as $key => $value)
3936 {
3937 echo "<li><a onclick='document.getElementById(\"queryval\").value = this.textContent;' href='#'>".htmlencode($value)."</a></li>";
3938 }
3939 echo "</ul><br/><br/>";
3940 }
3941 echo "<textarea style='width:100%; height:300px;' name='queryval' id='queryval' cols='50' rows='8'>".htmlencode($queryStr)."</textarea>";
3942 echo $lang['delimit']." <input type='text' name='delimiter' value='".htmlencode($delimiter)."' style='width:50px;'/> ";
3943 echo "<input type='submit' name='query' value='".$lang['go']."' class='btn'/>";
3944 echo "</form>";
3945 echo "</fieldset>";
3946 }
3947 else if($view=="vacuum")
3948 {
3949 //- Vacuum database confirmation (=vacuum)
3950 if(isset($_POST['vacuum']))
3951 {
3952 $query = "VACUUM";
3953 $db->query($query);
3954 echo "<div class='confirm'>";
3955 printf($lang['db_vac'], htmlencode($db->getName()));
3956 echo "</div><br/>";
3957 }
3958 echo "<form method='post' action='?view=vacuum'>";
3959 printf($lang['vac_desc'],htmlencode($db->getName()));
3960 echo "<br/><br/>";
3961 echo "<input type='submit' value='".$lang['vac']."' name='vacuum' class='btn'/>";
3962 echo "</form>";
3963 }
3964 else if($view=="export")
3965 {
3966 //- Export view (=export)
3967 echo "<form method='post' action='?view=export'>";
3968 echo "<fieldset style='float:left; width:260px; margin-right:20px;'><legend><b>".$lang['export']."</b></legend>";
3969 echo "<select multiple='multiple' size='10' style='width:240px;' name='tables[]'>";
3970 $query = "SELECT name FROM sqlite_master WHERE type='table' OR type='view' ORDER BY name";
3971 $result = $db->selectArray($query);
3972 for($i=0; $i<sizeof($result); $i++)
3973 {
3974 if(substr($result[$i]['name'], 0, 7)!="sqlite_" && $result[$i]['name']!="")
3975 echo "<option value='".htmlencode($result[$i]['name'])."' selected='selected'>".htmlencode($result[$i]['name'])."</option>";
3976 }
3977 echo "</select>";
3978 echo "<br/><br/>";
3979 echo "<label><input type='radio' name='export_type' checked='checked' value='sql' onclick='toggleExports(\"sql\");'/> ".$lang['sql']."</label>";
3980 echo "<br/><label><input type='radio' name='export_type' value='csv' onclick='toggleExports(\"csv\");'/> ".$lang['csv']."</label>";
3981 echo "</fieldset>";
3982
3983 echo "<fieldset style='float:left; max-width:350px;' id='exportoptions_sql'><legend><b>".$lang['options']."</b></legend>";
3984 echo "<label><input type='checkbox' checked='checked' name='structure'/> ".$lang['export_struct']."</label> ".helpLink($lang['help5'])."<br/>";
3985 echo "<label><input type='checkbox' checked='checked' name='data'/> ".$lang['export_data']."</label> ".helpLink($lang['help6'])."<br/>";
3986 echo "<label><input type='checkbox' name='drop'/> ".$lang['add_drop']."</label> ".helpLink($lang['help7'])."<br/>";
3987 echo "<label><input type='checkbox' checked='checked' name='transaction'/> ".$lang['add_transact']."</label> ".helpLink($lang['help8'])."<br/>";
3988 echo "<label><input type='checkbox' checked='checked' name='comments'/> ".$lang['comments']."</label> ".helpLink($lang['help9'])."<br/>";
3989 echo "</fieldset>";
3990
3991 echo "<fieldset style='float:left; max-width:350px; display:none;' id='exportoptions_csv'><legend><b>".$lang['options']."</b></legend>";
3992 echo "<div style='float:left;'>".$lang['fld_terminated']."</div>";
3993 echo "<input type='text' value=';' name='export_csv_fieldsterminated' style='float:right;'/>";
3994 echo "<div style='clear:both;'>";
3995 echo "<div style='float:left;'>".$lang['fld_enclosed']."</div>";
3996 echo "<input type='text' value='\"' name='export_csv_fieldsenclosed' style='float:right;'/>";
3997 echo "<div style='clear:both;'>";
3998 echo "<div style='float:left;'>".$lang['fld_escaped']."</div>";
3999 echo "<input type='text' value='\' name='export_csv_fieldsescaped' style='float:right;'/>";
4000 echo "<div style='clear:both;'>";
4001 echo "<div style='float:left;'>".$lang['rep_null']."</div>";
4002 echo "<input type='text' value='NULL' name='export_csv_replacenull' style='float:right;'/>";
4003 echo "<div style='clear:both;'>";
4004 echo "<label><input type='checkbox' name='export_csv_crlf'/> ".$lang['rem_crlf']."</label><br/>";
4005 echo "<label><input type='checkbox' checked='checked' name='export_csv_fieldnames'/> ".$lang['put_fld']."</label>";
4006 echo "</fieldset>";
4007
4008 echo "<div style='clear:both;'></div>";
4009 echo "<br/><br/>";
4010 echo "<fieldset><legend><b>".$lang['save_as']."</b></legend>";
4011 $file = pathinfo($db->getPath());
4012 $name = $file['filename'];
4013 echo "<input type='text' name='filename' value='".htmlencode($name)."_".date("Y-m-d").".dump' style='width:400px;'/> <input type='submit' name='export' value='".$lang['export']."' class='btn'/>";
4014 echo "</fieldset>";
4015 echo "</form>";
4016 echo "<div class='confirm' style='margin-top: 2em'>".sprintf($lang['backup_hint'], "<a href='?download=".urlencode($currentDB['path'])."' title='".$lang['backup']."'>".$lang["backup_hint_linktext"]."</a>")."</div>";
4017 }
4018 else if($view=="import")
4019 {
4020 //- Import view (=import)
4021 if(isset($_POST['import']))
4022 {
4023 echo "<div class='confirm'>";
4024 if($importSuccess===true)
4025 echo $lang['import_suc'];
4026 else
4027 echo $importSuccess;
4028 echo "</div><br/>";
4029 }
4030
4031 echo "<form method='post' action='?view=import' enctype='multipart/form-data'>";
4032 echo "<fieldset style='float:left; width:260px; margin-right:20px;'><legend><b>".$lang['import']."</b></legend>";
4033 echo "<label><input type='radio' name='import_type' checked='checked' value='sql' onclick='toggleImports(\"sql\");'/> ".$lang['sql']."</label>";
4034 echo "<br/><label><input type='radio' name='import_type' value='csv' onclick='toggleImports(\"csv\");'/> ".$lang['csv']."</label>";
4035 echo "</fieldset>";
4036
4037 echo "<fieldset style='float:left; max-width:350px;' id='importoptions_sql'><legend><b>".$lang['options']."</b></legend>";
4038 echo $lang['no_opt'];
4039 echo "</fieldset>";
4040
4041 echo "<fieldset style='float:left; max-width:350px; display:none;' id='importoptions_csv'><legend><b>".$lang['options']."</b></legend>";
4042 echo "<div style='float:left;'>".$lang['csv_tbl']."</div>";
4043 echo "<select name='single_table' style='float:right;'>";
4044 $query = "SELECT name FROM sqlite_master WHERE type='table' OR type='view' ORDER BY name";
4045 $result = $db->selectArray($query);
4046 for($i=0; $i<sizeof($result); $i++)
4047 {
4048 if(substr($result[$i]['name'], 0, 7)!="sqlite_" && $result[$i]['name']!="")
4049 echo "<option value='".htmlencode($result[$i]['name'])."'>".htmlencode($result[$i]['name'])."</option>";
4050 }
4051 echo "</select>";
4052 echo "<div style='clear:both;'>";
4053 echo "<div style='float:left;'>".$lang['fld_terminated']."</div>";
4054 echo "<input type='text' value=';' name='import_csv_fieldsterminated' style='float:right;'/>";
4055 echo "<div style='clear:both;'>";
4056 echo "<div style='float:left;'>".$lang['fld_enclosed']."</div>";
4057 echo "<input type='text' value='\"' name='import_csv_fieldsenclosed' style='float:right;'/>";
4058 echo "<div style='clear:both;'>";
4059 echo "<div style='float:left;'>".$lang['fld_escaped']."</div>";
4060 echo "<input type='text' value='\' name='import_csv_fieldsescaped' style='float:right;'/>";
4061 echo "<div style='clear:both;'>";
4062 echo "<div style='float:left;'>".$lang['null_represent']."</div>";
4063 echo "<input type='text' value='NULL' name='import_csv_replacenull' style='float:right;'/>";
4064 echo "<div style='clear:both;'>";
4065 echo "<label><input type='checkbox' checked='checked' name='import_csv_fieldnames'/> ".$lang['fld_names']."</label>";
4066 echo "</fieldset>";
4067
4068 echo "<div style='clear:both;'></div>";
4069 echo "<br/><br/>";
4070
4071 echo "<fieldset><legend><b>".$lang['import_f']."</b></legend>";
4072 echo "<input type='file' value='".$lang['choose_f']."' name='file' style='background-color:transparent; border-style:none;'/> <input type='submit' value='".$lang['import']."' name='import' class='btn'/>";
4073 echo "</fieldset>";
4074 }
4075 else if($view=="rename")
4076 {
4077 //- Rename database confirmation (=rename)
4078 if(isset($extension_not_allowed))
4079 {
4080 echo "<div class='confirm'>";
4081 echo $lang['extension_not_allowed'].': ';
4082 echo implode(', ', array_map('htmlencode', $allowed_extensions));
4083 echo '<br />'.$lang['add_allowed_extension'];
4084 echo "</div><br/>";
4085 }
4086 if(isset($dbexists))
4087 {
4088 echo "<div class='confirm'>";
4089 if($oldpath==$newpath)
4090 echo $lang['err'].": ".$lang['warn_dumbass'];
4091 else{
4092 echo $lang['err'].": ";
4093 printf($lang['db_exists'], htmlencode($newpath));
4094 }
4095 echo "</div><br/>";
4096 }
4097 if(isset($justrenamed))
4098 {
4099 echo "<div class='confirm'>";
4100 printf($lang['db_renamed'], htmlencode($oldpath));
4101 echo " '".htmlencode($newpath)."'.";
4102 echo "</div><br/>";
4103 }
4104 echo "<form action='?view=rename&database_rename=1' method='post'>";
4105 echo "<input type='hidden' name='oldname' value='".htmlencode($db->getPath())."'/>";
4106 echo $lang['db_rename']." '".htmlencode($db->getPath())."' ".$lang['to']." <input type='text' name='newname' style='width:200px;' value='".htmlencode($db->getPath())."'/> <input type='submit' value='".$lang['rename']."' name='rename' class='btn'/>";
4107 echo "</form>";
4108 }
4109 else if($view=="delete")
4110 {
4111 //- Delete database confirmation (=delete)
4112 echo "<form action='?database_delete=1' method='post'>";
4113 echo "<div class='confirm'>";
4114 echo sprintf($lang['ques_del_db'],htmlencode($db->getPath()))."<br/><br/>";
4115 echo "<input name='database_delete' value='".htmlencode($db->getPath())."' type='hidden'/>";
4116 echo "<input type='submit' value='".$lang['confirm']."' class='btn'/> ";
4117 echo "<a href='".PAGE."'>".$lang['cancel']."</a>";
4118 echo "</div>";
4119 echo "</form>";
4120 }
4121
4122 echo "</div>";
4123}
4124
4125//- HTML: page footer
4126echo "<br/>";
4127echo "<span style='font-size:11px;'>".$lang['powered']." <a href='".PROJECT_URL."' target='_blank' style='font-size:11px;'>".PROJECT."</a> | ";
4128echo $lang['free_software']." <a href='".DONATE_URL."' target='_blank' style='font-size:11px;'>".$lang['please_donate']."</a> | ";
4129printf($lang['page_gen'], $pageTimer);
4130echo "</span>";
4131echo "</td></tr></table>";
4132$db->close(); //close the database
4133echo "</body>";
4134echo "</html>";
4135
4136//- End of main code
4137
4138// Authorization class
4139// Maintains user's logged-in state and security of application
4140//
4141class Authorization
4142{
4143 private $authorized;
4144 private $login_failed;
4145 private $system_password_encrypted;
4146
4147 public function __construct()
4148 {
4149 // the salt and password encrypting is probably unnecessary protection but is done just
4150 // for the sake of being very secure
4151 if(!isset($_SESSION[COOKIENAME.'_salt']) && !isset($_COOKIE[COOKIENAME.'_salt']))
4152 {
4153 // create a random salt for this session if a cookie doesn't already exist for it
4154 $_SESSION[COOKIENAME.'_salt'] = self::generateSalt(20);
4155 }
4156 else if(!isset($_SESSION[COOKIENAME.'_salt']) && isset($_COOKIE[COOKIENAME.'_salt']))
4157 {
4158 // session doesn't exist, but cookie does so grab it
4159 $_SESSION[COOKIENAME.'_salt'] = $_COOKIE[COOKIENAME.'_salt'];
4160 }
4161
4162 // salted and encrypted password used for checking
4163 $this->system_password_encrypted = md5(SYSTEMPASSWORD."_".$_SESSION[COOKIENAME.'_salt']);
4164
4165 $this->authorized =
4166 // no password
4167 SYSTEMPASSWORD == ''
4168 // correct password stored in session
4169 || isset($_SESSION[COOKIENAME.'password']) && $_SESSION[COOKIENAME.'password'] == $this->system_password_encrypted
4170 // correct password stored in cookie
4171 || isset($_COOKIE[COOKIENAME]) && isset($_COOKIE[COOKIENAME.'_salt']) && md5(SYSTEMPASSWORD."_".$_COOKIE[COOKIENAME.'_salt']) == $_COOKIE[COOKIENAME];
4172 }
4173
4174 public function attemptGrant($password, $remember)
4175 {
4176 if ($password == SYSTEMPASSWORD) {
4177 if ($remember) {
4178 // user wants to be remembered, so set a cookie
4179 $expire = time()+60*60*24*30; //set expiration to 1 month from now
4180 setcookie(COOKIENAME, $this->system_password_encrypted, $expire, null, null, null, true);
4181 setcookie(COOKIENAME."_salt", $_SESSION[COOKIENAME.'_salt'], $expire, null, null, null, true);
4182 } else {
4183 // user does not want to be remembered, so destroy any potential cookies
4184 setcookie(COOKIENAME, "", time()-86400, null, null, null, true);
4185 setcookie(COOKIENAME."_salt", "", time()-86400, null, null, null, true);
4186 unset($_COOKIE[COOKIENAME]);
4187 unset($_COOKIE[COOKIENAME.'_salt']);
4188 }
4189
4190 $_SESSION[COOKIENAME.'password'] = $this->system_password_encrypted;
4191 $this->authorized = true;
4192 return true;
4193 }
4194
4195 $this->login_failed = true;
4196 return false;
4197 }
4198
4199 public function revoke()
4200 {
4201 //destroy everything - cookies and session vars
4202 setcookie(COOKIENAME, "", time()-86400, null, null, null, true);
4203 setcookie(COOKIENAME."_salt", "", time()-86400, null, null, null, true);
4204 unset($_COOKIE[COOKIENAME]);
4205 unset($_COOKIE[COOKIENAME.'_salt']);
4206 session_unset();
4207 session_destroy();
4208 $this->authorized = false;
4209 }
4210
4211 public function isAuthorized()
4212 {
4213 return $this->authorized;
4214 }
4215
4216 public function isFailedLogin()
4217 {
4218 return $this->login_failed;
4219 }
4220
4221 public function isPasswordDefault()
4222 {
4223 return SYSTEMPASSWORD == 'admin';
4224 }
4225
4226 private static function generateSalt($saltSize)
4227 {
4228 $set = 'ABCDEFGHiJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
4229 $setLast = strlen($set) - 1;
4230 $salt = '';
4231 while ($saltSize-- > 0) {
4232 $salt .= $set[mt_rand(0, $setLast)];
4233 }
4234 return $salt;
4235 }
4236
4237}
4238// Database class
4239// Generic database abstraction class to manage interaction with database without worrying about SQLite vs. PHP versions
4240//
4241class Database
4242{
4243 protected $db; //reference to the DB object
4244 protected $type; //the extension for PHP that handles SQLite
4245 protected $data;
4246 protected $lastResult;
4247 protected $alterError;
4248
4249 public function __construct($data)
4250 {
4251 global $lang;
4252 $this->data = $data;
4253 try
4254 {
4255 if(!file_exists($this->data["path"]) && !is_writable(dirname($this->data["path"]))) //make sure the containing directory is writable if the database does not exist
4256 {
4257 echo "<div class='confirm' style='margin:20px;'>";
4258 printf($lang['db_not_writeable'], htmlencode($this->data["path"]), htmlencode(dirname($this->data["path"])));
4259 echo "<form action='".PAGE."' method='post'>";
4260 echo "<input type='submit' value='Log Out' name='".$lang['logout']."' class='btn'/>";
4261 echo "</form>";
4262 echo "</div><br/>";
4263 exit();
4264 }
4265
4266 $ver = $this->getVersion();
4267
4268 switch(true)
4269 {
4270 case (FORCETYPE=="PDO" || ((FORCETYPE==false || $ver!=-1) && class_exists("PDO") && ($ver==-1 || $ver==3))):
4271 $this->db = new PDO("sqlite:".$this->data['path']);
4272 if($this->db!=NULL)
4273 {
4274 $this->type = "PDO";
4275 break;
4276 }
4277 case (FORCETYPE=="SQLite3" || ((FORCETYPE==false || $ver!=-1) && class_exists("SQLite3") && ($ver==-1 || $ver==3))):
4278 $this->db = new SQLite3($this->data['path']);
4279 if($this->db!=NULL)
4280 {
4281 $this->type = "SQLite3";
4282 break;
4283 }
4284 case (FORCETYPE=="SQLiteDatabase" || ((FORCETYPE==false || $ver!=-1) && class_exists("SQLiteDatabase") && ($ver==-1 || $ver==2))):
4285 $this->db = new SQLiteDatabase($this->data['path']);
4286 if($this->db!=NULL)
4287 {
4288 $this->type = "SQLiteDatabase";
4289 break;
4290 }
4291 default:
4292 $this->showError();
4293 exit();
4294 }
4295 $this->query("PRAGMA foreign_keys = ON");
4296 }
4297 catch(Exception $e)
4298 {
4299 $this->showError();
4300 exit();
4301 }
4302 }
4303
4304 public function registerUserFunction($ids)
4305 {
4306 // in case a single function id was passed
4307 if (is_string($ids))
4308 $ids = array($ids);
4309
4310 if ($this->type == 'PDO') {
4311 foreach ($ids as $id) {
4312 $this->db->sqliteCreateFunction($id, $id, 1);
4313 }
4314 } else { // type is Sqlite3 or SQLiteDatabase
4315 foreach ($ids as $id) {
4316 $this->db->createFunction($id, $id, 1);
4317 }
4318 }
4319 }
4320
4321 public function getError()
4322 {
4323 if($this->alterError!='')
4324 {
4325 $error = $this->alterError;
4326 $this->alterError = "";
4327 return $error;
4328 }
4329 else if($this->type=="PDO")
4330 {
4331 $e = $this->db->errorInfo();
4332 return $e[2];
4333 }
4334 else if($this->type=="SQLite3")
4335 {
4336 return $this->db->lastErrorMsg();
4337 }
4338 else
4339 {
4340 return sqlite_error_string($this->db->lastError());
4341 }
4342 }
4343
4344 public function showError()
4345 {
4346 global $lang;
4347 $classPDO = class_exists("PDO");
4348 $classSQLite3 = class_exists("SQLite3");
4349 $classSQLiteDatabase = class_exists("SQLiteDatabase");
4350 if($classPDO) // PDO is there, check if the SQLite driver for PDO is missing
4351 $PDOSqliteDriver = (in_array("sqlite", PDO::getAvailableDrivers() ));
4352 else
4353 $PDOSqliteDriver = false;
4354 echo "<div class='confirm' style='margin:20px;'>";
4355 printf($lang['db_setup'], $this->getPath());
4356 echo ".<br/><br/><i>".$lang['chk_ext']."...<br/><br/>";
4357 echo "<b>PDO</b>: ".($classPDO ? $lang['installed'] : $lang['not_installed'])."<br/>";
4358 echo "<b>PDO SQLite Driver</b>: ".($PDOSqliteDriver ? $lang['installed'] : $lang['not_installed'])."<br/>";
4359 echo "<b>SQLite3</b>: ".($classSQLite3 ? $lang['installed'] : $lang['not_installed'])."<br/>";
4360 echo "<b>SQLiteDatabase</b>: ".($classSQLiteDatabase ? $lang['installed'] : $lang['not_installed'])."<br/>";
4361 echo "<br/>...".$lang['done'].".</i><br/><br/>";
4362 if(!$classPDO && !$classSQLite3 && !$classSQLiteDatabase)
4363 printf($lang['sqlite_ext_support'], PROJECT);
4364 else
4365 {
4366 if(!$classPDO && !$classSQLite3 && $this->getVersion()==3)
4367 printf($lang['sqlite_v_error'], 3, PROJECT, 2);
4368 else if(!$classSQLiteDatabase && $this->getVersion()==2)
4369 printf($lang['sqlite_v_error'], 2, PROJECT, 3);
4370 else
4371 echo $lang['report_issue'].' '.PROJECT_BUGTRACKER_LINK.'.';
4372 }
4373 echo "<p>See ".PROJECT_INSTALL_LINK." for help.</p>";
4374
4375 $this->print_db_list();
4376
4377 echo "</div>";
4378 }
4379
4380 // print the list of databases
4381 public function print_db_list()
4382 {
4383 global $databases, $lang;
4384 echo "<fieldset style='margin:15px;'><legend><b>".$lang['db_ch']."</b></legend>";
4385 if(sizeof($databases)<10) //if there aren't a lot of databases, just show them as a list of links instead of drop down menu
4386 {
4387 $i=0;
4388 foreach($databases as $database)
4389 {
4390 $i++;
4391 echo '[' . ($database['readable'] ? 'r':' ' ) . ($database['writable'] && $database['writable_dir'] ? 'w':' ' ) . '] ';
4392 if($database == $_SESSION[COOKIENAME.'currentDB'])
4393 echo "<a href='?switchdb=".urlencode($database['path'])."' class='active_db'>".htmlencode($database['name'])."</a> (<a href='?download=".urlencode($database['path'])."' title='".$lang['backup']."'>↓</a>)";
4394 else
4395 echo "<a href='?switchdb=".urlencode($database['path'])."'>".htmlencode($database['name'])."</a> (<a href='?download=".urlencode($database['path'])."' title='".$lang['backup']."'>↓</a>)";
4396 if($i<sizeof($databases))
4397 echo "<br/>";
4398 }
4399 }
4400 else //there are a lot of databases - show a drop down menu
4401 {
4402 echo "<form action='".PAGE."' method='post'>";
4403 echo "<select name='database_switch'>";
4404 foreach($databases as $database)
4405 {
4406 $perms_string = htmlencode('[' . ($database['readable'] ? 'r':' ' ) . ($database['writable'] && $database['writable_dir'] ? 'w':' ' ) . '] ');
4407 if($database == $_SESSION[COOKIENAME.'currentDB'])
4408 echo "<option value='".htmlencode($database['path'])."' selected='selected'>".$perms_string.htmlencode($database['name'])."</option>";
4409 else
4410 echo "<option value='".htmlencode($database['path'])."'>".$perms_string.htmlencode($database['name'])."</option>";
4411 }
4412 echo "</select> ";
4413 echo "<input type='submit' value='".$lang['go']."' class='btn'>";
4414 echo "</form>";
4415 }
4416 echo "</fieldset>";
4417 }
4418
4419 public function __destruct()
4420 {
4421 if($this->db)
4422 $this->close();
4423 }
4424
4425 //get the exact PHP extension being used for SQLite
4426 public function getType()
4427 {
4428 return $this->type;
4429 }
4430
4431 // get the version of the SQLite library
4432 public function getSQLiteVersion()
4433 {
4434 $queryVersion = $this->select("SELECT sqlite_version() AS sqlite_version");
4435 return $queryVersion['sqlite_version'];
4436 }
4437
4438 //get the name of the database
4439 public function getName()
4440 {
4441 return $this->data["name"];
4442 }
4443
4444 //get the filename of the database
4445 public function getPath()
4446 {
4447 return $this->data["path"];
4448 }
4449
4450 //is the db-file writable?
4451 public function isWritable()
4452 {
4453 return $this->data["writable"];
4454 }
4455
4456 //is the db-folder writable?
4457 public function isDirWritable()
4458 {
4459 return $this->data["writable_dir"];
4460 }
4461
4462 //get the version of the database
4463 public function getVersion()
4464 {
4465 if(file_exists($this->data['path'])) //make sure file exists before getting its contents
4466 {
4467 $content = strtolower(file_get_contents($this->data['path'], NULL, NULL, 0, 40)); //get the first 40 characters of the database file
4468 $p = strpos($content, "** this file contains an sqlite 2"); //this text is at the beginning of every SQLite2 database
4469 if($p!==false) //the text is found - this is version 2
4470 return 2;
4471 else
4472 return 3;
4473 }
4474 else //return -1 to indicate that it does not exist and needs to be created
4475 {
4476 return -1;
4477 }
4478 }
4479
4480 //get the size of the database (in KB)
4481 public function getSize()
4482 {
4483 return round(filesize($this->data["path"])*0.0009765625, 1);
4484 }
4485
4486 //get the last modified time of database
4487 public function getDate()
4488 {
4489 global $lang;
4490 return date($lang['date_format'], filemtime($this->data['path']));
4491 }
4492
4493 //get number of affected rows from last query
4494 public function getAffectedRows()
4495 {
4496 if($this->type=="PDO")
4497 if(!is_object($this->lastResult))
4498 // in case it was an alter table statement, there is no lastResult object
4499 return 0;
4500 else
4501 return $this->lastResult->rowCount();
4502 else if($this->type=="SQLite3")
4503 return $this->db->changes();
4504 else if($this->type=="SQLiteDatabase")
4505 return $this->db->changes();
4506 }
4507
4508 public function getTypeOfTable($table)
4509 {
4510 $result = $this->select("SELECT `type` FROM `sqlite_master` WHERE `name`=" . $this->quote($table), 'assoc');
4511 return $result['type'];
4512 }
4513
4514 public function close()
4515 {
4516 if($this->type=="PDO")
4517 $this->db = NULL;
4518 else if($this->type=="SQLite3")
4519 $this->db->close();
4520 else if($this->type=="SQLiteDatabase")
4521 $this->db = NULL;
4522 }
4523
4524 public function beginTransaction()
4525 {
4526 $this->query("BEGIN");
4527 }
4528
4529 public function commitTransaction()
4530 {
4531 $this->query("COMMIT");
4532 }
4533
4534 public function rollbackTransaction()
4535 {
4536 $this->query("ROLLBACK");
4537 }
4538
4539 //generic query wrapper
4540 //returns false on error and the query result on success
4541 public function query($query, $ignoreAlterCase=false)
4542 {
4543 global $debug;
4544 if(strtolower(substr(ltrim($query),0,5))=='alter' && $ignoreAlterCase==false) //this query is an ALTER query - call the necessary function
4545 {
4546 preg_match("/^\s*ALTER\s+TABLE\s+\"((?:[^\"]|\"\")+)\"\s+(.*)$/i",$query,$matches);
4547 if(!isset($matches[1]) || !isset($matches[2]))
4548 {
4549 if($debug) echo "<span title='".htmlencode($query)."' onclick='this.innerHTML=\"".htmlencode(str_replace('"','\"',$query))."\"' style='cursor:pointer'>SQL?</span><br />";
4550 return false;
4551 }
4552 $tablename = str_replace('""','"',$matches[1]);
4553 $alterdefs = $matches[2];
4554 if($debug) echo "ALTER TABLE QUERY=(".htmlencode($query)."), tablename=($tablename), alterdefs=($alterdefs)<hr>";
4555 $result = $this->alterTable($tablename, $alterdefs);
4556 }
4557 else //this query is normal - proceed as normal
4558 {
4559 $result = $this->db->query($query);
4560 if($debug) echo "<span title='".htmlencode($query)."' onclick='this.innerHTML=\"".htmlencode(str_replace('"','\"',$query))."\"' style='cursor:pointer'>SQL?</span><br />";
4561 }
4562 if($result===false)
4563 return false;
4564 $this->lastResult = $result;
4565 return $result;
4566 }
4567
4568 //wrapper for an INSERT and returns the ID of the inserted row
4569 public function insert($query)
4570 {
4571 $result = $this->query($query);
4572 if($this->type=="PDO")
4573 return $this->db->lastInsertId();
4574 else if($this->type=="SQLite3")
4575 return $this->db->lastInsertRowID();
4576 else if($this->type=="SQLiteDatabase")
4577 return $this->db->lastInsertRowid();
4578 }
4579
4580 //returns an array for SELECT
4581 public function select($query, $mode="both")
4582 {
4583 $result = $this->query($query);
4584 if(!$result) //make sure the result is valid
4585 return NULL;
4586 if($this->type=="PDO")
4587 {
4588 if($mode=="assoc")
4589 $mode = PDO::FETCH_ASSOC;
4590 else if($mode=="num")
4591 $mode = PDO::FETCH_NUM;
4592 else
4593 $mode = PDO::FETCH_BOTH;
4594 return $result->fetch($mode);
4595 }
4596 else if($this->type=="SQLite3")
4597 {
4598 if($mode=="assoc")
4599 $mode = SQLITE3_ASSOC;
4600 else if($mode=="num")
4601 $mode = SQLITE3_NUM;
4602 else
4603 $mode = SQLITE3_BOTH;
4604 return $result->fetchArray($mode);
4605 }
4606 else if($this->type=="SQLiteDatabase")
4607 {
4608 if($mode=="assoc")
4609 $mode = SQLITE_ASSOC;
4610 else if($mode=="num")
4611 $mode = SQLITE_NUM;
4612 else
4613 $mode = SQLITE_BOTH;
4614 return $result->fetch($mode);
4615 }
4616 }
4617
4618 //returns an array of arrays after doing a SELECT
4619 public function selectArray($query, $mode="both")
4620 {
4621 $result = $this->query($query);
4622 //make sure the result is valid
4623 if($result=== false || $result===NULL)
4624 return NULL; // error
4625 if(!is_object($result)) // no rows returned
4626 return array();
4627 if($this->type=="PDO")
4628 {
4629 if($mode=="assoc")
4630 $mode = PDO::FETCH_ASSOC;
4631 else if($mode=="num")
4632 $mode = PDO::FETCH_NUM;
4633 else
4634 $mode = PDO::FETCH_BOTH;
4635 return $result->fetchAll($mode);
4636 }
4637 else if($this->type=="SQLite3")
4638 {
4639 if($mode=="assoc")
4640 $mode = SQLITE3_ASSOC;
4641 else if($mode=="num")
4642 $mode = SQLITE3_NUM;
4643 else
4644 $mode = SQLITE3_BOTH;
4645 $arr = array();
4646 $i = 0;
4647 while($res = $result->fetchArray($mode))
4648 {
4649 $arr[$i] = $res;
4650 $i++;
4651 }
4652 return $arr;
4653 }
4654 else if($this->type=="SQLiteDatabase")
4655 {
4656 if($mode=="assoc")
4657 $mode = SQLITE_ASSOC;
4658 else if($mode=="num")
4659 $mode = SQLITE_NUM;
4660 else
4661 $mode = SQLITE_BOTH;
4662 return $result->fetchAll($mode);
4663 }
4664 }
4665
4666 //returns an array of the next row in $result
4667 public function fetch($result, $mode="both")
4668 {
4669 //make sure the result is valid
4670 if($result=== false || $result===NULL)
4671 return NULL; // error
4672 if(!is_object($result)) // no rows returned
4673 return array();
4674 if($this->type=="PDO")
4675 {
4676 if($mode=="assoc")
4677 $mode = PDO::FETCH_ASSOC;
4678 else if($mode=="num")
4679 $mode = PDO::FETCH_NUM;
4680 else
4681 $mode = PDO::FETCH_BOTH;
4682 return $result->fetch($mode);
4683 }
4684 else if($this->type=="SQLite3")
4685 {
4686 if($mode=="assoc")
4687 $mode = SQLITE3_ASSOC;
4688 else if($mode=="num")
4689 $mode = SQLITE3_NUM;
4690 else
4691 $mode = SQLITE3_BOTH;
4692 $arr = array();
4693 $i = 0;
4694 while($res = $result->fetch($mode))
4695 {
4696 $arr[$i] = $res;
4697 $i++;
4698 }
4699 return $arr;
4700 }
4701 else if($this->type=="SQLiteDatabase")
4702 {
4703 if($mode=="assoc")
4704 $mode = SQLITE_ASSOC;
4705 else if($mode=="num")
4706 $mode = SQLITE_NUM;
4707 else
4708 $mode = SQLITE_BOTH;
4709 return $result->fetch($mode);
4710 }
4711 }
4712
4713
4714 // SQlite supports multiple ways of surrounding names in quotes:
4715 // single-quotes, double-quotes, backticks, square brackets.
4716 // As sqlite does not keep this strict, we also need to be flexible here.
4717 // This function generates a regex that matches any of the possibilities.
4718 private function sqlite_surroundings_preg($name,$preg_quote=true,$notAllowedCharsIfNone="'\"",$notAllowedName=false)
4719 {
4720 if($name=="*" || $name=="+")
4721 {
4722 if($notAllowedName!==false && $preg_quote)
4723 $notAllowedName = preg_quote($notAllowedName,"/");
4724 // use possesive quantifiers to save memory
4725 $nameSingle = ($notAllowedName!==false?"(?!".$notAllowedName."')":"")."(?:[^']$name+|'')$name+";
4726 $nameDouble = ($notAllowedName!==false?"(?!".$notAllowedName."\")":"")."(?:[^\"]$name+|\"\")$name+";
4727 $nameBacktick = ($notAllowedName!==false?"(?!".$notAllowedName."`)":"")."(?:[^`]$name+|``)$name+";
4728 $nameSquare = ($notAllowedName!==false?"(?!".$notAllowedName."\])":"")."(?:[^\]]$name+|\]\])$name+";
4729 $nameNo = ($notAllowedName!==false?"(?!".$notAllowedName."\s)":"")."[^".$notAllowedCharsIfNone."]$name";
4730 }
4731 else
4732 {
4733 if($preg_quote) $name = preg_quote($name,"/");
4734
4735 $nameSingle = str_replace("'","''",$name);
4736 $nameDouble = str_replace('"','""',$name);
4737 $nameBacktick = str_replace('`','``',$name);
4738 $nameSquare = str_replace(']',']]',$name);
4739 $nameNo = $name;
4740 }
4741
4742 $preg = "(?:'".$nameSingle."'|". // single-quote surrounded or not in quotes (correct SQL for values/new names)
4743 $nameNo."|". // not surrounded (correct SQL if not containing reserved words, spaces or some special chars)
4744 "\"".$nameDouble."\"|". // double-quote surrounded (correct SQL for identifiers)
4745 "`".$nameBacktick."`|". // backtick surrounded (MySQL-Style)
4746 "\[".$nameSquare."\])"; // square-bracket surrounded (MS Access/SQL server-Style)
4747 return $preg;
4748 }
4749
4750 // Returns the last PREG error as a string, '' if no error occured
4751 private function getPregError()
4752 {
4753 $error = preg_last_error();
4754 switch ($error)
4755 {
4756 case PREG_NO_ERROR: return 'No error';
4757 case PREG_INTERNAL_ERROR: return 'There is an internal error!';
4758 case PREG_BACKTRACK_LIMIT_ERROR: return 'Backtrack limit was exhausted!';
4759 case PREG_RECURSION_LIMIT_ERROR: return 'Recursion limit was exhausted!';
4760 case PREG_BAD_UTF8_ERROR: return 'Bad UTF8 error!';
4761 case PREG_BAD_UTF8_ERROR: return 'Bad UTF8 offset error!';
4762 default: return 'Unknown Error';
4763 }
4764 }
4765
4766 // function that is called for an alter table statement in a query
4767 // code borrowed with permission from http://code.jenseng.com/db/
4768 // this has been completely debugged / rewritten by Christopher Kramer
4769 public function alterTable($table, $alterdefs)
4770 {
4771 global $debug, $lang;
4772 $this->alterError="";
4773 $errormsg = sprintf($lang['alter_failed'],htmlencode($table)).' - ';
4774 if($debug) echo "ALTER TABLE: table=($table), alterdefs=($alterdefs)<hr>";
4775 if($alterdefs != '')
4776 {
4777 $recreateQueries = array();
4778 $resultArr = $this->selectArray("SELECT sql,name,type FROM sqlite_master WHERE tbl_name = ".$this->quote($table));
4779 if(sizeof($resultArr)<1)
4780 {
4781 $this->alterError = $errormsg . sprintf($lang['tbl_inexistent'], htmlencode($table));
4782 if($debug) echo "ERROR: unknown table<hr>";
4783 return false;
4784 }
4785 for($i=0; $i<sizeof($resultArr); $i++)
4786 {
4787 $row = $resultArr[$i];
4788 if($row['type'] != 'table')
4789 {
4790 if($row['sql']!='')
4791 {
4792 // store the CREATE statements of triggers and indexes to recreate them later
4793 $recreateQueries[] = $row;
4794 if($debug) echo "recreate=(".$row['sql'].";)<hr />";
4795 }
4796 }
4797 else
4798 {
4799 // ALTER the table
4800 $tmpname = 't'.time();
4801 $origsql = $row['sql'];
4802 $preg_remove_create_table = "/^\s*+CREATE\s++TABLE\s++".$this->sqlite_surroundings_preg($table)."\s*+(\(.*+)$/is";
4803 $origsql_no_create = preg_replace($preg_remove_create_table, '$1', $origsql, 1);
4804 if($debug) echo "origsql=($origsql)<br />preg_remove_create_table=($preg_remove_create_table)<hr>";
4805 if($origsql_no_create == $origsql)
4806 {
4807 $this->alterError = $errormsg . $lang['alter_tbl_name_not_replacable'];
4808 if($debug) echo "ERROR: could not get rid of CREATE TABLE<hr />";
4809 return false;
4810 }
4811 $createtemptableSQL = "CREATE TEMPORARY TABLE ".$this->quote($tmpname)." ".$origsql_no_create;
4812 if($debug) echo "createtemptableSQL=($createtemptableSQL)<hr>";
4813 $createindexsql = array();
4814 $preg_alter_part = "/(?:DROP(?! PRIMARY KEY)|ADD(?! PRIMARY KEY)|CHANGE|RENAME TO|ADD PRIMARY KEY|DROP PRIMARY KEY)" // the ALTER command
4815 ."(?:"
4816 ."\s+\(".$this->sqlite_surroundings_preg("+",false,"\"'\[`)")."+\)" // stuff in brackets (in case of ADD PRIMARY KEY)
4817 ."|" // or
4818 ."\s+".$this->sqlite_surroundings_preg("+",false,",'\"\[`") // column names and stuff like this
4819 .")*/i";
4820 if($debug)
4821 echo "preg_alter_part=(".$preg_alter_part.")<hr />";
4822 preg_match_all($preg_alter_part,$alterdefs,$matches);
4823 $defs = $matches[0];
4824
4825 $get_oldcols_query = "PRAGMA table_info(".$this->quote_id($table).")";
4826 $result_oldcols = $this->selectArray($get_oldcols_query);
4827 $newcols = array();
4828 $coltypes = array();
4829 $primarykey = array();
4830 foreach($result_oldcols as $column_info)
4831 {
4832 $newcols[$column_info['name']] = $column_info['name'];
4833 $coltypes[$column_info['name']] = $column_info['type'];
4834 if($column_info['pk'])
4835 $primarykey[] = $column_info['name'];
4836 }
4837 $newcolumns = '';
4838 $oldcolumns = '';
4839 reset($newcols);
4840 while(list($key, $val) = each($newcols))
4841 {
4842 $newcolumns .= ($newcolumns?', ':'').$this->quote_id($val);
4843 $oldcolumns .= ($oldcolumns?', ':'').$this->quote_id($key);
4844 }
4845 $copytotempsql = 'INSERT INTO '.$this->quote_id($tmpname).'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$this->quote_id($table);
4846 $dropoldsql = 'DROP TABLE '.$this->quote_id($table);
4847 $createtesttableSQL = $createtemptableSQL;
4848 if(count($defs)<1)
4849 {
4850 $this->alterError = $errormsg . $lang['alter_no_def'];
4851 if($debug) echo "ERROR: defs<1<hr />";
4852 return false;
4853 }
4854 foreach($defs as $def)
4855 {
4856 if($debug) echo "def=$def<hr />";
4857 $preg_parse_def =
4858 "/^(DROP(?! PRIMARY KEY)|ADD(?! PRIMARY KEY)|CHANGE|RENAME TO|ADD PRIMARY KEY|DROP PRIMARY KEY)" // $matches[1]: command
4859 ."(?:" // this is either
4860 ."(?:\s+\((.+)\)\s*$)" // anything in brackets (for ADD PRIMARY KEY)
4861 // then $matches[2] is what there is in brackets
4862 ."|" // OR:
4863 ."(?:\s+\"((?:[^\"]|\"\")+)\"|\s+'((?:[^']|'')+)')"// (first) column name, either in single or double quotes
4864 // in case of RENAME TO, it is the new a table name
4865 // $matches[3] will be the column/table name without the quotes if double quoted
4866 // $matches[4] will be the column/table name without the quotes if single quoted
4867 ."(" // $matches[5]: anything after the column name
4868 ."(?:\s+'((?:[^']|'')+)')?" // $matches[6] (optional): a second column name surrounded with single quotes
4869 // (the match does not contain the quotes)
4870 ."\s+"
4871 ."((?:[A-Z]+\s*)+(?:\(\s*[+-]?\s*[0-9]+(?:\s*,\s*[+-]?\s*[0-9]+)?\s*\))?)\s*" // $matches[7]: a type name
4872 .".*".
4873 ")"
4874 ."?\s*$"
4875 .")?\s*$/i"; // in case of DROP PRIMARY KEY, there is nothing after the command
4876 if($debug) echo "preg_parse_def=$preg_parse_def<hr />";
4877 $parse_def = preg_match($preg_parse_def,$def,$matches);
4878 if($parse_def===false)
4879 {
4880 $this->alterError = $errormsg . $lang['alter_parse_failed'];
4881 if($debug) echo "ERROR: !parse_def<hr />";
4882 return false;
4883 }
4884 if(!isset($matches[1]))
4885 {
4886 $this->alterError = $errormsg . $lang['alter_action_not_recognized'];
4887 if($debug) echo "ERROR: !isset(matches[1])<hr />";
4888 return false;
4889 }
4890 $action = strtolower($matches[1]);
4891 if(($action == 'add' || $action == 'rename to') && isset($matches[4]) && $matches[4]!='')
4892 $column = str_replace("''","'",$matches[4]); // enclosed in ''
4893 elseif($action == 'add primary key' && isset($matches[2]) && $matches[2]!='')
4894 $column = $matches[2];
4895 elseif($action == 'drop primary key')
4896 $column = ''; // DROP PRIMARY KEY has no column definition
4897 elseif(isset($matches[3]) && $matches[3]!='')
4898 $column = str_replace('""','"',$matches[3]); // enclosed in ""
4899 else
4900 $column = '';
4901
4902 $column_escaped = str_replace("'","''",$column);
4903
4904 if($debug) echo "action=($action), column=($column), column_escaped=($column_escaped)<hr />";
4905
4906 /* we build a regex that devides the CREATE TABLE statement parts:
4907 Part example Group Explanation
4908 1. CREATE TABLE t... ( $1
4909 2. 'col1' ..., 'col2' ..., 'colN' ..., $3 (with col1-colN being columns that are not changed and listed before the col to change)
4910 3. 'colX' ..., (with colX being the column to change/drop)
4911 4. 'colX+1' ..., ..., 'colK') $5 (with colX+1-colK being columns after the column to change/drop)
4912 */
4913 $preg_create_table = "\s*+(CREATE\s++TEMPORARY\s++TABLE\s++".preg_quote($this->quote($tmpname),"/")."\s*+\()"; // This is group $1 (keep unchanged)
4914 $preg_column_definiton = "\s*+".$this->sqlite_surroundings_preg("+",true," '\"\[`,",$column)."(?:\s*+".$this->sqlite_surroundings_preg("*",false,"'\",`\[ ").")++"; // catches a complete column definition, even if it is
4915 // 'column' TEXT NOT NULL DEFAULT 'we have a comma, here and a double ''quote!'
4916 // this definition does NOT match columns with the column name $column
4917 if($debug) echo "preg_column_definition=(".$preg_column_definiton.")<hr />";
4918 $preg_columns_before = // columns before the one changed/dropped (keep)
4919 "(?:".
4920 "(". // group $2. Keep this one unchanged!
4921 "(?:".
4922 "$preg_column_definiton,\s*+". // column definition + comma
4923 ")*". // there might be any number of such columns here
4924 $preg_column_definiton. // last column definition
4925 ")". // end of group $2
4926 ",\s*+" // the last comma of the last column before the column to change. Do not keep it!
4927 .")?"; // there might be no columns before
4928 if($debug) echo "preg_columns_before=(".$preg_columns_before.")<hr />";
4929 $preg_columns_after = "(,\s*(.+))?"; // the columns after the column to drop. This is group $3 (drop) or $4(change) (keep!)
4930 // we could remove the comma using $6 instead of $5, but then we might have no comma at all.
4931 // Keeping it leaves a problem if we drop the first column, so we fix that case in another regex.
4932 $table_new = $table;
4933
4934 switch($action)
4935 {
4936 case 'add':
4937 if($column=='')
4938 {
4939 $this->alterError = $errormsg . ' (add) - '. $lang['alter_no_add_col'];
4940 return false;
4941 }
4942 $new_col_definition = "'$column_escaped' ".(isset($matches[5])?$matches[5]:'');
4943 $preg_pattern_add = "/^".$preg_create_table. // the CREATE TABLE statement ($1)
4944 "((?:(?!,\s*(?:PRIMARY\s+KEY\s*\(|CONSTRAINT\s|UNIQUE\s*\(|CHECK\s*\(|FOREIGN\s+KEY\s*\()).)*)". // column definitions ($2)
4945 "(.*)\\)\s*$/si"; // table-constraints like PRIMARY KEY(a,b) ($3) and the closing bracket
4946 // append the column definiton in the CREATE TABLE statement
4947 $newSQL = preg_replace($preg_pattern_add, '$1$2, '.strtr($new_col_definition, array('\\' => '\\\\', '$' => '\$')).' $3', $createtesttableSQL).')';
4948 $preg_error = $this->getPregError();
4949 if($debug)
4950 {
4951 echo $createtesttableSQL."<hr>";
4952 echo $newSQL."<hr>";
4953 echo $preg_pattern_add."<hr>";
4954 }
4955 if($newSQL==$createtesttableSQL) // pattern did not match, so column adding did not succed
4956 {
4957 $this->alterError = $errormsg . ' (add) - '.$lang['alter_pattern_mismatch'].'. PREG ERROR: '.$preg_error;
4958 return false;
4959 }
4960 $createtesttableSQL = $newSQL;
4961 break;
4962 case 'change':
4963 if(!isset($matches[6]) || !isset($matches[7]))
4964 {
4965 $this->alterError = $errormsg . ' (change) - '.$lang['alter_col_not_recognized'];
4966 return false;
4967 }
4968 $new_col_name = $matches[6];
4969 $new_col_type = $matches[7];
4970 $new_col_definition = "'$new_col_name' $new_col_type";
4971 $preg_column_to_change = "\s*".$this->sqlite_surroundings_preg($column)."(?:\s+".preg_quote($coltypes[$column]).")?(\s+(?:".$this->sqlite_surroundings_preg("*",false,",'\"`\[").")+)?";
4972 // replace this part (we want to change this column)
4973 // group $3 contains the column constraints (keep!). the name & data type is replaced.
4974 $preg_pattern_change = "/^".$preg_create_table.$preg_columns_before.$preg_column_to_change.$preg_columns_after."\s*\\)\s*$/s";
4975
4976 // replace the column definiton in the CREATE TABLE statement
4977 $newSQL = preg_replace($preg_pattern_change, '$1$2,'.strtr($new_col_definition, array('\\' => '\\\\', '$' => '\$')).'$3$4)', $createtesttableSQL);
4978 $preg_error = $this->getPregError();
4979 // remove comma at the beginning if the first column is changed
4980 // probably somebody is able to put this into the first regex (using lookahead probably).
4981 $newSQL = preg_replace("/^\s*(CREATE\s+TEMPORARY\s+TABLE\s+".preg_quote($this->quote($tmpname),"/")."\s+\(),\s*/",'$1',$newSQL);
4982 if($debug)
4983 {
4984 echo "preg_column_to_change=(".$preg_column_to_change.")<hr />";
4985 echo $createtesttableSQL."<hr />";
4986 echo $newSQL."<hr />";
4987
4988 echo $preg_pattern_change."<hr />";
4989 }
4990 if($newSQL==$createtesttableSQL || $newSQL=="") // pattern did not match, so column removal did not succed
4991 {
4992 $this->alterError = $errormsg . ' (change) - '.$lang['alter_pattern_mismatch'].'. PREG ERROR: '.$preg_error;
4993 return false;
4994 }
4995 $createtesttableSQL = $newSQL;
4996 $newcols[$column] = str_replace("''","'",$new_col_name);
4997 break;
4998 case 'drop':
4999 $preg_column_to_drop = "\s*".$this->sqlite_surroundings_preg($column)."\s+(?:".$this->sqlite_surroundings_preg("*",false,",'\"\[`").")+"; // delete this part (we want to drop this column)
5000 $preg_pattern_drop = "/^".$preg_create_table.$preg_columns_before.$preg_column_to_drop.$preg_columns_after."\s*\\)\s*$/s";
5001
5002 // remove the column out of the CREATE TABLE statement
5003 $newSQL = preg_replace($preg_pattern_drop, '$1$2$3)', $createtesttableSQL);
5004 $preg_error = $this->getPregError();
5005 // remove comma at the beginning if the first column is removed
5006 // probably somebody is able to put this into the first regex (using lookahead probably).
5007 $newSQL = preg_replace("/^\s*(CREATE\s+TEMPORARY\s+TABLE\s+".preg_quote($this->quote($tmpname),"/")."\s+\(),\s*/",'$1',$newSQL);
5008 if($debug)
5009 {
5010 echo $createtesttableSQL."<hr>";
5011 echo $newSQL."<hr>";
5012 echo $preg_pattern_drop."<hr>";
5013 }
5014 if($newSQL==$createtesttableSQL || $newSQL=="") // pattern did not match, so column removal did not succed
5015 {
5016 $this->alterError = $errormsg . ' (drop) - '.$lang['alter_pattern_mismatch'].'. PREG ERROR: '.$preg_error;
5017 return false;
5018 }
5019 $createtesttableSQL = $newSQL;
5020 unset($newcols[$column]);
5021 break;
5022 case 'rename to':
5023 // don't change column definition at all
5024 $newSQL = $createtesttableSQL;
5025 // only change the name of the table
5026 $table_new = $column;
5027 break;
5028 case 'add primary key':
5029 // we want to add a primary key for the column(s) stored in $column
5030 $newSQL = preg_replace("/\)\s*$/", ", PRIMARY KEY (".$column.") )", $createtesttableSQL);
5031 $createtesttableSQL = $newSQL;
5032 break;
5033 case 'drop primary key':
5034 // we want to drop the primary key
5035 if($debug) echo "DROP";
5036 if(sizeof($primarykey)==1)
5037 {
5038 // if not compound primary key, might be a column constraint -> try removal
5039 $column = $primarykey[0];
5040 if($debug) echo "<br>Trying to drop column constraint for column $column <br>";
5041 /*
5042 TODO: This does not work yet:
5043 CREATE TABLE 't12' ('t1' INTEGER CONSTRAINT "bla" NOT NULL CONSTRAINT 'pk' PRIMARY KEY ); ALTER TABLE "t12" DROP PRIMARY KEY
5044 This does: ! !
5045 CREATE TABLE 't12' ('t1' INTEGER CONSTRAINT bla NOT NULL CONSTRAINT 'pk' PRIMARY KEY ); ALTER TABLE "t12" DROP PRIMARY KEY
5046 */
5047 $preg_column_to_change = "(\s*".$this->sqlite_surroundings_preg($column).")". // column ($3)
5048 "(?:". // opt. type and column constraints
5049 "(\s+(?:".$this->sqlite_surroundings_preg("(?:[^PC,'\"`\[]|P(?!RIMARY\s+KEY)|".
5050 "C(?!ONSTRAINT\s+".$this->sqlite_surroundings_preg("+",false," ,'\"\[`")."\s+PRIMARY\s+KEY))",false,",'\"`\[").")*)". // column constraints before PRIMARY KEY ($3)
5051 // primary key constraint (remove this!):
5052 "(?:CONSTRAINT\s+".$this->sqlite_surroundings_preg("+",false," ,'\"\[`")."\s+)?".
5053 "PRIMARY\s+KEY".
5054 "(?:\s+(?:ASC|DESC))?".
5055 "(?:\s+ON\s+CONFLICT\s+(?:ROLLBACK|ABORT|FAIL|IGNORE|REPLACE))?".
5056 "(?:\s+AUTOINCREMENT)?".
5057 "((?:".$this->sqlite_surroundings_preg("*",false,",'\"`\[").")*)". // column constraints after PRIMARY KEY ($4)
5058 ")";
5059 // replace this part (we want to change this column)
5060 // group $3 (column) $4 (constraints before) and $5 (constraints after) contain the part to keep
5061 $preg_pattern_change = "/^".$preg_create_table.$preg_columns_before.$preg_column_to_change.$preg_columns_after."\s*\\)\s*$/si";
5062
5063 // replace the column definiton in the CREATE TABLE statement
5064 $newSQL = preg_replace($preg_pattern_change, '$1$2,$3$4$5$6)', $createtesttableSQL);
5065 // remove comma at the beginning if the first column is changed
5066 // probably somebody is able to put this into the first regex (using lookahead probably).
5067 $newSQL = preg_replace("/^\s*(CREATE\s+TEMPORARY\s+TABLE\s+".preg_quote($this->quote($tmpname),"/")."\s+\(),\s*/",'$1',$newSQL);
5068 if($debug)
5069 {
5070 echo "preg_column_to_change=(".$preg_column_to_change.")<hr />";
5071 echo $createtesttableSQL."<hr />";
5072 echo $newSQL."<hr />";
5073
5074 echo $preg_pattern_change."<hr />";
5075 }
5076 if($newSQL!=$createtesttableSQL && $newSQL!="") // pattern did match, so PRIMARY KEY constraint removed :)
5077 {
5078 $createtesttableSQL = $newSQL;
5079 if($debug) echo "<br>SUCCEEDED<br>";
5080 }
5081 else
5082 {
5083 if($debug) echo "NO LUCK";
5084 // TODO: try removing table constraint
5085 return false;
5086 }
5087 $createtesttableSQL = $newSQL;
5088 } else
5089 // TODO: Try removing table constraint
5090 return false;
5091
5092 break;
5093 default:
5094 if($debug) echo 'ERROR: unknown alter operation!<hr />';
5095 $this->alterError = $errormsg . $lang['alter_unknown_operation'];
5096 return false;
5097 }
5098 }
5099 $droptempsql = 'DROP TABLE '.$this->quote_id($tmpname);
5100
5101 $createnewtableSQL = "CREATE TABLE ".$this->quote($table_new)." ".preg_replace("/^\s*CREATE\s+TEMPORARY\s+TABLE\s+'?".str_replace("'","''",preg_quote($tmpname,"/"))."'?\s+(.*)$/is", '$1', $createtesttableSQL, 1);
5102
5103 $newcolumns = '';
5104 $oldcolumns = '';
5105 reset($newcols);
5106 while(list($key,$val) = each($newcols))
5107 {
5108 $newcolumns .= ($newcolumns?', ':'').$this->quote_id($val);
5109 $oldcolumns .= ($oldcolumns?', ':'').$this->quote_id($key);
5110 }
5111 $copytonewsql = 'INSERT INTO '.$this->quote_id($table_new).'('.$newcolumns.') SELECT '.$oldcolumns.' FROM '.$this->quote_id($tmpname);
5112 }
5113 }
5114 $alter_transaction = 'BEGIN; ';
5115 $alter_transaction .= $createtemptableSQL.'; '; //create temp table
5116 $alter_transaction .= $copytotempsql.'; '; //copy to table
5117 $alter_transaction .= $dropoldsql.'; '; //drop old table
5118 $alter_transaction .= $createnewtableSQL.'; '; //recreate original table
5119 $alter_transaction .= $copytonewsql.'; '; //copy back to original table
5120 $alter_transaction .= $droptempsql.'; '; //drop temp table
5121
5122 $preg_index="/^\s*(CREATE\s+(?:UNIQUE\s+)?INDEX\s+(?:".$this->sqlite_surroundings_preg("+",false," '\"\[`")."\s*)*ON\s+)(".$this->sqlite_surroundings_preg($table).")(\s*\((?:".$this->sqlite_surroundings_preg("+",false," '\"\[`")."\s*)*\)\s*)\s*$/i";
5123 foreach($recreateQueries as $recreate_query)
5124 {
5125 if($recreate_query['type']=='index')
5126 {
5127 // this is an index. We need to make sure the index is not on a column that we drop. If it is, we drop the index as well.
5128 $indexInfos = $this->selectArray('PRAGMA index_info('.$this->quote_id($recreate_query['name']).')');
5129 foreach($indexInfos as $indexInfo)
5130 {
5131 if(!isset($newcols[$indexInfo['name']]))
5132 {
5133 if($debug) echo 'Not recreating the following index: <hr />'.htmlencode($recreate_query['sql']).'<hr />';
5134 // Index on a column that was dropped. Skip recreation.
5135 continue 2;
5136 }
5137 }
5138 }
5139 // TODO: In case we renamed a column on which there is an index, we need to recreate the index with the column name adjusted.
5140
5141 // recreate triggers / indexes
5142 if($table == $table_new)
5143 {
5144 // we had no RENAME TO, so we can recreate indexes/triggers just like the original ones
5145 $alter_transaction .= $recreate_query['sql'].';';
5146 } else
5147 {
5148 // we had a RENAME TO, so we need to exchange the table-name in the CREATE-SQL of triggers & indexes
5149 switch ($recreate_query['type'])
5150 {
5151 case 'index':
5152 $recreate_queryIndex = preg_replace($preg_index, '$1'.$this->quote_id(strtr($table_new, array('\\' => '\\\\', '$' => '\$'))).'$3 ', $recreate_query['sql']);
5153 if($recreate_queryIndex!=$recreate_query['sql'] && $recreate_queryIndex != NULL)
5154 $alter_transaction .= $recreate_queryIndex.';';
5155 else
5156 {
5157 // the CREATE INDEX regex did not match. this normally should not happen
5158 if($debug) echo 'ERROR: CREATE INDEX regex did not match!?<hr />';
5159 // just try to recreate the index originally (will fail most likely)
5160 $alter_transaction .= $recreate_query['sql'].';';
5161 }
5162 break;
5163
5164 case 'trigger':
5165 // TODO: IMPLEMENT
5166 $alter_transaction .= $recreate_query['sql'].';';
5167 break;
5168 default:
5169 if($debug) echo 'ERROR: Unknown type '.htmlencode($recreate_query['type']).'<hr />';
5170 $alter_transaction .= $recreate_query['sql'].';';
5171 }
5172 }
5173 }
5174 $alter_transaction .= 'COMMIT;';
5175 if($debug) echo $alter_transaction;
5176 return $this->multiQuery($alter_transaction);
5177 }
5178 }
5179
5180 //multiple query execution
5181 //returns true on success, false otherwise. Use getError() to fetch the error.
5182 public function multiQuery($query)
5183 {
5184 if($this->type=="PDO")
5185 $success = $this->db->exec($query);
5186 else if($this->type=="SQLite3")
5187 $success = $this->db->exec($query);
5188 else
5189 $success = $this->db->queryExec($query, $error);
5190 return $success;
5191 }
5192
5193
5194 // checks whether a table has a primary key
5195 public function hasPrimaryKey($table)
5196 {
5197 $query = "PRAGMA table_info(".$this->quote_id($table).")";
5198 $table_info = $this->selectArray($query);
5199 foreach($table_info as $row_id => $row_data)
5200 {
5201 if($row_data['pk'])
5202 {
5203 return true;
5204 }
5205
5206 }
5207 return false;
5208 }
5209
5210 // Returns an array of columns by which rows can be uniquely adressed.
5211 // For tables with a rowid column, this is always array('rowid')
5212 // for tables without rowid, this is an array of the primary key columns.
5213 public function getPrimaryKey($table)
5214 {
5215 $primary_key = array();
5216 // check if this table has a rowid
5217 $getRowID = $this->select('SELECT ROWID FROM '.$this->quote_id($table).' LIMIT 0,1');
5218 if(isset($getRowID[0]))
5219 // it has, so we prefer addressing rows by rowid
5220 return array('rowid');
5221 else
5222 {
5223 // the table is without rowid, so use the primary key
5224 $query = "PRAGMA table_info(".$this->quote_id($table).")";
5225 $table_info = $this->selectArray($query);
5226 foreach($table_info as $row_id => $row_data)
5227 {
5228 if($row_data['pk'])
5229 $primary_key[] = $row_data['name'];
5230 }
5231 }
5232 return $primary_key;
5233 }
5234
5235 // selects a row by a given key $pk, which is an array of values
5236 // for the columns by which a row can be adressed (rowid or primary key)
5237 public function wherePK($table, $pk)
5238 {
5239 $where = "";
5240 $primary_key = $this->getPrimaryKey($table);
5241 foreach($primary_key as $pk_index => $column)
5242 {
5243 if($where!="")
5244 $where .= " AND ";
5245 $where .= $this->quote_id($column) . ' = ';
5246 if(is_int($pk[$pk_index]) || is_float($pk[$pk_index]))
5247 $where .= $pk[$pk_index];
5248 else
5249 $where .= $this->quote($pk[$pk_index]);
5250 }
5251 return $where;
5252 }
5253
5254 //get number of rows in table
5255 public function numRows($table, $dontTakeLong = false)
5256 {
5257 // as Count(*) can be slow on huge tables without PK,
5258 // if $dontTakeLong is set and the size is > 2MB only count() if there is a PK
5259 if(!$dontTakeLong || $this->getSize() <= 2000 || $this->hasPrimaryKey($table))
5260 {
5261 $result = $this->select("SELECT Count(*) FROM ".$this->quote_id($table));
5262 return $result[0];
5263 } else
5264 {
5265 return '?';
5266 }
5267 }
5268
5269 //correctly escape a string to be injected into an SQL query
5270 public function quote($value)
5271 {
5272 if($this->type=="PDO")
5273 {
5274 // PDO quote() escapes and adds quotes
5275 return $this->db->quote($value);
5276 }
5277 else if($this->type=="SQLite3")
5278 {
5279 return "'".$this->db->escapeString($value)."'";
5280 }
5281 else
5282 {
5283 return "'".sqlite_escape_string($value)."'";
5284 }
5285 }
5286
5287 //correctly escape an identifier (column / table / trigger / index name) to be injected into an SQL query
5288 public function quote_id($value)
5289 {
5290 // double-quotes need to be escaped by doubling them
5291 $value = str_replace('"','""',$value);
5292 return '"'.$value.'"';
5293 }
5294
5295
5296 //import sql
5297 //returns true on success, error message otherwise
5298 public function import_sql($query)
5299 {
5300 $import = $this->multiQuery($query);
5301 if(!$import)
5302 return $this->getError();
5303 else
5304 return true;
5305 }
5306
5307 //import csv
5308 //returns true on success, error message otherwise
5309 public function import_csv($filename, $table, $field_terminate, $field_enclosed, $field_escaped, $null, $fields_in_first_row)
5310 {
5311 // CSV import implemented by Christopher Kramer - http://www.christosoft.de
5312 $csv_handle = fopen($filename,'r');
5313 $csv_insert = "BEGIN;\n";
5314 $csv_number_of_rows = 0;
5315 // PHP requires enclosure defined, but has no problem if it was not used
5316 if($field_enclosed=="") $field_enclosed='"';
5317 // PHP requires escaper defined
5318 if($field_escaped=="") $field_escaped='\\';
5319 while(!feof($csv_handle))
5320 {
5321 $csv_data = fgetcsv($csv_handle, 0, $field_terminate, $field_enclosed, $field_escaped);
5322 if($csv_data[0] != NULL || count($csv_data)>1)
5323 {
5324 $csv_number_of_rows++;
5325 if($fields_in_first_row && $csv_number_of_rows==1)
5326 {
5327 $fields_in_first_row = false;
5328 continue;
5329 }
5330 $csv_col_number = count($csv_data);
5331 $csv_insert .= "INSERT INTO ".$this->quote_id($table)." VALUES (";
5332 foreach($csv_data as $csv_col => $csv_cell)
5333 {
5334 if($csv_cell == $null) $csv_insert .= "NULL";
5335 else
5336 {
5337 $csv_insert.= $this->quote($csv_cell);
5338 }
5339 if($csv_col == $csv_col_number-2 && $csv_data[$csv_col+1]=='')
5340 {
5341 // the CSV row ends with the separator (like old phpliteadmin exported)
5342 break;
5343 }
5344 if($csv_col < $csv_col_number-1) $csv_insert .= ",";
5345 }
5346 $csv_insert .= ");\n";
5347
5348 if($csv_number_of_rows > 5000)
5349 {
5350 $csv_insert .= "COMMIT;\nBEGIN;\n";
5351 $csv_number_of_rows = 0;
5352 }
5353 }
5354 }
5355 $csv_insert .= "COMMIT;";
5356 fclose($csv_handle);
5357 $import = $this->multiQuery($csv_insert);
5358 if(!$import)
5359 return $this->getError();
5360 else
5361 return true;
5362 }
5363
5364 //export csv
5365 public function export_csv($tables, $field_terminate, $field_enclosed, $field_escaped, $null, $crlf, $fields_in_first_row)
5366 {
5367 @set_time_limit(-1);
5368 $field_enclosed = $field_enclosed;
5369 $query = "SELECT * FROM sqlite_master WHERE type='table' or type='view' ORDER BY type DESC";
5370 $result = $this->selectArray($query);
5371 for($i=0; $i<sizeof($result); $i++)
5372 {
5373 $valid = false;
5374 for($j=0; $j<sizeof($tables); $j++)
5375 {
5376 if($result[$i]['tbl_name']==$tables[$j])
5377 $valid = true;
5378 }
5379 if($valid)
5380 {
5381 $query = "PRAGMA table_info(".$this->quote_id($result[$i]['tbl_name']).")";
5382 $temp = $this->selectArray($query);
5383 $cols = array();
5384 for($z=0; $z<sizeof($temp); $z++)
5385 $cols[$z] = $temp[$z][1];
5386 if($fields_in_first_row)
5387 {
5388 for($z=0; $z<sizeof($cols); $z++)
5389 {
5390 echo $field_enclosed.$cols[$z].$field_enclosed;
5391 // do not terminate the last column!
5392 if($z < sizeof($cols)-1)
5393 echo $field_terminate;
5394 }
5395 echo "\r\n";
5396 }
5397 $query = "SELECT * FROM ".$this->quote_id($result[$i]['tbl_name']);
5398 $table_result = $this->query($query);
5399 $firstRow=true;
5400 while($row = $this->fetch($table_result, "assoc"))
5401 {
5402 if(!$firstRow)
5403 echo "\r\n";
5404 else
5405 $firstRow=false;
5406
5407 for($y=0; $y<sizeof($cols); $y++)
5408 {
5409 $cell = $row[$cols[$y]];
5410 if($crlf)
5411 {
5412 $cell = str_replace("\n","", $cell);
5413 $cell = str_replace("\r","", $cell);
5414 }
5415 $cell = str_replace($field_terminate,$field_escaped.$field_terminate,$cell);
5416 $cell = str_replace($field_enclosed,$field_escaped.$field_enclosed,$cell);
5417 // do not enclose NULLs
5418 if($cell == NULL)
5419 echo $null;
5420 else
5421 echo $field_enclosed.$cell.$field_enclosed;
5422 // do not terminate the last column!
5423 if($y < sizeof($cols)-1)
5424 echo $field_terminate;
5425 }
5426 }
5427 if($i<sizeof($result)-1)
5428 echo "\r\n";
5429 }
5430 }
5431 }
5432
5433 //export sql
5434 public function export_sql($tables, $drop, $structure, $data, $transaction, $comments)
5435 {
5436 global $lang;
5437 @set_time_limit(-1);
5438 if($comments)
5439 {
5440 echo "----\r\n";
5441 echo "-- ".PROJECT." ".$lang['db_dump']." (".PROJECT_URL.")\r\n";
5442 echo "-- ".PROJECT." ".$lang['ver'].": ".VERSION."\r\n";
5443 echo "-- ".$lang['exported'].": ".date($lang['date_format'])."\r\n";
5444 echo "-- ".$lang['db_f'].": ".$this->getPath()."\r\n";
5445 echo "----\r\n";
5446 }
5447 $query = "SELECT * FROM sqlite_master WHERE type='table' OR type='index' OR type='view' OR type='trigger' ORDER BY type='trigger', type='index', type='view', type='table'";
5448 $result = $this->selectArray($query);
5449
5450 if($transaction)
5451 echo "BEGIN TRANSACTION;\r\n";
5452
5453 //iterate through each table
5454 for($i=0; $i<sizeof($result); $i++)
5455 {
5456 $valid = false;
5457 for($j=0; $j<sizeof($tables); $j++)
5458 {
5459 if($result[$i]['tbl_name']==$tables[$j])
5460 $valid = true;
5461 }
5462 if($valid)
5463 {
5464 if($drop)
5465 {
5466 if($comments)
5467 {
5468 echo "\r\n----\r\n";
5469 echo "-- ".$lang['drop']." ".$result[$i]['type']." ".$lang['for']." ".$result[$i]['name']."\r\n";
5470 echo "----\r\n";
5471 }
5472 echo "DROP ".strtoupper($result[$i]['type'])." ".$this->quote_id($result[$i]['name']).";\r\n";
5473 }
5474 if($structure)
5475 {
5476 if($comments)
5477 {
5478 echo "\r\n----\r\n";
5479 if($result[$i]['type']=="table" || $result[$i]['type']=="view")
5480 echo "-- ".ucfirst($result[$i]['type'])." ".$lang['struct_for']." ".$result[$i]['tbl_name']."\r\n";
5481 else // index or trigger
5482 echo "-- ".$lang['struct_for']." ".$result[$i]['type']." ".$result[$i]['name']." ".$lang['on_tbl']." ".$result[$i]['tbl_name']."\r\n";
5483 echo "----\r\n";
5484 }
5485 echo $result[$i]['sql'].";\r\n";
5486 }
5487 if($data && $result[$i]['type']=="table")
5488 {
5489 $query = "SELECT * FROM ".$this->quote_id($result[$i]['tbl_name']);
5490 $table_result = $this->query($query, "assoc");
5491
5492 if($comments)
5493 {
5494 $numRows = $this->numRows($result[$i]['tbl_name']);
5495 echo "\r\n----\r\n";
5496 echo "-- ".$lang['data_dump']." ".$result[$i]['tbl_name'].", ".sprintf($lang['total_rows'], $numRows)."\r\n";
5497 echo "----\r\n";
5498 }
5499 $query = "PRAGMA table_info(".$this->quote_id($result[$i]['tbl_name']).")";
5500 $temp = $this->selectArray($query);
5501 $cols = array();
5502 $cols_quoted = array();
5503 for($z=0; $z<sizeof($temp); $z++)
5504 {
5505 $cols[$z] = $temp[$z][1];
5506 $cols_quoted[$z] = $this->quote_id($temp[$z][1]);
5507 }
5508 while($row = $this->fetch($table_result))
5509 {
5510 $vals = array();
5511 for($y=0; $y<sizeof($cols); $y++)
5512 {
5513 if($row[$cols[$y]] === NULL)
5514 $vals[$cols[$y]] = 'NULL';
5515 else
5516 $vals[$cols[$y]] = $this->quote($row[$cols[$y]]);
5517 }
5518 echo "INSERT INTO ".$this->quote_id($result[$i]['tbl_name'])." (".implode(",", $cols_quoted).") VALUES (".implode(",", $vals).");\r\n";
5519 }
5520 }
5521 }
5522 }
5523 if($transaction)
5524 echo "COMMIT;\r\n";
5525 }
5526}
5527// class MicroTimer (issue #146)
5528// wraps calls to microtime(), calculating the elapsed time and rounding output
5529//
5530class MicroTimer {
5531
5532 private $startTime, $stopTime;
5533
5534 // creates and starts a timer
5535 function __construct()
5536 {
5537 $this->startTime = microtime(true);
5538 }
5539
5540 // stops a timer
5541 public function stop()
5542 {
5543 $this->stopTime = microtime(true);
5544 }
5545
5546 // returns the number of seconds from the timer's creation, or elapsed
5547 // between creation and call to ->stop()
5548 public function elapsed()
5549 {
5550 if ($this->stopTime)
5551 return round($this->stopTime - $this->startTime, 4);
5552
5553 return round(microtime(true) - $this->startTime, 4);
5554 }
5555
5556 // called when using a MicroTimer object as a string
5557 public function __toString()
5558 {
5559 return (string) $this->elapsed();
5560 }
5561
5562}
5563// class Resources (issue #157)
5564// outputs secondary files, such as css and javascript
5565// data is stored gzipped (gzencode) and encoded (base64_encode)
5566//
5567class Resources {
5568
5569 // set this to the file containing getInternalResource;
5570 // currently unused in split mode; set to __FILE__ for built PLA.
5571 public static $embedding_file = __FILE__;
5572
5573 private static $_resources = array(
5574 'css' => array(
5575 'mime' => 'text/css',
5576 'data' => 'resources/phpliteadmin.css',
5577 ),
5578 'javascript' => array(
5579 'mime' => 'text/javascript',
5580 'data' => 'resources/phpliteadmin.js',
5581 ),
5582 'favicon' => array(
5583 'mime' => 'image/x-icon',
5584 'data' => 'resources/favicon.ico',
5585 'base64' => 'true',
5586 ),
5587 );
5588
5589 // outputs the specified resource, if defined in this class.
5590 // the main script should do no further output after calling this function.
5591 public static function output($resource)
5592 {
5593 if (isset(self::$_resources[$resource])) {
5594 $res =& self::$_resources[$resource];
5595
5596 if (function_exists('getInternalResource') && $data = getInternalResource($res['data'])) {
5597 $filename = self::$embedding_file;
5598 } else {
5599 $filename = $res['data'];
5600 }
5601
5602 // use last-modified time as etag; etag must be quoted
5603 $etag = '"' . filemtime($filename) . '"';
5604
5605 // check headers for matching etag; if etag hasn't changed, use the cached version
5606 if (isset($_SERVER['HTTP_IF_NONE_MATCH']) && $_SERVER['HTTP_IF_NONE_MATCH'] == $etag) {
5607 header('HTTP/1.0 304 Not Modified');
5608 return;
5609 }
5610
5611 header('Etag: ' . $etag);
5612
5613 // cache file for at most 30 days
5614 header('Cache-control: max-age=2592000');
5615
5616 // output resource
5617 header('Content-type: ' . $res['mime']);
5618
5619 if (isset($data)) {
5620 if (isset($res['base64'])) {
5621 echo base64_decode($data);
5622 } else {
5623 echo $data;
5624 }
5625 } else {
5626 readfile($filename);
5627 }
5628 }
5629 }
5630
5631}
5632
5633
5634// returns data from internal resources, available in single-file mode
5635function getInternalResource($res) {
5636 $resources = array('resources/phpliteadmin.css'=>array(0=>0,1=>4010,),'resources/phpliteadmin.js'=>array(0=>4010,1=>4178,),'resources/favicon.ico'=>array(0=>8188,1=>1448,),);
5637
5638 if (isset($resources[$res]) && $f = fopen(__FILE__, 'r')) {
5639 fseek($f, __COMPILER_HALT_OFFSET__ + $resources[$res][0]);
5640 $data = fread($f, $resources[$res][1]);
5641 fclose($f);
5642 return $data;
5643 }
5644 return false;
5645}
5646
5647// resources embedded below, do not edit!
5648__halt_compiler() ?>body{margin:0px;padding:0px;font-family:Arial,Helvetica,sans-serif;font-size:14px;color:#000;background-color:#e0ebf6;overflow:auto}.body_tbl td{padding:9px 2px 9px 9px}.left_td{width:100px}a{color:#03F;text-decoration:none;cursor:pointer}a:hover{color:#06F}hr{height:1px;border:0;color:#bbb;background-color:#bbb;width:100%}h1{margin:0px;padding:5px;font-size:24px;background-color:#f3cece;text-align:center;color:#000;border-top-left-radius:5px;border-top-right-radius:5px;-moz-border-radius-topleft:5px;-moz-border-radius-topright:5px}#headerlinks{text-align:center;margin-bottom:10px;padding:5px 15px;border-color:#03F;border-width:1px;border-style:solid;border-left-style:none;border-right-style:none;font-size:12px;background-color:#e0ebf6;font-weight:bold}h1 #version{color:#000;font-size:16px}h1 #logo{color:#000}h2{margin:0px;padding:0px;font-size:14px;margin-bottom:20px}input,select,textarea{font-family:Arial,Helvetica,sans-serif;background-color:#eaeaea;color:#03F;border-color:#03F;border-style:solid;border-width:1px;margin:5px;border-radius:5px;-moz-border-radius:5px;padding:3px}input.btn{cursor:pointer}input.btn:hover{background-color:#ccc}fieldset label{min-width:200px;display:block;float:left}fieldset{padding:15px;border-color:#03F;border-width:1px;border-style:solid;border-radius:5px;-moz-border-radius:5px;background-color:#f9f9f9}#container{padding:10px}#leftNav{min-width:250px;padding:0px;border-color:#03F;border-width:1px;border-style:solid;background-color:#FFF;padding-bottom:15px;border-radius:5px;-moz-border-radius:5px}.viewTable tr td{padding:1px}#loginBox{width:500px;margin-left:auto;margin-right:auto;margin-top:50px;border-color:#03F;border-width:1px;border-style:solid;background-color:#FFF;border-radius:5px;-moz-border-radius:5px}#main{border-color:#03F;border-width:1px;border-style:solid;padding:15px;background-color:#FFF;border-bottom-left-radius:5px;border-bottom-right-radius:5px;border-top-right-radius:5px;-moz-border-radius-bottomleft:5px;-moz-border-radius-bottomright:5px;-moz-border-radius-topright:5px}.td1{background-color:#f9e3e3;text-align:right;font-size:12px;padding-left:10px;padding-right:10px}.td2{background-color:#f3cece;text-align:right;font-size:12px;padding-left:10px;padding-right:10px}.tdheader{border-color:#03F;border-width:1px;border-style:solid;font-weight:bold;font-size:12px;padding-left:10px;padding-right:10px;background-color:#e0ebf6;border-radius:5px;-moz-border-radius:5px}.confirm{border-color:#03F;border-width:1px;border-style:dashed;padding:15px;background-color:#e0ebf6}.tab{display:block;padding:5px;padding-right:8px;padding-left:8px;border-color:#03F;border-width:1px;border-style:solid;margin-right:5px;float:left;border-bottom-style:none;position:relative;top:1px;padding-bottom:4px;background-color:#eaeaea;border-top-left-radius:5px;border-top-right-radius:5px;-moz-border-radius-topleft:5px;-moz-border-radius-topright:5px}.tab_pressed{display:block;padding:5px;padding-right:8px;padding-left:8px;border-color:#03F;border-width:1px;border-style:solid;margin-right:5px;float:left;border-bottom-style:none;position:relative;top:1px;background-color:#FFF;cursor:default;border-top-left-radius:5px;border-top-right-radius:5px;-moz-border-radius-topleft:5px;-moz-border-radius-topright:5px}.helpq{font-size:11px;font-weight:normal}#help_container{padding:0px;font-size:12px;margin-left:auto;margin-right:auto;background-color:#fff}.help_outer{background-color:#FFF;padding:0px;height:300px;position:relative}.help_list{padding:10px;height:auto}.headd{font-size:14px;font-weight:bold;display:block;padding:10px;background-color:#e0ebf6;border-color:#03F;border-width:1px;border-style:solid;border-left-style:none;border-right-style:none}.help_inner{padding:10px}.help_top{display:block;position:absolute;right:10px;bottom:10px}.warning,.delete,.empty,.drop,.delete_db{color:red}.sidebar_table{font-size:11px}.active_table,.active_db{text-decoration:underline}.null{color:#888}.found{background:#FF0;text-decoration:none}
5649function initAutoincrement()
5650{var i=0;while(document.getElementById('i'+i+'_autoincrement')!=undefined)
5651{document.getElementById('i'+i+'_autoincrement').disabled=true;i++;}}
5652function toggleAutoincrement(i)
5653{var type=document.getElementById('i'+i+'_type');var primarykey=document.getElementById('i'+i+'_primarykey');var autoincrement=document.getElementById('i'+i+'_autoincrement');if(!autoincrement)return false;if(type.value=='INTEGER'&&primarykey.checked)
5654autoincrement.disabled=false;else
5655{autoincrement.disabled=true;autoincrement.checked=false;}}
5656function toggleNull(i)
5657{var pk=document.getElementById('i'+i+'_primarykey');var notnull=document.getElementById('i'+i+'_notnull');if(pk.checked)
5658{notnull.disabled=true;notnull.checked=true;}
5659else
5660{notnull.disabled=false;}}
5661function checkAll(field)
5662{var i=0;while(document.getElementById('check_'+i)!=undefined)
5663{document.getElementById('check_'+i).checked=true;i++;}}
5664function uncheckAll(field)
5665{var i=0;while(document.getElementById('check_'+i)!=undefined)
5666{document.getElementById('check_'+i).checked=false;i++;}}
5667function changeIgnore(area,e,u)
5668{if(area.value!="")
5669{if(document.getElementById(e)!=undefined)
5670document.getElementById(e).checked=false;if(document.getElementById(u)!=undefined)
5671document.getElementById(u).checked=false;}}
5672function moveFields()
5673{var fields=document.getElementById("fieldcontainer");var selected=new Array();for(var i=0;i<fields.options.length;i++)
5674if(fields.options[i].selected)
5675selected.push(fields.options[i].value);for(var i=0;i<selected.length;i++)
5676insertAtCaret("queryval",'"'+selected[i].replace(/"/g,'""')+'"');}
5677function insertAtCaret(areaId,text)
5678{var txtarea=document.getElementById(areaId);var scrollPos=txtarea.scrollTop;var strPos=0;var br=((txtarea.selectionStart||txtarea.selectionStart=='0')?"ff":(document.selection?"ie":false));if(br=="ie")
5679{txtarea.focus();var range=document.selection.createRange();range.moveStart('character',-txtarea.value.length);strPos=range.text.length;}
5680else if(br=="ff")
5681strPos=txtarea.selectionStart;var front=(txtarea.value).substring(0,strPos);var back=(txtarea.value).substring(strPos,txtarea.value.length);txtarea.value=front+text+back;strPos=strPos+text.length;if(br=="ie")
5682{txtarea.focus();var range=document.selection.createRange();range.moveStart('character',-txtarea.value.length);range.moveStart('character',strPos);range.moveEnd('character',0);range.select();}
5683else if(br=="ff")
5684{txtarea.selectionStart=strPos;txtarea.selectionEnd=strPos;txtarea.focus();}
5685txtarea.scrollTop=scrollPos;}
5686function notNull(checker)
5687{document.getElementById(checker).checked=false;}
5688function disableText(checker,textie)
5689{if(checker.checked)
5690{document.getElementById(textie).value="";document.getElementById(textie).disabled=true;}
5691else
5692{document.getElementById(textie).disabled=false;}}
5693function toggleExports(val)
5694{document.getElementById("exportoptions_sql").style.display="none";document.getElementById("exportoptions_csv").style.display="none";document.getElementById("exportoptions_"+val).style.display="block";}
5695function toggleImports(val)
5696{document.getElementById("importoptions_sql").style.display="none";document.getElementById("importoptions_csv").style.display="none";document.getElementById("importoptions_"+val).style.display="block";}
5697function openHelp(section)
5698{PopupCenter('?help=1#'+section,"Help Section");}
5699var helpsec=false;function PopupCenter(pageURL,title)
5700{helpsec=window.open(pageURL,title,"toolbar=0,scrollbars=1,location=0,statusbar=0,menubar=0,resizable=0,width=400,height=300");}
5701function checkLike(srchField,selOpt)
5702{if(selOpt=="LIKE%"){var textArea=document.getElementById(srchField);textArea.value="%"+textArea.value+"%";}}
5703function createCORSRequest(method,url)
5704{var xhr=new XMLHttpRequest();if("withCredentials"in xhr)
5705{xhr.open(method,url,true);}
5706else if(typeof XDomainRequest!="undefined")
5707{xhr=new XDomainRequest();xhr.open(method,url);}
5708else
5709{xhr=null;}
5710return xhr;}
5711function checkVersion(installed,url)
5712{var xhr=createCORSRequest('GET',url);if(!xhr)
5713return false;xhr.onload=function()
5714{if(xhr.responseText.split("\n").indexOf(installed)==-1)
5715{document.getElementById('oldVersion').style.display='inline';}};xhr.send();}AAABAAEAEBAAAAEAIAAoBAAAFgAAACgAAAAQAAAAIAAAAAEAIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADwoKZQAAABMAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABEMDJMAAAAdAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAASDg7BAAAASAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAETDw9CCQkJ1QUFBb4AAABjAAAAAQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADgsLVgAAAO8YExP/AAAA7QAAALEAAAARAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABQNDUsAAADJGBMT/xgTE/8AAAD/AAAAuAAAAA8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAZERE8DQoKwhgTE/8QDQ2sGBMT/xgTE/8AAAA/AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAwICHkAAAD/EQwMzQAAAMIAAAD/AAAA7gAAAGEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAOCwtWAAAA8RgTE/8IBQW1AAAA/wAAAP8AAADlAAAAHQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA0KCsEAAAD/EQ8PzAAAAMkAAAD/AAAA/wAAAHoAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAABDAAAA/xgTE/8TDw/FAAAA8gAAAP8AAADqAAAAJgAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAgAAALUAAAD/GBMT/wAAALkAAAD/AAAA/wAAAIkAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAfAAAA4wAAAP8GBgbFAAAA2QAAAP8AAADGAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAF4AAAD5AAAA/RgTE/8AAAD/AAAA0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAQQAAAOIAAAD/AAAA/wAAAHYAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAjAAAArgAAAG4AAAAAAAAAAAAAAAAAAAAA