Matches in a database.

Gabriel Gunderson gabe at gundy.org
Tue Jun 28 12:24:15 MDT 2005


On Tue, 2005-06-28 at 11:20 -0600, C. Ed Felt wrote:
> I am getting repeat records in my billing database for a VoIP company.  
> There is no fix for this as it is a SIP, (Session Initiation Protocol), 
> specific problem so I am working on a post process solution.

How does SIP make this a problem?


> I am writing a script in Python and the database is MySQL.  The 
> programming language doesn't matter since I am only looking for a 
> suggestion on my sql (MySQL) query.  My current plan is to pull all 
> records (called Call Data Records or CDRs) from the database for the 
> last 24 hours in to a big array.  If any of the records match on a field 
> called 'sessid' then they are copies (though not exact copies as there 
> can be timestamp differences). 

This approach works for PostgreSQL but you will have to rely on the
MySQL users on the list to tell you if there is an equivalent for that
server.

You could use the RULES system to change how the INSERTS are made.  If
you see a matching record you could just ignore the INSERT.  See more
information here...
http://www.postgresql.org/docs/8.0/static/rules-update.html

I like this because it keeps the tables smaller and the data can be used
with out worrying about dupes every time you query it.  This might not
be a good fit for a system that does tons of INSERTs and very few
SELECTs.  In that setting, consider using a SELECT DISTINCT...
http://www.postgresql.org/docs/8.0/static/queries-select-lists.html

Good Luck,
Gabe




More information about the PLUG mailing list