Hi:
I'm trying to configure a Kamailio 3.2 server as a Redirect Server.
A softswitch we own will send a SIP INVITE for some calls like this "INVITE sip:102@sipwise.telecentro.net.ar SIP/2.0". Once detected the B number "102" (in this example), the kamailio server will search in a sqlite3 database the transltated number the call should be directed. And then the server will send a 300 Redirect with the new request uri. Like this "SIP/2.0 300 Redirect /.../ Contact: sip:63793266@voip.telecentro.net.ar". And the softswitch will let the call go to the right number. The translation desition is made based on the geographic area where the A party is located.
If the translated number is like 63793266 or 1557311721 the system is working ok. So the .cfg file seems to be OK.
But if the translated number is like 8003330303 the variable holding the SQL return is set to "-486604289".
I think this is related to data types. In the logs I can see that kamailio is defaulting datatypes to INT (I've tested varchar, int, and bigint in sqlite3), but I don't know how large could an integer be in Kamailio. According to sqlite3 documentation the number 8003330303 is not long enough and in fact the number is in the database.
Has anybody seen something like this? Can anyone help?
I can provide the hole script and database if this helps.
Regards, Sebastian Ferguson
On Wed, 16 May 2012 18:06:08 -0300 Sebastian Ferguson sebastian.ferguson@gmail.com wrote:
I think this is related to data types. In the logs I can see that kamailio is defaulting datatypes to INT (I've tested varchar, int, and bigint in sqlite3), but I don't know how large could an integer be in Kamailio. According to sqlite3 documentation the number 8003330303 is not long enough and in fact the number is in the database.
Yes. Sounds definitely like it's treated as INT instead of string or BIGINT. Since it's address part, it should be string (as there can be letters too).
SQLite is a little bit unusual on how the typing system works. It does not really care about how you define your tables, it just stores the data there as given. That is, even if your table is specified as INT, it can still have STRING in it.
I wrote the SQLite driver to consult the table declaration to see what there should be. So the driver tries to do the right thing, but apparently can go wrong in certain circumstances.
However, I think either your table declaration is wrong. If the column is VARCHAR or TEXT it should work. Also, some of the tests are case-sensitive (strstr function used). Could you try if using uppercase VARCHAR or TEXT fixes it? If it does, I need to fix decltype_to_dbtype() to be case-insensitive.
If possible, can I see the related table schema?
- Timo
Hi Timo:
I've tryied with varchar(20) (lowercase) before I sent the original e-mail and it doesn't work. And I have tryied storing data as a number and as a stirng (I know that sqlite has not stict type definitios).
After your e-mail I've built the database again like this (all uppercase as you recommend (and as it should be ;-)):
sqlite> CREATE TABLE traducciones('zona' VARCHAR(25), 'e100' VARCHAR(20), 'e101' VARCHAR(20), 'e102' VARCHAR(20)); sqlite> INSERT INTO traducciones VALUES("caballito","1557311721","1557311721","08103330303"); sqlite> INSERT INTO traducciones VALUES("flores","63793266","08103330303","1557311721"); sqlite> select * from traducciones; caballito|1557311721|1557311721|08103330303 flores|63793266|08103330303|1557311721
AND NOW IT's WORKING!!!!!
The production table will have all emergency numbers in Argentina (10x, 13x, 911).
Best regards and thank you very much!!! Sebastián Ferguson
On Thu, May 17, 2012 at 1:45 PM, Timo Teras timo.teras@iki.fi wrote:
On Wed, 16 May 2012 18:06:08 -0300 Sebastian Ferguson sebastian.ferguson@gmail.com wrote:
I think this is related to data types. In the logs I can see that kamailio is defaulting datatypes to INT (I've tested varchar, int, and bigint in sqlite3), but I don't know how large could an integer be in Kamailio. According to sqlite3 documentation the number 8003330303 is not long enough and in fact the number is in the database.
Yes. Sounds definitely like it's treated as INT instead of string or BIGINT. Since it's address part, it should be string (as there can be letters too).
SQLite is a little bit unusual on how the typing system works. It does not really care about how you define your tables, it just stores the data there as given. That is, even if your table is specified as INT, it can still have STRING in it.
I wrote the SQLite driver to consult the table declaration to see what there should be. So the driver tries to do the right thing, but apparently can go wrong in certain circumstances.
However, I think either your table declaration is wrong. If the column is VARCHAR or TEXT it should work. Also, some of the tests are case-sensitive (strstr function used). Could you try if using uppercase VARCHAR or TEXT fixes it? If it does, I need to fix decltype_to_dbtype() to be case-insensitive.
If possible, can I see the related table schema?
- Timo
On Thu, 17 May 2012 15:02:30 -0300 Sebastian Ferguson sebastian.ferguson@gmail.com wrote:
I've tryied with varchar(20) (lowercase) before I sent the original e-mail and it doesn't work. And I have tryied storing data as a number and as a stirng (I know that sqlite has not stict type definitios).
After your e-mail I've built the database again like this (all uppercase as you recommend (and as it should be ;-)):
sqlite> CREATE TABLE traducciones('zona' VARCHAR(25), 'e100' sqlite> VARCHAR(20), 'e101' VARCHAR(20), 'e102' VARCHAR(20)); sqlite> INSERT INTO traducciones VALUES("caballito","1557311721","1557311721","08103330303"); sqlite> INSERT INTO traducciones VALUES("flores","63793266","08103330303","1557311721"); sqlite> select * from traducciones; caballito|1557311721|1557311721|08103330303 flores|63793266|08103330303|1557311721
AND NOW IT's WORKING!!!!!
Ok. Thanks for the confirmation. I'll fix this sometime this or next week to work case-insensitively.
The production table will have all emergency numbers in Argentina (10x, 13x, 911).
Very nice :)
-Timo
Search for keywords case insensitively instead of using strstr(). SQlite stores decltype as-is, and does the affinity type testing case insensitively too. --- modules_k/db_sqlite/dbase.c | 53 +++++++++++++++++++++++++++++-------------- 1 file changed, 36 insertions(+), 17 deletions(-)
I'm testing currently this patch. Unless something comes up, I will push this shortly.
diff --git a/modules_k/db_sqlite/dbase.c b/modules_k/db_sqlite/dbase.c index 67db13b..f69192a 100644 --- a/modules_k/db_sqlite/dbase.c +++ b/modules_k/db_sqlite/dbase.c @@ -237,28 +237,47 @@ static int db_sqlite_submit_query(const db1_con_t* _h, const str* _s) return 0; }
+#define H3(a,b,c) ((a<<16) + (b<<8) + c) +#define H4(a,b,c,d) ((a<<24) + (b<<16) + (c<<8) + d) + static int decltype_to_dbtype(const char *decltype) { /* SQlite3 has dynamic typing. It does not store the actual * exact type, instead it uses 'affinity' depending on the * value. We have to go through the declaration types to see - * what to return. */ - if (strstr(decltype, "INT") != NULL || - strncasecmp(decltype, "SERIAL", 6) == 0) - return DB1_INT; - if (strstr(decltype, "CHAR") != NULL) - return DB1_STRING; - if (strstr(decltype, "TEXT") != NULL) - return DB1_STR; - if (strstr(decltype, "REAL") != NULL || - strstr(decltype, "FLOA") != NULL || - strstr(decltype, "DOUB") != NULL) - return DB1_DOUBLE; - if (strstr(decltype, "BLOB") != NULL) - return DB1_BLOB; - if (strncasecmp(decltype, "TIME", 4) == 0 || - strncasecmp(decltype, "DATE", 4) == 0) - return DB1_DATETIME; + * what to return. This differs from SQlite affinity type, as + * we want to match with srdb type. */ + + uint32_t h = 0; + + for (; *decltype; decltype++) { + h <<= 8; + h += toupper(*decltype); + + switch (h & 0x00ffffff) { + case H3('I','N','T'): + return DB1_INT; + } + + switch (h) { + case H4('S','E','R','I'): /* SERIAL */ + return DB1_INT; + case H4('C','H','A','R'): + return DB1_STRING; + case H4('T','E','X','T'): + return DB1_STR; + case H4('R','E','A','L'): + case H4('F','L','O','A'): + case H4('D','O','U','B'): + return DB1_DOUBLE; + case H4('B','L','O','B'): + case H4('B','Y','T','E'): + return DB1_BLOB; + case H4('T','I','M','E'): + case H4('D','A','T','E'): + return DB1_DATETIME; + } + }
LM_ERR("sqlite decltype '%s' not recognized, defaulting to int", decltype);
Hello,
On 5/19/12 10:39 PM, Timo Teräs wrote:
Search for keywords case insensitively instead of using strstr(). SQlite stores decltype as-is, and does the affinity type testing case insensitively too.
Perhaps you should push it asap, so it gets some testing before 3.3.0 is released.
Thanks, Daniel
modules_k/db_sqlite/dbase.c | 53 +++++++++++++++++++++++++++++-------------- 1 file changed, 36 insertions(+), 17 deletions(-)
I'm testing currently this patch. Unless something comes up, I will push this shortly.
diff --git a/modules_k/db_sqlite/dbase.c b/modules_k/db_sqlite/dbase.c index 67db13b..f69192a 100644 --- a/modules_k/db_sqlite/dbase.c +++ b/modules_k/db_sqlite/dbase.c @@ -237,28 +237,47 @@ static int db_sqlite_submit_query(const db1_con_t* _h, const str* _s) return 0; }
+#define H3(a,b,c) ((a<<16) + (b<<8) + c) +#define H4(a,b,c,d) ((a<<24) + (b<<16) + (c<<8) + d)
- static int decltype_to_dbtype(const char *decltype) { /* SQlite3 has dynamic typing. It does not store the actual
- exact type, instead it uses 'affinity' depending on the
- value. We have to go through the declaration types to see
* what to return. */- if (strstr(decltype, "INT") != NULL ||
strncasecmp(decltype, "SERIAL", 6) == 0)return DB1_INT;- if (strstr(decltype, "CHAR") != NULL)
return DB1_STRING;- if (strstr(decltype, "TEXT") != NULL)
return DB1_STR;- if (strstr(decltype, "REAL") != NULL ||
strstr(decltype, "FLOA") != NULL ||strstr(decltype, "DOUB") != NULL)return DB1_DOUBLE;- if (strstr(decltype, "BLOB") != NULL)
return DB1_BLOB;- if (strncasecmp(decltype, "TIME", 4) == 0 ||
strncasecmp(decltype, "DATE", 4) == 0)return DB1_DATETIME;
* what to return. This differs from SQlite affinity type, as* we want to match with srdb type. */uint32_t h = 0;
for (; *decltype; decltype++) {
h <<= 8;h += toupper(*decltype);switch (h & 0x00ffffff) {case H3('I','N','T'):return DB1_INT;}switch (h) {case H4('S','E','R','I'): /* SERIAL */return DB1_INT;case H4('C','H','A','R'):return DB1_STRING;case H4('T','E','X','T'):return DB1_STR;case H4('R','E','A','L'):case H4('F','L','O','A'):case H4('D','O','U','B'):return DB1_DOUBLE;case H4('B','L','O','B'):case H4('B','Y','T','E'):return DB1_BLOB;case H4('T','I','M','E'):case H4('D','A','T','E'):return DB1_DATETIME;}}
LM_ERR("sqlite decltype '%s' not recognized, defaulting to int", decltype);
On Tue, 22 May 2012 10:46:34 +0200 Daniel-Constantin Mierla miconda@gmail.com wrote:
Hello,
On 5/19/12 10:39 PM, Timo Teräs wrote:
Search for keywords case insensitively instead of using strstr(). SQlite stores decltype as-is, and does the affinity type testing case insensitively too.
Perhaps you should push it asap, so it gets some testing before 3.3.0 is released.
I have few minor fixes. Will push within an hour.
-Timo
On Tue, 22 May 2012 13:11:04 +0300 Timo Teras timo.teras@iki.fi wrote:
On Tue, 22 May 2012 10:46:34 +0200 Daniel-Constantin Mierla miconda@gmail.com wrote:
Hello,
On 5/19/12 10:39 PM, Timo Teräs wrote:
Search for keywords case insensitively instead of using strstr(). SQlite stores decltype as-is, and does the affinity type testing case insensitively too.
Perhaps you should push it asap, so it gets some testing before 3.3.0 is released.
I have few minor fixes. Will push within an hour.
Ok. Pushed the immediate fix.
I would also like to push the following change:
--- a/doc/stylesheets/dbschema_k/xsl/db_sqlite.xsl +++ b/doc/stylesheets/dbschema_k/xsl/db_sqlite.xsl @@ -87,7 +87,7 @@ </xsl:when> <xsl:when test="$type='binary' or $type='largebinary'"> - xsl:textBYTEA</xsl:text> + xsl:textBLOB</xsl:text> <xsl:call-template name="column.size"/> <xsl:call-template name="column.trailing"/> </xsl:when>
This ensures that the binary stuff is put properly in to the database. We really need to change the schema mapping, as otherwise the column gets wrong affinity in sqlite.
This probably means binary breakage. And after "make dbschema" I got some tables versions changed, even though it's only the sqlite that changed.
Could someone do this change, and regenerate the necessary files?
Sorry for the inconvenience.
Thanks, Timo
Hello,
On 5/22/12 12:33 PM, Timo Teras wrote:
On Tue, 22 May 2012 13:11:04 +0300 Timo Teras timo.teras@iki.fi wrote:
On Tue, 22 May 2012 10:46:34 +0200 Daniel-Constantin Mierla miconda@gmail.com wrote:
Hello,
On 5/19/12 10:39 PM, Timo Teräs wrote:
Search for keywords case insensitively instead of using strstr(). SQlite stores decltype as-is, and does the affinity type testing case insensitively too.
Perhaps you should push it asap, so it gets some testing before 3.3.0 is released.
I have few minor fixes. Will push within an hour.
Ok. Pushed the immediate fix.
I would also like to push the following change:
--- a/doc/stylesheets/dbschema_k/xsl/db_sqlite.xsl +++ b/doc/stylesheets/dbschema_k/xsl/db_sqlite.xsl @@ -87,7 +87,7 @@ </xsl:when> <xsl:when test="$type='binary' or $type='largebinary'">
<xsl:text>BYTEA</xsl:text>
<xsl:text>BLOB</xsl:text> <xsl:call-template name="column.size"/> <xsl:call-template name="column.trailing"/> </xsl:when>This ensures that the binary stuff is put properly in to the database. We really need to change the schema mapping, as otherwise the column gets wrong affinity in sqlite.
This probably means binary breakage. And after "make dbschema" I got some tables versions changed, even though it's only the sqlite that changed.
Could someone do this change, and regenerate the necessary files?
I regenerated the db creation scripts with existing schema -- new lcr_rule table version was not taken in consideration, indeed.
Now you should be able to apply your patch and regenerate dbschema creation scripts with no other interferences. I can do it if it is not convenient for you.
Cheers, Daniel
-
Daniel-Constantin Mierla -
Sebastian Ferguson -
Timo Teras