1: <?php
2: /*
3: You may not change or alter any portion of this comment or credits
4: of supporting developers from this source code or any supporting source code
5: which is considered copyrighted (c) material of the original comment or credit authors.
6:
7: This program is distributed in the hope that it will be useful,
8: but WITHOUT ANY WARRANTY; without even the implied warranty of
9: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
10: */
11:
12: namespace Xmf\Database;
13:
14: use Xmf\Yaml;
15:
16: /**
17: * Xmf\Database\TableLoad
18: *
19: * load a database table
20: *
21: * @category Xmf\Database\TableLoad
22: * @package Xmf
23: * @author Richard Griffith <richard@geekwright.com>
24: * @copyright 2013-2016 XOOPS Project (http://xoops.org)
25: * @license GNU GPL 2 or later (http://www.gnu.org/licenses/gpl-2.0.html)
26: * @link http://xoops.org
27: */
28: class TableLoad
29: {
30:
31: /**
32: * loadTableFromArray
33: *
34: * @param string $table name of table to load without prefix
35: * @param array $data array of rows to insert
36: * Each element of the outer array represents a single table row.
37: * Each row is an associative array in 'column' => 'value' format.
38: *
39: * @return int number of rows inserted
40: */
41: public static function loadTableFromArray($table, $data)
42: {
43: /** @var \XoopsDatabase */
44: $db = \XoopsDatabaseFactory::getDatabaseConnection();
45:
46: $prefixedTable = $db->prefix($table);
47: $count = 0;
48:
49: foreach ($data as $row) {
50: $insertInto = 'INSERT INTO ' . $prefixedTable . ' (';
51: $valueClause = ' VALUES (';
52: $first = true;
53: foreach ($row as $column => $value) {
54: if ($first) {
55: $first = false;
56: } else {
57: $insertInto .= ', ';
58: $valueClause .= ', ';
59: }
60:
61: $insertInto .= $column;
62: $valueClause .= $db->quote($value);
63: }
64:
65: $sql = $insertInto . ') ' . $valueClause . ')';
66:
67: $result = $db->queryF($sql);
68: if (false !== $result) {
69: ++$count;
70: }
71: }
72:
73: return $count;
74: }
75:
76: /**
77: * loadTableFromYamlFile
78: *
79: * @param string $table name of table to load without prefix
80: * @param string $yamlFile name of file containing data dump in YAML format
81: *
82: * @return int number of rows inserted
83: */
84: public static function loadTableFromYamlFile($table, $yamlFile)
85: {
86: $count = 0;
87:
88: $data = Yaml::loadWrapped($yamlFile); // work with phpmyadmin YAML dumps
89: if (false !== $data) {
90: $count = static::loadTableFromArray($table, $data);
91: }
92:
93: return $count;
94: }
95:
96: /**
97: * truncateTable - empty a database table
98: *
99: * @param string $table name of table to truncate
100: *
101: * @return int number of affected rows
102: */
103: public static function truncateTable($table)
104: {
105: /** @var \XoopsDatabase */
106: $db = \XoopsDatabaseFactory::getDatabaseConnection();
107:
108: $prefixedTable = $db->prefix($table);
109: $sql = 'TRUNCATE TABLE ' . $prefixedTable;
110: $result = $db->queryF($sql);
111: if (false !== $result) {
112: $result = $db->getAffectedRows();
113: }
114: return $result;
115: }
116:
117: /**
118: * countRows - get count of rows in a table
119: *
120: * @param string $table name of table to count
121: * @param \CriteriaElement $criteria optional criteria
122: *
123: * @return int number of rows
124: */
125: public static function countRows($table, $criteria = null)
126: {
127: /** @var \XoopsDatabase */
128: $db = \XoopsDatabaseFactory::getDatabaseConnection();
129:
130: $prefixedTable = $db->prefix($table);
131: $sql = 'SELECT COUNT(*) as count FROM ' . $prefixedTable . ' ';
132: if (isset($criteria) && is_subclass_of($criteria, '\CriteriaElement')) {
133: /* @var $criteria \CriteriaCompo */
134: $sql .= $criteria->renderWhere();
135: }
136: $result = $db->query($sql);
137: $row = $db->fetchArray($result);
138: $count = $row['count'];
139: $db->freeRecordSet($result);
140: return (int)$count;
141: }
142:
143: /**
144: * extractRows - get rows, all or a subset, from a table as an array
145: *
146: * @param string $table name of table to count
147: * @param \CriteriaElement $criteria optional criteria
148: * @param string[] $skipColumns do not include columns in this list
149: *
150: * @return array of table rows
151: */
152: public static function extractRows($table, $criteria = null, $skipColumns = array())
153: {
154: /** @var \XoopsDatabase */
155: $db = \XoopsDatabaseFactory::getDatabaseConnection();
156:
157: $prefixedTable = $db->prefix($table);
158: $sql = 'SELECT * FROM ' . $prefixedTable . ' ';
159: if (isset($criteria) && is_subclass_of($criteria, '\CriteriaElement')) {
160: /* @var $criteria \CriteriaCompo */
161: $sql .= $criteria->renderWhere();
162: }
163: $rows = array();
164: $result = $db->query($sql);
165: if ($result) {
166: while ($row = $db->fetchArray($result)) {
167: $rows[] = $row;
168: }
169: }
170:
171: $db->freeRecordSet($result);
172:
173: if (!empty($skipColumns)) {
174: foreach ($rows as $index => $row) {
175: foreach ($skipColumns as $column) {
176: unset($rows[$index][$column]);
177: }
178: }
179: }
180:
181: return $rows;
182: }
183:
184: /**
185: * Save table data to a YAML file
186: *
187: * @param string $table name of table to load without prefix
188: * @param string $yamlFile name of file containing data dump in YAML format
189: * @param \CriteriaElement $criteria optional criteria
190: * @param string[] $skipColumns do not include columns in this list
191: *
192: * @return bool true on success, false on error
193: */
194: public static function saveTableToYamlFile($table, $yamlFile, $criteria = null, $skipColumns = array())
195: {
196: $rows = static::extractRows($table, $criteria, $skipColumns);
197:
198: $count = Yaml::save($rows, $yamlFile);
199:
200: return (false !== $count);
201: }
202: }
203: