function _local_bigtree_update_200()
{
global $cms, $admin;
// Drop unused comments column
sqlquery("ALTER TABLE bigtree_pending_changes DROP COLUMN `comments`");
// Add extension columns
sqlquery("ALTER TABLE bigtree_callouts ADD COLUMN `extension` VARCHAR(255)");
sqlquery("ALTER TABLE bigtree_callouts ADD FOREIGN KEY (extension) REFERENCES `bigtree_extensions` (id) ON DELETE CASCADE");
sqlquery("ALTER TABLE bigtree_feeds ADD COLUMN `extension` VARCHAR(255)");
sqlquery("ALTER TABLE bigtree_feeds ADD FOREIGN KEY (extension) REFERENCES `bigtree_extensions` (id) ON DELETE CASCADE");
sqlquery("ALTER TABLE bigtree_field_types ADD COLUMN `extension` VARCHAR(255)");
sqlquery("ALTER TABLE bigtree_field_types ADD FOREIGN KEY (extension) REFERENCES `bigtree_extensions` (id) ON DELETE CASCADE");
sqlquery("ALTER TABLE bigtree_modules ADD COLUMN `extension` VARCHAR(255)");
sqlquery("ALTER TABLE bigtree_modules ADD FOREIGN KEY (extension) REFERENCES `bigtree_extensions` (id) ON DELETE CASCADE");
sqlquery("ALTER TABLE bigtree_module_groups ADD COLUMN `extension` VARCHAR(255)");
sqlquery("ALTER TABLE bigtree_module_groups ADD FOREIGN KEY (extension) REFERENCES `bigtree_extensions` (id) ON DELETE CASCADE");
sqlquery("ALTER TABLE bigtree_settings ADD COLUMN `extension` VARCHAR(255)");
sqlquery("ALTER TABLE bigtree_settings ADD FOREIGN KEY (extension) REFERENCES `bigtree_extensions` (id) ON DELETE CASCADE");
sqlquery("ALTER TABLE bigtree_templates ADD COLUMN `extension` VARCHAR(255)");
sqlquery("ALTER TABLE bigtree_templates ADD FOREIGN KEY (extension) REFERENCES `bigtree_extensions` (id) ON DELETE CASCADE");
// New publish_hook column, consolidate other hooks into one column
sqlquery("ALTER TABLE bigtree_pending_changes ADD COLUMN `publish_hook` VARCHAR(255)");
sqlquery("ALTER TABLE bigtree_module_forms ADD COLUMN `hooks` TEXT");
sqlquery("ALTER TABLE bigtree_module_embeds ADD COLUMN `hooks` TEXT");
$q = sqlquery("SELECT * FROM bigtree_module_forms");
while ($f = sqlfetch($q)) {
$hooks = array();
$hooks["pre"] = $f["preprocess"];
$hooks["post"] = $f["callback"];
$hooks["publish"] = "";
sqlquery("UPDATE bigtree_module_forms SET hooks = '" . BigTree::json($hooks, true) . "' WHERE id = '" . $f["id"] . "'");
}
$q = sqlquery("SELECT * FROM bigtree_module_embeds");
while ($f = sqlfetch($q)) {
$hooks = array();
$hooks["pre"] = $f["preprocess"];
$hooks["post"] = $f["callback"];
$hooks["publish"] = "";
sqlquery("UPDATE bigtree_module_embeds SET hooks = '" . BigTree::json($hooks, true) . "' WHERE id = '" . $f["id"] . "'");
}
sqlquery("ALTER TABLE bigtree_module_forms DROP COLUMN `preprocess`");
sqlquery("ALTER TABLE bigtree_module_forms DROP COLUMN `callback`");
sqlquery("ALTER TABLE bigtree_module_embeds DROP COLUMN `preprocess`");
sqlquery("ALTER TABLE bigtree_module_embeds DROP COLUMN `callback`");
// Adjust groups/callouts for multi-support -- first we drop the foreign key
$table_desc = BigTree::describeTable("bigtree_callouts");
foreach ($table_desc["foreign_keys"] as $name => $definition) {
if ($definition["local_columns"][0] === "group") {
sqlquery("ALTER TABLE bigtree_callouts DROP FOREIGN KEY `{$name}`");
}
}
// Add the field to the groups
sqlquery("ALTER TABLE bigtree_callout_groups ADD COLUMN `callouts` TEXT AFTER `name`");
// Find all the callouts in each group
$q = sqlquery("SELECT * FROM bigtree_callout_groups");
while ($f = sqlfetch($q)) {
$callouts = array();
$qq = sqlquery("SELECT * FROM bigtree_callouts WHERE `group` = '" . $f["id"] . "' ORDER BY position DESC, id ASC");
while ($ff = sqlfetch($qq)) {
$callouts[] = $ff["id"];
}
sqlquery("UPDATE bigtree_callout_groups SET `callouts` = '" . BigTree::json($callouts, true) . "' WHERE id = '" . $f["id"] . "'");
}
// Drop the group column
sqlquery("ALTER TABLE bigtree_callouts DROP COLUMN `group`");
// Security policy setting
sqlquery("INSERT INTO `bigtree_settings` (`id`,`value`,`system`) VALUES ('bigtree-internal-security-policy','{}','on')");
sqlquery("CREATE TABLE `bigtree_login_attempts` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT, `ip` int(11) DEFAULT NULL, `user` int(11) DEFAULT NULL, `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
sqlquery("CREATE TABLE `bigtree_login_bans` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT, `ip` int(11) DEFAULT NULL, `user` int(11) DEFAULT NULL, `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `expires` datetime DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
// Media settings
sqlquery("INSERT INTO `bigtree_settings` (`id`,`value`,`system`) VALUES ('bigtree-internal-media-settings','{}','on')");
// New field types
@unlink(SERVER_ROOT . "cache/bigtree-form-field-types.json");
// Setup an anonymous function for converting a resource set
$resource_converter = function ($resources) {
$new_resources = array();
foreach ($resources as $item) {
// Array of Items no longer exists, switching to Matrix
if ($item["type"] == "array") {
$item["type"] = "matrix";
$item["columns"] = array();
$x = 0;
foreach ($item["fields"] as $field) {
$x++;
$item["columns"][] = array("id" => $field["key"], "type" => $field["type"], "title" => $field["title"], "display_title" => $x == 1 ? "on" : "");
}
unset($item["fields"]);
}
$r = array("id" => $item["id"], "type" => $item["type"], "title" => $item["title"], "subtitle" => $item["subtitle"], "options" => array());
foreach ($item as $key => $val) {
if ($key != "id" && $key != "title" && $key != "subtitle" && $key != "type") {
$r["options"][$key] = $val;
}
}
$new_resources[] = $r;
}
return BigTree::json($new_resources, true);
};
$field_converter = function ($fields) {
$new_fields = array();
foreach ($fields as $id => $field) {
// Array of Items no longer exists, switching to Matrix
if ($field["type"] == "array") {
$field["type"] = "matrix";
$field["columns"] = array();
$x = 0;
foreach ($field["fields"] as $subfield) {
$x++;
$field["columns"][] = array("id" => $subfield["key"], "type" => $subfield["type"], "title" => $subfield["title"], "display_title" => $x == 1 ? "on" : "");
}
unset($field["fields"]);
}
$r = array("column" => $id, "type" => $field["type"], "title" => $field["title"], "subtitle" => $field["subtitle"], "options" => array());
foreach ($field as $key => $val) {
if ($key != "id" && $key != "title" && $key != "subtitle" && $key != "type") {
$r["options"][$key] = $val;
}
}
$new_fields[] = $r;
}
return $new_fields;
};
// New resource format to be less restrictive on option names
$q = sqlquery("SELECT * FROM bigtree_callouts");
while ($f = sqlfetch($q)) {
$resources = $resource_converter(json_decode($f["resources"], true));
sqlquery("UPDATE bigtree_callouts SET resources = '{$resources}' WHERE id = '" . $f["id"] . "'");
}
$q = sqlquery("SELECT * FROM bigtree_templates");
while ($f = sqlfetch($q)) {
$resources = $resource_converter(json_decode($f["resources"], true));
sqlquery("UPDATE bigtree_templates SET resources = '{$resources}' WHERE id = '" . $f["id"] . "'");
}
// Forms and Embedded Forms
$q = sqlquery("SELECT * FROM bigtree_module_forms");
while ($f = sqlfetch($q)) {
$fields = $field_converter(json_decode($f["fields"], true));
sqlquery("UPDATE bigtree_module_forms SET fields = '" . BigTree::json($fields, true) . "' WHERE id = '" . $f["id"] . "'");
}
$q = sqlquery("SELECT * FROM bigtree_module_embeds");
while ($f = sqlfetch($q)) {
$fields = $field_converter(json_decode($f["fields"], true));
sqlquery("UPDATE bigtree_module_embeds SET fields = '" . BigTree::json($fields, true) . "' WHERE id = '" . $f["id"] . "'");
}
// Settings
$q = sqlquery("SELECT * FROM bigtree_settings WHERE type = 'array'");
while ($f = sqlfetch($q)) {
// Update settings options to turn array into matrix
$options = json_decode($f["options"], true);
$options["columns"] = array();
$x = 0;
foreach ($options["fields"] as $field) {
$x++;
$options["columns"][] = array("id" => $field["key"], "type" => $field["type"], "title" => $field["title"], "display_title" => $x == 1 ? "on" : "");
if ($x == 1) {
$display_key = $field["key"];
}
}
unset($options["fields"]);
// Update the value to set an internal title key
$value = BigTreeCMS::getSetting($f["id"]);
foreach ($value as &$entry) {
$entry["__internal-title"] = $entry[$display_key];
}
unset($entry);
// Update type/options
sqlquery("UPDATE bigtree_settings SET type = 'matrix', options = '" . BigTree::json($options, true) . "' WHERE id = '" . $f["id"] . "'");
// Update value separately
BigTreeAdmin::updateSettingValue($f["id"], $value);
}
}
function search($query, $order = false, $limit = false, $split_search = false, $case_sensitive = false, $columns = false)
{
$table_description = BigTree::describeTable($this->Table);
$where = array();
if ($split_search) {
$pieces = explode(" ", $query);
foreach ($pieces as $piece) {
if ($piece) {
$where_piece = array();
foreach ($table_description["columns"] as $field => $parameters) {
if ($case_sensitive) {
$where_piece[] = "`{$field}` LIKE '%" . sqlescape($piece) . "%'";
} else {
$where_piece[] = "LOWER(`{$field}`) LIKE '%" . sqlescape(strtolower($piece)) . "%'";
}
}
$where[] = "(" . implode(" OR ", $where_piece) . ")";
}
}
return $this->fetch($order, $limit, implode(" AND ", $where), $columns);
} else {
foreach ($table_description["columns"] as $field => $parameters) {
if ($case_sensitive) {
$where[] = "`{$field}` LIKE '%" . sqlescape($query) . "%'";
} else {
$where[] = "LOWER(`{$field}`) LIKE '%" . sqlescape(strtolower($query)) . "%'";
}
}
return $this->fetch($order, $limit, implode(" OR ", $where), $columns);
}
}
static function updateItem($table, $id, $data, $many_to_many = array(), $tags = array())
{
$table_description = BigTree::describeTable($table);
$query = "UPDATE `{$table}` SET ";
foreach ($data as $key => $val) {
if (array_key_exists($key, $table_description["columns"])) {
if ($val === "NULL" || $val == "NOW()") {
$query .= "`{$key}` = {$val},";
} else {
if (is_array($val)) {
$val = json_encode(BigTree::translateArray($val));
}
$query .= "`{$key}` = '" . sqlescape($val) . "',";
}
}
}
$query = rtrim($query, ",") . " WHERE id = '{$id}'";
sqlquery($query);
// Handle many to many
if (!empty($many_to_many)) {
foreach ($many_to_many as $mtm) {
sqlquery("DELETE FROM `" . $mtm["table"] . "` WHERE `" . $mtm["my-id"] . "` = '{$id}'");
$table_description = BigTree::describeTable($mtm["table"]);
if (is_array($mtm["data"])) {
$x = count($mtm["data"]);
foreach ($mtm["data"] as $item) {
if (isset($table_description["columns"]["position"])) {
sqlquery("INSERT INTO `" . $mtm["table"] . "` (`" . $mtm["my-id"] . "`,`" . $mtm["other-id"] . "`,`position`) VALUES ('{$id}','{$item}','{$x}')");
} else {
sqlquery("INSERT INTO `" . $mtm["table"] . "` (`" . $mtm["my-id"] . "`,`" . $mtm["other-id"] . "`) VALUES ('{$id}','{$item}')");
}
$x--;
}
}
}
}
// Handle the tags
sqlquery("DELETE FROM bigtree_tags_rel WHERE `table` = '" . sqlescape($table) . "' AND entry = '{$id}'");
if (!empty($tags)) {
foreach ($tags as $tag) {
sqlquery("DELETE FROM bigtree_tags_rel WHERE `table` = '" . sqlescape($table) . "' AND entry = {$id} AND tag = {$tag}");
sqlquery("INSERT INTO bigtree_tags_rel (`table`,`entry`,`tag`) VALUES ('" . sqlescape($table) . "',{$id},{$tag})");
}
}
// Clear out any pending changes.
sqlquery("DELETE FROM bigtree_pending_changes WHERE item_id = '{$id}' AND `table` = '{$table}'");
if ($table != "bigtree_pages") {
self::recacheItem($id, $table);
}
self::track($table, $id, "updated");
}
$list = array();
foreach ($column["options"] as $option) {
$list[] = array("value" => $option, "description" => $option);
}
$options = array("list_type" => "static", "list" => $list);
if ($column["allow_null"]) {
$options["allow-empty"] = "Yes";
} else {
$options["allow-empty"] = "No";
}
}
// Database populated list for foreign keys.
if (substr($column["type"], -3, 3) == "int" && isset($foreign_keys[$column["name"]]) && implode("", $foreign_keys[$column["name"]]["other_columns"]) == "id") {
$type = "list";
// Describe this other table
$other_table = BigTree::describeTable($foreign_keys[$column["name"]]["other_table"]);
$ot_columns = $other_table["columns"];
$desc_column = "";
// Find the first short title-esque column and use it as the populated list descriptor
while (!$desc_column && next($ot_columns)) {
$col = current($ot_columns);
if (($col["type"] == "varchar" || $col["type"] == "char") && $col["size"] > 2) {
$desc_column = $col;
}
}
$options = array("list_type" => "db", "pop-table" => $foreign_keys[$column["name"]]["other_table"]);
if ($desc_column) {
$options["pop-description"] = $desc_column["name"];
$options["pop-sort"] = $desc_column["name"] . " ASC";
}
if ($column["allow_null"]) {
<?php
if ($_GET["table"]) {
$table = $_GET["table"];
}
$used = array();
$unused = array();
$tblfields = array();
// To tolerate someone selecting the blank spot again when creating a feed.
if ($table) {
$table_description = BigTree::describeTable($table);
} else {
$table_description = array("columns" => array());
}
foreach ($table_description["columns"] as $column => $details) {
$tblfields[] = $column;
}
if (isset($fields)) {
foreach ($fields as $key => $field) {
$used[] = $key;
}
// Figure out the fields we're not using so we can offer them back.
foreach ($tblfields as $field) {
if (!in_array($field, $used)) {
$unused[] = array("title" => ucwords(str_replace("_", " ", $field)), "field" => $field);
}
}
} else {
$fields = array();
foreach ($tblfields as $f) {
$title = ucwords(str_replace(array("-", "_"), " ", $f));
static function updateModuleViewColumnNumericStatus($view)
{
if (is_array($view["fields"])) {
$form = BigTreeAutoModule::getRelatedFormForView($view);
$table = BigTree::describeTable($view["table"]);
foreach ($view["fields"] as $key => $field) {
$numeric = false;
$t = $table["columns"][$key]["type"];
if ($t == "int" || $t == "float" || $t == "double" || $t == "double precision" || $t == "tinyint" || $t == "smallint" || $t == "mediumint" || $t == "bigint" || $t == "real" || $t == "decimal" || $t == "dec" || $t == "fixed" || $t == "numeric") {
$numeric = true;
}
if ($field["parser"] || $form["fields"][$key]["type"] == "list" && $form["fields"][$key]["list_type"] == "db") {
$numeric = false;
}
$view["fields"][$key]["numeric"] = $numeric;
}
$fields = BigTree::json($view["fields"], true);
sqlquery("UPDATE bigtree_module_views SET fields = '{$fields}' WHERE id = '" . $view["id"] . "'");
}
}
}
if (count($children)) {
if ($expanded) {
echo "<ul>";
} else {
echo '<ul style="display: none;">';
}
_localDrawLevel($children, $depth + 1);
echo "</ul>";
}
?>
</li>
<?php
}
}
$table_description = BigTree::describeTable($bigtree["view"]["table"]);
if ($table_description["columns"][$bigtree["view"]["options"]["nesting_column"]]["allow_null"]) {
_localDrawLevel(BigTreeAutoModule::getViewDataForGroup($bigtree["view"], "", "position DESC, id ASC", "both"), 1);
} else {
_localDrawLevel(BigTreeAutoModule::getViewDataForGroup($bigtree["view"], "0", "position DESC, id ASC", "both"), 1);
}
?>
<script>
$("#nested_container").addClass("nested_table");
<?php
if ($permission == "p") {
?>
BigTree.localCreateSortable("#table_data");
<?php
}
?>
$table_description = BigTree::describeTable($table);
foreach ($table_description["columns"] as $column => $details) {
if (!in_array($column, $used_fields)) {
$unused_fields[] = array("field" => $column, "title" => str_replace(array("Url", "Pdf", "Sql"), array("URL", "PDF", "SQL"), ucwords(str_replace(array("-", "_"), " ", $details["name"]))));
}
if (!in_array($column, $used_filters)) {
$unused_filters[] = array("field" => $column, "title" => str_replace(array("Url", "Pdf", "Sql"), array("URL", "PDF", "SQL"), ucwords(str_replace(array("-", "_"), " ", $details["name"]))));
}
$table_columns[] = $column;
}
} else {
$fields = array();
$filters = array();
// To tolerate someone selecting the blank spot in the table dropdown again when creating a form.
if ($table) {
$table_info = BigTree::describeTable($table);
} else {
$table_info = array("foreign_keys" => array(), "columns" => array());
}
foreach ($table_info["columns"] as $column) {
$table_columns[] = $column["name"];
$title = str_replace(array("Url", "Pdf", "Sql"), array("URL", "PDF", "SQL"), ucwords(str_replace(array("-", "_"), " ", $column["name"])));
$fields[$column["name"]] = $title;
$type = "search";
if ($column["type"] == "date" || $column["type"] == "datetime" || $column["type"] == "timestamp") {
$type = "date-range";
}
if ($column["name"] == "approved" || $column["name"] == "archived" || $column["name"] == "featured") {
$type = "boolean";
}
$filters[$column["name"]] = array("title" => $title, "type" => $type);
static function tableContents($table)
{
$inserts = array();
// Figure out which columns are binary and need to be pulled as hex
$description = BigTree::describeTable($table);
$column_query = array();
$binary_columns = array();
foreach ($description["columns"] as $key => $column) {
if ($column["type"] == "tinyblob" || $column["type"] == "blob" || $column["type"] == "mediumblob" || $column["type"] == "longblob" || $column["type"] == "binary" || $column["type"] == "varbinary") {
$column_query[] = "HEX(`{$key}`) AS `{$key}`";
$binary_columns[] = $key;
} else {
$column_query[] = "`{$key}`";
}
}
// Get the rows out of the table
$qq = sqlquery("SELECT " . implode(", ", $column_query) . " FROM `{$table}`");
while ($ff = sqlfetch($qq)) {
$keys = array();
$vals = array();
foreach ($ff as $key => $val) {
$keys[] = "`{$key}`";
if ($val === null) {
$vals[] = "NULL";
} else {
if (in_array($key, $binary_columns)) {
$vals[] = "X'" . str_replace("\n", "\\n", sqlescape($val)) . "'";
} else {
$vals[] = "'" . str_replace("\n", "\\n", sqlescape($val)) . "'";
}
}
}
$inserts[] = "INSERT INTO `{$table}` (" . implode(",", $keys) . ") VALUES (" . implode(",", $vals) . ")";
}
return $inserts;
}
$total_results++;
}
}
if (count($pages)) {
$results["Pages"] = $pages;
}
// Get every module's results based on auto module views.
$modules = $admin->getModules("name ASC");
foreach ($modules as $m) {
// Get all auto module view actions for this module.
$actions = $admin->getModuleActions($m);
foreach ($actions as $action) {
if ($action["view"]) {
$view = BigTreeAutoModule::getView($action["view"]);
$m_results = array();
$table_description = BigTree::describeTable($view["table"]);
$qparts = array();
foreach ($table_description["columns"] as $column => $data) {
$qparts[] = "`{$column}` LIKE {$w}";
}
// Get matching results
$qs = sqlquery("SELECT * FROM `" . $view["table"] . "` WHERE " . implode(" OR ", $qparts));
// Ignore SQL failures because we might have bad collation.
while ($r = sqlfetch($qs, true)) {
foreach ($r as &$piece) {
$piece = $cms->replaceInternalPageLinks($piece);
}
unset($piece);
$m_results[] = $r;
$total_results++;
}
<?php
// Find out whether this is a draggable Many to Many.
$table_description = BigTree::describeTable($field["options"]["mtm-connecting-table"]);
$cols = $table_description["columns"];
$sortable = false;
if (isset($cols["position"])) {
$sortable = true;
}
$entries = array();
// If we have existing data then this item is either pending or has pending changes so we use that data.
if (is_array($field["value"])) {
foreach ($field["value"] as $oid) {
$g = sqlfetch(sqlquery("SELECT * FROM `" . $field["options"]["mtm-other-table"] . "` WHERE id = '{$oid}'"));
if ($g) {
$entries[$g["id"]] = $g[$field["options"]["mtm-other-descriptor"]];
}
}
// No pending data, let's query the connecting table directly for the entries, but only if this isn't a new entry
} elseif ($bigtree["edit_id"]) {
if ($sortable) {
$q = sqlquery("SELECT * FROM `" . $field["options"]["mtm-connecting-table"] . "` WHERE `" . $field["options"]["mtm-my-id"] . "` = '" . $bigtree["edit_id"] . "' ORDER BY `position` DESC");
} else {
$q = sqlquery("SELECT * FROM `" . $field["options"]["mtm-connecting-table"] . "` WHERE `" . $field["options"]["mtm-my-id"] . "` = '" . $bigtree["edit_id"] . "'");
}
while ($f = sqlfetch($q)) {
// Get the title from the other table.
$g = sqlfetch(sqlquery("SELECT * FROM `" . $field["options"]["mtm-other-table"] . "` WHERE id = '" . $f[$field["options"]["mtm-other-id"]] . "'"));
if ($g) {
$entries[$g["id"]] = $g[$field["options"]["mtm-other-descriptor"]];
}