There is a part of the application we want to develop first:
this is the inbound-outbound messaging system.
At now the application have 2 different way to manage messaging:
-via email (alarms that can be setted and sended via a crontab file check)
-via db
We think the best will be to manage all via db. why? all can be stored and
used for data miming. no external connections a dependings from mail server
storage and so on. What do you think?
So, let's call this new feature "in-out". The best will be that some
incomings external informations (web forms-mails) can be recognized and
stored in a tabe of the db. Then some internals (your collegue that assign
you an entity- a problem to solve, an alarm you setted, a problem near to
his duedate) are stored and showed somewhere in the application.
In this way we will avoid the using of a crontab (we can set date in db and
make some easy query to show informations we need): application will follow
his goal to be os-independed as we imagined it.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Ok let's start identifying which message will be managed in order to understand how db could be:
-emails coming from external (customers via email)
in this case the email sended to a specific user (to his email) will be stored in db and showed to him as a message on browser. How can we do this?
-form from website
this seems simple. once a form is submittet, this fill directly the database and the new entity is automatically showed on entity brief list (maybe with a special icon). this could be implemented with the other feature for online tracking
-internal application's change of statuses
this seems simple too. some cases:
when "assigned to" change we could notify the new assignee and the old one; when an entity owned or assigned to a user is validated from a validator (edit2 and edit3); when a duedate is reached, when an alarm setted is reached, ...
I think we could add a table for messages. It will have an ID, then a line for message type, then the body, then maybe the destination (the user_id to which the message will be showed).
Users will have such a mycrm page, where all messages will be visible. Will be possible for users to create and send messages to collegues.
In future some statistics could be managed on it.
What do you think?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I agree input from web page forms,
status notifications via email,
are easy to implement,
but input from email will need some type of form or field deliniation (XML?) to provide clean data, so make that a lower priority?
A message table is good, especially for stats!.
A 'mycrm' home page is good!
This all looks great!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Tell me more about field delimitation. Seem interesting.
I will ask to Daniele to follow a bit our discussion here because is planning something which is xml related..
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I would think to store incoming email messages into a table with incoming email details fields such as To:, CC:, From:, message content, date received, importance,... would work and then the user could, be notified to, retrieve his msgs.
What I was thinking of about field deliniation/extraction, was a way to use AI or rules to extract the content of the incoming email to partially proactively route the message depending on the above fields. If enough information in the msg body is not adequate to decide on a routing method then the above would be the default.
This could be on the wish list, what you gents think?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I think that is a good idea, but at the moment is better to develope simple features. When we have a stable version we can implement innovative features that are welcome in the project.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
OK, Would the idea of storing the incoming email msg in a table with this type of field schema work for this aspect
or do we need to look at some other method?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi guys,
I had a long talk with Niki today: here some thinking.
-we think that storing all incomings emails will fill too many data into the db. Imagine a 20/30 users installation with 10.000 customers (hardware seller for exemple): we will collect a lot of data for MySql.
Maybe Oracle could support that amount. Then some procedures will be needed to delete some old records depending on some rules. seems complicated
-so we imagined some other way. one is an old idea I got: put a mailserver embedded into the application. We could find and integrate an opensource lin/win server and the install script will choose.
Or we can use a java mailserver. Sorry Darryl :)))
-anyway data could be showed using a frame with auto-refresh (simple and suitable). we allready used this solution in past with a Chat java+html based chat engine (a big project having Oracle as a Db, RMI, http tunnelling to bypass firewalls and so on). We know how to handle it. Sometimes easy things works great!
This are some ideas. Please comment and contribute. This feature is a milestone for the application and for our full platmform endependency walk.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I agree on the db overflow problem as I was also thinking about scalability.
A mailserver (even java) sounds fine as long as it does not affect the portability and easy of installation/support of CT-CRM.
So far it is quite easy being just PHP & MySQL.
Anyone else have thoughts there?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Can't help but think Sendmail is the answer .. We have a helpdesk now that handles in/out emails via sendmail and a perl script that's called via the aliases file ..
support: "|/etc/smrsh/email.cgi"
ticket: "|/etc/smrsh/email.cgi"
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I think that the amount of data stored is not a problem. Customer-Touch is modular, every user may choose if use or not a module.
The user have to know (because we tell him) the limit of the application and varous modules.
If a user need this module and the amount of data is very large he will use DB2 or Oracle version of cutomer-touch... i hope it :-)
P.S.: I don't know limit of various DB, i know that SourceForge work with db2 without problem, i'm sure that Oracle is one of the better DB so i think it have not problems. What about Postgree? Do you think it is better then MySql?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
The amount of data is a big problem: imagine that with some features like mycrm some recoursive queries will stress the db. I mean if messages are stored into db, mycrm will ask looking for data into the whole msg table every time.
MySql will give us all its help until about 15 pages server per second: up to this will fail. If a mycrm frame autorefresh and is loaded at every page load, we can imagine that this limit will be reached fast.
Sure we could say: use oracle for better performance! But at now we are supporting just MySql and we need this in-out now.
About MySql-Postgree I was reading a beanchmark I did some (mmmh, a lot) time ago:
at the time of my interest, Postgree had a limit of 8k bit data for row. We could surpass it with in-out.
I know is possible to avoid this limit, and maybe new versions have it fixed, but it's structure seems old. MySql is faster.
Another problem (need to verify) is that "serial" in Postgree (read: auto_increment) create a sequence which is not dropped, and this will give some problems. Note also that MySql increments auto_increment (sorry!) when import some new data, where Postgree dont make this (I have to verify new version).
The plus of Postgre is that seems more stable in long term.
Answeryng to Darryl: MySql have no triggers, no transactions and so on.
Anyway again about this in-out module: yesterday me and Darryl have tested a smart php/mysql messenger. I have all the stuff I imagined for our goals. I will tell to niki to go in that way for now.
We will develop something like that and we will add a possiblity to send mails (we will use php to send, as usual).
About receiving them at now for my opinion 2 suitable ways:
-find, build a win/lin mailserver to embed (we wont be os-endep at all)
-create an administrative page for settings the paramethers for mailserver connection
Let me know
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
There is a part of the application we want to develop first:
this is the inbound-outbound messaging system.
At now the application have 2 different way to manage messaging:
-via email (alarms that can be setted and sended via a crontab file check)
-via db
We think the best will be to manage all via db. why? all can be stored and
used for data miming. no external connections a dependings from mail server
storage and so on. What do you think?
So, let's call this new feature "in-out". The best will be that some
incomings external informations (web forms-mails) can be recognized and
stored in a tabe of the db. Then some internals (your collegue that assign
you an entity- a problem to solve, an alarm you setted, a problem near to
his duedate) are stored and showed somewhere in the application.
In this way we will avoid the using of a crontab (we can set date in db and
make some easy query to show informations we need): application will follow
his goal to be os-independed as we imagined it.
HI Darryl, I''m waiting you here in the forum to discuss a bit about the feature you were assigned to.
Let me know
Alex
Hi Alessandro,
Go ahead and outline what your wishes for the in-out,... features are.
Thanks,
Darryl
Ok let's start identifying which message will be managed in order to understand how db could be:
-emails coming from external (customers via email)
in this case the email sended to a specific user (to his email) will be stored in db and showed to him as a message on browser. How can we do this?
-form from website
this seems simple. once a form is submittet, this fill directly the database and the new entity is automatically showed on entity brief list (maybe with a special icon). this could be implemented with the other feature for online tracking
-internal application's change of statuses
this seems simple too. some cases:
when "assigned to" change we could notify the new assignee and the old one; when an entity owned or assigned to a user is validated from a validator (edit2 and edit3); when a duedate is reached, when an alarm setted is reached, ...
I think we could add a table for messages. It will have an ID, then a line for message type, then the body, then maybe the destination (the user_id to which the message will be showed).
Users will have such a mycrm page, where all messages will be visible. Will be possible for users to create and send messages to collegues.
In future some statistics could be managed on it.
What do you think?
I agree input from web page forms,
status notifications via email,
are easy to implement,
but input from email will need some type of form or field deliniation (XML?) to provide clean data, so make that a lower priority?
A message table is good, especially for stats!.
A 'mycrm' home page is good!
This all looks great!
Tell me more about field delimitation. Seem interesting.
I will ask to Daniele to follow a bit our discussion here because is planning something which is xml related..
I would think to store incoming email messages into a table with incoming email details fields such as To:, CC:, From:, message content, date received, importance,... would work and then the user could, be notified to, retrieve his msgs.
What I was thinking of about field deliniation/extraction, was a way to use AI or rules to extract the content of the incoming email to partially proactively route the message depending on the above fields. If enough information in the msg body is not adequate to decide on a routing method then the above would be the default.
This could be on the wish list, what you gents think?
I think that is a good idea, but at the moment is better to develope simple features. When we have a stable version we can implement innovative features that are welcome in the project.
OK, Would the idea of storing the incoming email msg in a table with this type of field schema work for this aspect
or do we need to look at some other method?
At the moment I think that storing emails in details fields is the better solution.
Hi guys,
I had a long talk with Niki today: here some thinking.
-we think that storing all incomings emails will fill too many data into the db. Imagine a 20/30 users installation with 10.000 customers (hardware seller for exemple): we will collect a lot of data for MySql.
Maybe Oracle could support that amount. Then some procedures will be needed to delete some old records depending on some rules. seems complicated
-so we imagined some other way. one is an old idea I got: put a mailserver embedded into the application. We could find and integrate an opensource lin/win server and the install script will choose.
Or we can use a java mailserver. Sorry Darryl :)))
-anyway data could be showed using a frame with auto-refresh (simple and suitable). we allready used this solution in past with a Chat java+html based chat engine (a big project having Oracle as a Db, RMI, http tunnelling to bypass firewalls and so on). We know how to handle it. Sometimes easy things works great!
This are some ideas. Please comment and contribute. This feature is a milestone for the application and for our full platmform endependency walk.
I agree on the db overflow problem as I was also thinking about scalability.
A mailserver (even java) sounds fine as long as it does not affect the portability and easy of installation/support of CT-CRM.
So far it is quite easy being just PHP & MySQL.
Anyone else have thoughts there?
I for one say NO to JAVA anything .. if you want to keep this easy to install and use then NO JAVA ..
Brian
Brian,
AHA I am NOT alone! J2anything too heavy eh?
Any ideas as to alternative solution to handle incoming emails?
(Java belongs in the mug:)
Can't help but think Sendmail is the answer .. We have a helpdesk now that handles in/out emails via sendmail and a perl script that's called via the aliases file ..
support: "|/etc/smrsh/email.cgi"
ticket: "|/etc/smrsh/email.cgi"
Ok, seems that all hate java :)))
Maybe because I'm italian: a lot of cofee=a lot of mugs :))
How is possible to run Sendmail in win?
I think that the amount of data stored is not a problem. Customer-Touch is modular, every user may choose if use or not a module.
The user have to know (because we tell him) the limit of the application and varous modules.
If a user need this module and the amount of data is very large he will use DB2 or Oracle version of cutomer-touch... i hope it :-)
P.S.: I don't know limit of various DB, i know that SourceForge work with db2 without problem, i'm sure that Oracle is one of the better DB so i think it have not problems. What about Postgree? Do you think it is better then MySql?
The amount of data is a big problem: imagine that with some features like mycrm some recoursive queries will stress the db. I mean if messages are stored into db, mycrm will ask looking for data into the whole msg table every time.
MySql will give us all its help until about 15 pages server per second: up to this will fail. If a mycrm frame autorefresh and is loaded at every page load, we can imagine that this limit will be reached fast.
Sure we could say: use oracle for better performance! But at now we are supporting just MySql and we need this in-out now.
About MySql-Postgree I was reading a beanchmark I did some (mmmh, a lot) time ago:
at the time of my interest, Postgree had a limit of 8k bit data for row. We could surpass it with in-out.
I know is possible to avoid this limit, and maybe new versions have it fixed, but it's structure seems old. MySql is faster.
Another problem (need to verify) is that "serial" in Postgree (read: auto_increment) create a sequence which is not dropped, and this will give some problems. Note also that MySql increments auto_increment (sorry!) when import some new data, where Postgree dont make this (I have to verify new version).
The plus of Postgre is that seems more stable in long term.
Answeryng to Darryl: MySql have no triggers, no transactions and so on.
Anyway again about this in-out module: yesterday me and Darryl have tested a smart php/mysql messenger. I have all the stuff I imagined for our goals. I will tell to niki to go in that way for now.
We will develop something like that and we will add a possiblity to send mails (we will use php to send, as usual).
About receiving them at now for my opinion 2 suitable ways:
-find, build a win/lin mailserver to embed (we wont be os-endep at all)
-create an administrative page for settings the paramethers for mailserver connection
Let me know