Jump to content
BitPoet

JsonNativeField: key-value table + native JSON = fully searchable

Recommended Posts

Another proof-of-concept module:

JsonNativeField

Leverages MySQL >= 5.7.8's native JSON column type for key-value storage.

This gives us the opportunity to store arbitrary (textual) fields in the database and allows us to search for them with plain subfield selector syntax, including wildcard operators. Possible applications are storing submitted form data or adding user-defined properties to their profile.

Currently, the module is still really, really alpha, but I wanted to get input early on.  Let me know what you think.

Download from GitHub.

Here are a few screenshots for anybody interested but unable to try for themselves:

1. First page with json field and custom subfields:

jsonfield1-page1.png

 

2. Second page with json field and custom subfields:

jsonfield2-page2.png

 

3. Searching through all subfields in the json field:

jsonfield3-search-all-subfields.png

 

4. Searching only in a specific subfield:

jsonfield4-search-specific-subfield.png

 

The interface is really rather crude still. The "delete" buttons only work after saving (have to attach listeners to the newly created entries yet), and I've got to straighten out supported operators for Lister to pick up. I'll see if I find some time tomorrow to work on these issues and brush up the visual side a bit.

Edited by BitPoet
Added screenshots
  • Like 19

Share this post


Link to post
Share on other sites

Sounds great, unfortunately the MySQL requirements doesn't allow me to try. Any chance to add a screenshot to see something "tangible"? :)

  • Like 1

Share this post


Link to post
Share on other sites
25 minutes ago, tpr said:

Sounds great, unfortunately the MySQL requirements doesn't allow me to try. Any chance to add a screenshot to see something "tangible"? :)

Done. :)

  • Like 2

Share this post


Link to post
Share on other sites

Thanks, looks even better :) I often have a need for a WordPress "custom fields"-like field in PW and this module has this.

  • Like 2

Share this post


Link to post
Share on other sites

this could be a good field to hold site settings when your users need to be able to keep defining/adding settings.

Share this post


Link to post
Share on other sites

Just a quick status update: I've started implementing different field types (text, integer, float, date, date+time) with native inputfields. Types can be switched back and forth, and I'll make it hookable so custom inputfields can be added as well. Will be a while though, since it looks like I'll be busy with a lot of other stuff until the end of next week. Here's a teaser though...

jsonfield5-types.png

  • Like 8

Share this post


Link to post
Share on other sites

Unexpectedly found a half day to spare, so I've pushed 0.0.3 to GitHub.

New features:

  • Field settings for custom date, date+time and text fields can now be made on the inputfield's input tab, e.g. date and time formats, placeholder text or text validatoin patterns (numeric field settings are on the todo list)
  • Names for custom fields are limited to start and end with ASCII letters and only have alphanumeric ASCII characters and hyphens in between
  • Custom field name of "data" is silently stripped to avoid bollixing up all entries
  • The custom fields table in the backend is now sorted by name (new entries simply get appended)

The module is still alpha. Things are getting smoother, but there are still a few items on the todo list. :)

  • Like 9

Share this post


Link to post
Share on other sites

@BitPoet

Wow, this is perfect timing, as I am just working on the requirements for a project that requires storing a lot of JSON.

Does this enable storage and retrieval of nested JSON content that is multiple layers deep? E.g. $pages->find('jsonField.subField.field=foo') ?

Obviously this would present some challenges for displaying the data on the back end using your current tabular format. Maybe a nested list (like ProcessWire's page tree) would be a more scalable approach?

Share this post


Link to post
Share on other sites

I admit I haven't thought too much about nesting, and the wiring around the field's json data would obviously need to be completely different. InnoDB does support searching in nested structures, but PW already treats subfields on the left side of a selector in its own way and throws away anything starting at the second full stop. A different syntax would be necessary, e.g.

$pages->find('jsonField=/subField/field:foo') 

There might be issues where search expressions need some kind of escaping (e.g. searching for literal text "/subfield"), but this would allow for wildcard queries.

$pages->find('jsonField=/subField/*/name:foo') 

Or even searching arrays (any element or by index):

$pages->find('jsonField=/subField/fields#*/name:foo');

$pages->find('jsonField=/subField/field#12/name:foo');

Here's a tiny adoption of my module to allow this search syntax. There's no back and forth conversion though, just a textarea containing the JSON.

<?php

class FieldtypeJsonDocument extends FieldtypeTextarea implements Module {
	public static function getModuleInfo() {
		return array(
			"title"				=>	"Fieldtype Json Document",
			"summary"			=>	"Fieldtype utilizing native MySQL support for searching JSON documents.",
			"version"			=>	"0.0.3",
		);
	}
	
	public function getMatchQuery($query, $table, $subfield, $operator, $value) {
		$database = $this->wire("database");

		list($path, $value) = explode(':', $value, 2);
		if(empty($value) && !empty($path)) {
			$value = $path;
			$path = "";
		}
		$path = '$' . ((empty($subfield) || $subfield == "data") ? "" : ".$subfield") . (empty($path) ? ".*" : str_replace('/', '.', preg_replace('~/?#(\d+|\*)~', "[$1]", $path)));

		$table = $database->escapeTable($table);
		$value = $database->escapeStr($value);
		if($operator == "=") {
			$query->where("JSON_SEARCH({$table}.data, 'one', '$value', NULL, '$path') IS NOT NULL");			
		} else if($operator == "*=" || $operator == "%=") {
			$query->where("JSON_SEARCH({$table}.data, 'one', '%$value%', NULL, '$path') IS NOT NULL");			
		} else if($operator == "^=") {
			$query->where("JSON_SEARCH({$table}.data, 'one', '$value%', NULL, '$path') IS NOT NULL");			
		} else if($operator == "$=") {
			$query->where("JSON_SEARCH({$table}.data, 'one', '%$value', NULL, '$path') IS NOT NULL");			
		}

		$this->log->message($query->getQuery());

		return $query; 
	}

	
	public function getDatabaseSchema(Field $field) {
		$engine = $this->wire('config')->dbEngine; 
		$charset = $this->wire('config')->dbCharset;
		$schema = array(
			'pages_id' => 'int UNSIGNED NOT NULL', 
			'data' => "JSON", // each Fieldtype should override this in particular
			'keys' => array(
				'primary' => 'PRIMARY KEY (`pages_id`)',
			),
			// additional data 
			'xtra' => array(
				// any optional statements that should follow after the closing paren (i.e. engine, default charset, etc)
				'append' => "ENGINE=$engine DEFAULT CHARSET=$charset", 
				
				// true (default) if this schema provides all storage for this fieldtype.
				// false if other storage is involved with this fieldtype, beyond this schema (like repeaters, PageTable, etc.)
				'all' => true, 
			)
		); 
		return $schema; 
	}

    public function getInputfield(Page $page, Field $field) {
        $inputField = $this->modules->get('InputfieldTextarea');
        return $inputField;
    }
	
	public function install() {
		if($this->config->dbEngine != "InnoDB") {
			throw new WireException($this->_("InnoDB database engine needs to be used for native JSON support"));
		}
		
		$dbver = $this->database->getAttribute(PDO::ATTR_SERVER_VERSION);
		if(version_compare($dbver, '5.7.8', '<')) {
			throw new WireException(sprintf($this->_("MySQL Server version needs to be at least 5.7.8 for fully working JSON support, installed version is %s"), $dbver));
		}
	}
}

There would probably be a bit of recursive shuffling necessary in sleep-/wakeup-/sanitizeValue to build nested structures based on WireData/WireArray so accessing subfields and filtering PageArrays is possible, but that should be doable too. How difficult the backend input + display parts get depends solely on your exact requirements.

  • Like 4

Share this post


Link to post
Share on other sites

Status update: JsonNativeField version 0.0.4 now has configuration options for all field types in the field's input settings. Coming up next is some more data validation (i.e. "don't shoot your foot" checking) both in the UI when switching input type and in the module code when saving to and loading from the db. This will be the major step towards moving the module from alpha to beta stage.

  • Like 6

Share this post


Link to post
Share on other sites

It's a bit on the back burner right now. I was hoping for some more elaborate possibilities with MySQL 8, but the changes to JSON support there were on the homeopathic end rather than the dynamic typing support I had hoped for. I plan to brush up the UI and client side validation a bit once PW 3.1 gets out, though, and test things out in depth with UIkit admin theme.

  • Like 6

Share this post


Link to post
Share on other sites

I've been searching for a viable means to store json without having to use a document db as well. Is this still on the back burner? If not, any suggestions on efficiently searching large datasets?
Thanks!

Share this post


Link to post
Share on other sites

Maybe totally OT but I had to store some really large JSON objects and therefore stored them straight in pages. Maybe not ideal but even after 12,000 entries/pages the system responds super fast.

If I remember correctly I used a textarea field and never had any issues.

Not perfect but... well... it works.

  • Like 1

Share this post


Link to post
Share on other sites

Yes, that is what I am currently doing. It saves the data fine and I can read the data fine. Searching values from keys, not so much. lol

Share this post


Link to post
Share on other sites

My search was based on "client entries" so they can browse through all requests and saved entries they ever made.

Maybe I find that code again or can gain access to the system. If so... I will let you know.

  • Like 1

Share this post


Link to post
Share on other sites

I'm having to read the whole dataset into an array in order to process entries. Some pages have 5K+ entries, others are more manageable. It's a global Company <--> Contractor relationship.

Share this post


Link to post
Share on other sites

Ok... you stepped up my game. No doubt. 
Still... I'll try to get into my old code.

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By robert
      I often had the need for an overview of all used fields and their contents for a specific page/template while developing new websites without switching to the backend, so I made a small module which lists all the needed information in a readable manner (at least for me):
      Debug Page Fields
      https://github.com/robertweiss/ProcessDebugPageFields
      It adds two new properties to all pages:
      $page->debugFieldValues – returns an object with all (sub-)fields, their labels, fieldtypes and values $page->debugFieldTypes – returns an object with all fieldtypes and their corresponding fields // List all values of a pages $page->debugFieldValues // List a specific field $page->debugFieldValues->fieldname // List all used fieldtypes of a page $page->debugFieldTypes I recommend using it in combination with Tracy Debugger, Ray, Xdebug etc. as it returns an object and is only meant for developing/debugging uses. 
      For now, the fieldtype support includes mostly fieldtypes I use in my projects, but can easily be extended by adding a new FieldtypeFIELDNAME method to the module. I use it with five different client installations (all PW 3.0.*), but of course there might be some (or more) field configurations which are not covered correctly yet.
      Supported fieldtypes
      Button Checkbox Color Combo Datetime Email FieldsetPage * File FontIconPicker Functional Image ImageReference MapMarker Multiplier Mystique Options Page PageIDs PageTitle Radio Repeater * RepeaterMatrix * RockAwesome SeoMaestro Table Text Textarea Textareas Toggle URL * The fields with complete subfield-support also list their corresponding subfields.
      Installation
      Download the zip file at Github or clone the repo into your site/modules directory. If you downloaded the zip file, extract it in your sites/modules directory. In your admin, go to Modules > Refresh, then Modules > New, then click on the Install button for this module. As this is my first ›public‹ module, I hope I did not miss any important things to mention here.
    • By horst
      Wire Mail SMTP

      An extension to the (new) WireMail base class that uses SMTP-transport

      This module integrates EmailMessage, SMTP and SASL php-libraries from Manuel Lemos into ProcessWire. I use this continously evolved libraries for about 10 years now and there was never a reason or occasion not to do so. I use it nearly every day in my office for automated composing and sending personalized messages with attachments, requests for Disposition Notifications, etc. Also I have used it for sending personalized Bulkmails many times.

      The WireMailSmtp module extends the new email-related WireMail base class introduced in ProcessWire 2.4.1 (while this writing, the dev-branch only).
       
      Here are Ryans announcement.



      Current Version 0.6.0
      Changelog: https://github.com/horst-n/WireMailSmtp/blob/master/CHANGELOG.md
      get it from the Modules Directory Install and Configure

      Download the module into your site/modules/ directory and install it.

      In the config page you fill in settings for the SMTP server and optionaly the (default) sender, like email address, name and signature.
      You can test the smtp settings directly there. If it says "SUCCESS! SMTP settings appear to work correctly." you are ready to start using it in templates, modules or bootstrap scripts.


      Usage Examples
      The simplest way to use it:
      $numSent = wireMail($to, $from, $subject, $textBody); $numSent = wireMail($to, '', $subject, $textBody); // or with a default sender emailaddress on config page This will send a plain text message to each recipient.
       
      You may also use the object oriented style:
      $mail = wireMail(); // calling an empty wireMail() returns a wireMail object $mail->to($toEmail, $toName); $mail->from = $yourEmailaddress; // if you don't have set a default sender in config // or if you want to override that $mail->subject($subject); $mail->body($textBody); $numSent = $mail->send(); Or chained, like everywhere in ProcessWire:
      $mail = wireMail(); $numSent = $mail->to($toEmail)->subject($subject)->body($textBody)->send(); Additionaly to the basics there are more options available with WireMailSmtp. The main difference compared to the WireMail BaseClass is the sendSingle option. With it you can set only one To-Recipient but additional CC-Recipients.
      $mail = wireMail(); $mail->sendSingle(true)->to($toEmail, $toName)->cc(array('person1@example.com', 'person2@example.com', 'person3@example.com')); $numSent = $mail->subject($subject)->body($textBody)->send(); The same as function call with options array:
      $options = array( 'sendSingle' => true, 'cc' => array('person1@example.com', 'person2@example.com', 'person3@example.com') ); $numSent = wireMail($to, '', $subject, $textBody, $options); There are methods to your disposal to check if you have the right WireMail-Class and if the SMTP-settings are working:
      $mail = wireMail(); if($mail->className != 'WireMailSmtp') { // Uups, wrong WireMail-Class: do something to inform the user and quit echo "<p>Couldn't get the right WireMail-Module (WireMailSmtp). found: {$mail->className}</p>"; return; } if(!$mail->testConnection()) { // Connection not working: echo "<p>Couldn't connect to the SMTP server. Please check the {$mail->className} modules config settings!</p>"; return; }  
      A MORE ADVANCED DEBUG METHOD!
      You can add some debug code into a template file and call a page with it:
      $to = array('me@example.com'); $subject = 'Wiremail-SMTP Test ' . date('H:i:s') . ' äöü ÄÖÜ ß'; $mail = wireMail(); if($mail->className != 'WireMailSmtp') { echo "<p>Couldn't get the right WireMail-Module (WireMailSmtp). found: {$mail->className}</p>"; } else { $mail->from = '--INSERT YOUR SENDER ADDRESS HERE --'; // <--- !!!! $mail->to($to); $mail->subject($subject); $mail->sendSingle(true); $mail->body("Titel\n\ntext text TEXT text text\n"); $mail->bodyHTML("<h1>Titel</h1><p>text text <strong>TEXT</strong> text text</p>"); $dump = $mail->debugSend(1); } So, in short, instead of using $mail->send(), use $mail->debugSend(1) to get output on a frontend testpage.
      The output is PRE formatted and contains the areas: SETTINGS, RESULT, ERRORS and a complete debuglog of the server connection, like this one:
       
      Following are a ...


      List of all options and features


      testConnection () - returns true on success, false on failures


      sendSingle ( true | false ) - default is false

      sendBulk ( true | false ) - default is false, Set this to true if you have lots of recipients (50+)


      to ($recipients) - one emailaddress or array with multiple emailaddresses

      cc ($recipients) - only available with mode sendSingle, one emailaddress or array with multiple emailaddresses

      bcc ($recipients) - one emailaddress or array with multiple emailaddresses

       
      from = 'person@example.com' - emailaddress, can be set in module config (called Sender Emailaddress) but it can be overwritten here

      fromName = 'Name Surname' - optional, can be set in module config (called Sender Name) but it can be overwritten here


      priority (3) - 1 = Highest | 2 = High | 3 = Normal | 4 = Low | 5 = Lowest

      dispositionNotification () or notification () - request a Disposition Notification


      subject ($subject) - subject of the message

      body ($textBody) - use this one alone to create and send plainText emailmessages

      bodyHTML ($htmlBody) - use this to create a Multipart Alternative Emailmessage (containing a HTML-Part and a Plaintext-Part as fallback)

      addSignature ( true | false ) - the default-behave is selectable in config screen, this can be overridden here
      (only available if a signature is defined in the config screen)

      attachment ($filename, $alternativeBasename = "") - add attachment file, optionally alternative basename


      send () - send the message(s) and return number of successful sent messages


      debugSend(1) - returns and / or outputs a (pre formatted) dump that contains the areas: SETTINGS, RESULT, ERRORS and a complete debuglog of the server connection. (See above the example code under ADVANCED DEBUG METHOD for further instructions!)


      getResult () - returns a dump (array) with all recipients (to, cc, bcc) and settings you have selected with the message, the message subject and body, and lists of successfull addresses and failed addresses,


      logActivity ($logmessage) - you may log success if you want

      logError ($logmessage) - you may log warnings, too. - Errors are logged automaticaly
       
       
      useSentLog (true | false) - intended for usage with e.g. third party newsletter modules - tells the send() method to make usage of the sentLog-methods - the following three sentLog methods are hookable, e.g. if you don't want log into files you may provide your own storage, or add additional functionality here

      sentLogReset ()  - starts a new LogSession - Best usage would be interactively once when setting up a new Newsletter

      sentLogGet ()  - is called automaticly within the send() method - returns an array containing all previously used emailaddresses

      sentLogAdd ($emailaddress)  - is called automaticly within the send() method
      Changelog: https://github.com/horst-n/WireMailSmtp/blob/master/CHANGELOG.md
       
       
    • By Cybermano
      Food Allergens Module
      A simple List of Food Allergens
      My needs were to provide a simple list of food allergens for our clients with restaurant related activity.
      The idea was to simply output the list (to speed up the data entry) without leaving the food menu editing, eg. opening another page in new tab or window.
      This isn't a perfect solution, but it works fine for my needs and I decided to share the base idea.
      This could also be easily used to show little notes or short "vademecum", not only for the list of food allergens.
      ---
      Main features
      The basis
      All moves from a short editing of the module in this tutorial: How to create custom admin pages by @bernhard
      First of all it creates an empty admin page, with a dedicated permission to let safe-user to see it (this permission has to be created as a new ones, manually or by the module).
      Once the page is created, I have hooked its behaviour into the ready.php, to show the content (basically a list).
      A step further
      With the tips of  @bernhard, @Soma (and many others), see here , the magic happens. 
      The new page will be shown as a panel, so editors will not abandon their data entry to have a quick view to the list.
      A little further
      Why scroll to the top of the page to click a link?
      The next step was to create a sticky button only in the food menu pages.
      Again with a @bernhard tip I moved into the customization of this simple module and the related hook.
      ---
      How to use this module
      After installed, it creates the page /admin/page/allergens/ and the module is to be setted up. The first field is a CKEditor with multi-language. This is the place where to write the informations that will be shown into the page. The next field is a simply text-area where to place a bit of JS that will be appended to the markup of the text (omit the 'script' tags). I also putted a checkbox with a silly statement: this to think at least twice on the safety of the written JS. Now comes the first way to display the link to the page
      Field Note with Link. Enable and save it. The module will display a new row with 4 selects (1 standard and 3 ASM):
      View mode (to show the page as Panel or as Modal PopUp); Templates to select: select one or more and save before proceed, so the  asm-select of the pages will be populated showing all the pages of the selected templates. Pages to select: also here select at least one and save before proceed to populate the asm-select for fields only with the ones that belong to the selected pages. Select the fields where to place the note and save again. That's all: now you will find into the notes of the selected fields the link "See the List of Allergens".
      At the same way, the option for the sticky button, but with a plus
      The field select is obviously unnecessary, but you could play with the last row: the inline styles to fix your sticky button where you like. Here you could set the sticky position of the <div> and the absolute ones of the <a>.

      Video Explanation
      In these screencasts you could see a custom JS that show a "copy" button near a "hanna-code" call.
      This because I've set a specific one for each allergen to show up a tooltip in the front end.

      Registrazione #33.mp4  

      Registrazione #34.mp4 ---
      Last but not the least
      Actually it works fine for my needs, even if it's much improvable: I'm working on the permissions creation, the uninstall section, a separate configs and defaults and how to include the hook into the module leaving free the ready.php. According to a simpler uninstall. Also I would make the link text as a dynamic text field, so it will be more flexible.
      I always learn a lot here, so I would share my code for whom it could be interested.
      I removed the hanna code references, but I provide you the html list of the allergens, English and Italian too, so you can paste them into the "source" of the CKEditor field to have a ready to use module.
      Obviously you are free to modify the code as per your needs.
      Please, keep in mind that I'm not a pro coder and I beg your pardon for my verbosity (speaking and coding). 😉
      I hope be helpful or for inspiration.
      Bye
      ready.phpList-ITA.htmlList-ENG.htmlAllergens.module
      README.md
    • By Robin S
      This module is sort of an upgrade to my earlier ImageToMarkdown module, and might be useful to anyone working with Markdown in ProcessWire.
      Copy Markdown
      Adds icons to images and files that allow you to copy a Markdown string to the clipboard. When you click the icon a message at the top left of the screen notifies you that the copying has occurred.
      Screencast

      Note: in the screencast an EasyMDE inputfield is used to preview the Markdown. It's not required to use EasyMDE - an ordinary textarea field could be used.
      Usage: Images
      When you hover on an item in an Images field an asterisk icon appears on the thumbnail. Click the icon to copy an image Markdown string to clipboard. If the "Description" field is populated it is used as the alt text.
      You can also open the "Variations" modal for an image and click the asterisk icon to copy an image Markdown string for an individual variation.
      Usage: Files
      When you hover on an item in a Files field an asterisk icon appears next to the filename. Click the icon to copy a link Markdown string to the clipboard. If the "Description" field is populated it is used as the link text, otherwise the filename is used.
       
      https://github.com/Toutouwai/CopyMarkdown
      https://processwire.com/modules/copy-markdown/
    • By BitPoet
      I've realized that I've been jumping back and forth between the PW API docs and the source code for site modules far too much. The idea to hold all necessary documentation locally in one place has occurred to me before, but getting PHPDocumentor et al set up and running reliably (and producing readable output) as always been too much of a hassle. Today I was asked how I find the right hooks and their arguments, and that inspired me to finally get my backside down on the chair and whip something up, namely the
      Module Api Doc Viewer
      ProcessModuleApiDoc
      It lets you browse the inline documentation and public (optionally also protected) class/method/property information for all modules, core classes and template files in the ProcessWire instance. The documentation is generated on the fly, so you don't have to remember to update your docs whenever you update a module.
      The module is quite fresh, so expect some bugs there. Behind the scenes it uses PHP-Parser together with a custom class that extracts the information I needed, and the core TextformatterMarkdownExtra module for rendering the description part in the phpdoc style comments.
      This is not a replacement / competitor to the API Viewer included in the commercial ProDevTools package. There is quite some information included in the inline documentation that my module can't (and won't) parse, but which makes up parts of the official ProcessWire API docs.
      This, instead, is a kind of Swiss army knife to view PHPDoc style information and get a quick class or function reference.
      If you feel daring and want to give it a spin, or if you just want to read a bit more, visit the module's GitHub repository.
      This is the overview page under "Setup" -> "Module API Docs":

      And this is what the documentation for an individual class looks like:

      The core module documentation can of course be found online, but it didn't make sense not to include them.
      Let me know what you think!

×
×
  • Create New...