Testirati SQL u serveru/klijentu i popisati nedostatke

Ovaj odgovor se proteže kroz 4 odlomka:

1. Popis naredbi koje su "implementirane" i trenutno dostupne u sučelju klijenta
1. Testiranje naredbi koje su implementirane i trenutno dostupne u sučelju klijenta
1. Popis naredbi / fukcionalnosti koje su implementirane na strani C koda a nisu u Pythonu
1. Popis standardnih SQL naredbi koje dosad nisu obuhvaćene niti jednim dijelom

I. Popis naredbi koje su "implementirane" i trenutno dostupne u sučelju klijenta su:

\q - Disconnect from the server and quit akdb client. \? - List all commands supported in akdb. _\d - Prints out table details \p - Prints out table \t ?_ - Check whether the given table exists in database or not.
_create index _ - Create index create user \<username> with password \<password> - Create user _create trigger _ - Create trigger _create sequence _ - Creating sequence in AK_sequence table _create table _ - Create table _create view as - Create view (Nedostaje implementacija :warning:)_ _create function returns - Create function (Nedostaje implementacija :warning:)_ _alter index - Alter index (Nedostaje implementacija :warning:)_ _alter table - Alter table (Nedostaje implementacija :warning:)_ _alter sequence - Alter sequence (Nedostaje implementacija :warning:)_ _alter view - Alter view (Nedostaje implementacija :warning:)_ _select _ - Select data from table
_update _ - Update table _insert into _ - Insert command
_grant _ - Grant privilege _drop _ - Drop sql object _begin commit - Transaction (Nedostaje implementacija :warning:)_ history - List all previously typed commands.

II. Testiranje naredbi koje su implementirane i trenutno dostupne u sučelju klijenta

1) \q - Ispravno radi :heavy_check_mark:

2) \? - Ispravno radi :heavy_check_mark:

3) _\d _ - Uzrokuje crash servera :x: -> :heavy_check_mark: AttributeError: Table_details_command instance has no attribute 'execute'

Uzork greske je bio atribut 'execute' definiran kao 'execute1' u skripti _sqlexecutor.py. Ispravljanjem i testiranjem toga server više ne pada međutim za bilo koju tablicu javlja broj atributa i zapisa -2 (stavljeno na granu 'jakov-issue176') \d jakov Number of attributes: -2 Number of records: -2

4) _\p _ - Ne radi :x:

Klijent vraća poruku OK dok server ispisuje poruku Table jakov does not exist! da prethodno kreirana tablica ne postoji.
U kodu se koristi _ak47.ak_table_exists(tablename) za koju postoje komentari u susjednim metodama da iz nekog razloga ne radi. Primjer: ''' For some reason, AK_table_exists won't work, it always just exits here, so it's commented out. '''

5) _\t ?_ - Ne radi :x:

Nakon kreiranja tablice, klijent javlja da tablica ne postoji u bazi podataka. akdb>create table jakov(name varchar(30)) Table created akdb>\t jakov? Table does not exist.

6) _create index _ - Ne radi :x:

Dostupna dva komentara u kodu: 1) For some reason, AK_table_exists won't work, it always just exits here, so it's commented out. 2) Not working - typeError: in method 'AK_create_Index', argument 2 of type 'AK_list*'

7) create user \<username> with password \<password> - Ne radi :x: akdb>create user myuser with password jakov Wrong command.

Dostupan komentar uz zakomentirane blokove koda vezane uz ovu operaciju: # User insert does not work. Password needs to be inserted in hash format.

8) _create trigger _ - Ne radi :x:

Dostupni komentari u kodu: 1) For some reason, AK_table_exists won't work, it always just exits here, so it's commented out. 2) Not working - TypeError: in method 'AK_trigger_add, argument 3 of type 'AK_list*'

9) _create sequence _ - Ne radi, uzrokuje crash servera :x:

Dostupni komentari u kodu: 1) needed revision in sequence.c in function AK_sequence_add which receives only int values but posible is also bigint which is default for undefined values 2) isinstance needs revision for swig 3) Needs more revision for swig after buffer overflow is handled

10) _create table _ - Ne može se potvrditi ispravnost implementacije naredbe jer funkcija za provjeru da li tablica postoji ne radi :warning: akdb>create table tablica(redak varchar(30)) Table created akdb>\t tablica? Table does not exist.

Dostupni komentari u kodu: 1) For some reason, AK_table_exists won't work, it always just exits here, so it's commented out. 2) Create table in table.c currently takes only name and type of attributes. Parsing works for other attribute properties as well, so it should be added here when possible.

11) _create view as _ - Ne radi :x: , _sqlexecutor.py nema apsolutno nikakvu implementaciju za CREATE VIEW naredbu :question: akdb>create view comedies as select * from films where kind = 'comedy' Wrong command.

_create function returns _ - Ne radi :x: , _sqlexecutor.py nema apsolutno nikakvu implementaciju za CREATE FUNCTION naredbu :question: create function add(integer, integer) returns integer as 'select $1 + $2;' language sql returns null on null input Wrong command.

12) _alter index _ - Ne radi :x: , _sqlexecutor.py nema apsolutno nikakvu implementaciju za ALTER INDEX naredbu :question: create index idx_name on jakov(name) Wrong command. alter index idx_name rename to idx_test Wrong command.

13) _alter table _ - Ne radi :x: , _sqlexecutor.py nema apsolutno nikakvu implementaciju za ALTER TABLE naredbu :question:

14) _alter sequence _ - Ne radi :x: , _sqlexecutor.py nema apsolutno nikakvu implementaciju za ALTER SEQUENCE naredbu :question:

15) _alter view _ - Ne radi :x: , _sqlexecutor.py nema apsolutno nikakvu implementaciju za ALTER VIEW naredbu :question:

16) _select _ - Ne radi :x:

Dostupan komentar u kodu: ALERT translate expression from infix to postfix add rest of akdb types to 'get_attr_type()' such as TYPE_ATTRIBS, TYPE_OPERAND, TYPE_OPERATOR, etc. fix 'selection_test()' -> 'AK_selection' --> it selects all (*) data instead of taking 'selected attributes' in account similar problems also found with WHERE clause Justin Case check select tokenizer/executor logic Warning: contains HC-ed data for testing purpose

17) _update _ - Ne radi :x:

Dostupan komentar u kodu: ALERT reimplementation of 'AK_update[_row] for simplicity !? 'AK_update_row' returns valid answer (True/EXIT_SUCCESS) although change isn't visible -> possible problems with update tokenizer/executor logic or AK_update_row implementation

18) _insert into _ - Ne može se potvrditi ispravnost implementacije naredbe jer funkcija za provjeru da li tablica postoji ne radi :warning: akdb>create table jakov2(ime varchar(30)) Table created akdb>insert into jakov2 values ('mojeime') Wrong command. akdb>insert into jakov2(ime) values ('mojeime') Wrong command.

19) _grant _ - Ne može se potvrditi ispravnost implementacije naredbe jer funkcija za provjeru da li tablica postoji ne radi :warning:

Primjer: 1) Klijent: akdb>create table novcic(boja varchar(30)) Table created akdb>grant insert on novcic to public GRANT -1

2) Server: 2019-04-17 00:08:18,065 - INFO - create table novcic(boja varchar(30)) . . . 2019-04-17 00:08:31,654 - INFO - grant insert on novcic to public . . . Printing out : ` Invalid table name or username! ERROR: User or table does not exsist: publicnovcic`

20) _drop _ - Naredba uzrokuje crash servera. :x: Ne može se potvrditi ispravnost implementacije naredbe jer funkcija za provjeru da li tablica postoji ne radi :warning:

Testirano: 1) Klijent akdb>create table jakov22(redak varchar(20)) Table created akdb>drop table jakov22 ` akdb>` 2) Server *crashes*

21) _begin commit_ - Ne radi :x: , _sqlexecutor.py nema apsolutno nikakvu implementaciju za BEGIN naredbu :question:

22) history - Ispravno radi :heavy_check_mark:

Primjer: akdb>\? Commands: . . . akdb>\t jakov? Table does not exist. akdb>create table pogledajmeuhistoryu(hehe varchar(69)) Table created akdb>history ['\\?', '\\t jakov?', 'create table pogledajmeuhistoryu(hehe varchar(69))', 'history'] 'akdb>'

III. Popis naredbi / fukcionalnosti koje su implementirane na strani C koda a nisu u Pythonu

*Poseban slučaj* create group command ima svoju implementaciju na strani _sqlexecutor.py koda ali je cijela zakomentirana

Nema nikakvih komentara uz metodu zašto je to tako i ako postoje problemi koji su to.

1) #### Popis naredbi koje pišu da su implementirane u Python katalogu ali zapravno im nedostaje implementacija: ####

_create view as - Create view (Nedostaje implementacija :warning:)_ _create function returns - Create function (Nedostaje implementacija :warning:)_ _alter index - Alter index (Nedostaje implementacija :warning:)_ _alter table - Alter table (Nedostaje implementacija :warning:)_ _alter sequence - Alter sequence (Nedostaje implementacija :warning:)_ _alter view - Alter view (Nedostaje implementacija :warning:)_ _begin commit - Transaction (Nedostaje implementacija :warning:)_

2) #### Popis naredbi / fukcionalnosti koje su implementirane na strani C koda a nisu u Pythonu: ####

1) _int Ak_deleterow()

Funkcija koja briše retke iz tablice i DELETE funkcionalnost općenito sa srodnim funkcijama poput _int AK_group_remove_byname()

2) _int AK_function_change_returntype() , _int AK_funtionrename() , ...

Odnosni se na sve srodne funkcije koje postoje u kodu uz ove, a pomogle bi u ostvarivanju ALTER FUNCTION funkcionalnosti

3) _int AK_userrename() , _int AK_revoke_priviledgeuser()

Odnosi se na sve srodne funkcije koje postoje u kodu uz ove, a pomogle bi u ostvarivanju ALTER USER funkcionalnosti

4) _int AK_revoke_privilegegroup()

Odnosi se na sve srodne funkcije koje postoje u kodu uz ovu, a pomogle bi u ostvarivanju ALTER GROUP fukcionalnosti

5) _int AK_checkprivilege() , _int AK_check_userprivilege(), ...

Odnosi se na sve srodne funkcije koje postoje u kodu uz ovu, a pomogle bi u ostvarivanju funkcionalnosti ISPISA KORISNIKA, GRUPA, po privilegijima i slično

6) _int AK_triggeredit() , _int AK_triggerrename() ...

Odnosi se na sve srodne funkcije koje postoje u kodu uz ove, a pomogle bi u ostvarivanju funkcionalnosti ALTER TRIGGER

IV. Popis standardnih SQL naredbi koje dosad nisu obuhvaćene niti jednim dijelom:

U nastavku slijedi popis funkcionalnosti odnosno SQL naredbi koje nisu dosada nikako implementirane

1) ABORT - abort the current transaction 2) CREATE DATABASE - create a new database 3) ALTER DATABASE - change a database 4) ANALYZE - collect statistics about a database 5) CHECKPOINT - force a transaction log checkpoint 6) CLUSTER - cluster a table according to an index 7) COMMENT - define or change the comment of an object 8) COPY - copy data between files and tables 9) CREATE DOMAIN - define a new domain 10) DROP DOMAIN - remove a user-defined domain 11) LOCK - explicitly lock a table 12) TRUNCATE - empty a table 13) VACUUM - garbage-collect and optionally analyze a database

STANJE NA 27.05.2020. 15:33 ------------------------------------------

Nakon posljednjeg pull requesta više nije moguća prijava s podacima "testingUser" i "testingPass" unutar klijenta. Potrebno je napraviti promjenu kod metode check_auth_password(self, username, password) u server_functions.py.

Uz to je još potrebno i popraviti metodu recv_data(self) u akdb_cli.py. Ona trenutno ništa ne vraća (nema naredbu return), a njezin rezultat je potreban za daljnji rad.

I. Popis naredbi koje se provjeravaju (u sql_executor.py) i zatim pokušavaju izvršiti kada ih se upiše unutar klijenta:

\p - Print out a table \ps \d - Print out table details \t - Check whether a given table exists in a database

create sequence - Create a sequence create table - Create a table create index - Create an index create trigger - Create a trigger insert into - Insert a data into a table grant - Grant a privilege select - Select a data from a table update - Update a table drop - Drop a SQL object

II. Popis naredbi koje bi bilo dobro implementirati u Python dijelu (sql_executor.py):

create user with password - Create a user create group with password - Create a group (postoji u sql_executor.py, ali je unutar komentara) create view as - Create a view create function returns - Create a function alter index - Alter an index alter table - Alter a table alter sequence - Alter a sequence alter view - Alter a view begin commit - Transactions history - List all previously typed commands \q - Disconnect from the server and quit the akdb client \? - List all commands supported in the akdb

III. Testiranje naredbi unutar klijenta:

akdb> create table proba(id int primary key) - [-] Failed command execution: execute() takes exactly 1 argument (2 given) (ne radi)

Napomena: Kako ne radi naredba za kreiranje tablice, automatski nećemo vidjeti niti rezultate ostalih naredbi koje se vežu na tu tablicu.

akdb> \p proba - [-] Failed command execution: execute() takes exactly 1 argument (2 given) (ne radi) akdb> \d proba - [-] Failed command execution: in method 'AK_num_attr', argument 1 of type 'char *' (ne radi) akdb> \t proba - There was a problem. Error message: Error. Wrong command: \t proba (ne radi) akdb> insert into proba values(1) - There was a problem. Error message: Error. Wrong command: insert into proba values(1) (ne radi) akdb> history - There was a problem. Error message: Error. Wrong command: history* (ne radi) akdb> select from proba - There was a problem. Error message: Error. Wrong command: select * from proba (ne radi) akdb> drop table proba - [-] Failed command execution: execute() takes exactly 1 argument (2 given) (ne radi) akdb> grant update on proba to public - [-] Failed command execution: execute() takes exactly 1 argument (2 given) (ne radi) akdb> update proba set id = 2 where id = 1 - There was a problem. Error message: Error. Wrong command: update proba set id = 2 where id = 1 (ne radi) akdb> create index najbolji_indeks on proba(id) - There was a problem. Error message: Error. Wrong command: ON table_name (column1create index najbolji_indeks on proba(id)** (ne radi)

create trigger (ne radi) create sequence (ne radi)

IV. Testiranje naredbi dostupnih unutar servera:

Za vrijeme izvođenja programa server.py, u terminalu je moguće koristiti sljedeće dvije naredbe:

stop - [-] Server closed (radi) start - [-] Server resumed (radi)

V. Naredbe koje su implementirane na strani C-a (bilo u obliku konkretne naredbe, bilo u obliku funkcionalnosti koja se može upotrijebiti), a nisu u Pythonu:

int AK_delete_row() int AK_function_change_return_type(), int AK_function_rename(), ... int AK_user_rename(), int AK_revoke_privilege_user() int AK_revoke_privilege_group() int AK_check_privilege(), int AK_check_user_privilege(), ... int AK_trigger_edit(), int AK_trigger_rename(), ...

VI. Standardne SQL naredbe koje nisu obuhvaćene ni prvim ni drugim dijelom:

abort - abort the current transaction create database - create a new database alter database - change a database analyze - collect statistics about a database checkpoint - force a transaction log checkpoint cluster - cluster a table according to an index comment - define or change a comment of an object copy - copy a data between files and tables create domain - define a new domain drop domain - remove a user-defined domain lock - explicitly lock a table truncate - empty a table vacuum - garbage-collect and optionally analyze a database

VII. Testovi

akdb> testme - Izvrše se svi testovi koji se nalaze u datoteci tests.py (19 testova). Ima grešaka koje je potrebno ispraviti

AILab-FOI / akdb