Revize 1174
Přidáno uživatelem Ondřej Fibich před více než 13 roky(ů)
| freenetis/branches/testing/application/vendors/unit_tester/unit_testing_config.xml | ||
|---|---|---|
|
<input>
|
||
|
<param value="21"/>
|
||
|
</input>
|
||
|
<input>
|
||
|
<param value="ef'esf"/>
|
||
|
</input>
|
||
|
</values>
|
||
|
</method>
|
||
|
<method name="edit_transfer" autogenerate="off">
|
||
| ... | ... | |
|
</input>
|
||
|
</values>
|
||
|
</method>
|
||
|
<method name="get_all_to_device_admin" autogenerate="on">
|
||
|
<attributes></attributes>
|
||
|
<values>
|
||
|
<input></input>
|
||
|
</values>
|
||
|
</method>
|
||
|
<method name="select_list_grouped" autogenerate="on">
|
||
|
<attributes></attributes>
|
||
|
<values>
|
||
| freenetis/branches/testing/application/models/iface.php | ||
|---|---|---|
|
SELECT i.id, CONCAT(d.name, ' - ', i.name) AS name
|
||
|
FROM ifaces i
|
||
|
LEFT JOIN devices d ON d.id = i.device_id
|
||
|
");
|
||
|
")->result()->result_array();
|
||
|
|
||
|
$result = array();
|
||
|
|
||
| freenetis/branches/testing/application/models/user.php | ||
|---|---|---|
|
{
|
||
|
$order_by = self::$arr_sql[$order_by];
|
||
|
}
|
||
|
|
||
|
// optimalization
|
||
|
if (empty($where))
|
||
|
{
|
||
|
return $this->db->query("
|
||
|
SELECT u.*, m.name AS member_name
|
||
|
FROM users u
|
||
|
LEFT JOIN members m ON m.id = u.member_id
|
||
|
ORDER BY $order_by $order_by_direction
|
||
|
LIMIT " . intval($limit_from) . ", " . intval($limit_results) . "
|
||
|
", $member_id);
|
||
|
}
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT * FROM
|
||
| ... | ... | |
|
if ($member_id)
|
||
|
$where .= ($where != '') ? ' AND member_id = '.intval($member_id) :
|
||
|
'WHERE member_id = '.intval($member_id);
|
||
|
|
||
|
// optimalization
|
||
|
if (empty($where))
|
||
|
return $this->count_all();
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT COUNT(*) AS total FROM
|
||
| ... | ... | |
|
Contact_Model::TYPE_ICQ
|
||
|
))->current()->total;
|
||
|
}
|
||
|
|
||
|
/**
|
||
|
* Gets all users of member
|
||
|
*
|
||
|
* @param integer $member_id
|
||
|
* @param integer $limit_from
|
||
|
* @param integer $limit_results
|
||
|
* @param string $order_by
|
||
|
* @param string $order_by_direction
|
||
|
* @return unknown_type
|
||
|
*/
|
||
|
public function get_all_users_of_member(
|
||
|
$member_id = NULL, $limit_from = 0, $limit_results = 50,
|
||
|
$order_by = 'id', $order_by_direction = 'ASC')
|
||
|
{
|
||
|
// order by direction check
|
||
|
if (strtolower($order_by_direction) != 'desc')
|
||
|
{
|
||
|
$order_by_direction = 'asc';
|
||
|
}
|
||
|
|
||
|
if (!$this->has_column($order_by))
|
||
|
{
|
||
|
$order_by = 'id';
|
||
|
}
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT u.*
|
||
|
FROM users u
|
||
|
WHERE member_id = ?
|
||
|
ORDER BY $order_by $order_by_direction
|
||
|
LIMIT " . intval($limit_from) . "," . intval($limit_results) . "
|
||
|
", $member_id);
|
||
|
}
|
||
|
|
||
|
/**
|
||
|
* Login test function
|
||
|
*
|
||
| ... | ... | |
|
}
|
||
|
|
||
|
/**
|
||
|
* Gets list of devices
|
||
|
*
|
||
|
* @return array
|
||
|
*/
|
||
|
public function get_all_to_device_admin()
|
||
|
{
|
||
|
$users = $this->orderby('member_id')->find_all();
|
||
|
|
||
|
$arr_users = array();
|
||
|
foreach ($users as $user)
|
||
|
$arr_users[$user->id] = $user->member_id.' - '.$user->name.' '.$user->surname;
|
||
|
|
||
|
return $arr_users;
|
||
|
}
|
||
|
|
||
|
/**
|
||
|
* Gets array of users for selectbox
|
||
|
*
|
||
|
* @return array[string]
|
||
|
*/
|
||
|
public function select_list_grouped()
|
||
|
{
|
||
|
$users = $this->db->query("
|
||
|
SELECT id, CONCAT(surname, ' ', name, ' - ', login) AS value
|
||
|
FROM users
|
||
|
ORDER BY name, surname, login
|
||
|
");
|
||
|
|
||
|
$arr_users = array();
|
||
|
|
||
|
foreach ($users as $user)
|
||
|
{
|
||
|
$arr_users[$user->id] = $user->value;
|
||
|
}
|
||
|
|
||
|
return $arr_users;
|
||
|
return $this->select_list('id', "CONCAT(surname, ' ', name, ' - ', login)");
|
||
|
}
|
||
|
|
||
|
|
||
| freenetis/branches/testing/application/models/message.php | ||
|---|---|---|
|
* Counts all activated redirections from junction table messages_ip_addresses.
|
||
|
*
|
||
|
*
|
||
|
* @author Jiri Svitak
|
||
|
* @author Jiri Svitak, Ondrej Fibich
|
||
|
* @return unknown_type
|
||
|
*/
|
||
|
public function count_all_redirections($filter_sql = '')
|
||
|
{
|
||
|
$where = "";
|
||
|
if ($filter_sql)
|
||
|
if (!empty($filter_sql))
|
||
|
{
|
||
|
$where = "WHERE $filter_sql";
|
||
|
}
|
||
|
else
|
||
|
{
|
||
|
// Optimalization:
|
||
|
// don't want search throught staff down here because of filter
|
||
|
// which will be still unused
|
||
|
return $this->db->count_records('messages_ip_addresses');
|
||
|
}
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT COUNT(*) AS total FROM
|
||
| ... | ... | |
|
/**
|
||
|
* Counts all messages.
|
||
|
*
|
||
|
* @return unknown_type
|
||
|
* @return integer
|
||
|
*/
|
||
|
public function count_all_messages()
|
||
|
{
|
||
| freenetis/branches/testing/application/models/address_point.php | ||
|---|---|---|
|
/**
|
||
|
* Returns all address points
|
||
|
*
|
||
|
* !!!!!! SECURITY WARNING !!!!!!
|
||
|
* Be careful when you using this method, param $filter_sql is unprotected
|
||
|
* for SQL injections, security should be made at controller site using
|
||
|
* Filter_form class.
|
||
|
* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
|
||
|
*
|
||
|
* @author Michal Kliment
|
||
|
* @param $limit_from
|
||
|
* @param $limit_results
|
||
|
* @param $order_by
|
||
|
* @param $order_by_direction
|
||
|
* @param $filter_values
|
||
|
* @param integer $limit_from
|
||
|
* @param integer $limit_results
|
||
|
* @param string $order_by
|
||
|
* @param string $order_by_direction
|
||
|
* @param integer $member_id
|
||
|
* @param string $filter_sql
|
||
|
* @return ORM iterator
|
||
|
*/
|
||
|
public function get_all_address_points($limit_from = 0, $limit_results = 50,
|
||
| ... | ... | |
|
/**
|
||
|
* Counts all address points
|
||
|
*
|
||
|
* @author Michal Kliment
|
||
|
* @return integer Count of all address points
|
||
|
* !!!!!! SECURITY WARNING !!!!!!
|
||
|
* Be careful when you using this method, param $filter_sql is unprotected
|
||
|
* for SQL injections, security should be made at controller site using
|
||
|
* Filter_form class.
|
||
|
* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
|
||
|
*
|
||
|
* @author Michal Kliment, Ondřej Fibich
|
||
|
* @param integer $member_id Member who use this address point
|
||
|
* @param string $filter_sql Search filter
|
||
|
* @return integer Count of all address points
|
||
|
*/
|
||
|
public function count_all_address_points($member_id = NULL, $filter_sql = '')
|
||
|
{
|
||
| ... | ... | |
|
if ($member_id && is_numeric($member_id))
|
||
|
$member_where = "WHERE member_id = ".intval($member_id);
|
||
|
|
||
|
// optimalization
|
||
|
if (empty($where) && empty($member_where))
|
||
|
{
|
||
|
return $this->count_all();
|
||
|
}
|
||
|
|
||
|
// query
|
||
|
return $this->db->query("
|
||
|
SELECT COUNT(*) AS total FROM
|
||
| freenetis/branches/testing/application/models/member.php | ||
|---|---|---|
|
* Filter_form class.
|
||
|
* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
|
||
|
*
|
||
|
* @param $filter_values
|
||
|
* @param string $filter_values
|
||
|
* @return unknown_type
|
||
|
*/
|
||
|
public function count_all_members($filter_sql = "")
|
||
|
{
|
||
|
$where = '';
|
||
|
if ($filter_sql != '')
|
||
|
// optimalization
|
||
|
if (!empty($filter_sql))
|
||
|
{
|
||
|
$where = "WHERE $filter_sql";
|
||
|
}
|
||
|
else
|
||
|
{
|
||
|
return $this->count_all();
|
||
|
}
|
||
|
|
||
|
return $this->db->query("
|
||
|
SELECT COUNT(id) AS total
|
||
| freenetis/branches/testing/application/controllers/users.php | ||
|---|---|---|
|
if (($sql_offset = ($page - 1) * $limit_results) > $total_users)
|
||
|
$sql_offset = 0;
|
||
|
|
||
|
$query = $model_users->get_all_users($sql_offset, (int) $limit_results, $order_by, $order_by_direction, "", $member_id);
|
||
|
$query = $model_users->get_all_users_of_member(
|
||
|
$member_id, $sql_offset, (int) $limit_results, $order_by,
|
||
|
$order_by_direction
|
||
|
);
|
||
|
|
||
|
$grid = new Grid(
|
||
|
url_lang::base() . 'users',
|
||
| freenetis/branches/testing/application/controllers/device_admins.php | ||
|---|---|---|
|
$arr_users = arr::merge(array
|
||
|
(
|
||
|
NULL => '----- ' . __('select user') . ' -----'
|
||
|
), ORM::factory('user')->get_all_to_device_admin());
|
||
|
), ORM::factory('user')->select_list_grouped());
|
||
|
|
||
|
$devices1 = ORM::factory('device_admin')->get_all_devices_not_in_user_device_admins(
|
||
|
$user->id, $search_system_input, $left_user
|
||
| freenetis/branches/testing/application/controllers/ip_addresses.php | ||
|---|---|---|
|
|
||
|
if (!$device->id)
|
||
|
Controller::error(RECORD);
|
||
|
|
||
|
$arr_ifaces = array();
|
||
|
$arr_vlan_ifaces = array();
|
||
|
|
||
|
$arr_ifaces[0] = '----- '.__('select interface').' -----';
|
||
|
$arr_vlan_ifaces[0] = '----- '.__('select vlan interface').' -----';
|
||
|
|
||
|
$ifaces = $device->ifaces;
|
||
|
|
||
|
foreach ($ifaces as $iface) {
|
||
|
$arr_ifaces[$iface->id] = $iface->name;
|
||
|
}
|
||
|
|
||
|
$vlan_ifaces = $vlan_iface_model->get_all_vlan_ifaces_by_device_id($device->id);
|
||
|
$vlan_ifaces = $vlan_iface_model->get_all_vlan_ifaces_by_device_id(
|
||
|
$device->id
|
||
|
);
|
||
|
|
||
|
foreach ($vlan_ifaces as $vlan_iface)
|
||
|
{
|
||
|
$arr_vlan_ifaces[$vlan_iface->id] = $vlan_iface->name;
|
||
|
}
|
||
|
|
||
|
$title = __('Add new IP address to device').' '.$device->name;
|
||
|
$link_back_url = url_lang::base().'devices/show/'.$device->id;
|
||
| ... | ... | |
|
}
|
||
|
else
|
||
|
{
|
||
|
$iface_model = new Iface_Model();
|
||
|
$ifaces = $iface_model->select('id','name')
|
||
|
->orderby('name')
|
||
|
->find_all();
|
||
|
$arr_ifaces = array
|
||
|
(
|
||
|
NULL => '----- '.__('select interface').' -----'
|
||
|
) + ORM::factory('iface')->select_list('id', 'name');
|
||
|
|
||
|
$vlan_ifaces = $vlan_iface_model->select('id','name')
|
||
|
->orderby('name')
|
||
|
->find_all();
|
||
|
$arr_vlan_ifaces = array
|
||
|
(
|
||
|
NULL => '----- '.__('select vlan interface').' -----'
|
||
|
) + ORM::factory('vlan_iface')->select_list('id', 'name');
|
||
|
|
||
|
$title = __('Add new IP address');
|
||
|
$link_back_url = url_lang::base().'ip_addresses/show_all';
|
||
| ... | ... | |
|
|
||
|
$this->form->set_attr('class', 'form_class')
|
||
|
->set_attr('method', 'post');
|
||
|
|
||
|
$arr_ifaces = array();
|
||
|
$arr_vlan_ifaces = array();
|
||
|
|
||
|
$arr_ifaces[0] = '----- '.__('select interface').' -----';
|
||
|
$arr_vlan_ifaces[0] = '----- '.__('select vlan interface').' -----';
|
||
|
|
||
|
foreach ($ifaces as $iface) {
|
||
|
$arr_ifaces[$iface->id] = $iface->name;
|
||
|
}
|
||
|
|
||
|
foreach ($vlan_ifaces as $vlan_iface) {
|
||
|
$arr_vlan_ifaces[$vlan_iface->id] = $vlan_iface->name;
|
||
|
}
|
||
|
|
||
|
$subnet_model = new Subnet_Model();
|
||
|
|
||
|
$arr_subnets = array(
|
||
|
$arr_subnets = array
|
||
|
(
|
||
|
NULL => '----- '.__('select subnet').' -----'
|
||
|
) + $subnet_model->select_list_by_net();
|
||
|
) + ORM::factory('subnet')->select_list_by_net();
|
||
|
|
||
|
$arr_bool = array
|
||
|
(
|
||
| freenetis/branches/testing/application/controllers/members.php | ||
|---|---|---|
|
$view->content->member_links = implode(' | ',$member_links);
|
||
|
$view->content->user_links = implode(' | ',$user_links);
|
||
|
$view->render(TRUE);
|
||
|
|
||
|
} // end of show function
|
||
|
|
||
|
|
||
| ... | ... | |
|
$arr_towns = $town_model->select_list_with_quater();
|
||
|
|
||
|
// engineers
|
||
|
$user_model = new User_Model();
|
||
|
$member = new Member_Model($member_id);
|
||
|
|
||
|
$engineers = $user_model->select('id','name','surname','login')
|
||
|
->orderby('id')
|
||
|
->find_all();
|
||
|
|
||
|
$arr_engineers[0] = '----- '.__('select user').' -----';
|
||
|
|
||
|
foreach ($engineers as $engineer)
|
||
|
{
|
||
|
$arr_engineers[$engineer->id] = $engineer->surname.' '
|
||
|
. $engineer->name.' - '.$engineer->login;
|
||
|
}
|
||
|
asort($arr_engineers, SORT_LOCALE_STRING);
|
||
|
$arr_engineers = array
|
||
|
(
|
||
|
'----- '.__('select user').' -----'
|
||
|
) + ORM::factory('user')->select_list(
|
||
|
'id', "CONCAT(surname, ' ', name, ' - ', login)", FALSE
|
||
|
);
|
||
|
|
||
|
$allowed_subnets_count = ($member->allowed_subnets_count) ?
|
||
|
$member->allowed_subnets_count->count : 0;
|
||
| ... | ... | |
|
|
||
|
if ($member->save())
|
||
|
{
|
||
|
$this->session->set_flash('message', __('Membership of the member has been ended.'
|
||
|
$this->session->set_flash('message', __(
|
||
|
'Membership of the member has been ended.'
|
||
|
));
|
||
|
}
|
||
|
else
|
||
|
{
|
||
|
$this->session->set_flash('message', __('Error - cant end membership.'
|
||
|
$this->session->set_flash('message', __(
|
||
|
'Error - cant end membership.'
|
||
|
));
|
||
|
}
|
||
|
|
||
| freenetis/branches/testing/application/controllers/accounts.php | ||
|---|---|---|
|
}
|
||
|
|
||
|
// members list
|
||
|
$members = ORM::factory('member')->find_all();
|
||
|
foreach ($members as $member)
|
||
|
{
|
||
|
$arr_members[$member->id] = $member->id . ' - ' . $member->name;
|
||
|
}
|
||
|
$arr_members = ORM::factory('member')
|
||
|
->select_list('id', 'CONCAT(id, \' - \', name)', FALSE);
|
||
|
|
||
|
// form
|
||
|
$form = new Forge(
|
||
| freenetis/branches/testing/application/upgrade_sql/upgrade_sql.php | ||
|---|---|---|
|
|
||
|
/**
|
||
|
* Array of sql queries that upgrade database
|
||
|
* Adds forein keys to all tables.
|
||
|
* Optimalization for accounts.
|
||
|
*
|
||
|
* @author Ondřej Fibich
|
||
|
*/
|
||
| ... | ... | |
|
|
||
|
/* Accounts */
|
||
|
|
||
|
// delete old indexes
|
||
|
"ALTER TABLE `accounts` DROP INDEX `account_attribute_id`;",
|
||
|
|
||
|
// delete invalid rows
|
||
|
"DELETE FROM accounts
|
||
|
WHERE account_attribute_id = 0;",
|
||
|
|
||
|
// delete wrong references
|
||
|
"UPDATE accounts
|
||
|
SET member_id = NULL
|
||
|
WHERE member_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM members
|
||
|
);",
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `accounts` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` (`id`) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for account_attribute to disable delete
|
||
|
"ALTER TABLE `accounts` ADD FOREIGN KEY ( `account_attribute_id` )
|
||
|
REFERENCES `account_attributes` (`id`) ON DELETE RESTRICT;",
|
||
|
|
||
|
// add key for comments_thread
|
||
|
"ALTER TABLE `accounts` ADD FOREIGN KEY ( `comments_thread_id` )
|
||
|
REFERENCES `comments_threads` (`id`) ON DELETE SET NULL;",
|
||
|
|
||
|
/* Accounts bank accounts */
|
||
|
|
||
|
// add key for account
|
||
|
"ALTER TABLE `accounts_bank_accounts` ADD FOREIGN KEY ( `account_id` )
|
||
|
REFERENCES `accounts` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for bank_account
|
||
|
"ALTER TABLE `accounts_bank_accounts` ADD FOREIGN KEY ( `bank_account_id` )
|
||
|
REFERENCES `bank_accounts` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
/* Address points */
|
||
|
|
||
|
// fix wrong values of street
|
||
|
"UPDATE `address_points` SET street_id = NULL WHERE street_id = 0;",
|
||
|
|
||
|
// add key for street
|
||
|
"ALTER TABLE `address_points` ADD FOREIGN KEY ( `street_id` )
|
||
|
REFERENCES `streets` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for town
|
||
|
"ALTER TABLE `address_points` ADD FOREIGN KEY ( `town_id` )
|
||
|
REFERENCES `towns` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// fix column type to correxpondes countries.id
|
||
|
"ALTER TABLE `address_points`
|
||
|
CHANGE `country_id` `country_id` SMALLINT( 6 ) NOT NULL;",
|
||
|
|
||
|
// add key for country
|
||
|
"ALTER TABLE `address_points` ADD FOREIGN KEY ( `country_id` )
|
||
|
REFERENCES `countries` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Allowed subnets */
|
||
|
|
||
|
// drop old wrong index
|
||
|
"ALTER TABLE `allowed_subnets` DROP INDEX `member_id`;",
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `allowed_subnets` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key fo subnet
|
||
|
"ALTER TABLE `allowed_subnets` ADD FOREIGN KEY ( `subnet_id` )
|
||
|
REFERENCES `subnets` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Allowed subnets counts */
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `allowed_subnets_counts` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
/* Approval template items */
|
||
|
|
||
|
// change column to allow NULL
|
||
|
"ALTER TABLE `approval_template_items`
|
||
|
CHANGE `approval_template_id` `approval_template_id` INT( 11 ) NULL DEFAULT NULL;",
|
||
|
|
||
|
// add key for approval_template
|
||
|
"ALTER TABLE `approval_template_items` ADD FOREIGN KEY ( `approval_template_id` )
|
||
|
REFERENCES `approval_templates` (`id`) ON DELETE SET NULL ;",
|
||
|
|
||
|
// add key for approval_type
|
||
|
"ALTER TABLE `approval_template_items` ADD FOREIGN KEY ( `approval_type_id` )
|
||
|
REFERENCES `approval_types` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
/* AXO */
|
||
|
|
||
|
// optimalization
|
||
|
"ALTER TABLE `axo` ADD INDEX ( `value` );",
|
||
|
"ALTER TABLE `accounts` ADD INDEX ( `balance` )",
|
||
|
|
||
|
/* Bacups */
|
||
|
|
||
|
// not used
|
||
|
"DROP TABLE `backups`;",
|
||
|
|
||
|
/* Bank accounts */
|
||
|
|
||
|
// length of id doest correspondes to other value in system
|
||
|
"ALTER TABLE `bank_accounts`
|
||
|
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;",
|
||
|
|
||
|
// change column restrictions
|
||
|
"ALTER TABLE `bank_accounts`
|
||
|
CHANGE `member_id` `member_id` INT( 11 ) NULL DEFAULT NULL;",
|
||
|
|
||
|
// fix wrong values of members
|
||
|
"UPDATE `bank_accounts` SET member_id = NULL WHERE member_id = 0;",
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `bank_accounts` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` (`id`) ON DELETE SET NULL ;",
|
||
|
|
||
|
/* Bank statements */
|
||
|
|
||
|
// add key for account
|
||
|
"ALTER TABLE `bank_statements` ADD FOREIGN KEY ( `bank_account_id` )
|
||
|
REFERENCES `bank_accounts` (`id`) ON DELETE RESTRICT ;",
|
||
|
|
||
|
// after deleting of user user_id has to be filled to NULL
|
||
|
"ALTER TABLE `bank_statements`
|
||
|
CHANGE `user_id` `user_id` INT( 11 ) NULL DEFAULT NULL;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `bank_statements` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE SET NULL ;",
|
||
|
|
||
|
/* Bank transfers */
|
||
|
|
||
|
// columns have 0 values => change them to NULL
|
||
|
"ALTER TABLE `bank_transfers`
|
||
|
CHANGE `origin_id` `origin_id` INT( 11 ) NULL DEFAULT NULL
|
||
|
COMMENT 'id of the origin bank account in bank_accounts table',
|
||
|
CHANGE `destination_id` `destination_id` INT( 11 ) NULL DEFAULT NULL
|
||
|
COMMENT 'id of the destination bank account in bank_accounts table';",
|
||
|
|
||
|
// fix wrong values of origin
|
||
|
"UPDATE `bank_transfers` SET origin_id = NULL WHERE origin_id = 0;",
|
||
|
|
||
|
// fix wrong values of destination
|
||
|
"UPDATE `bank_transfers` SET destination_id = NULL WHERE destination_id = 0;",
|
||
|
|
||
|
// add key for origin
|
||
|
"ALTER TABLE `bank_transfers` ADD FOREIGN KEY ( `origin_id` )
|
||
|
REFERENCES `bank_accounts` (`id`) ON DELETE SET NULL ;",
|
||
|
|
||
|
// add key for destination
|
||
|
"ALTER TABLE `bank_transfers` ADD FOREIGN KEY ( `destination_id` )
|
||
|
REFERENCES `bank_accounts` (`id`) ON DELETE SET NULL ;",
|
||
|
|
||
|
// delete wrong references
|
||
|
"UPDATE bank_transfers
|
||
|
SET transfer_id = NULL
|
||
|
WHERE transfer_id IS NOT NULL AND transfer_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM transfers
|
||
|
WHERE id IS NOT NULL
|
||
|
);",
|
||
|
|
||
|
// add key for transfer
|
||
|
"ALTER TABLE `bank_transfers` ADD FOREIGN KEY ( `transfer_id` )
|
||
|
REFERENCES `transfers` (`id`) ON DELETE SET NULL ;",
|
||
|
|
||
|
// add key for destination
|
||
|
"ALTER TABLE `bank_transfers` ADD FOREIGN KEY ( `bank_statement_id` )
|
||
|
REFERENCES `bank_statements` (`id`) ON DELETE SET NULL ;",
|
||
|
|
||
|
/* Cash */
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `cash` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for transfer
|
||
|
"ALTER TABLE `cash` ADD FOREIGN KEY ( `transfer_id` )
|
||
|
REFERENCES `transfers` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Comments */
|
||
|
|
||
|
// wrong old index
|
||
|
"ALTER TABLE `comments` DROP INDEX `comments_thread_id`;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `comments` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
// add key for comments_thread
|
||
|
"ALTER TABLE `comments` ADD FOREIGN KEY ( `comments_thread_id` )
|
||
|
REFERENCES `comments_threads` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
/* Contacts countries */
|
||
|
|
||
|
// add key for country
|
||
|
"ALTER TABLE `contacts_countries` ADD FOREIGN KEY ( `country_id` )
|
||
|
REFERENCES `countries` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM contacts_countries
|
||
|
WHERE contact_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM contacts
|
||
|
);",
|
||
|
|
||
|
// add key for contact
|
||
|
"ALTER TABLE `contacts_countries` ADD FOREIGN KEY ( `contact_id` )
|
||
|
REFERENCES `contacts` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
/* Device */
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `devices` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// delete wrong references
|
||
|
"UPDATE devices
|
||
|
SET address_point_id = NULL
|
||
|
WHERE address_point_id IS NOT NULL AND address_point_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM address_points
|
||
|
WHERE id IS NOT NULL
|
||
|
);",
|
||
|
|
||
|
// add key for address_point
|
||
|
"ALTER TABLE `devices` ADD FOREIGN KEY ( `address_point_id` )
|
||
|
REFERENCES `address_points` (`id`) ON DELETE RESTRICT ;",
|
||
|
|
||
|
/* Device admins */
|
||
|
|
||
|
// drop wring index
|
||
|
"ALTER TABLE `device_admins` DROP INDEX `device_has_admin`;",
|
||
|
|
||
|
// drop wring index
|
||
|
"ALTER TABLE `device_admins` DROP INDEX `user_administers_device`;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `device_admins` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for device
|
||
|
"ALTER TABLE `device_admins` ADD FOREIGN KEY ( `device_id` )
|
||
|
REFERENCES `devices` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Devices engeneers */
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `device_engineers` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// delte wrong references
|
||
|
"DELETE FROM `device_engineers`
|
||
|
WHERE device_id NOT IN (SELECT id FROM devices);",
|
||
|
|
||
|
// add key for device
|
||
|
"ALTER TABLE `device_engineers` ADD FOREIGN KEY ( `device_id` )
|
||
|
REFERENCES `devices` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Enum types */
|
||
|
|
||
|
// drop wrong index
|
||
|
"ALTER TABLE `enum_types` DROP INDEX `value_of_type`;",
|
||
|
|
||
|
// add key for enum_type_names
|
||
|
"ALTER TABLE `enum_types` ADD FOREIGN KEY ( `type_id` )
|
||
|
REFERENCES `enum_type_names` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Fees */
|
||
|
|
||
|
// fix wrong column
|
||
|
"ALTER TABLE `fees` CHANGE `type_id` `type_id` INT( 11 ) NOT NULL;",
|
||
|
|
||
|
// add key for enum_type
|
||
|
"ALTER TABLE `fees` ADD FOREIGN KEY ( `type_id` )
|
||
|
REFERENCES `enum_types` ( `id` ) ON DELETE RESTRICT;",
|
||
|
|
||
|
/* Groups aro map */
|
||
|
|
||
|
// delte wrong references
|
||
|
"DELETE FROM `groups_aro_map`
|
||
|
WHERE aro_id NOT IN (SELECT id FROM users);",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `groups_aro_map` ADD FOREIGN KEY ( `aro_id` )
|
||
|
REFERENCES `users` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Ifaces */
|
||
|
|
||
|
// optimalization
|
||
|
"ALTER TABLE `ifaces` ADD INDEX ( `mac` );",
|
||
|
|
||
|
// delete wrong references
|
||
|
"UPDATE ifaces
|
||
|
SET device_id = NULL
|
||
|
WHERE device_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM devices
|
||
|
)",
|
||
|
|
||
|
// add key for device
|
||
|
"ALTER TABLE `ifaces` ADD FOREIGN KEY ( `device_id` )
|
||
|
REFERENCES `devices` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for segment
|
||
|
"ALTER TABLE `ifaces` ADD FOREIGN KEY ( `segment_id` )
|
||
|
REFERENCES `segments` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
/* Invoice */
|
||
|
|
||
|
// columns can have NULL values now
|
||
|
"ALTER TABLE `invoices`
|
||
|
CHANGE `supplier_id` `supplier_id` INT( 11 ) NULL DEFAULT NULL",
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `invoices` ADD FOREIGN KEY ( `supplier_id` )
|
||
|
REFERENCES `members` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Invoice items */
|
||
|
|
||
|
// add key for invoice
|
||
|
"ALTER TABLE `invoice_items` ADD FOREIGN KEY ( `invoice_id` )
|
||
|
REFERENCES `invoices` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* IP adresses */
|
||
|
|
||
|
// drop old indexes
|
||
|
"ALTER TABLE `ip_addresses` DROP INDEX `iface_address`",
|
||
|
"ALTER TABLE `ip_addresses` DROP INDEX `subnet_address`",
|
||
|
"ALTER TABLE `ip_addresses` DROP INDEX `VLAN_iface_address`",
|
||
|
"ALTER TABLE `ip_addresses` DROP INDEX `member_id`",
|
||
|
|
||
|
// fix wrong values
|
||
|
"UPDATE `ip_addresses` SET iface_id = NULL WHERE iface_id = 0;",
|
||
|
"UPDATE `ip_addresses` SET vlan_iface_id = NULL WHERE vlan_iface_id = 0;",
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `ip_addresses` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for iface
|
||
|
"ALTER TABLE `ip_addresses` ADD FOREIGN KEY ( `iface_id` )
|
||
|
REFERENCES `ifaces` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for vlan_iface
|
||
|
"ALTER TABLE `ip_addresses` ADD FOREIGN KEY ( `vlan_iface_id` )
|
||
|
REFERENCES `vlan_ifaces` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for vlan_iface
|
||
|
"ALTER TABLE `ip_addresses` ADD FOREIGN KEY ( `subnet_id` )
|
||
|
REFERENCES `subnets` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
/* Items */
|
||
|
|
||
|
// not used
|
||
|
"DROP TABLE items, item_locations, item_borrowings;",
|
||
|
|
||
|
/* Jobs */
|
||
|
|
||
|
// drop old indexes
|
||
|
// "ALTER TABLE `jobs` DROP INDEX IF EXISTS `commited_by`;",
|
||
|
// "ALTER TABLE `jobs` DROP INDEX `transfer_salary`;",
|
||
|
// "ALTER TABLE `jobs` DROP INDEX `comments_thread_id`;",
|
||
|
|
||
|
// change added_by_id for setting to NULL after adder deletion
|
||
|
"ALTER TABLE `jobs`
|
||
|
CHANGE `added_by_id` `added_by_id` INT( 11 ) NULL DEFAULT NULL;",
|
||
|
|
||
|
// add key for job_report
|
||
|
"ALTER TABLE `jobs` ADD FOREIGN KEY ( `job_report_id` )
|
||
|
REFERENCES `job_reports` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `jobs` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` ( `id` ) ON DELETE RESTRICT;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `jobs` ADD FOREIGN KEY ( `added_by_id` )
|
||
|
REFERENCES `users` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for approval_template
|
||
|
"ALTER TABLE `jobs` ADD FOREIGN KEY ( `approval_template_id` )
|
||
|
REFERENCES `approval_templates` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for approval_template
|
||
|
"ALTER TABLE `jobs` ADD FOREIGN KEY ( `transfer_id` )
|
||
|
REFERENCES `transfers` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for approval_template
|
||
|
"ALTER TABLE `jobs` ADD FOREIGN KEY ( `comments_thread_id` )
|
||
|
REFERENCES `comments_threads` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
/* Logs */
|
||
|
|
||
|
// just add index, no forein for security reasons
|
||
|
"ALTER TABLE `logs` ADD INDEX ( `user_id` )",
|
||
|
|
||
|
/* Login logs */
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM login_logs
|
||
|
WHERE user_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM users
|
||
|
)",
|
||
|
|
||
|
// delete old index
|
||
|
"ALTER TABLE `login_logs` DROP INDEX `user_id`",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `login_logs` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Mail messages */
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM mail_messages
|
||
|
WHERE from_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM users
|
||
|
)",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `mail_messages` ADD FOREIGN KEY ( `from_id` )
|
||
|
REFERENCES `users` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM mail_messages
|
||
|
WHERE to_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM users
|
||
|
)",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `mail_messages` ADD FOREIGN KEY ( `to_id` )
|
||
|
REFERENCES `users` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Member */
|
||
|
|
||
|
// fix wrong values of user_id
|
||
|
"UPDATE `members` SET user_id = NULL WHERE user_id = 0;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `members` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for address point
|
||
|
"ALTER TABLE `members` ADD FOREIGN KEY ( `address_point_id` )
|
||
|
REFERENCES `address_points` ( `id` ) ON DELETE RESTRICT;",
|
||
|
|
||
|
/* Membership interrupts */
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `membership_interrupts` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for member_fee
|
||
|
"ALTER TABLE `membership_interrupts` ADD FOREIGN KEY ( `members_fee_id` )
|
||
|
REFERENCES `members_fees` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
/* Members domiciles */
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `members_domiciles` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for address point
|
||
|
"ALTER TABLE `members_domiciles` ADD FOREIGN KEY ( `address_point_id` )
|
||
|
REFERENCES `address_points` ( `id` ) ON DELETE RESTRICT;",
|
||
|
|
||
|
/* Members fees */
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `members_fees` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for fee
|
||
|
"ALTER TABLE `members_fees` ADD FOREIGN KEY ( `fee_id` )
|
||
|
REFERENCES `fees` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Members traffics */
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `members_traffics` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Members traffics daily */
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `members_traffics_daily` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Messages ip addresses */
|
||
|
|
||
|
// user id has wrong values (0) => set it to NULL
|
||
|
"ALTER TABLE `messages_ip_addresses`
|
||
|
CHANGE `user_id` `user_id` INT( 11 ) NULL DEFAULT NULL
|
||
|
COMMENT 'user id of admin who redirects'",
|
||
|
|
||
|
// fix wrong values
|
||
|
"UPDATE `messages_ip_addresses` SET user_id = NULL WHERE user_id = 0;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `messages_ip_addresses` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` ( `id` ) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for message
|
||
|
"ALTER TABLE `messages_ip_addresses` ADD FOREIGN KEY ( `message_id` )
|
||
|
REFERENCES `messages` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for ip_addresses
|
||
|
"ALTER TABLE `messages_ip_addresses` ADD FOREIGN KEY ( `ip_address_id` )
|
||
|
REFERENCES `ip_addresses` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Phone calls */
|
||
|
|
||
|
// optimalizations
|
||
|
"ALTER TABLE `phone_calls` ADD INDEX ( `datetime` );",
|
||
|
"ALTER TABLE `phone_calls` ADD INDEX ( `number` );",
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM phone_calls
|
||
|
WHERE phone_invoice_user_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM phone_invoice_users
|
||
|
)",
|
||
|
|
||
|
// add key for phone_invoice_user
|
||
|
"ALTER TABLE `phone_calls` ADD FOREIGN KEY ( `phone_invoice_user_id` )
|
||
|
REFERENCES `phone_invoice_users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Phone connections */
|
||
|
|
||
|
// optimalizations
|
||
|
"ALTER TABLE `phone_connections` ADD INDEX ( `datetime` );",
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM phone_connections
|
||
|
WHERE phone_invoice_user_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM phone_invoice_users
|
||
|
)",
|
||
|
|
||
|
// add key for phone_invoice_user
|
||
|
"ALTER TABLE `phone_connections` ADD FOREIGN KEY ( `phone_invoice_user_id` )
|
||
|
REFERENCES `phone_invoice_users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Phone fixed calls */
|
||
|
|
||
|
// optimalizations
|
||
|
"ALTER TABLE `phone_fixed_calls` ADD INDEX ( `datetime` );",
|
||
|
"ALTER TABLE `phone_fixed_calls` ADD INDEX ( `number` );",
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM phone_fixed_calls
|
||
|
WHERE phone_invoice_user_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM phone_invoice_users
|
||
|
)",
|
||
|
|
||
|
// add key for phone_invoice_user
|
||
|
"ALTER TABLE `phone_fixed_calls` ADD FOREIGN KEY ( `phone_invoice_user_id` )
|
||
|
REFERENCES `phone_invoice_users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Phone invoice users */
|
||
|
|
||
|
// optimalizations
|
||
|
"ALTER TABLE `phone_invoice_users` ADD INDEX ( `phone_number` )",
|
||
|
|
||
|
// user id has wrong values (0) => set it to NULL
|
||
|
"ALTER TABLE `phone_invoice_users`
|
||
|
CHANGE `user_id` `user_id` INT( 11 ) NULL DEFAULT NULL
|
||
|
COMMENT 'ID of user or NULL if user was not assigned yet'",
|
||
|
|
||
|
// fix wrong values
|
||
|
"UPDATE `phone_invoice_users` SET user_id = NULL WHERE user_id = 0;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `phone_invoice_users` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM phone_invoice_users
|
||
|
WHERE phone_invoice_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM phone_invoices
|
||
|
)",
|
||
|
|
||
|
// add key fo invoice
|
||
|
"ALTER TABLE `phone_invoice_users` ADD FOREIGN KEY ( `phone_invoice_id` )
|
||
|
REFERENCES `phone_invoices` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Phone pays */
|
||
|
|
||
|
// optimalizations
|
||
|
"ALTER TABLE `phone_pays` ADD INDEX ( `datetime` );",
|
||
|
"ALTER TABLE `phone_pays` ADD INDEX ( `number` );",
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM phone_pays
|
||
|
WHERE phone_invoice_user_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM phone_invoice_users
|
||
|
);",
|
||
|
|
||
|
// add key for phone_invoice_user
|
||
|
"ALTER TABLE `phone_pays` ADD FOREIGN KEY ( `phone_invoice_user_id` )
|
||
|
REFERENCES `phone_invoice_users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Phone roaming sms messages */
|
||
|
|
||
|
// optimalizations
|
||
|
"ALTER TABLE `phone_roaming_sms_messages` ADD INDEX ( `datetime` );",
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM phone_roaming_sms_messages
|
||
|
WHERE phone_invoice_user_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM phone_invoice_users
|
||
|
);",
|
||
|
|
||
|
// add key for phone_invoice_user
|
||
|
"ALTER TABLE `phone_roaming_sms_messages` ADD FOREIGN KEY ( `phone_invoice_user_id` )
|
||
|
REFERENCES `phone_invoice_users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Phone sms messages */
|
||
|
|
||
|
// optimalizations
|
||
|
"ALTER TABLE `phone_sms_messages` ADD INDEX ( `datetime` );",
|
||
|
"ALTER TABLE `phone_sms_messages` ADD INDEX ( `number` );",
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM phone_sms_messages
|
||
|
WHERE phone_invoice_user_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM phone_invoice_users
|
||
|
);",
|
||
|
|
||
|
// add key for phone_invoice_user
|
||
|
"ALTER TABLE `phone_sms_messages` ADD FOREIGN KEY ( `phone_invoice_user_id` )
|
||
|
REFERENCES `phone_invoice_users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Phone vpn calls */
|
||
|
|
||
|
// optimalizations
|
||
|
"ALTER TABLE `phone_vpn_calls` ADD INDEX ( `datetime` );",
|
||
|
"ALTER TABLE `phone_vpn_calls` ADD INDEX ( `number` );",
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM phone_vpn_calls
|
||
|
WHERE phone_invoice_user_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM phone_invoice_users
|
||
|
);",
|
||
|
|
||
|
// add key for phone_invoice_user
|
||
|
"ALTER TABLE `phone_vpn_calls` ADD FOREIGN KEY ( `phone_invoice_user_id` )
|
||
|
REFERENCES `phone_invoice_users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Ports */
|
||
|
|
||
|
// delete old indexes
|
||
|
"ALTER TABLE `ports` DROP INDEX `device_port`;",
|
||
|
"ALTER TABLE `ports` DROP INDEX `segment_port`;",
|
||
|
|
||
|
// add index for device
|
||
|
"ALTER TABLE `ports` ADD FOREIGN KEY ( `device_id` )
|
||
|
REFERENCES `devices` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// add index for segments
|
||
|
"ALTER TABLE `ports` ADD FOREIGN KEY ( `segment_id` )
|
||
|
REFERENCES `segments` (`id`) ON DELETE SET NULL;",
|
||
|
|
||
|
/* Ports VLANs */
|
||
|
|
||
|
// add index for port
|
||
|
"ALTER TABLE `ports_vlans` ADD FOREIGN KEY ( `port_id` )
|
||
|
REFERENCES `ports` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// add index for vlans
|
||
|
"ALTER TABLE `ports_vlans` ADD FOREIGN KEY ( `vlan_id` )
|
||
|
REFERENCES `vlans` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Segments */
|
||
|
|
||
|
// optimalization
|
||
|
"ALTER TABLE `segments` ADD INDEX ( `name` );",
|
||
|
|
||
|
/* SMS messages */
|
||
|
|
||
|
// delete old indexes
|
||
|
"ALTER TABLE `sms_messages` DROP INDEX `sms_message_id`;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `sms_messages` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for sms message
|
||
|
"ALTER TABLE `sms_messages` ADD FOREIGN KEY ( `sms_message_id` )
|
||
|
REFERENCES `sms_messages` (`id`) ON DELETE SET NULL;",
|
||
|
|
||
|
/* Streets */
|
||
|
|
||
|
// optimalization
|
||
|
"ALTER TABLE `streets` ADD INDEX ( `street` );",
|
||
|
|
||
|
/* Subnets */
|
||
|
|
||
|
// optimalization
|
||
|
"ALTER TABLE `subnets` ADD INDEX ( `name` )",
|
||
|
"ALTER TABLE `subnets` ADD INDEX ( `network_address` );",
|
||
|
"ALTER TABLE `subnets` ADD INDEX ( `netmask` );",
|
||
|
|
||
|
/* Subnets owner */
|
||
|
|
||
|
// drop old indexes
|
||
|
"ALTER TABLE `subnets_owners` DROP INDEX `subnet_id`;",
|
||
|
|
||
|
// add key for subnet
|
||
|
"ALTER TABLE `subnets_owners` ADD FOREIGN KEY ( `subnet_id` )
|
||
|
REFERENCES `subnets` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `subnets_owners` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Towns */
|
||
|
|
||
|
// optimalizations
|
||
|
"ALTER TABLE `towns` ADD INDEX ( `town` );",
|
||
|
"ALTER TABLE `towns` ADD INDEX ( `zip_code` );",
|
||
|
|
||
|
/* Transfers */
|
||
|
|
||
|
// delete old indexes
|
||
|
"ALTER TABLE `transfers` DROP INDEX `from`;",
|
||
|
"ALTER TABLE `transfers` DROP INDEX `to`;",
|
||
|
"ALTER TABLE `transfers` DROP INDEX `previous_transfer_id`;",
|
||
|
|
||
|
// fix wrong values
|
||
|
"UPDATE `transfers` SET origin_id = NULL WHERE origin_id = 0;",
|
||
|
"UPDATE `transfers` SET destination_id = NULL WHERE destination_id = 0;",
|
||
|
"UPDATE `transfers` SET previous_transfer_id = NULL WHERE previous_transfer_id = 0;",
|
||
|
"UPDATE `transfers` SET member_id = NULL WHERE member_id = 0;",
|
||
|
"UPDATE `transfers` SET user_id = NULL WHERE user_id = 0;",
|
||
|
|
||
|
// add key for account
|
||
|
"ALTER TABLE `transfers` ADD FOREIGN KEY ( `origin_id` )
|
||
|
REFERENCES `accounts` (`id`) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for account
|
||
|
"ALTER TABLE `transfers` ADD FOREIGN KEY ( `destination_id` )
|
||
|
REFERENCES `accounts` (`id`) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `transfers` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` (`id`) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `transfers` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE SET NULL;",
|
||
|
|
||
|
/* Translations */
|
||
|
|
||
|
// optimalization
|
||
|
"ALTER TABLE `translations` ADD INDEX ( `original_term` );",
|
||
|
|
||
|
/* Users */
|
||
|
|
||
|
// optimalizations
|
||
|
"ALTER TABLE `users` ADD INDEX ( `name` );",
|
||
|
"ALTER TABLE `users` ADD INDEX ( `surname` );",
|
||
|
|
||
|
// drop old indexes
|
||
|
"ALTER TABLE `users` DROP INDEX `belogs_to_member`;",
|
||
|
|
||
|
// fix wrong column
|
||
|
"ALTER TABLE `users`
|
||
|
CHANGE `member_id` `member_id` INT( 11 ) NOT NULL",
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `users` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* User keys */
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `users_keys` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
/* VLAN */
|
||
|
|
||
|
// optimalization
|
||
|
"ALTER TABLE `vlans` ADD INDEX ( `name` );",
|
||
|
|
||
|
/* VLAN Ifaces */
|
||
|
|
||
|
// add key for vlan
|
||
|
"ALTER TABLE `vlan_ifaces` ADD FOREIGN KEY ( `vlan_id` )
|
||
|
REFERENCES `vlans` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for interface
|
||
|
"ALTER TABLE `vlan_ifaces` ADD FOREIGN KEY ( `iface_id` )
|
||
|
REFERENCES `ifaces` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
/* VoIP sips */
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `voip_sips` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Votes */
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `votes` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE RESTRICT;",
|
||
|
|
||
|
// add key for jobs
|
||
|
"ALTER TABLE `votes` ADD FOREIGN KEY ( `fk_id` )
|
||
|
REFERENCES `jobs` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for aro group
|
||
|
"ALTER TABLE `votes` ADD FOREIGN KEY ( `aro_group_id` )
|
||
|
REFERENCES `aro_groups` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Wireless settings */
|
||
|
|
||
|
// add key for iface
|
||
|
"ALTER TABLE `wireless_settings` ADD FOREIGN KEY ( `iface_id` )
|
||
|
REFERENCES `ifaces` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Works */
|
||
|
|
||
|
// not used
|
||
|
"DROP TABLE works;"
|
||
|
|
||
|
);
|
||
| freenetis/branches/testing/application/upgrade_sql/upgrade_sql_1171.php | ||
|---|---|---|
|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
|
/*
|
||
|
* This file is part of open source system FreeNetIS
|
||
|
* and it is released under GPLv3 licence.
|
||
|
*
|
||
|
* More info about licence can be found:
|
||
|
* http://www.gnu.org/licenses/gpl-3.0.html
|
||
|
*
|
||
|
* More info about project can be found:
|
||
|
* http://www.freenetis.org/
|
||
|
*
|
||
|
*/
|
||
|
|
||
|
/**
|
||
|
* Array of sql queries that upgrade database.
|
||
|
* Adds forein keys to all tables.
|
||
|
*
|
||
|
* @author Ondřej Fibich
|
||
|
*/
|
||
|
$upgrade_sql[1171] = array
|
||
|
(
|
||
|
|
||
|
/* Accounts */
|
||
|
|
||
|
// delete old indexes
|
||
|
"ALTER TABLE `accounts` DROP INDEX `account_attribute_id`;",
|
||
|
|
||
|
// delete invalid rows
|
||
|
"DELETE FROM accounts
|
||
|
WHERE account_attribute_id = 0;",
|
||
|
|
||
|
// delete wrong references
|
||
|
"UPDATE accounts
|
||
|
SET member_id = NULL
|
||
|
WHERE member_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM members
|
||
|
);",
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `accounts` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` (`id`) ON DELETE SET NULL;",
|
||
|
|
||
|
// add key for account_attribute to disable delete
|
||
|
"ALTER TABLE `accounts` ADD FOREIGN KEY ( `account_attribute_id` )
|
||
|
REFERENCES `account_attributes` (`id`) ON DELETE RESTRICT;",
|
||
|
|
||
|
// add key for comments_thread
|
||
|
"ALTER TABLE `accounts` ADD FOREIGN KEY ( `comments_thread_id` )
|
||
|
REFERENCES `comments_threads` (`id`) ON DELETE SET NULL;",
|
||
|
|
||
|
/* Accounts bank accounts */
|
||
|
|
||
|
// add key for account
|
||
|
"ALTER TABLE `accounts_bank_accounts` ADD FOREIGN KEY ( `account_id` )
|
||
|
REFERENCES `accounts` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for bank_account
|
||
|
"ALTER TABLE `accounts_bank_accounts` ADD FOREIGN KEY ( `bank_account_id` )
|
||
|
REFERENCES `bank_accounts` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
/* Address points */
|
||
|
|
||
|
// fix wrong values of street
|
||
|
"UPDATE `address_points` SET street_id = NULL WHERE street_id = 0;",
|
||
|
|
||
|
// add key for street
|
||
|
"ALTER TABLE `address_points` ADD FOREIGN KEY ( `street_id` )
|
||
|
REFERENCES `streets` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for town
|
||
|
"ALTER TABLE `address_points` ADD FOREIGN KEY ( `town_id` )
|
||
|
REFERENCES `towns` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// fix column type to correxpondes countries.id
|
||
|
"ALTER TABLE `address_points`
|
||
|
CHANGE `country_id` `country_id` SMALLINT( 6 ) NOT NULL;",
|
||
|
|
||
|
// add key for country
|
||
|
"ALTER TABLE `address_points` ADD FOREIGN KEY ( `country_id` )
|
||
|
REFERENCES `countries` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Allowed subnets */
|
||
|
|
||
|
// drop old wrong index
|
||
|
"ALTER TABLE `allowed_subnets` DROP INDEX `member_id`;",
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `allowed_subnets` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key fo subnet
|
||
|
"ALTER TABLE `allowed_subnets` ADD FOREIGN KEY ( `subnet_id` )
|
||
|
REFERENCES `subnets` ( `id` ) ON DELETE CASCADE;",
|
||
|
|
||
|
/* Allowed subnets counts */
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `allowed_subnets_counts` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
/* Approval template items */
|
||
|
|
||
|
// change column to allow NULL
|
||
|
"ALTER TABLE `approval_template_items`
|
||
|
CHANGE `approval_template_id` `approval_template_id` INT( 11 ) NULL DEFAULT NULL;",
|
||
|
|
||
|
// add key for approval_template
|
||
|
"ALTER TABLE `approval_template_items` ADD FOREIGN KEY ( `approval_template_id` )
|
||
|
REFERENCES `approval_templates` (`id`) ON DELETE SET NULL ;",
|
||
|
|
||
|
// add key for approval_type
|
||
|
"ALTER TABLE `approval_template_items` ADD FOREIGN KEY ( `approval_type_id` )
|
||
|
REFERENCES `approval_types` (`id`) ON DELETE CASCADE ;",
|
||
|
|
||
|
/* AXO */
|
||
|
|
||
|
// optimalization
|
||
|
"ALTER TABLE `axo` ADD INDEX ( `value` );",
|
||
|
|
||
|
/* Bacups */
|
||
|
|
||
|
// not used
|
||
|
"DROP TABLE `backups`;",
|
||
|
|
||
|
/* Bank accounts */
|
||
|
|
||
|
// length of id doest correspondes to other value in system
|
||
|
"ALTER TABLE `bank_accounts`
|
||
|
CHANGE `id` `id` INT( 11 ) NOT NULL AUTO_INCREMENT;",
|
||
|
|
||
|
// change column restrictions
|
||
|
"ALTER TABLE `bank_accounts`
|
||
|
CHANGE `member_id` `member_id` INT( 11 ) NULL DEFAULT NULL;",
|
||
|
|
||
|
// fix wrong values of members
|
||
|
"UPDATE `bank_accounts` SET member_id = NULL WHERE member_id = 0;",
|
||
|
|
||
|
// add key for member
|
||
|
"ALTER TABLE `bank_accounts` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` (`id`) ON DELETE SET NULL ;",
|
||
|
|
||
|
/* Bank statements */
|
||
|
|
||
|
// add key for account
|
||
|
"ALTER TABLE `bank_statements` ADD FOREIGN KEY ( `bank_account_id` )
|
||
|
REFERENCES `bank_accounts` (`id`) ON DELETE RESTRICT ;",
|
||
|
|
||
|
// after deleting of user user_id has to be filled to NULL
|
||
|
"ALTER TABLE `bank_statements`
|
||
|
CHANGE `user_id` `user_id` INT( 11 ) NULL DEFAULT NULL;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `bank_statements` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE SET NULL ;",
|
||
|
|
||
|
/* Bank transfers */
|
||
|
|
||
|
// columns have 0 values => change them to NULL
|
||
|
"ALTER TABLE `bank_transfers`
|
||
Také k dispozici: Unified diff
Novinky:
- ORM::select_list umi CONCAT, a obsahuje vyber razeni
Optimalizace:
- optimalizace dropdownu pomoci ORM::select_list
- optimalizace uzivatelu a clenu pri prazdnem vyhledavani
- pridan index k accounts.balance
Upravy:
- prejmenovan Profiler pro naseptavani