Working on a DB2 Schema... need some help

Yii 1.1 General Discussion
1

Hey everyone. Based on the information I have gathered, I believe that I need to create a DB2 schema for Yii. I would love to share it with the community as I feel it would really help expand the reach of this framework; however, still being a little new to Yii and completely new to the schema files… I am having some issues - luckily only minor things. I want to post my progress and questions here. Please help me if you can by answer my questions and providing your thoughts and knowledge. :D

My first question is sort of simple I think, but in the schema files, datatypes are “mapped” from a common name like “string” to a physical type like “varchar(255)”. That makes sense really; but what if the type is not supported at all? For example, db2 doesn’t have a DATETIME datatype. It has a DATE and it has a TIME and it has a TIMESTAMP… no DATETIME. So how should I set that? Is it ok to just remove it? or is there some way of throwing an exception if it is being used to let the user know that type is not supported?

Thank you. :)

**wanted to add: here is what I have so far for the datatypes; please let me know if you see issues with it; thanks




public $columnTypes=array(

        'pk' => 'nummeric(11,0) GENERATED ALWAYS AS IDENTITY',

        'string' => 'varchar(255)',

        'text' => 'clob',

        'integer' => 'int',

        'float' => 'float',

        'decimal' => 'decimal',

        //'datetime' => 'datetime',

        'timestamp' => 'timestamp',

        'time' => 'time',

        'date' => 'date',

        'binary' => 'blob',

        'boolean' => 'numeric(1,0)',

    );
2

does it actually help having time data as time data in a db?

I have been doing web apps for 6 years now and always use unsigned INT fields to store time.

Are there any pros in keeping time data as data objects? because space and speed do take a hit that way.

3

He’s trying to write a database schema for DB2 for Christ sakes. :)

Whether one like it or not, DB2 does have date/datetime/time fields…

4

XD thank you.

And I know of the date/time… but I’m not seeing datetime in my IBM DB2 table construction client. I suppose uncommenting that line shouldn’t do much against me. It is just a datatype map for Database types to PHP “types”.

Anyway - FOR AN UPDATE.

The SCHEMA is pretty much complete and working; however, there is still a lot to do. Just some tweaks on the actual schema object and extending the CommandBuilder. But as a reference to gauge my progress - at the end of my work Friday, I had a Yii app running on an IBM i5 server, using a DB2 database schema. I had 2 (non system) tables in my database, and was able to successfully use Gii to create their models and CRUD. Then with a small edit to the command builder (via my own class that extends the base), I was able to use the CRUD near flawlessly. I am having some problems with a few details, but I will get them straightened out first thing Monday.

And in case there is any concern or confusion - I want to clarify that I have NOT edited any of the base framework files. Although, I have added my schema files at that level (in the same directory as the others). And for the auto loading to work, I did need to add my new files to an array in the YiiBase - but this is more of a hack to get things moving forward not really specific to the task at hand.

Also, this does NOT use odbc. One tech at my work place was mildly concerned about the performance with odbc on the i5 (honestly, I have no opinion on this comment). Truth is it didn’t matter as there is a PDO_IBM driver that can be used… and that is what I did.

Again this schema is for the IBM i5 servers DB2 databases… and progress is going well. :D

5

I got the schema fully working. I am now able to create Yii applications on an IBM DB2 database. I can also use Active Record and (from what I can see) all other elements of Yii.

Still a problem with the older database structures due to the common use of composite keys and special characters like # in the field names… but with little effort, these can all be modified.

I’ll probably post a more detailed view point of this later, but I searched a bit on the issue of CRUD generation with a composite key and saw some nasty work-arounds… but my suggestion in these cases are just to not use the CRUD generation, it’s not supported yet… but you can generate a model, a form, and a controller… MVC. What more do you need? From there, just copy paste some create and update code from a properly generated object, and tweak it to work how you need it to. A little more time, but its clean and arguably quicker since there aren’t any pitfalls with this route.

Anyway, I am still refining things, but I plan to contact the Yii Staff in hopes they can use this schema… works quite nicely right now. :)

6

You are a fast worker. Respect. :)

7

Thank you much, but my place of work is to blame. We need this working ASAP and they aren’t big on waiting for new technology to be created. But thank you even still. :D

Still having some problems though. Minor things, but quite tedious and real pains in the neck. Not sure if it has to do with the connection though. Seems to be an issue with auto commit, but 1) the PDO driver doesn’t seem to handle the default flags like the other PDO drivers and 2) there doesn’t seem to be an easy way to pass connection options into the connection; but I have some workarounds… and I guess I’ll just keep pressing on… :wacko:

I’ll keep everyone posted on the progress.

8

Man, i’m really interested in a DB2 schema. I would appreciate to see your code, at least as a guideline to implement mine. In my workplace we created an abstraction layer over DB2 but is not full featured as Yii’s Active Record and we’re missing it.

Keep us updated about the progress. :)

9

Sorry for the delay on a reply here… I sort of dropped of the map (too much work and distractions in life).

The truth is this is in place and working. We actively use it and it seems to work great so far. However, with the lack of field testing and my perfectionist ideals in coding, I am afraid to release it to the public as I am sure there are things that could be better or functionality to be added…

However… if it is needed, I’ll get it packaged and put up for download ASAP. All I ask is to be notified of changes… aside from that, you and anyone else can use this code as is for any reason. I did it to fill a void that needed filled for my work place; there is no needed for others to do the same. I’ll be in touch. :D

10

Well, why not put it in Bitbucket or Github ?

You will be notified, patches are easier to contribute and it will be a win-win for both you and your company and others. :)

11

Any update about the schema publication?

Thanks

12

Was the schema uploaded somewhere at last? it would be very useful for me since Im running through the same situation right now I have to make the application run with DB2.

Thanks in advance from Argentina!

13

Any updates on this? I also need to use this DB2 Schema.

I’d like to ask the admins to send him an e-mail because probably he isn’t reading this trheads anymore.

14

Since Snake coudn’t share his schema with us, I decided to start coding the schema for my needs, it is hosted at:

There is also a feature request issued for that:

http://code.google.com/p/yii/issues/detail?can=2&start=0&num=100&q=&colspec=ID%20Type%20Status%20Priority%20Milestone%20Owner%20Stars%20Summary&groupby=&sort=&id=658

Currently it only support SELECT operations. Any helps on evolving this code are welcome.

Best regards.

15

My extension for IBM DB2: YiiDB2