search

caiwang / board2ihost

0 stars 0 forks source link

[CLOSED] table schema check and update on iserver #9

Open caiwang opened 9 years ago

caiwang commented 9 years ago

Issue by unissoft-bj Thursday Jan 01, 2015 at 02:21 GMT Originally opened as https://github.com/caiwang/ihostsrc/issues/9

dump table schema userd on iserver

mysqldump --opt -d -uroot -p wlsp authclient authmac authsms authmacip actvst wlact wlsta useraccounts usermacs

useractive > wlsponiserver.sql

remove dump info

sed -i 's|\/!40101 SET \@saved_cs_client = \@\@character_set_client \/\;||g' wlsponiserver.sql sed -i 's|\/!40101 SET character_set_client = utf8 \/\;||g' wlsponiserver.sql sed -i 's|\/!40101 SET character_set_client = \@saved_cs_client \/\;||g' wlsponiserver.sql

remove blank line

sed -i '/^$/d' wlsponiserver.sql

cat wlsponiserver.sql

copy & paste to excel, check difference

update tables on iserver

ALTER TABLE authsms ADD COLUMN msgid varchar(64) DEFAULT NULL; # new column ALTER TABLE authsms MODIFY COLUMN prefix varchar(64) DEFAULT ''; # legth 60 to 64 ALTER TABLE authsms MODIFY COLUMN sms varchar(128) DEFAULT NULL; # legth 10 to 128 ALTER TABLE authsms MODIFY COLUMN postfix varchar(64) DEFAULT ''; # legth 30 to 64

ALTER TABLE useraccounts ADD KEY usercode (usercode); # new index

ALTER TABLE usermacs ADD KEY mac (mac); # new index

ALTER TABLE useractive MODIFY COLUMN updby varchar(128) DEFAULT NULL; # legth 30 to 128 ALTER TABLE useractive MODIFY COLUMN insby varchar(128) DEFAULT NULL; # legth 30 to 128

ALTER TABLE useractive ADD KEY mac (mac); # new index

new table 'smsrcv' on iserver; used by sms server to receive short message(reserved)

----------------------------

Table structure for smsrcv

----------------------------

DROP TABLE IF EXISTS smsrcv; CREATE TABLE smsrcv ( id int NOT NULL AUTO_INCREMENT, msg varchar(128) DEFAULT NULL, phone varchar(32) DEFAULT NULL, msgtime datetime DEFAULT NULL, mlocation varchar(32) DEFAULT NULL, mfolder varchar(16) DEFAULT NULL, rectime datetime DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

new table 'prodorder' on iserver; used by rest service to receive order from ihost

----------------------------

Table structure for prodorder

----------------------------

DROP TABLE IF EXISTS prodorder; # products to be deliveried to user CREATE TABLE prodorder ( #
id int NOT NULL AUTO_INCREMENT, #
userid varchar(36) DEFAULT NULL, # username varchar(36) DEFAULT NULL, # username
srcid int DEFAULT NULL, # iserver field prodcode varchar(10) DEFAULT NULL, # product code prodname varchar(36) DEFAULT NULL, # product name prodtype varchar(36) DEFAULT NULL, # product type prodspec varchar(36) DEFAULT NULL, # product specification proddesp varchar(36) DEFAULT NULL, # product description quan decimal(10,2) DEFAULT NULL, # quantity of product unit varchar(36) DEFAULT NULL, # unit of product pkg varchar(36) DEFAULT NULL, # package of product recipaddr varchar(128) DEFAULT NULL, # recipient address recipname varchar(36) DEFAULT NULL, # recipient name recipphone1 varchar(30) DEFAULT NULL, # recipient phone number #1 recipphone2 varchar(30) DEFAULT NULL, # recipient phone number #2 recipemail varchar(64) DEFAULT NULL, # recipient phone email assignto varchar(64) DEFAULT 'iserver', # processor of the order: iserver/local delicode varchar(36) DEFAULT NULL, # delivery code (link to delivery table) delidesp varchar(128) DEFAULT NULL, # delivery description delimemo varchar(128) DEFAULT NULL, # delivery memo srcip varchar(64) DEFAULT NULL, # iserver field sender varchar(36) DEFAULT NULL, # iserver field netid varchar(36) DEFAULT NULL, # iserver field progid varchar(36) DEFAULT NULL, # iserver field updtime datetime DEFAULT NULL, # record updated time rectime datetime DEFAULT NULL, # record created time pushflag smallint DEFAULT '1', PRIMARY KEY (id), #
KEY userid (userid), #
KEY prodcode (prodcode), #
KEY delicode (delicode) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

tables,views not used on iserver (aslo merged into wlsp.sys.iserver.sql)

DROP TABLE IF EXISTS authblkmac; DROP TABLE IF EXISTS authnlist; DROP TABLE IF EXISTS actvrf; DROP TABLE IF EXISTS pushrurl; DROP TABLE IF EXISTS wlpkt; DROP TABLE IF EXISTS smspool; DROP TABLE IF EXISTS userpoints; DROP TABLE IF EXISTS userlog; DROP TABLE IF EXISTS userinfochk; DROP VIEW IF EXISTS viewaction; DROP VIEW IF EXISTS viewstation;

caiwang commented 9 years ago

Comment by unissoft-bj Thursday Jan 01, 2015 at 02:23 GMT

mysql> ALTER TABLE authsms ADD COLUMN msgid varchar(64) DEFAULT NULL; # new column Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE authsms MODIFY COLUMN prefix varchar(64) DEFAULT ''; # legth 60 to 64 Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE authsms MODIFY COLUMN sms varchar(128) DEFAULT NULL; # legth 10 to 128 Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE authsms MODIFY COLUMN postfix varchar(64) DEFAULT ''; # legth 30 to 64 Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0

mysql> mysql> ALTER TABLE useraccounts ADD KEY usercode (usercode); # new index Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0

mysql> mysql> ALTER TABLE usermacs ADD KEY mac (mac); # new index Query OK, 25 rows affected (0.03 sec) Records: 25 Duplicates: 0 Warnings: 0

mysql> mysql> ALTER TABLE useractive MODIFY COLUMN updby varchar(128) DEFAULT NULL; # legth 30 to 128 Query OK, 808 rows affected (0.02 sec) Records: 808 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE useractive MODIFY COLUMN insby varchar(128) DEFAULT NULL; # legth 30 to 128 Query OK, 808 rows affected (0.02 sec) Records: 808 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE useractive ADD KEY mac (mac); # new index Query OK, 808 rows affected (0.15 sec) Records: 808 Duplicates: 0 Warnings: 0

mysql> DROP TABLE IF EXISTS smsrcv; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE smsrcv ( -> id int NOT NULL AUTO_INCREMENT, -> msg varchar(128) DEFAULT NULL, -> phone varchar(32) DEFAULT NULL, -> msgtime datetime DEFAULT NULL, -> mlocation varchar(32) DEFAULT NULL, -> mfolder varchar(16) DEFAULT NULL, -> rectime datetime DEFAULT NULL, -> PRIMARY KEY (id) -> ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.02 sec)

mysql> DROP TABLE IF EXISTS prodorder;#products to be deliveried to user Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE prodorder (# -> id int NOT NULL AUTO_INCREMENT,# -> userid varchar(36) DEFAULT NULL,# -> username varchar(36) DEFAULT NULL,#username -> srcid int DEFAULT NULL,#iserver field -> prodcode varchar(10) DEFAULT NULL,#product code -> prodname varchar(36) DEFAULT NULL,#product name -> prodtype varchar(36) DEFAULT NULL,#product type -> prodspec varchar(36) DEFAULT NULL,#product specification -> proddesp varchar(36) DEFAULT NULL,#product description -> quan decimal(10,2) DEFAULT NULL,#quantity of product -> unit varchar(36) DEFAULT NULL,#unit of product -> pkg varchar(36) DEFAULT NULL,#package of product -> recipaddr varchar(128) DEFAULT NULL,#recipient address -> recipname varchar(36) DEFAULT NULL,#recipient name -> recipphone1 varchar(30) DEFAULT NULL,#recipient phone number #1 -> recipphone2 varchar(30) DEFAULT NULL,#recipient phone number #2 -> recipemail varchar(64) DEFAULT NULL,#recipient phone email -> assignto varchar(64) DEFAULT 'iserver',#processor of the order: iserver/local -> delicode varchar(36) DEFAULT NULL,#delivery code (link to delivery table) -> delidesp varchar(128) DEFAULT NULL,#delivery description -> delimemo varchar(128) DEFAULT NULL,#delivery memo -> srcip varchar(64) DEFAULT NULL,#iserver field -> sender varchar(36) DEFAULT NULL, #iserver field -> netid varchar(36) DEFAULT NULL, #iserver field -> progid varchar(36) DEFAULT NULL, #iserver field -> updtime datetime DEFAULT NULL,#record updated time -> rectime datetime DEFAULT NULL,#record created time -> pushflag smallint DEFAULT '1', -> PRIMARY KEY (id),# -> KEY userid (userid),# -> KEY prodcode (prodcode),# -> KEY delicode (delicode) -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS authblkmac; Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE IF EXISTS authnlist; Query OK, 0 rows affected (0.02 sec)

mysql> DROP TABLE IF EXISTS actvrf; Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS pushrurl; Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS wlpkt; Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS smspool; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS userpoints; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS userlog; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS userinfochk; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP VIEW IF EXISTS viewaction; Query OK, 0 rows affected (0.00 sec)

mysql> DROP VIEW IF EXISTS viewstation; Query OK, 0 rows affected (0.00 sec)