Hello. Very simple question. I think.

foreach i $columns {
blah blah blah,
create list of columns and types,
set eval [list of columns]
}

sqlwrite eval "CREATE TABLE $tablename($eval)"

I am really struggling with that last line. I've tried all kinds of syntax
and every one fails. It seems to me that Tcl interprets the form
$variable(parentheses) as an element of an array. I can only make
it work hard coding the table's name, which is not what I want.

What would the correct way for that be?

You want more code, don't you? Alright.

set datatypes {
Dirname text
Contains int
Data real
X text
Y int
Z real
}

set fe 0
foreach i $datatypes {
append eval " [lindex $datatypes $fe]"
incr fe
append eval " [lindex $datatypes $fe]"
if {[expr $fe + 1] < [llength $datatypes]} {
set eval "$eval,"
} else {set eval "$eval"}
incr fe
}

puts $eval

sqlite3 sqlwrite /path/to/file.db
set tablename "nicetable"
sqlwrite eval "CREATE TABLE $tablename($eval)"

--
Luc
>>

On Saturday, November 19, 2022 at 1:55:30 PM UTC+1, Luc wrote:
> Hello. Very simple question. I think.
>
> foreach i $columns {
> blah blah blah,
> create list of columns and types,
> set eval [list of columns]
> }
>
> sqlwrite eval "CREATE TABLE $tablename($eval)"
>
> I am really struggling with that last line. I've tried all kinds of syntax
> and every one fails. It seems to me that Tcl interprets the form
> $variable(parentheses) as an element of an array. I can only make
> it work hard coding the table's name, which is not what I want.
>
> What would the correct way for that be?
>
>
> You want more code, don't you? Alright.
>
> set datatypes {
> Dirname text
> Contains int
> Data real
> X text
> Y int
> Z real
> }
>
> set fe 0
> foreach i $datatypes {
> append eval " [lindex $datatypes $fe]"
> incr fe
> append eval " [lindex $datatypes $fe]"
> if {[expr $fe + 1] < [llength $datatypes]} {
> set eval "$eval,"
> } else {set eval "$eval"}
> incr fe
> }
>
> puts $eval
>
> sqlite3 sqlwrite /path/to/file.db
> set tablename "nicetable"
> sqlwrite eval "CREATE TABLE $tablename($eval)"
>
> --
> Luc
> >>
Better avoid eval as name of a variable, as eval is a Tcl command.
#one possible way without using lists could be
set description "name1 type 1, name2 type 2"
#then append description to tablename like
append tablename ( $description )
# note the 3 parameters to the append command
sqlwrite eval "CREATE TABLE $tablename"
# you probably want to create variable description from your list variable datatypes, to insert the required comma in SQL-syntax.

Roland Frank

On 11/19/22 06:55, Luc wrote:
> Hello. Very simple question. I think.
>
> foreach i $columns {
> blah blah blah,
> create list of columns and types,
> set eval [list of columns]
> }
>
> sqlwrite eval "CREATE TABLE $tablename($eval)"
>
> I am really struggling with that last line. I've tried all kinds of syntax
> and every one fails. It seems to me that Tcl interprets the form
> $variable(parentheses) as an element of an array. I can only make
> it work hard coding the table's name, which is not what I want.
>
> What would the correct way for that be?
>
>
> You want more code, don't you? Alright.
>
> set datatypes {
> Dirname text
> Contains int
> Data real
> X text
> Y int
> Z real
> }
>
> set fe 0
> foreach i $datatypes {
> append eval " [lindex $datatypes $fe]"
> incr fe
> append eval " [lindex $datatypes $fe]"
> if {[expr $fe + 1] < [llength $datatypes]} {
> set eval "$eval,"
> } else {set eval "$eval"}
> incr fe
> }
>
> puts $eval
>
> sqlite3 sqlwrite /path/to/file.db
> set tablename "nicetable"
> sqlwrite eval "CREATE TABLE $tablename($eval)"
>

Correct code:
set datatypes {
Dirname text
Contains int
Data real
X text
Y int
Z real
}
sqlite3 sqlwrite /path/to/file.db
set tablename "nicetable"
sqlwrite eval "CREATE TABLE $tablename ( [join $eval {,}] )"

--
+----------------------------------------------------------------------+
| Gerald W. Lester, President, KNG Consulting LLC |
| Email: Gerald.Lester@kng-consulting.net |
+----------------------------------------------------------------------+

On 11/19/2022 7:55 AM, Luc wrote:
>
> sqlwrite eval "CREATE TABLE $tablename($eval)"
>
> I am really struggling with that last line. I've tried all kinds of syntax

A couple of options: You can use space around the parentheses:
"$tablename ($eval)". Or you can escape them: "$tablename\($eval\)".

Luc <no@no.no> wrote:
> Hello. Very simple question. I think.
>
> foreach i $columns {
> blah blah blah,
> create list of columns and types,
> set eval [list of columns]
> }
>
> sqlwrite eval "CREATE TABLE $tablename($eval)"
>
> I am really struggling with that last line. I've tried all kinds of syntax
> and every one fails. It seems to me that Tcl interprets the form
> $variable(parentheses) as an element of an array. I can only make
> it work hard coding the table's name, which is not what I want.
>
> What would the correct way for that be?

Is there a reason you want to dynamically create SQL create table
statements?

Most often, the SQL table structure is pre-determined, and one's code
simply inserts/deletes/updates with the existing tables.

On Sat, 19 Nov 2022 10:01:21 -0600, Gerald Lester wrote:

> Correct code:
> sqlwrite eval "CREATE TABLE $tablename ( [join $eval {,}] )"

That works for the insertion of data, but not for 'create table.'
For 'create table' I need two words before each comma: the name of
the field and the data type.

On Sat, 19 Nov 22 16:28:06 GMT, clt.to.davebr@dfgh.net wrote:

> More ideas:
>
> # foreach can take more than one value from the list at a time
> foreach {name type} $datatypes {
> # first make a list of column definitions
> lappend colDefs "$name $type"

Oooh, I didn't know that. That's useful. I could have used it so many
times if I'd known that before.

Many other great tips there. Thank you all!

On Sat, 19 Nov 2022 19:08:30 -0000 (UTC), Rich wrote:

> Is there a reason you want to dynamically create SQL create table
> statements?
>
> Most often, the SQL table structure is pre-determined, and one's code
> simply inserts/deletes/updates with the existing tables.

Well, it's not pre-determined until I determine it. I am creating the
table from scratch. Since I make many mistakes, I didn't use sqlite
for some 15 years and have to learn everything again, it's definitely
better to automate the creation of the database, tables and everything.
Every time I screw up, the script rebuilds the test pad and I can
pick it up where I left off.

Again, thank you all for the tips. They really help.

--
Luc
>>

Luc <no@no.no> wrote:
> On Sat, 19 Nov 2022 10:01:21 -0600, Gerald Lester wrote:
>
>> Correct code:
>> sqlwrite eval "CREATE TABLE $tablename ( [join $eval {,}] )"
>
>
> That works for the insertion of data, but not for 'create table.'
> For 'create table' I need two words before each comma: the name of
> the field and the data type.
>
>
>
> On Sat, 19 Nov 22 16:28:06 GMT, clt.to.davebr@dfgh.net wrote:
>
>> More ideas:
>>
>> # foreach can take more than one value from the list at a time
>> foreach {name type} $datatypes {
>> # first make a list of column definitions
>> lappend colDefs "$name $type"
>
>
> Oooh, I didn't know that. That's useful. I could have used it so many
> times if I'd known that before.

It /is/ right there in the documentation:

SYNOPSIS
foreach varname list body
foreach varlist1 list1 ?varlist2 list2 ...? body

Note the second version.

And a bit further down in the manpage:

In the general case there can be more than one value list (e.g.,
list1 and list2), and each value list can be associated with a
list of loop variables (e.g., varlist1 and varlist2). During
each iteration of the loop the variables of each varlist are
assigned consecutive values from the corresponding list. Values
in each list are used in order from first to last, and each
value is used exactly once. The total number of loop iterations
is large enough to use up all the values from all the value
lists. If a value list does not contain enough elements for
each of its loop variables in each iteration, empty values are
used for the missing elements.

You can do all kinds of 'magic' with foreach.

> On Sat, 19 Nov 2022 19:08:30 -0000 (UTC), Rich wrote:
>
>> Is there a reason you want to dynamically create SQL create table
>> statements?
>>
>> Most often, the SQL table structure is pre-determined, and one's code
>> simply inserts/deletes/updates with the existing tables.
>
>
> Well, it's not pre-determined until I determine it. I am creating the
> table from scratch. Since I make many mistakes, I didn't use sqlite
> for some 15 years and have to learn everything again, it's definitely
> better to automate the creation of the database, tables and everything.
> Every time I screw up, the script rebuilds the test pad and I can
> pick it up where I left off.

By 'predetermined' I mean that in the most common instance, you know
the Sqlite DB will represent, say, an address book (this below will be
very US centric, but is meant as an example). That fact
'pre-determines' the table structure, so your code would do this to
'initialize' the database: (assuming "db" is the command already setup
to point to a file on disk):

db eval {create table addresses (first_name text, last_name text,
address1 text, address2 text,
city text, state text, zip text);}
db eval {create index zip_index on addresses(zip);} ;# assuming you
# planned to 'look up by zip code' often

No need to dynamically 'write' SQL create table statements, just write
out the full create table statement in full once up front and be done.

Then, later, you'd do things like:

db eval {select * from addresses where zip = '12345';}

to get all addresses in zipcode '12345'.

Or:

db eval {insert into addresses (first_name, last_name, address1,
city, state, zip) values (:first, :last, :addr, :city,
:state, :zip);}

To insert the contents of the tcl variables "first" "last" "addr"
"city" "state" "zip", visible in the current variable context, into a
new row in the database.

For 98% of most uses of SQL databases, there is no need to dynamically
write the create table statements (as in the code 'writes' the create
table statements as opposed to a person just 'writing it out in full up
front'). The other 2% is when one is doing something like building a
"SQL query interface app" for others to use to build databases -- and
you've not told us that is what you are trying to do yet.

On 11/19/22 10:01, Gerald Lester wrote:
> On 11/19/22 06:55, Luc wrote:
>> Hello. Very simple question. I think.
>>
>> foreach i $columns    {
>>     blah blah blah,
>>     create list of columns and types,
>>     set eval [list of columns]
>> }
>>
>> sqlwrite eval "CREATE TABLE $tablename($eval)"
>>
>> I am really struggling with that last line. I've tried all kinds of
>> syntax
>> and every one fails. It seems to me that Tcl interprets the form
>> $variable(parentheses) as an element of an array. I can only make
>> it work hard coding the table's name, which is not what I want.
>>
>> What would the correct way for that be?
>>
>>
>> You want more code, don't you? Alright.
>>
>> set datatypes {
>>     Dirname        text
>>     Contains    int
>>     Data        real
>>     X            text
>>     Y            int
>>     Z            real
>> }
>>
>> set fe 0
>> foreach i $datatypes    {
>>     append eval " [lindex $datatypes $fe]"
>>     incr fe
>>     append eval " [lindex $datatypes $fe]"
>>         if {[expr $fe + 1] < [llength $datatypes]}    {
>>             set eval "$eval,"
>>         } else {set eval "$eval"}
>>     incr fe
>> }
>>
>> puts $eval
>>
>> sqlite3 sqlwrite /path/to/file.db
>> set tablename "nicetable"
>> sqlwrite eval "CREATE TABLE $tablename($eval)"
>>
>
Correct code:
 set datatypes {
     {Dirname      text}
     {Contains    int}
     {Data        real}
     {X            text}
     {Y            int}
     {Z            real}
 }
 sqlite3 sqlwrite /path/to/file.db
 set tablename "nicetable"
 sqlwrite eval "CREATE TABLE $tablename ( [join $eval {,}] )"

--
+----------------------------------------------------------------------+
| Gerald W. Lester, President, KNG Consulting LLC |
| Email: Gerald.Lester@kng-consulting.net |
+----------------------------------------------------------------------+

On Sat, 19 Nov 2022 22:23:54 -0600, Gerald Lester wrote:

> Correct code:
>  set datatypes {
>      {Dirname      text}
>      {Contains    int}
>      {Data        real}
>      {X            text}
>      {Y            int}
>      {Z            real}
>  }
>  sqlite3 sqlwrite /path/to/file.db
>  set tablename "nicetable"
>  sqlwrite eval "CREATE TABLE $tablename ( [join $eval {,}] )"

Thank you again. I love syntatic sugar! And Tcl is the best for that.

Just for the sake of discussion, I noticed that the resulting $eval by
that approach preserves all the tabs I used for "drawing" a neat table
in the code. So the resulting $eval looks messy.

That doesn't matter for creating database tables, but in other
situations I would have to do something like this:

set eval [join $datatypes ","]
regsub -all {\s+} $eval { } eval
regsub -all {,} $eval {, } eval

Unless you have some other less obvious trick up your sleeve. :-)

--
Luc
>>

On 11/20/22 08:13, Luc wrote:
> On Sat, 19 Nov 2022 22:23:54 -0600, Gerald Lester wrote:
>
>> Correct code:
>>  set datatypes {
>>      {Dirname      text}
>>      {Contains    int}
>>      {Data        real}
>>      {X            text}
>>      {Y            int}
>>      {Z            real}
>>  }
>>  sqlite3 sqlwrite /path/to/file.db
>>  set tablename "nicetable"
>>  sqlwrite eval "CREATE TABLE $tablename ( [join $eval {,}] )"
>
>
> Thank you again. I love syntatic sugar! And Tcl is the best for that.
>
> Just for the sake of discussion, I noticed that the resulting $eval by
> that approach preserves all the tabs I used for "drawing" a neat table
> in the code. So the resulting $eval looks messy.
>
> That doesn't matter for creating database tables, but in other
> situations I would have to do something like this:
>
> set eval [join $datatypes ","]
> regsub -all {\s+} $eval { } eval
> regsub -all {,} $eval {, } eval
>
> Unless you have some other less obvious trick up your sleeve. :-)
>

I would never use regsub for that -- way overkill. String map is where
I'd go, but why bother?

--
+----------------------------------------------------------------------+
| Gerald W. Lester, President, KNG Consulting LLC |
| Email: Gerald.Lester@kng-consulting.net |
+----------------------------------------------------------------------+