[sqlite] SQLite to SQL Server

[Drago, William @ CSG - NARDA-MITEQ]

All,

Is there a convenient way to transfer data from SQLite to SQL Server? I'm 
presently writing code to do a row by row transfer of all my tables, but the 
thought crossed my mind that maybe there's a better way.

Thanks,
--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] JSON expressions for records and synchoronisation

[James K. Lowden]

On Fri, 10 Apr 2015 14:15:43 -0700
Roger Binns  wrote:

> On 04/10/2015 01:51 PM, Simon Slavin wrote:
> > With a cursory glance I do see important incompatibilities with
> > SQLite.
> 
> I use JSON as the data format for $work stuff (startups) for years,
> and these JSON schemas etc miss why some of us use JSON.  If you are
> using a statically typed language like Java or C++, then arbitrary
> data is very difficult to deal with.  XML is very popular in that
> world because DTDs mean you can make it statically typed.  The mindset
> is that if everything can be specified and constrained in advance,
> then successfully compiling the code means it is mostly correct.

I beg to differ, Roger.  The programming language of choice doesn't
affect the correctness of a function.  A function has preconditions and
postconditions.  They define the domain of the input and the range of
the output.  Failure to meet a precondition invalidates the function,
irrespective of the language used.  Lack of a compiler doesn't change
that.  

It's not a matter of "mindset", and type-correctness isn't eliminated
by uncompiled languages.  I program in Python and C++. In both
languages, if I try to take the substring of an integer, I get an
error.  The difference is that in C++ the error comes from the
complier.  I suspect lack of compilation in popular languages like
Python is driving the current mania for test-driven development.
Instead of letting the compiler find type errors systematically, we use
an ad-hoc framework to discover them manually. This we call
"progress".  

> JSON like several other languages (eg Python, Ruby) is dynamically
> typed.  You can stick anything you want in variables/fields, since the
> type is associated with the value, not the variable name.  

That's fine as far as it goes.  If your language lets you use
variable foo to refer to value X in one place and to value Y somewhere
else, that's OK with me.  

C lets you do the same, by the way, in a limited sense.  One difference
is that in C the name denotes storage, and C won't let you re-use a name
within the same scope to refer to different storage. Another difference
is that C checks to ensure the storage (i.e. value) is used
consistently according to its declared type *before* the program is
executed.  In Python, that work is left to the interpreter, and errors
are consequently discovered later.  

I don't see why name-value association and static type-checking are
necessarily related.  I see that static type-checking in Python is
difficult, but not why it's undesirable.  

> Importing code does not mean it is correct - running it is how you
> figure that out.

Exactly.  

> This allows for far greater flexibility, and especially means you do
> not have to decide in advance exactly what you will do with data.  

I think you know better.  When you write a program, you *have*
decided "in advance" (of running it) "exactly what you will do" with
the data.  What you have not done, in a language like Python, is use
the computer to verify to any extent the correctness of your program
before you run it.  

There are two contradictory language trends in our industry today, one
scientific and one not.  Rooted in computer science are languages like
Haskell that enforce type-checking rigorously and, to a limited extent,
let the programmer write programs that are provably correct.   At the
other end of the spectrum are lightweight languages like Python and
Javascript that offer no typechecking and rely on programmer
discipline and testing to reduce the error count to an acceptable
nonzero number.  

Coming back to SQLite and JSON, the issues of type, domain, and range
continue to be relevant regardless of storage format and rule
enforcement.  JSON won't enforce uniqueness constraints or referential
integrity.  Lack of such support places the burden of correctness on
the programmer, precisely the situation that the relational model was
invented to obviate.  Any apparent "flexibility" afforded by lack of
constraint enforcement is a chimera.  The constraints must be met; it's
only a question of who will make sure they are, and when, and how.  

--jkl

[sqlite] SQLite to SQL Server

[James K. Lowden]

On Mon, 13 Apr 2015 21:38:25 +
"Drago, William @ CSG - NARDA-MITEQ"  wrote:

> Is there a convenient way to transfer data from SQLite to SQL Server? 

If I were doing it, I'd produce tab-delimited files with sqlite3 and
use the SQL Server bcp utility to upload the output.  

The current trunk of the FreeTDS freebcp utility accepts input on a
pipe, such that

$ sqlite3 -separator "  " db "select ... " | freebcp [...]

will work.  

HTH.  

--jkl

[sqlite] JSON expressions for records and synchoronisation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/13/2015 03:15 PM, James K. Lowden wrote:
> I beg to differ, Roger.

Note that I deliberately said "some" and not "all".  Everyone has
their reasons, and not everyone has the same reason.

> The programming language of choice doesn't affect the correctness
> of a function.

I didn't mention compilers, nor are they relevant.  I am generalising
to a mindset, where those that use a static type system as implemented
in various popular languages, tend to lean more heavily on that type
system and its rigidity, hence expecting the same thing from their
data, hence being concerned about schemas etc.  And those using
dynamic type systems as implemented in various popular languages can
favour that same dynamism in their data, where these schema proposals
aren't particularly helpful.  Dynamic data is harder to deal with in
static type systems.

> I think you know better.  When you write a program, you *have* 
> decided "in advance" (of running it) "exactly what you will do"
> with the data.

I mean things like you can just put a single value in that you may
look at later, and then later figure out if a single value or a list
of values is better.  Or make it an object (in the JSON sense) or a
list of them. Dynamic type systems make it far easier to deal with
this, to change your mind later, and to deal with mixtures of
different types in the data.

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUsYzsACgkQmOOfHg372QT0dgCgkSSIlby2WGzFlDw+fxSy8LAe
EwwAnjTNpX5PQ65HHweaSAH0Pv+vBUGV
=6lK3
-END PGP SIGNATURE-

[sqlite] Performance issue

[R.Smith]

On 2015-04-13 09:49 AM, Jeff Roux wrote:
> Hi everyone,
>
> I have copied the original database on my personnal website in a tbz
> archive here :
>
> http://nice-waterpolo.com/misc/db/
>
> There is only one index on timestamp,protocol.

Hi Jeff,

I am not sure what is wrong your side, but the table as I downloaded it 
produced the said query (including all rows) in just over 2 seconds for 
me - that's from a spinning disk.

Good news is I found some improvements. I've included the schema in the 
script below to see I'm not cheating - I dropped the index and made only 
1 index on timestamp - and then for the large query made it ignore the 
index so it was faster through a table scan (but this changes when you 
constrain the selection to a subset, as the last two queries in the 
output show).

Your Query runs on the full set in sub 1-second times with this setup.

This ran in Windows in SQLitespeed while the GUI was up, so if I pushed 
it through the sqlite3.exe command-line shell, another few milliseconds 
would be shaved off.

After the output below I list the pragma settings too in case they 
differ from your setup. I'm hoping the answer presents itself among all 
this. (and I hope the e-mail word-wrapping doesn't mess it up too much).


   -- Processing SQL in: D:\Documents\SQLiteAutoScript.sql
   -- Script Items: 9  Parameter Count: 0
   -- 2015-04-13 14:31:53.280  |  [Info]   Script Initialized, 
Started executing...
   -- 


SELECT sqlite_version() AS Ver;

   -- Ver
   -- ---
   -- 3.8.9


SELECT * FROM sqlite_master;

   -- type| name  | tbl_name   | rootpage   | sql
   -- --- | - | -- |  | 

   -- table   | flows | flows |   2  | CREATE 
TABLE flows (idx INTEGER PRIMARY KEY, ipLan INTEGER,
   -- |   | |  | ipWan INTEGER, 
flags INTEGER, portLan INTEGER, portWan
   -- |   | |  | INTEGER, tos 
INTEGER, protocol INTEGER, wanTcpFlags
   -- |   | |  | INTEGER, 
nbBytesDecompOut INTEGER, nbBytesCompIn INTEGER,
   -- |   | |  | duration 
INTEGER, applId INTEGER, CST INTEGER, SRT INTEGER,
   -- |   | |  | RTT INTEGER, 
lan_retransmission INTEGER, wan_retransmission
   -- |   | |  | INTEGER, 
nbPktDecompIn INTEGER, nbPktCompOut INTEGER,
   -- |   | |  | nbBytesDecompIn 
INTEGER, nbBytesCompOut INTEGER, timestamp
   -- |   | |  | INTEGER, 
rtpTypeLAN INTEGER, rtpPktsLossLAN INTEGER,
   -- |   | |  | rtpJitterLAN 
INTEGER, rtpFactorRLAN INTEGER, rtpTypeWAN
   -- |   | |  | INTEGER, 
rtpPktsLossWAN INTEGER, rtpJitterWAN INTEGER,
   -- |   | |  | rtpFactorWLAN 
INTEGER, nbBytesDbcDecompOut INTEGER,
   -- |   | |  | 
nbBytesDbcCompIn INTEGER, nbBytesDefDecompOut INTEGER,
   -- |   | |  | 
nbBytesDefCompIn INTEGER, nbPktDecompOut INTEGER,
   -- |   | |  | nbPktCompIn 
INTEGER, nbBytesDbcDecompIn INTEGER,
   -- |   | |  | 
nbBytesDbcCompOut INTEGER, nbBytesDefDecompIn INTEGER,
   -- |   | |  | 
nbBytesDefCompOut INTEGER)
   -- table   | application   | application |   4  | 
CREATE TABLE application (id INTEGER CONSTRAINT applpk
   -- |   | |  | PRIMARY KEY, 
shortname VARCHAR(64), name VARCHAR(256))
   -- table   | sqlite_stat1  | sqlite_stat1 |   6  | 
CREATE TABLE sqlite_stat1(tbl,idx,stat)
   -- index   | Idx_flows_timestamp   | flows |   7  | CREATE 
INDEX Idx_flows_timestamp ON flows (timestamp)


SELECT * FROM sqlite_stat1;

   -- tbl   | idx   | stat
   -- - | - | ---
   -- application   |   | 108
   -- flows | Idx_flows_timestamp   | 100 992

-- All-rows Query using the Index
EXPLAIN QUERY PLAN
SELECT SUM(nbBytesDecompOut+nbBytesCompIn) as vol, portLan as item
   FROM flows
  WHERE timestamp BETWEEN 1383770600 AND 1384770600 AND protocol IN (17, 6)
  GROUP BY portLan
  ORDER BY vol DESC LIMIT 6;

   -- selectid | order | from | detail
   --  | - |  | 

   -- 0|   0   |   0  | SEARCH TABLE flows USING INDEX 
Idx_flows_timestamp
   --  |   | 

[sqlite] Performance issue

[Jeff Roux]

Hi everyone,

I have copied the original database on my personnal website in a tbz
archive here :

http://nice-waterpolo.com/misc/db/

There is only one index on timestamp,protocol.

Thanks.

2015-04-08 14:38 GMT+02:00 R.Smith :

>
>
> On 2015-04-08 01:57 PM, Dominique Devienne wrote:
>
>> No Dominique, it's not that -
>>>
>>>  Perhaps. But that still doesn't get to my point. With a LIMIT clause, in
>> such a GROUP BY ORDER BY returning a large result set, would SQLite:
>> 1) sort the whole result-set and then keep only the first top-N rows?
>> 2) or instead do a partial-sort of the first top-N rows only, as in
>> http://www.cplusplus.com/reference/algorithm/partial_sort/?
>>
>> I'm interested in finding out for sure. Perhaps that's highjacking this
>> thread a bit, but in case of SQLite doing #1, and MySQL doing #2, it could
>> explain some of the difference. (although sorting a 1M array is so fast
>> nowadays, I doubt it.).
>>
>
> I think the partial sort algorithm only finds the first N items and then
> stops sorting, but for that to be possible the result set must be present
> in full and finished calculating in full already. The partial sort itself
> might save a millisecond or two from a complete sort in large lists. Either
> way, SQLite is more clever than that as Richard pointed out.
>
>
>  His rows are "fatter", since he mentioned 41 columns. Which might make it
>> go over some threshold(s) (page cache?) slowing things down once past it.
>>
>> But indeed, sharing the DB (if not sensitive data) would be the way to go.
>>
> No no, we know his rows' fatness exactly, he did send the schema, they are
> 41 integer values, i.e it doesn't matter.
>
> So yeah, there must be some trivial thing which the OP (and I) are
> missing. Even an obscured values DB that still causes the slow query will
> work...
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>