Migrating to Yii - existing DB w/ GUIDs as primary keys

I was wondering if I could get feedback from somebody who has used GUIDs as PKs in Yii.

Unfortunately, the whole current database (67 tables) is driven by GUIDs.

I could possibly write some scripts to turn the keys into integers, which might turn into a hassle (and might not). Or, I could hang my hat on GUIDs and say that I’ll figure out how to make it work in Yii.

While my initial "testing" phase (CRUD w/ some custom Gii templates) seem to be working okay (except insert), I was just wondering if any body had any "heads up" ideas on problems I might encounter in Yii using GUIDs.

Or any ideas on how I might extend AR for my models.

Thanks for any ideas!

  • B

just a thought, FYI

what i would do

  • add ID field as primary key and remove GUID as primary key;

  • do all your gii stuff and add extra function to generate GUID to be called from actionCreate

  • redefine model relations on GUID rather than ID;

  • then ID field becomes dead field, stays for the sake of AR only;

OR

once all db migrated, you can relink the tables using ID, then get rid of GUID totally

I’ve never used GUID’s as PKs in combination with a SQL database (only a noSQL one). But the question is: Why were GUIDs chosen over integer values in the first place? Maybe there has been/ is a valid reason for that. Some systems use GUIDs to provide unique PKs among several tables,databases and external services etc. so you might actually break that if you are using classic auto_increment values. Second question: Do you know how the GUIDs were created? There are several ways to do that with PHP (see uniqid() method - PHP manual) and you could use ActiveRecords beforeSave() hook to create a valid GUID before insertion.

I believe the GUIDs were used to sync offline clients to the db as this would ensure no duplicates between client and server. The new system will create niche functionality not easily available in the current system, so we have no real plans to duplicate the offline client syncing – though it would be nice to keep that option open.

My final thoughts are:

  1. inheriting a beforeSave function to create GUIDs (not sure which method they used to create them)

  2. inheriting a sanitizing function for findByPk

Any other thoughts before I choose to go forward with GUIDs??

Thanks - B

My 2 cents: If you have the chance not to use GUIDs then don’t ;) But I guess you know what you are doing so regarding your second point: I wouldn’t overwrite findByPk() if that’s what you ment. You can define a custom rule for the guid field and check if it is a valid GUID by doing some regex using the CRegularExpressionValidator So you won’t have to overwrite any existing core parts.

Greetings,

Hannes