|
The "Database is a Bucket" Mentality
Front and center again...I just woke up from a nap, I'm grumpy, so I must write. Besides, I haven't had a good rant in quite some time.
Friend of mine asked me last week for some advice, specifically asking if there was a tool to convert Oracle SQL Syntax to the ANSI SQL syntax. (A quick search turned up this (it was the first result), if you're interested). I had to ask why. Client is switching to an open source database, i.e. "free." Oracle licensing is way too pricey. I'm sure Oracle costs a lot of money, it's pretty darn good software. Quite possibly the best in the world especially in the database realm. I've written about the incredibly feature rich goodness that is the Oracle database here here...actually, just trust me. It's in my name. Why is there even a comparison? Could it be that everyone out there believes that the sole purpose of a database is to store data? That it can't do anything else? The storage and retrieval of data...that's all it does of course. It's like saying the Democrats and Republicans are the same...at face value, perhaps, but the devil is in the details. This, this "Bit Bucket" mentality is what is so incredibly frustrating. I am no position to argue the differences between the various flavors of database, I lack the experience. But if I were using SQL Server, I would leverage the shit out of it's capabilities. If I were using MySQL, I would leverage the shit out of it's capabilities. If I were using Firebird, I would leverage the shit out of it's capabilities. Same goes for every single flavor out there. Get my point here? The database is NOT a bit bucket! Do I need to use more 4-letter words? I know that Oracle is feature rich and that 99% percent of your code can live in the database...think APEX and PL/SQL. You could probably put ALL of your code inside the database if you wanted to put the javascript in BLOBs as well. Please, please please quit telling me they are the same...they are not. Follow up rant by Mr. O'Neill can be found on this following post Everything is a Bit Bucket Labels: database, design, development, oracle, rant ¶ 3/09/2010 10:50:00 PM 10 Comments Links to this post 
PARALLEL Rant?
Let's say you have DEGREE set at the table and index level.
I ask you if this is appropriate, instead offering up using whatever tool accessing the data to provide the PARALLEL hint. The DOP is set pretty low, given our current system. But it's still set and can't be easily turned off. I'm all for PARALLEL, but it's been beaten into my head that it should be used, specifically for batch operations. In other words, transforming or loading data. With the DOP set at the table or index level, it is not (necessarily, see resource limits below) controllable. If you have 1 or 2 users issuing SELECT statements against the table, it's not a big deal. Let's say the DOP is set to 8. 8 processes are spun off for each user. That's 16 processes now running that SELECT statement. Now let's say you have 1000 users. You probably won't make it to 8000 processes...your machine will probably keel over and die...or worse, just sit there forever. But we have to set DOP at the table/index level...our users don't know how to write SQL. Fair enough...teach them how to write it. That takes too much time. How do you ever expect them to learn? It might be a good short term solution, but is it really a good long term solution? Teaching your users how to write better SQL would be in everyone's best interest.
OK, that might be a bit of a fantasy. What about setting up resource profiles for the users? I've never used them, but I was reading up on them tonight for this post. What can you control with this feature?
Labels: design, development, parallel, random ¶ 2/14/2010 10:12:00 PM 8 Comments Links to this post
PRIMARY KEY and NOT NULL
I've seen this far too often. A table with a primary key (good) and a check constraint (NOT NULL) on the same column.
Stop doing it. Watch. CREATE TABLE tAs HillbillyToad said,  It is better than no constraint, that's for sure. The heart was in the right place... Labels: constraints, development, sql, wtf ¶ 2/03/2010 07:46:00 PM 15 Comments Links to this post
Free Oracle Developer/dba
There is a serious lack of work in the Tampa market and desperate times call for desperate measures.
Now, I've always wanted to do this, but was never in a position to do so financially...I'm still not, but something is way better than nothing. I'm going to offer my services for free. Not forever mind you, somewhere between 2 and 4 weeks. It is negotiable. If it works out, i.e. you like what I can bring to your organization, I prove that I pick new systems up quickly and I fit in well with your team; and I like working in your organization, then you pay me for that time worked and I sign up full-time either as a permanent employee or some sort of contract. If it doesn't work out, we part ways and no one is worse for wear. You get free work from me and I get to experience a new environment (i.e. meet new people, expand my network, etc). You can view my resume here (permanent link up top coming soon). Some basic highlights: PL/SQL: Expert (I don't like that term) DBA: Junior to Mid-level (or DBA in lowercase, "dba") APEX: Worked with up to version 3.2, admittedly rusty, but passionate about the product. Design: Love modeling data. Model just about everything in my head (in normal life). Use Visio extensively for visual representations. Experience with SQL Developer Data Modeler, ERwin, etc. Documentation: Give me a wiki or something similar and I'll document just about everything that I do. That's it. Contact info is on my resume or up top on the "email" icon. It's now posted on craigslist as well. Labels: database, dba, development, jobs, plsql, work ¶ 10/27/2009 12:25:00 PM 3 Comments Links to this post
Wu Wei
...is an important concept of Taoism (Daoism), that involves knowing when to act and when not to act. Another perspective to this is that "Wu Wei" means natural action - as planets revolve around the sun, they "do" this revolving, but without "doing" it; or as trees grow, they "do", but without "doing". Thus knowing when (and how) to act is not knowledge in the sense that one would think "now" is the right time to do "this", but rather just doing it, doing the natural thing.From Wikipedia. Labels: development ¶ 10/20/2009 12:25:00 AM 1 Comments Links to this post
Database Cleanup: Metrics
Before my current refactor/redesign goes to production, I would like to capture some metrics. I'm fairly limited in what I can actually do (i.e. I can't use DBMS_PROFILER in production).
So far, this is what I have come up with: 1. Lines of Code (LOC) - I don't believe this is necessarily a reflection of good or bad code. For instance, I can take that 2 line INSERT statement and turn it into 20 lines. Was INSERT INTO my_table(id, col1, col2, col3, col4, col5, col6, col7, col8, col9 )Is INSERT INTO my_tableThat's a pretty sane example. The 2 line version isn't all that bad, but it does run off the page. The point I am trying to make is that "cleaning" up can actually add more lines to your code. In my opinion, when more than one person is going to support the code, readability is a nice thing. Whether or not you like my style, it is (more) readable. So LOC is not necessarily a great metric, but it can give you an idea which way you are going (after it has been properly formatted anyway). 2. COMMITs - Many argue that there should (almost) never be commits (an exception is logging with the AUTONOMOUS_TRANSACTION pragma) in the database. The calling application should perform the commits. Unfortunately that general rule is not always followed. I've added it to my list of metrics because it is pertinent to our environment. Of course I have gone to great pains to make sure that the removal of one commit will not impact the entire system...that possibility does exist when you have commits everywhere. 3. Text - This was a real stretch. What is the size of the individual procedure, package or function? I wouldn't have considered it (I never have until now), but I was desperate to define something...anything. How do you determine that? SELECT name, type, SUM( LENGTH( text ) ) t 4. Dependencies - Also known, to me, as modular code. Why have 200 INSERT statements into a single table when you could wrap that up into a single procedure and call that? If you add a column, you'll still have to go through and fix all those occurences (if it's not defaulted to something). But if you remove a column from that table, it can easily be hidden from the calling code, thus you only have to change it in one place. Of course you wouldn't want to leave it there forever, but it can be done piece-meal, bit by bit as you work on affected parts. Have you ever thought about this before? What kind of metrics would you suggest? I know mine are a bit of a stretch...so please share. Labels: design, development ¶ 9/29/2009 02:14:00 PM 3 Comments Links to this post
How To: Clean Your Schema
I'm going to be testing my migration script in my personal sandbox. I just want to make sure I have the exact order of execution for all the objects before sending it on to the DBA.
In that regard, I had to clean up my existing schemas. By "clean up" I mean remove all of the objects. Laurent Schneider posted something very similar last year, but it didn't handle scheduling objects (there is a reference to scheduled jobs in the comments though). So here's my version which handles TYPES, JOBS (Scheduler), PROGRAMs, CHAINs and TABLEs. There are probably more cases that I did not catch, but this is the most I have come across so far. DECLAREEnjoy. Labels: development, howto, testing ¶ 9/17/2009 01:47:00 PM 0 Comments Links to this post
The Case For Views
I recently had to "defend" my use of views.
To me, they seem natural. Using them is almost always a good thing to do. I've met those that don't really care for them...I just never understood why. Then again, those same people are still not convinced of PL/SQL APIs. Maybe there is something to that mindset... Being forced to articulate one's views is a good thing, it's part of why I blog. I won't lie though, it gets frustrating to have do this, seemingly, all the time. I'm going to do it here, again. Complex Joins No, I'm not afraid of joins. I am afraid of others who are afraid of joins though. More specifically, I'm afraid of those who aren't proficient at writing SQL. Let me do it, once, and let everyone else have access to the view. Besides, I'm the subject matter expert (SME) on the given set of tables, so it follows that I should create the interface to those tables. Yes, I said interface. It's exactly what a view is and interface to the underlying data. Encapsulation Write it once and let it propogate everywhere. When I had to "defend" my use of views, I mistakenly used the example of adding columns. Oops. That would (possibly) require changes throughout the system. I meant to say remove columns, in which case you could keep the placeholder in the view using NULL without having to change all of the code. This does not mean that proper analysis does not need to be performed, it does, but you could possibly get away with not having to change everything that references the view. My second example was a derived value. This makes more sense to some people thankfully. I've seen the same calculation done on a specific field done 10s, even 100s of times throughout the code. Why not do it one time? Perfect use for views. Security Following the least privileges necessary to perform a given action, views allow you to give access to the data without direct access to the tables. Views can also be used to hide or mask data that certain individuals should not have access to. In conjunction with VPD or Application Contexts, it's a powerful way to prevent unauthorized access. Maintenance Maintenance has been alluded to above, but not explicitly stated. For derived values: If you have a derived or calculated value and that calculation is performed all over the place, what happens when it changes? You have to update it everywhere. If you had used a view, change it once and it propogates everywhere. What was once a project is now a "simple" code change. This affects IT in how they choose and assign resources as well as the Business. For complex joins: What if one table is no longer used or needed? What if that table is littered throughout the code base? You have a project on your hands. If that table were part of a view, you could "simply" remove it, keep the columns in the view and you're done. There might be places where code needs to be adjusted, but overall, you have a much smaller impact. That's a good thing. Other I tried putting the following statement in a category up above, but couldn't make it fit. Records in a table typically constitute data. Tables, joined together, in a view, tend to turn that data into information. Labels: design, development, rant, views ¶ 9/03/2009 12:40:00 PM 3 Comments Links to this post
How To Kill a Code Review
Today's guest post is from Gary Myers from Igor's Oracle Lab. I was first introduced to Gary via comments left here. I can't find the first one of course...but he has left plenty of them. All well thought out and informative. Most recently he introduced me to the ability to do a bulk bind using %ROWTYPE here.
This is a topic that is all too often ignored, as you all know. Steven Feuerstein states here that "Everyone knows that code review is a good idea" The problem is what happens after the review. Once upon a time there was a piece of code. That code had been in production for a long time, ran pretty slow but not slow enough that it had reached the top of the pile of stuff people complained about. A change was done to that code for a new enhancement, and in one of those bursts of enthusiasm that sometimes hits a development team, it got subjected to a code review. The whole structure of that code was ugly, with unnecessary nested cursor loops. The big kicker for performance was that at the end of one of these inner loops was a TRUNCATE TABLE. Because when you are deleting all the rows from a table, every-one *KNOWS* that a truncate is fastest, right ? Of course, as the TRUNCATE is DDL, it meant that all the SQL using that table inside that loop was getting re-parsed each and every time through the loop. There were other problems in the code too. I believe one was with variables not being re-initialized at various points in the loop, so there was a risk of incorrect results in some unlikely cases. The verdict of the review was that the code needed a re-write. The problem was, since it was already in production, no-one wanted to admit that there were bugs in it (and the users hadn't spotted any incorrect results). The new code would go into a future patch, but that wouldn't go live for months. However it had been promised for delivery to a test environment. A rewrite would mean missing the drop deadline. A quick-fix could be done to improve performance. A rewrite could be done and the deadline missed. The quick-fix could be done and still meet the deadline, then a later rewrite to fix the underlying problems (but those problems probably would have been blamed on the quick fix). A compromise was reached. Since the change to the code didn't actually introduce any new bugs, it would be allowed to go through to test with no changes from the review. And there was a promise to actually rewrite the code. Of course, once the delivery was done, lots of other priorities came ahead. I don't know whether the code ever got the rewrite, but I suspect not. Definitely, for at least six months, there was a batch job taking hours when a five minute code change could have cut it to minutes. At least the developers who participated in the review learnt that a TRUNCATE has drawbacks. The code reviews pretty much never happened again though. Labels: code, development, discipline, gmyers ¶ 8/23/2009 09:52:00 PM 7 Comments Links to this post
PL/SQL: Coding Practices
I've struggled this week to write. Lately I've had lots of technical content and not much philosophical content. I have lots of philosophical content now, but the blog isn't the place for it...at least not now.
The big project I'm working on now is refactoring our payment processing system. We interface with multiple gateways for redundancy purposes. The code is comprised of 3 stand-alone procedures. One of those procedures has (had) 17 private procedures/functions in it. On one hand, it made sense, since everything was a stand-alone procedure or function, you didn't want all these dependencies on other objects. On the other hand, testing was virtually impossible. If you needed to make a change to private procedure #13, you had to run through an almost infinite number of test cases to ensure that you covered that particular case. I've now moved those 3 procedures into a package. Theoretically, it's hot deployable now as long as we don't change the package signature. That's a big win in my book. I've also moved those 17 private functions into their own individual procedures and functions that can be exposed via the package specification for unit testing purposes (in production they will be private as nothing else needs access). The hardest part of that effort was there were no parameters being passed to the procedure, it just relied on the declared variables. So for each and every one of those I had to figure out what it relied on to work and what variables it set. No small task. Here is the table: CREATE TABLE tand a procedure (which does nothing obviously) CREATE OR REPLACEWhat's the best way to integrate the procedure into your code? I've seen this: CREATE OR REPLACESince I default the input parameters to 0, I didn't have to specify each individual parameter every time I called it. I like that. I don't much like having 4 separate calls to UPATE_T though. 1. It makes it difficult (without further logging), to determine where exactly it's being called in the control statement. 2. Seems like a waste of space. P_ID is always going to be the same, why set it 4 times? I decided to make just one call to UPDATE_T. I create local variables, then set them in the control statement, and then make the call to UPDATE_T. CREATE OR REPLACENot much savings in space (and sometimes you'll actually have more), but for me, this is much easier to read. If I have to debug this, it feels a lot easier to concentrate on the control statement without the calls to UPDATE_T. What do you do in these kinds of situations? Same as me? Different? Think I'm off my rocker (yeah, I know some of you do)? Labels: design, development, plsql ¶ 8/19/2009 10:04:00 PM 2 Comments Links to this post
%ROWTYPE, Part II
In the last entry on the subject, Gary Meyers pointed me to this link. Not having used %ROWTYPE that often I was not aware of some of the features.
The 2 that interested me the most were the INSERT and UPDATE: DECLARESo I began to use them (despite the bad feeling I get...). I started to receive errors however, NOT NULL constraint violations in fact. How could that be? Here's the definition of MY_TABLE CREATE TABLE my_tableSee the column DEFAULTs? I would imagine these are functioning as expected, but I don't like it. One benefit I see to this method is that it saves a little bit of typing (despite the SELECT * feel), or so I thought. If you don't explicitly set the value like this: l_table.status := 'A';you are hosed. Plus, what if the calling application had already set those, now you have this: IF l_table.status IS NULL THENSo where is the savings in that? Labels: %ROWTYPE, development ¶ 8/11/2009 06:00:00 PM 6 Comments Links to this post
PL/SQL: Parse URL Strings
Finally, I can put it all together now.
PL/SQL: Split URL Parameters PL/SQL: Split Key-Value Pairs REGEXP_REPLACE - Credit Card (CC) Numbers PL/SQL: Pipelined Function It all culminates in this (hopefully final) post. The goal was to be able to take in a URL string and parse it out accordingly. I ultimately decided that persisting that data was not of importance, so I built a fairly flexible function which returns a user-defined-type (UDT), also known as a SQL object. We'll start with the UDTs: CREATE OR REPLACE TYPE r_key_value_record IS OBJECTNothing fancy there. I'm not exactly sure why I added the ORDEROF column, other than I think it will come in good use down the road. FUNCTION parse_urlI tried to make this as flexible as possible. Different payment gateways return different response strings so this seemed necessary. I suppose I could build one for each...but that wouldn't be as fun. P_URL - self-explanatory P_TOKEN_DELIMITER - For most URL strings, this will be the ampersand (&) that separates the key/value pairs. P_KEYVALUE_DELIMITER - Usually the equals (=) sign, but can vary. P_ENCLOSED_BY - occasionally a string will be enclosed by quotes (") P_LINE_START - much more rare, the string has one or more characters at the beginning of the line P_LINE_END - much more rare, the string has one or more characters at the end of the line The declaration: ISNothing special here. IF p_line_start IS NOT NULL THENGetting rid of any start or end characters. Next up, my kludge: IF SUBSTR( l_string, -1, l_token_delimiter_length ) != p_token_delimiter THENJust adding the delimiter to the end of the string. Apparently I'm too lazy to figure out a better way. Finally, the meat of the process: LOOPWhat does all that do? Let's see. SET DEFINE OFFHow about a different flavor of URL string? BEGINOK, one more. For this one, there will be no key/value pair, it's simply ordered (yes, you need the API to decipher it). BEGIN Labels: development, plsql ¶ 8/10/2009 09:47:00 PM 0 Comments Links to this post
Users and Roles - Revisited
In a previous post, How To: Users and Roles (Simple), I went through and demonstrated one way to build a simple security system using users and roles.
I'm using the Database Authentication security scheme in Apex. Patrick Wolf [ blog ] pointed out that I should create database roles instead of using my own. That is the end game of course as I've limited the role_name column to VARCHAR2(30). But for now, this will have to do. 38 lines is now 27 The previous code looked like this: FUNCTION is_authorizedand I found it didn't work too well. Specifically, the person with the least privileges, TAB_ADMIN, could see everything that only the ADMIN could see. There was another small problem, using INSTR was screwed up to. Can you guess why? 'ADMIN'? That would return a hit back for 3 roles: ADMIN, SECTION_ADMIN and TAB_ADMIN. Bad developer, bad. (Another great reason you should NOT roll your own security!) What was the problem again? Let me quickly recap what I am trying to do with a "pretty" picture:  I'm going to try and articulate this with words, please don't laugh. ADMIN is the all powerful, it should be able to do anything it wants to do. There are 5 authorization schemes, one for each role; placed on different objects (pages, items, tabs, etc). If a button has the TAB_ADMIN authorization schema, ADMIN should be able to run it through inheritance. The picture above is trying to depict that with those red arrows. So, what's the point of this post? I've forgotten, so I'm skipping to the end. I refactored the IS_AUTHORIZED procedure from 38 lines to 27 and I believe it's a bit more intuitive than it was. The key for me was included ROLE_LEVEL which is just an alias for the pseudo-column LEVEL. If the ROLE_LEVEL, of the enabled roles for a user, is less than the ROLE_LEVEL where ROLE_NAME is part of the ROLE_PATH (SYS_CONNECT_BY_PATH), you're in. Or something like that. Here's the final product: FUNCTION is_authorizedI'll also be updating the other post to point to this one. Small reminder: TESTING, TESTING, TESTING Labels: development, plsql, security ¶ 8/06/2009 06:15:00 PM 2 Comments Links to this post
Logging, Debugging, Instrumentation and Profiling
It started with this tweet from Cary Millsap [company | book | blog | twitter ]
 That took me to this article on his blog, Multitasking: Productivity Killer. I then began to look through his blog history and one in particular caught my attention, On the Usefulness of Software Instrumentation, from February of this year. I left a comment: I'm having a conceptual crisis.To which he responded: Hmm, good one. I don't have enough time left today to work on it before close of business, but let's chat about it via email. That ought to help you out, and then I'll be glad to keep thinking about it and write something up. Write me via http://method-r.com/contact-us. —CaryWhat follows is the conversation between Cary and I, reprinted with here with his permission. Me: July 15, 2009, 10:09 PM As directed...;)Cary: July 15, 2009 11:36 PM Here are some thoughts. I'd like you to press back with your questions and comments before I consider putting this up as a blogpost.Me: July 16, 2009 12:11 PM OK, Debugging and Logging are much more clear now.Cary: July 16, 2009 11:11 AM Inline below...Me: July 16, 2009 2:30 PM Ah ha! I think I have it now. Cary: July 16, 2009 3:10 PM What I mean by artistic is that there aren't any hard-and-fast rules that a programmer can use to determine what code paths to instrument, and which not to. And there are no hard-and-fast rules describing how to segment the instrumentation; e.g., 10046 and 10200 should be separately controlled? or should both instrumentation types be combined into one switch? I use the "art" word to denote tasks for which there's no deterministic list of steps that an inexperienced, untalented person can follow to do just as good a job as an experienced, talented one.I'm still looking for the public link to this paper. I will update this post when I find it. Me: July 21, 2009 12:47 AM Interesting read. I haven't quite wrapped my head around, but I've only read it once. Cary: July 21, 2009 9:03 AM Well, as a PL/SQL programmer, you have 10046 data, which can be assembled into a profile. And you have DBMS_PROFILER, which gives you a response time profile partitioned by PL/SQL line number. As Jeff and I suggested in our book, the analysis process there is:Me: July 21, 2009 11:36 AM duh...dbms_profiler. I am a bit slow on occasion. SYS@TESTING>BEGINYou can find more information on DBMS_PROFILER here. I have not yet completely wrapped my mind around this yet. I think it might be in part due to the fact that I have never really programmed in another language like C or Java (well, a little) where you run the debugger and profiler against your code. I don't know if this conversation makes me look like a complete dolt either...I don't really care if it does. Cary graciously made himself available to answer my questions and tolerated my...inexperience in this area. It's no wonder he is held in such high regard in the Oracle community. Not only does he know his stuff, he's more than willing to share it. Hopefully some of you will get something out of this conversation. I'm still hoping that Cary will do his own posts in the future, but I am grateful he let me share this with you. Labels: debug, development, instrumentation, logging, profiling ¶ 7/26/2009 08:04:00 PM 1 Comments Links to this post
SQL Objects, JDeveloper, JPublisher and Java
This is one for my application developer friends.
Say your application uses stored procedures. Not just any stored procedures, but ones that accept arrays or SQL Object types (User Defined Types - UDTs). One thing you have to do is map a java object to that SQL Object before you can pass it back to the stored procedure. I can't remember the specifics, but I can show you how to "automatically" create the Java code that maps to the SQL Object. First, create type UDT. If will be a table of a record...is it called a multi-dimensional array? Whatever. CREATE TYPE r_record IS OBJECTI'll create a procedure that accepts T_TABLE as an input parameter. It won't do anything, but just to give you an idea. CREATE OR REPLACENow go into JDeveloper and through the Database Navigator, select the schema you placed these objects.  Right click on the object and select Generate Java (as shown above). You'll then be presented with an options menu:  It's all foreign to me so I accept the defaults. I do notice however that it's pretty customizable, for instance, you can select how you want to Number Types to be generated, either objectjdbc, oracle, jdbc or bigdecimal. You'll know better about what this means than I will.  From that, you get 3 files, TTable.java which maps to the TABLE OF R_RECORD, RRecord.java which maps to the SQL Type R_RECORD, and finally RRecordRef.java...which I don't really know what it does. I'm sure you will though. Here's the code generated, in order. TTable.java import java.sql.SQLException;RRecord.java import java.sql.SQLException;RRecordRef.java import java.sql.SQLException;How do you integrate all that? I'm not really sure, but Marc recently wrote up a piece on using this method to tie into SQLUnit, pretty nice one too. Labels: database, development, java, oracle, sqlunit ¶ 7/23/2009 05:34:00 PM 0 Comments Links to this post
Constraints to the Max!
I ran across this question today on the Oracle-l list:
Hi List,Later in the thread, Niall Litchfield, replied with the following: Assuming that you mean you want to make all string data case insensitive and that the requirement has come from developers who don't want to check their inputs for case errors (though I bet they want to say business logic is an application function) then a check constraint on each column that the inserted/updated value is equal to its uppercase representation is a start. Then they'll have to either check for the constraint failing or start discussions with you about database input validation aka constraints.That gave me an idea. CREATE TABLE tWhat does that do? It insures that the value put in X is always uppercase. PARTY@TESTING>INSERT INTO t ( x ) VALUES ( 'home' );Cool. Now the Application Developers have to deal with it. No wiggle room there. How far can you go? Let's see. DROP TABLE t PURGE;I'm sure I could go on and on...but it's kind of fun. ARTY@TESTING>INSERT INTO t ( x ) VALUES ( 'HOME SCHOOL' );No spaces allowed. PARTY@TESTING>INSERT INTO t ( x ) VALUES ( '12345' );No digits allowed! Why? Constraints are awesome. The very definition of constraints is awesome. Some of the best ideas come to you when you are constrained by something, usually time. OK, maybe not time in software development. Time in writing I've heard is pretty cool. Limit the colors an artist has available and see what they come up with. Less choices sometimes means better. Typically I'll constrain the crap out of a data model. With development and testing, some of those will be relaxed. Some will be added. The point is, don't be afraid of them. Constraints are a very good thing indeed. Labels: constraints, database, development, oracle ¶ 7/21/2009 12:53:00 AM 2 Comments Links to this post
How To: Users and Roles (Simple)
I'm using Apex again, which is nice. I love the database work; cleaning, refactoring, etc. but there's just something very cool about being able to put a "face" on that database stuff.
I'm mostly done with the application and I'm going through adding in security. It's not an after thought, I'm just not going to add it first as it will make my life very difficult. I would love to use Database Authentication, but it only checks to see if you have an account. Your database roles do not carry over into Apex (yet). I don't want to use Apex Authentication mostly because I've never used it. I could create my own table based security, but not yet. I'm trying to keep it as simple as possible for now. I decided on a mix of Database Authentication and table based users and roles. I will not be storing passwords nor writing any custom authentication. I'll just rely on Oracle to do that. I will however capture the username and put it in an Application Item. In the Administrator's interface, the users will be able to create users...but only if they already exist in the database. I will be creating the following roles: --ADMIN - this role can do anything. full access to the application. --SECTION_DEVELOPER - as it stands, we can not completely hand this off to the business. There will be some intervention required by IT. This role should be able to do all that the business person can do and a couple of extra actions. --SECTION_ADMIN - this will be the role assigned to the business person. --TAB_DEVELOPER - similar to SECTION_DEVELOPER, just another section of the application --TAB_ADMIN - same as SECTION_ADMIN, assigned to business user Perhaps an easier way to visualize it: ADMIN --SECTION_DEVELOPER ----SECTION_ADMIN --TAB_DEVELOPER ----TAB_ADMIN Here's the table to hold the roles: CREATE TABLE t_rolesI'm creating a view on top of this table because I want to use some of the hierarchical capabilities. CREATE OR REPLACEI created a procedure, is_authorized which took in the username (Application Item) and a literal; ADMIN, SECTION_DEVELOPER, SECTION_ADMIN, TAB_DEVELOPER, and TAB_ADMIN. It returned either TRUE or FALSE (boolean). Here's the table definitions for the user and user/role intersection tables: CREATE TABLE t_usersNow I need a FUNCTION that will tell me whether this person is authorized or not. I struggled with this a little bit (hence the post). DECLAREOK, so if the code from Apex is passing in CJUSTICE for the username and ADMIN for the role, it works. But ADMIN is the all powerful user. So I need another check in there: DECLAREThat's where I kind of got stuck. The extra SELECT COUNT(*) would have needed at least one more additional IF THEN control statement. Time to step away. Thinking about it, it might be easier if ADMIN were the bottom-most...leaf? In other words: SECTION_ADMIN --SECTION_DEVELOPER ----ADMIN Using SYS_CONNECT_BY_PATH it would have been easy to check. I think. If ADMIN is at the bottom though, you'll have to jump through all kinds of code hoops to keep it at the bottom. That's the indicator to me that I'm overthinking it. What about a loop, using ROLE_PATH (SYS_CONNECT_BY_PATH)? Something like this: FOR i IN ( SELECT role_pathIf the user's role (ADMIN) is at a place closer to the front of the string and the passed in role (TAB_ADMIN) is further back, the user will be authorized to see/view/execute whatever the authorization is applied to. Here's the code all put together: CREATE OR REPLACEIt's certainly not the best solution in the world. When I have a bit more time, I'll certainly revisit it. Integration with Apex Since I haven't worked with Apex regularly since 2.2, I started to use the Conditions section to store the code. What that would mean is possibly storing RETURN is_authorized( :F_USERNAME, 'ADMIN' /*(or others)*/ );in about 60 locations throughout the application. Then I saw this:
I had completely forgotten about that! Authorization Schemes allow you to define as many...schemes as you want. I created 5, one for each role. Added bonus, when I need to change these, I only have to do so in one place. You can add Authorization Schemes to almost everything. Pages, HTML Regions, Buttons, Items, Columns in reports, etc. As fine a granularity as you would ever need. Update: 08/07/2009 This function does not work! Sue me. There is a later post addressing the issues I found, Users and Roles - Revisited. Labels: apex, development, howto, security ¶ 7/20/2009 04:25:00 PM 4 Comments Links to this post
Classic: Application Developers vs. Database Developers II
The original (with all the fun comments) can be found here. Originally posted on December 9, 2008. This is the "followup" to yesterday's post.
You can read the first article here. My application developer friend, Mr. M, emailed me and another fine gentleman this little blurb recently: Mr. M: OH YEAH BABY!!! TEN TIMES FASTER!!!! YEAH!!!!!!!!This was prompted by a recent Oracle email blast about the Exadata storage system/Warehouse. As I did before, I'll just put the email here. Me: Agreed, their client tools aren't all the great. Which ones are you using?Mr. M: This was followed by a package he had been working on. I wouldn't say it was the greatest, but it wasn't all bad either. Me: goodness gracious.Of course note the use of "naturally" in my lexicon. Thanks Jake. Mr. M: well dude, we are back to our old discussion - you arguing that procedural sql code is perfectly fine for building apps, and by extension, that the last 20 years of computer science have basically been a misguided lost journey down the meandering, fruitless trail of oop. um.....no. select this from that. otherwise keep that sql crap caged up where it belongs.Me: I would say, based on limited knowledge of software development, that the OOP movement was started because the database (specifically Oracle) was not mature enough to do what was needed. Plus, I seem to recall that the OOP movement was supposed to have solved all the world's problems by now.Me (again): oh yeah...and PL/SQL is/was built on top of ADA, FYI.Mr. M: "I still believe that if application developers better understood how a DB works (Oracle, MySQL, SQLServer, etc) they would end up writing less code. Database constraints alone force you to write less (and better) code simultaneously ensuring good data."Me: CHECK, NOT NULL (same as CHECK) and FOREIGN KEY constraints all fit into that category.Mr. M: Ahh, jeez dude, I wasn't sure if you were referring to the literal "constraint" or not.Me: So we've had this discussion at work...for a high transaction system, do Java/C/etc handle exceptions well or what?Mr. M: "for a high transaction system"I was laughing at this point because the link above points to one of our consulting architects (I'm not really sure what his role is at this point). Me: i agree in any application that you want to minimize the number of round trips...Mr. M: "database is the bottleneck because people don't know how to write SQL.....some dumb-ass c#/java guy thought he could write a better query than i."Mr. V (note, not Mr. M): My 2 canadian cents:Mr. V, I believe, is a little bit more sensible. Mr. M on the other hand is just trying to rile (sp?) me up. Me: Someone will probably create something like that, but it still gets at the heart of one of my arguments, many developers don't know how to use a database thus will go to any means to circumvent it. Embrace it I say.Mr. V: Haaa chet.And that's where it ended. I must say it's always fun. Mr. M and Mr. V are both very smart individuals and I highly respect what they do. We have different perspectives...but I think they listen, if only a little, as I listen to them. Their voices creep up on me especially now...which is definitely a good thing. Labels: design, development, funny, humility, ideas, java, plsql ¶ 7/07/2009 05:15:00 PM 1 Comments Links to this post
|
Guest Authors
How To
EBS Install Guide
Learn OBIEE Parallel Processing: DBMS_JOB Write File to Disk Populate Time Dimension DBMS_CRYPTO PL/SQL: Split URL Parameters Instrumentation: DBMS_APPLICATION_INFO OBIEE: Migrate Your rpd Application Express: A Guide
Popular
Why You Should Blog
AppDev vs DataDev Coding is Easy Fun With Linux Code Style Index Better than Tom Kyte? Previous Posts
Afraid to COMMIT;
Everything is a Bit Bucket APEX: Create and Parse Arrays The "Database is a Bucket" Mentality Code Comment WTF? Part 209 APEX: LDAP Authentication February Top 10 SQL Developer: Install Unit Testing Repository OBIEE: Default Answers Template? OBIEE: XML File as Data Source Code Projects
Archives
August 2007 /
September 2007 /
October 2007 /
November 2007 /
December 2007 /
January 2008 /
February 2008 /
March 2008 /
April 2008 /
May 2008 /
June 2008 /
July 2008 /
August 2008 /
September 2008 /
October 2008 /
November 2008 /
December 2008 /
January 2009 /
February 2009 /
March 2009 /
April 2009 /
May 2009 /
June 2009 /
July 2009 /
August 2009 /
September 2009 /
October 2009 /
November 2009 /
December 2009 /
January 2010 /
February 2010 /
March 2010 /
|
