On Tuesday, August 14, 2012 1:43:18 PM UTC-4, Dennis Lee Bieber wrote:
>
> On Mon, 13 Aug 2012 21:58:59 -0700 (PDT), Jeff Dickens
> <drea...@gmail.com <javascript:>> declaimed the following in
> gmane.comp.python.django.user:
>
> > Hi all. I have a number of models, each of which is based on an
> abstract
> > class that includes a Timestamp field. The model definitions look like
> > this:
> >
> > class Md_model(models.Model):
> > Timestamp =
> > models.DateTimeField(auto_now_add=True,unique=True,db_index=True)
> > def __unicode__(self):
> > return self.Timestamp.strftime('%Y %m %d %H:%M')
> > def as_dict(self):
> > return(model_to_dict(self))
> > class Meta:
> > abstract = True
> >
> > class md_10_1(Md_model):
> > name='md_10_1'
> > dataSource = 'http://192.168.0.10/auto/001'
> > n1 = models.DecimalField(max_digits=10,decimal_places=3)
> > n2 = models.DecimalField(max_digits=10,decimal_places=3)
> > n3 = models.DecimalField(max_digits=10,decimal_places=3)
> >
> > class md_10_2(Md_model):
> > name='md_10_2'
> > dataSource = 'http://192.168.0.10/auto/002'
> > n4 = models.DecimalField(max_digits=10,decimal_places=3)
> > n5 = models.DecimalField(max_digits=10,decimal_places=3)
> >
> This example makes me think the design is faulty... Especially if
> you mean you have more tables than just the two...
>
It's not. Read on...
>
> Though I think better in SQL -- are "name" and "dataSource" items
> that would be stored in the database? Right now, all they seem to do is
> duplicate information as to which "model" the record represents.
>
No, they are not items that are stored in the database. They exist because
the script that populates the table (every minute) is generalized to handle
an unlimited number of data models. It uses introspection for everything -
as DRY as it can possibly be. Otherwise it would very quickly become
unmanageable.
>
> Given just your example I'd probably have created just one table
> containing all of n1..n5, with option to permit Null values, and coded
> to do INSERT if the timestamp didn't exist, and UPDATE if the timestamp
> was already in the table.
>
> There are now 8 data models and I have to be able to add more over time
without having to reprocess the (voluminous) data that has been logged into
the existing models.
> If you do mean you have half a dozen or more all similar I'd try
> to
> isolate back to the form that would support the most... If all the data
> fields are identical (all 10.3 decimal) say... (Again, I'm going to use
> pseudo SQL)
>
> create table Sources
> (
> ID integer auto increment primary key,
> timestamp datetime,
> source char(80), #whatever length you need
> model char(20) #ditto
> );
>
> create table Fields
> (
> ID integer auto increment primary key,
> sourceID integer foreign key Source (ID),
> fieldID integer,
> fieldValue decimal
> )
>
> These wouldn't fit as regular Django form without a lot of
> logic...
> Instead of /n/ tables looking like
>
> timestamp n1 n2 n3 ...
>
> you'd have
>
> 1 timestamp http://...
> 2 timestamp http://...
>
> and
>
> 1 1 1 value
> 2 1 2 value
> 3 1 3 value
> 4 2 1 value
> 5 2 2 value
> ...
>
> This schema allows for any number of identical datatype values per
> "record", but putting them /into/ record order requires looping over the
> result set.
>
>
>
> >
> > What I want to do is join them all (actually some programatically
> defined
> > subset of them) into a single query set or some other data structure
> that I
> > can pass into the template. This set would have only one Timestamp
> field,
> > and all of the other fields from all of the other models that are to be
> > joined. She thre result would have Timestamp, n1, n2, n3, n4, n5 in the
> > example above,
>
> INNER, LEFT OUTER, or RIGHT OUTER; if you needed both OUTER
> conditions in one result set you'd have to run a UNION wrapped by a
> SELECT DISTINCT
>
> Raw SQL might look like
>
> select distinct * from
> (select md_10_1.timestamp as tstamp, n1, ..., n5 from md_10_1
> left join md_10_2 on md_10_1.timestamp = md_10_2.timestamp
> union
> select md_10_2.timestamp as tstamp, n1, ..., n5 from md_10_1
> right join md_10_2 on md_10_1.timestamp =
> md_10_2.timestamp)
> where tstamp > starttime and tstamp < endtime
> order by tstamp
>
> {If the DBMS doesn't implement RIGHT JOIN make it a LEFT and swap the
> table order around the clause}
> --
> Wulfraed Dennis Lee Bieber AF6VN
> wlf...@ix.netcom.com <javascript:>
> HTTP://wlfraed.home.netcom.com/
>
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/django-users/-/dSba_tVSJxEJ.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
