We came across a need to save just the Time in the database, the requirement is to store time of the day, like say the user likes to have Breakfast at 8.15AM and Lunch at 12.32PM etc. Off course oracle does not have a time only data type. So we ended up using DATE as the data type and just setting the time. for example:
Some environments like to have access to the database tables routed via stored procedures. Instead of using Create/Read/Update/Delete aka CRUD with DML, stored procedures are invoked with the parameters to perform the required operation. I’m not arguing about the benefits/pitfalls of this approach, if you have to do stored procedures, here are some things to look at.
Couple of weeks back I was given a choice to upgrade my work Laptop to a Mac Book Pro or a Windows Laptop. I choose Mac ( I know everyone is into macs nowadays). The transition was pretty good, with the exception of moving my oracle database from windows to mac, since there is no native installation of oracle on mac I had to use VMWare fusion to install oracle.
When creating a Foreign Key constraint on the database as shown below
ALTER TABLE BOOK ADD (CONSTRAINT FK_BOOK_AUTHOR FOREIGN KEY (AUTHORID) REFERENCES AUTHOR) / In the above example we are telling the database to check if the BOOK.AUTHORID is a valid value in the Author.AuthorID. When the Author table is being changed, the database does data verification on the BOOK table using SELECT against the BOOK table for the AUTHORID some thing like this
We had a weird requirement on our project recently..
Find all the Rows in All the tables that do not comply with the Constraints that we have in development but not in QA environments
We have been using DBDeploy on my project for more than 6 months now and wanted to show how things are going. First lets talk about set up, we are using dbdeploy in our Java development environment with ANT as our build scripting tool, against a Oracle 10g database.
Currently working on a legacy application, thats been in production for a long time now. I wanted to find out what are the Tables and Columns being used by the application. Since we could see that some table columns where not being used. We are using a Object Relational mapping framework on the project, so we decided to write some code that would parse all the mapping files and gives us a list of table names and columns. We used this list to create rows in a table with two columns tablename and columnname. Once the table had this data, we just ran one more SELECT against the metadata of the database and our table which pretty much gave us a list of Table and Columns that we are not using
When you are refactoring large databases, you will have certain tables that have millions of rows, so lets say we are doing the Move Column refactoring, moving the TaxAmount column from Charge table which has millions of rows to TaxCharge table. Create the TaxAmount column in the TaxCharge table. Then have to move the data from the TaxAmount column in the Charge table to the TaxAmount column you created in the TaxCharge table.
When you are writing xUnit tests you are in certain ways trying to make sure that the test breaks when the code that is being tested changes the assumptions you made when writing the Test and Production code.
Last week I was at SD Best Practices in Moscow, doing a presentation on “Refactoring Databases: Evolutionary Database Design”. Moscow seems like a interesting place, loads of huge buildings, squares, fountains and roads. Things some how feel rundown, feels like a player trying to regain his former ability or glory.