-- Initialize Query for SQLite
-- WindyHanas Solanara:SQLite - http://www.solanara.net/solanara/sqlite
select 'SQLite ' || sqlite_version();
.print ""
.print "CREATE TABLE, VIEW"
create table if not exists authinfo (
        seq integer
                constraint authinfo_seq_pk primary key autoincrement,
        userid   varchar(255)
                constraint authinfo_userid_nn not null
                constraint authinfo_userid_uq unique,
        pswd     char(32)
            constraint authinfo_pswd_nn not null,
        salt     char(4)
            constraint authinfo_salt_nn not null,
        ldate    datetime default current_timestamp
            constraint authinfo_ldate_nn not null,
        sdate    date default current_timestamp
                constraint authinfo_sdate_nn not null,
        pdate    datetime default current_timestamp
            constraint authinfo_pdate_nn not null
);
create table if not exists userinfo (
        seq integer
                constraint userinfo_seq_pk primary key
                constraint userinfo_seq_fk references authinfo (seq),
        username varchar(255)
                constraint userinfo_username_nn not null,
        tel1     varchar(255),
        tel2     varchar(255),
        grade    real default 1.0,
        desc     text,
        photo    blob,
        rdate    datetime default current_timestamp
                constraint userinfo_rdate_nn not null
);
create index if not exists authinfo_sdate_ix on authinfo (sdate);
drop view if exists userlist;
create view if not exists userlist as select userid, username, tel1, grade, datetime(rdate, 'localtime') rdate from authinfo a inner join userinfo u using (seq);
drop view if exists userjoinstat;
create view if not exists userjoinstat as select datetime(sdate, 'localtime') sdate, count(a.seq) cnt from authinfo a indexed by authinfo_sdate_ix group by sdate;
begin transaction;
insert into authinfo (userid, pswd, salt) values ('windy', '1111', 'SQLT');
insert into userinfo (seq, username, tel1) values (1, '윈디하나', '010-000-0000');
insert into authinfo (userid, pswd, salt) values ('root', '1111', 'SQLT');
insert into userinfo (seq, username, tel1) values (2, '루트', '010-111-1111');
commit;
.print ""
.print "ANALYZE"
analyze authinfo;
analyze userinfo;
.print ""
explain query plan select * from userlist where userid = 'windy';
.print ""
.print "SELECT USERLIST"
select * from userlist;
.print ""
.print "SELECT USERJOINSTAT"
select * from userjoinstat;
.print ""
.print "SELECT DBSTAT"
select * from dbstat;