conn=.ooSQLiteConnection~new('patients.db')
say "conn:" conn
say "trying to recreate database..."
res=conn~exec(.resources~createPersonsTable~toString)
say "res:" res
say
stmt=.ooSQLiteStmt~new(conn, "select svnr, fName, lName from persons order by lName, fName;")
do counter i while stmt~step=stmt~row
   say '#' i~right(2)":" stmt~columnText(1)~left(10,'.') stmt~columnText(3)"," stmt~columnText(2)
end
conn~close


::requires 'ooSQLite.cls'

::resource createPersonsTable
   DROP TABLE IF EXISTS persons;

   CREATE TABLE persons (
       fName text,
       lName text,
       gender text,
       svnr int PRIMARY KEY,
       birthday text,
       allergy text,
       drugs text,
       mail text,
       phone text,
       street text,
       house_number int,
       address_additional text,
       zip int,
       city text,
       next_appointment text
       );

   INSERT INTO persons VALUES('Thomas', 'KAHR', 'male', 2084, '17.02.1983', 'no', 'ibuprofen', 'thomas@vaime.at', '067763700355', 'jueptnergasse', 18, 'text', 1220, 'vienna', '2020-04-30,1300' ) ;
   INSERT INTO persons VALUES('Tanja', 'MATEJEVIC', 'female', 1234, '10.01.1995', 'no', 'no', 'h0192@s.wu.ac.at', '0664123456789', 'testgasse', 1, 'text', 1130, 'vienna', '2020-05-30,1200' ) ;
   INSERT INTO persons VALUES('Stefan', 'ZIMMERMANN', 'male', 5031, '15.02.1973', 'yes', 'ibuprofen', 'zimmermann@hotmail.at', '06766758467', 'Ringstraße', 34, 'text', 1230, 'Vienna', '2020-04-26 13:50' ) ;
   INSERT INTO persons VALUES('Anna', 'ZIMMERMANN', 'female', 5065, '01.09.1973', 'yes', 'parkemed', 'aw2021s@alice.wu-wien.ac.at', '06608758467', 'Leopoldgasse', 14, 'text', 1210, 'Vienna', '2020-04-26 13:50' ) ;
   INSERT INTO persons VALUES('Herbert', 'MUELLER', 'male', 4567, '04.02.1963', 'yes', 'ibuprofen, parkemed, antibiotika', 'mueller@hotmail.at', '06766673457', 'Leopoldgasse', 14, 'text', 1210, 'Vienna', '2020-03-15 10:33' ) ;
   INSERT INTO persons VALUES('Sara', 'BERGER', 'female', 7834, '30.07.1998', 'no', 'keine', 'berger@hotmail.at', '06605632111', 'Jupitergasse', 123, 'text', 1220, 'Vienna', '2021-01-22 11:03' ) ;
   INSERT INTO persons VALUES('Lena', 'BAUER', 'female', 4066, '14.03.2001', 'no', 'parkemed', 'bauer.lena@gmail.at', '06601345222', 'Ferdinand-Platz', 3, 'text', 1030, 'Vienna', '2019-11-10 17:48' ) ;
   INSERT INTO persons VALUES('Franz', 'WIESER', 'male', 8873, '26.09.1984', 'yes', 'parkemed', 'wieser@hotmail.com', '06766675743', 'Steinergasse', 56, 'text', 1190, 'Vienna', '2021-03-17 15:09' ) ;
   INSERT INTO persons VALUES('Josef', 'LANG', 'male', 5411, '25.06.1996', 'yes', 'antibiotika', 'j.lang@gmail.at', '0687956435', 'Hammerling Platz', 15, 'text', 1230, 'Vienna', '2021-04-27 08:57' ) ;
::END