Hello Klaus,
To answer your question: Yes and no.
sqlbox was originally developed to work together with Postgres (development funded by sendmytxt). After this was working, MySQL support was added.
The bugs that you see now is the result of a major booboo in version mismanagement. Accidentally I copied the mysql version over the pgsql version and the question where I left my backups still puzzles me until today.
The other question: No, this is not too many things in one email. Actually I am happy with it and I will solve those items one by one (promise!).
Thanks for testing!
Kind regards,
Rene Kluwen Chimit
Klaus Darilion wrote:
Hi Rene!
Can it be that I am the first person using sqlbox with postgresql?
I found some postgresql related bugs in sqlbox_pgsql.c from sqlbox_patch-20041031.tar. (Looks like "copy and paste" bugs)
I would be great if you can review the suggested changes and include them into your patch.
1.
When you are trying to get the username and password for the postgres connection from the configuration file, your are looking for the wrong parameters.
They are called "username" and "password" instead of "pgsql-username" and "pgsql-password". (this differs from the mysql syntax)
The follwing works now: if (!(pgsql_user = cfg_get(grp, octstr_imm("username")))) panic(0, "SQLBOX: PGSQL: directive 'username' is not specified!"); if (!(pgsql_pass = cfg_get(grp, octstr_imm("password")))) panic(0, "SQLBOX: PGSQL: directive 'password' is not specified!");
2. Another error when creating the dbpool: pool = dbpool_create(DBPOOL_PGSQL, db_conf, pool_size); instead of pool = dbpool_create(DBPOOL_MYSQL, db_conf, pool_size);
3.
I have to comment the part where you create the tables in case they don't exist, because this SQL syntax (to check the existenz of a table) is not supported by postgresql. Also the auto_increment is not supported by postgresql - this will be handled by a SERIAL/SEQUENCE.
/* create send_sms && sent_sms tables if they do not exist */ /* does not work with postgresql sql = octstr_format("CREATE TABLE IF NOT EXISTS %S ( ... sql_update(sql); octstr_destroy(sql); sql = octstr_format("CREATE TABLE IF NOT EXISTS %S ( ... sql_update(sql); octstr_destroy(sql); */
IMO, I think it is better to remove the table creation from the sqlbox and therefore put some CREATE TABLE statements into a separate file for mysql and postgresql.
These are the SQL statements I used to create the tables (tested with postgresql 7.2):
CREATE TABLE sent_sms ( sql_id SERIAL primary key, momt VARCHAR(5) CHECK (momt IN('MO','MT','NULL')) default 'NULL', sender varchar(20) null, receiver varchar(20) null, udhdata varchar(255) null, msgdata varchar(255) null, time bigint null, smsc_id varchar(255) null, service varchar(255) null, account varchar(255) null, id bigint null, sms_type bigint null, mclass bigint null, mwi bigint null, coding bigint null, compress bigint null, validity bigint null, deferred bigint null, dlr_mask bigint null, dlr_url varchar(255) null, pid bigint null, alt_dcs bigint null, rpi bigint null, charset varchar(255) null, boxc_id varchar(255) null, binfo varchar(255) null );
CREATE TABLE send_sms (
sql_id SERIAL primary key,
momt VARCHAR(5) CHECK (momt IN('MO','MT','NULL')) default 'NULL',
sender varchar(20) null,
receiver varchar(20) null,
udhdata varchar(255) null,
msgdata varchar(255) null,
time bigint null,
smsc_id varchar(255) null,
service varchar(255) null,
account varchar(255) null,
id bigint null,
sms_type bigint null,
mclass bigint null,
mwi bigint null,
coding bigint null,
compress bigint null,
validity bigint null,
deferred bigint null,
dlr_mask bigint null,
dlr_url varchar(255) null,
pid bigint null,
alt_dcs bigint null,
rpi bigint null,
charset varchar(255) null,
boxc_id varchar(255) null,
binfo varchar(255) null
);
Note: postgres will automatically create the sequences "sent_sms_sql_id_seq" and "send_sms_sql_id_seq". Don't forget to give proper permissions to the tables and the SEQUENCE for the kannel DB user. If, for example the database user for kannel is called "kannel", you can grant permissions using:
GRANT INSERT, SELECT, UPDATE, DELETE ON sent_sms TO kannel;
GRANT INSERT, SELECT, UPDATE, DELETE ON send_sms TO kannel;
GRANT INSERT, SELECT, UPDATE ON sent_sms_sql_id_seq TO kannel;
GRANT INSERT, SELECT, UPDATE ON send_sms_sql_id_seq TO kannel;
4.
The changes in 3 lead to problems when inserting into the tables. Just remove the sql_id when inserting and everything works fine:
sqlbox_pgsql.c: ~ line 200 // values = octstr_format("NULL, %S, %S, %S, %S, %S, %S, %S ... values = octstr_format(" %S, %S, %S, %S, %S, %S, %S ... // sql = octstr_format("INSERT INTO %S (sql_id, momt, sender ... sql = octstr_format("INSERT INTO %S ( momt, sender ...
5.
If the postgresql database will be restarted/shut down, the sqlbox stops - this is not good. The sqlbox should try to reconnect after a few seconds (queuing INSERT would be fine but is not necessary).
Debug output:
2004-11-26 17:45:18 [17818] [1] ERROR: PGSQL: FATAL 1: This connection has been terminated by the administrator.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
2004-11-26 17:45:19 [17818] [1] ERROR: PGSQL: database check failed!
6.
msgdata varchar(255) null,
I think this to small as bigger SMS are no unusual. Is it safe to increase this value? Does kannel handle the splitting or do I havbe to split the message before?
BTW: Otherwise the patch works fine. If I found more bugs, I will post them.
Finally a question: I want to put SMS in the send_sms table - which table columns are required, e.g. MO, MT? time? smsc_id? service? sms_type (what are the available types?)? coding? dlr_url?
I hope these are not to many issues in one email :)
regards, klaus
Rene Kluwen wrote:
Hello Klaus,
Yeah, there's priliminary code for MS-SQL for dlr storage and sqlbox tables (sending and receiving).
However, this code has never been tested by myself and AFAIK also not by others.
Rene Kluwen Chimit
Klaus Darilion wrote:
Hi Rene!
Thanks for your answers. I will try it step by step. I now compile CVS from source with pgsql support and test the DB-connectivity using DLR storage. If this works, i will test the sqlbox.
I reviewed the patch and found some stuff for MSSQL. Sothis patch is not only for sqlbox, but also for additional DB support?
thanks for help klaus
Rene Kluwen wrote:
Hello Klaus,
An answer to your question, one by one:
Sqlbox is not included in CVS as of yet.
You can compile with either MySQL or Postgres support alternatively. Both will work.
The latest patch is against CVS version of Oktober 31th but should be applicable to current HEAD as well. In case you get errors, please post on the list or contact me directly and I will generate a new patch.
Hope this helps,
Rene Kluwen Chimit
Klaus Darilion wrote:
Hi all!
I'm using kannel 1.3.2 for connecting to an SMSC and sending/receiving SMS works fine. In the mailing list archive I found some threads about an SQLBOX, which sounds great and I like to use. But I'm little confused about the current status of the sqlbox:
Is it already included in the kannel main distribution or do I have to patch?
If included: Can I use the available debian packages or do I have to compile myself for postgres support?
If patch: Which kannel version with which patch? Can I patch against current CVS or do I have to check out a certain tagged release?
Thanks for help, Klaus
