Thinking through object/text fields: Findings summarized, advice solicited

classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
Having just started looking at object fields and learned a few things, I
have a few thoughts. For background, I've been using 4D's C_OBJECT and
ARRAY OBJECT for some time, and now I'm looking at object fields. I think
that I've gotten a grip on some of the details through experimentation,
help from people on this list, and help from people on the 4D Forums in
France. If I say something that makes no sense or is wrong, I'd would
really like to hear about it. The more accurate my picture of how things
work, the more intelligently I can make choices. For the record, some of my
requirements are probably atypical, but knowing about the underlying
performance and storage behaviors in 4D is useful knowledge for most people.

For background (corrections and additions invited), I think this is
more-or-less the story:

* 4D stores all textual data as UTF16.

* 4D's native JSON features aren't a complete JSON implementation. The
commands only support a limited/specific set of JSON
styles/formats/patterns. These patterns are valid JSON, but they're not
particularly space optimized. This isn't a bug, it's just how it is.

* Object fields are basically text fields with extra constraints *and*
extra features. So, the storage requirements for the same data are roughly
(or exactly?) the same for the same JSON.

* The extra constraint on object fields is that they can only store JSON
objects that are parsable by 4D. For example, you can't store an ARRAY
OBJECT without injecting it into a C_OBJECT wrapper first.

* The extra feature on object fields is the index. There's a special kind
of index available (the details are secret) that makes lookups by key fast
and somewhat flexible.

* Text and object fields may be different internally, or they may be the
same...it doesn't matter to us as 4D developers - we just care about how
they work and what we can do with them.

* Unless you need the object field index & search, there is exactly *no
reason* to use an object field instead of a text field.

* Text fields give you a keyword search option which object fields lack.

* If you're storing something like some key names and numbers in an object
field, the storage requirements are going to be *much* higher than storing
the same data in real numeric fields for two reasons:

-- The key name is repeated in each object
-- The value is stored as text - which makes numerics take 4x times as much
space.

* The previous point is _only_ a concern to people (like me) that are
trying to store and process a lot of data. Also (in my case), I'm storing
fully prepared JSON for export to/interaction with other systems. So, using
tables as a JSON repository, not because there is any reason to search the
JSON. The JSON is an output product. In such a case, it makes sense to push
the data out to something where you can use a smaller character set
(Latin1, UTF8), if you can get away with it. If you're storing Chinese
surnames, you need UTF16 (I think), if you're storing numbers, you may only
need Latin1.

* If you're dealing with some sane number of records, space issues are
likely not a big deal for you and it's absolutely not worth worrying about.

* Standard fields are directly searchable, easy to display, easy to sort
by, simple to export, etc. If you're burying data inside of an object
instead, you need a good reason to justify the costs of making the data
harder to access. I'd love to hear what people have found as good reasons
in their own work.

Does that all sound about right? Am I missing reasons why I would want to
use object fields. vs. text fields? Any other technical details that people
have learned or figured out?

Thanks.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
On Jul 14, 2017, at 4:21 AM, David Adams via 4D_Tech <[hidden email]> wrote:
>
> Does that all sound about right? Am I missing reasons why I would want to
> use object fields. vs. text fields? Any other technical details that people
> have learned or figured out?

I’d add that using an object field is only meaningful if your objects have a variable structure, that is, objects differ among records not just by contents but have a variable internal structure.
If all objects in all records share a common structure, that is, they all have the same attributes (even if some are optional and may be empty/null), then go with standard 4D fields. As you pointed out, they take a lot less space, can be individually indexed/keyworded, are easier to deal with, their usage is syntax checked by compiler, etc..

Just my $.02
--
Julio Carneiro
[hidden email]



**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
Julio,

Thanks for the excellent points you added to the first post.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
In reply to this post by 4D Tech mailing list
I agree... If the structure is fixed and the values need to be visualised,I:

Store in specific arrays.
Arrays to blob.
Blob to field (or Document).

To visualise:
Blob from Document or field
Blob to arrays
Arrays to Listbox
and my QuerySort component to search etc

On Fri, Jul 14, 2017 at 10:49 AM, Julio Carneiro via 4D_Tech <
[hidden email]> wrote:

> On Jul 14, 2017, at 4:21 AM, David Adams via 4D_Tech <[hidden email]>
> wrote:
> >
> > Does that all sound about right? Am I missing reasons why I would want to
> > use object fields. vs. text fields? Any other technical details that
> people
> > have learned or figured out?
>
> I’d add that using an object field is only meaningful if your objects have
> a variable structure, that is, objects differ among records not just by
> contents but have a variable internal structure.
> If all objects in all records share a common structure, that is, they all
> have the same attributes (even if some are optional and may be empty/null),
> then go with standard 4D fields. As you pointed out, they take a lot less
> space, can be individually indexed/keyworded, are easier to deal with,
> their usage is syntax checked by compiler, etc..
>
> Just my $.02
> --
> Julio Carneiro
> [hidden email]
>
>
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************
>



--
Jim Dorrance
[hidden email]
[hidden email]
www.4d.dorrance.eu

PS: If you know of anyone that needs an experienced 4D programmer to add
energy and experience to their team, please let me know. I have
experience in many areas. Reasonable rates. Remote or Paris only.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
In reply to this post by 4D Tech mailing list
If storage space is a primary constraint and object indexing is not needed, you can use a blob to store as UTF-8 text.

VARIABLE TO BLOB with object uses UTF-8 text (tested 15.4, not 16).

In your example where you basically wanted to store an object array directly, you could stringify the array then text to blob field with UTF-8.

John DeSoi, Ph.D.



> On Jul 13, 2017, at 11:21 PM, David Adams via 4D_Tech <[hidden email]> wrote:
>
> * The previous point is _only_ a concern to people (like me) that are
> trying to store and process a lot of data. Also (in my case), I'm storing
> fully prepared JSON for export to/interaction with other systems. So, using
> tables as a JSON repository, not because there is any reason to search the
> JSON. The JSON is an output product. In such a case, it makes sense to push
> the data out to something where you can use a smaller character set
> (Latin1, UTF8), if you can get away with it. If you're storing Chinese
> surnames, you need UTF16 (I think), if you're storing numbers, you may only
> need Latin1.

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
John,

Good point thanks. That could be a meaningful help in a situation where 4D
is being used to generate JSON blocks to pump out to another system. That's
exactly what I'm thinking about. 4D doesn't have built in data analysis or
visualization tools (well, there is sort of graph feature, but it's just a
toy.) Anyway, there are *tons* of amazing visualization tools and platforms
out there now so pumping out CSV/TSV/JSON is sometimes the whole goal.
Saving 50% on the storage space is a big win.

Thanks!
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
In reply to this post by 4D Tech mailing list
David,

Given that 4D and Wakanda use the same data engine (from my limited understanding of Wakanda, I believe they do), have you spoken to anyone on the Wakanda team about your concerns/questions?

Just thought I would throw that into the mix.

Thanks,
Walt Nelson (Seattle)
New stuff coming!
www.foundationshell.com
[hidden email]

> On Jul 13, 2017, at 10:21 PM, David Adams via 4D_Tech <[hidden email]> wrote:
>
> Having just started looking at object fields and learned a few things, I
> have a few thoughts. For background, I've been using 4D's C_OBJECT and
> ARRAY OBJECT for some time, and now I'm looking at object fields. I think
> that I've gotten a grip on some of the details through experimentation,
> help from people on this list, and help from people on the 4D Forums in
> France.

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
In reply to this post by 4D Tech mailing list
I think just one more thing and that is a caveat. You can NOT access object
fields with SQL. You would have to write a sql fn call that would return
the data to you.

I for one will not be using object fields until they become available
through sql. As much as I like the idea, I move to much data between
systems using SQL.

Regards

Chuck

On Thu, Jul 13, 2017 at 11:21 PM, David Adams via 4D_Tech <
[hidden email]> wrote:

> Having just started looking at object fields and learned a few things, I
> have a few thoughts. For background, I've been using 4D's C_OBJECT and
> ARRAY OBJECT for some time, and now I'm looking at object fields. I think
> that I've gotten a grip on some of the details through experimentation,
> help from people on this list, and help from people on the 4D Forums in
> France. If I say something that makes no sense or is wrong, I'd would
> really like to hear about it. The more accurate my picture of how things
> work, the more intelligently I can make choices. For the record, some of my
> requirements are probably atypical, but knowing about the underlying
> performance and storage behaviors in 4D is useful knowledge for most
> people.
>
> For background (corrections and additions invited), I think this is
> more-or-less the story:
>
> * 4D stores all textual data as UTF16.
>
> * 4D's native JSON features aren't a complete JSON implementation. The
> commands only support a limited/specific set of JSON
> styles/formats/patterns. These patterns are valid JSON, but they're not
> particularly space optimized. This isn't a bug, it's just how it is.
>
> * Object fields are basically text fields with extra constraints *and*
> extra features. So, the storage requirements for the same data are roughly
> (or exactly?) the same for the same JSON.
>
> * The extra constraint on object fields is that they can only store JSON
> objects that are parsable by 4D. For example, you can't store an ARRAY
> OBJECT without injecting it into a C_OBJECT wrapper first.
>
> * The extra feature on object fields is the index. There's a special kind
> of index available (the details are secret) that makes lookups by key fast
> and somewhat flexible.
>
> * Text and object fields may be different internally, or they may be the
> same...it doesn't matter to us as 4D developers - we just care about how
> they work and what we can do with them.
>
> * Unless you need the object field index & search, there is exactly *no
> reason* to use an object field instead of a text field.
>
> * Text fields give you a keyword search option which object fields lack.
>
> * If you're storing something like some key names and numbers in an object
> field, the storage requirements are going to be *much* higher than storing
> the same data in real numeric fields for two reasons:
>
> -- The key name is repeated in each object
> -- The value is stored as text - which makes numerics take 4x times as much
> space.
>
> * The previous point is _only_ a concern to people (like me) that are
> trying to store and process a lot of data. Also (in my case), I'm storing
> fully prepared JSON for export to/interaction with other systems. So, using
> tables as a JSON repository, not because there is any reason to search the
> JSON. The JSON is an output product. In such a case, it makes sense to push
> the data out to something where you can use a smaller character set
> (Latin1, UTF8), if you can get away with it. If you're storing Chinese
> surnames, you need UTF16 (I think), if you're storing numbers, you may only
> need Latin1.
>
> * If you're dealing with some sane number of records, space issues are
> likely not a big deal for you and it's absolutely not worth worrying about.
>
> * Standard fields are directly searchable, easy to display, easy to sort
> by, simple to export, etc. If you're burying data inside of an object
> instead, you need a good reason to justify the costs of making the data
> harder to access. I'd love to hear what people have found as good reasons
> in their own work.
>
> Does that all sound about right? Am I missing reasons why I would want to
> use object fields. vs. text fields? Any other technical details that people
> have learned or figured out?
>
> Thanks.
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************




--
-----------------------------------------------------------------------------------------
 Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064
 Informed Solutions, Inc.
 Brookline, MA 02446 USA Registered 4D Developer
       Providers of 4D, Sybase & SQL Sever connectivity
          http://www.informed-solutions.com
-----------------------------------------------------------------------------------------
This message and any attached documents contain information which may be
confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution, disclosure,
printing, copying, storage, modification or the taking of any action in
reliance upon this transmission is strictly prohibited.  Delivery of this
message to any person other than the intended recipient shall not
compromise or waive such confidentiality, privilege or exemption
from disclosure as to this communication.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
In reply to this post by 4D Tech mailing list
> * Unless you need the object field index & search, there is exactly *no
> reason* to use an object field instead of a text field.
We have a need for customers to create their own fields - dates, numbers,
texts, possibly pictures.
Haven't actually done it yet, but we expect that Object fields will be
ideal for our needs.
Some of the UI capabilities of Object fields look great.

(We have had a nicely normalized, but text data type only version for many
years).

Jim Hays
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
In reply to this post by 4D Tech mailing list
Julio,
I don't think 'variable structure' is necessarily the defining element.
It's certainly good for that but now that we can search on these fields
they're also good for situations where you want to allow each record to
have some large number of fields most of which won't ever be used but need
to be there. It's just sad to see a table in an old db with a hundred
fields, 20 or so marked "unused" and only 5 or 6 of which are actually
populated.

Perhaps this is what you are saying and I'm just reading too narrowly (it's
been that sort of week).

On Fri, Jul 14, 2017 at 1:49 AM, Julio Carneiro via 4D_Tech <
[hidden email]> wrote:

> On Jul 14, 2017, at 4:21 AM, David Adams via 4D_Tech <[hidden email]>
> wrote:
> >
> > Does that all sound about right? Am I missing reasons why I would want to
> > use object fields. vs. text fields? Any other technical details that
> people
> > have learned or figured out?
>
> I’d add that using an object field is only meaningful if your objects have
> a variable structure, that is, objects differ among records not just by
> contents but have a variable internal structure.
> If all objects in all records share a common structure, that is, they all
> have the same attributes (even if some are optional and may be empty/null),
> then go with standard 4D fields. As you pointed out, they take a lot less
> space, can be individually indexed/keyworded, are easier to deal with,
> their usage is syntax checked by compiler, etc..
>

--
Kirk Brooks
San Francisco, CA
=======================

*The only thing necessary for the triumph of evil is for good men to do
nothing.*

*- Edmund Burke*
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
In reply to this post by 4D Tech mailing list
Right, good point about the SQL goof. I have a little test window for SQL
statements I use regularly for data exploration. Handy!  Double-click a
table name and automatically get a pre-built SELECT, etc. I had to update
it to scan the list of fields for object field types and exclude them. Pity.

This is a good place to mention there's an existing feature request on this
subject. (There may be multiple requests, actually.) Please vote:

Object Field Support in s?QL
http://forums.4d.fr/Post/EN/18268404/1/18268405
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
In reply to this post by 4D Tech mailing list
On Sat, Jul 15, 2017 at 7:36 AM, Kirk Brooks via 4D_Tech <
[hidden email]> wrote:

>
> Perhaps this is what you are saying and I'm just reading too narrowly (it's
> been that sort of week).
>

I'm not sure, but I was reading Julio's comment as  something akin to
"David, don't do what you were offering as an example recently."
Specifically, storing a zillion copies of what amounts to a record stuffed
into an object and then an object field. Like, if you've *always* got first
name, middle name, last name, put them in fields! Don't put them in an
object that you then put into an object field. You can end up spending most
of your space storing redundant data, namely the keys.

Now, if you have a bunch of different JSON formats, that's a different
story. Some records might have first name, middle name, last name and
others have interval length, start date, and end date. I don't know, I'm
just making something up.

The other day I started my experimentation on object field with some code
I'd written. It generated zillions of objects, some in an array and some as
summaries of the array. I thought, hey! I'll store these and then can bring
the results back for different sorts of analysis later. Sure, why not? I
stuffed them into object fields and they were absolutely ginormous. So, I'd
have a record with a field with 96 array elements (24 hours broken into 15
minute buckets with stats for that interval)...and most of the data is just
the structure of the data...which is the same for every record. It's
obviously a lot more efficient to store the findings in proper tables,
boiled down to a more compact representation, or boiled down to a more
compact representation and stuffed into a blob as UTF8, or exported and
compressed. Whatever.

So, I was talking about what is called a "stupid example." I believe that
is the correct, contemporary term of art...but it's exactly the sort of
mistake someone else might make, so it's worth thinking about. If you have
an entirely regular structure, why would you store it in an object field? I
have a weird situation where the goal is to store JSON itself, but leave
that out. What is the point of storing your data in an object field?
(Thomas Maul and others also made a point like this on the Forums, unless
I'm mis-paraphrasing.) It doesn't generally make any sense. Here:

{"total":5}

If you store this in every single record in a table, what do you gain?
Well, nothing, so far as I can see. instead, put it in a regular field
named "total". Then you don't need to store the string "total" with every
record (the field name itself is the 'key') and the number is stored
directly as a number (more compact, easier to get into arrays, etc.)

I really have no idea how people are using object fields. 4D has some
demos. I've asked several times in different venues for several months and
have had very little response. So, I suspect that people mostly haven't had
a chance to get to V16 and use them yet. That makes this a good time to
think them through.

More thoughts and comments wanted! It would be helpful to everyone to hear
real-world stories about how you're finding object fields helpful. For my
money, I'm not likely to use them much. But, like any tool, it's good to
know how they work so that you can fit them to purpose. When there's a good
time to use an object field, I'll be glad to have already thought the
subject through enough to recognize the situation immediately.

Just to keep things in one place, here's where I can imagine using object
fields:

* Storing prefs, etc. Don't know if this is "proper" but it sure feels like
a good idea. I often use external JSON files for configuration data anyway.
Very handy.

* Storing messaging data. If I were to write another distributed,
record-based, task or message queue in 4D, I'd stuff the job/message data
into an object field in a heartbeat. That's a perfect use.

Where I won't use them, of don't expect to, is for making what amount to
"repeating fields." You know

Quarter_1_Total
Quarter_2_Total
Quarter_3_Total
Quarter_4_Total

...and then the financial years changes and you have to push figures down,
etc. That's just a bad design. I'm also now terribly likely to use them for
things like "aunt Mildred's phone number." I'd have a related table with a
keyword (key) and whatever the value is (value.) Same idea, different
implementation.

One tricky area that I suspect will come up for people are "type of"
relationships that don't work neatly in the relational model. As an
example, imagine that you've got a bunch of health care facilities. Some
are hospitals, some are clinics:

[Facility]
Type_    "Hosptial" or "Clinic"

...now some fields only make sense for a hospital and some only make sense
for a clinic. Then again, a whole bunch of fields are identical for both.
What's the right design? I don't know. I'm not even sure what this
situation is called as a technical design problem. I do know that classical
ERD syntax has no vocabulary for it. (In contrast, IDEFX and UML both do.)
For my money, this is the one place where the relational model is awkward
enough to bug me. Otherwise, the standard relational model has *massive*
benefits over stuffing a bunch of stuff into a keyword soup.

Right, done rambling...for now.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
Although I use object extensively in code, I don’t use them in very many fields. I do have several “settings” fields and find object type fields very useful here. Being able to add settings in the future without changing the structure is great and it seems like less work to have an extensive, but organized list of settings in an object than other methods I’ve tried in the past.

The only other object type field I use is one for tracking the breed of an cow. Each cow can be composed of 0..n breeds, each with a percentage of the total. This could have been in another table, but in this case it worked very nicely to put this information into an object field for each animal.

I guess the bulk of the data I work with in current projects tends to be pretty structured, so regular fields work best. I worked on a project many years ago where object fields would have been perfect. The users could create their own “fields”. Lots of them. I think Pat Bensky does something like that, doesn’t she?

Anyway, that’s all I do with them.

--
Cannon.Smith
Synergy Farm Solutions Inc.
Hill Spring, AB Canada
403-626-3236
<[hidden email]>
<www.synergyfarmsolutions.com>


> On Jul 14, 2017, at 4:15 PM, David Adams via 4D_Tech <[hidden email]> wrote:
>
> More thoughts and comments wanted! It would be helpful to everyone to hear
> real-world stories about how you're finding object fields helpful.

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
In reply to this post by 4D Tech mailing list
Correct, David, you got the gist of my comment.

Jim Hays gave a great example where I can see object fields excel. If your app requires user defined fields, then an object field is just perfect for that. I’ve had similar situations in the past and used tab-separated text fields first, and then blob fields when that became available. Now object fields would be much much better because you can query on those ‘user defined’ fields, a great improvement over previous implementations.

julio

> On Jul 14, 2017, at 11:15 PM, David Adams via 4D_Tech <[hidden email]> wrote:
>
> On Sat, Jul 15, 2017 at 7:36 AM, Kirk Brooks via 4D_Tech <
> [hidden email]> wrote:
>
>>
>> Perhaps this is what you are saying and I'm just reading too narrowly (it's
>> been that sort of week).
>>
>
> I'm not sure, but I was reading Julio's comment as  something akin to
> "David, don't do what you were offering as an example recently."
> Specifically, storing a zillion copies of what amounts to a record stuffed
> into an object and then an object field. Like, if you've *always* got first
> name, middle name, last name, put them in fields! Don't put them in an
> object that you then put into an object field. You can end up spending most
> of your space storing redundant data, namely the keys.
>
> Now, if you have a bunch of different JSON formats, that's a different
> story. Some records might have first name, middle name, last name and
> others have interval length, start date, and end date. I don't know, I'm
> just making something up.
>
> The other day I started my experimentation on object field with some code
> I'd written. It generated zillions of objects, some in an array and some as
> summaries of the array. I thought, hey! I'll store these and then can bring
> the results back for different sorts of analysis later. Sure, why not? I
> stuffed them into object fields and they were absolutely ginormous. So, I'd
> have a record with a field with 96 array elements (24 hours broken into 15
> minute buckets with stats for that interval)...and most of the data is just
> the structure of the data...which is the same for every record. It's
> obviously a lot more efficient to store the findings in proper tables,
> boiled down to a more compact representation, or boiled down to a more
> compact representation and stuffed into a blob as UTF8, or exported and
> compressed. Whatever.
>
> So, I was talking about what is called a "stupid example." I believe that
> is the correct, contemporary term of art...but it's exactly the sort of
> mistake someone else might make, so it's worth thinking about. If you have
> an entirely regular structure, why would you store it in an object field? I
> have a weird situation where the goal is to store JSON itself, but leave
> that out. What is the point of storing your data in an object field?
> (Thomas Maul and others also made a point like this on the Forums, unless
> I'm mis-paraphrasing.) It doesn't generally make any sense. Here:
>
> {"total":5}
>
> If you store this in every single record in a table, what do you gain?
> Well, nothing, so far as I can see. instead, put it in a regular field
> named "total". Then you don't need to store the string "total" with every
> record (the field name itself is the 'key') and the number is stored
> directly as a number (more compact, easier to get into arrays, etc.)
>
> I really have no idea how people are using object fields. 4D has some
> demos. I've asked several times in different venues for several months and
> have had very little response. So, I suspect that people mostly haven't had
> a chance to get to V16 and use them yet. That makes this a good time to
> think them through.
>
> More thoughts and comments wanted! It would be helpful to everyone to hear
> real-world stories about how you're finding object fields helpful. For my
> money, I'm not likely to use them much. But, like any tool, it's good to
> know how they work so that you can fit them to purpose. When there's a good
> time to use an object field, I'll be glad to have already thought the
> subject through enough to recognize the situation immediately.
>
> Just to keep things in one place, here's where I can imagine using object
> fields:
>
> * Storing prefs, etc. Don't know if this is "proper" but it sure feels like
> a good idea. I often use external JSON files for configuration data anyway.
> Very handy.
>
> * Storing messaging data. If I were to write another distributed,
> record-based, task or message queue in 4D, I'd stuff the job/message data
> into an object field in a heartbeat. That's a perfect use.
>
> Where I won't use them, of don't expect to, is for making what amount to
> "repeating fields." You know
>
> Quarter_1_Total
> Quarter_2_Total
> Quarter_3_Total
> Quarter_4_Total
>
> ...and then the financial years changes and you have to push figures down,
> etc. That's just a bad design. I'm also now terribly likely to use them for
> things like "aunt Mildred's phone number." I'd have a related table with a
> keyword (key) and whatever the value is (value.) Same idea, different
> implementation.
>
> One tricky area that I suspect will come up for people are "type of"
> relationships that don't work neatly in the relational model. As an
> example, imagine that you've got a bunch of health care facilities. Some
> are hospitals, some are clinics:
>
> [Facility]
> Type_    "Hosptial" or "Clinic"
>
> ...now some fields only make sense for a hospital and some only make sense
> for a clinic. Then again, a whole bunch of fields are identical for both.
> What's the right design? I don't know. I'm not even sure what this
> situation is called as a technical design problem. I do know that classical
> ERD syntax has no vocabulary for it. (In contrast, IDEFX and UML both do.)
> For my money, this is the one place where the relational model is awkward
> enough to bug me. Otherwise, the standard relational model has *massive*
> benefits over stuffing a bunch of stuff into a keyword soup.
>
> Right, done rambling...for now.
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************

--
Julio Carneiro



--
Julio Carneiro
[hidden email]



**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
> On Mon, Jul 17, 2017 at 6:50 AM, Julio Carneiro via 4D_Tech <
[hidden email]> wrote:
> Correct, David, you got the gist of my comment.

> Jim Hays gave a great example where I can see object fields excel. If
your app
> requires user defined fields, then an object field is just perfect for
that.
> I’ve had similar situations in the past and used tab-separated text fields
> first, and then blob fields when that became available. Now object fields
> would be much much better because you can query on those ‘user defined’
> fields, a great improvement over previous implementations.

Yes, I can easily see that object fields are the best current, native way
to handle user defined fields in 4D. I'm getting the impression that is
pretty much their entire purpose, or close to it. I can see how that is
value to some OEMs (I'd expect DataWorks to use it as they've always found
UDFs a competitive advantage for them.) But for me? I've never had that
requirement and don't expect to.

The uses I have for storing JSON sound like they're just not on the list of
problems that object fields in 4D are *intended* to solve. So, it's not a
great match. I had a quick look at what PostgreSQL offers and their 9.4
release (2.5+ years ago) has a couple of native options. They've got a
'json' field type and a 'jsonb' field type. The first is for straight
validation of JSON as it is, but with engine-level validation. So,
basically a text field that can store any valid JSON that kicks an error if
you put something else in. The jsonb type restructures the JSON into a
custom binary format for index optimization. Not to save space, but to make
searches possible, flexible, and fast. This is integrated with some ongoing
work they've been going with full-text searching, a la Solr. (The GIN
index, specifically.) So, super optimized to complete for a lot of the
spaces that MongoDB plays in. PostgreSQL supports some syntactic
conventions to allow for indexed searches *within* nested JSON, very nice.

Out in that wider world, saving raw JSON for log analysis, messaging, API
analysis, pre-calculations, etc. is all entirely ordinary. In an all 4D
world, apparently not so much. In an all 4D world, those uses seem to be
"not recommended." In a world where 4D is part of a larger pipeline, some
of the other tools sound like a more natural fit for JSON storage.

It took me about 45 minutes to get a really good sense of the options in
PostgreSQL along with costs, limitations, trade-offs, and competitive
comparisons. The information was available at whatever level of technical
detail I could have been interested in...and then some. It's pretty fun to
read about this stuff.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
Note: PostgreSQL automatically and transparently compresses large variable length column values.

https://www.postgresql.org/docs/9.6/static/storage-toast.html

John DeSoi, Ph.D.


> On Jul 17, 2017, at 12:10 AM, David Adams via 4D_Tech <[hidden email]> wrote:
>
> The jsonb type restructures the JSON into a
> custom binary format for index optimization. Not to save space, but to make
> searches possible, flexible, and fast.

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
Better and better, thanks John. That's great, I'd seen mention of TOAST but
wasn't sure how it fit in. Now after 1 hour total, I'm feeling like a
PostgreSQL smartie ;-)

Since we're on this (not 4D) subject, I'll just mention a key detail about
jsonb queries in PostgreSQL:

   They cannot take advantage of the query optimizers frequency tables.

I don' think that 4D has frequency tables (or at least not much, cluster
B-Trees might do much the same.) The idea is to maintain a list of unique
values in a table and their occurrence. This lets the query planner
optimize individual queries based on the values in your query statement and
the values in your database. So, the database engine maintains table
statistics per table, to improve performance. Sweet.

Rob has a PostgreSQL plug-in for 4D:

http://www.pluggers.nl/product/postgresql-plugin/

I checked the manual and it made no mention of json or jsonb field types. I
wrote Rob and he says that passing the data as text works just fine with
PostgreSQL. PostgreSQL has long had an extensible type system and it's able
to coerce text values into more specific types on the server.  (Assuming I
understood Rob correctly and am not mis-quotting.)

My biggest problem with PostgreSQL is the relative lack of nice front-end
tools. My beloved SequelPro for MySQL has long hinted at a PostgreSQL
version, but it doesn't seem to be there yet. For MySQL though, it's great
(and free):

http://www.sequelpro.com/

Navicat can do more (stored procedures, copy data across structures, better
trigger support, etc.) but, dang, that "Look Ma! I wrote it in Java!" UI is
just so ugly. I'll pay for it, I'll use it, I'll be grateful...but I won't
be loving it.

Any recommendations for good PostgreSQL tools?
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
I bought Navicat 11 a few months ago after they made some nice improvements in the SQL editor. I thought it was the most Mac friendly of any PostgreSQL tools. Shortly thereafter Navicat 12 was released. I'm pretty sure it is not Java, but I think they switched to some other GUI library to make their cross platform work easier. It seems not as Mac friendly and I think they did some other things to make it less usable. I have version 12, but I'm still using version 11. They seem to be receptive to feedback, so you might send them some comments on why you think their Mac implementation looks like it is done in Java.

John DeSoi, Ph.D.



> On Jul 17, 2017, at 8:40 AM, David Adams via 4D_Tech <[hidden email]> wrote:
>
> Navicat can do more (stored procedures, copy data across structures, better
> trigger support, etc.) but, dang, that "Look Ma! I wrote it in Java!" UI is
> just so ugly. I'll pay for it, I'll use it, I'll be grateful...but I won't
> be loving it.
>
> Any recommendations for good PostgreSQL tools?

**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
Hi John,
I haven't done very much communicating from 4D to a SQL database so this is
a very naive question - but could you talk about how a tool like Navicat
helps you with 4D?

On Mon, Jul 17, 2017 at 8:39 AM, John DeSoi via 4D_Tech <
[hidden email]> wrote:

> I bought Navicat 11 a few months ago after they made some nice
> improvements in the SQL editor. I thought it was the most Mac friendly of
> any PostgreSQL tools.
> ​...
> > On Jul 17, 2017, at 8:40 AM, David Adams via 4D_Tech <
> [hidden email]> wrote:
> >
> > Navicat can do more (stored procedures, copy data across structures,
> better
> > trigger support, etc.) but, dang, that "Look Ma! I wrote it in Java!" UI
> is
> > just so ugly.
>
--
Kirk Brooks
San Francisco, CA
=======================

*The only thing necessary for the triumph of evil is for good men to do
nothing.*

*- Edmund Burke*
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Thinking through object/text fields: Findings summarized, advice solicited

4D Tech mailing list
In reply to this post by 4D Tech mailing list
On Mon, Jul 17, 2017 at 5:40 AM, David Adams via 4D_Tech <
[hidden email]> wrote:

>
> My biggest problem with PostgreSQL is the relative lack of nice front-end
> tools. My beloved SequelPro for MySQL has long hinted at a PostgreSQL
> version, but it doesn't seem to be there yet. For MySQL though, it's great
> (and free):
>
> http://www.sequelpro.com/
>
> Navicat can do more (stored procedures, copy data across structures, better
> trigger support, etc.) but, dang, that "Look Ma! I wrote it in Java!" UI is
> just so ugly. I'll pay for it, I'll use it, I'll be grateful...but I won't
> be loving it.
>
> Any recommendations for good PostgreSQL tools?
>


Agreed on how great Sequel Pro is.

For Postgres, I use PostgresApp https://postgresapp.com/ to run Postgres on
my mac.

For the client I use Navicat Premium Essentials (their cheaper Swiss Army
Knife tool.) It's ugly, but you can do what you need to do.

The fellow who wrote the Postrgres Mac App has a client app, called
Positco: https://eggerapps.at/postico/

Something to check out.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
12