Ralf Schneider wrote: > > Am Montag, 19. September 2005 17:28 schrieb Robert Klemme: > > You could do reports like > > > > select user, count(*) > > from your_table > > > > to see which user takes up how much space of the table. If you > > combine this with information about the whole table's size you can > > even calculate MB a certain user uses. > > But this would be quite inexact. Assume there are several VARCHAR columns > that > may have 1000 characters, but the values stored are only a few characters > long. The users would not understand why they reached their limit although > they entered only short texts. > > My idea was to use triggers for all the tables that calculate the usage of > the > currently modified record and store this value in a special table. This > would > be something like > > CREATE TRIGGER trg_update FOR <table> AFTER UPDATE EXECUTE ( > UPDATE users SET db_usage = db_usage - :OLD.size + > :NEW.size WHERE user_id = :OLD.user_id;) > > Is there a way to dynamically calculate the size of a record? In this case > something like size (:NEW)? Or can I pass :NEW and :OLD to a dbproc? > > Best regards, > Ralf. >
1. user-limits (as in Oracle) are able to calculate how much data is stored in someones schema, but will never be able to see that column xyz in table abc will be filled with userids/usernames and has to be taken into account as well. --> even Oracle would not be able to add x bytes for row 1 to user m's account and y bytes of the next row to someone else's account. 2. the statement 'select user, count(*)from your_table' will not do what was described together with the statement. The statement will result in the name of the current user and the number of all rows in the table. What could do would be Select <userid-column>, count(*) from your_table group by <userid_column> 3. There is no way to see (with a function or so) the length needed for storing the whole row in MaxDB. 4. I do not know how many users you have, if rows in your tables can only be updated/deleted as the user which was given during insertion. If this were the case I could think of having different tables (one for each user --> size needed for storing can be calculated) and the selection is done with a union for all of these tables. 5. The szenario that different people have to share one table, but are limited to some amount of space, seems to me uncommon. May I ask for some background-info? I think that info stored in a table used by different people is really needed, not existing just for fun and should not be limited to some amount. But, maybe I am wrong. Elke SAP Labs Berlin > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
