Re: [sqlite] Is the table created by "create temp table" in the database file or in memory?

[sub sk79]

Also these links which maybe easier to grok:
https://sqlite.org/tempfiles.html
 (sections  2.6 and 3)
https://sqlite.org/tempfiles.html#tempstore

-Neal

On Sat, Feb 1, 2020 at 10:09 AM J. King  wrote:

> On February 1, 2020 10:03:28 a.m. EST, Peng Yu 
> wrote:
> >But it doesn't explain what is "the temp database". Is it an actual
> >database file? Or it is just a in-memory database?
>
>
> It depends. See  for
> details ans links to further reading.
> --
> J. King
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[sub sk79]

Also maybe Slipstreamed?

-Neal

On Monday, January 27, 2020, sub sk79  wrote:

> How about Seamless, Integrated or Baked-in?
>
> -Neal
>
> On Monday, January 27, 2020, Warren Young  wrote:
>
>> On Jan 27, 2020, at 3:18 PM, Richard Hipp  wrote:
>> >
>> > "serverless" has become a popular buzz-word that
>> > means "managed by my hosting provider rather than by me.”
>>
>> “Serverless” it a screwy buzzword anyway, because of course there’s still
>> a server under its new meaning.
>>
>> My vote?  Keep using the term.  We were here first.
>>
>> This is an ancient problem.  It is why is any serious dictionary the
>> count of definitions considerably exceeds the count of headwords.  These
>> new kids?  “serverless, sense 2.”
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New word to replace "serverless"

[sub sk79]

How about Seamless, Integrated or Baked-in?

-Neal

On Monday, January 27, 2020, Warren Young  wrote:

> On Jan 27, 2020, at 3:18 PM, Richard Hipp  wrote:
> >
> > "serverless" has become a popular buzz-word that
> > means "managed by my hosting provider rather than by me.”
>
> “Serverless” it a screwy buzzword anyway, because of course there’s still
> a server under its new meaning.
>
> My vote?  Keep using the term.  We were here first.
>
> This is an ancient problem.  It is why is any serious dictionary the count
> of definitions considerably exceeds the count of headwords.  These new
> kids?  “serverless, sense 2.”
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Capturing the changes in columns in a table

[sub sk79]

> Yes, a dumb of a system is provided daily


There are some great solutions already here for offline processing as
stated in your question. However, something you might also want to consider
(if you have requisite access to the system) is to use ‘after insert’
trigger(s) to online capture the change log  into a table and dump that
table along with other tables. This would be easiest and fastest solution
while not needing any indexes to be added or queries to be optimized.

- Neal

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

Re: [sqlite] Allow inclusion of generate_series function

[sub sk79]

Hi,

I think both your requirements and Dr Hipp's point-of-view are valid if we
continue to see SQLite as just a tool. Yes, SQLite is an amazing tool for
all the things it does and especially for a great design which almost never
stands in the way of user customization and extension.
However, SQLite's user base has grown so much that it should not be seen
just as a tool but also as a business with multiple use-cases to which it
is applied.

The question of which features to enable by default to please everyone is
not answerable if one continues to see it as a tool.
However, if seen as a business, there may be a solution possible. The
canonical way businesses approach this is to rely on its ecosystem  -
partners, resellers, app-stores etc. Such an approach is a win-win-win for
core-tool-builder (by helping keep focus on key-features), customers
(getting niche features without breaking a sweat) and partners. But this
approach needs a willingness to nurture the ecosystem by providing , as
needed, tech support, access to paying customer lists with niche
requirements, funding, guidance etc - possibly in exchange for a cut and
strong control over ecosystem tool's testing, metrics etc - in short, a
Partner Program.

I built one such SQLite ecosystem solution called StepSqlite - an advanced
PL/SQL compiler  which is backward compatible with subset of Oracle-TM
PL/SQL. It is full of features which will blow your mind - one of the
features, relevant here, is automatic detection and setting of SQLite
pragmas and extensions  based on user code. You simply write PL/SQL-like
code for DB access and StepSqlite customizes a SQLite library baked with
your code(translated to C++) and make it available to you as a shared
library for download which can be used in your app(s) - which in turn can
be written in any major client language and for any major OS without any
bridge-layer (No JDBC-ODBC etc needed). I designed it specifically to
target requirements like your's:

> Without risk of bringing malware from other downloads.
> Without the hassle of rebuilding it every time I need a new version.
> Without having to remember one or two years later upon ...
> Consistent across machines...
>

There are several such power features in StepSqlite (Pls. see this Google
doc link for a list of StepSqlite features:
https://docs.google.com/document/d/1037VTdEhuGYi8D6vVQ2KOD86fn9-qt4WVYVos9lpBqE/edit?usp=sharing)
- I even  added window-functions one year before SQLite core - and IMHO, is
still a better value than core-version (because it works on older SQLite
versions using just CTE , does not add complexity to core, has more
included window-functions and is dead-easy to write custom ones. for ex,

FUNCTION MyAvg(val float) RETURN float
ANALYTIC is sum(val)/count(val);

Needless to say, IMO, StepSqlite has a great value proposition but I am
having a hard time funding it myself and reaching a critical mass of
customers.
If SQLite had a partner program I could bring it to market much faster and
it would be a win-win-win instead of loose-loose-loose (SQLite core gets
bulky loosing its key USP)-(users get upset because the required features
take a lot of hand-customization)-(well-wishing potential partners like me
languish).

And I am just one such ecosystem solution of many: there are other great
ones: for ex, bedrockdb, litereplica etc which would probably benefit from
a well defined SQLite partner program.

In any case, that would be my go-to solution for the issue at hand: SQLite
partner program.

Best Regards,
S Kashikar



On Tue, Dec 11, 2018 at 6:47 AM Digital Dog  wrote:

> On Thu, Dec 6, 2018 at 8:06 PM Keith Medcalf  wrote:
>
> The issue is not limited to generate_series (although that one seems to be
> pain point for many users). ... Projects using these platforms require
> custom builds of sqlite
> anyway.
>
> The ability to download and immediately start working with a really
> capable shell.
> Without risk of bringing malware from other downloads.
> Without the hassle of rebuilding it every time I need a new version.
> Without having to remember one or two years later upon returning to a
> stuff that
> need changes that there was a custom built sqlite shell there.
> Consistent across machines, because it was just downloaded from official
> download site.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

[sub sk79]

On Tue, Oct 9, 2018 at 5:18 PM Warren Young  wrote:

> ... The problem is happening because spam gets reflected off this list, so
> people click “This is Spam” in Gmail, which causes Gmail’s spam filters to
> treat all messages on the list as more spammish.  The more that happens,
> the less likely a given SQLite ML message is to get to a Gmail user.
>
> That problem will affect any mail system with centralized spam detection.


You can prevent this by creating a whitelist filter in your gmail
account for sqlite-users mailing list address.

Instructions here:
https://www.lifewire.com/how-to-whitelist-a-sender-or-domain-in-gmail-1172106

>
BTW, I generally prefer mailing-list. But forum has some advantages too. At
the end of the day, I think, it would be best to have both options readily
available as *interfaces* to the sqlite knowledge-base.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIMIT

[sub sk79]

> What would the window-function query be?


Note: For anyone stumbling upon this thread, below code is not supported in
SQLite natively.

SELECT F1, F2

FROM (

SELECT F2, F1, Rank()

  OVER (PARTITION BY F2

ORDER BY F1 ) AS Rank

FROM T

)  WHERE Rank <= 10

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

Re: [sqlite] StepSqlite: SuperPowers for SQLite and BerkeleyDB

[sub sk79]

Thanks for bringing that to my attention. The website is under renovation
as well. Hope to have it fixed soon.

Regards,
SKashikar

On Tue, Dec 5, 2017 at 7:15 PM, Keith Medcalf <kmedc...@dessus.com> wrote:

>
> Uses an expired SSL certificate ...
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of sub sk79
> >Sent: Tuesday, 5 December, 2017 14:48
> >To: SQLite mailing list
> >Subject: [sqlite] StepSqlite: SuperPowers for SQLite and BerkeleyDB
> >
> >Hi All:
> >
> >The next version of StepSqlite - enhanced-PL/SQL compiler for SQLite
> >and Oracle-TM
> >BerkeleyDB
> >(https://www.metatranz.com/stepsqlite)
> >is due soon and is packed with Super Powers which will blow your
> >mind!
> >
> >For a start, how about powerful Analytic Window Functions,
> >Collections,
> >Bulk-Ops?
> >Now, if you think that is huge, wait, there is a lot more!!!
> >Look for a detailed announcement in first week of New Year 2018.
> >
> >Currently aiming for a January end beta-release after the ongoing
> >rigorous
> >testing and bug-fix cycle.
> >
> >Meanwhile you can sign-up for beta. Everyone signing up for beta -
> >till Jan
> >31, 2018, gets a professional version free for 6 months (July 31,
> >2018).
> >Those already on the list need not re-register - you are already
> >included! There
> >will always be a free developer version (size-limited ).
> >
> >Be the first to experience the power - get on the waiting list:
> >http://www.metatranz.com/stepsqlite/ShowSignUp?guestlogin=ON
> >
> >Happy New Year!!
> >
> >-SKashikar
> >(Founder)
> >__
> >**StepSqlite** enhanced-PL/SQL on Sqlite
> >and BerkeleyDB: *Be Done at the Speed of Lite!*
> >
> >Twitter: https://twitter.com/metatranz
> >Facebook: https://www.facebook.com/Metatranz/
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] StepSqlite: SuperPowers for SQLite and BerkeleyDB

[sub sk79]

Hi All:

The next version of StepSqlite - enhanced-PL/SQL compiler for SQLite
and Oracle-TM
BerkeleyDB
(https://www.metatranz.com/stepsqlite)
is due soon and is packed with Super Powers which will blow your mind!

For a start, how about powerful Analytic Window Functions, Collections,
Bulk-Ops?
Now, if you think that is huge, wait, there is a lot more!!!
Look for a detailed announcement in first week of New Year 2018.

Currently aiming for a January end beta-release after the ongoing rigorous
testing and bug-fix cycle.

Meanwhile you can sign-up for beta. Everyone signing up for beta - till Jan
31, 2018, gets a professional version free for 6 months (July 31, 2018).
Those already on the list need not re-register - you are already
included! There
will always be a free developer version (size-limited ).

Be the first to experience the power - get on the waiting list:
http://www.metatranz.com/stepsqlite/ShowSignUp?guestlogin=ON

Happy New Year!!

-SKashikar
(Founder)
__
**StepSqlite** enhanced-PL/SQL on Sqlite
and BerkeleyDB: *Be Done at the Speed of Lite!*

Twitter: https://twitter.com/metatranz
Facebook: https://www.facebook.com/Metatranz/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[sub sk79]

On Tue, Nov 28, 2017 at 9:33 PM, J. King  wrote:

> ...*that I never thought to ask*, many of which have been helpful in
> refining my application. ...
>

+1 that.
That is the main advantage of a mailing list over a forum with
filter-bubble or god forbid 'personalization'.
I even miss the keyword search engines of yesteryear for same reason. The
to-the-pointedness of google makes me dumb.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[sub sk79]

Sqlite had a forum in the past on Nabble. Seems nice to me, I still get
several hits to nabble when googling for sqlite issues.
What didn't work there?

-SK

On Tue, Nov 21, 2017 at 9:30 AM, Richard Hipp  wrote:

> On 11/21/17, Paul Sanderson  wrote:
> > Coincidence!  I have just been in my gmail folder marking a load of
> SQLite
> > email as 'not spam'
>
> I've been seeing mailing list emails go to spam for a while now.
> Nothing has changed with MailMan.  I think what we are seeing is the
> beginning of the end of email as a viable communication medium.
>
> I really need to come up with an alternative to the mailing list.
> Perhaps some kind of forum system.  Suggestions are welcomed.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Many ML emails going to GMail's SPAM

[sub sk79]

I vote to keep mailing list. It works great for me with GMail (accessible
from all my devices instantly)
Reasons:
1. GMail has a threaded view built-in which works great. You might need to
enable it in settings.
2. I doubt if any spam filter can ever be better than Gmail's. Spam
fighting is not just a matter of having Bayesian algos right -- it needs
mountains of data to work right. Who can beat Google in that?
3. Searching fast and accurate is crucial to my use of SQLite mailing list.
Again Google can't be beat.
4. If at all needed, maybe this offering  from Google is the way to go:
https://support.google.com/a/answer/167430?hl=en (I have no experience
using it, though)

Thanks,
SK



On Tue, Nov 21, 2017 at 1:53 PM, Mike King  wrote:

>
> Another vote for a threaded forum here. I do try and keep up with the ML
> but if it was threaded it would be a lot easier.
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] segfault in load-extension but not in app

[sub sk79]

Hi,

I am getting seg fault when same code is run as loadable-extension but not
when run as shared lib linked to app.
Thanks in advance for all help.

The C++ code is compiled to
1. shared library and linked to app
2. loadable-extension and loaded from sqlite command line using '.load'.

The C++ code is exactly same in both cases (except extension init section)

Sqlite version is 3.21.0. Ubuntu 16.04 LTS 64-bit in virtualbox VM on
windows 7 host. GCC 6.1.0

I do not get any issues when the code is run as app.
The code runs fine as loadable-extension as well except, it SEG faults
during cleanup:
Here are two snippets of the app's C++ code where I have observed this:
==Snippet 1
  //Drop all views created in sqlite
  std::vector  view_vector;
  view_vector.push_back("MYDATA");

std::string dropView;
  for (std::vector ::iterator
iter=view_vector.begin(); iter != view_vector.end(); ++iter)
  {
LOG_DEBUG << "Dropping View: " << (*iter) << endl;
dropView=("DROP VIEW IF EXISTS "+(*iter));
if (SQLITE_OK != sqlite3_exec(m_pDb, dropView.c_str(),
NULL, NULL, NULL))
{
 LOG_ERROR << "Exec failed for Drop View " << (*iter)
<< ". Error Code= " << sqlite3_errcode(m_pDb)<< " Error Message= "<<
sqlite3_errmsg(m_pDb) << " Line=" << __LINE__ << endl;
};
  };
==Snippet 2
  //Generic Finalize loop -- requires Sqlite version 3.5+
//NOTE: Since we are finalizing it is correct to pass 0 as second
arg all the time.
  sqlite3_stmt *pStmt;
  while( (pStmt = sqlite3_next_stmt(m_pDb, 0))!=0 )
  {
LOG_TRACE << "Finalizing SQL: " << sqlite3_sql(pStmt) <,
run_list_atexit=run_list_atexit@entry=true)
at exit.c:82
#20 0x77626045 in __GI_exit (status=) at exit.c:104
#21 0x7760c837 in __libc_start_main (main=0x404550 , argc=4,
argv=0x7fffdb58, init=, fini=,
rtld_fini=, stack_end=0x7fffdb48) at
../csu/libc-start.c:325
#22 0x004052b9 in _start ()
(gdb)

==

Sometimes I also get SEG fault at:
===
(gdb) where
#0  malloc_consolidate (av=av@entry=0x779b0b20 ) at
malloc.c:4167
#1  0x7766c678 in _int_free (av=0x779b0b20 ,
p=, have_lock=0) at malloc.c:4075
#2  0x7767053c in __GI___libc_free (mem=) at
malloc.c:2968
#3  0x00417074 in sqlite3_free ()
#4  0x00421572 in sqlite3VdbeClearObject ()
#5  0x00421954 in sqlite3VdbeDelete ()
#6  0x00454b74 in sqlite3VdbeFinalize ()
#7  0x00454bde in sqlite3_finalize ()
#8  0x76889a2a in WRAPPER1::~WRAPPER1 (this=0x7121e0,
__in_chrg=) at src/wrapper1_defn.cpp:181
#9  0x76887de5 in WRAPPER1::removeInstance () at
src/wrapper1_defn.cpp:81
#10 0x76886e71 in ss_world::removeInstance (this=0x76daa8e0
) at src/load_extension.cpp:196
#11 0x76884e80 in ss_world::~ss_world (this=0x76daa8e0
, __in_chrg=) at src/load_extension.cpp:30
#12 0x77625ff8 in __run_exit_handlers (status=0,
listp=0x779b05f8 <__exit_funcs>,
run_list_atexit=run_list_atexit@entry=true)
at exit.c:82
#13 0x77626045 in __GI_exit (status=) at exit.c:104
#14 0x7760c837 in __libc_start_main (main=0x404550 , argc=4,
argv=0x7fffdb58, init=, fini=,
rtld_fini=, stack_end=0x7fffdb48) at
../csu/libc-start.c:325
#15 0x004052b9 in _start ()
(gdb)

===

I suspect memory corruption so I ran valgrind. It reports some possible
lost blocks during extension loading but none 

Re: [sqlite] Ordering a GROUP BY, or other concatenating tricks?

[sub sk79]

On Wed, Oct 4, 2017 at 12:29 PM, Richard Hipp  wrote:

>
> This restriction on the query flattener causes your example
> query above to do what you want.
>

If subquery-flattening needs to be disabled explicitly, is using "LIMIT -1
OFFSET 0 " the recommended way?



> SQLite version 3.21.0 adds new restrictions on the query flattener
> which allows the application to control whether expensive functions
> (or subqueries) are run before or after sorting.
>

Is 'expensive' going to be a flag in fourth parameter to create_function
like SQLITE_DETERMINISTIC is?

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

[sqlite] Held transaction: behavior different for Rollback-To and Rollback

[sub sk79]

Hi,

Using SQLite3.6.22

If a transaction is holding database lock (transaction started using
BEGIN IMMEDIATE) and application code, without first calling
sqlite3_reset(), tries to
1. Rollback-To:  hangs waiting for the lock
2. Rollback:  fails with message "database is locked".

Is this difference in behavior a bug? if yes, is this fixed in later
versions - which?
or is it by design - why?

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

[sqlite] Membership operator 'IN' documentation

[sub sk79]

Hi,

The 'IN' operator syntax diagram shows a possible table name operand
   IN  [Database_Name DOT ] Table_Name
But its description seems to be missing from the paragraph about 'IN'
operator down on the page.

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

Re: [sqlite] Avoiding Database Is Locked Error

[sub sk79]

> I'm running on SCO OpenServer so I'm not
sure your product would work for me but I'll take a look

StepSqlite compiler's Linux target generates shared objects (.so) that
should work on virtually all Unix-like systems thanks to ELF format.

As an aside, further in SQLite exploration you may find it convenient
to use SQLite loadable extensions
(http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions). StepSqlite
makes it a snap to generate a loadable extension; a simple radio
button choice to generate either a Loadable Extension or a Regular C++
library from *same* PL/SQL source.

-Swapnil Kashikar
support @ metatranz . com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Avoiding Database Is Locked Error

[sub sk79]

Hi Shawn,

>  demonstrate SQLite best
>  practices, including how to correctly handle error conditions, such as
>  the database is locked condition?

>  If you pre-select and then modify, you have to be aware enough to
>  realize you MUST wrap the whole process in a manual transaction,
>  and you still need to know how to deal with all the locking and
>  busy issues that come with that.

One way to assure use of best-practices in dealing with SQLite
nitty-gritties of transactions, locking and busy errors would be to
use a tool like StepSqlite PL/SQL compiler
(https://www.metatranz.com/stepsqlite).
It automates SQLite best-practices (as recommended by Jay, Pavel and
Simon in this thread)  by wrapping things in transactions,  handling
BUSY errors and retrying after delay and others like preparing all SQL
statements only once etc.

SQLite combined with a 4GL like StepSqlite PL/SQL lets you focus on
writing your code instead of having to learn to deal with SQLite
idiosyncrasies right at the beginning.

Full disclosure: I am the creator of StepSqlite.

-Swapnil Kashikar
support @ metatranz . com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Berkeley DB adds SQL using SQLite API !!

[sub sk79]

Hi,

Somehow no one seems to  have mentioned it on this mailing list so far!?
Here is the scoop...

On March 23, Oracle announced the latest release of Oracle® Berkeley
DB - 11g Release 2 -  which introduces a new SQL API, based on lo and
behold, SQLite v3 API. What this means is that all tools that work
with SQLite will also work with Oracle Berkeley DB.

Here is the link to press release:
http://money.cnn.com/news/newsfeeds/articles/marketwire/0599534.htm

There is a supporting quote from DRH in the announcement:
 "Oracle Berkeley DB 11g Release 2 combines seamlessly the benefits of
SQLite's ubiquitous API with Oracle Berkeley DB's concurrency and
performance into an easy-to-use product," said Dr. Richard Hipp,
architect, SQLite. "SQLite users can now benefit from enhanced
concurrency and performance, and Oracle Berkeley DB users can now
benefit from an easy-to-use SQL API."

What I am confused about is the following quote from Mike Owens,
author of "The Definitive Guide to SQLite.":
"...Oracle has done a fantastic job of not only integrating the
technologies, but also being an exemplary participant in the open
source community by contributing time, expertise and resources to the
SQLite project."

How So? Is SQLite getting a high concurrency module from BDB in
exchange for its SQL API?
If yes, could someone provide details?
If not, could someone please explain why one would choose SQLite over
BDB going forward?

Thanks,
Swapnil Kashikar
www.metatranz.com/stepsqlite
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need Help SQL

[sub sk79]

Hi!,

Here is a non-math version using PL/SQL date operators and functions
available in StepSqlite (https://www.metatranz.com/stepsqlite/).
Hopefully this should be easier to follow.

You can compile the below code directly to a win32 dll on the
StepSqlite website and then use it in your VB code.

Assumes Date column is in '-MM-DD'. If using a different format,
just call   DateTime.setDateFormat()   to set proper format.


create table items(ID integer, Date date, Price float);
PACKAGE BODY MyPackage IS
PROCEDURE get_prices (start_month char, start_day char, end_month
char, end_day char  ) IS
BEGIN
-- n_* below are dates normalized to fall in a given year, here I
chose year 2000 because its a leap year and has all possible
day-numbers for proper normalization.
FOR item IN
(SELECT id, to_char(date, 'MM') mon, to_char(date, 'DD') day, price
 FROM (SELECT id, date, price,
 to_date('2000-' || start_month||'-'||start_day,
'-MM-DD') n_start,
 to_date('2000-' || end_month  ||'-'||end_day,
'-MM-DD') n_end,
 to_date(to_char(date, '2000-MM-DD'), '-MM-DD')
n_date
FROM items
  )
 WHERE (n_start < n_end AND n_date between n_start and n_end)
OR (n_start > n_end AND n_date NOT between n_end and n_start)
 ORDER BY to_char(date, 'MM-DD')
 )
LOOP
DBMS_OUTPUT.put_line(item.id || '  ' || item.mon || '
'||item.day||'  '||item.price);
END LOOP;
END;

BEGIN
insert into items(id, date, price) values(1,'2004-01-01',  1.1);
insert into items(id, date, price) values(2,'2004-02-01',  1.1);
insert into items(id, date, price) values(3,'2004-02-16',  1.1);
insert into items(id, date, price) values(4,'2004-10-01',  1.1);
insert into items(id, date, price) values(5,'2004-10-22',  1.1);

insert into items(id, date, price) values(51,'2005-01-01',  5.1);
insert into items(id, date, price) values(52,'2005-02-01',  5.1);
insert into items(id, date, price) values(53,'2005-02-16',  5.1);
insert into items(id, date, price) values(54,'2005-10-01',  5.1);
insert into items(id, date, price) values(55,'2005-10-22',  5.1);

insert into items(id, date, price) values(61,'2006-01-01',  6.1);
insert into items(id, date, price) values(62,'2006-02-01',  6.1);
insert into items(id, date, price) values(63,'2006-02-16',  6.1);
insert into items(id, date, price) values(64,'2006-10-01',  6.1);
insert into items(id, date, price) values(65,'2006-10-22',  6.1);

DBMS_OUTPUT.put_line('Price data Range: 02-15 to 10-21');
get_prices('02', '15', '10', '21');

DBMS_OUTPUT.put_line('Price data Range: 10-21 to 02-15');
get_prices('10', '21', '02','15');

rollback;
END;


Result:

Price data Range: 02-15 to 10-21
3  02  16  1.1
53  02  16  5.1
63  02  16  6.1
4  10  01  1.1
54  10  01  5.1
64  10  01  6.1
Price data Range: 10-21 to 02-15
1  01  01  1.1
51  01  01  5.1
61  01  01  6.1
2  02  01  1.1
52  02  01  5.1
62  02  01  6.1
5  10  22  1.1
55  10  22  5.1
65  10  22  6.1



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

Re: [sqlite] ROWID bug in SELECT with JOIN?

[sub sk79]

select *  from (select * from names, friends where first_name = fname)
 where _rowid_ < 2;

Above query works - which _rowid_ is this accessing?
Since the query works I get an impression the rowid is attached with the
result-set returned by a select?

-sk


On Fri, Sep 25, 2009 at 6:07 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> sub sk79 <subs...@gmail.com> wrote:
>> Select with two tables joined has no _rowid_ column in select columns.
>> I think this is a bug - just a quick check with the community before
>> filing a bug report.
>>
>> select * from names, friends where first_name = fname and _rowid_ < 2;
>> SQL error: no such column: _rowid_
>
> So, which table's _rowid_ is this statement supposed to access? The
> statement is wrong and shouldn't run. The only issue here is that the
> error message is somewhat misleading - it could probably say something
> like "ambiguous column name".
>
> Igor Tandetnik
>
>
>
> ___
> 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] Most wanted features of SQLite ?

[sub sk79]

StepSqlite PL/SQL compiler for SQLite is certainly not for all.
It is simply aimed at what we believe is a majority.

http://www.metatranz.com/stepsqlite/

regards,
-sk

>>But I write stored procedures and triggers for PostgreSQL on Tcl. PL/pgSQL or 
>>PL/SQL is not the best solution to all.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Most wanted features of SQLite ?

[sub sk79]

>> 1.  Generate a loadable SQLite extension and distribute it with DB.
>
> We can write C extension without any wrapper. Why we need to learn
> your extension for getting the same result and possible new bugs?

PL/SQL has a very wide user-base and a huge repository of existing
code-base in the world. Using StepSqlite PL/SQL compiler this huge
base can use SQLite by reusing their code as well as reusing their
skills - no learning curve for this set of users.

For those who do need to learn PL/SQL, it is still worthwhile to
utilize StepSqlite because:

PL/SQL is specially designed for and very suitable for handling
database operations, while other general purpose languages like TCL,
lua, C , C++ etc are very suitable indeed for application programming.
  StepSqlite enables combining the respective power of these two sets
of languages in writing apps with SQLite back-ends by using the
concept of Stored Procedures/Functions and Packages.
All big databases operate this way as well: for ex, Oracle lets apps
written in C++ to call stored procedures written in PL/SQL.

StepSqlite is committed to bringing this power and convenience from
the big database world to the small database world while preserving
the small, fast and reliable.

More benefits of StepSqlite described here:
http://www.metatranz.com/stepsqlite/benefits.html

http://www.metatranz.com/stepsqlite

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

Re: [sqlite] Most wanted features of SQLite ?

[sub sk79]

The word 'Stored Procedures ' when used in the context of DBMS is used
to refer to several meanings:

1. Efficiency
   - compile once when 'stored' and run multiple-times.
2. Data Encapsulation & Access control for DB
- DB owns and controls access to its API 'stored' in it.
3. Client-server design
-  server 'stores' the procedures that any client can then use.
4. Procedural language
-  a language which supports procedural statements (in
addition to the declarative ones provided by SQL): loops,
conditionals, variables etc.

5. Physically residing in DB
- API resides and moves with the DB. This is the meaning most
people are familiar with.

StepSqlite satisfies #1, #2, #3 and #4 today and looks forward to
support for #5 being implemented in future versions of SQLite.

As for the question about distributing libs, StepSqlite gives users
two ways to integrate the compiled PL/SQL code into their SQLite
applications:

1.  Generate a loadable SQLite extension and distribute it with DB.

2.  If distributing loadable extensions is a concern (security or
otherwise), StepSqlite also has an option to generate a regular C++
library instead. This library can be linked into the user's
application code and thus becomes part of the user's code just like
any other library would. Wherever the app goes the stored
procedures/functions go too.

http://www.metatranz.com/stepsqlite

Regards,
-sk



On Sat, Sep 19, 2009 at 10:33 AM, Alexey Pechnikov
 wrote:
> Hello!
>
> On Saturday 19 September 2009 02:17:39 Subsk79 wrote:
>> StepSqlite brings powerful Stored Procedure support with full power of
>> PL/SQL syntax to SQLite. It is a 'compiler' as opposed to a mere
>> 'wrapper' so it generates much more efficient code than any wrapper
>> could ever achieve - for instance, it pre-compiles all SQL in your
>> code right when the lib is loaded - no compile-overload at runtime -
>> this is exactly what  one expects from a  true 'Stored' Procedure.
>
> Do you have support for compiled extension stored into database table?
> It's not good way to distribute external libs.
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] Booleans in SQLite

[sub sk79]

>
>                I'm just curious how difficult it would be to add
> support for booleans in SQLite.
>
> as well as use those keywords instead of creating integer
> fields using 0 and 1.
>

Check out StepSqlite PL/SQL compiler for SQLite which supports
BOOLEAN data type among many other goodies.

http://www.metatranz.com/stepsqlite

It lets you script SQLite to do things like:

create table  admissions(fn varchar(20), ln varchar(20), accepted BOOLEAN);
begin
  for student in (select * from admissions where accepted = TRUE)
  loop
dbms_output.put_line('First name:' || student.fn || '  Last name:'
|| student.ln || '  Accepted: '||student.accepted );

 if student.accepted then
  dbms_output.put_line(student.fn ||'  '|| student.ln || '  has
been admitted.' );
 else
  dbms_output.put_line(student.fn ||'  '|| student.ln || '  has
NOT been admitted.' );
 end if;
  end loop;
end;

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

Re: [sqlite] load extension -- unload hook?

[sub sk79]

Sure!

Here are the results:
 Good:
   The 'destructor' function attribute works just as advertised. I
have included below the modified half.so example code from sqlite website
with the 'constructor' and  'destructor' functions (for linux using gcc).

Bad:
The '.q' command on sqlite command-line utility  attempts to close
the database before calling 'dlclose' on the loaded extension library. I
think this should be fixed in SQLite code.  Here is the log:
===
sqlite> .load ./libMyLib.so
Success create function: MyLib_test
sqlite> .q
error closing database: Unable to close due to unfinalised statements
Success drop function: MyLib_test
===

MODIFIED HALF.c
===
#include 
SQLITE_EXTENSION_INIT1

void construct() __attribute__ ((constructor));
void construct(){
printf("-In Constructor-\n");
};
void destruct() __attribute__ ((destructor));
void destruct(){
printf("-In Destructor-\n");
};
/*
** The half() SQL function returns half of its input value.
*/
static void halfFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  sqlite3_result_double(context, 0.5*sqlite3_value_double(argv[0]));
}

/* SQLite invokes this routine once when it loads the extension.
** Create new functions, collating sequences, and virtual table
** modules here.  This is usually the only exported symbol in
** the shared library.
*/
int sqlite3_extension_init(
  sqlite3 *db,
  char **pzErrMsg,
  const sqlite3_api_routines *pApi
){
//sqlite3_enable_load_extension(db,1);
  SQLITE_EXTENSION_INIT2(pApi)
  sqlite3_create_function(db, "half", 1, SQLITE_ANY, 0, halfFunc, 0, 0);
  return 0;
}

===


LOG OF HALF.SO
===
sqlite> .load ./half.so
-In Constructor-
sqlite> .q
-In Destructor-
===

Thanks,
SK

On Tue, Sep 1, 2009 at 5:54 PM, Jean-Christophe Deschamps wrote:

> Hi,
>
> ´¯¯¯
> >2. Following up on windows dllmain  info - which was very useful in itself
> >-  but since we use both windows and linux, I checked the equivalent for
> >linux as well and yes, luckily, gcc allows you to define a 'function
> >attribute' called 'constructor' and 'destructor' which can be used to
> >export
> >the functions in your shared lib to  invoke at dlopen and dlclose. So, I
> >should be able to use this 'destructor'-attributed function for library
> >stuff cleanup on linux - haven't gotten to testing it yet though.
> `---
>
> I wasn't sure such a possibility was available on Linux, but it was
> nonetheless probable nowadays.
>
> So if it works more or less similarily, you are just a few #ifdef away
> from a workable solution for both systems.  That's a good news.  Let us
> know how it turns out.  I bet others would love to find an already
> tested solution in some future.
>
>
>
> ___
> 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] load extension -- unload hook?

[sub sk79]

Firstly thanks to both of you for useful replies.

Here is the new info I got from your replies:
1. sqlite_load_extension is per-db-connection - Oddly I did not see any
explicit reference to this in either of the two places I looked for it: i)
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions or ii)
http://www.sqlite.org/c3ref/load_extension.html

2. Following up on windows dllmain  info - which was very useful in itself
-  but since we use both windows and linux, I checked the equivalent for
linux as well and yes, luckily, gcc allows you to define a 'function
attribute' called 'constructor' and 'destructor' which can be used to export
the functions in your shared lib to  invoke at dlopen and dlclose. So, I
should be able to use this 'destructor'-attributed function for library
stuff cleanup on linux - haven't gotten to testing it yet though.

3. Libs loaded but no more useful will be swapped out and will not consume
memory - interesting point.

As for what I have tried from your suggestions so far:
1. Tried the collating sequence xDestroy trick but that did not work for my
case - this because my lib prepares some statements internally and seems
like close() checks for unfinalised prepared statements and fails even
before control ever reaches xDestroy for collations:
Now, if only, it reached the xDestroy -  I do finalize those prepared
statements there!
===
sqlite> .load ./libMylib.so
sqlite> .q
error closing database: Unable to close due to unfinalised statements
===

2. I would prefer having an 'unload lib' command because libs will be given
to users (other than me) on both embedded and desktop-type envs and how
their code uses it is beyond my control. But still, yes, I already have a
function in my lib which can be called to do cleanup and I have registered
it with sqlite so that it can be invoked using "SELECT mylib_cleanup();"  -
though not a real unload, I can set a flag within mylib_cleanup() to
indicate the lib 'closed' (and check this flag in beginning of all functions
exported from lib so that user code cannot access it) and then let this
'closed' lib remain dead in the memory or let it be swapped out as you
mentioned. While this works and I will use it just because my libs must work
on older and current sqlite versions, for future sqlite versions, I would
suggest having an 'unload lib' command to simplify coding this unload
behavior.

Thanks,
SK

On Mon, Aug 31, 2009 at 10:51 AM, Jean-Christophe Deschamps
wrote:

>
> At 18:25 30/08/2009, you wrote:
> 疮
>
>  When we load an extension it invokes  sqlite3_extension_init(). Lets say,
>> in
>> addition to creating functions, the loaded extension library also does
>> some
>> internal data structure allocations,  initializations etc here.
>>
>> Now, when the database is closed the loaded extension needs  to do
>> cleanup.
>> To do that:
>> 1. Is it possible to register a callback which is invoked on database
>> close?
>> 2. or is there a plan to add something like: sqlite3_extension_end() which
>> can be used for this?
>> 3.  and if answer to 2 is yes, how about add an 'unload' command for
>> extensions as well so that we can unload extensions when they are no more
>> needed?
>>
>> 4. If none of the above functionality exists yet, is there a workaround
>> available today to achieve this?
>>
> `---
>
> As a sidenote to what Roger just disclosed (umm, what a strange "hack"!),
> it is possible that a more conventional way exist.  From what I can tell, it
> all depends on the environment.  At least there is one clean way in the case
> of a .dll extension under Windows.  It's quite possible that a similar way
> could work for other systems as well.
>
> What is your environment?
>
>
> In the Windows .dll case, you can have the extension registered in several
> ways:
>
>  1a) your app calls sqlite3_load_extension()
> or
>  1b) your app issues a "SELECT load_extension('yourext.dll'[,
> 'entry_point']);"
>
> and your .dll has code like this:
>
> DLL_EXPORT int sqlite3_extension_init(   // std name of entry point
>  sqlite3 *db,
>  char **pzErrMsg,
>  const sqlite3_api_routines *pApi
> ){
>  SQLITE_EXTENSION_INIT2(pApi)
>  // calls to sqlite3_create_function(db, ...)
>  // and/or calls to sqlite3_create_collation(db, ...)
>  return SQLITE_OK; // or SQLITE_ERROR
> }
>
> The drawback is that the extension is registered for the calling
> connection
> only. Besides what Roger suggested there's no official callback for
> cleanup
> (again on a connection-basis).
>
>  2) your app issues a call to sqlite_auto_extension(entry_point).  Then the
> extension
> will be available for any _new_ DB connection.
> You can invoke sqlite3_reset_auto_extension() to free memory allocated
> to
> manage the extension, but I believe you don't have an official hook to
> clean
> anything else of your own.
>
>  3) your app loads the extension as 1a) or 1b) and your load entry point
> looks like
>
> DLL_EXPORT int 

[sqlite] load extension -- unload hook?

[sub sk79]

Hi,

When we load an extension it invokes  sqlite3_extension_init(). Lets say, in
addition to creating functions, the loaded extension library also does some
internal data structure allocations,  initializations etc here.

Now, when the database is closed the loaded extension needs  to do cleanup.
To do that:
1. Is it possible to register a callback which is invoked on database close?
2. or is there a plan to add something like: sqlite3_extension_end() which
can be used for this?
3.  and if answer to 2 is yes, how about add an 'unload' command for
extensions as well so that we can unload extensions when they are no more
needed?

4. If none of the above functionality exists yet, is there a workaround
available today to achieve this?

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

Re: [sqlite] loadable extension doesn't load

[sub sk79]

Thanks!

-SK

On Tue, Aug 25, 2009 at 8:09 PM, Roger Binns <rog...@rogerbinns.com> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> sub sk79 wrote:
> > I followed the exact instructions (
> > http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions) on SQLite wiki
> for
> > loadable extensions and still have run into this issue.
> > Can someone help me here?
>
> The instructions are how to make a loadable extension in C, but you are
> using C++.
>
> > /home/sk/utils/sqlite/sqlite-3.6.17/half.so: undefined symbol:
> > sqlite3_extension_init
>
> You need to declare sqlite3_extension_init as extern "C".  If you do not
> then the name gets mangled according to normal C++ symbol mangling rules.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkqUfT4ACgkQmOOfHg372QS/xQCfVwBrbNLGlX6/cliQiLDY2yrJ
> YvoAoMb7d+pA4KJauvBREa8CWfSkkSwB
> =tyLu
> -END PGP SIGNATURE-
> ___
> 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] loadable extension doesn't load

[sub sk79]

Hi,

I followed the exact instructions (
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions) on SQLite wiki for
loadable extensions and still have run into this issue.
Can someone help me here?


@HOME-Ubuntu:~/utils/sqlite/sqlite-3.6.17$ gcc -I`pwd` -fPIC -shared
./loadableext.cpp -o half.so
@HOME-Ubuntu:~/utils/sqlite/sqlite-3.6.17$ ls hal*
half.so
@HOME-Ubuntu:~/utils/sqlite/sqlite-3.6.17$ ./sqlite3
SQLite version 3.6.17
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load half.so
/home/sk/utils/sqlite/sqlite-3.6.17/half.so: undefined symbol:
__gxx_personality_v0


I googled  *__gxx_personality_v0* and one solution was to link with
libstdc++ ,   so I tried that but it still won't work though it failed with
a different error this time. here is the log for that:


@HOME-Ubuntu:~/utils/sqlite/sqlite-3.6.17$ gcc -I`pwd` -fPIC -shared
-lstdc++ ./loadableext.cpp -o half.so
@HOME-Ubuntu:~/utils/sqlite/sqlite-3.6.17$ ./sqlite3
SQLite version 3.6.17
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load half.so
/home/sk/utils/sqlite/sqlite-3.6.17/half.so: undefined symbol:
sqlite3_extension_init



GCC Version is: gcc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu4)

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

Re: [sqlite] how can we solve IF EXIST in SQLite

[sub sk79]

Hi,

You might want to check-out StepSqlite PL/SQL compiler for SQLite at
http://www.metatranz.com/stepsqlite
Using it you can write the trigger (as part of a package body) almost the
way you wrote in original post. StepSqlite compiles the PL/SQL code to a
linux x86 shared library which can be linked in to your C++ app and used as
the database interface to SQLite. There is an easy-to-follow  tutorial here:
 http://www.metatranz.com/stepsqlite/tutorial.html

*If you do not use C++*, wait till StepSqlite adds support for creating a
loadable SQLite extension for SQLite and then you can simply load the
compiled shared library into your SQLite database.

(NOTE: below given is not pseudo code - this is actual code which you need
to write and leave the rest to StepSqlite):

===
CREATE TABLE stock_tab(stock_id NUMBER(5), prod_batch_code NUMBER(5),
stock_qty NUMBER(5), stock_date date);
CREATE TABLE purchase_tab(product_batch_code NUMBER(5), purchase_qty
NUMBER(5));

PACKAGE BODY MyDBinterface IS
BEGIN
CREATE TRIGGER insert_stock_from_product
AFTER INSERT ON purchase_tab
DECLARE
count NUMBER(5);
BEGIN
SELECT count(prod_batch_code) INTO count FROM stock_tab WHERE
prod_batch_code= :new.product_batch_code;
IF count > 0  THEN
UPDATE stock_tab
SET stock_qty = stock_qty + :new.purchase_qty
WHERE prod_batch_code= :new.product_batch_code
ELSE
INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
stock_date)
VALUES (20009, :new.product_batch_code, :new.purchase_qty,
sysdate() );
END IF;
END;
END;

===

-SK

On Tue, Jun 2, 2009 at 8:20 AM, robinsmathew wrote:

>
> guys i ll clarify the problem
> this is the purchase table here purchase id is PK
>
> purchase_id  prod_batch_code  vendor_type_code  purchase_qty  purchase_date
> ---  ---    
> ---
> 11000 1 100 2009-05-26
> 18:19:27
> 21001 1 100 2009-05-26
> 18:19:31
> 31002 1 100 2009-05-26
> 18:19:35
> 41003 1 100 2009-05-26
> 18:19:49
>
> this is the stock table here stock_id is PK and prod_batch_code is FK
>
> stock_idprod_batch_code  stock_qty   stock_date
> --  ---  --  ---
> 20001   1001 105 2009-05-26 18:19:27
> 20002   1002 100ps   2009-05-26 18:19:31
> 20003   1003 100ps   2009-05-26 18:19:35
> 20004   1003 100ps   2009-05-26 18:19:43
> 20005   1002 100ps   2009-05-26 18:19:44
> 20006   1001 100ps   2009-05-26 18:19:49
> 20007   1000 85  2009-05-26 18:19:50
> 20008   1000 85  2009-05-26 18:19:51
>
> i wrote a trigger
> CREATE TRIGGER insert_stock_from_product
>AFTER INSERT ON purchase_tab
>BEGIN
> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date)
> values (new.purchase_id+2, new.prod_batch_code,
> new.purchase_qty,
> new.purchase_date );
>END;
>
> instead of inserting the same products repeatedly in the stock table i jus
> want the quantity as well as the dates to be updated . and wen i insert a
> new product_batch_code to the purchase table its shuld be inserted in the
> stock table also...
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users