2020-10-14 18:20:44 +00:00
< ? php
/*
2022-02-06 20:37:02 +00:00
This file is a part of myTinyTodo .
2025-02-20 15:29:04 +00:00
( C ) Copyright 2009 - 2011 , 2020 - 2025 Max Pozdeev < maxpozdeev @ gmail . com >
2022-02-06 20:37:02 +00:00
Licensed under the GNU GPL version 2 or any later . See file COPYRIGHT for details .
2020-10-14 18:20:44 +00:00
*/
2022-01-31 18:49:26 +00:00
// Can be used to upgrade database from myTinyTodo v1.4 or later
2022-12-05 07:45:24 +00:00
$lastVer = '1.8' ;
2020-10-14 18:20:44 +00:00
2022-11-20 13:02:13 +00:00
if ( version_compare ( PHP_VERSION , '7.2.0' ) < 0 ) {
die ( " PHP 7.2 or above is required " );
}
2022-01-31 18:49:26 +00:00
if ( getenv ( 'MTT_ENABLE_DEBUG' ) == 'YES' ) {
2022-02-06 20:37:02 +00:00
set_exception_handler ( 'debugExceptionHandler' );
2024-01-07 11:24:03 +00:00
define ( 'MTT_DEBUG' , true );
2022-01-31 18:49:26 +00:00
}
else {
2022-02-06 20:37:02 +00:00
set_exception_handler ( 'myExceptionHandler' );
2024-01-07 11:24:03 +00:00
define ( 'MTT_DEBUG' , false );
2025-02-20 14:28:47 +00:00
#ini_set('zend.exception_ignore_args', 1); // php 7.4+
2020-10-14 18:20:44 +00:00
}
2022-01-31 18:49:26 +00:00
if ( ! defined ( 'MTTPATH' )) define ( 'MTTPATH' , dirname ( __FILE__ ) . '/' );
if ( ! defined ( 'MTTINC' )) define ( 'MTTINC' , MTTPATH . 'includes/' );
require_once ( MTTINC . 'common.php' );
require_once ( MTTINC . 'class.dbconnection.php' );
require_once ( MTTINC . 'class.config.php' );
2022-02-07 16:08:41 +00:00
require_once ( MTTINC . 'version.php' );
2021-07-28 15:05:14 +00:00
2022-01-31 18:49:26 +00:00
$db = null ;
$ver = '' ;
$error = '' ;
2025-02-20 14:28:47 +00:00
$passwordAskedV14 = false ;
2022-01-31 18:49:26 +00:00
2025-02-20 15:29:04 +00:00
$csrfToken = setupToken ();
if ( $csrfToken == '' || strlen ( $csrfToken ) != 36 ) {
$csrfToken = setSetupToken ();
2022-10-28 19:38:27 +00:00
}
2025-02-20 15:29:04 +00:00
$csrfToken = htmlspecialchars ( $csrfToken );
2022-10-28 19:38:27 +00:00
2022-01-31 18:49:26 +00:00
$configExists = file_exists ( MTTPATH . 'config.php' );
$oldConfigExists = file_exists ( MTTPATH . 'db/config.php' );
2020-10-14 18:20:44 +00:00
2022-12-05 08:40:39 +00:00
$mttVersion = htmlspecialchars ( mytinytodo\Version :: VERSION );
echo " <html><head><meta name='robots' content='noindex,nofollow'><title>myTinyTodo $mttVersion Setup</title></head><body> " ;
2022-02-07 16:08:41 +00:00
echo " <big><b>myTinyTodo $mttVersion Setup</b></big><br><br> " ;
2020-10-14 18:20:44 +00:00
2022-01-31 18:49:26 +00:00
if ( ! $configExists && $oldConfigExists )
{
2025-02-20 14:28:47 +00:00
// First we need to migrate database config of db v1.4
2022-02-06 20:37:02 +00:00
require_once ( MTTPATH . 'db/config.php' );
2025-02-20 15:29:04 +00:00
askPasswordV14 ( $config , $csrfToken );
2025-02-20 14:28:47 +00:00
$passwordAskedV14 = true ;
2022-02-06 20:37:02 +00:00
Config :: loadConfigV14 ( $config );
tryToSaveDBConfig ();
$configExists = true ;
2022-01-31 18:49:26 +00:00
}
if ( $configExists )
{
2022-02-06 20:37:02 +00:00
// No need to migrate database config
require_once ( MTTPATH . 'config.php' );
$db = testConnect ( $error );
if ( ! $db ) {
exitMessage ( " Database connection config file seems to be incorrect. You can remove config.php or edit it manually and then reload setup.<br><br> " .
" <b>Error:</b> " . htmlspecialchars ( $error ) );
}
// Config file v1.7 already exists and set up correctly
$dbtype = MTT_DB_TYPE ;
// Determine current installed db version
$ver = databaseVersion ( $db );
2024-01-07 11:24:03 +00:00
if ( MTT_DEBUG ) {
error_log ( " Database version detected: $ver " );
}
2022-02-06 20:37:02 +00:00
2023-08-03 20:09:32 +00:00
if ( $ver == '' ) {
2024-01-07 11:24:03 +00:00
// Clean install
// Don't load settings from database in init.php
2023-08-03 20:09:32 +00:00
Config :: $noDatabase = true ;
}
else if ( version_compare ( $ver , '1.4' ) < 0 ) {
// Very old or previously failed while install
exitMessage ( htmlspecialchars ( " Can not update. Unsupported database version ( $ver ). " ));
}
else if ( $ver == '1.4' ) {
2022-02-06 20:37:02 +00:00
// Need to upgrade. Do not ask for old password
2024-01-07 11:23:31 +00:00
Config :: $noDatabase = true ; //don't load settings from db
2022-02-06 20:37:02 +00:00
require_once ( MTTPATH . 'db/config.php' );
2025-02-20 14:28:47 +00:00
if ( ! $passwordAskedV14 ) {
2025-02-20 15:29:04 +00:00
askPasswordV14 ( $config , $csrfToken );
2025-02-20 14:28:47 +00:00
$passwordAskedV14 = true ;
}
2022-02-06 20:37:02 +00:00
Config :: loadConfigV14 ( $config );
unset ( $config );
DBConnection :: init ( $db );
}
2023-08-03 20:09:32 +00:00
require_once ( './init.php' );
2025-02-20 14:28:47 +00:00
if ( ! Config :: $noDatabase && ! is_logged () ) {
2023-08-03 20:09:32 +00:00
die ( " Access denied!<br> Disable password protection or Log in. " );
2022-02-06 20:37:02 +00:00
}
2023-08-03 20:09:32 +00:00
2022-01-31 18:49:26 +00:00
}
2020-10-14 18:20:44 +00:00
2022-01-31 18:49:26 +00:00
if ( $ver == '' )
2020-10-14 18:20:44 +00:00
{
2022-02-06 20:37:02 +00:00
$install = trim ( _post ( 'install' ));
if ( $install == '' && $db !== null )
{
# We already have settings file and need to create tables.
2023-08-03 20:09:32 +00:00
exitMessage ( " <form method=post>Click next to create tables in " . htmlspecialchars ( databaseTypeName ( $db )) . " database.<br><br>
2025-02-20 15:29:04 +00:00
< input type = hidden name = stoken value = '$csrfToken' >
2022-10-28 19:38:27 +00:00
< input type = hidden name = install value = create >
< input type = submit value = ' Next ' ></ form > " );
2022-02-06 20:37:02 +00:00
}
elseif ( $install == '' )
{
# Specify database type and connection settings to save.
exitMessage ( "
< form method = post > Select database type to use :< br >< br >
< input type = hidden name = install value = config >
2025-02-20 15:29:04 +00:00
< input type = hidden name = stoken value = '$csrfToken' >
2023-08-03 20:09:32 +00:00
< label >< input type = radio name = db_type value = sqlite checked = checked onclick = \ " document.getElementById('dbsettings').style.display='none' \" > SQLite</label><br><br>
< label >< input type = radio name = db_type value = mysql onclick = \ " document.getElementById('dbsettings').style.display='' \" > MySQL</label><br><br>
< label >< input type = radio name = db_type value = postgres onclick = \ " document.getElementById('dbsettings').style.display='' \" > PostgreSQL (beta)</label><br>
< div id = 'dbsettings' style = 'display:none; margin-left:30px;' >< br >< table >
2022-02-06 20:37:02 +00:00
< tr >< td > Host :</ td >< td >< input name = db_host value = localhost ></ td ></ tr >
< tr >< td > Database :</ td >< td >< input name = db_name value = mytinytodo ></ td ></ tr >
< tr >< td > User :</ td >< td >< input name = db_user value = mtt ></ td ></ tr >
< tr >< td > Password :</ td >< td >< input type = password name = db_password ></ td ></ tr >
< tr >< td > Table prefix :</ td >< td >< input name = db_prefix value = 'mtt_' ></ td ></ tr >
</ table ></ div >< br >< input type = submit value = ' Next ' ></ form >
" );
}
elseif ( $install == 'config' )
{
2025-02-20 15:29:04 +00:00
checkSetupToken ();
2022-02-06 20:37:02 +00:00
# Save configuration
2023-08-03 20:09:32 +00:00
$dbtype = $_POST [ 'db_type' ] ? ? '' ;
if ( $dbtype != 'mysql' && $dbtype != 'postgres' && $dbtype != 'sqlite' ) {
exitMessage ( " Unknown database type $dbtype " );
}
2022-02-06 20:37:02 +00:00
Config :: set ( 'db.type' , $dbtype );
2023-08-03 20:09:32 +00:00
if ( $dbtype == 'mysql' || $dbtype == 'postgres' ) {
2022-02-06 20:37:02 +00:00
Config :: set ( 'db.host' , _post ( 'db_host' ));
Config :: set ( 'db.name' , _post ( 'db_name' ));
Config :: set ( 'db.user' , _post ( 'db_user' ));
Config :: set ( 'db.password' , _post ( 'db_password' ));
Config :: set ( 'db.prefix' , trim ( _post ( 'db_prefix' )));
}
Config :: defineDbConstants ();
$db = testConnect ( $error );
if ( ! $db ) {
exitMessage ( " Database connection error: " . htmlspecialchars ( $error ));
}
if ( defined ( 'MTT_DB_DRIVER' )) {
Config :: set ( 'db.driver' , MTT_DB_DRIVER );
}
tryToSaveDBConfig ();
2022-10-28 19:38:27 +00:00
exitMessage ( " <form method=post> This will create myTinyTodo database <br><br>
< input type = hidden name = install value = create >
2025-02-20 15:29:04 +00:00
< input type = hidden name = stoken value = '$csrfToken' >
2022-10-28 19:38:27 +00:00
< input type = submit value = ' Install ' ></ form > " );
2022-02-06 20:37:02 +00:00
}
elseif ( $install == 'create' )
{
2025-02-20 15:29:04 +00:00
checkSetupToken ();
2022-02-06 20:37:02 +00:00
# install database
2023-08-07 18:25:24 +00:00
createAllTables ( $db , $dbtype ); # throws
2022-02-06 20:37:02 +00:00
# create default list
$db -> ex ( " INSERT INTO { $db -> prefix } lists (uuid,name,d_created,taskview) VALUES (?,?,?,?) " , array ( generateUUID (), 'Todo' , time (), 1 ) );
Config :: save ();
}
else {
exitMessage ( " Unknown action " );
}
2022-01-31 18:49:26 +00:00
}
elseif ( $ver == $lastVer )
{
2022-02-06 20:37:02 +00:00
exitMessage ( " Installed version does not require database update. " );
2022-01-31 18:49:26 +00:00
}
else
{
2022-12-05 07:45:24 +00:00
if ( ! in_array ( $ver , array ( '1.4' , '1.7' ))) {
2022-02-06 20:37:02 +00:00
exitMessage ( htmlspecialchars ( " Can not update. Unsupported database version ( $ver ). " ));
}
if ( ! isset ( $_POST [ 'update' ])) {
2022-11-03 12:40:14 +00:00
exitMessage ( htmlspecialchars ( " Update database v $ver to v $lastVer " ) . " <br><br>
< form name = frm method = post >
2022-10-28 19:38:27 +00:00
< input type = hidden name = update value = 1 >
2025-02-20 15:29:04 +00:00
< input type = hidden name = stoken value = '$csrfToken' >
2022-10-28 19:38:27 +00:00
< input type = submit value = ' Update ' >
</ form > " );
2022-02-06 20:37:02 +00:00
}
# update process
2025-02-20 15:29:04 +00:00
checkSetupToken ();
2022-12-05 07:45:24 +00:00
if ( $ver == '1.4' ) {
2022-02-06 20:37:02 +00:00
update_14_17 ( $db , $dbtype );
2022-12-05 07:45:24 +00:00
update_17_18 ( $db , $dbtype );
}
elseif ( $ver == '1.7' ) {
update_17_18 ( $db , $dbtype );
2022-02-06 20:37:02 +00:00
}
2022-01-31 18:49:26 +00:00
}
2022-02-06 11:15:21 +00:00
echo " Done<br><br> <b>Attention!</b> Delete this file for security reasons. <br><br> Go to <a href=' " . htmlspecialchars ( url_dir ( getRequestUri ())) . " '>homepage</a>. " ;
2022-01-31 18:49:26 +00:00
printFooter ();
2025-02-20 15:29:04 +00:00
function setupToken ()
2022-10-28 19:38:27 +00:00
{
return $_COOKIE [ 'mtt-s-token' ] ? ? '' ;
}
2025-02-20 15:29:04 +00:00
function setSetupToken () : string
2022-10-28 19:38:27 +00:00
{
2025-02-20 15:29:04 +00:00
$token = randomString ( 36 );
2022-11-02 21:20:32 +00:00
if ( PHP_VERSION_ID < 70300 ) {
setcookie ( 'mtt-s-token' , $token , 0 , url_dir ( getRequestUri ()) . '; samesite=lax' , '' , false , true ) ;
}
else {
2025-02-17 09:31:10 +00:00
/** @disregard P1006 available in php 7.3 */
2022-11-02 21:20:32 +00:00
setcookie ( 'mtt-s-token' , $token , [
'path' => url_dir ( getRequestUri ()),
'httponly' => true ,
'samesite' => 'lax'
]);
}
2022-10-28 19:38:27 +00:00
$_COOKIE [ 'mtt-s-token' ] = $token ;
return $token ;
}
2025-02-20 14:28:47 +00:00
2025-02-20 15:29:04 +00:00
function checkSetupToken ()
2022-10-28 19:38:27 +00:00
{
$token = $_POST [ 'stoken' ] ? ? '' ;
2025-02-20 15:29:04 +00:00
if ( $token == '' || $token != setupToken () ) {
2022-10-28 19:38:27 +00:00
die ( " Access denied! No token provided. " );
}
}
2025-02-20 14:28:47 +00:00
function askPasswordV14 (
#[\SensitiveParameter]
array $config ,
2025-02-20 15:29:04 +00:00
string $csrfToken )
2025-02-20 14:28:47 +00:00
{
if ( ! isset ( $config [ 'password' ]) || $config [ 'password' ] == '' ) {
return ;
}
if ( isset ( $_POST [ 'configpassword' ])) {
2025-02-20 15:29:04 +00:00
checkSetupToken ();
2025-02-20 14:28:47 +00:00
}
if ( isset ( $_COOKIE [ 'mtt-v14token' ])) {
if ( validateTokenV14 ( $config , $_COOKIE [ 'mtt-v14token' ])) {
return ; //authorized
}
if ( MTT_DEBUG ) error_log ( " Failed validation of v14token " );
}
if ( ! isset ( $_POST [ 'configpassword' ]) || $_POST [ 'configpassword' ] != $config [ 'password' ] ) {
exitMessage ( " Enter current password to continue.
2025-02-20 15:29:04 +00:00
< form method = post >< input type = hidden name = stoken value = '$csrfToken' >
2025-02-20 14:28:47 +00:00
< input type = password name = configpassword > < input type = submit value = ' Continue ' ></ form > " );
}
$token = generateTokenV14 ( $config );
if ( PHP_VERSION_ID < 70300 ) {
setcookie ( 'mtt-v14token' , $token , 0 , url_dir ( getRequestUri ()) . '; samesite=lax' , '' , false , true ) ;
}
else {
/** @disregard P1006 available in php 7.3 */
setcookie ( 'mtt-v14token' , $token , [
'path' => url_dir ( getRequestUri ()),
'httponly' => true ,
'samesite' => 'lax'
]);
}
}
function generateTokenV14 (
#[\SensitiveParameter]
array $config ) : ? string
{
if ( ! isset ( $config [ 'password' ]) || $config [ 'password' ] == '' ) {
return null ;
}
$payload = base64_encode ( json_encode ([
'exp' => time () + 300 # 5 min lifetime
]));
return $payload . '.' . base64_encode ( hash_hmac ( 'sha256' , $payload , $config [ 'password' ], true ));
}
function validateTokenV14 (
#[\SensitiveParameter]
array $config ,
string $token ) : bool
{
if ( ! isset ( $config [ 'password' ]) || $config [ 'password' ] == '' ) {
return true ;
}
$parts = explode ( '.' , $token );
if ( count ( $parts ) != 2 ) {
return false ;
}
$signature = base64_decode ( $parts [ 1 ]); //binary
if ( $signature === false ) {
return false ;
}
if ( ! hash_equals ( $signature , hash_hmac ( 'sha256' , $parts [ 0 ], $config [ 'password' ], true )) ) {
return false ;
}
$payload = json_decode ( base64_decode ( $parts [ 0 ]), true );
if ( ! isset ( $payload [ 'exp' ]) || time () > $payload [ 'exp' ]) {
return false ;
}
return true ;
}
2022-10-28 19:38:27 +00:00
2022-01-31 18:49:26 +00:00
function createAllTables ( $db , $dbtype )
{
2022-02-06 20:37:02 +00:00
if ( $dbtype == 'mysql' ) {
createMysqlTables ( $db );
}
2023-08-03 20:09:32 +00:00
else if ( $dbtype == 'postgres' ) {
createPostgresTables ( $db );
}
2022-02-06 20:37:02 +00:00
else {
createSqliteTables ( $db );
}
2022-01-31 18:49:26 +00:00
}
2023-08-03 20:09:32 +00:00
/* ===== mysql ========================================================= */
2023-08-08 18:34:53 +00:00
function createMysqlTables ( Database_Abstract $db )
2022-01-31 18:49:26 +00:00
{
2023-08-08 18:34:53 +00:00
//$collation = hasMysqlUnicode520($db) ? 'utf8mb4_unicode_520_ci' : 'utf8mb4_unicode_ci';
$collation = 'utf8mb4_unicode_520_ci' ;
2024-02-04 21:02:38 +00:00
//TODO: use BIGINT for time() timestamp to avoid the Year-2038 problem (2106 here)
2023-08-08 18:34:53 +00:00
// Mysql does not support transactions while executing DDL
2022-02-06 20:37:02 +00:00
$db -> ex (
2020-10-14 18:20:44 +00:00
" CREATE TABLE { $db -> prefix } lists (
2022-02-06 20:37:02 +00:00
`id` INT UNSIGNED NOT NULL auto_increment ,
2023-08-08 18:34:53 +00:00
`uuid` CHAR ( 36 ) CHARACTER SET latin1 NOT NULL default '' ,
2022-02-06 20:37:02 +00:00
`ow` INT NOT NULL default 0 ,
2022-07-12 19:44:50 +00:00
`name` VARCHAR ( 250 ) NOT NULL default '' ,
2022-02-06 20:37:02 +00:00
`d_created` INT UNSIGNED NOT NULL default 0 ,
`d_edited` INT UNSIGNED NOT NULL default 0 ,
`sorting` TINYINT UNSIGNED NOT NULL default 0 ,
`published` TINYINT UNSIGNED NOT NULL default 0 ,
`taskview` INT UNSIGNED NOT NULL default 0 ,
2022-02-07 21:04:55 +00:00
`extra` TEXT ,
2022-02-06 20:37:02 +00:00
PRIMARY KEY ( `id` ),
UNIQUE KEY ( `uuid` )
2023-08-08 18:34:53 +00:00
) CHARSET = utf8mb4 COLLATE $collation " );
2020-10-14 18:20:44 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2020-10-14 18:20:44 +00:00
" CREATE TABLE { $db -> prefix } todolist (
2022-02-06 20:37:02 +00:00
`id` INT UNSIGNED NOT NULL auto_increment ,
2023-08-08 18:34:53 +00:00
`uuid` CHAR ( 36 ) CHARACTER SET latin1 NOT NULL default '' ,
2022-02-06 20:37:02 +00:00
`list_id` INT UNSIGNED NOT NULL default 0 ,
`d_created` INT UNSIGNED NOT NULL default 0 , /* time() timestamp */
`d_completed` INT UNSIGNED NOT NULL default 0 , /* time() timestamp */
`d_edited` INT UNSIGNED NOT NULL default 0 , /* time() timestamp */
`compl` TINYINT UNSIGNED NOT NULL default 0 ,
`title` VARCHAR ( 250 ) NOT NULL ,
`note` TEXT ,
2022-07-10 12:35:19 +00:00
`prio` TINYINT NOT NULL default 0 , /* priority -,0,+ */
`ow` INT NOT NULL default 0 , /* order weight */
2022-02-06 20:37:02 +00:00
`duedate` DATE default NULL ,
PRIMARY KEY ( `id` ),
KEY ( `list_id` ),
UNIQUE KEY ( `uuid` )
2023-08-08 18:34:53 +00:00
) CHARSET = utf8mb4 COLLATE $collation " );
2020-10-14 18:20:44 +00:00
2023-08-07 18:25:24 +00:00
// Max length of varchar of utf8mb4 with UNIQUE index is 191 until Mysql 5.7 and MariaDB 10.2
2022-02-06 20:37:02 +00:00
$db -> ex (
2020-10-14 18:20:44 +00:00
" CREATE TABLE { $db -> prefix } tags (
2022-02-06 20:37:02 +00:00
`id` INT UNSIGNED NOT NULL auto_increment ,
2023-03-24 15:19:21 +00:00
`name` VARCHAR ( 250 ) NOT NULL default '' ,
2022-02-06 20:37:02 +00:00
PRIMARY KEY ( `id` ),
UNIQUE KEY `name` ( `name` )
2023-08-08 18:34:53 +00:00
) CHARSET = utf8mb4 COLLATE $collation " );
2020-10-14 18:20:44 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2020-10-14 18:20:44 +00:00
" CREATE TABLE { $db -> prefix } tag2task (
2022-02-06 20:37:02 +00:00
`tag_id` INT UNSIGNED NOT NULL ,
`task_id` INT UNSIGNED NOT NULL ,
`list_id` INT UNSIGNED NOT NULL ,
KEY ( `tag_id` ),
KEY ( `task_id` ),
2022-07-10 12:35:19 +00:00
KEY ( `list_id` ) /* for tagcloud */
2023-08-08 18:34:53 +00:00
) CHARSET = utf8mb4 COLLATE $collation " );
2020-10-14 18:20:44 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2021-07-28 15:05:14 +00:00
" CREATE TABLE { $db -> prefix } settings (
2023-08-08 18:34:53 +00:00
`param_key` VARCHAR ( 250 ) CHARACTER SET latin1 NOT NULL default '' ,
2022-02-06 20:37:02 +00:00
`param_value` TEXT ,
UNIQUE KEY `param_key` ( `param_key` )
2023-08-08 18:34:53 +00:00
) CHARSET = utf8mb4 COLLATE $collation " );
2021-07-28 15:05:14 +00:00
2021-07-29 13:52:44 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2021-07-29 13:52:44 +00:00
" CREATE TABLE { $db -> prefix } sessions (
2023-08-08 18:34:53 +00:00
`id` VARCHAR ( 64 ) CHARACTER SET latin1 NOT NULL default '' , /* upto 64 bytes for sha256 */
2022-02-06 20:37:02 +00:00
`data` TEXT ,
2022-07-10 12:35:19 +00:00
`last_access` INT UNSIGNED NOT NULL default 0 , /* time() timestamp */
`expires` INT UNSIGNED NOT NULL default 0 , /* time() timestamp */
2022-02-06 20:37:02 +00:00
UNIQUE KEY `id` ( `id` )
2023-08-08 18:34:53 +00:00
) CHARSET = utf8mb4 COLLATE $collation " );
2022-01-31 18:49:26 +00:00
}
2021-07-29 13:52:44 +00:00
2023-08-03 20:09:32 +00:00
/* ===== postgres =============================================== */
2023-08-08 18:34:53 +00:00
function createPostgresTables ( Database_Abstract $db )
2023-08-03 20:09:32 +00:00
{
2024-02-04 21:02:38 +00:00
//TODO: use BIGINT for time() timestamp to avoid the Year-2038 problem
2023-08-03 20:09:32 +00:00
$db -> ex (
" CREATE TABLE { $db -> prefix } lists (
id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
uuid CHAR ( 36 ) NOT NULL default '' ,
ow INTEGER NOT NULL default 0 ,
name VARCHAR ( 250 ) NOT NULL default '' ,
d_created INTEGER NOT NULL default 0 ,
d_edited INTEGER NOT NULL default 0 ,
sorting SMALLINT NOT NULL default 0 ,
published SMALLINT NOT NULL default 0 ,
taskview INTEGER NOT NULL default 0 ,
extra TEXT
) " );
$db -> ex ( " CREATE UNIQUE INDEX { $db -> prefix } lists_uuid ON { $db -> prefix } lists (uuid) " );
$db -> ex (
" CREATE TABLE { $db -> prefix } todolist (
id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
uuid CHAR ( 36 ) NOT NULL default '' ,
list_id INTEGER NOT NULL default 0 ,
d_created INTEGER NOT NULL default 0 ,
d_completed INTEGER NOT NULL default 0 ,
d_edited INTEGER NOT NULL default 0 ,
compl SMALLINT NOT NULL default 0 ,
title VARCHAR ( 250 ) NOT NULL default '' ,
note TEXT default NULL ,
prio SMALLINT NOT NULL default 0 ,
ow INTEGER NOT NULL default 0 ,
duedate DATE default NULL
) " );
$db -> ex ( " CREATE INDEX { $db -> prefix } todo_list_id ON { $db -> prefix } todolist (list_id) " );
$db -> ex ( " CREATE UNIQUE INDEX { $db -> prefix } todo_uuid ON { $db -> prefix } todolist (uuid) " );
$db -> ex (
" CREATE TABLE { $db -> prefix } tags (
id INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
name VARCHAR ( 250 ) NOT NULL DEFAULT ''
) " );
2023-09-06 19:30:57 +00:00
$db -> ex ( " CREATE UNIQUE INDEX { $db -> prefix } tags_lower_name ON { $db -> prefix } tags ((LOWER(name))) " );
2023-08-03 20:09:32 +00:00
$db -> ex (
" CREATE TABLE { $db -> prefix } tag2task (
tag_id INTEGER NOT NULL ,
task_id INTEGER NOT NULL ,
list_id INTEGER NOT NULL
) " );
$db -> ex ( " CREATE INDEX { $db -> prefix } tag2task_tag_id ON { $db -> prefix } tag2task (tag_id) " );
$db -> ex ( " CREATE INDEX { $db -> prefix } tag2task_task_id ON { $db -> prefix } tag2task (task_id) " );
$db -> ex ( " CREATE INDEX { $db -> prefix } tag2task_list_id ON { $db -> prefix } tag2task (list_id) " );
$db -> ex (
" CREATE TABLE { $db -> prefix } settings (
param_key VARCHAR ( 250 ) NOT NULL default '' ,
param_value TEXT
) " );
$db -> ex ( " CREATE UNIQUE INDEX { $db -> prefix } settings_key ON { $db -> prefix } settings (param_key) " );
$db -> ex (
" CREATE TABLE { $db -> prefix } sessions (
id VARCHAR ( 64 ) NOT NULL default '' ,
data TEXT ,
last_access INTEGER NOT NULL default 0 ,
expires INTEGER NOT NULL default 0
) " );
$db -> ex ( " CREATE UNIQUE INDEX { $db -> prefix } sessions_id ON { $db -> prefix } sessions (id) " );
}
/* ===== sqlite =============================================== */
2023-08-08 18:34:53 +00:00
function createSqliteTables ( Database_Abstract $db )
2022-01-31 18:49:26 +00:00
{
2022-02-06 20:37:02 +00:00
$db -> ex (
2020-10-14 18:20:44 +00:00
" CREATE TABLE { $db -> prefix } lists (
2022-02-06 20:37:02 +00:00
id INTEGER PRIMARY KEY ,
uuid CHAR ( 36 ) NOT NULL ,
ow INTEGER NOT NULL default 0 ,
2023-03-24 15:19:21 +00:00
name VARCHAR ( 250 ) NOT NULL ,
2022-02-06 20:37:02 +00:00
d_created INTEGER UNSIGNED NOT NULL default 0 ,
d_edited INTEGER UNSIGNED NOT NULL default 0 ,
sorting TINYINT UNSIGNED NOT NULL default 0 ,
published TINYINT UNSIGNED NOT NULL default 0 ,
2022-02-07 21:04:55 +00:00
taskview INTEGER UNSIGNED NOT NULL default 0 ,
extra TEXT
2020-10-14 18:20:44 +00:00
) " );
2022-02-06 20:37:02 +00:00
$db -> ex ( " CREATE UNIQUE INDEX lists_uuid ON { $db -> prefix } lists (uuid) " );
2020-10-14 18:20:44 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2020-10-14 18:20:44 +00:00
" CREATE TABLE { $db -> prefix } todolist (
2022-02-06 20:37:02 +00:00
id INTEGER PRIMARY KEY ,
2022-12-05 07:45:24 +00:00
uuid CHAR ( 36 ) NOT NULL default '' ,
2022-02-06 20:37:02 +00:00
list_id INTEGER UNSIGNED NOT NULL default 0 ,
d_created INTEGER UNSIGNED NOT NULL default 0 ,
d_completed INTEGER UNSIGNED NOT NULL default 0 ,
d_edited INTEGER UNSIGNED NOT NULL default 0 ,
compl TINYINT UNSIGNED NOT NULL default 0 ,
2022-12-05 07:45:24 +00:00
title VARCHAR ( 250 ) NOT NULL default '' COLLATE UTF8CI ,
note TEXT COLLATE UTF8CI default NULL ,
2022-02-06 20:37:02 +00:00
prio TINYINT NOT NULL default 0 ,
ow INTEGER NOT NULL default 0 ,
duedate DATE default NULL
2020-10-14 18:20:44 +00:00
) " );
2022-02-06 20:37:02 +00:00
$db -> ex ( " CREATE INDEX todo_list_id ON { $db -> prefix } todolist (list_id) " );
$db -> ex ( " CREATE UNIQUE INDEX todo_uuid ON { $db -> prefix } todolist (uuid) " );
2020-10-14 18:20:44 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2020-10-14 18:20:44 +00:00
" CREATE TABLE { $db -> prefix } tags (
2022-02-06 20:37:02 +00:00
id INTEGER PRIMARY KEY AUTOINCREMENT ,
2023-08-03 20:09:32 +00:00
name VARCHAR ( 250 ) NOT NULL DEFAULT '' COLLATE UTF8CI
2020-10-14 18:20:44 +00:00
) " );
2022-12-05 07:45:24 +00:00
$db -> ex ( " CREATE INDEX tags_name ON { $db -> prefix } tags (name) " ); //NB: unique in mysql
2020-10-14 18:20:44 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2020-10-14 18:20:44 +00:00
" CREATE TABLE { $db -> prefix } tag2task (
2022-02-06 20:37:02 +00:00
tag_id INTEGER NOT NULL ,
task_id INTEGER NOT NULL ,
list_id INTEGER NOT NULL
2020-10-14 18:20:44 +00:00
) " );
2022-02-06 20:37:02 +00:00
$db -> ex ( " CREATE INDEX tag2task_tag_id ON { $db -> prefix } tag2task (tag_id) " );
$db -> ex ( " CREATE INDEX tag2task_task_id ON { $db -> prefix } tag2task (task_id) " );
2022-07-10 12:35:19 +00:00
$db -> ex ( " CREATE INDEX tag2task_list_id ON { $db -> prefix } tag2task (list_id) " ); /* for tagcloud */
2020-10-14 18:20:44 +00:00
2021-07-28 15:05:14 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2021-07-28 15:05:14 +00:00
" CREATE TABLE { $db -> prefix } settings (
2022-07-12 19:44:50 +00:00
param_key VARCHAR ( 250 ) NOT NULL default '' ,
2022-02-06 20:37:02 +00:00
param_value TEXT
2021-07-28 15:05:14 +00:00
) " );
2022-02-06 20:37:02 +00:00
$db -> ex ( " CREATE UNIQUE INDEX settings_key ON { $db -> prefix } settings (param_key COLLATE NOCASE) " );
2021-07-28 15:05:14 +00:00
2021-07-29 13:52:44 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2021-07-29 13:52:44 +00:00
" CREATE TABLE { $db -> prefix } sessions (
2022-02-06 20:37:02 +00:00
id VARCHAR ( 64 ) NOT NULL default '' ,
data TEXT ,
last_access INTEGER UNSIGNED NOT NULL default 0 ,
expires INTEGER UNSIGNED NOT NULL default 0
2021-07-29 13:52:44 +00:00
) " );
2022-02-06 20:37:02 +00:00
$db -> ex ( " CREATE UNIQUE INDEX sessions_id ON { $db -> prefix } sessions (id COLLATE NOCASE) " );
2020-10-14 18:20:44 +00:00
}
2022-01-31 18:49:26 +00:00
function databaseVersion ( Database_Abstract $db ) : string
2020-10-14 18:20:44 +00:00
{
2022-02-06 20:37:02 +00:00
if ( ! $db ) return '' ;
if ( ! $db -> tableExists ( $db -> prefix . 'todolist' ) ) return '' ;
$v = '1.0' ;
if ( ! $db -> tableExists ( $db -> prefix . 'tags' ) ) return $v ;
$v = '1.1' ;
if ( ! $db -> tableFieldExists ( $db -> prefix . 'todolist' , 'duedate' ) ) return $v ;
$v = '1.2' ;
if ( ! $db -> tableExists ( $db -> prefix . 'lists' ) ) return $v ;
$v = '1.3.0' ;
if ( ! $db -> tableFieldExists ( $db -> prefix . 'todolist' , 'd_completed' ) ) return $v ;
$v = '1.3.1' ;
if ( ! $db -> tableFieldExists ( $db -> prefix . 'todolist' , 'd_edited' ) ) return $v ;
$v = '1.4' ;
if ( ! $db -> tableExists ( $db -> prefix . 'settings' ) ) return $v ;
$v = '1.7' ;
2023-03-24 15:19:21 +00:00
if ( $db -> tableFieldExists ( $db -> prefix . 'todolist' , 'tags' ) ) return $v ;
$v = '1.8' ;
2022-02-06 20:37:02 +00:00
return $v ;
2020-10-14 18:20:44 +00:00
}
2023-08-08 18:34:53 +00:00
function hasMysqlUnicode520 ( Database_Abstract $db ) : bool
{
$r = $db -> sq ( " SHOW COLLATION WHERE Charset='utf8mb4' and Collation='utf8mb4_unicode_520_ci' " );
return $r ? true : false ;
}
2020-10-14 18:20:44 +00:00
function exitMessage ( $s )
{
2022-02-06 20:37:02 +00:00
echo $s ;
printFooter ();
exit ;
2020-10-14 18:20:44 +00:00
}
function printFooter ()
{
2022-02-06 20:37:02 +00:00
echo " </body></html> " ;
2020-10-14 18:20:44 +00:00
}
2022-01-31 18:49:26 +00:00
function tryToSaveDbConfig ()
2020-10-14 18:20:44 +00:00
{
2022-02-06 20:37:02 +00:00
if ( ! file_exists ( MTTPATH . 'config.php' )) {
@ touch ( MTTPATH . 'config.php' );
}
if ( ! is_writable ( MTTPATH . 'config.php' )) {
exitMessage ( " Database connection config file ('config.php') is not writable. You need to edit it manually, set contents to this and run setup once more. <br><br> \n " .
" <textarea id='contents' style='width:90%; min-height:300px;'> \n " .
htmlspecialchars ( Config :: dbConfigAsFileContents ()) .
" </textarea> \n " .
" <script type='text/javascript'>document.getElementById('contents').select();</script> "
);
}
Config :: saveDbConfig ();
2020-10-14 18:20:44 +00:00
}
function testConnect ( & $error )
{
2022-02-06 20:37:02 +00:00
$db = null ;
try
{
2022-11-28 21:27:51 +00:00
if ( ! defined ( 'MTT_DB_TYPE' )) {
throw new Exception ( " MTT_DB_TYPE is not defined " );
}
2022-02-06 20:37:02 +00:00
if ( MTT_DB_TYPE == 'mysql' )
{
$hasPDO = false ;
$hasMysqli = false ;
if ( defined ( 'PDO::MYSQL_ATTR_FOUND_ROWS' )) {
$hasPDO = true ;
}
if ( function_exists ( " mysqli_connect " )) {
$hasMysqli = true ;
}
$driver = '' ;
if ( defined ( 'MTT_DB_DRIVER' )) {
// forced to use specific mysql interface
if ( in_array ( MTT_DB_DRIVER , [ 'mysqli' , 'pdo' , '' ]) ) {
$driver = MTT_DB_DRIVER ;
if ( $driver == '' ) $driver = 'pdo' ; // default
}
else {
throw new Exception ( " Unknown database driver " );
}
}
if ( $driver == '' ) {
// auto-detect driver
if ( $hasPDO ) $driver = 'pdo' ;
else if ( $hasMysqli ) $driver = 'mysqli' ;
}
2022-02-08 18:06:50 +00:00
$db = null ;
2022-02-06 20:37:02 +00:00
if ( $driver == 'mysqli' ) {
if ( $hasMysqli ) {
require_once ( MTTINC . 'class.db.mysqli.php' );
if ( ! defined ( 'MTT_DB_DRIVER' )) define ( 'MTT_DB_DRIVER' , 'mysqli' );
2022-02-08 18:06:50 +00:00
$db = new Database_Mysqli ();
2022-02-06 20:37:02 +00:00
}
else {
throw new Exception ( " Required PHP extension 'MySQLi' is not installed. " );
}
}
else {
if ( $hasPDO ) {
require_once ( MTTINC . 'class.db.mysql.php' );
if ( ! defined ( 'MTT_DB_DRIVER' )) define ( 'MTT_DB_DRIVER' , '' ); // set pdo?
2022-02-08 18:06:50 +00:00
$db = new Database_Mysql ();
2022-02-06 20:37:02 +00:00
}
else {
throw new Exception ( " Required PHP extension 'PDO_MySQL' is not installed. " );
}
}
foreach ([ 'MTT_DB_HOST' , 'MTT_DB_USER' , 'MTT_DB_PASSWORD' , 'MTT_DB_NAME' , 'MTT_DB_PREFIX' ] as $c ) {
if ( ! defined ( $c )) throw new Exception ( " $c is not defined " );
}
2023-08-03 20:09:32 +00:00
$db -> connect ([
2022-02-06 20:37:02 +00:00
'host' => MTT_DB_HOST ,
'user' => MTT_DB_USER ,
'password' => MTT_DB_PASSWORD ,
'db' => MTT_DB_NAME
2023-08-03 20:09:32 +00:00
]);
}
else if ( MTT_DB_TYPE == 'postgres' )
{
if ( ! defined ( 'PDO::PGSQL_ATTR_DISABLE_PREPARES' )) {
throw new Exception ( " Required PHP extension 'PDO_PostgreSQL' is not installed. " );
}
require_once ( MTTINC . 'class.db.postgres.php' );
foreach ([ 'MTT_DB_HOST' , 'MTT_DB_USER' , 'MTT_DB_PASSWORD' , 'MTT_DB_NAME' , 'MTT_DB_PREFIX' ] as $c ) {
if ( ! defined ( $c )) throw new Exception ( " $c is not defined " );
}
$db = new Database_Postgres ;
$db -> connect ([
'host' => MTT_DB_HOST ,
'user' => MTT_DB_USER ,
'password' => MTT_DB_PASSWORD ,
'db' => MTT_DB_NAME
]);
2022-02-06 20:37:02 +00:00
}
else if ( MTT_DB_TYPE == 'sqlite' )
{
if ( false === $f = @ fopen ( MTTPATH . 'db/todolist.db' , 'a+' )) {
throw new Exception ( " database file is not readable/writable " );
}
else {
fclose ( $f );
}
if ( ! is_writable ( MTTPATH . 'db/' )) {
throw new Exception ( " database directory ('db') is not writable " );
}
require_once ( MTTINC . 'class.db.sqlite3.php' );
$db = new Database_Sqlite3 ;
2023-08-03 20:09:32 +00:00
$db -> connect ([
'filename' => MTTPATH . 'db/todolist.db'
]);
2022-02-06 20:37:02 +00:00
}
else {
2022-11-28 21:27:51 +00:00
throw new Exception ( " Unsupported database type: " . MTT_DB_TYPE );
2022-02-06 20:37:02 +00:00
}
if ( ! defined ( 'MTT_DB_PREFIX' )) define ( 'MTT_DB_PREFIX' , '' );
$db -> setPrefix ( MTT_DB_PREFIX );
}
catch ( Exception $e ) {
//if (MTT_DEBUG) throw $e;
$error = $e -> getMessage ();
return null ;
}
$error = '' ;
return $db ;
2020-10-14 18:20:44 +00:00
}
2023-08-07 18:25:24 +00:00
function debugExceptionHandler ( Throwable $e )
2021-09-20 11:30:12 +00:00
{
2022-02-06 20:37:02 +00:00
echo '<br><b>Error (' . htmlspecialchars ( get_class ( $e )) . '):</b> \'' . htmlspecialchars ( $e -> getMessage ()) . '\' in <i>' . htmlspecialchars ( $e -> getFile () . ':' . $e -> getLine ()) . '</i>' .
" \n <pre> " . htmlspecialchars ( $e -> getTraceAsString ()) . " </pre> \n " ;
exit ;
2021-09-20 11:30:12 +00:00
}
2023-08-07 18:25:24 +00:00
function myExceptionHandler ( Throwable $e )
2020-10-14 18:20:44 +00:00
{
2023-08-07 18:25:24 +00:00
$called = '' ;
foreach ( $e -> getTrace () as $a ) {
if ( $a [ 'file' ] == __FILE__ ) {
$called = " in <i> " . htmlspecialchars ( basename ( $a [ 'file' ]) . ':' . $a [ 'line' ]) . " </i> " ;
break ;
}
}
echo '<br><b>Error:</b> \'' . htmlspecialchars ( $e -> getMessage ()) . '\'' . $called ;
2022-02-06 20:37:02 +00:00
exit ;
2020-10-14 18:20:44 +00:00
}
2023-08-03 20:09:32 +00:00
function databaseTypeName ( Database_Abstract $db )
{
2023-08-07 18:25:24 +00:00
switch ( $db :: DBTYPE ) {
case DBConnection :: DBTYPE_MYSQL : return " MySQL " ;
case DBConnection :: DBTYPE_POSTGRES : return " PostgreSQL " ;
case DBConnection :: DBTYPE_SQLITE : return " SQLite " ;
default : throw new Exception ( " Unsupported database type: " . $db :: DBTYPE );
2023-08-03 20:09:32 +00:00
}
}
2020-10-14 18:20:44 +00:00
2021-07-28 15:05:14 +00:00
### update v1.4 to v1.7 ##########
function update_14_17 ( Database_Abstract $db , $dbtype )
{
2022-02-06 20:37:02 +00:00
$db -> ex ( " BEGIN " );
2021-07-29 13:52:44 +00:00
2022-02-06 20:37:02 +00:00
if ( $dbtype == 'mysql' )
{
2022-02-07 21:04:55 +00:00
$db -> ex ( " ALTER TABLE { $db -> prefix } lists ADD `extra` TEXT " );
2022-07-12 19:44:50 +00:00
# increase the length of list and tag name
2023-03-24 15:19:21 +00:00
# (not applicable to sqlite because it uses VARCHAR fields of any length as TEXT)
2022-07-12 19:44:50 +00:00
$db -> ex ( " ALTER TABLE { $db -> prefix } todolist CHANGE `tags` `tags` VARCHAR(2000) NOT NULL default '' " );
$db -> ex ( " ALTER TABLE { $db -> prefix } tags CHANGE `name` `name` VARCHAR(250) NOT NULL default '' " );
$db -> ex ( " ALTER TABLE { $db -> prefix } lists CHANGE `name` `name` VARCHAR(250) NOT NULL default '' " );
2022-02-06 20:37:02 +00:00
# convert charset to utf8mb4
2021-07-28 15:50:23 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex ( " ALTER TABLE { $db -> prefix } lists CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci " );
$db -> ex ( " ALTER TABLE { $db -> prefix } todolist CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci " );
$db -> ex ( " ALTER TABLE { $db -> prefix } tags CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci " );
$db -> ex ( " ALTER TABLE { $db -> prefix } tag2task CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci " );
2021-07-28 15:50:23 +00:00
2022-02-06 20:37:02 +00:00
# create settings table
2021-07-28 15:50:23 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2021-07-28 15:05:14 +00:00
" CREATE TABLE { $db -> prefix } settings (
2022-07-12 19:44:50 +00:00
`param_key` VARCHAR ( 250 ) NOT NULL default '' ,
2021-07-28 15:05:14 +00:00
`param_value` TEXT ,
UNIQUE KEY `param_key` ( `param_key` )
2021-07-28 15:15:29 +00:00
) CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci " );
2021-07-29 13:52:44 +00:00
2022-02-06 20:37:02 +00:00
# create sessions table
2021-07-29 13:52:44 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2021-07-29 13:52:44 +00:00
" CREATE TABLE { $db -> prefix } sessions (
`id` VARCHAR ( 64 ) NOT NULL default '' ,
`data` TEXT ,
`last_access` INT UNSIGNED NOT NULL default 0 ,
`expires` INT UNSIGNED NOT NULL default 0 ,
UNIQUE KEY `id` ( `id` )
) CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci " );
2022-02-06 20:37:02 +00:00
}
2021-07-28 15:50:23 +00:00
2022-02-06 20:37:02 +00:00
else #sqlite
{
2022-02-07 21:04:55 +00:00
$db -> ex ( " ALTER TABLE { $db -> prefix } lists ADD extra TEXT " );
# settings
2022-02-06 20:37:02 +00:00
$db -> ex (
2021-07-28 15:05:14 +00:00
" CREATE TABLE { $db -> prefix } settings (
param_key VARCHAR ( 100 ) NOT NULL default '' ,
param_value TEXT
) " );
2022-02-06 20:37:02 +00:00
$db -> ex ( " CREATE UNIQUE INDEX settings_key ON { $db -> prefix } settings (param_key COLLATE NOCASE) " );
2021-07-29 13:52:44 +00:00
2022-02-06 20:37:02 +00:00
# sessions
2021-07-29 13:52:44 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex (
2021-07-29 13:52:44 +00:00
" CREATE TABLE { $db -> prefix } sessions (
2022-07-12 19:44:50 +00:00
id VARCHAR ( 250 ) NOT NULL default '' ,
2021-07-29 13:52:44 +00:00
data TEXT ,
last_access INTEGER UNSIGNED NOT NULL default 0 ,
expires INTEGER UNSIGNED NOT NULL default 0
) " );
2022-02-06 20:37:02 +00:00
$db -> ex ( " CREATE UNIQUE INDEX sessions_id ON { $db -> prefix } sessions (id COLLATE NOCASE) " );
}
2021-07-29 13:52:44 +00:00
2022-02-06 20:37:02 +00:00
$db -> ex ( " COMMIT " );
2021-07-28 15:05:14 +00:00
2022-02-06 20:37:02 +00:00
Config :: save ();
Config :: saveDbConfig ();
2021-07-28 15:05:14 +00:00
}
### end of 1.7 #####
2022-12-05 07:45:24 +00:00
### update v1.7 to v1.8 ##########
function update_17_18 ( Database_Abstract $db , $dbtype )
{
$db -> ex ( " BEGIN " );
if ( $dbtype == 'sqlite' )
{
2023-03-24 15:19:21 +00:00
// Use UTF8CI collate. Old sqlite does not support DROP COLUMN (before v3.35.0 2021-03-12)
2022-12-05 07:45:24 +00:00
$db -> ex ( " DROP INDEX todo_list_id " );
$db -> ex ( " DROP INDEX todo_uuid " );
$db -> ex ( " ALTER TABLE { $db -> prefix } todolist RENAME TO { $db -> prefix } todolist_old " );
$db -> ex (
" CREATE TABLE { $db -> prefix } todolist (
id INTEGER PRIMARY KEY ,
uuid CHAR ( 36 ) NOT NULL default '' ,
list_id INTEGER UNSIGNED NOT NULL default 0 ,
d_created INTEGER UNSIGNED NOT NULL default 0 ,
d_completed INTEGER UNSIGNED NOT NULL default 0 ,
d_edited INTEGER UNSIGNED NOT NULL default 0 ,
compl TINYINT UNSIGNED NOT NULL default 0 ,
title VARCHAR ( 250 ) NOT NULL default '' COLLATE UTF8CI ,
note TEXT COLLATE UTF8CI default NULL ,
prio TINYINT NOT NULL default 0 ,
ow INTEGER NOT NULL default 0 ,
duedate DATE default NULL ) "
);
2023-03-24 15:19:21 +00:00
$db -> ex ( " INSERT INTO { $db -> prefix } todolist SELECT id,uuid,list_id,d_created,d_completed,d_edited,compl,title,note,prio,ow,duedate FROM { $db -> prefix } todolist_old " );
2022-12-05 07:45:24 +00:00
$db -> ex ( " CREATE INDEX todo_list_id ON { $db -> prefix } todolist (list_id) " );
$db -> ex ( " CREATE UNIQUE INDEX todo_uuid ON { $db -> prefix } todolist (uuid) " );
$db -> ex ( " DROP TABLE { $db -> prefix } todolist_old " );
$db -> ex ( " DROP INDEX tags_name " );
$db -> ex ( " ALTER TABLE { $db -> prefix } tags RENAME TO { $db -> prefix } tags_old " );
$db -> ex (
" CREATE TABLE { $db -> prefix } tags (
id INTEGER PRIMARY KEY AUTOINCREMENT ,
2023-03-24 15:19:21 +00:00
name VARCHAR ( 250 ) NOT NULL DEFAULT '' COLLATE UTF8CI ) "
2022-12-05 07:45:24 +00:00
);
$db -> ex ( " INSERT INTO { $db -> prefix } tags SELECT * FROM { $db -> prefix } tags_old " );
$db -> ex ( " CREATE INDEX tags_name ON { $db -> prefix } tags (name) " );
$db -> ex ( " DROP TABLE { $db -> prefix } tags_old " );
}
2023-03-24 15:19:21 +00:00
else // mysql
{
$db -> ex ( " ALTER TABLE { $db -> prefix } todolist DROP COLUMN tags " );
$db -> ex ( " ALTER TABLE { $db -> prefix } todolist DROP COLUMN tags_ids " );
2023-08-08 18:34:53 +00:00
// if mysql db was created in v1.7.x then
// tags.name field has length of 50 instead of 250,
// settings.param_key field has length of 100 instead of 250
$db -> ex ( " ALTER TABLE { $db -> prefix } lists MODIFY `uuid` CHAR(36) CHARACTER SET latin1 NOT NULL default '' " );
$db -> ex ( " ALTER TABLE { $db -> prefix } lists MODIFY `name` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL default '' " );
$db -> ex ( " ALTER TABLE { $db -> prefix } lists MODIFY `extra` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci " );
$db -> ex ( " ALTER TABLE { $db -> prefix } todolist MODIFY `uuid` CHAR(36) CHARACTER SET latin1 NOT NULL default '' " );
$db -> ex ( " ALTER TABLE { $db -> prefix } todolist MODIFY `title` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL default '' " );
$db -> ex ( " ALTER TABLE { $db -> prefix } todolist MODIFY `note` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci " );
$db -> ex ( " ALTER TABLE { $db -> prefix } tags MODIFY `name` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL default '' " );
$db -> ex ( " ALTER TABLE { $db -> prefix } settings MODIFY `param_key` VARCHAR(250) CHARACTER SET latin1 NOT NULL default '' " );
$db -> ex ( " ALTER TABLE { $db -> prefix } settings MODIFY `param_value` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci " );
$db -> ex ( " ALTER TABLE { $db -> prefix } sessions MODIFY `id` VARCHAR(64) CHARACTER SET latin1 NOT NULL default '' " );
$db -> ex ( " ALTER TABLE { $db -> prefix } sessions MODIFY `data` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci " );
2023-03-24 15:19:21 +00:00
}
2022-12-05 07:45:24 +00:00
$db -> ex ( " COMMIT " );
if ( $dbtype == 'sqlite' ) {
$db -> ex ( " VACUUM " );
}
}
### end of 1.8 #####
