/* ----------------------------------------------------------------------- *//**
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 *
 * @file model_arch_table.sql_in
 *
 * @brief SQL functions for multilayer perceptron
 * @date June 2012
 *
 *
 *//* ----------------------------------------------------------------------- */

m4_include(`SQLCommon.m4')
/**
@addtogroup grp_keras_model_arch

@brief Utility function to load model architectures and weights into a table.

\warning <em> This MADlib method is still in early stage development.
Interface and implementation are subject to change. </em>

<div class="toc"><b>Contents</b><ul>
<li class="level1"><a href="#load_keras_model">Load Model</a></li>
<li class="level1"><a href="#delete_keras_model">Delete Model</a></li>
<li class="level1"><a href="#example">Examples</a></li>
<li class="level1"><a href="#related">Related Topics</a></li>
</ul></div>

This utility function loads model architectures and
weights into a table for use by deep learning algorithms.
Model architecture is in JSON form
and model weights are in the form of PostgreSQL binary data types (bytea).
If the output table already exists, a new row is inserted
into the table so it can act as a repository for multiple model
architectures and weights.

There is also a utility function to delete a model
from the table.

@anchor load_keras_model
@par Load Model

<pre class="syntax">
load_keras_model(
    keras_model_arch_table,
    model_arch,
    model_weights,
    name,
    description
    )
</pre>
\b Arguments
<dl class="arglist">
  <dt>keras_model_arch_table</dt>
  <dd>VARCHAR. Output table to load keras model architecture and weights.
  </dd>

  <dt>model_arch</dt>
  <dd>JSON. JSON of the model architecture to load.
  </dd>

  <dt>model_weights (optional)</dt>
  <dd>bytea. Model weights to load as a PostgreSQL
  binary data type.
  </dd>

  <dt>name (optional)</dt>
  <dd>TEXT, default: NULL. Free text string to provide
  a name, if desired.
  </dd>

  <dt>description (optional)</dt>
  <dd>TEXT, default: NULL. Free text string to provide
  a description, if desired.
  </dd>

</dl>

<b>Output table</b>
<br>
    The output table contains the following columns:
    <table class="output">
      <tr>
        <th>model_id</th>
        <td>SERIAL PRIMARY KEY. Model ID.
        </td>
      </tr>
      <tr>
        <th>model_arch</th>
        <td>JSON. JSON blob of the model architecture.
        </td>
      </tr>
      <tr>
        <th>model_weights</th>
        <td>BYTEA. Weights of the model which may be used for warm start
        or transfer learning.
        Weights are stored as a PostgreSQL binary data type.
        </td>
      </tr>
      <tr>
        <th>name</th>
        <td>TEXT. Name of model (free text).
        </td>
      </tr>
      <tr>
        <th>description</th>
        <td>TEXT. Description of model (free text).
        </td>
      </tr>
      <tr>
        <th>__internal_madlib_id__</th>
        <td>TEXT. Unique id for model arch.  This is an id used internally be MADlib.
        </td>
      </tr>
    </table>
</br>

@anchor delete_keras_model
@par Delete Model

<pre class="syntax">
delete_keras_model(
    keras_model_arch_table
    model_id
)
</pre>
\b Arguments
<dl class="arglist">
  <dt>keras_model_arch_table</dt>
  <dd>VARCHAR. Table containing model architectures and weights.
  </dd>

  <dt>model_id</dt>
  <dd>INTEGER. The id of the model to be deleted.
  </dd>
</dl>

@anchor example
@par Examples
-# Define model architecture.  Use Keras to define
the model architecture:
<pre class="example">
import keras
from keras.models import Sequential
from keras.layers import Dense
model_simple = Sequential()
model_simple.add(Dense(10, activation='relu', input_shape=(4,)))
model_simple.add(Dense(10, activation='relu'))
model_simple.add(Dense(3, activation='softmax'))
model_simple.summary()
\verbatim

_________________________________________________________________
Layer (type)                 Output Shape              Param #
=================================================================
dense_1 (Dense)              (None, 10)                50
_________________________________________________________________
dense_2 (Dense)              (None, 10)                110
_________________________________________________________________
dense_3 (Dense)              (None, 3)                 33
=================================================================
Total params: 193
Trainable params: 193
Non-trainable params: 0
\endverbatim
</pre>
Export the model to JSON:
<pre class="example">
model_simple.to_json()
</pre>
<pre class="result">
'{"class_name": "Sequential", "keras_version": "2.1.6", "config": [{"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "VarianceScaling", "config": {"distribution": "uniform", "scale": 1.0, "seed": null, "mode": "fan_avg"}}, "name": "dense_1", "kernel_constraint": null, "bias_regularizer": null, "bias_constraint": null, "dtype": "float32", "activation": "relu", "trainable": true, "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", "config": {}}, "units": 10, "batch_input_shape": [null, 4], "use_bias": true, "activity_regularizer": null}}, {"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "VarianceScaling", "config": {"distribution": "uniform", "scale": 1.0, "seed": null, "mode": "fan_avg"}}, "name": "dense_2", "kernel_constraint": null, "bias_regularizer": null, "bias_constraint": null, "activation": "relu", "trainable": true, "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", "config": {}}, "units": 10, "use_bias": true, "activity_regularizer": null}}, {"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "VarianceScaling", "config": {"distribution": "uniform", "scale": 1.0, "seed": null, "mode": "fan_avg"}}, "name": "dense_3", "kernel_constraint": null, "bias_regularizer": null, "bias_constraint": null, "activation": "softmax", "trainable": true, "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", "config": {}}, "units": 3, "use_bias": true, "activity_regularizer": null}}], "backend": "tensorflow"}'
</pre>
-# Load into model architecture table:
<pre class="example">
DROP TABLE IF EXISTS model_arch_library;
SELECT madlib.load_keras_model('model_arch_library',  -- Output table,
$$
{"class_name": "Sequential", "keras_version": "2.1.6", "config": [{"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "VarianceScaling", "config": {"distribution": "uniform", "scale": 1.0, "seed": null, "mode": "fan_avg"}}, "name": "dense_1", "kernel_constraint": null, "bias_regularizer": null, "bias_constraint": null, "dtype": "float32", "activation": "relu", "trainable": true, "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", "config": {}}, "units": 10, "batch_input_shape": [null, 4], "use_bias": true, "activity_regularizer": null}}, {"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "VarianceScaling", "config": {"distribution": "uniform", "scale": 1.0, "seed": null, "mode": "fan_avg"}}, "name": "dense_2", "kernel_constraint": null, "bias_regularizer": null, "bias_constraint": null, "activation": "relu", "trainable": true, "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", "config": {}}, "units": 10, "use_bias": true, "activity_regularizer": null}}, {"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "VarianceScaling", "config": {"distribution": "uniform", "scale": 1.0, "seed": null, "mode": "fan_avg"}}, "name": "dense_3", "kernel_constraint": null, "bias_regularizer": null, "bias_constraint": null, "activation": "softmax", "trainable": true, "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", "config": {}}, "units": 3, "use_bias": true, "activity_regularizer": null}}], "backend": "tensorflow"}
$$
::json,  -- JSON blob
                               NULL,                  -- Weights
                               'Sophie',              -- Name
                               'A simple model'       -- Descr
);
SELECT COUNT(*) FROM model_arch_library;
</pre>
<pre class="result">
 count
-------+
     1
</pre>
Load another model architecture:
<pre class="example">
SELECT madlib.load_keras_model('model_arch_library',  -- Output table,
$$
{"class_name": "Sequential", "keras_version": "2.1.6", "config": [{"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "VarianceScaling", "config": {"distribution": "uniform", "scale": 1.0, "seed": null, "mode": "fan_avg"}}, "name": "dense_1", "kernel_constraint": null, "bias_regularizer": null, "bias_constraint": null, "dtype": "float32", "activation": "relu", "trainable": true, "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", "config": {}}, "units": 10, "batch_input_shape": [null, 4], "use_bias": true, "activity_regularizer": null}}, {"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "VarianceScaling", "config": {"distribution": "uniform", "scale": 1.0, "seed": null, "mode": "fan_avg"}}, "name": "dense_2", "kernel_constraint": null, "bias_regularizer": null, "bias_constraint": null, "activation": "relu", "trainable": true, "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", "config": {}}, "units": 10, "use_bias": true, "activity_regularizer": null}}, {"class_name": "Dense", "config": {"kernel_initializer": {"class_name": "VarianceScaling", "config": {"distribution": "uniform", "scale": 1.0, "seed": null, "mode": "fan_avg"}}, "name": "dense_3", "kernel_constraint": null, "bias_regularizer": null, "bias_constraint": null, "activation": "softmax", "trainable": true, "kernel_regularizer": null, "bias_initializer": {"class_name": "Zeros", "config": {}}, "units": 3, "use_bias": true, "activity_regularizer": null}}], "backend": "tensorflow"}
$$
::json,  -- JSON blob
                               NULL,                  -- Weights
                               'Maria',               -- Name
                               'Also a simple model'  -- Descr
);
SELECT COUNT(*) FROM model_arch_library;
</pre>
<pre class="result">
 count
-------+
     2
</pre>
-# Load model weights.  To load weights from previous
MADlib run, use UPDATE to load directly into the table.
For example, if 'model_weights' are the weights in the
output table 'iris_model' from a previous run
of 'madlib_keras_fit()' :
<pre class="example">
UPDATE model_arch_library SET model_weights = model_weights FROM iris_model WHERE model_id = 2;
</pre>
To load weights from Keras using a PL/Python function,
we need to flatten then serialize the weights to store
as a PostgreSQL binary data type. Byte format is more
efficient on space and memory compared to a numeric array.
The model weights will be de-serialized when
passed to Keras functions.
<pre class="example">
CREATE OR REPLACE FUNCTION load_weights() RETURNS VOID AS
$$
from keras.layers import *
from keras import Sequential
import numpy as np
import plpy
\#
\# create model
model = Sequential()
model.add(Dense(10, activation='relu', input_shape=(4,)))
model.add(Dense(10, activation='relu'))
model.add(Dense(3, activation='softmax'))
\#
\# get weights, flatten and serialize
weights = model.get_weights()
weights_flat = [w.flatten() for w in weights]
weights1d =  np.concatenate(weights_flat).ravel()
weights_bytea = weights1d.tostring()
\#
\# load query
load_query = plpy.prepare("""SELECT madlib.load_keras_model(
                        'model_arch_library',
                        $1, $2)
                    """, ['json','bytea'])
plpy.execute(load_query, [model.to_json(), weights_bytea])
$$ language plpythonu;
-- Call load function
SELECT load_weights();
-- Check weights loaded OK
SELECT COUNT(*) FROM model_arch_library WHERE model_weights IS NOT NULL;
</pre>
<pre class="result">
 count
-------+
     1
</pre>
Load weights from Keras using psycopg2.  (Psycopg is a PostgreSQL database adapter for the
Python programming language.) As above we need to flatten then serialize the weights to store as a
PostgreSQL binary data type.  Note that the psycopg2.Binary function used below will increase the size of the
Python object for the weights, so if your model is large it might be better to use a PL/Python function as above.
<pre class="example">
import psycopg2
import psycopg2 as p2
conn = p2.connect('postgresql://gpadmin@35.239.240.26:5432/madlib')
cur = conn.cursor()
from keras.layers import *
from keras import Sequential
import numpy as np
\#
\# create model
model = Sequential()
model.add(Dense(10, activation='relu', input_shape=(4,)))
model.add(Dense(10, activation='relu'))
model.add(Dense(3, activation='softmax'))
\#
\# get weights, flatten and serialize
weights = model.get_weights()
weights_flat = [w.flatten() for w in weights]
weights1d =  np.concatenate(weights_flat).ravel()
weights_bytea = psycopg2.Binary(weights1d.tostring())
\#
\# load query
query = "SELECT madlib.load_keras_model('model_arch_library', %s,%s)"
cur.execute(query,[model.to_json(),weights_bytea])
conn.commit()
</pre>
From SQL check if weights loaded OK:
<pre class="example">
SELECT COUNT(*) FROM model_arch_library WHERE model_weights IS NOT NULL;
</pre>
<pre class="result">
 count
-------+
     2
</pre>
-# Delete one of the models:
<pre class="example">
SELECT madlib.delete_keras_model('model_arch_library',   -- Output table
                                  1                      -- Model id
                                );
SELECT COUNT(*) FROM model_arch_library;
</pre>
<pre class="result">
 count
-------+
     3
</pre>

@anchor related
@par Related Topics

See keras_model_arch_table.sql_in

*/

CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.load_keras_model(
    keras_model_arch_table VARCHAR,
    model_arch             JSON,
    model_weights          bytea,
    name                   TEXT,
    description            TEXT
)
    RETURNS VOID AS $$
    PythonFunctionBodyOnlyNoSchema(`deep_learning', `keras_model_arch_table')
    from utilities.control import AOControl
    with AOControl(False):
        keras_model_arch_table.load_keras_model(**globals())
$$ LANGUAGE plpythonu VOLATILE;

-- Function to add a keras model to arch table
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.load_keras_model(
    keras_model_arch_table VARCHAR,
    model_arch             JSON
)
RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.load_keras_model($1, $2, NULL, NULL, NULL)
$$ LANGUAGE sql VOLATILE
m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA');

CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.load_keras_model(
    keras_model_arch_table VARCHAR,
    model_arch             JSON,
    model_weights          bytea
)
    RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.load_keras_model($1, $2, $3, NULL, NULL)
$$ LANGUAGE sql VOLATILE
m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA');

CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.load_keras_model(
    keras_model_arch_table VARCHAR,
    model_arch             JSON,
    model_weights          bytea,
    name                   TEXT
)
    RETURNS VOID AS $$
    SELECT MADLIB_SCHEMA.load_keras_model($1, $2, $3, $4, NULL)
$$ LANGUAGE sql VOLATILE
m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA');


-- Functions for online help
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.load_keras_model(
    message VARCHAR
) RETURNS VARCHAR AS $$
    PythonFunctionBodyOnly(deep_learning, keras_model_arch_table)
    return keras_model_arch_table.KerasModelArchDocumentation.load_keras_model_help(schema_madlib, message)
$$ LANGUAGE plpythonu VOLATILE
m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `');

CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.load_keras_model()
RETURNS VARCHAR AS $$
    PythonFunctionBodyOnly(deep_learning, keras_model_arch_table)
    return keras_model_arch_table.KerasModelArchDocumentation.load_keras_model_help(schema_madlib, '')
$$ LANGUAGE plpythonu VOLATILE
m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `');

-- Function to delete a keras model from arch table
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.delete_keras_model(
    keras_model_arch_table VARCHAR,
    model_id INTEGER
)
RETURNS VOID AS $$
    PythonFunctionBodyOnlyNoSchema(`deep_learning',`keras_model_arch_table')
    from utilities.control import AOControl
    with AOControl(False):
        keras_model_arch_table.delete_keras_model(**globals())
$$ LANGUAGE plpythonu VOLATILE;

-- Functions for online help
CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.delete_keras_model(
    message VARCHAR
) RETURNS VARCHAR AS $$
    PythonFunctionBodyOnly(deep_learning, keras_model_arch_table)
    return keras_model_arch_table.KerasModelArchDocumentation.delete_keras_model_help(schema_madlib, message)
$$ LANGUAGE plpythonu VOLATILE
m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `');

CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.delete_keras_model()
RETURNS VARCHAR AS $$
    PythonFunctionBodyOnly(deep_learning, keras_model_arch_table)
    return keras_model_arch_table.KerasModelArchDocumentation.delete_keras_model_help(schema_madlib, '')
$$ LANGUAGE plpythonu VOLATILE
m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `');