core using sqlite 3.17.0 on solaris

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

core using sqlite 3.17.0 on solaris

Marcel Hofstetter
hi

I used sqlite 3.11.1 in the past year. works well.

compiled 3.17.0 now. running on solaris 11.
uname -a
SunOS g0069 5.11 11.3 sun4v sparc sun4v

simple select on existing table cores

# ./sqlite3 /var/opt/jomasoft/vdcf/db/infra.db
SQLite version 3.17.0 2017-02-13 16:02:40
Enter ".help" for usage hints.
sqlite> select * from server;
Bus Error (core dumped)

# pstack core
core 'core' of 5490:    ./sqlite3 /var/opt/jomasoft/vdcf/db/infra.db
 00000001000e04a8 sqlite3ParserInit (ffffffff7fffda1c, 0, 0, 0, 0, 0) + 18
 00000001000e6160 sqlite3RunParser (ffffffff7fffe7b8, 100205150,
ffffffff7fffea10, fffffffffffffff8, 0, ffffffff7fffea08) + 78
 00000001000b57a0 sqlite3Prepare (1002028e8, 100205150,
ffffffffffffffff, 1, 0, ffffffff7fffed08) + 2a0
 00000001000b5b88 sqlite3LockAndPrepare (1002028e8, 100205150,
ffffffffffffffff, 1, 0, ffffffff7fffed08) + c0
 00000001000b5e1c sqlite3_prepare_v2 (1002028e8, 100205150,
ffffffffffffffff, ffffffff7fffed08, ffffffff7fffecf8, 0) + 44
 000000010000926c shell_exec (1002028e8, 100205150, 1000062d0,
ffffffff7ffff090, ffffffff7fffee60, 0) + 4c
 0000000100012d20 runOneSqlLine (ffffffff7ffff090, 100205150, 0, 1, 1,
14) + 50
 00000001000132ec process_input (ffffffff7ffff090, 0, 0, 1002050d0, 15,
100205150) + 424
 0000000100014be0 main (1, 0, 0, 0, 2, 100205b20) + 1218
 0000000100004e5c _start (0, 0, 0, 0, 0, 0) + 17c

details about the database
# ./sqlite3 /var/opt/jomasoft/vdcf/db/infra.db
SQLite version 3.17.0 2017-02-13 16:02:40
Enter ".help" for usage hints.
sqlite> .dbinfo
database page size:  1024
write format:        1
read format:         1
reserved bytes:      0
file change counter: 47643
database page count: 138
freelist page count: 17
schema cookie:       549
schema format:       2
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:       1 (utf8)
user version:        0
application id:      0
software version:    3011001
Bus Error (core dumped)


Thanks,
Marcel
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: core using sqlite 3.17.0 on solaris

Richard Hipp-3
On 3/29/17, Marcel Hofstetter <[hidden email]> wrote:
>
> compiled 3.17.0 now. running on solaris 11.
>
> simple select on existing table cores
>

Please apply the patch below and let me know whether or not it helps:

 */
 int sqlite3RunParser(Parse *pParse, const char *zSql, char **pzErrMsg){
   int nErr = 0;                   /* Number of errors encountered */
   void *pEngine;                  /* The LEMON-generated LALR(1) parser */
   int n = 0;                      /* Length of the next token token */
   int tokenType;                  /* type of the next token */
   int lastTokenParsed = -1;       /* type of the previous token */
   sqlite3 *db = pParse->db;       /* The database connection */
   int mxSqlLen;                   /* Max length of an SQL string */
 #ifdef sqlite3Parser_ENGINEALWAYSONSTACK
-  unsigned char zSpace[sizeof(yyParser)];  /* Space for parser engine object */
+  /* Space to hold the Lemon-generated Parser object */
+  sqlite3_uint64 zSpace[sizeof(yyParser)/sizeof(sqlite_uint64)];
 #endif

   assert( zSql!=0 );
   mxSqlLen = db->aLimit[SQLITE_LIMIT_SQL_LENGTH];
   if( db->nVdbeActive==0 ){
     db->u1.isInterrupted = 0;
   }
   pParse->rc = SQLITE_OK;
   pParse->zTail = zSql;
   assert( pzErrMsg!=0 );



--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: core using sqlite 3.17.0 on solaris

Clemens Ladisch
Richard Hipp wrote:
>  #ifdef sqlite3Parser_ENGINEALWAYSONSTACK
> -  unsigned char zSpace[sizeof(yyParser)];  /* Space for parser engine object */
> +  /* Space to hold the Lemon-generated Parser object */
> +  sqlite3_uint64 zSpace[sizeof(yyParser)/sizeof(sqlite_uint64)];
>  #endif

The yyParser type is known at this place (otherwise, sizeof() would not
work).  So why isn't a variable of this type defined directly?


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: core using sqlite 3.17.0 on solaris

Scott Robison-2
On Thu, Mar 30, 2017 at 10:44 AM, Clemens Ladisch <[hidden email]> wrote:
> Richard Hipp wrote:
>>  #ifdef sqlite3Parser_ENGINEALWAYSONSTACK
>> -  unsigned char zSpace[sizeof(yyParser)];  /* Space for parser engine object */
>> +  /* Space to hold the Lemon-generated Parser object */
>> +  sqlite3_uint64 zSpace[sizeof(yyParser)/sizeof(sqlite_uint64)];
>>  #endif
>
> The yyParser type is known at this place (otherwise, sizeof() would not
> work).  So why isn't a variable of this type defined directly?

Also, isn't the new code potentially allocating a smaller buffer in
zSpace? If sizeof(yyParser) is 15, the removed line would allocate a
15 element array of unsigned char objects for a total of 15 bytes. The
added line would allocate a 15/8 = 1 element array of sqlite3_uint64
objects for a total of 8 bytes.

--
Scott Robison
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: core using sqlite 3.17.0 on solaris

Richard Hipp-3
In reply to this post by Clemens Ladisch
On 3/30/17, Clemens Ladisch <[hidden email]> wrote:
> Richard Hipp wrote:
>>  #ifdef sqlite3Parser_ENGINEALWAYSONSTACK
>> -  unsigned char zSpace[sizeof(yyParser)];  /* Space for parser engine
>> object */
>> +  /* Space to hold the Lemon-generated Parser object */
>> +  sqlite3_uint64 zSpace[sizeof(yyParser)/sizeof(sqlite_uint64)];
>>  #endif
>
> So why isn't a variable of this type defined directly?

Lack of sense.  Fixed now.  Thanks.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: core using sqlite 3.17.0 on solaris

Richard Hipp-3
In reply to this post by Scott Robison-2
On 3/30/17, Scott Robison <[hidden email]> wrote:
>>
> Also, isn't the new code potentially allocating a smaller buffer in
> zSpace? If sizeof(yyParser) is 15, the removed line would allocate a
> 15 element array of unsigned char objects for a total of 15 bytes. The
> added line would allocate a 15/8 = 1 element array of sqlite3_uint64
> objects for a total of 8 bytes.
>

The code that I actually checked in fixed that.
https://www.sqlite.org/src/artifact/d62a8f87?ln=486

But that is an academic question now that the problem is fixed
properly, per Clemens' suggestion.
https://www.sqlite.org/src/artifact/de2ec4fe?ln=485

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: core using sqlite 3.17.0 on solaris

Scott Robison-2
On Thu, Mar 30, 2017 at 11:22 AM, Richard Hipp <[hidden email]> wrote:

> On 3/30/17, Scott Robison <[hidden email]> wrote:
>>>
>> Also, isn't the new code potentially allocating a smaller buffer in
>> zSpace? If sizeof(yyParser) is 15, the removed line would allocate a
>> 15 element array of unsigned char objects for a total of 15 bytes. The
>> added line would allocate a 15/8 = 1 element array of sqlite3_uint64
>> objects for a total of 8 bytes.
>>
>
> The code that I actually checked in fixed that.
> https://www.sqlite.org/src/artifact/d62a8f87?ln=486
>
> But that is an academic question now that the problem is fixed
> properly, per Clemens' suggestion.
> https://www.sqlite.org/src/artifact/de2ec4fe?ln=485

Right. I posted 60 seconds or so before your email hit my inbox. :)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: core using sqlite 3.17.0 on solaris

Marcel Hofstetter
In reply to this post by Richard Hipp-3
Am 30.03.2017 um 19:22 schrieb Richard Hipp:

> On 3/30/17, Scott Robison <[hidden email]> wrote:
>>>
>> Also, isn't the new code potentially allocating a smaller buffer in
>> zSpace? If sizeof(yyParser) is 15, the removed line would allocate a
>> 15 element array of unsigned char objects for a total of 15 bytes. The
>> added line would allocate a 15/8 = 1 element array of sqlite3_uint64
>> objects for a total of 8 bytes.
>>
>
> The code that I actually checked in fixed that.
> https://www.sqlite.org/src/artifact/d62a8f87?ln=486

Fix above works here.
Thank you.

Best regards,
Marcel Hofstetter

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

If I got many to many relationship data first, how do I insert them to my table?

邱朗
Say my mobile app has a customer table, a product table, and an order table to record who buys what, the order table basically has 2 foreign keys, customer_id & product_id.

Now I got the order information first, within in it I can't find the customer information in my local sqlite table. As it turns out this is a new customer, whose information will come later from another thread/queue. To make things even worse the customer id I got from the order information is not the same one I used locally. My local customer id is INTEGER PRIMARY KEY (I do record that "true customer id" as another column and set index on it)

So how I do record this order information? I can come up with some clumsy solution, e.g. if I can't find contact info, I insert a record for it first. Later after I get the real information for this customer, I update customer & order table. But I was wondering is there any "standard" way for the situation like this?
BTW, I did ask the same question at stackoverflow, but because I use sqlite (while all the data come from web storing in MySQL) I was wondering if sqlite has any specific solution for it.
Thanks,
Qiulang
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: If I got many to many relationship data first, how do I insert them to my table?

R Smith

On 2017/03/31 12:08 PM, 邱朗 wrote:
> Say my mobile app has a customer table, a product table, and an order table to record who buys what, the order table basically has 2 foreign keys, customer_id & product_id.
>
> Now I got the order information first, within in it I can't find the customer information in my local sqlite table. As it turns out this is a new customer, whose information will come later from another thread/queue. To make things even worse the customer id I got from the order information is not the same one I used locally. My local customer id is INTEGER PRIMARY KEY (I do record that "true customer id" as another column and set index on it)
>
> So how I do record this order information? I can come up with some clumsy solution, e.g. if I can't find contact info, I insert a record for it first. Later after I get the real information for this customer, I update customer & order table. But I was wondering is there any "standard" way for the situation like this?
> BTW, I did ask the same question at stackoverflow, but because I use sqlite (while all the data come from web storing in MySQL) I was wondering if sqlite has any specific solution for it.

Your question would arise no matter which DB you use, so it's not really
an SQLite question - but - we're a fun bunch of people, and many here
would have run into the same problem, so you might find some answers.

To start with, Your "clumsy" idea is not so clumsy, it is a practice
some people use. The flaw in that is when the customer actually existed
already, you just didn't know who it was, so now you end up with 2
records that mean the same customer with disjointed keys and Orders
connected to both.

What we usually do is put any orders that are new in a table that looks
exactly like the orders table (only without any foreign key constraints
and such) and then move them to the main order table as soon as they are
confirmed and connected to a customer. This also helps if this is, for
instance, an online interface or help-desk type system and people can
make orders which they might still cancel or amend, etc.
The biggest problem with this method is that for queries on "current
orders" you would need to join the output from the two tables - easy
enough to do, but if you already have millions of lines of code, it
could involve a lot of changes.

Another option is to create an "Unknown" customer, and link any new
orders to it. You can easily change that parent-id on the order later.

If however your problem boils down to you not being "sure" if you have
now the correct parent (at the point of insert) - we can't help you with
that. You have to either be sure, or use a temporary situation until you
are.

Whatever the solution you find, at the point stuff gets inserted to a
permanent table with foreign key constraints, all the correct
constraint-related information/links has to be known.

HTH - Cheers,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: If I got many to many relationship data first, how do I insert them to my table?

Quan Yong Zhai
In reply to this post by 邱朗
对于这样的问题SQLite也没有特别的解决方案。你说的方法几乎就是“标准”的做法,先把customer_id 设置为null, 然后插入该订单,( 这样就不会引起外部键检查失败), 当获取该订单准确的customer_id后,再把它更新。
App的逻辑应考虑到这一点,所有customer_id 为null的订单都是不完整的,相当于草稿。

Zhai

Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10

From: 邱朗<mailto:[hidden email]>
Sent: 2017年3月31日 18:09
To: SQLite mailing list<mailto:[hidden email]>
Subject: [sqlite] If I got many to many relationship data first, how do I insert them to my table?

Say my mobile app has a customer table, a product table, and an order table to record who buys what, the order table basically has 2 foreign keys, customer_id & product_id.

Now I got the order information first, within in it I can't find the customer information in my local sqlite table. As it turns out this is a new customer, whose information will come later from another thread/queue. To make things even worse the customer id I got from the order information is not the same one I used locally. My local customer id is INTEGER PRIMARY KEY (I do record that "true customer id" as another column and set index on it)

So how I do record this order information? I can come up with some clumsy solution, e.g. if I can't find contact info, I insert a record for it first. Later after I get the real information for this customer, I update customer & order table. But I was wondering is there any "standard" way for the situation like this?
BTW, I did ask the same question at stackoverflow, but because I use sqlite (while all the data come from web storing in MySQL) I was wondering if sqlite has any specific solution for it.
Thanks,
Qiulang
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

If I got many to many relationship data first, how do I insert them to my table?

邱朗
In reply to this post by R Smith
"Another option is to create an "Unknown" customer, and link any new  orders to it. You can easily change that parent-id on the order later."This solution seems not work (especially in my case) because it is easy to have more than one unknown customer. Then I can't decide who orders what later.


The other solution (from stackoverflow) is to use "Deferred Foreign Key Constraints". That seems quite promising.



Thanks,
Qiulang



At 2017-03-31 19:44:56, "R Smith" <[hidden email]> wrote:

>
>On 2017/03/31 12:08 PM, 邱朗 wrote:
>> Say my mobile app has a customer table, a product table, and an order table to record who buys what, the order table basically has 2 foreign keys, customer_id & product_id.
>>
>> Now I got the order information first, within in it I can't find the customer information in my local sqlite table. As it turns out this is a new customer, whose information will come later from another thread/queue. To make things even worse the customer id I got from the order information is not the same one I used locally. My local customer id is INTEGER PRIMARY KEY (I do record that "true customer id" as another column and set index on it)
>>
>> So how I do record this order information? I can come up with some clumsy solution, e.g. if I can't find contact info, I insert a record for it first. Later after I get the real information for this customer, I update customer & order table. But I was wondering is there any "standard" way for the situation like this?
>> BTW, I did ask the same question at stackoverflow, but because I use sqlite (while all the data come from web storing in MySQL) I was wondering if sqlite has any specific solution for it.
>
>Your question would arise no matter which DB you use, so it's not really
>an SQLite question - but - we're a fun bunch of people, and many here
>would have run into the same problem, so you might find some answers.
>
>To start with, Your "clumsy" idea is not so clumsy, it is a practice
>some people use. The flaw in that is when the customer actually existed
>already, you just didn't know who it was, so now you end up with 2
>records that mean the same customer with disjointed keys and Orders
>connected to both.
>
>What we usually do is put any orders that are new in a table that looks
>exactly like the orders table (only without any foreign key constraints
>and such) and then move them to the main order table as soon as they are
>confirmed and connected to a customer. This also helps if this is, for
>instance, an online interface or help-desk type system and people can
>make orders which they might still cancel or amend, etc.
>The biggest problem with this method is that for queries on "current
>orders" you would need to join the output from the two tables - easy
>enough to do, but if you already have millions of lines of code, it
>could involve a lot of changes.
>
>Another option is to create an "Unknown" customer, and link any new
>orders to it. You can easily change that parent-id on the order later.
>
>If however your problem boils down to you not being "sure" if you have
>now the correct parent (at the point of insert) - we can't help you with
>that. You have to either be sure, or use a temporary situation until you
>are.
>
>Whatever the solution you find, at the point stuff gets inserted to a
>permanent table with foreign key constraints, all the correct
>constraint-related information/links has to be known.
>
>HTH - Cheers,
>Ryan
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: If I got many to many relationship data first, how do I insert them to my table?

petern
Qiulang. I am curious about this requirement. Is there an example commerce
site in the real world where having a one to one match in a master customer
login table to all real customers is vital to the mission?  What sort of
business would have that?  Even banks do not have such surety about
customer entity if the entity has multiple tax numbers.

It is also normal for customer-centric systems to later allow login
identities and their transaction histories to be consolidated by manual
intervention given the login owner wishes it and common identity can be
satisfactorily established.  There are also practical and lawful reasons
why some entities would need or prefer to have multiple login identities.

FYI, if you are trying implement a just-in-time shipment consolidation
function, couldn't an extra order process page/form be added where
different login with identical name and delivery address makes subsequent
order(s) during the same shipping cycle?  This condition can be detected
very easily by simple query over the hypothetical tables you've mentioned.

Peter





On Tue, Apr 4, 2017 at 9:38 PM, 邱朗 <[hidden email]> wrote:

> "Another option is to create an "Unknown" customer, and link any new
> orders to it. You can easily change that parent-id on the order later."This
> solution seems not work (especially in my case) because it is easy to have
> more than one unknown customer. Then I can't decide who orders what later.
>
>
> The other solution (from stackoverflow) is to use "Deferred Foreign Key
> Constraints". That seems quite promising.
>
>
>
> Thanks,
> Qiulang
>
>
>
> At 2017-03-31 19:44:56, "R Smith" <[hidden email]> wrote:
> >
> >On 2017/03/31 12:08 PM, 邱朗 wrote:
> >> Say my mobile app has a customer table, a product table, and an order
> table to record who buys what, the order table basically has 2 foreign
> keys, customer_id & product_id.
> >>
> >> Now I got the order information first, within in it I can't find the
> customer information in my local sqlite table. As it turns out this is a
> new customer, whose information will come later from another thread/queue.
> To make things even worse the customer id I got from the order information
> is not the same one I used locally. My local customer id is INTEGER PRIMARY
> KEY (I do record that "true customer id" as another column and set index on
> it)
> >>
> >> So how I do record this order information? I can come up with some
> clumsy solution, e.g. if I can't find contact info, I insert a record for
> it first. Later after I get the real information for this customer, I
> update customer & order table. But I was wondering is there any "standard"
> way for the situation like this?
> >> BTW, I did ask the same question at stackoverflow, but because I use
> sqlite (while all the data come from web storing in MySQL) I was wondering
> if sqlite has any specific solution for it.
> >
> >Your question would arise no matter which DB you use, so it's not really
> >an SQLite question - but - we're a fun bunch of people, and many here
> >would have run into the same problem, so you might find some answers.
> >
> >To start with, Your "clumsy" idea is not so clumsy, it is a practice
> >some people use. The flaw in that is when the customer actually existed
> >already, you just didn't know who it was, so now you end up with 2
> >records that mean the same customer with disjointed keys and Orders
> >connected to both.
> >
> >What we usually do is put any orders that are new in a table that looks
> >exactly like the orders table (only without any foreign key constraints
> >and such) and then move them to the main order table as soon as they are
> >confirmed and connected to a customer. This also helps if this is, for
> >instance, an online interface or help-desk type system and people can
> >make orders which they might still cancel or amend, etc.
> >The biggest problem with this method is that for queries on "current
> >orders" you would need to join the output from the two tables - easy
> >enough to do, but if you already have millions of lines of code, it
> >could involve a lot of changes.
> >
> >Another option is to create an "Unknown" customer, and link any new
> >orders to it. You can easily change that parent-id on the order later.
> >
> >If however your problem boils down to you not being "sure" if you have
> >now the correct parent (at the point of insert) - we can't help you with
> >that. You have to either be sure, or use a temporary situation until you
> >are.
> >
> >Whatever the solution you find, at the point stuff gets inserted to a
> >permanent table with foreign key constraints, all the correct
> >constraint-related information/links has to be known.
> >
> >HTH - Cheers,
> >Ryan
> >
> >_______________________________________________
> >sqlite-users mailing list
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: If I got many to many relationship data first, how do I insert them to my table?

Richard Damon
In reply to this post by 邱朗
On 3/31/17 6:08 AM, 邱朗 wrote:
> Say my mobile app has a customer table, a product table, and an order table to record who buys what, the order table basically has 2 foreign keys, customer_id & product_id.
>
> Now I got the order information first, within in it I can't find the customer information in my local sqlite table. As it turns out this is a new customer, whose information will come later from another thread/queue. To make things even worse the customer id I got from the order information is not the same one I used locally. My local customer id is INTEGER PRIMARY KEY (I do record that "true customer id" as another column and set index on it)
>
> So how I do record this order information? I can come up with some clumsy solution, e.g. if I can't find contact info, I insert a record for it first. Later after I get the real information for this customer, I update customer & order table. But I was wondering is there any "standard" way for the situation like this?
> BTW, I did ask the same question at stackoverflow, but because I use sqlite (while all the data come from web storing in MySQL) I was wondering if sqlite has any specific solution for it.
> Thanks,
> Qiulang
This sounds like a problem in definition. Normally one would not 'book'
the order into the main database until it is complete and actually
placed, and at that point you should have all the data (you don't want
order delivery to start shipping the item(s) until the order is
confirmed and payed for) . While the order is being built, you normally
keep track of the data in a separate place, a 'shopping cart', and for
those things, you will be keying to shopping cart id (a session or
cookie id).

--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: If I got many to many relationship data first, how do I insert them to my table?

邱朗
In reply to this post by petern
Hi, Thanks for answering my question. But no matter whether it is "a problem in definition" or other reasons, it already happened in my system (And that was why I asked the question in the first place).
And if we just ignore my particular question, I think it is not uncommon that many to many relationship data come first and how do we deal with it.

Qiulang


At 2017-04-05 17:14:02, "petern" <[hidden email]> wrote:

>Qiulang. I am curious about this requirement. Is there an example commerce
>site in the real world where having a one to one match in a master customer
>login table to all real customers is vital to the mission?  What sort of
>business would have that?  Even banks do not have such surety about
>customer entity if the entity has multiple tax numbers.
>
>It is also normal for customer-centric systems to later allow login
>identities and their transaction histories to be consolidated by manual
>intervention given the login owner wishes it and common identity can be
>satisfactorily established.  There are also practical and lawful reasons
>why some entities would need or prefer to have multiple login identities.
>
>FYI, if you are trying implement a just-in-time shipment consolidation
>function, couldn't an extra order process page/form be added where
>different login with identical name and delivery address makes subsequent
>order(s) during the same shipping cycle?  This condition can be detected
>very easily by simple query over the hypothetical tables you've mentioned.
>
>Peter
>
>
>
>
>
>On Tue, Apr 4, 2017 at 9:38 PM, 邱朗 <[hidden email]> wrote:
>
>> "Another option is to create an "Unknown" customer, and link any new
>> orders to it. You can easily change that parent-id on the order later."This
>> solution seems not work (especially in my case) because it is easy to have
>> more than one unknown customer. Then I can't decide who orders what later.
>>
>>
>> The other solution (from stackoverflow) is to use "Deferred Foreign Key
>> Constraints". That seems quite promising.
>>
>>
>>
>> Thanks,
>> Qiulang
>>
>>
>>
>> At 2017-03-31 19:44:56, "R Smith" <[hidden email]> wrote:
>> >
>> >On 2017/03/31 12:08 PM, 邱朗 wrote:
>> >> Say my mobile app has a customer table, a product table, and an order
>> table to record who buys what, the order table basically has 2 foreign
>> keys, customer_id & product_id.
>> >>
>> >> Now I got the order information first, within in it I can't find the
>> customer information in my local sqlite table. As it turns out this is a
>> new customer, whose information will come later from another thread/queue.
>> To make things even worse the customer id I got from the order information
>> is not the same one I used locally. My local customer id is INTEGER PRIMARY
>> KEY (I do record that "true customer id" as another column and set index on
>> it)
>> >>
>> >> So how I do record this order information? I can come up with some
>> clumsy solution, e.g. if I can't find contact info, I insert a record for
>> it first. Later after I get the real information for this customer, I
>> update customer & order table. But I was wondering is there any "standard"
>> way for the situation like this?
>> >> BTW, I did ask the same question at stackoverflow, but because I use
>> sqlite (while all the data come from web storing in MySQL) I was wondering
>> if sqlite has any specific solution for it.
>> >
>> >Your question would arise no matter which DB you use, so it's not really
>> >an SQLite question - but - we're a fun bunch of people, and many here
>> >would have run into the same problem, so you might find some answers.
>> >
>> >To start with, Your "clumsy" idea is not so clumsy, it is a practice
>> >some people use. The flaw in that is when the customer actually existed
>> >already, you just didn't know who it was, so now you end up with 2
>> >records that mean the same customer with disjointed keys and Orders
>> >connected to both.
>> >
>> >What we usually do is put any orders that are new in a table that looks
>> >exactly like the orders table (only without any foreign key constraints
>> >and such) and then move them to the main order table as soon as they are
>> >confirmed and connected to a customer. This also helps if this is, for
>> >instance, an online interface or help-desk type system and people can
>> >make orders which they might still cancel or amend, etc.
>> >The biggest problem with this method is that for queries on "current
>> >orders" you would need to join the output from the two tables - easy
>> >enough to do, but if you already have millions of lines of code, it
>> >could involve a lot of changes.
>> >
>> >Another option is to create an "Unknown" customer, and link any new
>> >orders to it. You can easily change that parent-id on the order later.
>> >
>> >If however your problem boils down to you not being "sure" if you have
>> >now the correct parent (at the point of insert) - we can't help you with
>> >that. You have to either be sure, or use a temporary situation until you
>> >are.
>> >
>> >Whatever the solution you find, at the point stuff gets inserted to a
>> >permanent table with foreign key constraints, all the correct
>> >constraint-related information/links has to be known.
>> >
>> >HTH - Cheers,
>> >Ryan
>> >
>> >_______________________________________________
>> >sqlite-users mailing list
>> >[hidden email]
>> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...