Input needed - Dynamic Tables in dB

Posts   
1  /  2
 
    
EdH
User
Posts: 26
Joined: 10-Dec-2003
# Posted on: 10-Dec-2003 17:33:07   

Hi,

We have just pruchased LLBLGen Pro, and it is great. We are currently in the process of converting our current code to LLBL, and are sure it will save us many many hours of Dev time in the future.

Our SQL server dB is pretty normal, and simple, except for one item. We would like some pointers on how to deal with this from LLBL. We know we will have to write some custom code to make this work, but any advice is appreciated.

Here is the issue:

We store a lot of large Blobs (Several Gigs++) in our dB, which need to be archived at a project level. There are some other business reasons for this as well, but Removing the whole table when a project is complete is the main factor.

Here is what happens:

We have a Project Table. When a new Row is added, a trigger automatically creates a new ProjectBlob table which holds the actual data.

If the Project added were named MyProject, the trigger would create a table called MyProjectBlob. We control and always know what the blob table will be named.

There is also a MasterBlob table that holds the information (Such as File Name, Access Rights, TimeStamps, Notes, Etc..) about all the documents stored in all the ProjectXBlob tables.

All three of these have a FK that links things together.

What we would like to do is create a generic Entity wrapper for the Blob table, so we can always access the table from our code, by passing in the appropriate Blob table name (The Column names are always the same for all of them).

I hope all this makes sense. Any input appreciated.

Thanks!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 10-Dec-2003 18:04:26   

You can overwrite the table name in an entity by overwriting the SourceObjectName property of a field. Here's how:


string myBlobTable="FooBlob";
TheBlobEntity myBlob = new TheBlobEntity("Blob");
for(int i=0;i<myBlob.Fields.Count;i++)
{
    myBlob.Fields[i].SourceObjectName = myBlobTable;
}

After this, the fields inside that entity contain the tablename "FooBlob" and all actions will take place on that table.

The schema name stays the same, if you want to overwrite that one too, overwrite SourceSchemaName as well simple_smile

Overwriting it before you fetch anything (thus creating a clean entity) will cause that when you try to fetch entity data, it will be read from the table with the name you just wrote into the property.

Frans Bouma | Lead developer LLBLGen Pro
EdH
User
Posts: 26
Joined: 10-Dec-2003
# Posted on: 28-Jan-2004 17:27:07   

I tried this and the fetch doesn't work. I've verified that I'm using the right data and that data exists in the "new" table that I use in SourceObjectName.

I just do a"

entity.FetchUsingPK(myvalue);

I get a return value of false. Don't know what I'm doing wrong. Any ideas?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 28-Jan-2004 18:42:46   

Have you stepped into the code and verified what the query is? (step through the code until you're in the DAO class, the step over the line where the query is constructed, then type in the command window:

?((RetrievalQuery)selectQuery).ToString()

Frans Bouma | Lead developer LLBLGen Pro
EdH
User
Posts: 26
Joined: 10-Dec-2003
# Posted on: 28-Jan-2004 19:50:48   

Here's what's going on, I loop through the list originally and change the SourceObjectName for each field, like you said. Here's the sample:

for (int i=0;i<templateEntity.Fields.Count;i++) templateEntity.Fields[i].SourceObjectName = "test";

I then loop through again and Messagebox the SourceObjectName for each field. Like this:

for (int j=0;j<templateEntity.Fields.Count;j++) MessageBox.Show(templateEntity.Fields[j].SourceObjectName);

At this point, the SourceObjectName comes back as "test, which is what I expected. I then call fetchusingpk:

templateEntity.FetchUsingPK(myKey);

now, I reloop through the SourceObjectName as above and get returned the original values. What's up?

Also, the query in the DAO file shows up as being the original query.

Thanks

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 28-Jan-2004 20:05:16   

Oh darn, I know what's wrong... disappointed

The fetch routine in the DAO object creates a new Fields object, fills it and returns that fields object, which is then becoming the fields object in the entity, removing the fields object you changed...

For saving this is thus ok, but for reading this isn't working.

Hmm, this is not as smooth as I'd hoped... It creates a new fields object btw, to save the trouble of resetting it from previous data.

You could try the adapter template set for this, however I don't know how big your application is which needs this. At the moment I don't see a runtime-only solution with selfservicing, you have to alter the templates to make this work (which is in this case not that recommended because when I change something you can re-change the templates.)

Frans Bouma | Lead developer LLBLGen Pro
EdH
User
Posts: 26
Joined: 10-Dec-2003
# Posted on: 28-Jan-2004 22:57:03   

So, are you saying I'm SoL?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 29-Jan-2004 00:01:16   

What's 'SoL' ? confused

Frans Bouma | Lead developer LLBLGen Pro
EdH
User
Posts: 26
Joined: 10-Dec-2003
# Posted on: 29-Jan-2004 15:40:35   

An American phrase meaning basically 'dead in the water' but with a not so nice word... simple_smile

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 29-Jan-2004 17:09:01   

EdH wrote:

An American phrase meaning basically 'dead in the water' but with a not so nice word... simple_smile

Hmm simple_smile Well, I wished I could help you with mangling selfservicing functionality, but I think this is where the flexibility of selfservicing ends. disappointed

It requires a template change in the entity fetch code together with the DAO template. Basicly it should pass the entity fields object from the entity class to the Fetch routine in the DAO object instead of creating a new one in the DAO object.

I've added this to the TODO list so the next revision of selfservicing will no longer create a new set of fields in the DAO class but will use the fields passed in from the entity object. This will leave the oppertunity to modify the templates yourself so it works for the time being and when the revision of the templates are done (with some of the adapter logic ported to selfservicing) you can forget about your own changes and keep using the new templates.

Frans Bouma | Lead developer LLBLGen Pro
EdH
User
Posts: 26
Joined: 10-Dec-2003
# Posted on: 30-Jan-2004 15:20:31   

ok, I'll give that a shot. thanks.

netclectic avatar
netclectic
User
Posts: 255
Joined: 28-Jan-2004
# Posted on: 02-Feb-2004 15:11:58   

Otis wrote:

You could try the adapter template set for this, however I don't know how big your application is which needs this. At the moment I don't see a runtime-only solution with selfservicing, you have to alter the templates to make this work (which is in this case not that recommended because when I change something you can re-change the templates.)

Could you expand on this, i'd be interested to know how this could be achieved with the adapter template set. I'd like to know how to dynamically create entities/entity collections at runtime given a tablename.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-Feb-2004 15:26:28   

netclectic wrote:

Otis wrote:

You could try the adapter template set for this, however I don't know how big your application is which needs this. At the moment I don't see a runtime-only solution with selfservicing, you have to alter the templates to make this work (which is in this case not that recommended because when I change something you can re-change the templates.)

Could you expand on this, i'd be interested to know how this could be achieved with the adapter template set. I'd like to know how to dynamically create entities/entity collections at runtime given a tablename.

After some investigation, a design flaw was discovered in the current Adapter templates.

It should have been: - one set of protected methods in DataAccessAdapter which retrieves all persistence information for a given field/entity - You then could inherit from DataAccessAdapter and override this method, injecting your own code to manipulate persistence information on a per-call basis.

It now is however: - DataAccessAdapter class is reading persistence information on various places, also using a set of routines, but these are private.

When I wrote what you quoted, I mixed up multi-database access with dynamic table access as Ed wanted. That's my mistake, and I'm sorry.

The design flaw is scheduled to be fixed soon and will be released when the SelfServicing template update is released, which is expected to go beta within a week.

Frans Bouma | Lead developer LLBLGen Pro
netclectic avatar
netclectic
User
Posts: 255
Joined: 28-Jan-2004
# Posted on: 02-Feb-2004 15:36:13   

Ok, that makes sense.

I'll look forward to the updates.

ellis127
User
Posts: 8
Joined: 31-Oct-2003
# Posted on: 05-Feb-2004 03:26:45   

Otis wrote:

The fetch routine in the DAO object creates a new Fields object, fills it and returns that fields object, which is then becoming the fields object in the entity, removing the fields object you changed...

I haven't used Self Servicing in a while, but don't they take a EntityFactory?

Couldn't he just write his own factory that will produce the fields with the desired table name?

Ryan Ellis

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 05-Feb-2004 12:10:45   

ellis127 wrote:

Otis wrote:

The fetch routine in the DAO object creates a new Fields object, fills it and returns that fields object, which is then becoming the fields object in the entity, removing the fields object you changed...

I haven't used Self Servicing in a while, but don't they take a EntityFactory?

Couldn't he just write his own factory that will produce the fields with the desired table name?

No, that's the problem. simple_smile SelfServicing does take an entity factory to easily retrieve a custom entity factory for a getmulti call if you want to fill a collection of that type as well. However the problem is in the DAO classes, in the Fetch<type> to be exact. These routines create a new EntityFields instance using the hardcoded factory for the type of the dao class (so CustomerDAO.FetchCustomer() creates an EntityFields instance with customer fields). This is of course not correct, as the entity object already has an EntityFields object which was created during instantiation of the entity. Therefore, selfservicing has to be updated so that CustomerEntity.Fetch() passes its local EntityFields object to CustomerDAO.FetchCustomer() which then fills it instead of the other way around where CustomerDAO.FetchCustomer() creates a new EntityFields object and which it returns to its caller, which then makes that EntityFields object as its fields object, discarding the empty new one which was created at entity construction.

Frans Bouma | Lead developer LLBLGen Pro
bertcord avatar
bertcord
User
Posts: 206
Joined: 01-Dec-2003
# Posted on: 24-Feb-2004 23:34:18   

I need to do the following. I have a table for each day in the foramt of LogDataYYYYMMDD. I would like to at runtime set the table name to one wahtever the curent day is. for example for today LogData20040224.

Form reading hte above thread it appears I can not do this yet.

Otis wrote:

The design flaw is scheduled to be fixed soon and will be released when the SelfServicing template update is released, which is expected to go beta within a week.

Has this ben corrected in the 18-feb hotfix, or is it included in the beta runtime libraries?

Thanks Bert

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 25-Feb-2004 08:23:47   

This is corrected in the beta runtimes simple_smile

Frans Bouma | Lead developer LLBLGen Pro
IowaDave
User
Posts: 83
Joined: 02-Jun-2004
# Posted on: 22-Apr-2005 01:32:03   

Using adapter, how do I do this? I read the thread, but I didn't understand how to do it. We need to use dynamic names for some tables.

I am using the Feb 4, 2005 release

Thanks Dave

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 22-Apr-2005 08:46:25   

IowaDave wrote:

Using adapter, how do I do this? I read the thread, but I didn't understand how to do it. We need to use dynamic names for some tables.

I am using the Feb 4, 2005 release

Thanks Dave

Derive a class from DataAccessAdapter.

In there override the GetFieldPersistenceInfo* methods and call the base class' version first. In these overrides, you can then manipulate the table names. It's up to you how you determine when to change which name into which name.

Frans Bouma | Lead developer LLBLGen Pro
abdo
User
Posts: 10
Joined: 23-Feb-2009
# Posted on: 17-Mar-2009 09:32:36   

Dear Team,

I have done this, but when I'm trying to edit SourceObjectName property, what happens that I have compile time error because it's a read only property.

and I'll be grateful if you can attache, a pieced of code that doing that.
Thanks and Regards. Abdo.

abdo
User
Posts: 10
Joined: 23-Feb-2009
# Posted on: 17-Mar-2009 09:46:59   

thanks, it's working now i actually i was in the wrong direction.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 17-Mar-2009 09:48:59   

That's good to hear. simple_smile

abdo
User
Posts: 10
Joined: 23-Feb-2009
# Posted on: 17-Mar-2009 17:13:08   

Thanks Wala very much

ekcd
User
Posts: 5
Joined: 04-Oct-2010
# Posted on: 04-Oct-2010 15:38:58   

Dear Team,

I am using DataAccessAdapter and I am looking for a solution to change the tablename dynamically.

I tried to do this:

'In there override the GetFieldPersistenceInfo* methods and call the base class' version first. In these overrides, you can then manipulate the table names. It's up to you how you determine when to change which name into which name'

But unfortunately I was not sucessfull. Do you have an example where I can see what to do ?

Greetings from Germany

Werner

1  /  2