是否有一种方法来“监听”数据库事件并实时更新页面？ [英] Is there a way to 'listen' for a database event and update a page in real time?
问题描述
我正在寻找一种方法来创建一个简单的HTML表，可以在数据库更改事件时实时更新;特别是添加了一个新记录。
换句话说，想象它像一个执行仪表板。如果销售并在数据库中添加了一个新行（我的情况下为MySQL），那么网页应该用新行刷新表。
我已经看到一些关于新的使用 EVENT GATEWAY 的信息，但所有的例子使用Coldfusion作为推动者，而不是消费者。我想让Coldfusion更新/推送一个事件到网关，并消耗响应。
如果这可以使用AJAX和CF的组合，请让我们我知道！
我只是想了解在哪里开始实时更新。
谢谢
我结束了。
up to @ bpeterson76的回答，因为在这一刻，它是最容易实现小规模。我真的很喜欢他的Datatables的建议，这是我用来更新接近实时。随着我的网站变大了（希望），我不是确保这将是一个可扩展的解决方案，因为每个用户将击中一个侦听器页面，然后查询我的数据库。我的查询相对简单，但我仍然担心未来的性能。
在我看来，随着HTML5开始成为Web标准，Web套接字@iKnowKungFoo建议的方法很可能是最好的方法。彗星与长轮询也是一个好主意，但它是一个有点麻烦实施/也似乎有一些缩放问题。
所以，让我们希望网络用户开始采纳更现代的浏览器支持HTML5，因为Web Sockets是一个相对容易和可扩展的方式接近实时。
如果你觉得我做错了决定请离开
最后，这里是一些源代码：
/ strong>
注意，这是一个非常简单的实现。它只是希望查看当前数据库中的记录数是否已更改，如果是，请更新表并发出警报。生产代码更长，更多的参与。这只是显示一个接近实时更新的简单方法。
< script src = http://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.js\"> ;</script>
< script type =text / javascriptcharset =utf-8>
var originalNumberOfRecsInDatatable = 0;
var oTable;
var setChecker = setInterval（checkIfNewRecordHasBeenAdded，5000）; // 5秒间隔
函数checkIfNewRecordHasBeenAdded（）{
// json对象发布到CFM页面
var postData = {
numberOfRecords：originalNumberOfRecsInDatatable
};
var ajaxResponse = $ .ajax（{
type：post，
url：./tabs/checkIfNewItemIsAvailable.cfm，
contentType：application / json，
data：JSON.stringify（postData）
}）
//当响应返回时，如果更新可用
// - 绘制数据表并向用户发出警告
ajaxResponse.then（
function（apiResponse）{
var obj = jQuery.parseJSON（apiResponse）;
if（obj.isUpdateAvail ==Yes）
{
oTable = $（'＃MY_DATATABLE_ID'）。dataTable（）;
oTable.fnDraw（false）;
originalNumberOfRecsInDatatable = obj.recordcount;
alert（'已添加新行！'）;
}
}
）;
}
< / script>
Coldfusion ：
< cfset requestBody = toString（getHttpRequestData（）。content）/>
<！---仔细检查以确保它是一个JSON值。 --->
< cfif isJSON（requestBody）>
< cfset deserializedResult = deserializeJSON（requestBody）>
< cfset numberOFRecords =＃deserializedResult.originalNumberOfRecsInDatatable＃>
< cfquery name =qCountdatasource =＃Application.DBdsn＃username =＃Application.DBusername＃password =＃Application.DBpw＃>
SELECT COUNT（ID）as total
FROM myTable
< / cfquery>
< cfif＃qCount.total＃neq＃variables.originalNumberOfRecsInDatatable＃>
{isUpdateAvail：是，recordcount：< cfoutput>＃qCount.total＃< / cfoutput>}
< cfelse&
{isUpdateAvail：No}
< / cfif>
< / cfif>
简单的方法是通过.append添加：
$（'#table> tbody：last'）。append '< tr id =id>< td> stuff< / td>< / tr>'）
添加元素实时是不可能的。你必须运行一个Ajax查询，在循环中更新以捕获更改。所以，不是完全实时，但非常，非常接近它。
但是如果你想要更多的参与，我建议你看看 DataTables 。它提供了很多新的功能，包括排序，分页，过滤，限制，搜索和ajax加载。从那里，你可以通过ajax添加一个元素并刷新表视图，或者只是通过其API附加。我已经在我的应用程序中使用DataTables一段时间了，他们一直被引用为数字1功能，使大量的数据可用。
-Edit -
因为不是很明显，更新DataTable时，您的调用会将Datatables调用设置为一个变量：
var oTable = $（'＃selector'）。dataTable（）;
然后运行此操作进行更新：
oTable.fnDraw（false）;
更新 - 5年后，2016年2月：
今天更有可能它是在2011年。新的JavaScript框架，如Backbone.js可以直接连接到数据库，并触发UI元素上的更改，包括更改，更新或删除数据的表....这是这些框架的主要优点之一。此外，UI可以通过套接字连接馈送到Web服务的实时更新，这也可以被捕获和执行。虽然这里描述的技术仍然有效，但今天有更多的活方式做事。
I'm looking for a way to create a simple HTML table that can be updated in real-time upon a database change event; specifically a new record added.
In other words, think of it like an executive dashboard. If a sale is made and a new line is added in a database (MySQL in my case) then the web page should "refresh" the table with the new line.
I have seen some information on the new using EVENT GATEWAY but all of the examples use Coldfusion as the "pusher" and not the "consumer". I would like to have Coldfusion both update / push an event to the gateway and also consume the response.
If this can be done using a combination of AJAX and CF please let me know!
I'm really just looking to understand where to get started with real-time updating.
Thank you in advance!!
EDIT / Explanation of selected answer:
I ended up going with @bpeterson76's answer because at the moment it was easiest to implement on a small scale. I really like his Datatables suggestion, and that's what I am using to update in close to real time.
As my site gets larger though (hopefully), I'm not sure if this will be a scalable solution as every user will be hitting a "listener" page and then subsequently querying my DB. My query is relatively simple, but I'm still worried about performance in the future.
In my opinion though, as HTML5 starts to become the web standard, the Web Sockets method suggested by @iKnowKungFoo is most likely the best approach. Comet with long polling is also a great idea, but it's a little cumbersome to implement / also seems to have some scaling issues.
So, let's hope web users start to adopt more modern browsers that support HTML5, because Web Sockets is a relatively easy and scalable way to get close to real time.
If you feel that I made the wrong decision please leave a comment.
Finally, here is some source code for it all:
Javascript:
note, this is a very simple implementation. It's only looking to see if the number of records in the current datatable has changed and if so update the table and throw an alert. The production code is much longer and more involved. This is just showing a simple way of getting a close to real-time update.
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.1/jquery.js"></script>
<script type="text/javascript" charset="utf-8">
var originalNumberOfRecsInDatatable = 0;
var oTable;
var setChecker = setInterval(checkIfNewRecordHasBeenAdded,5000); //5 second intervals
function checkIfNewRecordHasBeenAdded() {
//json object to post to CFM page
var postData = {
numberOfRecords: originalNumberOfRecsInDatatable
};
var ajaxResponse = $.ajax({
type: "post",
url: "./tabs/checkIfNewItemIsAvailable.cfm",
contentType: "application/json",
data: JSON.stringify( postData )
})
// When the response comes back, if update is available
//then re-draw the datatable and throw an alert to the user
ajaxResponse.then(
function( apiResponse ){
var obj = jQuery.parseJSON(apiResponse);
if (obj.isUpdateAvail == "Yes")
{
oTable = $('#MY_DATATABLE_ID').dataTable();
oTable.fnDraw(false);
originalNumberOfRecsInDatatable = obj.recordcount;
alert('A new line has been added!');
}
}
);
}
</script>
Coldfusion:
<cfset requestBody = toString( getHttpRequestData().content ) />
<!--- Double-check to make sure it's a JSON value. --->
<cfif isJSON( requestBody )>
<cfset deserializedResult = deserializeJSON( requestBody )>
<cfset numberOFRecords = #deserializedResult.originalNumberOfRecsInDatatable#>
<cfquery name="qCount" datasource="#Application.DBdsn#" username="#Application.DBusername#" password="#Application.DBpw#">
SELECT COUNT(ID) as total
FROM myTable
</cfquery>
<cfif #qCount.total# neq #variables.originalNumberOfRecsInDatatable#>
{"isUpdateAvail": "Yes", "recordcount": <cfoutput>#qCount.total#</cfoutput>}
<cfelse>
{"isUpdateAvail": "No"}
</cfif>
</cfif>
This isn't too difficult. The simple way would be to add via .append:
$( '#table > tbody:last').append('<tr id="id"><td>stuff</td></tr>');
Adding elements real-time isn't entirely possible. You'd have to run an Ajax query that updates in a loop to "catch" the change. So, not totally real-time, but very, very close to it. Your user really wouldn't notice the difference, though your server's load might.
But if you're going to get more involved, I'd suggest looking at DataTables. It gives you quite a few new features, including sorting, paging, filtering, limiting, searching, and ajax loading. From there, you could either add an element via ajax and refresh the table view, or simply append on via its API. I've been using DataTables in my app for some time now and they've been consistently cited as the number 1 feature that makes the immense amount of data usable.
--Edit --
Because it isn't obvious, to update the DataTable you call set your Datatables call to a variable:
var oTable = $('#selector').dataTable();
Then run this to do the update:
oTable.fnDraw(false);
UPDATE -- 5 years later, Feb 2016: This is much more possible today than it was in 2011. New Javascript frameworks such as Backbone.js can connect directly to the database and trigger changes on UI elements including tables on change, update, or delete of data....it's one of these framework's primary benefits. Additionally, UI's can be fed real-time updates via socket connections to a web service, which can also then be caught and acted upon. While the technique described here still works, there are far more "live" ways of doing things today.
这篇关于是否有一种方法来“监听”数据库事件并实时更新页面？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
