annotate create_tj_db.tcl @ 613:ea6ebcf42b47

TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop XTRA user flags for storing data, we now employ a SQLite3 database. The database schema is probably not yet "final", so maybe beware of deploying this script yet. Also add example configuration file and script for creating the empty database tables.
author Matti Hamalainen <ccr@tnsp.org>
date Wed, 10 Feb 2021 11:12:13 +0200
parents
children 9596cd122639
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
613
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
1 #!/usr/bin/tclsh
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
2 # TCL script for creating (empty) TJ 2 SQLite3 database
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
3 #
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
4 # Written by Matti 'ccr' Hamalainen <ccr@tnsp.org>
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
5 # (C) Copyright 2021 Tecnic Software productions (TNSP)
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
6 #
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
7 package require sqlite3
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
8 source [file dirname [info script]]/utillib.tcl
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
9
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
10 set db_create_desc "Creates tables for tj.tcl in target SQLite3 file"
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
11 set db_table_list {tj}
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
12
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
13
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
14 proc db_create_table { dbh utable } {
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
15 set sch {}
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
16 lappend sch "id INTEGER PRIMARY KEY AUTOINCREMENT"
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
17
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
18 # username this item belongs to
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
19 lappend sch "uuser VARCHAR(32)"
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
20
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
21 # created timestamp
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
22 lappend sch "ucreated INT"
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
23
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
24 # description
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
25 lappend sch "udesc VARCHAR(1024)"
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
26
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
27 # 0 = TJ, 1 = one-time reminder, 2 = repeat reminder
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
28 lappend sch "utype INT"
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
29
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
30 # unique identifier for type 0
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
31 lappend sch "uid VARCHAR(32)"
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
32
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
33 # target date/time
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
34 lappend sch "utarget DATETIME"
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
35
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
36 utl_create_table dbh "$utable" [join $sch ","]
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
37 }
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
38
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
39 proc db_drop_table { dbh utable } {
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
40 utl_drop_table dbh "$utable"
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
41 }
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
42
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
43 proc db_get_table_list { utable } {
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
44 return [list $utable]
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
45 }
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
46
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
47
ea6ebcf42b47 TJ: Initial commit of the TJ2.0 refactoring effort. Instead of using Eggdrop
Matti Hamalainen <ccr@tnsp.org>
parents:
diff changeset
48 source [file dirname [info script]]/utilcreate.tcl