Home Php C# Sql C C++ Javascript Python Java Go Android Git Linux Asp.net Django .net Node.js Ios Xcode Cocoa Iphone Mysql Tomcat Mongodb Bash Objective-c Scala Visual-studio Apache Elasticsearch Jar Eclipse Jquery Ruby-on-rails Ruby Rubygems Android-studio Spring Lua Sqlite Emacs Ubuntu Perl Docker Swift Amazon-web-services Svn Html Ajax Xml Java-ee Maven Intellij-idea Rvm Macos Unix Css Ipad Postgresql Css3 Json Windows-server Vue.js Typescript Oracle Hibernate Internet-explorer Github Tensorflow Laravel Symfony Redis Html5 Google-app-engine Nginx Firefox Sqlalchemy Lucene Erlang Flask Vim Solr Webview Facebook Zend-framework Virtualenv Nosql Ide Twitter Safari Flutter Bundle Phonegap Centos Sphinx Actionscript Tornado Register | Login | Edit Tags | New Questions | 繁体 | 简体


10 questions online user: 82

61
votes
answers
14 views
+10

How do I create an index in PostgreSQL based on lowercase only?

How would I set up an index based on lower case only?

Even though the actual field contains both upper and lower case letters.

Also, can I run a query and have only the lower case index value returned?

up vote 50 down vote accepted favorite
沙发
+500
+50

您可以創建索引並將字段轉換為大寫或小寫。然後,當你進行查詢時,你可以做同樣的轉換,它會做正確的事情。

所以:

  CREATE UNIQUE INDEX lower_case_username ON用戶((更低) (用戶名)));   

然後查詢同樣的事情:

  SELECT username FROM users WHERE lower(username)='bob';  
     
			
        

謝謝你的幫助!這是我的SQL語句:CREATE UNIQUE INDEX“lcase_idx”ON“public”。“members”using btree((lower(Memb_Name))); 錯誤:列“memb_name”不存在。似乎不是使用小寫的Memb_Name來構建索引,而是使用較小的函數來使字段的名稱引用小寫。我在Memb_Name附近使用和不使用引號嘗試了它。 - Paul Townsend 2010年10月21日16:35

您使用的是什麼版本的PostgreSQL?我只是在我的一張桌子上(在8.3上)做到了這一點並且工作正常。“d public.members”為該列顯示了什麼? - 尖銳的2010年10月21日16:45

我不知道“d public.members”是什麼。我正在使用8.4--我讓它在另一個測試表上工作。 - 保羅湯森2010年10月21日18:09

另外,請確保將bind var設置為小寫;-) - atrain 2011年5月18日1:06

@glyph不,Postgresql很聰明,可以識別函數應用程序與索引的定義相匹配。參考 - 尖銳的2月7日'17在2:24

+80

根據文檔,您可以這樣做:< / p>

  CREATE UNIQUE INDEX lower_title_idx ON films((lower(title)));  
     
			
        

謝謝你的幫助!這是我的SQL語句:CREATE UNIQUE INDEX“lcase_idx”ON“public”。“members”using btree((lower(Memb_Name))); 錯誤:列“memb_name”不存在。似乎不是使用小寫的Memb_Name來構建索引,而是使用較小的函數來使字段的名稱引用小寫。我在Memb_Name附近使用和不使用引號嘗試了它。 - 保羅湯森2010年10月21日16:40

PS:我似乎工作得很好......只有字段名稱全是小寫的。 - 保羅湯森2010年10月21日18:33

+30
  CREATE UNIQUE INDEX my_index_name ON my_table(LOWER(my_field));  
     
			
        

謝謝你的幫助!這是我的SQL語句:CREATE UNIQUE INDEX“lcase_idx”ON“public”。“members”using btree((lower(Memb_Name))); 錯誤:列“memb_name”不存在。似乎不是使用小寫的Memb_Name來構建索引,而是使用較小的函數來使字段的名稱引用小寫。我在Memb_Name附近使用和不使用引號嘗試了它。 - 保羅湯森2010年10月21日16:41

PS:我似乎工作得很好......只有字段名稱全是小寫的。 - 保羅湯森2010年10月21日18:34

0
votes
answers
11 views
+10

在Visual Studio中更新tableadapter數據庫

1

我試圖讓我的程序使用事件處理程序和tableadapters將一行添加到數據庫。我可以成功添加一行到數據庫,但是當我單擊「btnRefresh」按鈕時它不會保存。這是事件處理程序的代碼。在Visual Studio中更新tableadapter數據庫

Private Sub btnRefresh_Click(sender As Object, e As EventArgs) Handles btnRefresh.Click 
    Try 
     Validate() 
     CarsBindingSource.EndEdit() 
     CarsTableAdapter.Update(Me.ABVDataSet.Cars) 
     MsgBox("Refreshed Successfully") 
    Catch ex As Exception 
     MsgBox("Update failed") 
    End Try 
End Sub 
+0

你叫上車表或ABVDataSet AcceptChanges的? – Steve

+0

AcceptChanges?不,我沒有。我該怎麼做,那會做什麼? –

+1

Update方法選擇要查看Cars表中每行的DataRowState的內容。 AcceptChanges將此狀態重置爲「未更改」,因此更新對這些行不做任何處理。我建議使用調試器來檢查Cars表是否包含任何DataRowState屬性不同的行,並且這些行與Unchanged – Steve

沙发
0
0
Private Sub btnRefresh_Click(sender As Object, e As EventArgs) Handles btnRefresh.Click Try Validate() CarsBindingSource.EndEdit() CarsTableAdapter.Update(ABVDataSet,"table_name") MsgBox("Refreshed Successfully") Catch ex As Exception MsgBox("Update failed") End Try End Sub 
0
votes
answers
25 views
+10

ORA-02291:完整性約束(H00300581.FK_CLASSES)違反 - 父鍵沒有找到

0

當我做插入給我這個錯誤:ORA-02291:完整性約束(H00300581.FK_CLASSES)違反 - 父鍵沒有找到

ORA-02291: integrity constraint (H00300581.FK_CLASSES) violated - parent key not found

這種錯誤發生的所有鑲入登記表。我需要幫助,請嘗試爲我解決。

它來自表中的外鍵嗎?這可能是因爲我嘗試了一切,但它沒有奏效。

創建表

drop table g3_trainers CASCADE CONSTRAINTS; 
drop table g3_classes CASCADE CONSTRAINTS; 
drop table g3_members CASCADE CONSTRAINTS; 
drop table g3_registration CASCADE CONSTRAINTS; 

CREATE TABLE G3_TRAINERS 
(
TRAINER_ID VARCHAR(6) NOT NULL, 
TRAINER_NAME VARCHAR(50) NOT NULL, 
COUNTRY VARCHAR(25) NOT NULL, 
DOB DATE NOT NULL, 
SPECIALITY VARCHAR(30) NOT NULL, 
DESCRIPTION VARCHAR(500) NOT NULL, 
PRIMARY KEY (TRAINER_ID) 
); 

CREATE TABLE G3_CLASSES 
(
CLASS_NUM VARCHAR(6) NOT NULL, 
TRAINER_ID VARCHAR(6) NOT NULL, 
CLASS_TITLE VARCHAR(50) NOT NULL, 
START_DATE DATE NOT NULL, 
END_DATE DATE NOT NULL, 
COST NUMBER(6) NOT NULL, 
PRIMARY KEY (CLASS_NUM), 
CONSTRAINT fk_TRAINERS FOREIGN KEY(TRAINER_ID) REFERENCES G3_TRAINERS(TRAINER_ID) 
); 

CREATE TABLE G3_MEMBERS 
(
MEMBER_ID VARCHAR(6) NOT NULL, 
MEMBER_NAME VARCHAR(50) NOT NULL, 
MEMBER_AGE NUMBER(2) NOT NULL, 
GENDER VARCHAR(10) NOT NULL, 
PHONE_NUMBER VARCHAR(10) NOT NULL, 
CHECK (regexp_like (PHONE_NUMBER,'^(d{3}-d{3}-?d{4}|d{10})$')), 
CHECK (MEMBER_AGE>=17), 
CHECK(MEMBER_AGE NOT LIKE '%[^a-zA-Z]%'), 
PRIMARY KEY (MEMBER_ID) 
); 

CREATE TABLE G3_REGISTRATION 
(
REGISTRATION_ID VARCHAR(6) NOT NULL, 
MEMBER_ID VARCHAR(6) NOT NULL, 
AMOUNT_TO_PAY NUMBER(6) NOT NULL, 
CLASS_NUM VARCHAR(6) NOT NULL, 
PRIMARY KEY (REGISTRATION_ID), 
CONSTRAINT fk_MEMBERS FOREIGN KEY(MEMBER_ID) REFERENCES G3_MEMBERS(MEMBER_ID), 
CONSTRAINT fk_CLASSES FOREIGN KEY(CLASS_NUM) REFERENCES G3_CLASSES(CLASS_NUM) 
); 

創建序列

CREATE SEQUENCE G3_TRAINER_Seq 
START WITH  10001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

CREATE SEQUENCE G3_MEMBER_Seq 
START WITH  20001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

CREATE SEQUENCE G3_CLASSE_Seq 
START WITH  30001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

CREATE SEQUENCE G3_REGISTRATIONS_Seq 
START WITH  40001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

訓練數據

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Ahmed', 'Eygpt', '2/3/1988', 'Body Building', 'I describe my self as an active guy i workout everyday and thats why i desided to train people to be good as me'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'John', 'Canada', '4/6/1987', 'Crossfit', 'I like being healthy and work as much as i can to be fit and to have a great life'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Sara', 'Canada', '9/1/1990', 'Yoga', 'I like interacting with poeple, and to teach them how yoga will be a very good source of flexing and relaxing your body'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Jun', 'China', '12/4/1981', 'Crossfit', 'I describe my self as an daily working guy i like my job because i get to know more about people and i teach many people how to be fit'); 

    INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Mike', 'Ohio', '2/22/1981', 'Body Building', 'Being strong and big is what i am aiming for, i like keeping my diet healthy and to train poeple to be healthy and big like me'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Mia', 'Germany', '12/12/1987', 'Yoga', 'I describe myself as a hard working i love my job and i love my members'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Essam', 'UAE', '1/5/1980', 'Body Building', 'I am a hard worker and i like to train my members and i want them to be healthy and strong'); 

類的數據

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10001, 'Weight Lifting Class', '1/12/2017', '1/1/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10001, 'Weight Lifting Class', '1/2/2018', '1/3/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10002, 'Body Weight Class', '2/1/2018', '2/2/2018', 950); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10003, 'Yoga Class', '3/2/2018', '3/3/2018', 750); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10003, 'Yoga Class', '5/5/2018', '5/6/2018', 750); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10004, 'Body Weight Class', '10/2/2018', '10/3/2018', 950); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10004, 'Body Weight Class', '10/4/2018', '10/5/2018', 950); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '12/20/2017', '1/20/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '2/20/2018', '3/20/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '4/20/2018', '5/20/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10006, 'Yoga Class', '12/28/2017', '1/28/2018', 750); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10007, 'Weight Lifting Class', '10/3/2018', '10/4/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10007, 'Weight Lifting Class', '10/5/2018', '10/6/2018', 1150); 

成員數據

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Mohammed', '21', 'Male', '0502202212'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Afra', '28', 'Female', '0555544558'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Essa', '30', 'Male', '0555544558'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Ahmed', '19', 'Male', '0563020300'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Alex', '25', 'Female', '0521022932'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Abdulla', '24', 'Male', '0504493393'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Abdulrahman', '20', 'Male', '0502292290'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Olivia', '21', 'Female', '0561020200'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Omar', '30', 'Male', '0502503323'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Nasser', '29', 'Male', '0504334439'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Sophia', '22', 'Female', '0561616316'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Nadia', '21', 'Female', '0507797786'); 

註冊數據

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20001, 1150, 30001); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20001, 750, 30004); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20002, 750, 30004); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20002, 950, 30006); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150, 30001); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150, 300010); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20004, 950, 30006); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20004, 1150, 30010); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20005, 1150, 30007); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20005, 750, 30011); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20006, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20006, 1150, 30012); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20007, 1150, 30009); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20007, 750, 30011); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20008, 1150, 30008); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20008, 750, 30011); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20009, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20009, 750, 30005); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 1150, 30012); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 950, 30007); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20011, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20011, 750, 30005); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20012, 750, 30004); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20012, 1150, 30009); 
+0

您的腳本有太多錯誤。有幾個地方丟失了逗號。您在插入語句中使用的序列名稱中存在拼寫錯誤。首先解決這些問題。 –

+0

@Kaushik Nayak我修復了一些小問題,我如何添加代碼? –

+0

@Kaushik Nayak我編輯它看看它 –

沙发
0
0

commas缺少的大多數語句,如:

between 'Eygpt' & '12/3/1988' in :

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, 
SPECIALITY, DESCRIPTION) VALUES (G3_TRAINERS_Seq.nextVal, 'Ahmed', 
'Eygpt' '12/3/1988', 'Body Building', 'I describe my self as an active 
guy i workout everyday and thats why i desided to train people to be 
good as me'); 

OR

between 'Weight Lifting Class' & '1/12/2017'

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSES_Seq.nextVal, 10001, 'Weight Lifting Class' '1/12/2017', '1/1/2018', 1150); 

你有CONSTRAINT fk_CLASSES FOREIGN KEY(CLASS_NUM) REFERENCES G3_CLASSES(CLASS_NUM)爲約束

G3_CLASSES_Seq序列填充g3_classes表的class_num列,應該是方便,fk_CLASSES FOREIGN KEY,但你有另一個序列G3_CLASSE_Seq開始30000。順便說一句,現在看來,這應該與30001

i.e. Pay attention that G3_CLASSE_Seq and G3_CLASSES_Seq are different.

的方式啓動,刪除空格在column names之間create sentences類似以下內容:

爲例,其轉換

CREATE TABLE G3_TRAINERS 
(

TRAINER_ID VARCHAR(6) NOT NULL, 
TRAINER_NAME VARCHAR(50) NOT NULL, 
COUNTRY VARCHAR(25) NOT NULL, 
DOB DATE NOT NULL, 
SPECIALITY VARCHAR(30) NOT NULL, 
DESCRIPTION VARCHAR(500) NOT NULL, 

PRIMARY KEY (TRAINER_ID) 

); 

對此:

CREATE TABLE G3_TRAINERS 
(
TRAINER_ID VARCHAR(6) NOT NULL, 
TRAINER_NAME VARCHAR(50) NOT NULL, 
COUNTRY VARCHAR(25) NOT NULL, 
DOB DATE NOT NULL, 
SPECIALITY VARCHAR(30) NOT NULL, 
DESCRIPTION VARCHAR(500) NOT NULL, 
PRIMARY KEY (TRAINER_ID) 
); 

An d在以下語句中存在另一個問題(300010

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150, 300010);

+0

非常感謝,解決了我的其他問題之一,但我發佈了主要問題 –

+0

@abdulrahmanarab請看我最後編輯的外鍵和序列。 –

+0

@abdulrahmanarab我認爲,問題不是使用'G3_CLASSE_Seq'(你應該使用這個,從30001開始)。您正在使用'G3_CLASSES_Seq'。 –

0
votes
answers
10 views
+10

PostgreSQL:如何根據臨界值分割範圍?

0

我有一個簡單的數據在我的PostgreSQL數據庫9.5類似如下:PostgreSQL:如何根據臨界值分割範圍?

id c_value start_r end_r m_value 
1  171  61  201  19.3 
2  89  59  119  22.1 
3  47  137  227  18.9 
3  47  227  287  0.0 
3  47  287  347  27.1 
3  47  347  47  6.5 
4  65  120  150  14.1 

柱解釋:Id (integer)而不是唯一的。 C_value (integer)是關鍵的價值。 start_r (integer)end_r (integer)是範圍的開始和結束值,而m_value (numeric)是平均值。

對於每一行,我只需要根據c_value修改start_rend_r。也就是說,如果遇到臨界值,我需要將範圍分割成片,否則輸出將相同。預期結果如下:

id c_value start_r end_r m_value 
1  171  61  171  19.3 
1  171  171  201  19.3 
2  89  59  89  22.1 
2  89  89  119  22.1 
3  47  137  227  18.9 
3  47  227  287  0.0 
3  47  287  347  27.1 
3  47  347  47  6.5 
4  65  120  150  14.1 

有人可以建議我如何根據臨界值來分割範圍?

+0

提示:LEAST(),GREATEST(),UNION ALL – wildplasser

沙发
0
2

一種方法是一個簡單的union all

??
0
votes
answers
32 views
+10

Python:從每個打開的Google Chrome標籤獲取所有Url

4

我需要從python 3中所有打開的Chrome瀏覽器標籤中獲取所有的URL,而不需要用戶干預。林在Windows上使用Microsoft Visual Studio Python3 10Python:從每個打開的Google Chrome標籤獲取所有Url

我用盡:

直接與開放(路徑當前選項卡)打開它 - 不工作,因爲我沒有permission-我認爲它鎖定,因爲鉻activly寫入它。

Current_Tabs_Source = open(r"C:UsersBeniAppDataLocalGoogleChromeUser 
DataDefaultCurrent Tabs", "r") 
Current_Tabs_Raw = Current_Tabs_Source.read() 
print(Current_Tabs_Raw) #just for checking 

PermissionError:[錯誤13]許可被拒絕通過sglite3

開幕 - 行不通的,因爲其鎖定。我無法在任何地方找到密碼。我試圖打開URL的歷史記錄,但它不起作用。

import sqlite3 
from os import path 

data_path = path.expanduser('~') + r"AppDataLocalGoogleChromeUser 
DataDefault" 
files = listdir(data_path) 
history_db = path.join(data_path, 'history') 

c = sqlite3.connect(history_db) 
cursor = c.cursor() 
select_statement = "SELECT urls.url, urls.visit_count FROM urls, visits 
WHERE urls.id = visits.url;" 
cursor.execute(select_statement) 

results = cursor.fetchall() 

print(results) #just for checking 

sqlite3.OperationalError:數據庫被鎖定

使用硒和第三方Chrome擴展到所有的URL複製到剪貼板 - 不工作,因爲這些擴展僅在活動窗口硒工作。所以Windows中的標籤,我想不要被複制。

我曾考慮過將Chrome擴展每30秒複製一份臨時文件。但是我只知道最小的Javascript,所以這件事讓我很生氣。

那麼有沒有人知道在Python中做到這一點的方法?任何其他解決方案,不勝感激。

+0

我想不出任何可能的方式這可以在Python來完成。我甚至不知道你如何試圖這樣使用SQLite,數據庫語言...您最好的選擇確實是更像JavaScript的前端。 – Mangohero1

+1

這將是有益的,如果你可以分享你一直工作的代碼,直到現在,所以我們可以更好地瞭解你的方法 –

+1

@HamSam我上傳了一些代碼,所以你可以看到即時嘗試 –

沙发
0
0

如果要訪問數據庫,應關閉所有瀏覽器。

Source

0
votes
answers
24 views
+10

如何laravel

1

使用控制器傳遞多個變量視圖這是我的控制器如何laravel

public function show($id) 
{ 
    $products = Item::find($id)->products; 

    $categories = Item::all(); 

    return view('category.index', compact(['categories', 'products'])); 
} 

這是我的看法

@extends('layout.front') 
@section('page') 
<div class="row" id="portfolio"> 

<!-- Page Content --> 
<div class="container"> 

    <div class="row"> 

     <div class="col-lg-3" style="margin-top:20px;"> 

      <nav class="main-nav"> 
       <ul class="main-nav-ul"> 
        <li style="background-color: #343A40; color: #ffffff; font-weight: 600;" id="sidebar-header"><a>Product List</a></li> 
        <li><a href="#" style="font-weight: 600;">HouseHold<span></span></a> 
         <ul> 
          @if(!empty($categories)) 
           @forelse($categories as $category) 
           <li><a href="{{ route('category.show', $category->id)}}">{{ $category->name }}</a></li> 

           @empty 
            <li>No data found</li> 
           @endforelse 
          @endif 
         </ul> 
        </li> 
        <li><a href="#" style="font-weight: 600;">Vegetables</a></li> 
        <li><a href="#" style="font-weight: 600;">Fruits</a></li> 
       </ul> 
      </nav> 

     </div> 
     <!-- /.col-lg-3 --> 

     <div class="col-lg-9"> 


      <div class="row" style="margin-top:20px;"> 



        @foreach($products as $key=>$product) 
         <div class="col-md-4 col-sm-6 portfolio-item" id="items"> 
          <div class="card h-100"> 

           <a href="#"><img class="card-img-top" src="/storage/{{ $product->image }}" alt="Product Image"></a> 
           <div class="card-body"> 
            <h4 class="card-title"> 
             <a href="#"><br />{{ $product->item_name}}</a> 
            </h4> 
            <p class="card-text" style="color: #A9A9A9;text-decoration: line-through;">LKR {{ $product->old_price}}</p> 
            <h4 style="text-align: center; color: #fff;"><a class="waves-effect waves-light btn btn-dark btn-block">LKR {{ $product->new_price}}</a></h4> 

           </div> 
          </div> 
         </div> 
        @endforeach 


      </div> 
      <!-- /.row --> 

     </div> 
     <!-- /.col-lg-9 --> 

    </div> 
    <!-- /.row --> 

</div> 
<!-- /.container --> 
</div> 
@endsection 

我想通過在同我的類別列表和產品項目視圖。但是我無法將這兩個變量從控制器傳遞到我的視圖。它顯示了這個錯誤。

未定義的變量:產品(查看:/var/www/html/hzone_new/resources/views/category/index.blade.php)

沙发
0
1

更新您的控制器功能,如:

$categories = Item::all(); 
$products = Item::find($id)->products; 
return view('category.index', compact('categories', 'products')); 

注意:你不能定義你的函數變量的產品定義,請

@if(!empty($products)) 
    @foreach($products as $key=>$product) 
         <div class="col-md-4 col-sm-6 portfolio-item" id="items"> 
          <div class="card h-100"> 

           <a href="#"><img class="card-img-top" src="/storage/{{ $product->image }}" alt="Product Image"></a> 
           <div class="card-body"> 
            <h4 class="card-title"> 
             <a href="#"><br />{{ $product->item_name}}</a> 
            </h4> 
            <p class="card-text" style="color: #A9A9A9;text-decoration: line-through;">LKR {{ $product->old_price}}</p> 
            <h4 style="text-align: center; color: #fff;"><a class="waves-effect waves-light btn btn-dark btn-block">LKR {{ $product->new_price}}</a></h4> 

           </div> 
          </div> 
         </div> 
        @endforeach 
@endif 
+0

感謝您支持我兄弟。你的代碼工作正常。非常感謝 –

+0

@ K.Mihiranga:很高興幫助! –

0
votes
answers
28 views
+10

從終端打開MySQL

0

我在我的MacBook上安裝了MySQL,並希望通過簡單地鍵入mysql來通過終端訪問MySQL。我該怎麼做呢?從終端打開MySQL

沙发
0
2

您需要將MySQL添加到$ PATH,因此當您在終端中鍵入mysql時,它將加載。

要做到這一點,你需要打開終端並鍵入以下內容:

sudo nano /etc/profile 

然後,系統會要求您的計算機密碼(不是你的MySQL密碼)。 GNU Nano將會開放。鍵入以下內容:

export PATH=/usr/local/mysql/bin:$PATH 

按Ctrl O,然後按Ctrl X.

類型到終端:

source /etc/profile 

您現在就可以開始使用MySQL從終端。確保MySQL服務器正在運行(最簡單的方法是打開系統首選項 - > MySQL - >啓動MySQL服務器)。

在終端,打開MySQL中,鍵入以下內容(你會被要求輸入密碼爲您的連接):

mysql -u root -p 

您可以更改根到任何MySQL連接的用戶名是。爲了確保您連接到正確的連接,類型: 顯示

show databases; 

這會告訴你,你已經打開了MySQL連接上可用的數據庫。

0
votes
answers
15 views
+10

在excel(單元格)中引用日期的SQL查詢

0

我有通過VBA的Mysql查詢,並且在使用單元格中的日期時出錯。我需要使用單元格值來實現循環目的。我認爲它是由於excel和Mysql之間的日期格式兼容性。感謝你的幫助。謝謝!在excel(單元格)中引用日期的SQL查詢

For R = 6 to lastrow 

SQLQuery2 = "SELECT * FROM Mfg.databasemodels_note where typeId = " & Sheets("Sheet1").Range("B" & R) & " AND date < " & Sheets("Sheet1").Range("I" & R) & " order by date asc limit 1;" 
+0

順便說一下,例如我的單元格的值2017年10月26日上午03點03分44秒由於 – Kaison

沙发
0
0

簡短的回答是您的SQL字符串中包含這是無法識別到MySQL的日期。在建立字符串之後加入一個斷點,運行代碼,然後查看字符串的內容; MySQL應該如何理解它?

按MySQL的參考材料(下面的鏈接),這裏是你更新的代碼行:

SQLQuery2 = "SELECT * FROM Mfg.databasemodels_note where typeId = " & Sheets("Sheet1").Range("B" & R).Value & " AND date < " & Format(Sheets("Sheet1").Range("I" & R).Value, "'YYYY-MM-DD Hh:NN:SS'") & " order by date asc limit 1;" 

真正的答案是,你永遠,永遠想建立一個SQL字符串包含用戶輸入,從頭開始,因爲SQL injection risks。閱讀How To Invoke a Parameterized ADO Query Using VBA/C++/Java以快速瞭解您應該做什麼(在運行您自己的基本驗證之上)。

基準物質:MySQL 5.7: The DATE, DATETIME, and TIMESTAMP Types/Visual Basic for Applications: Format Function

進一步閱讀:VBA, ADO.Connection and query parameters/ADODB Command failing Execute with parameterised SQL query

+0

的@Excelosaurus ..轉換日期到字符串解決問題。非常感謝!! – Kaison

0
votes
answers
27 views
+10

如何使用實體框架更新自引用圖?

2

我使用實體框架來處理數據庫,我也自參照模型如下:如何使用實體框架更新自引用圖?

public class PhysicalObject 
{ 
    public PhysicalObject() 
    { 
     SubPhysicalObjects = new HashSet<PhysicalObject>(); 
    } 

    [Key] 
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] 
    public int Id { get; set; } 

    public int? ParentId { get; set; } 

    [StringLength(150)] 
    public string Title { get; set; } 

    public virtual PhysicalObject Parent { get; set; } 

    public virtual ICollection<PhysicalObject> SubPhysicalObjects { get; set; } 

} 

我用GraphDiff庫更新斷開的繪圖,但似乎它不支持自我更新引用圖。

我的問題是:什麼是更新使用實體框架的自我參照圖的最佳方式:

  • 刪除/更新現有physicalObjects

  • 插入不存在physicalObjects

沙发
0
0

假設我有兩個實體,如下所示:

public class PhysicalObject 
{ 

    [Key] 
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] 
    public int Id { get; set; } 

    public int? ParentId { get; set; } 

    public int StorageRequestId { get; set; } 

    public string Title { get; set; } 

    public virtual PhysicalObject Parent { get; set; } 

    public virtual ICollection<PhysicalObject> SubPhysicalObjects { get; set; } 

    public virtual StorageRequest StorageRequest { get; set; } 

} 

public class StorageRequest 
{ 
    public StorageRequest() 
    { 
     PhysicalObjects = new HashSet<PhysicalObject>(); 
    } 

    [Key] 
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] 
    public int Id { get; set; } 

    public string Title { get; set; } 

    public virtual ICollection<PhysicalObject> PhysicalObjects { get; set; } 
} 

請注意,PhysicalObject是自引用表。

var oldPhysicalObjects = dbContext.PhysicalObjects.Where(x => x.StorageRequestId== storageRequestId).ToList(); 

var existingIds = new HashSet<int>(); 
foreach (var item in newGraphDto.PhysicalObjects.ToList()) 
    { 
    updateGraph(item, oldPhysicalObjects, dbContext, storageRequestId,existingIds); 
    } 
var posToDelete = oldPhysicalObjects.Where(x => existingIds.All(e => e != x.Id)).ToList(); 
dbContext.PhysicalObjects.RemoveRange(posToDelete); 
dbContext.SaveChanges(); 

updateGraph方法將更新PhysicalObjects遞歸的每一棵樹,它看起來像:

private void updateGraph(PhysicalObjectDto physicalObjectDto, IList<PhysicalObject> oldPhysicalObjects, MyDbContext dbContext, int storageRequestId, HashSet<int> existingIds, PhysicalObject parent = null) 
    { 
     if (physicalObjectAddEditDto.Id == 0) 
     { 
      PhysicalObject po = new PhysicalObject 
      { 
       Id = physicalObjectAddEditDto.Id, 
       Title = physicalObjectAddEditDto.Title, 
       StorageRequestId = storageRequestId, 
       Parent=parent 

      }; 

      dbContext.PhysicalObjects.Add(po); 

      parent = po; 
     } 
     else 
     { 
      var po = oldPhysicalObjects.FirstOrDefault(x => x.Id == physicalObjectAddEditDto.Id); 
      po.Title = physicalObjectAddEditDto.Title; 
      po.StorageRequestId = storageRequestId; 
      po.Parent = parent; 
      dbContext.Entry(po).CurrentValues.SetValues(po); 

      parent = po; 
     } 


     existingIds.Add(parent.Id); 
     foreach (var subPhysicalObject in physicalObjectAddEditDto.SubPhysicalObjects) 
     { 
      updateGraph(subPhysicalObject, oldPhysicalObjects, dbContext, mailRoomRequestId, existingIds, parent); 
     } 

    } 

我希望我的代碼將幫助別人知道如何使用實體框架

不是讓我們更新圖表更新自引用表格的圖形樹

77
votes
answers
33 views
+10

Is using a load balancer with ElasticSearch unnecessary?

I have a cluster of 3 ElasticSearch nodes running on AWS EC2. These nodes are setup using OpsWorks/Chef. My intent is to design this cluster to be very resilient and elastic (nodes can come in and out when needed).

From everything I've read about ElasticSearch, it seems like no one recommends putting a load balancer in front of the cluster; instead, it seems like the recommendation is to do one of two things:

  1. Point your client at the URL/IP of one node, let ES do the load balancing for you and hope that node never goes down.

  2. Hard-code the URLs/IPs of ALL your nodes into your client app and have the app handle the failover logic.

My background is mostly in web farms where it's just common sense to create a huge pool of autonomous web servers, throw an ELB in front of them and let the load balancer decide what nodes are alive or dead. Why does ES not seem to support this same architecture?

up vote 17 down vote accepted favorite
沙发
+170
+50

You don't need a load balancer — ES is already providing that functionality. You'd just another component, which could misbehave and which would add an unnecessary network hop.

ES will shard your data (by default into 5 shards), which it will try to evenly distribute among your instances. In your case 2 instances should have 2 shards and 1 just one, but you might want to change the shards to 6 for an equal distribution.

By default replication is set to "number_of_replicas":1, so one replica of each shard. Assuming you are using 6 shards, it could look something like this (R is a replicated shard):

  • node0: 1, 4, R3, R6
  • node1: 2, 6, R1, R5
  • node2: 3, 5, R2, R4

Assuming node1 dies, the cluster would change to the following setup:

  • node0: 1, 4, 6, R3 + new replicas R5, R2
  • node2: 3, 5, 2, R4 + new replicas R1, R6

Depending on your connection setting, you can either connect to one instance (transport client) or you could join the cluster (node client). With the node client you'll avoid double hops, since you'll always connect to the correct shard / index. With the transport client, your requests will be routed to the correct instance.

So there's nothing to load balance for yourself, you'd just add overhead. The auto-clustering is probably ES's greatest strength.

@xeraa那麼“節點”客戶端通過廣播或其他東西自動解析可用的彈性節點/集群? - Tuukka Mustonen 2014年9月15日11:25

這種方法的犧牲是,現在您的服務器拓撲上的客戶端直接依賴。而且,不,你不是“添加另一個網絡躍點”。您只是改變了域名解析的方式。除非你在談論甚至在客戶端配置中使用IP地址,在這種情況下......是的。 - GlenRSmith 2015年3月14日20:09

這不回答這個問題。這是關於ES如何在內部平衡數據的一個很好的文章,但是連接到ES集群的客戶端應用程序仍然需要一種機制,以便在node1失敗時切換到node2的IP。浮動IP或IP負載均衡器可以完成這項工作,但它不包含在ES中。 - 鹼性4月7日'16在4:01

+490

我相信負載平衡Elasticsearch集群是一個好主意(設計容錯系統,對單節點故障具有彈性。)

要構建您的集群,您需要有關Elasticsearch的兩個主要功能的背景知識:1。編寫和更新文檔以及2.查詢文檔。

在elasticsearch中編寫/索引文檔:

  1. 當新文檔進入Elasticsearch進行索引時,Elasticsearch使用“碎片路由算法”確定應將文檔分配給的“主分片”
  2. 與分片關聯的Lucene進程“映射”文檔中的字段;
  3. Lucene進程將文檔添加到shard的Lucene“倒排索引”
  4. 然後任何“複製碎片”都會收到該文件; 副本分片“映射”文檔並將文檔添加到副本分片的Lucene“倒排索引”

在Elasticsearch中查詢文檔:

  1. 默認情況下,當查詢發送到Elasticsearch時,查詢會命中一個節點 - 這將成為該查詢的“查詢節點”或“網關查詢節點”
  2. 節點將查詢廣播到索引中的每個分片(主要和副本)
  3. 每個分片對分片的本地Lucene倒排索引執行查詢。
  4. 每個分片返回前10 - 20個結果到“網關查詢節點”
  5. 然後,“網關查詢節點”對從其他分片返回的組合結果執行合併排序,
  6. 一旦合併排序完成,“網關查詢節點”並將結果返回給客戶端
    • merge-sort是CPU和內存資源很重

為寫入/索引/更新構建負載均衡器

Elasticsearch自我管理節點上的分片位置。“主節點”保持並更新“分片路由表”。“主節點”將分片路由表的副本提供給集群中的其他節點。

通常,您不希望主節點執行的操作遠遠超過群集的運行狀況檢查,更新路由表以及管理分片。

最好將負載均衡器指向“數據節點”(數據節點是包含數據=分片的節點)並讓數據節點使用其分片路由表來獲取對正確分片的寫入。

構建查詢

Elasticsearch創建了一個特殊的節點類型:“客戶端節點”,其中包含“無數據”,不能成為“主節點”。客戶端節點的功能是在查詢結束時執行最終資源重合併排序。

對於AWS,您可能使用c3或c4實例類型作為“客戶端節點”

最佳做法是將負載均衡器指向客戶機節點的查詢。

乾杯!

參考文獻:

  1. Elasticsearch節點類型
  2. Elasticsearch:分片路由算法
  3. Elasticsearch:Replica Shards
  4. Elasticsearch:群集狀態,即分片路由表
  5. ElasticHQ - Elasticsearch Video簡介
  6. Elasticsearch:分片數和群集縮放

感謝您的詳細報導! - 馬特2015年2月1日5:22

+110

您想要設計“故障轉移”是非常正確的,而在AWS中,我建議您這樣做。

1)限制群集中可以選為主節點的節點。其餘的,設置node.client:true。根據您有多少主可選節點選擇您可用於故障轉移的數量。

2)創建僅包含主可選節點的ELB。

3)在Route 53中,為您的群集創建一個CNAME,其值設置為ELB的DNS名稱。