MySQL X DevAPI 8.0.3 for PHP is here!

Dear MySQL users,

I’m glad to announce that MySQL X DevAPI extension for PHP 8.0.3 has been recently released!

If for some reason you don’t know what this is all about, then in short the MySQL X DevAPI for PHP add support for the new X DevAPI in PHP, and it’s released as an extension to the language. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models –MySQL as a Document Store– using a modern NoSQL-like syntax that does not assume previous experience writing traditional SQL.

For general documentation about how to get started using MySQL as a document store, see “Using MySQL as a Document Store”.

Download the extension.

Depending on your operating system and working environment, you can download the extensio from:

  • Direct link to the mysql_devapi extension pecl web page, here.
  • On Fedor-ish Linux system, use the Remi’s repo.
  • Clone the source code repo, here.

Further instruction on how to install the extension with samples are included in the readme script and documentation.

This new release contains many bug fixes, refactoring’s and improvements that are a step forward in our work to improve the quality of the code base and thus of the extension itself, beside the minor tasks we were able to deliver some big features:

Support for Array or Object “contains” operator:

New CONTAINS and NOT_CONTAINS operators introduced in any expression valid in CRUD operations. They are different from the IN operator, in that IN works with SQL and requires a set of literals at the right side. CONTAINS should accept JSON values at the left and right sides of the operator,including expressions that generate a JSON value.
Example (Where $coll is a Column object):

New API’s: getOne, removeOne, replaceOne, addOrReplaceOne:

This extension is specific to Collections. These are commands that operate at a single document level, unlike the other CRUD commands that operate on all documents that match a filter the following collection commands are introduced as a set of direct-execution complementary operations that reference documented by id, as opposed to a free-form expression.

When working with small documents (majority of the time), the simplest and easiest way to change a certain document is to use a load/modify/save pattern:

New row locking mechanism for Crud.Find / Table.Select

The MySQL SELECT statement supports now locking matching rows, for reads and for writes (SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE).

The find() CRUD method is extended to support this feature, allowing safe, transactional document updates on collections. Transaction support is an important MySQL differentiator compared to other NoSQL databases. Example:

Client A execute:

Client B execute:

Then Client A commits:

And finally Client B can continue:

Support for the new MySQL Server 8.x, including fixes for phpize build.

Large refactorings and fixes where made by the team to allow this PHP extension to fully support he new MySQL server 8.x, also the problems with the phpize build are now solved and we’re happy to deliver a rock solid release with plenty of support for the new MySQL’s.

Thanks for reading,

On behalf of the MySQL/Oracle X DevAPI for PHP Team, Filip Janiszewski

MySQL Connector/Net 8.0.8-dmr has been released

MySQL Connector/Net 8.0.8 is the fifth development release that expands cross-platform
support to Linux and macOS when using Microsoft’s .NET Core framework. Now, .NET
developers can use the X DevAPI with .NET Core and Entity Framework Core (EF Core)
1.0 to create server applications that run on Windows, Linux and macOS. We are very
excited about this change and really look forward to your feedback on it!

MySQL Connector/Net 8.0.8 is also the seventh development release of MySQL
Connector/Net to add support for the new X DevAPI. The X DevAPI enables application
developers to write code that combines the strengths of the relational and document
models using a modern, NoSQL-like syntax that does not assume previous experience
writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see
http://dev.mysql.com/doc/x-devapi-userguide/en/index.html.
For more information about how the X DevAPI is implemented in Connector/Net, see
http://dev.mysql.com/doc/dev/connector-net.

Note

The X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X
Plugin enabled. For general documentation about how to get started using MySQL
as a document store, see http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

To download MySQL Connector/Net 8.0.8-dmr, see the “Development Releases” tab at
http://dev.mysql.com/downloads/connector/net/

 


Changes in MySQL Connector/Net 8.0.8 (2017-07-10, Development Milestone)

   MySQL Connectors and other MySQL client tools and
   applications now synchronize the first digit of their version
   number with the (highest) MySQL server version they support.
   For example, MySQL Connector/Net 8.0.12 would be designed to
   support all features of MySQL server version 8 (or lower).
   This change makes it easy and intuitive to decide which
   client version to use for which server version.

   MySQL Connector/Net 8.0.8 is the first release to use the new
   numbering. It is the successor to MySQL Connector/Net 7.0.7.

     * Functionality Added or Changed

     * Bugs Fixed

   Functionality Added or Changed

     * X DevAPI: The format of document ID values generated when
       adding documents to a collection has changed. It is still
       a string of 32 hexadecimal digits based on UUID, but the
       order of digits was changed to match the requirement of a
       stable ID prefix.

     * All connections created using MySQL Connector/Net now are
       secure by default. Also, the Ssl-Enable connection option
       has been replaced by Ssl-Mode. Permitted Ssl-Mode values
       are None, Required (the default), VerifyCA, and
       VerifyFull.

       With this change, a non-SSL enabled server now requires
       the Ssl-Mode option be set to None explicitly in the
       connection string or the connection will fail.

     * X DevAPI: It is no longer permitted to pass an empty
       search condition, such as the NULL value or an empty
       string, to the Collection.Modify and Collection.Remove
       methods.
	
     * X DevAPI: The NodeSession class has been renamed to
       Session and the MySQLX.GetNodeSession method has been
       renamed to MySQLX.GetSession. Also, the XSession class
       has been removed.

     * X DevAPI: When creating a new connection, multiple hosts
       now can be specified as part of the connection string,
       which will try each host until a successful connection is
       established or all elements from the host list have been
       tried. The following connection-string formats are
       supported:

	    var mySession = MySQLX.GetSession(
	      "mysqlx://dbuser:password@[" +
	      "localhost:33060," +
	      "192.1.10.10:33060," +
	      "[2001:db8:85a3:8d3:1319:8a2e:370:7348]:33060" +
	      "]"
	    );

	    var mySession = MySQLX.GetSession(
	      "user=dbuser;" +
	      "password=dbpassword;" +
	      "server=" +
	      "192.1.10.10," +
	      "server.example.com," +
	      "localhost;" +
	      "port=33060;"
	    );

   Bugs Fixed

     * EF Core: The Database First feature did not support the
       following data types: BINARY, VARBINARY, MEDIUMBLOB,
       LONGBLOB, SET, DATE, TIME, and YEAR. (Bug #25493209)

     * EF Core: Database First support produced an error when
       the existing MySQL database included one or more views.
       (Bug #25493086)

     * EF Core: Using
       System.ComponentModel.DataAnnotations.Schema.TableAttribu
       te to initialize a new class instance that specified the
       name of an existing MySQL table produced incorrect
       mappings of table and column names.
       (Bug #25394223, Bug #84423)

Nuget

Packages are available at:

https://www.nuget.org/packages/MySql.Data/8.0.8-dmr
https://www.nuget.org/packages/MySql.Web/8.0.8-dmr
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore/8.0.8-dmr
https://www.nuget.org/packages/MySql.Data.EntityFrameworkCore.Design/8.0.8-dmr

Enjoy and thanks for the support!

On Behalf of MySQL/Oracle Release Engineering Team
Prashant Team

MySQL for Visual Studio 2.0.5 has been released

The MySQL Windows Experience Team is proud to announce the release of  MySQL for Visual Studio 2.0.5 m4. Note that this is a development preview release and not intended for production usage.

MySQL for Visual Studio 2.0.5 m4 is the fourth development release of the MySQL for Visual Studio 2.0 series. This series adds support for the new X DevAPI. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see http://dev.mysql.com/doc/x-devapi-userguide/en/. For more information about how the X DevAPI is implemented in MySQL for Visual Studio, and its usage, see http://dev.mysql.com/doc/refman/5.7/en/mysql-shell-visual-studio.html.

Please note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

You can download MySQL Installer from our official Downloads page at http://dev.mysql.com/downloads/installer/.

MySQL for Visual Studio can also be downloaded by using the product standalone installer found at http://dev.mysql.com/downloads/windows/visualstudio/, under the tab “Development Releases”.

 

Changes in MySQL for Visual Studio 2.0.5 (2017-04-24)

Known limitation of this release: MySQL Connections Manager does not save the changes made to the default schema when editing a connection. Use one of the following workarounds to resolve this limitation:

  • For new connections created with MySQL Connections Manager in which a default schema is specified, close MySQL Connections Manager and then reopen it. After performing this action, you can add the connection to the Microsoft Visual Studio Server Explorer.
  • For existing connections, set the default schema with MySQL Workbench.

Functionality Added or Changed

  • Removed Support for Microsoft Visual Studio 2010.
  • Added support for Microsoft Visual Studio 2017.
  • Added an output pane to the MySQL SQL Editor window. The new MySQL Output pane includes the information that previously appeared in the Messages tab.

Bugs Fixed

  • The MySQL Data Export Tool exported data from the wrong schema when a second schema was selected and then deselected. In addition, all selected schemas were deselected when a single schema was deselected. (Bug #23666666)
  • Script files did not accept most keyboard input after the file was saved for the first time. (Bug #24751945)
  • The Field Type view in the result tab of an executed SQL query did not display field information correctly when the query included a schema name explicitly. (Bug #24601948)
  • The bit() data type was incorrectly implemented as bit(10) for MySQL tables in the Table Designer window.  (Bug #13477821)

 

Quick links

MySQL for Visual Studio 1.2.7 has been released

The MySQL Windows Experience Team is proud to announce the release of MySQL for Visual Studio 1.2.7. This is a maintenance release for 1.2.x. It can be used for production environments.

MySQL for Visual Studio is a product that includes all of the Visual Studio integration functionality to create and manage MySQL databases when developing .NET applications.

MySQL for Visual Studio is installed using the MySQL Installer for Windows which comes in 2 versions:

  • Full (352.8 MB) which includes a complete set of MySQL products with their binaries included in the downloaded bundle.
  • Web (1.7 MB – a network install) which will just pull MySQL for Visual Studio over the web and install it when run.

You can download MySQL Installer from our official Downloads page at http://dev.mysql.com/downloads/installer/.

MySQL for Visual Studio can also be downloaded by using the product standalone installer found at http://dev.mysql.com/downloads/windows/visualstudio/.

 

Changes in MySQL for Visual Studio 1.2.7 (2017-04-10)

Functionality Added or Changed

  • Removed Support for Microsoft Visual Studio 2010.
  • Added support for Microsoft Visual Studio 2017.

Bugs Fixed

  • The MySQL Data Export Tool exported data from the wrong
    schema when a second schema was selected and then deselected.
    In addition, all selected schemas were deselected when a single
    schema was deselected. (Bug #25713981)
  • Script files did not accept most keyboard input after the
    file was saved for the first time. (Bug #25713638)

 

What Is New In MySQL for Visual Studio 1.2

  • New MySQL Project Items for creating data views in Windows Forms and ASP.NET MVC web applications.
  • A new option in web configuration tool for the ASP.NET Personalization Provider (this feature requires MySQL Connector/NET 6.9 or newer).
  • A new option in web configuration tool for the ASP.NET Site Map Provider (this feature requires MySQL Connector/NET 6.9 or newer).
  • A new option for the MySQLSimpleMembership provider in the web configuration tool. (This feature requires MySQL Connector/NET or newer).

 

MySQL Windows Forms Project Item
——————————————————————————————-
This Project Item is available on the Add New Item dialog in Visual Studio when adding a new item to an existing project.

The dialog presented to create the MySQL Windows Forms Project Item automates the generation of a Windows Form, representing a view for MySQL data available through an existing Entity Framework’s model entity containing a MySQL table or view. Different view types are available to present the data:

  • Single-column: A form that contains one control by each existing column in the table with navigation controls and that allows CRUD operations.All controls can include validations for numeric and DateTime data types.
  • Grid: A form with a data grid view that contains navigation controls.
  • Master-detail: A form with a single control layout for the Parent table and a data grid view to navigate through child table’s data.

Supported with C# or Visual Basic language. This feature requires Connector/NET 6.7.5, 6.8.3 or 6.9.x.

For more details on the features included check the documentation at: https://dev.mysql.com/doc/connector-net/en/visual-studio-project-items-forms.html

 

MySQL ASP.NET MVC Project Item
——————————————————————————————-
This Project Item is available on the Add New Item dialog in Visual Studio when adding a new item to an existing project.

The dialog presented to create the MySQL ASP.NET MVC Item automates the generation of a controller and its corresponding view, representing a view for MySQL data available through an existing Entity Framework’s model entity containing a MySQL table or view. The MVC versions supported by this wizard are 3 when using Visual Studio 2010 or 2012, and 4 when using Visual Studio 2013 or greater.

The generation of the MVC items is done by creating an Entity Framework data model either with Entity Framework version 5 or 6 depending on the user’s selection.

Supported with C# or Visual Basic language. This feature requires Connector/NET 6.7.5, 6.8.3 or 6.9.x.

For more details on the features included check the documentation at: https://dev.mysql.com/doc/connector-net/en/visual-studio-project-items-mvc.html

 

New option in web configuration tool for the ASP.NET Personalization Provider
——————————————————————————————-
Personalization provider allows to store personalization state-state data regarding the content and layout of Web Parts pages-generated by the Web Parts personalization service using MySQL as a data source. This feature requires Connector/NET 6.9.x or greater.

https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-web-personalization-provider.html

 

New option in web configuration tool for the ASP.NET Site Map Provider
——————————————————————————————-
Site Map provider allows to show a hierarchical list of links that describe the structure of a site. This feature requires Connector/NET 6.9.x or greater.

http://dev.mysql.com/doc/connector-net/en/connector-net-website-config.html

 

New option in web configuration tool for the ASP.NET Simple Membership provider
——————————————————————————————-
The latest provider added to handle web site membership tasks with ASP.NET. This feature requires Connector/Net 6.9.x or greater.

http://dev.mysql.com/doc/connector-net/en/connector-net-simple-membership-tutorial.html

 

 

Quick links:

Enjoy and thanks for the support!

Develop by Example – Document Store: Working with Express.js, AngularJS and Node.js

In previous blog posts we explained how to perform certain actions in a MySQL database set up as a document store using Connector/Node.js. In this blog post we are going to use some of the examples covered to explain how to start working with an application created with Express.js, AngularJS, Node.js, and MySQL Connector/Node.js.

Required

Optional

Before we start we need to install MySQL Server, Node.js and download MySQL Connector/Node.js.

Configuring MySQL Server

If you are using Windows and you installed MySQL Server using the MySQL Installer you might have already enabled the MySQL X Protocol plugin or you can enable it through a re-configuration of the MySQL Server, for more details go to the Plugins and Extensions section in the official documentation.

If you are not using windows or the MySQL Installer, once MySQL Server is installed and running we need to enable the X Plugin to be able to use MySQL as a document store.

To enable the X Plugin in the MySQL server using the MySQL command-line client you need to use the root account or an account with INSERT privilege on the mysql.plugin table:

  • Invoke the MySQL command-line client: mysql -u user –p
  • Run the following command: INSTALL PLUGIN mysqlx SONAME ‘mysqlx.so’;

Note: Replace mysqlx.so with mysqlx.dll for Windows.

Please refer to the official documentation for more information about setting up MySQL as a document store.

Installing and using Express

After Node.js is also installed we need to open a terminal and navigate to the path where we want to create our project. Once we are in the desired path, we are going to install the Express application generator, which will help us to create the skeleton for our application. To install it, run the following command in the terminal:

$ npm install express-generator g

The -g flag specifies that the package will be installed globally.

Now we are going to create the skeleton for our application by running the following command the in terminal:

$ express mysql-news --ejs

The –ejs flag is to specify that we are going to use EJS as our template language to use standard HTML instead of Jade, which is the default view engine used by Express. The name for our application was specified as mysql-news. After running the command, we are going to see a folder with the same name as our application with the following structure:

mysql-news/
├───app.js
├───package.json
├───bin/
└─www
├───public/
├───images/
├───javascripts/
└───stylesheets/
└─style.css
├───routes/
├─index.js
└─users.js
└───views
├─error.ejs
└─index.ejs

Now we need to install the dependencies of the template created. Run the following command in the terminal at the path level were our application folder were created:

$ cd mysql-news && npm install

To start the newly created application, run the following command in the terminal:

$ npm start

To verify that our application is running, open a browser and type: http://localhost:3000.
If everything it is ok, we will be able to see a webpage with the text: “Welcome to Express”

Installing MySQL Connector/Node.js

In our application folder copy the MySQL Connector/Node.js tar.gz file that you may have downloaded previously. Once it has copied, run the following command to add the package to our application:

$ npm install mysql-connector-nodejs-1.0.4.tar.gz

Note: verify that the version of the file downloaded matches the version of the command.

Once MySQL Connector/Node.js is installed, we can navigate to the path /node_modules/@mysql/xdevapi to see where it is located.

Installing AngularJS and Angular-Route

In this blog post we are going to use AngularJS and the Angular-Route module both using the 1.5.8 version. To install them, run the following command:

$ npm install angular@1.5.8 angular-route@1.5.8

Once the packages are downloaded and installed in the node_modules folder we are going to copy the folders angular and angular-route inside our public/javascripts folder.

Note: to avoid doing this manually you can install Bower, use the CDN for the AngularJS modules, or any other alternative.

Optional Software

Now just the optional software is left. If you want a visual representation of the schema and collections in the application, download and install MySQL Workbench. And if you want to add a boost to the front-end development, install Bootstrap. In this blog post we are using just the CDN for the CSS, but you can install the complete package.

The folder structure

The last thing before we start working on the code of our application is to create two folders in the root path of our application: models and data. In the end, our application will have something like the following structure:
mysql-news/
├───app.js
├───mysql-connector-nodejs-1.0.4.tar.gz
├───package.json
├───bin/
└─www
├───data/
├───models/
├───node_modules/
├───public/
├───images/
├───javascripts/
└───stylesheets/
└─style.css
├───routes/
├─index.js
└─users.js
└───views
├─error.ejs
└─index.ejs

Building the application

Uploading initial data

As you may have guessed by its name, this application will display news about MySQL. We will have the ability to create new posts or add comments to the news items that are already in the database.

Now we can start building out our application. The first thing that we are going to do is to add a JSON file in the data folder. We are going to name it news.json or you can choose another name. Copy and paste the following data to the file:

This initial data will help us to visualize our progress. To continue we need to update our application to create our schema and collection where we are going to upload the initial data.

Open the www file that it is inside the bin folder. This file contains the configuration for the web server that will host our application. The first thing that we are going to add is a function that creates our schema, our collection, and then upload the data to the collection. Add the following code at the end of the file:

In the previous code we define the configureDataBase function where we create a session to our local instance of MySQL server. Once we have the session we verify if the schema mysqlNews exists. If not, we create it and the news collection. Finally, we load the initial data into the news collection.

We have the function that initializes our schema and now we need to add another function that calls it just one time when the application starts. In the www file you will find a section that creates the http server below the line:

var server = http.createServer(app);

We are going to add the following code below that line:

In the previous code first we load our initial data into the initialData variable as well as the xdevapi module into the mysql variable. These variables are used by the configureDataBase function. The variables need to be defined before the function is called. Then we create an instance of an EventEmitter, and we configure the event that is in charge of calling the function that creates our schema and collection.

To complete the configuration, we just need to add the following lines inside the initializeDatabase function:

Note: the lines should be below our initializeDatabase function, verify that are not duplicated

Since we are going to load some data when the application starts, we need to first configure our database before the server accepts connections, that is the reason we are moving the above lines inside our function. In the end, our function should look like this:

Now that we have the code that configures our schema and loads some initial data, it is time to create the function that retrieves the data from the collection.


Getting the data

In our model folder we are going to add a new file called news.js. This module will contain all the methods used to perform actions in our collections. The first method that we are going to add to this module is the one that gets the documents from our collection.

First we are going to define two variables, one to load the MySQL xdevapi module and other one to store the configuration used to create connections to our server.

Now we are going to create the method that returns all the documents stored in our collection:

First we added the method getAll to the exports object of our module and then we call the getSession method to create a connection to our server. Once we have the session we get the schema and the collection where our documents are stored. We define an array variable to store the documents returned from the collection and we execute the find method without a filter to return all the documents. When the execute method is reached every document will be added to the array variable that will be returned. Then when the execute method is completed, if we received any document; we return null as the error message and the array of documents, or an error message and null as the array of documents otherwise.

Now we need to create the required files to call the method and display the documents from the database in the application.

In this blog post we are going to follow the folder structure suggested by AngularJS, putting each feature/entity in its own file and having a directory for feature. First we are going to create an AngularJS component named app.module.js inside the folder path public\javascripts, we are not creating a folder for this component because it will be used by all the features we are going to add.

In this file we are going to configure the modules required by the application and a factory to access the methods that we are going to add to our model file (news.js). For more information about a factory please see the AngularJS documentation.

We are going to add the following code in the file:

In the first line we set a name for our application (mysqlNews), and specify the modules that we are going to use: ngRoute and newsList. The newsList module is going to be the module that loads the documents in our application. And the ngRoute is an AngularJS module that provides the route and deeplinking services.

Then we create a factory that is going to be used by the other modules, the factory is named as newsFactory and we specify the module $http as the module that is going to be used by our factory. The $http module is core service that facilities the communication with the HTTP servers. Inside the factory we define an object that will have a property named news that will store the documents of our application and a method called getAll that will get the documents and copy them into the news property.

Now we are going to create another component named app.config.js, and also this component does not have a directory because will be used by all the features too. In this file we are going to configure the routes of our application.

In the previous code we set ‘!‘ as our prefix for our routing configuration and also we configure the template that is going to be used to load the news in our application.

To continue we are going to create a folder named newsList, this folder will contain the components and the template to display the news in our application. To this folder we are going to add a component named news-list.module.js with the following content:

angular.module('newsList', []);

The previous file just contains the name of this module, if we require additional modules we can set it here in this file. Now add another component named news-list.component.js with the following content:

The code we have just seen configures the template and also the controller that loads the documents into the template via the factory we created earlier. The news from the factory are copied to a property of the controller and then the method to get all the news is called, and also a property named order that is going to be used to sort the news, is added to the controller. The last file that we are going to add to this folder is the template file to display the documents. The file will be named news-list.template.html with the following content:

In the template we have a search and a filter section, and a list where we will display our documents. The search input is set to the controller’s search property that used in this template ($ctrl.search), the property is applied to the list of items to filter them. The sort select is set to the controller’s order property that is also applied to the list of items to sort them by title or by date published.

To complete this section code and have something we can test, we need to do just two more things. The first one is to update the file index.js that is inside the routes folder. We need to load our model, append the following line to the section where the variables to do that are defined:

var mysql = require('../models/news.js');

And we are going to add a new get method:

In the above method we are calling our model’s getAll method and sending back the information in JSON format. The last step to test our application is to update the index.ejs file inside the views folder.

And the second thing is to update our index file index.ejs. To start with, we update the html tag to include the language and the application name. The name is as defined in the app.module.js file: mysqlNews. If we do not set the ng-app directive AngularJS will not find the root element of our application and our components will not be loaded. The html tag should appear as follow:

<html lang="en" ng-app="mysqlNews">

Then we are going to add the link to our scripts and to the bootstrap CSS in the header:

And finally we are going to update the body adding the ng-view directive that is used by $route service to render the template configured by the current route loaded:

To test the application and verify that everything is configured properly, run the following command in a terminal and navigate to http://localhost:3000/:

$ npm start

We should see a search/sort section on the left-hand side of your browser and a list of the initial documents loaded to our collection next to it.

index

Adding Comments

At this time, we have an application that list the documents from a collection and let us do a search to filter the information and sort the documents by the published field. But the Comments section is still empty, so let us add the functionality to add comments.

Go to our model file news.js, and add the following method at the end:

In the previous method, first we create the session to work in our server then we get the schema and the collection. Once we have the collection we are calling the modify method specifying the _id of the post where we want to add the new comment. The comment is added to the document using the arrayAppend method which appends the comment to the end of the array. Once the execute method finishes, we verify how many documents were affected and sent the results back basis on it.

Then we need to update our factory (app.module.js), adding the new module that will be used to add comments newComment:

Also we need to add the AngularJS $location and $filter modules. We are going to use the $location module to change the URL of the application, and the $filter module to know to which post a new comment belongs.

Finally, we are going to add the method to create comments or cancel the addition:

In the addComment method, we send the data to the /comments route. Then the result returned is added to our local copy of the data. The $filter module is used to find by _id the post to which belongs the new comment. To finish we change the location of the application to the main page.

The next step is to update our route provider in the app.config.js file to handle the add comments route:

Now as we did for the components to display the news, we are going to create a folder in the public/javascripts path with the name comments, this folder will contain the components and the template to add new comments. We are going to start adding the new-comment.module.js component to the folder with the following content:

angular.module('newComment', ['ngRoute']);

In the file we are specifying that our new module has the name newComment and it will use the ngRoute module. To continue we are going to add another component named new-comment.component.js with the following code:

The previous code configures the template that is going to be used to add new comments and defines the controller to be used in the view. The controller receives the _id of the news to update and is stored in a controller property, then two methods are defined: addComment that sends the new comment to be added to our factory and cancelAddComment that cancels the current action.

The template that we are going to use to add new comments is going to be called new-comment.template.html and is defined next:

The item template consists only of a text area where the user can enter a comment and some basic validation. The Submit button is enabled only when the comment passes validation, this is done by the ng-disabled directive that check if all the inputs in the form (addComment) has valid values. The ng-model directive is used to set the value of the input to a controller’s property, comment is the property’s name in this case.

Then we need to update our route file (routes/index.js) adding a new method to save the new comments:

This method sends the new comment to the model to be stored in collection. Once it has been stored successfully, it returns the comment in JSON format.

We are going to add a link that will load the new view when it is clicked, we are going to update the template in the path javascripts/newsList/news-list.template.html adding the following code under the paragraph that lists the comments:

And the last step is to add the reference to our new scripts in the header of the index file (views/index.ejs):

To test these changes, run the following command in a terminal and navigate to http://localhost:3000/:

$ npm start

We see the new option to add coments:

add_comment

And when we click on it, we see a form to write new comments:

submit_comment

We see the new comment below the news item in the index page:

display_comment

Adding new posts

To finish the application, we are going to add the feature to add new posts to the application. We are going to start adding a new method to our model (models/news.js) at the end:

This new method accepts as a parameter a JSON object to be added to the collection. Once the new document is stored in the collection it is returned to the application. If the document cannot be stored, the method returns null and display an error message to the user.

Then we need to update our factory (app.module.js), and again the first thing to update is to add the new module that will be used to add new posts addNews:

Now we are going to add the new methods that add a new post or cancel the process:

The first method receives the data for the new post and sends it to the /addNews route, the document returned is added to our local copy of the data and the app location is changed to the index.

Next we are going to update our route provider in the app.config.js file to handle the add news route:

Now we are going to create a folder in the public/javascripts path with the name news. This folder will contain the components and the template used to add new posts. Then we continue adding the add-news.module.js component to the folder with the following content:

angular.module('addNews', ['ngRoute']);

In the component we are specifying that our new module has the name addNews and it will use the ngRoute module. Then we add another component named add-news.component.js with the following code:

The previous code configures the template that is going to be used to add new posts and defines the controller to be used in the view. The controller receives the data to create a JSON object and set the published date to be the current date. The addNews method sends the JSON object to the factory and the cancelAddNews method cancels the current action.

The last file for this folder is going to be the view template named add-news.template.html, and is defined with the following:

The item template contains the text inputs to set the Title, Link and Introduction for the new post. It also contains some validation checks to enable the Submit button just when the required fields are valid. Also the value of the inputs is set to the controller’s properties: title, link and introduction.

Then we need to update our route file (routes/index.js) adding a new method to save the new post:

This method sends the new post to our model and it returns the new document added in JSON format once it is saved in the collection.

We are going to add a link that will load the new view when it is clicked, we are going to update the file in the path javascripts/newsList/news-list.template.html adding the following code under the paragraph that has the search and sort options:

And the last step is to add the reference to our new scripts in the header of the index file (views/index.ejs):

To test the latest changes, run the following command in a terminal and navigate to http://localhost:3000/:

$ npm start

We see the new option to add new posts:

add_post

And when we click on it, we see a form to add new posts:

submit_post

After we add a new post, it is displayed in the index page:

display_post

In this post we learned how to create a full stack JavaScript application using MySQL, Node.js, Express, AngularJS. We hope you enjoyed the post and please stay tuned because we have a lot of new content to share with you.

See you in the next blog post.

MySQL for Visual Studio 2.0.4 has been released

The MySQL Windows Experience Team is proud to announce the release of MySQL for Visual Studio 2.0.4 m3. Note that this is a development preview release and not intended for production usage.

MySQL for Visual Studio 2.0.4 m3 is the third development preview release of the MySQL for Visual Studio 2.0 series.  This series adds support for the new X DevAPI. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see http://dev.mysql.com/doc/x-devapi-userguide/en/. For more information about how the X DevAPI is implemented in MySQL for Visual Studio, and its usage, see http://dev.mysql.com/doc/refman/5.7/en/mysql-shell-visual-studio.html.

Please note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

You can download MySQL Installer from our official Downloads page at http://dev.mysql.com/downloads/installer/.

MySQL for Visual Studio 2.0.4 m3 can also be downloaded by using the product standalone installer found at http://dev.mysql.com/downloads/windows/visualstudio/, under the tab “Development Releases”.

Changes in MySQL for Visual Studio 2.0.4 m3

What’s new in 2.0.4 m3

  • Enhanced the logic that migrates stored MySQL connections to the MySQL Workbench connections.xml file. In previous versions, the migration was offered and if not done at that moment, the dialog to migrate was shown every time the MySQL Connections Manager dialog was shown, but there was no way to choose to remain storing MySQL connections in the MySQL for Visual Studio configuration folder instead of merging them with MySQL Workbench.  Now, the connection migration dialog offers a way to postpone the migration by either 1 hour, 1 day, 1 week, 1       month or indefinitely. If the migration is postponed, the dialog is shown again after that time elapses. If it is postponed indefinitely, then an option is added to the MySQL Connections Manager dialog that permits the connection migration to be done manually, as long as MySQL Workbench is installed.
  • When the MySQL Script Editor window is opened (either by selecting SQL, JavaScript, or Python from the New MySQL Script menu), the Results area was visible showing a big gray area in the editor, despite having nothing to display. Now, this area is hidden until results are ready to display, which happens after clicking Run.
  • The SQL syntax parser was updated to handle the optimizer hints that were introduced in MySQL 5.7.7.
  • The SQL editor was updated to check the syntax of a script before sending the statements to the server. If syntax errors are present, the Visual Studio Output pane displays the errors and the script is not executed.
  • A drop-down list of connections was added in the MySQL editor toolbar to enable fast switching among connections while editing SQL, JavaScript, and Python scripts. The list, located next to the Connect to MySQL button, shows all Server Explorer connections for the MySQL data provider with compatible protocol       support.  Note JavaScript and Python editors show only the connections that support the X Protocol.

Bugs Fixed

  • MySQL Workbench was unable to open connections added from the MySQL Connection Manager, as executing them would emit the following error: “Cannot connect to Database Server”. This is because an invalid “socket=.” value was added to the connection definition. To solve this, MySQL for Visual Studio was updated to use the latest MySQL.Utility version, which fixes code in the MySQL Workbench class to only write the socket value into the XML nodes if the connection is of type Sockets or Pipe.
    • Bug #24666952
  • After connections were added to the Server Explorer through the MySQL Connections Manager, clicking Refresh on the Server Explorer toolbar caused the refresh operation to hang, and nothing else could be done within the Server Explorer pane. Clicking Cancel Refresh was required to fix this.
    • Bug #24666931
  • SHOW statements executed from the SQL editor failed to display the expected output.
    • Bug #24622881
  • Queries executed from the SQL editor against nonunique data in NoSQL tables emitted error messages and failed to return result sets.
    • Bug #24462707
  • The main XML element in %APPDATA%\Oracle\MySQL For Visual Studio\settings.config was renamed to MySQLforVisualStudio from MySQLForExcel. Indentations were added to improve the overall readability of configuration settings.
    • Bug #24291264
  • JavaScript and Python editors permitted connections to versions of MySQL that did not support the X Protocol if those connections were unnamed or temporary, but the scripts did not work properly. This fix adds validation to ensure that all connections related to JavaScript and Python scripts are to servers with the X Plugin enabled.
    • Bug #24290570
  • Selecting the New MySQL Script context-menu option by right-clicking a Server Explorer connection in Visual Studio emitted an error if the connection was first closed and then opened.
    • Bug #24064816
  • Visual Studio was unable to open a design window for any selected table in a MySQL database.
    • Bug #23642010
  • Opening a new MySQL script file from the Visual Studio File > New menu displayed the following error: “Object reference not set to an instance of an object”.
    • Bug #23619311
  • Processing a Result object from the X Protocol substituted an error or information message in place of some collection output.
    • Bug #22983803
  • Advanced, Connect, and Cancel buttons were missing from the Connect to MySQL dialog for connections made from the script editor window.
    • Bug #22114315
  • Dragging and dropping a table from the Server Explorer in Visual Studio to a project would fail with the following error: “Some updating commands could not be generated automatically. The database returned the following error: You have a usable connection already”. After clicking OK, Visual Studio would emit the following error “Mouse drag operation failed. Could not retrieve schema information from table or view”. To solve this issue, MySql.Data was updated to version 6.9.9.
  • From the Query Designer, selecting Verify SQL Syntax would always display “Not supported by the provider” instead of verifying the SQL syntax.
  • The Python code editor displayed session options for JavaScript instead of options for the Python language.
  • In some cases, selecting Retrieve Data for a table with a valid connection failed to populate the table with data.

Quick links

Enjoy and thanks for the support!

MySQL for Visual Studio Team.

MySQL Connector/NET 7.0.4 m3 has been released

Dear MySQL users,

MySQL Connector/Net 7.0.4 is the first development release that expands cross-platform support to Linux and OS X when using Microsoft’s .NET Core framework. Now, .NET developers can use the X DevAPI with .NET Core and Entity Framework Core (EF Core) 1.0 to create server applications that run on Windows, Linux and OS X. We are very excited about this change and really look forward to your feedback on it!

MySQL Connector/Net 7.0.4 is also the third development release of MySQL Connector/Net to add support for the new X DevAPI. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models using a modern, NoSQL-like syntax that does not assume previous experience writing traditional SQL.

To learn more about how to write applications using the X DevAPI, see http://dev.mysql.com/doc/x-devapi-userguide/en/index.html. For more information about how the X DevAPI is implemented in Connector/Net, see http://dev.mysql.com/doc/dev/connector-net.

Please note that the X DevAPI requires at least MySQL Server version 5.7.12 or higher with the X Plugin enabled. For general documentation about how to get started using MySQL as a document store, see http://dev.mysql.com/doc/refman/5.7/en/document-store.html.

To download MySQL Connector/Net 7.0.4 M3, see the “Development Releases” tab at http://dev.mysql.com/downloads/connector/net/

Also you can find the new nuget packages for the .net core framework at the nuget gallery or using the following direct links:

MySql.Data package for core framework

MySql.Data.EntityFrameworkcore for core framework

Packages for full .net framework

MySql.Data package

MySql.Data.Entity package

MySql.Web package

MySql.Fabric package

Functionality Added or Changed in 7.0.4 M3 development release

  • Added X DevAPI support for URI connection strings in the following formats:
    mysqlx://[<user>[:<password>]@]<host>[:<port>]
    mysqlx://[<user>[:<password>]@]<host>[:<port>]/<database>
    mysqlx://[<user>[:<password>]@]<host>[:<port>]/[<database>]?<option>=<value>[&<option>=<value>]
    mysqlx+ssh://[<user>[:<password>]@]<host>[:<port>]
  • Added X DevAPI support for flexible parameter lists that do not require string parsing.
  • Views are no longer implemented using a separate View class in the X DevAPI. Instead, the IsView property has been added to the Table class and views are implemented as tables.
  • Added in MySql.Data support for .NET Core 1.0, which runs on Windows, OS X, and Linux.
  • Added in MySQL.Data.EntityFrameworkCore support for Entity Framework (EF) Core (includes support for .NET Framework 4.5.1).

Bugs Fixed

  • Passing no document or a DbDoc object that contained an empty array to the Add() method of a collection would throw an exception. Now, passing in either an empty document or array of documents returns a Results object in which RecordsAffected is zero. (Bug #23542066)
  • Passing a DbDoc object that contained an array to the Add() method of a collection would throw an exception. (Bug #23542031)

We love to hear your thoughts or any comments you have about our product. Please send us your feedback at our forums, fill a bug at our community site, or leave us any comment at the social media channels.

Enjoy and thanks for the support!

On behalf of the MySQL Connector/Net team

MySQL Notifier 1.1.7 has been released

Dear MySQL users,

The MySQL Windows Experience Team is proud to announce the release of MySQL Notifier version 1.1.7.

MySQL Notifier enables developers and DBAs to easily monitor, start and stop all their MySQL database instances. It provides a familiar Microsoft SQL Server look and feel and integrates with MySQL Workbench.

 

MySQL Notifier is installed using the MySQL Installer for Windows.

The MySQL Installer for Windows comes in 2 versions

  • Full (150 MB) which includes a complete set of MySQL products with their binaries included in the download
  • Web (1.5 MB – a network install) which will just pull the MySQL Notifier over the web and install it when run.

You can download the MySQL Installer for Windows from our official Downloads page at http://dev.mysql.com/downloads/installer/.

 

Changes in MySQL Notifier 1.1.7 (2016-08-01)

Functionality Added or Changed

  • The automatic migration of connections from MySQL Notifier to MySQL Workbench can now be delayed for a period of time or suspended indefinitely.
  • An option for setting the interval to ping monitored MySQL Server instances for status changes was added to the Actions > Options menu.

Bugs Fixed

  • The Configure Instance option was renamed to Manage Instance.
  • An error was displayed when valid domain user credentials were used to add a remote Windows service.
  • Testing new connections for remote MySQL instances would display a “High Severity Error” message.
  • When MySQL Notifier was installed using the MySQL Installer for Windows, an exception was thrown and MySQL Notifier failed to start.
  • The SQL Editor… link to MySQL Workbench was disabled for each monitored MySQL instance or service when MySQL Workbench was installed.
  • Validation checking identified domain\username as an invalid format to use when adding a remote Windows service to be monitored. Now, domain users with sufficient permissions to access the remote computer can connect using the domain\username format.
  • The main XML element in “%APPDATA%\Oracle\MySQL Notifier\settings.config” was renamed to MySQLNotifier from MySQLForExcel. Indentations were added to improve the overall readability of configuration settings.
  • Windows services on a remote computer were not retrieved and could not be added to the list services to be monitored.
  • A corrupted connections.xml file caused MySQL Notifier to display a “Root element is missing” error message when started.
  • Attempts to subscribe to a remote Windows service for the first time failed and returned a “High Severity Error” message when the remote service was an instance of MySQL.
  • Case-sensitive naming prevented status changes to MySQL instances running as services for Windows. Now, service names for MySQL instances are added with compatible casing.
  • A “High Severity Error” raised by MySQL Notifier appeared intermittently during installations performed using the MySQL Installer for Windows.

 

Quick links:

Enjoy and thanks for the support!

Develop By Example – Document Store: Working with documents using Node.js

In the previous blog post we explained how work with the collection CRUD operations. In this blog post we are going to explain other functions that are related to document management.

We already know how to create collections, as well as how to add, delete, update and retrieve documents from them. But, how can we add a new field to a document or documents that are in a collection?

The following code demonstrates how to do it:

In the previous code, first we get the schema and the collection objects (schema and coll). Then we define the query variable that contains the where clause, and newDoc with the document used in the example. Next, we call the coll object’s modify method that receives the query variable as a parameter. Chained to the modify method is the set method, which receives a pair of objects; the first one is the field name and the second one is the value for the field. If you have already read the previous blog post, you may notice that the code is the same as the one used to update the value of a field in a document, except for the line that adds the new document we want to work with. The set method is used to add new fields or to update their value. Once the execute method is finished, the number of documents updated are written to the console as well as the updated document, and finally the connection is closed.

The following code demonstrates how to add multiple fields to a document:

As you can see in the previous code, we just chained the set method two times to add multiple fields to a document. You can chain the set method as many times as needed. If the query passed to the modify method finds multiple documents, all the documents found will be updated adding the new fields sent; so be careful with it.

What if we want to remove a field? We just need to use the unset method to accomplish this. In the next example we demonstrate how to do it:

The previous code looks almost identical to the code used to add fields to a document; the difference is that we use the unset method instead of the set method. The rest of the code is the same.

In the previous examples we used an array as the value for one of the fields of the documents we added. Connector/Node.js also has methods to handle the items inside the array.

If we want to add items to an array we have several options. One such option is demonstrated in the following example:

The code looks quite similar to the code of the previous examples; there is just a new method: appendArray. The method receives two parameters; the first one is the field that has an array as its value. The second parameter is the value for the item to be added, which will be added at the end of the array.

As well as for the set method, if you send a query to the modify method that returns multiple documents; all the documents will be updated as well.

Another way of adding items to an array field is by using the arrayInsert method:

As you can see the previous code is almost equal to the code where we used the appendArray method. The only difference is that we now use the arrayInsert method instead. The arrayInsert method receives two parameters; the first one is the name of the field including the index of the array where you want to insert the new item, if the index is out of bounds it will be inserted at the end of the array. The second parameter is the value of the new item.

If you remember, in previous examples we used the set method to update field values and to add new fields to a document.

Well we can also use set to add new items to an array. The following code demonstrates how to do it.

The code above is identical to previous examples which used the set method. In this example when calling the set method we use an index that is out of bounds, by doing that the method understands that the item does not exist in the array; so it will be added to the end of the array. But if the index passed to the method is the index of an existing item, the method will update it instead of adding a new item.

At this point we know how to add and update items in an array, but also we can remove items. The following code is demonstrates this:

In this example we pass the name of the field and the index position of the item we want to remove as parameters to the unset method. If we pass an index that is out of bounds of the array, no item will be removed from the array and we will not receive an exception, so be careful with that.

See you in the next blog post.

Develop By Example – Document Store: working with collections using Node.js

In the previous blog post we explained how to create schemas and collections. In this one we are going to explain how to work with collections: adding, updating and deleting documents.

The following code demonstrates how to add a single document to an existing collection:

In the previous code, first we get the objects that represent the schema (schema) and the collection (coll) that we want to work with. An object using JSON (newDoc) is created and is passed as a parameter to the coll object’s add method. Calling the execute method the document is added to the collection. Once the execute method has finished, we receive an object (added) that contains information about the document added. To verify if the document was added, we can call the added object’s getAffectedItemsCount method, which will return how many documents were added.

But, what if you want to add multiple documents?

You can do it with almost no changes in your code. The following code adds two documents at the same time:

As you can see, the previous code is almost identical to the first example. We just add an extra line to declare the new document (newDoc2), and we add the new document as a parameter in the coll object’s add method. At the end we call added object’s getAffectedItemsCount method to verify we added the two documents.

Now we know how to add multiples documents to a collection using multiple variables and passing them as parameters, but we can also do the same using an Array object. In the following code example we create an array object to use it to add new documents to a collection.

The previous code is almost identical to the first example; the difference is that we pass an array object as a parameter instead of a JSON object. The rest of the code is the same. This could be useful if you receive an array of objects from the client or if you load the data from a JSON file, you just pass the whole array to upload it to the collection.

Updating a field in a document is also very easy to do. The following code is an example of how to do it:

In the previous code, first we get the objects that represent the schema (schema) and the collection (coll) we want to work. Then we declare the query variable which contains the where clause for our update. Next, we call the coll object’s modify method that receives the query variable as a parameter. Chained to the modify method is the set method, which receives a pair of objects; the first one is the field to update and the second one the new value to be set. As we did in our previous examples we call the execute method to perform the action requested. When the execute method finishes we receive an object (updated) with information about the update. To know how many documents were updated we call the updated object’s getAffectedItemsCount method.

Now that we know how to add and update documents in a collection, we are going to explain how to remove them. The following code demonstrates it.

The previous code defines the objects that represent the schema (schema) and the collection (coll) where we want to work. Then, we define the query variable again to contain the where clause for our operation, the remove in this case. To remove a document we call the coll object’s remove method followed by the execute method. Once the execute method is completed, we receive an object (deleted) with the information about the operation that has finished. By calling the deleted object’s getAffectedItemsCount method, we know how many documents were removed from the collection.

Now we are going to see how to get documents from a collection. In the following example, we are retrieving the document that match the _id that we want:

The previous code defines the objects that represent the schema (schema) and the collection (coll) we want to work with. Then the query variable is defined and the where clause is set to it. Then we call the coll object’s execute method to perform the query. When the execute method completes, we receive the document that match our search criteria and is send to the console to view it.

But, what if we want all the records from a collection? Well that is simple; we just need to remove the search criteria from the find method. The updated code would look like the following:

Now we know how to search a specific document and get all the documents from a collection. What if we want to get just some number of documents that match query criteria? The next example shows the code to do it:

The previous code looks very similar to the example that returns one document with a specific _id, the difference here is that our query is performing a like and we are adding a call to the coll object’s limit method. Note that the query statement is case sensitive; this means that if we have documents that have  ‘test’ in the field ‘name’, those documents will not be returned because we are searching for ‘Test’ names.

See you in the next blog post.