Re: [sqlite] Free tool to visualize a schema?

[Peter Halasz]

Unfortunately SQLite does not fully support introspection.

On Sun, Apr 15, 2018 at 7:52 PM, Csányi Pál  wrote:

> Hi,
>
> I think the visualization of a schema helps to develop a sqlite database.
>
> I am searching for a free software, like SchemaCrawler.
> It is good, but can't show CONSTRAINT and FOREIGN KEY names in the diagram.
>
> I just tried out the Sqleton but it can't be start on my system so far.
>
> Do you uses such a free tool?
>
> --
> Best, Pali
> ___
> 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] kooky thought: a vm-only build (for embedded). feasible?

[Richard Hipp]

On 4/15/18, dave  wrote:
> I had a stray thought, and wanted to ask if it's been thunk before,and if so
> what is the thinking?  Or just for commentary.
>
> I have been building a system, part of which uses sqlite and virtual tables.
> This is working great in a desktop/mobile environment.  However, eventually
> one day, I will want to migrate aspects of the product to deeply embedded
> systems (e.g. something like an STM32F4 class chip), and am thinking about
> size -- both code and RAM.  I know about the various compile switches that
> can turn off various features, but I wonder if I can really strip it down
> further by eliminating parsing, query planning, etc, altogether, and only
> support the virtual machine.  I do need virtual tables, though.  In my
> particular use-case, I only need read access -- no create or update.  The
> thinking being that I can build queries offline and compile them into the
> p-code (or whatever it's called), and either burn those well know queries
> into flash, or perhaps send them down the wire as needed.  Then of course
> (maybe even more critically), can I control ram usage in a deterministic way
> such that it will still work on memory-constrained devices (e.g. having a
> total of 128 KiB max for the whole system).
>
> Anway, has this been discussed before?  Or is it a fool's errand?

We did this once, back in 2005, for a startup company in Boston.  It
was called "SSE".  Unfortunately, we didn't continue to support it.  I
went looking for the source code and could not find it.

The database was to run on a smart-card with limited RAM.  All of the
prepared statements were generated on a workstation, then serialized
and stored in a special table in the database file.  The application
would then use a special API that would deserialize a prepared
statement (identified by a well-known integer) then bind parameters
and run it.

So much has changed in the SQLite bytecode engine since then that
there is basically zero chance that SSE would still run today, even if
I could find the source code.

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

Re: [sqlite] CLI thoughts

[Kees Nuyt]

On Sun, 15 Apr 2018 14:38:33 +0100, Martin
 wrote:

>
>  I am a relatively new user of sqlite.  My current use is to learn
> sqlite sql to solve solitaire battleship puzzles. I am not a programmer,
> so use the command line interface on a Mac.
>
>  As a result I wonder whether it is worth considering a few
> amendments to the commands in the CLI tool. My first thoughts being:
>
>  1. adding an escape option to  where it is available; such as
> sha3sum. (Naively I have defined some tables with names begining
> '_'. I know I can rename them, but I prefer not to.)

https://www.sqlite.org/lang_expr.html#like
, the ESCAPE keyword.

>  2. adding a new command to allow a simple variable to be set.
> For example,
>
>   .let a ...
>
> where ... is a bona fide dot command (less the dot) that makes
> usage sense.  For example, I find the .cd command tedious in
> practice. Global exported variables are not interned so such as
> .cd $HOME fails.

Use a temp table (local to the database session):
CREATE TEMP TABLE vars (
var TEXT PRIMARY KEY NOT NULL
,   val 
) WITHOUT ROWID;

Assign a new value:
INSERT OR REPLACE INTO vars (var,val) VALUES ('var1','value1');
Retrieve:
SELECT val FROM vars WHERE var='var1');

For more complex cases, people usually use a host language, like
php, perl, python, tcl, lua, C, dotnet, or even a shell script.
SQLite originally was a database extension to tcl, and that is
powerful.

> Perhaps a sequence like the following:
>
>   .let a system echo $dev
>   .cd -let a
>
> Here, -let as an option was my initail (no thought) idea, but
> thinking of an alternative begins to break the association
> between setting (.let) and using.  In one's mind read '.let a' as
> 'let the variable a be', and '-let a' as 'use the value of letter
> a'.
>
> Further, I thought restricted permissible variable names 'a'
> through to 'z' would be plenty. Certainly better than none as
> seems to be the case currently.  Similarly, they should be
> limited in size to a pathname. Or possibly set the size using
> .limit?
>
> Alternative usage as a dot command option could be
>   .cd -val a
> or
>   .cd $a
> or
>   .cd *a
>
> Obviously these variable names do not exist unless set.
>
> Example (maybe via .read):
>   .once .dat
>   select date('now');
>   .let f system echo "words-`cat .dat`.txt"
>   .once -let f
>   select word from words order by 1;
>
> The system call argument maybe just 'todaysfile' which executes.

You could write and load an extension function or a virtual
table function in C to retrieve exported environment variables.
Somebody probably has done that already. However, those will
only work within SQL, not in dot commands.

The problem with expanding the dot command intterface to a full
scripting language is that there is no end to it, people will
ask for more, like conditional statements, control structures
etc.. 

>  3. the .load command does not have a complement .loaded; how does
> one check?

I guess the easiest way is to just load the extension again, if
you are not sure it is loaded at some point.
If it fails to load, an exception is raised.

> Well, just thinking out loud.

No problem, so do I ;)

-- 
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Free tool to visualize a schema?

[dmp]

> Hi,
> I think the visualization of a schema helps to develop a sqlite database.
>
> I am searching for a free software, like SchemaCrawler.
> It is good, but can't show CONSTRAINT and FOREIGN KEY names in the
> diagram.
>
> I just tried out the Sqleton but it can't be start on my system so far.
> Do you uses such a free tool?
>
> Best, Pali

Hello Pali,

Ajqvue,
A free open source database access tool.
http://ajqvue.com/

It has a visual QueryBuilder, plugin.
http://ajqvue.com/plugins.html
http://ajqvue.com/images/screenshots/querybuilder/querybuilder_diagram.html

The application is in Java, and uses a JDBC to access a SQLite
database, file. At this time I no longer bundle a JDBC, with the
project for SQLite. One can be found though at:

https://github.com/xerial/sqlite-jdbc

Place its Jar in your jre/lib/ext folder and Ajqvue will have
access to it. I have not tried this current library and usually
build my own.

Dana M. Proctor
Ajquve Project Manager

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

Re: [sqlite] kooky thought: a vm-only build (for embedded).feasible?

[dave]

That was more-or-less my thinking.

Mostly, my inquiry is to solicit any advice or wisdom-of-the-ages, or even
advice against it.  After having sent that, I suspect that this would more
likely wind up being something I'm on my own in doing the hands-on work, but
I still welcome any advice on how to approach the surgery.

-dave

> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
> Behalf Of Simon Slavin
> Sent: Sunday, April 15, 2018 2:06 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] kooky thought: a vm-only build (for 
> embedded).feasible?
> 
> 
> 
> 
> On 15 Apr 2018, at 7:54pm, dave  wrote:
> 
> > I wonder if I can really strip it down
> > further by eliminating parsing, query planning, etc, 
> altogether, and only
> > support the virtual machine.
> 
> I wonder what you would find if you looked through the data 
> structure of sqlite3_stmt.  Presumably the compilation 
> process would convert the SQL text into bytecode and the 
> bytecode would be stored in the statement.
> 
> Once you have seen the bytecode from your desired SQL, it 
> might be possible to write a C function which accepts a 
> pointer and a length and creates a statement with a copy of 
> that chunk of memory as the bytecode and everything else set 
> up the way it is in a newly-created statement.
> 
> Or something like that.
> 
> Simon.
> ___
> 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] kooky thought: a vm-only build (for embedded). feasible?

[Simon Slavin]

On 15 Apr 2018, at 7:54pm, dave  wrote:

> I wonder if I can really strip it down
> further by eliminating parsing, query planning, etc, altogether, and only
> support the virtual machine.

I wonder what you would find if you looked through the data structure of 
sqlite3_stmt.  Presumably the compilation process would convert the SQL text 
into bytecode and the bytecode would be stored in the statement.

Once you have seen the bytecode from your desired SQL, it might be possible to 
write a C function which accepts a pointer and a length and creates a statement 
with a copy of that chunk of memory as the bytecode and everything else set up 
the way it is in a newly-created statement.

Or something like that.

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

[sqlite] kooky thought: a vm-only build (for embedded). feasible?

[dave]

I had a stray thought, and wanted to ask if it's been thunk before,and if so
what is the thinking?  Or just for commentary.
 
I have been building a system, part of which uses sqlite and virtual tables.
This is working great in a desktop/mobile environment.  However, eventually
one day, I will want to migrate aspects of the product to deeply embedded
systems (e.g. something like an STM32F4 class chip), and am thinking about
size -- both code and RAM.  I know about the various compile switches that
can turn off various features, but I wonder if I can really strip it down
further by eliminating parsing, query planning, etc, altogether, and only
support the virtual machine.  I do need virtual tables, though.  In my
particular use-case, I only need read access -- no create or update.  The
thinking being that I can build queries offline and compile them into the
p-code (or whatever it's called), and either burn those well know queries
into flash, or perhaps send them down the wire as needed.  Then of course
(maybe even more critically), can I control ram usage in a deterministic way
such that it will still work on memory-constrained devices (e.g. having a
total of 128 KiB max for the whole system).
 
Anway, has this been discussed before?  Or is it a fool's errand?
 
Cheers!
 
-dave
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-autoconf-3200100 : Where is the test suite for sqlite ?

[Dennis Clarke]

On 14/09/17 02:00 PM, Richard Hipp wrote:

On 9/14/17, Dennis Clarke  wrote:

RE : sqlite-autoconf-3200100





The sqlite-autoconf-3200100 tarball strips out all the tests.  Maybe
grab a copy of the canonical source code
(https://sqlite.org/2017/sqlite-src-3200100.zip) and then run
"./configure; make test".



A few tests fail on Debian linux 4.15.0-2-powerpc64 where we have
uname -m says "ppc64" :

.
.
.
Time: zerodamage.test 63 ms
! zipfile-2.4a.2.1 expected: [dirname2 16877 1523740919 {} 
dirname2/file1.txt 33188 1523740919 abcdefghijklmnop dirname3 16877 
1523740919 {}]
! zipfile-2.4a.2.1 got:  [dirname2 17901 1523740919 {} 
dirname2/file1.txt 33188 1523740919 abcdefghijklmnop dirname3 17901 
1523740919 {}]
! zipfile-2.4a.2.2 expected: [dirname2 16877 1523740919 {} 
dirname2/file1.txt 33188 1523740919 abcdefghijklmnop dirname3 16877 
1523740919 {}]
! zipfile-2.4a.2.2 got:  [dirname2 17901 1523740919 {} 
dirname2/file1.txt 33188 1523740919 abcdefghijklmnop dirname3 17901 
1523740919 {}]

Time: zipfile.test 1172 ms
Time: zipfile2.test 76 ms
SQLite 2018-04-10 17:39:29 
4bb2294022060e61de7da5c227a69ccd846ba330e31626ebcd59a94efd148b3b

2 errors out of 144935 tests on nix Linux 64-bit big-endian
!Failures on these tests: zipfile-2.4a.2.1 zipfile-2.4a.2.2
All memory allocations freed - no leaks
Maximum memory usage: 9262760 bytes
Current memory usage: 0 bytes
Number of malloc()  : -1 calls
gmake: *** [Makefile:1187: tcltest] Error 1
Command exited with non-zero status 2
nix$

Is there a way to gather more details from the failed tests?

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

[sqlite] CLI thoughts

[Martin]

  I am a relatively new user of sqlite.  My current use is to learn
sqlite sql to solve solitaire battleship puzzles. I am not a programmer,
so use the command line interface on a Mac.

  As a result I wonder whether it is worth considering a few
amendments to the commands in the CLI tool. My first thoughts being:

  1. adding an escape option to  where it is available; such as
 sha3sum. (Naively I have defined some tables with names begining
 '_'. I know I can rename them, but I prefer not to.)

  2. adding a new command to allow a simple variable to be set.
 For example,

   .let a ...

 where ... is a bona fide dot command (less the dot) that makes
 usage sense.  For example, I find the .cd command tedious in
 practice. Global exported variables are not interned so such as
 .cd $HOME fails.

 Perhaps a sequence like the following:

   .let a system echo $dev
   .cd -let a

 Here, -let as an option was my initail (no thought) idea, but
 thinking of an alternative begins to break the association
 between setting (.let) and using.  In one's mind read '.let a' as
 'let the variable a be', and '-let a' as 'use the value of letter
 a'.

 Further, I thought restricted permissible variable names 'a'
 through to 'z' would be plenty. Certainly better than none as
 seems to be the case currently.  Similarly, they should be
 limited in size to a pathname. Or possibly set the size using
 .limit?

 Alternative usage as a dot command option could be
   .cd -val a
 or
   .cd $a
 or
   .cd *a

 Obviously these variable names do not exist unless set.

 Example (maybe via .read):
   .once .dat
   select date('now');
   .let f system echo "words-`cat .dat`.txt"
   .once -let f
   select word from words order by 1;

 The system call argument maybe just 'todaysfile' which executes.

  3. the .load command does not have a complement .loaded; how does
 one check?

Well, just thinking out loud.

Thanks, Martin.

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

Re: [sqlite] Free tool to visualize a schema?

[Csányi Pál]

2018-04-15 15:49 GMT+02:00 Lifepillar :
> On 15/04/2018 14:14, Csányi Pál wrote:
>>
>> 2018-04-15 14:08 GMT+02:00 R Smith :
>>>
>>> On 2018/04/15 11:52 AM, Csányi Pál wrote:


 Hi,

 I think the visualization of a schema helps to develop a sqlite
 database.

 I am searching for a free software, like SchemaCrawler.
 It is good, but can't show CONSTRAINT and FOREIGN KEY names in the
 diagram.

 I just tried out the Sqleton but it can't be start on my system so far.
>
>
> DbVisualizer (https://www.dbvis.com) is cross-platform, has free/pro
> versions, and has a good visualization tool, which displays foreign key
> names and other constraints (but not, AFAICS, arbitrary constraint́…
> check… clauses). It supports SQLite, too.
>
> Life.

Thanks!

-- 
Best, Paul
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Free tool to visualize a schema?

[Lifepillar]

On 15/04/2018 14:14, Csányi Pál wrote:

2018-04-15 14:08 GMT+02:00 R Smith :

On 2018/04/15 11:52 AM, Csányi Pál wrote:


Hi,

I think the visualization of a schema helps to develop a sqlite database.

I am searching for a free software, like SchemaCrawler.
It is good, but can't show CONSTRAINT and FOREIGN KEY names in the
diagram.

I just tried out the Sqleton but it can't be start on my system so far.


DbVisualizer (https://www.dbvis.com) is cross-platform, has free/pro
versions, and has a good visualization tool, which displays foreign key
names and other constraints (but not, AFAICS, arbitrary constraint́…
check… clauses). It supports SQLite, too.

Life.

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

Re: [sqlite] In memory only WAL file

[Wout Mertens]

ah ok, now I understand. So you would like some setting like "limit writes
to x/s", keeping the intermediate writes in memory.

Here is a great writeup on how to tune sqlite for writes:
https://stackoverflow.com/q/1711631/124416

But depending on your app you might just group a ton of writes in a single
transaction and retain all the regular safeties. E.g. suppose you have an
incoming stream of messages to write, you could group them and confirm
receipt of each message after the transaction completes.

On Tue, Apr 10, 2018, 10:43 AM Pavel Cernohorsky, <
pavel.cernohor...@appeartv.com> wrote:

> I understand that the SQLite tells the OS all the correct things, but as
> I described earlier, that may just produce way too much IOPS, even if
> everything in the chain does the correct thing down to the last silicon
> transistor. That is why I was asking about a way how to go around it by
> holding part of the newest data in memory only and manually checkpoint
> (and I hoped WAL will be a way). And I got some very good suggestions
> earlier.
>
> In my last mail, I have outlined a calculation suggesting the SSD may
> wear out pretty soon with the described load. Yesterday I have found a
> way how not to speculate and get some real data - Samsung's Enterprise
> SSDs which we use provide a diagnostics which measures a real world load
> and using their drive's SMART capabilities calculates the estimated
> SSD's lifetime, in other words saying how bad the load really is. It is
> actually a very useful thing, so if somebody else needs it as well, here
> is the link:
>
> https://www.slideshare.net/SamsungBusinessUSA/using-smart-attributes-to-estimate-enterprise-ssd-lifetime
>
> Pavel
>
>
>
> On 04/09/2018 02:47 PM, Simon Slavin wrote:
> > On 9 Apr 2018, at 8:41am, Pavel Cernohorsky <
> pavel.cernohor...@appeartv.com> wrote:
> >
> >> if you use bigger pro-level SSD, you get on a bit less that 1 year..
> still not good enough. Of course, big unknown in this is the constant
> representing the ability of the SSD controller to merge requests and
> minimize block re-writes.
> > If you compiled SQLite with standard options, and have the correct
> settings for
> >
> > PRAGMA fullfsync  (Macintosh only)
> > PRAGMA journal_mode
> > PRAGMA synchronous
> >
> > then SQLite tells the operating system all the correct things.
> >
> > Whether the operating system, the storage driver, and the storage
> subsystem itself do the right things is out of control of software.  Not
> only can you not tell what's being done, but it can change with what looks
> like a minor change in driver or hardware.
> >
> > Simon.
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Free tool to visualize a schema?

[R Smith]

Ahh, sqleton is probably the best then - Why does it not work for you?

Maybe ask the devs there?


On 2018/04/15 2:14 PM, Csányi Pál wrote:

2018-04-15 14:08 GMT+02:00 R Smith :

On 2018/04/15 11:52 AM, Csányi Pál wrote:

Hi,

I think the visualization of a schema helps to develop a sqlite database.

I am searching for a free software, like SchemaCrawler.
It is good, but can't show CONSTRAINT and FOREIGN KEY names in the
diagram.

I just tried out the Sqleton but it can't be start on my system so far.

Do you uses such a free tool?


I think most SQLite (or indeed other DB) admin systems have some function
for producing schema visualization - at least, in the standard sense of the
word.

Does this example do what you would like?
http://sqlc.rifin.co.za/SchemaDocExample1.html
or (in other colour scheme):
http://sqlc.rifin.co.za/SchemaDocExample2.html

If so you can find those in the schema documentation HTML export of
sqlitespeed (http://sqlc.rifin.co.za/) but it's unfortunately on Windows
only. The sqlitebrowser project has some nice features and work in the *nix
systems too. (http://sqlitebrowser.org/)

There might even be a command-line tool that does a good job, I think I've
seen someone here talking about it, but am not sure now.

Good luck!
Ryan

No, I would like a tool such as sqleton
https://github.com/inukshuk/sqleton



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

Re: [sqlite] Free tool to visualize a schema?

[Csányi Pál]

2018-04-15 14:08 GMT+02:00 R Smith :
> On 2018/04/15 11:52 AM, Csányi Pál wrote:
>>
>> Hi,
>>
>> I think the visualization of a schema helps to develop a sqlite database.
>>
>> I am searching for a free software, like SchemaCrawler.
>> It is good, but can't show CONSTRAINT and FOREIGN KEY names in the
>> diagram.
>>
>> I just tried out the Sqleton but it can't be start on my system so far.
>>
>> Do you uses such a free tool?
>
>
> I think most SQLite (or indeed other DB) admin systems have some function
> for producing schema visualization - at least, in the standard sense of the
> word.
>
> Does this example do what you would like?
> http://sqlc.rifin.co.za/SchemaDocExample1.html
> or (in other colour scheme):
> http://sqlc.rifin.co.za/SchemaDocExample2.html
>
> If so you can find those in the schema documentation HTML export of
> sqlitespeed (http://sqlc.rifin.co.za/) but it's unfortunately on Windows
> only. The sqlitebrowser project has some nice features and work in the *nix
> systems too. (http://sqlitebrowser.org/)
>
> There might even be a command-line tool that does a good job, I think I've
> seen someone here talking about it, but am not sure now.
>
> Good luck!
> Ryan

No, I would like a tool such as sqleton
https://github.com/inukshuk/sqleton

-- 
Best, Paul
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Free tool to visualize a schema?

[R Smith]

On 2018/04/15 11:52 AM, Csányi Pál wrote:

Hi,

I think the visualization of a schema helps to develop a sqlite database.

I am searching for a free software, like SchemaCrawler.
It is good, but can't show CONSTRAINT and FOREIGN KEY names in the diagram.

I just tried out the Sqleton but it can't be start on my system so far.

Do you uses such a free tool?


I think most SQLite (or indeed other DB) admin systems have some 
function for producing schema visualization - at least, in the standard 
sense of the word.


Does this example do what you would like?
http://sqlc.rifin.co.za/SchemaDocExample1.html
or (in other colour scheme):
http://sqlc.rifin.co.za/SchemaDocExample2.html

If so you can find those in the schema documentation HTML export of 
sqlitespeed (http://sqlc.rifin.co.za/) but it's unfortunately on Windows 
only. The sqlitebrowser project has some nice features and work in the 
*nix systems too. (http://sqlitebrowser.org/)


There might even be a command-line tool that does a good job, I think 
I've seen someone here talking about it, but am not sure now.


Good luck!
Ryan

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

Re: [sqlite] SQLite equivalent of SQL Over(Partition)

[Max Vlasov]

Simon,
I think the Group by might work correctly, but sometimes (as in OP case)
would require a lot of rewriting (copy-paste). The key point here is that
the Window function doesn't change the set, but only allows wider access to
other rows of the set at the current row "time". So we just have to move
the group by to the column.

An example.

The good answer to a PARTITION BY question is at

https://stackoverflow.com/questions/2404565/sql-server-difference-between-partition-by-and-group-by#30907639
(I enforced the second popular answer)

The table
  is CREATE TABLE [TableA] ([id] integer, [firstname] text, [lastname]
text, [Mark] integer)

and the PARTITION BY query from the answer

  select SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname from TableA

can be rewritten in SQlite as

  select (select sum(mark) from TableA grpTableA where
grpTableA.id=TableA.id) as marksum, firstname from TableA

The problem with the rewriting of OP query is that we have to move the
whole FROM TblContractTasks ... part to the column with the group by
sub-query. Not only the query becomes less readable, we will probably get
some performance penalty unless Sqlite does some optimization for the
column sub-query. But at least it's formally possible.

Max


On Fri, Apr 6, 2018 at 8:20 PM, Simon Slavin  wrote:

> On 5 Apr 2018, at 11:41am, DThomas  wrote:
>
> > Select DISTINCT Sites.Customer, Sites.Digit,
> > Count(TblContractTasks.TaskNumber)
> > OVER (PARTITION BY Sites.Digit)  As TaskCount
> > FROM TblContractTasks INNER Join (Sites INNER Join TblContractDetails On
> > Sites.Digit = TblContractDetails.SiteDigit) On
> > TblContractTasks.ContractNumber = TblContractDetails.ContractNumber
> > WHERE (Sites.Consultant='xx' ) ORDER BY Sites.Digit
>
> Instead of PARTITION BY use GROUP BY .  See "with COUNT function" example
> here:
>
> 
>
> I think everything else used will continue to work.
>
> Simon.
> ___
> 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

[sqlite] Free tool to visualize a schema?

[Csányi Pál]

Hi,

I think the visualization of a schema helps to develop a sqlite database.

I am searching for a free software, like SchemaCrawler.
It is good, but can't show CONSTRAINT and FOREIGN KEY names in the diagram.

I just tried out the Sqleton but it can't be start on my system so far.

Do you uses such a free tool?

-- 
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users