Re: [sqlite] how can I make SQLite work on Mono multiplatform project?

[Joe Mistachkin]

Please see:

http://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q6

--
Joe Mistachkin
 

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

[sqlite] Difference between SQLITE_CONFIG_MULTITHREAD & SQLITE_CONFIG_SERIALIZED options

[Rittick Gupta]

What is the difference between the SQLITE_CONFIG_MULTITHREAD &  
SQLITE_CONFIG_SERIALIZED options - When a) the same handle is shared between 
multiple threads in a process and when different handles are used by threads in 
a proceses.

Is there any difference in concurrency ? What should be used in a multi 
threaded process with  more than one thread updating and reading the database.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Function context

[Steinar Midtskogen]

[Simon Slavin]

> On 13 Feb 2012, at 7:51pm, Steinar Midtskogen wrote:
>
>> One should think that the ability to calculate a moving average would
>> be a pretty common request.  But people do it in their application
>> code instead?
>
> Actually, my expectation is the other way.  I'm continually surprised by 
> posts on this list that people expect to be able to use SQLite with no 
> external programming at all.  I often see complicated compound JOIN and 
> sub-SELECT SQL commands here which can be replaced by four lines in any 
> programming language, yielding faster simpler code which would be easier to 
> document and debug.

Well, yes, to do things in C or similar when SQL becomes too unwieldy
is precisely what I want, but the question is rather whether it's
feasible to do it within SQLite's framework for custom functions and
virtual tables.  This is the first time I do something with SQLite, so
my naive approach is that anything would be neater to have as custom
functions or virtual tables before doing it completely ad hoc in C.
Not just for the sake of using one of SQLite's key features, but to
keep things more reusable and to keep the core application as simple
as possible.

On the other side, I realise that if the API is to grant every wish
any programmer might have, it will break one of SQLite's other key
features: simplicity.

I think in this order:

1. Feasible using SQL?
2. Feasible using custom functions?
3. Feasible using virtual tables?
4. Ad hoc application code.

Perhaps number 4 will get me faster to my goal right now, but I'd like
to think that trying the approaches higher up first can give me
something back in the longer run.

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

[sqlite] how can I make SQLite work on Mono multiplatform project?

[zax zax]

 Hi all! I'm almost finished with an application of mine that I developed 
with M Visual Studio 2010 using c#. The software uses a lot of SQLite 
transactions, all of which work perfect compiled and run under Windows.Now I 
try to make my app multiplatform, using Mono (for the first time). And I 
can't seem to get Mono work with SQLite properly. Run w Mono basically the 
first of almost any type of SQLite transaction makes the app instacrashquit.:( 
I have a reference forusing System.Data.SQLite; as a starting point. Mono 
adapter tool MoMa throws 100 errors about this current .dll I downloaded a dll 
called Mono.Data.Sqlite;but as funny as it seems, MoMa throws around 100 other 
problems about this .dll too! I'm sure I'm missing something as I'm 
pretty inexperienced yet especially w Mono, porting and other systems than Win. 
Please help me what to do next! Thx! Best Regards ZAX 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Function context

[Marc L. Allen]

> More generally, I *often* see good C++ programmers pushing application
> logic into SQL, and getting it wrong because they don't really know
> SQL.  As a result, my rule of thumb is to make the separation between
> application code and SQL code based on whether the code is filtering
> result sets.  If the code reduces the number of results, it probably
> should be in the SQL, but if it just processes the results without
> removing any rows, it should probably be in application code.  In a
> client/server system like MySQL, it can sometimes be worthwhile to
> additionally have the server do operations which reduce the physical
> size of the individual results (say taking a substring of a column),
> but for SQLite that is seldom worthwhile (application code can make
> better targeted decisions about memory allocation and the like).
> 
> Since SQLite is running in-process, communication is cheap, so it is
> also often the case that there is no particular performance gain from
> having SQLite do calculations rather than your application code.
> SQLite can probably optimize SELECT COUNT(x) better than your
> application code can, but SELECT MAX(x) would probably be about the
> same performance if implemented in application code.

Truth.  And I like the rule of thumb, but only in cases where you are 
in-process, or, at a minimum, in-machine.  Sometimes you need to crunch a 
really large number of records and the cost of transmitting them to an 
application program is prohibitive.  

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

Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

[Tim Leland]

Thanks Michael I got it to work.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Saturday, February 11, 2012 7:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5



Here's exactly what I did.  You get a different result?



sqlite3 test1.db

create table grocery(a int,b int);

insert into grocery values(1,2);

insert into grocery values(2,3);

.quit



copy test1.db test2.db

copy test1.db test3.db



At this point I have:

02/11/2012  06:53 AM   228 dump.bat
02/11/2012  06:42 AM49 loop.bat
02/11/2012  06:48 AM 2,048 test1.db
02/11/2012  06:48 AM 2,048 test2.db
02/11/2012  06:48 AM 2,048 test3.db



loop test?.db



Then I have

02/11/2012  06:53 AM   228 dump.bat
02/11/2012  06:54 AM70 grocery.sql
02/11/2012  06:42 AM49 loop.bat
02/11/2012  06:54 AM 8 test1.csv
02/11/2012  06:48 AM 2,048 test1.db
02/11/2012  06:54 AM 8 test2.csv
02/11/2012  06:48 AM 2,048 test2.db
02/11/2012  06:54 AM 8 test3.csv
02/11/2012  06:48 AM 2,048 test3.db
   9 File(s)  6,515 bytes
   2 Dir(s)  201,627,729,920 bytes free

D:\SQLite\xx>more test1.csv
1,2
2,3



Using these two batch files

loop.bat

@for %%i in (%1) do @call dump %%i

dump.bat

@del /q %~n1.csv
@if not exist %1 echo No such file: %1
@echo .separator "," >grocery.sql
@echo .output %~n1.csv >>grocery.sql
@echo select * from grocery; >>grocery.sql
@echo .quit >>grocery.sql
@sqlite3 %1 mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Thursday, February 09, 2012 3:04 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5

Let's change them a touch...one too many @'s in loop.bat

loop.bat

@for %%i in (%1) do @call dump %%i

dump.bat

@echo .separator "," >grocery.sql
@echo .output %~n1.csv >>grocery.sql
@echo select * from grocery; >>grocery.sql
@echo .quit >>grocery.sql
@sqlite3 %1 grocery.sql
echo .output %~n1.csv >>grocery.sql
echo select * from grocery; >>grocery.sql echo .quit >>grocery.sql
sqlite3 %1 mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Thursday, February 09, 2012 1:39 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Compiling SQLite3 to run on Windows Mobile 6.5


On 9 Feb 2012, at 6:28pm, Tim Leland wrote:

> That will work but we have to import the sqlite file into our
AS400/Iseries
> IBM (DB2)system. Easiest way is convert it to a .csv file for the import.
My
> last resort is just run a script on the PC side that will run sqlite3.exe
> shell and convert the sqlite file to a .csv but I want to avoid that step.
> If you know of a way for an as400 to import sqlite files that would be
> great!

Oh, you're that guy.  Okay, I'd forgotten your hosting side was on an AS400.

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


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


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


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

Re: [sqlite] Function context

[Scott Hess]

On Mon, Feb 13, 2012 at 12:28 PM, Simon Slavin  wrote:
> On 13 Feb 2012, at 7:51pm, Steinar Midtskogen wrote:
>> One should think that the ability to calculate a moving average would
>> be a pretty common request.  But people do it in their application
>> code instead?
>
> Actually, my expectation is the other way.  I'm continually
> surprised by posts on this list that people expect to be able
> to use SQLite with no external programming at all.  I often see
> complicated compound JOIN and sub-SELECT SQL commands here
> which can be replaced by four lines in any programming
> language, yielding faster simpler code which would be easier to
> document and debug.

More generally, I *often* see good C++ programmers pushing application
logic into SQL, and getting it wrong because they don't really know
SQL.  As a result, my rule of thumb is to make the separation between
application code and SQL code based on whether the code is filtering
result sets.  If the code reduces the number of results, it probably
should be in the SQL, but if it just processes the results without
removing any rows, it should probably be in application code.  In a
client/server system like MySQL, it can sometimes be worthwhile to
additionally have the server do operations which reduce the physical
size of the individual results (say taking a substring of a column),
but for SQLite that is seldom worthwhile (application code can make
better targeted decisions about memory allocation and the like).

Since SQLite is running in-process, communication is cheap, so it is
also often the case that there is no particular performance gain from
having SQLite do calculations rather than your application code.
SQLite can probably optimize SELECT COUNT(x) better than your
application code can, but SELECT MAX(x) would probably be about the
same performance if implemented in application code.

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

Re: [sqlite] Function context

[Marc L. Allen]

> Actually, my expectation is the other way.  I'm continually surprised
> by posts on this list that people expect to be able to use SQLite with
> no external programming at all.  I often see complicated compound JOIN
> and sub-SELECT SQL commands here which can be replaced by four lines in
> any programming language, yielding faster simpler code which would be
> easier to document and debug.

I'm not surprised at all.  I happen to be a programmer, so this doesn't cause 
me any problem, and, in fact, I'm excited I can do it this way.

But, you do have a fair number of people that come into this from environments 
where you can't necessarily combine standard programming and SQL.  For 
instance, I come from an MSSQL world, and while they now support a very nice C# 
environment within the SQL server to do both SQL and algorithmic programming, 
before that, it often took too much time to extract data from a remote server, 
manipulate it, and then send back changes.

At least, it did in my environment.

So, we learned to use the minimal algorithmic abilities (in this case, T-SQL).  
Complex joins, subselects, T-SQL whiles, and cursors where the order of the day.

Even though the majority of my development is standard programming, my mind 
still hitched on a message last week or so on this forum that told some poster 
that there was no "if" statement.  I had a momentary panic until I remembered 
that I could just write regular code.
 

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

Re: [sqlite] Function context

[Scott Hess]

On Mon, Feb 13, 2012 at 11:51 AM, Steinar Midtskogen
 wrote:
> [Scott Hess]
>> Unfortunately, I can't offhand think of a reasonable solution for you,
>> I think I'd just use the SELECT to generate the data, while
>> calculating the moving average in my application code.
>
> Yes, but that reduces sqlite to just a way to store data.  It would be
> nice to be able to use SQL and aggregate functions on the resulting
> moving average (in particular max() and min()).

Well, SQLite isn't a data warehouse or analysis solution, for the most
part it IS "just a way to store data."

> Perhaps the moving average can be implemented as a virtual table?

The virtual table could wrap another table, and manually include ORDER
BY when querying for data from the backing table.  The virtual table
could provide a custom function, so it would look a lot like what you
suggested, except with the part about having to create the virtual
table in the first place.  Given sufficient work, you could even
potentially track the ordering of the outer query, and maybe expose
the query optimization info from the underlying table.  Probably a
little involved for just this function, but if one was looking to
implement an analysis tool which might have a bunch of other
functions, it might make some sense.

> One should think that the ability to calculate a moving average would
> be a pretty common request.  But people do it in their application
> code instead?

"lite", right?  :-).  But even being serious, I think this is a pretty
low-volume use case.  The current custom functions are pretty
straight-forward, you can either convert a series of inputs into a
series of outputs, or you can convert a series of inputs into a single
output.  In both cases the ordering of the inputs should not affect
the final output(s), which makes things a lot cleaner to verify and
test.  This is consistent with SQL in general - in the abstract,
results are not ordered, which allows the engine flexibility to
optimize things, and ordering only required to happen at the point
when you're reading out the results.

[That last statement may not initially make sense.  Think in terms of
SQL as an expression of relational algebra.  The only reason things
even need to have an order is because of our primitive APIs.]

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

Re: [sqlite] Function context

[Simon Slavin]

On 13 Feb 2012, at 7:51pm, Steinar Midtskogen wrote:

> One should think that the ability to calculate a moving average would
> be a pretty common request.  But people do it in their application
> code instead?

Actually, my expectation is the other way.  I'm continually surprised by posts 
on this list that people expect to be able to use SQLite with no external 
programming at all.  I often see complicated compound JOIN and sub-SELECT SQL 
commands here which can be replaced by four lines in any programming language, 
yielding faster simpler code which would be easier to document and debug.  
There are a few posters here who don't know how to program, and I can 
understand them wanting a SQL expression to do all the work for them.  But 
really, anyone who can understand anything more than a simple JOIN can learn 
how to program and it'll probably save them time in the long run.

Your requirement does seem to be difficult because it requires an aggregate 
function applied after ORDER BY.  The easiest way to implement it involves 
defeating some of the optimization in SQLite, thus worsening it for most users.

But as someone who does know how to program, I'd expect to do it by handing a 
'SELECT' statement to a function I'd written.  I wouldn't expect a tiny simple 
fast API like SQLite to do it, and writing a simple routing in whatever 
programming language the rest of my code is written in would be far easier for 
me than writing a SQLite extension in C.

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

Re: [sqlite] Function context

[Steinar Midtskogen]

[Scott Hess]

> I think you're making unwarranted assumptions about the order your
> custom function will be called.  Even if you added "ORDER BY" to the
> end of the query, that wouldn't necessarily order the calls to your
> custom function.  Even if you find a workaround which allows you to
> implement something in the current version of SQLite, it wouldn't
> necessarily work in a future version.

Yes, I'm fearing that.  For instance, non-aggregate functions would be
natural candidates for parallelisation on a multicore platform, and
then the order will surely be broken.  While the sqlite site declares
that "threads are evil", it seems likely that people will ask for more
performance on their 100 core CPU's, so it might be a dangerous bet to
assume that sqlite never will go that path eventually.

> Unfortunately, I can't offhand think of a reasonable solution for you,
> I think I'd just use the SELECT to generate the data, while
> calculating the moving average in my application code.

Yes, but that reduces sqlite to just a way to store data.  It would be
nice to be able to use SQL and aggregate functions on the resulting
moving average (in particular max() and min()).

Perhaps the moving average can be implemented as a virtual table?

One should think that the ability to calculate a moving average would
be a pretty common request.  But people do it in their application
code instead?

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

Re: [sqlite] Diacritics handling in FTS with a custom tokenizer

[George Ionescu]

Hello Dan,

thank you for the response, that did it.
Unfortunately, this 'breaks' the fts_table_terms functionality in the sense
that real terms (e.g. with diacritics) cannot be retrieved using that
table, since they're stored without.
I wanted to use that table for autocomplete feature in a text input.
But I can live with that, as long as searching works with *and* without
diacritics.

Thanks again,
George.

On Wed, Feb 8, 2012 at 8:30 PM, Dan Kennedy  wrote:

> On 02/09/2012 12:49 AM, George Ionescu wrote:
>
>> Hello Dan,
>>
>> yes, I thought of that. But wouldn't this break the snippet's function?
>> If the tokenizer will return text without diacritics, wouldn't the snippet
>> return the same?
>>
>
> Should be Ok. Snippet should be based on the original content.
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Function context

[Scott Hess]

On Mon, Feb 13, 2012 at 9:24 AM, Steinar Midtskogen
 wrote:
> [Peter Aronson]
>> (2) You can associate data with an argument to a regular user-defined
>> function using sqlite3_set_auxdata() and sqlite3_get_auxdata() as long
>> as the value of the argument is static.  If you don't normally have a
>> static argument to your function, you can add one (say a string
>> MAVG').  I actually used this approach with some application generated
>> SQL in my current project at one point.
>
> Thanks.  I'm intending to write a function so I can do:
>
> SELECT unix_time, mavg(value, unix_time, ) FROM tab;
>
> assuming:
>
> CREATE TABLE tab (value REAL, unix_time INTEGER, PRIMARY KEY (unix_time));
>
> So I assume that your second approach could work, since the third
> argument to mavg() (the period, window size in seconds) is static,
> e.g. mavg(value, unix_time, 86400) will give me the moving daily
> average.

I think you're making unwarranted assumptions about the order your
custom function will be called.  Even if you added "ORDER BY" to the
end of the query, that wouldn't necessarily order the calls to your
custom function.  Even if you find a workaround which allows you to
implement something in the current version of SQLite, it wouldn't
necessarily work in a future version.

Unfortunately, I can't offhand think of a reasonable solution for you,
I think I'd just use the SELECT to generate the data, while
calculating the moving average in my application code.

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

Re: [sqlite] Function context

[Peter Aronson]

I believe It's private to that query as it is stored in sqlite3_context 
structure at the moment (the context being stored in a stack variable during 
SQL 
execution as far as I can tell).  I'd assume this is stable behavior, but you'd 
have to ask the SQLite Dev team for an official answer.  I aggree that the 
documentation isn't very clear.

Best regards,

Peter


From: Steinar Midtskogen 
To: General Discussion of SQLite Database 
Sent: Mon, February 13, 2012 10:24:56 AM
Subject: Re: [sqlite] Function context

[Peter Aronson]

> (2) You can associate data with an argument to a regular user-defined
> function using sqlite3_set_auxdata() and sqlite3_get_auxdata() as long
> as the value of the argument is static.  If you don't normally have a
> static argument to your function, you can add one (say a string
> MAVG').  I actually used this approach with some application generated
> SQL in my current project at one point.

Thanks.  I'm intending to write a function so I can do:

SELECT unix_time, mavg(value, unix_time, ) FROM tab;

assuming:

CREATE TABLE tab (value REAL, unix_time INTEGER, PRIMARY KEY (unix_time));

So I assume that your second approach could work, since the third
argument to mavg() (the period, window size in seconds) is static,
e.g. mavg(value, unix_time, 86400) will give me the moving daily
average.

But will the data be private to only one query?  That is, if two
queries using the same period happen to run simultaniously, will it
still work?  The documentation wasn't clear.  In its example of using
this data for storing a compiled regexp, it would rather be useful if
it was not strictly private.

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

Re: [sqlite] Function context

[Steinar Midtskogen]

[Peter Aronson]

> (2) You can associate data with an argument to a regular user-defined
> function using sqlite3_set_auxdata() and sqlite3_get_auxdata() as long
> as the value of the argument is static.  If you don't normally have a
> static argument to your function, you can add one (say a string
> MAVG').  I actually used this approach with some application generated
> SQL in my current project at one point.

Thanks.  I'm intending to write a function so I can do:

SELECT unix_time, mavg(value, unix_time, ) FROM tab;

assuming:

CREATE TABLE tab (value REAL, unix_time INTEGER, PRIMARY KEY (unix_time));

So I assume that your second approach could work, since the third
argument to mavg() (the period, window size in seconds) is static,
e.g. mavg(value, unix_time, 86400) will give me the moving daily
average.

But will the data be private to only one query?  That is, if two
queries using the same period happen to run simultaniously, will it
still work?  The documentation wasn't clear.  In its example of using
this data for storing a compiled regexp, it would rather be useful if
it was not strictly private.

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

Re: [sqlite] How to check whether a table is empty or not in sqlite.

[Igor Tandetnik]

On 2/13/2012 11:20 AM, Kit wrote:

2012/2/13 Igor Tandetnik:

bhaskarReddy  wrote:

How to check whether a table is empty or not. If a table is empty, i
want to do some logic. If not another logic.


select exists (select 1 from MyTable);


SELECT exists(SELECT 1 FROM MyTable LIMIT 1);


exists() predicate is smart enough to stop as soon as the first row is 
retrieved. LIMIT 1 clause is redundant, though harmless.

--
Igor Tandetnik

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

Re: [sqlite] Call PRAGMA from TCL API

[Simon Slavin]

On 13 Feb 2012, at 4:05pm, Tilsley, Jerry M. wrote:

> How can I call the pragma statements, more specifically the PRAGMA 
> FOREIGN_KEYS=ON from within the Tcl environment?

Execute them the same way you'd execute a 'SELECT' SQL command.

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

Re: [sqlite] How to check whether a table is empty or not in sqlite.

[Kit]

2012/2/13 Igor Tandetnik :
> bhaskarReddy  wrote:
>>        How to check whether a table is empty or not. If a table is empty, i
>> want to do some logic. If not another logic.
>
> select exists (select 1 from MyTable);

SELECT exists(SELECT 1 FROM MyTable LIMIT 1);
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to check whether a table is empty or not in sqlite.

[Jay A. Kreibich]

On Mon, Feb 13, 2012 at 03:48:24PM +, Simon Slavin scratched on the wall:
> 
> On 13 Feb 2012, at 1:02pm, Igor Tandetnik wrote:
> 
> > bhaskarReddy  wrote:
> >>   How to check whether a table is empty or not. If a table is empty, i
> >> want to do some logic. If not another logic.
> > 
> > select exists (select 1 from MyTable);

> SELECT COUNT(*) from MyTable

  For a large table, that might take some time.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Call PRAGMA from TCL API

[Tilsley, Jerry M.]

All,

How can I call the pragma statements, more specifically the PRAGMA 
FOREIGN_KEYS=ON from within the Tcl environment?

Thanks,

Jerry


Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center. If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited. If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] freelist_count - can it shrink back?

[Donald Griggs]

Regarding:

My question is, when I insert new data, is the needed space allocated out
> of the free_list or do the db file grows larger and the free space stays
> till vacuum operation is performed?
> Basically, my question is: on intensive inserts and deletes will the freed
> space be re-used ?
>
> =
>

Yes, new items are allocated from the freed list.  For many applications
vacuum will seldom be needed.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to check whether a table is empty or not in sqlite.

[Simon Slavin]

On 13 Feb 2012, at 1:02pm, Igor Tandetnik wrote:

> bhaskarReddy  wrote:
>>   How to check whether a table is empty or not. If a table is empty, i
>> want to do some logic. If not another logic.
> 
> select exists (select 1 from MyTable);

Or

SELECT COUNT(*) from MyTable

which will give you 0 if the table is empty, and some other integer otherwise.

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

Re: [sqlite] Function context

[Peter Aronson]

You can't call sqlite3_aggregate_context() in a non-aggregate function.  
However, there *are* a couple of other things you can do.


(1) All forms of sqlite3_create_function() take as 5th argument a void 
pointer to an arbitrary user data structure.  This data structure can be 
accessed in a regular user-defined function by calling 
sqlite3_user_data().  The difficultly with this approach is initializing 
this information for a scan.  There are a number of approaches you could 
take there.  One possibility is to pass the same argument in as user 
data into sqlite3_set_authorizer(), and have your authorizer function 
clear it whenever a SELECT is checked.


(2) You can associate data with an argument to a regular user-defined 
function using sqlite3_set_auxdata() and sqlite3_get_auxdata() as long 
as the value of the argument is static.  If you don't normally have a 
static argument to your function, you can add one (say a string 
'MAVG').  I actually used this approach with some application generated 
SQL in my current project at one point.


Best regards,

Peter

On 2/13/2012 7:48 AM, Steinar Midtskogen wrote:

Hello

Is it possible to have a context for a custom SQL function that is NOT
an aggregate function?  It might sound silly, but if a SELECT
statement with this function causes the function to be called in a
defined order (as with the step function of an aggregate function),
this can be useful to calculate the moving average.  That is, in its
simplest form, to return the average of the N last values.

sqlite spiral to a crash if I call sqlite3_aggregate_context() when I
don't have a finalise function.



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

[sqlite] Function context

[Steinar Midtskogen]

Hello

Is it possible to have a context for a custom SQL function that is NOT
an aggregate function?  It might sound silly, but if a SELECT
statement with this function causes the function to be called in a
defined order (as with the step function of an aggregate function),
this can be useful to calculate the moving average.  That is, in its
simplest form, to return the average of the N last values.

sqlite spiral to a crash if I call sqlite3_aggregate_context() when I
don't have a finalise function.

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

Re: [sqlite] multiple rows of VALUES in an INSERT incompatible with SQLITE_OMIT_COMPOUND_SELECT

[Ralf Junker]

On 13.02.2012 10:18, Dan Kennedy wrote:

> On 02/10/2012 11:06 PM, Ralf Junker wrote:
>>
>>> The new feature to insert multiple rows of VALUES in a single INSERT
>>>
>>>http://www.sqlite.org/src/info/eb3b6a0ceb
>>>
>>> gives wrong results if SQLite is compiled with
>>> SQLITE_OMIT_COMPOUND_SELECT.
>>
>> Has the team seen this or has it been overlooked? Shall I file a ticket?
> 
> I get this:
> 
>   SQLite version 3.7.11 2012-02-13 08:50:23
>   Enter ".help" for instructions
>   Enter SQL statements terminated with a ";"
>   sqlite> CREATE TABLE t1(x, y);
>   sqlite> INSERT INTO t1 VALUES(1, 2), (3, 4);
>   Error: near ",": syntax error
> 
> Is this what you are seeing? That the syntax is rejected with
> an error message? Or some other problem?

I believe Richard has already take care of this and disabled multiple
rows in the VALUES clause if SQLITE_OMIT_COMPOUND_SELECT is defined:

  http://127.0.0.1:8080/info/92131195d0

Before this, I did not see any error message. After, I expect an error
message similar to yours (from reading the code, I did not compile and run).

So do I guess correctly that you were testing with trunk later than
check-in [92131195d0]?

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

Re: [sqlite] How to check whether a table is empty or not in sqlite.

[Igor Tandetnik]

bhaskarReddy  wrote:
>How to check whether a table is empty or not. If a table is empty, i
> want to do some logic. If not another logic.

select exists (select 1 from MyTable);

-- 
Igor Tandetnik

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

[sqlite] SQLite extensions repository

[Marco Bambini]

Hello,
I am wondering if there is there a place that collect all available sqlite 
extensions.

Please let me know.
Thanks.
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs







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

[sqlite] How to check whether a table is empty or not in sqlite.

[bhaskarReddy]

HI Friends,

How to check whether a table is empty or not. If a table is empty, i
want to do some logic. If not another logic.


 Can any one tell me how to check if table in a data base is empty
or not, using sqlite3.


Regards,
Bhaskar.
-- 
View this message in context: 
http://old.nabble.com/How-to-check-whether-a-table-is-empty-or-not-in-sqlite.-tp33314679p33314679.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] multiple rows of VALUES in an INSERT incompatible with SQLITE_OMIT_COMPOUND_SELECT

[Dan Kennedy]

On 02/10/2012 11:06 PM, Ralf Junker wrote:

On 07.02.2012 12:28, Ralf Junker wrote:


The new feature to insert multiple rows of VALUES in a single INSERT

   http://www.sqlite.org/src/info/eb3b6a0ceb

gives wrong results if SQLite is compiled with SQLITE_OMIT_COMPOUND_SELECT.


Has the team seen this or has it been overlooked? Shall I file a ticket?


I get this:

  SQLite version 3.7.11 2012-02-13 08:50:23
  Enter ".help" for instructions
  Enter SQL statements terminated with a ";"
  sqlite> CREATE TABLE t1(x, y);
  sqlite> INSERT INTO t1 VALUES(1, 2), (3, 4);
  Error: near ",": syntax error

Is this what you are seeing? That the syntax is rejected with
an error message? Or some other problem?

Dan.



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