From b29f41eec996e40355c3ad130507704e8b2b73d0 Mon Sep 17 00:00:00 2001 From: Kilian Saffran Date: Sun, 18 Aug 2019 19:05:18 +0200 Subject: [PATCH] db updates --- dev/db/diff_db.sh | 6 +- dev/db/dumppg.sh | 9 +- dev/db/juridiglu_db.columns.live.txt | 213 ++++++++++++++++++++++++++ dev/db/juridiglu_db.columns.local.txt | 213 ++++++++++++++++++++++++++ 4 files changed, 437 insertions(+), 4 deletions(-) create mode 100644 dev/db/juridiglu_db.columns.live.txt create mode 100644 dev/db/juridiglu_db.columns.local.txt diff --git a/dev/db/diff_db.sh b/dev/db/diff_db.sh index f764a47..e903d6c 100644 --- a/dev/db/diff_db.sh +++ b/dev/db/diff_db.sh @@ -5,11 +5,13 @@ DBNAME="juridiglu_db" DBUSER="juridiglu_user" export PGPASSWORD="hfdR2C9pK9rQV4cH" -psql -h ${DBHOST} -U ${DBUSER} -w ${DBNAME} -c "select table_name,column_name,data_type from information_schema.columns where table_schema='public' order by table_name, column_name;" > ${CALLDIR}'/'${DBNAME}'.columns.local.txt' +psql -h ${DBHOST} -U ${DBUSER} -w ${DBNAME} -c "select table_name,column_name,data_type from information_schema.columns where table_schema='public' order by table_name, column_name;" | sort > ${CALLDIR}'/'${DBNAME}'.columns.local.txt' DBHOST="sql629.your-server.de" -psql -h ${DBHOST} -U ${DBUSER} -w ${DBNAME} -c "select table_name,column_name,data_type from information_schema.columns where table_schema='public' order by table_name, column_name;" > ${CALLDIR}'/'${DBNAME}'.columns.live.txt' +psql -h ${DBHOST} -U ${DBUSER} -w ${DBNAME} -c "select table_name,column_name,data_type from information_schema.columns where table_schema='public' order by table_name, column_name;" | sort > ${CALLDIR}'/'${DBNAME}'.columns.live.txt' + +diff ${CALLDIR}'/'${DBNAME}'.columns.live.txt' ${CALLDIR}'/'${DBNAME}'.columns.local.txt' diff --git a/dev/db/dumppg.sh b/dev/db/dumppg.sh index 974ad48..c2fd484 100644 --- a/dev/db/dumppg.sh +++ b/dev/db/dumppg.sh @@ -5,8 +5,13 @@ DBNAME="juridiglu_db" DBUSER="juridiglu_user" export PGPASSWORD="hfdR2C9pK9rQV4cH" echo ${CALLDIR} - +echo "dump locals" pg_dump -h ${DBHOST} -U ${DBUSER} -w ${DBNAME} > ${CALLDIR}'/'${DBNAME}'.pg.full.sql' pg_dump -h ${DBHOST} -U ${DBUSER} -w -O -x -s ${DBNAME} > ${CALLDIR}'/'${DBNAME}'.pg.schema.sql' pg_dump -h ${DBHOST} -U ${DBUSER} -w --column-inserts -O -x -a ${DBNAME} > ${CALLDIR}'/'${DBNAME}'.pg.data.sql' -export PGPASSWORD="" \ No newline at end of file +echo "dump live schema" + + + +export PGPASSWORD="" + diff --git a/dev/db/juridiglu_db.columns.live.txt b/dev/db/juridiglu_db.columns.live.txt new file mode 100644 index 0000000..3defe39 --- /dev/null +++ b/dev/db/juridiglu_db.columns.live.txt @@ -0,0 +1,213 @@ + +-------------------+--------------------+----------------------------- +(209 rows) + appaccess | expiration | date + appaccess | id_app | integer + appaccess | id | integer + appaccess | id_user | integer + appaccess | privateenabled | boolean + appaccess | publicenabled | boolean + apps | activated | boolean + apps | app | character varying + apps | description | text + apps | id | bigint + apps | id_usergroup | integer + apps | inittables | text + apps | moderated | boolean + apps | moderatedmsg | text + apps | name | character varying + billjee | activationcode | text + billjeebooking | bookingtime | timestamp without time zone + billjeebooking | id | bigint + billjeebooking | id_billjeeticket | bigint + billjeebooking | id_user | bigint + billjee | currentview | text + billjee | dispo | ARRAY + billjee | id | integer + billjee | id_user | bigint + billjee | invites | json + billjee | lastsearch | json + billjee | msgoffer | text + billjee | msgrequest | text + billjee | senderemail | text + billjee | textdefaultoffer | text + billjee | textdefaultrequest | text + billjeetickets | court | text + billjeetickets | dayperiod | text + billjeetickets | eventdate | date + billjeetickets | eventmsg | text + billjeetickets | eventtype | text + billjeetickets | id | bigint + billjeetickets | id_user | bigint + billjeetickets | invites | json + billjeetickets | isdisabled | boolean + billjeetickets | room | text + billjeetickets | starttime | time without time zone + categories | category | character varying + categories | id | integer + categories | link | character varying + categories | usertype | character varying + clients | email | text + clients | id | integer + clients | id_user | integer + clients | isblocked | boolean + clients | phone | text + clients | prename | text + clients | surname | text + countries | iso2 | text + countries | iso3 | text + countries | langde | text + countries | langen | text + countries | langfr | text + countries | prio | integer + countries | region | text + exceptions | daydate | date + exceptions | dayhours | text + exceptions | id | bigint + exceptions | id_user | bigint + invoiceelements | description | text + invoiceelements | discountpercent | numeric + invoiceelements | id | bigint + invoiceelements | id_invoice | bigint + invoiceelements | id_price | integer + invoiceelements | id_voucher | integer + invoiceelements | quantity | numeric + invoiceelements | unitamount | numeric + invoiceelements | unit | text + invoiceelements | vatpercent | numeric + invoices | address | text + invoices | city | text + invoices | clientnumber | text + invoices | countryshort | text + invoices | email | text + invoices | id_app | integer + invoices | id | bigint + invoices | id_user | bigint + invoices | invoicedata | json + invoices | invoicedate | date + invoices | payedamount | numeric + invoices | payementmethod | text + invoices | payementnote | text + invoices | pdfname | text + invoices | recipient | text + invoices | reference | text + invoices | reminderdate | date + invoices | statusdate | date + invoices | status | text + invoices | sumgrossamount | numeric + invoices | sumnetamount | numeric + invoices | sumvatamount | numeric + invoices | transactiondata | json + invoices | vatpercent | numeric + invoices | voucher | text + invoices | zip | text + lawyercatalog | address | character varying + lawyercatalog | city | character varying + lawyercatalog | company | character varying + lawyercatalog | council | json + lawyercatalog | country | text + lawyercatalog | court | text + lawyercatalog | description | text + lawyercatalog | email | character varying + lawyercatalog | id | bigint + lawyercatalog | id_user | bigint + lawyercatalog | languages | json + lawyercatalog | location_link | text + lawyercatalog | location_url | text + lawyercatalog | logo | text + lawyercatalog | phone | character varying + lawyercatalog | photo | text + lawyercatalog | prename | character varying + lawyercatalog | price | real + lawyercatalog | profile_link | character varying + lawyercatalog | surname | character varying + lawyercatalog | title | text + lawyercatalog | website | text + lawyercatalog | zip | character varying + lawyercategories | id_catalog | bigint + lawyercategories | id_category | integer + lawyercategories | id | integer + lawyercategories | selection | boolean + maillayouts | id | integer + maillayouts | layoutname | text + maillayouts | mailtemplate | text + maillog | command | text + maillog | id | bigint + maillog | logdata | text + maillog | sendtime | timestamp without time zone + mailtemplates | emaildatasql | text + mailtemplates | emailtext | text + mailtemplates | errormsg | text + mailtemplates | id | bigint + mailtemplates | id_maillayout | integer + mailtemplates | mailfrom | text + mailtemplates | mailsubject | text + mailtemplates | successmsg | text + mailtemplates | templatename | text + modulepreferences | appident | text + modulepreferences | id | integer + modulepreferences | id_user | integer + modulepreferences | page | text + modulepreferences | preference | text + prices | duration | integer + prices | evaluation | boolean + prices | id_app | bigint + prices | id | bigint + prices | package | character varying + prices | price | numeric + prices | vatpercent | numeric + rendezvous | client_status | character varying + rendezvous | clientuuid | character varying + rendezvous | id | bigint + rendezvous | id_client | bigint + rendezvous | id_user | bigint + rendezvous | rdvoptions | json + rendezvous | starttimestamp | timestamp without time zone + rendezvous | stoptimestamp | timestamp without time zone + rendezvous | user_status | character varying + rendezvous | useruuid | character varying + reporttemplates | data_de | json + reporttemplates | data_en | json + reporttemplates | data_fr | json + reporttemplates | id | integer + reporttemplates | report | text + reporttemplates | structure | json + sessions | created | timestamp without time zone + sessions | id | bigint + sessions | idsession | character varying + sessions | id_user | bigint + sessions | remote_addr | character varying + sessions | user_agent | character varying + table_name | column_name | data_type + timesheets | enddate | date + timesheets | endtime | time without time zone + timesheets | hourinterval | integer + timesheets | id | bigint + timesheets | id_user | bigint + timesheets | rdvhours | json + timesheets | rdvoption | json + timesheets | startdate | date + timesheets | starttime | time without time zone + userclients | clientblocked | smallint + userclients | id_client | bigint + userclients | id_user | bigint + usergroups | id | integer + usergroups | isdefault | boolean + usergroups | usergroup | text + useringroups | id_group | bigint + useringroups | id_user | bigint + users | blocked | boolean + users | confirmkey | character varying + users | created | timestamp without time zone + users | id | bigint + users | id_usergroup | integer + users | prename | character varying + users | surname | character varying + users | username | text + users | userpassword | character varying + vouchers | expiration | date + vouchers | id | integer + vouchers | id_price | bigint + vouchers | percentdiscount | real + vouchers | used | boolean + vouchers | voucher | text diff --git a/dev/db/juridiglu_db.columns.local.txt b/dev/db/juridiglu_db.columns.local.txt new file mode 100644 index 0000000..3defe39 --- /dev/null +++ b/dev/db/juridiglu_db.columns.local.txt @@ -0,0 +1,213 @@ + +-------------------+--------------------+----------------------------- +(209 rows) + appaccess | expiration | date + appaccess | id_app | integer + appaccess | id | integer + appaccess | id_user | integer + appaccess | privateenabled | boolean + appaccess | publicenabled | boolean + apps | activated | boolean + apps | app | character varying + apps | description | text + apps | id | bigint + apps | id_usergroup | integer + apps | inittables | text + apps | moderated | boolean + apps | moderatedmsg | text + apps | name | character varying + billjee | activationcode | text + billjeebooking | bookingtime | timestamp without time zone + billjeebooking | id | bigint + billjeebooking | id_billjeeticket | bigint + billjeebooking | id_user | bigint + billjee | currentview | text + billjee | dispo | ARRAY + billjee | id | integer + billjee | id_user | bigint + billjee | invites | json + billjee | lastsearch | json + billjee | msgoffer | text + billjee | msgrequest | text + billjee | senderemail | text + billjee | textdefaultoffer | text + billjee | textdefaultrequest | text + billjeetickets | court | text + billjeetickets | dayperiod | text + billjeetickets | eventdate | date + billjeetickets | eventmsg | text + billjeetickets | eventtype | text + billjeetickets | id | bigint + billjeetickets | id_user | bigint + billjeetickets | invites | json + billjeetickets | isdisabled | boolean + billjeetickets | room | text + billjeetickets | starttime | time without time zone + categories | category | character varying + categories | id | integer + categories | link | character varying + categories | usertype | character varying + clients | email | text + clients | id | integer + clients | id_user | integer + clients | isblocked | boolean + clients | phone | text + clients | prename | text + clients | surname | text + countries | iso2 | text + countries | iso3 | text + countries | langde | text + countries | langen | text + countries | langfr | text + countries | prio | integer + countries | region | text + exceptions | daydate | date + exceptions | dayhours | text + exceptions | id | bigint + exceptions | id_user | bigint + invoiceelements | description | text + invoiceelements | discountpercent | numeric + invoiceelements | id | bigint + invoiceelements | id_invoice | bigint + invoiceelements | id_price | integer + invoiceelements | id_voucher | integer + invoiceelements | quantity | numeric + invoiceelements | unitamount | numeric + invoiceelements | unit | text + invoiceelements | vatpercent | numeric + invoices | address | text + invoices | city | text + invoices | clientnumber | text + invoices | countryshort | text + invoices | email | text + invoices | id_app | integer + invoices | id | bigint + invoices | id_user | bigint + invoices | invoicedata | json + invoices | invoicedate | date + invoices | payedamount | numeric + invoices | payementmethod | text + invoices | payementnote | text + invoices | pdfname | text + invoices | recipient | text + invoices | reference | text + invoices | reminderdate | date + invoices | statusdate | date + invoices | status | text + invoices | sumgrossamount | numeric + invoices | sumnetamount | numeric + invoices | sumvatamount | numeric + invoices | transactiondata | json + invoices | vatpercent | numeric + invoices | voucher | text + invoices | zip | text + lawyercatalog | address | character varying + lawyercatalog | city | character varying + lawyercatalog | company | character varying + lawyercatalog | council | json + lawyercatalog | country | text + lawyercatalog | court | text + lawyercatalog | description | text + lawyercatalog | email | character varying + lawyercatalog | id | bigint + lawyercatalog | id_user | bigint + lawyercatalog | languages | json + lawyercatalog | location_link | text + lawyercatalog | location_url | text + lawyercatalog | logo | text + lawyercatalog | phone | character varying + lawyercatalog | photo | text + lawyercatalog | prename | character varying + lawyercatalog | price | real + lawyercatalog | profile_link | character varying + lawyercatalog | surname | character varying + lawyercatalog | title | text + lawyercatalog | website | text + lawyercatalog | zip | character varying + lawyercategories | id_catalog | bigint + lawyercategories | id_category | integer + lawyercategories | id | integer + lawyercategories | selection | boolean + maillayouts | id | integer + maillayouts | layoutname | text + maillayouts | mailtemplate | text + maillog | command | text + maillog | id | bigint + maillog | logdata | text + maillog | sendtime | timestamp without time zone + mailtemplates | emaildatasql | text + mailtemplates | emailtext | text + mailtemplates | errormsg | text + mailtemplates | id | bigint + mailtemplates | id_maillayout | integer + mailtemplates | mailfrom | text + mailtemplates | mailsubject | text + mailtemplates | successmsg | text + mailtemplates | templatename | text + modulepreferences | appident | text + modulepreferences | id | integer + modulepreferences | id_user | integer + modulepreferences | page | text + modulepreferences | preference | text + prices | duration | integer + prices | evaluation | boolean + prices | id_app | bigint + prices | id | bigint + prices | package | character varying + prices | price | numeric + prices | vatpercent | numeric + rendezvous | client_status | character varying + rendezvous | clientuuid | character varying + rendezvous | id | bigint + rendezvous | id_client | bigint + rendezvous | id_user | bigint + rendezvous | rdvoptions | json + rendezvous | starttimestamp | timestamp without time zone + rendezvous | stoptimestamp | timestamp without time zone + rendezvous | user_status | character varying + rendezvous | useruuid | character varying + reporttemplates | data_de | json + reporttemplates | data_en | json + reporttemplates | data_fr | json + reporttemplates | id | integer + reporttemplates | report | text + reporttemplates | structure | json + sessions | created | timestamp without time zone + sessions | id | bigint + sessions | idsession | character varying + sessions | id_user | bigint + sessions | remote_addr | character varying + sessions | user_agent | character varying + table_name | column_name | data_type + timesheets | enddate | date + timesheets | endtime | time without time zone + timesheets | hourinterval | integer + timesheets | id | bigint + timesheets | id_user | bigint + timesheets | rdvhours | json + timesheets | rdvoption | json + timesheets | startdate | date + timesheets | starttime | time without time zone + userclients | clientblocked | smallint + userclients | id_client | bigint + userclients | id_user | bigint + usergroups | id | integer + usergroups | isdefault | boolean + usergroups | usergroup | text + useringroups | id_group | bigint + useringroups | id_user | bigint + users | blocked | boolean + users | confirmkey | character varying + users | created | timestamp without time zone + users | id | bigint + users | id_usergroup | integer + users | prename | character varying + users | surname | character varying + users | username | text + users | userpassword | character varying + vouchers | expiration | date + vouchers | id | integer + vouchers | id_price | bigint + vouchers | percentdiscount | real + vouchers | used | boolean + vouchers | voucher | text -- 2.39.5