[Devel] Trying Oracle module -- complains "Invalid table version"
Daniel-Constantin Mierla
daniel at voice-system.ro
Wed Oct 12 09:34:57 CEST 2005
On 10/11/05 21:33, Juan Carlos Castro y Castro wrote:
> Any clues? I'm annexing the script I used to create the tables. I've
> downloaded the module from
> http://cvs.berlios.de/cgi-bin/viewcvs.cgi/ser/experimental/oracle/ and
> added it to OpenSER 0.9.5. The schema was adapted from OpenSER's
> mysqldb.sh -- notice how I had to let go of lots of "NOT NULL" because
> Oracle won't accept empty-string values in NOT NULL fields.
>
> Oct 11 15:25:22 localhost openser[6440]: Maxfwd module- initializing
> Oct 11 15:25:22 localhost openser[6440]: WARNING! This module is
> experimental and may crash SER or create unexpected results. You use
> the module at your own risk. Please submit bugs at
> http://bugs.sip-router.org/
> Oct 11 15:25:22 localhost openser[6440]: auth_db:str_fixup: Invalid
> table version (use ser_mysql.sh reinstall)
> Oct 11 15:25:22 localhost openser[6440]: ERROR: fix_expr : fix_actions
> error
It seems that you use a database schema that it is not suitable for the
version of openser you have. Some modules check for the version of the
table they use (that because different version of openser use different
table structures). What you posted below it is different that what
should be there for OpenSER CVS version, please see the file mysqldb.sh
which creates the table 'version' and populate it with the proper values.
Online from cvs:
http://cvs.sourceforge.net/viewcvs.py/openser/sip-server/scripts/mysqldb.sh?rev=1.8&view=markup
One question, out of topic a bit, does ORACLE have a REPLACE sql command
with same functionality as in MySQL?
Cheers,
Daniel
> ------------------------------------------------------------------------
>
> CREATE TABLE version (
> table_name varchar(64) NOT NULL,
> table_version smallint DEFAULT '0' NOT NULL
> );
>
> INSERT INTO version VALUES ( 'subscriber', '5');
> INSERT INTO version VALUES ( 'reserved', '1');
> INSERT INTO version VALUES ( 'phonebook', '1');
> INSERT INTO version VALUES ( 'pending', '4');
> INSERT INTO version VALUES ( 'missed_calls', '2');
> INSERT INTO version VALUES ( 'location', '6');
> INSERT INTO version VALUES ( 'grp', '2');
> INSERT INTO version VALUES ( 'event', '1');
> INSERT INTO version VALUES ( 'aliases', '6');
> INSERT INTO version VALUES ( 'active_sessions', '1');
> INSERT INTO version VALUES ( 'acc', '2');
> INSERT INTO version VALUES ( 'config', '1');
> INSERT INTO version VALUES ( 'silo', '3');
> INSERT INTO version VALUES ( 'realm', '1');
> INSERT INTO version VALUES ( 'domain', '1');
> INSERT INTO version VALUES ( 'uri', '1');
> INSERT INTO version VALUES ( 'server_monitoring', '1');
> INSERT INTO version VALUES ( 'server_monitoring_agg', '1');
> INSERT INTO version VALUES ( 'trusted', '1');
> INSERT INTO version VALUES ( 'usr_preferences', '1');
> INSERT INTO version VALUES ( 'preferences_types', '1');
> INSERT INTO version VALUES ( 'admin_privileges', '1');
> INSERT INTO version VALUES ( 'calls_forwarding', '1');
> INSERT INTO version VALUES ( 'speed_dial', '1');
> INSERT INTO version VALUES ( 'dbaliases', '1');
>
> CREATE TABLE acc (
> sip_from varchar(128) default '' /*NOT NULL*/,
> sip_to varchar(128) default '' /*NOT NULL*/,
> sip_status varchar(128) default '' /*NOT NULL*/,
> sip_method varchar(16) default '' /*NOT NULL*/,
> i_uri varchar(128) default '' /*NOT NULL*/,
> o_uri varchar(128) default '' /*NOT NULL*/,
> from_uri varchar(128) default '' /*NOT NULL*/,
> to_uri varchar(128) default '' /*NOT NULL*/,
> sip_callid varchar(128) default '' /*NOT NULL*/,
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> FROMTag varchar(128) default '' /*NOT NULL*/,
> totag varchar(128) default '' /*NOT NULL*/,
> time date default TIMESTAMP '0000-01-01 00:00:00' NOT NULL,
> timestamp timestamp NOT NULL,
> src varchar(128) default NULL,
> dst varchar(128) default NULL
> );
> CREATE INDEX acc_user ON acc (username, domain);
> CREATE INDEX sip_callid ON acc (sip_callid);
>
> CREATE TABLE active_sessions (
> sid varchar(32) default '' /*NOT NULL*/,
> name varchar(32) default '' /*NOT NULL*/,
> val CLOB,
> changed varchar(14) default '' /*NOT NULL*/,
> PRIMARY KEY (name,sid)
> );
> CREATE INDEX changed ON active_sessions (changed);
>
> CREATE TABLE aliases (
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> contact varchar(255) default '' /*NOT NULL*/,
> received varchar(255) default NULL,
> expires date default TIMESTAMP '2020-05-28 21:32:15' NOT NULL,
> q float default '1.0' NOT NULL,
> callid varchar(255) default 'Default-Call-ID' NOT NULL,
> cseq int default '42' NOT NULL,
> last_modified timestamp NOT NULL,
> flags int default '0' NOT NULL,
> user_agent varchar(50) default '' /*NOT NULL*/,
> socket varchar(128) default NULL,
> PRIMARY KEY(username, domain, contact)
> );
> CREATE INDEX aliases_contact ON aliases (contact);
>
> CREATE TABLE dbaliases (
> alias_username varchar(64) default '' /*NOT NULL*/,
> alias_domain varchar(128) default '' /*NOT NULL*/,
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> PRIMARY KEY (alias_username,alias_domain)
> );
> CREATE INDEX alias_user ON dbaliases (username, domain);
>
> CREATE TABLE grp (
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> grp varchar(50) default '' /*NOT NULL*/,
> last_modified date default TIMESTAMP '0000-01-01 00:00:00' NOT NULL,
> PRIMARY KEY(username, domain, grp)
> );
>
> CREATE TABLE location (
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> contact varchar(255) default '' /*NOT NULL*/,
> received varchar(255) default NULL,
> expires date default TIMESTAMP '2020-05-28 21:32:15' NOT NULL,
> q float default '1.0' NOT NULL,
> callid varchar(255) default 'Default-Call-ID' NOT NULL,
> cseq int default '42' NOT NULL,
> last_modified timestamp NOT NULL,
> flags int default '0' NOT NULL,
> user_agent varchar(50) default '' /*NOT NULL*/,
> socket varchar(128) default NULL,
> PRIMARY KEY(username, domain, contact)
> );
>
> CREATE TABLE missed_calls (
> sip_from varchar(128) default '' /*NOT NULL*/,
> sip_to varchar(128) default '' /*NOT NULL*/,
> sip_status varchar(128) default '' /*NOT NULL*/,
> sip_method varchar(16) default '' /*NOT NULL*/,
> i_uri varchar(128) default '' /*NOT NULL*/,
> o_uri varchar(128) default '' /*NOT NULL*/,
> from_uri varchar(128) default '' /*NOT NULL*/,
> to_uri varchar(128) default '' /*NOT NULL*/,
> sip_callid varchar(128) default '' /*NOT NULL*/,
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> fromtag varchar(128) default '' /*NOT NULL*/,
> totag varchar(128) default '' /*NOT NULL*/,
> time date default TIMESTAMP '0000-01-01 00:00:00' NOT NULL,
> timestamp timestamp NOT NULL,
> src varchar(128) default NULL,
> dst varchar(128) default NULL
> );
> CREATE INDEX mc_user ON missed_calls (username, domain);
>
> CREATE TABLE pending (
> phplib_id varchar(32) default '' /*NOT NULL*/,
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> password varchar(25) default '' /*NOT NULL*/,
> first_name varchar(25) default '' /*NOT NULL*/,
> last_name varchar(45) default '' /*NOT NULL*/,
> phone varchar(15) default '' /*NOT NULL*/,
> email_address varchar(50) default '' /*NOT NULL*/,
> datetime_created date default TIMESTAMP '0000-01-01 00:00:00' NOT NULL,
> datetime_modified date default TIMESTAMP '0000-01-01 00:00:00' NOT NULL,
> confirmation varchar(64) default '' /*NOT NULL*/,
> flag char(1) default 'o' NOT NULL,
> sendnotification varchar(50) default '' /*NOT NULL*/,
> greeting varchar(50) default '' /*NOT NULL*/,
> ha1 varchar(128) default '' /*NOT NULL*/,
> ha1b varchar(128) default '' /*NOT NULL*/,
> allow_find char(1) default '0' NOT NULL,
> timezone varchar(128) default NULL,
> rpid varchar(128) default NULL,
> domn int default NULL,
> uuid varchar(64) default NULL,
> PRIMARY KEY (username, domain)
> );
> CREATE INDEX user_2 ON pending (username);
> CREATE UNIQUE INDEX phplib_id ON pending (phplib_id);
>
> CREATE SEQUENCE phonebook_id_seq;
> CREATE TABLE phonebook (
> id int NOT NULL,
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> fname varchar(32) default '' /*NOT NULL*/,
> lname varchar(32) default '' /*NOT NULL*/,
> sip_uri varchar(128) default '' /*NOT NULL*/,
> PRIMARY KEY (id)
> );
> CREATE TRIGGER phonebook_trigger
> BEFORE INSERT ON phonebook
> FOR EACH ROW
> BEGIN
> SELECT phonebook_id_seq.nextval INTO :new.id FROM DUAL;
> END;
> /
>
> CREATE TABLE subscriber (
> phplib_id varchar(32) default '' /*NOT NULL*/,
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> password varchar(25) default '' /*NOT NULL*/,
> first_name varchar(25) default '' /*NOT NULL*/,
> last_name varchar(45) default '' /*NOT NULL*/,
> phone varchar(15) default '' /*NOT NULL*/,
> email_address varchar(50) default '' /*NOT NULL*/,
> datetime_created date default TIMESTAMP '0000-01-01 00:00:00' NOT NULL,
> datetime_modified date default TIMESTAMP '0000-01-01 00:00:00' NOT NULL,
> confirmation varchar(64) default '' /*NOT NULL*/,
> flag char(1) default 'o' NOT NULL,
> sendnotification varchar(50) default '' /*NOT NULL*/,
> greeting varchar(50) default '' /*NOT NULL*/,
> ha1 varchar(128) default '' /*NOT NULL*/,
> ha1b varchar(128) default '' /*NOT NULL*/,
> allow_find char(1) default '0' NOT NULL,
> timezone varchar(128) default NULL,
> rpid varchar(128) default NULL,
> domn int default NULL,
> uuid varchar(64) default NULL,
> nickname varchar(64) default '' /*NOT NULL*/,
> PRIMARY KEY (username, domain)
> );
> CREATE UNIQUE INDEX phplib_id_a ON subscriber (phplib_id);
> CREATE INDEX user_2a ON subscriber (username);
>
> CREATE SEQUENCE silo_mid_seq;
> CREATE TABLE silo(
> mid INTEGER NOT NULL PRIMARY KEY,
> src_addr VARCHAR(255) DEFAULT '' /*NOT NULL*/,
> dst_addr VARCHAR(255) DEFAULT '' /*NOT NULL*/,
> r_uri VARCHAR(255) DEFAULT '' /*NOT NULL*/,
> username VARCHAR(64) DEFAULT '' /*NOT NULL*/,
> domain VARCHAR(128) DEFAULT '' /*NOT NULL*/,
> inc_time INTEGER DEFAULT 0 NOT NULL,
> exp_time INTEGER DEFAULT 0 NOT NULL,
> ctype VARCHAR(32) DEFAULT 'text/plain' NOT NULL,
> body BLOB DEFAULT '' /*NOT NULL*/
> );
> CREATE TRIGGER silo_trigger
> BEFORE INSERT ON silo
> FOR EACH ROW
> BEGIN
> SELECT silo_mid_seq.nextval INTO :new.mid FROM DUAL;
> END;
> /
>
> CREATE TABLE domain (
> domain varchar(128) default '' /*NOT NULL*/,
> last_modified date default TIMESTAMP '0000-01-01 00:00:00' NOT NULL,
> PRIMARY KEY (domain)
> );
>
> CREATE TABLE uri (
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> uri_user varchar(50) default '' /*NOT NULL*/,
> last_modified date default TIMESTAMP '0000-01-01 00:00:00' NOT NULL,
> PRIMARY KEY (username, domain, uri_user)
> );
>
> CREATE TABLE server_monitoring (
> time date default TIMESTAMP '0000-01-01 00:00:00' NOT NULL,
> id int default '0' NOT NULL,
> param varchar(32) default '' NOT NULL,
> value int default '0' NOT NULL,
> increment_ int default '0' NOT NULL,
> PRIMARY KEY (id,param)
> );
>
> CREATE TABLE usr_preferences (
> uuid varchar(64) default '' NOT NULL,
> username varchar(100) default '0' NOT NULL,
> domain varchar(128) default '' /*NOT NULL*/,
> attribute varchar(32) default '' /*NOT NULL*/,
> type int default '0' NOT NULL,
> value varchar(128) default '' /*NOT NULL*/,
> modified timestamp NOT NULL,
> PRIMARY KEY (attribute,username,domain)
> );
>
> CREATE TABLE trusted (
> src_ip varchar(39) NOT NULL,
> proto varchar(4) NOT NULL,
> from_pattern varchar(64) NOT NULL,
> PRIMARY KEY (src_ip, proto, from_pattern)
> );
>
> CREATE TABLE server_monitoring_agg (
> param varchar(32) default '' /*NOT NULL*/,
> s_value int default '0' NOT NULL,
> s_increment int default '0' NOT NULL,
> last_aggregated_increment int default '0' NOT NULL,
> av float default '0' NOT NULL,
> mv int default '0' NOT NULL,
> ad float default '0' NOT NULL,
> lv int default '0' NOT NULL,
> min_val int default '0' NOT NULL,
> max_val int default '0' NOT NULL,
> min_inc int default '0' NOT NULL,
> max_inc int default '0' NOT NULL,
> lastupdate date default TIMESTAMP '0000-01-01 00:00:00' NOT NULL,
> PRIMARY KEY (param)
> );
>
> CREATE TABLE admin_privileges (
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> priv_name varchar(64) default '' /*NOT NULL*/,
> priv_value varchar(64) default '' /*NOT NULL*/,
> PRIMARY KEY (username,priv_name,priv_value,domain)
> );
>
> CREATE TABLE speed_dial (
> username varchar(64) default '' /*NOT NULL*/,
> domain varchar(128) default '' /*NOT NULL*/,
> sd_username varchar(64) default '' /*NOT NULL*/,
> sd_domain varchar(128) default '' /*NOT NULL*/,
> new_uri varchar(192) default '' /*NOT NULL*/,
> description varchar(64) default '' /*NOT NULL*/,
> PRIMARY KEY (username,domain,sd_domain,sd_username)
> );
>
> INSERT INTO subscriber
> (username, password, first_name, last_name, phone,
> email_address, datetime_created, datetime_modified, confirmation,
> flag, sendnotification, greeting, ha1, domain, ha1b, phplib_id )
> VALUES ( 'admin', 'openserrw', 'Initial', 'Admin', '123',
> 'root at localhost', TIMESTAMP '2002-09-04 19:37:45', TIMESTAMP '0000-01-01 00:00:00',
> '57DaSIPuCm52UNe54LF545750cfdL48OMZfroM53', 'o', '', '',
> '0659615e6a9cab976a072f87577edb42', 'asterisk1', 'b9580dfa4c7f547cbd16b5e5e6ee2427', '58660a1f595bbb62d4c1f1317b386767' );
>
> INSERT INTO admin_privileges (username, domain, priv_name, priv_value)
> VALUES ('admin', 'asterisk1', 'is_admin', '1');
>
> INSERT INTO admin_privileges (username, domain, priv_name, priv_value)
> VALUES ('admin', 'asterisk1', 'change_privileges', '1');
>
> QUIT;
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Devel mailing list
> Devel at openser.org
> http://openser.org/cgi-bin/mailman/listinfo/devel
>
More information about the Devel
mailing list