macOS Would it be wise to create a chat client based on PHP/MySQL?

[mrzeve]

Hey guys,

So I want to create a desktop application for instant messaging.

I've read the typical way of doing it (and did it back in my VB days) of creating a server and a client, with the server always needing to be running and such.

But my question is what if I took a php/mysql approach.


Heres how I envisioned it working:

- Layer 1 is one backend of the application. This part sends the inputed text to the MySQL database.
- Layer 2 pings the MySQL database and looks for new content. If there is no new content for that specific user, nothing occurs.
- Layer 3 displays the new content that is found in Layer 2. So basically this is the GUI.

Here are the issues I see: would it be to costly/straining for the database and server to constantly check it? And, well, what else?

The reason I was thinking about this method is because A. it is going to be much easier for me to make and B. it does not need to be real time. Think of it like text messaging. Or if any of you have a blackberry, just like blackberry messenger.

I look forward to hearing all of your thoughts and opinions.

 

[lee1210]

do messages live forever in the DB, or are they transient?

Each presents a different problem for DB design and maintenance.
if they are permanent you have tractless DB growth and pulling any single message becomes more difficult. You probably also need to track if each message is new.
if it is transient and you delete messages it requires more vigilant maintenance to keep the indicies fresh and to actually reclaim the space for reuse. You also need to institute a deletion policy so you only delete things when the client confirms it has the message.
in both cases you have to index things for fast access, and design the database with constraints. It should be a pretty small schema so this should not be too hard.

-Lee

 

[SDDave2007]

In order to make a chat system worth a darn in PHP, you need to investigate AJAX. otherwise you will be redrawing a ton of webpage everytime your refresh.

I wrote one just like this.. and its main problem was the way I crafted the mySQL queries over-taxed the server if there were more than a dozen people in the chat. But this was partially design flaw on my part, and partly because my dang ISP only supports mySQL 3.43.

Using PHP 5, and mySQL 5 along with AJAX you could if you were knowledgable enough create a really good application.

Google Darren Gates FlashChat.... this is what I ended up using.. it cost a whopping $5 and so far has been bullet proof for me..

 

[lee1210]

thought of a few more things...

Will you allow for one-to-many messages? If so you have to decide if the space vs. simplicity is worth it to store the message N times and have the recipient and sender stored in the same table. Otherwise you could store the sender and message in one table, then have a join table between messages and recipients. This also muddles with read status of the messages, deletion policy, etc.

You might also consider if users can send attatchments now, so you can design it in from the start. I would not keep these in the db itself, but just in the filesystem with paths stored in the db. On the client side you could filter on type and size. If you allow this definitely only keep 1 copy of each attachment regardless of the number of recipients.

One last thing is to always remember the tale of Little Bobby Tables and sanitize your db inputs. You don't want a smartass to send the message:
'); delete from messages;
and have it ruin the database.

As for PHP and mysql, I am a postgres guy myself, but I am sure they are up to the task.
-Lee

 

[psingh01]

What you describe sounds a lot like a php/mysql forum. Where the threads are private to specific users having a conversation/meeting.

 

[wildwobby]

Or, the best option would be to implement the reverse of AJAX:

Comet:
http://en.wikipedia.org/wiki/Comet_(programming)

 

[mrzeve]

You guys all bring up some great points.

I think if I did it in PHP/MySql I would be able to create the database very efficiently, I have it all mapped out in my head.

The ultimate goal is to make an iPhone application. So basically the application would need to do the following:

- Ping the server every X amount of time that elapses to check for new messages. (Select from database where msg = new, etc etc). I'd probably say it needs to check the server every 30 seconds.
- If there are new messages, sort them based on the senders ID and display them to the user of the application
- POST a message to a .php file that will add the users sent message to the database.

Thats basically all that would be required of the iPhone application. The rest is done (quite easily) by the PHP/MySQL end of things. It is for this reason that I do not believe I would need to use Comet or Ajax either, since the iPhone application isnt running as a browser, but as a downloader of the content. The pages the iPhone application would download are plain text with the information that it needs. But then the interesting question is whether or not I can keep an active (comet-like) connection open between the iPhone and the webserver to check for new messages, or should it just refresh the feed every 30 or so seconds.

What do you guys think, is that an inefficient way of doing that?

 

[exabytes18]

It can be done as you describe, but it is inefficient. I've tried a similar technique with Memory Tables and such to speed things up. It worked, but it was hardly impressive. Querying the database so frequently puts undue strain on the MySQL especially as the number of users increases.

I was still interested in creating an actual chat client in Java and from my past experience with constantly querying the database, knew it wouldn't hold, so I went with the asynchronous approach. It was all done in memory, but I did push the data to a MySQL database for archiving. It's definitely the preferred way to do it, but it took a long time to get it all setup and running.

 

[lee1210]

Since this is asynchronous I don't think the Comet approach is as workable. Unless you could deliver the message as soon as it was sent, you have to hit the database to get messages when a user does become "active". That means that whether the server is polling the database to see if the user is available and if so, what messages they have and pushing them every N seconds is just as hard on the database as the client asking every N seconds if there are messages available.

I think the goal of the database design is to have a very fast, very efficient way of finding new messages. I think one way to achieve this is to have a "new messages" join table that has user id and message id for any messages that are not read. This table should stay rather small as long as users are picking up their messages regularly. I don't know if mysql does triggers and stored procedures yet, but if so this would be an easy way to keep this table up to date. If not you will have to do so manually (insert when new message is inserted, or on an update from read=false to read=true if users can mark a message unread, delete when message is read or user is removed from the DB).

This avoids big queries against the messages table, which will be very large. It also means that this table, if it's accessed often enough, should have its indicies in cache (whether that's the DB cache, system memory because the index on disk is cached by the OS, etc.) which should avoid the real performance-killer of user-facing disk access.

At every step of the design, just try to keep in mind what MUST make the user wait. Once a user presses send, you only have to make them wait until the message itself is committed to the database. Once it is, no longer need to be involved/waiting. All of the side table maintenance, notification if recipient is logged in, etc. is none of their concern. You can do all of that in the background on the server. You also want to be sure that when a user wants to know if they have messages, this should be very fast as that will be far more common than a user writing a message. This is why you might have to design it a little more than the obvious "Query the huge message table to see if user X has any messages that are marked as new".

Just try to make the frequent queries hit the smallest, most optimized, best indexed dataset possible.

As an aside, I am working on something that resembles this problem set right now. Essentially there needs to be a fast way for users to get a 10000' view into a very complex system often. The design I came up with involved a background process generating the summary data and putting it in a very small table once every N minutes. Then when hundreds of users want to see if the the data has changed every minute, the queries generated are very, very fast. The generation in the background is very intensive, but no user is waiting for that to happen when they press a button, etc.. I wanted to use a push approach when things changed, but in this scenario it turned out to be impractical, and this setup is working pretty well.

-Lee

 

[wildwobby]

Actually,

Comet really would be the best option.

Read the wiki:

http://en.wikipedia.org/wiki/Comet_(programming)

 

[lee1210]

Actually,

Comet really would be the best option.

Read the wiki:

http://en.wikipedia.org/wiki/Comet_(programming)

I just did, and maybe I'm still not "getting it", but if a client isn't logged in, they can't have a message pushed to them. If they are, they can. That seems to add a lot of complexity, and I'm not completely sure of what the gain would be. I guess it wouldn't be that bad, but I guess i'm still not getting why the push really helps here.

-Lee

 

[mrzeve]

I just did, and maybe I'm still not "getting it", but if a client isn't logged in, they can't have a message pushed to them. If they are, they can. That seems to add a lot of complexity, and I'm not completely sure of what the gain would be. I guess it wouldn't be that bad, but I guess i'm still not getting why the push really helps here.

-Lee

Nor am I. I would love an explinanation on it cause it sounds cool. The draw back that I see is that regardless, the client still needs to be connected to a page that is going to constantly be checking the database for a new message. So basically what you are skipping out on is the HTML refresh of the page, but the database is still pinged just as often.

 

[ChrisA]

What you have described is a e-mail system. Why not replace the DBMS with and email server using SMTP? The other ready-made text store and forward protocol is NNTP. DBMSes are not designed to transporting messages but for storage. You main proplem is transport and routing. Don't re-invent that wheel

 

[lee1210]

What you have described is a e-mail system. Why not replace the DBMS with and email server using SMTP? The other ready-made text store and forward protocol is NNTP. DBMSes are not designed to transporting messages but for storage. You main proplem is transport and routing. Don't re-invent that wheel

I would have said that it's more like ICQ, but it's sort of e-mail like. Eventually every email server needs a backing store, anyway, though. If users leave their mail on the server and just access with IMAP, and rarely remove things, there's still SOME database. Exchange uses MSSQL server, others use the file system only, without an RDBMS.

I don't think the OP wants to make a new ICQ, this seems like a bit of practice and a "because I can" project, not a commercial venture or professional need. I say go for it... the worst thing that happens is you don't get all the way there, and you keep emailing your buddies.

-Lee

 

[ChrisA]

I think the goal of the database design is to have a very fast, very efficient way of finding new messages. I think one way to achieve this is to have a "new messages" join table that has user id and message id for any messages that are not read. This table should stay rather small as long as users are picking up their messages regularly. I don't know if mysql does triggers and stored procedures yet,

Trigers is the way to do this. Polling a DBMS is NEVER a good idea. Pooling does not scale. What hapens if your user base grows? How does performance change as you add users? Also with poling you always have to wait, on average 1/2 the poll interval and users will perceive a "lag" even on a lightly loaded system. Much better to use a trigger so the action can happen with not delay, Also trigger scale and the system will gradually degrade it's performance under load. Don't poll.

Why use MySQL? Why not pick a DBMS the has triggers? It's backwards to pick the DBMS first before you have you requirements spelled out. It's like buying a house before you think about how many bedrooms you need. Figure out what your DBMS needs to do then pick one that has those features.

MySQL is very poor if you have many simulations clients. It has a rather gross level of table locking and it you have many clients locks become the bottleneck.

For this kind of interactive application server based PHP is not going to work to well. You will need some kind of client side programming. Does the app really need to be web-delivered? Then you are looking at Javascript or java or can it be a standard Objective-C application?

As I said in another post. What you want is not a DBMS but an SMPT mail server. Mail servers accept messages with to/from headers, hold those messages and then make delivery attempts. They do the exact same job you want to implement. You could write a client to mail mail look like chat

 

[mrzeve]

I would have said that it's more like ICQ, but it's sort of e-mail like. Eventually every email server needs a backing store, anyway, though. If users leave their mail on the server and just access with IMAP, and rarely remove things, there's still SOME database. Exchange uses MSSQL server, others use the file system only, without an RDBMS.

I don't think the OP wants to make a new ICQ, this seems like a bit of practice and a "because I can" project, not a commercial venture or professional need. I say go for it... the worst thing that happens is you don't get all the way there, and you keep emailing your buddies.

-Lee

Basically. The thing I miss most about my blackberry (besides its stability compared to the iPhone) is Blackberry Messenger. This was a program that allowed you to communicate with other blackberry users. It basically worked like text messages but has the feel of an Instant Messaging client. It was texting for free amongst blackberry users. So basically, me and my friends all spoke without charges. The goal is to create something similar, if not exactly the same, for the iPhone.

 

[mrzeve]

Trigers is the way to do this. Polling a DBMS is NEVER a good idea. Pooling does not scale. What hapens if your user base grows? How does performance change as you add users? Also with poling you always have to wait, on average 1/2 the poll interval and users will perceive a "lag" even on a lightly loaded system. Much better to use a trigger so the action can happen with not delay, Also trigger scale and the system will gradually degrade it's performance under load. Don't poll.

Why use MySQL? Why not pick a DBMS the has triggers? It's backwards to pick the DBMS first before you have you requirements spelled out. It's like buying a house before you think about how many bedrooms you need. Figure out what your DBMS needs to do then pick one that has those features.

Does MySQL not have triggers? I believe it does, although I don't know if its slower then others.

MySQL is very poor if you have many simulations clients. It has a rather gross level of table locking and it you have many clients locks become the bottleneck.

For this kind of interactive application server based PHP is not going to work to well. You will need some kind of client side programming. Does the app really need to be web-delivered? Then you are looking at Javascript or java or can it be a standard Objective-C application?

What do you mean web delivered? Just the way it receives the content? The application is not going to work through a browser or anything, only through a client application.

 

[mrzeve]

Another option is creating a Jabber server and then building a Objective-C client, correct?