Re: [sqlite] Hexadecimal integer literals

[Markus Schaber]

Hi, Eric,

Von: Eric Rubin-Smith
> 
> So far no one has raised the idea of using a "big int" layer to implement
> proper integer arithmetic past 64 bits.  The fact that it hasn't been
> mentioned makes me worry that it's a blatantly silly idea for SQLite for some
> reason -- but I'm tossing it out there on the off chance that it's useful.

Actually, I did: 
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-July/054285.html

 
> Personally, I would have loved to be able to use 128-bit integer math a few
> weeks ago when I was investigating the idea of implementing a longest-prefix
> search capability for IPv6 networks using SQLite's R*Tree.
>  I had to implement all the shifting and such in C, which is fine, but it
> would have been pretty sexy to have gotten it all done in SQL.

I'm not sure whether such functionalities are a must for SQLite core, but
I think that extensions providing such functionalities should be possible.

I'm not that much into SQLite internals yet, but I know that PostgreSQL allows
extensions to define their own data types with their own parsers and syntax,
which is used e. G. by the PostGIS extension. On the other hand, PostgreSQL
has built-in "decimal" and "numeric" types which allow up to 131072 digits
before and 16383 digits after the decimal point.
http://www.postgresql.org/docs/current/static/datatype-numeric.html

So one might argue such datatypes could also fit well into SQLite :-)


Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915

This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient (or have received
this e-mail in error) please notify the sender immediately and destroy this 
e-mail. Any unauthorised copying, disclosure
or distribution of the material in this e-mail is strictly forbidden.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Markus Schaber]

Hi,

Von: Scott Robison

> On Wed, Jul 23, 2014 at 9:46 PM, J Decker  wrote:
> 
> > Seems like adding hex interpreting is just adding code for the sake of
> > adding code.
> > Unless the data is coming from some pre written text file, isn't just
> > just as easy to format an into into decimal as it is for hex without
> > having to add extra characters for the prefix?
> >
> 
> One desirable aspect of code is that it is easily understood, that future
> modifications / changes be as easy as possible. It is usually much easier to
> see that 0x4000 is a single bit set in a 32 bit integer vs seeing the
> number 1073741824 and wondering exactly what it means. At least it is to me.
> 
> For that reason, I think adding hex constants to the parser is a good idea.
> If I were expressing wishes, I'd suggest adding binary constants (with a 0b
> prefix) but that's probably pushing luck. :)
> 
> But no octal! At least not using the C syntax. I like being able to prefix a
> decimal integer with 0 and not having it change the meaning (which can't be
> done in C).

Python 3, Rust and others adopted the syntax 0o123 for octal literals.

The IEC 61131 languages use the syntax base#value, so 16#12ab is a hex number,
and 8#123 is an octal number.


Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915

This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient (or have received
this e-mail in error) please notify the sender immediately and destroy this 
e-mail. Any unauthorised copying, disclosure
or distribution of the material in this e-mail is strictly forbidden.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Producing RFC4180-compliant CSV output

[Peter Waller]

Hi All,

We're using `sqlite -csv` to generate CSV files because it is the best
performing thing available to us. It turns out however that the CSV spec
demands CRLF line endings [1].

There is software in the wild that barfs on non-CRLF-line-ending files,
such as SQL Server Integration Services [2].

Currently as best as we can tell from inspecting the source the output
depends on the platform you run on, depending on what "text mode" means
there. On Linux and on Windows using the official binaries, that means you
get the wrong line endings (just 'plain' \n).

We have made a patch which works well in our environment which just makes
CSV output \r\n instead of just \n. On Windows using the same compiler as
the official binaries are built with the behaviour should be the same, but
it may produce different output if a different compiler is used, because
the behaviour of a file opened in text mode could vary.

Would a patch be accepted to change the line endings to \r\n?

Thanks,

- Peter

[1] http://tools.ietf.org/html/rfc4180#section-2
[2] https://en.wikipedia.org/wiki/SQL_Server_Integration_Services
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Producing RFC4180-compliant CSV output

[Hick Gunter]

How about piping your csv file through unix2dos?

-Ursprüngliche Nachricht-
Von: Peter Waller [mailto:pe...@scraperwiki.com]
Gesendet: Donnerstag, 24. Juli 2014 11:27
An: sqlite-users@sqlite.org
Cc: developers
Betreff: [sqlite] Producing RFC4180-compliant CSV output

Hi All,

We're using `sqlite -csv` to generate CSV files because it is the best 
performing thing available to us. It turns out however that the CSV spec 
demands CRLF line endings [1].

There is software in the wild that barfs on non-CRLF-line-ending files, such as 
SQL Server Integration Services [2].

Currently as best as we can tell from inspecting the source the output depends 
on the platform you run on, depending on what "text mode" means there. On Linux 
and on Windows using the official binaries, that means you get the wrong line 
endings (just 'plain' \n).

We have made a patch which works well in our environment which just makes CSV 
output \r\n instead of just \n. On Windows using the same compiler as the 
official binaries are built with the behaviour should be the same, but it may 
produce different output if a different compiler is used, because the behaviour 
of a file opened in text mode could vary.

Would a patch be accepted to change the line endings to \r\n?

Thanks,

- Peter

[1] http://tools.ietf.org/html/rfc4180#section-2
[2] https://en.wikipedia.org/wiki/SQL_Server_Integration_Services
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Producing RFC4180-compliant CSV output

[Richard Hipp]

On Thu, Jul 24, 2014 at 5:46 AM, Hick Gunter  wrote:

> How about piping your csv file through unix2dos?
>

Yeah.  Having a unix program generate \r\n line endings just seems wrong.
Standard or no standard.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Producing RFC4180-compliant CSV output

[Peter Waller]

Unfortunately, we already tried this, sed, and writing our own translation
program in C.

In the best case it doubled (!) the CPU-time usage and ate more than one
core at a time, which isn't acceptable in our environment. Making the
change in place in the sqlite source has no observable impact.

Most other programs and APIs which generate or read CSVs that we have seen
treat them correctly according to the standard, regardless of platform.

I too am sad that CRLF is mandated in the specification and still in wide
use because of Windows. But the fact is that there are database import
programs which can't deal with plain LF input. Which is insane, but there
are large datasets being repeatedly imported into those programs.

Please could you reconsider, or in the worst case could we consider a way
to make it optionally output specification-complaint output?

Thanks,

- Peter


On 24 July 2014 11:02, Richard Hipp  wrote:

> On Thu, Jul 24, 2014 at 5:46 AM, Hick Gunter  wrote:
>
> > How about piping your csv file through unix2dos?
> >
>
> Yeah.  Having a unix program generate \r\n line endings just seems wrong.
> Standard or no standard.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Producing RFC4180-compliant CSV output

[Stephen Chrzanowski]

Of course, I've not seen the code, but, I'd throw in that a command line
option be added that'll force a particular mode.  By default, it is 'as is'
right now being platform dependent, but, if a CLO is added, either mode is
forced, or toggled.  I'd ask for separate toggles for forced or toggle. :]


On Thu, Jul 24, 2014 at 6:02 AM, Richard Hipp  wrote:

> On Thu, Jul 24, 2014 at 5:46 AM, Hick Gunter  wrote:
>
> > How about piping your csv file through unix2dos?
> >
>
> Yeah.  Having a unix program generate \r\n line endings just seems wrong.
> Standard or no standard.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Send parameter from batch file to sqlite

[shweta gk]

I want to unsubscribe from this forum. Please remove my mail from the
list. Its irritating to get unnessary mail.
Thanks & Regards,
Shweta.G.K


On Wed, Jul 23, 2014 at 5:50 PM, shweta gk  wrote:
> Operating system : Windows
> I am using DOS command shell.
> Thanks & Regards,
> Shweta.G.K
>
>
> On Wed, Jul 23, 2014 at 5:48 PM, Simon Slavin  wrote:
>> We don't know what operating system or command shell you're using.  So our 
>> advice is to write your own editor which takes your export.sql file and the 
>> output of your batch file and puts them together to make a new .sql file 
>> with the commands you want.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Send parameter from batch file to sqlite

[John McKown]

No one here can do it for you. We're not admins. But if you go to the
web site referenced at the bottom of _every single email_ from this
list, you can do it yourself.

On Thu, Jul 24, 2014 at 5:35 AM, shweta gk  wrote:
> I want to unsubscribe from this forum. Please remove my mail from the
> list. Its irritating to get unnessary mail.
> Thanks & Regards,
> Shweta.G.K
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Producing RFC4180-compliant CSV output

[Richard Hipp]

On Thu, Jul 24, 2014 at 6:15 AM, Peter Waller  wrote:

> Unfortunately, we already tried this, sed, and writing our own translation
> program in C.
>

The other problem is that it adds \r in front of *ever* \n, including those
that are part of data and which are not record separators.

I'm working on a patch for the sqlite3.exe now



>
> In the best case it doubled (!) the CPU-time usage and ate more than one
> core at a time, which isn't acceptable in our environment. Making the
> change in place in the sqlite source has no observable impact.
>
> Most other programs and APIs which generate or read CSVs that we have seen
> treat them correctly according to the standard, regardless of platform.
>
> I too am sad that CRLF is mandated in the specification and still in wide
> use because of Windows. But the fact is that there are database import
> programs which can't deal with plain LF input. Which is insane, but there
> are large datasets being repeatedly imported into those programs.
>
> Please could you reconsider, or in the worst case could we consider a way
> to make it optionally output specification-complaint output?
>
> Thanks,
>
> - Peter
>
>
> On 24 July 2014 11:02, Richard Hipp  wrote:
>
> > On Thu, Jul 24, 2014 at 5:46 AM, Hick Gunter  wrote:
> >
> > > How about piping your csv file through unix2dos?
> > >
> >
> > Yeah.  Having a unix program generate \r\n line endings just seems wrong.
> > Standard or no standard.
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Producing RFC4180-compliant CSV output

[David Empson]

On 24/07/2014, at 10:15 pm, Peter Waller  wrote:

> I too am sad that CRLF is mandated in the specification and still in wide use 
> because of Windows.

It may be nothing to do with Windows. CRLF is the Internet standard for the end 
of a line, as mentioned in RFC2234 and mandated by most if not all Internet 
standards, including RFC822 (e-mail and similarly structured text, where CRLF 
is required at the end of each header line), HTTP and other text-based 
protocols.

RFC4180 specifying CRLF as the end of line in CSV files is consistent with 
other RFCs and with Internet standards.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Async I/O not working for me on v 3.8.5

[Tom]

Test code follows. The update query takes 3 seconds with or without the
async code.
Any ideas?


void Async_Thread() 
{
sqlite3async_run();
}

//-
void CDatabase::Init(void)
{
int rc = 0;

rc = sqlite3_open("game.db", );
FUN_ASSERT(rc == SQLITE_OK);
FUN_ASSERT(PDB != NULL);

rc = sqlite3async_initialize(NULL, 1);
FUN_ASSERT(rc == SQLITE_OK);

std::thread t(Async_Thread);

char query_string[1024] = "";
::_snprintf_s(query_string, _countof(query_string), "UPDATE players SET
VotesUpdateDate = CURRENT_DATE;");

LOG(L"Executing...\n");
rc = sqlite3_exec(PDB, query_string, NULL, NULL, NULL);
FUN_ASSERT(rc == SQLITE_OK);
LOG(L"Done\n");

sqlite3async_control(SQLITEASYNC_HALT, SQLITEASYNC_HALT_IDLE);
t.join();
sqlite3async_shutdown();

rc = sqlite3_close(PDB);
FUN_ASSERT(rc == SQLITE_OK);
PDB = NULL;




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Async-I-O-not-working-for-me-on-v-3-8-5-tp76920.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does SQLite lock the entire DB for writes, or lock by table?

[Tom]

I have a DB operation that will take a few minutes (computing a leaderboard)
so it needs to be done in a background thread (with its own connection). I
was hoping to do this:

1. read various fields from table A as inputs to the leaderboard calculation
2. in a thread, compute leaderboard and write to table B (which main thread
doesn't write to).

In other words, the main thread only writes to table A and the background
thread only writes to table B.
I was hoping that miscellaneous concurrent writes to table A would not give
me SQLITE_BUSY, since the background thread is only writing to table B. But
I get the impression the entire DB is locked - is that correct? My code will
be simpler if I don't ever have to deal with SQLITE_BUSY.

Cheers

Tom



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Does-SQLite-lock-the-entire-DB-for-writes-or-lock-by-table-tp76921.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite lock the entire DB for writes, or lock by table?

[Simon Slavin]

On 24 Jul 2014, at 8:01am, Tom  wrote:

> 1. read various fields from table A as inputs to the leaderboard calculation
> 2. in a thread, compute leaderboard and write to table B (which main thread
> doesn't write to).
> 
> In other words, the main thread only writes to table A and the background
> thread only writes to table B.
> I was hoping that miscellaneous concurrent writes to table A would not give
> me SQLITE_BUSY, since the background thread is only writing to table B. But
> I get the impression the entire DB is locked - is that correct? My code will
> be simpler if I don't ever have to deal with SQLITE_BUSY.

SQLite does not do table-level locks.  If anything is locked, the entire 
database is locked.  Table-level locks would not work for SQLite because a 
table may have related rows in another table enforced with FOREIGN KEYs.

If the application you described above is meant to be used concurrently with 
another application (e.g. while that process is running, another process or 
another computer may be updating the database) then the whole operation should 
be done as one transaction, e.g. with a BEGIN before step 1 and an END after 
step 2.  This is because someone might be updating the data for table A while 
it's being read.

You should not have to deal with SQLITE_BUSY if you set a reasonable timeout 
for your database connection.  You can use either of the following:




A reasonable timeout is not half a second.  A reasonable timeout is two minutes.

Something as simple as a leaderboard calculation, even for a thousand teams, 
should not be taking a few minutes to calculate unless your language is very 
slow.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Producing RFC4180-compliant CSV output

[Richard Hipp]

On Thu, Jul 24, 2014 at 7:47 AM, Richard Hipp  wrote:

>
>
>
> On Thu, Jul 24, 2014 at 6:15 AM, Peter Waller 
> wrote:
>
>> Unfortunately, we already tried this, sed, and writing our own translation
>> program in C.
>>
>
> The other problem is that it adds \r in front of *ever* \n, including
> those that are part of data and which are not record separators.
>
> I'm working on a patch for the sqlite3.exe now
>

www.sqlite.org/src/info/16c8ce10e15
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Producing RFC4180-compliant CSV output

[Simon Slavin]

On 24 Jul 2014, at 1:21pm, Richard Hipp  wrote:

> www.sqlite.org/src/info/16c8ce10e15

If I might suggest, for maximum flexibility, that the end-line-separator accept 
the following values:

CR
NL
CRNL
r
n
rn

and maybe even

\r
\n
\r\n

.  That should future-proof it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] feature request: aliasing (or renaming) db (not table) names

[Stephan Beal]

Hi, sqlite team,

i meant to address this directly to Richard a couple weeks back, but we got
carried away with other topics...

In the Fossil SCM and (by extension) libfossil, we juggle 1-3 db handles
for the config, checkout, and repo dbs. The first db which gets opened
(it's use-case dependent!) becomes the "main" db and the others get
attached with a well-defined name. The problem is, an application does not
(cannot) necessarily know which order the dbs were opened, so it doesn't
really know if "main" is the repo db, the checkout db, or the config db.

Normally this is not a problem because we have no table name collisions
which would require full qualification. However, it becomes exceedingly
tedious when, for example, a client app wants to install a new table in
(e.g.) the repo db. He doesn't know if he needs to use "create table
main.foo..." or "... repo.foo...". He can, thanks to C APIs, fetch this
info, but it intrudes quite a lot on the client code. (@Richard: this
hasn't come up in fossil(1) yet only because client's don't have a way to
do it, but it has come up in fossil(3) while experimenting with
client-installed extensions.)

My request is the ability to alias or rename the db (not table) names, so
that i can tell libfossil that, e.g., "repo" is always the repo db, even if
it's really (also) "main".

Perhaps a pragma:

pragma table_alias oldname aliasname
or
pragma table_rename oldname newname

either one would suit my purposes just fine.

:-?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

[Simon Slavin]

On 24 Jul 2014, at 2:11pm, Stephan Beal  wrote:

> The problem is, an application does not
> (cannot) necessarily know which order the dbs were opened, so it doesn't
> really know if "main" is the repo db, the checkout db, or the config db.

Create a fourth database with no content.  That's always the main one.  
Everything else is always attached to it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

[Stephan Beal]

On Thu, Jul 24, 2014 at 3:18 PM, Simon Slavin  wrote:

> Create a fourth database with no content.  That's always the main one.
>  Everything else is always attached to it.
>

i did in fact try that (way back in the beginning), using a :memory: db as
my main db. However, the :memory: VFS is (interestingly) not capable of
generating temp file names, and i need that feature :/.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

[Stephan Beal]

On Thu, Jul 24, 2014 at 3:51 PM, Stephan Beal  wrote:

> i did in fact try that (way back in the beginning), using a :memory: db as
> my main db.
>

note that i can't justify using a file for this purpose, because that file
has to live somewhere, and the only reasonable place for it is in the
checkout directory. It would clutter the source trees.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

[Simon Slavin]

On 24 Jul 2014, at 2:52pm, Stephan Beal  wrote:

> note that i can't justify using a file for this purpose, because that file
> has to live somewhere, and the only reasonable place for it is in the
> checkout directory. It would clutter the source trees.

Really ?  It would dramatically simplify your programming and not take up much 
space.  Oh well.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

[Stephan Beal]

On Thu, Jul 24, 2014 at 4:02 PM, Simon Slavin  wrote:

> Really ?  It would dramatically simplify your programming and not take up
> much space.  Oh well.
>

It's not the space, but the "pile of files" debate which has raged for
years in SCMs. Fossil already has its one "clutter" file, just like svn/git
have their .svn/.git dirs. libfossil (a library-based implementation of
Fossil) can't justify adding its own clutter to that.

A simpler solution which would serve my goals just as well: the ability to
rename only 'main' (e.g. sqlite3_rename_db(sqlite3*, char const *
newName)). i don't need 'main' because main is fluid in these apps. i need
a well-defined name which sticks with a db regardless of whether it is
opened or attached.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

[Simon Slavin]

On 24 Jul 2014, at 3:07pm, Stephan Beal  wrote:

> A simpler solution which would serve my goals just as well: the ability to
> rename only 'main' (e.g. sqlite3_rename_db(sqlite3*, char const *
> newName)). i don't need 'main' because main is fluid in these apps. i need
> a well-defined name which sticks with a db regardless of whether it is
> opened or attached.

What happens if you open any SQLite database, then ATTACH the same database to 
the same connection ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

[Stephan Beal]

On Thu, Jul 24, 2014 at 4:10 PM, Simon Slavin  wrote:

>
> On 24 Jul 2014, at 3:07pm, Stephan Beal  wrote:
>
> > A simpler solution which would serve my goals just as well: the ability
> to
> > rename only 'main' (e.g. sqlite3_rename_db(sqlite3*, char const *
> > newName)). i don't need 'main' because main is fluid in these apps. i
> need
> > a well-defined name which sticks with a db regardless of whether it is
> > opened or attached.
>
> What happens if you open any SQLite database, then ATTACH the same
> database to the same connection ?
>
>
i was almost ready to kiss you, but that seems to not work:

[stephan@host:~/cvs/fossil/libfossil/s2]$ sqlite3 foo.db
SQLite version 3.8.4.1 2014-03-12 19:38:38
Enter ".help" for usage hints.
sqlite> create table t(a);
sqlite> attach 'foo.db' as foo;
sqlite> .schema foo.t
sqlite> .dump foo.t
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> .schema t
CREATE TABLE t(a);
sqlite>

And yet...

sqlite> insert into foo.t(a) values(1),(2),(3);
sqlite> select * from foo.t;
1
2
3
sqlite> ^D
[stephan@host:~/cvs/fossil/libfossil/s2]$ sqlite3 foo.db
SQLite version 3.8.4.1 2014-03-12 19:38:38
Enter ".help" for usage hints.
sqlite> select * from t;
1
2
3

So ... that seems to work (just not with those shell .commands, but that's
okay). i'll try it out and come back crying if it doesn't.

THANK YOU!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

[Simon Slavin]

On 24 Jul 2014, at 3:38pm, Stephan Beal  wrote:

> THANK YOU!

You're welcome.  I'm still learning more from this list than I'm putting out.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

[Stephan Beal]

On Thu, Jul 24, 2014 at 4:45 PM, Simon Slavin  wrote:

>
> On 24 Jul 2014, at 3:38pm, Stephan Beal  wrote:
>
> > THANK YOU!
>
> You're welcome.  I'm still learning more from this list than I'm putting
> out.
>

Hope we never meet, because i will likely kiss you if we do:

[stephan@host:~/cvs/fossil/libfossil/src]$ f-query -e "select * from
ckout.vfile limit 1" -S
SQL TRACE #1: PRAGMA foreign_keys=OFF;
SQL TRACE #2: ATTACH DATABASE '/home/stephan/cvs/fossil/libfossil/_FOSSIL_'
AS ckout;
^^^ that's your baby.

SQL TRACE #3: SELECT value FROM vvar WHERE name='repository';
SQL TRACE #4: ATTACH DATABASE '/home/stephan/cvs/fossil/libfossil.fsl' AS
repo;
SQL TRACE #5: SELECT login FROM user WHERE uid=1;
SQL TRACE #6: SELECT value FROM config WHERE name='allow-symlinks';
SQL TRACE #7: SELECT value FROM vvar WHERE name='checkout';
SQL TRACE #8: SELECT uuid FROM blob WHERE rid=5864;
SQL TRACE #9: BEGIN TRANSACTION;
SQL TRACE #10: select * from ckout.vfile limit 1;
id vid chnged deleted isexe islink rid mrid mtime pathname origname
1397 5864 0 0 0 0 2605 2605 1395763875 .fossil-settings/binary-glob NULL
SQL TRACE #11: COMMIT;
SQL TRACE #12: DETACH DATABASE repo;

before your patch, that would have failed with "unknown db" because ckout
was only known as "main".

So

[stephan@host:~/cvs/fossil/libfossil/src]$ f com -m "Eureka: Simon Slavin
found a way to apply a concrete name to the main db. Seems to work."
fsl_cx.c
Autosync:
http://step...@fossil.wanderinghorse.net/repos/libfossil/index.cgi
Round-trips: 1   Artifacts sent: 0  received: 0
Pull finished with 2964 bytes sent, 2238 bytes received
New_Version: 5abda43115e11c357aa36a1b7231780767b04c23
Autosync:
http://step...@fossil.wanderinghorse.net/repos/libfossil/index.cgi
Round-trips: 1   Artifacts sent: 2  received: 0
Sync finished with 5625 bytes sent, 4906 bytes received

Resulting in:

http://fossil.wanderinghorse.net/repos/libfossil/index.cgi/info/5abda43115e11c357aa36a1b7231780767b04c23


THANK YOU!

(But i still think the ability to rename the main would be a useful
feature!)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] feature request: aliasing (or renaming) db (not table) names

[Stephan Beal]

On Thu, Jul 24, 2014 at 4:51 PM, Stephan Beal  wrote:

> [stephan@host:~/cvs/fossil/libfossil/src]$ f-query -e "select * from
> ckout.vfile limit 1" -S
>

BTW: the -S option has historically meant "SQL Tracing," but i think i'll
rename it to "Simon" now ;). i've been fighting with this db name juggling
for almost exactly a year, and one line of code resolves it completely.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory ownership for sqlite3_temp_directory

[Richard Hipp]

On Wed, Jul 23, 2014 at 1:29 PM, Nicolás Brailovsky  wrote:

>
> Android. If I don't specify the temp dir, I end up triggering a bunch of
> bugs in fuse :)
>

We've heard that before.  And we had a patch in the code at
http://www.sqlite.org/src/info/10707d35786403ea5392d980f593bfecdae063dd to
try to address the issue.  But we later removed that patch when we were
told it didn't completely solve the problem.  More recently, we are told
the patch might have worked after all.

Are you able to try building using the SQLite source code version linked
above and report whether or not the change clears the FuseFS problems you
are seeing?  If you report in the affirmative, we will probably remerge the
patch.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sometimes when my process restarts, it returns error "database is locked"

[Mayank Kumar (mayankum)]

Thanks Simon. I found from the code, there are times when our process dies 
without closing the db , so I will be fixing those.

Regarding
> Do you call have a timeout value set before you make the API call which opens 
> the database ?
How does this impact the locked db ?
-mayank

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Wednesday, July 23, 2014 1:24 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sometimes when my process restarts, it returns error 
"database is locked"


On 23 Jul 2014, at 8:56pm, Mayank Kumar (mayankum)  wrote:

> I have already enabled extended error codes but I am not seeing any extended 
> error codes being returned. I also enabled the error logging callback which 
> just prints the errorcode and the error message which I was already getting 
> when the sqlite3 api was failing.

If you have enabled extended error codes you should be getting extended error 
codes returned by the API calls themselves.  I'm afraid that is all you can get.

> I know this error logging callback can report misuse of api's etc, but can 
> this facility or any other logging facility tell me more about why an api is 
> failing like for .e.g. in some customer scenarios I get sqlite_busy or 
> database is locked and I am trying to add the error logging callback to see 
> if I can get more details on why the database is locked or which process has 
> locked it, etc.

Sorry, SQLite does not know what process has the database locked.  The 
processed do not have codes, and no code is stored then the database is locked, 
just the fact that a process wants it to be locked.

> I have only one process which opens a connection to the database and 
> sometimes  when it restarts and then it detects the database is locked 
> although there is no other process which accesses it.

Do you call have a timeout value set before you make the API call which opens 
the database ?

Your description suggests that your process is not unlocking the database 
correctly when it quits to restart.  Is that process quitting under its own 
control, or is it crashing ?  If it is quitting under its own control is should 
be calling sqlite3_close_v2() and then sqlite3_shutdown() and checking the 
responses to make sure it was correctly able to release all resources.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite lock the entire DB for writes, or lock by table?

[Tom]

OK that's what I figured.

My application presently does all the DB operations on the main thread. But
once a day I need it do the player leaderboards, which could be 500K players
or so, hence several minutes. I can't have the main thread blocking for any
significant length of time - a second or so at most. Hence my desire to do
that one long operation in a separate thread and not have other DB writes
block.

I don't suppose temporary tables would make any difference? I.e. use a
temporary table for the LB results. Would that allow concurrent writes? (to
temporary table, and to normal table, from different threads).

What about using 2 databases?

Cheers



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Does-SQLite-lock-the-entire-DB-for-writes-or-lock-by-table-tp76921p76943.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DMV available in Sqlite?

[Andreas Hofmann]

Hi,

 

I know how to use SQL server DMV for analyzing query performance.   I am
wondering if something similar to the MS SQL Server DMV is available for
Sqlite (http://msdn.microsoft.com/en-us/library/ms188754.aspx)?   Anyone?

 

Thanks

Andy

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite lock the entire DB for writes, or lock by table?

[Richard Hipp]

WAL mode (http://www.sqlite.org/wal.html) allows one process to write to
the database while another reads.  That might solve your problem.


On Thu, Jul 24, 2014 at 10:21 AM, Tom  wrote:

>
> OK that's what I figured.
>
> My application presently does all the DB operations on the main thread. But
> once a day I need it do the player leaderboards, which could be 500K
> players
> or so, hence several minutes. I can't have the main thread blocking for any
> significant length of time - a second or so at most. Hence my desire to do
> that one long operation in a separate thread and not have other DB writes
> block.
>
> I don't suppose temporary tables would make any difference? I.e. use a
> temporary table for the LB results. Would that allow concurrent writes? (to
> temporary table, and to normal table, from different threads).
>
> What about using 2 databases?
>
> Cheers
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Does-SQLite-lock-the-entire-DB-for-writes-or-lock-by-table-tp76921p76943.html
> Sent from the SQLite mailing list archive at Nabble.com.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Scott Robison]

Right, I've seen the 0o prefix syntax, I just figured I was already pushing
my luck with the 0b prefix. Regardless, I wanted to speak against the idea
of true C style octal constants before someone else asked for them. :)

Apologies for top posting, on my phone.
On Jul 24, 2014 2:58 AM, "Markus Schaber"  wrote:

> Hi,
>
> Von: Scott Robison
>
> > On Wed, Jul 23, 2014 at 9:46 PM, J Decker  wrote:
> >
> > > Seems like adding hex interpreting is just adding code for the sake of
> > > adding code.
> > > Unless the data is coming from some pre written text file, isn't just
> > > just as easy to format an into into decimal as it is for hex without
> > > having to add extra characters for the prefix?
> > >
> >
> > One desirable aspect of code is that it is easily understood, that future
> > modifications / changes be as easy as possible. It is usually much
> easier to
> > see that 0x4000 is a single bit set in a 32 bit integer vs seeing the
> > number 1073741824 and wondering exactly what it means. At least it is to
> me.
> >
> > For that reason, I think adding hex constants to the parser is a good
> idea.
> > If I were expressing wishes, I'd suggest adding binary constants (with a
> 0b
> > prefix) but that's probably pushing luck. :)
> >
> > But no octal! At least not using the C syntax. I like being able to
> prefix a
> > decimal integer with 0 and not having it change the meaning (which can't
> be
> > done in C).
>
> Python 3, Rust and others adopted the syntax 0o123 for octal literals.
>
> The IEC 61131 languages use the syntax base#value, so 16#12ab is a hex
> number,
> and 8#123 is an octal number.
>
>
> Best regards
>
> Markus Schaber
>
> CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH
>
> Inspiring Automation Solutions
>
> 3S-Smart Software Solutions GmbH
> Dipl.-Inf. Markus Schaber | Product Development Core Technology
> Memminger Str. 151 | 87439 Kempten | Germany
> Tel. +49-831-54031-979 | Fax +49-831-54031-50
>
> E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS
> store: http://store.codesys.com
> CODESYS forum: http://forum.codesys.com
>
> Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner |
> Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915
>
> This e-mail may contain confidential and/or privileged information. If you
> are not the intended recipient (or have received
> this e-mail in error) please notify the sender immediately and destroy
> this e-mail. Any unauthorised copying, disclosure
> or distribution of the material in this e-mail is strictly forbidden.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Send parameter from batch file to sqlite

[Tony Papadimitriou]

Not possible directly from SQLite.

Some weeks ago I suggested a possible way to have this capability added to 
the shell version of SQLite3 but there seems to be zero interest from the 
developers.  So, don't hold your breath.  Better yet, write a Lua script or 
something to do it.  (If you do, maybe you'd like to share it.)


-Original Message- 
From: shweta gk


...
One of the queries in export.sql has where clause , to which i have to
send a value from batch file. Which syntax is used for this
functionality.

...

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Send parameter from batch file to sqlite

[Scott Robison]

On Wed, Jul 23, 2014 at 4:28 AM, shweta gk  wrote:

> Hi SQlite Support Team,
>
> I have queries to generate csv file written in a export.sql file. I'm
> calling export.sql from a batch file.
>
> One of the queries in export.sql has where clause , to which i have to
> send a value from batch file. Which syntax is used for this
> functionality.
>
> I need to send the a parameter from batch file to export.sql file. How
> can this be done. Kindly send me sample code, as i could not find
> anything regarding this issue in the web search.
>

I don't know if you're still reading since I read a rather terse message
earlier wanting out of the list, but just in case this might help you (or
others) here is a way you can accomplish this sort of task with Windows
batch files without needing any changes to the sqlite command line tool.

Instead of creating a sql script that takes parameters from a batch file,
use the batch file to create the sql script. For example:

mybatchfile.bat:

@REM 
@echo off
if "%1"=="" echo usage: mybatchfile value
if "%1"=="" goto :EOF
echo SELECT * > mysqlscript.sql
echo FROM TABLE >> mysqlscript.sql
echo WHERE COLUMN = %1; >> mysqlscript.sql
sqlite3 database.db ".read mysqlscript.sql"
del mysqlscript.sql
@REM 

Now this is just an example of course and it would require adapting to your
exact situation. Regardless, it should give you an idea of how you can
create the SQL code you need from the batch file itself (expanding
parameters passed from the batch file into the generated SQL) without
needing any modifications to sqlite3.exe.

SDR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sometimes when my process restarts, it returns error "database is locked"

[Simon Slavin]

On 24 Jul 2014, at 8:21pm, Mayank Kumar (mayankum)  wrote:

> Thanks Simon. I found from the code, there are times when our process dies 
> without closing the db , so I will be fixing those.
> 
> Regarding
>> Do you call have a timeout value set before you make the API call which 
>> opens the database ?
> How does this impact the locked db ?

By default, if a process tried to access the database and is locked, SQLite 
immediately returns SQLITE_BUSY or SQLITE_LOCKED and you have to handle the 
problem in your own programming.

If you set a timeout, after finding the database locked, SQLite will wait a 
short while (milliseconds) and then see if the database is unlocked yet.  If 
not, it will was a little longer, then try again.  If still locked it will wait 
a little longer then try again.  It will keep waiting and retrying until the 
timeout period you set has passed.  This is all done inside the SQLite API call 
and you don't have to do any of it inside your own programming.

Many database locking problems are solved by setting a timeout of a couple of 
minutes.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does SQLite lock the entire DB for writes, or lock by table?

[Simon Slavin]

On 24 Jul 2014, at 3:21pm, Tom  wrote:

> My application presently does all the DB operations on the main thread. But
> once a day I need it do the player leaderboards, which could be 500K players
> or so, hence several minutes. I can't have the main thread blocking for any
> significant length of time - a second or so at most. Hence my desire to do
> that one long operation in a separate thread and not have other DB writes
> block.

Even 500K players shouldn't be taking SQLite "a few minutes" unless there are 
no good indexes to use.  Are the scores saved in your player table ?  Do you 
have indexes suitable for the commands you are executing ?

> I don't suppose temporary tables would make any difference? I.e. use a
> temporary table for the LB results. Would that allow concurrent writes? (to
> temporary table, and to normal table, from different threads).

It still won't do what you want.  Dr Hipp's suggestion of WAL mode is probably 
your best bed.  With this you can have many readers and one writer all working 
at the same time.

> What about using 2 databases?

It would probably be better to try figuring out good SELECTs and indexes first.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DMV available in Sqlite?

[Andreas Hofmann]

If not anything available, I could consider porting my db from sqlite to
MSSQL and just change the data access.   Wonder if anyone has attempted this
and if this would give me the information what I am looking for (like: is
the db designed correctly wrt indexes etc.).

 

Thanks,

Andy

 

 

From: Andreas Hofmann [mailto:andreas.hofm...@ku7t.org] 
Sent: Thursday, July 24, 2014 12:33 PM
To: 'General Discussion of SQLite Database'
Subject: DMV available in Sqlite?

 

Hi,

 

I know how to use SQL server DMV for analyzing query performance.   I am
wondering if something similar to the MS SQL Server DMV is available for
Sqlite (http://msdn.microsoft.com/en-us/library/ms188754.aspx)?   Anyone?

 

Thanks

Andy

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DMV available in Sqlite?

[Simon Slavin]

On 24 Jul 2014, at 8:32pm, Andreas Hofmann  wrote:

> I am
> wondering if something similar to the MS SQL Server DMV is available for
> Sqlite

DMV returns information about the database server.  SQLite does not involve any 
servers: all database access is done by looking at the file directly.  There is 
no need for DMV since there is no server to optimize.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DMV available in Sqlite?

[Richard Hipp]

On Thu, Jul 24, 2014 at 3:32 PM, Andreas Hofmann 
wrote:

> Hi,
>
>
>
> I know how to use SQL server DMV for analyzing query performance.   I am
> wondering if something similar to the MS SQL Server DMV is available for
> Sqlite (http://msdn.microsoft.com/en-us/library/ms188754.aspx)?   Anyone?
>

DMV accumulates statistics *on the server*.  But SQLite doesn't have a
server.  So

That said, SQLite does provide information about queries that are not using
indices effectively.  It's up to the application to ask for that
information, though, and then deal with it, because there is no server
available to remember it.  The API you are interested in is

http://www.sqlite.org/c3ref/stmt_status.html

Note that your application has to be engineered to call
sqlite3_stmt_status() after each SQL statement runs, gather the statistics,
then do something with those statistics when the application shuts down.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users