MX Administrative Interface: Difference between revisions

Line 260:

=== MySQL settings ===

Cumulus MX includes functionality not in Cumulus 1, and this is one example of a new feature. It is designed to automate updating of MySQL databases whose schema has each table based on one of the Cumulus log files. This MX feature was developed from [[ImportCumulusFile|this script]] for Cumulus 1.

'''IMPORTANT''' The MX automatic standard updates described below only work if the database table they are updating has columns named exactly as defined in the standard update. All the MX updates will work even if you have defined additional columns in the standard tables and update those in a separate process. But when extra columns are added as at versions 3.6.0 and 3.7.0, the MX updates will NOT work unless you have added the extra columns introduced in those releases with correct names. For those releases, the release announcement includes a script to run to add the extra columns needed for those (and subsequent) releases.

==== Brief background on SQL ====

It was IBM who first invented the concept of Relational Databases in the 1970s and they needed a language for all aspects of interaction with the new database and they called it Structured (English) Query Language. The brackets indicate the word was later removed. You may find when SQL is talked about, it is either pronounced "sequel" (as if there is still a "E" after the "S") or the 3 letters are simply spelt out.

*SQL is not '''Structured''' in the modern computing usage of that word concerning languages that can implement decision trees; instead the word structured here comes from the ability to make a query from a main query and a sub-query, although ~~you might believe~~ it ~~relates to how~~ the key words must appear in a particular order.

It was IBM who first invented the concept of Relational Databases in the 1970s and they needed a language for all aspects of interaction with the new database and they called it Structured (English) Query Language. The brackets indicate the word that was later removed. You may find when SQL is talked about, it is either pronounced "sequel" (as if there is still a "E" after the "S") or the 3 letters are simply spelt out.

*SQL is not '''Structured''' in the modern computing usage of that word concerning languages that can implement decision trees; instead the word structured here comes from the ability to make a query from a main query and a sub-query, although for some people it is a useful reminder that the key words must appear in a particular order.

*SQL is not just for running '''Queries''', it can give and revoke access permissions, create and drop tables, and do many more tasks than just query a database to get results.

*SQL is a '''Language''' as it does have a set vocabulary, a defined sequence in which key words must appear, and it is used for describing tasks to a database.

A relational database has to satisfy a number of conditions, but the basic one is that all data appears in a table with rows and columns. The columns have a particular order, but there is no control over the order of rows, so you can't specify a row number, you '''either''' specify a primary key that identifies a particular row, '''or''' you specify a sort by the value in any column in a particular order and ~~which row (or~~ rows~~) you want out of~~ that sorted order.

A relational database has to satisfy a number of conditions, but the basic one is that all data appears in a table with rows and columns. The columns have a particular order, but there is no control over the order of rows, so you can't specify a row number, you '''either''' specify a primary key that identifies a particular row, '''or''' you specify a sort by the value in any column in a particular order and SQL then returns your selected rows in that sorted order.

Like happened with video recorders and browsers, there was a relational database war, and thus division in language adoption, in the 1980s between 2 big players IBM's SQL and Ingres' QUEL, each gaining popularity in different ways, but the latter lost out, with newcomer Oracle taking the lion share of the commercial usage of SQL soon so very widely adopted, it achieved world dominance. There were a number of minor players who implemented their own relational databases, and initially their own languages, but SQL obtained a ISO definition in 1985 and was then widely adopted, surviving the invention of the internet, and the move from mainframes to small devices. MySQL is one of the rivals, but all versions of SQL are related and the dialect differences are comparatively minor compared to the commonality of the majority of the language. SQL is designed to be largely independent of how data is stored, so from 2000 as per newer standard SQL now works with [[Xml_webtags|XML]] as well as relational databases.

Like happened with video recorders and browsers, there was a relational database war, and thus division in language adoption, in the 1980s between 2 big players IBM's SQL and Ingres' QUEL, each gaining popularity in different ways, but the latter lost out, with newcomer Oracle taking the lion share of the commercial usage of SQL soon so very widely adopted, it achieved world dominance. There were a number of minor players who implemented their own relational databases, and initially their own languages, but SQL obtained a ISO definition in 1985 and was then widely adopted, surviving the invention of the internet, and the move from mainframes to small devices.

MySQL is one of the rivals, but all versions of SQL are related and the dialect differences are comparatively minor compared to the commonality of the majority of the language. SQL is designed to be largely independent of how data is stored, so from 2000 as per newer standard for SQL, it now works with [[Xml_webtags|XML]] as well as relational databases. That said, because it can define creation of columns, some SQL code is dependent on how data is stored in a particular database system.

==== Implementation ====

~~==== Implementation ====~~

Mono and .Net implement SQL capability, and this is utilised by Cumulus MX in the queries it generates. However, for SQL to work on your web site, you need to have a relational database available on your web server that accepts the MySQL that MX generates. It is worth saying that one difference between MySQL and the SQL standard is the former can insert multiple rows in one MySQL statement, but in the standard for SQL the specification says only one row can be inserted by one statement. Having made that clear, the SQL that is generated by the MX engine does only insert one row at a time, so the SQL MX generates is standard SQL.

MX does not include a database to install on your web site. Note, this does not mean your web server must have a MySQL database, as other products will understand the updating SQL (because of the dialect, commonality, and standard-meeting points I have just made), so the automatic updating should always work. One common difference between products is which data types available, so ''it is just possible that you might'' have a database that does not understand the column definitions in the '''MX option to create a table for you''' using MySQL dialect, so do be prepared in case you have to create each table yourself using a different method, the SQL to create a table is fairly simple, it even starts "CREATE TABLE table_name", but it is the list of columns with their data types and sizes that follows that is tricky if you don't know what is allowed on the database you are using.

Equally, the separate application, '''ExportMySQL.exe''' mentioned in its context below, includes SQL to create a table (with MYSQL data types) and add multiple rows in one instruction, so it is only able to work with databases that use MySQL (the clue is in the name of the application).

Equally, the separate application, '''ExportMySQL.exe''' mentioned in its context below, includes SQL to create a table (with MYSQL data types) and add multiple rows in one instruction, so it is only able to work with databases that use MySQL (the clue is in the name of the application), or databases where the tables have already been set up so it is just issuing '''Insert Ignore''' SQL.

====Mandatory section====

* Server Details - expand this drop down as it is used for essential information for any access to the database on your web server:

*# Enter your host name or a IPv4 address for your web server. If you host your own server, it might be something like 127.0.0.1. It will be the same as you enter for host in the "internet settings" screen.

MX Administrative Interface: Difference between revisions

Navigation menu

Page actions

Page actions

Personal tools

Wiki navigation

Search

Cumulus

Tools

@@ Line 260: / Line 260: @@
 === MySQL settings ===
 Cumulus MX includes functionality not in Cumulus 1, and this is one example of a new feature.  It is designed to automate updating of MySQL databases whose schema has each table based on one of the Cumulus log files. This MX feature was developed from [[ImportCumulusFile|this script]] for Cumulus 1.
+'''IMPORTANT''' The MX automatic standard updates described below only work if the database table they are updating has columns named exactly as defined in the standard update. All the MX updates will work even if you have defined additional columns in the standard tables and update those in a separate process. But when extra columns are added as at versions 3.6.0 and 3.7.0, the MX updates will NOT work unless you have added the extra columns introduced in those releases with correct names. For those releases, the release announcement includes a script to run to add the extra columns needed for those (and subsequent) releases.
 ==== Brief background on SQL ====
-It was IBM who first invented the concept of Relational Databases in the 1970s and they needed a language for all aspects of interaction with the new database and they called it Structured (English) Query Language. The brackets indicate the word was later removed. You may find when SQL is talked about, it is either pronounced "sequel" (as if there is still a "E" after the "S") or the 3 letters are simply spelt out.
-*SQL is not '''Structured''' in the modern computing usage of that word concerning languages that can implement decision trees; instead the word structured here comes from the ability to make a query from a main query and a sub-query, although you might believe it relates to how the key words must appear in a particular order.
+It was IBM who first invented the concept of Relational Databases in the 1970s and they needed a language for all aspects of interaction with the new database and they called it Structured (English) Query Language. The brackets indicate the word that was later removed. You may find when SQL is talked about, it is either pronounced "sequel" (as if there is still a "E" after the "S") or the 3 letters are simply spelt out.
+*SQL is not '''Structured''' in the modern computing usage of that word concerning languages that can implement decision trees; instead the word structured here comes from the ability to make a query from a main query and a sub-query, although for some people it is a useful reminder that the key words must appear in a particular order.
 *SQL is not just for running '''Queries''', it can give and revoke access permissions, create and drop tables, and do many more tasks than just query a database to get results.
 *SQL is a '''Language''' as it does have a set vocabulary, a defined sequence in which key words must appear, and it is used for describing tasks to a database.
-A relational database has to satisfy a number of conditions, but the basic one is that all data appears in a table with rows and columns. The columns have a particular order, but there is no control over the order of rows, so you can't specify a row number, you '''either''' specify a primary key that identifies a particular row, '''or''' you specify a sort by the value in any column in a particular order and which row (or rows) you want out of that sorted order.
+A relational database has to satisfy a number of conditions, but the basic one is that all data appears in a table with rows and columns. The columns have a particular order, but there is no control over the order of rows, so you can't specify a row number, you '''either''' specify a primary key that identifies a particular row, '''or''' you specify a sort by the value in any column in a particular order and SQL then returns your selected rows in that sorted order.
-Like happened with video recorders and browsers, there was a relational database war, and thus division in language adoption, in the 1980s between 2 big players IBM's SQL and Ingres' QUEL, each gaining popularity in different ways, but the latter lost out, with newcomer Oracle taking the lion share of the commercial usage of SQL soon so very widely adopted, it achieved world dominance. There were a number of minor players who implemented their own relational databases, and initially their own languages, but SQL obtained a ISO definition in 1985 and was then widely adopted, surviving the invention of the internet, and the move from mainframes to small devices. MySQL is one of the rivals, but all versions of SQL are related and the dialect differences are comparatively minor compared to the commonality of the majority of the language. SQL is designed to be largely independent of how data is stored, so from 2000 as per newer standard SQL now works with [[Xml_webtags|XML]] as well as relational databases.
+Like happened with video recorders and browsers, there was a relational database war, and thus division in language adoption, in the 1980s between 2 big players IBM's SQL and Ingres' QUEL, each gaining popularity in different ways, but the latter lost out, with newcomer Oracle taking the lion share of the commercial usage of SQL soon so very widely adopted, it achieved world dominance. There were a number of minor players who implemented their own relational databases, and initially their own languages, but SQL obtained a ISO definition in 1985 and was then widely adopted, surviving the invention of the internet, and the move from mainframes to small devices.
+MySQL is one of the rivals, but all versions of SQL are related and the dialect differences are comparatively minor compared to the commonality of the majority of the language. SQL is designed to be largely independent of how data is stored, so from 2000 as per newer standard for SQL, it now works with [[Xml_webtags|XML]] as well as relational databases. That said, because it can define creation of columns, some SQL code is dependent on how data is stored in a particular database system.
+==== Implementation ====
-==== Implementation ====
 Mono and .Net implement SQL capability, and this is utilised by Cumulus MX in the queries it generates. However, for SQL to work on your web site, you need to have a relational database available on your web server that accepts the MySQL that MX generates. It is worth saying that one difference between MySQL and the SQL standard is the former can insert multiple rows in one MySQL statement, but in the standard for SQL the specification says only one row can be inserted by one statement. Having made that clear, the SQL that is generated by the MX engine does only insert one row at a time, so the SQL MX generates is standard SQL.
 MX does not include a database to install on your web site.  Note, this does not mean your web server must have a MySQL database, as other products will understand the updating SQL (because of the dialect, commonality, and standard-meeting points I have just made), so the automatic updating should always work. One common difference between products is which data types available, so ''it is just possible that you might'' have a database that does not understand the column definitions in the '''MX option to create a table for you''' using MySQL dialect, so do be prepared in case you have to create each table yourself using a different method, the SQL to create a table is fairly simple, it even starts "CREATE TABLE table_name", but it is the list of columns with their data types and sizes that follows that is tricky if you don't know what is allowed on the database you are using.
-Equally, the separate application, '''ExportMySQL.exe''' mentioned in its context below, includes SQL to create a table (with MYSQL data types) and add multiple rows in one instruction, so it is only able to work with databases that use MySQL (the clue is in the name of the application).
+Equally, the separate application, '''ExportMySQL.exe''' mentioned in its context below, includes SQL to create a table (with MYSQL data types) and add multiple rows in one instruction, so it is only able to work with databases that use MySQL (the clue is in the name of the application), or databases where the tables have already been set up so it is just issuing '''Insert Ignore''' SQL.
 ====Mandatory section====
 * Server Details - expand this drop down as it is used for essential information for any access to the database on your web server:
 *# Enter your host name or a IPv4 address for your web server. If you host your own server, it might be something like 127.0.0.1. It will be the same as you enter for host in the "internet settings" screen.