git:master: utils/kamctl: rebuilt .sql files to reflect changes to pua, rls , and xcap tables
Module: sip-router Branch: master Commit: 6853a8c061df2456291d825415769625f741daf6 URL: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commit;h=6853a8c0...
Author: Peter Dunkley peter.dunkley@crocodile-rcs.com Committer: Peter Dunkley peter.dunkley@crocodile-rcs.com Date: Mon Mar 19 16:12:02 2012 +0000
utils/kamctl: rebuilt .sql files to reflect changes to pua, rls, and xcap tables
---
utils/kamctl/db_sqlite/presence-create.sql | 22 +++++++++++++--------- utils/kamctl/db_sqlite/rls-create.sql | 6 ++++++ utils/kamctl/mysql/presence-create.sql | 22 +++++++++++++--------- utils/kamctl/mysql/rls-create.sql | 6 ++++++ utils/kamctl/oracle/presence-create.sql | 22 +++++++++++++--------- utils/kamctl/oracle/rls-create.sql | 6 ++++++ utils/kamctl/postgres/presence-create.sql | 22 +++++++++++++--------- utils/kamctl/postgres/rls-create.sql | 6 ++++++ 8 files changed, 76 insertions(+), 36 deletions(-)
Diff: http://git.sip-router.org/cgi-bin/gitweb.cgi/sip-router/?a=commitdiff;h=6853...
Peter Dunkley writes:
utils/kamctl/mysql/presence-create.sql | 22 +++++++++++++---------
peter,
something went wrong with this commit. at least mysql version of resulting xcap table is now incorrect:
mysql> CREATE TABLE xcap ( -> id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, -> username VARCHAR(64) NOT NULL, -> domain VARCHAR(64) NOT NULL, -> doc MEDIUMBLOB NOT NULL, -> doc_type INT(11) NOT NULL, -> etag VARCHAR(64) NOT NULL, -> source INT(11) NOT NULL, -> doc_uri VARCHAR(255) PRIMARY KEY NOT NULL, -> port INT(11) NOT NULL, -> CONSTRAINT account_doc_type_idx UNIQUE (username, domain, doc_type), -> CONSTRAINT account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri), -> CONSTRAINT account_doc_uri_idx UNIQUE (username, domain, doc_uri) -> ) ENGINE=MyISAM; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
problem is that id field is auto increment field, but does not anymore have a key.
-- juha
Hi,
That's interesting.
I am using PostgreSQL here, and it seems (with PostgreSQL) at least, an auto-increment field doesn't have to be a primary key - which is good as the doc_uri really should be the primary key on this table.
Is there anyone who is more familiar with the Kamailio DB schema and MySQL who can tell me if there is a way to make ID a non-primary key, and whether or not this will allow ID to be auto-increment?
Thanks,
Peter
On Tue, 2012-03-20 at 09:03 +0200, Juha Heinanen wrote:
Peter Dunkley writes:
utils/kamctl/mysql/presence-create.sql | 22 +++++++++++++---------
peter,
something went wrong with this commit. at least mysql version of resulting xcap table is now incorrect:
mysql> CREATE TABLE xcap ( -> id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, -> username VARCHAR(64) NOT NULL, -> domain VARCHAR(64) NOT NULL, -> doc MEDIUMBLOB NOT NULL, -> doc_type INT(11) NOT NULL, -> etag VARCHAR(64) NOT NULL, -> source INT(11) NOT NULL, -> doc_uri VARCHAR(255) PRIMARY KEY NOT NULL, -> port INT(11) NOT NULL, -> CONSTRAINT account_doc_type_idx UNIQUE (username, domain, doc_type), -> CONSTRAINT account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri), -> CONSTRAINT account_doc_uri_idx UNIQUE (username, domain, doc_uri) -> ) ENGINE=MyISAM; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
problem is that id field is auto increment field, but does not anymore have a key.
-- juha
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
Hello,
cannot doc_uri be just unique key? It is how we have the db schema, id is primary key and the other constraints are unique keys -- it worked very well so far with all db engines.
Cheers, Daniel
On 3/20/12 11:09 AM, Peter Dunkley wrote:
Hi,
That's interesting.
I am using PostgreSQL here, and it seems (with PostgreSQL) at least, an auto-increment field doesn't have to be a primary key - which is good as the doc_uri really should be the primary key on this table.
Is there anyone who is more familiar with the Kamailio DB schema and MySQL who can tell me if there is a way to make ID a non-primary key, and whether or not this will allow ID to be auto-increment?
Thanks,
Peter
On Tue, 2012-03-20 at 09:03 +0200, Juha Heinanen wrote:
Peter Dunkley writes:
utils/kamctl/mysql/presence-create.sql | 22 +++++++++++++---------
peter,
something went wrong with this commit. at least mysql version of resulting xcap table is now incorrect:
mysql> CREATE TABLE xcap ( -> id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, -> username VARCHAR(64) NOT NULL, -> domain VARCHAR(64) NOT NULL, -> doc MEDIUMBLOB NOT NULL, -> doc_type INT(11) NOT NULL, -> etag VARCHAR(64) NOT NULL, -> source INT(11) NOT NULL, -> doc_uri VARCHAR(255) PRIMARY KEY NOT NULL, -> port INT(11) NOT NULL, -> CONSTRAINT account_doc_type_idx UNIQUE (username, domain, doc_type), -> CONSTRAINT account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri), -> CONSTRAINT account_doc_uri_idx UNIQUE (username, domain, doc_uri) -> ) ENGINE=MyISAM; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
problem is that id field is auto increment field, but does not anymore have a key.
-- juha
sr-dev mailing list sr-dev@lists.sip-router.org mailto:sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
-- Peter Dunkley Technical Director Crocodile RCS Ltd
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
Hi Juha,
I have pushed a fix for this into git master. Please let me know if you have any further trouble.
Thanks,
Peter
On Tue, 2012-03-20 at 09:03 +0200, Juha Heinanen wrote:
Peter Dunkley writes:
utils/kamctl/mysql/presence-create.sql | 22 +++++++++++++---------
peter,
something went wrong with this commit. at least mysql version of resulting xcap table is now incorrect:
mysql> CREATE TABLE xcap ( -> id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, -> username VARCHAR(64) NOT NULL, -> domain VARCHAR(64) NOT NULL, -> doc MEDIUMBLOB NOT NULL, -> doc_type INT(11) NOT NULL, -> etag VARCHAR(64) NOT NULL, -> source INT(11) NOT NULL, -> doc_uri VARCHAR(255) PRIMARY KEY NOT NULL, -> port INT(11) NOT NULL, -> CONSTRAINT account_doc_type_idx UNIQUE (username, domain, doc_type), -> CONSTRAINT account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri), -> CONSTRAINT account_doc_uri_idx UNIQUE (username, domain, doc_uri) -> ) ENGINE=MyISAM; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
problem is that id field is auto increment field, but does not anymore have a key.
-- juha
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
Hello,
I think you forgot to add column reference in the unique key:
+ CONSTRAINT xcap_doc_uri_idx UNIQUE ()
Cheers, Daniel
On 3/20/12 5:48 PM, Peter Dunkley wrote:
Hi Juha,
I have pushed a fix for this into git master. Please let me know if you have any further trouble.
Thanks,
Peter
On Tue, 2012-03-20 at 09:03 +0200, Juha Heinanen wrote:
Peter Dunkley writes:
utils/kamctl/mysql/presence-create.sql | 22 +++++++++++++---------
peter,
something went wrong with this commit. at least mysql version of resulting xcap table is now incorrect:
mysql> CREATE TABLE xcap ( -> id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, -> username VARCHAR(64) NOT NULL, -> domain VARCHAR(64) NOT NULL, -> doc MEDIUMBLOB NOT NULL, -> doc_type INT(11) NOT NULL, -> etag VARCHAR(64) NOT NULL, -> source INT(11) NOT NULL, -> doc_uri VARCHAR(255) PRIMARY KEY NOT NULL, -> port INT(11) NOT NULL, -> CONSTRAINT account_doc_type_idx UNIQUE (username, domain, doc_type), -> CONSTRAINT account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri), -> CONSTRAINT account_doc_uri_idx UNIQUE (username, domain, doc_uri) -> ) ENGINE=MyISAM; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
problem is that id field is auto increment field, but does not anymore have a key.
-- juha
sr-dev mailing list sr-dev@lists.sip-router.org mailto:sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
-- Peter Dunkley Technical Director Crocodile RCS Ltd
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
I noticed it immediately after I did the push. Fixed it this time.
Peter
On Tue, 2012-03-20 at 17:51 +0100, Daniel-Constantin Mierla wrote:
Hello,
I think you forgot to add column reference in the unique key:
- CONSTRAINT xcap_doc_uri_idx UNIQUE ()
Cheers, Daniel
On 3/20/12 5:48 PM, Peter Dunkley wrote:
Hi Juha,
I have pushed a fix for this into git master. Please let me know if you have any further trouble.
Thanks,
Peter
On Tue, 2012-03-20 at 09:03 +0200, Juha Heinanen wrote:
Peter Dunkley writes:
utils/kamctl/mysql/presence-create.sql | 22 +++++++++++++---------
peter,
something went wrong with this commit. at least mysql version of resulting xcap table is now incorrect:
mysql> CREATE TABLE xcap ( -> id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL, -> username VARCHAR(64) NOT NULL, -> domain VARCHAR(64) NOT NULL, -> doc MEDIUMBLOB NOT NULL, -> doc_type INT(11) NOT NULL, -> etag VARCHAR(64) NOT NULL, -> source INT(11) NOT NULL, -> doc_uri VARCHAR(255) PRIMARY KEY NOT NULL, -> port INT(11) NOT NULL, -> CONSTRAINT account_doc_type_idx UNIQUE (username, domain, doc_type), -> CONSTRAINT account_doc_type_uri_idx UNIQUE (username, domain, doc_type, doc_uri), -> CONSTRAINT account_doc_uri_idx UNIQUE (username, domain, doc_uri) -> ) ENGINE=MyISAM; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
problem is that id field is auto increment field, but does not anymore have a key.
-- juha
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
-- Peter Dunkley Technical Director Crocodile RCS Ltd
sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
-- Daniel-Constantin Mierla Kamailio Advanced Training, April 23-26, 2012, Berlin, Germany http://www.asipto.com/index.php/kamailio-advanced-training/
-
Daniel-Constantin Mierla -
Juha Heinanen -
Peter Dunkley