class.tx_nG6_upgrade.php 14.2 KB
Newer Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
/***************************************************************
*  Copyright notice
*
*  (c) 2009 PF bioinformatique de Toulouse <>
*  All rights reserved
*
*  This script is part of the TYPO3 project. The TYPO3 project is
*  free software; you can redistribute it and/or modify
*  it under the terms of the GNU General Public License as published by
*  the Free Software Foundation; either version 2 of the License, or
*  (at your option) any later version.
*
*  The GNU General Public License can be found at
*  http://www.gnu.org/copyleft/gpl.html.
*
*  This script is distributed in the hope that it will be useful,
*  but WITHOUT ANY WARRANTY; without even the implied warranty of
*  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
*  GNU General Public License for more details.
*
*  This copyright notice MUST APPEAR in all copies of the script!
***************************************************************/

25
use nG6\Controller\tx_nG6_db;
26
27
28
29
30
31
32
33
34
35
36
37
38
/**
 * Class_nG6_db' for the 'nG6' extension.
 * Allows to migrate usergroups system to (users) rights system.
 *
 * @author	PF bioinformatique de Toulouse <>
 */
class tx_nG6_upgrade {
	
	/**
	 * Main function for upgrade.
	 * 
	 * @param string $version the version 
	 */
39
	function upgrade($version, $data_storage){
40
41
42
43
		// version 1.2 : users rights' system 
		if($version == '1.2'){
			tx_nG6_upgrade::upgrade_1_2();
			return "Upgrade successfuly ended to version 1.2!";
Jerome Mariette's avatar
Jerome Mariette committed
44
		} else if($version == '2.0'){
45
			tx_nG6_upgrade::upgrade_2_0($data_storage);
Jerome Mariette's avatar
Jerome Mariette committed
46
			return "Upgrade successfuly ended to version 2.0!";
47
48
49
50
51
52
		} else {
			return "Upgrading to version " . $version . " not suported!";
		}
	}
	
	
53
54
	/*
	 * version 2.0
Jerome Mariette's avatar
Jerome Mariette committed
55
56
	 *------------------------------------------------------------*/
	
57
58
59
60
	/**
	 * Main function to upgrade to version 2.0.
	 */
	function upgrade_2_0($data_storage){
Celine Noirot's avatar
Celine Noirot committed
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
		// Update cruser_id of tx_nG6_project
		$queryUpdateCruseridProject = "
				UPDATE tx_nG6_project p 
				SET p.cruser_id = (
				                SELECT u.uid FROM fe_users u , fe_rights r 
				                WHERE r.fe_user_id = u.uid 
				                    AND r.right_id = 2 
				                    AND r.project_id = p.uid 
				                HAVING MIN(u.tstamp)
				                ) 
				WHERE p.cruser_id = 0" ;
		
		// Update cruser_id for tx_nG6_run table
		$queryUpdateCruseridRun = "
				UPDATE tx_nG6_run ru
				SET ru.cruser_id = (
						SELECT p.cruser_id FROM tx_nG6_project p , tx_nG6_project_run pr
						WHERE p.uid = pr.project_id
						AND pr.run_id=ru.uid
					)
				WHERE ru.cruser_id = 0	";
		
		// Update cruser_id for tx_nG6_analysis linked to a run
		$queryUpdateCruseridAnalysisRun = "
				UPDATE tx_nG6_analyze a
				SET a.cruser_id = (
						SELECT r.cruser_id FROM tx_nG6_run r , tx_nG6_run_analyze ra
						WHERE a.uid = ra.analyze_id
						AND ra.run_id = r.uid
					)
				WHERE a.cruser_id = 0";
		
		// Update cruser_id for tx_nG6_analysis linked to a project
		$queryUpdateCruseridAnalysisProject = "
				UPDATE tx_nG6_analyze a
				SET a.cruser_id = (
					SELECT p.cruser_id FROM tx_nG6_project p , tx_nG6_project_analyze pa
					WHERE a.uid = pa.analyze_id
					AND pa.project_id = p.uid
					)
				WHERE a.cruser_id = 0";
		
		// Alter table fe_groups and add two columns tx_nG6_location and tx_nG6_organism
		$queryAlterFeGroups = "
				ALTER TABLE fe_groups ADD tx_nG6_organism VARCHAR( 50 ) NOT NULL AFTER title ,
				ADD tx_nG6_location VARCHAR( 50 ) NOT NULL AFTER tx_nG6_organism
			" ;
		
		// Alter tx_nG6_analyze and add is_editable
		$queryAlterTxNG6Analayze = "
				ALTER TABLE tx_nG6_analyze 
				ADD is_editable boolean NOT NULL DEFAULT FALSE AFTER params 
			";		
		
115
116
117
118
		// Alter tx_nG6_analyze and add storage_size
		$queryAlterTxNG6AnalayzeSsize = "
		ALTER TABLE tx_nG6_analyze
		ADD storage_size bigint(20) NOT NULL DEFAULT '0' AFTER is_editable
119
120
		";
		
121
122
123
124
		// Alter tx_nG6_run and add storage_size
		$queryAlterTxNG6Run = "
		ALTER TABLE tx_nG6_run
		ADD storage_size bigint(20) NOT NULL DEFAULT '0' AFTER sequencer
125
126
		";
		
Celine Noirot's avatar
Celine Noirot committed
127
128
129
130
131
132
133
134
135
		/*
		 * Execute all queries
		 */
		$GLOBALS['TYPO3_DB']->sql_query($queryUpdateCruseridProject);
		$GLOBALS['TYPO3_DB']->sql_query($queryUpdateCruseridRun);
		$GLOBALS['TYPO3_DB']->sql_query($queryUpdateCruseridAnalysisRun);
		$GLOBALS['TYPO3_DB']->sql_query($queryUpdateCruseridAnalysisProject);
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterFeGroups);
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6Analayze);
136
137
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6AnalayzeSsize);
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6Run);
Jerome Mariette's avatar
Jerome Mariette committed
138
139
140
141
		
		// finaly update storage fields
		tx_nG6_upgrade::set_storage_size($data_storage);
		
142
143
		// add comment support
		tx_nG6_upgrade::add_comment_support();
Penom Nom's avatar
Penom Nom committed
144
145
146
		
		// add sample support
		tx_nG6_upgrade::upgrade_samples();
Jerome Mariette's avatar
Jerome Mariette committed
147
148
	}
	
149
150
	function set_storage_size($data_storage) {
		// update run storage size
151
152
153
154
155
156
157
		$queryParts = array(
				'SELECT' 	=> 'tx_nG6_run.uid as uid, tx_nG6_run.directory as directory',
				'FROM' 		=> 'tx_nG6_run ',
				'WHERE' 	=> "",
				'GROUPBY' 	=> '',
				'ORDERBY' 	=> '',
				'LIMIT' 	=> ''
158
159
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
160
		$current_size = 0;
161
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
162
			$current_size = tx_nG6_upgrade::get_directory_size($data_storage.$res_row['directory']);
163
164
165
166
			$GLOBALS['TYPO3_DB']-> exec_UPDATEquery('tx_nG6_run', 'uid='.$res_row['uid'], array('storage_size' => strval($current_size)));
		}
		
		// update analysis storage size
167
168
169
170
171
172
173
174
175
176
177
178
179
		$queryParts = array(
				'SELECT' 	=> 'tx_nG6_analyze.uid as uid, tx_nG6_analyze.directory as directory',
				'FROM' 		=> 'tx_nG6_analyze ',
				'WHERE' 	=> "",
				'GROUPBY' 	=> '',
				'ORDERBY' 	=> '',
				'LIMIT' 	=> ''
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		$current_size = 0;
		while($res_row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			$current_size = tx_nG6_upgrade::get_directory_size($data_storage.$res_row['directory']);
			$GLOBALS['TYPO3_DB']-> exec_UPDATEquery('tx_nG6_analyze', 'uid='.$res_row['uid'], array('storage_size' => strval($current_size)));
180
181
182
183
		}
		
	}
	
184
185
186
187
188
189
190
191
	function get_directory_size($data_folder) {
		$full_size = 0;
		foreach(scandir($data_folder) as $file) {
			if (is_file($data_folder."/".$file)) {
				$full_size += filesize($data_folder."/".$file);
			}
		}
		return $full_size;
192
193
	}
	
194
195
196
197
198
199
200
201
202
203
204
205
	function add_comment_support(){
	
		// Create comment table
		$queryCreatecomment = "
			CREATE TABLE IF NOT EXISTS `tx_nG6_comment` (
			`uid` int(11) NOT NULL AUTO_INCREMENT,
			`pid` int(11) NOT NULL DEFAULT '0',
			`tstamp` int(11) NOT NULL DEFAULT '0',
			`crdate` int(11) NOT NULL DEFAULT '0',
			`cruser_id` int(11) NOT NULL DEFAULT '0',
			`deleted` tinyint(4) NOT NULL DEFAULT '0',
			`hidden` tinyint(4) NOT NULL DEFAULT '0',
206
			`comment` text NOT NULL,
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
			PRIMARY KEY (`uid`),
			KEY `parent` (`pid`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
		";
	
		// Create project_comment table
		$queryCreateProjectcomment = "
				CREATE TABLE IF NOT EXISTS `tx_nG6_project_comment` (
				`uid` int(11) NOT NULL AUTO_INCREMENT,
				`pid` int(11) NOT NULL DEFAULT '0',
				`tstamp` int(11) NOT NULL DEFAULT '0',
				`crdate` int(11) NOT NULL DEFAULT '0',
				`cruser_id` int(11) NOT NULL DEFAULT '0',
				`deleted` tinyint(4) NOT NULL DEFAULT '0',
				`hidden` tinyint(4) NOT NULL DEFAULT '0',
				`project_id` int(11) NOT NULL DEFAULT '0',
				`comment_id` int(11) NOT NULL DEFAULT '0',
				PRIMARY KEY (`uid`),
				KEY `parent` (`pid`)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
		";
	
		// Create run_comment table
		$queryCreateRuncomment = "
				CREATE TABLE IF NOT EXISTS `tx_nG6_run_comment` (
				`uid` int(11) NOT NULL AUTO_INCREMENT,
				`pid` int(11) NOT NULL DEFAULT '0',
				`tstamp` int(11) NOT NULL DEFAULT '0',
				`crdate` int(11) NOT NULL DEFAULT '0',
				`cruser_id` int(11) NOT NULL DEFAULT '0',
				`deleted` tinyint(4) NOT NULL DEFAULT '0',
				`hidden` tinyint(4) NOT NULL DEFAULT '0',
				`run_id` int(11) NOT NULL DEFAULT '0',
				`comment_id` int(11) NOT NULL DEFAULT '0',
				PRIMARY KEY (`uid`),
				KEY `parent` (`pid`)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
		";
	
		// Create analyze_comment table
		$queryCreateAnalyzecomment = "
				CREATE TABLE IF NOT EXISTS `tx_nG6_analyze_comment` (
				`uid` int(11) NOT NULL AUTO_INCREMENT,
				`pid` int(11) NOT NULL DEFAULT '0',
				`tstamp` int(11) NOT NULL DEFAULT '0',
				`crdate` int(11) NOT NULL DEFAULT '0',
				`cruser_id` int(11) NOT NULL DEFAULT '0',
				`deleted` tinyint(4) NOT NULL DEFAULT '0',
				`hidden` tinyint(4) NOT NULL DEFAULT '0',
				`analyze_id` int(11) NOT NULL DEFAULT '0',
				`comment_id` int(11) NOT NULL DEFAULT '0',
				PRIMARY KEY (`uid`),
				KEY `parent` (`pid`)
				) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
		";
	
		/*
		 * Execute all queries
		*/
		$GLOBALS['TYPO3_DB']->sql_query($queryCreatecomment);
		$GLOBALS['TYPO3_DB']->sql_query($queryCreateProjectcomment);
		$GLOBALS['TYPO3_DB']->sql_query($queryCreateRuncomment);
		$GLOBALS['TYPO3_DB']->sql_query($queryCreateAnalyzecomment);
	}
	
Penom Nom's avatar
Penom Nom committed
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
	
	
	function upgrade_samples(){
		
		// Alter tx_nG6_sample change mid to sample_id
		$queryAlterTxNG6SampleMID = "
				ALTER TABLE tx_nG6_sample 
				CHANGE mid sample_id VARCHAR(255) NOT NULL DEFAULT ''
		";
		
		// Alter tx_nG6_sample description to name
		$queryAlterTxNG6SampleDescription = "
				ALTER TABLE tx_nG6_sample
				CHANGE description  name VARCHAR(255) NOT NULL DEFAULT ''
		";
		
Penom Nom's avatar
Penom Nom committed
288
		// Add column description, reads1, reads2, type, insert_size, species, nb_sequences
Penom Nom's avatar
Penom Nom committed
289
290
291
		$queryAlterTxNG6SampleAddColumns = "
				ALTER TABLE tx_nG6_sample
				ADD `description` varchar(255) NOT NULL DEFAULT '' AFTER name ,
292
293
				ADD `reads1` text AFTER description,
				ADD `reads2` text AFTER reads1,
Penom Nom's avatar
Penom Nom committed
294
295
296
				ADD `type` varchar(255) NOT NULL DEFAULT '' AFTER reads2,
				ADD `insert_size` int(11) NOT NULL DEFAULT '0' AFTER type,
				ADD `species` varchar(255) NOT NULL DEFAULT '' AFTER insert_size,
297
				ADD `nb_sequences` int(11) NOT NULL DEFAULT '0' AFTER species,
Penom Nom's avatar
Penom Nom committed
298
				ADD `full_seq_size` int(11) NOT NULL DEFAULT '0' AFTER nb_sequences
Penom Nom's avatar
Penom Nom committed
299
300
301
302
303
304
305
306
307
308
		";

		/*
		 * Execute all queries
		*/
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6SampleMID);
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6SampleDescription);
		$GLOBALS['TYPO3_DB']->sql_query($queryAlterTxNG6SampleAddColumns);
	}
	
309
310
	/*
	 * version 1.2
Jerome Mariette's avatar
Jerome Mariette committed
311
	 *------------------------------------------------------------*/
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
	
	/**
	 * Main function to upgrade to version 1.2.
	 */
	function upgrade_1_2(){

		$queryParts = array(
			'SELECT' => 'uid, fe_group',
			'FROM' => 'tx_nG6_project',
			'WHERE' => '',
			'GROUPBY' => '',
			'ORDERBY' => '',
			'LIMIT' => ''
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
			$project_id = $row['uid'];
			$current_group = $row['fe_group'];
			// users in the current group
			$group_users = tx_nG6_upgrade::get_all_users_in_group($current_group);

333
			// set current group users' right to "member"
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
			foreach($group_users as $id => $user_id){
				tx_nG6_upgrade::assign_right($user_id, $project_id, 0);
			}
			//assign rights to the super groups users for the specified project id
			tx_nG6_upgrade::assign_super_groups_rights($current_group, $project_id);
		}
		
	}
	
	
	
	/**
	 * Assign the 'administrator' right to all users found in the supergroups of 'basegroup'.
	 * 
	 * @param int $base_group	the base group id
	 * @param int $project_id	the project id
	 */
	function assign_super_groups_rights($base_group, $project_id){		
		$super_groups_ids = tx_nG6_upgrade::get_all_super_groups_of($base_group);
		foreach($super_groups_ids as $id => $group_id){
			$group_users = tx_nG6_upgrade::get_all_users_in_group($group_id);
			// super users = admin
			foreach($group_users as $id => $user_id){
				tx_nG6_upgrade::assign_right($user_id, $project_id, 2);
			}
		}
	}
	
	
	/**
	 * Assign a user right to a specific project.
	 * 
	 * @param int $user_id		the user id
	 * @param int $project_id	the project id
368
	 * @param int $right		the right id (0=member, 1=manager, 2=admininistrator)
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
	 */
	function assign_right($user_id, $project_id, $right){
		$assign_right = Array(
    		'fe_user_id' 	=> $user_id,
    		'project_id' 	=> $project_id,
    		'right_id'		=> $right
		);
		$GLOBALS['TYPO3_DB']->exec_INSERTquery('fe_rights', $assign_right);
	}	
	
	
	/**
	 * Return all users id from a usergroup. 
	 * 
	 * @param int $group_id the usergroup
	 */
	function get_all_users_in_group($group_id){
		$users = array();
		$queryParts = array(
			'SELECT' => 'uid',
			'FROM' => 'fe_users',
			'WHERE' => 'usergroup='.$group_id,
			'GROUPBY' => '',
			'ORDERBY' => '',
			'LIMIT' => ''
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {			
			$users[] = $row['uid'];
		}
		return $users;
	}
	
	
	
    /**
    * Return the supergroups list of the specified group.
    * 
    * @param string	$group	the group
    * @return array
    */ 
	function get_super_groups_of ($group) {
		// First get the super group of the given group
		$queryParts = array(
			'SELECT' => 'uid, subgroup',
			'FROM' => 'fe_groups',
			'WHERE' => '',
			'GROUPBY' => '',
			'ORDERBY' => '',
			'LIMIT' => ''
		);
		$res = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);
		$reslist = array();
		$i = 0;
		
		while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {			
425
			if (in_array($group, \TYPO3\CMS\Core\Utility\GeneralUtility::intExplode(',',$row['subgroup']))) {
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
				$reslist[$i] = $row['uid'];
				$i++;
			}
		}
 		return $reslist;
	}
    
	/**
     * Return the recursive supergroups list of the specified group
     *
     * @param string $group the group id
     * @return array
     */
    function get_all_super_groups_of ($group) {
    	// return the recursive list of all $group super groups
    	$first_list = tx_nG6_upgrade::get_super_groups_of($group);
    	$super_list = $first_list;    	
    	foreach ($first_list as $first_list_group_id) {
    		$next_list = tx_nG6_upgrade::get_all_super_groups_of($first_list_group_id, $level);
    		$super_list = array_merge($super_list,$next_list);
    	}
    	return $super_list;
    }
	
}

if (defined('TYPO3_MODE') && $TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/nG6/class.tx_nG6_upgrade.php']) {
    include_once($TYPO3_CONF_VARS[TYPO3_MODE]['XCLASS']['ext/nG6/class.tx_nG6_upgrade.php']);
}

456
?>