Re: Thinking through object/text fields: Findings summarized, advice solicited
Thanks for the people that answered "what has Postgres have to do with 4D" so well. Also, thanks for the advice on tools. My quick findings for desktop tools (I didn't look at Web tools) Valentina Studio Surprisingly good and complete for a free tool. Design, admin, and data browsing. The 'pro' version offers reporting features, etc. but the free version is all I'll likely ever need. Navicat 12 Hey! I like it better than Naviat 11. To each their own. If they don't have a feature, it probably doesn't exist. Postico A simple design and browe tool. Looks nice. From the folks that put the server together that Lee recommended. Postgres.app. I'm running that on OS X (10.12.5) and it took only a couple of minutes to set up, just like it said on the tin. SQLPro for Postgres (no relation to Sequel Pro for MySQL.) A bit cheaper than Postico (neither are expensive) and, it looked to me, a bit more capable. Not for admin, but for design and browsing. There are free trials available for all of the paid products. External databases can really extend the usefulness of 4D, so it's great to have tools available. I would, by preference, only use MySQL/MariaDB or Postgres because Rob's plug-ins let us avoid ODBC. At this point, I'd prefer Postgres because, well, it's just kind of mind-blowingly impressive and well-supported by cloud-based stuff. In the whole MySQL vs. Postgres conversation, I have never met anyone that picked Postgres and regretted not using MySQL. The other way around? Yes. But plenty of MySQL users are totally happy and have no regrets, so it's not worth leaving it if you like it and it's working. But when starting from scratch, I'm going to vote for Postgres. ** 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
Hi Kirk, Navicat is just a GUI tool to view and manipulate PostgreSQL data. It is really that PostgreSQL helps me with 4D. 4D is great, but PostgreSQL is better suited for many tasks. I use it for websites (with Drupal) and sync the necessary data with 4D. I also use it a lot for preprocessing data that needs to be imported into 4D. John > On Jul 17, 2017, at 12:08 PM, Kirk Brooks via 4D_Tech <4d_tech@lists.4d.com> > wrote: > > 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? ** 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
Hey Lee, thanks for the suggestions, that should save me some time for sure. On Tue, Jul 18, 2017 at 3:26 AM, Lee Hinde via 4D_Tech <4d_tech@lists.4d.com > wrote: > On Mon, Jul 17, 2017 at 5:40 AM, David Adams via 4D_Tech < > 4d_tech@lists.4d.com> 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:4d_tech-unsubscr...@lists.4d.com > ** > ** 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
On Tue, Jul 18, 2017 at 2:08 AM, Kirk Brooks via 4D_Tech < 4d_tech@lists.4d.com> wrote: > 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? It doesn't. But if you're piping data to PostgreSQL from 4D, then it's great to have a visual SQL client to go and look and and manipulate the data in the outside database. It's super handy, for example, to be able to see that all of your values transferred the way that you want. ** 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
On Mon, Jul 17, 2017 at 5:40 AM, David Adams via 4D_Tech < 4d_tech@lists.4d.com> 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
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 < 4d_tech@lists.4d.com> 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 < > 4d_tech@lists.4d.com> 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
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 <4d_tech@lists.4d.com> > 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
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 <4d_tech@lists.4d.com> > 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
> On Mon, Jul 17, 2017 at 6:50 AM, Julio Carneiro via 4D_Tech < 4d_tech@lists.4d.com> 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
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 <4d_tech@lists.4d.com>
> wrote:
>
> On Sat, Jul 15, 2017 at 7:36 AM, Kirk Brooks via 4D_Tech <
> 4d_tech@lists.4d.com> 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
Re: Thinking through object/text fields: Findings summarized, advice solicited
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 > On Jul 14, 2017, at 4:15 PM, David Adams via 4D_Tech <4d_tech@lists.4d.com> > 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
On Sat, Jul 15, 2017 at 7:36 AM, Kirk Brooks via 4D_Tech <
4d_tech@lists.4d.com> 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
Re: Thinking through object/text fields: Findings summarized, advice solicited
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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
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 < 4d_tech@lists.4d.com> wrote: > On Jul 14, 2017, at 4:21 AM, David Adams via 4D_Tech <4d_tech@lists.4d.com> > 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
> * 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
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 < 4d_tech@lists.4d.com> 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:4d_tech-unsubscr...@lists.4d.com > ** -- - 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 ---
Re: Thinking through object/text fields: Findings summarized, advice solicited
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 w...@foundationshell.com > On Jul 13, 2017, at 10:21 PM, David Adams via 4D_Tech <4d_tech@lists.4d.com> > 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
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 <4d_tech@lists.4d.com> > 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
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 < 4d_tech@lists.4d.com> wrote: > On Jul 14, 2017, at 4:21 AM, David Adams via 4D_Tech <4d_tech@lists.4d.com> > 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 > jjfo...@gmail.com > > > > ** > 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:4d_tech-unsubscr...@lists.4d.com > ** > -- Jim Dorrance jim.dorra...@gmail.com 4...@dorrance.eu 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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
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:4d_tech-unsubscr...@lists.4d.com **
Re: Thinking through object/text fields: Findings summarized, advice solicited
On Jul 14, 2017, at 4:21 AM, David Adams via 4D_Tech <4d_tech@lists.4d.com> 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 jjfo...@gmail.com ** 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:4d_tech-unsubscr...@lists.4d.com **
Thinking through object/text fields: Findings summarized, advice solicited
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:4d_tech-unsubscr...@lists.4d.com **
