Database Questions

[GBTech]

We have a test install of GoAutoDial:

VERSION: 2.2.1-237
BUILD: 100510-2015

A few questions about the database:

On a fresh install of GoAutoDial, the database tables, are they built as InnoDB tables or MyISAM tables? This question is in regards to referential integrity, meaning, if we were to change a field, say the lead_id since it seems to be the primary key in a lot of tables, would it change the lead_id records associated with the initial record changed?

[mflorell]

Default is MyISAM, and this is for a reason, InnoDB causes problems under high load.

Also, if you go changing fields in the database referential integrity is the least of your worries, since the programming relies on the database to be consistent.

[williamconley]

you cannot easily (whoa!) change the field name of a table.

if you change the value of the lead_id field in the vicidial_list table, it will not cascade change the associated records. MyISAM is not relational AFAIK. So no relationships are enforced at any level.

You would do better to use another field for whatever it is you are trying to do.

If you add any fields, add them at the END of the table structure and test thoroughly before using in production.

Do not delete or modify any field names. They are generally used in scripts via field order, but i believe many have been converted to field name, which means that changing EITHER in any way will require a lot of work ... and this work will need to be redone when you upgrade. BELIEVE me when I say keeping your modifications upgrade safe is VERY necessary: changes happen daily, new toys ensue

[GBTech]

The question is not in the changing of the table column or the database schema, but rather whether any relational ties between primary table keys are in place. The problem is specific data is using incorrect values (mainly vicidial_list.lead_id), so if the value used in a record is changed, would that change cascade through the other tables?

I know that MySQL used a sudo-relational tie that could be put in place on any indexed column for MyISAM tables. While it's not a true foreign key reference, it can cascade a value update. MyISAM tables are great for searching through text entries, but suck for any type of data integrity. The InnoDB tables are better suited, but both tables do have their scalability limits. If an InnoDB table is indexed properly, that limit is in the 10/100 of millions of records, or higher in some specific cases. This is why Oracle spent around 10 million in buying the rights to InnoDB.

The question, in the most basic form would be, is there any referential integrity used on the Vicidial DB schema? If not, is there any documentation on the relations between primary keys from one table to a column on another table? Does the code base use a database abstraction layer or make direct innate PHP MySQL function calls? Any information on this would be greatly appreciated. Thanks.

[williamconley]

MyISAM is not relational AFAIK. So no relationships are enforced at any level.

there are no ties to put in place. the value from this field is in use in many other tables, but the enforcement of this relationship is not enforced in mysql because myisam is not capable of it. i am not aware of any method of enforcing any relationship inside myisam. but i DO know that none are in place in this system.

innodb's problem is SPEED, not "capacity". it cannot keep up.

there is NO abstraction layer in vicidial's databasing. it has been made specifically to accomplish the tasks in this system, and it does so.

IF you are volunteering to write an abstraction layer or "db interface", please try testing it at full speed before you try to put it in actual production (a mysql server with 16 processors supporting 250 agents on LOTS of dialers must be able to deal with the data calls, each of the dialers agi scripts accessing vicidial using these abstraction layers must still handle the load, and each of the perl scripts and web pages must also still be able to manage the data with the new method). Absolutely a great idea ... but ...

[GBTech]

It is definitely a lesser know feature, but it is possible to do relational linking of table columns in a MySQL MyISAM table. It doesn't have all the bells and whistles of a true foreign key reference, but it's better than nothing. You can even set these using a simple tool like PhpMyAdmin, example below.

Here you can see a mixed database, using both MyISAM and InnoDB tables


Here is a MyISAM table set with "relations", which is active for any index column defined in the table.


And now the equivalent using InnoDB tables, which has true foreign key referencing.


While the MyISAM relations is far from ideal, it at the very least more clearly shows the DB schema for linking data records.

Have you considering porting the whole thing to PostgreSQL? There are a few 100% translation free tools available for doing the conversion. While the two database types are very similar in access, structure, and what have you, PostgreSQL has become far superior ever since their release of 8.0. They are now a fully relational DB, with 4GL layer, ability to compile triggers written in several languages, and can handle 100 million records in any or every table.[/img]

[williamconley]

it's not about "relational" or "100million records", it's about Speed on a Core2Quad server

I may have used that tool in phpmyadmin, but i forget, the question is what changes that makes to the tables, does it actually create a function in mysql that manages the relationship when phpmyadmin is NOT running or is that a ... well, somewhat limited to phpmyadmin thing?

ie: if you use that tool, then make a change in command line mysql, will it enforce the relationship or ignore it?

I sincerely doubt that anyone has considered porting to postgre any more than MSSQL. unless it can handle the speed ... mysql is the weener.