پایگاه داده PostgresSQL

از ویکی پارس پویش
(تفاوت بین نسخه‌ها)
پرش به: ناوبری, جستجو
 
(۸۶ ویرایش میانی توسط ۲ کاربر نشان داده نشده‌است)
سطر ۱: سطر ۱:
−
[[پیکربندی دیتابیس]]
+
 
  
 
<font face ="Tahoma">  
 
<font face ="Tahoma">  
سطر ۵: سطر ۵:
 
<div dir=rtl lang=fa>
 
<div dir=rtl lang=fa>
  
−
[[پرونده:PostgreSQL.pdf]]
+
= DataBase Schema : =
 +
 
 +
DataBase مورد استفاده در شرکت پارس پویش  Postgres بوده که از نوع DataBase های  SQL Base میباشد. Schema ارتباطات میان table های اصلی در شکل زیر نشان داده شده است . در ادامه نیز به معرفی table های اصلی میپردازیم :
 +
 
 +
 
 +
[[پرونده:Db11.png|center]]
 +
 
 +
 
 +
همانطور که در شکل میبینیم PK ها یا همان Primary Key ها کلید های unique در هر Table هستند و برای ارتباط با table های مجاور به کار میروند . این کلید در table مجاور حکم FK یا همان Foreign Key را دارد . به عنوان مثال ستون charge_id در Table  charge  یک Primary Key به حساب می آید و در table charge_rules یک Foreign Key محسوب میشود.
 +
 
 +
 
 +
= Main Tables of IBSng DataBase : =
 +
 
 +
table های اصلی مربوط به IBSng Database به شرح زیر میباشد :
  
−
'''دستورات دیتابیس مربوط به IBSng''' :
+
* users
 +
* normal_users
 +
* user_attrs
 +
* charges
 +
* charge_rules
 +
* charge_rule_attrs
 +
* groups
 +
* group_attrs
 +
* ras
  
−
نصب دیتابیس Postgresql:
+
'''table users:'''
  
 +
این table شامل ستون های زیر می باشد
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
user_id | isp_id |  credit  | deposit | status | group_id |  creation_date  | deposit_recharge | nearest_exp_date
 +
</div>
 +
ستون user_id مربوط به id کاربر میباشد که unique است و برای ارتباط با table های دیگر استفاده میشود و در اصطلاح sql این فیلد primary key  است.
  
−
apt-get install postgresql-9.4
+
ستون isp_id مربوط به id ای-اس-پی میباشد که unique است .
  
−
اضافه کردن کاربر به دیتابیس:
+
ستون credit میزان credit مصرفی کاربر را نمایش میدهد .
  
 +
ستون deposit میزان حساب ذخیره کاربر را نمایش میدهد .
 +
 +
ستون status وضعیت charging  را نشان میدهد .
 +
 +
ستون group_id مربوط به id گروهی است که کاربر عضو آن است . هر کابر میتواند تنها عضو یک گروه باشد.
 +
 +
ستون creation_date زمان ساخت کاربر را نمایش میدهد .
 +
 +
ستون deposit_recharge میزان حساب ذخیره کاربر برای ترافیک مازاد را نمایش میدهد .
 +
 +
ستون nearest_exp_date تاریخ انقضای کاربر را نمایش میدهد .
 +
 +
 +
'''table normal_users:'''
 +
 +
این table شامل ستون های زیر می باشد
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
user_id |  normal_username  |  normal_password  | second_normal_username
 +
</div>
  
 +
ستون user_id مربوط به id کاربر میباشد که unique است و برای ارتباط با table های دیگر استفاده میشود و در اصطلاح sql این فیلد primary key  است.
  
−
su - postgres
+
ستون normal_username نام کاربری کاربر را نمایش میدهد.
−
createuser -s ibs
+
  
−
اضافه کردن دیتابیس:
+
ستون normal_password گذرواژه کاربر را نمایش میدهد.
 +
 
 +
'''table user_attrs:'''
 +
 
 +
این table شامل ستون های زیر می باشد
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
user_id | attr_name |  attr_value                                                                                                                                                                     
 +
</div>
 +
 +
ستون user_id مربوط به id کاربر میباشد که unique است و برای ارتباط با table های دیگر استفاده میشود و در اصطلاح sql این فیلد primary key  است.
 +
 +
ستون attr_name همان attribute مربوط به کاربر  را نمایش میدهد.
  
−
su - postgres
+
ستون attr_value مقدار مربوط به هر attr_name را نمایش میدهد.
−
createdb IBSng
+
  
−
Drop کردن کاربر و یا دیتابیس:
+
'''table charges:'''
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
charge_id | charge_name  |  comment  | isp_id
 +
</div>
  
−
su - postgres
+
ستون charge_id مربوط به id هر شارژ میباشد که unique است و برای ارتباط با table های دیگر استفاده میشود و در اصطلاح sql این فیلد primary key  است.
−
dropdb IBSng
+
−
dropuser ibs
+
  
−
وارد شدن به دیتابیس از طریق خط فرمان:
+
ستون charge_name نام مربوط به هر charge_id را نمایش میدهد
 +
 
 +
ستون comment توضیحات مربوط به هر charge_id را نمایش میدهد
 +
 
 +
ستون isp_id مربوط به id ای-اس-پی میباشد که unique است .
 +
 
 +
'''table charge_rules:'''
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
charge_rule_id | charge_rule_description | charge_rule_priority | charge_id
 +
</div>
  
−
psql -U ibs IBSng
+
ستون charge_rule_id مربوط به id هر charge_rule میباشد که unique است و  و در اصطلاح sql این فیلد primary key  است.
  
−
بکاپ گرفتن از دیتابیس بصورت کامل:
+
ستون charge_rule_description نام مربوط به هر charge_rule_id را نمایش میدهد
 +
 
 +
ستون charge_rule_priority الویت هر charge_rule_id را نمایش میدهد
 +
 
 +
ستون charge_id مربوط به id هر شارژ میباشد که unique است.هر شارژ یا همان charge_id میتواند چندین charge_rule داشته باشد.
 +
 
 +
'''table charge_rule_attrs:'''
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
charge_rule_id |  attr_name  |  attr_value
 +
</div>
 +
 +
ستون charge_rule_id مربوط به id هر charge_rule میباشد که unique است و  و در اصطلاح sql این فیلد primary key  است.
 +
 +
ستون attr_name همان attribute مربوط به charge_rule_id  را نمایش میدهد.
  
−
pg_dump -Fc -U ibs IBSng > IBSng_dump
+
ستون attr_value مقدار مربوط به هر attr_name را نمایش میدهد.
  
−
بازگرداندن بکاپ:
+
'''table groups:'''
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
group_id |  group_name  | isp_id |    comment  | status
 +
</div>
  
−
pg_restore -Fc -j CPU_CORES -U ibs -d IBSng IBSng_dump
+
ستون group_id مربوط به id گروه  میباشد که unique است و برای ارتباط با table های دیگر استفاده میشود و در اصطلاح sql این فیلد primary key  است.هر گروه تنها میتواند عضو یک شارژ (charge_id ) باشد.
 +
ستون group_name نام هر گروه را نمایش میدهد.
  
−
مشاهده لیست Object های یک فایل بکاپ:
+
ستون isp_id مربوط به id ای-اس-پی میباشد که unique است .
 +
 
 +
ستون comment توضیحات مربوط به هر group_id را نمایش میدهد.
 +
 
 +
ستون status وضعیت هر group را نمایش میدهد.
 +
 
 +
'''table group_attrs:'''
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
group_id | attr_name  |  attr_value
 +
</div>
  
−
pg_restore -Fc -l IBSng_dump | less
+
ستون group_id مربوط به id گروه  میباشد که unique است
  
−
بازگرداندن فقط جداولی که نیاز است:
+
ستون attr_name همان attribute مربوط به گروه  را نمایش میدهد.
 +
 
 +
ستون attr_value مقدار مربوط به هر attr_name را نمایش میدهد.
 +
 
 +
 
 +
'''table ras:'''
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
ras_id |  ras_description  |  ras_ip  |  ras_type | radius_secret | active |  comment 
 +
</div>
 +
 +
ستون ras_id مربوط به id هر ras  میباشد که unique است
 +
 +
ستون ras_description نام ras را نمایش میدهد.
 +
 +
ستون ras_ip نشان دهنده ip هر ras L میباشد.
 +
 +
ستون ras_type نمایش دهنده نوع ras مربوطه میباشد
 +
 +
ستون radius_secret پسورد ras مربوطه را نشان میدهد
 +
 +
ستون active وضعیت فعال یا غیر فعال بودن ras مربوطه را نشان میدهد
 +
 +
ستون comment هم توضیحات احتمالی مربوط به هر ras است
 +
 +
 +
= SQL Query : =
 +
 +
query های کلی برروی postgresql  به چند دسته تقسیم میشوند :
  
−
pg_restore -Fc -j CPU_CORES -U ibs -d IBSng -L RESTORE.txt IBSng_dump
+
*: <big>select</big> : این دستور برای دیدن محتویات یک table استفاده میشود . برای مثال :
  
−
دیدن لیست دیتابیس های سیستم:
 
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
select * from users;
 +
</div>
 +
 +
خروجی این دستور تمامی ستون های table users را نمایش میدهد. همچنین برای مشاهده تحویات یک ستون خاص از دستور زیر استفاده میکنیم :
 +
<div dir=ltr lang=en>
 +
select user_id from users;
 +
</div>
 +
 +
*: <big>update</big> : این دستور برای update محتویات یک table استفاده میشود . برای مثال :
 +
<div dir=ltr lang=en>
 +
update users set credit = '1000' where user_id = '2';
 +
</div>
  
−
\l+
+
خروجی این دستور credit مربوط به user با id = 2 را update میکند و مقدارش را به 1000 تغییر میدهد .
 +
 
 +
 
 +
 
 +
*: <big>insert</big> : این دستور برای update محتویات یک table استفاده میشود . برای مثال :
  
−
دیدن لیست جداول دیتابیس IBSng:
 
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
insert into charges values  (3000,'test1','hh',0);
 +
</div>
  
−
\d+
+
خروجی این دستور یک ردیف به table charge با مقادیر زیراضافه میکند :charge_id = 3000 - charge_name = test1 - comment=hh isp_id=0
 +
 
 +
 
 +
 
 +
*: <big>delete</big> : این دستور برای delete محتویات یک table استفاده میشود . برای مثال :
  
−
مثال
 
−
مشاهده راس های ثبت شده:
 
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
delete from users  where user_id = '2';
 +
</div>
 +
 +
خروجی این دستور ردیف مربوط به user با id = 2 را delete میکند .
 +
 +
  
−
select * from ras ;
+
*: <big>alter</big> : این دستور برای تفییر ستون های یک table استفاده میشود.این تغییرات شامل افزودن کم کردن و یا تغییرنام میباشد . برای مثال :
  
−
مشاهده کاربران:
 
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
 +
alter table users add  culumn1 text;
  
−
select * from normal_users ;
+
</div>
 +
 
 +
خروجی این دستور یک ستون با نام text به table user اضافه میکند .
 +
 
 +
 
 +
 
 +
= IBSng DataBase : =
 +
 
 +
 
 +
'''نصب دیتابیس Postgresql:'''
  
−
مشاهده لیست ادمین ها:
 
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
  
−
select * from admin ;
+
apt-get install postgresql-9.4
 +
</div>
 +
 
 +
'''اضافه کردن کاربر به دیتابیس:'''
  
−
بکاپ گرفتن از یک جدول در خود دیتابیس:
 
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
  
−
select * into backup_TABLE from TABLE ;
+
su - postgres
 +
createuser -s ibs
 +
</div>
  
−
بکاپ گرفتن از یک جدول در فایل CSV:
+
'''اضافه کردن دیتابیس:'''
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
  
−
copy TABLE into ‘/tmp/FILE.csv’ csv header ;
+
su - postgres
 +
createdb IBSng
 +
</div>
  
 +
'''Drop کردن کاربر و یا دیتابیس:'''
 +
<div dir=ltr lang=en>
 +
 +
su - postgres
 +
dropdb IBSng
 +
dropuser ibs
 +
</div>
 +
 +
'''وارد شدن به دیتابیس از طریق خط فرمان:'''
 +
<div dir=ltr lang=en>
 +
 +
psql -U ibs IBSng
 +
</div>
 +
 +
برای دسترسی دادن به یک دیتابیس  یک خط به صورت زیر در این فایل اضافه میکنیم:
 +
<div dir=ltr lang=en>
 +
 +
nano /etc/postgresql/9.4/main/pg_hba.conf
 +
 +
local IBSng ibs trust
 +
</div>
 +
 +
'''وارد شدن به دیتابیس remote از طریق خط فرمان:'''
 +
<div dir=ltr lang=en>
 +
 +
psql -U ibs IBSng -h <ipaddress>
 +
</div>
 +
 +
'''بکاپ گرفتن از دیتابیس بصورت کامل:'''
 +
<div dir=ltr lang=en>
 +
 +
pg_dump -Fc -U ibs IBSng > IBSng_dump
 +
</div>
 +
 +
'''بازگرداندن بکاپ:'''
 +
<div dir=ltr lang=en>
 +
 +
pg_restore -Fc -j CPU_CORES -U ibs -d IBSng IBSng_dump
 +
</div>
 +
 +
'''مشاهده لیست Object های یک فایل بکاپ:'''
 +
<div dir=ltr lang=en>
 +
 +
pg_restore -Fc -l IBSng_dump | less
 +
</div>
 +
 +
'''بازگرداندن فقط جداولی که نیاز است:'''
 +
<div dir=ltr lang=en>
 +
 +
pg_restore -Fc -j CPU_CORES -U ibs -d IBSng -L RESTORE.txt IBSng_dump
 +
</div>
 +
 +
'''دیدن لیست دیتابیس های سیستم:'''
 +
<div dir=ltr lang=en>
 +
 +
\l+
 +
</div>
 +
 +
'''دیدن لیست جداول دیتابیس IBSng:'''
 +
<div dir=ltr lang=en>
 +
 +
\d+
 +
</div>
 +
 +
'''مشاهده راس های ثبت شده:'''
 +
<div dir=ltr lang=en>
 +
 +
select * from ras ;
 +
</div>
 +
 +
'''disable کردن یک ras (با id =1 ):'''
 +
 +
<div dir=ltr lang=en>
 +
update ras set active = 'f' where ras_id = 1;
 +
</div>
 +
 +
'''مشاهده کاربران:'''
 +
<div dir=ltr lang=en>
 +
 +
select * from normal_users ;
 +
</div>
 +
 +
'''مشاهده لیست ادمین ها:'''
 +
<div dir=ltr lang=en>
 +
 +
select * from admin ;
 +
</div>
 +
 +
'''بکاپ گرفتن از یک جدول در خود دیتابیس:'''
 +
<div dir=ltr lang=en>
 +
 +
select * into backup_TABLE from TABLE ;
 +
</div>
 +
 +
'''بکاپ گرفتن از یک جدول در فایل CSV:'''
 +
<div dir=ltr lang=en>
 +
 +
copy TABLE into ‘/tmp/FILE.csv’ csv header ;
 +
</div>
 +
 +
'''انتقال خروجی یک دستور به فایل CSV:'''
 +
<div dir=ltr lang=en>
 +
 +
copy  (select * from users) to ‘/tmp/FILE.csv’ CSV HEADER ;
 +
</div>
 +
 +
'''انتقال خروجی یک فایل CSV و تبدیلش به table :'''
 +
 +
بدین منظور میبایست ابتدا table ی در DataBase به تعداد ستون های فایل csv مربوط بسازیم :
 +
<div dir=ltr lang=en>
 +
CREATE TABLE new_table(username text, password text)  ;
 +
</div>
 +
 +
سپس فایل csv را در درون تیبل ایجاد شده میریزیم:
 +
<div dir=ltr lang=en>
 +
COPY new_table FROM '/tmp/user-pass.csv' CSV ;
 +
</div>
 +
 +
'''remote access:'''
 
برای دسترسی دادن به یک دیتابیس از یک IP خاص یک خط به صورت زیر در این فایل اضافه میکنیم:
 
برای دسترسی دادن به یک دیتابیس از یک IP خاص یک خط به صورت زیر در این فایل اضافه میکنیم:
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
  
−
nano /etc/postgresql/9.4/main/pg_hba.conf
+
nano /etc/postgresql/9.4/main/pg_hba.conf
  
−
host DatabaseName Username IPMask AuthenticaionMethod
+
host DatabaseName Username IPMask AuthenticaionMethod
−
e.g: host template1 postgres 192.168.1.2/32 md5
+
e.g: host template1 postgres 192.168.1.2/32 md5
 +
</div>
  
 
و پس از آن دیتابیس رو Reload میکنیم:
 
و پس از آن دیتابیس رو Reload میکنیم:
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
  
−
/etc/init.d/postgresql reload
+
/etc/init.d/postgresql reload
 +
</div>
  
−
مشاهده Session های فعال و کاربران متصل به دیتابیس:
+
'''مشاهده Session های فعال و کاربران متصل به دیتابیس:'''
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
  
−
select * from pg_stat_activity;
+
select * from pg_stat_activity;
 +
</div>
  
−
Kill کردن یک پروسه از دیتابیس‌ (توصیه نمیشود):
+
'''Kill کردن یک پروسه از دیتابیس‌ (توصیه نمیشود):'''
 
<div dir=ltr lang=en>
 
<div dir=ltr lang=en>
  
−
select pg_cancel_backend(PID);
+
select pg_cancel_backend(PID);
 +
</div>
  
 
توجه فرمایید که در هر مرحله میتوانید با زدن کلید TAB دستور را تکمیل نمایید.
 
توجه فرمایید که در هر مرحله میتوانید با زدن کلید TAB دستور را تکمیل نمایید.
 
این دستورات اصلی برای شروع کار با دیتابیس است که قطعا بسته به کاری که نیاز دارید انجام دهید دستورات فرق میکنند.
 
این دستورات اصلی برای شروع کار با دیتابیس است که قطعا بسته به کاری که نیاز دارید انجام دهید دستورات فرق میکنند.
 +
 +
 +
شکل زیر چگونگی ارتباط میان charge ها charge-rule ها attr_name ها و attr_value ها را نمایش میدهد :
 +
 +
 +
[[پرونده:Db1.png|center]]
 +
 +
 +
همانطور که در شکل پیداست شارژ Hamedan-POSTPAID-UNLIMIT-64-FIX-N با ای-اس-پی hamedan دارای تعدادی charge_rule و attr_name است .
 +
 +
 +
برای بررسی دقیقتر table مربوط به charge_rule_attrs برای دو  charge_rule_id  بررسی میکنیم :
 +
 +
 +
[[پرونده:Db6.png|center]]
 +
 +
 +
همانطور که در شکل پیداست charge_rule_id 9952 مربوط به کاربران failed است که در شکل قبل هم مشخص است .
 +
 +
 +
همچنین charge_rule_id 10936 مربوط به سرویس master است ( attr_name = service و attr_value = Master:Master::1:1 )  که این مورد در شکل زیر به وضوح مشخص است :
 +
 +
 +
 +
[[پرونده:Db4.png|center]]
 +
 +
 +
در ادامه به توضیح مقادیر state در charge_rule_attrs میپردازیم . برای هریک از حالات موجود در attr_name مربوط به state یک عدد در DataBase
 +
مشخص شده است :
 +
 +
<div dir=ltr lang=en>
 +
Negative Credit: Yes  .......... 1
 +
Package: Yes .......... 2
 +
Recharged: Yes .......... 4
 +
Temporary Extended: Yes .......... 8
 +
VoIP Outgoing: Yes .......... 16
 +
Failed Login: Yes .......... 32
 +
IDLE Login: Yes .......... 64
 +
 +
</div>
 +
 +
همانطور که مشخص است برای قعال کردن دو value به صورت همزمان مقدار مربوطه از جمع دو مقدار حاصل میشود . برای مثال با فعال کردن گزینه های Package و Recharged عدد ثبت شده در DataBase عدد 6 میباشد.
 +
 +
به عنوان مثال دستور زیر charge_rule_id هایی که دارای Negative Credit فعال میباشند را نمایش میدهد :
 +
<div dir=ltr lang=en>
 +
select charge_rule_id from charge_rule_attrs where attr_name = 'state' and mod (attr_value::integer, 2) = '1'
 +
 +
</div>
 +
همچنین مقادیر مربوط به دیگر attr ها در دو شکل زیر برای charge_rule 974 مشخص شده است :
 +
 +
[[پرونده:Db7.png|center]]
 +
 +
مقادیرcpmb_upload , hours_interval ,  و service به ترتیب در web panel نام های زیر را دارا هستند:
 +
<div dir=ltr lang=en>
 +
Charge Per MegaByte of Upload ...... cpmb_download
 +
Hours ...... hours_interval
 +
Charge Per MegaByte of Download ...... cpmb_upload
 +
service ...... Sub Service
 +
 +
</div>
 +
 +
همچنین مقادیر Auto Start و  Auto Check در attr_name مربوط به service به شرح ذیل میباشد :
 +
 +
<div dir=ltr lang=en>
 +
1 .... Auto Start: Yes
 +
1 .... Auto Check: Yes
 +
 +
</div>
 +
 +
[[پرونده:Db8.png|center]]

نسخهٔ کنونی تا ‏۲۷ اوت ۲۰۱۷، ساعت ۱۲:۳۷


محتویات

[ویرایش] DataBase Schema :

DataBase مورد استفاده در شرکت پارس پویش Postgres بوده که از نوع DataBase های SQL Base میباشد. Schema ارتباطات میان table های اصلی در شکل زیر نشان داده شده است . در ادامه نیز به معرفی table های اصلی میپردازیم :


Db11.png


همانطور که در شکل میبینیم PK ها یا همان Primary Key ها کلید های unique در هر Table هستند و برای ارتباط با table های مجاور به کار میروند . این کلید در table مجاور حکم FK یا همان Foreign Key را دارد . به عنوان مثال ستون charge_id در Table charge یک Primary Key به حساب می آید و در table charge_rules یک Foreign Key محسوب میشود.


[ویرایش] Main Tables of IBSng DataBase :

table های اصلی مربوط به IBSng Database به شرح زیر میباشد :

  • users
  • normal_users
  • user_attrs
  • charges
  • charge_rules
  • charge_rule_attrs
  • groups
  • group_attrs
  • ras

table users:

این table شامل ستون های زیر می باشد 

user_id | isp_id |  credit  | deposit | status | group_id |  creation_date   | deposit_recharge | nearest_exp_date

ستون user_id مربوط به id کاربر میباشد که unique است و برای ارتباط با table های دیگر استفاده میشود و در اصطلاح sql این فیلد primary key است.

ستون isp_id مربوط به id ای-اس-پی میباشد که unique است .

ستون credit میزان credit مصرفی کاربر را نمایش میدهد .

ستون deposit میزان حساب ذخیره کاربر را نمایش میدهد .

ستون status وضعیت charging را نشان میدهد .

ستون group_id مربوط به id گروهی است که کاربر عضو آن است . هر کابر میتواند تنها عضو یک گروه باشد.

ستون creation_date زمان ساخت کاربر را نمایش میدهد .

ستون deposit_recharge میزان حساب ذخیره کاربر برای ترافیک مازاد را نمایش میدهد .

ستون nearest_exp_date تاریخ انقضای کاربر را نمایش میدهد .


table normal_users:

این table شامل ستون های زیر می باشد 

user_id |  normal_username   |  normal_password   | second_normal_username

ستون user_id مربوط به id کاربر میباشد که unique است و برای ارتباط با table های دیگر استفاده میشود و در اصطلاح sql این فیلد primary key است.

ستون normal_username نام کاربری کاربر را نمایش میدهد.

ستون normal_password گذرواژه کاربر را نمایش میدهد.

table user_attrs:

این table شامل ستون های زیر می باشد 

user_id | attr_name |  attr_value

ستون user_id مربوط به id کاربر میباشد که unique است و برای ارتباط با table های دیگر استفاده میشود و در اصطلاح sql این فیلد primary key است.

ستون attr_name همان attribute مربوط به کاربر را نمایش میدهد.

ستون attr_value مقدار مربوط به هر attr_name را نمایش میدهد.

table charges: 

charge_id | charge_name   |  comment   | isp_id

ستون charge_id مربوط به id هر شارژ میباشد که unique است و برای ارتباط با table های دیگر استفاده میشود و در اصطلاح sql این فیلد primary key است.

ستون charge_name نام مربوط به هر charge_id را نمایش میدهد

ستون comment توضیحات مربوط به هر charge_id را نمایش میدهد

ستون isp_id مربوط به id ای-اس-پی میباشد که unique است .

table charge_rules: 

charge_rule_id | charge_rule_description | charge_rule_priority | charge_id

ستون charge_rule_id مربوط به id هر charge_rule میباشد که unique است و و در اصطلاح sql این فیلد primary key است.

ستون charge_rule_description نام مربوط به هر charge_rule_id را نمایش میدهد

ستون charge_rule_priority الویت هر charge_rule_id را نمایش میدهد

ستون charge_id مربوط به id هر شارژ میباشد که unique است.هر شارژ یا همان charge_id میتواند چندین charge_rule داشته باشد.

table charge_rule_attrs: 

charge_rule_id |  attr_name  |   attr_value

ستون charge_rule_id مربوط به id هر charge_rule میباشد که unique است و و در اصطلاح sql این فیلد primary key است.

ستون attr_name همان attribute مربوط به charge_rule_id را نمایش میدهد.

ستون attr_value مقدار مربوط به هر attr_name را نمایش میدهد.

table groups: 

group_id |   group_name   | isp_id |    comment  | status

ستون group_id مربوط به id گروه میباشد که unique است و برای ارتباط با table های دیگر استفاده میشود و در اصطلاح sql این فیلد primary key است.هر گروه تنها میتواند عضو یک شارژ (charge_id ) باشد. ستون group_name نام هر گروه را نمایش میدهد.

ستون isp_id مربوط به id ای-اس-پی میباشد که unique است .

ستون comment توضیحات مربوط به هر group_id را نمایش میدهد.

ستون status وضعیت هر group را نمایش میدهد.

table group_attrs: 

group_id | attr_name   |  attr_value

ستون group_id مربوط به id گروه میباشد که unique است

ستون attr_name همان attribute مربوط به گروه را نمایش میدهد.

ستون attr_value مقدار مربوط به هر attr_name را نمایش میدهد.


table ras: 

ras_id |  ras_description  |   ras_ip   |  ras_type | radius_secret | active |  comment

ستون ras_id مربوط به id هر ras میباشد که unique است

ستون ras_description نام ras را نمایش میدهد.

ستون ras_ip نشان دهنده ip هر ras L میباشد.

ستون ras_type نمایش دهنده نوع ras مربوطه میباشد

ستون radius_secret پسورد ras مربوطه را نشان میدهد

ستون active وضعیت فعال یا غیر فعال بودن ras مربوطه را نشان میدهد

ستون comment هم توضیحات احتمالی مربوط به هر ras است


[ویرایش] SQL Query :

query های کلی برروی postgresql به چند دسته تقسیم میشوند :

  • select : این دستور برای دیدن محتویات یک table استفاده میشود . برای مثال : 
select * from users;

خروجی این دستور تمامی ستون های table users را نمایش میدهد. همچنین برای مشاهده تحویات یک ستون خاص از دستور زیر استفاده میکنیم : 

select user_id from users;
  • update : این دستور برای update محتویات یک table استفاده میشود . برای مثال : 
update users set credit = '1000' where user_id = '2';

خروجی این دستور credit مربوط به user با id = 2 را update میکند و مقدارش را به 1000 تغییر میدهد .


  • insert : این دستور برای update محتویات یک table استفاده میشود . برای مثال : 
insert into charges values  (3000,'test1','hh',0);

خروجی این دستور یک ردیف به table charge با مقادیر زیراضافه میکند :charge_id = 3000 - charge_name = test1 - comment=hh isp_id=0


  • delete : این دستور برای delete محتویات یک table استفاده میشود . برای مثال : 
delete from users  where user_id = '2';

خروجی این دستور ردیف مربوط به user با id = 2 را delete میکند .


  • alter : این دستور برای تفییر ستون های یک table استفاده میشود.این تغییرات شامل افزودن کم کردن و یا تغییرنام میباشد . برای مثال : 
alter table users add  culumn1 text;

خروجی این دستور یک ستون با نام text به table user اضافه میکند .


[ویرایش] IBSng DataBase :

نصب دیتابیس Postgresql: 

apt-get install postgresql-9.4

اضافه کردن کاربر به دیتابیس: 

su - postgres
createuser -s ibs

اضافه کردن دیتابیس: 

su - postgres
createdb IBSng

Drop کردن کاربر و یا دیتابیس: 

su - postgres
dropdb IBSng
dropuser ibs

وارد شدن به دیتابیس از طریق خط فرمان: 

psql -U ibs IBSng

برای دسترسی دادن به یک دیتابیس یک خط به صورت زیر در این فایل اضافه میکنیم: 

nano /etc/postgresql/9.4/main/pg_hba.conf

local IBSng ibs trust

وارد شدن به دیتابیس remote از طریق خط فرمان: 

psql -U ibs IBSng -h <ipaddress>

بکاپ گرفتن از دیتابیس بصورت کامل: 

pg_dump -Fc -U ibs IBSng > IBSng_dump

بازگرداندن بکاپ: 

pg_restore -Fc -j CPU_CORES -U ibs -d IBSng IBSng_dump

مشاهده لیست Object های یک فایل بکاپ: 

pg_restore -Fc -l IBSng_dump | less

بازگرداندن فقط جداولی که نیاز است: 

pg_restore -Fc -j CPU_CORES -U ibs -d IBSng -L RESTORE.txt IBSng_dump

دیدن لیست دیتابیس های سیستم: 

\l+

دیدن لیست جداول دیتابیس IBSng: 

\d+

مشاهده راس های ثبت شده: 

select * from ras ;

disable کردن یک ras (با id =1 ): 

update ras set active = 'f' where ras_id = 1;

مشاهده کاربران: 

select * from normal_users ;

مشاهده لیست ادمین ها: 

select * from admin ;

بکاپ گرفتن از یک جدول در خود دیتابیس: 

select * into backup_TABLE from TABLE ;

بکاپ گرفتن از یک جدول در فایل CSV: 

copy TABLE into ‘/tmp/FILE.csv’ csv header ;

انتقال خروجی یک دستور به فایل CSV: 

copy  (select * from users) to ‘/tmp/FILE.csv’ CSV HEADER ;

انتقال خروجی یک فایل CSV و تبدیلش به table :

بدین منظور میبایست ابتدا table ی در DataBase به تعداد ستون های فایل csv مربوط بسازیم : 

CREATE TABLE new_table(username text, password text)  ;

سپس فایل csv را در درون تیبل ایجاد شده میریزیم: 

COPY new_table FROM '/tmp/user-pass.csv' CSV ;

remote access: برای دسترسی دادن به یک دیتابیس از یک IP خاص یک خط به صورت زیر در این فایل اضافه میکنیم: 

nano /etc/postgresql/9.4/main/pg_hba.conf

host DatabaseName Username IPMask AuthenticaionMethod
e.g: host template1 postgres 192.168.1.2/32 md5

و پس از آن دیتابیس رو Reload میکنیم: 

/etc/init.d/postgresql reload

مشاهده Session های فعال و کاربران متصل به دیتابیس: 

select * from pg_stat_activity;

Kill کردن یک پروسه از دیتابیس‌ (توصیه نمیشود): 

select pg_cancel_backend(PID);

توجه فرمایید که در هر مرحله میتوانید با زدن کلید TAB دستور را تکمیل نمایید. این دستورات اصلی برای شروع کار با دیتابیس است که قطعا بسته به کاری که نیاز دارید انجام دهید دستورات فرق میکنند.


شکل زیر چگونگی ارتباط میان charge ها charge-rule ها attr_name ها و attr_value ها را نمایش میدهد :


Db1.png


همانطور که در شکل پیداست شارژ Hamedan-POSTPAID-UNLIMIT-64-FIX-N با ای-اس-پی hamedan دارای تعدادی charge_rule و attr_name است .


برای بررسی دقیقتر table مربوط به charge_rule_attrs برای دو charge_rule_id بررسی میکنیم :


Db6.png


همانطور که در شکل پیداست charge_rule_id 9952 مربوط به کاربران failed است که در شکل قبل هم مشخص است .


همچنین charge_rule_id 10936 مربوط به سرویس master است ( attr_name = service و attr_value = Master:Master::1:1 ) که این مورد در شکل زیر به وضوح مشخص است :


Db4.png


در ادامه به توضیح مقادیر state در charge_rule_attrs میپردازیم . برای هریک از حالات موجود در attr_name مربوط به state یک عدد در DataBase مشخص شده است : 

Negative Credit: Yes   .......... 1
Package: Yes .......... 2
Recharged: Yes .......... 4
Temporary Extended: Yes .......... 8
VoIP Outgoing: Yes .......... 16
Failed Login: Yes .......... 32
IDLE Login: Yes .......... 64

همانطور که مشخص است برای قعال کردن دو value به صورت همزمان مقدار مربوطه از جمع دو مقدار حاصل میشود . برای مثال با فعال کردن گزینه های Package و Recharged عدد ثبت شده در DataBase عدد 6 میباشد.

به عنوان مثال دستور زیر charge_rule_id هایی که دارای Negative Credit فعال میباشند را نمایش میدهد : 

select charge_rule_id from charge_rule_attrs where attr_name = 'state' and mod (attr_value::integer, 2) = '1'

همچنین مقادیر مربوط به دیگر attr ها در دو شکل زیر برای charge_rule 974 مشخص شده است :

Db7.png
مقادیرcpmb_upload , hours_interval ,   و service به ترتیب در web panel نام های زیر را دارا هستند:

Charge Per MegaByte of Upload ...... cpmb_download
Hours ...... hours_interval
Charge Per MegaByte of Download ...... cpmb_upload
service ...... Sub Service

همچنین مقادیر Auto Start و Auto Check در attr_name مربوط به service به شرح ذیل میباشد : 

1 .... Auto Start: Yes
1 .... Auto Check: Yes
Db8.png

برگرفته از «http://wiki.parspooyesh.com/index.php?title=%D9%BE%D8%A7%DB%8C%DA%AF%D8%A7%D9%87_%D8%AF%D8%A7%D8%AF%D9%87_PostgresSQL&oldid=2779»
ابزارهای شخصی

گویش‌ها
فضاهای نام
عملکردها
گشتن
جعبه‌ابزار