Revize 1170
Přidáno uživatelem Ondřej Fibich před asi 13 roky(ů)
| freenetis/branches/testing/application/vendors/unit_tester/unit_testing_config.xml | ||
|---|---|---|
|
<param value=""/>
|
||
|
</input>
|
||
|
<input>
|
||
|
<param value="a"/>
|
||
|
<param value="1"/>
|
||
|
<param value="1"/>
|
||
|
<param value="1"/>
|
||
|
<param value="1"/>
|
||
|
</input>
|
||
|
<input>
|
||
|
<param value="a%'se"/>
|
||
| ... | ... | |
|
</input>
|
||
|
</values>
|
||
|
</method>
|
||
|
<method name="get_users_not_in_engeneer_of" autogenerate="on">
|
||
|
<method name="get_users_not_in_engineer_of" autogenerate="on">
|
||
|
<attributes>
|
||
|
<attribute name="device_id" default_value="" />
|
||
|
</attributes>
|
||
| freenetis/branches/testing/application/models/subnet.php | ||
|---|---|---|
|
for ($i=1;$i<=$total_available;$i++)
|
||
|
$ip_queries[] = "SELECT '".long2ip($network+$i)."' AS ip_address";
|
||
|
|
||
|
if (!count($ip_queries))
|
||
|
return array();
|
||
|
|
||
|
$ip_query = implode("\nUNION\n", $ip_queries);
|
||
|
|
||
|
$ips = $this->db->query("
|
||
| freenetis/branches/testing/application/models/user.php | ||
|---|---|---|
|
", $id);
|
||
|
}
|
||
|
|
||
|
public function get_users_not_in_engeneer_of($device_id)
|
||
|
public function get_users_not_in_engineer_of($device_id)
|
||
|
{
|
||
|
return $this->db->query("
|
||
|
SELECT u.id, CONCAT(u.surname, ' ', u.name, ' - ', u.login) AS name
|
||
| freenetis/branches/testing/application/models/message.php | ||
|---|---|---|
|
*/
|
||
|
public function activate_interrupted_membership_message($user_id)
|
||
|
{
|
||
|
// user id
|
||
|
$user_id = intval($user_id);
|
||
|
$user_id = ($user_id) ? $user_id : 'NULL';
|
||
|
// preparation
|
||
|
$message = ORM::factory('message')->where(
|
||
|
'type', self::$interrupted_membership_message)->find();
|
||
| ... | ... | |
|
foreach($ips as $ip)
|
||
|
{
|
||
|
// insert values
|
||
|
$values[] = "($message->id, $ip->id, " . intval($user_id) . ", '', '$datetime')";
|
||
|
$values[] = "($message->id, $ip->id, $user_id, '', '$datetime')";
|
||
|
$ip_count++;
|
||
|
}
|
||
|
$sql_insert .= implode(",", $values);
|
||
| ... | ... | |
|
*/
|
||
|
public function activate_debtor_message($user_id, $debtor_boundary)
|
||
|
{
|
||
|
// user id
|
||
|
$user_id = intval($user_id);
|
||
|
$user_id = ($user_id) ? $user_id : 'NULL';
|
||
|
// preparation
|
||
|
$message = ORM::factory('message')->where(
|
||
|
'type', self::$debtor_message)->find();
|
||
| ... | ... | |
|
foreach($ips as $ip)
|
||
|
{
|
||
|
// insert values
|
||
|
$values[] = "($message->id, $ip->id, " . intval($user_id) . ", '', '$datetime')";
|
||
|
$values[] = "($message->id, $ip->id, $user_id, '', '$datetime')";
|
||
|
$ip_count++;
|
||
|
}
|
||
|
$sql_insert .= implode(",", $values);
|
||
| ... | ... | |
|
public function activate_payment_notice_message(
|
||
|
$user_id, $payment_notice_boundary, $debtor_boundary)
|
||
|
{
|
||
|
// user id
|
||
|
$user_id = intval($user_id);
|
||
|
$user_id = ($user_id) ? $user_id : 'NULL';
|
||
|
// preparation
|
||
|
$message = ORM::factory('message')->where(
|
||
|
'type', self::$payment_notice_message)->find();
|
||
| ... | ... | |
|
foreach($ips as $ip)
|
||
|
{
|
||
|
// insert values
|
||
|
$values[] = "($message->id, $ip->id, " . intval($user_id) . ", '', '$datetime')";
|
||
|
$values[] = "($message->id, $ip->id, $user_id, '', '$datetime')";
|
||
|
$ip_count++;
|
||
|
}
|
||
|
$sql_insert .= implode(",", $values);
|
||
| freenetis/branches/testing/application/models/bank_account.php | ||
|---|---|---|
|
|
||
|
/**
|
||
|
* @author Tomas Dulik
|
||
|
* @param $name - name of the bank account
|
||
|
* @param $account_nr - bank account number
|
||
|
* @param $bank_nr - bank number
|
||
|
* @param $member_id - id of the owner
|
||
|
* @param string $name - name of the bank account
|
||
|
* @param integer $account_nr - bank account number
|
||
|
* @param integer $bank_nr - bank number
|
||
|
* @param integer $member_id - id of the owner
|
||
|
* @return new object containing the new record model
|
||
|
*/
|
||
|
public static function create($name, $account_nr, $bank_nr, $member_id)
|
||
|
{
|
||
|
$member_id = intval($member_id);
|
||
|
|
||
|
$bank_acc = new Bank_account_model();
|
||
|
$bank_acc->member_id = $member_id;
|
||
|
$bank_acc->member_id = ($member_id) ? $member_id : NULL;
|
||
|
$bank_acc->name = $name;
|
||
|
$bank_acc->account_nr = $account_nr;
|
||
|
$bank_acc->bank_nr = $bank_nr;
|
||
|
$bank_acc->bank_nr = $bank_acc;
|
||
|
$bank_acc->save();
|
||
|
return $bank_acc;
|
||
|
}
|
||
| freenetis/branches/testing/application/controllers/device_engineers.php | ||
|---|---|---|
|
if ($device->id == 0)
|
||
|
Controller::error(RECORD);
|
||
|
|
||
|
$users = ORM::factory('user')->get_users_not_in_engeneer_of($device_id);
|
||
|
$users = ORM::factory('user')->get_users_not_in_engineer_of($device_id);
|
||
|
|
||
|
foreach ($users as $user)
|
||
|
{
|
||
| freenetis/branches/testing/application/controllers/users.php | ||
|---|---|---|
|
|
||
|
$user_model = new User_Model($user_id);
|
||
|
|
||
|
if (!$user_model || $user_model->id)
|
||
|
if (!$user_model || !$user_model->id)
|
||
|
Controller::error(RECORD);
|
||
|
|
||
|
$member_id = $user_model->member_id;
|
||
| freenetis/branches/testing/application/controllers/members.php | ||
|---|---|---|
|
// address of connecting place is same as address of domicile
|
||
|
else
|
||
|
{
|
||
|
$member->members_domicile->delete();
|
||
|
|
||
|
// delete orphan address point
|
||
|
if ($address_point_model->count_all_items_by_address_point_id(
|
||
|
$member->members_domicile->address_point_id) == 1)
|
||
|
{
|
||
|
$member->members_domicile->address_point->delete();
|
||
|
}
|
||
|
|
||
|
$member->members_domicile->delete();
|
||
|
}
|
||
|
// removes duplicity
|
||
|
if ($member->members_domicile->address_point_id == $member->address_point_id)
|
||
| freenetis/branches/testing/application/controllers/invoices.php | ||
|---|---|---|
|
if (!$this->acl_check_new('Accounts_Controller', 'invoices'))
|
||
|
Controller::Error(ACCESS);
|
||
|
|
||
|
$member_model = new Member_Model();
|
||
|
$members = $member_model->find_all();
|
||
|
$arr_members = ORM::factory('member')->select_list('id', 'name');
|
||
|
|
||
|
$arr_members = arr::from_objects($members);
|
||
|
|
||
|
// creates form
|
||
|
|
||
|
$this->form = new Forge(
|
||
| freenetis/branches/testing/application/controllers/devices.php | ||
|---|---|---|
|
$form_data["street_id"], $form_data["street_number"],
|
||
|
$gpsx, $gpsy
|
||
|
);
|
||
|
|
||
|
$device_saved = TRUE;
|
||
|
|
||
|
// add address point if there is no such
|
||
|
if (!$address_point->id)
|
||
| ... | ... | |
|
$address_point->save();
|
||
|
// delete old?
|
||
|
$addr_id = $device->address_point->id;
|
||
|
// add to device
|
||
|
$device->address_point_id = $address_point->id;
|
||
|
$device_saved = $device->save();
|
||
|
// change just for this device?
|
||
|
if ($address_point->count_all_items_by_address_point_id($addr_id) <= 1)
|
||
|
if ($address_point->count_all_items_by_address_point_id($addr_id) < 1)
|
||
|
{
|
||
|
$addr = new Address_point_Model($addr_id);
|
||
|
$addr->delete();
|
||
| ... | ... | |
|
$address_point->gps = '';
|
||
|
$address_point->save();
|
||
|
}
|
||
|
|
||
|
$device->address_point_id = $address_point->id;
|
||
|
$device_saved = $device->save();
|
||
|
|
||
|
unset($form_data);
|
||
|
|
||
| freenetis/branches/testing/application/upgrade_sql/upgrade_sql.php | ||
|---|---|---|
|
return $svnid;
|
||
|
}
|
||
|
|
||
|
// array of sql queries that upgrade database
|
||
|
/**
|
||
|
* Array of sql queries that upgrade database
|
||
|
* Adds forein keys to all tables.
|
||
|
*
|
||
|
* @author Ondřej Fibich
|
||
|
*/
|
||
|
$upgrade_sql[get_SVN_rev()] = array
|
||
|
(
|
||
|
/* Cleaning GACL mess */
|
||
|
|
||
|
"DROP TABLE acl_sections, acl_seq, aco_sections, aco_sections_seq, aco_seq,
|
||
|
aro_groups_id_seq, aro_map, aro_sections, aro_sections_seq,
|
||
|
aro_seq, axo_groups, axo_groups_id_seq, axo_groups_map,
|
||
|
axo_sections_seq, axo_seq, groups_axo_map, phpgacl;",
|
||
|
/* Accounts */
|
||
|
|
||
|
// delete old indexes
|
||
|
"ALTER TABLE `accounts` DROP INDEX `is_owned_by`;",
|
||
|
"ALTER TABLE `accounts` DROP INDEX `account_attribute_id`;",
|
||
|
|
||
|
"DROP VIEW `aro`;",
|
||
|
// 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;",
|
||
|
|
||
|
"ALTER TABLE `acl`
|
||
|
DROP `allow`,
|
||
|
DROP `enabled`,
|
||
|
DROP `return_value`,
|
||
|
DROP `updated_date`,
|
||
|
DROP `section_value`;",
|
||
|
/* Accounts bank accounts */
|
||
|
|
||
|
"ALTER TABLE `aco`
|
||
|
DROP `order_value`,
|
||
|
DROP `hidden`,
|
||
|
DROP `section_value`;",
|
||
|
// 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 ;",
|
||
|
|
||
|
"ALTER TABLE `aco_map`
|
||
|
DROP `section_value`;",
|
||
|
/* Address points */
|
||
|
|
||
|
"ALTER TABLE `axo`
|
||
|
DROP `order_value`,
|
||
|
DROP `hidden`;",
|
||
|
// fix wrong values of street
|
||
|
"UPDATE `address_points` SET street_id = NULL WHERE street_id = 0;",
|
||
|
|
||
|
"ALTER TABLE `axo_sections`
|
||
|
DROP `order_value`,
|
||
|
DROP `hidden`;",
|
||
|
// 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`
|
||
|
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;",
|
||
|
|
||
|
/* Private phone contacts */
|
||
|
|
||
|
// delete old indexes
|
||
|
"ALTER TABLE `private_users_contacts` DROP INDEX `user_id`;",
|
||
|
"ALTER TABLE `private_users_contacts` DROP INDEX `private_users_contacts_ibfk_2`;",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `private_users_contacts` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// delete wrong references
|
||
|
"DELETE FROM private_users_contacts
|
||
|
WHERE contact_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM contacts
|
||
|
);",
|
||
|
|
||
|
// add key for contact
|
||
|
"ALTER TABLE `private_users_contacts` ADD FOREIGN KEY ( `contact_id` )
|
||
|
REFERENCES `contacts` (`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`
|
||
|
DROP `aro_section_value`,
|
||
|
DROP `aro_order_value`,
|
||
|
DROP `aro_hidden`;",
|
||
|
CHANGE `member_id` `member_id` INT( 11 ) NOT NULL",
|
||
|
|
||
|
/* Index for login logs for improoving poor speed */
|
||
|
// add key for member
|
||
|
"ALTER TABLE `users` ADD FOREIGN KEY ( `member_id` )
|
||
|
REFERENCES `members` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
"ALTER TABLE `login_logs` ADD INDEX ( `user_id` )",
|
||
|
/* Users contacts */
|
||
|
|
||
|
/* Remove unused tables */
|
||
|
// drop old indexes
|
||
|
"ALTER TABLE `users_contacts` DROP INDEX `users_contacts_ibfk_2`;",
|
||
|
|
||
|
"DROP TABLE config_files, general_messages, logging, outter_addresses,
|
||
|
smokepings;",
|
||
|
// delete wrong references
|
||
|
"DELETE FROM users_contacts
|
||
|
WHERE contact_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM contacts
|
||
|
) OR user_id NOT IN (
|
||
|
SELECT id
|
||
|
FROM users
|
||
|
);",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `users_contacts` ADD FOREIGN KEY ( `user_id` )
|
||
|
REFERENCES `users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
// add key for contact
|
||
|
"ALTER TABLE `users_contacts` ADD FOREIGN KEY ( `contact_id` )
|
||
|
REFERENCES `contacts` (`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 lbilling accounts */
|
||
|
|
||
|
// unused
|
||
|
"DROP TABLE IF EXISTS `voip_lbilling_accounts`;",
|
||
|
|
||
|
/* VoIP lbilling payments */
|
||
|
|
||
|
// change table
|
||
|
"ALTER TABLE `voip_lbilling_payments` ENGINE = InnoDB;",
|
||
|
|
||
|
// primary key
|
||
|
"ALTER TABLE `voip_lbilling_payments` ADD PRIMARY KEY ( `id` );",
|
||
|
|
||
|
// add key for user
|
||
|
"ALTER TABLE `voip_lbilling_payments` ADD FOREIGN KEY ( `userid` )
|
||
|
REFERENCES `users` (`id`) ON DELETE CASCADE;",
|
||
|
|
||
|
/* VoIP lbilling users */
|
||
|
|
||
|
// change table
|
||
|
"ALTER TABLE `voip_lbilling_users` ENGINE = InnoDB",
|
||
|
|
||
|
// primary key
|
||
|
"ALTER TABLE `voip_lbilling_users` ADD PRIMARY KEY ( `id` );",
|
||
|
|
||
|
/* 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_1147.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
|
||
|
$upgrade_sql[1147] = array
|
||
|
(
|
||
|
|
||
|
/* Cleaning GACL mess */
|
||
|
|
||
|
"DROP TABLE acl_sections, acl_seq, aco_sections, aco_sections_seq, aco_seq,
|
||
|
aro_groups_id_seq, aro_map, aro_sections, aro_sections_seq,
|
||
|
aro_seq, axo_groups, axo_groups_id_seq, axo_groups_map,
|
||
|
axo_sections_seq, axo_seq, groups_axo_map, phpgacl;",
|
||
|
|
||
|
"DROP VIEW `aro`;",
|
||
|
|
||
|
"ALTER TABLE `acl`
|
||
|
DROP `allow`,
|
||
|
DROP `enabled`,
|
||
|
DROP `return_value`,
|
||
|
DROP `updated_date`,
|
||
|
DROP `section_value`;",
|
||
|
|
||
|
"ALTER TABLE `aco`
|
||
|
DROP `order_value`,
|
||
|
DROP `hidden`,
|
||
|
DROP `section_value`;",
|
||
|
|
||
|
"ALTER TABLE `aco_map`
|
||
|
DROP `section_value`;",
|
||
|
|
||
|
"ALTER TABLE `axo`
|
||
|
DROP `order_value`,
|
||
|
DROP `hidden`;",
|
||
|
|
||
|
"ALTER TABLE `axo_sections`
|
||
|
DROP `order_value`,
|
||
|
DROP `hidden`;",
|
||
|
|
||
|
"ALTER TABLE `users`
|
||
|
DROP `aro_section_value`,
|
||
|
DROP `aro_order_value`,
|
||
|
DROP `aro_hidden`;",
|
||
|
|
||
|
/* Index for login logs for improoving poor speed */
|
||
|
|
||
|
"ALTER TABLE `login_logs` ADD INDEX ( `user_id` )",
|
||
|
|
||
|
/* Remove unused tables */
|
||
|
|
||
|
"DROP TABLE config_files, general_messages, logging, outter_addresses,
|
||
|
smokepings;",
|
||
|
|
||
|
);
|
||
Také k dispozici: Unified diff
!!! UPOZORNENI !!!
- Tyto upravy jsou narocne na cas straceny v databazi, mozna budete muset navysit limit pro beh scriptu.
- Pri chybnych datech v databazi neni zarucen bezproblemovy prechod na tuto verzi (z duvodu jeste vetsiho navyseni doby behu).
!!! A PROTO: PRED UPDATEM ZALOHUJTE VASI DATABAZI !!!
Upravy:
- do vsech tabulek byli pridany cizi klice
- pridany dalsi optimalizacni indexi
- nektere sloupce byli upraveny, aby meli spravnou formu
- promazana chybna data
Opravy:
- opravy nalezenych chyb po zavedeni databazovych zmen