Re: [sqlite] Elegant printout of table (with header) via TCL (solved)
On 3 Mar 2012, rod wrote:
On 2/9/12, rod wrote:
> My apologies if this question should be directed to some other list.
>
> I'm looking for a better way to printout the:
> Column Headers,
> followed by the table contents -
> in comma separated value (tsv) format
> from a SELECT statement:
>
>
> So far I have two solutions, neither seems elegant enough.
>
> The second solution makes use of a test of count
I'm excited, I may have solved my problem. By setting a trace I am
able to reset a variable called counter to -0- every time a new sql
statment is compiled. In addition I also save the sql statement to
be evaluated to a variable called ::last_sql via:
# this trace is executed at the end of the compiling of an sql statement
sql trace {set ::count 0; set ::last_sql }
Then this proc will print the headers if ::count=0 (ie only prior to
processing the the first row of the select results.
proc fqry {row {sep "\t"} {header 1}} {
# you can't pass the value in the array to the proc
# you need to upvar it to another var
upvar $row g_row
set val {}
set col {}
set col_name {}
foreach {col_name } $g_row(*) {
lappend val $g_row($col_name)
lappend col $col_name
set len [string length $col_name]
set dash [string repeat - $len]
lappend hd $dash
}
#always prints the vals; only print headers and "---"'s if counter==0
if {$::count==0 && $header } {puts [join $col $sep]; puts $hd}
puts [join $val $sep]
incr ::count
}
Usage:
%sql eval "Select col2, col4, col6 as Results from table 1" q_array
{fqry q_array}
Q) Does this seem like the way to accomplish this??? Are there other
typical uses of trace that I should be aware of prior to expanding on
this solution.
Personally, your first methods seemed just as good, and no more
inelegant. When you posted earlier, I looked for a way, using the TCL
interface, to get the column names once SQLite has compiled the
statement but before it starts fetching rows. The column names are
available then, and it solves the problem that I am about to pose.
Unfortunately, the TCL interface provides no access, as now written.
What I find very unsatisfactory about both of your solutions, and about
anything that looks reasonably doable through the present TCL interface,
is that if the result set is empty, no header will be emitted. I find
that for things I do, the possibility of an empty table cannot be
precluded. Nevertheless, the process that uses the table still has to
"query" it, unless there is special logic to see that it is missing or
completely empty (including the header). Such special-casing is very
distasteful to programmers who think in terms of sets.
This issue is what has led me to create a variant of the SQLite3 shell
that has no more trouble doing the "right" header output when the result
set is empty than when there is at least one row (that would have
sufficed to trigger header emission with the original code).
Best regards,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Elegant printout of table (with header) via TCL (solved)
On 2/9/12, rod wrote:
> My apologies if this question should be directed to some other list.
>
> I'm looking for a better way to printout the:
> Column Headers,
> followed by the table contents -
> in comma separated value (tsv) format
> from a SELECT statement:
>
>
> So far I have two solutions, neither seems elegant enough.
>
> The second solution makes use of a test of count
I'm excited, I may have solved my problem. By setting a trace I am
able to reset a variable called counter to -0- every time a new sql
statment is compiled. In addition I also save the sql statement to
be evaluated to a variable called ::last_sql via:
# this trace is executed at the end of the compiling of an sql statement
sql trace {set ::count 0; set ::last_sql }
Then this proc will print the headers if ::count=0 (ie only prior to
processing the the first row of the select results.
proc fqry {row {sep "\t"} {header 1}} {
# you can't pass the value in the array to the proc
# you need to upvar it to another var
upvar $row g_row
set val {}
set col {}
set col_name {}
foreach {col_name } $g_row(*) {
lappend val $g_row($col_name)
lappend col $col_name
set len [string length $col_name]
set dash [string repeat - $len]
lappend hd $dash
}
#always prints the vals; only print headers and "---"'s if counter==0
if {$::count==0 && $header } {puts [join $col $sep]; puts $hd}
puts [join $val $sep]
incr ::count
}
Usage:
%sql eval "Select col2, col4, col6 as Results from table 1" q_array
{fqry q_array}
Q) Does this seem like the way to accomplish this??? Are there other
typical uses of trace that I should be aware of prior to expanding on
this solution.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/3 Christoph P.U. Kukulies : > I'm building a unique ID made up from the basename, md5sum and > size of the file. This results > as a TEXT PRIMARY KEY (e.g. filename_md5sum_size). > Can I use (I'm using System.Data.SQLite) a try clause to find out whether > the entry is already there? > > I think this can be faster than asking the database by an SQL statement, > whether the KEY is already there. > Christoph Normalize. One cell, one atomic information. You may use md5sum as "text primary key" or you may define PRIMARY KEY(filename,md5sum,size). Filename, version, date and size put to another table with md5sum as a foreign key. Inspire with Git system or use it. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
On 03 Mar 2012 at 15:57, Mark Schonewille wrote: > Could you just tell me where I find a good on-line manual? Thanks. I'm not aware of any similar to the mysql one - but perhaps I haven't looked particularly. But you didn't answer my question about the diagrams, or indeed whether my short example clarified matters. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
On Mar 3, 2012, at 4:57 PM, Mark Schonewille wrote: > Could you just tell me where I find a good on-line manual? As this is a pro bono project, you are not entitled to anything aside from the kindness of strangers. Don't waste it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
On Mar 3, 2012, at 4:15 PM, Mark Schonewille wrote: > How do I read diagrammes like http://sqlite.org/images/syntax/sql-stmt.gif ? It's a called a syntax diagram: http://en.wikipedia.org/wiki/Syntax_diagram > Where can I find a good reference manual? http://www.sqlite.org/lang.html Additionally, there are several books related to SQLite: http://www.sqlite.org/books.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
On 03-03-2012 17:12, Luuk wrote: > On 03-03-2012 17:08, Mark Schonewille wrote: >> Luuk, >> >> You're invited to write a good, simple explanation as to how to read those >> diagrams. >> >> -- > > Can you explain which part it is, you don't understand? or take a look at: http://en.wikipedia.org/wiki/Syntax_diagram ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
Follow the arrow... Noël Le 3/03/2012 17:08, Mark Schonewille a écrit : Luuk, You're invited to write a good, simple explanation as to how to read those diagrams. -- Best regards, Mark Schonewille ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
On 03-03-2012 17:08, Mark Schonewille wrote: > Luuk, > > You're invited to write a good, simple explanation as to how to read those > diagrams. > > -- Can you explain which part it is, you don't understand? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
Luuk, You're invited to write a good, simple explanation as to how to read those diagrams. -- Best regards, Mark Schonewille Economy-x-Talk Consulting and Software Engineering Homepage: http://economy-x-talk.com Twitter: http://twitter.com/xtalkprogrammer KvK: 50277553 Download the Installer Maker Plugin 1.7 for LiveCode here http://qery.us/za >> > > i dont need "university level computer science" to understand it. ;) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
Tim, Could you just tell me where I find a good on-line manual? Thanks. -- Best regards, Mark Schonewille Economy-x-Talk Consulting and Software Engineering Homepage: http://economy-x-talk.com Twitter: http://twitter.com/xtalkprogrammer KvK: 50277553 Download the Installer Maker Plugin 1.7 for LiveCode here http://qery.us/za On 3 mrt 2012, at 16:43, Tim Streater wrote: > > Do you mean that you find all the diagrams incomprehensible, or just some? > Take this one: > > http://www.sqlite.org/lang_expr.html#litvalue > > as an example. It is a pictorial way of describing how to form an > SQLite-legal numeric value. Following the pathways in the diagram, one can > see that 4, 12.8e-7, 0.6, are all legal numerics. Interestingly, +3 and -66 > appear not to be. > >> Where can I find a good reference manual just like MySQL's? > > There are books available, some even written by denizens of this list, such > as "Using SQLite" by Jay Kreibich. > > -- > Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
On 03-03-2012 16:43, Tim Streater wrote: > On 03 Mar 2012 at 15:15, Mark Schonewille > wrote: > >> MySQL has a really great manual with extensive explanations and dozens of >> examples for each command. SQLite is none of all this. It just has a limited >> number of pages listing a small number of commands and some special features. >> It also has a number of really weird diagrammes. >> >> How do I read diagrammes like http://sqlite.org/images/syntax/sql-stmt.gif ? > > Do you mean that you find all the diagrams incomprehensible, or just some? > Take this one: > >http://www.sqlite.org/lang_expr.html#litvalue > > as an example. It is a pictorial way of describing how to form an > SQLite-legal numeric value. Following the pathways in the diagram, one can > see that 4, 12.8e-7, 0.6, are all legal numerics. Interestingly, +3 and -66 > appear not to be. > Because they are signed-number: http://www.sqlite.org/lang_expr.html#signed-number ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
On 03 Mar 2012 at 15:15, Mark Schonewille wrote: > MySQL has a really great manual with extensive explanations and dozens of > examples for each command. SQLite is none of all this. It just has a limited > number of pages listing a small number of commands and some special features. > It also has a number of really weird diagrammes. > > How do I read diagrammes like http://sqlite.org/images/syntax/sql-stmt.gif ? Do you mean that you find all the diagrams incomprehensible, or just some? Take this one: http://www.sqlite.org/lang_expr.html#litvalue as an example. It is a pictorial way of describing how to form an SQLite-legal numeric value. Following the pathways in the diagram, one can see that 4, 12.8e-7, 0.6, are all legal numerics. Interestingly, +3 and -66 appear not to be. > Where can I find a good reference manual just like MySQL's? There are books available, some even written by denizens of this list, such as "Using SQLite" by Jay Kreibich. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
John, Every diagram needs instructions about how to read it. There are no such instructions on the SQLite site. So, my question remains, where do I find a good reference manual like that of MySQL? -- Best regards, Mark Schonewille Economy-x-Talk Consulting and Software Engineering Homepage: http://economy-x-talk.com Twitter: http://twitter.com/xtalkprogrammer KvK: 50277553 Download the Installer Maker Plugin 1.7 for LiveCode here http://qery.us/za On 3 mrt 2012, at 16:39, John Drescher wrote: > On Sat, Mar 3, 2012 at 10:15 AM, Mark Schonewille > wrote: >> Hi, >> >> MySQL has a really great manual with extensive explanations and dozens of >> examples for each command. SQLite is none of all this. It just has a limited >> number of pages listing a small number of commands and some special >> features. It also has a number of really weird diagrammes. >> >> How do I read diagrammes like http://sqlite.org/images/syntax/sql-stmt.gif ? >> > > Are you a programmer? That diagram is very basic and should be easy to > understand if you took university level computer science courses. > > John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
On 03-03-2012 16:39, John Drescher wrote: > On Sat, Mar 3, 2012 at 10:15 AM, Mark Schonewille > wrote: >> Hi, >> >> MySQL has a really great manual with extensive explanations and dozens of >> examples for each command. SQLite is none of all this. It just has a limited >> number of pages listing a small number of commands and some special >> features. It also has a number of really weird diagrammes. >> >> How do I read diagrammes like http://sqlite.org/images/syntax/sql-stmt.gif ? >> > > Are you a programmer? That diagram is very basic and should be easy to > understand if you took university level computer science courses. > i dont need "university level computer science" to understand it. ;) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
On 03-03-2012 16:15, Mark Schonewille wrote: > Hi, > > MySQL has a really great manual with extensive explanations and dozens of > examples for each command. SQLite is none of all this. It just has a limited > number of pages listing a small number of commands and some special features. > It also has a number of really weird diagrammes. > > How do I read diagrammes like http://sqlite.org/images/syntax/sql-stmt.gif ? > > Where can I find a good reference manual just like MySQL's? > > -- What do you expect from a good reference manual, besides the information that is given on the website( sqlite.org) ? like: http://sqlite.org/books.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Manual
On Sat, Mar 3, 2012 at 10:15 AM, Mark Schonewille wrote: > Hi, > > MySQL has a really great manual with extensive explanations and dozens of > examples for each command. SQLite is none of all this. It just has a limited > number of pages listing a small number of commands and some special features. > It also has a number of really weird diagrammes. > > How do I read diagrammes like http://sqlite.org/images/syntax/sql-stmt.gif ? > Are you a programmer? That diagram is very basic and should be easy to understand if you took university level computer science courses. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Manual
Hi, MySQL has a really great manual with extensive explanations and dozens of examples for each command. SQLite is none of all this. It just has a limited number of pages listing a small number of commands and some special features. It also has a number of really weird diagrammes. How do I read diagrammes like http://sqlite.org/images/syntax/sql-stmt.gif ? Where can I find a good reference manual just like MySQL's? -- Best regards, Mark Schonewille Economy-x-Talk Consulting and Software Engineering Homepage: http://economy-x-talk.com Twitter: http://twitter.com/xtalkprogrammer KvK: 50277553 Download the Installer Maker Plugin 1.7 for LiveCode here http://qery.us/za ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 command shell dump possible bug
Hi Simon, thanks for your help. All worked now :) I only lost some data at the end of the table, not so bad. Thank you :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)
Its does not crash on x86. I havent built the shell for my mips device.
Sreekumar
On Mar 3, 2012 10:26 AM, "Dan Kennedy" wrote:
> On 03/03/2012 10:30 AM, Sreekumar TP wrote:
>
>> Could someone throw some light on this issue too?
>>
>
> I can't see from the stack trace why this is crashing.
>
> Does it crash if you run the query from the sqlite shell?
>
> Maybe try building the shell without optimizations, and
> then running it under valgrind.
>
> Dan.
>
>
>
>> Sreekumar
>> On Mar 2, 2012 10:05 AM, "Sreekumar TP" wrote:
>>
>> The backtrace
>>> ===
>>>
>>>
>>> Program received signal SIGSEGV, Segmentation fault.
>>> 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=...,
>>> pParse=0x2d4035c8) at sqlite3.c:106740
>>> 106740 }while( yymajor!=YYNOCODE&& yypParser->yyidx>=0 );
>>> #0 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119,
>>> yyminor=...,
>>> pParse=0x2d4035c8) at sqlite3.c:106740
>>> yyminorunion = {
>>> yyinit = 735636932,
>>> yy0 = {
>>> z = 0x2bd8edc4 "FROM dir_table",
>>> n = 4
>>> },
>>> yy4 = 735636932,
>>> yy90 = {
>>> a = 735636932,
>>> b = 0x4
>>> },
>>> yy118 = {
>>> pExpr = 0x2bd8edc4,
>>> zStart = 0x4,
>>> zEnd = 0x2b697000 ""
>>> },
>>> yy203 = 0x2bd8edc4,
>>> yy210 = 196 '\304',
>>> yy215 = {
>>> value = 735636932,
>>> mask = 4
>>> },
>>> yy259 = 0x2bd8edc4,
>>> yy292 = {
>>> pLimit = 0x2bd8edc4,
>>> pOffset = 0x4
>>> },
>>> yy314 = 0x2bd8edc4,
>>> yy322 = 0x2bd8edc4,
>>> yy342 = {
>>> eOperator = {
>>> z = 0x2bd8edc4 "FROM dir_table",
>>> n = 4
>>> },
>>> not = 728330240
>>> },
>>> yy384 = 0x2bd8edc4,
>>> yy387 = 0x2bd8edc4
>>> }
>>> yyact = 21104640
>>> yyendofinput = 0
>>> yypParser = 0x2d401e40
>>> #1 0x2b65a210 in sqlite3RunParser (pParse=0x2d4035c8, zSql=0x2bd8eda4
>>> "SELECT COUNT(distinct Alb_Name) FROM dir_table", pzErrMsg=0x2bd8cbf8) at
>>> sqlite3.c:107465
>>> nErr = 0
>>> i = 36
>>> pEngine = 0x2d401e40
>>> tokenType = 119
>>> lastTokenParsed =
>>> enableLookaside = 1 '\001'
>>> db = 0x6a14b0
>>> mxSqlLen = 10
>>> #2 0x2b65ac68 in sqlite3Prepare (db=0x6a14b0, zSql=0x2bd8eda4 "SELECT
>>> COUNT(distinct Alb_Name) FROM dir_table", nBytes=-1, saveSqlFlag=0,
>>> pReprepare=0x0, ppStmt=0x2bd8cca8, pzTail=0x0) at sqlite3.c:90212
>>> pParse = 0x2d4035c8
>>> zErrMsg = 0x0
>>> rc =
>>> i =
>>> #3 0x2b65b468 in sqlite3LockAndPrepare (db=,
>>> zSql=, nBytes=-1, ppStmt=,
>>> pzTail=0x0) at sqlite3.c:90304
>>> rc =
>>> #4 sqlite3_prepare (db=, zSql=>> out>,
>>> nBytes=-1, ppStmt=, pzTail=0x0) at sqlite3.c:24831
>>> rc = 21104640
>>> #5 0x00466730 in sql_stmt (db=9, stmt=0xffc0>> of bounds>) at /localhome/user/a/b/c/d/e/f/g/**h/k.c:496
>>> errmsg = 0x0
>>> retval =
>>> __PRETTY_FUNCTION__ = "\000\000\000\000\000\000\000\**000"
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Thu, Mar 1, 2012 at 11:42 PM, Dan Kennedy**
>>> wrote:
>>>
>>> On 03/01/2012 10:54 PM, Sreekumar TP wrote:
version 3.7.7.1
> The query works on x86, but fails on MIPS processor!
>
>
Are you able to post a stack trace? Ideally generated by
the gdb "where full" command. Thanks.
Dan.
> Sreekumar
>
>
>
> On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy
> wrote:
>
> On 03/01/2012 05:48 PM, Sreekumar TP wrote:
>
>>
>> In my system, the statement causes sqlite3parser function to crash.
>>
>>> My compiler is mips , little endian, gcc version is 4.5.2
>>>
>>>
>>> SQLite version? 3.7.10 seems Ok here.
>>
>> Dan.
>>
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-***
>> ***users
>>
>> >
>>
>>
>> >
>>
>>>
>>>
>> ___
>>
> sqlite-users mailing list
>
Re: [sqlite] TEXT PRIMARY KEY
Am 02.03.2012 13:59, schrieb Jay A. Kreibich: On Fri, Mar 02, 2012 at 10:44:20AM +0100, Christoph P.U. Kukulies scratched on the wall: When defining a column TEXT PRIMARY KEY (is that possible on TEXT?), would this imply uniqueness? Kind of. It implies uniqueness in the SQL sense, which does not include NULLs (remember, NULL != NULL). The SQL term "PRIMARY KEY" should imply both "UNIQUE" and "NOT NULL", but there is a long standing issue in SQLite that allows NULLs in non-integer PRIMARY KEY columns. This allows "duplicate" NULL entries in a PK column. Normally this isn't an issue, as you shouldn't have NULLs in a single-column PK anyways. Or would I have to write something like TEXT PRIMARY KEY UNIQUE ? No, but to be extra safe you should write "TEXT PRIMARY KEY NOT NULL." -j Thanks. And thanks to others for contributing. My logic is the following: When I'm inserting files and their respective contents into the database, I have to decide, whether a file is already there. I'm building a unique ID made up from the basename, md5sum and size of the file. This results as a TEXT PRIMARY KEY (e.g. filename_md5sum_size). Can I use (I'm using System.Data.SQLite) a try clause to find out whether the entry is already there? I think this can be faster than asking the database by an SQL statement, whether the KEY is already there. -- Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
