Shimon Krokhmal's blog

One of the most important rules about running an application (specially web application) is giving to the application only the needed permissions to run, and no more ! (running a web application with an administrator user is bad....).
Sure, it's very comfortable to give the application all the permissions that the system has, and not trying to solve permission related problems directly,
But this kind of an approach is a security-breach prone approach.
suppose, you wrote an application that has a minor security hole in it, and it allows the user to execute some unwanted script.
If it will run in a full permissions context, than it would be very easy to hijack the entire system, or even just do a system-wide damage.
But, if it will run only with the needed permission, then the attacker would have a hard time doing it.

So, what is the solution ?

i've added some gallery to my site ring.
check it out : http://gallery.krokhmal.com

changed on 23.09.06

hi folks.

the blog address has been changed to : shimon.krokhmal.com
apparently, google removed me from his index after i have created a 301 redirect on the root folder to the "/portal" folder,
so i decided to make some face lifting and change the web site structure.
note, that it is the same blog.
stay tuned for more upcoming surprises from the krokhmal house.

update:
the blog brought back to www.krokhmal.com .
the address mentioned above is no longer available.

While surfing the net for some new hardware tech toys, i encountered an article about multi-core guided software architecture.

"Intel's support in multi-core education is critical for two reasons," said Karsten Schwan, professor of College of Computing, Georgia Institute of Technology. "First, getting early access to advanced technology and new equipment is something that always excites students. Second, companies like Intel have a perspective that looks beyond research to see the broader potential for technology."

For us, it means changing the whole perception of software architecture, design and implementation such as parallelism, threading concepts, threading methodology and programming with threads

i got to the conclusion that timing is everything.

no matter if you are the best at what you do,
if you wont do it at the right time, it's useless....
sometimes it's better to wait a little while for the right moment, and only then do your magic.
by the way, it's a very tricky situation, because you cam miss the momment too ...

Ever wanted to do many things all at once, but never had the time to do that?
So, what do you do if you want it all? - You just do it! (No. it's not a commercial for Nike shoes...)
the problem with that, is when you do that, it wears you down, and big time.
Once you started that, soon you realize that you don't have time for anything else except those obligations that you have already got yourself into.
So, what tha hell am I talking about?

when planning a software solution, one of the most important things is to design the solution the best way it can be,
even before writing the first line of code, we need to know exactly how we should implement it.

so what would you do if you never planned/worked/implemented such type of a solution ?

needless to say that inventing the wheel all over again will be unnecessary ,
first you consult with the "elders" (the more experienced co-workers),
trying maybe google up the solution,
going over the references and the guide lines in developing this kind of a solution or at the given platform,
starting some thinking team and so ...

finally, you came up with a solution, and....
start implementing it.

now, in a perfect world, once you did the steps mentioned above, you have a perfect solution.
but in the real world, something must to go wrong.

recently i came across some interesting example of this phenomena.
while designing a "file Version manager solution" on SPS (Share point Server) platform,
following the SPS development guidelines, which says that you (the developer), should never approach the SPS database.
always, but always get the data through the object model.

so, by doing that, you came up with a working application,but..... catastrophic performance.
after consulting with other staff, we came to the conclusion that the bottleneck is in the object model itself.
so the only thing we had to do is the direct approach to the database ( a big NO NO on the guide lines).
and apparently the performance graph suddenly got a dramatic change (better performance).

i think that guide lines did not meant to become laws, but to be just as a suggestions to most of the cases.
what do you think about this issue ?

remember this neat syntax that exists in oracle database for hierarchy selects ?
actually , its pretty simple :

SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

ever tried to do the same in sql server ?
well, these kind of syntax just does not exist,
we need to work very hard to create such a feature in our database.

so, what do we have:

• a table that contains entities
• each entity connected to some father entity
• each connection describes a "father - son" relation between the two entities

lets not forget the things we need to relay on , when implementing :

• what will happen when we will delete the father of some sub tree ?
• what should we do when we update/add a record ?
• how will we select the data ?
• algorithm efficiency is crucial, if we will need to wait 5 minutes for the data, it's not worth it

the first (but apparently the worst) idea that came to my mind is recursion
lets look at this table :

we have we that the employee shimon is the "big boss" (because there is no other boss above him),
under shimon we have the employees Gaby and yossi,
and under Gaby, we have another 2 employees : Koby and jack

the recursive solution is to write some stored procedure that will receive the employeeID and return as a data-Table the results
i will not add the code for this solution and surely will not recommend it because it was many problems :

• for each record we received as a descendant ,
  we need to run with the function and get her descendants,
  and so on, until there are no descendants for the node
• we are limited to 32 levels of hierarchy
• the runtime will depend on the row count that is in the table (we will need to run on each of the rows one at the time)
• the run on the node will look like this :
  
  
  

by the way, the most common way that I've seen to select hierarchical structure,
is simply by setting a join between the levels in the select query.
for example :

SELECT TopBoss.Name TopBoss, Boss.Name Boss, Employees.Name Employee
FROM Employees
INNER JOIN Employees AS Boss ON Employees.BossID=Boss.EmployeeID
INNER JOIN Employees TopBoss ON Boss.BossID=TopBoss.EmployeeID

this apply to the selection of three levels

For each level, you'd need to join the table to itself...not an attractive option if you have 5 or more levels ,
you don't know how many levels you will have to select, there is no way can control it!
It would be great if it could join itself as many times as needed. This is called a recursive join, and though some database products support it (Oracle has the CONNECT BY syntax) SQL Server is not one of them.

the other way is based on a thread that i read here about hierarchies,

lets create a table :

CREATE TABLE Tree (
Node int NOT NULL IDENTITY(100, 1),
ParentNode int,
EmployeeID int NOT NULL,
Depth tinyint,
Lineage varchar(255) )

the extra fields that has been added are the "lineage" and the "depth"

• Depth - for saving the current depth of the record in the hierarchy
• Lineage - for saving all the ancestors of the record as a concatenated string

after filling the needed data for relations, the table looks like this :

The next part is to find the root node of the tree, also known as the top-level, etc.
That's the node that has no parent (Null), so we will start there and set the Lineage column as the root:

UPDATE Tree SET Lineage='/', Depth=0 WHERE ParentNode Is Null

Once we did that,
we can then update the rows who are the descendant of the root node:

WHILE EXISTS (SELECT * FROM Tree WHERE Depth Is Null) 
   UPDATE T SET T.depth = P.Depth + 1, 
   T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/' 
   FROM Tree AS T 
   INNER JOIN Tree AS P ON (T.ParentNode=P.Node) 
   WHERE P.Depth>=0 
   AND P.Lineage Is Not Null 
   AND T.Depth Is Null

this loop will run once for each level of the hierarchy (not for each node as the recursion method.)
so, with data representation of 10,000 records with 8 levels of hierarchy,
this code will run only 8 times to populate the needed data of the "lineage" field and the "depth" field, and this "heavy" procedure will happen only once at the setup.
the table should look like this after the given operation :

You'll notice that for each node, the entire lineage back to the root is stored. This means that finding someone's boss, or their boss' boss, doesn't require any self-joins or recursion to create an indented list. In fact, it can be accomplished with a single SELECT.

SELECT Space(T.Depth*2) + E.Name AS Name
FROM Employees E
INNER JOIN Tree T ON E.EmployeeID=T.EmployeeID
ORDER BY T.Lineage + Ltrim(Str(T.Node,6,0))

maintaining the table is really not a big deal if we will use triggers.
think about the new inserted record as the row that has not been filled in the setup process.
so the insert trigger should be :

UPDATE T SET T.depth = P.Depth + 1,
T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/'
FROM Tree AS T
INNER JOIN Tree AS P ON (T.ParentNode=P.Node)
WHERE P.Depth>=0
AND P.Lineage Is Not Null
AND T.Depth Is Null

the update trigger should do pretty much the same : building the 2 extra field all over again.

suggestions and request will be repplied :)