Re: [sqlite] Helping with table definition?
Cesar D. Rodas wrote:
Hello,
Merry Christmas for every one!
I am wondering if someone did a function (the language doesn't care very
much) to get the table information and want to share him/her code.
Here's some Tcl code that may help. It does more than look at the SQL,
but that part may be of interest, too. It was written when my Tcl skills
were even weaker than they are now, so it won't win any style points,
but it has been working for years.
Merry Xmas,
Gerry
proc fillattachwin attachedname {
# Global data filled up here:
#arrays (with first index $attachedname, second index starting at 1,
matching line numbers) of:
#::GEB::tablename (names of the tables)
#::GEB::tableindex ("inverse" of ::GEB::tablename e.g.
$::GEB::tableindex(main.table1) = 1)
#::GEB::entrycount (# entries in each table),
#::GEB::fieldlist (lists of field names, indices starting at 0)
#::GEB::colnamelist (lists of column names, indices starting at 0)
#::GEB::coldeflist (lists of column def'ns--name+type, indices
starting at 0)
#::GEB::fieldcount (numbers of fields)
#::GEB::tablesql (sql that created the table)
# ::GEB::tablelist: table names (just names, not datatypes)
#::GEB::tablesarray($attachedname): array of tablelists
#::GEB::attachtablelist: $attachedname.$tablename -- (created in
fillattachwins)
#::GEB::attachednames: list of attachednames, starting with main
# ::GEB::tcltablelist($attachedname): list of tables in that file
with fields named tcl
wm title .$attachedname "SQLite $::GEB::attachfilename($attachedname)
as $attachedname"
set tables [sq eval "select sql from $attachedname.sqlite_master"]
set numtables 0
set ::GEB::tablelist ""
set ::GEB::fieldcount($attachedname,max) 0
foreach dbtable $tables {
regsub -all { +} $dbtable { } dbtable
if {[regexp -nocase "create table" $dbtable]} {
incr numtables
regsub -nocase {create table } $dbtable {} dbtable
scan $dbtable {%[^(]%[^!]} currentname
::GEB::tablesql($attachedname,$numtables)
set currentname [string trim $currentname]
set ::GEB::tablename($attachedname,$numtables)
$attachedname.$currentname
set ::GEB::tableindex($attachedname.$currentname) $numtables
lappend ::GEB::tablelist $::GEB::tablename($attachedname,$numtables)
set tablefields($numtables) [string range
$::GEB::tablesql($attachedname,$numtables) 1 end-1]
regsub -all {, } $tablefields($numtables) {,} tablefields($numtables)
set ::GEB::entrycount($attachedname,$numtables) "[sq eval "select
count(*) from $::GEB::tablename($attachedname,$numtables)"]"
set ::GEB::coldeflist($attachedname,$numtables) {}
foreach fldnm [split $tablefields($numtables) ,] {
lappend ::GEB::coldeflist($attachedname,$numtables) [string trim
$fldnm]
}
set ::GEB::fieldcount($attachedname,$numtables) [expr [regsub -all
, $tablefields($numtables) { } tablefields($numtables)] + 1]
if {$::GEB::fieldcount($attachedname,$numtables) >
$::GEB::fieldcount($attachedname,max)} {
set ::GEB::fieldcount($attachedname,max)
$::GEB::fieldcount($attachedname,$numtables)
}
}
}
array set ::GEB::fieldlist [array get ::GEB::coldeflist]
3
set ::GEB::colnamelist [firstwords ::GEB::coldeflist]
set ::GEB::tablesarray($attachedname) $::GEB::tablelist
set ::GEB::tcltablelist($attachedname) {}
foreach maybetcltablename $::GEB::tablelist {
if {[lsearch
$::GEB::fieldlist($attachedname,$::GEB::tableindex($maybetcltablename))
tcl ] > -1} {
lappend ::GEB::tcltablelist($attachedname) $maybetcltablename
}
}
# List the table names and other info in the main window
# While doing so, force a redraw
$::GEB::tablestktable($attachedname) clear cache
$::GEB::tablestktable($attachedname) configure -state normal -cols
[expr $::GEB::fieldcount($attachedname,max) + 2] \
-rows [expr $numtables + 1] -command [list GEB::readtktable4file %i
%r %c %s $attachedname] -cache 1 \
-drawmode single -bd 1 -ipadx 2 -titlerows 1 -titlecols 1 -anchor w
-browsecmd [list showtableattachwin %S $attachedname] \
-padx [$::GEB::tablestktable($attachedname) cget -padx]
$::GEB::tablestktable($attachedname) tag row headers 0
$::GEB::tablestktable($attachedname) tag configure headers -anchor center
$::GEB::tablestktable($attachedname) tag col entries 1
$::GEB::tablestktable($attachedname) tag configure entries -anchor e
}
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Helping with table definition?
This is code I used a while ago. Don't use it anymore as I have a better
way to do this via my VB wrapper. There are some lines that deal with code
in other parts of my application, but I take it you can see that.
In case you didn't know this is VB(A).
Function GetSQLiteTableInfo2(strDB As String, _
strTable As String, _
Optional strSelect As String, _
Optional strOmitFields As String, _
Optional strAlias As String, _
Optional strFields As String, _
Optional strDataTypes As String, _
Optional bCurrentConnection As Boolean) As
String()
'will produce the table fields as an 0-based 1-D array
'and make the strings:
'field1,field2,field3 etc.
'field1, field2, field3 etc.
'datatype1,datatype2,datatype3 etc.
'--
Dim r As Long
Dim c As Long
Dim strSQL As String
Dim arr
Dim arr2
Dim arr3
Dim strAlias2 As String
Dim arrOmitFields
Dim bOmit As Boolean
Dim bDoneFirst As Boolean
Dim lRows As Long
Dim strError As String
Dim lDBHandle As Long
10 If Len(strAlias) > 0 Then
20 strAlias2 = strAlias & "."
30 End If
40 If Len(strOmitFields) > 0 Then
50 arrOmitFields = Split(strOmitFields, ",")
60 bOmit = True
70 End If
80 If bShowErrors Then
90 On Error GoTo 0
100 Else
110 On Error GoTo ERROROUT
120 End If
130 If bCurrentConnection = False Then
140 OpenDB strDB
150 End If
160 strSQL = "pragma table_info('" & strTable & "')"
170 arr = GetFromDB(strSQL, lRows, strError, strDB)
180 If lRows = -1 Then
190 GoTo ERROROUT
200 End If
210 If bOmit Then
220 For c = 0 To UBound(arr)
230If ValueIn1DArray(CStr(c + 1), arrOmitFields) = -1 Then
240 If bDoneFirst = False Then
250 strSelect = strAlias2 & arr(c, 1)
260 strFields = arr(c, 1)
270 strDataTypes = arr(c, 2)
280 bDoneFirst = True
290 Else
300 strSelect = strSelect & ", " & strAlias2 & arr(c, 1)
310 strFields = strFields & "," & arr(c, 1)
320 strDataTypes = strDataTypes & "," & arr(c, 2)
330 End If
340End If
350 Next
360 Else
370 For c = 0 To UBound(arr)
380If c = 0 Then
390 strFields = arr(c, 1)
400 strSelect = strAlias2 & arr(c, 1)
410 strDataTypes = arr(c, 2)
420Else
430 strFields = strFields & "," & arr(c, 1)
440 strSelect = strSelect & ", " & strAlias2 & arr(c, 1)
450 strDataTypes = strDataTypes & "," & arr(c, 2)
460End If
470 Next
480 End If
490 arr2 = Split(strFields, ",")
500 ReDim arr3(0 To UBound(arr2)) As String
510 For r = 0 To UBound(arr2)
520 arr3(r) = arr2(r)
530 Next
540 GetSQLiteTableInfo2 = arr3
550 Exit Function
ERROROUT:
560 ReDim arr2(0 To 6) As String
570 arr2(0) = "-1"
580 arr2(1) = CStr(Err.Number)
590 arr2(2) = Err.Description
600 arr2(3) = CStr(Erl)
610 arr2(4) = strTable
620 arr2(5) = strOmitFields
630 arr2(6) = strAlias
650 GetSQLiteTableInfo2 = arr2
End Function
Have a nice Christmas as well.
RBS
-Original Message-
From: Cesar D. Rodas [mailto:[EMAIL PROTECTED]
Sent: 25 December 2007 01:35
To: sqlite-users@sqlite.org
Subject: [sqlite] Helping with table definition?
Hello,
Merry Christmas for every one!
I am wondering if someone did a function (the language doesn't care very
much) to get the table information and want to share him/her code.
I know that you can have the SQL definition of a table doing a "select *
from sqlite_master where type='table' ", but I need to parse SQL and
understand it.
Thanks in advance.
--
Best Regards
Cesar D. Rodas
http://www.cesarodas.com
http://www.thyphp.com
http://www.phpajax.org
Phone: +595-961-974165
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Helping with table definition?
Hi, I think you should look at "PRAGMA table_info(table-name);" instead: For each column in the named table, invoke the callback function once with information about that column, including the column name, data type, whether or not the column can be NULL, and the default value for the column. Ronny I am wondering if someone did a function (the language doesn't care very much) to get the table information and want to share him/her code. I know that you can have the SQL definition of a table doing a "select * from sqlite_master where type='table' ", but I need to parse SQL and understand it. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Helping with table definition?
Hello, Merry Christmas for every one! I am wondering if someone did a function (the language doesn't care very much) to get the table information and want to share him/her code. I know that you can have the SQL definition of a table doing a "select * from sqlite_master where type='table' ", but I need to parse SQL and understand it. Thanks in advance. -- Best Regards Cesar D. Rodas http://www.cesarodas.com http://www.thyphp.com http://www.phpajax.org Phone: +595-961-974165
