sqlite -> usrloc tables deleted after 1 minute - sr-users

Carsten Bock

18 Apr 18 Apr

4:56 p.m.

Hi Pedro, did you check your expires date in your preloaded data? Kamailio will delete it, if expires is in the past.... This is most likely your problem. So long, Carsten 2012/4/18 Pedro Antonio Vico Solano <pvsolano(a)amper.es>es>:

...

Hello, I'm having a strange behaviour with sqlite and usrloc tables (location, aliases). I've created the database with kamdbctl and preloaded many tables with my own data. After starting kamailio 3.2.2 (with sqlite engine) location and aliases tables get empty after 1 minute. It does not happen with other tables (e.g. dialplan & subscriber). ul.dump command also shows empty tables. Some configuration: # -- usrloc params -- # use sqlite database for persistent storage, write-thru modparam("usrloc", "db_mode", 1) modparam("usrloc|dialplan", "db_url", "sqlite:////etc/kamailio/db.sqlite") [Attached SQLite schema.] Can anyone help me? Thanks in advance. BR, Pedro ADVERTENCIA Este mensaje y/o sus anexos, pueden contener información personal y confidencial cuyo uso, reproducción o distribución no autorizados están legalmente prohibidos. Por lo tanto, si Vd. no fuera su destinatario y, erróneamente, lo hubiera recibido, le rogamos que informe al remitente y lo borre de inmediato. En cumplimiento de la Ley Orgánica 15/1999, de Protección de Datos de Carácter Personal le informamos de que su dirección de correo electrónico, así como sus datos personales y de empresa pasarán a formar parte de nuestro fichero de Gestión, y serán tratados con la única finalidad de mantenimiento de la relación adquirida con usted. Los datos personales que existen en nuestro poder están protegidos por nuestra Política de Seguridad, y no serán compartidos con ninguna otra empresa. Usted puede ejercitar los derechos de acceso, rectificación, cancelación y oposición dirigiéndose por escrito a la dirección arriba indicada. This e-mail and its attachments may include confidential personal information which may be protected by any legal rules and cannot be used, copied, distributed or disclosed to any person without authorisation. If you are not the intended recipient and have received this e-mail by mistake, please advise the sender and erase it. In compliance with the Spanish Organic Act 15/1999 on Personal Data Protection, we hereby inform you that your email address, as well as your personal and business information, will be included in our Management files and used solely for purposes corresponding to our commercial relationship. All personal data in our possession is protected by our Data Safety Policy and thus shall not be released to any other third party whatsoever. You may exercise your right to access, rectify, cancel and contest by writing to the address provided above. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

-- Carsten Bock CEO (Geschäftsführer) ng-voice GmbH Schomburgstr. 80 D-22767 Hamburg / Germany http://www.ng-voice.com mailto:carsten@ng-voice.com Mobile +49 179 2021244 Office +49 40 34927219 Fax +49 40 34927220 Sitz der Gesellschaft: Hamburg Registergericht: Amtsgericht Hamburg, HRB 120189 Geschäftsführer: Carsten Bock Ust-ID: DE279344284 Hier finden Sie unsere handelsrechtlichen Pflichtangaben: http://www.ng-voice.com/imprint/ -- Meet ng-voice at LinuxTag 2012 in Berlin - May 23rd - 26th, 2012. Save the date!

Reply

Carsten Bock

6:06 p.m.

Hi, SQLLite has no datatype for dates, so "0" is probably not correct... https://www.sqlite.org/datatype3.html The Kamailio-SQLLite Module stores datetime-values as a REAL: case DB1_DATETIME: rc = sqlite3_bind_double(stmt, i, timet_to_sqlite(VAL_TIME(val))); With the following conversion method: static double timet_to_sqlite(time_t t) { return ((((double) t) - 0.5) / 86400.0) + 2440587.5; } So, 0 is definitely incorrect and somewhen back in time. So long, Carsten 2012/4/18 Pedro Antonio Vico Solano <pvsolano(a)amper.es>es>:

...

Hello Carsten, Expire column is 0, what I thought it was "permanent", am I right? I've tried with the default value: '2020-05-28 21:32:15' and happens the same. Maybe is it a problem with the data type of 'expire' column? And I've done another test: if I load tables after Kamailio starts they are not deleted after 1 minute! Strange. BR De: Carsten Bock <carsten(a)ng-voice.com> Para: "SIP Router - Kamailio (OpenSER) and SIP Express Router (SER) - Users Mailing List" <sr-users(a)lists.sip-router.org> Fecha: 18/04/2012 15:57 Asunto: Re: [SR-Users] sqlite -> usrloc tables deleted after 1 minute Enviado por: sr-users-bounces(a)lists.sip-router.org ________________________________ Hi Pedro, did you check your expires date in your preloaded data? Kamailio will delete it, if expires is in the past.... This is most likely your problem. So long, Carsten 2012/4/18 Pedro Antonio Vico Solano <pvsolano(a)amper.es>es>:

Hello, I'm having a strange behaviour with sqlite and usrloc tables (location, aliases). I've created the database with kamdbctl and preloaded many tables with my own data. After starting kamailio 3.2.2 (with sqlite engine) location and aliases tables get empty after 1 minute. It does not happen with other tables (e.g. dialplan & subscriber). ul.dump command also shows empty tables. Some configuration: # -- usrloc params -- # use sqlite database for persistent storage, write-thru modparam("usrloc", "db_mode", 1) modparam("usrloc|dialplan", "db_url", "sqlite:////etc/kamailio/db.sqlite") [Attached SQLite schema.] Can anyone help me? Thanks in advance. BR, Pedro ADVERTENCIA Este mensaje y/o sus anexos, pueden contener información personal y confidencial cuyo uso, reproducción o distribución no autorizados están legalmente prohibidos. Por lo tanto, si Vd. no fuera su destinatario y, erróneamente, lo hubiera recibido, le rogamos que informe al remitente y lo borre de inmediato. En cumplimiento de la Ley Orgánica 15/1999, de Protección de Datos de Carácter Personal le informamos de que su dirección de correo electrónico, así como sus datos personales y de empresa pasarán a formar parte de nuestro fichero de Gestión, y serán tratados con la única finalidad de mantenimiento de la relación adquirida con usted. Los datos personales que existen en nuestro poder están protegidos por nuestra Política de Seguridad, y no serán compartidos con ninguna otra empresa. Usted puede ejercitar los derechos de acceso, rectificación, cancelación y oposición dirigiéndose por escrito a la dirección arriba indicada. This e-mail and its attachments may include confidential personal information which may be protected by any legal rules and cannot be used, copied, distributed or disclosed to any person without authorisation. If you are not the intended recipient and have received this e-mail by mistake, please advise the sender and erase it. In compliance with the Spanish Organic Act 15/1999 on Personal Data Protection, we hereby inform you that your email address, as well as your personal and business information, will be included in our Management files and used solely for purposes corresponding to our commercial relationship. All personal data in our possession is protected by our Data Safety Policy and thus shall not be released to any other third party whatsoever. You may exercise your right to access, rectify, cancel and contest by writing to the address provided above. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

-- Carsten Bock CEO (Geschäftsführer) ng-voice GmbH Schomburgstr. 80 D-22767 Hamburg / Germany http://www.ng-voice.com mailto:carsten@ng-voice.com Mobile +49 179 2021244 Office +49 40 34927219 Fax +49 40 34927220 Sitz der Gesellschaft: Hamburg Registergericht: Amtsgericht Hamburg, HRB 120189 Geschäftsführer: Carsten Bock Ust-ID: DE279344284 Hier finden Sie unsere handelsrechtlichen Pflichtangaben: http://www.ng-voice.com/imprint/ -- Meet ng-voice at LinuxTag 2012 in Berlin - May 23rd - 26th, 2012. Save the date! _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users ADVERTENCIA Este mensaje y/o sus anexos, pueden contener información personal y confidencial cuyo uso, reproducción o distribución no autorizados están legalmente prohibidos. Por lo tanto, si Vd. no fuera su destinatario y, erróneamente, lo hubiera recibido, le rogamos que informe al remitente y lo borre de inmediato. En cumplimiento de la Ley Orgánica 15/1999, de Protección de Datos de Carácter Personal le informamos de que su dirección de correo electrónico, así como sus datos personales y de empresa pasarán a formar parte de nuestro fichero de Gestión, y serán tratados con la única finalidad de mantenimiento de la relación adquirida con usted. Los datos personales que existen en nuestro poder están protegidos por nuestra Política de Seguridad, y no serán compartidos con ninguna otra empresa. Usted puede ejercitar los derechos de acceso, rectificación, cancelación y oposición dirigiéndose por escrito a la dirección arriba indicada. This e-mail and its attachments may include confidential personal information which may be protected by any legal rules and cannot be used, copied, distributed or disclosed to any person without authorisation. If you are not the intended recipient and have received this e-mail by mistake, please advise the sender and erase it. In compliance with the Spanish Organic Act 15/1999 on Personal Data Protection, we hereby inform you that your email address, as well as your personal and business information, will be included in our Management files and used solely for purposes corresponding to our commercial relationship. All personal data in our possession is protected by our Data Safety Policy and thus shall not be released to any other third party whatsoever. You may exercise your right to access, rectify, cancel and contest by writing to the address provided above. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

-- Carsten Bock CEO (Geschäftsführer) ng-voice GmbH Schomburgstr. 80 D-22767 Hamburg / Germany http://www.ng-voice.com mailto:carsten@ng-voice.com Mobile +49 179 2021244 Office +49 40 34927219 Fax +49 40 34927220 Sitz der Gesellschaft: Hamburg Registergericht: Amtsgericht Hamburg, HRB 120189 Geschäftsführer: Carsten Bock Ust-ID: DE279344284 Hier finden Sie unsere handelsrechtlichen Pflichtangaben: http://www.ng-voice.com/imprint/ -- Meet ng-voice at LinuxTag 2012 in Berlin - May 23rd - 26th, 2012. Save the date!

Reply

Carsten Bock

8:04 p.m.

Hi, If you want to change aliases & location during runtime by updating or inserting values to SQLlite, you need to change the "db_mode" of usrloc. Your current setting is "1", which will only read data at startup and save all changes made by Kamailio directly into the database (but it will never read from the database again). If you change your db_mode to 3, you will directly read and write to the database. http://kamailio.org/docs/modules/devel/modules_k/usrloc.html#id2495853 Kind regards, Carsten 2012/4/18 Pedro Antonio Vico Solano <pvsolano(a)amper.es>es>:

...

I've found it!. If you want a permanent alias/location you have to put the following value on 'expire': 2440587.49999421 is it possible to modify aliases/location via updating or inserting values on tables via SQLite? BR, Pedro De: Carsten Bock <carsten(a)ng-voice.com> Para: "SIP Router - Kamailio (OpenSER) and SIP Express Router (SER) - Users Mailing List" <sr-users(a)lists.sip-router.org> Fecha: 18/04/2012 17:07 Asunto: Re: [SR-Users] sqlite -> usrloc tables deleted after 1 minute Enviado por: sr-users-bounces(a)lists.sip-router.org ________________________________ Hi, SQLLite has no datatype for dates, so "0" is probably not correct... https://www.sqlite.org/datatype3.html The Kamailio-SQLLite Module stores datetime-values as a REAL: case DB1_DATETIME: rc = sqlite3_bind_double(stmt, i, timet_to_sqlite(VAL_TIME(val))); With the following conversion method: static double timet_to_sqlite(time_t t) { return ((((double) t) - 0.5) / 86400.0) + 2440587.5; } So, 0 is definitely incorrect and somewhen back in time. So long, Carsten 2012/4/18 Pedro Antonio Vico Solano <pvsolano(a)amper.es>es>:

Hello Carsten, Expire column is 0, what I thought it was "permanent", am I right? I've tried with the default value: '2020-05-28 21:32:15' and happens the same. Maybe is it a problem with the data type of 'expire' column? And I've done another test: if I load tables after Kamailio starts they are not deleted after 1 minute! Strange. BR De: Carsten Bock <carsten(a)ng-voice.com> Para: "SIP Router - Kamailio (OpenSER) and SIP Express Router (SER) - Users Mailing List" <sr-users(a)lists.sip-router.org> Fecha: 18/04/2012 15:57 Asunto: Re: [SR-Users] sqlite -> usrloc tables deleted after 1 minute Enviado por: sr-users-bounces(a)lists.sip-router.org ________________________________ Hi Pedro, did you check your expires date in your preloaded data? Kamailio will delete it, if expires is in the past.... This is most likely your problem. So long, Carsten 2012/4/18 Pedro Antonio Vico Solano <pvsolano(a)amper.es>es>:

Hello, I'm having a strange behaviour with sqlite and usrloc tables (location, aliases). I've created the database with kamdbctl and preloaded many tables with my own data. After starting kamailio 3.2.2 (with sqlite engine) location and aliases tables get empty after 1 minute. It does not happen with other tables (e.g. dialplan & subscriber). ul.dump command also shows empty tables. Some configuration: # -- usrloc params -- # use sqlite database for persistent storage, write-thru modparam("usrloc", "db_mode", 1) modparam("usrloc|dialplan", "db_url", "sqlite:////etc/kamailio/db.sqlite") [Attached SQLite schema.] Can anyone help me? Thanks in advance. BR, Pedro ADVERTENCIA Este mensaje y/o sus anexos, pueden contener información personal y confidencial cuyo uso, reproducción o distribución no autorizados están legalmente prohibidos. Por lo tanto, si Vd. no fuera su destinatario y, erróneamente, lo hubiera recibido, le rogamos que informe al remitente y lo borre de inmediato. En cumplimiento de la Ley Orgánica 15/1999, de Protección de Datos de Carácter Personal le informamos de que su dirección de correo electrónico, así como sus datos personales y de empresa pasarán a formar parte de nuestro fichero de Gestión, y serán tratados con la única finalidad de mantenimiento de la relación adquirida con usted. Los datos personales que existen en nuestro poder están protegidos por nuestra Política de Seguridad, y no serán compartidos con ninguna otra empresa. Usted puede ejercitar los derechos de acceso, rectificación, cancelación y oposición dirigiéndose por escrito a la dirección arriba indicada. This e-mail and its attachments may include confidential personal information which may be protected by any legal rules and cannot be used, copied, distributed or disclosed to any person without authorisation. If you are not the intended recipient and have received this e-mail by mistake, please advise the sender and erase it. In compliance with the Spanish Organic Act 15/1999 on Personal Data Protection, we hereby inform you that your email address, as well as your personal and business information, will be included in our Management files and used solely for purposes corresponding to our commercial relationship. All personal data in our possession is protected by our Data Safety Policy and thus shall not be released to any other third party whatsoever. You may exercise your right to access, rectify, cancel and contest by writing to the address provided above. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

-- Carsten Bock CEO (Geschäftsführer) ng-voice GmbH Schomburgstr. 80 D-22767 Hamburg / Germany http://www.ng-voice.com mailto:carsten@ng-voice.com Mobile +49 179 2021244 Office +49 40 34927219 Fax +49 40 34927220 Sitz der Gesellschaft: Hamburg Registergericht: Amtsgericht Hamburg, HRB 120189 Geschäftsführer: Carsten Bock Ust-ID: DE279344284 Hier finden Sie unsere handelsrechtlichen Pflichtangaben: http://www.ng-voice.com/imprint/ -- Meet ng-voice at LinuxTag 2012 in Berlin - May 23rd - 26th, 2012. Save the date! _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users ADVERTENCIA Este mensaje y/o sus anexos, pueden contener información personal y confidencial cuyo uso, reproducción o distribución no autorizados están legalmente prohibidos. Por lo tanto, si Vd. no fuera su destinatario y, erróneamente, lo hubiera recibido, le rogamos que informe al remitente y lo borre de inmediato. En cumplimiento de la Ley Orgánica 15/1999, de Protección de Datos de Carácter Personal le informamos de que su dirección de correo electrónico, así como sus datos personales y de empresa pasarán a formar parte de nuestro fichero de Gestión, y serán tratados con la única finalidad de mantenimiento de la relación adquirida con usted. Los datos personales que existen en nuestro poder están protegidos por nuestra Política de Seguridad, y no serán compartidos con ninguna otra empresa. Usted puede ejercitar los derechos de acceso, rectificación, cancelación y oposición dirigiéndose por escrito a la dirección arriba indicada. This e-mail and its attachments may include confidential personal information which may be protected by any legal rules and cannot be used, copied, distributed or disclosed to any person without authorisation. If you are not the intended recipient and have received this e-mail by mistake, please advise the sender and erase it. In compliance with the Spanish Organic Act 15/1999 on Personal Data Protection, we hereby inform you that your email address, as well as your personal and business information, will be included in our Management files and used solely for purposes corresponding to our commercial relationship. All personal data in our possession is protected by our Data Safety Policy and thus shall not be released to any other third party whatsoever. You may exercise your right to access, rectify, cancel and contest by writing to the address provided above. _______________________________________________ SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list sr-users(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users

-- Carsten Bock CEO (Geschäftsführer) ng-voice GmbH Schomburgstr. 80 D-22767 Hamburg / Germany http://www.ng-voice.com mailto:carsten@ng-voice.com Mobile +49 179 2021244 Office +49 40 34927219 Fax +49 40 34927220 Sitz der Gesellschaft: Hamburg Registergericht: Amtsgericht Hamburg, HRB 120189 Geschäftsführer: Carsten Bock Ust-ID: DE279344284 Hier finden Sie unsere handelsrechtlichen Pflichtangaben: http://www.ng-voice.com/imprint/ -- Meet ng-voice at LinuxTag 2012 in Berlin - May 23rd - 26th, 2012. Save the date!

Reply