1: <?php
2: /*****************************************************************************************
3: * X2Engine Open Source Edition is a customer relationship management program developed by
4: * X2Engine, Inc. Copyright (C) 2011-2016 X2Engine Inc.
5: *
6: * This program is free software; you can redistribute it and/or modify it under
7: * the terms of the GNU Affero General Public License version 3 as published by the
8: * Free Software Foundation with the addition of the following permission added
9: * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
10: * IN WHICH THE COPYRIGHT IS OWNED BY X2ENGINE, X2ENGINE DISCLAIMS THE WARRANTY
11: * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
12: *
13: * This program is distributed in the hope that it will be useful, but WITHOUT
14: * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
15: * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
16: * details.
17: *
18: * You should have received a copy of the GNU Affero General Public License along with
19: * this program; if not, see http://www.gnu.org/licenses or write to the Free
20: * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
21: * 02110-1301 USA.
22: *
23: * You can contact X2Engine, Inc. P.O. Box 66752, Scotts Valley,
24: * California 95067, USA. or at email address contact@x2engine.com.
25: *
26: * The interactive user interfaces in modified source and object code versions
27: * of this program must display Appropriate Legal Notices, as required under
28: * Section 5 of the GNU Affero General Public License version 3.
29: *
30: * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
31: * these Appropriate Legal Notices must retain the display of the "Powered by
32: * X2Engine" logo. If the display of the logo is not reasonably feasible for
33: * technical reasons, the Appropriate Legal Notices must display the words
34: * "Powered by X2Engine".
35: *****************************************************************************************/
36:
37: Yii::import('application.components.util.*');
38:
39: /**
40: * Sample/dummy data exporter.
41: *
42: * A command for exporting non-application (human-entered) data into an SQL
43: * script for use as sample data. Requires the "mysqldump" utility to be
44: * installed on the system.
45: *
46: * The SQL generated by this script can be used as an alternate method for
47: * exporting data, reinstalling and importing data into the fresh installation.
48: * Note, however that it does not save custom modules or any of the tables
49: * listed in $tblsExclude for these reasons:
50: *
51: * - x2_auth tables: there is no easy, reliable way of distinguishing
52: * user-entered data in this table from default application data.
53: * - x2_sessions/x2_temp_files: This data is entirely ephemeral
54: * - x2_timezones/x2_timezone_points: This is static data inserted during
55: * installation and doesn't need to be exported.
56: *
57: * Note also that any files in the uploads folder will also need to be backed up,
58: * if the data is to be re-used elsewhere; references to files on the server
59: * will otherwise point to nonexistent files.
60: * @package application.commands
61: * @author Demitri Morgan <demitri@x2engine.com>
62: */
63: class SampleDataCommand extends CConsoleCommand {
64:
65: public $pdo;
66:
67: /**
68: * Format a string with a value such that it can be used in an SQL statement
69: *
70: * @param type $x
71: * @return str
72: */
73: public function sqlValue($val) {
74: if ($val === null) {
75: return "NULL";
76: } else if (is_int($val)) {
77: return "$val";
78: } else if (is_bool($val)) {
79: return (string) ((int) $val);
80: } else {// string
81: return $this->pdo->quote($val);
82: }
83: }
84:
85: /**
86: * Exports the database content into dummy data files
87: *
88: * @param array $args
89: * @param PDOException $e
90: * @return type
91: */
92: public function actionExport($args) {
93:
94: if (!copy("./data/install_timestamp", "./data/dummy_data_date")) {
95: die("Error: actionExport: failed to copy install_timestamp to dummy_data_date");
96: }
97:
98: // [edition] => [array of table names]
99: $tblEditions = require(realpath(Yii::app()->basePath . '/data/nonFreeTables.php'));
100: $allEditions = array_keys($tblEditions);
101: $nonFreeEditions = array_diff($allEditions, array('opensource'));
102: $specTemplate = array_fill_keys($allEditions, array());
103: $this->pdo = Yii::app()->db->pdoInstance;
104: $conf = realpath(Yii::app()->basePath . '/config/X2Config.php');
105: if ($conf) {
106: if ((include $conf) !== 1) {
107: die('Configuration import failed.');
108: }
109: } else {
110: die("Configuration file not found. This script must be run in protected/data.\n");
111: }
112: $getTbls = $this->pdo->prepare("SHOW TABLES IN `$dbname`");
113: $getTbls->execute();
114: try {
115: $allTbls = array_map(function($tr)use($dbname) {
116: return $tr["Tables_in_$dbname"];
117: }, $getTbls->fetchAll(PDO::FETCH_ASSOC));
118: } catch (PDOException $e) {
119: die("Database error: " . $e->getMessage() . "\n");
120: }
121:
122: /**
123: * The command for exporting data:
124: */
125: $command = "mysqldump -tc -u $user -p$pass $dbname ";
126:
127: // Ignore pattern for lines in output of mysqldump:
128: $lPat = '/^(\/\*|\-\-|\s*$';
129: // Export current app's data as "dummy" (usage example) data
130: $lPat.='|(?:UN)?LOCK TABLES)/';
131: $out = FileUtil::rpath(Yii::app()->basePath . '/data/dummy_data%s.sql');
132:
133: /**
134: * Update the list of tables for each edition with the default tables:
135: */
136: $nonFreeTbls = array_reduce($allEditions,
137: function($a, $e)use($tblEditions) {
138: return array_merge($tblEditions[$e], $a);
139: }, array());
140: $tblEditions['opensource'] = array_diff($allTbls, $nonFreeTbls);
141:
142: /**
143: * Declare the export specification arrays
144: *
145: * Here it's specified what data will be exported and how.
146: * Each of these arrays follows the basic pattern of $specTemplate:
147: * [edition] => [array of table names or ([table name] =>[spec])]
148: */
149: /**
150: * These will be excluded from data export altogether
151: */
152: $tblsExclude = $specTemplate;
153: // These will be excluded for open source and above:
154: $tblsExclude['opensource'] = array_merge(array(
155: 'x2_admin',
156: 'x2_auth_assignment',
157: 'x2_auth_item',
158: 'x2_auth_item_child',
159: 'x2_doc_folders',
160: 'x2_modules',
161: 'x2_sessions',
162: 'x2_temp_files',
163: 'x2_timezone_points',
164: 'x2_timezones',
165: 'x2_tips',
166: ), $tblEditions['pro'], $tblEditions['pla']);
167: // These for professional edition:
168: $tblsExclude['pro'] = array_merge(array(
169: 'x2_forwarded_email_patterns',
170: 'x2_charts',
171: 'x2_reports_2',
172: ), $tblEditions['pla']);
173: // These for platform/platinum edition:
174: $tblsExclude['pla'] = array(
175: 'x2_forwarded_email_patterns'
176: );
177:
178: /**
179: * These will be included, but with specific criteria
180: */
181: $tblsWhere = $specTemplate;
182: $tblsWhere['opensource'] = array(
183: 'x2_dropdowns' => 'id>=1000',
184: 'x2_fields' => 'custom=1',
185: 'x2_form_layouts' => 'id>=1000',
186: 'x2_media' => '(id>11 AND id<1000) OR (id>1006 AND id<2000) OR id>2002', // Quit messing with my head, guys! I mean it! -- keep the "id" field following a simple and consistent pattern in protected/modules/media/data/install.sql
187: 'x2_profile' => 'id>2',
188: 'x2_users' => 'id>2',
189: 'x2_social' => 'id>1',
190: 'x2_docs' => 'id>52 OR id<52' // exclude the sample quote template, which is default
191: );
192:
193: /**
194: * Update statements will be generated for these tables on which there's no way
195: * of inserting it at install time without running into duplicate primary key
196: * errors (because it's a record inserted by the installer itself). In each table:
197: * 'pk' => primary key (string for single-column or array for multi-column)
198: * 'fields' => array of fields to update or "*" to update all fields. Must include primary key.
199: * 'where' => records for which to generate update statements
200: */
201: $tblsChangeDefault = $specTemplate;
202: $tblsChangeDefault['opensource'] = array(
203: 'x2_profile' => array(
204: 'pk' => 'id',
205: 'fields' => '*',
206: 'where' => '`id`=1'
207: ),
208: 'x2_users' => array(
209: 'pk' => 'id',
210: 'fields' => array('id', 'firstName', 'lastName', 'officePhone',
211: 'cellPhone', 'showCalendars', 'calendarViewPermission',
212: 'calendarEditPermission', 'calendarFilter',
213: 'setCalendarPermissions', 'recentItems', 'topContacts'),
214: 'where' => '`id`=1'
215: )
216: );
217:
218: /**
219: * Switch the order of output generation so that foreign key constraints don't
220: * fail during insertion. List dependencies here.
221: */
222: $insertFirst = $specTemplate;
223: $insertFirst['opensource'] = array(
224: 'x2_action_meta_data' => array('x2_actions'),
225: 'x2_role_to_permission' => array('x2_roles'),
226: 'x2_role_to_user' => array('x2_roles'),
227: 'x2_list_criteria' => array('x2_lists'),
228: 'x2_list_items' => array('x2_lists'),
229: 'x2_role_to_workflow' => array('x2_workflow_stages', 'x2_roles', 'x2_workflows'),
230: 'x2_workflow_stages' => array('x2_workflows'),
231: 'x2_action_text' => array('x2_actions'),
232: 'x2_actions' => array('x2_workflows', 'x2_workflow_stages'),
233: );
234: /**
235: * This array stores tables to be executed "next"
236: */
237: $insertNext = $specTemplate;
238:
239: /**
240: * The resulting SQL to be written to files
241: */
242: $allSql = $specTemplate;
243:
244: /**
245: * Assemble the array of combined export specs.
246: *
247: * Note that since the "where" conditions are put in the array last, they'll
248: * take precedence (so if it's listed in both $tblsExclude and $tblsWhere,
249: * only $tblsWhere will apply).
250: */
251: $allTbls = array();
252: foreach ($allEditions as $edition) {
253: $allTbls[$edition] = array_fill_keys($tblEditions[$edition], true);
254: foreach ($tblsExclude[$edition] as $tbl)
255: $allTbls[$edition][$tbl] = false;
256: foreach ($tblsWhere[$edition] as $tbl => $where)
257: $allTbls[$edition][$tbl] = $where;
258: }
259:
260: // The update statement that will be used for updating records post-insertion:
261: $updateStatement = "UPDATE `%s` SET %s WHERE %s;";
262:
263: foreach ($nonFreeEditions as $edition)
264: $allSql[$edition][] = "/* @edition:$edition */";
265:
266: /**
267: * Generate SQL for the data:
268: */
269: foreach ($allTbls as $edition => $tbls) {
270:
271: /**
272: * Generate insertion statements
273: */
274: $eTbls = $tbls;
275: while (count($eTbls) > 0) {
276: $tblsTmp = $eTbls;
277: foreach ($tblsTmp as $tbl => $where) {
278: if ($where != false) {
279: // This table is to be included in the data export
280: if (array_key_exists($tbl, $insertFirst[$edition])) {
281: // This table depends on other tables being ready with data
282: $skip = False;
283: foreach ($insertFirst[$edition][$tbl] as $tblFirst)
284: // Check to see if the table has been accounted for already
285: if (array_key_exists($tblFirst, $eTbls)) {
286: $skip = True;
287: break;
288: }
289: if ($skip)
290: // Not all dependencies of this table have been resolved yet.
291: continue;
292: }
293: $output = array();
294: $tblCommand = "$command $tbl" . ($where !== true ? " --where='" . $where . "' "
295: : ' ');
296: exec($tblCommand, $output);
297: foreach ($output as $line) {
298: if (!preg_match($lPat, $line)) {
299: $allSql[$edition][] = $line;
300: }
301: }
302: }
303: unset($eTbls[$tbl]);
304: }
305: }
306:
307: /**
308: * Generate update statements
309: */
310: foreach ($tblsChangeDefault[$edition] as $tbl => $how) {
311: $colSel = $how['fields'];
312: if (is_array($how['fields']))
313: $colSel = '`' . implode('`,`', $how['fields']) . '`';
314: $query = $this->pdo->prepare("SELECT $colSel FROM `$tbl` WHERE {$how['where']}");
315: $query->execute();
316: $recs = $query->fetchAll(PDO::FETCH_ASSOC);
317: $pk = $how['pk'];
318: if (!is_array($pk)) $pk = array($pk);
319: foreach ($recs as $rec) {
320: // Generate a "where" clause criterion to refer to this record by its primary key
321: $whereSelector = array();
322: foreach ($pk as $c) {
323: $whereSelector[] = "`$c`=" . $this->sqlValue($rec[$c]);
324: }
325: // Exclude the primary key from the columns to be updated:
326: foreach ($pk as $col) unset($rec[$col]);
327: $fieldsSet = array();
328:
329: foreach ($rec as $col => $val)
330: $fieldsSet[] = "`$col`=" . $this->sqlValue($val);
331:
332: $allSql[$edition][] = sprintf($updateStatement, $tbl,
333: implode(',', $fieldsSet),
334: implode(' AND ', $whereSelector));
335: }
336: }
337: }
338:
339: // Create dummy data files
340: foreach ($allSql as $edition => $sqls)
341: file_put_contents(sprintf($out,
342: $edition == 'opensource' ? '' : "-$edition"),
343: implode("\n/*&*/\n", $sqls));
344: }
345:
346: /**
347: * Hunts through the database for in-the-future timestamps and reports them
348: *
349: * @param type $args
350: */
351: public function actionFutureTimes($args) {
352: $dateFields = require(realpath(Yii::app()->basePath . '/data/dateFields.php'));
353: $maxFuture = array(
354: 'table' => null,
355: 'column' => null,
356: 'key' => null,
357: 'date' => 0
358: );
359: $useFile = array_pop($args);
360: if ($useFile)
361: $time = (int) file_get_contents(realpath(Yii::app()->basePath . '/data/dummy_data_date'));
362: else $time = time();
363: $minFuture = array_merge(array(), $maxFuture);
364: $minFuture['date'] = PHP_INT_MAX;
365: $time = time();
366: $futureFields = require(realpath(Yii::app()->basePath . '/data/futureFields.php'));
367: $futureTables = array_keys($futureFields);
368:
369: foreach ($dateFields as $table => $cols) {
370: $pk = Yii::app()->db->schema->getTable($table)->primaryKey;
371:
372: $pastCols = $cols;
373: // Exclude fields that are permitted to be in the future:
374: if (in_array($table, $futureTables))
375: $pastCols = array_diff($pastCols, $futureFields[$table]);
376:
377: $select = array_merge(is_array($pk) ? $pk : array($pk), $pastCols);
378: $where = '`' . implode("`>$time OR `", $pastCols) . "`>$time";
379: $dates = Yii::app()->db->createCommand()
380: ->select($select)
381: ->from($table)
382: ->where($where)
383: ->queryAll();
384: if (!empty($dates)) echo implode("\t", $pastCols) . "\t($table)\n";
385: foreach ($dates as $record) {
386: $line = '';
387: foreach ($select as $col) {
388: $line .= ($record[$col] == null ? "NULL" : $record[$col]) . "\t";
389: }
390: foreach ($pastCols as $dateField) {
391: $date = $record[$dateField];
392: if ($date > $maxFuture['date']) {
393: $maxFuture['table'] = $table;
394: $maxFuture['column'] = $dateField;
395: $maxFuture['key'] = var_export($pk, true);
396: $maxFuture['date'] = $date;
397: }
398: if ($date > $time && $date < $minFuture['date']) {
399: $minFuture['table'] = $table;
400: $minFuture['column'] = $dateField;
401: $minFuture['key'] = var_export($pk, true);
402: $minFuture['date'] = $date;
403: }
404: }
405: echo "$line\n";
406: }
407: }
408: echo "\nRecord furthest in the future:\n";
409: print_r($maxFuture);
410: echo strftime('%c', $maxFuture['date']);
411: echo "\nRecord least far in the future:\n";
412: print_r($minFuture);
413: echo strftime('%c', $minFuture['date']);
414: }
415:
416: /**
417: * "Compress" all sample data timestamps
418: *
419: * Brings all timestamps closer to "now" using a logarithmic scale. This is
420: * to bring really far-apart events closer together while avoiding too much
421: * "clumping" of events around the installation timestamp.
422: *
423: * @param array $newDisp The new furthest time into the past that any event
424: * is allowed to go.
425: */
426: public function actionSquashtime($dtnew) {
427: $newDisp = (int) trim($dtnew);
428: echo "Finding the oldest event in the sample data...\n";
429: $dateFields = require(realpath(Yii::app()->basePath . '/data/dateFields.php'));
430: $installTimestamp = (integer) file_get_contents(implode(DIRECTORY_SEPARATOR,
431: array(
432: Yii::app()->basePath, 'data', 'dummy_data_date'
433: )));
434: $now = $installTimestamp;
435: $min = $now;
436: foreach ($dateFields as $table => $columns) {
437: $newMin = Yii::app()->db->createCommand()
438: ->select(count($columns) > 1 ? 'LEAST(MIN(`' . implode('`),MIN(`',
439: $columns) . '`))' : 'MIN(`' . reset($columns) . '`)')
440: ->from($table)
441: ->queryScalar();
442: if (!empty($newMin) && $newMin < $min) {
443: $min = $newMin;
444: echo "Older timestamp $newMin found in table $table\n";
445: }
446: }
447: echo "min: $min\nnow: $now\n";
448: $oldDisp = $installTimestamp - $min;
449:
450: $yn = $this->prompt("The oldest record is $oldDisp seconds in the "
451: . "past. Are you sure you want to proceed with adjusting all "
452: . "timestamps logarithmically such that the old maximum time "
453: . "displacement into the past $oldDisp becomes the new, $newDisp?");
454: if (!preg_match('/^y(es)?$/i', trim($yn))) Yii::app()->end();
455:
456: foreach ($dateFields as $table => $columns) {
457: foreach ($columns as $column) {
458: list($setClause, $params) = $this->timeCompressSql($column,
459: $installTimestamp, $oldDisp, $newDisp);
460: $sqlRun = "UPDATE `$table` " . $setClause;
461: Yii::app()->db->createCommand($sqlRun)
462: ->execute($params);
463: echo 'Ran "' . strtr($sqlRun, $params) . "\"\n";
464: }
465: }
466: }
467:
468: /**
469: * Generates update SQL for a timestamp column to "compress" times
470: *
471: * @param string $column Attribute/column name to be changed
472: * @param type $ti Timestamp of installation ("now")
473: * @param type $dtMax Furthest time into the past that events go
474: * @param type $dtMaxNew New furthest time into the past that events can go
475: * @return type
476: */
477: public function timeCompressSql($column, $ti, $dtMax, $dtMaxNew) {
478: $sql = "SET `$column`=(:ti1-:dtMaxNew*LOG2(1+(:ti2-`$column`)/:dtMax)) "
479: . "WHERE `$column` < :ti3";
480: $params = array(
481: ':ti1' => $ti,
482: ':ti2' => $ti,
483: ':ti3' => $ti,
484: ':dtMaxNew' => $dtMaxNew,
485: ':dtMax' => $dtMax
486: );
487: return array($sql, $params);
488: }
489:
490: }
491:
492: ?>
493: