SQL Collection

/* SQL Command within postgres: */

SELECT now() - pg_postmaster_start_time() uptime

/* using psql command line utility deployed with Postgres: */
 
$> psql -c "SELECT now() - pg_postmaster_start_time() uptime"
$> sudo apt-get install freetds-bin

#At Lazarus:
#Put TZConnection component (ZConnection1) and set LibraryLocation as shown:

#  ZConnect1.LibraryLocation:=libsybdb.so.5;

#  and we're done!
#!/bin/bash
#
#/etc/init.d/oracledb
#
#Run-level Startup script for the Oracle Listener and Instances
#It relies on the information on /etc/oratab

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbname_1
export ORACLE_OWNR=oracle
export PATH=$PATH:$ORACLE_HOME/bin

if [ ! -f $ORACLE_HOME/bin/dbstart -o ! -d $ORACLE_HOME ]
then
  echo "Oracle startup: cannot start"
  exit 1
fi

case "$1" in
  start)
    #Oracle listener and instance startup
    echo -n "Starting Oracle: "
    su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl start"
    su $ORACLE_OWNR -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
    touch /var/lock/oracle
    echo "OK"
    ;;
  stop)
    #Oracle listener and instance shutdown
    echo -n "Shutdown Oracle: "
    su $ORACLE_OWNR -c "$ORACLE_HOME/bin/lsnrctl stop"
    su $ORACLE_OWNR -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
    rm -f /var/lock/oracle
    echo "OK"
    ;;
  reload|restart)
    $0 stop
    $0 start
    ;;
  *)
    echo "Usage: `basename $0` start|stop|restart|reload"
    exit 1
esac

exit 0
##################################################
# let's create a backup from remote postgresql database using pg_dump:
#
#   pg_dump -h [host address] -Fc -o -U [database user] <database name> > [dump file]
#
# later it could be restored at the same remote server using:
#
#   sudo -u postgres pg_restore -C mydb_backup.dump
#
#Ex:

pg_dump -h 67.8.78.10 -p 5432 -Fc -o -U myuser mydb > mydb_backup.dump

pg_restore -C mydb_backup.dump



#complete (all databases and objects)

pg_dumpall -U myuser -h 67.8.78.10 -p 5432 --clean --file=mydb_backup.dump


#restore from pg_dumpall --clean:

psql -f mydb_backup.dump postgres #it doesn't matter which db you select here
# Edit your /etc/postgresql/9.3/main/postgresql.conf, and change the lines as follows:

# Note: If you didn't find the postgresql.conf file, then just type 

$> locate postgresql.conf 

# in a terminal

1) change #log_directory = 'pg_log' to log_directory = 'pg_log'
2) change #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' to log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
3) change #log_statement = 'none' to log_statement = 'all'
4) change #logging_collector = off to logging_collector = on

# Optional: SELECT set_config('log_statement', 'all', true);

sudo /etc/init.d/postgresql restart or sudo service postgresql restart

#Fire query in postgresql: select 2+2

# Find current log in /var/lib/pgsql/9.2/data/pg_log/

#The log files tend to grow a lot over a time, and might kill your machine. For your safety, write a bash script that'll delete logs and restart postgresql server.
---Sql:
    
CREATE EXTENSION dblink;

---Then you can use it like this:
    
select * from dblink('dbname=otherdbname','select field1, field2 from otherdb_table') as foreign_table(field1 varchar, field2 integer)
##src: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps#configure-the-master-server

############## Master:

psql -c "CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'yourpassword';"

#//at file /etc/postgresql/9.5/main/pg_hba.conf 

	host    replication     rep     IP_address_of_slave/32   md5

#//at file /etc/postgresql/9.5/main/postgresql.conf

	listen_addresses = 'localhost,IP_address_of_THIS_host'
	wal_level = 'hot_standby'
	archive_mode = on
	archive_command = 'cd .'
	max_wal_senders = 1
	hot_standby = on

service postgresql restart


############### Slave:

service postgresql stop

#//at file /etc/postgresql/9.5/main/pg_hba.conf 

	host    replication     rep     IP_address_of_master/32  md5

#//at file /etc/postgresql/9.5/main/postgresql.conf

	listen_addresses = 'localhost,IP_address_of_THIS_host'
	wal_level = 'hot_standby'
	archive_mode = on
	archive_command = 'cd .'
	max_wal_senders = 1
	hot_standby = on


################## Master:

psql -c "select pg_start_backup('initial_backup');"
rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.5/main/ slave_IP_address:/var/lib/postgresql/9.5/main/
psql -c "select pg_stop_backup();"


################### Slave:

cd /var/lib/postgresql/9.5/main/recovery.conf

	standby_mode = 'on'
	primary_conninfo = 'host=master_IP_address port=5432 user=rep password=yourpassword'
	trigger_file = '/tmp/postgresql.trigger.5432' ##When we want to set SLAVE db to Master (because of original MASTER fail) creating this file is enough. With the existence of this file db will act like MASTER.

service postgresql start

## we check if no problem:

less /var/log/postgresql/postgresql-9.5-main.log
#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
requires:

1) Install Linux dependences (search for specific linux distro instructions for this):
python-dev, python-pip, freetds-dev, freetds-bin, libaio1

2) Install instantclient-basic-lite
follow these instructions:

http://www.oracle.com/technetwork/database/features/linuxx86-64soft-092277.html?printOnly=1
(go to "Installation of ZIP files" section at the bottom of document)

3) Install python includes:

sudo -H pip install cx_Oracle
sudo -H pip install pymssql

"""

import cx_Oracle
import pymssql

""" ====== let's connect to Oracle DB Server ====="""

orcl_host = "host1"
orcl_port = 1521
orcl_user = "user1"
orcl_pwd  = "password1"
orcl_dbn  = "service_name"

connstr = orcl_user+"/"+orcl_pwd+"@"+orcl_host+":"+str(orcl_port)+"/"+orcl_dbn
orcl = cx_Oracle.connect(connstr)

#If all is correct we will see and object print:

print(orcl)

"""===== let's connect to sqlsvr: ====="""

sql_host = "host2"
sql_user = "user2"
sql_pwd  = "password2"
sql_dbn  = "database_name"

conexion_sql = pymssql.connect(sql_host, sql_user, sql_pwd, sql_dbn)

#If all is correct we will see and object print:

print(conexion_sql)

SELECT TRUNC (SYSDATE - ROWNUM) dt
  FROM DUAL CONNECT BY ROWNUM < :ndates;
  
/* from now on */
SELECT TRUNC (SYSDATE + ROWNUM - 2) dt
  FROM DUAL CONNECT BY ROWNUM < 100
select * from json_to_recordset( (
  select array_to_json(array_agg(row_to_json(t)))
    from (
      select field1, field2, field3,... from mytable
    ) t
)) as x(
  "field1" integer,
  "field2" integer,
  "field3" text,
  :
)

--- ejemplo de texto json a recordset:
select * from json_to_recordset((select detalles from d_factura_caja where id=4216)::json) as x(
  "seq" integer,
  "padre" integer,
  "tipo" text,
  "idx" integer,
  "nivel" integer,
  "descripcion" text,
  "cantidad" numeric(6,2),
  "presentacion" text,
  "precio_unitario" numeric(15,2),
  "precio" numeric(15,2),
  "porc_impuesto" numeric(6,2),
  "impuesto" numeric,
  "neto"numeric
)
SELECT n.nspname AS schema_name
      ,p.proname AS function_name
      ,pg_get_functiondef(p.oid) AS func_def
      ,pg_get_function_arguments(p.oid) AS args
      ,pg_get_function_result(p.oid) AS result
FROM   pg_proc p
JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  p.proname ILIKE 'func_name%';
# Backup:
docker exec -t -u postgres your-db-container pg_dumpall -c > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

# Restore:
cat your_dump.sql | docker exec -i your-db-container psql -U postgres
<?php

// sudo apt install php7.0-sybase


header("content-type: text/plain; charset=iso-8859-1");
ini_set("display_errors","1");
error_reporting(E_ALL);

$host="localhost";
$db="test";
$uid="sa";
$pwd="mypassword";

$query = "select top 10 * from testtable";

$conn = new PDO( "dblib:host=$host:1433;dbname=$db;", $uid, $pwd);
$stmt = $conn->prepare( $query );
$stmt->execute();

while ($r=$stmt->fetch(PDO::FETCH_ASSOC)){
	print_r($r);
}

?>
create function f_thetable_biud()
returns trigger as
$body$
--declare vars
begin
    -- new, old
    -- tg_op = INSERT,DELETE,UPDATE
    return new/old;
end;
$body$ language plpgsql;

create trigger t_thetable_biud
before/after insert or update or delete
on thetable
for each row execute procedure f_thetable_biud();
SELECT *, count(*) OVER() AS full_count
FROM   tbl
WHERE  /* whatever */
ORDER  BY col1
LIMIT  ?
OFFSET ?
<?php 

$sql = 'select fielda, fieldb, fieldc from table1
order by field, fieldb, fieldc
union all
select field1, field2, field3 from table2
order by field1, field2 desc, field3 asc';


print_r($sql2 = invert_order($sql));


function invert_order($str){
    $re = '/(?<=order\sby\s)(.*)(?=)/mi';

    preg_match_all($re, $str, $matches, PREG_SET_ORDER, 0);
    
    $mat = $matches[sizeof($matches)-1]; //-- get only last order field list
    $mat=$mat[sizeof($mat)-1];
    $mat=explode(",",$mat);
    
    
    for($i=0; $i<sizeof($mat); $i++){   //-- reverse each pair of order field/direction 
        $duet = preg_split("/\s+/", trim($mat[$i]));
        if (sizeof($duet)<2) $duet[1]="";
        switch(strtolower($duet[1])) {
            case "desc":
                $duet[1] = "asc";
                break;
            default:
                $duet[1] = "desc";
                break;
        }
        $mat[$i] = implode(" ",$duet);
    }
    
    $re2 = '/(order\s+by\s+.*)*$/i';    //-- replace last order by with new inverted order by:
    $subst = "order by ".implode(", ",$mat);
    $result = preg_replace($re2, "", $str);
    return $result . " $subst";
}


?>
select id, name from table 
where name ilike all(string_to_array(replace('%'||TRIM('JOHN DOE')||'%',' ','%,%'), ','))

-- 1) it converts spaces into commas (,)
-- 2) it converts string to array using resulting comma separated string
-- 3) it apply ilike to each word in search string

-- It finds (for example) name = 'DOE, JHON'  in table
create table <tablename>_log(
    id serial primary key,
    idx integer,
    antes json,
    despues json,
    id_m_usuario integer,
    fecha_registro timestamp default now(),
    tipo char(1) default 'U',
    unico varchar(36)
);

create function f_<tablename>_log()
returns trigger
LANGUAGE plpgsql as
$body$
begin
    case tg_op
        when 'INSERT' then
            insert into <tablename>_log (idx, despues, tipo)
            select new.id, row_to_json(new), 'I';
            return new;
        when 'UPDATE' then
            if (.. <condiciones new.campo1 <> new.campo2>... ) then
                insert into <tablename>_log (idx, antes, despues, tipo)
                select new.id, row_to_json(old), row_to_json(new), 'U';
            end if;
            return new;
        when 'DELETE' then
            insert into <tablename>_log (idx, antes, tipo)
            select new.id, row_to_json(old), 'D';
            return old;
    end case;
end;
$body$;

create trigger t_<tablename>_log
before insert or update or delete
on <tablename>
for each row execute procedure f_<tablename>_log();
select regexp_matches('FV{YY}{MM}{DD}-{UU}-{###}', '\{([A-Za-z#]+)\}', 'g');
<?php

/*==========  install at ubuntu: ==============

sudo apt install php-sybase


//==================================

append this into /etc/freetds/freetds.conf:
(192.168.0.250:2638 is an example:)

//==================================

sudo nano /etc/freetds/freetds.conf

[connName]
    host = 192.168.0.250  
    port = 2638
    tds version = 7.4
    database = MYDB
    
//======  php: */

$connName = "connName";
$user = "myuser";
$pass = "mypass";

$st="dblib:host=$connName;charset=iso8859-1";
$conn = new PDO($st,$user,$pass);

/* $conn->prepare($sql_query);
...etc
*/
SELECT procpid, age(clock_timestamp(), query_start), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- show running queries (9.2)
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- kill running query
SELECT pg_cancel_backend(procpid);

-- kill idle query
SELECT pg_terminate_backend(procpid);

-- vacuum command
VACUUM (VERBOSE, ANALYZE);

-- all database users
select * from pg_stat_activity where current_query not like '<%';

-- all databases and their sizes
select * from pg_user;

-- all tables and their size, with/without indexes
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database
order by pg_database_size(datname) desc;

-- cache hit rates (should not be less than 0.99)
SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;

-- table index usage rates (should not be less than 0.99)
SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

-- how many indexes are in cache
SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM pg_statio_user_indexes;

-- Dump database on remote host to file
$ pg_dump -U username -h hostname databasename > dump.sql

-- Import dump into existing database
$ psql -d newdb -f dump.sql

--(On 9.2+): Queries running more than 2 minutes
SELECT now() - query_start as "runtime", usename, datname, waiting, state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '2 minutes'::interval
 ORDER BY runtime DESC;

-- 
select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;

--
select relname, n_dead_tup, last_vacuum, last_autovacuum from 
pg_catalog.pg_stat_all_tables
where n_dead_tup > 0 and relname =  ’table1' order by n_dead_tup desc;

--Tables and views used by a given view:
with recursive view_tree(parent_schema, parent_obj, child_schema, child_obj, ind, ord) as 
(
  select vtu_parent.view_schema, vtu_parent.view_name, 
    vtu_parent.table_schema, vtu_parent.table_name, 
    '', array[row_number() over (order by view_schema, view_name)]
  from information_schema.view_table_usage vtu_parent
  where vtu_parent.view_schema = '<SCHEMA NAME>' and vtu_parent.view_name = '<VIEW NAME>'
  union all
  select vtu_child.view_schema, vtu_child.view_name, 
    vtu_child.table_schema, vtu_child.table_name, 
    vtu_parent.ind || '  ', 
    vtu_parent.ord || (row_number() over (order by view_schema, view_name))
  from view_tree vtu_parent, information_schema.view_table_usage vtu_child
  where vtu_child.view_schema = vtu_parent.child_schema 
  and vtu_child.view_name = vtu_parent.child_obj
) 
select tree.ind || tree.parent_schema || '.' || tree.parent_obj 
  || ' depends on ' || tree.child_schema || '.' || tree.child_obj txt, tree.ord
from view_tree tree
order by ord;


--Check the size (as in disk space) of all databases:
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) 
    ELSE 'No Access' 
  END AS SIZE 
FROM pg_catalog.pg_database d 
ORDER BY 
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') 
    THEN pg_catalog.pg_database_size(d.datname)
    ELSE NULL 
  END;

--Check the size (as in disk space) of each table:
SELECT nspname || '.' || relname AS "relation",
   pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
   AND C.relkind <> 'i'
   AND nspname !~ '^pg_toast'
 ORDER BY pg_total_relation_size(C.oid) DESC;

--Script to kill all running connections of a current database
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()  
  AND pid <> pg_backend_pid();
select id, id2, debito, credito, 
  sum(debito-credito) over (partition BY id ORDER BY id, id2) saldo
from 
(
	select m.id, 0 id2, importe debito, 0 credito
		from m_cxc m 
		where m.id=:id
	union all 
	select c.id_m_cxc id, c.id id2, debito, credito
		from d_cxc c 
  		where c.id_m_cxc=:id
) a
WITH cte AS (
   SELECT *
   FROM   m_paciente mp 
   WHERE  apellido like 'P%'
   )
SELECT *
FROM  (
   TABLE  cte
   ORDER  BY apellido, nombre
   LIMIT  10
   OFFSET 190
   ) sub
RIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;
-- Example table:

create table patient (
    id serial primary key,
    firstname varchar(100),
    lastname varchar(100)
);

insert into patient(firstname, lastname) values ('MARCO ANTONIO', 'PEREZ SANDERS');

-- Create field tsvector field with text columns (preferable using insert and update trigger);
alter table patient add tsv_name tsvector;

-- initially fill field.
update patient set tsv_name = to_tsvector(firstname || ' ' || lastname);

-- create index for fast search:
create index tsv_name_idx on patient using gin(tsv_name);

-- then you can search as:
select * from patient where tsv_name @@ to_tsquery('MARCO & PEREZ');
select * from patient where tsv_name @@ to_tsquery('MARCO & SANDERS');
select * from patient where tsv_name @@ to_tsquery('ANTONIO & PEREZ');

select * from m_paciente 
where tsv_nombre @@ to_tsquery(array_to_string(string_to_array('LEIRY SEVERINO',' '),':* & ')||':*');

-- other:

select * from patient where fullname ~ all(array['(?=^LEI.*|\s+LEI\w*)','(?=^ME.*|\s+ME\w*)']);
---$> psql: user my_user and database my_original_db must exists:

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'my_original_db' AND pid <> pg_backend_pid();

CREATE DATABASE my_new_db WITH TEMPLATE my_original_db OWNER my_user;
<?php

//===========notifier.sql:
    
/*
CREATE OR REPLACE FUNCTION public.notify_channel()
RETURNS trigger
AS $function$
  BEGIN
	  PERFORM pg_notify('channel_name', row_to_json(NEW)::text);
	  RETURN NULL;
  END;
$function$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_on_insert AFTER INSERT ON mytable
FOR EACH ROW EXECUTE PROCEDURE notify_channel();
*/

set_time_limit(0);

//-- using PDO:

$db = new PDO(
    'pgsql:dbname=dbname host=host port=5432;options=--application_name=APPLICATION_NAME',
    'user',
    'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);

$db->exec('LISTEN channel_name');

while (true) {
    while ($db->pgsqlGetNotify(PDO::FETCH_ASSOC, 30000)) {
        echo json_encode($result).PHP_EOL;
    }
}

//-- using pg_connect:
//<?php

include '../conn.php';
set_time_limit(0);
ob_end_clean();
pg_query($conn, 'LISTEN table_changed;');

while(true){
    
    $notify = pg_get_notify($conn);
    
	if (!$notify) {
        echo json_encode(array('result'=>false, 'data'=>'No messages')).PHP_EOL;
        ob_flush();
        flush();
        sleep(1);
	} else {
        echo json_encode(array('result'=>true, 'process_id'=>$pid , 'pid' => pg_get_pid($conn), 'data' => $notify)).PHP_EOL;
	}
}

gsec -user sysdba -pass masterkey -add billyboy -pw sekrit66 -admin yes
WITH RECURSIVE PADRE AS (
	SELECT
		COD_CTA,
		DES_CTA
	FROM
		CATALOGO
	WHERE
		COD_CTA=:COD_CTA
	UNION ALL
		SELECT
			e.COD_CTA,
			e.DES_CTA
		FROM
			CATALOGO e
		INNER JOIN padre p ON POSITION(e.COD_CTA, p.COD_CTA) = 1
		  AND p.COD_CTA > COD_CTA
		ROWS 1
) SELECT
	DISTINCT cod_cta
FROM
	PADRE
WHERE COD_CTA < :COD_CTA
:> docker run -it --name fb --rm -v ~/tmp:/tmp almeida/firebird gbak -b -v 192.168.1.251:c:/host/path/database.fdb /tmp/backup.bak -user sysdba -pass XXXXX
#backup

gbak -t -v -user <username> -password "<password>" <host>:/path/to/db.fdb path/to/file.gbk

#restore

gbak -c -v -user <username> -password "<password>" path/to/file.gbk <host>:/path/to/db.fdb
alter role <username> set search_path = <schema1>, ..., <scheman>, public;
#put your databases on /svr/fb-databases

docker run -d --name fb -p 3050:3050 -v /srv/fb-databases:/db almeida/firebird

#connect using localhost:3050:db/<database file>.gdb
CREATE TABLE GAP(
    COUNTER INTEGER PRIMARY KEY
);

insert into gap (counter) values (1);
insert into gap (counter) values (2);
--::::: 3
--::::: 4
insert into gap (counter) values (5);
insert into gap (counter) values (6);
--::::: 7
insert into gap (counter) values (8);
insert into gap (counter) values (9);
insert into gap (counter) values (10);



--===== here the trick :

SELECT 
	list(CASE 
		WHEN N2-N1-2 > 0 THEN (N1+1) || '-'|| (N2-1)
		ELSE CAST(N1+1 AS VARCHAR(10))
	END) Missing
FROM (
SELECT a.counter N1, 
  (SELECT counter FROM gap WHERE counter > a.counter rows 1) N2 
FROM gap a
) B
WHERE N2-N1 > 1;



-- results:

Missing
=======
3-4,7
sudo mkdir -p /your/custom/path/oracle-19c/oradata/
sudo chmod -R 777 /your/custom/path/

docker run -d --name oracle19db \
  -p 1521:1521 \
  -e ORACLE_SID=ORCL \
  -e ORACLE_PDB=ORCLDB \
  -e ORACLE_PWD=Oracle123 \
  -e ORACLE_CHARSET=AL32UTF8 \
  -v /your/custom/path/oracle-19c/oradata:/opt/oracle/oradata \
  banglamon/oracle193db:19.3.0-ee

# Charset Value: WE8MSWIN1252, AL16UTF8

# ALTER SESSION SET NLS_DATE_FORMAT = 'RRRR-MM-DD';
# ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SS';
# ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'RRRR-MM-DD HH24:MI:SS';
# ALTER SESSION SET NLS_TIME_TZ_FORMAT = 'HH24:MI:SS TZR';
# ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'RRRR-MM-DD HH24:MI:SS TZR';

# docker exec -it oracle19db bash -c "source /home/oracle/.bashrc; sqlplus /nolog”
# connect sys as sysdba;

# alter session set "_ORACLE_SCRIPT"=true;
# create user sistemas identified by las36horas;
# GRANT CONNECT, RESOURCE, DBA TO sistemas;
# GRANT UNLIMITED TABLESPACE TO sistemas;
CREATE OR ALTER PROCEDURE GETINTEGERLIST(AINTEGERLIST VARCHAR(32000))
returns (
  ID integer
)
as
  declare variable IntegerList varchar(32000);
  declare variable CommaPos integer;
  declare variable IntegerVal varchar(10);
begin
  IntegerList = AIntegerList || ' ';
  CommaPos = Position(',', IntegerList);

  while (CommaPos > 0) do
  begin
    IntegerVal = Trim(SubString(IntegerList from 1 for CommaPos - 1));

    if (Char_Length(IntegerVal) > 0) then
    begin
      if (IntegerVal similar to '[0-9]*') then
      begin
        ID = Cast(IntegerVal as integer);
        suspend;
      end
    end

    if (Char_Length(IntegerList) > CommaPos) then
      IntegerList = SubString(IntegerList from CommaPos + 1);
    else
      IntegerList = '';

    CommaPos = Position(',', IntegerList);
  end

  IntegerList = Trim(IntegerList);

  if (Char_Length(IntegerList) > 0) then
  begin
    if (IntegerList similar to '[0-9]*') then
    begin
      ID = Cast(IntegerList as integer);
      suspend;
    end
  end
end;

/* use:

SELECT *
FROM CITY
WHERE ID_CITY IN (SELECT ID FROM GetIntegerList('1, 2, 12, 45, 75, 45'))

*/
#use oracle user from system:

sqlplus "/ as sysdba"

SQL> ALTER USER SYS IDENTIFIED BY [password]; 
SQL> ALTER USER SYSTEM IDENTIFIED BY [password];
DROP TABLESPACE MYSPACENAME INCLUDING contents;

CREATE TABLESPACE MYSPACENAME 
   DATAFILE 'ts1_filename.dbf' 
   SIZE 2000m 
   autoextend on NEXT 1000m maxsize unlimited;
   
 alter tablespace MYSPACENAME coalesce;
alter session set "_ORACLE_SCRIPT"=true;

CREATE USER SISTEMAS IDENTIFIED BY las36horas;

GRANT CREATE TABLE TO SISTEMAS;
GRANT CONNECT TO SISTEMAS;
GRANT CTXAPP TO SISTEMAS;
GRANT RESOURCE TO SISTEMAS;
GRANT CREATE ANY CONTEXT TO SISTEMAS;
GRANT CREATE ANY SYNONYM TO SISTEMAS;
GRANT CREATE VIEW TO SISTEMAS;
GRANT DROP ANY CONTEXT TO SISTEMAS;
GRANT QUERY REWRITE TO SISTEMAS;
EXECUTE ON SYS.DBMS_AQ_BQVIEW
EXECUTE ON SYS.DBMS_LOB
EXECUTE ON SYS.UTL_RAW
export ORACLE_SID=$1
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
export USUARIO=system/org24h
export PATHBACKUP=/respaldo/o24/export
export FILENAME=CMLGDB`date +%d%m%Y%H%M`.DMP
export FILENAMELOG=CMLGDB`date +%d%m%Y%H%M`.log
echo  $PATHBACKUP

rm $PATHBACKUP/*.* -rf

if [ -a $PATHBACKUP ] ; then
	expdp $USUARIO FULL=yes DUMPFILE=dpump_dir1:$FILENAME LOGFILE=dpump_dir1:$FILENAMELOG
	#exp $USUARIO file=$PATHBACKUP/$FILENAME full=yes compress=yes indexes=no consistent=yes log=$PATHBACKUP/$FILENAMELOG
else
	echo "ERROR: Export no encontro el directorio de Respaldo"
	exit 1
fi
docker run -d -e ACCEPT_EULA=Y -e "SA_PASSWORD=P@ssW0rd" -p 1433:1433 \
  --restart unless-stopped \
  -v /var/opt/mssql/data:/var/opt/mssql/data \
  -v /tmp/:/backups/ \
  --name sqlserver \
  mcr.microsoft.com/mssql/server

#backup:

# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P P@ssW0rd -Q "BACKUP DATABASE [dbname] TO DISK = N'/tmp/dbname-full.bak' WITH NOFORMAT, NOINIT, NAME = 'dbname-bak-full', SKIP, NOREWIND, NOUNLOAD, STATS = 10"

# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P P@ssW0rd -Q "BACKUP LOG [dbname] TO DISK = N'/tmp/dbname-log.bak' WITH NOFORMAT, NOINIT, NAME = N'dbname-bak-log', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5"

#restore:

# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P P@ssW0rd -Q "RESTORE DATABASE [dbname] FROM DISK = N'/tmp/dbname-full.bak' WITH FILE = 1, NOUNLOAD, REPLACE, NORECOVERY, STATS = 5"

# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P P@ssW0rd -Q "RESTORE LOG [dbname] FROM DISK = N'/var/opt/mssql/data/dbname-log.bak'"


#create login myuser with password ='strongPass';
#create user myuser for login myuser;
#ALTER LOGIN [myuser] enable;
select * from 
( 
    select 
        sql_id, 
        elapsed_time, 
        executions, 
        sql_text 
    from v$sql  
    ORDER BY elapsed_time desc
) 
where rownum < 11;
You can determine the version of the primary MDF file of a database by looking at the two bytes at offset 0x12064

SQL Server Version	    Internal DB Version     DB Compat Level	    Supported DB Compatibility Levels
SQL Server 2022             ?                           160	                       ?
SQL Server 2019 CTP 3.2 / RC 1 / RC 1.1 / RTM	
                            904	                        150	        150,140,130,120,110,100
SQL Server 2019 CTP 3.0 / 3.1	
                            902	                        150	        150,140,130,120,110,100
SQL Server 2019 CTP 2.3 / 2.4 / 2.5	
                            897	                        150	        150,140,130,120,110,100
SQL Server 2019 CTP 2.1 / 2.2	
                            896	                        150	        150,140,130,120,110,100
SQL Server 2019 CTP 2.0	    895	                        150	        150,140,130,120,110,100
SQL Server 2017	            868 / 869	                140	        140,130,120,110,100
SQL Server 2016	            852	                        130         130,120,110,100
SQL Server 2014	            782	                        120	        120,110,100
SQL Server 2012	            706	                        110	        110,100,90
SQL Server 2012 CTP1
(a.k.a. SQL Server 2011 Denali)	
                            684	                        110	        110,100,90
SQL Server 2008 R2	        660 / 661	                100	        100,90,80
SQL Server 2008	            655	                        100	        100,90,80
SQL Server 2005 SP2+
with VarDecimal enabled	    612	                        90	        90,80,70
SQL Server 2005	            611	                        90	        90,80,70
SQL Server 2000	            539	                        80	        80,70
SQL Server 7.0	            515	                        70	        70
SQL Server 6.5	            408                     	65	        65
SQL Server 6.0	            406	                        60	        60
#Copy the image

$ docker pull doctorkirk/oracle-19c

#Create local directory

$ mkdir -p /your/custom/path/oracle-19c/oradata
$ cd /your/custom/path/
$ sudo chown -R 54321:54321 oracle-19c/

#Run the Container

docker run --name oracle-19c \
  -p 1521:1521 \
  -e ORACLE_SID=[ORACLE_SID] \
  -e ORACLE_PWD=[ORACLE_PASSWORD] \
  -e ORACLE_CHARACTERSET=[CHARSET] \
  -v /your/custom/path/oracle-19c/oradata/:/opt/oracle/oradata \
doctorkirk/oracle-19c

#Charset: WE8MSWIN1252(*default), AL16UTF8, US7ASCI
#* If omitted in docker run , the default characterset for this build will be WE8MSWIN1252.
#Backup

gbak -b -v -user SYSDBA -password "masterkey" D:\database.FDB E:\database.fbk

#Restore

gbak -c -user SYSDBA -password masterkey E:\database.fbk E:\database_restore.fdb
CREATE OR ALTER FUNCTION AGE(DFROM TIMESTAMP, DTO TIMESTAMP, CODED BOOLEAN = FALSE)
RETURNS VARCHAR(30)
AS
DECLARE y varchar(3);
DECLARE m varchar(2);
DECLARE d varchar(2);
BEGIN
	y = CASE 
			WHEN datediff(year, :DFROM, :DTO) <> datediff(day, :DFROM, :DTO)/365
				THEN datediff(year, :DFROM, :DTO)-1
			ELSE datediff(year, :DFROM, :DTO)
		END;
	m = CASE 
			WHEN datediff(year, :DFROM, :DTO) <> datediff(day, :DFROM, :DTO)/365
				AND datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)<0
				THEN datediff(month, dateadd(year, datediff(year, :DFROM, :DTO)-1, :DFROM), :DTO)-1
			WHEN datediff(year, :DFROM, :DTO) <> datediff(day, :DFROM, :DTO)/365
				AND datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)>=0
				THEN datediff(month, dateadd(year, datediff(year, :DFROM, :DTO)-1, :DFROM), :DTO)
			WHEN datediff(year, :DFROM, :DTO) = datediff(day, :DFROM, :DTO)/365
				AND datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)<0
				THEN datediff(month, dateadd(year, datediff(year, :DFROM, :DTO), :DFROM), :DTO)-1
			ELSE datediff(month, dateadd(year, datediff(year, :DFROM, :DTO), :DFROM), :DTO)
		END;
	d = CASE 
			WHEN datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)<0
				THEN datediff(day, dateadd(month, datediff(month, :DFROM, :DTO)-1, :DFROM), :DTO)
			ELSE datediff(day, dateadd(month, datediff(month, :DFROM, :DTO), :DFROM), :DTO)
		END;
	RETURN 
		CASE 
			WHEN :CODED THEN lpad(Y,3,'0')||'-'||lpad(m,2,'0')||'-'||lpad(d,2,'0')
			ELSE Y||'y '||m||'m '||d||'d' 
		END;
END;
select 
  DATE_PART('year', AGE('2012-03-05', '2010-04-01')) AS years,
  DATE_PART('month', AGE('2012-03-05', '2010-04-01')) AS months,
  DATE_PART('day', AGE('2012-03-05', '2010-04-01')) AS days;
CREATE OR ALTER PROCEDURE date_range(
    startdate date, 
    enddate date, 
    interv integer DEFAULT 1, 
	unit varchar(6) DEFAULT 'DAY', 
	fromtime time DEFAULT null, 
	totime time DEFAULT null
) RETURNS (dateval timestamp)
AS
BEGIN
    dateval = startdate;
    while (dateval < enddate) do
    BEGIN
	    IF ((:fromtime IS NULL OR cast(:dateval AS time) >= :fromtime)
		  AND (:totime IS NULL OR cast(:dateval AS time) < :totime)) THEN 
		BEGIN 
			suspend;
		END 
		
  		SELECT 
	  		CASE  
		  		WHEN :unit = 'MINUTE' THEN dateadd(MINUTE, :interv, :dateval)
		  		WHEN :unit = 'HOUR' THEN dateadd(HOUR, :interv, :dateval)
		  		WHEN :unit = 'YEAR' THEN dateadd(YEAR, :interv, :dateval)
		  		WHEN :unit = 'MONTH' THEN dateadd(MONTH, :interv, :dateval)
		  		else dateadd(DAY, :interv, :dateval)
			END 
		FROM RDB$DATABASE 
		INTO :dateval;
    END
END;

/*
Example: ranges from today to next 10 days, from 8:00am to 6:00pm, in fraction of 30 minutes:

SELECT 
    cast(dateval AS date) TheDate, 
    CAST(dateval AS time) FromTime, 
    CAST(dateadd(MINUTE, 30, dateval) AS time) ToTime 
FROM date_range(
    current_date, ---- initial date
    current_date + 10, ---- next 10 days
    30, 'MINUTE', ---- fraction
    time '08:00', time '18:00') --- from time, to time
*/
CREATE OR ALTER FUNCTION DATE_TO_CHAR(TDATE TIMESTAMP, FORMAT VARCHAR(100))
RETURNS VARCHAR(100)
AS
DECLARE SDATE VARCHAR(100);
DECLARE DD VARCHAR(2);
DECLARE MM VARCHAR(2);
DECLARE YY VARCHAR(4);
DECLARE HH VARCHAR(2);
DECLARE MI VARCHAR(2);
DECLARE SS VARCHAR(2);
DECLARE XFORMAT VARCHAR(100);
DECLARE AM VARCHAR(2);
BEGIN
	XFORMAT = UPPER(FORMAT);
	SDATE = CAST(TDATE AS VARCHAR(100));
	YY = SUBSTRING(SDATE FROM 1 FOR 4);
	MM = SUBSTRING(SDATE FROM 6 FOR 2);
	DD = SUBSTRING(SDATE FROM 9 FOR 2);
	HH = SUBSTRING(SDATE FROM 12 FOR 2);
	MI = SUBSTRING(SDATE FROM 15 FOR 2);
	SS = SUBSTRING(SDATE FROM 18 FOR 2);
	XFORMAT = REPLACE(XFORMAT, 'YYYY', YY);
	XFORMAT = REPLACE(XFORMAT, 'MM', MM);
	XFORMAT = REPLACE(XFORMAT, 'DD', DD);
	XFORMAT = REPLACE(XFORMAT, 'YY', SUBSTRING(YY FROM 3 FOR 2));
	XFORMAT = REPLACE(XFORMAT, 'HH24', HH);
	AM = 'AM';

	IF (HH='12') THEN
	BEGIN
		AM = 'M';
		IF (MI > '00') THEN 
		BEGIN
			AM='PM';
		END
	END
	
	IF (HH='00') THEN 
	BEGIN
		HH='12';
		AM='AM';
	END
	
	IF (HH>'12') THEN
	BEGIN
		HH = TRIM(CAST(CAST(HH AS INTEGER)-12 AS VARCHAR(2)));
		IF (CHAR_LENGTH(HH)<2) THEN 
		BEGIN
			HH='0'||HH;
		END
		AM='PM';
	END
	XFORMAT = REPLACE(XFORMAT, 'HH12', HH);
	XFORMAT = REPLACE(XFORMAT, 'HH', HH);
	XFORMAT = REPLACE(XFORMAT, 'MI', MI);
	XFORMAT = REPLACE(XFORMAT, 'SS', SS);
	
	RETURN XFORMAT;
END;

/* Examples:

    SELECT DATE_TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YYYY HH24:MI:SS') FROM RDB$DATABASE;
    SELECT DATE_TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YYYY HH12:MI AM') FROM RDB$DATABASE;
    SELECT DATE_TO_CHAR(CURRENT_TIMESTAMP, 'DD/MM/YY HH24:MI') FROM RDB$DATABASE;
    -- still uncomplete...
*/
/*use "sa" user*/

/*backup*/
BACKUP DATABASE [servicedesk] TO DISK = N'/var/opt/mssql/data/SDDB20230221.BAK';

/*restore*/
ALTER DATABASE [servicedesk] SET OFFLINE WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [servicedesk] FROM DISK = N'/var/opt/mssql/data/SDDB20230221.BAK' WITH REPLACE;

/*go online after restore*/
ALTER DATABASE [servicedesk] SET ONLINE;
SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' ')  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;
CREATE OR ALTER PROCEDURE SP_SPLIT_INTO_WORDS (A_TEXT VARCHAR(32000), A_DELS VARCHAR(100) default ',.<>/?;:''"[]{}`~!@#$%^&*()-_=+\|/', A_SPECIAL CHAR(1) default ' ')
RETURNS (
	WORD VARCHAR(50), POSIC integer
)
AS
DECLARE norder integer;
BEGIN

	-- Aux SP, used only in oltp_data_filling.sql to filling table PATTERNS
	-- with miscelan combinations of words to be used in SIMILAR TO testing.
	
	norder = 0;

	for
	    with recursive
	    j as( -- loop #1: transform list of delimeters to rows
	        select s,1 i, substring(s from 1 for 1) del
	        from(
	          select replace(:a_dels,:a_special,'') s
	          from rdb$database
	        )
	        
	        UNION ALL
	        
	        select s, i+1, substring(s from i+1 for 1)
	        from j
	        where substring(s from i+1 for 1)<>''
	    )
	 
	    ,d as(
	        select :a_text s, :a_special sp from rdb$database
	    )
	    ,e as( -- loop #2: perform replacing each delimeter to `space`
	        select d.s, replace(d.s, j.del, :a_special) s1, j.i, j.del
	        from d join j on j.i=1
	 
	        UNION ALL
	 
	        select e.s, replace(e.s1, j.del, :a_special) s1, j.i, j.del
	        from e
	        -- nb: here 'column unknown: e.i' error will be on old builds of 2.5,
	        -- e.g: WI-V2.5.2.26540 (letter from Alexey Kovyazin, 24.08.2014 14:34)
	        join j on j.i = e.i + 1
	    )
	    ,f as(
	        select s1 from e order by i desc rows 1
	    )
	    
	    ,r as ( -- loop #3: perform split text into single words
	        select iif(t.k>0, substring(t.s from t.k+1 ), t.s) s,
	             iif(t.k>0,position( del, substring(t.s from t.k+1 )),-1) k,
	             t.i,
	             t.del,
	             iif(t.k>0,left(t.s, t.k-1),t.s) word
	        from(
	          select f.s1 s, d.sp del, position(d.sp, s1) k, 0 i from f cross join d
	        )t
	 
	        UNION ALL
	 
	        select iif(r.k>0, substring(r.s from r.k+1 ), r.s) s,
	             iif(r.k>0,position(r.del, substring(r.s from r.k+1 )),-1) k,
	             r.i+1,
	             r.del,
	             iif(r.k>0,left(r.s, r.k-1),r.s) word
	        from r
	        where r.k>=0
	    )
	    select word from r where word>''
	    INTO word
	do
	BEGIN
		posic = norder;
    	suspend;
    	norder = norder + 1;
    end
end;

CREATE TABLE alumno_srch_nombre(
	id integer NOT NULL,
	valor varchar(255) NOT NULL,
	orden integer
);

CREATE INDEX ndx_alum_srch_nombre ON alumno_srch_nombre(id, valor);

CREATE OR ALTER TRIGGER t_alumno_biu_ndx
FOR alumno 
BEFORE INSERT OR UPDATE 
AS
BEGIN
	DELETE FROM alumno_srch_nombre
	WHERE id = NEW.id;

	INSERT INTO alumno_srch_nombre
	SELECT NEW.id, word, posic FROM SP_SPLIT_INTO_WORDS(NEW.apenom);
END;

SELECT * FROM (
SELECT a.*,
  CASE 
	  WHEN a.apenom LIKE replace(:INPUT,' ','%') || '%' THEN 0
	  ELSE 1
  END NIVEL
FROM alumno a
INNER JOIN (
	SELECT id, count(*) cnt
	FROM (
		SELECT id, word, min(orden) orden 
		FROM SP_SPLIT_INTO_WORDS(:input) x
		INNER JOIN ALUMNO_SRCH_NOMBRE asn ON asn.valor LIKE word || '%'
		GROUP BY id, word
	)
	GROUP BY id
	HAVING count(*) = (SELECT count(*) FROM SP_SPLIT_INTO_WORDS(:input))
) b ON b.id=a.id
) ORDER BY NIVEL;


--- OTHER EXAMPLE:

SELECT a.* FROM alumno a
INNER JOIN (
	SELECT id, count(*) cant FROM alumno
	INNER JOIN SP_SPLIT_INTO_WORDS(:input) X ON 
		ALUMNO.APELLIDOS LIKE X.WORD||'%'
	  		OR ALUMNO.NOMBRES LIKE X.WORD||'%'
	  		OR ALUMNO.CEDULA LIKE X.WORD||'%'
	GROUP BY id
	HAVING count(*) = (SELECT count(*) FROM SP_SPLIT_INTO_WORDS(:input))
) x ON a.id = x.id 
UNION 
SELECT a.* FROM ALUMNO a
where a.cedula LIKE :input || '%'
CREATE TABLE SECUENCIA(
	ID INTEGER PRIMARY KEY,
	CODIGO VARCHAR(60) NOT NULL,
	PREFIJO VARCHAR(60),
	DESCRIPCION VARCHAR(255) NOT NULL,
	NUMERO INTEGER DEFAULT 0 NOT NULL
);

CREATE SEQUENCE SECUENCIA_ID_GEN;

CREATE TRIGGER T_SECUENCIA_BI
FOR SECUENCIA BEFORE INSERT
AS
BEGIN
  IF (NEW.ID IS NULL) THEN
      NEW.ID = GEN_ID(SECUENCIA_ID_GEN, 1);
END;


CREATE OR ALTER PROCEDURE SP_GENERA_SECUENCIA(CODIGO VARCHAR(60)) 
RETURNS (
	SECUENCIA INTEGER
)
AS
DECLARE SEC INTEGER;
BEGIN
  UPDATE SECUENCIA 
  	SET NUMERO = NUMERO + 1 
  WHERE CODIGO = :CODIGO
  RETURNING NUMERO INTO :SECUENCIA;

  IF (SECUENCIA IS NULL) THEN
  BEGIN
  	INSERT INTO SECUENCIA(CODIGO, DESCRIPCION, NUMERO) VALUES (:CODIGO, :CODIGO, 1);
  	SECUENCIA = 1;
  END
  SUSPEND;
END;

-- EJ
/*
 * SELECT SECUENCIA FROM SP_GENERA_SECUENCIA('PRUEBA');
 * ó .. NEW.SECUENCIA = (SELECT SECUENCIA FROM SP_GENERA_SECUENCIA('PRUEBA'))
 */
CREATE OR ALTER PROCEDURE DATE_TO_CHAR(TDATE TIMESTAMP, FORMAT VARCHAR(100)) RETURNS (
	RESULT VARCHAR(100)
)
AS
DECLARE SDATE VARCHAR(100);
DECLARE DD VARCHAR(2);
DECLARE MM VARCHAR(2);
DECLARE YY VARCHAR(4);
DECLARE HH VARCHAR(2);
DECLARE MI VARCHAR(2);
DECLARE SS VARCHAR(2);
DECLARE ZZZ VARCHAR(3);
DECLARE XFORMAT VARCHAR(100);
DECLARE AM VARCHAR(2);
BEGIN
	XFORMAT = UPPER(FORMAT);
	SDATE = CAST(TDATE AS VARCHAR(100));
	YY = SUBSTRING(SDATE FROM 1 FOR 4);
	MM = SUBSTRING(SDATE FROM 6 FOR 2);
	DD = SUBSTRING(SDATE FROM 9 FOR 2);
	HH = SUBSTRING(SDATE FROM 12 FOR 2);
	MI = SUBSTRING(SDATE FROM 15 FOR 2);
	SS = SUBSTRING(SDATE FROM 18 FOR 2);
	ZZZ = SUBSTRING(SDATE FROM 21 FOR 3);

	XFORMAT = REPLACE(XFORMAT, 'YYYY', YY);
	XFORMAT = REPLACE(XFORMAT, 'MM', MM);
	XFORMAT = REPLACE(XFORMAT, 'DD', DD);
	XFORMAT = REPLACE(XFORMAT, 'YY', SUBSTRING(YY FROM 3 FOR 2));
	XFORMAT = REPLACE(XFORMAT, 'HH24', HH);

	AM = 'AM';

	IF (HH='12') THEN
	BEGIN
		AM = 'M';
		IF (MI > '00') THEN 
		BEGIN
			AM='PM';
		END
	END
	
	IF (HH='00') THEN 
	BEGIN
		HH='12';
		AM='AM';
	END
	
	IF (HH>'12') THEN
	BEGIN
		HH = TRIM(CAST(CAST(HH AS INTEGER)-12 AS VARCHAR(2)));
		IF (CHAR_LENGTH(HH)<2) THEN 
		BEGIN
			HH='0'||HH;
		END
		AM='PM';
	END
	XFORMAT = REPLACE(XFORMAT, 'HH12', HH);
	XFORMAT = REPLACE(XFORMAT, 'HH', HH);
	XFORMAT = REPLACE(XFORMAT, 'MI', MI);
	XFORMAT = REPLACE(XFORMAT, 'SS', SS);
	XFORMAT = REPLACE(XFORMAT, 'ZZZ', ZZZ);

	RESULT = XFORMAT;
	SUSPEND;
END;
/********* tables *************/

CREATE TABLE xsequences (
	id      int IDENTITY(1,1) NOT NULL,
	code    varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	prefix  varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NOT NULL,
	digits  int DEFAULT 1 NOT NULL,
	currsec int DEFAULT 0 NOT NULL,
	PRIMARY KEY (id)
);

CREATE INDEX ndx_seq_code_prf ON xsequences(code, prefix);

CREATE TABLE HISDBTST.dbo.xsequences_format (
	id      int IDENTITY(1,1) NOT NULL,
	code    varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	prefix  varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NOT NULL,
	digits  int DEFAULT 1 NOT NULL,
	PRIMARY KEY (id)
);

CREATE INDEX ndx_seq_fmt_code ON xsequences_format(code);




/************************ p_sequence: Sequence generator ***********/
/*
code            prefix          digits (zero filled)
--------------  -----------     ------
PATRECORD             yyMM-	    4
EMERGENCY       "EM"yyMMdd-	    3
OUTPATIENT	    "OP"yyMMdd-	    3
INPATIENT   	"IP"yyMMdd-	    3
REQUIREMENT	    "RQ"yyMMdd-	    3
*/

CREATE procedure [dbo].p_sequence(
	@code   varchar(20), 
	@date   datetime, 
	@result varchar(100) output
)
as
begin
	declare @currsec int;
	declare @prefix varchar(20);
	declare @digits int;

	select @prefix=prefix,
	        @digits=digits
	from xsequences_format sf 
	where code=@code;

	set @prefix = format(isnull(@date,getdate()), @prefix);
	
	insert into xsequences(code, prefix, currsec)
	select @code, @prefix, 0
	WHERE NOT EXISTS(SELECT ID FROM xsequences WHERE code=@code and prefix=@prefix);	

	update dbo.xsequences 
	set currsec=currsec + 1, @currsec = currsec + 1
	WHERE code=@code and prefix=@prefix;

	set @result = isnull(@prefix,'') + dbo.lpad(@currsec, @digits);
end;



/********************* trigger example **************************/

CREATE TRIGGER [dbo].t_patient_record  ON  [dbo].patient 
for insert not for replication 
AS 
BEGIN
	SET NOCOUNT ON;
	
	declare @RecordNo varchar(30)
	declare @Date datetime = GETDATE();

	-- select @Date = Creation from inserted;

    /***/
	exec p_sequence 'RECORD', @Date, @RecordNo output;
	/***/
	
	update Pacient set RecordNo=@RecordNo where PatientId =(select PatientId from inserted)
END

Similiar Collections

Python strftime reference pandas.Period.strftime python - Formatting Quarter time in pandas columns - Stack Overflow python - Pandas: Change day - Stack Overflow python - Check if multiple columns exist in a df - Stack Overflow Pandas DataFrame apply() - sending arguments examples python - How to filter a dataframe of dates by a particular month/day? - Stack Overflow python - replace a value in the entire pandas data frame - Stack Overflow python - Replacing blank values (white space) with NaN in pandas - Stack Overflow python - get list from pandas dataframe column - Stack Overflow python - How to drop rows of Pandas DataFrame whose value in a certain column is NaN - Stack Overflow python - How to drop rows of Pandas DataFrame whose value in a certain column is NaN - Stack Overflow python - How to lowercase a pandas dataframe string column if it has missing values? - Stack Overflow How to Convert Integers to Strings in Pandas DataFrame - Data to Fish How to Convert Integers to Strings in Pandas DataFrame - Data to Fish create a dictionary of two pandas Dataframe columns? - Stack Overflow python - ValueError: No axis named node2 for object type <class 'pandas.core.frame.DataFrame'> - Stack Overflow Python Pandas iterate over rows and access column names - Stack Overflow python - Creating dataframe from a dictionary where entries have different lengths - Stack Overflow python - Deleting DataFrame row in Pandas based on column value - Stack Overflow python - How to check if a column exists in Pandas - Stack Overflow python - Import pandas dataframe column as string not int - Stack Overflow python - What is the most efficient way to create a dictionary of two pandas Dataframe columns? - Stack Overflow Python Loop through Excel sheets, place into one df - Stack Overflow python - How do I get the row count of a Pandas DataFrame? - Stack Overflow python - How to save a new sheet in an existing excel file, using Pandas? - Stack Overflow Python Loop through Excel sheets, place into one df - Stack Overflow How do I select a subset of a DataFrame? — pandas 1.2.4 documentation python - Delete column from pandas DataFrame - Stack Overflow python - Convert list of dictionaries to a pandas DataFrame - Stack Overflow How to Add or Insert Row to Pandas DataFrame? - Python Examples python - Check if a value exists in pandas dataframe index - Stack Overflow python - Set value for particular cell in pandas DataFrame using index - Stack Overflow python - Pandas Dataframe How to cut off float decimal points without rounding? - Stack Overflow python - Pandas: Change day - Stack Overflow python - Clean way to convert quarterly periods to datetime in pandas - Stack Overflow Pandas - Number of Months Between Two Dates - Stack Overflow python - MonthEnd object result in <11 * MonthEnds> instead of number - Stack Overflow python - Extracting the first day of month of a datetime type column in pandas - Stack Overflow
MySQL MULTIPLES INNER JOIN How to Use EXISTS, UNIQUE, DISTINCT, and OVERLAPS in SQL Statements - dummies postgresql - SQL OVERLAPS PostgreSQL Joins: Inner, Outer, Left, Right, Natural with Examples PostgreSQL Joins: A Visual Explanation of PostgreSQL Joins PL/pgSQL Variables ( Format Dates ) The Ultimate Guide to PostgreSQL Date By Examples Data Type Formatting Functions PostgreSQL - How to calculate difference between two timestamps? | TablePlus Date/Time Functions and Operators PostgreSQL - DATEDIFF - Datetime Difference in Seconds, Days, Months, Weeks etc - SQLines CASE Statements in PostgreSQL - DataCamp SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN PostgreSQL DESCRIBE TABLE Quick and best way to Compare Two Tables in SQL - DWgeek.com sql - Best way to select random rows PostgreSQL - Stack Overflow PostgreSQL: Documentation: 13: 70.1. Row Estimation Examples Faster PostgreSQL Counting How to Add a Default Value to a Column in PostgreSQL - PopSQL How to Add a Default Value to a Column in PostgreSQL - PopSQL SQL Subquery - Dofactory SQL IN - SQL NOT IN - JournalDev DROP FUNCTION (Transact-SQL) - SQL Server | Microsoft Docs SQL : Multiple Row and Column Subqueries - w3resource PostgreSQL: Documentation: 9.5: CREATE FUNCTION PostgreSQL CREATE FUNCTION By Practical Examples datetime - PHP Sort a multidimensional array by element containing date - Stack Overflow database - Oracle order NULL LAST by default - Stack Overflow PostgreSQL: Documentation: 9.5: Modifying Tables PostgreSQL: Documentation: 14: SELECT postgresql - sql ORDER BY multiple values in specific order? - Stack Overflow How do I get the current unix timestamp from PostgreSQL? - Database Administrators Stack Exchange SQL MAX() with HAVING, WHERE, IN - w3resource linux - Which version of PostgreSQL am I running? - Stack Overflow Copying Data Between Tables in a Postgres Database php - How to remove all numbers from string? - Stack Overflow sql - How to get a list column names and datatypes of a table in PostgreSQL? - Stack Overflow postgresql - How do I remove all spaces from a field in a Postgres database in an update query? - Stack Overflow sql - How to get a list column names and datatypes of a table in PostgreSQL? - Stack Overflow How to change PRIMARY KEY of an existing PostgreSQL table? · GitHub Drop tables w Dependency Tracking ( constraints ) Import CSV File Into PosgreSQL Table How To Import a CSV into a PostgreSQL Database How can I drop all the tables in a PostgreSQL database? - Stack Overflow How can I drop all the tables in a PostgreSQL database? - Stack Overflow PostgreSQL CASE Statements & Examples using WHEN-THEN, if-else and switch | DataCamp PostgreSQL LEFT: Get First N Characters in a String How can I drop all the tables in a PostgreSQL database? - Stack Overflow How can I drop all the tables in a PostgreSQL database? - Stack Overflow PostgreSQL - Copy Table - GeeksforGeeks PostgreSQL BETWEEN Query with Example sql - Postgres Query: finding values that are not numbers - Stack Overflow PostgreSQL UPDATE Join with A Practical Example
Request API Data with JavaScript or PHP (Access a Json data with PHP API) PHPUnit – The PHP Testing Framework phpspec array_column How to get closest date compared to an array of dates in PHP Calculating past and future dates < PHP | The Art of Web PHP: How to check which item in an array is closest to a given number? - Stack Overflow implode php - Calculate difference between two dates using Carbon and Blade php - Create a Laravel Request object on the fly testing - How can I measure the speed of code written in PHP? testing - How can I measure the speed of code written in PHP? What to include in gitignore for a Laravel and PHPStorm project Laravel Chunk Eloquent Method Example - Tuts Make html - How to solve PHP error 'Notice: Array to string conversion in...' - Stack Overflow PHP - Merging two arrays into one array (also Remove Duplicates) - Stack Overflow php - Check if all values in array are the same - Stack Overflow PHP code - 6 lines - codepad php - Convert array of single-element arrays to one a dimensional array - Stack Overflow datetime - PHP Sort a multidimensional array by element containing date - Stack Overflow sql - Division ( / ) not giving my answer in postgresql - Stack Overflow Get current date, given a timezone in PHP? - Stack Overflow php - Get characters after last / in url - Stack Overflow Add space after 7 characters - PHP Coding Help - PHP Freaks php - Laravel Advanced Wheres how to pass variable into function? - Stack Overflow php - How can I manually return or throw a validation error/exception in Laravel? - Stack Overflow php - How to add meta data in laravel api resource - Stack Overflow php - How do I create a webhook? - Stack Overflow Webhooks - Examples | SugarOutfitters Accessing cells - PhpSpreadsheet Documentation Reading and writing to file - PhpSpreadsheet Documentation PHP 7.1: Numbers shown with scientific notation even if explicitely formatted as text · Issue #357 · PHPOffice/PhpSpreadsheet · GitHub How do I install Java on Ubuntu? nginx - How to execute java command from php page with shell_exec() function? - Stack Overflow exec - Executing a java .jar file with php - Stack Overflow Measuring script execution time in PHP - GeeksforGeeks How to CONVERT seconds to minutes? PHP: Check if variable exist but also if has a value equal to something - Stack Overflow How to declare a global variable in php? - Stack Overflow How to zip a whole folder using PHP - Stack Overflow php - Saving file into a prespecified directory using FPDF - Stack Overflow PHP 7.0 get_magic_quotes_runtime error - Stack Overflow How to Create an Object Without Class in PHP ? - GeeksforGeeks Recursion in PHP | PHPenthusiast PHP PDO Insert Tutorial Example - DEV Community PHP Error : Unparenthesized `a ? b : c ? d : e` is deprecate | Laravel.io mysql - Which is faster: multiple single INSERTs or one multiple-row INSERT? - Stack Overflow Display All PHP Errors: Basic & Advanced Usage Need to write at beginning of file with PHP - Stack Overflow Append at the beginning of the file in PHP - Stack Overflow PDO – Insert, update, and delete records in PHP – BrainBell php - How to execute a raw sql query with multiple statement with laravel - Stack Overflow
Clear config cache Eloquent DB::Table RAW Query / WhereNull Laravel Eloquent "IN" Query get single column value in laravel eloquent php - How to use CASE WHEN in Eloquent ORM? - Stack Overflow AND-OR-AND + brackets with Eloquent - Laravel Daily Database: Query Builder - Laravel - The PHP Framework For Web Artisans ( RAW ) Combine Foreach Loop and Eloquent to perform a search | Laravel.io Access Controller method from another controller in Laravel 5 How to Call a controller function in another Controller in Laravel 5 php - Create a Laravel Request object on the fly php - Laravel 5.6 Upgrade caused Logging to break Artisan Console - Laravel - The PHP Framework For Web Artisans What to include in gitignore for a Laravel and PHPStorm project php - Create a Laravel Request object on the fly Process big DB table with chunk() method - Laravel Daily How to insert big data on the laravel? - Stack Overflow php - How can I build a condition based query in Laravel? - Stack Overflow Laravel Chunk Eloquent Method Example - Tuts Make Database: Migrations - Laravel - The PHP Framework For Web Artisans php - Laravel Model Error Handling when Creating - Exception Laravel - Inner Join with Multiple Conditions Example using Query Builder - ItSolutionStuff.com laravel cache disable phpunit code example | Newbedev In PHP, how to check if a multidimensional array is empty? · Humblix php - Laravel firstOrNew how to check if it's first or new? - Stack Overflow get base url laravel 8 Code Example Using gmail smtp via Laravel: Connection could not be established with host smtp.gmail.com [Connection timed out #110] - Stack Overflow php - Get the Last Inserted Id Using Laravel Eloquent - Stack Overflow php - Laravel-5 'LIKE' equivalent (Eloquent) - Stack Overflow Accessing cells - PhpSpreadsheet Documentation How to update chunk records in Laravel php - How to execute external shell commands from laravel controller? - Stack Overflow How to convert php array to laravel collection object 3 Best Laravel Redis examples to make your site load faster How to Create an Object Without Class in PHP ? - GeeksforGeeks Case insensitive search with Eloquent | Laravel.io How to Run Specific Seeder in Laravel? - ItSolutionStuff.com PHP Error : Unparenthesized `a ? b : c ? d : e` is deprecate | Laravel.io How to chunk query results in Laravel php - How to execute a raw sql query with multiple statement with laravel - Stack Overflow
PostgreSQL POSITION() function PostgresQL ANY / SOME Operator ( IN vs ANY ) PostgreSQL Substring - Extracting a substring from a String How to add an auto-incrementing primary key to an existing table, in PostgreSQL PostgreSQL STRING_TO_ARRAY()function mysql FIND_IN_SET equivalent to postgresql PL/pgSQL Variables ( Format Dates ) The Ultimate Guide to PostgreSQL Date By Examples Data Type Formatting Functions PostgreSQL - How to calculate difference between two timestamps? | TablePlus Date/Time Functions and Operators PostgreSQL - DATEDIFF - Datetime Difference in Seconds, Days, Months, Weeks etc - SQLines CASE Statements in PostgreSQL - DataCamp SQL Optimizations in PostgreSQL: IN vs EXISTS vs ANY/ALL vs JOIN PL/pgSQL Variables PostgreSQL: Documentation: 11: CREATE PROCEDURE Reading a Postgres EXPLAIN ANALYZE Query Plan Faster PostgreSQL Counting sql - Fast way to discover the row count of a table in PostgreSQL - Stack Overflow PostgreSQL: Documentation: 9.1: tablefunc PostgreSQL DESCRIBE TABLE Quick and best way to Compare Two Tables in SQL - DWgeek.com sql - Best way to select random rows PostgreSQL - Stack Overflow How to Add a Default Value to a Column in PostgreSQL - PopSQL How to Add a Default Value to a Column in PostgreSQL - PopSQL PL/pgSQL IF Statement PostgreSQL: Documentation: 9.1: Declarations SQL Subquery - Dofactory SQL IN - SQL NOT IN - JournalDev PostgreSQL - IF Statement - GeeksforGeeks How to work with control structures in PostgreSQL stored procedures: Using IF, CASE, and LOOP statements | EDB PL/pgSQL IF Statement How to combine multiple selects in one query - Databases - ( loop reference ) DROP FUNCTION (Transact-SQL) - SQL Server | Microsoft Docs SQL : Multiple Row and Column Subqueries - w3resource PostgreSQL: Documentation: 9.5: CREATE FUNCTION PostgreSQL CREATE FUNCTION By Practical Examples datetime - PHP Sort a multidimensional array by element containing date - Stack Overflow database - Oracle order NULL LAST by default - Stack Overflow PostgreSQL: Documentation: 9.5: Modifying Tables PostgreSQL: Documentation: 14: SELECT PostgreSQL Array: The ANY and Contains trick - Postgres OnLine Journal postgresql - sql ORDER BY multiple values in specific order? - Stack Overflow sql - How to aggregate two PostgreSQL columns to an array separated by brackets - Stack Overflow How do I get the current unix timestamp from PostgreSQL? - Database Administrators Stack Exchange SQL MAX() with HAVING, WHERE, IN - w3resource linux - Which version of PostgreSQL am I running? - Stack Overflow Postgres login: How to log into a Postgresql database | alvinalexander.com Copying Data Between Tables in a Postgres Database PostgreSQL CREATE FUNCTION By Practical Examples php - How to remove all numbers from string? - Stack Overflow sql - How to get a list column names and datatypes of a table in PostgreSQL? - Stack Overflow postgresql - How do I remove all spaces from a field in a Postgres database in an update query? - Stack Overflow sql - How to get a list column names and datatypes of a table in PostgreSQL? - Stack Overflow A Step-by-Step Guide To PostgreSQL Temporary Table How to change PRIMARY KEY of an existing PostgreSQL table? · GitHub PostgreSQL UPDATE Join with A Practical Example PostgreSQL: Documentation: 15: CREATE SEQUENCE How can I drop all the tables in a PostgreSQL database? - Stack Overflow PostgreSQL Show Tables Drop tables w Dependency Tracking ( constraints ) Import CSV File Into PosgreSQL Table How To Import a CSV into a PostgreSQL Database How can I drop all the tables in a PostgreSQL database? - Stack Overflow How can I drop all the tables in a PostgreSQL database? - Stack Overflow PostgreSQL CASE Statements & Examples using WHEN-THEN, if-else and switch | DataCamp PostgreSQL LEFT: Get First N Characters in a String How can I drop all the tables in a PostgreSQL database? - Stack Overflow How can I drop all the tables in a PostgreSQL database? - Stack Overflow postgresql - Binary path in the pgAdmin preferences - Database Administrators Stack Exchange postgresql - Binary path in the pgAdmin preferences - Database Administrators Stack Exchange PostgreSQL - Copy Table - GeeksforGeeks postgresql duplicate key violates unique constraint - Stack Overflow PostgreSQL BETWEEN Query with Example VACUUM FULL - PostgreSQL wiki How To Remove Spaces Between Characters In PostgreSQL? - Database Administrators Stack Exchange sql - Postgres Query: finding values that are not numbers - Stack Overflow PostgreSQL LEFT: Get First N Characters in a String unaccent: Getting rid of umlauts, accents and special characters
כמה עוד נשאר למשלוח חינם גם לעגלה ולצקאאוט הוספת צ'קבוקס לאישור דיוור בצ'קאאוט הסתרת אפשרויות משלוח אחרות כאשר משלוח חינם זמין דילוג על מילוי כתובת במקרה שנבחרה אפשרות איסוף עצמי הוספת צ'קבוקס לאישור דיוור בצ'קאאוט שינוי האפשרויות בתפריט ה-סידור לפי בווקומרס שינוי הטקסט "אזל מהמלאי" הערה אישית לסוף עמוד העגלה הגבלת רכישה לכל המוצרים למקסימום 1 מכל מוצר קבלת שם המוצר לפי ה-ID בעזרת שורטקוד הוספת כפתור וואטסאפ לקנייה בלופ ארכיון מוצרים הפיכה של מיקוד בצ'קאאוט ללא חובה מעבר ישיר לצ'קאאוט בלחיתה על הוספה לסל (דילוג עגלה) התראה לקבלת משלוח חינם בדף עגלת הקניות גרסה 1 התראה לקבלת משלוח חינם בדף עגלת הקניות גרסה 2 קביעה של מחיר הזמנה מינימלי (מוצג בעגלה ובצ'קאאוט) העברת קוד הקופון ל-ORDER REVIEW העברת קוד הקופון ל-ORDER REVIEW Kadence WooCommerce Email Designer קביעת פונט אסיסנט לכל המייל בתוסף מוצרים שאזלו מהמלאי - יופיעו מסומנים באתר, אבל בתחתית הארכיון הוספת כפתור "קנה עכשיו" למוצרים הסתרת אפשרויות משלוח אחרות כאשר משלוח חינם זמין שיטה 2 שינוי סימן מטבע ש"ח ל-ILS להפוך סטטוס הזמנה מ"השהייה" ל"הושלם" באופן אוטומטי תצוגת הנחה באחוזים שינוי טקסט "בחר אפשרויות" במוצרים עם וריאציות חיפוש מוצר לפי מק"ט שינוי תמונת מוצר לפי וריאציה אחרי בחירה של וריאציה אחת במקרה של וריאציות מרובות הנחה קבועה לפי תפקיד בתעריף קבוע הנחה קבועה לפי תפקיד באחוזים הסרה של שדות משלוח לקבצים וירטואליים הסתרת טאבים מעמוד מוצר הצגת תגית "אזל מהמלאי" בלופ המוצרים להפוך שדות ל-לא חובה בצ'קאאוט שינוי טקסט "אזל מהמלאי" לוריאציות שינוי צבע ההודעות המובנות של ווקומרס הצגת ה-ID של קטגוריות המוצרים בעמוד הקטגוריות אזל מהמלאי- שינוי ההודעה, תגית בלופ, הודעה בדף המוצר והוספת אזל מהמלאי על וריאציה הוספת שדה מחיר ספק לדף העריכה שינוי טקסט אזל מהמלאי תמונות מוצר במאונך לצד תמונת המוצר הראשית באלמנטור הוספת כפתור קנה עכשיו לעמוד המוצר בקניה הזו חסכת XX ש''ח לאפשר למנהל חנות לנקות קאש ברוקט לאפשר רק מוצר אחד בעגלת קניות הוספת סימון אריזת מתנה ואזור להוראות בצ'קאאוט של ווקומרס הצגת הנחה במספר (גודל ההנחה) הוספת "אישור תקנון" לדף התשלום הצגת רשימת תכונות המוצר בפרונט שינוי כמות מוצרים בצ'קאאוט ביטול השדות בצ'קאאוט שינוי כותרות ופלייסהולדר של השדות בצ'קאאוט
החלפת טקסט באתר (מתאים גם לתרגום נקודתי) הסרת פונטים של גוגל מתבנית KAVA ביטול התראות במייל על עדכון וורדפרס אוטומטי הוספת תמיכה בקבצי VCF באתר (קבצי איש קשר VCARD) - חלק 1 להחריג קטגוריה מסוימת מתוצאות החיפוש שליפת תוכן של ריפיטר יצירת כפתור שיתוף למובייל זיהוי אלו אלמנטים גורמים לגלילה אופקית התקנת SMTP הגדרת טקסט חלופי לתמונות לפי שם הקובץ הוספת התאמת תוספים לגרסת WP הוספת טור ID למשתמשים הסרת כותרת בתבנית HELLO הסרת תגובות באופן גורף הרשאת SVG חילוץ החלק האחרון של כתובת העמוד הנוכחי חילוץ הסלאג של העמוד חילוץ כתובת העמוד הנוכחי מניעת יצירת תמונות מוקטנות התקנת SMTP הצגת ה-ID של קטגוריות בעמוד הקטגוריות להוריד מתפריט הניהול עמודים הוספת Favicon שונה לכל דף ודף הוספת אפשרות שכפול פוסטים ובכלל (של שמעון סביר) הסרת תגובות באופן גורף 2 בקניה הזו חסכת XX ש''ח חיפוש אלמנטים סוררים, גלישה צדית במובייל שיטה 1 לאפשר רק מוצר אחד בעגלת קניות הצגת הנחה במספר (גודל ההנחה) הוספת "אישור תקנון" לדף התשלום שינוי צבע האדמין לפי סטטוס העמוד/פוסט שינוי צבע אדמין לכולם לפי הסכמות של וורדפרס תצוגת כמות צפיות מתוך הדשבורד של וורדפרס הצגת סוג משתמש בפרונט גלילה אין סופית במדיה שפת הממשק של אלמנטור תואמת לשפת המשתמש אורך תקציר מותאם אישית
הודעת שגיאה מותאמת אישית בטפסים להפוך כל סקשן/עמודה לקליקבילית (לחיצה) - שיטה 1 להפוך כל סקשן/עמודה לקליקבילית (לחיצה) - שיטה 2 שינוי הגבלת הזיכרון בשרת הוספת לינק להורדת מסמך מהאתר במייל הנשלח ללקוח להפוך כל סקשן/עמודה לקליקבילית (לחיצה) - שיטה 3 יצירת כפתור שיתוף למובייל פתיחת דף תודה בטאב חדש בזמן שליחת טופס אלמנטור - טופס בודד בדף פתיחת דף תודה בטאב חדש בזמן שליחת טופס אלמנטור - טפסים מרובים בדף ביי ביי לאריק ג'ונס (חסימת ספאם בטפסים) זיהוי אלו אלמנטים גורמים לגלילה אופקית לייבלים מרחפים בטפסי אלמנטור יצירת אנימציה של "חדשות רצות" בג'ט (marquee) שינוי פונט באופן דינאמי בג'ט פונקציה ששולפת שדות מטא מתוך JET ומאפשרת לשים הכל בתוך שדה SELECT בטופס אלמנטור הוספת קו בין רכיבי התפריט בדסקטופ ולדציה למספרי טלפון בטפסי אלמנטור חיבור שני שדות בטופס לשדה אחד שאיבת נתון מתוך כתובת ה-URL לתוך שדה בטופס וקידוד לעברית מדיה קוורי למובייל Media Query תמונות מוצר במאונך לצד תמונת המוצר הראשית באלמנטור הצגת תאריך עברי פורמט תאריך מותאם אישית תיקון שדה תאריך בטופס אלמנטור במובייל שאיבת פרמטר מתוך הכתובת והזנתו לתוך שדה בטופס (PARAMETER, URL, INPUT) עמודות ברוחב מלא באלמנטור עמודה דביקה בתוך אלמנטור יצירת "צל" אומנותי קוד לסוויצ'ר, שני כפתורים ושני אלמנטים סקריפט לסגירת פופאפ של תפריט לאחר לחיצה על אחד העמודים הוספת כפתור קרא עוד שפת הממשק של אלמנטור תואמת לשפת המשתמש להריץ קוד JS אחרי שטופס אלמנטור נשלח בהצלחה מצב ממורכז לקרוסלת תמונות של אלמנטור לייבלים מרחפים בטפסי פלואנטפורמס