$this->dataArray[$name_dataArray][$array[$i]]=$array[$i+1];$this->queryColumn[]=$array[$i];

}else{$this->ERROR=true;$this->ERROR_STR.="[set]::cтолбец '".$array[$i]."' не ^[a-zA-Z0-9_.]*$ <br>";}

}}else{$this->ERROR = true;$this->ERROR_STR.= "[".$name_dataArray."]::количество ".sizeof($array)."!%2 <br>";}

return $this;

}

//--CONSTRUCT DATA--------------------------------------------------------------

private function constructWhere(){return $this->paker_1("where","WHERE","AND","constructWhere");}

private function constructSet(){return $this->paker_1("set","SET",",","constructSet");}

private function constructDistinct(){if(sizeof($this->dataArray['distinct'])>0){return " DISTINCT ON(".implode(", ",$this->dataArray['distinct']).") ";}else{return "";}}

private function constructAscOrderBy(){if(sizeof($this->dataArray['ascOrderBy'])>0){return " ORDER BY (".implode(", ",$this->dataArray['ascOrderBy']).") ASC ";}else{return "";}}

private function constructDescOrderBy(){if(sizeof($this->dataArray['descOrderBy'])>0){return " ORDER BY (".implode(", ",$this->dataArray['descOrderBy']).") DESC ";}else{return "";}}

private function constructColumns(){if(sizeof($this->dataArray['columns'])>0){return " (".implode(", ",$this->dataArray['columns']).") ";}else{return "";}}

private function constructValues(){if(sizeof($this->dataArray['values'])>0){$ar=array();foreach($this->dataArray["merge_insert"] as $key => $value){$ar[]=$value;};return " VALUES (".implode(", ",$ar).") ";}else{return "";}}

private function constructJoin(){

$ar = array();

for($i=0;$i<sizeof($this->dataArray["joinArray"]);$i++){

Switch($this->dataArray["joinArray"][$i]["method"]){

case"using":

$ar[] = $this->dataArray["joinArray"][$i]["type"]." ".$this->dataArray["joinArray"][$i]["table"]." USING (".implode(", ",$this->dataArray["joinArray"][$i]["ar"]).") ";

break;

}

};return " ".implode(" ",$ar);

}

//--CHECKER DATA-----------------------------------------------------------------

private function check(){

$ar = array();$ar_table = array();

$arTable = array_merge($this->dataArray['table'],$this->dataArray['EXT_table']);

for($i=0;$i<sizeof($arTable);$i++){$ar_table[$i] = explode(" ",$arTable[$i]);$ar_table[$i] = $ar_table[$i][0];}

for($i=0;$i<sizeof($ar_table);$i++){$ar[] = "table_name = '".$ar_table[$i]."'";}

$query = "select column_name,column_default,data_type,is_nullable,table_name FROM information_schema.columns where table_schema = 'public' and (".implode(" OR ",$ar).")";

$query = $_ENV['db']->send_query($query);

for($i=0;$i<sizeof($ar_table);$i++){

$flag = true;

for($j=0;$j<sizeof($query);$j++){if($ar_table[$i]==$query[$j]['table_name']){$flag=false;break;}}

if($flag){$this->ERROR=true;$this->ERROR_STR.="[check]::таблица '".$ar_table[$i]."' не найдена<br>";}

}

if(sizeof($query)>0){

$this->checkErrorHandler($this->dataArray["where"]);$this->checkErrorHandler($this->dataArray["set"]);

$this->restore_value("where",$query);$this->restore_value("set",$query);

$this->isset_select_columns($query);$this->isset_insert_value($query);

}else{

$this->ERROR=true;$this->ERROR_STR.="[check]::колонки в таблице '".implode(" , ",$arTable)."' не найдены<br>";

}

}

private function isset_insert_value($meta_info){

for($i=0;$i<sizeof($this->dataArray['columns']);$i++){

if($this->dataArray['columns'][$i]!="" && $this->dataArray['values'][$i]!=""){

$this->dataArray["merge_insert"][$this->dataArray['columns'][$i]] = $this->dataArray['values'][$i];

}

};

$this->checkErrorHandler($this->dataArray["merge_insert"]);

$this->restore_value("merge_insert",$meta_info);

}

private function isset_select_columns($meta_info){

for($i=0;$i<sizeof($this->dataArray['select']);$i++){

$column_name = explode(".",$this->dataArray['select'][$i]);

if(sizeof($column_name)>1){$column_name = $column_name[1];}else{$column_name = $column_name[0];}

for($j=0;$j<sizeof($meta_info);$j++){

$flag = false;

if($column_name==$meta_info[$j]["column_name"]){$flag=true;break;}elseif($column_name=="*"){$flag=true;break;}

}

if(!$flag){$this->ERROR=true;$this->ERROR_STR.="[isset_select_columns]::колонка '".$this->dataArray['select'][$i]."' не найдена.<br>";}

}

}

private function checkErrorHandler($array){

if(sizeof($this->dataArray['errorHandler'])>0){

foreach($array as $key => $value){

for($i=0;$i<sizeof($this->dataArray['errorHandler']);$i++){

if($key == $this->dataArray['errorHandler'][$i][0]){

if(!preg_match($this->dataArray['errorHandler'][$i][1],$value)){$this->ERROR=true;$this->ERROR_STR.=$this->dataArray['errorHandler'][$i][2].". <br>";}break;

}

}

}

}

}

private function restore_value($key_data,$meta_info){

foreach($this->dataArray[$key_data] as $key => $value){

$flag = false;$column_name = explode(".",$key);

if(sizeof($column_name)>1){$column_name = $column_name[1];}else{$column_name = $column_name[0];}

for($i=0;$i<sizeof($meta_info);$i++){

if($column_name==$meta_info[$i]["column_name"]){

$flag = true;

if($this->is_type("int",$meta_info[$i]["data_type"])){

if($value==""){

if($meta_info[$i]["column_default"]==""){

if($meta_info[$i]["is_nullable"]=="NO"){$this->ERROR.=true;$this->ERROR_STR="[restoreValue]::колонка '".$key."' должна иметь значение типа ".$meta_info[$i]["data_type"]." не нулевое. <br>";

}else{$this->dataArray[$key_data][$key] = "null";}

}else{$this->dataArray[$key_data][$key] = $meta_info[$i]["column_default"];}

}else{

if(is_numeric($value)){$this->dataArray[$key_data][$key] = $value;}

else{$this->ERROR=true;$this->ERROR_STR.="[restoreValue]::значение колонки '".$key."' не может быть равно '".$value."' так как тип данных ".$meta_info[$i]["data_type"].". <br>";}

}

}elseif($this->is_type("char",$meta_info[$i]["data_type"]) || $this->is_type("text",$meta_info[$i]["data_type"])){

if($value==""){

if($meta_info[$i]["column_default"]==""){

if($meta_info[$i]["is_nullable"]=="NO"){$this->ERROR.=true;$this->ERROR_STR="[restoreValue]::колонка '".$key."' должна иметь значение типа ".$meta_info[$i]["data_type"]." не нулевое. <br>";

}else{$this->dataArray[$key_data][$key] = "null";}

}else{$this->dataArray[$key_data][$key] = $meta_info[$i]["column_default"];}

}else{

if(is_string($value)){$this->dataArray[$key_data][$key] = "'".$this->string_encode($value)."'";}

else{$this->ERROR=true;$this->ERROR_STR.="[restoreValue]::значение колонки '".$key."' не может быть равно '".$value."' так как тип данных ".$meta_info[$i]["data_type"].". <br>";}

}

}elseif($this->is_type("date",$meta_info[$i]["data_type"]) || $this->is_type("times",$meta_info[$i]["data_type"])){

if($value==""){

if($meta_info[$i]["column_default"]==""){

if($meta_info[$i]["is_nullable"]=="NO"){$this->ERROR.=true;$this->ERROR_STR="[restoreValue]::колонка '".$key."' должна иметь значение типа ".$meta_info[$i]["data_type"]." не нулевое. <br>";

}else{$this->dataArray[$key_data][$key] = "null";}

}else{$this->dataArray[$key_data][$key] = $meta_info[$i]["column_default"];}

}else{

if(@date("Y-m-d",strtotime($value))==$value){

$this->dataArray[$key_data][$key] = "'".$this->string_encode($value)."'";

}else{

$this->ERROR=true;$this->ERROR_STR.="[restoreValue]::значение колонки '".$key."' не может быть равно '".$value."' так как тип данных ".$meta_info[$i]["data_type"]." (Y-m-d). <br>";

}

}

}

}

};if(!$flag){$this->ERROR=true;$this->ERROR_STR.="[restoreValue]::колонка '".$key."' не найдена.<br>";}

}

}

private function is_type($type,$str){if(sizeof(explode($type,$str))>1){return true;}else{return false;}}

private function string_encode($str){$str = htmlspecialchars($str,ENT_QUOTES);return $str;}

//--RETURN ANSWER---------------------------------------------------------------

private function getQuery(){

$this->check();

$this->checker = true;

$answer = "";

Switch($this->direction){

case"SELECT ":

$answer = $this->direction.$this->constructDistinct().implode(", ",$this->dataArray["select"])." FROM ".implode(", ",$this->dataArray["table"]).$this->constructJoin().$this->constructWhere().$this->constructAscOrderBy().$this->constructDescOrderBy();

break;

case"UPDATE ":

$answer = $this->direction.implode(", ",$this->dataArray["table"]).$this->constructSet().$this->constructWhere().$this->constructAscOrderBy().$this->constructDescOrderBy();

break;

case"INSERT INTO ":

$answer = $this->direction.implode(", ",$this->dataArray["table"]).$this->constructColumns().$this->constructValues();

break;

case"DELETE ":

$answer = $this->direction."FROM ".implode(", ",$this->dataArray["table"]).$this->constructWhere();

break;

default:

$this->ERROR=true;$this->ERROR_STR.= "[getQuery]::не определено направление [select||update||insert] <br>";

break;

};return $answer;

}

private function getUsingColumn(){return array_unique($this->queryColumn);}

public function getTextSQL(){return $this->getQuery();}

//---SEND--------------------------------------------------------------------------

public function errorHandler(){$this->dataArray['errorHandler'] = func_get_args();return $this;}

public function perform(){

$query = $this->getQuery();

if(!$this->ERROR){

if($query!=""){

if($this->cache){

$md5_name = md5($query.$_ENV['config']['system_password']);

$filename = $_ENV['config']['cache_query_dir'].$md5_name.".txt";

if($handler=@fopen($filename,'r')){

$this->ans = $_ENV['ext']->getCacheArray(&$handler);

}else{

$this->ans = $_ENV['db']->send_query($query);

$_ENV['ext']->cacheArray($this->ans,$filename);

}

}else{

$this->ans = @$_ENV['db']->send_query($query);

}

if($this->ans){foreach($this->ans[0] as $key=>$value){$this->alias($key,$key);}}

}else{

$this->ERROR=true;$this->ERROR_STR.= "[send]::запрос пуст <br>";

}

};return $this;

}

public function copy(&$tmp){$tmp = $this->ans;return $this;}

public function error(&$flag,&$error,$jxNotice=false){

if(!$this->checker){$this->check();}

if($this->ERROR){

$flag = true;

$error="<div style='font-family:Verdana;font-size:8pt;padding:5px;border:1px solid #cccccc;background:#efefef;width:400px;color:#777777;'>".$this->ERROR_STR."</div>";

if($jxNotice){

$_ENV['ajax']->jxCode("alert(\"".str_replace("<br>","\r\n",$this->ERROR_STR)."\");");

}

}else{$flag = false;};return $this;}

public function cache(){$this->cache=true;return $this;}

public function group($key){

if(!$this->ERROR){

$array = func_get_args();

if($this->sort_deptch==0){$this->ans=$this->grouper($this->ans,$key,$array);}

else{$this->ans=$this->grouper2($this->ans,$key,$array,0);};

$this->sort_deptch++;

};return $this;

}

private function grouper2($arrays,$key,$array,$deptch){//---group in deptch---

for($i=0;$i<sizeof($arrays);$i++){

if($deptch==$this->sort_deptch-1){$arrays[$i]["group"] = $this->grouper($arrays[$i]["group"],$key,$array);}

else{$b=$deptch+1;$arrays[$i]["group"]=$this->grouper2($arrays[$i]["group"],$key,$array,$b);}

};return $arrays;

}

private function grouper($arrays,$key,$array){//--group local array---

$dop = array();for($i=2;$i<sizeof($array);$i++){$dop[] = $array[$i];}

$uniq_key = array();$uniq_key2 = array();

for($i=0;$i<sizeof($arrays);$i++){foreach($arrays[$i] as $tmp_key=>$tmp_value){if($tmp_key==$key){$uniq_key[]=$tmp_value;}}}

$uniq_key = array_unique($uniq_key);

foreach($uniq_key as $tmp_key => $value){$uniq_key2[]=$value;};$uniq_key = $uniq_key2;

$uniq_key2 = array();

for($i=0;$i<sizeof($uniq_key);$i++){

$uniq_key2[$i]["meta"][$key] = $uniq_key[$i];

for($j=0;$j<sizeof($arrays);$j++){

if($arrays[$j][$key]==$uniq_key[$i]){

$uniq_key2[$i]["group"][] = $arrays[$j];

if(sizeof($uniq_key2[$i]["group"])==1){

for($g=0;$g<sizeof($dop);$g++){$uniq_key2[$i]["meta"][$dop[$g]] = $arrays[$j][$dop[$g]];}

}

}

}

};return $uniq_key2;

}

public function alias(){$arg=func_get_args();for($i=0;$i<sizeof($arg);$i+=2){$this->alias_ar[$arg[$i]]=$arg[$i+1];};return $this;}

public function createXML(){

if(!$this->ERROR){

$arg = func_get_args();$this->inner_tag_name = $arg[0];

$this->use_xml_tag = array();

for($i=1;$i<sizeof($arg);$i++){$this->use_xml_tag[]=$arg[$i];}

$this->dom = new DOMDocument('1.0','UTF-8');

$this->root = $this->dom->createElement('dbQuery');

$this->dom->appendChild($this->root);

$this->recursiveXML($this->ans,$this->root);

$this->ans = $this->dom->saveXML();

};return $this;

}

private function recursiveXML($array,&$ref_parent){

for($i=0;$i<sizeof($array);$i++){

if(is_array($array[$i]["meta"]) || is_array($array[$i]["group"])){

$counter_foreach = 0;$arg = array();

foreach($array[$i]["meta"] as $key => $value){

if($counter_foreach==0){$tag_name=$this->alias_ar[$key];$arg[]=$key;$arg[]=$value;}else{$arg[]=$key;$arg[]=$value;}

$counter_foreach++;

}

$tmp = $this->addTag($tag_name,"",$arg);$ref_parent->appendChild($tmp);

if(sizeof($array[$i]["group"])!=0){$this->recursiveXML($array[$i]["group"],$tmp);}

}else{

$arg = array();

for($j=0;$j<sizeof($this->use_xml_tag);$j++){

foreach($array[$i] as $key => $value){if($this->use_xml_tag[$j]==$key){$arg[]=$key;$arg[]=$value;break;}}

};$tmp=$this->addTag($this->inner_tag_name,"",$arg);$ref_parent->appendChild($tmp);

}

}

}

private function addTag($tag_name,$value){

$jx = $this->dom->createElement($tag_name,$value);

$args = func_get_args();

if(sizeof($args[2])!=0){

$args = $args[2];

for($i=0;$i<sizeof($args);$i+=2){

if($this->alias_ar[$args[$i]]!=""){$atr = $this->alias_ar[$args[$i]];}

else{$atr = $args[$i];}

$id_att= $this->dom->createAttribute($atr);

$jx_id = $this->dom->createTextNode($args[$i+1]);

$id_att->appendChild($jx_id);

$jx->appendChild($id_att);

}

};return $jx;

}

}

class postgreSQL{

function __construct(){

$this->hostname = $_ENV['config']['db_hostname'];

$this->database = $_ENV['config']['db_database'];

$this->username = $_ENV['config']['db_username'];

$this->password = $_ENV['config']['db_password'];

$this->port = $_ENV['config']['db_port'];

$this->link = pg_connect("host=$this->hostname port=$this->port dbname=$this->database user=$this->username password=$this->password") or $this->access=false;

}

public function send_query($query){

$args=func_get_args();

$result = pg_query($this->link,$query);

if($result){

$ar = array();

$row_counter = 0;

while($row = pg_fetch_assoc($result)){

$counter = 0;

foreach($row as $key => $value){

$ar[$row_counter][$key]=htmlspecialchars_decode($value,ENT_QUOTES)

$counter++;

}

$row_counter++;

};

switch($args[1]){

case"row":return $ar[$args[2]];break;

default:return $ar;break;

}

}else{return false;}

}

public function dataQuery(){return new dataQuery($ans);}

public function convertKeyToIndex($ans){

$ar=array();

for($i=0;$i<sizeof($ans);$i++){$ar[$i]=array();foreach($ans[$i] as $key => $value){$ar[$i][]=$value;}}

return $ar;

}

}

$_ENV['db'] = new postgreSQL();

?>

CREATE TABLE public.audio (

id_audio serial NOT NULL,

title text NOT NULL,

"text" text,

audio_file varchar(255) NOT NULL,

date_add date NOT NULL,

/* Keys */

CONSTRAINT audio_pkey

PRIMARY KEY (id_audio),

CONSTRAINT audio_audio_file_key

UNIQUE (audio_file)

) WITH (

OIDS = FALSE

);

ALTER TABLE public.audio

OWNER TO postgres;

CREATE TABLE public.block (

id_block serial NOT NULL,

id_page integer NOT NULL,

id_city integer,

block_position integer,

block_title text NOT NULL,

block_content text NOT NULL,

/* Keys */

CONSTRAINT block_pkey

PRIMARY KEY (id_block),

/* Checks */

CONSTRAINT block_block_content_check

CHECK (block_content <> ''::text),

CONSTRAINT block_block_title_check

CHECK (block_title <> ''::text),

/* Foreign keys */

CONSTRAINT block_id_city_fkey

FOREIGN KEY (id_city)

REFERENCES public.city(id_city)

ON DELETE SET NULL

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

CREATE TRIGGER block_fts

AFTER INSERT OR UPDATE

ON public.block

FOR EACH ROW

EXECUTE PROCEDURE public.block_fts();

CREATE TRIGGER check_block_bit

AFTER INSERT OR DELETE

ON public.block

FOR EACH ROW

EXECUTE PROCEDURE public.trg_check_block_bit();

ALTER TABLE public.block

OWNER TO postgres;

CREATE TABLE public.block_fts (

id_block integer NOT NULL,

block_fts tsvector,

/* Keys */

CONSTRAINT block_fts_pkey

PRIMARY KEY (id_block),

/* Foreign keys */

CONSTRAINT block_fts_id_block_fkey

FOREIGN KEY (id_block)

REFERENCES public.block(id_block)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

ALTER TABLE public.block_fts

OWNER TO postgres;

CREATE TABLE public.block_image (

id_block_image serial NOT NULL,

id_block integer NOT NULL,

file text NOT NULL,

/* Keys */

CONSTRAINT block_image_pkey

PRIMARY KEY (id_block_image),

CONSTRAINT block_image_file_key

UNIQUE (file),

/* Checks */

CONSTRAINT block_image_file_check

CHECK (file <> ''::text),

/* Foreign keys */

CONSTRAINT block_image_id_block_fkey

FOREIGN KEY (id_block)

REFERENCES public.block(id_block)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

ALTER TABLE public.block_image

OWNER TO postgres;

CREATE TABLE public.city (

id_city serial NOT NULL,

city_name varchar NOT NULL,

city_yes bit(1) NOT NULL DEFAULT B'0'::"bit",

/* Keys */

CONSTRAINT city_pkey

PRIMARY KEY (id_city),

/* Checks */

CONSTRAINT city_city_name_check

CHECK ((city_name)::text <> ''::text)

) WITH (

OIDS = FALSE

);

ALTER TABLE public.city

OWNER TO postgres;

CREATE TABLE public.gallery (

id_gallery serial NOT NULL,

id_gallery_album integer NOT NULL,

gallery_file text NOT NULL,

ru_about text,

/* Keys */

CONSTRAINT gallery_pkey

PRIMARY KEY (id_gallery),

CONSTRAINT gallery_gallery_file_key

UNIQUE (gallery_file),

/* Checks */

CONSTRAINT gallery_gallery_file_check

CHECK (gallery_file <> ''::text),

/* Foreign keys */

CONSTRAINT gallery_id_gallery_album_fkey

FOREIGN KEY (id_gallery_album)

REFERENCES public.gallery_album(id_gallery_album)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

ALTER TABLE public.gallery

OWNER TO postgres;

CREATE TABLE public.gallery_album (

id_gallery_album serial NOT NULL,

gallery_album_file text NOT NULL,

gallery_album_text text NOT NULL,

gallery_album_date_add date NOT NULL DEFAULT (now())::date,

/* Keys */

CONSTRAINT gallery_album_pkey

PRIMARY KEY (id_gallery_album),

CONSTRAINT gallery_album_gallery_album_file_key

UNIQUE (gallery_album_file),

/* Checks */

CONSTRAINT gallery_album_gallery_album_file_check

CHECK (gallery_album_file <> ''::text),

CONSTRAINT gallery_album_gallery_album_text_check

CHECK (gallery_album_text <> ''::text)

) WITH (

OIDS = FALSE

);

ALTER TABLE public.gallery_album

OWNER TO postgres;

CREATE TABLE public.gallery_magu (

id_gallery_magu serial NOT NULL,

gallery_file text NOT NULL,

ru_about text,

/* Keys */

CONSTRAINT gallery_magu_pkey

PRIMARY KEY (id_gallery_magu),

CONSTRAINT gallery_magu_gallery_file_key

UNIQUE (gallery_file),

/* Checks */

CONSTRAINT gallery_magu_gallery_file_check

CHECK (gallery_file <> ''::text)

) WITH (

OIDS = FALSE

);

ALTER TABLE public.gallery_magu

OWNER TO postgres;

CREATE TABLE public.gallery_magu_album (

id_gallery_magu_album serial NOT NULL,

gallery_magu_album_file text NOT NULL,

gallery_magu_album_text text NOT NULL,

gallery_magu_album_date_add date NOT NULL DEFAULT (now())::date,

/* Keys */

CONSTRAINT gallery_magu_album_pkey

PRIMARY KEY (id_gallery_magu_album),

CONSTRAINT gallery_magu_album_gallery_magu_album_file_key

UNIQUE (gallery_magu_album_file),

/* Checks */

CONSTRAINT gallery_magu_album_gallery_magu_album_file_check

CHECK (gallery_magu_album_file <> ''::text),

CONSTRAINT gallery_magu_album_gallery_magu_album_text_check

CHECK (gallery_magu_album_text <> ''::text)

) WITH (

OIDS = FALSE

);

ALTER TABLE public.gallery_magu_album

OWNER TO postgres;

CREATE TABLE public.gallery_top (

id_gallery_top serial NOT NULL,

file text NOT NULL,

/* Keys */

CONSTRAINT gallery_top_pkey

PRIMARY KEY (id_gallery_top),

CONSTRAINT gallery_top_file_key

UNIQUE (file),

/* Checks */

CONSTRAINT gallery_top_file_check

CHECK (file <> ''::text)

) WITH (

OIDS = FALSE

);

ALTER TABLE public.gallery_top

OWNER TO postgres;

CREATE TABLE public.journal (

id_journal serial NOT NULL,

journal_name text NOT NULL,

count_page integer NOT NULL,

date_add date NOT NULL DEFAULT (now())::date,

journal_href text NOT NULL,

/* Keys */

CONSTRAINT journal_pkey

PRIMARY KEY (id_journal),

CONSTRAINT journal_journal_href_key

UNIQUE (journal_href),

/* Checks */

CONSTRAINT journal_journal_href_check

CHECK (journal_href <> ''::text),

CONSTRAINT journal_journal_name_check

CHECK (journal_name <> ''::text)

) WITH (

OIDS = FALSE

);

ALTER TABLE public.journal

OWNER TO postgres;

CREATE TABLE public.link (

id_link serial NOT NULL,

link_text text,

link_url text NOT NULL,

link_image text,

/* Keys */

CONSTRAINT link_pkey

PRIMARY KEY (id_link),

CONSTRAINT link_link_image_key

UNIQUE (link_image),

/* Checks */

CONSTRAINT link_link_url_check

CHECK (link_url <> ''::text)

) WITH (

OIDS = FALSE

);

ALTER TABLE public.link

OWNER TO postgres;

CREATE TABLE public.main_page (

id_menu_top integer NOT NULL,

id_page integer NOT NULL,

/* Keys */

CONSTRAINT main_page_pkey

PRIMARY KEY (id_menu_top),

/* Foreign keys */

CONSTRAINT main_page_id_menu_top_fkey

FOREIGN KEY (id_menu_top)

REFERENCES public.menu_top(id_menu_top)

ON DELETE CASCADE

ON UPDATE CASCADE,

CONSTRAINT main_page_id_page_fkey

FOREIGN KEY (id_page)

REFERENCES public.page(id_page)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

ALTER TABLE public.main_page

OWNER TO postgres;

CREATE TABLE public.menu (

id_menu serial NOT NULL,

id_sub_menu integer,

id_page integer,

menu_name varchar NOT NULL,

menu_alt text,

menu_url text,

/* Keys */

CONSTRAINT menu_pkey

PRIMARY KEY (id_menu),

/* Checks */

CONSTRAINT menu_menu_name_check

CHECK ((menu_name)::text <> ''::text),

/* Foreign keys */

CONSTRAINT menu_id_sub_menu_fkey

FOREIGN KEY (id_sub_menu)

REFERENCES public.menu(id_menu)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

ALTER TABLE public.menu

OWNER TO postgres;

CREATE TABLE public.menu_top (

id_menu_top serial NOT NULL,

id_sub_menu integer,

id_page integer,

menu_top_name varchar NOT NULL,

menu_alt text,

menu_url text,

menu_top_image text,

/* Keys */

CONSTRAINT menu_top_pkey

PRIMARY KEY (id_menu_top),

/* Checks */

CONSTRAINT menu_top_menu_top_name_check

CHECK ((menu_top_name)::text <> ''::text),

/* Foreign keys */

CONSTRAINT menu_top_id_page_fkey

FOREIGN KEY (id_page)

REFERENCES public.page(id_page)

ON DELETE SET NULL

ON UPDATE CASCADE,

CONSTRAINT menu_top_id_sub_menu_fkey

FOREIGN KEY (id_sub_menu)

REFERENCES public.menu_top(id_menu_top)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

ALTER TABLE public.menu_top

OWNER TO postgres;

CREATE TABLE public.news (

id_news serial NOT NULL,

id_page integer NOT NULL,

date_add date NOT NULL DEFAULT now(),

theme varchar NOT NULL,

text_html text NOT NULL,

/* Keys */

CONSTRAINT news_pkey

PRIMARY KEY (id_news),

/* Checks */

CONSTRAINT news_text_html_check

CHECK (text_html <> ''::text),

CONSTRAINT news_theme_check

CHECK ((theme)::text <> ''::text),

/* Foreign keys */

CONSTRAINT news_id_page_fkey

FOREIGN KEY (id_page)

REFERENCES public.page(id_page)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

CREATE TRIGGER check_news_bit

AFTER INSERT OR DELETE

ON public.news

FOR EACH ROW

EXECUTE PROCEDURE public.trg_check_news_bit();

CREATE TRIGGER news_fts

AFTER INSERT OR UPDATE

ON public.news

FOR EACH ROW

EXECUTE PROCEDURE public.news_fts();

ALTER TABLE public.news

OWNER TO postgres;

CREATE TABLE public.news_fts (

id_news integer NOT NULL,

news_fts tsvector,

/* Keys */

CONSTRAINT news_fts_pkey

PRIMARY KEY (id_news),

/* Foreign keys */

CONSTRAINT news_fts_id_news_fkey

FOREIGN KEY (id_news)

REFERENCES public.news(id_news)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

ALTER TABLE public.news_fts

OWNER TO postgres;

CREATE TABLE public.news_image (

id_news_image serial NOT NULL,

id_news integer NOT NULL,

file text NOT NULL,

sub_title text,

/* Keys */

CONSTRAINT news_image_pkey

PRIMARY KEY (id_news_image),

CONSTRAINT news_image_file_key

UNIQUE (file),

/* Checks */

CONSTRAINT news_image_file_check

CHECK (file <> ''::text),

/* Foreign keys */

CONSTRAINT news_image_id_news_fkey

FOREIGN KEY (id_news)

REFERENCES public.news(id_news)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

ALTER TABLE public.news_image

OWNER TO postgres;

CREATE TABLE public.page (

id_page serial NOT NULL,

page_content bytea,

php_file text,

news_bit bit(1) NOT NULL DEFAULT B'0'::"bit",

block_bit bit(1) DEFAULT B'0'::"bit",

plane_bit bit(1) DEFAULT B'0'::"bit",

quest_bit bit(1) DEFAULT B'0'::"bit",

count_view integer NOT NULL DEFAULT 0,

id_sub_page integer,

page_name text,

/* Keys */

CONSTRAINT page_pkey

PRIMARY KEY (id_page),

/* Checks */

CONSTRAINT page_count_view_check

CHECK (count_view >= 0),

/* Foreign keys */

CONSTRAINT page_sub_page_fkey

FOREIGN KEY (id_sub_page)

REFERENCES public.page(id_page)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

ALTER TABLE public.page

OWNER TO postgres;

CREATE TABLE public.plane (

id_plane serial NOT NULL,

id_page integer NOT NULL,

section integer NOT NULL,

"year" integer NOT NULL,

date_add date NOT NULL,

date_eqv date,

date_end date,

task_text text NOT NULL,

"comment" text,

/* Keys */

CONSTRAINT plane_pkey

PRIMARY KEY (id_plane),

/* Checks */

CONSTRAINT plane_section_check

CHECK ((section >= 1) AND (section <= 4)),

CONSTRAINT plane_task_text_check

CHECK (task_text <> ''::text),

CONSTRAINT plane_year_check

CHECK ((year >= 1990) AND (year <= 2030)),

/* Foreign keys */

CONSTRAINT plane_id_page_fkey

FOREIGN KEY (id_page)

REFERENCES public.page(id_page)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

CREATE TRIGGER check_plane_bit

AFTER INSERT OR DELETE

ON public.plane

FOR EACH ROW

EXECUTE PROCEDURE public.trg_check_plane_bit();

ALTER TABLE public.plane

OWNER TO postgres;

CREATE TABLE public.plane_image (

id_plane_image serial NOT NULL,

id_plane integer NOT NULL,

file text NOT NULL,

/* Keys */

CONSTRAINT plane_image_pkey

PRIMARY KEY (id_plane_image),

CONSTRAINT plane_image_file_key

UNIQUE (file),

/* Checks */

CONSTRAINT plane_image_file_check

CHECK (file <> ''::text),

/* Foreign keys */

CONSTRAINT plane_image_id_plane_fkey

FOREIGN KEY (id_plane)

REFERENCES public.plane(id_plane)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

ALTER TABLE public.plane_image

OWNER TO postgres;

CREATE TABLE public.project (

id_project serial NOT NULL,

project_file text NOT NULL,

project_url text NOT NULL,

project_alt text,

/* Keys */

CONSTRAINT project_pkey

PRIMARY KEY (id_project),

/* Checks */

CONSTRAINT project_project_file_check

CHECK (project_file <> ''::text),

CONSTRAINT project_project_url_check

CHECK (project_url <> ''::text)

) WITH (

OIDS = FALSE

);

ALTER TABLE public.project

OWNER TO postgres;

CREATE TABLE public.quest_answer (

id_quest_answer serial NOT NULL,

id_page integer NOT NULL,

quest_city varchar NOT NULL,

quest_fio varchar NOT NULL,

quest_email varchar,

quest_text text NOT NULL,

quest_date date NOT NULL DEFAULT now(),

answer_text text,

answer_date date,

/* Keys */

CONSTRAINT quest_answer_pkey

PRIMARY KEY (id_quest_answer),

/* Checks */

CONSTRAINT quest_answer_check

CHECK (quest_date <= answer_date),

CONSTRAINT quest_answer_quest_city_check

CHECK ((quest_city)::text <> ''::text),

CONSTRAINT quest_answer_quest_fio_check

CHECK ((quest_fio)::text <> ''::text),

CONSTRAINT quest_answer_quest_text_check

CHECK (quest_text <> ''::text),

/* Foreign keys */

CONSTRAINT quest_answer_id_page_fkey

FOREIGN KEY (id_page)

REFERENCES public.page(id_page)

ON DELETE CASCADE

ON UPDATE CASCADE

) WITH (

OIDS = FALSE

);

ALTER TABLE public.quest_answer

OWNER TO postgres;

CREATE OR REPLACE FUNCTION audio_add(a_title text, a_text text, a_date date, a_file character varying)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO audio (title, "text", audio_file, date_add)

VALUES (a_title, a_text, a_file, a_date);

RETURN currval ('audio_id_audio_seq');

EXCEPTION

WHEN foreign_key_violation THEN RETURN -1;

WHEN not_null_violation THEN RETURN -2;

WHEN unique_violation THEN RETURN -3;

WHEN check_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION audio_add(text, text, date, character varying) OWNER TO postgres;

CREATE OR REPLACE FUNCTION audio_get(lim integer, ofs integer)

RETURNS SETOF audio AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM audio a

ORDER BY a.date_add DESC

LIMIT lim OFFSET ofs

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION audio_get(integer, integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION audio_get_count()

RETURNS integer AS

$BODY$

BEGIN

RETURN (

SELECT COUNT(*)

FROM audio n

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION audio_get_count() OWNER TO postgres;

CREATE OR REPLACE FUNCTION block_add(a_id_page integer, a_id_city integer, a_block_title text, a_block_content text)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO block (id_page, id_city, block_title, block_content)

VALUES (a_id_page, a_id_city, a_block_title, a_block_content);

RETURN currval ('block_id_block_seq');

EXCEPTION

WHEN foreign_key_violation THEN RETURN -1;

WHEN not_null_violation THEN RETURN -2;

WHEN unique_violation THEN RETURN -3;

WHEN check_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION block_add(integer, integer, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION block_delete(d_id_block integer)

RETURNS SETOF block_image AS

$BODY$

BEGIN

RETURN QUERY(

SELECT *

FROM block_image

WHERE id_block = d_id_block

);

DELETE FROM block

WHERE id_block = d_id_block;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION block_delete(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION block_fts_search(_text text)

RETURNS SETOF block AS

$BODY$

BEGIN

RETURN QUERY

SELECT b.*

FROM

block b JOIN block_fts bf

USING (id_block)

WHERE bf.block_fts @@ plainto_tsquery('russian', _text)

ORDER BY

ts_rank_cd (bf.block_fts, plainto_tsquery('russian', _text), 32) DESC;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION block_fts_search(text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION block_get(id_page integer)

RETURNS SETOF block AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM block b

WHERE b.id_page = id_page

ORDER BY b.block_title DESC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION block_get(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION block_get_by_id_block(id_block integer)

RETURNS block AS

$BODY$

DECLARE

ret block%ROWTYPE;

BEGIN

SELECT INTO ret b.*

FROM block b

WHERE b.id_block = id_block;

RETURN ret;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION block_get_by_id_block(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION block_image_add(a_id_block integer, a_file text)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO block_image (id_block, file)

VALUES (a_id_block, a_file);

RETURN currval ('block_image_id_block_image_seq');

EXCEPTION

WHEN foreign_key_violation THEN RETURN -1;

WHEN not_null_violation THEN RETURN -2;

WHEN unique_violation THEN RETURN -3;

WHEN check_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION block_image_add(integer, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION block_image_delete(d_id_block_image integer)

RETURNS block_image AS

$BODY$

DECLARE

ret block_image%ROWTYPE;

BEGIN

SELECT INTO ret bi.*

FROM block_image bi

WHERE bi.id_block_image = d_id_block_image;

DELETE FROM block_image

WHERE id_block_image = d_id_block_image;

RETURN ret;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION block_image_delete(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION block_image_get(id_block integer)

RETURNS SETOF block_image AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM block_image bi

WHERE bi.id_block = id_block

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION block_image_get(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION block_update(u_id_block integer, u_id_city integer, u_block_title text, u_block_content text)

RETURNS integer AS

$BODY$

BEGIN

UPDATE block

SET id_city = u_id_city, block_title = u_block_title, block_content = u_block_content

WHERE id_block = u_id_block;

RETURN 1;

EXCEPTION

WHEN foreign_key_violation THEN RETURN -1;

WHEN not_null_violation THEN RETURN -2;

WHEN unique_violation THEN RETURN -3;

WHEN check_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION block_update(integer, integer, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION city_get()

RETURNS SETOF city AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM city

ORDER BY city_yes DESC, city_name ASC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION city_get() OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_add(file text, a_ru_about text, a_ud_about text)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO gallery (gallery_file, ru_about, ud_about)

VALUES (file, a_ru_about, a_ud_about);

RETURN currval ('gallery_id_gallery_seq');

EXCEPTION

WHEN not_null_violation THEN RETURN -1;

WHEN unique_violation THEN RETURN -2;

WHEN check_violation THEN RETURN -3;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_add(text, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_add(a_id_gallery_album integer, file text, a_ru_about text)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO gallery (id_gallery_album, gallery_file, ru_about)

VALUES (a_id_gallery_album, file, a_ru_about);

RETURN currval ('gallery_id_gallery_seq');

EXCEPTION

WHEN check_violation THEN RETURN -1;

WHEN unique_violation THEN RETURN -2;

WHEN not_null_violation THEN RETURN -3;

WHEN foreign_key_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_add(integer, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_album_add(album_file text, album_text text)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO gallery_album (gallery_album_file, gallery_album_text)

VALUES (album_file, album_text);

RETURN 1;

EXCEPTION

WHEN check_violation THEN RETURN -1;

WHEN unique_violation THEN RETURN -2;

WHEN not_null_violation THEN RETURN -3;

WHEN foreign_key_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_album_add(text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_album_delete(d_id_gallery_album integer)

RETURNS text AS

$BODY$

DECLARE

album_file text;

BEGIN

SELECT INTO album_file ga.gallery_album_file

FROM gallery_album ga

WHERE ga.id_gallery_album = d_id_gallery_album;

DELETE FROM gallery_album

WHERE id_gallery_album = d_id_gallery_album;

RETURN album_file;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_album_delete(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_album_get()

RETURNS SETOF gallery_album AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM gallery_album

ORDER BY gallery_album_date_add DESC, id_gallery_album DESC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION gallery_album_get() OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_album_get_by_id_album(id_gallery_album integer)

RETURNS gallery_album AS

$BODY$

DECLARE

ret gallery_album%ROWTYPE;

BEGIN

SELECT INTO ret ga.*

FROM gallery_album ga

WHERE ga.id_gallery_album = id_gallery_album;

RETURN ret;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_album_get_by_id_album(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_album_update(u_id_gallery_album integer, u_gallery_album_file text, u_gallery_album_text text)

RETURNS text AS

$BODY$

DECLARE

album_file text;

BEGIN

IF u_gallery_album_file IS NOT NULL AND u_gallery_album_file <> ''::text THEN

SELECT INTO album_file ga.gallery_album_file

FROM gallery_album ga

WHERE ga.id_gallery_album = u_id_gallery_album;

UPDATE gallery_album

SET gallery_album_file = u_gallery_album_file, gallery_album_text = u_gallery_album_text

WHERE id_gallery_album = u_id_gallery_album;

RETURN album_file;

ELSE

UPDATE gallery_album

SET gallery_album_text = u_gallery_album_text

WHERE id_gallery_album = u_id_gallery_album;

RETURN '1';

END IF;

EXCEPTION

WHEN check_violation THEN RETURN '-1';

WHEN unique_violation THEN RETURN '-2';

WHEN not_null_violation THEN RETURN '-3';

WHEN foreign_key_violation THEN RETURN '-4';

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_album_update(integer, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_delete(d_id_gallery integer)

RETURNS text AS

$BODY$

DECLARE

name text;

BEGIN

SELECT INTO name g.gallery_file

FROM gallery g

WHERE g.id_gallery = d_id_gallery;

DELETE FROM gallery

WHERE id_gallery = d_id_gallery;

RETURN name;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_delete(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_get(id_gallery_album integer)

RETURNS SETOF gallery AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM gallery g

WHERE g.id_gallery_album = id_gallery_album

ORDER BY g.id_gallery ASC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION gallery_get(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_magu_add(file text, about text)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO gallery_magu (gallery_file, ru_about)

VALUES (file, about);

RETURN currval ('gallery_magu_id_gallery_magu_seq');

EXCEPTION

WHEN not_null_violation THEN RETURN -1;

WHEN unique_violation THEN RETURN -2;

WHEN check_violation THEN RETURN -3;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_magu_add(text, text) OWNER TO fobos;

CREATE OR REPLACE FUNCTION gallery_magu_add(a_id_gallery_magu_album integer, file text, a_ru_about text)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO gallery_magu (id_gallery_magu_album, gallery_file, ru_about)

VALUES (a_id_gallery_magu_album, file, a_ru_about);

RETURN currval ('gallery_magu_id_gallery_magu_seq');

EXCEPTION

WHEN check_violation THEN RETURN -1;

WHEN unique_violation THEN RETURN -2;

WHEN not_null_violation THEN RETURN -3;

WHEN foreign_key_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_magu_add(integer, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_magu_album_add(album_file text, album_text text)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO gallery_magu_album (gallery_magu_album_file, gallery_magu_album_text)

VALUES (album_file, album_text);

RETURN 1;

EXCEPTION

WHEN check_violation THEN RETURN -1;

WHEN unique_violation THEN RETURN -2;

WHEN not_null_violation THEN RETURN -3;

WHEN foreign_key_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_magu_album_add(text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_magu_album_delete(d_id_gallery_magu_album integer)

RETURNS text AS

$BODY$

DECLARE

album_file text;

BEGIN

SELECT INTO album_file ga.gallery_magu_album_file

FROM gallery_magu_album ga

WHERE ga.id_gallery_magu_album = d_id_gallery_magu_album;

DELETE FROM gallery_magu_album

WHERE id_gallery_magu_album = d_id_gallery_magu_album;

RETURN album_file;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_magu_album_delete(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_magu_album_get()

RETURNS SETOF gallery_magu_album AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM gallery_magu_album

ORDER BY gallery_magu_album_date_add DESC , id_gallery_magu_album DESC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION gallery_magu_album_get() OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_magu_album_get_by_id_album(id_gallery_magu_album integer)

RETURNS gallery_album AS

$BODY$

DECLARE

ret gallery_magu_album%ROWTYPE;

BEGIN

SELECT INTO ret ga.*

FROM gallery_magu_album ga

WHERE ga.id_gallery_magu_album = id_gallery_magu_album;

RETURN ret;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_magu_album_get_by_id_album(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_magu_album_update(u_id_gallery_magu_album integer, u_gallery_magu_album_file text, u_gallery_magu_album_text text)

RETURNS text AS

$BODY$

DECLARE

album_file text;

BEGIN

IF u_gallery_magu_album_file IS NOT NULL AND u_gallery_magu_album_file <> ''::text THEN

SELECT INTO album_file ga.gallery_magu_album_file

FROM gallery_magu_album ga

WHERE ga.id_gallery_magu_album = u_id_gallery_magu_album;

UPDATE gallery_magu_album

SET gallery_magu_album_file = u_gallery_magu_album_file, gallery_magu_album_text = u_gallery_magu_album_text

WHERE id_gallery_magu_album = u_id_gallery_magu_album;

RETURN album_file;

ELSE

UPDATE gallery_magu_album

SET gallery_magu_album_text = u_gallery_magu_album_text

WHERE id_gallery_magu_album = u_id_gallery_magu_album;

RETURN '1';

END IF;

EXCEPTION

WHEN check_violation THEN RETURN '-1';

WHEN unique_violation THEN RETURN '-2';

WHEN not_null_violation THEN RETURN '-3';

WHEN foreign_key_violation THEN RETURN '-4';

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_magu_album_update(integer, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_magu_delete(d_id_gallery integer)

RETURNS text AS

$BODY$

DECLARE

name text;

BEGIN

SELECT INTO name g.gallery_file

FROM gallery_magu g

WHERE g.id_gallery_magu = d_id_gallery;

DELETE FROM gallery_magu

WHERE id_gallery_magu = d_id_gallery;

RETURN name;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_magu_delete(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_magu_get(id_gallery_magu_album integer)

RETURNS SETOF gallery AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM gallery_magu g

WHERE g.id_gallery_magu_album = id_gallery_magu_album

ORDER BY g.id_gallery_magu ASC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION gallery_magu_get(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_magu_get()

RETURNS SETOF gallery_magu AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM gallery_magu

ORDER BY id_gallery_magu ASC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION gallery_magu_get() OWNER TO fobos;

CREATE OR REPLACE FUNCTION gallery_magu_update(u_id_gallery integer, u_ru_about text)

RETURNS integer AS

$BODY$

BEGIN

UPDATE gallery_magu

SET ru_about = u_ru_about

WHERE id_gallery_magu = u_id_gallery;

RETURN 1;

EXCEPTION

WHEN not_null_violation THEN RETURN -1;

WHEN check_violation THEN RETURN -2;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_magu_update(integer, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_top_add(a_file text)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO gallery_top (file)

VALUES (a_file);

RETURN currval('gallery_top_id_gallery_top_seq');

EXCEPTION

WHEN not_null_violation THEN RETURN -1;

WHEN unique_violation THEN RETURN -2;

WHEN check_violation THEN RETURN -3;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_top_add(text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_top_delete(d_id_gallery_top integer)

RETURNS text AS

$BODY$

DECLARE

ret text;

BEGIN

SELECT INTO ret gt.file

FROM gallery_top gt

WHERE gt.id_gallery_top = d_id_gallery_top;

DELETE FROM gallery_top

WHERE id_gallery_top = d_id_gallery_top;

RETURN ret;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_top_delete(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_top_get()

RETURNS SETOF gallery_top AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM gallery_top

ORDER BY id_gallery_top ASC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION gallery_top_get() OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_update(u_id_gallery integer, u_ru_about text)

RETURNS integer AS

$BODY$

BEGIN

UPDATE gallery

SET ru_about = u_ru_about

WHERE id_gallery = u_id_gallery;

RETURN 1;

EXCEPTION

WHEN not_null_violation THEN RETURN -1;

WHEN check_violation THEN RETURN -2;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_update(integer, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION gallery_update(u_id_gallery integer, u_ru_about text, u_ud_about text)

RETURNS integer AS

$BODY$

BEGIN

UPDATE gallery

SET ru_about = u_ru_about, ud_about = u_ud_about

WHERE id_gallery = u_id_gallery;

RETURN 1;

EXCEPTION

WHEN not_null_violation THEN RETURN -1;

WHEN check_violation THEN RETURN -2;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION gallery_update(integer, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION journal_add(a_journal_name text, a_count_page integer, a_href text)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO journal (journal_name, count_page, journal_href)

VALUES (a_journal_name, a_count_page, a_href);

RETURN currval('journal_id_journal_seq');

EXCEPTION

WHEN check_violation THEN RETURN -1;

WHEN unique_violation THEN RETURN -2;

WHEN not_null_violation THEN RETURN -3;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION journal_add(text, integer, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION journal_delete(id_journal integer)

RETURNS journal AS

$BODY$

DECLARE

ret journal;

BEGIN

SELECT INTO ret j.*

FROM journal j

WHERE j.id_journal = id_journal;

DELETE FROM journal j

WHERE j.id_journal = id_journal;

RETURN ret;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION journal_delete(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION journal_get()

RETURNS SETOF journal AS

$BODY$

BEGIN

RETURN QUERY(

SELECT *

FROM journal

ORDER BY date_add DESC, id_journal DESC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION journal_get() OWNER TO postgres;

CREATE OR REPLACE FUNCTION link_add(a_link_image text, a_link_text text, a_link_url text)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO link (link_text, link_url, link_image)

VALUES (a_link_text, a_link_url, a_link_image);

RETURN currval ('link_id_link_seq');

EXCEPTION

WHEN foreign_key_violation THEN RETURN -1;

WHEN not_null_violation THEN RETURN -2;

WHEN unique_violation THEN RETURN -3;

WHEN check_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION link_add(text, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION link_delete(d_id_link integer)

RETURNS link AS

$BODY$

DECLARE

ret link%ROWTYPE;

BEGIN

SELECT INTO ret l.*

FROM link l

WHERE l.id_link = d_id_link;

DELETE FROM link

WHERE id_link = d_id_link;

RETURN ret;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION link_delete(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION link_get()

RETURNS SETOF link AS

$BODY$

BEGIN

RETURN QUERY(

(SELECT *

FROM link

WHERE

link_image IS NOT NULL OR

link_image <> ''

ORDER BY id_link ASC)

UNION ALL

(SELECT *

FROM link

WHERE

link_image IS NULL OR

link_image = ''

ORDER BY id_link ASC)

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION link_get() OWNER TO postgres;

CREATE OR REPLACE FUNCTION link_update(u_id_link integer, u_link_text text, u_link_url text)

RETURNS integer AS

$BODY$

BEGIN

UPDATE link

SET link_text = u_link_text, link_url = u_link_url

WHERE id_link = u_id_link;

RETURN 1;

EXCEPTION

WHEN foreign_key_violation THEN RETURN -1;

WHEN not_null_violation THEN RETURN -2;

WHEN unique_violation THEN RETURN -3;

WHEN check_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION link_update(integer, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION main_page_add(_id_menu_top integer, _id_page integer)

RETURNS integer AS

$BODY$

BEGIN

INSERT INTO main_page

VALUES (_id_menu_top, _id_page);

RETURN 1;

EXCEPTION

WHEN unique_violation THEN

UPDATE main_page

SET id_page = _id_page

WHERE id_menu_top = _id_menu_top;

IF FOUND THEN

RETURN 1;

END IF;

RETURN 0;

WHEN not_null_violation THEN RETURN -3;

WHEN foreign_key_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION main_page_add(integer, integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION main_page_delete(_id_menu_top integer)

RETURNS integer AS

$BODY$

BEGIN

DELETE FROM main_page

WHERE id_menu_top = _id_menu_top;

IF FOUND THEN

RETURN 1;

END IF;

RETURN 0;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION main_page_delete(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION menu_add(a_menu_name character varying, a_menu_alt text, a_menu_url text)

RETURNS integer AS

$BODY$

DECLARE

t_page_id integer;

BEGIN

t_page_id:=page_add();

INSERT INTO menu (id_page,menu_name,menu_alt,menu_url)

VALUES (t_page_id,a_menu_name,a_menu_alt,a_menu_url);

RETURN t_page_id;

EXCEPTION

WHEN foreign_key_violation THEN RETURN -1;

WHEN not_null_violation THEN RETURN -2;

WHEN unique_violation THEN RETURN -3;

WHEN check_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION menu_add(character varying, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION menu_add(a_id_sub_menu integer, a_menu_name character varying, a_menu_alt text, a_menu_url text)

RETURNS integer AS

$BODY$

DECLARE

t_page_id integer;

BEGIN

t_page_id:=page_add();

INSERT INTO menu (id_sub_menu,id_page,menu_name,menu_alt,menu_url)

VALUES (a_id_sub_menu,t_page_id,a_menu_name,a_menu_alt,a_menu_url);

RETURN t_page_id;

EXCEPTION

WHEN foreign_key_violation THEN RETURN -1;

WHEN not_null_violation THEN RETURN -2;

WHEN unique_violation THEN RETURN -3;

WHEN check_violation THEN RETURN -4;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION menu_add(integer, character varying, text, text) OWNER TO postgres;

CREATE OR REPLACE FUNCTION menu_delete(d_menu_id integer)

RETURNS integer AS

$BODY$

DECLARE

t_page_id integer;

BEGIN

SELECT id_page INTO t_page_id FROM menu WHERE id_menu = d_menu_id;

DELETE FROM menu WHERE id_menu = d_menu_id;

DELETE FROM page WHERE id_page = t_page_id;

RETURN 1;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION menu_delete(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION menu_get()

RETURNS SETOF menu AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM menu

ORDER BY menu_name ASC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION menu_get() OWNER TO postgres;

CREATE OR REPLACE FUNCTION menu_get_id_page(d_menu_id integer)

RETURNS integer AS

$BODY$

DECLARE

t_page_id integer;

t_page_id2 integer;

BEGIN

SELECT INTO t_page_id m.id_page

FROM menu m

WHERE m.id_menu = d_menu_id;

IF t_page_id IS NULL THEN

t_page_id2 := page_add();

UPDATE menu

SET id_page = t_page_id2

WHERE id_menu = d_menu_id;

RETURN t_page_id2;

ELSE

RETURN t_page_id;

END IF;

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100;

ALTER FUNCTION menu_get_id_page(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION menu_get_one_cascade()

RETURNS SETOF menu AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM menu

WHERE id_sub_menu IS NULL

ORDER BY id_sub_menu ASC, menu_name ASC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION menu_get_one_cascade() OWNER TO postgres;

CREATE OR REPLACE FUNCTION menu_get_two_cascade(id_menu integer)

RETURNS SETOF menu AS

$BODY$

BEGIN

RETURN QUERY (

SELECT *

FROM menu m

WHERE

m.id_sub_menu IS NOT NULL AND

m.id_sub_menu = id_menu

ORDER BY m.id_menu ASC, m.menu_name ASC

);

END

$BODY$

LANGUAGE 'plpgsql' VOLATILE

COST 100

ROWS 1000;

ALTER FUNCTION menu_get_two_cascade(integer) OWNER TO postgres;

CREATE OR REPLACE FUNCTION menu_top_add(a_menu_id integer, a_menu_name character varying, a_menu_alt text, a_menu_url text)