Re: [sqlite] how to disable dot commands?
I like this answer!! I think I think it's the easiest way.
On Mon, Jan 13, 2020 at 10:22 AM Keith Medcalf wrote:
>
> On Sunday, 12 January, 2020 18:44, Xingwei Lin
> wrote:
>
> >Is there any way can we disable the dot commands feature in sqlite?
>
> SQLite does not process dot commands, they are commands to the shell.c
> SQLite Application program.
>
> The current shell.c application currently does not have a way to omit the
> meta commands.
>
> However, if you build your own you can simply search shell.c (or
> shell.c.in) for the function do_meta_command function definition and have
> it execute a "return 0;" at the top of the function after the variable
> declarations rather that process the meta command. This will not remove
> the code that handles the meta commands but merely bypass the processing of
> them.
>
> That is make it look thusly by inserting the return 0; line:
>
> /*
> ** If an input line begins with "." then invoke this routine to
> ** process that line.
> **
> ** Return 1 on error, 2 to exit, and 0 otherwise.
> */
> static int do_meta_command(char *zLine, ShellState *p){
> int h = 1;
> int nArg = 0;
> int n, c;
> int rc = 0;
> char *azArg[52];
>
> return 0;
>
> #ifndef SQLITE_OMIT_VIRTUALTABLE
> if( p->expert.pExpert ){
> expertFinish(p, 1, 0);
> }
> #endif
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
Best regards，
Xingwei Lin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable dot commands?
On Sunday, 12 January, 2020 18:44, Xingwei Lin wrote:
>Is there any way can we disable the dot commands feature in sqlite?
SQLite does not process dot commands, they are commands to the shell.c SQLite
Application program.
The current shell.c application currently does not have a way to omit the meta
commands.
However, if you build your own you can simply search shell.c (or shell.c.in)
for the function do_meta_command function definition and have it execute a
"return 0;" at the top of the function after the variable declarations rather
that process the meta command. This will not remove the code that handles the
meta commands but merely bypass the processing of them.
That is make it look thusly by inserting the return 0; line:
/*
** If an input line begins with "." then invoke this routine to
** process that line.
**
** Return 1 on error, 2 to exit, and 0 otherwise.
*/
static int do_meta_command(char *zLine, ShellState *p){
int h = 1;
int nArg = 0;
int n, c;
int rc = 0;
char *azArg[52];
return 0;
#ifndef SQLITE_OMIT_VIRTUALTABLE
if( p->expert.pExpert ){
expertFinish(p, 1, 0);
}
#endif
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable dot commands?
Hi, On Sun, Jan 12, 2020 at 7:44 PM Xingwei Lin wrote: > > Hi, > > Is there any way can we disable the dot commands feature in sqlite? Are you talking about the SQLite shell? Why do you want to disable them? What is your specific scenario? Thank you. > > -- > Best regards， > Xingwei Lin > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to disable dot commands?
On 13 Jan 2020, at 1:43am, Xingwei Lin wrote: > Is there any way can we disable the dot commands feature in sqlite? SQLite – the library you call from C and other programming languages – does not support the dot commands. It doesn't recognise them. If you try to use them you will get a complaint about bad syntax. The dot commands are part of the SQLite command line shell program. Only this one program understands the dot commands. The source code for this program is part of the SQLite download package. You could make your own copy of that program which did not support the dot commands. But that would not stop someone else from using their copy on your own database files. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to disable dot commands?
Hi, Is there any way can we disable the dot commands feature in sqlite? -- Best regards， Xingwei Lin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Test failures on GPFS
I use SQLite over GPFS , but in DELETE (which I think is the default) mode. Not WAL mode. No issues with locking, except performance when accessing concurrently from multiple nodes. As others pointed out, this has to do with the overhead due to lock requests. GPFS must coordinate with many nodes. My observation is that when concurrent access is from a few nodes, the performance is OK even though number of nodes is always the same. Thus, GPFS coordinates in some smart way only between nodes actively involved. One reason I do not use mySQL with its more efficient network access is that sys admin must set it up. With SQLite, I am independent. In addition, in my SQL there are authentication issues to be dealt with. I rely on GPFS file access permissions (access control list, ACL) to regulate access to database. I heard about BeadrockDB, which internally uses SQLite and provides network access with replication. I have not tried it and do not know what is involved. Roman From: sqlite-users on behalf of Richard Hipp Sent: Saturday, January 11, 2020 8:59 PM To: SQLite mailing list Subject: Re: [sqlite] Test failures on GPFS CAUTION: This email comes from an external source; the attachments and/or links may compromise our secure environment. Do not open or click on suspicious emails. Please click on the “Phish Alert” button on the top right of the Outlook dashboard to report any suspicious emails. On 1/11/20, J. King wrote: > > WAL mode does not work over the network, so the test failures are presumably > to be expected. > WAL mode should work on a network filesystem, as long as all of the clients are on the same host computer, and as long as mmap()-ing the *-shm file gives all the clients shared memory. Dunno if GPFS does that or not, though. Maybe not. Or, maybe not reliably. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://nam04.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einsteinmed.org%7Cba1544a0f3584e8a077008d7970309d8%7C9c01f0fd65e040c089a82dfd51e62025%7C0%7C1%7C637143911624961155sdata=udLAzknx7zL4yHzQk8ZPQI8mAWltFusqvcb%2FW31XuaY%3Dreserved=0 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained table bloat
On 2020/01/13 12:25 AM, Tom Browder wrote:
On Sun, Jan 12, 2020 at 14:05 Keith Medcalf wrote:
Close, but no banana. Every value has a type. A column may contain
multiple values (as in one per row)
Thanks, Keith.
I assume that is just for SQLite, or am I wrong again?
You are not wrong. This comes up from time to time and is always quite
interesting.
As Keith explained, the Relational Model can be applied on most kinds of
data-stores. SQLite's data store happens to allow per-value typing, and
then only using "Duck typing" (https://en.wikipedia.org/wiki/Duck_typing).
While on the subject of SQLite peculiarities, there's a few to note if
you are new to SQLite, like:
- You don't need any type for a column, but untyped columns are treated
as having blob affinity, not text.
- A column declared as VARCHAR(30), or TEXT(30) or INT(11) for that
matter, will be fully accepted, but there is no actual length
constraint. You can put any length value in the column.
- NULL values are distinct from each other (in some cases) so that if A
is NULL and B is NULL, then A = B returns FALSE (0) and A <> B also
returns FALSE!. This is useful, but note that a Primary Key in SQLite
without also having the NOT NULL constraint, will allow duplicate NULL
values in the key. (the row-id alias being the exception).
- While on the point, the special type "INTEGER PRIMARY KEY" is an Alias
for the internal index (row-id) of the table, and so doesn't allow NULLs.
- While on it still... Tables don't always have row-ids, and there is no
easy check to know if it does, so unless you made the DB yourself, you
can't safely just query the row-id.
- Since typing is per value, it is not an error in SQLite for Parent and
Child Key columns in a Foreign-Key relationship to have different types
- this can have some unexpected results!
- A datetime is a Numeric type expressed as text (ISO8601) and doesn't
inherently know anything about time or time-zones. (The date-handling
functions work amazingly well though).
- A column with the type STRING will have Integer affinity.
- A spelling error in your schema, such as CREATE TABLE t(id INT, name
T3XT); will not be an error - SQLite will silently regard that column
affinity as blob.
- Quotes are more or less ambivalent... CREATE TABLE t("id" int, [name]
text, `age` [int]) is a perfectly valid schema statement, and Set a =
"John" can mean different things based on whether there is a column
named John or not.
I'm probably not remembering all of them now, but we've spent lots of
time musing about it before (forum searches will probably provide a host
of discussions, including much lobbying for a "strict" mode) and because
of all that, there's a fun feature added in SQLitespeed that does
Schema-checking and prints a list of warnings if it contains one or more
of these SQLite quirks (including misspelled types, which helps me a
lot), and on the SQLite site there's also a section on some of these
peculiarities (https://www.sqlite.org/quirks.html).
I'm hoping someone else will add the quirks which I forgot about :)
We all came to love (mostly) and often use these quirks to some
advantage, but it pays to be aware of them, especially coming from
another DB architecture where typing and the like are more rigid.
Cheers!
Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best way to store key,value pairs
On Tue, 7 Jan 2020 17:11:45 -1000 Jens Alfke wrote: > Consider encoding the headers as JSON and storing them in a single > column. SQLite has a JSON extension that makes it easy to access > values from JSON data in a query. What is the motivation behind this advice? It's completely unnecessary. For all the complexity JSON adds to the design, it adds exactly no power: precisely the same effects can be achieved without it. I can understand the utility of using SQLite's JSON features where there's pre-existing JSON, especially if there's need to preserve it and reproduce it later. I see no advantage to introducing JSON to a system with no external use for it. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained table bloat
On Sunday, 12 January, 2020 15:31, Simon Slavin wrote:
>You're generally right. SQLite always uses affinities (more or less
>'weak typing') rather than strong typing. I don't know of any other SQL
>implementations which allow this without explicit declaration, and most
>don't allow it at all.
SQLite is strongly typed however "entities" (table rows) are composed of a
fixed number of dynamically typed values. A collection of entities (a table)
has an affinity (preference for a particular type if possible) for each
"column" in the collection.
This is not "weak typing" nor is it "duck typing". It is "dynamic typing".
>You can enforce strong typing in SQLite using a constraint. But most
>people enforce type in their own code, before the value gets to SQLite.
Well, no. You can restrict the size of the dynamic though a check constraint,
but you cannot make the members of entities strongly typed. For example:
create table x
(
c text check (typeof(c) is 'text')
);
does not strongly type the column "c" of entity collection "x". What it does
is ensure that only text values are stored in that particular dynamically typed
column of the entity. You will note that the declaration is inconsistent --
the two available correct forms would be:
create table x
(
c text not null check (typeof(c) is 'text')
);
and
create table x
(
c text check (typeof(c) in ('null', 'text'))
);
while one may be tempted to claim that "consistency is the hobgoblin of little
minds" its converse, inconsistency, is often a source of errors.
Some affinities are more complicated. For example:
create table x
(
c numeric check (typeof(c) in ('null', 'real', 'integer'))
);
both 'real' and 'integer' are required for numeric affinity enforcement, and
the 'null' because the column may contain nulls.
Doing this does not really do much since you still have to check the type on
retrieval of the value anyway in order to know what to do with it.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [lemon] lempar function visibility
Hi all
I have been using lemon successfully to generate a parser used in a
library, and I'd like some source-level way of limiting the visibility
of some lempar symbols.
I threw together a proof of concept for a macro that can be user defined
to allow setting `static` or e.g.
`__attribute___((visibility("hidden")))` for the `Parse.*` functions. By
default it is empty which maintains the existing behaviour.
My proof of concept patch is included below using the macro name
`YYAPIFUNC`.
I feel this is of general utility, and if you find it useful, please
feel free to adopt as you see fit under the usual sqlite terms.
All the Best
Luke
---
Index: tool/lempar.c
==
--- tool/lempar.c
+++ tool/lempar.c
@@ -231,10 +231,18 @@
#include
static FILE *yyTraceFILE = 0;
static char *yyTracePrompt = 0;
#endif /* NDEBUG */
+/*
+** Any required function qualifiers for public parts of the parser should be
+** defined ** here, e.g. static, declspec, etc.
+*/
+#ifndef YYAPIFUNC
+#define YYAPIFUNC /* default is empty */
+#endif
+
#ifndef NDEBUG
/*
** Turn parser tracing on by giving a stream to which to write the trace
** and a prompt to preface each trace message. Tracing is turned off
** by making either argument NULL
@@ -249,11 +257,11 @@
**
**
** Outputs:
** None.
*/
-void ParseTrace(FILE *TraceFILE, char *zTracePrompt){
+YYAPIFUNC void ParseTrace(FILE *TraceFILE, char *zTracePrompt){
yyTraceFILE = TraceFILE;
yyTracePrompt = zTracePrompt;
if( yyTraceFILE==0 ) yyTracePrompt = 0;
else if( yyTracePrompt==0 ) yyTraceFILE = 0;
}
@@ -318,11 +326,11 @@
# define YYMALLOCARGTYPE size_t
#endif
/* Initialize a new parser that has already been allocated.
*/
-void ParseInit(void *yypRawParser ParseCTX_PDECL){
+YYAPIFUNC void ParseInit(void *yypRawParser ParseCTX_PDECL){
yyParser *yypParser = (yyParser*)yypRawParser;
ParseCTX_STORE
#ifdef YYTRACKMAXSTACKDEPTH
yypParser->yyhwm = 0;
#endif
@@ -357,11 +365,11 @@
**
** Outputs:
** A pointer to a parser. This pointer is used in subsequent calls
** to Parse and ParseFree.
*/
-void *ParseAlloc(void *(*mallocProc)(YYMALLOCARGTYPE) ParseCTX_PDECL){
+YYAPIFUNC void *ParseAlloc(void *(*mallocProc)(YYMALLOCARGTYPE)
ParseCTX_PDECL){
yyParser *yypParser;
yypParser = (yyParser*)(*mallocProc)( (YYMALLOCARGTYPE)sizeof(yyParser) );
if( yypParser ){
ParseCTX_STORE
ParseInit(yypParser ParseCTX_PARAM);
@@ -425,11 +433,11 @@
}
/*
** Clear all secondary memory allocations from the parser
*/
-void ParseFinalize(void *p){
+YYAPIFUNC void ParseFinalize(void *p){
yyParser *pParser = (yyParser*)p;
while( pParser->yytos>pParser->yystack ) yy_pop_parser_stack(pParser);
#if YYSTACKDEPTH<=0
if( pParser->yystack!=>yystk0 ) free(pParser->yystack);
#endif
@@ -442,11 +450,11 @@
**
** If the YYPARSEFREENEVERNULL macro exists (for example because it
** is defined in a %include section of the input grammar) then it is
** assumed that the input pointer is never NULL.
*/
-void ParseFree(
+YYAPIFUNC void ParseFree(
void *p,/* The parser to be deleted */
void (*freeProc)(void*) /* Function used to reclaim memory */
){
#ifndef YYPARSEFREENEVERNULL
if( p==0 ) return;
@@ -458,11 +466,11 @@
/*
** Return the peak depth of the stack for a parser.
*/
#ifdef YYTRACKMAXSTACKDEPTH
-int ParseStackPeak(void *p){
+YYAPIFUNC int ParseStackPeak(void *p){
yyParser *pParser = (yyParser*)p;
return pParser->yyhwm;
}
#endif
@@ -482,11 +490,11 @@
** (2) is not a syntax error.
**
** Return the number of missed state/lookahead combinations.
*/
#if defined(YYCOVERAGE)
-int ParseCoverage(FILE *out){
+YYAPIFUNC int ParseCoverage(FILE *out){
int stateno, iLookAhead, i;
int nMissed = 0;
for(stateno=0; stateno
**
** Outputs:
** None.
*/
-void Parse(
+YYAPIFUNC void Parse(
void *yyp, /* The parser */
int yymajor, /* The major token code number */
ParseTOKENTYPE yyminor /* The value for the token */
ParseARG_PDECL /* Optional %extra_argument parameter */
){
@@ -1063,14 +1071,14 @@
/*
** Return the fallback token corresponding to canonical token iToken, or
** 0 if iToken has no fallback.
*/
-int ParseFallback(int iToken){
+YYAPIFUNC int ParseFallback(int iToken){
#ifdef YYFALLBACK
assert( iToken<(int)(sizeof(yyFallback)/sizeof(yyFallback[0])) );
return yyFallback[iToken];
#else
(void)iToken;
return 0;
#endif
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained table bloat
On Sunday, 12 January, 2020 15:29, Richard Damon wrote: >On 1/12/20 5:25 PM, Tom Browder wrote: >> On Sun, Jan 12, 2020 at 14:05 Keith Medcalf wrote: >>> On Sunday, 12 January, 2020 09:03, Tom Browder >>> wrote: Am I missing something? I thought every column has to have a type? >>> Close, but no banana. Every value has a type. A column may contain >>> multiple values (as in one per row) >> I assume that is just for SQLite, or am I wrong again? > That the entries for a given column in different rows can have different > types is a peculiarity of SQLite. In a 'Standard' SQL database, a column > has a defined type, and all rows will have values of that type (or NULL). It is a peculiarity of the underlying datastore used by SQLite. Many (most in fact) datastores require that all instances of the same "column" in an "entity" be the same type -- some do not (SQLite is one of them). SQL is simply a Structured Query Language that can be overlaid on a variety of datastore models, one of which is the Relational model. When SQL is used on other database organizations it is entirely possible for the "type" of a particular returned "column" to vary by row as it may have been fetched from different entities. DB-Vista, MDBS, and NOMAD are a couple of CODASYL style databases which have (optional extra) SQL query interfaces that can return data of multiple value types row by each for the same column. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained table bloat
On 12 Jan 2020, at 22:25, Tom Browder wrote: > On Sun, Jan 12, 2020 at 14:05 Keith Medcalf wrote: > >> On Sunday, 12 January, 2020 09:03, Tom Browder >> wrote: >> >Am I missing something? I thought every column has to have a type? >> >> Close, but no banana. Every value has a type. A column may contain >> multiple values (as in one per row) > I assume that is just for SQLite, or am I wrong again? In general that's correct. Lengths of VARCHARs are ignored, too, as you probably saw. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained table bloat
On 12 Jan 2020, at 10:25pm, Tom Browder wrote: > I assume that is just for SQLite, or am I wrong again? You're generally right. SQLite always uses affinities (more or less 'weak typing') rather than strong typing. I don't know of any other SQL implementations which allow this without explicit declaration, and most don't allow it at all. You can enforce strong typing in SQLite using a constraint. But most people enforce type in their own code, before the value gets to SQLite. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained table bloat
On 1/12/20 5:25 PM, Tom Browder wrote: On Sun, Jan 12, 2020 at 14:05 Keith Medcalf wrote: On Sunday, 12 January, 2020 09:03, Tom Browder wrote: Am I missing something? I thought every column has to have a type? Close, but no banana. Every value has a type. A column may contain multiple values (as in one per row) Thanks, Keith. I assume that is just for SQLite, or am I wrong again? -Tom That the entries for a given column in different rows can have different types is a peculiarity of SQLite. In a 'Standard' SQL database, a column has a defined type, and all rows will have values of that type (or NULL). -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained table bloat
On Sun, Jan 12, 2020 at 14:05 Keith Medcalf wrote: > On Sunday, 12 January, 2020 09:03, Tom Browder > wrote: > >Am I missing something? I thought every column has to have a type? > > Close, but no banana. Every value has a type. A column may contain > multiple values (as in one per row) Thanks, Keith. I assume that is just for SQLite, or am I wrong again? -Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained table bloat
On Sunday, 12 January, 2020 09:03, Tom Browder wrote: >Am I missing something? I thought every column has to have a type? Close, but no banana. Every value has a type. A column may contain multiple values (as in one per row). Therefore each of those values has a type, which may be different from the type of the value in the same column on another row. Columns have an affinity, which is a preference for the type of the value to be stored in that columns' rows if conversion from the value provided to be stored to the specified affinity is possible, lossless, and reversible. https://sqlite.org/datatype3.html -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unexplained table bloat
Am I missing something? I thought every column has to have a type? -Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
