Mysql – Add info to thesql view around an IP address – 10.10.1.100 to https://10.10.1.100

MySQLview

I have built a database in MySQL to replace the use of MANY excel spreadsheets that document our servers, network, IP addresses, etc.

In that database I have created a view called DRAC (dell remote access card) that pulls a server name, asset tag, and IP address.

SELECT Matrix.Name AS Name,
  Matrix.DracIP AS DracIP,
  Matrix.Asset
FROM Matrix
WHERE Matrix.Type = 'Physical-Lou1'

I have been able to pull this information to a webpage (w/ DokuWiki+sqlcomp plugin) and it generates a nice table for me.

I was wondering if there was some kind of foo I could use to translate the ip address from this: "10.10.1.100" into this "https://10.10.1.100" within the view so it will display on the page as links that people can use instead of just a bare IP.

EDIT: I ended up w/ this code for those that are interested.

SELECT Matrix.Name AS Name,
  Concat('<a href="https://', Matrix.DracIP, '">', Matrix.DracIP,'</a>') AS DracIP,
  Matrix.Asset AS Asset                                                         
FROM Matrix
WHERE Matrix.Type = 'Physical-Lou1'

Best Answer

SELECT Matrix.Name AS Name,
  CONCAT('https://',Matrix.DracIP) AS DracIP,
  Matrix.Asset
FROM Matrix
WHERE Matrix.Type = 'Physical-Lou1'

Related Solutions

Mysql – View metadata refresh in MySQL

To see the view's definition, run one of the following:

SHOW CREATE VIEW viewname\G
SELECT * from information_schema.views where table_name='viewname'\G

Here is a quick example:

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `age` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from mytable;
+----+-----+
| id | age |
+----+-----+
|  1 |  10 |
|  2 |  15 |
|  3 |  20 |
|  4 |   5 |
+----+-----+
4 rows in set (0.00 sec)

mysql> create view mytable_tens as select * from mytable where MOD(age,10) = 0;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from mytable_tens;
+----+-----+
| id | age |
+----+-----+
|  1 |  10 |
|  3 |  20 |
+----+-----+
2 rows in set (0.00 sec)

mysql> show create view mytable_tens\G
*************************** 1. row ***************************
                View: mytable_tens
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `mytable_tens` AS select `mytable`.`id` AS `id`,`mytable`.`age` AS `age` from `mytable` where ((`mytable`.`age` % 10) = 0)
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> select * from information_schema.views where table_name='mytable_tens'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: johnlocke
          TABLE_NAME: mytable_tens
     VIEW_DEFINITION: select `johnlocke`.`mytable`.`id` AS `id`,`johnlocke`.`mytable`.`age` AS `age` from `johnlocke`.`mytable` where ((`johnlocke`.`mytable`.`age` % 10) = 0)
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: @
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
1 row in set (0.02 sec)

Whichever method you choose, you can visibly see the query making up the view. You can sculpt the CREATE OR REPLACE VIEW using whatever is shown.

Hope this helps, and Welcome to the DBA MinionExchange !!!

Mysql – Determining what algorithm MYSQL View is using

If the view is being processed with the TEMPTABLE algorithm, the view will be treated by the optimizer as if it were written as a subquery in the from clause (derived table) and should show up in the EXPLAIN output as a derived table.

Here's an example of a view that was explicitly defined with the TEMPTABLE algorithm. Two tables show up in the query plan, although this view was just defined as SELECT * from a single table.

+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   33 | NULL  |
|  2 | DERIVED     | task       | ALL  | NULL          | NULL | NULL    | NULL |   33 | NULL  |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+

When the view is defined with the UNDEFINED algorithm, the optimizer...

prefers MERGE over TEMPTABLE if possible

^{— http://dev.mysql.com/doc/refman/5.6/en/view-algorithms.html (also previous versions ~sqlbot)}

Aggregate functions/group by/having, distinct, limit, unions, scalar subqueries, and views that return literals only (not from a table) require TEMPTABLE be used... a view without those should be processed as MERGE consistently when the algorithm on the view definition is the UNDEFINED algorithm.

Because of the nature of the two view algorithms, the only one that can use indexes on the underlying tables to find rows matching the outer WHERE -- not one inside the view definition, but the one SELECT ... FROM view WHERE << here >>. If the EXPLAIN output shows those indexes being used the way you would typically hope, then you're getting MERGE.

I generally define views as MERGE explictly. The server will accept and save the definition, but somewhat quietly change it back to UNDEFINED and throw a warning if it can't be actually be processed with MERGE.

mysql> CREATE ALGORITHM=MERGE VIEW priority_counts AS SELECT priority_id, COUNT(1) AS quanity FROM task GROUP BY priority_id;
Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------------------+
| Level   | Code | Message                                                                       |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined algorithm) |
+---------+------+-------------------------------------------------------------------------------+
1 row in set (0.08 sec)

SHOW CREATE VIEW subsequently shows ALGORITHM=UNDEFINED.

Of course, when MERGE is accepted, it does show in the view definition, as does TEMPTABLE when it's explicitly declared that way.

Best Answer

Related Solutions

Mysql – View metadata refresh in MySQL

Mysql – Determining what algorithm MYSQL View is using

Related Question