On the right path.

So I made an Index version of the DBObjects to test this new method. To say the least, it's been a very inlighting. 

For my test each object had to get 1,248 User records 100 times (so I could get an average). As far as just retraving the inital list, the index method worked considerablly faster. This didn't come as a surprise because it's just geting all the IDs for all of the user records in the system.

Normal method Retreave List: 3.8 seconds
Index method Retreave List:    0.43 seconds

As I said this isn't surprising because the index method only fetches 1 out of 5 columns. Now on to actually accessing a property for every item in the list

Normal method Access: 0.0 secionds (around 6,700 ticks)
Index method Access:    3:50.50 minutes (around 850,000,000 ticks)!

The index method lost horribly! It just takes too long to query the database 1,248 times. So far this is pretty much what I expected. Heres the total averages.

Normal Retreave & Access: 2.13 secionds (21,380,733 ticks)
Index Retreave & Access: 1:53.21 minuetes (around 1,132,100,354 ticks)

So far it looks like I've been on the right track. In my experiment I did create something neat. For the index I created the abliblity to get a list of objects from the DBList object, with a call like DBList users = new DBList(typeof(DBUser));

One thing I might want to try, is instead of making 1,248 calls to the database, to get batches at a time. So if I want user.id = 1, it'll actually load the first 100 or so items. That would reduce our class to 13. But still I just don't see how 13 calls is going to be any faster than our single call to get all 1,248 items.

Speed Questions

I've been messing around with Objective-C and developing in OSX. So I was reading a tutoral using SQLite3 and the tutoral did something I'd never seen or thought of with the database object. He had the list object populate only the ids (index/primary key) of the objects. Then when you tried to access any of the properties on the object, it would use that id to fetch the rest of the object.

I was intreged, this is better? I had assumed that I would want to get as much data as possible in the fewest calls as possible. But I never actaully tested this theory (or have any idea where it came from.)

So I'm going to test this. I'll perform three tests.

1. I'll try the way I've been developing the objects. Where I try to get as much informaiton in each SQL statement as possible.


2. I'll use the method I read about in the tutoral, where I'll get the ids from everything in the list and return a completed object as I call it.


3. I'll try a varation of the new method. I'll get all the ids, but I'll only return the property requested as it's requested instead of filling out the whole object.
   

The database is MySql 5.1 with InnoDB tables

Difficulties

I've been having problems completing this next step. I think I've been trying to solve two difficult problems at once. The Dynamic loading of the property, and the joining of the two tables.

So I'm taking a step back. Let's solve the join first. How can I solve this problem with the code I already have?
Well I could just manually make the call to Tag in the User property. I would need to use the .Where() method to get what I want.
But, the .Where() method only looks at the objects table. I need to reference a joining table (tag_user to be exact.)

So, I need to be able to specify a joining table in the .Where() method, and then pull normally. I need to identify the table name, the matching columns, and then do the .Where() method normally.

Sounds good, I'll get on this in the morning.

How

I changed some things around. I added a GetProperty and SetPropery because I didn't like have to do a check all the time. I think it simplifies things and allows _properties to be private instead of protected.

I also created a GetColumns that gives the full name with an optional prefix for select statements. I created a GetTable to help out with this as well.

helpers

I which I could put this in the base class somehow, but we can simplify the code if the inherited class override the By and Where methods like so

        /// 

        /// Get a single User By the property and value specified
        /// 
        /// 
        /// 
        /// 
        static public DBUser By(string property, object value)
        {
            return (DBUser)DBObject.By(property, value, typeof(DBUser), DBUser._conn);
        }
        
        /// 

        /// Returns DBList where all the DBUsers have a matching property
        /// 
        /// 
        /// 
        /// 
        /// 
        static public DBList Where(string property, string evaluator, object value)
        {
            return DBObject.Where(property, evaluator, value, typeof(DBUser), DBUser._conn);
        }

Then we can make simple calls like:

DBUser u3 = DBUser.By("firstname", "chris");
//and
DBList users1 = DBUser.Where("id", "<=", 1313);

Next Step

Well I guess the next thing to do is to allow an object to relate to another object. For work I use tags for objects (as I discussed earlier) so that's going to be my starting point.

So I have two objects. User and Tag.

A single User record can have an unlimited number of tags.
A single tag record can have only one user. But a Tag object can have an unlimited number of users.

So we have a many to many relationship, with a table joining them. The table already exists (tag_user) so I need to specify that.

When I get a user, it should get all of his tags as well, like wise when getting a tag it should get all the users. except that might not always be a good idea. What if I have 10,000 users with the tag 'Public' I might want to get that tag without getting all the users who have that tag. alsoIf i'm getting a list of users and they all have the 'Public' tag, it shouldn't the tag shouldn't pull that list of 10,000 for each user in my list. It should only pull it once, and thats only if I ever request it.

So I need to do some lazy loading. Say it only gets the related object if it's requested. So if I get a list of users, it'll just fill out that list. If I access the tags on one of the users, then it'll go and fetch his tags.

So first things first:

Create Many to Many relationship, if you access the related object, then it goes and gets it.

DBObject.Where

Ok it works. I also created a checker for the opertators so we don't get invalid/malicious code.

 /// 

        /// Returns a DBList of objects that match the Where
        /// 
        /// Property to Match
        /// "=", "<", ">", "LIKE"
        /// Value to Match
        /// Type of the object to get (Must inherent from DBOBject)
        /// Connection String to use
        /// 
        static protected DBList Where(string property, string evaluator, object value, Type who_type, string connection)
        {
            DBList list = new DBList();

            //Find the Column Name and check that we have everything, if fail return the blank list
            string column_name = DBObject.GetColumn(property, who_type);
            if (column_name == string.Empty || evaluator == string.Empty || value == null) { return list; }
            //Check the evaluator, If fail, Return a blank list
            evaluator = DBObject.CheckEvaluator(evaluator);
            if (evaluator == string.Empty) { return list; }

            //Create the WHERE
            string query = DBObject.SelectQuery(who_type) + " WHERE " + column_name + " " + evaluator + "?value";

            using (MySqlConnection conn = new MySqlConnection(connection))
            {
                MySqlCommand cmd = new MySqlCommand(query, conn);
                cmd.Parameters.AddWithValue("?value", value);

                try
                {
                    conn.Open();
                    MySqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        DBObject obj = (DBObject)DBObject.FromReader(reader, who_type);
                        list.Add( obj );
                    }

                    conn.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("\n\nERROR:\n" + ex.Message);
                }
            }

            //No matter what happens, always return a valid list
            return list;
        }


/// 

        /// Accepted Evaluators: '<', '<=', '>', '>=', '=', '!=', 'LIKE', 'REGEXP', 'IS NOT'
        /// 
        /// 
        /// 
        static protected string CheckEvaluator(string evaluator)
        {
            //Lower and trim
            evaluator = evaluator.ToLower().Trim();

            switch (evaluator)
            {
                case "<":
                    return evaluator;
                    break;
                case "<=":
                    return evaluator;
                    break;
                case ">":
                    return evaluator;
                    break;
                case ">=":
                    return evaluator;
                    break;
                case "=":
                    return evaluator;
                    break;
                case "!=":
                    return evaluator;
                    break;
                case "like":
                    return evaluator;
                    break;
                case "regexp":
                    return evaluator;
                    break;
                case "is not":
                    return evaluator;
                    break;
                default:
                    return string.Empty;
            }
        }

And an example:

//Now lets try to get a list of users
            Console.WriteLine("\n\nTrying the Where Methiod");
            DBList users1 = DBObject.Where("id", "<=", 1313, typeof(DBUser), Utility.connMy);
            foreach (DBUser u4 in users1)
            {
                Console.WriteLine(u4.ID + " : " + u4.FullName);
            }

Next step.

I have two thoughts of what the next step should be. The first is the ability to return a collection of objects (maybe LINQ computable, but I'm not worried about that right now.) The other is that the object should be able to fetch it's own tags.

In my system, each object has tags associated with it. This allows objects to 'subscribe' to other objects. For example, we have a user object and a task object. The user can use tags to subscribe to a task (or many tasks). Many users can subscribe to a same task using this method. The tasks also have an optional prefix. This prefix can say things like; who owns the task, who can modify it, who can complete it, etc. (Just having the tag means you've subscribed and can thus view it.)
So when I get the object it really needs to go and fetch all if it's tags as well.

Because objects rarely have only a single tag, I should probably start with the collections. I'm going to call them lists from now on. These lists must inherit from CollectionBase class in order keep compatibility.

So I think what I want, is a DBList : CollectionBase object that can be used as the return value for static methods.

So I could do something like 

(DBList)DBObject.Where("Age", ">=", "25", typeof(DBUser), Utility.connMy);
and it will return a list of all the users whos age is 25 or older. Or

(DBList)DBObject.Where("Nake", "LIKE", "%chris%", typeof(DBUser), Utility.connMy);

I'm not opposed at all of passing the evaluator as text (">=", "LIKE", "=", "<", etc.) Where this does become a problem is when we need to specify more than a single Where clause. But I figure thats a bridge we can cross when we come to it.