[sqlite] Memory Leak in ext/misc/compress.c

[Keith Medcalf]

static void uncompressFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const unsigned char *pIn;
  unsigned char *pOut;
  unsigned int nIn;
  unsigned long int nOut;
  int rc;
  int i;

  pIn = sqlite3_value_blob(argv[0]);
  nIn = sqlite3_value_bytes(argv[0]);
  nOut = 0;
  for(i=0; i

[sqlite] Appropriate Uses For SQLite

[Simon Slavin]

On 25 Feb 2015, at 8:13pm, Jim Callahan  
wrote:

> I first learned about SQLite in the Bioconductor branch of R. I figured if
> they could handle massive genetic databases in SQLite, SQLite ought to be
> able to handle a million (or even 12 million) voters in a voter file.

I'm guessing that you're not British, but it's funny to a Brit to see someone 
with your name posting about voters:



Simon.

[sqlite] recurrent failure mode

[Simon Slavin]

On 25 Feb 2015, at 8:23pm, Dave Dyer  wrote:

> The facile explanation would be that a transaction to insert a new
> record was executed twice, but the indexes were incorrectly maintained.
> 
> INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda 
> Richardson','scrollPos','0');

Can you provide the schema (the CREATE TABLE and any CREATE INDEX commands) for 
that table ?

Do you have any multi-access things going on ?  Two or more computers, 
applications, processes or threads trying to access the database at the same 
time ?

Does your application check the result code returned from all sqlite3_ calls ?  
Not just the ones inserting rows, but also those opening and closing the file, 
setting PRAGMAs, or anything else it does with the sqlite3_ library ?

Simon.

[sqlite] recurrent failure mode

[James K. Lowden]

On Wed, 25 Feb 2015 16:26:45 -0800
Dave Dyer  wrote:

> >Do you have any multi-access things going on ?  Two or more
> >computers, applications, processes or threads trying to access the
> >database at the same time ?
> 
> No, but it would be normal for the database to be on a different
> computer than the sqlite client, and be using whatever networked 
> file system is common.  The culprit clients seem to be macs, we're
> still seeking more information about the specifics.

You might want to read my message on the topic from the list archives,
dated Sat, 31 Jan 2015.  

Although "bugs" are frequently blamed, in fact the semantics of
networked filesystems are different from that of local filesystems.
Making a database work on a network filesystem might be possible, but
requires considerable work to support cache coherency.  Expecting
SQLite to do that is not too different from expecting to read a letter
the moment it's dropped in the mailbox across town.  

Just consider this scenario:  two clients open the same database, where
the file is on a fileserver somewhere on the network.  Each one does,
say, "select * from T" and peruses the data.  Then, 

client A inserts record 17:
lock record
insert row
free lock

All good.  Now,

client B inserts record 17:
lock record
insert row
free lock

Should result in a primary key violation, right?  No.  

Client A has updated his *local* cache, his in-memory image of some
part of the database.  That's not a SQLite cache; that's the kernel's
filebuffer cache representing the state of some part of the filesystem.
It's completely correct from A's point of view.  SQLite depends on that
cache being correct, and it is: from A's point of view.  

If B is on the same machine as A, they share a single, kernel-provided
filebuffer cache, and when B attempt to insert the duplicate record,
SQLite will see A's record and reject the insert.  

N machines is N caches.  When A flushes his cache with sync(2), how
long before B learns of the change?  With N=1 (same machine), B
learns instantaneously.  With N > 1?  NFS promises only that B will
see A's changes after closing and reopening the file.  

When B is on a different machine, the local representation of the state
of the filesystem does not include A's update.  SQLite examines the
"file", sees no record 17, and updates its local image.  When the
kernel eventually flushes B's update, A's local cache becomes stale.  

With some "luck", you can actually go on like this for a while with no
one noticing.  As long as different clients are updating different
parts of the database and fortuitously refereshing their caches (by
re-reading updated parts that the network isn't helpfully caching,
too), it can seem to sort of work.  Failure is guaranteed in the most
important scenario: when SQLite requires a coherent cache and doesn't
have one.  

So it really doesn't matter if the locking mechanism on network
filesystems are perfect.  By *design*, they make weaker promises than
Posix.  Expecting them to do something they're documented not to do is
asking for trouble.  Which is to say, unfortunately, that you got what
you asked for.  :-/

HTH.  

--jkl

[sqlite] Date - invlaid datetime

[Keith Medcalf]

Date's represented as text (and the textual representation of a date) in SQLite 
are big endian (ISO8601).  THis means that the biggest part goes on the left, 
the next biggest to the right of that, and so on.  Time is in the 24 hour clock 
and AM/PM are not used.  THis results in sortable strings as long as the GMT 
offset is always the same (for a localized datetime) or always GMT if no offset 
is specified (ie, the timestring is naive).  You could try using strptime (from 
the standard C library) to parse the date into a normal format, or ask for the 
datetime as a unix epoch time or a JD/MJD/RD etc datetime number.  See 
https://www.sqlite.org/lang_datefunc.html for how SQLite handles datetime data.

Microsoft, being a weeny company that only exists in a single timezone is 
notoriously bad at handling date/time data (they have never produced or 
released a product that can handle date/times properly, and the few they have 
bought they have immediately made "Microsoft Compatible" and rendered 
completely useless at handling date/time information), however I cannot believe 
that you cannot set the format in which you want the data returned to you.  You 
want either ISO8601 or JIS.  JIS (Japanese Industrial Format) being the closest 
thing that Microsoft comprehends to a rational datestring format.

You can also request the datetime in huns (hundredths of a second from 
1980-01-01 00:00:00 GMT) and convert that into either a unix epoch or JD 
variant as numeric data is intrinsically sortable.

You can also, rather than using MS program derived data to get "now" to store 
in your create date field, source it from the sqlite function datetime('now') 
which will return the correct ISO8601 GMT timestring.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
>bounces at mailinglists.sqlite.org] On Behalf Of DSI
>Sent: Wednesday, 25 February, 2015 10:14
>To: sqlite-users at sqlite.org
>Subject: [sqlite] Date - invlaid datetime
>
>Good Morning,
>
>I am building a Windows Universal App using Azure Mobile
>Services and implementing offline data using sqlite as a local database.
>Everything is working great.  Thank you for making this available.  When
>I
>Sync the data everything works well except for the created date column
>which
>has a format of "2/20/2015 2:13:26 AM +00:00".  When I attempt to view
>the
>data in the sqlite table I receive an error stating that
>"System.FormatException: String was not recognized as a valid DateTime".
>We
>have no control over how azure stores the date.  As a work around I have
>also been storing the date as a string in a text field but this makes
>trying
>to sort by date impossible.  I would like to sort by the created date but
>cannot if sqlite sees it as an invalid date.
>
>
>
>Any help you can offer would be greatly appreciated.
>
>
>
>Thank you
>
>David
>
>___
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Appropriate Uses For SQLite

[Paul Linehan]

Hi Dr. Hipp, group,


> In a feeble effort to do "marketing",

I am sure that nothing you do is "feeble"!


> I have revised the "Appropriate
> Uses For SQLite" webpage to move trendy buzzwords like "Internet of
> Things" and "Edge of the Network" above the break.  See:


Nothing wrong with TLAs (some would call them FTLAs :-) )


> https://www.sqlite.org/whentouse.html

> Please be my "focus group", and provide feedback, comments,
> suggestions, and/or criticism about the revised document.   Send your
> remarks back to this mailing list, or directly to me at the email in
> the signature.


I will preface anything I say by saying that I have done very little with
SQLite - I ran Bugzilla (compiled from source) with it once - great
for passing bugs around and other bits and pieces (College projects
and such).

I have however always been impressed by SQLite's ability to
squeeze a lot into a small package - SQLite punches well
above its weight!

Take a look here (at slide 13)
https://www.usenix.org/legacy/events/lisa11/tech/slides/stonebraker.pdf


I think that using SQLite as the db server (where SQLite is effectively the
VoltDB engine) in a VoltDB scenario, putting database requests into some
sort of queue - each transaction runs to completion, (thus eliminating
latching, locking, buffering, recovery (undo/redo) - which Stonebraker feels
to be useless "work") might be something worth examining.

There was another thread about
"The only thing I'd change about SQLite is the SQL bit.".

Now, I'll readily confess that I didn't grasp everything, but it seems
to me that this is starting to go down the route of separate storage
engines (? la MySQL). I don't think this is a good idea - SQLite
(Doh! look at the name) does SQL - when something better comes along,
then let it do that, but it should IMHO remain true to that.

Respectfully submitted.


Paul...


> D. Richard Hipp

-- 

linehanp at tcd.ie

Mob: 00 353 86 864 5772

[sqlite] sqlite3_column_count and sqlite3_data_count

[Richard Hipp]

On 2/25/15, Igor Tandetnik  wrote:
>
> Why both are needed, I'm not sure.
>

Historical accident
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] sqlite3_column_count and sqlite3_data_count

[Igor Tandetnik]

On 2/25/2015 7:03 PM, Bart Smissaert wrote:
> Could somebody tell me what the difference is between these 2 functions?

sqlite3_column_count() returns correct number as soon as the statement 
is prepared. sqlite3_data_count returns zero unless the statement is 
actually positioned on a row (sqlite3_step was called, and returned 
SQLITE_ROW).

Why both are needed, I'm not sure.
-- 
Igor Tandetnik

[sqlite] Database connection from within Visual Studio

[Erik Ejlskov Jensen]

Yes, there a several things that could be done, given statistics on number of 
downloads.


A simple fix could be to reverse the order of the .net framework versions 
listed, so list 4,5,1 at the top and 2,0 at the bottom.


And then Group all downloads for a Framework version together, instead of 
having 3 sections per Framework version. And indicate/promote/highlight the 
most useful.


So order by .net version, then bitness,( then usefullnes?if that is possible)



Sendt fra Surface

[sqlite] PhD student

[VASILEIOU Eleftheria]

 Hi,

I would need to use R for my analysis for my Project and my supervisor 
suggested me to learn the SQL language for R.
Could you please provide me some resources for learning SQL and R?


Thanks in advance,
Eleftheria

Eleftheria Vasileiou BSc, MPH
Research Student, Centre for Population Health Sciences
Room 815, Old Medical School, University of Edinburgh

E.Vasileiou at ed.ac.uk
-- next part --
An embedded and charset-unspecified text was scrubbed...
Name: not available
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150225/8d92f1dd/attachment.ksh>

[sqlite] recurrent failure mode

[Dave Dyer]

>
>Can you provide the schema (the CREATE TABLE and any CREATE INDEX commands) 
>for that table ?

CREATE TABLE preference_table (
 preferenceSet text,/* name of this preference group */
 preferenceName text,   /* a preference in this group */
 preferenceValue text   /* sort order of this k...;
CREATE UNIQUE INDEX preferenceindex on 
preference_table(preferenceSet,preferenceName);



>Do you have any multi-access things going on ?  Two or more computers, 
>applications, processes or threads trying to access the database at the same 
>time ?

No, but it would be normal for the database to be on a different
computer than the sqlite client, and be using whatever networked 
file system is common.  The culprit clients seem to be macs, we're
still seeking more information about the specifics.

>Does your application check the result code returned from all sqlite3_ calls ? 
> Not just the ones inserting rows, but also those opening and closing the 
>file, setting PRAGMAs, or anything else it does with the sqlite3_ library ?

Yes.  It all goes through a common interface function which is
careful about checking.

As I said in the original message, this is something that has been
working without problems for a few years, the only thing that's changing
is the network and OS environment it's deployed in.  My hypothesis is
that a new failure mode in the file system is tickling a sqlite bug.
Based on the evidence available now, a transaction that is trying to 
insert 4 records fails, and is retried, resulting in 8 records which
can't be indexed.

[sqlite] sqlite crash with malloc_printerr in sqlite3MemFree

[Zhao Lin]

A short while after I submitted the post we checked our log files and found a 
few SQLITE_FULL errors which may have something to do with this. We will 
investigate on that.

Thanks for your reply!

Regards,
Zhao

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Wednesday, February 25, 2015 12:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite crash with malloc_printerr in sqlite3MemFree

On 2/25/15, Zhao Lin  wrote:
> Hi all,
>
> We sometimes, not always reproducible but it does recur from time to 
> time, get the following crash stack with sqlite3 version 3.8.1, we 
> couldn't tell much from our stack, wondering if there are possibly 
> related bugs on sqlite3?

The usual cause of problems inside of malloc() is heap corruption within the 
application.  Have you tried running with Valgrind to see what that shows?

>
> #0  0x7fdd66214925 in raise () from /lib64/libc.so.6
> #1  0x7fdd66216105 in abort () from /lib64/libc.so.6
> #2  0x7fdd66252837 in __libc_message () from /lib64/libc.so.6
> #3  0x7fdd66258166 in malloc_printerr () from /lib64/libc.so.6
> #4  0x7fdd6887d867 in sqlite3MemFree () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #5  0x7fdd6887e66b in sqlite3_free () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #6  0x7fdd6888ab26 in pcache1Free () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #7  0x7fdd6888ac11 in pcache1FreePage () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #8  0x7fdd6888b040 in pcache1TruncateUnsafe () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #9  0x7fdd6888b901 in pcache1Truncate () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #10 0x7fdd6888a4cb in sqlite3PcacheTruncate () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #11 0x7fdd6888a519 in sqlite3PcacheClear () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #12 0x7fdd6888cfec in pager_reset () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #13 0x7fdd6888f58e in sqlite3PagerClose () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #14 0x7fdd688994c1 in sqlite3BtreeClose () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #15 0x7fdd6890729f in sqlite3LeaveMutexAndCloseZombie () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #16 0x7fdd689071bf in sqlite3Close () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #17 0x7fdd689071e3 in sqlite3_close () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #18 0x7fdd680631a5 in dbhandle_close_write (handle=0x7fffa8968d88,
> should_abort=0) at dbhandle.c:1809
> ...
>
> Thanks,
> Zhao
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

This might give an impression of the scale of what the BioConductor people
are doing.

"The Gene Expression Omnibus (GEO) at the National Center for Biotechnology
Information (NCBI) is the largest fully public repository [as of 2005] for
high-throughput molecular abundance data, primarily gene expression data."
http://www.ncbi.nlm.nih.gov/pubmed/15608262

"The NCBI Gene Expression Omnibus (GEO) represents the largest public
repository of microarray data. However, finding data in GEO can be
challenging. We have developed GEOmetadb in an attempt to make querying the
GEO metadata both easier and more powerful. All GEO metadata records as
well as the relationships between them are parsed and stored in a local
MySQL database. ... In addition, a Bioconductor package, GEOmetadb that
utilizes a SQLite export of the entire GEOmetadb database is also
available, rendering the entire GEO database accessible with full power of
SQL-based queries from within R."
http://www.ncbi.nlm.nih.gov/pubmed/18842599

Annotation Database Interface

Bioconductor version: Release (3.0)

Provides user interface and database connection code for annotation data
packages using SQLite data storage.

Author: Herve Pages, Marc Carlson, Seth Falcon, Nianhua Li

Maintainer: Bioconductor Package Maintainer 

Citation (from within R, enter citation("AnnotationDbi")):

Pages H, Carlson M, Falcon S and Li N. *AnnotationDbi: Annotation Database
Interface*. R package version 1.28.1.

http://master.bioconductor.org/packages/release/bioc/html/AnnotationDbi.html

To really understand the enormity of what they attempting, you need a
picture like the one "Figure 1: Annotation Packages: the big picture" on
the first page of this document:
http://master.bioconductor.org/packages/release/bioc/vignettes/AnnotationDbi/inst/doc/IntroToAnnotationPackages.pdf

Just to grasp the scale and complexity of what they are doing; one of the
databases mentioned GO.db stores a gigantic directed acyclic graph (DAG).

"GOBPANCESTOR Annotation of GO Identifiers to their Biological Process
Ancestors Description This data set describes associations between GO
Biological Process (BP) terms and their ancestor BP terms, based on the
directed acyclic graph (DAG) defined by the Gene Ontology Consortium. The
format is an R object mapping the GO BP terms to all ancestor terms, where
an ancestor term is a more general GO term that precedes the given GO term
in the DAG (in other words, the parents, and all their parents, etc.)."

I get the idea that they are storing a DAG in a SQLite database for use in
R, explaining "associations between GO Biological Process (BP) terms and
their ancestor BP terms, based on the directed acyclic graph (DAG) defined
by the Gene Ontology Consortium."

DAG, SQLite, R, Biological Processes and Gene Ontology in one paragraph;
oh, my head hurts, I think I'll stick to simpler stuff.

Jim





On Wed, Feb 25, 2015 at 3:13 PM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> I first learned about SQLite in the Bioconductor branch of R. I figured if
> they could handle massive genetic databases in SQLite, SQLite ought to be
> able to handle a million (or even 12 million) voters in a voter file.
>
> Here is a brief article from 2006, "How to Use SQLite with R" by Seth
> Falcon.
>
> http://master.bioconductor.org/help/course-materials/2006/rforbioinformatics/labs/thurs/SQLite-R-howto.pdf
> Jim
>
> On Thu, Feb 19, 2015 at 2:08 PM, Jim Callahan <
> jim.callahan.orlando at gmail.com> wrote:
>
>> Strongly agree with using the R package Sqldf.
>> I used both RSQLite and Sqldf, both worked extremely well (and I am both
>> a lazy and picky end user). Sqldf had the advantage that it took you all
>> the way to your destination the workhorse R object the data frame (R can
>> define new objects, but the data frame as an in memory table is the
>> default).
>> The SQLITE3 command line interface and the R command line had a nice
>> synergy; SQL was great for getting a subset of rows and columns or building
>> a complex view from multiple tables. Both RSqlite and Sqldf could
>> understand the query/view as a table and all looping in both SQL and R took
>> place behind the scenes in compiled code.
>>
>> Smart phone users say "there is an app for that". R users would say
>> "there is a package for that" and CRAN is the equivalent of the Apple app
>> store or Google Play.
>>
>> R has packages for graphics, classical statistics, Bayesian statistics
>> and machine learning. R also has packages for spacial statistics (including
>> reading ESRI shapefiles), for graph theory and for building decision trees.
>> There is another whole app store for biological applications "bioconductor".
>>
>> The CRAN website has "views" (pages or blogs) showing how packages solve
>> common problems in a variety of academic disciplines or application areas.
>>
>> Jim Callahan
>>  On Feb 19, 2015 11:38 AM, "Gabor Grothendieck" 
>> wrote:
>>
>>> On Wed, Feb 18, 2015 at 9:53 AM, Richard Hipp  

[sqlite] sqlite crash with malloc_printerr in sqlite3MemFree

[Richard Hipp]

On 2/25/15, Zhao Lin  wrote:
> Hi all,
>
> We sometimes, not always reproducible but it does recur from time to time,
> get the following crash stack with sqlite3 version 3.8.1, we couldn't tell
> much from our stack, wondering if there are possibly related bugs on
> sqlite3?

The usual cause of problems inside of malloc() is heap corruption
within the application.  Have you tried running with Valgrind to see
what that shows?

>
> #0  0x7fdd66214925 in raise () from /lib64/libc.so.6
> #1  0x7fdd66216105 in abort () from /lib64/libc.so.6
> #2  0x7fdd66252837 in __libc_message () from /lib64/libc.so.6
> #3  0x7fdd66258166 in malloc_printerr () from /lib64/libc.so.6
> #4  0x7fdd6887d867 in sqlite3MemFree () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #5  0x7fdd6887e66b in sqlite3_free () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #6  0x7fdd6888ab26 in pcache1Free () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #7  0x7fdd6888ac11 in pcache1FreePage () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #8  0x7fdd6888b040 in pcache1TruncateUnsafe () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #9  0x7fdd6888b901 in pcache1Truncate () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #10 0x7fdd6888a4cb in sqlite3PcacheTruncate () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #11 0x7fdd6888a519 in sqlite3PcacheClear () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #12 0x7fdd6888cfec in pager_reset () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #13 0x7fdd6888f58e in sqlite3PagerClose () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #14 0x7fdd688994c1 in sqlite3BtreeClose () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #15 0x7fdd6890729f in sqlite3LeaveMutexAndCloseZombie () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #16 0x7fdd689071bf in sqlite3Close () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #17 0x7fdd689071e3 in sqlite3_close () from
> /opt/DataInsight/bin/../lib/libsqlite3.so.0
> #18 0x7fdd680631a5 in dbhandle_close_write (handle=0x7fffa8968d88,
> should_abort=0) at dbhandle.c:1809
> ...
>
> Thanks,
> Zhao
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Regarding creating a mem object and copying contents to it in SQLite

[Sairam Gaddam]

Yaa we work together and we need some assistance regarding that.

On Wed, Feb 25, 2015 at 1:18 PM, Hick Gunter  wrote:

> Maybe you would like to exchange experiences with Prakash Premkumar <
> prakash.prax at gmail.com> about hacking SQLite internals.
>
> -Urspr?ngliche Nachricht-
> Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
> Gesendet: Mittwoch, 25. Februar 2015 08:23
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Regarding creating a mem object and copying contents
> to it in SQLite
>
> I am trying to optimize certain operations of SQLite internally, so i
> created mem object.
>
> On Wed, Feb 25, 2015 at 12:36 PM, Hick Gunter  wrote:
>
> > The mem object is internal to sqlite, it is not intended to be
> > created/changed by user code.
> >
> > What are you trying to do that makes you think you need to manipulate
> > internal structures?
> >
> > -Urspr?ngliche Nachricht-
> > Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
> > Gesendet: Mittwoch, 25. Februar 2015 07:41
> > An: sqlite-users at sqlite.org
> > Betreff: [sqlite] Regarding creating a mem object and copying contents
> > to it in SQLite
> >
> > hello,
> >  I created a mem object in SQLite and copied a string value to
> > its member and i initialized some other members to default values
> > manually, it worked well but some times it shows realloc() error.
> >  I also found a function sqlite3VdbeMemSetStr() which will
> > copy the string value into new mem but even it fails sometimes.
> > So what is the correct way of creating a mem object and
> > copying a string to its member so that SQLite will work
> fruitfully.Anyone know???
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> > ___
> >  Gunter Hick
> > Software Engineer
> > Scientific Games International GmbH
> > FN 157284 a, HG Wien
> > Klitschgasse 2-4, A-1130 Vienna, Austria
> > Tel: +43 1 80100 0
> > E-Mail: hick at scigames.at
> >
> > This communication (including any attachments) is intended for the use
> > of the intended recipient(s) only and may contain information that is
> > confidential, privileged or legally protected. Any unauthorized use or
> > dissemination of this communication is strictly prohibited. If you
> > have received this communication in error, please immediately notify
> > the sender by return e-mail message and delete all copies of the
> > original communication. Thank you for your cooperation.
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] new line now \r\n regardless the OS

[Andrea Giammarchi]

Funny enough, apparently we all get \n in Windows now :-)

( and yes, \r\n in Linux, isn't this hilarious )

Somebody filed a bug about this and I've actually/eventually/finally
patched my code doing lazy evaluation of whatever comes after a 'SELECT 1'
in CSV mode so that I should be sure enough that's the end of the line I am
looking for.

Not sure this will affect more Windows users/programs though

There was a but about buffered / non-buffered flush per each query in order
to normalize behaviour in Linux, Mac, and Windows, maybe that forced flush
went it and now things are different for Widnows only ?

Not sure that's even related but thanks for double checking.

Best Regards



On Wed, Feb 25, 2015 at 1:40 PM, Richard Hipp  wrote:

> On 2/25/15, Andrea Giammarchi  wrote:
> > Dear All,
> >   it looks like we are back in town with '\n' at the end of a line **even
> > in CSV** mode.
> >
> > Can anyone please confirm me (ccing me since apparently I'm not part of
> > this ML) that SQLite no longer forces RFC4180 \r\n ?
> >
>
> I get \r\n in CSV output from the command-line shell in SQLite 3.8.8.x
> when I try it on Linux.
>
> --
> D. Richard Hipp
> drh at sqlite.org
>

[sqlite] Sqlite subqueries

[Rob Richardson]

A Google search for "USS Yorktown" turned up the following:

"On September 21, 1997, a division by zero error on board the USS Yorktown 
(CG-48) Remote Data Base Manager brought down all the machines on the network, 
causing the ship's propulsion system to fail."

RobR

-Original Message-


To eliminate the need to reference a table would require combining  300 tables 
into one table. A user editing entries for one space could crash the whole 
system. That's basically what happened aboard the Yorktown in 1997. A cook 
trying to enter an item into the lunch menu killed the engines on the ship.

[sqlite] Appropriate Uses For SQLite

[Jim Callahan]

I first learned about SQLite in the Bioconductor branch of R. I figured if
they could handle massive genetic databases in SQLite, SQLite ought to be
able to handle a million (or even 12 million) voters in a voter file.

Here is a brief article from 2006, "How to Use SQLite with R" by Seth
Falcon.
http://master.bioconductor.org/help/course-materials/2006/rforbioinformatics/labs/thurs/SQLite-R-howto.pdf
Jim

On Thu, Feb 19, 2015 at 2:08 PM, Jim Callahan <
jim.callahan.orlando at gmail.com> wrote:

> Strongly agree with using the R package Sqldf.
> I used both RSQLite and Sqldf, both worked extremely well (and I am both a
> lazy and picky end user). Sqldf had the advantage that it took you all the
> way to your destination the workhorse R object the data frame (R can define
> new objects, but the data frame as an in memory table is the default).
> The SQLITE3 command line interface and the R command line had a nice
> synergy; SQL was great for getting a subset of rows and columns or building
> a complex view from multiple tables. Both RSqlite and Sqldf could
> understand the query/view as a table and all looping in both SQL and R took
> place behind the scenes in compiled code.
>
> Smart phone users say "there is an app for that". R users would say "there
> is a package for that" and CRAN is the equivalent of the Apple app store or
> Google Play.
>
> R has packages for graphics, classical statistics, Bayesian statistics and
> machine learning. R also has packages for spacial statistics (including
> reading ESRI shapefiles), for graph theory and for building decision trees.
> There is another whole app store for biological applications "bioconductor".
>
> The CRAN website has "views" (pages or blogs) showing how packages solve
> common problems in a variety of academic disciplines or application areas.
>
> Jim Callahan
>  On Feb 19, 2015 11:38 AM, "Gabor Grothendieck" 
> wrote:
>
>> On Wed, Feb 18, 2015 at 9:53 AM, Richard Hipp  wrote:
>> > On 2/18/15, Jim Callahan  wrote:
>> >> I would mention the open source statistical language R in the "data
>> >> analysis" section.
>> >
>> > I've heard of R but never tried to use it myself.  Is an SQLite
>> > interface built into R, sure enough?  Or is that something that has to
>> > be added in separately?
>> >
>>
>> RSQLite is an add-on package to R; however, for data analysis (as
>> opposed to specific database manipulation) I would think most R users
>> would use my sqldf R add-on package (which uses RSQLite by default and
>> also can use driver packages of certain other databases) rather than
>> RSQLite directly if they were going to use SQL for that.
>>
>> In R a data.frame is like an SQL table but in memory and sqldf lets
>> you apply SQL statements to them as if they were all one big SQLite
>> database.  A common misconception is it must be slow but in fact its
>> sufficiently fast that some people use it to get a speed advantage
>> over plain R.  Others use it to learn SQL or to ease the transition to
>> R and others use it allow them to manipulate R data frames without
>> knowing much about R provided they know SQL.
>>
>> If you have not tried R this takes you through installing R and
>> running sqldf in about 5 minutes:
>> https://sqldf.googlecode.com/#For_Those_New_to_R
>>
>> The rest of that page gives many other examples.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>

[sqlite] Sqlite subqueries

[russ lyttle]

On 02/25/2015 10:31 AM, Rob Richardson wrote:
> A Google search for "USS Yorktown" turned up the following:
> 
> "On September 21, 1997, a division by zero error on board the USS Yorktown 
> (CG-48) Remote Data Base Manager brought down all the machines on the 
> network, causing the ship's propulsion system to fail."
> 
> RobR
> 
> -Original Message-
> 
> 
> To eliminate the need to reference a table would require combining  300 
> tables into one table. A user editing entries for one space could crash the 
> whole system. That's basically what happened aboard the Yorktown in 1997. A 
> cook trying to enter an item into the lunch menu killed the engines on the 
> ship.
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
That's the basic story. The DB people claimed the OS should have
protected against the division by zero, the OS people claimed the DB
should have not let the cook overflow the menu record and cause a
division by 0. The ship had to be towed in, the ship got under weigh in
a couple of hours, etc. My goal is to make sure that nothing like any of
that happens. Or if it does has the least effect possible.

It's beginning to look like I should replace the arduinos with R-Pi
model A running sqlite3. The 'a' db then becomes a data collection and
report generator, each remote has an individual 'b' db. Everything can
be managed by simple sql and Python scripts. The R-Pi has more computing
power than the old towers and are lots cheaper.





-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150225/f727abfc/attachment.pgp>

[sqlite] How to insert a pointer data to sqlite?

[Joseph T.]

Unless, I'm wrong. What you want to do is use two tables. One to store the node 
values and another that references them for whatever object using them. Say, 
points for a pair of triangles, a,b,c,d,e. If table triangle is a table 
pointing at the table point (id,object,point) you could have a triangle sharing 
points and then when the shared point is changed the triangles would change to 
if reloaded.




Sent from my Samsung Epic? 4G TouchYAN HONG YE  wrote:I 
have a data:
Id  pid namemark
1   0   f1   sample
2   1   f2   sample
3   1   f3   sample
4   2   f4   sample
5   2   *id(2).name *id(2).mark

These means that under id(2) and id(5) have same node, if change one of the 
node, the other update auto,
How to realize this function?
Thank you!

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

[sqlite] Sqlite with VS2013

[Manoj Sakhare]

Can anyone please guide me to install sqlite desinger capabilities for
VS2013.

I have installed 'sqlite-netFx451-setup-bundle-x86-2013-1.0.94.0.exe' on my
machine.

I am not able to get the sqlite option in 'Choose Data Source' window.

I am having VS2013 Professional version,

Attached is the installation log.

-- 

-- 
Manoj Sakhare
-- next part --
2015-02-25 13:20:28.498   Log opened. (Time zone: UTC+05:30)
2015-02-25 13:20:28.498   Setup version: Inno Setup version 5.5.5 (a)
2015-02-25 13:20:28.498   Original Setup EXE: 
D:\Smita\sqlite-netFx451-setup-bundle-x86-2013-1.0.94.0.exe
2015-02-25 13:20:28.498   Setup command line: 
/SL5="$8052A,10256641,56832,D:\Smita\sqlite-netFx451-setup-bundle-x86-2013-1.0.94.0.exe"
 
2015-02-25 13:20:28.498   Windows version: 6.1.7601 SP1  (NT platform: Yes)
2015-02-25 13:20:28.498   64-bit Windows: Yes
2015-02-25 13:20:28.498   Processor architecture: x64
2015-02-25 13:20:28.498   User privileges: Administrative
2015-02-25 13:20:28.498   64-bit install mode: No
2015-02-25 13:20:28.498   Created temporary directory: 
C:\Users\msakhare\AppData\Local\Temp\is-HMOF4.tmp
2015-02-25 13:20:28.529   Extracting temporary file: 
C:\Users\msakhare\AppData\Local\Temp\is-HMOF4.tmp\vcredist_x86_2013_VSU2.exe
2015-02-25 13:21:49.008   Starting the installation process.
2015-02-25 13:21:49.008   Creating directory: C:\Program Files 
(x86)\System.Data.SQLite
2015-02-25 13:21:49.008   Creating directory: C:\Program Files 
(x86)\System.Data.SQLite\2013
2015-02-25 13:21:49.008   Creating directory: C:\Program Files 
(x86)\System.Data.SQLite\2013\bin
2015-02-25 13:21:49.008   Creating directory: C:\Program Files 
(x86)\System.Data.SQLite\2013\doc
2015-02-25 13:21:49.008   Creating directory: C:\Program Files 
(x86)\System.Data.SQLite\2013\GAC
2015-02-25 13:21:49.008   Directory for uninstall files: C:\Program Files 
(x86)\System.Data.SQLite\2013\uninstall
2015-02-25 13:21:49.008   Creating directory: C:\Program Files 
(x86)\System.Data.SQLite\2013\uninstall
2015-02-25 13:21:49.008   Creating new uninstall log: C:\Program Files 
(x86)\System.Data.SQLite\2013\uninstall\unins000.dat
2015-02-25 13:21:49.008   -- File entry --
2015-02-25 13:21:49.008   Dest filename: C:\Program Files 
(x86)\System.Data.SQLite\2013\uninstall\unins000.exe
2015-02-25 13:21:49.008   Time stamp of our file: 2015-02-25 13:20:28.337
2015-02-25 13:21:49.008   Installing the file.
2015-02-25 13:21:49.008   Uninstaller requires administrator: Yes
2015-02-25 13:21:49.008   Successfully installed the file.
2015-02-25 13:21:49.008   -- File entry --
2015-02-25 13:21:49.008   Dest filename: C:\Program Files 
(x86)\System.Data.SQLite\2013\System.Data.SQLite.url
2015-02-25 13:21:49.008   Time stamp of our file: 2014-09-05 12:16:40.000
2015-02-25 13:21:49.008   Installing the file.
2015-02-25 13:21:49.055   Successfully installed the file.
2015-02-25 13:21:49.055   -- File entry --
2015-02-25 13:21:49.055   Dest filename: C:\Program Files 
(x86)\System.Data.SQLite\2013\readme.htm
2015-02-25 13:21:49.055   Time stamp of our file: 2014-09-06 01:29:52.000
2015-02-25 13:21:49.055   Installing the file.
2015-02-25 13:21:49.055   Successfully installed the file.
2015-02-25 13:21:49.055   -- File entry --
2015-02-25 13:21:49.055   Dest filename: C:\Program Files 
(x86)\System.Data.SQLite\2013\GAC\System.Data.SQLite.dll
2015-02-25 13:21:49.055   Time stamp of our file: 2014-09-05 19:22:16.000
2015-02-25 13:21:49.055   Installing the file.
2015-02-25 13:21:49.102   Successfully installed the file.
2015-02-25 13:21:49.102   Incrementing shared file count (32-bit).
2015-02-25 13:21:49.102   Installing into GAC
2015-02-25 13:21:49.911   -- File entry --
2015-02-25 13:21:49.911   Dest filename: C:\Program Files 
(x86)\System.Data.SQLite\2013\bin\System.Data.SQLite.dll
2015-02-25 13:21:49.911   Time stamp of our file: 2014-09-05 19:22:16.000
2015-02-25 13:21:49.911   Installing the file.
2015-02-25 13:21:49.911   Successfully installed the file.
2015-02-25 13:21:49.911   -- File entry --
2015-02-25 13:21:49.911   Dest filename: C:\Program Files 
(x86)\System.Data.SQLite\2013\bin\System.Data.SQLite.pdb
2015-02-25 13:21:49.911   Time stamp of our file: 2014-09-05 19:22:14.000
2015-02-25 13:21:49.911   Installing the file.
2015-02-25 13:21:49.973   Successfully installed the file.
2015-02-25 13:21:49.973   -- File entry --
2015-02-25 13:21:49.973   Dest filename: C:\Program Files 
(x86)\System.Data.SQLite\2013\bin\System.Data.SQLite.dll.config
2015-02-25 13:21:49.973   Time stamp of our file: 2014-06-17 13:10:14.000
2015-02-25 13:21:49.973   Installing the file.
2015-02-25 13:21:49.973   Successfully installed the file.
2015-02-25 13:21:49.973   -- File entry --
2015-02-25 13:21:49.973   Dest filename: C:\Program Files 
(x86)\System.Data.SQLite\2013\GAC\System.Data.SQLite.Linq.dll
2015-02-25 13:21:49.973   Time stamp of our file: 2014-09-05 19:25:32.000
2015-02-25 13:21:49.973   Installing the file.
2015-02-25 

[sqlite] Regarding creating a mem object and copying contents to it in SQLite

[Sairam Gaddam]

I am trying to optimize certain operations of SQLite internally, so i
created mem object.

On Wed, Feb 25, 2015 at 12:36 PM, Hick Gunter  wrote:

> The mem object is internal to sqlite, it is not intended to be
> created/changed by user code.
>
> What are you trying to do that makes you think you need to manipulate
> internal structures?
>
> -Urspr?ngliche Nachricht-
> Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
> Gesendet: Mittwoch, 25. Februar 2015 07:41
> An: sqlite-users at sqlite.org
> Betreff: [sqlite] Regarding creating a mem object and copying contents to
> it in SQLite
>
> hello,
>  I created a mem object in SQLite and copied a string value to its
> member and i initialized some other members to default values manually, it
> worked well but some times it shows realloc() error.
>  I also found a function sqlite3VdbeMemSetStr() which will copy
> the string value into new mem but even it fails sometimes.
> So what is the correct way of creating a mem object and copying a
> string to its member so that SQLite will work fruitfully.Anyone know???
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] sqlite crash with malloc_printerr in sqlite3MemFree

[Zhao Lin]

Hi all,

We sometimes, not always reproducible but it does recur from time to time, get 
the following crash stack with sqlite3 version 3.8.1, we couldn't tell much 
from our stack, wondering if there are possibly related bugs on sqlite3?

#0  0x7fdd66214925 in raise () from /lib64/libc.so.6
#1  0x7fdd66216105 in abort () from /lib64/libc.so.6
#2  0x7fdd66252837 in __libc_message () from /lib64/libc.so.6
#3  0x7fdd66258166 in malloc_printerr () from /lib64/libc.so.6
#4  0x7fdd6887d867 in sqlite3MemFree () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#5  0x7fdd6887e66b in sqlite3_free () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#6  0x7fdd6888ab26 in pcache1Free () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#7  0x7fdd6888ac11 in pcache1FreePage () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#8  0x7fdd6888b040 in pcache1TruncateUnsafe () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#9  0x7fdd6888b901 in pcache1Truncate () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#10 0x7fdd6888a4cb in sqlite3PcacheTruncate () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#11 0x7fdd6888a519 in sqlite3PcacheClear () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#12 0x7fdd6888cfec in pager_reset () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#13 0x7fdd6888f58e in sqlite3PagerClose () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#14 0x7fdd688994c1 in sqlite3BtreeClose () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#15 0x7fdd6890729f in sqlite3LeaveMutexAndCloseZombie () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#16 0x7fdd689071bf in sqlite3Close () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#17 0x7fdd689071e3 in sqlite3_close () from 
/opt/DataInsight/bin/../lib/libsqlite3.so.0
#18 0x7fdd680631a5 in dbhandle_close_write (handle=0x7fffa8968d88, 
should_abort=0) at dbhandle.c:1809
...

Thanks,
Zhao

[sqlite] recurrent failure mode

[Dave Dyer]

We're experiencing a new, recurrent failure mode in an old (ie; not recently 
changed) sqlite application.   This may be associated with buggy networked
file system implementations (thanks to apple and/or microsoft)

The apparent problem is that indexes on a small table become corrupted
by not being unique.  Except for the non-uniqueness of the index keys,
there's no apparent damage.

The facile explanation would be that a transaction to insert a new
record was executed twice, but the indexes were incorrectly maintained.

INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda 
Richardson','scrollPos','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda 
Richardson','nFill','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda 
Richardson','placeInBW','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda 
Richardson','DB_Subset','');


I suppose that this might be a sqlite bug if the "insert records" step
and the "maintain indexes" step were separated by a disk error and the
rollback of the failed transaction was incomplete.

[sqlite] recurrent failure mode

[Dave Dyer]

We're experiencing a new, recurrent failure mode in an old (ie; not recently 
changed) sqlite application.   This may be associated with buggy networked
file system implementations (thanks to apple and/or microsoft)

The apparent problem is that indexes on a small table become corrupted
by not being unique.  Except for the non-uniqueness of the index keys,
there's no apparent damage.

The facile explanation would be that a transaction to insert a new
record was executed twice, but the indexes were incorrectly maintained.

INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda 
Richardson','scrollPos','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda 
Richardson','nFill','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda 
Richardson','placeInBW','0');
INSERT INTO "preference_table" VALUES('Picture Placer-707-1304b-19-Maranda 
Richardson','DB_Subset','');


I suppose that this might be a sqlite bug if the "insert records" step
and the "maintain indexes" step were separated by a disk error and the
rollback of the failed transaction was incomplete.

[sqlite] Regarding creating a mem object and copying contents to it in SQLite

[Sairam Gaddam]

hello,
 I created a mem object in SQLite and copied a string value to its
member and i initialized some other members to default values manually, it
worked well but some times it shows realloc() error.
 I also found a function sqlite3VdbeMemSetStr() which will copy the
string value into new mem but even it fails sometimes.
So what is the correct way of creating a mem object and copying a
string to its member so that SQLite will work fruitfully.Anyone know???

[sqlite] How to insert a pointer data to sqlite?

[Christopher Vance]

Have you considered normalizing your database?

On Wed, Feb 25, 2015 at 12:07 PM, YAN HONG YE  wrote:

> I have a data:
> Id  pid namemark
> 1   0   f1  sample
> 2   1   f2  sample
> 3   1   f3  sample
> 4   2   f4  sample
> 5   2   *id(2).name *id(2).mark
>
> These means that under id(2) and id(5) have same node, if change one of
> the node, the other update auto,
> How to realize this function?
> Thank you!
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Christopher Vance

[sqlite] Is readline ubiquitous on 32-bit x86 Linux?

[Christopher Vance]

Or there's editline, used for some things as a partial readline substitute.

On Wed, Feb 25, 2015 at 5:12 AM, Andreas Kupries 
wrote:

> A possible (and small alternative) to readline would be Antirez
> "linenoise".
> Steve Bennet's fork adds windows portability and some other things.
>
> https://github.com/antirez/linenoise
> https://github.com/msteveb/linenoise
>
> That is small enough to be directly built as part of the shell, I believe.
>
>
>
> On Tue, Feb 24, 2015 at 10:01 AM, Dan Kennedy 
> wrote:
> >
> >
> > The pre-built sqlite3 shell tool for x86 Linux available for download
> here:
> >
> >   http://www.sqlite.org/download.html
> >
> > does not include readline support. Which makes it painful to use.
> >
> > Does anyone think that many systems would be affected if it dynamically
> > linked against the system readline? This means that the binary would not
> > work on systems without libreadline.so installed. Or is readline
> considered
> > ubiquitous by now?
> >
> > Dan.
> >
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Andreas Kupries
> Senior Tcl Developer
> Code to Cloud: Smarter, Safer, Faster?
> F: 778.786.1133
> andreask at activestate.com, http://www.activestate.com
> Learn about Stackato for Private PaaS: http://www.activestate.com/stackato
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Christopher Vance

[sqlite] Sqlite subqueries

[russ lyttle]

On 02/25/2015 09:40 AM, Igor Tandetnik wrote:
> On 2/25/2015 9:16 AM, russ lyttle wrote:
>> To eliminate the need to reference a table would require combining  300
>> tables into one table.
> 
> Yes.
> 
>> A user editing entries for one space could crash
>> the whole system.
> 
> I don't see how this follows.
> 
>> That's basically what happened aboard the Yorktown in
>> 1997. A cook trying to enter an item into the lunch menu killed the
>> engines on the ship.
> 
> Did the software store engine configuration and menu in the same table?
> Did the software need to run statements joining engine configuration
> tables with lunch menu tables, thus necessitating putting them into the
> same database? How is scattering essentially the same data across 300
> different tables is expected to help prevent a similar mishap?
> 
>> It's beginning to look like the 'b' table should be broken into a
>> separate db and the 'a' table have indicators as to which table in b.db
>> to use.
> 
> What failure mode do you envision that would be avoided by this design?

There was a lot of finger pointing about the Yorktown incident. The DB
people blaming NT, the Microsoft people blaming the DB. Apparently the
cook tried to enter more items in the supper menu than was allowed and
caused a cascading failure ending in an engine shutdown. A complete
system reboot was required. Whether or not that was due to the DB or OS
doesn't matter now. The goal is to prevent similar events. Or at least
make it obvious where the fault lies.

I was personally involved in one incident where an occupant stuck a soda
can in a damper and caused a system shutdown.

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150225/699f61a0/attachment.pgp>

[sqlite] Regarding creating a mem object and copying contents to it in SQLite

[Simon Slavin]

On 25 Feb 2015, at 10:21am, Sairam Gaddam  wrote:

> Yaa we work together and we need some assistance regarding that.

See our responses to Prakash Premkumar.  The things he thought he could 
optimize turned out to be unhelpful.  You will find that just using normal 
SQLite calls you get acceptable performance.

Nobody here is going to tell you how to hack the internal structures of SQLite. 
 The source code from SQLite is completely public and includes plentiful 
comments.  If you can't work out what you need to do by reading the source 
code, it's probably not useful to do that thing.

> So what is the correct way of creating a mem object and
> copying a string to its member so that SQLite will work
> fruitfully.

Create your own memory object using whatever tools your language gives you.  
Use a _bind_ function when you need SQLite to 'see' that object and a _column_ 
function when you need to retrieve it.




Simon.

[sqlite] Sqlite subqueries

[Igor Tandetnik]

On 2/25/2015 9:16 AM, russ lyttle wrote:
> To eliminate the need to reference a table would require combining  300
> tables into one table.

Yes.

> A user editing entries for one space could crash
> the whole system.

I don't see how this follows.

> That's basically what happened aboard the Yorktown in
> 1997. A cook trying to enter an item into the lunch menu killed the
> engines on the ship.

Did the software store engine configuration and menu in the same table? 
Did the software need to run statements joining engine configuration 
tables with lunch menu tables, thus necessitating putting them into the 
same database? How is scattering essentially the same data across 300 
different tables is expected to help prevent a similar mishap?

> It's beginning to look like the 'b' table should be broken into a
> separate db and the 'a' table have indicators as to which table in b.db
> to use.

What failure mode do you envision that would be avoided by this design?
-- 
Igor Tandetnik

[sqlite] Sqlite subqueries

[russ lyttle]

On 02/24/2015 08:53 PM, Igor Tandetnik wrote:
> On 2/24/2015 8:42 PM, russ lyttle wrote:
>> The 'a' table defines spaces to be controlled, the 'b' tables the
>> control schedules and parameters.
>> It would not be unreasonable to assume the 'a' table has >100 rows.
>> Each row in the 'a' table is associated with 3 'b' tables, all the names
>> known in advance and created off line at the same time as the row in the
>> 'a' table.
>> Each 'b' table has up to 1,440 rows.
> 
> Replace these three tables with a single table, holding three times as
> many rows. It would have an extra column holding the "original source"
> indicator - a value that indicates which of the three tables this row
> originated from. Now, in table "a" store this indicator where you
> planned to store the table name.

Thanks for the input.

To eliminate the need to reference a table would require combining  300
tables into one table. A user editing entries for one space could crash
the whole system. That's basically what happened aboard the Yorktown in
1997. A cook trying to enter an item into the lunch menu killed the
engines on the ship.

It's beginning to look like the 'b' table should be broken into a
separate db and the 'a' table have indicators as to which table in b.db
to use.

If there were an sqlite3 sketch for arduino, that would work better than
my original plan. Each space could have its own copy of the tables it is
to use. Lots of database management and communications problems go away.

-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 473 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20150225/6e08d46f/attachment.pgp>

[sqlite] Date - invlaid datetime

[DSI]

Good Morning, 

I am building a Windows Universal App using Azure Mobile
Services and implementing offline data using sqlite as a local database.
Everything is working great.  Thank you for making this available.  When I
Sync the data everything works well except for the created date column which
has a format of "2/20/2015 2:13:26 AM +00:00".  When I attempt to view the
data in the sqlite table I receive an error stating that
"System.FormatException: String was not recognized as a valid DateTime".  We
have no control over how azure stores the date.  As a work around I have
also been storing the date as a string in a text field but this makes trying
to sort by date impossible.  I would like to sort by the created date but
cannot if sqlite sees it as an invalid date.



Any help you can offer would be greatly appreciated.



Thank you

David 

[sqlite] Using incremental BLOB functions against a BLOB column in a virtual table

[Dominique Devienne]

On Wed, Feb 25, 2015 at 7:52 AM, Hick Gunter  wrote:

> And the "explain" output will contain virtual table opcodes
>

I use this technique to discover which tables and virtual tables a view
accesses (instead of trying to parse the query.)

I execute "explain select rowid from $viewname", and look for TableLock and
VOpen "opcode"s, and look at the "p4" column for table names and for
virtual tables, p4 contains vtab:module-hex-address:vtable-hex-address,
where module-hex-address is your sqlite3_module* in hexadecimal, and
vtable-address your sqlite3_vtab*, which typically you code can translate
to the vtable names. --DD

[sqlite] new line now \r\n regardless the OS

[Andrea Giammarchi]

also relevant: https://tools.ietf.org/html/rfc2046#section-4.1.1

grabbed from:

 Encoding considerations:

  As per section 4.1.1. of RFC 2046
 [3
], this media type uses
CRLF
  to denote line breaks.  However, implementors should be aware that
  some implementations may use other values.




On Wed, Feb 25, 2015 at 8:48 AM, Andrea Giammarchi <
andrea.giammarchi at gmail.com> wrote:

> Dear All,
>   it looks like we are back in town with '\n' at the end of a line **even
> in CSV** mode.
>
> Can anyone please confirm me (ccing me since apparently I'm not part of
> this ML) that SQLite no longer forces RFC4180 \r\n ?
>
> Thank you!
>
> On Sun, Aug 17, 2014 at 7:28 PM, Andrea Giammarchi <
> andrea.giammarchi at gmail.com> wrote:
>
>> I wonder if this is meant to be or not, it seems that starting from
>> version `3.8.6` sqlite3-cli/shell now uses only \r\n strings instead of
>> using the default new line in the OS.
>>
>> This broke badly a spawned parser of mine but I'd like to know the
>> rationale behind this choice, it does not feel right to find that \r\n in
>> Linux or Mac.
>>
>> Thanks in advance for any clarification, I am just trying to fix properly
>> an open source library of mine that suddenly does not work anymore.
>>
>> Best Regards
>>
>
>

[sqlite] new line now \r\n regardless the OS

[Andrea Giammarchi]

Dear All,
  it looks like we are back in town with '\n' at the end of a line **even
in CSV** mode.

Can anyone please confirm me (ccing me since apparently I'm not part of
this ML) that SQLite no longer forces RFC4180 \r\n ?

Thank you!

On Sun, Aug 17, 2014 at 7:28 PM, Andrea Giammarchi <
andrea.giammarchi at gmail.com> wrote:

> I wonder if this is meant to be or not, it seems that starting from
> version `3.8.6` sqlite3-cli/shell now uses only \r\n strings instead of
> using the default new line in the OS.
>
> This broke badly a spawned parser of mine but I'd like to know the
> rationale behind this choice, it does not feel right to find that \r\n in
> Linux or Mac.
>
> Thanks in advance for any clarification, I am just trying to fix properly
> an open source library of mine that suddenly does not work anymore.
>
> Best Regards
>

[sqlite] new line now \r\n regardless the OS

[Richard Hipp]

On 2/25/15, Andrea Giammarchi  wrote:
> Dear All,
>   it looks like we are back in town with '\n' at the end of a line **even
> in CSV** mode.
>
> Can anyone please confirm me (ccing me since apparently I'm not part of
> this ML) that SQLite no longer forces RFC4180 \r\n ?
>

I get \r\n in CSV output from the command-line shell in SQLite 3.8.8.x
when I try it on Linux.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Regarding creating a mem object and copying contents to it in SQLite

[Hick Gunter]

Maybe you would like to exchange experiences with Prakash Premkumar 
 about hacking SQLite internals.

-Urspr?ngliche Nachricht-
Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
Gesendet: Mittwoch, 25. Februar 2015 08:23
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Regarding creating a mem object and copying contents to 
it in SQLite

I am trying to optimize certain operations of SQLite internally, so i created 
mem object.

On Wed, Feb 25, 2015 at 12:36 PM, Hick Gunter  wrote:

> The mem object is internal to sqlite, it is not intended to be
> created/changed by user code.
>
> What are you trying to do that makes you think you need to manipulate
> internal structures?
>
> -Urspr?ngliche Nachricht-
> Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
> Gesendet: Mittwoch, 25. Februar 2015 07:41
> An: sqlite-users at sqlite.org
> Betreff: [sqlite] Regarding creating a mem object and copying contents
> to it in SQLite
>
> hello,
>  I created a mem object in SQLite and copied a string value to
> its member and i initialized some other members to default values
> manually, it worked well but some times it shows realloc() error.
>  I also found a function sqlite3VdbeMemSetStr() which will
> copy the string value into new mem but even it fails sometimes.
> So what is the correct way of creating a mem object and
> copying a string to its member so that SQLite will work fruitfully.Anyone 
> know???
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the use
> of the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you
> have received this communication in error, please immediately notify
> the sender by return e-mail message and delete all copies of the
> original communication. Thank you for your cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Regarding creating a mem object and copying contents to it in SQLite

[Hick Gunter]

The mem object is internal to sqlite, it is not intended to be created/changed 
by user code.

What are you trying to do that makes you think you need to manipulate internal 
structures?

-Urspr?ngliche Nachricht-
Von: Sairam Gaddam [mailto:gaddamsairam at gmail.com]
Gesendet: Mittwoch, 25. Februar 2015 07:41
An: sqlite-users at sqlite.org
Betreff: [sqlite] Regarding creating a mem object and copying contents to it in 
SQLite

hello,
 I created a mem object in SQLite and copied a string value to its 
member and i initialized some other members to default values manually, it 
worked well but some times it shows realloc() error.
 I also found a function sqlite3VdbeMemSetStr() which will copy the 
string value into new mem but even it fails sometimes.
So what is the correct way of creating a mem object and copying a 
string to its member so that SQLite will work fruitfully.Anyone know???
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Using incremental BLOB functions against a BLOB column in a virtual table

[Hick Gunter]

Yes. The stored SQL string will contain the keyword "VIRTUAL".



asql> select sql from sqlite_master where name='sgb_dd_draw';

sql

-

CREATE VIRTUAL TABLE sgb_dd_draw USING Partition ( 
'sgbDrawDefPartitionDM-PARTITION_ACISQL.xml', sgb_dd_draw )



And the "explain" output will contain virtual table opcodes



asql> explain select jurisdiction_no, game_no from sgb_dd_draw limit 1;

addr  opcode p1p2p3p4 p5  comment

  -        -  --  -

?

3 VOpen  0 0 0 vtab:1D46D128:2B9BCF7769D0  00  NULL

..

6 VFilter0 122   00  NULL

7 VColumn0 0 400  
sgb_dd_draw.jurisdiction_no

8 VColumn0 1 500  sgb_dd_draw.game_no

?



-Urspr?ngliche Nachricht-
Von: Evans, Randall [mailto:REvans at seasoft.com]
Gesendet: Dienstag, 24. Februar 2015 20:18
An: sqlite-users at mailinglists.sqlite.org
Betreff: Re: [sqlite] Using incremental BLOB functions against a BLOB column in 
a virtual table



Thanks to Richard Hipp and Hick Gunter for their replies on this topic.



Given that support for support for BLOBs in virtual tables differs from that 
for BLOBs in physical tables, is there any method or function available to the 
sqlite3_x() caller that can be used to distinguish tables defined as 
virtual tables vs. physical tables?



Thanks in advance for any assistance provided.



Randy Evans



>

> From: Richard Hipp mailto:drh at sqlite.org>>

>

> On 2/23/15, Evans, Randall mailto:REvans at 
> seasoft.com>> wrote:

> > Can the incremental BLOB functions (sqlite_blob_open(),

> > sqlite_blob_read(),

> > etc) access BLOB column data defined in a SQLite virtual table? If

> > it matters, I am only interested in read-only access at this time.

>

> no.

>

> >

> > If the answer is yes, is there any facility in virtual table support

> > for xColumn or any other method that will expose to the virtual

> > table module the positioning/length values supplied as the 3rd and

> > 4th arguments of the

> > sqlite_blob_read() function (N bytes and offset values)?

>

> There would have to be, in order for incremental blob I/O to be

> supported on virtual tables.  The absence of such a method is one

> reason why the answer above is "no".

>

> >

> >

> > Randy Evans

___

sqlite-users mailing list

sqlite-users at mailinglists.sqlite.org

http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna,Austria
Tel: +43 1 80100 - 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] Sqlite with VS2013

[Joe Mistachkin]

Please try the 1.0.95.0 pre-release of that same install package.

Sent from my iPod

> On Feb 25, 2015, at 12:08 AM, Manoj Sakhare  wrote:
> 
> Can anyone please guide me to install sqlite desinger capabilities for
> VS2013.
> 
> I have installed 'sqlite-netFx451-setup-bundle-x86-2013-1.0.94.0.exe' on my
> machine.
> 
> I am not able to get the sqlite option in 'Choose Data Source' window.
> 
> I am having VS2013 Professional version,
> 
> Attached is the installation log.
> 
> -- 
> 
> -- 
> Manoj Sakhare
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is readline ubiquitous on 32-bit x86 Linux?

[Stephan Beal]

On Tue, Feb 24, 2015 at 7:12 PM, Andreas Kupries 
wrote:

> A possible (and small alternative) to readline would be Antirez
> "linenoise".
> Steve Bennet's fork adds windows portability and some other things.
>
> https://github.com/antirez/linenoise
> https://github.com/msteveb/linenoise
>
> That is small enough to be directly built as part of the shell, I believe.
>

i can +1 that - been using it in several trees since ... seems like a year
or more. The only minor oddity of its build is that it requires C99.

editline is not as well-known on Linux systems, and rarely (if ever)
preinstalled, whereas linenoise is easy to embed in one's project.

Linenoise example:

http://fossil.wanderinghorse.net/r/libfossil/dir/s2

the Makefile uses the linenoise directory (copied 1-to-1 from the github
repo) to link linenoise in to that app (shell.c). Grep the makefile and
shell.c for "linenoise" to see the compile flags and API uses (essentially
identical to the basic readline API).

-- 
- 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] Err

[R.Smith]

There is nothing unusual about the output - it looks exactly as expected 
(without knowing what is inside the tables referenced).

As Igor already asked: Is there anything specific you find unusual?

Is it the stats block that confuses you, but which you specifically 
requested with the ".stats on" command?
Is it the fact that your version_control table is empty?
Is it the fact that the .tables command does not work in upper case?

Give us a clue! :)


On 2015-02-24 07:42 PM, Jonathan Camilleri wrote:
> Unusual output when trying a SQL Select statement from the command line
>
> SQLite version 3.8.8.2 2015-01-30 14:30:45
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .open taxi-invoicing-db
> sqlite> .stats on
> sqlite> .tables
> Address  Invoice  VERSION_CONTROL
> Customer InvoiceLine
> sqlite> select * from version_control;
> Memory Used: 80840 (max 86816) bytes
> Number of Outstanding Allocations:   182 (max 193)
> Number of Pcache Overflow Bytes: 6904 (max 6904) bytes
> Number of Scratch Overflow Bytes:0 (max 0) bytes
> Largest Allocation:  64000 bytes
> Largest Pcache Allocation:   1176 bytes
> Largest Scratch Allocation:  0 bytes
> Lookaside Slots Used:10 (max 54)
> Successful lookaside attempts:   173
> Lookaside failures due to size:  41
> Lookaside failures due to OOM:   0
> Pager Heap Usage:7508 bytes
> Page cache hits: 7
> Page cache misses:   6
> Page cache writes:   0
> Schema Heap Usage:   4032 bytes
> Statement Heap/Lookaside Usage:  2368 bytes
> Fullscan Steps:  0
> Sort Operations: 0
> Autoindex Inserts:   0
> Virtual Machine Steps:   8
> sqlite> select count(1) from version_control;
> 0
> Memory Used: 81824 (max 86816) bytes
> Number of Outstanding Allocations:   184 (max 193)
> Number of Pcache Overflow Bytes: 8080 (max 8080) bytes
> Number of Scratch Overflow Bytes:0 (max 0) bytes
> Largest Allocation:  64000 bytes
> Largest Pcache Allocation:   1176 bytes
> Largest Scratch Allocation:  0 bytes
> Lookaside Slots Used:6 (max 54)
> Successful lookaside attempts:   193
> Lookaside failures due to size:  46
> Lookaside failures due to OOM:   0
> Pager Heap Usage:8672 bytes
> Page cache hits: 1
> Page cache misses:   1
> Page cache writes:   0
> Schema Heap Usage:   4040 bytes
> Statement Heap/Lookaside Usage:  1616 bytes
> Fullscan Steps:  0
> Sort Operations: 0
> Autoindex Inserts:   0
> Virtual Machine Steps:   12
> sqlite> .tabls
> Error: unknown command or invalid arguments:  "tabls". Enter ".help" for
> help
> sqlite> .tables
> Address  Invoice  VERSION_CONTROL
> Customer InvoiceLine
> sqlite> .vfsname ?AUX?
> sqlite>
> sqlite> .TABLES
> Error: unknown command or invalid arguments:  "TABLES". Enter ".help" for
> help
> sqlite> .tables
> Address  Invoice  VERSION_CONTROL
> Customer InvoiceLine
> sqlite> select * from VERSION_CONTROL;
> Memory Used: 82024 (max 86816) bytes
> Number of Outstanding Allocations:   184 (max 196)
> Number of Pcache Overflow Bytes: 8080 (max 8080) bytes
> Number of Scratch Overflow Bytes:0 (max 0) bytes
> Largest Allocation:  64000 bytes
> Largest Pcache Allocation:   1176 bytes
> Largest Scratch Allocation:  0 bytes
> Lookaside Slots Used:10 (max 54)
> Successful lookaside attempts:   448
> Lookaside failures due to size:  86
> Lookaside failures due to OOM:   0
> Pager Heap Usage:8672 bytes
> Page cache hits: 12
> Page cache misses:   0
> Page cache writes:   0
> Schema Heap Usage:   4040 bytes
> Statement Heap/Lookaside Usage:  2368 bytes
> Fullscan Steps:  0
> Sort Operations: 0
> Autoindex Inserts:   0
> Virtual Machine Steps:   8
> sqlite>
>
> See online doc at http://www.sqlite.org/cli.html
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite subqueries

[R.Smith]

There's been many discussions on this topic, you can search for it, but 
I will try to recap in short:

SQL does not work like this, not in SQLite or any other SQL engine may 
an entity construct be referenced by an uncontrolled data value. Of 
course it is easy to get around this in code whereby you can read a 
table name from one DB and use it in an SQL statement for any DB, but 
the onus here is on the maker of such software to implement whatever 
safety checks are needed to prevent corruption or indeed SQL injections 
and other mischief made possible by vulnerabilities exposed in this way.

What you are trying here is not possible in pure SQL by design.

There may however be other ways of achieving your goals, maybe 
explaining to us what you would like to do in a system/setup like this 
will help - surely someone here have done some similar thing before and 
they are always glad to assist.


On 2015-02-24 11:37 PM, russ lyttle wrote:
> I got the "Using SQLite" book and didn't find the answer there, or in a
> Google, DuckDuckGo, or Gigiblast search.
> I'm trying to create a field in a table to hold the name of a second
> table, then retrieve that name for use.
> The code below is the simplest of all the things I've tried. Can anyone
> say what should be done so (10.) returns the same as (8.)?
> Thanks
>
> 1.sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY, name VARCHAR(16),
> anotherTable TEXT);
> 2.sqlite> SELECT * FROM sqlite_master;
>  table|a|a|2|CREATE TABLE a (id INTEGER PRIMARY KEY, name
> VARCHAR(16), anotherTable TEXT)
> 3.sqlite> INSERT INTO a (name, anotherTable) VALUES ('table1', 'b');
> 4.sqlite> SELECT * FROM a;
>  1|table1|b
> 5.sqlite> CREATE TABLE b (id INTEGER PRIMARY KEY, name VARCHAR(16),
> data FLOAT);
> 6.sqlite> INSERT INTO b (name, data) VALUES ('B1', 35.0);
> 7.sqlite> INSERT INTO b (name, data) VALUES ('B2', 40.0);
> 8.sqlite> SELECT * FROM b;
>  1|B1|35.0
>  2|B2|40.0
> 9.sqlite> SELECT anotherTable FROM a WHERE name='table1';
>  b
> 10.sqlite> SELECT * FROM (SELECT anotherTable FROM a Where
> name='table1');
>  b
>  sqlite>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to insert a pointer data to sqlite?

[Simon Slavin]

On 25 Feb 2015, at 1:07am, YAN HONG YE  wrote:

> I have a data:
> Idpid namemark
> 1 0   f1  sample
> 2 1   f2  sample
> 3 1   f3  sample
> 4 2   f4  sample
> 5 2   *id(2).name *id(2).mark
> 
> These means that under id(2) and id(5) have same node, if change one of the 
> node, the other update auto,
> How to realize this function?

Sorry, you cannot store a calculation or a function in a SQLite table.

Simon.

[sqlite] How to insert a pointer data to sqlite?

[YAN HONG YE]

I have a data:
Id  pid namemark
1   0   f1  sample
2   1   f2  sample
3   1   f3  sample
4   2   f4  sample
5   2   *id(2).name *id(2).mark

These means that under id(2) and id(5) have same node, if change one of the 
node, the other update auto,
How to realize this function?
Thank you!

[sqlite] Is readline ubiquitous on 32-bit x86 Linux?

[Dan Kennedy]

The pre-built sqlite3 shell tool for x86 Linux available for download here:

   http://www.sqlite.org/download.html

does not include readline support. Which makes it painful to use.

Does anyone think that many systems would be affected if it dynamically 
linked against the system readline? This means that the binary would not 
work on systems without libreadline.so installed. Or is readline 
considered ubiquitous by now?

Dan.